///////////////////////以下生成 Model
#region 多表时,生成实例类文件
/// <summary>
/// 多表时,生成实例类文件
/// </summary>
/// <param name="ds"></param>
public string ToModels(bool confirmWrite, List <string> tablesName)
{
StringBuilder ret = new StringBuilder();
ProcTable pt = new ProcTable();
ProcString ps = new ProcString();
ps.SetTabs(strNameSpace);
DataSet dsColumnsDescription = pt.GetTablesColumnDecription(tablesName);
foreach (string tableName in tablesName)
{
//if (tableName == " JHPIX_PAT_MASTER_INDEX")
// MessageBox.Show("JHPIX_PAT_MASTER_INDEX类的GetUpdateSQL方法特殊！不可自动生成");
string strClass = "";
TableInfo tableInfo = new TableInfo(strNameSpace, strPrefix, tableName);
DataSet dsTableInfo = tableInfo.dsTableInfo;
DataTable dtColumnType = dsTableInfo.Tables[1];
//DataColumnCollection dtcols = dtColumnType.Columns;
DataTable dtColumnsDescription = null;
string tableDecription = pt.GetTableDesciption(tableName);
for (int i = 0; i < dsColumnsDescription.Tables.Count; i++)
{
dtColumnsDescription = dsColumnsDescription.Tables[i].Copy();
if (dtColumnsDescription.TableName == tableName)
{
dsColumnsDescription.Tables.RemoveAt(i);
break;
}
}
//缺少主键校验
strClass = pt.GetTable(dtColumnType, dtColumnsDescription
, tableDecription
, strPrefix, strNameSpace);
ret.AppendLine(strClass);
#region 写文件
if (confirmWrite)
{
string tem = path + "/Model/";
if (!Directory.Exists(tem))
{
Directory.CreateDirectory(tem);
}
string className = ps.GetClassNameByTableName(tableName, strPrefix.Trim()); //类名大写
//className = ps.GetClassName(className);//lsj
string fullPath = tem + className + ".cs"; //2021-2-2-lsj-首字母大写
Write(fullPath, strClass);
}
#endregion
}
return(ret.ToString());
}
public string GetIndexSQL(TableInfo tableInfo)
{
StringBuilder ret = new StringBuilder();
ProcString ps = new ProcString();
StringBuilder sbBody = new StringBuilder();
#region
sbBody.AppendLine(tableInfo.GetIndexSQL(""));
#endregion
ret.Append(sbBody.ToString());
return(ret.ToString());
}
///////////////////////以上生成 Model
////////////////////////////以下生成 数据访问层
#region 生成单表数据访问层
/// <summary>
/// 生成单表数据访问层
/// </summary>
/// <param name="confirmWrite">是否生成文件</param>
/// <returns></returns>
public string ToDataAccess(bool confirmWrite, List <string> tablesName)
{
StringBuilder ret = new StringBuilder();
ProcTable pt = new ProcTable();
ProcString ps = new ProcString();
ps.SetTabs(strNameSpace);
ps.strImportNameSpace = @"using System;
using System.Data;
using System.Collections.Generic;
";
foreach (string tableName in tablesName)
{
string strMember = @"IDBOperator idb = DBOperator.GetInstance();";
string strClassBody = ps.GetDataAccessClassBody(tableName, strPrefix, strNameSpace);
string str = ps.GetCLassByModel(strNameSpace, strPrefix, tableName, strADO, strMember, strClassBody);
ret.Append(str);
#region 写文件
if (confirmWrite)
{
string tem = path + "/ADO/";
string strClassName = ps.GetClassNameByTableName(tableName, strPrefix);
if (!Directory.Exists(tem))
{
Directory.CreateDirectory(tem);
}
string fullpath = tem + strADO + strClassName + ".cs";
Write(fullpath, ret.ToString());
}
#endregion
}
return(ret.ToString());
}
/// <summary>
/// 生成数据库访问的函数
/// </summary>
/// <param name="strTableName"></param>
/// <param name="strPrefix"></param>
/// <param name="strNamespace"></param>
/// <returns></returns>
public string GetDataAccessEntity(string strTableName
, string strPrefix
, string strNamespace)
{
StringBuilder ret = new StringBuilder();
ProcString ps = new ProcString();
ps.SetTabs(strNamespace);
TableInfo tableInfo = new TableInfo(strNamespace, strPrefix, strTableName);
StringBuilder sbBody = new StringBuilder();
string strClassName = ps.GetClassName(strTableName); //2021-2-2-lsj-类名首字母大写
string strClassObject = strClassName.ToLower(); //2021-2-2-lsj-类对象全部字母小写
sbBody.AppendLine(ps.tabMember + "#region GetObject Function");
#region public GetUserList
sbBody.AppendLine(ps.tabMember + "/// <summary>");
sbBody.AppendLine(ps.tabMember + "/// 获取" + strClassName + "数据");
sbBody.AppendLine(ps.tabMember + "/// <summary>");
sbBody.AppendLine(ps.tabMember + "public List<" + strClassName + "> Get" + strClassName + "List(int Page, int Size)");
sbBody.AppendLine(ps.tabMember + "{");
sbBody.AppendLine(ps.tabLocalVar + "List<" + strClassName + "> " + strClassObject + "s = new List<" + strClassName + ">();");
sbBody.AppendLine(ps.tabLocalVar + "string strSQL = \" select * from \"");
sbBody.AppendLine(ps.tabLocalVar + "+ \" (select *,row_number() over (order by [" + strTableName + "]." + tableInfo.KeyColumns[0] + ") as t \"");
sbBody.Append(ps.tabLocalVar + "+ \" from [" + strTableName + "] ");
if (tableInfo.ExistIsDeleted())
{
sbBody.Append(" where IsDeleted=0 ");
}
sbBody.AppendLine(" ) as o where o.t between @Begin and @End\"; ");
sbBody.AppendLine(ps.tabLocalVar + "SqlParameter[] sp = new SqlParameter[2];");
sbBody.AppendLine(ps.tabLocalVar + "sp[0] = new SqlParameter(\"@Begin\", (Page - 1) * Size + 1);");
sbBody.AppendLine(ps.tabLocalVar + "sp[1] = new SqlParameter(\"@End\", Page * Size);");
sbBody.AppendLine(ps.tabLocalVar + "using (SqlDataReader dr = new DbHelper().ExcuteReader(strSQL, sp)){");
sbBody.AppendLine(ps.tabLocalVar + " while (dr.Read()){");
sbBody.AppendLine(ps.tabLocalVar + " " + strClassName + " " + strClassObject + " = new " + strClassName + "();");
sbBody.AppendLine(tableInfo.GetEntity(strClassName));
sbBody.AppendLine(ps.tabLocalVar + " " + strClassObject + "s.Add(" + strClassObject + ");}}");
sbBody.AppendLine(ps.tabLocalVar + "return " + strClassObject + "s;");
sbBody.AppendLine(ps.tabMember + "}");
#endregion
#region public GetUsersCount
sbBody.AppendLine(ps.tabMember + "/// <summary>");
sbBody.AppendLine(ps.tabMember + "/// 获取" + strClassName + "总数");
sbBody.AppendLine(ps.tabMember + "/// <summary>");
sbBody.AppendLine(ps.tabMember + "public int Get" + strClassName + "sCount()");
sbBody.AppendLine(ps.tabMember + "{");
sbBody.Append(ps.tabLocalVar + "string strSQL = \" select count(*) from [" + strTableName + "] ");
if (tableInfo.ExistIsDeleted())
{
sbBody.Append(" where IsDeleted=0 ");
}
sbBody.AppendLine(" \"; ");
sbBody.AppendLine(ps.tabLocalVar + "using (SqlDataReader dr = new DbHelper().ExcuteReader(strSQL)){");
sbBody.AppendLine(ps.tabLocalVar + " if (dr.Read()){");
sbBody.AppendLine(ps.tabLocalVar + " return dr[0] == DBNull.Value ? 0 : Convert.ToInt32(dr[0].ToString());}");
sbBody.AppendLine(ps.tabLocalVar + " else return -1;}");
sbBody.AppendLine(ps.tabMember + "}");
#endregion
#region public GetUserList
sbBody.AppendLine(ps.tabMember + "/// <summary>");
sbBody.AppendLine(ps.tabMember + "/// 获取" + strClassName + "数据");
sbBody.AppendLine(ps.tabMember + "/// <summary>");
sbBody.AppendLine(ps.tabMember + "public List<" + strClassName + "> Get" + strClassName + "List()");
sbBody.AppendLine(ps.tabMember + "{");
sbBody.AppendLine(ps.tabLocalVar + "List<" + strClassName + "> " + strClassObject + "s = new List<" + strClassName + ">();");
sbBody.AppendLine(ps.tabLocalVar + "string strSQL = \" select * from [" + strTableName + "]\";");
sbBody.AppendLine(ps.tabLocalVar + "using (SqlDataReader dr = new DbHelper().ExcuteReader(strSQL)){");
sbBody.AppendLine(ps.tabLocalVar + " while (dr.Read()){");
sbBody.AppendLine(ps.tabLocalVar + " " + strClassName + " " + strClassObject + " = new " + strClassName + "();");
sbBody.AppendLine(tableInfo.GetEntity(strClassName));
sbBody.AppendLine(ps.tabLocalVar + " " + strClassObject + "s.Add(" + strClassObject + ");}}");
sbBody.AppendLine(ps.tabLocalVar + "return " + strClassObject + "s;");
sbBody.AppendLine(ps.tabMember + "}");
if (tableInfo.ExistIsDeleted())
{
sbBody.AppendLine(ps.tabMember + "/// <summary>");
sbBody.AppendLine(ps.tabMember + "/// 获取" + strClassName + "数据 ");
sbBody.AppendLine(ps.tabMember + "/// <summary>");
sbBody.AppendLine(ps.tabMember + "public List<" + strClassName + "> Get" + strClassName + "ListNotDeleted()");
sbBody.AppendLine(ps.tabMember + "{");
sbBody.AppendLine(ps.tabLocalVar + "List<" + strClassName + "> " + strClassObject + "s = new List<" + strClassName + ">();");
sbBody.AppendLine(ps.tabLocalVar + "string strSQL = \" select * from [" + strTableName + "] Where IsDeleted=0\";");
sbBody.AppendLine(ps.tabLocalVar + "using (SqlDataReader dr = new DbHelper().ExcuteReader(strSQL)){");
sbBody.AppendLine(ps.tabLocalVar + " while (dr.Read()){");
sbBody.AppendLine(ps.tabLocalVar + " " + strClassName + " " + strClassObject + " = new " + strClassName + "();");
sbBody.AppendLine(tableInfo.GetEntity(strClassName));
sbBody.AppendLine(ps.tabLocalVar + " " + strClassObject + "s.Add(" + strClassObject + ");}}");
sbBody.AppendLine(ps.tabLocalVar + "return " + strClassObject + "s;");
sbBody.AppendLine(ps.tabMember + "}");
}
#endregion
sbBody.AppendLine(ps.tabMember + "#endregion GetObject Function");
ret.Append(sbBody.ToString());
return(ret.ToString());
}
/// <summary>
/// 生成访问层类的主体
/// </summary>
/// <param name="strTableName">表名</param>
/// <param name="strPrefix">表名前缀(要处理的)</param>
/// <param name="strNamespace">名称空间</param>
/// <returns></returns>
public string GetDataAccessClassBody_SQL(string strTableName
, string strPrefix
, string strNamespace)
{
StringBuilder ret = new StringBuilder();
ProcString ps = new ProcString();
ps.SetTabs(strNamespace);
TableInfo tableInfo = new TableInfo(strNamespace, strPrefix, strTableName);
StringBuilder sbBody = new StringBuilder();
sbBody.AppendLine(ps.tabMember + "#region SetSQL Function");
#region public ind SetInsert()
sbBody.AppendLine(ps.tabMember + "/// <summary>");
sbBody.AppendLine(ps.tabMember + "/// 获取添加数据的SQL语句");
sbBody.AppendLine(ps.tabMember + "/// <summary>");
sbBody.Append(ps.tabMember + "public string SetInsert()");
sbBody.AppendLine(ps.tabMember + "{");
sbBody.AppendLine(ps.tabLocalVar + "string strSQL = \"INSERT INTO [" + strTableName + "] (" + tableInfo.GetInsertFields("") + ") VALUES (" + tableInfo.GetInsertValues_sp() + ")\";");
if (strTableName != "Area")
{
sbBody.AppendLine(ps.tabLocalVar + tableInfo.GetKeyValue() + "= snowflakeIdWorker.NextId().ToString();");
}
sbBody.AppendLine(tableInfo.GetSqlParameter(ColumnsType.AllColumns));
sbBody.AppendLine(ps.tabLocalVar + "new DbHelper().ExcuteNonQuery(strSQL,sp);");
sbBody.AppendLine(ps.tabLocalVar + "return " + tableInfo.GetKeyValue() + ";");
sbBody.AppendLine(ps.tabMember + "}");
//事务处理
sbBody.Append(ps.tabMember + "public string SetInsertForTransaction(DbHelper dbHelper)");
sbBody.AppendLine(ps.tabMember + "{");
sbBody.AppendLine(ps.tabLocalVar + "string strSQL = \"INSERT INTO [" + strTableName + "] (" + tableInfo.GetInsertFields("") + ") VALUES (" + tableInfo.GetInsertValues_sp() + ")\";");
sbBody.AppendLine(ps.tabLocalVar + tableInfo.GetKeyValue() + "= snowflakeIdWorker.NextId().ToString();");
sbBody.AppendLine(tableInfo.GetSqlParameter(ColumnsType.AllColumns));
sbBody.AppendLine(ps.tabLocalVar + "dbHelper.ExcuteNonQueryForTransaction(strSQL,sp);");
sbBody.AppendLine(ps.tabLocalVar + "return " + tableInfo.GetKeyValue() + ";");
sbBody.AppendLine(ps.tabMember + "}");
#endregion
#region public ind Update()
//-----------
if (tableInfo.KeyColumns.Length != tableInfo.AllColumns.Length)//关系表没有数据需要更新，只有主外键。如需更新，删除重新添加。
{
sbBody.AppendLine(ps.tabMember + "/// <summary>");
sbBody.AppendLine(ps.tabMember + "/// 更新数据");
sbBody.AppendLine(ps.tabMember + "/// <summary>");
sbBody.AppendLine(ps.tabMember + "public int SetUpdateForAll()");
sbBody.AppendLine(ps.tabMember + "{");
//20210317--lsj--作废，使用Parameter
//sbBody.AppendLine(ps.tabLocalVar + "string strSQL = \"UPDATE [" + strTableName + "] SET " + tableInfo.GetUpdateValues(""));
sbBody.AppendLine(ps.tabLocalVar + "string strSQL = \"UPDATE [" + strTableName + "] SET " + tableInfo.GetUpdateValues_Parameter());
sbBody.AppendLine(tableInfo.GetSqlParameter(ColumnsType.AllColumns));
sbBody.AppendLine(ps.tabLocalVar + "return new DbHelper().ExcuteNonQuery(strSQL,sp);");
sbBody.AppendLine(ps.tabMember + "}");
//-----------
sbBody.AppendLine(ps.tabMember + "/// <summary>");
sbBody.AppendLine(ps.tabMember + "/// 更新部分数据");
sbBody.AppendLine(ps.tabMember + "/// <summary>");
sbBody.AppendLine(ps.tabMember + "public int SetUpdate()");
sbBody.AppendLine(ps.tabMember + "{");
//20210317--lsj--作废，使用Parameter
//sbBody.AppendLine(ps.tabLocalVar + "string strSQL = \"UPDATE [" + strTableName + "] SET " + tableInfo.GetUpdateValues(""));
sbBody.AppendLine(ps.tabLocalVar + "bool boolPartial = false;");
sbBody.AppendLine(ps.tabLocalVar + "string strSQL = \"UPDATE [" + strTableName + "] SET \";");
sbBody.AppendLine(tableInfo.GetUpdateValues_ParameterForPartial());
sbBody.AppendLine(tableInfo.GetSqlParameter(ColumnsType.AllColumns));
sbBody.AppendLine(ps.tabLocalVar + "return new DbHelper().ExcuteNonQuery(strSQL,sp);");
sbBody.AppendLine(ps.tabMember + "}");
//事务处理
sbBody.AppendLine(ps.tabMember + "public int SetUpdateForTransaction(DbHelper dbHelper)");
sbBody.AppendLine(ps.tabMember + "{");
//20210317--lsj--作废，使用Parameter
//sbBody.AppendLine(ps.tabLocalVar + "string strSQL = \"UPDATE [" + strTableName + "] SET " + tableInfo.GetUpdateValues(""));
sbBody.AppendLine(ps.tabLocalVar + "bool boolPartial = false;");
sbBody.AppendLine(ps.tabLocalVar + "string strSQL = \"UPDATE [" + strTableName + "] SET \";");
sbBody.AppendLine(tableInfo.GetUpdateValues_ParameterForPartial());
sbBody.AppendLine(tableInfo.GetSqlParameter(ColumnsType.AllColumns));
sbBody.AppendLine(ps.tabLocalVar + "return dbHelper.ExcuteNonQueryForTransaction(strSQL,sp);");
sbBody.AppendLine(ps.tabMember + "}");
//-----------
#region public ind Delete()
sbBody.AppendLine(ps.tabMember + "/// <summary>");
sbBody.AppendLine(ps.tabMember + "/// 获取删除数据的SQL语句");
sbBody.AppendLine(ps.tabMember + "/// <summary>");
sbBody.AppendLine(ps.tabMember + "public int SetDelete(string " + tableInfo.GetKeyValue() + ")");
sbBody.AppendLine(ps.tabMember + "{");
if (tableInfo.ExistIsDeleted())
{
if (tableInfo.ExistChangeTime())
{
sbBody.AppendLine(ps.tabLocalVar + "string strSQL = \"update [" + strTableName + "] set IsDeleted=1, ChangeTime=getdate() WHERE " + tableInfo.GetDeleteValues_sp());
}
else
{
sbBody.AppendLine(ps.tabLocalVar + "string strSQL = \"update [" + strTableName + "] set IsDeleted=1 WHERE " + tableInfo.GetDeleteValues_sp());
}
}
else
{
sbBody.AppendLine(ps.tabLocalVar + "string strSQL = \"DELETE [" + strTableName + "] WHERE " + tableInfo.GetDeleteValues_sp());
}
sbBody.AppendLine(tableInfo.GetSqlParameter(ColumnsType.KeyColumns));
sbBody.AppendLine(ps.tabLocalVar + "return new DbHelper().ExcuteNonQuery(strSQL,sp);");
sbBody.AppendLine(ps.tabMember + "}");
sbBody.AppendLine(ps.tabMember + "/// <summary>");
sbBody.AppendLine(ps.tabMember + "/// 获取删除数据的SQL语句");
sbBody.AppendLine(ps.tabMember + "/// <summary>");
sbBody.AppendLine(ps.tabMember + "public int SetDelete()");
sbBody.AppendLine(ps.tabMember + "{");
if (tableInfo.ExistIsDeleted())
{
if (tableInfo.ExistChangeTime())
{
sbBody.AppendLine(ps.tabLocalVar + "string strSQL = \"update [" + strTableName + "] set IsDeleted=1, ChangeTime=getdate() WHERE " + tableInfo.GetDeleteValues_sp());
}
else
{
sbBody.AppendLine(ps.tabLocalVar + "string strSQL = \"update [" + strTableName + "] set IsDeleted=1 WHERE " + tableInfo.GetDeleteValues_sp());
}
}
else
{
sbBody.AppendLine(ps.tabLocalVar + "string strSQL = \"DELETE [" + strTableName + "] WHERE " + tableInfo.GetDeleteValues_sp());
}
sbBody.AppendLine(tableInfo.GetSqlParameter(ColumnsType.KeyColumns));
sbBody.AppendLine(ps.tabLocalVar + "return new DbHelper().ExcuteNonQuery(strSQL,sp);");
sbBody.AppendLine(ps.tabMember + "}");
#endregion
//lsj--类名
//string strClassName = strTableName;//ps.GetClassName(strTableName);//lsj
string strClassName = ps.GetClassName(strTableName);//2021-2-2-lsj-类名首字母大写
//lsj--小写的类名--在此做类的对象来用。
string strLowerClassName = ps.ConvertStringToUpperOrLower(strClassName, false);
//------------------------------
}
#endregion
sbBody.AppendLine(ps.tabMember + "#endregion SetSQL Function");
ret.Append(sbBody.ToString());
return(ret.ToString());
}
/// <summary>
/// 生成访问层类的主体
/// </summary>
/// <param name="strTableName">表名</param>
/// <param name="strPrefix">表名前缀(要处理的)</param>
/// <param name="strNamespace">名称空间</param>
/// <returns></returns>
public string GetDataAccessClassBody(string strTableName
, string strPrefix
, string strNamespace)
{
StringBuilder ret = new StringBuilder();
ProcString ps = new ProcString();
ps.SetTabs(strNamespace);
TableInfo tableInfo = new TableInfo(strNamespace, strPrefix, strTableName);
StringBuilder sbBody = new StringBuilder();
string className = tableInfo.ClassName;
string classObject = tableInfo.ClassObject;
string tableDescription = string.IsNullOrEmpty(tableInfo.tableDescription) ? "" : tableInfo.tableDescription + " ";
string strAddOrUpdateParameters = tableInfo.GetParamaters(ColumnsType.NonIdentityColumns, "idb", classObject);
#region public ind Add()
sbBody.AppendLine(ps.tabMember + "/// <summary>");
sbBody.AppendLine(ps.tabMember + "/// 添加" + tableDescription + className + "对象(即:一条记录");
sbBody.AppendLine(ps.tabMember + "/// <summary>");
sbBody.AppendLine(ps.tabMember + "public int Add(" + className + " " + classObject + ")");
sbBody.AppendLine(ps.tabMember + "{");
sbBody.AppendLine(ps.tabLocalVar + "string sql = \"INSERT INTO " + strTableName + " (" + tableInfo.GetInsertFields("") + ") VALUES (" + tableInfo.GetInsertFields("@") + ")\";");
sbBody.AppendLine(strAddOrUpdateParameters);
sbBody.AppendLine(ps.tabLocalVar + "return idb.ExeCmd(sql);");
sbBody.AppendLine(ps.tabMember + "}");
#endregion
#region public ind Update()
sbBody.AppendLine(ps.tabMember + "/// <summary>");
sbBody.AppendLine(ps.tabMember + "/// 更新" + tableDescription + className + "对象(即:一条记录");
sbBody.AppendLine(ps.tabMember + "/// <summary>");
sbBody.AppendLine(ps.tabMember + "public int Update(" + className + " " + classObject + ")");
sbBody.AppendLine(ps.tabMember + "{");
sbBody.AppendLine(ps.tabLocalVar + "string sql = \"UPDATE " + strTableName + " SET " + tableInfo.GetUpdateFields() + " WHERE 1=1 \";");
sbBody.AppendLine(strAddOrUpdateParameters);
sbBody.AppendLine(ps.tabLocalVar + "return idb.ExeCmd(sql);");
sbBody.AppendLine(ps.tabMember + "}");
#endregion
string strKeyParameters = tableInfo.GetParamaters(ColumnsType.KeyColumns, "idb");
#region public ind Delete(string var1,.....)
sbBody.AppendLine(ps.tabMember + "/// <summary>");
sbBody.AppendLine(ps.tabMember + "/// 删除" + tableDescription + className + "对象(即:一条记录");
sbBody.AppendLine(ps.tabMember + "/// <summary>");
sbBody.AppendLine(ps.tabMember + "public int Delete(" + tableInfo.GetFunctionParams() + ")");
sbBody.AppendLine(ps.tabMember + "{");
sbBody.AppendLine(ps.tabLocalVar + "string sql = \"DELETE " + strTableName + " WHERE 1=1 " + tableInfo.GetDeleteFields() + " \";");
sbBody.AppendLine(strKeyParameters);
sbBody.AppendLine(ps.tabLocalVar + "return idb.ExeCmd(sql);");
sbBody.AppendLine(ps.tabMember + "}");
#endregion
strKeyParameters = tableInfo.GetParamaters(ColumnsType.KeyColumns, "idb");
#region public ind GetByKey(string var1,.....)
sbBody.AppendLine(ps.tabMember + "/// <summary>");
sbBody.AppendLine(ps.tabMember + "/// 获取指定的" + tableDescription + className + "对象(即:一条记录");
sbBody.AppendLine(ps.tabMember + "/// <summary>");
sbBody.AppendLine(ps.tabMember + "public List<" + className + "> GetByKey(" + tableInfo.GetFunctionParams() + ")");
sbBody.AppendLine(ps.tabMember + "{");
sbBody.AppendLine(ps.tabLocalVar + "List<" + className + "> ret = new List<" + className + ">();");
sbBody.AppendLine(ps.tabLocalVar + "string sql = \"SELECT " + tableInfo.AllColumnsString + " FROM " + strTableName + " WHERE 1=1 " + tableInfo.GetWhereFields(ColumnsType.KeyColumns, " AND ") + " \";");
sbBody.AppendLine(strKeyParameters);
sbBody.AppendLine(ps.tabLocalVar + "DataTable dt = idb.ReturnDataTable(sql);");
sbBody.AppendLine(ps.tabLocalVar + "foreach (DataRow dr in dt.Rows)");
sbBody.AppendLine(ps.tabLocalVar + "{");
sbBody.AppendLine(ps.tabIfLocalVarTop1 + className + " " + classObject + " = new " + className + "();");
string[] all = tableInfo.AllColumns;
foreach (string field in all)
{
sbBody.AppendLine(ps.tabIfLocalVarTop1 + tableInfo.GetPropertyString(field));
}
sbBody.AppendLine(ps.tabIfLocalVarTop1 + "ret.Add(" + classObject + ");");
sbBody.AppendLine(ps.tabLocalVar + "}");
sbBody.AppendLine(ps.tabLocalVar + "return ret;");
sbBody.AppendLine(ps.tabMember + "}");
#endregion
//strKeyParameters = tableInfo.GetParamaters(ColumnsType.KeyColumns, "idb", classObject);
#region public ind GetAll()
sbBody.AppendLine(ps.tabMember + "/// <summary>");
sbBody.AppendLine(ps.tabMember + "/// 获取所有的" + tableDescription + className + "对象(即:一条记录");
sbBody.AppendLine(ps.tabMember + "/// <summary>");
sbBody.AppendLine(ps.tabMember + "public List<" + className + "> GetAll()");
sbBody.AppendLine(ps.tabMember + "{");
sbBody.AppendLine(ps.tabLocalVar + "List<" + className + "> ret = new List<" + className + ">();");
sbBody.AppendLine(ps.tabLocalVar + "string sql = \"SELECT " + tableInfo.AllColumnsString + " FROM " + strTableName + " \";");
//sbBody.AppendLine(strKeyParameters);
sbBody.AppendLine(ps.tabLocalVar + "DataTable dt = idb.ReturnDataTable(sql);");
sbBody.AppendLine(ps.tabLocalVar + "foreach (DataRow dr in dt.Rows)");
sbBody.AppendLine(ps.tabLocalVar + "{");
sbBody.AppendLine(ps.tabIfLocalVarTop1 + className + " " + classObject + " = new " + className + "();");
//string[] all = tableInfo.AllColumns;
foreach (string field in all)
{
sbBody.AppendLine(ps.tabIfLocalVarTop1 + tableInfo.GetPropertyString(field));
}
sbBody.AppendLine(ps.tabIfLocalVarTop1 + "ret.Add(" + classObject + ");");
sbBody.AppendLine(ps.tabLocalVar + "}");
sbBody.AppendLine(ps.tabLocalVar + "return ret;");
sbBody.AppendLine(ps.tabMember + "}");
#endregion
ret.Append(sbBody.ToString());
return(ret.ToString());
}
/// <summary>
/// 生成数据库文档
/// </summary>
/// <param name="tablesName"></param>
public void ToWord(List <string> tablesName)
{
ProcTable pt = new ProcTable();
DataSet dsColumnsDescription = pt.GetTablesColumnDecription(tablesName);
string fullPath = path + "/数据库文档.doc";
ProcWord word = new ProcWord();
word.FileName = fullPath;
foreach (string tableName in tablesName)
{
TableInfo tableInfo = new TableInfo(strNameSpace, strPrefix, tableName);
DataSet dsTableInfo = tableInfo.dsTableInfo; //0表名 1列类型 2标识列
DataTable dtColumnType = tableInfo.ColumnType; //列名0 类型1 长度3 可空6
DataTable dtIdentity = tableInfo.dsTableInfo.Tables[2]; //列名0 seed 1 increment 2
string[] keyColumns = tableInfo.KeyColumns; //主键列
DataTable dtColumnsDescription = pt.GetTableColumnDecription(tableName); //列名0 说明1
//默认值
DataTable dtConstraint = null;//constraint_type(DEFAULT) constraint_keys默认值
//表关系
DataTable dtRelation = null;
if (tableInfo.dsTableInfo.Tables.Count > 6)
{
dtConstraint = tableInfo.dsTableInfo.Tables[6]; //constraint_type(DEFAULT) constraint_keys默认值
dtRelation = tableInfo.dsTableInfo.Tables[6]; //column 0
}
word.WriteText("表名:" + tableName + " [" + tableInfo.tableDescription + "]");
int rows = dtColumnType.Rows.Count;
word.AddParagraph();
word.AddTable(rows + 1, 7);
#region 添加列头
word.AddTableContent(1, 1, "列名", 1, Microsoft.Office.Interop.Word.WdColor.wdColorBlack);
word.AddTableContent(1, 2, "类型(长度)", 1, Microsoft.Office.Interop.Word.WdColor.wdColorBlack);
word.AddTableContent(1, 3, "可空", 1, Microsoft.Office.Interop.Word.WdColor.wdColorBlack);
word.AddTableContent(1, 4, "默认值", 1, Microsoft.Office.Interop.Word.WdColor.wdColorBlack);
word.AddTableContent(1, 5, "标识", 1, Microsoft.Office.Interop.Word.WdColor.wdColorBlack);
word.AddTableContent(1, 6, "主键", 1, Microsoft.Office.Interop.Word.WdColor.wdColorBlack);
word.AddTableContent(1, 7, "列说明", 1, Microsoft.Office.Interop.Word.WdColor.wdColorBlack);
#endregion
ProcString ps = new ProcString();
string strColName = "";
string strIdentity = "";
string strPrimary = "";
string strDefault = "";
string strColDescription = "";
string tem = "";
int j = 1;
for (int i = 0; i < rows; i++)
{
j++;
//列名
strColName = dtColumnType.Rows[i][0].ToString();
word.AddTableContent(j, 1, strColName);
//列类型(长度)
word.AddTableContent(j, 2, dtColumnType.Rows[i][1].ToString() + "(" + dtColumnType.Rows[i][3].ToString() + ")");
//可空
word.AddTableContent(j, 3, dtColumnType.Rows[i][6].ToString());
//默认值
#region 默认值
if (null != dtConstraint && dtConstraint.Rows.Count > 0)
{
foreach (DataRow dr in dtConstraint.Rows)
{
tem = dr[0].ToString();
if (tem.IndexOf("DEFAULT") >= 0)
{
tem = tem.Substring(18);
if (tem == strColName)
{
strDefault = dr["constraint_keys"].ToString();
break;
}
}
strDefault = "";
}
}
#endregion
word.AddTableContent(j, 4, strDefault);
//标识
#region 标识
if (null != dtIdentity && dtIdentity.Rows.Count > 0)
{
foreach (DataRow dr in dtIdentity.Rows)
{
if (strColName == dr[0].ToString())
{
strIdentity = "yes(" + dr[1].ToString() + "," + dr[2].ToString() + ")";
break;
}
strIdentity = "";
}
}
#endregion
word.AddTableContent(j, 5, strIdentity);
//主键
strPrimary = ps.IsContains(strColName, tableInfo.KeyColumns) ? "yes" : "";
word.AddTableContent(j, 6, strPrimary);
//列说明
#region 列说明
if (null != dtColumnsDescription && dtColumnsDescription.Rows.Count > 0)
{
foreach (DataRow dr in dtColumnsDescription.Rows)
{
if (strColName == dr[0].ToString())
{
strColDescription = dr[1].ToString();
break;
}
strColDescription = "";
}
}
#endregion
word.AddTableContent(j, 7, strColDescription);
}
#region 表关系
if (dtRelation.Rows.Count > 0)
{
tem = "";
foreach (DataRow dr in dtRelation.Rows)
{
if (dr["status_enabled"].ToString() == "Enabled")
{
tem += dr["constraint_name"].ToString() + "[" + dr["constraint_keys"].ToString() + "]" + " , ";
}
}
if (tem.EndsWith(" , "))
{
tem = tem.Substring(0, tem.Length - 3);
word.WriteText("表关系:" + tem);
}
}
#endregion
word.AddParagraph();
word.AddParagraph();
}//end foreach
word.SaveAs();
}
/// <summary>
/// 单表时,生成实例类文件
/// </summary>
/// <param name="tableName">表名</param>
/// <returns></returns>
public string ToModel(bool confirmWrite, string tableName)
{
StringBuilder ret = new StringBuilder();
ProcTable pt = new ProcTable();
ProcString ps = new ProcString();
ps.SetTabs(strNameSpace);
DataTable dtColumnDescription = pt.GetTableColumnDecription(tableName);
string strTableDescription = pt.GetTableDesciption(tableName);
DataTable dtColumnType = pt.GetTableInfo(tableName).Tables[1];
string strClassName = ps.GetClassNameByTableName(tableName, strPrefix);
StringBuilder sbBody = new StringBuilder();
TableInfo tableInfo = new TableInfo(strNameSpace, strPrefix, tableName);
foreach (DataRow drType in dtColumnDescription.Rows)
{
string columnName = drType["ColumnName"].ToString(); //列名
string columnType = drType["ColumnDescription"].ToString(); //列数据类型
string firstLowerColumnName = ""; //首字母小写,用于字段
string firstUpperColumnName = ""; //首字母大写,用于属性
#region 以下为字段
foreach (DataRow drDescription in dtColumnDescription.Rows)
{
if (drDescription["ColumnName"].ToString() == columnName)
{
string strColumnDescription = drDescription["ColumnDescription"].ToString();
sbBody.AppendLine(ps.tabMember + "/// <summary>");
sbBody.AppendLine(ps.tabMember + "/// " + strColumnDescription);
sbBody.AppendLine(ps.tabMember + "/// <summary>");
}
}
firstLowerColumnName = ps.ConvertStringToUpperOrLower(columnName, false);
//if (!string.IsNullOrEmpty(columnDefault))
//{
// if (columnName.ToUpper() != "HOSPITAL_NO")
// {
// sbBody.AppendLine(ps.tabMember + "private " + ps.ConvertType(columnType, columnName, tableInfo) + " " + firstLowerColumnName + "=\"" + columnDefault + "\";");
// }
//}
//else
//{
sbBody.AppendLine(ps.tabMember + "private " + ps.ConvertType(columnType, columnName, tableInfo) + " " + firstLowerColumnName + ";");
//}
#endregion
#region 以下为属性
foreach (DataRow drDescription in dtColumnDescription.Rows)
{
if (drDescription["ColumnName"].ToString() == columnName)
{
string strColumnDescription = drDescription["ColumnDescription"].ToString();
sbBody.AppendLine(ps.tabMember + "/// <summary>");
sbBody.AppendLine(ps.tabMember + "/// " + strColumnDescription);
sbBody.AppendLine(ps.tabMember + "/// <summary>");
}
}
firstUpperColumnName = ps.ConvertStringToUpperOrLower(columnName, true);
sbBody.AppendLine(ps.tabMember + "public " + ps.ConvertType(columnType, columnName, tableInfo) + " " + firstUpperColumnName);
sbBody.AppendLine(ps.tabMember + "{");
sbBody.AppendLine(ps.tabLocalVar + "get{ return " + firstLowerColumnName + "; }");
sbBody.AppendLine(ps.tabLocalVar + "set{ " + firstLowerColumnName + " = value; }");
sbBody.AppendLine(ps.tabMember + "}");
#endregion
sbBody.AppendLine();
}
string strClass = ps.GetCLassByModel(strNameSpace, strPrefix, tableName, sbBody.ToString());
ret.Append(strClass);
#region 写文件
if (confirmWrite)
{
string tem = path + "/Model/";
if (!Directory.Exists(tem))
{
Directory.CreateDirectory(tem);
}
string fullpath = tem + strClassName + ".cs";
Write(fullpath, ret.ToString());
}
#endregion
return(ret.ToString());
}
public string GetIndexSQL(string strTableName, out string strPKTableName, out string strWithoutPKTableName
, out string strNotReadTableName, string HOSPITAL_NO)
{
strNotReadTableName = "";
strWithoutPKTableName = "";
strPKTableName = "";
try
{
StringBuilder ret = new StringBuilder();
ProcString ps = new ProcString();
//lsj--类名
string strClassName = strTableName;//ps.GetClassName(strTableName);//lsj
//lsj--小写的类名--在此做类的对象来用。
string strLowerClassName = ps.ConvertStringToUpperOrLower(strClassName, false);
TableInfo tableInfo = new TableInfo("", "", strTableName);
StringBuilder sbBody = new StringBuilder();
//---------------------------------------------
//if (strTableName.Substring(0, 2) != "JH"
// && (tableInfo.CheckColumnName("PATIENT_ID") //表中需要有PATIENT_ID
// && tableInfo.CheckColumnName("HOSPITAL_NO"))
// && tableInfo.strPKName != "")//有主键
//{
// strReadTableName = strTableName;
//}
if (strTableName.Substring(0, 2) != "JH"
//&& (tableInfo.CheckColumnName("PATIENT_ID") //表中需要有PATIENT_ID
&& !tableInfo.CheckColumnName("HOSPITAL_NO"))
// && tableInfo.strPKName != "")//有主键
{
sbBody.AppendLine(" ------------" + strTableName + "------------------------");
sbBody.AppendLine(" EXECUTE IMMEDIATE 'alter table " + strTableName + " add HOSPITAL_NO VARCHAR2(16) default " + HOSPITAL_NO + " not null';");
if (!string.IsNullOrEmpty(tableInfo.strPKName))
{
strPKTableName = strTableName;
sbBody.AppendLine(" EXECUTE IMMEDIATE 'alter table " + strTableName + " drop constraint " + tableInfo.strPKName + " cascade';");
sbBody.AppendLine(" select count(*) into index_count from user_indexes where index_name='" + tableInfo.strPKName + "';");
sbBody.AppendLine(" if index_count > 0 then");
sbBody.AppendLine(" EXECUTE IMMEDIATE 'drop index " + tableInfo.strPKName + "';");
sbBody.AppendLine(" end if;");
sbBody.AppendLine(" EXECUTE IMMEDIATE 'alter table " + strTableName + " add constraint " + tableInfo.strPKName
+ " primary key (" + ps.GetIndexSQL(tableInfo) + ", HOSPITAL_NO)';");
}
else//无主键的表不修改主键也不增加主键
{
strWithoutPKTableName = strTableName;
}
ret.Append(sbBody.ToString());
}
else
{
strNotReadTableName = strTableName;
ret.Append(sbBody.ToString());
}
return(ret.ToString());
}
catch (Exception ex)
{
string st = ex.Message;
return(null);
}
}
/// <summary>
/// 多表时,生成单表实体字符串
/// </summary>
/// <param name="dsColumnType">列类型表</param>
/// <param name="dtColumnDescription">列描述表</param>
/// <param name="dtDescription">表描述(表)</param>
/// <returns></returns>
public string GetTable(DataTable dtColumnType
, DataTable dtColumnDescription
, string strTableDescription
, string strPrefix
, string strNamespace)
{
StringBuilder ret = new StringBuilder();
string strTableName = dtColumnType.TableName;
//lsj--构造方法1所用的参数默认值
string strDefaultValue = "";
//lsj--构造方法2所用的参数列表
string strPara = "";
//lsj--构造方法3的赋值
int index = 0;
string strIndex = "";
string strValue = "";
//lsj--构造方法1所用的赋值语句。
string[] strSetValues1 = new string[dtColumnType.Rows.Count];
//lsj--构造方法2所用的赋值语句。
string[] strSetValues2 = new string[dtColumnType.Rows.Count];
ProcString ps = new ProcString();
ps.SetTabs(strNamespace);
//lsj--类名
string strClassName = ps.GetClassName(strTableName);//2021-2-2-lsj
//lsj--小写的类名--在此做类的对象来用。
string strLowerClassName = ps.ConvertStringToUpperOrLower(strClassName, false);
TableInfo tableInfo = new TableInfo(strNamespace, strPrefix, strTableName);
if (string.IsNullOrEmpty(tableInfo.strPKName))
{
return("");
}
//---------------------------------------------
#region Member Variables / Attributes 属性
StringBuilder sbBody = new StringBuilder();
sbBody.AppendLine(ps.tabMember + "#region Member Variables");
sbBody.AppendLine(ps.tabMember + "SnowflakeIdWorker snowflakeIdWorker = new SnowflakeIdWorker();//雪花ID");
for (int i = 0; i < dtColumnType.Rows.Count; i++)
{
DataRow drType = dtColumnType.Rows[i];
string columnName = drType["Column_name"].ToString(); //列名
string columnType = drType["Type"].ToString(); //列数据类型
//string columnDefault = drType["Data_default"].ToString().Trim();//列默认值//增加默认值 lsj@20130222--不支持SYSTIMESTAMP等系统函数
//2021-2-2-lsj--未找到默认值
string columnDefault = "";
string firstLowerColumnName = "";//首字母小写,用于字段
#region 以下为字段
firstLowerColumnName = ps.ConvertStringToUpperOrLower(columnName, false);
//if (tableInfo.KeyColumns.Length>1 && tableInfo.KeyColumns[0] == columnName && tableInfo.KeyType == "NUMBER")
// sbBody.AppendLine(ps.tabMember + "private Int64 " + firstLowerColumnName + ";");
//else
//{
// sbBody.AppendLine(ps.tabMember + "private " + ps.ConvertType(columnType, columnName, tableInfo) + " " + firstLowerColumnName + ";");
//}
//列默认值//增加默认值 lsj@20130222--不支持SYSTIMESTAMP等系统函数
sbBody.AppendLine(ps.tabMember + "private " + ps.ConvertType(columnType, columnName, tableInfo) + " " + firstLowerColumnName + ";");
#endregion
}
sbBody.AppendLine(ps.tabMember + "#endregion");
sbBody.AppendLine(ps.tabMember + "#region Attributes");
for (int n = 0; n < dtColumnType.Rows.Count; n++)
{
DataRow drType = dtColumnType.Rows[n];
string columnName = drType["Column_name"].ToString(); //列名
string columnType = drType["Type"].ToString(); //列数据类型
//string columnDefault = drType["Data_default"].ToString().Trim();//列默认值//增加默认值 lsj@20130222--不支持SYSTIMESTAMP等系统函数
//2021-2-2-lsj--未找到默认值
string columnDefault = "";
string firstLowerColumnName = ""; //首字母小写,用于字段
string firstUpperColumnName = ""; //首字母大写,用于属性
string strColumnDescription = ""; //列描述
firstLowerColumnName = ps.ConvertStringToUpperOrLower(columnName, false);
firstUpperColumnName = ps.ConvertStringToUpperOrLower(columnName, true);
if (null != dtColumnDescription)
{
for (int i = 0; i < dtColumnDescription.Rows.Count; i++)
{
string strColumnName = dtColumnDescription.Rows[i]["ColumnName"].ToString();
if (strColumnName == columnName)
{
strColumnDescription = dtColumnDescription.Rows[i]["ColumnDescription"].ToString();
dtColumnDescription.Rows.RemoveAt(i);
break;
}
}
}
#region 以下为属性
if (!string.IsNullOrEmpty(strColumnDescription))
{
sbBody.AppendLine(ps.tabMember + "/// <summary>");
sbBody.AppendLine(ps.tabMember + "/// " + strColumnDescription);
sbBody.AppendLine(ps.tabMember + "/// <summary>");
}
//sbBody.AppendLine(ps.tabMember + "[DataMember]");//lsj-2021-2-2
firstUpperColumnName = ps.ConvertStringToUpperOrLower(columnName, true);
firstUpperColumnName = ps.ConvertToSpecial(firstUpperColumnName, strTableName);
//if (n == 0 && tableInfo.KeyColumns[0] == columnName && tableInfo.KeyType == "NUMBER")
// sbBody.AppendLine(ps.tabMember + "public Int64 " + firstUpperColumnName);
//else
//{
sbBody.AppendLine(ps.tabMember + "public " + ps.ConvertType(columnType, columnName, tableInfo) + " " + firstUpperColumnName);
//}
sbBody.AppendLine(ps.tabMember + "{");
sbBody.AppendLine(ps.tabLocalVar + "get{ return " + firstLowerColumnName + "; }");
sbBody.Append(ps.tabLocalVar + "set{ " + firstLowerColumnName + " = value; ");
sbBody.AppendLine("}");
sbBody.AppendLine(ps.tabMember + "}");
//----------------------------------------------
strSetValues1[index] = ps.tabLocalVar + "this." + firstLowerColumnName + " = " + firstLowerColumnName + ";//" + strColumnDescription;
strSetValues2[index] = ps.tabLocalVar + "this." + firstLowerColumnName + " = " + strLowerClassName + "." + firstLowerColumnName + ";//" + strColumnDescription;
if (index == 0)
{
//lsj--构造方法1所用的参数默认值
//增加默认值 lsj@20130222--不支持SYSTIMESTAMP等系统函数
strDefaultValue += ps.ConvertDefaultValue(columnType, columnName, columnDefault);
//lsj--构造方法2所用的参数列表
//if (n == 0 && tableInfo.KeyColumns[0] == columnName && tableInfo.KeyType == "NUMBER")
// strPara += " Int64 " + firstLowerColumnName;
//else
strPara += ps.ConvertType(columnType, columnName, tableInfo) + " " + firstLowerColumnName;
//lsj--构造方法3的赋值
strIndex += "{" + index.ToString() + "}";
strValue += "this." + firstLowerColumnName;
}
else//前面加，
{
//lsj--构造方法1所用的参数默认值
if (columnName == "")
{
strDefaultValue += ",0";
}
else
{
strDefaultValue += "," + ps.ConvertDefaultValue(columnType, columnName, columnDefault);
}
//lsj--构造方法2所用的参数列表
strPara += "," + ps.ConvertType(columnType, columnName, tableInfo) + " " + firstLowerColumnName;
//lsj--构造方法3的赋值
strIndex += "," + "{" + index.ToString() + "}";
strValue += ",this." + firstLowerColumnName;
}
//\"{0},{1},{2},{3}\", this.id, this.code, this.name, this.frequency
index++;
#endregion
}
sbBody.AppendLine(ps.tabMember + "#endregion ");
#endregion
#region Constructors--构造方法//lsj
sbBody.AppendLine(ps.tabMember + "#region Constructors");
//构造方法1
sbBody.AppendLine(ps.tabMember + "public " + strClassName + " (): this(" + strDefaultValue + "){}");
//构造方法2
sbBody.AppendLine(ps.tabMember + "public " + strClassName + " (" + strPara + ")");
sbBody.AppendLine(ps.tabMember + "{");
foreach (string setValue in strSetValues1)
{
sbBody.AppendLine(setValue);
}
sbBody.AppendLine(ps.tabMember + "}");
//构造方法3
sbBody.AppendLine(ps.tabMember + "public " + strClassName + " (" + strClassName + " " + strLowerClassName + ")");
sbBody.AppendLine(ps.tabMember + "{");
foreach (string setValue in strSetValues2)
{
sbBody.AppendLine(setValue);
}
sbBody.AppendLine(ps.tabMember + "}");
sbBody.AppendLine(ps.tabMember + "#endregion ");
#endregion
sbBody.Append(ps.GetDataAccessClassBody_SQL(strTableName, strPrefix, strNamespace));
//2021-2-10-lsj-写入数据库
sbBody.Append(ps.GetDataAccessEntity(strTableName, strPrefix, strNamespace));
string strClass = ps.GetCLassByModel(strNamespace, strPrefix, strTableName, sbBody.ToString());
ret.Append(strClass);
return(ret.ToString());
}
