/// <summary> /// 创建HanderAdmin层 /// </summary> /// <param name="table_name">表名称</param> /// <param name="table_comment">表说明</param> /// <param name="ds"></param> private void CreateHanderAdmin(string table_name, string table_comment, DataSet ds) { //创建BDHelper DbHelperPostgreSQL dbPGHelp = new DbHelperPostgreSQL(this.ConnectString); DirectoryInfo dir = Directory.CreateDirectory(cur_dir + "/HandAdmin"); //查询主键 //string keyColName = ""; //string keyTypeName = ""; //string strSql = "select pg_constraint.conname as pk_name,pg_attribute.attname as colname,pg_type.typname as typename from pg_constraint inner join pg_class on pg_constraint.conrelid = pg_class.oid inner join pg_attribute on pg_attribute.attrelid = pg_class.oid and pg_attribute.attnum = pg_constraint.conkey[1] inner join pg_type on pg_type.oid = pg_attribute.atttypid where pg_class.relname = '" + table_name + "' and pg_constraint.contype='p'"; //DataSet dsKey = dbPGHelp.Query(strSql.ToString()); //if (dsKey != null && dsKey.Tables[0].Rows.Count > 0) //{ // keyColName = dsKey.Tables[0].Rows[0]["colname"].ToString(); ; // keyTypeName = dsKey.Tables[0].Rows[0]["typename"].ToString(); //} HanderAdmin ha = new HanderAdmin(dir.FullName.ToString()); ha.CreateHanderAdmin(table_name, table_comment, ds); }
/// <summary> /// 创建BLL层 /// </summary> /// <param name="table_name">表名称</param> /// <param name="table_comment">表说明</param> /// <param name="ds"></param> private void CreateBLL(string table_name, string table_comment, DataSet ds) { //创建BDHelper DbHelperPostgreSQL dbPGHelp = new DbHelperPostgreSQL(this.ConnectString); DirectoryInfo dir = Directory.CreateDirectory(cur_dir + "/BLL"); //查询主键 string keyColName = ""; string keyTypeName = ""; string strSql = "select pg_constraint.conname as pk_name,pg_attribute.attname as colname,pg_type.typname as typename from pg_constraint inner join pg_class on pg_constraint.conrelid = pg_class.oid inner join pg_attribute on pg_attribute.attrelid = pg_class.oid and pg_attribute.attnum = pg_constraint.conkey[1] inner join pg_type on pg_type.oid = pg_attribute.atttypid where pg_class.relname = '" + table_name + "' and pg_constraint.contype='p'"; DataSet dsKey = dbPGHelp.Query(strSql.ToString()); if (dsKey != null && dsKey.Tables[0].Rows.Count > 0) { keyColName = dsKey.Tables[0].Rows[0]["colname"].ToString();; keyTypeName = dsKey.Tables[0].Rows[0]["typename"].ToString(); } StringBuilder sb = new StringBuilder(); sb.Append(@"using System; using System.Text; using System.Collections.Generic; using System.Data; using Model; using DAL; namespace BLL {"); sb.Append("\r\n //" + table_comment + ""); sb.Append("\r\n public class " + table_name + ""); sb.Append("\r\n {"); sb.Append("\r\n "); sb.Append("\r\n public " + table_name + "()"); sb.Append("\r\n" + @" { } /// <summary> /// 是否存在该记录 /// </summary> public static bool Exists(" + GetCSharpType(keyTypeName) + " " + keyColName + @") { return DAL." + table_name + ".Exists(" + keyColName + @"); } /// <summary> /// 增加一条数据 /// </summary> "); sb.Append(" public static bool Add(Model." + table_name + " model)"); sb.Append("\r\n" + @" {"); sb.Append("\r\n return DAL." + table_name + ".Add(model);"); sb.Append("" + @" } /// <summary> /// 更新一条数据 /// </summary> "); sb.Append(" public static bool Update(Model." + table_name + " model)"); sb.Append("\r\n" + @" {"); sb.Append("\r\n return DAL." + table_name + ".Update(model);"); sb.Append("" + @" } /// <summary> /// 删除一条数据 /// </summary> "); sb.Append(" public static bool Delete(" + GetCSharpType(keyTypeName) + " " + keyColName + ")"); sb.Append("\r\n {"); sb.Append("\r\n return DAL." + table_name + ".Delete(" + keyColName + ");"); sb.Append("\r\n" + @" } /// <summary> /// 得到一个对象实体 /// </summary> "); sb.Append(" public static Model." + table_name + " GetModel(" + GetCSharpType(keyTypeName) + " " + keyColName + ")"); sb.Append("\r\n {"); sb.Append("\r\n return DAL." + table_name + ".GetModel(" + keyColName + ");"); sb.Append("\r\n" + @" }"); sb.Append("\r\n" + @" /// <summary> /// 获得数据列表 /// </summary> public static DataSet GetList(string strWhere) {"); sb.Append("\r\n return DAL." + table_name + ".GetList(strWhere);"); sb.Append("" + @" } /// <summary> /// 获得数据总数 /// </summary> public static int GetRecordCount(string strWhere) {"); sb.Append("\r\n return DAL." + table_name + ".GetRecordCount(strWhere);"); sb.Append("" + @" } /// <summary> /// 分页获取数据列表 /// </summary> public static DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex, out int recordcount) {"); sb.Append("\r\n recordcount = DAL." + table_name + ".GetRecordCount(strWhere);"); sb.Append("\r\n return DAL." + table_name + ".GetListByPage(strWhere, orderby, startIndex, endIndex);"); sb.Append("" + @" } /// <summary> /// 获得数据列表 /// </summary> "); sb.Append(" public static List<Model." + table_name + "> GetModelList(string strWhere)"); sb.Append("\r\n" + @" {"); sb.Append("\r\n DataSet ds = DAL." + table_name + ".GetList(strWhere);"); sb.Append("\r\n return DataTableToList(ds.Tables[0]);"); sb.Append("" + @" } /// <summary> /// 获得数据列表 /// </summary> "); sb.Append(" public static List<Model." + table_name + "> DataTableToList(DataTable dt)"); sb.Append("\r\n {"); sb.Append("\r\n List<Model." + table_name + "> modelList = new List<Model." + table_name + ">();"); sb.Append("\r\n" + @" int rowsCount = dt.Rows.Count; if (rowsCount > 0) {"); sb.Append("\r\n Model." + table_name + " model;"); sb.Append("\r\n" + @" for (int n = 0; n < rowsCount; n++) {"); sb.Append("\r\n model = new Model." + table_name + "();"); for (int i = 0; i < ds.Tables["t_cols"].Rows.Count; i++) { sb.Append("\r\n if (dt.Rows[n][\"" + ds.Tables["t_cols"].Rows[i]["attname"] + "\"].ToString() != \"\")"); sb.Append("\r\n {"); sb.Append("\r\n model." + ds.Tables["t_cols"].Rows[i]["attname"] + " = Convert." + GetConvertType(ds.Tables["t_cols"].Rows[i]["typname"].ToString()) + "(dt.Rows[n][\"" + ds.Tables["t_cols"].Rows[i]["attname"] + "\"].ToString());"); sb.Append("\r\n }"); } sb.Append("\r\n" + @" modelList.Add(model); } } return modelList;"); sb.Append("\r\n" + @" } /// <summary> /// 获得数据列表 /// </summary> public static DataSet GetAllList() {"); sb.Append("\r\n return DAL." + table_name + ".GetAllList();"); sb.Append("" + @" } } }"); File.WriteAllText(dir.FullName + "/" + table_name + ".cs", sb.ToString()); }
/// <summary> /// 创建DAL层 /// </summary> /// <param name="table_name">表名称</param> /// <param name="table_comment">表说明</param> /// <param name="ds"></param> private void CreateDAL(string table_name, string table_comment, DataSet ds) { //创建BDHelper DbHelperPostgreSQL dbPGHelp = new DbHelperPostgreSQL(this.ConnectString); DirectoryInfo dir = Directory.CreateDirectory(cur_dir + "/DAL"); //表字段集合 string colNames = ""; string coltNames = "TT.R,"; string typeNames = ""; for (int i = 0; i < ds.Tables["t_cols"].Rows.Count; i++) { colNames += ds.Tables["t_cols"].Rows[i]["attname"].ToString() + ","; coltNames += "TT." + ds.Tables["t_cols"].Rows[i]["attname"].ToString() + ","; typeNames += ds.Tables["t_cols"].Rows[0]["typname"].ToString() + ","; } colNames = colNames.Substring(0, colNames.LastIndexOf(",")); coltNames = coltNames.Substring(0, coltNames.LastIndexOf(",")); typeNames = typeNames.Substring(0, typeNames.LastIndexOf(",")); //查询主键 string keyColName = ""; string keyTypeName = ""; string strSql = "select pg_constraint.conname as pk_name,pg_attribute.attname as colname,pg_type.typname as typename from pg_constraint inner join pg_class on pg_constraint.conrelid = pg_class.oid inner join pg_attribute on pg_attribute.attrelid = pg_class.oid and pg_attribute.attnum = pg_constraint.conkey[1] inner join pg_type on pg_type.oid = pg_attribute.atttypid where pg_class.relname = '" + table_name + "' and pg_constraint.contype='p'"; DataSet dsKey = dbPGHelp.Query(strSql.ToString()); if (dsKey != null && dsKey.Tables[0].Rows.Count > 0) { keyColName = dsKey.Tables[0].Rows[0]["colname"].ToString();; keyTypeName = dsKey.Tables[0].Rows[0]["typename"].ToString(); } StringBuilder sb = new StringBuilder(); sb.Append(@"using System; using System.Collections.Generic; using System.Linq; using System.Text; using Model; using System.Collections; using System.Data; using Npgsql; using NpgsqlTypes; using DBUtility; namespace DAL {"); sb.Append("\r\n" + @" public class " + table_name); sb.Append("\r\n" + @" { /// <summary> /// 是否存在该记录 /// </summary>"); sb.Append("\r\n public static bool Exists(" + GetCSharpType(keyTypeName) + " " + keyColName + ")"); sb.Append("\r\n {"); sb.Append("\r\n StringBuilder strSql = new StringBuilder();"); sb.Append("\r\n strSql.Append(\"select count(1) from " + table_name + "\");"); sb.Append("\r\n strSql.Append(\" where " + keyColName + "=@" + keyColName + "\");"); sb.Append("\r\n NpgsqlParameter[] parameters = {"); sb.Append("\r\n new NpgsqlParameter(\"@"+ keyColName + "\", NpgsqlDbType." + GetNpgsqlType(keyTypeName) + ")};"); sb.Append("\r\n parameters[0].Value = " + keyColName + ";"); sb.Append("\r\n return DbHelperPostgreSQL.Exists(strSql.ToString(), parameters);"); sb.Append("\r\n }"); sb.Append("\r\n" + @" /// <summary> /// 增加一条数据 /// </summary>"); sb.Append("\r\n" + @" public static bool Add(Model." + table_name + @" model) {"); sb.Append("\r\n" + @" StringBuilder strSql = new StringBuilder();"); sb.Append("\r\n strSql.Append(\"insert into " + table_name + "(\");"); sb.Append("\r\n strSql.Append(@\""); for (int i = 0; i < ds.Tables["t_cols"].Rows.Count; i++) { //如果第一列为自增长 if (i == 0) { if (ds.Tables["t_cols"].Rows[i]["atthasdef"].ToString().Trim() == "True")//有默认值,视为自增长ID { continue; } } if (i == ds.Tables["t_cols"].Rows.Count - 1) { sb.Append(ds.Tables["t_cols"].Rows[i]["attname"]); } else { sb.Append(ds.Tables["t_cols"].Rows[i]["attname"] + ","); } } sb.Append(")\");"); sb.Append("\r\n strSql.Append(\" values (\");"); sb.Append("\r\n strSql.Append(@\""); for (int i = 0; i < ds.Tables["t_cols"].Rows.Count; i++) { //如果第一列为自增长 if (i == 0) { if (ds.Tables["t_cols"].Rows[i]["atthasdef"].ToString().Trim() == "True")//有默认值,视为自增长ID { continue; } } if (i == ds.Tables["t_cols"].Rows.Count - 1) { sb.Append("@" + ds.Tables["t_cols"].Rows[i]["attname"]); } else { sb.Append("@" + ds.Tables["t_cols"].Rows[i]["attname"] + ","); } } sb.Append(")\");"); sb.Append("\r\n NpgsqlParameter[] parameters = {"); for (int i = 0; i < ds.Tables["t_cols"].Rows.Count; i++) { //如果第一列为自增长 if (i == 0) { if (ds.Tables["t_cols"].Rows[i]["atthasdef"].ToString().Trim() == "True")//有默认值,视为自增长ID { continue; } } if (i == ds.Tables["t_cols"].Rows.Count - 1) { sb.Append("\r\n" + " new NpgsqlParameter(\"@"+ ds.Tables["t_cols"].Rows[i]["attname"] + "\",NpgsqlDbType." + GetNpgsqlType(ds.Tables["t_cols"].Rows[i]["typname"].ToString()) + ")};"); } else { sb.Append("\r\n" + " new NpgsqlParameter(\"@"+ ds.Tables["t_cols"].Rows[i]["attname"] + "\",NpgsqlDbType." + GetNpgsqlType(ds.Tables["t_cols"].Rows[i]["typname"].ToString()) + "),"); } } for (int i = 0; i < ds.Tables["t_cols"].Rows.Count; i++) { //如果第一列为自增长 if (i == 0) { if (ds.Tables["t_cols"].Rows[i]["atthasdef"].ToString().Trim() == "True")//有默认值,视为自增长ID { continue; } } sb.Append("\r\n parameters[" + (i) + "].Value = model." + ds.Tables["t_cols"].Rows[i]["attname"] + ";"); } sb.Append("\r\n" + @" int rows = DbHelperPostgreSQL.ExecuteSql(strSql.ToString(), parameters); if (rows > 0) { return true; } else { return false; } } /// <summary> /// 更新一条数据 /// </summary> "); sb.Append(@" public static bool Update(Model." + table_name + @" model) { StringBuilder strSql = new StringBuilder(); "); sb.Append("\r\n strSql.Append(\"update " + table_name + " set \");"); for (int i = 1; i < ds.Tables["t_cols"].Rows.Count; i++) { if (i == ds.Tables["t_cols"].Rows.Count - 1) { sb.Append("\r\n strSql.Append(\"" + ds.Tables["t_cols"].Rows[i]["attname"] + "=@" + ds.Tables["t_cols"].Rows[i]["attname"] + "\");"); } else { sb.Append("\r\n strSql.Append(\"" + ds.Tables["t_cols"].Rows[i]["attname"] + "=@" + ds.Tables["t_cols"].Rows[i]["attname"] + ",\");"); } } sb.Append("\r\n strSql.Append(\" where " + keyColName + "=@" + keyColName + "\");"); sb.Append("\r\n NpgsqlParameter[] parameters = {"); for (int i = 0; i < ds.Tables["t_cols"].Rows.Count; i++) { if (i == ds.Tables["t_cols"].Rows.Count - 1) { sb.Append("\r\n" + " new NpgsqlParameter(\"@"+ ds.Tables["t_cols"].Rows[i]["attname"] + "\",NpgsqlDbType." + GetNpgsqlType(ds.Tables["t_cols"].Rows[i]["typname"].ToString()) + ")};"); } else { sb.Append("\r\n" + " new NpgsqlParameter(\"@"+ ds.Tables["t_cols"].Rows[i]["attname"] + "\",NpgsqlDbType." + GetNpgsqlType(ds.Tables["t_cols"].Rows[i]["typname"].ToString()) + "),"); } } for (int i = 0; i < ds.Tables["t_cols"].Rows.Count; i++) { sb.Append("\r\n parameters[" + i + "].Value = model." + ds.Tables["t_cols"].Rows[i]["attname"] + ";"); } sb.Append("\r\n" + @" int rows = DbHelperPostgreSQL.ExecuteSql(strSql.ToString(), parameters); if (rows > 0) { return true; } else { return false; } } /// <summary> /// 删除一条数据 /// </summary> public static bool Delete(" + GetCSharpType(keyTypeName) + " " + keyColName + @") { StringBuilder strSql = new StringBuilder();"); sb.Append("\r\n strSql.Append(\"delete from " + table_name + " \");"); sb.Append("\r\n strSql.Append(\" where " + keyColName + "=@" + keyColName + "\");"); sb.Append("\r\n NpgsqlParameter[] parameters = {"); sb.Append("\r\n new NpgsqlParameter(\"@"+ keyColName + "\", NpgsqlDbType." + GetNpgsqlType(keyTypeName) + ")};"); sb.Append("\r\n" + @" parameters[0].Value = " + keyColName + @"; int rows = DbHelperPostgreSQL.ExecuteSql(strSql.ToString(), parameters); if (rows > 0) { return true; } else { return false; } } /// <summary> /// 得到一个对象实体 /// </summary>"); sb.Append("\r\n public static Model." + table_name + " GetModel(" + GetCSharpType(keyTypeName) + " " + keyColName + ")"); sb.Append("\r\n" + @" { StringBuilder strSql = new StringBuilder(); "); sb.Append("\r\n strSql.Append(\"select " + colNames + " from " + table_name + "\");"); sb.Append("\r\n strSql.Append(\" where " + keyColName + "=@" + keyColName + "\");"); sb.Append("\r\n NpgsqlParameter[] parameters = {"); sb.Append("\r\n new NpgsqlParameter(\"@"+ keyColName + "\", NpgsqlDbType." + GetNpgsqlType(keyTypeName) + ")};"); sb.Append("\r\n parameters[0].Value = " + keyColName + ";"); sb.Append("\r\n Model." + table_name + " model = new Model." + table_name + "();"); sb.Append("\r\n" + @" DataSet ds = DbHelperPostgreSQL.Query(strSql.ToString(), parameters); if (ds.Tables[0].Rows.Count > 0) {"); for (int i = 0; i < ds.Tables["t_cols"].Rows.Count; i++) { sb.Append("\r\n if (ds.Tables[0].Rows[0][\"" + ds.Tables["t_cols"].Rows[i]["attname"] + "\"].ToString() != \"\")"); sb.Append("\r\n {"); sb.Append("\r\n model." + ds.Tables["t_cols"].Rows[i]["attname"] + " = Convert." + GetConvertType(ds.Tables["t_cols"].Rows[i]["typname"].ToString()) + "( ds.Tables[0].Rows[0][\"" + ds.Tables["t_cols"].Rows[i]["attname"] + "\"].ToString());"); sb.Append("\r\n }"); } sb.Append("\r\n" + @" return model; } else { return null; } } /// <summary> /// 获得数据列表 /// </summary> public static DataSet GetList(string strWhere) { StringBuilder strSql = new StringBuilder(); "); sb.Append("\r\n strSql.Append(\"select " + colNames + " \");"); sb.Append("\r\n strSql.Append(\" FROM " + table_name + "\");"); sb.Append("\r\n" + " if (strWhere.Trim() != \"\")"); sb.Append("\r\n {"); sb.Append("\r\n strSql.Append(\" where \" + strWhere);"); sb.Append("\r\n" + @" } return DbHelperPostgreSQL.Query(strSql.ToString()); } /// <summary> /// 获得数据列表 /// </summary> public static DataSet GetAllList() { StringBuilder strSql = new StringBuilder(); "); sb.Append("\r\n strSql.Append(\"select " + colNames + " \");"); sb.Append("\r\n strSql.Append(\" FROM " + table_name + "\");"); sb.Append("\r\n" + @" return DbHelperPostgreSQL.Query(strSql.ToString()); } "); sb.Append(@" /// <summary> /// 分页获取数据列表 /// </summary> public static DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex) { StringBuilder strSql = new StringBuilder();" ); sb.Append("\r\n strSql.Append(\"select " + coltNames + " from ( \");"); sb.Append("\r\n strSql.Append(\" select ROW_NUMBER() OVER (\");"); sb.Append("\r\n if (!string.IsNullOrEmpty(orderby.Trim()))"); sb.Append("\r\n {"); sb.Append("\r\n strSql.Append(\"order by T.\" + orderby);"); sb.Append("\r\n }"); sb.Append("\r\n strSql.Append(\")AS R, T.* from "+ table_name + " T \");"); sb.Append("\r\n if (!string.IsNullOrEmpty(strWhere.Trim()))"); sb.Append("\r\n {"); sb.Append("\r\n strSql.Append(\" where \" + strWhere);"); sb.Append("\r\n }"); sb.Append("\r\n strSql.Append(\" ) TT\");"); sb.Append("\r\n strSql.AppendFormat(\" where TT.R between {0} and {1}\", startIndex, endIndex);"); sb.Append("\r\n" + @" return DbHelperPostgreSQL.Query(strSql.ToString()); } "); sb.Append(@" /// <summary> /// 获取记录总数 /// </summary> public static int GetRecordCount(string strWhere) { StringBuilder strSql = new StringBuilder();" ); sb.Append("\r\n strSql.Append(\"select count(1) FROM "+ table_name + " \");"); sb.Append("\r\n if(strWhere.Trim() != \"\")"); sb.Append("\r\n {"); sb.Append("\r\n strSql.Append(\" where \"+strWhere);"); sb.Append("\r\n }"); sb.Append("\r\n" + @" object obj = DbHelperPostgreSQL.GetSingle(strSql.ToString()); if (obj == null) { return 0; } else { return Convert.ToInt32(obj); } "); sb.Append("\r\n" + @" } } } "); File.WriteAllText(dir.FullName + "/" + table_name + ".cs", sb.ToString()); }