public static string GetTables(Model.Database.DatabaseType type)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("SELECT d.name N'TableName',a.colorder N'FieldNumber',a.name N'FieldName', ");
strSql.Append("(case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '1'else '0' end) N'IsIdentifier',");
strSql.Append("(case when (SELECT count(*) ");
strSql.Append(" FROM sysobjects WHERE (name in (SELECT name FROM sysindexes ");
strSql.Append(" WHERE (id = a.id) AND (indid in (SELECT indid FROM sysindexkeys");
strSql.Append(" WHERE (id = a.id) AND (colid in (SELECT colid FROM syscolumns");
strSql.Append(" WHERE (id = a.id) AND (name = a.name))))))) AND (xtype = 'PK'))>0 ");
strSql.Append(" then '1' else '0' end) N'IsKeyField', b.name N'FieldType',a.length N'FieldSize', ");
strSql.Append(" COLUMNPROPERTY(a.id,a.name,'PRECISION') as N'FieldLength', ");
strSql.Append(" isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as N'DecimalDigits', ");
strSql.Append(" (case when a.isnullable=1 then '1'else '0' end) N'AllowNull', isnull(e.text,'') N'DefaultValue', ");
strSql.Append(" isnull(g.[value],'') AS N'FieldDescn' ");
strSql.Append(" FROM syscolumns a left join systypes b on a.xtype=b.xusertype inner join sysobjects d ");
strSql.Append(" on a.id=d.id and d.xtype='U' and d.name<>'dtproperties' left join syscomments e on a.cdefault=e.id ");
if (type == Model.Database.DatabaseType.Sql2005)
{
strSql.Append(" left join sys.extended_properties g on a.id=g.major_id AND a.colid = g.minor_id order by object_name(a.id),a.colorder");
}
else
{
strSql.Append(" left join sysproperties g on a.id=g.id AND a.colid = g.smallid order by object_name(a.id),a.colorder ");
}
return(strSql.ToString());
}
public FormCodeCreate(Model.Database.DatabaseType dbType, string dbName, Model.Table table)
{
InitializeComponent();
//如果是ACCESS数据库，不允许使用缓存依赖，不能使用存储过程方式
if (dbType == Model.Database.DatabaseType.Access)
{
cobCacheFrame.Items.Remove(cobCacheFrame.Items[2]);
cobDALFrame.Enabled = false;
}
_dbType = dbType;
tcCodes.Controls.Clear();
cobCodeFrame.SelectedIndex = 0;
cobCacheFrame.SelectedIndex = 0;
cobDALFrame.SelectedIndex = 0;
_dbName = dbName;
_table = table;
TextEditor.SetStyle(txtModel, "C#");
TextEditor.SetStyle(txtIDAL, "C#");
TextEditor.SetStyle(txtDBUtility, "C#");
TextEditor.SetStyle(txtDALFactory, "C#");
TextEditor.SetStyle(txtDAL, "C#");
TextEditor.SetStyle(txtBLL, "C#");
TextEditor.SetStyle(txtUserControl, "HTML");
TextEditor.SetStyle(txtUserControlCs, "C#");
TextEditor.SetStyle(txtICacheDependency, "C#");
TextEditor.SetStyle(txtTableDependency, "C#");
TextEditor.SetStyle(txtTableCacheDependency, "C#");
TextEditor.SetStyle(txtDependencyAccess, "C#");
TextEditor.SetStyle(txtDependencyFacade, "C#");
}
public FormCodeOutput(Model.Database database)
{
InitializeComponent();
cobCodeFrame.SelectedIndex = 0;
cobCacheFrame.SelectedIndex = 0;
cobDALFrame.SelectedIndex = 0;
//关于注册
//CheckForIllegalCrossThreadCalls = false;
//Thread threadCheckRegister = new Thread(CheckRegister);
//threadCheckRegister.Start();
_dbtype = database.Type;
_dbName = database.DatabaseName;
_tables = database.Tables;
//如果是Access数据库，不能使用缓存依赖，不能使用存储过程，不能生成存储过程
if (_dbtype == Model.Database.DatabaseType.Access)
{
cobCacheFrame.Items.Remove(cobCacheFrame.Items[2]);
cobDALFrame.Enabled = false;
btnOutputSp.Enabled = false;
}
foreach (Model.Table table in _tables)
{
lstTables.Items.Add(table.Name);
}
}
public Model.Database GetSchema(string connectionString, Model.Database.DatabaseType type)
{
Model.Database database = new Model.Database();
database.ConnectionString = connectionString;
database.Type = type;
using (SqlConnection connection = new SqlConnection())
{
try
{
DBUtility.DBHelper dbHelper = new DBUtility.DBHelper(DBUtility.DBHelper.DatabaseTypes.Sql, database.ConnectionString);
DataSet ds = dbHelper.ExecuteQuery(CommandType.Text,
GetSql(database.Type, SchemeHelper.SchemaType.Table), null);
database.Tables = GetSQLTableList(ds);
ds = dbHelper.ExecuteQuery(CommandType.Text,
GetSql(database.Type, SchemeHelper.SchemaType.View), null);
database.Views = GetSQLTableList(ds);
ds = dbHelper.ExecuteQuery(CommandType.Text, SqlForGetStoreProcedures, null);
database.StoreProcedures = GetSQLStoreProcedureList(ds);
return(database);
}
catch
{
return(null);
}
}
}
public Model.Database GetSchema(string connectionString, Model.Database.DatabaseType type)
{
Model.Database database = new Model.Database();
database.ConnectionString = connectionString;
database.Type = type;
//得到获取MySql结构的语句
string dbName;
string connStr = database.ConnectionString;
Match mDatabase = Regex.Match(connStr, @"Database=(?<Database>[^\;]*);");
if (mDatabase.Success)
{
dbName = mDatabase.Groups["Database"].Value;
connStr = connStr.Replace(string.Format("Database={0};", dbName), "Database=information_schema;");
}
else
{
return(null);
}
DBUtility.DBHelper dbHelper = new DBUtility.DBHelper(DBUtility.DBHelper.DatabaseTypes.MySql, connStr);
// 获取表
DataSet dsTables = dbHelper.ExecuteQuery(CommandType.Text,
string.Format("select distinct TABLE_NAME from information_schema.TABLES where TABLE_SCHEMA='{0}' and TABLE_TYPE='BASE TABLE'", dbName),
null);
foreach (DataRow rTable in dsTables.Tables[0].Rows)
{
Model.Table table = GetTable(dbHelper, dbName, rTable);
table.Fields.Sort();
database.Tables.Add(table);
}
// 获取视图
DataSet dsViews = dbHelper.ExecuteQuery(CommandType.Text,
string.Format("select distinct TABLE_NAME from information_schema.TABLES where TABLE_SCHEMA='{0}' and TABLE_TYPE='VIEW'", dbName),
null);
foreach (DataRow rView in dsViews.Tables[0].Rows)
{
Model.Table view = GetTable(dbHelper, dbName, rView);
view.Fields.Sort();
database.Views.Add(view);
}
// 获取存储过程
DataSet dsStoreProcedure = dbHelper.ExecuteQuery(CommandType.Text,
string.Format("select distinct SPECIFIC_NAME from ROUTINES where ROUTINE_SCHEMA='{0}'", dbName),
null);
foreach (DataRow r in dsStoreProcedure.Tables[0].Rows)
{
database.StoreProcedures.Add(r[0].ToString());
}
return(database);
}
public static void CreateDALFile(Model.Database.DatabaseType dbType, List <Model.Table> tables, Model.CodeStyle style, string path)
{
foreach (Model.Table table in tables)
{
string filePath = path + "\\" + style.AfterNamespaceDot + table.Name + ".cs";
CodeHelper.WriteUTF8File(filePath, Codes.DALCode.GetDALCode(dbType, table, style));
}
CodeHelper.WriteUTF8File(path + "\\" + style.AfterNamespaceDot + "Config.cs", Codes.DALCode.GetDALConfigCode(dbType, style));
}
public static void CreateBLLFile(Model.Database.DatabaseType dbType, List <Model.Table> tables, Model.CodeStyle style, string path)
{
foreach (Model.Table table in tables)
{
List <Model.Field> l = table.Fields;
string filePath = path + "\\" + style.AfterNamespaceDot + table.Name + ".cs";
CodeHelper.WriteUTF8File(filePath, Codes.BLLCode.GetBLLCode(dbType, table, style));
}
if (style.CacheFrame != Model.CodeStyle.CacheFrames.None)
{
CodeHelper.WriteUTF8File(path + "\\Caches.cs", Codes.BLLCode.GetCachesCode(style));
}
}
public Model.Database GetSchema(string connectionString, Model.Database.DatabaseType type)
{
Model.Database database = new Model.Database();
database.ConnectionString = connectionString;
database.Type = type;
try
{
//获取所有表
DataTable dtAllTable = GetDbSchema(database.ConnectionString, OleDbSchemaGuid.Tables, new object[] { null, null, null, "table" });
foreach (DataRow rt in dtAllTable.Rows)
{
Model.Table tb = new Model.Table();
tb.Name = rt["TABLE_NAME"].ToString();
DataTable dtColumns = GetDbSchema(database.ConnectionString, OleDbSchemaGuid.Columns, new object[] { null, null, tb.Name });
foreach (DataRow rc in dtColumns.Rows)
{
tb.Fields.Add(GetField(database.ConnectionString, tb.Name, rc));
}
tb.Fields.Sort();
database.Tables.Add(tb);
}
//获取所有视图
DataTable dtAllView = GetDbSchema(database.ConnectionString, OleDbSchemaGuid.Views, null);
foreach (DataRow rv in dtAllView.Rows)
{
Model.Table tb = new Model.Table();
tb.Name = rv["TABLE_NAME"].ToString();
DataTable dtColumns = GetDbSchema(database.ConnectionString, OleDbSchemaGuid.Columns, new object[] { null, null, tb.Name });
foreach (DataRow rc in dtColumns.Rows)
{
tb.Fields.Add(GetField(database.ConnectionString, tb.Name, rc));
}
tb.Fields.Sort();
database.Views.Add(tb);
}
//获取所有存储过程
DataTable dtAllStoreProcedure = GetDbSchema(database.ConnectionString, OleDbSchemaGuid.Procedures, null);
foreach (DataRow rsp in dtAllStoreProcedure.Rows)
{
database.StoreProcedures.Add(rsp["PROCEDURE_NAME"].ToString());
}
return(database);
}
catch
{
return(null);
}
}
public static string GetDALConfigCode(Model.Database.DatabaseType type, Model.CodeStyle style)
{
StringBuilder code = new StringBuilder();
code.AppendLine("using System;");
code.AppendLine("using System.Collections.Generic;");
code.AppendLine("using System.Text;");
code.AppendLine("using System.Configuration;");
code.AppendLine();
code.AppendLine("namespace " + style.BeforeNamespaceDot + Model.Database.GetDALNamespace(type) + style.DotAfterNamespace);
code.AppendLine("{");
code.AppendLine(" public class Config");
code.AppendLine(" {");
code.AppendLine(" public static string Connection = ConfigurationManager.ConnectionStrings[\"ConnectionString\"].ConnectionString;");
code.AppendLine(" }");
code.AppendLine("}");
return(code.ToString());
}
public Schema(string connectionString, Model.Database.DatabaseType type)
{
this.connectionString = connectionString;
Type = type;
}
/// <summary>
/// 生成BLL业务逻辑层
/// </summary>
/// <param name="l"></param>
/// <returns></returns>
public static string GetBLLCode(Model.Database.DatabaseType dbType, Model.Table table, Model.CodeStyle style)
{
List <Model.Field> l = table.Fields;
bool HasIdentifierRow;
Model.Field IdentifierRow = CodeHelper.GetKeyField(table, out HasIdentifierRow);
StringBuilder code = new StringBuilder(CommonCode.GetCSharpCopyrightCode());
code.AppendLine("using System;");
code.AppendLine("using System.Data;");
if (style.CodeFrame == Model.CodeStyle.CodeFrames.Factory)
{
code.AppendLine("using DALFactory;");
}
code.AppendLine("using " + style.BeforeNamespaceDot + "Model" + style.DotAfterNamespace + ";");
code.AppendLine("using System.Collections.Generic;");
code.AppendLine("using System.Text;");
code.AppendLine("using System.Web;");
code.AppendLine("using System.Web.Caching;");
code.AppendLine("");
code.AppendLine("namespace " + style.BeforeNamespaceDot + "BLL" + style.DotAfterNamespace);
code.AppendLine("{");
code.AppendLine(" /// <summary>");
code.AppendLine(" /// 业务逻辑类 " + table.Name);
code.AppendLine(" /// </summary>");
if (style.CacheFrame == Model.CodeStyle.CacheFrames.None)
{
code.AppendLine(" public class " + table.Name);
}
else
{
code.AppendLine(" public class " + table.Name + " : RemoveCaches");
}
code.AppendLine(" {");
if (style.CodeFrame == Model.CodeStyle.CodeFrames.Factory)
{
code.AppendLine(" private readonly " + style.BeforeNamespaceDot + "IDAL" + style.DotAfterNamespace + "." + "I" + table.Name + " dal = DataAccess.Create" + style.DotAfterNamespace.Replace(".", "") + table.Name + "();");
}
else
{
if (dbType == Model.Database.DatabaseType.Access)
{
code.AppendLine(" private readonly " + style.BeforeNamespaceDot + "AccessDAL" + style.DotAfterNamespace + "." + table.Name + " dal = new " + style.BeforeNamespaceDot + "AccessDAL" + style.DotAfterNamespace + "." + table.Name + "();");
}
else
{
code.AppendLine(" private readonly " + style.BeforeNamespaceDot + "SQLServerDAL" + style.DotAfterNamespace + "." + table.Name + " dal = new " + style.BeforeNamespaceDot + "SQLServerDAL" + style.DotAfterNamespace + "." + table.Name + "();");
}
}
code.AppendLine(" public " + table.Name + "()");
if (style.CacheFrame != Model.CodeStyle.CacheFrames.None)
{
code.AppendLine(" : base(\"" + style.AfterNamespaceLine + table.Name + "_\")");
}
code.AppendLine(" { }");
code.AppendLine("");
switch (style.CacheFrame)
{
case Model.CodeStyle.CacheFrames.None:
code.Append(CodeUtility.CodeHelper.ReadFromTemplate(System.Windows.Forms.Application.StartupPath + "\\BLL\\None.template", null, table, IdentifierRow, style));
break;
case Model.CodeStyle.CacheFrames.Cache:
code.Append(CodeUtility.CodeHelper.ReadFromTemplate(System.Windows.Forms.Application.StartupPath + "\\BLL\\Cache.template", null, table, IdentifierRow, style));
break;
case Model.CodeStyle.CacheFrames.AggregateDependency:
code.Append(CodeUtility.CodeHelper.ReadFromTemplate(System.Windows.Forms.Application.StartupPath + "\\BLL\\AggregateDependency.template", null, table, IdentifierRow, style));
break;
default:
break;
}
code.AppendLine(" }");
code.AppendLine("}");
return(code.ToString());
}
/// <summary>
/// 生成DAL数据层
/// </summary>
/// <param name="l"></param>
/// <returns></returns>
public static string GetDALCode(Model.Database.DatabaseType type, Model.Table table, Model.CodeStyle style)
{
List <Model.Field> fields = table.Fields;
bool hasIdentifierRow;
Model.Field keyRow = CodeUtility.CodeHelper.GetKeyField(table, out hasIdentifierRow);
StringBuilder code = new StringBuilder(CommonCode.GetCSharpCopyrightCode());
code.AppendLine("using System;");
code.AppendLine("using System.Data;");
code.AppendLine("using System.Text;");
code.AppendLine("using " + Model.Database.GetUsing(type) + ";");
if (style.CodeFrame == Model.CodeStyle.CodeFrames.Factory)
{
code.AppendLine("using " + style.BeforeNamespaceDot + "IDAL" + style.DotAfterNamespace + ";");
}
code.AppendLine("using System.Collections;");
code.AppendLine("using System.Collections.Generic;");
code.AppendLine("");
code.AppendLine("namespace " + style.BeforeNamespaceDot + Model.Database.GetDALNamespace(type) + style.DotAfterNamespace);
code.AppendLine("{");
code.AppendLine(" /// <summary>");
code.AppendLine(" /// 数据访问类 " + table.Name);
code.AppendLine(" /// </summary>");
if (style.CodeFrame == Model.CodeStyle.CodeFrames.Factory)
{
code.AppendLine(" public class " + table.Name + " : I" + table.Name);
}
else
{
code.AppendLine(" public class " + table.Name);
}
code.AppendLine(" {");
code.AppendLine(" public " + table.Name + "()");
code.AppendLine(" { }");
code.AppendLine("");
code.AppendLine(" #region 成员方法");
#region 增加一条数据
code.AppendLine(" /// <summary>");
code.AppendLine(" /// 增加一条数据");
code.AppendLine(" /// </summary>");
code.AppendLine(" public int Add(" + CodeHelper.GetModelClass(table, style) + " model)");
code.AppendLine(" {");
//没有标识列的情况下，要手动得到主键的值
if (!hasIdentifierRow)
{
if (DBUtility.SqlHelper.IsIntClassType(keyRow))
{
code.AppendLine(" model." + keyRow.FieldName + " = GetMaxId() + 1;");
}
if (keyRow.FieldType == Model.DataType.uniqueidentifierType)
{
code.AppendLine(" model." + keyRow.FieldName + " = Guid.NewGuid();");
}
}
switch (style.DALFrame)
{
case Model.CodeStyle.DALFrames.SQL:
code.AppendLine(" StringBuilder strSql = new StringBuilder();");
code.AppendLine(" strSql.Append(\"insert into " + table.Name + "(\");");
code.Append(" strSql.Append(\"");
foreach (Model.Field field in fields)
{
//把非标识且无默认值的字段排成如下格式：Id,Name,Value
if (!field.IsIdentifier && (field == keyRow || string.IsNullOrEmpty(field.DefaultValue)))
{
code.Append(field.FieldName + ",");
}
}
code.Remove(code.Length - 1, 1); //删除最后一个逗号
code.Append(")\");\n");
code.AppendLine(" strSql.Append(\" values (\");");
code.Append(" strSql.Append(\"");
foreach (Model.Field model in fields)
{
//把非标识且无默认值的字段排成如下格式：@Id,@Name,@Value
if (!model.IsIdentifier && (model == keyRow || string.IsNullOrEmpty(model.DefaultValue)))
{
code.Append("@" + model.FieldName + ",");
}
}
code.Remove(code.Length - 1, 1);
code.Append(")\");\n");
break;
case Model.CodeStyle.DALFrames.SP:
code.AppendLine(" int rowsAffected;");
break;
default:
break;
}
code.AppendLine(" " + Model.Database.GetParameterType(type) + "[] parameters = {");
foreach (Model.Field field in fields)
{
if (!field.IsIdentifier && (field == keyRow || string.IsNullOrEmpty(field.DefaultValue)))
{
if (!DBUtility.SqlHelper.IsCharClassType(field))
{
code.AppendLine(" new " + Model.Database.GetParameterType(type) + "(\"@" + field.FieldName + "\", " + CodeUtility.TypeConverter.DataTypeToSQLTypeString(field.FieldType, field.FieldSize) + "),");
}
else
{
code.AppendLine(" new " + Model.Database.GetParameterType(type) + "(\"@" + field.FieldName + "\", " + CodeUtility.TypeConverter.DataTypeToSQLTypeString(field.FieldType, field.FieldSize) + "," + field.FieldLength + "),");
}
}
}
code.Remove(code.Length - 3, 1); //删除最后一个逗号
code.AppendLine(" };");
int i = 0;
foreach (Model.Field field in fields)
{
if (!field.IsIdentifier && (field == keyRow || string.IsNullOrEmpty(field.DefaultValue)))
{
if (field.AllowNull)
{
if (field.FieldType == Model.DataType.uniqueidentifierType && string.IsNullOrEmpty(field.DefaultValue))
{
code.AppendLine("");
code.AppendLine(" if (model." + field.FieldName + " != Guid.Empty)");
code.AppendLine(" parameters[" + i.ToString() + "].Value = model." + field.FieldName + ";");
code.AppendLine(" else");
code.AppendLine(" parameters[" + i.ToString() + "].Value = DBNull.Value;");
code.AppendLine("");
}
else if (DBUtility.SqlHelper.IsDataTimeClassType(field))
{
code.AppendLine("");
code.AppendLine(" if (model." + field.FieldName + " != DateTime.MinValue)");
code.AppendLine(" parameters[" + i.ToString() + "].Value = model." + field.FieldName + ";");
code.AppendLine(" else");
code.AppendLine(" parameters[" + i.ToString() + "].Value = DBNull.Value;");
code.AppendLine("");
}
else if (DBUtility.SqlHelper.IsStringClassType(field))
{
code.AppendLine("");
code.AppendLine(" if (model." + field.FieldName + " != null)");
code.AppendLine(" parameters[" + i.ToString() + "].Value = model." + field.FieldName + ";");
code.AppendLine(" else");
code.AppendLine(" parameters[" + i.ToString() + "].Value = DBNull.Value;");
code.AppendLine("");
}
else
{
code.AppendLine(" parameters[" + i.ToString() + "].Value = model." + field.FieldName + ";");
}
}
else
{
code.AppendLine(" parameters[" + i.ToString() + "].Value = model." + field.FieldName + ";");
}
i++;
}
}
switch (style.DALFrame)
{
case Model.CodeStyle.DALFrames.SQL:
code.AppendLine(" return " + Model.Database.GetHelperClass(type) + ".ExecuteSql(" + style.ConnVariable + ", strSql.ToString(), parameters);");
break;
case Model.CodeStyle.DALFrames.SP:
code.AppendLine(" " + Model.Database.GetHelperClass(type) + ".RunProcedure(" + style.ConnVariable + ", \"sp_" + table.Name + "_Add\", parameters, out rowsAffected);");
code.AppendLine("");
code.AppendLine(" return rowsAffected > 0;");
break;
default:
break;
}
code.AppendLine(" }");
#endregion
code.AppendLine("");
#region 更新一条数据
code.AppendLine(" /// <summary>");
code.AppendLine(" /// 更新一条数据");
code.AppendLine(" /// </summary>");
code.AppendLine(" public bool Update(" + CodeHelper.GetModelClass(table, style) + " model)");
code.AppendLine(" {");
switch (style.DALFrame)
{
case Model.CodeStyle.DALFrames.SQL:
code.AppendLine(" StringBuilder strSql = new StringBuilder();");
code.AppendLine(" strSql.Append(\"update " + table.Name + " set \");");
foreach (Model.Field model in fields)
{
if (model != keyRow)
{
code.AppendLine(" strSql.Append(\"" + model.FieldName + "=@" + model.FieldName + ",\");");
}
}
code.Remove(code.Length - 6, 1);
code.AppendLine(" strSql.Append(\" where " + keyRow.FieldName + "=@" + keyRow.FieldName + "\");");
break;
case Model.CodeStyle.DALFrames.SP:
code.AppendLine(" int rowsAffected;");
break;
default:
break;
}
code.AppendLine(" " + Model.Database.GetParameterType(type) + "[] parameters = {");
foreach (Model.Field field in fields)
{
//不是标识列的放在前面
if (!field.IsIdentifier)
{
if (!DBUtility.SqlHelper.IsCharClassType(field))
{
code.AppendLine(" new " + Model.Database.GetParameterType(type) + "(\"@" + field.FieldName + "\", " + CodeUtility.TypeConverter.DataTypeToSQLTypeString(field.FieldType, field.FieldSize) + "),");
}
else
{
code.AppendLine(" new " + Model.Database.GetParameterType(type) + "(\"@" + field.FieldName + "\", " + CodeUtility.TypeConverter.DataTypeToSQLTypeString(field.FieldType, field.FieldSize) + "," + field.FieldLength + "),");
}
}
}
foreach (Model.Field field in fields)
{
//标识列放在后面
if (field.IsIdentifier)
{
if (!DBUtility.SqlHelper.IsCharClassType(field))
{
code.AppendLine(" new " + Model.Database.GetParameterType(type) + "(\"@" + field.FieldName + "\", " + CodeUtility.TypeConverter.DataTypeToSQLTypeString(field.FieldType, field.FieldSize) + "),");
}
else
{
code.AppendLine(" new " + Model.Database.GetParameterType(type) + "(\"@" + field.FieldName + "\", " + CodeUtility.TypeConverter.DataTypeToSQLTypeString(field.FieldType, field.FieldSize) + "," + field.FieldLength + "),");
}
}
}
code.Remove(code.Length - 3, 1); //删除最后一个逗号，并把换行取消
code.AppendLine(" };");
i = 0;
foreach (Model.Field model in fields)
{
//不是标识列放在前面
if (!model.IsIdentifier)
{
if (model.AllowNull && string.IsNullOrEmpty(model.DefaultValue))
{
if (model.FieldType == Model.DataType.uniqueidentifierType)
{
code.AppendLine("");
code.AppendLine(" if (model." + model.FieldName + " != Guid.Empty)");
code.AppendLine(" parameters[" + i.ToString() + "].Value = model." + model.FieldName + ";");
code.AppendLine(" else");
code.AppendLine(" parameters[" + i.ToString() + "].Value = DBNull.Value;");
code.AppendLine("");
}
else if (model.FieldType == Model.DataType.datetimeType || model.FieldType == Model.DataType.smalldatetimeType)
{
code.AppendLine("");
code.AppendLine(" if (model." + model.FieldName + " != DateTime.MinValue)");
code.AppendLine(" parameters[" + i.ToString() + "].Value = model." + model.FieldName + ";");
code.AppendLine(" else");
code.AppendLine(" parameters[" + i.ToString() + "].Value = DBNull.Value;");
code.AppendLine("");
}
else if (model.FieldType == Model.DataType.charType || model.FieldType == Model.DataType.ncharType || model.FieldType == Model.DataType.ntextType || model.FieldType == Model.DataType.nvarcharType || model.FieldType == Model.DataType.textType || model.FieldType == Model.DataType.varcharType)
{
code.AppendLine("");
code.AppendLine(" if (model." + model.FieldName + " != null)");
code.AppendLine(" parameters[" + i.ToString() + "].Value = model." + model.FieldName + ";");
code.AppendLine(" else");
code.AppendLine(" parameters[" + i.ToString() + "].Value = DBNull.Value;");
code.AppendLine("");
}
else
{
code.AppendLine(" parameters[" + i.ToString() + "].Value = model." + model.FieldName + ";");
}
}
else
{
code.AppendLine(" parameters[" + i.ToString() + "].Value = model." + model.FieldName + ";");
}
i++;
}
}
foreach (Model.Field model in fields)
{
//标识列放在后面
if (model.IsIdentifier)
{
if (model.AllowNull && string.IsNullOrEmpty(model.DefaultValue))
{
if (model.FieldType == Model.DataType.uniqueidentifierType)
{
code.AppendLine("");
code.AppendLine(" if (model." + model.FieldName + " != Guid.Empty)");
code.AppendLine(" parameters[" + i.ToString() + "].Value = model." + model.FieldName + ";");
code.AppendLine(" else");
code.AppendLine(" parameters[" + i.ToString() + "].Value = DBNull.Value;");
code.AppendLine("");
}
else if (model.FieldType == Model.DataType.datetimeType || model.FieldType == Model.DataType.smalldatetimeType)
{
code.AppendLine("");
code.AppendLine(" if (model." + model.FieldName + " != DateTime.MinValue)");
code.AppendLine(" parameters[" + i.ToString() + "].Value = model." + model.FieldName + ";");
code.AppendLine(" else");
code.AppendLine(" parameters[" + i.ToString() + "].Value = DBNull.Value;");
code.AppendLine("");
}
else if (model.FieldType == Model.DataType.charType || model.FieldType == Model.DataType.ncharType || model.FieldType == Model.DataType.ntextType || model.FieldType == Model.DataType.nvarcharType || model.FieldType == Model.DataType.textType || model.FieldType == Model.DataType.varcharType)
{
code.AppendLine("");
code.AppendLine(" if (model." + model.FieldName + " != null)");
code.AppendLine(" parameters[" + i.ToString() + "].Value = model." + model.FieldName + ";");
code.AppendLine(" else");
code.AppendLine(" parameters[" + i.ToString() + "].Value = DBNull.Value;");
code.AppendLine("");
}
else
{
code.AppendLine(" parameters[" + i.ToString() + "].Value = model." + model.FieldName + ";");
}
}
else
{
code.AppendLine(" parameters[" + i.ToString() + "].Value = model." + model.FieldName + ";");
}
i++;
}
}
code.AppendLine("");
switch (style.DALFrame)
{
case Model.CodeStyle.DALFrames.SQL:
code.AppendLine(" return " + Model.Database.GetHelperClass(type) + ".ExecuteSql(" + style.ConnVariable + ", strSql.ToString(), parameters) > 0;");
break;
case Model.CodeStyle.DALFrames.SP:
code.AppendLine(" " + Model.Database.GetHelperClass(type) + ".RunProcedure(" + style.ConnVariable + ", \"sp_" + table.Name + "_Update\", parameters, out rowsAffected);");
code.AppendLine(" return rowsAffected > 0;");
break;
default:
break;
}
code.AppendLine(" }");
#endregion
code.AppendLine("");
#region 除一条数据
code.AppendLine(" /// <summary>");
code.AppendLine(" /// 删除一条数据");
code.AppendLine(" /// </summary>");
code.AppendLine(" public bool Delete(" + CodeUtility.TypeConverter.DataTypeToCSharpTypeString(keyRow.FieldType) + " " + keyRow.FieldName + ")");
code.AppendLine(" {");
switch (style.DALFrame)
{
case Model.CodeStyle.DALFrames.SQL:
code.AppendLine(" StringBuilder strSql = new StringBuilder();");
code.AppendLine(" strSql.Append(\"delete " + table.Name + " \");");
code.AppendLine(" strSql.Append(\" where " + keyRow.FieldName + "=@" + keyRow.FieldName + "\");");
break;
case Model.CodeStyle.DALFrames.SP:
code.AppendLine(" int rowsAffected;");
break;
default:
break;
}
code.AppendLine(" " + Model.Database.GetParameterType(type) + "[] parameters = {");
if (!DBUtility.SqlHelper.IsCharClassType(keyRow))
{
code.AppendLine(" new " + Model.Database.GetParameterType(type) + "(\"@" + keyRow.FieldName + "\", " + CodeUtility.TypeConverter.DataTypeToSQLTypeString(keyRow.FieldType, keyRow.FieldSize) + ")};");
}
else
{
code.AppendLine(" new " + Model.Database.GetParameterType(type) + "(\"@" + keyRow.FieldName + "\", " + CodeUtility.TypeConverter.DataTypeToSQLTypeString(keyRow.FieldType, keyRow.FieldSize) + "," + keyRow.FieldLength + ")};");
}
code.AppendLine(" parameters[0].Value = " + keyRow.FieldName + ";");
code.AppendLine("");
switch (style.DALFrame)
{
case Model.CodeStyle.DALFrames.SQL:
code.AppendLine(" return " + Model.Database.GetHelperClass(type) + ".ExecuteSql(" + style.ConnVariable + ", strSql.ToString(), parameters) > 0;");
break;
case Model.CodeStyle.DALFrames.SP:
code.AppendLine(" " + Model.Database.GetHelperClass(type) + ".RunProcedure(" + style.ConnVariable + ", \"sp_" + table.Name + "_Delete\", parameters, out rowsAffected);");
code.AppendLine(" return rowsAffected > 0;");
break;
default:
break;
}
code.AppendLine(" }");
#endregion
code.AppendLine("");
if (DBUtility.SqlHelper.IsIntClassType(keyRow))
{
#region 得到最大ID
code.AppendLine(" /// <summary>");
code.AppendLine(" /// 得到最大ID");
code.AppendLine(" /// </summary>");
code.AppendLine(" public int GetMaxId()");
code.AppendLine(" {");
switch (style.DALFrame)
{
case Model.CodeStyle.DALFrames.SQL:
code.AppendLine(" return " + Model.Database.GetHelperClass(type) + ".GetMaxID(" + style.ConnVariable + ", \"" + keyRow.FieldName + "\", \"" + table.Name + "\");");
break;
case Model.CodeStyle.DALFrames.SP:
code.AppendLine(" int rowsAffected;");
code.AppendLine(" " + Model.Database.GetParameterType(type) + "[] parameters ={ };");
code.AppendLine(" return " + Model.Database.GetHelperClass(type) + ".RunProcedure(" + style.ConnVariable + ", \"sp_" + table.Name + "_GetMaxId\", parameters, out rowsAffected);");
break;
default:
break;
}
code.AppendLine(" }");
#endregion
code.AppendLine("");
}
#region 是否存在该记录
code.AppendLine(" /// <summary>");
code.AppendLine(" /// 是否存在该记录");
code.AppendLine(" /// </summary>");
code.AppendLine(" public bool Exists(" + CodeUtility.TypeConverter.DataTypeToCSharpTypeString(keyRow.FieldType) + " " + keyRow.FieldName + ")");
code.AppendLine(" {");
switch (style.DALFrame)
{
case Model.CodeStyle.DALFrames.SQL:
code.AppendLine(" StringBuilder strSql = new StringBuilder();");
code.AppendLine(" strSql.Append(\"select count(1) from " + table.Name + "\");");
code.AppendLine(" strSql.Append(\" where " + keyRow.FieldName + "= @" + keyRow.FieldName + "\");");
break;
case Model.CodeStyle.DALFrames.SP:
code.AppendLine(" int rowsAffected;");
break;
default:
break;
}
code.AppendLine(" " + Model.Database.GetParameterType(type) + "[] parameters = {");
if (!DBUtility.SqlHelper.IsCharClassType(keyRow))
{
code.AppendLine(" new " + Model.Database.GetParameterType(type) + "(\"@" + keyRow.FieldName + "\", " + CodeUtility.TypeConverter.DataTypeToSQLTypeString(keyRow.FieldType, keyRow.FieldSize) + ")};");
}
else
{
code.AppendLine(" new " + Model.Database.GetParameterType(type) + "(\"@" + keyRow.FieldName + "\", " + CodeUtility.TypeConverter.DataTypeToSQLTypeString(keyRow.FieldType, keyRow.FieldSize) + "," + keyRow.FieldLength + ")};");
}
code.AppendLine(" parameters[0].Value = " + keyRow.FieldName + ";");
switch (style.DALFrame)
{
case Model.CodeStyle.DALFrames.SQL:
code.AppendLine(" return " + Model.Database.GetHelperClass(type) + ".Exists(" + style.ConnVariable + ", strSql.ToString(), parameters);");
break;
case Model.CodeStyle.DALFrames.SP:
code.AppendLine(" return " + Model.Database.GetHelperClass(type) + ".RunProcedure(" + style.ConnVariable + ", \"sp_" + table.Name + "_Exists\", parameters, out rowsAffected) == 1;");
break;
default:
break;
}
code.AppendLine(" }");
#endregion
code.AppendLine("");
#region 得到一个对象实体
code.AppendLine(" /// <summary>");
code.AppendLine(" /// 得到一个对象实体");
code.AppendLine(" /// </summary>");
code.AppendLine(" public " + CodeHelper.GetModelClass(table, style) + " GetModel(" + CodeUtility.TypeConverter.DataTypeToCSharpTypeString(keyRow.FieldType) + " " + keyRow.FieldName + ")");
code.AppendLine(" {");
if (style.DALFrame == Model.CodeStyle.DALFrames.SQL)
{
code.AppendLine(" StringBuilder strSql = new StringBuilder();");
code.AppendLine(" strSql.Append(\"select * from " + table.Name + " \");");
code.AppendLine(" strSql.Append(\" where " + keyRow.FieldName + "=@" + keyRow.FieldName + "\");");
}
code.AppendLine(" " + Model.Database.GetParameterType(type) + "[] parameters = {");
if (!DBUtility.SqlHelper.IsCharClassType(keyRow))
{
code.AppendLine(" new " + Model.Database.GetParameterType(type) + "(\"@" + keyRow.FieldName + "\", " + CodeUtility.TypeConverter.DataTypeToSQLTypeString(keyRow.FieldType, keyRow.FieldSize) + ")};");
}
else
{
code.AppendLine(" new " + Model.Database.GetParameterType(type) + "(\"@" + keyRow.FieldName + "\", " + CodeUtility.TypeConverter.DataTypeToSQLTypeString(keyRow.FieldType, keyRow.FieldSize) + "," + keyRow.FieldLength + ")};");
}
code.AppendLine(" parameters[0].Value = " + keyRow.FieldName + ";");
switch (style.DALFrame)
{
case Model.CodeStyle.DALFrames.SQL:
code.AppendLine(" DataSet ds = " + Model.Database.GetHelperClass(type) + ".Query(" + style.ConnVariable + ", strSql.ToString(), parameters);");
break;
case Model.CodeStyle.DALFrames.SP:
code.AppendLine(" DataSet ds = " + Model.Database.GetHelperClass(type) + ".RunProcedure(" + style.ConnVariable + ", \"sp_" + table.Name + "_GetModel\", parameters, \"ds\");");
break;
default:
break;
}
code.AppendLine(" if (ds.Tables[0].Rows.Count > 0)");
code.AppendLine(" {");
code.AppendLine(" DataRow r = ds.Tables[0].Rows[0];");
code.AppendLine(" return GetModel(r);");
code.AppendLine(" }");
code.AppendLine(" else");
code.AppendLine(" {");
code.AppendLine(" return null;");
code.AppendLine(" }");
code.AppendLine(" }");
#endregion
code.AppendLine("");
switch (style.DALFrame)
{
case Model.CodeStyle.DALFrames.SQL:
#region Sql获取泛型数据列表
code.AppendLine(" /// <summary>");
code.AppendLine(" /// 获取泛型数据列表");
code.AppendLine(" /// </summary>");
code.AppendLine(" public List<" + CodeHelper.GetModelClass(table, style) + "> GetList()");
code.AppendLine(" {");
code.AppendLine(" StringBuilder strSql = new StringBuilder(\"select * from " + table.Name + "\");");
code.AppendLine(" DataSet ds = " + Model.Database.GetHelperClass(type) + ".Query(" + style.ConnVariable + ", strSql.ToString());");
code.AppendLine(" return GetList(ds);");
code.AppendLine(" }");
#endregion
code.AppendLine("");
break;
case Model.CodeStyle.DALFrames.SP:
#region 存储过程获取泛型数据列表
code.AppendLine(" /// <summary>");
code.AppendLine(" /// 获取泛型数据列表");
code.AppendLine(" /// </summary>");
code.AppendLine(" public List<" + CodeHelper.GetModelClass(table, style) + "> GetList()");
code.AppendLine(" {");
code.AppendLine(" " + Model.Database.GetParameterType(type) + "[] parameters ={ };");
code.AppendLine(" DataSet ds = " + Model.Database.GetHelperClass(type) + ".RunProcedure(" + style.ConnVariable + ", \"sp_" + table.Name + "_GetAllList\", parameters, \"ds\");");
code.AppendLine(" return GetList(ds);");
code.AppendLine(" }");
#endregion
code.AppendLine("");
break;
default:
break;
}
#region 分页获取泛型数据列表
code.AppendLine(" /// <summary>");
code.AppendLine(" /// 分页获取泛型数据列表");
code.AppendLine(" /// </summary>");
code.AppendLine(" public List<" + CodeHelper.GetModelClass(table, style) + "> GetList(int pageSize, int pageIndex, string fldSort, bool sort, string strCondition, out int count)");
code.AppendLine(" {");
code.AppendLine(" DataSet ds = " + Model.Database.GetHelperClass(type) + ".PageList(" + style.ConnVariable + ", \"" + table.Name + "\", pageSize, pageIndex, fldSort, sort, strCondition, out count);");
code.AppendLine(" return GetList(ds);");
code.AppendLine(" }");
#endregion
code.AppendLine(" #endregion");
code.AppendLine("");
#region 由一行数据得到一个实体
code.AppendLine(" /// <summary>");
code.AppendLine(" /// 由一行数据得到一个实体");
code.AppendLine(" /// </summary>");
code.AppendLine(" private " + CodeHelper.GetModelClass(table, style) + " GetModel(DataRow r)");
code.AppendLine(" {");
code.AppendLine(" " + CodeHelper.GetModelClass(table, style) + " model = new " + CodeHelper.GetModelClass(table, style) + "();");
foreach (Model.Field model in fields)
{
code.AppendLine(" model." + model.FieldName + " = DBUtility.ObjectHelper." + CodeUtility.TypeConverter.DataTypeToCSharpMethod(model.FieldType) + "(r[\"" + model.FieldName + "\"]);");
}
code.AppendLine(" return model;");
code.AppendLine(" }");
#endregion
code.AppendLine("");
#region 由数据集得到泛型数据列表
code.AppendLine(" /// <summary>");
code.AppendLine(" /// 由数据集得到泛型数据列表");
code.AppendLine(" /// </summary>");
code.AppendLine(" private List<" + CodeHelper.GetModelClass(table, style) + "> GetList(DataSet ds)");
code.AppendLine(" {");
code.AppendLine(" List<" + CodeHelper.GetModelClass(table, style) + "> l = new List<" + CodeHelper.GetModelClass(table, style) + ">();");
code.AppendLine(" foreach (DataRow r in ds.Tables[0].Rows)");
code.AppendLine(" {");
code.AppendLine(" l.Add(GetModel(r));");
code.AppendLine(" }");
code.AppendLine(" return l;");
code.AppendLine(" }");
#endregion
code.AppendLine(" }");
code.AppendLine("}");
return(code.ToString());
}
