/// <summary> /// Generate Model Layer /// </summary> /// <param name="tableName"></param> /// <param name="args"></param> public void GenerateModel(GeneratorArgs args) { 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("namespace " + args.RootNamespace + ".Model"); sb.AppendLine("{"); sb.AppendLine("public partial class " + tableName); sb.AppendLine("{"); foreach (DataRow row in dtCols.Rows) { string colName = Convert.ToString(row["Column_Name"]); string dataType = Convert.ToString(row["Data_Type"]); Type netType = GetDotNetTypeByDBType(dataType); string netTypeName; // Set value type to be nullable if (netType.IsValueType) { netTypeName = netType.ToString() + "?"; } else { netTypeName = netType.ToString(); } sb.AppendLine("public " + netTypeName + " " + colName + " { get; set; }"); } sb.AppendLine("}"); sb.AppendLine("}"); string modelDir = Path.Combine(args.OutputDir, "Model"); // Add folder seperator automatically string modelFile = Path.Combine(modelDir, tableName + ".cs"); Directory.CreateDirectory(modelDir); File.WriteAllText(modelFile, sb.ToString()); }
/// <summary> /// Generate BLL layer /// </summary> /// <param name="tableName"></param> /// <param name="args"></param> public void GenerateBLL(GeneratorArgs args) { 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 " + args.RootNamespace + ".Model;"); sb.AppendLine("using " + args.RootNamespace + ".DAL;"); sb.AppendLine("namespace " + args.RootNamespace + ".BLL"); sb.AppendLine("{"); sb.AppendLine("public partial class " + tableName + "BLL"); sb.AppendLine("{"); sb.AppendLine("private static readonly string _ClassMsg = \"Class: " + tableName + "BLL\";"); // class message for exception sb.AppendLine(); sb.AppendLine("public int AddNew(" + tableName + " model, string dbConnString){"); sb.AppendLine("string functionMsg = \"Function: AddNew(" + tableName + " model, string dbConnString)\";"); // function message for exception sb.AppendLine("try"); sb.AppendLine("{"); sb.AppendLine("return new " + tableName + "DAL().AddNew(model, dbConnString);}"); sb.AppendLine("catch (Exception ex)"); sb.AppendLine("{"); sb.AppendLine("throw new Exception(string.Format(\"Exception: {0}{1}{2}{3}{4}{5}\", ex.Message, Environment.NewLine, _ClassMsg, Environment.NewLine, functionMsg, Environment.NewLine));"); sb.AppendLine("}"); sb.AppendLine("}"); sb.AppendLine(); /*sb.AppendLine("public bool Delete(string whereSql, string dbConnString){"); sb.AppendLine("string functionMsg = \"Function: Delete(string whereSql, string dbConnString)\";"); // function message for exception sb.AppendLine("try"); sb.AppendLine("{"); sb.AppendLine("return new " + tableName + "DAL().Delete(whereSql, dbConnString);}"); sb.AppendLine("catch (Exception ex)"); sb.AppendLine("{"); sb.AppendLine("throw new Exception(string.Format(\"Exception: {0}{1}{2}{3}{4}{5}\", ex.Message, Environment.NewLine, _ClassMsg, Environment.NewLine, functionMsg, Environment.NewLine));"); sb.AppendLine("}"); sb.AppendLine("}"); sb.AppendLine();*/ /*sb.AppendLine("public bool Update(" + tableName + " model, string dbConnString){"); sb.AppendLine("string functionMsg = \"Function: Update(" + tableName + " model, string dbConnString)\";"); // function message for exception sb.AppendLine("try"); sb.AppendLine("{"); sb.AppendLine("return new " + tableName + "DAL().Update(model, dbConnString);}"); sb.AppendLine("catch (Exception ex)"); sb.AppendLine("{"); sb.AppendLine("throw new Exception(string.Format(\"Exception: {0}{1}{2}{3}{4}{5}\", ex.Message, Environment.NewLine, _ClassMsg, Environment.NewLine, functionMsg, Environment.NewLine));"); sb.AppendLine("}"); sb.AppendLine("}"); sb.AppendLine();*/ /*sb.AppendLine("public " + tableName + " Get(string whereSql, string dbConnString){"); sb.AppendLine("string functionMsg = \"Function: Get(string whereSql, string dbConnString)\";"); // function message for exception sb.AppendLine("try"); sb.AppendLine("{"); sb.AppendLine("return new " + tableName + "DAL().Get(whereSql, dbConnString);}"); sb.AppendLine("catch (Exception ex)"); sb.AppendLine("{"); sb.AppendLine("throw new Exception(string.Format(\"Exception: {0}{1}{2}{3}{4}{5}\", ex.Message, Environment.NewLine, _ClassMsg, Environment.NewLine, functionMsg, Environment.NewLine));"); sb.AppendLine("}"); sb.AppendLine("}"); sb.AppendLine();*/ sb.AppendLine("public List<" + tableName + "> ListAll(string dbConnString){"); sb.AppendLine("string functionMsg = \"Function: ListAll(string dbConnString)\";"); // function message for exception sb.AppendLine("try"); sb.AppendLine("{"); sb.AppendLine("return new " + tableName + "DAL().ListAll(dbConnString);}"); sb.AppendLine("catch (Exception ex)"); sb.AppendLine("{"); sb.AppendLine("throw new Exception(string.Format(\"Exception: {0}{1}{2}{3}{4}{5}\", ex.Message, Environment.NewLine, _ClassMsg, Environment.NewLine, functionMsg, Environment.NewLine));"); sb.AppendLine("}"); sb.AppendLine("}"); sb.AppendLine("}"); sb.AppendLine("}"); string bllDir = Path.Combine(args.OutputDir, "BLL"); string bllFile = Path.Combine(bllDir, tableName + "BLL.cs"); Directory.CreateDirectory(bllDir); File.WriteAllText(bllFile, sb.ToString()); }
/// <summary> /// Generate DAL layer /// </summary> /// <param name="tableName"></param> /// <param name="args"></param> public void GenerateDAL(GeneratorArgs args) { // Exclude columns with Identity property DataTable dtNoIdentityCols = SqlHelper.ExecuteDataTable("SELECT * FROM INFORMATION_SCHEMA.COLUMNS " + "WHERE COLUMNPROPERTY(OBJECT_ID(TABLE_NAME),COLUMN_NAME,'IsIdentity') = 0 AND TABLE_NAME = @tablename", new SqlParameter("tablename", tableName)); //"select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME=@tablename", new SqlParameter("tablename", tableName)); 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 " + args.RootNamespace + ".Model;"); sb.AppendLine("using System.Data.SqlClient;"); sb.AppendLine("using System.Data;"); sb.AppendLine("namespace " + args.RootNamespace + ".DAL"); sb.AppendLine("{"); sb.AppendLine("public partial class " + tableName + "DAL"); sb.AppendLine("{"); sb.AppendLine("private static readonly string _ClassMsg = \"Class: " + tableName + "DAL\";"); // class message for exception sb.AppendLine(); { sb.AppendLine("public int AddNew(" + tableName + " model, string dbConnString){"); sb.AppendLine("string functionMsg = \"Function: AddNew(" + tableName + " model, string dbConnString)\";"); // function message for exception sb.AppendLine("try"); sb.AppendLine("{"); string[] cols = GetCols(dtNoIdentityCols); string[] colParams = (from col in cols select "@" + col).ToArray(); sb.AppendLine("string sql = \"insert into " + tableName + "(" + string.Join(",", cols) + ") values(" + string.Join(",", colParams) + "); select @@ROWCOUNT AS 'AffectedCount'\";"); // Show the new inserted id sb.AppendLine("int affectedCount = (int)SqlHelper.ExecuteScalar(sql, dbConnString"); foreach (string col in cols) { if (dictDefaultValueCols.ContainsKey(col)) { sb.AppendLine(",new SqlParameter(\"" + col + "\", model." + col + " == null ? "+ dictDefaultValueCols[col] +" : model." + col + ")"); } else { //, new SqlParameter("TelPhone", model.TelPhone == null ? (object)DBNull.Value : model.TelPhone) sb.AppendLine(",new SqlParameter(\"" + col + "\", model." + col + " == null ? (object)DBNull.Value : model." + col + ")"); // sb.AppendLine(",new SqlParameter(\"" + col + "\", model." + col + ")"); } } sb.AppendLine(");"); sb.AppendLine("return affectedCount;"); sb.AppendLine("}"); sb.AppendLine("catch (Exception ex)"); sb.AppendLine("{"); sb.AppendLine("throw new Exception(string.Format(\"Exception: {0}{1}{2}{3}{4}{5}\", ex.Message, Environment.NewLine, _ClassMsg, Environment.NewLine, functionMsg, Environment.NewLine));"); sb.AppendLine("}"); sb.AppendLine("}"); } sb.AppendLine(); { sb.AppendLine("/* Used for reference"); sb.AppendLine("public bool Update(" + tableName + " model, string dbConnString){"); sb.AppendLine("string functionMsg = \"Function: Update(" + tableName + " model, string dbConnString)\";"); // function message for exception sb.AppendLine("try"); sb.AppendLine("{"); string[] cols = GetCols(dtNoIdentityCols); string[] colParams = (from col in cols select col + "=@" + col).ToArray(); string whereSql = " where"; foreach (DataRow dr in dtPrimaryKeys.Rows) { whereSql += string.Format(" {0}=@{0} and", dr["ColName"].ToString()); } whereSql = whereSql.TrimEnd('a', 'n', 'd'); sb.AppendLine("string sql = \"update " + tableName + " set " + string.Join(",", colParams) + whereSql + "\"; "); sb.AppendLine("int rows = SqlHelper.ExecuteNonQuery(sql, dbConnString"); foreach (string col in GetCols(dtCols)) { if (dictDefaultValueCols.ContainsKey(col)) { sb.AppendLine(",new SqlParameter(\"" + col + "\", model." + col + " == null ? " + dictDefaultValueCols[col] + " : model." + col + ")"); } else { sb.AppendLine(",new SqlParameter(\"" + col + "\", model." + col + " == null ? (object)DBNull.Value : model." + col + ")"); //sb.AppendLine(",new SqlParameter(\"" + col + "\", model." + col + ")"); } } sb.AppendLine(");"); sb.AppendLine("return rows > 0;"); sb.AppendLine("}"); sb.AppendLine("catch (Exception ex)"); sb.AppendLine("{"); sb.AppendLine("throw new Exception(string.Format(\"Exception: {0}{1}{2}{3}{4}{5}\", ex.Message, Environment.NewLine, _ClassMsg, Environment.NewLine, functionMsg, Environment.NewLine));"); sb.AppendLine("}"); sb.AppendLine("}"); sb.AppendLine("*/"); } sb.AppendLine(); { sb.AppendLine("private bool Delete(string whereSql, string dbConnString, SqlParameter[] parameters){"); sb.AppendLine("string functionMsg = \"Function: Delete(string whereSql, string dbConnString, SqlParameter[] parameters)\";"); // function message for exception sb.AppendLine("try"); sb.AppendLine("{"); sb.AppendLine("int rows = SqlHelper.ExecuteNonQuery(\"delete from " + tableName + " where \"+ whereSql +\"\", dbConnString, parameters);"); sb.AppendLine("return rows > 0;"); sb.AppendLine("}"); sb.AppendLine("catch (Exception ex)"); sb.AppendLine("{"); sb.AppendLine("throw new Exception(string.Format(\"Exception: {0}{1}{2}{3}{4}{5}\", ex.Message, Environment.NewLine, _ClassMsg, Environment.NewLine, functionMsg, Environment.NewLine));"); sb.AppendLine("}"); sb.AppendLine("}"); } sb.AppendLine(); { sb.AppendLine("private static " + tableName + " ToModel(DataRow row){"); sb.AppendLine("string functionMsg = \"Function: ToModel(DataRow row)\";"); // function message for exception sb.AppendLine("try"); sb.AppendLine("{"); sb.AppendLine(tableName + " model = new " + tableName + "();"); foreach (DataRow row in dtCols.Rows) { string colName = Convert.ToString(row["Column_Name"]); string dataType = Convert.ToString(row["Data_Type"]); Type netType = GetDotNetTypeByDBType(dataType); string netTypeName; // Convert nullable value type if (netType.IsValueType) { netTypeName = netType.ToString() + "?"; } else { netTypeName = netType.ToString(); } // model.Number = row.IsNull("Number") ? null : (System.Int32?)ConvertUtility.ToInt(row["Number"]); // model.Number = row.IsNull("Number") ? (System.Int32?)null : ConvertUtility.ToInt(row["Number"]); // The second one is more efficient than the fisrt one, cuz null is rare. // If get a DataTable from a .csv file, the value maybe "123", to cannot convert to int, should use: ConvertUtility.ToInt() sb.AppendLine("model." + colName + " = row.IsNull(\"" + colName + "\")?null:(" + netTypeName + ")row[\"" + colName + "\"];"); } sb.AppendLine("return model;"); sb.AppendLine("}"); sb.AppendLine("catch (Exception ex)"); sb.AppendLine("{"); sb.AppendLine("throw new Exception(string.Format(\"Exception: {0}{1}{2}{3}{4}{5}\", ex.Message, Environment.NewLine, _ClassMsg, Environment.NewLine, functionMsg, Environment.NewLine));"); sb.AppendLine("}"); sb.AppendLine("}"); } sb.AppendLine(); { sb.AppendLine("private " + tableName + " Get(string whereSql, string dbConnString, SqlParameter[] parameters){"); sb.AppendLine("string functionMsg = \"Function: Get(string whereSql, string dbConnString, SqlParameter[] parameters)\";"); // function message for exception sb.AppendLine("try"); sb.AppendLine("{"); sb.AppendLine("DataTable dt = SqlHelper.ExecuteDataTable(\"select * from " + tableName + " (nolock) where \"+ whereSql +\"\", dbConnString, parameters);"); sb.AppendLine("if (dt.Rows.Count > 1)"); sb.AppendLine("{throw new Exception(\"more than 1 row was found\");}"); sb.AppendLine("if (dt.Rows.Count <= 0){return null;}"); sb.AppendLine("DataRow row = dt.Rows[0];"); sb.AppendLine(tableName + " model = ToModel(row);"); sb.AppendLine("return model;"); sb.AppendLine("}"); sb.AppendLine("catch (Exception ex)"); sb.AppendLine("{"); sb.AppendLine("throw new Exception(string.Format(\"Exception: {0}{1}{2}{3}{4}{5}\", ex.Message, Environment.NewLine, _ClassMsg, Environment.NewLine, functionMsg, Environment.NewLine));"); sb.AppendLine("}"); sb.AppendLine("}"); } sb.AppendLine(); { sb.AppendLine("public List<" + tableName + "> ListAll(string dbConnString){"); sb.AppendLine("string functionMsg = \"Function: ListAll(string dbConnString)\";"); // function message for exception sb.AppendLine("try"); sb.AppendLine("{"); sb.AppendLine("List<" + tableName + "> list = new List<" + tableName + ">();"); sb.AppendLine("DataTable dt = SqlHelper.ExecuteDataTable(\"select * from " + tableName + " (nolock) \", dbConnString);"); sb.AppendLine("foreach (DataRow row in dt.Rows){"); sb.AppendLine("list.Add(ToModel(row));}"); sb.AppendLine("return list;"); sb.AppendLine("}"); sb.AppendLine("catch (Exception ex)"); sb.AppendLine("{"); sb.AppendLine("throw new Exception(string.Format(\"Exception: {0}{1}{2}{3}{4}{5}\", ex.Message, Environment.NewLine, _ClassMsg, Environment.NewLine, functionMsg, Environment.NewLine));"); sb.AppendLine("}"); sb.AppendLine("}"); } sb.AppendLine("}"); sb.AppendLine("}"); string dalDir = Path.Combine(args.OutputDir, "DAL"); string dalFile = Path.Combine(dalDir, tableName + "DAL.cs"); Directory.CreateDirectory(dalDir); File.WriteAllText(dalFile, sb.ToString()); string dalSqlHelperFile = Path.Combine(dalDir, "SqlHelper.cs"); File.WriteAllText(dalSqlHelperFile, GenerateSqlHelperForDAL(args.RootNamespace + ".DAL")); }
private void _GenerateBtn_Click(object sender, EventArgs e) { if(string.IsNullOrEmpty(_OutPutDirTxt.Text.Trim())) { MessageBox.Show("Please choose the folder you want to save!"); return; } if (_TablesClb.CheckedItems.Count <= 0) { MessageBox.Show("Pleae at least choose one table to generate!"); return; } _LogTxt.Clear(); GeneratorArgs args = new GeneratorArgs(); args.ConnectionString = _ConnStrTxt.Text; args.OutputDir = _OutPutDirTxt.Text; args.RootNamespace = _NameSpaceTxt.Text; foreach (string tableName in _TablesClb.CheckedItems) { GeneratorUtility generator = new GeneratorUtility(tableName); if (_GenerateModelCbx.Checked) { generator.GenerateModel(args); } if (_GenerateDALCbx.Checked) { generator.GenerateDAL(args); } if (_GenerateBLLCbx.Checked) { generator.GenerateBLL(args); } _LogTxt.AppendText(tableName + "Generate successfully!\n"); } _LogTxt.AppendText("All Done......\n"); MessageBox.Show("Generate Successfully!"); }