public void GenerateSqlSelectDetailTest()
{
string resultMsg = string.Empty;
var criteria = new GeneratorCriteria();
criteria.TableName = "TUSERCOMP";
criteria.SqlParameterPrefix = "v_";
var res = dal.GenerateSqlForSelectDetail(out resultMsg, criteria);
var res1 = string.Empty;
Assert.AreNotEqual(res, res1);
}
public void GenerateSqlForSelectDetailTest()
{
string errorMsg = string.Empty;
var criteria = new GeneratorCriteria();
criteria.DataBaseName = "Invoice_System";
criteria.TableName = "st_user_detail";
IList<ModelGeneration> res1 = null;
var res = dal.GenerateSqlForSelectDetail(out errorMsg, criteria);
Assert.AreNotEqual(res, res1);
}
public void ModelCodeGenerationTest()
{
string resultMsg = string.Empty;
var gen = new CodeGenerators();
//var code = gen.ModelCodeGeneration(out resultMsg, "Models", "SHIP_INFO", BaseDict.OracleData);
var criteria = new GeneratorCriteria();
criteria.ModelsNamespace = "Models";
criteria.ModelClassNamePrefix = "Model";
criteria.DalNamespace = "Logic";
criteria.DalClassNamePrefix = "Logic";
criteria.TableName = "TUSER";
criteria.DataBaseType = BaseDict.OracleData;
var code = gen.ModelCodeGeneration(out resultMsg, criteria.TableName, criteria.DataBaseType, criteria.DataBaseName, criteria.ModelsNamespace,
criteria.ModelClassNamePrefix);
var code1 = string.Empty;
Assert.AreNotEqual(code, code1);
}
/// <summary>
/// SQL Select All 语句生成
/// </summary>
public string GenerateSqlForSelectAll(out string resultMsg, GeneratorCriteria criteria)
{
resultMsg = string.Empty;
//查询字段列表
var colList = QueryColumnsByTable(out resultMsg, criteria);
//查询主键字段列表
var colPK = (from ModelGeneration model in colList
where model.IsPrimaryKey == true
select model).ToList();
string classNamePrivate = CommonMethod.StringToPrivateVar(criteria.TableName);
string classNamePublic = CommonMethod.StringToPublicVar(criteria.TableName);
string strParameter = string.Empty;
string strWhereSql = string.Empty;
StringBuilder sbSql = new StringBuilder();
sbSql.AppendLine("-- ===========================================================");
sbSql.AppendLine("-- Author: Jason.Yao");
sbSql.AppendFormat("-- Create date: {0}", DateTime.Now.ToString("yyyy-MM-dd"));
sbSql.AppendLine();
sbSql.AppendFormat("-- Description: {0} SELECT ALL", criteria.TableName);
sbSql.AppendLine();
sbSql.AppendLine("-- ===========================================================");
sbSql.AppendFormat("CREATE PROCEDURE usp_{0}_select_all", criteria.TableName.ToLower());
sbSql.AppendLine();
sbSql.AppendLine(strParameter);
sbSql.AppendLine("AS");
sbSql.AppendLine("BEGIN ");
sbSql.AppendFormat(" SELECT * FROM {0} ", criteria.TableName);
sbSql.AppendLine();
sbSql.AppendLine("END ");
string sql = sbSql.ToString();
return sql;
}
public void QueryTablesAllTest()
{
string errorMsg = string.Empty;
var criteria = new GeneratorCriteria();
criteria.DataBaseName = "CardMaintain";
IList<ModelGeneration> res1 = null;
var res = dal.QueryTablesAll(out errorMsg, criteria);
Assert.AreNotEqual(res, res1);
var PublicTableName = string.Empty;
var publicTName = res[0].TableName;
Assert.AreNotEqual(PublicTableName, publicTName);
}
/// <summary>
/// TSQL 语句生成
/// </summary>
/// <param name="ResultMsg">执行结果信息</param>
/// <param name="TableName">表名</param>
/// <param name="DataBaseType">数据库类型</param>
/// <param name="DataBaseName">数据库名</param>
/// <param name="SqlParameterPrefix">TSql参数名前缀</param>
/// <param name="SqlProcedurePrefix">TSql存储过程名称前缀</param>
/// <param name="IsSqlInsertUpdate">是否生成 Insert/Update</param>
/// <param name="IsSqlSelectDetail">是否生成 SelectDetail</param>
/// <param name="IsSqlUpdateStatus">是否生成 UpdateStatus</param>
/// <param name="IsSqlSelectPager">是否生成 SelectPager</param>
/// <param name="IsSqlSelectAll">是否生成 SqlSelectAll</param>
public string TSqlCodeGeneration(out string ResultMsg, string TableName, string DataBaseType = BaseDict.SqlServerData, string DataBaseName = null,
string SqlParameterPrefix = null, string SqlProcedurePrefix = null, string IsSqlInsertUpdate = "True", string IsSqlSelectDetail = "True", string IsSqlUpdateStatus = "True",
string IsSqlSelectPager = "True", string IsSqlSelectAll = "True", string IsSqlDelete = "True")
{
ResultMsg = string.Empty;
var criteria = new GeneratorCriteria(); // 条件对象
criteria.DataBaseName = DataBaseName; // 数据库名称
criteria.TableName = TableName; // 表名
criteria.SqlParameterPrefix = SqlParameterPrefix; // SQL参数前缀
criteria.DataBaseType = DataBaseType; // 数据库类型
criteria.SqlProcedurePrefix = SqlProcedurePrefix; // 存储过程名称前缀
IGeneration dal = CreateInstance(criteria.DataBaseType);
var code = string.Empty;
if (IsSqlUpdateStatus.Equals("True"))
code += dal.GenerateSqlForUpdateStatus(out ResultMsg, criteria);
if (IsSqlInsertUpdate.Equals("True"))
code += Environment.NewLine + dal.GenerateSqlForInsertUpdate(out ResultMsg, criteria);
if (IsSqlSelectDetail.Equals("True"))
code += Environment.NewLine + dal.GenerateSqlForSelectDetail(out ResultMsg, criteria);
if (IsSqlSelectPager.Equals("True"))
code += Environment.NewLine + dal.GenerateSqlForSelectPager(out ResultMsg, criteria);
if (IsSqlSelectAll.Equals("True"))
code += Environment.NewLine + dal.GenerateSqlForSelectAll(out ResultMsg, criteria);
if (IsSqlDelete.Equals("True"))
code += Environment.NewLine + dal.GenerateSqlForDelete(out ResultMsg, criteria);
return code;
}
public void QueryColumnsByTableTest()
{
string resultMsg = string.Empty;
var criteria = new GeneratorCriteria();
criteria.TableName = "SHIP_INFO";
IList<ModelGeneration> res1 = null;
var res = dal.QueryColumnsByTable(out resultMsg, criteria);
Assert.AreNotEqual(res, res1);
var PublicColName = string.Empty;
var publicCName = res[0].PublicVarName;
var pk = res[0].IsPrimaryKey;
Assert.AreNotEqual(PublicColName, publicCName);
}
/// <summary>
/// 查询所有表名集合
/// </summary>
public IList<ModelGeneration> QueryTablesAll(out string resultMsg, GeneratorCriteria criteria)
{
IList<ModelGeneration> list = new List<ModelGeneration>();
resultMsg = string.Empty;
try
{
StringBuilder SQL = new StringBuilder();
#region SQL拼接
SQL.AppendFormat("USE {0} ", string.IsNullOrWhiteSpace(criteria.DataBaseName) ? "master" : criteria.DataBaseName);
SQL.Append("SELECT C.NAME , ");
SQL.Append(" C.TYPE , ");
SQL.Append(" CAST(ISNULL(F.[VALUE], '') AS NVARCHAR(100)) AS REMARK ");
SQL.Append("FROM SYS.OBJECTS C ");
SQL.Append("LEFT JOIN SYS.EXTENDED_PROPERTIES F ");
SQL.Append("ON F.MAJOR_ID = C.OBJECT_ID ");
SQL.Append("AND F.MINOR_ID = 0 ");
SQL.Append("AND F.CLASS = 1 ");
SQL.Append("WHERE C.TYPE = 'u' ");
SQL.Append("OR C.TYPE = 'v' ");
SQL.Append("ORDER BY C.TYPE ,C.NAME");
#endregion
//存储过程名称
string strSql = SQL.ToString();
//查询执行
using (IDataReader dr = DBHelper.ExecuteReader(strSql, false))
{
list = GetTableModel(dr);
}
}
catch (Exception EX)
{
resultMsg = string.Format("{0} {1}", BaseDict.ErrorPrefix, EX.Message);
}
return list;
}
/// <summary>
/// SQL UpdateStatus 语句生成
/// </summary>
public string GenerateSqlForUpdateStatus(out string resultMsg, GeneratorCriteria criteria)
{
resultMsg = string.Empty;
// 查询字段列表
var colList = QueryColumnsByTable(out resultMsg, criteria);
// 查询主键字段列表
var colPK = (from ModelGeneration model in colList
where model.IsPrimaryKey == true
select model).ToList();
string classNamePrivate = CommonMethod.StringToPrivateVar(criteria.TableName); // 类名 私有
string classNamePublic = CommonMethod.StringToPublicVar(criteria.TableName); // 类名 公有
string strParameter = string.Empty;
string strWhereSql = string.Empty;
string strCols = string.Empty;
StringBuilder sbSql = new StringBuilder();
sbSql.AppendLine("-- ===========================================================");
sbSql.AppendLine("-- Author: Jason.Yao");
sbSql.AppendFormat("-- Create date: {0}", DateTime.Now.ToString("yyyy-MM-dd"));
sbSql.AppendLine();
sbSql.AppendFormat("-- Description: {0} UPDATE STATUS", criteria.TableName);
sbSql.AppendLine();
sbSql.AppendLine("-- ===========================================================");
sbSql.AppendFormat("CREATE PROCEDURE usp_{0}_update_status ", criteria.TableName.ToLower());
sbSql.AppendLine();
foreach (var item in colList)
{
if (item.IsPrimaryKey || item.ColumnName.ToUpper().Contains("STATUS") || item.ColumnName.ToUpper().Contains("DELETE"))
{
var strSqlTypeAndLength = GetSqlTypeAndLength(item);
sbSql.AppendFormat(" @{0}{1} {2}, ", criteria.SqlParameterPrefix, item.PublicVarName, strSqlTypeAndLength);
sbSql.AppendLine();
}
}
sbSql.AppendLine(" @resultMsg NVARCHAR(500) OUT");
sbSql.AppendLine("AS");
sbSql.AppendLine("BEGIN ");
sbSql.AppendFormat("IF EXISTS(SELECT * FROM {0} WHERE ", criteria.TableName);
foreach (var item in colPK)
{
if (string.IsNullOrWhiteSpace(strWhereSql))
strWhereSql += string.Format("[{0}] = @{1}{2} ", item.ColumnName, criteria.SqlParameterPrefix, item.PublicVarName);
else
strWhereSql += string.Format(" AND [{0}] = @{1}{2} ", item.ColumnName, criteria.SqlParameterPrefix, item.PublicVarName);
}
sbSql.Append(strWhereSql);
sbSql.Append(")");
sbSql.AppendLine();
sbSql.AppendLine(" BEGIN");
sbSql.AppendFormat(" UPDATE {0} ", criteria.TableName);
sbSql.AppendLine();
sbSql.AppendLine(" SET");
strParameter = string.Empty;
foreach (var item in colList)
{
if (item.ColumnName.ToUpper().Contains("STATUS") || item.ColumnName.ToUpper().Contains("DELETE"))
{
if (string.IsNullOrWhiteSpace(strParameter))
strParameter += string.Format(" [{0}] = CASE WHEN @{1}{2} IS NOT NULL THEN @{1}{3} ELSE [{4}] END ",
item.ColumnName, criteria.SqlParameterPrefix, item.PublicVarName, item.PublicVarName, item.ColumnName);
else
strParameter += string.Format(",{0} [{1}] = CASE WHEN @{2}{3} IS NOT NULL THEN @{2}{4} ELSE [{5}] END ",
Environment.NewLine, item.ColumnName, criteria.SqlParameterPrefix, item.PublicVarName,item.PublicVarName, item.ColumnName);
}
}
sbSql.AppendLine(strParameter);
sbSql.AppendFormat(" WHERE {0} ", strWhereSql);
sbSql.AppendLine();
sbSql.AppendFormat(" SET @resultMsg = '{0}{1}' ", BaseDict.SuccessPrefix, BaseDict.Success);
sbSql.AppendLine();
sbSql.AppendLine(" END ");
sbSql.AppendLine(" ELSE ");
sbSql.AppendLine(" BEGIN ");
sbSql.AppendFormat(" SET @resultMsg = '{0}{1}' ", BaseDict.ErrorPrefix, BaseDict.SqlExMsgNoData);
sbSql.AppendLine();
sbSql.AppendLine(" END");
sbSql.AppendLine("END");
string sql = sbSql.ToString();
return sql;
}
/// <summary>
/// SQL Select Pager 语句生成
/// </summary>
public string GenerateSqlForSelectPager(out string resultMsg, GeneratorCriteria criteria)
{
resultMsg = string.Empty;
//查询字段列表
var colList = QueryColumnsByTable(out resultMsg, criteria);
//查询主键字段列表
var colPK = (from ModelGeneration model in colList
where model.IsPrimaryKey == true
select model).ToList();
string classNamePrivate = CommonMethod.StringToPrivateVar(criteria.TableName);
string classNamePublic = CommonMethod.StringToPublicVar(criteria.TableName);
string strParameter = string.Empty;
string strParmPagerIndex = string.Format(" {0}PagerIndex", criteria.SqlParameterPrefix);
string strParmPagerSize = string.Format(" {0}PagerSize", criteria.SqlParameterPrefix);
string strParmRowCount = string.Format(" {0}RowCount", criteria.SqlParameterPrefix);
string strParmTotalPages = string.Format(" {0}TotalPages", criteria.SqlParameterPrefix);
string strParmStartRow = string.Format(" {0}StartRow", criteria.SqlParameterPrefix);
string strParmEndRow = string.Format(" {0}EndRow", criteria.SqlParameterPrefix);
StringBuilder sbSql = new StringBuilder();
sbSql.AppendLine("-- ===========================================================");
sbSql.AppendLine("-- Author: Jason.Yao");
sbSql.AppendFormat("-- Create date: {0}", DateTime.Now.ToString("yyyy-MM-dd"));
sbSql.AppendLine();
sbSql.AppendFormat("-- Description: {0} SELECT PAGER", criteria.TableName);
sbSql.AppendLine();
sbSql.AppendLine("-- ===========================================================");
sbSql.AppendFormat("CREATE OR REPLACE PROCEDURE USP_{0}_SELECT_PAGER",criteria.TableName);
sbSql.Append("(");
sbSql.AppendLine();
sbSql.AppendFormat(" {0} IN NUMBER,", strParmPagerIndex);
sbSql.AppendLine();
sbSql.AppendFormat(" {0} IN NUMBER,", strParmPagerSize);
sbSql.AppendLine();
sbSql.AppendFormat(" {0} OUT NUMBER,", strParmRowCount);
sbSql.AppendLine();
sbSql.Append(" CUR_OUT OUT SYS_REFCURSOR");
sbSql.AppendLine(")");
sbSql.AppendLine("IS");
sbSql.AppendFormat(" {0} NUMBER :=0;", strParmTotalPages);
sbSql.AppendLine();
sbSql.AppendFormat(" {0} NUMBER := 0;", strParmStartRow);
sbSql.AppendLine();
sbSql.AppendFormat(" {0} NUMBER :=0;", strParmEndRow);
sbSql.AppendLine();
sbSql.AppendLine("BEGIN ");
sbSql.AppendFormat(" SELECT COUNT(*) INTO {0} FROM {1} ; ", strParmRowCount,criteria.TableName);
sbSql.AppendLine();
sbSql.AppendFormat(" {0} := CEIL({1} / {2}); ", strParmTotalPages, strParmRowCount, strParmPagerSize);
sbSql.AppendLine();
sbSql.AppendFormat(" {0} := ({1} - 1 ) * {2} + 1; ", strParmStartRow, strParmPagerIndex, strParmPagerSize);
sbSql.AppendLine();
sbSql.AppendFormat(" {0} := CASE WHEN {1} > {2}*{3} THEN ({4} * {5}) ELSE {6} END ;",
strParmEndRow, strParmRowCount, strParmPagerIndex, strParmPagerSize, strParmPagerIndex, strParmPagerSize, strParmRowCount);
sbSql.AppendLine();
sbSql.AppendLine(" OPEN CUR_OUT FOR SELECT * FROM (");
sbSql.AppendFormat(" SELECT * FROM {0} -- Where 条件 ,Order By 排序 放这里",criteria.TableName);
sbSql.AppendLine();
sbSql.AppendFormat(" )WHERE ROWNUM BETWEEN {0} AND {1} ;", strParmStartRow, strParmEndRow);
sbSql.AppendLine();
sbSql.AppendLine("END;");
string sql = sbSql.ToString();
return sql;
}
/// <summary>
/// SQL Detail 语句生成
/// </summary>
public string GenerateSqlForSelectDetail(out string resultMsg, GeneratorCriteria criteria)
{
resultMsg = string.Empty;
//查询字段列表
var colList = QueryColumnsByTable(out resultMsg, criteria);
//查询主键字段列表
var colPK = (from ModelGeneration model in colList
where model.IsPrimaryKey == true
select model).ToList();
string classNamePrivate = CommonMethod.StringToPrivateVar(criteria.TableName);
string classNamePublic = CommonMethod.StringToPublicVar(criteria.TableName);
string strParameter = string.Empty;
StringBuilder sbSql = new StringBuilder();
sbSql.AppendLine("-- ===========================================================");
sbSql.AppendLine("-- Author: Jason.Yao");
sbSql.AppendFormat("-- Create date: {0}", DateTime.Now.ToString("yyyy-MM-dd"));
sbSql.AppendLine();
sbSql.AppendFormat("-- Description: {0} SELECT DETAIL", criteria.TableName);
sbSql.AppendLine();
sbSql.AppendLine("-- ===========================================================");
sbSql.AppendFormat("CREATE OR REPLACE PROCEDURE USP_{0}_SELECT_DETAIL_BY", criteria.TableName.ToUpper());
foreach (var item in colPK)
{
sbSql.AppendFormat("_{0}", item.ColumnName.ToUpper());
}
sbSql.Append("(");
sbSql.AppendLine();
foreach (var item in colPK)
{
sbSql.AppendFormat(" {0}{1} IN {2},", criteria.SqlParameterPrefix, item.PublicVarName, item.DataType);
sbSql.AppendLine();
}
sbSql.Append(" CUR_OUT OUT SYS_REFCURSOR");
sbSql.AppendLine(")");
sbSql.AppendLine("IS");
sbSql.AppendLine("BEGIN ");
sbSql.AppendFormat(" OPEN CUR_OUT FOR SELECT * FROM {0} WHERE ", criteria.TableName);
foreach (var item in colPK)
{
if (string.IsNullOrWhiteSpace(strParameter))
strParameter += string.Format("{0} = {1}{2} ", item.ColumnName, criteria.SqlParameterPrefix, item.PublicVarName);
else
strParameter += string.Format(" AND {0} = {1}{2} ", item.ColumnName, criteria.SqlParameterPrefix, item.PublicVarName);
}
sbSql.Append(strParameter);
sbSql.Append(";");
sbSql.AppendLine();
sbSql.AppendLine("END;");
string sql = sbSql.ToString();
return sql;
}
/// <summary>
/// SQL UpdateStatus 语句生成
/// </summary>
public string GenerateSqlForDelete(out string resultMsg, GeneratorCriteria criteria)
{
resultMsg = string.Empty;
//查询字段列表
var colList = QueryColumnsByTable(out resultMsg, criteria);
//查询主键字段列表
var colPK = (from ModelGeneration model in colList
where model.IsPrimaryKey == true
select model).ToList();
string classNamePrivate = CommonMethod.StringToPrivateVar(criteria.TableName);
string classNamePublic = CommonMethod.StringToPublicVar(criteria.TableName);
string strParameter = string.Empty;
string strWhereSql = string.Empty;
string strCols = string.Empty;
StringBuilder sbSql = new StringBuilder();
sbSql.AppendLine("-- ===========================================================");
sbSql.AppendLine("-- Author: Jason.Yao");
sbSql.AppendFormat("-- Create date: {0}", DateTime.Now.ToString("yyyy-MM-dd"));
sbSql.AppendLine();
sbSql.AppendFormat("-- Description: {0} DELETE", criteria.TableName);
sbSql.AppendLine();
sbSql.AppendLine("-- ===========================================================");
sbSql.AppendFormat("CREATE OR REPLACE PROCEDURE USP_{0}_DELETE(", criteria.TableName.ToUpper());
sbSql.AppendLine();
foreach (var item in colPK)
{
sbSql.AppendFormat(" {0}{1} IN {2},", criteria.SqlParameterPrefix, item.PublicVarName, item.DataType);
sbSql.AppendLine();
}
sbSql.Append(" resultMsg OUT VARCHAR2");
sbSql.AppendLine(")");
sbSql.AppendLine("IS");
sbSql.Append(" ROWCOUNT NUMBER; ");
sbSql.Append("BEGIN ");
sbSql.AppendFormat(" SELECT COUNT(*) INTO ROWCOUNT FROM {0} WHERE ", criteria.TableName);
foreach (var item in colPK)
{
if (string.IsNullOrWhiteSpace(strWhereSql))
strWhereSql += string.Format("{0} = {1}{2} ", item.ColumnName, criteria.SqlParameterPrefix, item.PublicVarName);
else
strWhereSql += string.Format(" AND {0} = {1}{2} ", item.ColumnName, criteria.SqlParameterPrefix, item.PublicVarName);
}
sbSql.Append(strWhereSql);
sbSql.Append(";");
sbSql.AppendLine();
sbSql.AppendLine(" IF ROWCOUNT > 0 THEN ");
sbSql.AppendFormat(" DELETE FROM {0} ", criteria.TableName);
sbSql.AppendFormat(" WHERE {0}; ", strWhereSql);
sbSql.AppendLine();
sbSql.AppendFormat(" resultMsg:='{0}{1}'; ", BaseDict.SuccessPrefix, BaseDict.Success);
sbSql.AppendLine();
sbSql.AppendLine(" ELSE ");
sbSql.AppendFormat(" resultMsg :='{0}{1}' ; ", BaseDict.ErrorPrefix, BaseDict.SqlExMsgNoData);
sbSql.AppendLine();
sbSql.AppendLine(" END IF; ");
sbSql.AppendLine("END;");
string sql = sbSql.ToString();
return sql;
}
/// <summary>
/// 生成Model Code
/// </summary>
/// <param name="resultMsg">执行结果信息</param>
/// <param name="tableName">表名</param>
/// <param name="dataBaseType">数据库类型</param>
/// <param name="dataBaseName">数据库名</param>
/// <param name="modelsNamespace">Model层命名空间</param>
/// <param name="modelClassNamePrefix">Model层类名前缀</param>
public string ModelCodeGeneration(out string resultMsg, string tableName, string dataBaseType = BaseDict.SqlServerData,
string dataBaseName = null, string modelsNamespace = null, string modelClassNamePrefix = "Model")
{
resultMsg = string.Empty;
var criteria = new GeneratorCriteria();
criteria.DataBaseType = dataBaseType;
criteria.TableName = tableName;
criteria.ModelsNamespace = modelsNamespace;
criteria.ModelClassNamePrefix = modelClassNamePrefix;
criteria.DataBaseName = dataBaseName;
IGeneration gen = CreateInstance(criteria.DataBaseType);
string dbName = CommonMethod.StringToPublicVar(dataBaseName);
var className = CommonMethod.StringToPublicVar(criteria.TableName);
var colList = gen.QueryColumnsByTable(out resultMsg, criteria);
StringBuilder sb = new StringBuilder();
sb.AppendLine("using System;");
sb.AppendLine("using System.Collections.Generic;");
sb.AppendLine("using System.Linq;");
sb.AppendLine("using System.Text;");
sb.AppendLine("using System.ComponentModel.DataAnnotations;");
sb.AppendLine();
if (string.IsNullOrWhiteSpace(modelsNamespace))
sb.AppendFormat("namespace Library.Models.{0}", dbName);
else
sb.AppendFormat("namespace Library.{0}", criteria.ModelsNamespace);
sb.AppendLine();
sb.AppendLine("{");
sb.AppendFormat(" public class {0}{1}", criteria.ModelClassNamePrefix, className);
sb.AppendLine();
sb.AppendLine(" {");
foreach (ModelGeneration item in colList)
{
Type type = CommonMethod.SqlTypeToCsharpType(item.DataType);
var regExpression = string.Empty; // CommonMethod.GetRegularExpression(type.Name);
if (!string.IsNullOrWhiteSpace(item.ColumnComments))
{
sb.AppendLine(" /// <summary> ");
sb.AppendFormat(" /// {0} ", item.ColumnComments.Replace(Environment.NewLine, " "));
sb.AppendLine();
sb.AppendLine(" /// </summary>");
}
if (!string.IsNullOrWhiteSpace(regExpression))
{
if (!item.IsNull)
sb.AppendLine(" [Required]");
sb.AppendFormat(" [Display(Name = \"{0}\")]", item.ColumnComments.Replace(Environment.NewLine, " "));
sb.AppendLine();
sb.Append(" " + string.Format(regExpression, item.ColumnComments.Replace(Environment.NewLine," ")));
sb.AppendLine();
}
sb.AppendFormat(" public {0} {1} ", CommonMethod.TypeConversion(type.Name), item.PublicVarName);
sb.Append("{ get;set;} ");
sb.AppendLine(Environment.NewLine);
}
sb.AppendLine(" }");
sb.AppendLine("}");
var code = sb.ToString();
return code;
}
public string IFacadeCodeGeneration(out string resultMsg, string tableName, string dataBaseType = BaseDict.SqlServerData, string dataBaseName = null,
string modelsNamespace = null, string modelClassNamePrefix = "Model", string dalNamespace = null, string dalClassNamePrefix = "Logic",
string facadeNamespace = null, string FacadeClassNamePrefix = "Facade")
{
resultMsg = string.Empty;
var criteria = new GeneratorCriteria();
criteria.DataBaseType = dataBaseType;
criteria.TableName = tableName;
criteria.ModelsNamespace = modelsNamespace;
criteria.DalNamespace = dalNamespace;
criteria.DalClassNamePrefix = dalClassNamePrefix;
criteria.ModelClassNamePrefix = modelClassNamePrefix;
criteria.DataBaseName = dataBaseName;
IGeneration gen = CreateInstance(criteria.DataBaseType);
var colList = gen.QueryColumnsByTable(out resultMsg, criteria);
string classNamePrivate = CommonMethod.StringToPrivateVar(criteria.TableName);
string classNamePublic = CommonMethod.StringToPublicVar(criteria.TableName);
string dbName = CommonMethod.StringToPublicVar(dataBaseName);
var strParameter = string.Empty; //参数
//主键列集合
var colPK = (from ModelGeneration col in colList
where col.IsPrimaryKey == true
select col).ToList();
StringBuilder sb = new StringBuilder();
#region Top
sb.AppendLine("using System;");
sb.AppendLine("using System.Collections.Generic;");
sb.AppendLine("using System.Linq;");
sb.AppendLine("using System.Text;");
sb.AppendLine("using System.Data;");
sb.AppendLine("using Library.Kernel.DataBaseHelper;");
sb.AppendLine("using Library.StringItemDict;");
sb.AppendLine("using Library.Common;");
sb.AppendLine("using System.Data.Common;");
if (string.IsNullOrWhiteSpace(modelsNamespace))
sb.AppendFormat("using Library.Models.{0}", dbName);
else
sb.AppendFormat("using Library.{0}", criteria.ModelsNamespace);
sb.AppendLine();
if (string.IsNullOrWhiteSpace(criteria.DalNamespace))
{
sb.AppendFormat("using Library.{0}", "Logics");
}
else
{
sb.AppendFormat("using " + criteria.DalNamespace + ".Interfaces", dbName);
sb.AppendLine();
sb.AppendFormat("using " + criteria.DalNamespace + ".Classes", dbName);
}
sb.AppendLine();
if (string.IsNullOrWhiteSpace(criteria.DalNamespace))
sb.AppendFormat("namespace Library.{0}", "Facades");
else
sb.AppendFormat("namespace " + facadeNamespace + ".Interfaces", dbName);
sb.AppendLine();
sb.AppendLine("{");
sb.AppendFormat(" public interface I{0}{1}", FacadeClassNamePrefix, classNamePublic);
sb.AppendLine();
sb.AppendLine(" {");
#endregion
#region Pager
sb.AppendLine(" /// <summary>");
sb.AppendLine(" /// 分页查询 ");
sb.AppendLine(" /// </summary>");
sb.AppendLine(" /// <param name=\"recordCount\">输出参数 数据总数</param>");
sb.AppendLine(" /// <param name=\"criteria\">查询条件对象</param>");
sb.AppendLine(" /// <param name=\"pageSize\">每页显示数量</param>");
sb.AppendLine(" /// <param name=\"pageIndex\">当前页索引</param>");
sb.AppendLine(" /// <returns>结果集 泛型</returns>");
sb.AppendFormat(" public IList<{0}{1}> Query{1}ListPager(out string resultMsg, out decimal recordCount, Criteria{1} criteria, int pageSize = 10, int pageIndex = 1)",
criteria.ModelClassNamePrefix, classNamePublic);
sb.AppendLine();
sb.AppendLine();
#endregion
#region Detail
sb.AppendLine(" /// <summary>");
sb.AppendLine(" /// 查询实体");
sb.AppendLine(" /// </summary>");
foreach (var item in colPK)
{
string dataTypeName = CommonMethod.SqlTypeToCsharpType(item.DataType).Name;
sb.AppendFormat(" /// <param name=\"{0}\">{1}{2} {3}</param>", item.PrivateVarName,
criteria.ModelClassNamePrefix, item.PublicVarName, item.ColumnComments);
sb.AppendLine();
}
sb.AppendFormat(" /// <returns>{0}{1}</returns>", criteria.ModelClassNamePrefix, classNamePublic);
sb.AppendLine();
sb.AppendFormat(" {0}{1} {2}Detail(out string resultMsg,", criteria.ModelClassNamePrefix, classNamePublic, classNamePublic);
foreach (var item in colPK)
{
string dataTypeName = CommonMethod.SqlTypeToCsharpType(item.DataType).Name;
if (string.IsNullOrWhiteSpace(strParameter))
strParameter += string.Format("{0} {1} ", CommonMethod.TypeConversion(dataTypeName), item.PrivateVarName);
else
strParameter += string.Format(",{0} {1} ", CommonMethod.TypeConversion(dataTypeName), item.PrivateVarName);
}
sb.Append(strParameter);
sb.AppendLine(");");
sb.AppendLine();
#endregion
#region InsertUpdate
sb.AppendLine(" /// <summary>");
sb.AppendLine(" /// 数据 添加/更新");
sb.AppendLine(" /// </summary>");
sb.AppendFormat(" /// <param name=\"{0}\">实体</param>", classNamePrivate);
sb.AppendLine();
sb.AppendLine(" /// <returns>执行结果</returns>");
sb.AppendFormat(" int {0}InsertUpdate(out string resultMsg,{1}{2} {3},DbTransaction tran =null);", classNamePublic, criteria.ModelClassNamePrefix, classNamePublic, classNamePrivate);
sb.AppendLine();
sb.AppendLine();
#endregion
#region UpdateStatus
sb.AppendLine(" /// <summary>");
sb.AppendLine(" /// 数据状态 更新");
sb.AppendLine(" /// </summary>");
foreach (var item in colList)
{
if (item.IsPrimaryKey)//判断主键
{
sb.AppendFormat(" /// <param name=\"{0}\">{1} {2}</param>", item.PrivateVarName, item.PublicVarName, item.ColumnComments);
sb.AppendLine();
}
if (item.ColumnName.ToLower().IndexOf("deleted") > -1 || item.ColumnName.ToLower().IndexOf("status") > -1)//判断状态字段
{
sb.AppendFormat(" /// <param name=\"{0}\">状态</param>", item.PrivateVarName);
sb.AppendLine();
}
}
sb.AppendLine(" /// <returns>执行结果</returns>");
sb.AppendFormat(" int {0}UpdateStatus(out string resultMsg", classNamePublic);
foreach (var item in colList)
{
string dataTypeName = CommonMethod.SqlTypeToCsharpType(item.DataType).Name;
strParameter = string.Empty;
if (item.IsPrimaryKey || item.ColumnName.ToLower().IndexOf("deleted") > -1 || item.ColumnName.ToLower().IndexOf("status") > -1)
{
strParameter += string.Format(",{0} {1} ", CommonMethod.TypeConversion(dataTypeName), item.PrivateVarName);
sb.Append(strParameter);
}
}
sb.AppendLine(",DbTransaction tran=null);");
sb.AppendLine();
#endregion
#region Detele
sb.AppendLine(" /// <summary>");
sb.AppendLine(" /// 数据 物理删除");
sb.AppendLine(" /// </summary>");
foreach (var item in colList)
{
if (item.IsPrimaryKey)//判断主键
{
sb.AppendFormat(" /// <param name=\"{0}\">{1} {2}</param>", item.PrivateVarName, item.PublicVarName, item.ColumnComments);
sb.AppendLine();
}
}
sb.AppendLine(" /// <returns>执行结果</returns>");
sb.AppendFormat(" int {0}Delete(out string resultMsg", classNamePublic);
foreach (var item in colList)
{
string dataTypeName = CommonMethod.SqlTypeToCsharpType(item.DataType).Name;
strParameter = string.Empty;
if (item.IsPrimaryKey)
{
strParameter += string.Format(",{0} {1} ", CommonMethod.TypeConversion(dataTypeName), item.PrivateVarName);
sb.Append(strParameter);
}
}
sb.AppendLine(",DbTransaction tran=null);");
sb.AppendLine();
#endregion
sb.AppendLine(" }");
sb.AppendLine("}");
return sb.ToString();
}
/// <summary>
/// DAL层代码生成
/// </summary>
/// <param name="resultMsg">执行结果信息</param>
/// <param name="tableName">表名</param>
/// <param name="dataBaseType">数据库类型</param>
/// <param name="dataBaseName">数据库名</param>
/// <param name="modelsNamespace">Model层命名空间</param>
/// <param name="modelClassNamePrefix">Model层类名前缀</param>
/// <param name="dalNamespace">Dal层命名空间</param>
/// <param name="dalClassNamePrefix">Dal层类名前缀</param>
public string DalCodeGeneration(out string resultMsg, string tableName, string dataBaseType = BaseDict.SqlServerData, string dataBaseName = null,
string modelsNamespace = null, string modelClassNamePrefix = "Model", string dalNamespace = null, string dalClassNamePrefix = "Logic")
{
resultMsg = string.Empty;
var criteria = new GeneratorCriteria();
criteria.DataBaseType = dataBaseType;
criteria.TableName = tableName;
criteria.ModelsNamespace = modelsNamespace;
criteria.DalNamespace = dalNamespace;
criteria.DalClassNamePrefix = dalClassNamePrefix;
criteria.ModelClassNamePrefix = modelClassNamePrefix;
criteria.DataBaseName = dataBaseName;
IGeneration gen = CreateInstance(criteria.DataBaseType);
var colList = gen.QueryColumnsByTable(out resultMsg, criteria);
string classNamePrivate = CommonMethod.StringToPrivateVar(criteria.TableName);
string classNamePublic = CommonMethod.StringToPublicVar(criteria.TableName);
string dbName = CommonMethod.StringToPublicVar(dataBaseName);
var strParameter = string.Empty; //参数
//主键列集合
var colPK = (from ModelGeneration col in colList
where col.IsPrimaryKey == true
select col).ToList();
StringBuilder sb = new StringBuilder();
#region Top
sb.AppendLine("using System;");
sb.AppendLine("using System.Collections.Generic;");
sb.AppendLine("using System.Linq;");
sb.AppendLine("using System.Text;");
sb.AppendLine("using System.Data;");
sb.AppendLine("using Library.Kernel.DataBaseHelper;");
sb.AppendLine("using Library.StringItemDict;");
sb.AppendLine("using Library.Common;");
sb.AppendLine("using System.Data.Common;");
if(string.IsNullOrWhiteSpace(criteria.ModelsNamespace))
sb.AppendFormat("using Library.Models.CustomsModels;", dbName);
else
sb.AppendFormat("using Library.Models;");
sb.AppendLine();
if (string.IsNullOrWhiteSpace(criteria.DalNamespace))
sb.AppendFormat("namespace Library.{0}", "Logics");
else
sb.AppendFormat("namespace " + criteria.DalNamespace + ".Classes", dbName);
sb.AppendLine();
sb.AppendLine("{");
sb.AppendFormat(" public class {0}{1} : I{0}{1}", criteria.DalClassNamePrefix, classNamePublic);
sb.AppendLine();
sb.AppendLine(" {");
sb.AppendLine();
#endregion
#region GetModel IDataReader
sb.AppendLine(" #region 私有函数");
sb.AppendLine(" /// <summary>");
sb.AppendLine(" /// Model 赋值 IDataReader");
sb.AppendLine(" /// </summary>");
sb.AppendFormat(" private IList<{0}{1}> GetModel(IDataReader dr)", criteria.ModelClassNamePrefix, classNamePublic);
sb.AppendLine();
sb.AppendLine(" {");
sb.AppendFormat(" var modelList = new List<{0}{1}>();", criteria.ModelClassNamePrefix, classNamePublic);
sb.AppendLine();
sb.AppendLine();
sb.AppendLine(" while (dr.Read())");
sb.AppendLine(" {");
sb.AppendFormat(" var model = new {0}{1}();", criteria.ModelClassNamePrefix, classNamePublic);
sb.AppendLine();
foreach (var item in colList)
{
var dataType = CommonMethod.SqlTypeToCsharpType(item.DataType);
var dataValue = CommonMethod.GetDataValueIsNotNull(dataType.Name, string.Format("dr[\"{0}\"]", item.ColumnName));
sb.AppendFormat(" model.{0} = {1};", item.PublicVarName, dataValue);
sb.AppendLine();
}
sb.AppendLine(" modelList.Add(model);");
sb.AppendLine(" }");
sb.AppendLine(" return modelList;");
sb.AppendLine(" }");
sb.AppendLine();
#endregion
#region GetModel DataSet
sb.AppendLine(" /// <summary>");
sb.AppendLine(" /// Model 赋值 DataSet");
sb.AppendLine(" /// </summary>");
sb.AppendFormat(" private IList<{0}{1}> GetModel(DataSet ds)", criteria.ModelClassNamePrefix, classNamePublic);
sb.AppendLine();
sb.AppendLine(" {");
sb.AppendLine(" var modelList = (from DataRow dr in ds.Tables[0].Rows");
sb.AppendFormat(" select new {0}{1}()", criteria.ModelClassNamePrefix, classNamePublic);
sb.AppendLine();
sb.AppendLine(" {");
foreach (var item in colList)
{
var dataType = CommonMethod.SqlTypeToCsharpType(item.DataType);
var dataValue = CommonMethod.GetDataValueIsNotNull(dataType.Name, string.Format("dr[\"{0}\"]", item.ColumnName));
sb.AppendFormat(" {0} = {1},", item.PublicVarName, dataValue);
sb.AppendLine();
}
sb.Remove(sb.Length - 1, 1);
sb.AppendLine(" }).ToList();");
sb.AppendLine(" return modelList;");
sb.AppendLine(" }");
sb.AppendLine();
sb.AppendLine(" #endregion");
sb.AppendLine();
#endregion
#region Pager
sb.AppendLine(" /// <summary>");
sb.AppendLine(" /// 分页查询 ");
sb.AppendLine(" /// </summary>");
sb.AppendLine(" /// <param name=\"recordCount\">输出参数 数据总数</param>");
sb.AppendLine(" /// <param name=\"criteria\">查询条件对象</param>");
sb.AppendLine(" /// <param name=\"pageSize\">每页显示数量</param>");
sb.AppendLine(" /// <param name=\"pageIndex\">当前页索引</param>");
sb.AppendLine(" /// <returns>结果集 泛型</returns>");
sb.AppendFormat(" public IList<{0}{1}> Query{1}ListPager(out string resultMsg, out decimal recordCount, Criteria{1}.Pager criteria, int pageSize = 10, int pageIndex = 1)",
criteria.ModelClassNamePrefix, classNamePublic);
sb.AppendLine();
sb.AppendLine(" {");
sb.AppendLine(" recordCount = decimal.Zero;");
sb.AppendLine(" resultMsg = string.Empty;");
sb.AppendFormat(" IList<{0}{1}> list = new List<{0}{1}>();", criteria.ModelClassNamePrefix, classNamePublic);
sb.AppendLine();
sb.AppendLine(" try");
sb.AppendLine(" {");
sb.AppendLine(" //存储过程名称");
sb.AppendFormat(" string sql = \"USP_{0}_SELECT_SEARCH_PAGER\";", criteria.TableName.ToUpper());
sb.AppendLine();
sb.AppendLine();
sb.AppendLine(" //参数添加");
sb.AppendLine(" IList<DBParameter> parm = new List<DBParameter>();");
sb.AppendLine(" parm.Add(new DBParameter() { ParameterName = \"PagerSize\", ParameterValue = pageSize, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.String });");
sb.AppendLine(" parm.Add(new DBParameter() { ParameterName = \"PagerIndex\", ParameterValue = pageIndex, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.String });");
sb.AppendLine(" parm.Add(new DBParameter() { ParameterName = \"RowCount\", ParameterInOut = BaseDict.ParmOut, ParameterType = DbType.String });");
sb.AppendLine();
sb.AppendLine(" //查询执行");
sb.AppendLine(" using (IDataReader dr = DBHelper.ExecuteReader(sql, true, parm))");
sb.AppendLine(" {");
sb.AppendLine(" //DataReader 转换成 List");
sb.AppendLine(" list = GetModel(dr);");
sb.AppendLine(" foreach (var item in parm)");
sb.AppendLine(" {");
sb.AppendLine(" //获取输出参数值");
sb.AppendLine(" if (item.ParameterName == \"RowCount\")");
sb.AppendLine(" {");
sb.AppendLine(" decimal.TryParse(item.ParameterValue.ToString(), out recordCount);");
sb.AppendLine(" break;");
sb.AppendLine(" }");
sb.AppendLine(" }");
sb.AppendLine(" }");
sb.AppendLine(" }");
sb.AppendLine(" catch (Exception ex)");
sb.AppendLine(" {");
sb.AppendLine(" resultMsg = string.Format(\"{0} {1}\", BaseDict.ErrorPrefix, ex.ToString());");
sb.AppendLine(" }");
sb.AppendLine(" return list;");
sb.AppendLine(" }");
sb.AppendLine();
sb.AppendLine();
#endregion
#region Detail
sb.AppendLine(" /// <summary>");
sb.AppendLine(" /// 查询实体");
sb.AppendLine(" /// </summary>");
foreach (var item in colPK)
{
string dataTypeName = CommonMethod.SqlTypeToCsharpType(item.DataType).Name;
sb.AppendFormat(" /// <param name=\"{0}\">{1}{2} {3}</param>", item.PrivateVarName,
criteria.ModelClassNamePrefix, item.PublicVarName, item.ColumnComments);
sb.AppendLine();
}
sb.AppendFormat(" /// <returns>{0}{1}</returns>", criteria.ModelClassNamePrefix, classNamePublic);
sb.AppendLine();
sb.AppendFormat(" public {0}{1} {2}Detail(out string resultMsg,", criteria.ModelClassNamePrefix, classNamePublic, classNamePublic);
foreach (var item in colPK)
{
string dataTypeName = CommonMethod.SqlTypeToCsharpType(item.DataType).Name;
if (string.IsNullOrWhiteSpace(strParameter))
strParameter += string.Format("{0} {1} ", CommonMethod.TypeConversion(dataTypeName), item.PrivateVarName);
else
strParameter += string.Format(",{0} {1} ", CommonMethod.TypeConversion(dataTypeName), item.PrivateVarName);
}
sb.Append(strParameter);
sb.AppendLine(")");
sb.AppendLine(" {");
sb.AppendLine(" resultMsg = string.Empty;");
sb.AppendFormat(" var model = new {0}{1}();", criteria.ModelClassNamePrefix, classNamePublic);
sb.AppendLine();
sb.AppendLine(" try");
sb.AppendLine(" {");
sb.AppendLine(" //存储过程名称");
sb.AppendFormat(" string sql = \"USP_{0}_SELECT_DETAIL_BY", criteria.TableName.ToUpper());
foreach (var item in colPK)
{
sb.AppendFormat("_{0}", item.ColumnName.ToUpper());
}
sb.AppendLine("\";");
sb.AppendLine();
sb.AppendLine(" //参数添加");
sb.AppendLine(" IList<DBParameter> parm = new List<DBParameter>();");
foreach (var item in colPK)
{
sb.Append(" parm.Add(new DBParameter() { ");
sb.AppendFormat("ParameterName = \"{0}\", ", item.PrivateVarName.ToUpper());
sb.AppendFormat("ParameterValue = {0}, ParameterInOut = BaseDict.ParmIn, ParameterType = {1} ",
item.PrivateVarName, CommonMethod.SqlTypeToCsharpTypeString(item.DataType));
sb.AppendLine("});");
}
sb.AppendLine();
sb.AppendLine(" //查询执行");
sb.AppendLine(" using (IDataReader dr = DBHelper.ExecuteReader(sql, true, parm))");
sb.AppendLine(" {");
sb.AppendFormat(" IList<{0}{1}> list = GetModel(dr);", criteria.ModelClassNamePrefix, classNamePublic);
sb.AppendLine();
sb.AppendLine(" model = list.First();");
sb.AppendLine(" }");
sb.AppendLine(" }");
sb.AppendLine(" catch (Exception ex)");
sb.AppendLine(" {");
sb.AppendLine(" resultMsg = string.Format(\"{0} {1}\", BaseDict.ErrorPrefix, ex.ToString());");
sb.AppendLine(" }");
sb.AppendLine(" return model;");
sb.AppendLine(" }");
sb.AppendLine();
#endregion
#region InsertUpdate
sb.AppendLine(" /// <summary>");
sb.AppendLine(" /// 数据 添加/更新");
sb.AppendLine(" /// </summary>");
sb.AppendFormat(" /// <param name=\"{0}\">实体</param>", classNamePrivate);
sb.AppendLine();
sb.AppendLine(" /// <returns>执行结果</returns>");
sb.AppendFormat(" public int {0}InsertUpdate(out string resultMsg,{1}{2} {3},DbTransaction tran =null)", classNamePublic, criteria.ModelClassNamePrefix, classNamePublic, classNamePrivate);
sb.AppendLine();
sb.AppendLine(" {");
sb.AppendLine(" resultMsg = string.Empty;");
sb.AppendLine(" int res = 0;");
sb.AppendLine(" try");
sb.AppendLine(" {");
sb.AppendLine(" //存储过程名称");
sb.AppendFormat(" string sql = \"USP_{0}_INSERT_UPDATE\";", criteria.TableName.ToUpper());
sb.AppendLine();
sb.AppendLine(" //参数添加");
sb.AppendLine(" IList<DBParameter> parm = new List<DBParameter>();");
foreach (var item in colList)
{
sb.Append(" parm.Add(new DBParameter() { ");
sb.AppendFormat("ParameterName = \"{0}\", ", item.PrivateVarName.ToUpper());
sb.AppendFormat("ParameterValue = {0}.{1}, ParameterInOut = BaseDict.ParmIn, ParameterType = {2} ",
classNamePrivate, item.PublicVarName, CommonMethod.SqlTypeToCsharpTypeString(item.DataType));
sb.AppendLine("});");
}
sb.AppendLine(" parm.Add(new DBParameter() { ParameterName = \"resultMsg\", ParameterInOut = BaseDict.ParmOut, ParameterType = DbType.String });");
sb.AppendLine();
sb.AppendLine(" //新增/更新执行");
sb.AppendLine(" res = DBHelper.ExecuteNonQuery(sql, true, parm, tran);");
sb.AppendLine(" foreach (var item in parm)");
sb.AppendLine(" {");
sb.AppendLine(" //获取输出参数值");
sb.AppendLine(" if (item.ParameterName == \"resultMsg\")");
sb.AppendLine(" {");
sb.AppendLine(" resultMsg = item.ParameterValue.ToString();");
sb.AppendLine(" break;");
sb.AppendLine(" }");
sb.AppendLine(" }");
sb.AppendLine(" }");
sb.AppendLine(" catch (Exception ex)");
sb.AppendLine(" {");
sb.AppendLine(" if(tran != null)");
sb.AppendLine(" tran.Rollback();");
sb.AppendLine(" resultMsg = string.Format(\"{0} {1}\", BaseDict.ErrorPrefix, ex.ToString());");
sb.AppendLine(" }");
sb.AppendLine(" return res;");
sb.AppendLine(" }");
sb.AppendLine();
#endregion
#region UpdateStatus
sb.AppendLine(" /// <summary>");
sb.AppendLine(" /// 数据状态 更新");
sb.AppendLine(" /// </summary>");
foreach (var item in colList)
{
if (item.IsPrimaryKey)//判断主键
{
sb.AppendFormat(" /// <param name=\"{0}\">{1} {2}</param>", item.PrivateVarName, item.PublicVarName, item.ColumnComments);
sb.AppendLine();
}
if (item.ColumnName.ToLower().IndexOf("deleted") > -1 || item.ColumnName.ToLower().IndexOf("status") > -1)//判断状态字段
{
sb.AppendFormat(" /// <param name=\"{0}\">状态</param>", item.PrivateVarName);
sb.AppendLine();
}
}
sb.AppendLine(" /// <returns>执行结果</returns>");
sb.AppendFormat(" public int {0}UpdateStatus(out string resultMsg", classNamePublic);
foreach (var item in colList)
{
string dataTypeName = CommonMethod.SqlTypeToCsharpType(item.DataType).Name;
strParameter = string.Empty;
if (item.IsPrimaryKey || item.ColumnName.ToLower().IndexOf("deleted") > -1 || item.ColumnName.ToLower().IndexOf("status") > -1)
{
strParameter += string.Format(",{0} {1} ", CommonMethod.TypeConversion(dataTypeName), item.PrivateVarName);
sb.Append(strParameter);
}
}
sb.AppendLine(",DbTransaction tran=null)");
sb.AppendLine(" {");
sb.AppendLine(" resultMsg = string.Empty;");
sb.AppendLine(" int res = 0;");
sb.AppendLine(" try");
sb.AppendLine(" {");
sb.AppendLine(" //存储过程名称");
sb.AppendFormat(" string sql = \"USP_{0}_UPDATE_STATUS\";", criteria.TableName);
sb.AppendLine();
sb.AppendLine(" //参数添加");
sb.AppendLine(" IList<DBParameter> parm = new List<DBParameter>();");
foreach (var item in colList)
{
strParameter = string.Empty;
if (item.IsPrimaryKey || item.ColumnName.ToLower().IndexOf("deleted") > -1 || item.ColumnName.ToLower().IndexOf("status") > -1)
{
sb.Append(" parm.Add(new DBParameter() { ");
sb.AppendFormat("ParameterName = \"{0}\", ", item.PrivateVarName.ToUpper());
sb.AppendFormat("ParameterValue = {0}, ParameterInOut = BaseDict.ParmIn, ParameterType = {1} ",
item.PrivateVarName, CommonMethod.SqlTypeToCsharpTypeString(item.DataType));
sb.AppendLine("});");
}
}
sb.AppendLine(" parm.Add(new DBParameter() { ParameterName = \"resultMsg\", ParameterInOut = BaseDict.ParmOut, ParameterType = DbType.String });");
sb.AppendLine(" //更新执行");
sb.AppendLine(" res = DBHelper.ExecuteNonQuery(sql, true, parm, tran);");
sb.AppendLine(" foreach (var item in parm)");
sb.AppendLine(" {");
sb.AppendLine(" //获取输出参数值");
sb.AppendLine(" if (item.ParameterName == \"resultMsg\")");
sb.AppendLine(" {");
sb.AppendLine(" resultMsg = item.ParameterValue.ToString();");
sb.AppendLine(" break;");
sb.AppendLine(" }");
sb.AppendLine(" }");
sb.AppendLine(" }");
sb.AppendLine(" catch (Exception ex)");
sb.AppendLine(" {");
sb.AppendLine(" if(tran != null)");
sb.AppendLine(" tran.Rollback();");
sb.AppendLine(" resultMsg = string.Format(\"{0} {1}\", BaseDict.ErrorPrefix, ex.ToString());");
sb.AppendLine(" }");
sb.AppendLine(" return res;");
sb.AppendLine(" }");
sb.AppendLine();
#endregion
#region Detele
sb.AppendLine(" /// <summary>");
sb.AppendLine(" /// 数据 删除");
sb.AppendLine(" /// </summary>");
foreach (var item in colList)
{
if (item.IsPrimaryKey)//判断主键
{
sb.AppendFormat(" /// <param name=\"{0}\">{1} {2}</param>", item.PrivateVarName, item.PublicVarName, item.ColumnComments);
sb.AppendLine();
}
}
sb.AppendLine(" /// <returns>执行结果</returns>");
sb.AppendFormat(" public int {0}Delete(out string resultMsg", classNamePublic);
foreach (var item in colList)
{
string dataTypeName = CommonMethod.SqlTypeToCsharpType(item.DataType).Name;
strParameter = string.Empty;
if (item.IsPrimaryKey)
{
strParameter += string.Format(",{0} {1} ", CommonMethod.TypeConversion(dataTypeName), item.PrivateVarName);
sb.Append(strParameter);
}
}
sb.AppendLine(",DbTransaction tran=null)");
sb.AppendLine(" {");
sb.AppendLine(" resultMsg = string.Empty;");
sb.AppendLine(" int res = 0;");
sb.AppendLine(" try");
sb.AppendLine(" {");
sb.AppendLine(" //存储过程名称");
sb.AppendFormat(" string sql = \" USP_{0}_DELETE \" ;", criteria.TableName.ToUpper());
sb.AppendLine();
sb.AppendLine();
sb.AppendLine(" //参数添加");
sb.AppendLine(" IList<DBParameter> parm = new List<DBParameter>();");
foreach (var item in colList)
{
strParameter = string.Empty;
if (item.IsPrimaryKey)
{
sb.Append(" parm.Add(new DBParameter() { ");
sb.AppendFormat("ParameterName = \"{0}\", ", item.PrivateVarName.ToUpper());
sb.AppendFormat("ParameterValue = {0}, ParameterInOut = BaseDict.ParmIn, ParameterType = {1} ",
item.PrivateVarName, CommonMethod.SqlTypeToCsharpTypeString(item.DataType));
sb.AppendLine("});");
}
}
sb.AppendLine(" parm.Add(new DBParameter() { ParameterName = \"resultMsg\", ParameterInOut = BaseDict.ParmOut, ParameterType = DbType.String });");
sb.AppendLine(" //更新执行");
sb.AppendLine(" res = DBHelper.ExecuteNonQuery(sql, true, parm, tran);");
sb.AppendLine(" foreach (var item in parm)");
sb.AppendLine(" {");
sb.AppendLine(" //获取输出参数值");
sb.AppendLine(" if (item.ParameterName == \"resultMsg\")");
sb.AppendLine(" {");
sb.AppendLine(" resultMsg = item.ParameterValue.ToString();");
sb.AppendLine(" break;");
sb.AppendLine(" }");
sb.AppendLine(" }");
sb.AppendLine(" }");
sb.AppendLine(" catch (Exception ex)");
sb.AppendLine(" {");
sb.AppendLine(" if(tran != null)");
sb.AppendLine(" tran.Rollback();");
sb.AppendLine(" resultMsg = string.Format(\"{0} {1}\", BaseDict.ErrorPrefix, ex.ToString());");
sb.AppendLine(" }");
sb.AppendLine(" return res;");
sb.AppendLine(" }");
sb.AppendLine();
#endregion
sb.AppendLine(" }");
sb.AppendLine("}");
return sb.ToString();
}
/// <summary>
/// SQL Detail 语句生成
/// </summary>
public string GenerateSqlForSelectDetail(out string resultMsg, GeneratorCriteria criteria)
{
resultMsg = string.Empty;
//查询字段列表
var colList = QueryColumnsByTable(out resultMsg, criteria);
//查询主键字段列表
var colPK = (from ModelGeneration model in colList
where model.IsPrimaryKey == true
select model).ToList();
string classNamePrivate = CommonMethod.StringToPrivateVar(criteria.TableName);
string classNamePublic = CommonMethod.StringToPublicVar(criteria.TableName);
string strParameter = string.Empty;
string strWhereSql = string.Empty;
StringBuilder sbSql = new StringBuilder();
sbSql.AppendLine("-- ===========================================================");
sbSql.AppendLine("-- Author: Jason.Yao");
sbSql.AppendFormat("-- Create date: {0}", DateTime.Now.ToString("yyyy-MM-dd"));
sbSql.AppendLine();
sbSql.AppendFormat("-- Description: {0} SELECT DETAIL", criteria.TableName);
sbSql.AppendLine();
sbSql.AppendLine("-- ===========================================================");
sbSql.AppendFormat("CREATE PROCEDURE usp_{0}_select_detail_by", criteria.TableName.ToLower());
foreach (var item in colPK)
{
sbSql.AppendFormat("_{0}", item.ColumnName.ToLower());
}
sbSql.AppendLine();
foreach (var item in colPK)
{
var strSqlTypeAndLength = GetSqlTypeAndLength(item);
if (string.IsNullOrWhiteSpace(strParameter))
strParameter += string.Format(" @{0}{1} {2}", criteria.SqlParameterPrefix, item.PublicVarName, strSqlTypeAndLength);
else
strParameter += string.Format(",{0} @{1}{2} {3}", Environment.NewLine, criteria.SqlParameterPrefix, item.PublicVarName, strSqlTypeAndLength);
}
sbSql.AppendLine(strParameter);
sbSql.AppendLine("AS");
sbSql.AppendLine(" BEGIN ");
sbSql.AppendFormat(" SELECT * FROM {0} WHERE ", criteria.TableName);
foreach (var item in colPK)
{
if (string.IsNullOrWhiteSpace(strWhereSql))
strWhereSql += string.Format("[{0}] = @{1}{2} ", item.ColumnName, criteria.SqlParameterPrefix, item.PublicVarName);
else
strWhereSql += string.Format(" AND [{0}] = @{1}{2} ", item.ColumnName, criteria.SqlParameterPrefix, item.PublicVarName);
}
sbSql.Append(strWhereSql);
sbSql.AppendLine();
sbSql.AppendLine(" END ");
string sql = sbSql.ToString();
return sql;
}
/// <summary>
/// SQL Select Pager 语句生成
/// </summary>
public string GenerateSqlForSelectPager(out string resultMsg, GeneratorCriteria criteria)
{
resultMsg = string.Empty;
//查询字段列表
var colList = QueryColumnsByTable(out resultMsg, criteria);
//查询主键字段列表
var colPK = (from ModelGeneration model in colList
where model.IsPrimaryKey == true
select model).ToList();
string classNamePrivate = CommonMethod.StringToPrivateVar(criteria.TableName);
string classNamePublic = CommonMethod.StringToPublicVar(criteria.TableName);
string strParameter = string.Empty;
string strWhereSql = string.Empty;
string strCols = string.Empty;
string strParmPagerIndex = string.Format("{0}PagerIndex", criteria.SqlParameterPrefix);
string strParmPagerSize = string.Format("{0}PagerSize", criteria.SqlParameterPrefix);
string strParmRowCount = string.Format("{0}RowCount", criteria.SqlParameterPrefix);
string strParmTotalPages = string.Format("{0}TotalPages", criteria.SqlParameterPrefix);
string strParmStartRow = string.Format("{0}StartRow", criteria.SqlParameterPrefix);
string strParmEndRow = string.Format("{0}EndRow", criteria.SqlParameterPrefix);
StringBuilder sbSql = new StringBuilder();
sbSql.AppendLine("-- ===========================================================");
sbSql.AppendLine("-- Author: Jason.Yao");
sbSql.AppendFormat("-- Create date: {0}", DateTime.Now.ToString("yyyy-MM-dd"));
sbSql.AppendLine();
sbSql.AppendFormat("-- Description: {0} DELETE", criteria.TableName);
sbSql.AppendLine();
sbSql.AppendLine("-- ===========================================================");
sbSql.AppendFormat("CREATE PROCEDURE usp_{0}_select_pager ", criteria.TableName.ToLower());
sbSql.AppendLine();
sbSql.AppendFormat(" @{0} INT , ", strParmPagerIndex);
sbSql.AppendLine();
sbSql.AppendFormat(" @{0} INT , ", strParmPagerSize);
sbSql.AppendLine();
sbSql.AppendFormat(" @{0} INT OUT ,", strParmRowCount);
sbSql.AppendLine();
sbSql.AppendFormat(" @{0} INT OUT ", strParmTotalPages);
sbSql.AppendLine();
sbSql.AppendLine("AS");
sbSql.AppendLine("BEGIN ");
sbSql.AppendFormat(" DECLARE @{0} INT ,@{1} INT", strParmStartRow, strParmEndRow);
sbSql.AppendLine();
sbSql.AppendFormat(" SET @{0} = (", strParmRowCount);
sbSql.AppendLine();
sbSql.AppendFormat(" SELECT COUNT(*) FROM {0}", criteria.TableName);
sbSql.AppendLine();
sbSql.AppendLine(" --WHERE 语句");
sbSql.AppendFormat(" )", criteria.TableName);
sbSql.AppendLine();
sbSql.AppendFormat(" SET @{0} = CEILING(CONVERT(FLOAT, @{1}) / @{2})",
strParmTotalPages, strParmRowCount, strParmPagerSize);
sbSql.AppendLine();
sbSql.AppendFormat(" IF @{0} > @{1} ", strParmPagerIndex, strParmTotalPages);
sbSql.AppendLine();
sbSql.AppendFormat(" SET @{0} = @{1}", strParmPagerIndex, strParmTotalPages);
sbSql.AppendLine();
sbSql.AppendFormat(" IF @{0} < 1 ", strParmPagerIndex);
sbSql.AppendLine();
sbSql.AppendFormat(" SET @{0} = 1", strParmPagerIndex);
sbSql.AppendLine();
sbSql.AppendFormat(" SET @{0} = ( @{1} - 1 ) * @{2} + 1", strParmStartRow, strParmPagerIndex, strParmPagerSize);
sbSql.AppendLine();
sbSql.AppendFormat(" IF @{0} > @{1} * @{2} ", strParmRowCount, strParmPagerIndex, strParmPagerSize);
sbSql.AppendLine();
sbSql.AppendFormat(" SET @{0} = @{1} * @{2} ", strParmEndRow, strParmPagerIndex, strParmPagerSize);
sbSql.AppendLine();
sbSql.AppendLine(" ELSE ");
sbSql.AppendFormat(" SET @{0} = @{1} ; ", strParmEndRow, strParmRowCount);
sbSql.AppendLine();
sbSql.AppendLine(" WITH temptbl AS ( ");
sbSql.Append(" SELECT ROW_NUMBER() OVER ( ORDER BY ");
strCols = string.Empty;
foreach (var item in colPK)
{
if (string.IsNullOrWhiteSpace(strCols))
strCols += string.Format(" [{0}] ", item.ColumnName);
else
strCols += string.Format(", [{0}] ",item.ColumnName);
}
sbSql.Append(strCols);
sbSql.Append(" DESC ) AS 'row_no' ,* ");
sbSql.AppendLine();
sbSql.AppendFormat(" FROM {0} ",criteria.TableName);
sbSql.AppendLine();
sbSql.AppendLine(" --WHERE --where 条件");
sbSql.AppendLine(" )");
sbSql.AppendLine(" SELECT * FROM temptbl ");
sbSql.AppendFormat(" WHERE row_no BETWEEN @{0} AND @{1}", strParmStartRow, strParmEndRow);
sbSql.AppendLine();
sbSql.AppendLine("END");
string sql = sbSql.ToString();
return sql;
}
/// <summary>
/// 根据表名查询列集合
/// </summary>
/// <param name="tableName">表名</param>
public IList<ModelGeneration> QueryColumnsByTable(out string resultMsg, GeneratorCriteria criteria)
{
IList<ModelGeneration> list = new List<ModelGeneration>();
resultMsg = string.Empty;
try
{
//存储过程名称
#region SQL拼接
StringBuilder SQL = new StringBuilder();
SQL.Append("SELECT ");
SQL.Append(" CASE ");
SQL.Append(" WHEN F.COLUMN_NAME = A.COLUMN_NAME ");
SQL.Append(" THEN 1 ");
SQL.Append(" ELSE 0 ");
SQL.Append(" END AS PRIMARYKEY, ");
SQL.Append(" C.TABLE_TYPE, ");
SQL.Append(" C.COMMENTS TABCOMMENTS, ");
SQL.Append(" B.COMMENTS COLCOMMENTS, ");
SQL.Append(" A.* ");
SQL.Append("FROM USER_TAB_COLUMNS A ");
SQL.Append("INNER JOIN USER_COL_COMMENTS B ");
SQL.Append("ON A.TABLE_NAME = B.TABLE_NAME ");
SQL.Append("AND A.TABLE_NAME =:TABLE_NAME ");
SQL.Append("AND A.COLUMN_NAME = B.COLUMN_NAME ");
SQL.Append("INNER JOIN USER_TAB_COMMENTS C ");
SQL.Append("ON C.TABLE_NAME = A.TABLE_NAME ");
SQL.Append("LEFT JOIN ");
SQL.Append(" (SELECT COL.* ");
SQL.Append(" FROM USER_CONSTRAINTS CON, ");
SQL.Append(" USER_CONS_COLUMNS COL ");
SQL.Append(" WHERE CON.CONSTRAINT_NAME=COL.CONSTRAINT_NAME ");
SQL.Append(" AND CON.CONSTRAINT_TYPE ='P' ");
SQL.Append(" AND COL.TABLE_NAME =:TABLE_NAME ");
SQL.Append(" ) F ");
SQL.Append("ON F.COLUMN_NAME = A.COLUMN_NAME ");
#endregion
string sql = SQL.ToString();
//参数添加
IList<DBParameter> parm = new List<DBParameter>();
parm.Add(new DBParameter() { ParameterName = "TABLE_NAME", ParameterValue = criteria.TableName, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.String });
//查询执行
using (IDataReader dr = DBHelper.ExecuteReader(sql, false, parm))
{
list = GetColumnModel(dr);
}
}
catch (Exception EX)
{
resultMsg = string.Format("{0} {1}", BaseDict.ErrorPrefix, EX.ToString());
}
return list;
}
/// <summary>
/// 根据表名查询列集合
/// </summary>
/// <param name="tableName">表名</param>
public IList<ModelGeneration> QueryColumnsByTable(out string resultMsg, GeneratorCriteria criteria)
{
IList<ModelGeneration> list = new List<ModelGeneration>();
resultMsg = string.Empty;
try
{
//存储过程名称
#region SQL语句
string strSql = @"USE {0}
SELECT COL.name AS [COLUMN_NAME] ,
COL.isnullable AS [NULLABLE] ,
col.length AS [DATA_LENGTH] ,
col.prec AS [PREC] ,
col.scale AS [SCALE] ,
SEP.Value AS [COLCOMMENTS] ,
ST.name AS [DATA_TYPE] ,
COLUMNPROPERTY(COL.id, COL.name, 'IsIdentity') AS [IsIdentity] ,
[PRIMARYKEY] = CASE WHEN EXISTS ( SELECT 1
FROM sysobjects
WHERE xtype = 'PK'
AND parent_obj = COL.id
AND name IN (
SELECT name
FROM sysindexes
WHERE indid IN (
SELECT
indid
FROM
sysindexkeys
WHERE
id = COL.id
AND colid = COL.colid ) ) )
THEN '1'
ELSE '0'
END
FROM SysColumns COL
LEFT JOIN sys.extended_properties SEP ON COL.id = SEP.major_id
AND COL.colid = SEP.minor_id
LEFT JOIN systypes ST ON COL.xusertype = ST.xusertype
WHERE COL.id = OBJECT_ID(@TableName)";
strSql = string.Format(strSql, criteria.DataBaseName);
#endregion
//参数添加
IList<DBParameter> parm = new List<DBParameter>();
parm.Add(new DBParameter() { ParameterName = "@TableName", ParameterValue = criteria.TableName, ParameterInOut = BaseDict.ParmIn, ParameterType = DbType.String });
//查询执行
using (IDataReader dr = DBHelper.ExecuteReader(strSql, false, parm))
{
list = GetColumnModel(dr);
}
}
catch (Exception EX)
{
resultMsg = string.Format("{0} {1}", BaseDict.ErrorPrefix, EX.Message);
}
return list;
}
/// <summary>
/// 查询所有表名集合
/// </summary>
public IList<ModelGeneration> QueryTablesAll(out string resultMsg, GeneratorCriteria criteria = null)
{
IList<ModelGeneration> list = new List<ModelGeneration>();
resultMsg = string.Empty;
try
{
//存储过程名称
string strSql = "SELECT * FROM USER_TAB_COMMENTS ";
//查询执行
using (IDataReader dr = DBHelper.ExecuteReader(strSql, false))
{
list = GetTableModel(dr);
}
}
catch (Exception EX)
{
resultMsg = string.Format("{0} {1}", BaseDict.ErrorPrefix, EX.Message);
}
return list;
}
/// <summary>
/// SQL Insert/Update 语句生成
/// </summary>
public string GenerateSqlForInsertUpdate(out string resultMsg, GeneratorCriteria criteria)
{
resultMsg = string.Empty;
//查询字段列表
var colList = QueryColumnsByTable(out resultMsg, criteria);
//查询主键字段列表
var colPK = (from ModelGeneration model in colList
where model.IsPrimaryKey == true
select model).ToList();
string classNamePrivate = CommonMethod.StringToPrivateVar(criteria.TableName);
string classNamePublic = CommonMethod.StringToPublicVar(criteria.TableName);
string strParameter = string.Empty;
string strWhereSql = string.Empty;
string strCols = string.Empty;
StringBuilder sbSql = new StringBuilder();
sbSql.AppendLine("-- ===========================================================");
sbSql.AppendLine("-- Author: Jason.Yao");
sbSql.AppendFormat("-- Create date: {0}", DateTime.Now.ToString("yyyy-MM-dd"));
sbSql.AppendLine();
sbSql.AppendFormat("-- Description: {0} INSERT UPDATE", criteria.TableName);
sbSql.AppendLine();
sbSql.AppendLine("-- ===========================================================");
sbSql.AppendFormat("CREATE PROCEDURE usp_{0}_insert_update ", criteria.TableName.ToLower());
sbSql.AppendLine();
foreach (var item in colList)
{
var strSqlTypeAndLength = GetSqlTypeAndLength(item);
if (string.IsNullOrWhiteSpace(strParameter))
strParameter += string.Format(" @{0}{1} {2}", criteria.SqlParameterPrefix, item.PublicVarName, strSqlTypeAndLength);
else
strParameter += string.Format(",{0} @{1}{2} {3}", Environment.NewLine, criteria.SqlParameterPrefix, item.PublicVarName, strSqlTypeAndLength);
}
sbSql.AppendLine(strParameter);
//sbSql.AppendLine(" @resultMsg NVARCHAR(500) OUT");
sbSql.AppendLine("AS");
sbSql.AppendLine("BEGIN ");
sbSql.AppendFormat("IF EXISTS(SELECT * FROM {0} WHERE ", criteria.TableName);
foreach (var item in colPK)
{
if (string.IsNullOrWhiteSpace(strWhereSql))
strWhereSql += string.Format("[{0}] = @{1}{2} ", item.ColumnName, criteria.SqlParameterPrefix, item.PublicVarName);
else
strWhereSql += string.Format(" AND [{0}] = @{1}{2} ", item.ColumnName, criteria.SqlParameterPrefix, item.PublicVarName);
}
sbSql.Append(strWhereSql);
sbSql.Append(")");
sbSql.AppendLine();
sbSql.AppendLine(" BEGIN");
sbSql.AppendFormat(" UPDATE {0} ", criteria.TableName);
sbSql.AppendLine();
sbSql.AppendLine(" SET");
strParameter = string.Empty;
foreach (var item in colList)
{
if (string.IsNullOrWhiteSpace(strParameter))
{
if (item.IsNull)
strParameter += string.Format(" [{0}] = @{1}{2} ", criteria.SqlParameterPrefix, item.PublicVarName);
else
strParameter += string.Format(" [{0}] = CASE WHEN @{1}{2} IS NOT NULL THEN @{1}{2} ELSE [{0}] END ",
item.ColumnName, criteria.SqlParameterPrefix, item.PublicVarName );
}
else
{
if(item.IsNull)
strParameter += string.Format(",{0} [{1}] = @{2}{3} ",
Environment.NewLine, item.ColumnName, criteria.SqlParameterPrefix, item.PublicVarName );
else
strParameter += string.Format(",{0} [{1}] = CASE WHEN @{2}{3} IS NOT NULL THEN @{2}{3} ELSE [{1}] END ",
Environment.NewLine, item.ColumnName, criteria.SqlParameterPrefix, item.PublicVarName );
}
}
sbSql.AppendLine(strParameter);
sbSql.AppendFormat(" WHERE {0} ", strWhereSql);
sbSql.AppendLine();
sbSql.AppendLine(" END ");
sbSql.AppendLine(" ELSE ");
sbSql.AppendLine(" BEGIN ");
sbSql.AppendFormat(" INSERT INTO {0} ( ", criteria.TableName);
sbSql.AppendLine();
foreach (var item in colList)
{
if (string.IsNullOrWhiteSpace(strCols))
strCols += string.Format(" [{0}]", item.ColumnName);
else
strCols += string.Format(",{0} [{1}] ", Environment.NewLine, item.ColumnName);
}
sbSql.AppendLine(strCols);
sbSql.AppendLine(" ) VALUES ( ");
strCols = string.Empty;
foreach (var item in colList)
{
if (string.IsNullOrWhiteSpace(strCols))
strCols += string.Format(" @{0}{1}", criteria.SqlParameterPrefix, item.PublicVarName);
else
strCols += string.Format(",{0} @{1}{2} ", Environment.NewLine, criteria.SqlParameterPrefix, item.PublicVarName);
}
sbSql.AppendLine(strCols);
sbSql.AppendLine(" ) ");
sbSql.AppendLine(" END");
sbSql.AppendLine("END");
//sbSql.AppendLine("IF @@ERROR <> 0 ");
//sbSql.AppendLine("BEGIN ");
//sbSql.AppendLine(" SET @resultMsg = 'Error:Failed to InsertUpdate charge.' ");
//sbSql.AppendLine(" RETURN ");
//sbSql.AppendLine("END ");
//sbSql.AppendLine("ELSE ");
//sbSql.AppendLine("BEGIN ");
//sbSql.AppendLine(" SET @resultMsg = 'Success' ");
//sbSql.AppendLine("END;");
string sql = sbSql.ToString();
return sql;
}
/// <summary>
/// SQL Insert/Update 语句生成
/// </summary>
public string GenerateSqlForInsertUpdate(out string resultMsg, GeneratorCriteria criteria)
{
resultMsg = string.Empty;
//查询字段列表
var colList = QueryColumnsByTable(out resultMsg, criteria);
//查询主键字段列表
var colPK = (from ModelGeneration model in colList
where model.IsPrimaryKey == true
select model).ToList();
string classNamePrivate = CommonMethod.StringToPrivateVar(criteria.TableName);
string classNamePublic = CommonMethod.StringToPublicVar(criteria.TableName);
string strParameter = string.Empty;
string strWhereSql = string.Empty;
string strCols = string.Empty;
StringBuilder sbSql = new StringBuilder();
sbSql.AppendLine("-- ===========================================================");
sbSql.AppendLine("-- Author: Jason.Yao");
sbSql.AppendFormat("-- Create date: {0}", DateTime.Now.ToString("yyyy-MM-dd"));
sbSql.AppendLine();
sbSql.AppendFormat("-- Description: {0} INSERT UPDATE", criteria.TableName);
sbSql.AppendLine();
sbSql.AppendLine("-- ===========================================================");
sbSql.AppendFormat("CREATE OR REPLACE PROCEDURE USP_{0}_INSERT_UPDATE(", criteria.TableName.ToUpper());
sbSql.AppendLine();
foreach (var item in colList)
{
if(string.IsNullOrWhiteSpace(strParameter))
strParameter += string.Format(" {0}{1} IN {2}", criteria.SqlParameterPrefix, item.PublicVarName, item.DataType);
else
strParameter += string.Format(",{0} {1}{2} IN {3}", Environment.NewLine,criteria.SqlParameterPrefix, item.PublicVarName, item.DataType);
}
sbSql.AppendLine(strParameter);
//sbSql.Append(" resultMsg OUT VARCHAR2");
sbSql.AppendLine(")");
sbSql.AppendLine("IS");
sbSql.AppendLine(" ROWCOUNT NUMBER; ");
sbSql.AppendLine("BEGIN ");
sbSql.AppendFormat(" SELECT COUNT(*) INTO ROWCOUNT FROM {0} WHERE ",criteria.TableName);
foreach (var item in colPK)
{
if (string.IsNullOrWhiteSpace(strWhereSql))
strWhereSql += string.Format("{0} = {1}{2} ", item.ColumnName, criteria.SqlParameterPrefix, item.PublicVarName);
else
strWhereSql += string.Format(" AND {0} = {1}{2} ", item.ColumnName, criteria.SqlParameterPrefix, item.PublicVarName);
}
sbSql.Append(strWhereSql);
sbSql.Append(";");
sbSql.AppendLine();
sbSql.AppendLine(" IF ROWCOUNT > 0 THEN ");
sbSql.AppendFormat(" UPDATE {0} ", criteria.TableName);
sbSql.AppendLine();
sbSql.AppendLine(" SET");
strParameter = string.Empty;
foreach (var item in colList)
{
if (string.IsNullOrWhiteSpace(strParameter))
strParameter += string.Format(" {0} = CASE WHEN {1}{2} IS NOT NULL THEN {3}{4} ELSE {5} END ",
item.ColumnName, criteria.SqlParameterPrefix, item.PublicVarName, criteria.SqlParameterPrefix, item.PublicVarName, item.ColumnName);
else
strParameter += string.Format(",{0} {1} = CASE WHEN {2}{3} IS NOT NULL THEN {4}{5} ELSE {6} END ",
Environment.NewLine, item.ColumnName, criteria.SqlParameterPrefix, item.PublicVarName, criteria.SqlParameterPrefix, item.PublicVarName, item.ColumnName);
}
sbSql.AppendLine(strParameter);
sbSql.AppendFormat(" WHERE {0}; ",strWhereSql);
sbSql.AppendLine();
sbSql.AppendLine(" ELSE ");
sbSql.AppendFormat(" INSERT INTO {0} ", criteria.TableName);
sbSql.AppendLine();
sbSql.AppendLine(" ( ");
foreach (var item in colList)
{
if (string.IsNullOrWhiteSpace(strCols))
strCols += string.Format(" {0}",item.ColumnName);
else
strCols += string.Format(",{0} {1} ", Environment.NewLine,item.ColumnName);
}
sbSql.AppendLine(strCols);
sbSql.AppendLine(" ) ");
sbSql.AppendLine(" VALUES ");
sbSql.AppendLine(" ( ");
strCols = string.Empty;
foreach (var item in colList)
{
if (string.IsNullOrWhiteSpace(strCols))
strCols += string.Format(" {0}{1}", criteria.SqlParameterPrefix, item.PublicVarName);
else
strCols += string.Format(",{0} {1}{2} ", Environment.NewLine, criteria.SqlParameterPrefix , item.PublicVarName);
}
sbSql.AppendLine(strCols);
sbSql.AppendLine(" ); ");
sbSql.AppendLine(" END IF; ");
sbSql.AppendLine("END;");
string sql = sbSql.ToString();
return sql;
}
public void QueryColumnsByTableTest()
{
string errorMsg = string.Empty;
var criteria = new GeneratorCriteria();
criteria.DataBaseName = "CardMaintain";
criteria.TableName = "Bank";
IList<ModelGeneration> res1 = null;
var res = dal.QueryColumnsByTable(out errorMsg, criteria);
Assert.AreNotEqual(res, res1);
var PublicColName = string.Empty;
var publicCName = res[0].PublicVarName;
var pk = res[0].IsPrimaryKey;
Assert.AreNotEqual(PublicColName, publicCName);
}
/// <summary>
/// 查询数据表 集合
/// </summary>
/// <param name="resultMsg"></param>
/// <param name="DataBaseType">数据库类型</param>
/// <param name="DataBaseName">数据库名称</param>
/// <returns></returns>
public IList<ModelGeneration> QueryTablesAll(out string resultMsg, string DataBaseType, string DataBaseName = null)
{
resultMsg = string.Empty;
GeneratorCriteria criteria = new GeneratorCriteria() { DataBaseName = DataBaseName };
IGeneration gen = CreateInstance(DataBaseType);
var list = gen.QueryTablesAll(out resultMsg, criteria);
return list;
}