分享最近抽空寫的一個代碼生成器，集成EasyDBUtility數(shù)據(jù)庫訪問幫助類
一直想寫一個自己的代碼生成器，但是因為工作事情多，一直擱置下來，最近下決心終于利用下班時間寫完了，現(xiàn)在分享給有需要的朋友，代碼生成器集成EasyDBUtility數(shù)據(jù)庫訪問幫助類，暫時只支持sqlserver數(shù)據(jù)庫，界面如下
部分代碼如下
/// <summary> /// 生成sqlserver相關(guān)代碼 /// </summary> public class SqlServer { //獲取所有數(shù)據(jù)庫名稱 public const string Get_DataBaseName_SQL = "SELECT Name FROM Master..SysDatabases where Name not in('master','model','msdb','tempdb') ORDER BY Name"; //獲取指定數(shù)據(jù)庫中所有表名 public const string Get_Tables_SQL = "SELECT Name FROM [{0}]..SysObjects Where XType='U' ORDER BY Name"; //獲取指定數(shù)據(jù)庫中所有視圖 public const string Get_Views_SQL = "SELECT Name FROM [{0}]..SysObjects Where XType='V' ORDER BY Name"; //獲取指定數(shù)據(jù)庫中所有存儲過程 public const string Get_Procedures_SQL = "SELECT Name FROM [{0}]..SysObjects Where XType='P' ORDER BY Name"; //獲取指定表或視圖中的所有列信息 public const string Get_Columns_SQL = "select a.name,b.name typeName,a.is_identity,a.is_nullable from sys.columns a " + "left join sys.types b on a.user_type_id=b.user_type_id where a.object_id=object_id('{0}') order by column_id"; //獲取指定列的數(shù)據(jù)類型 public const string Get_Type_Name_SQL = "SELECT type_name(user_type_id) as [type_name] FROM sys.columns where [object_id] = object_id('{0}') and [name] = '{1}'"; //獲取指定表所有列的說明 public const string Get_Columns_Description_SQL = "SELECT objname as ColumnName,[value] as ColumnDescription FROM ::fn_listextendedproperty('MS_Description', 'user', 'dbo', 'table', '{0}', 'column', DEFAULT)"; //獲取指定表中主鍵列名 public const string GetPKeys_SQL = "Declare @objectid int;Set @objectid=object_id('{0}');Select col_name(@objectid,colid) " + "From sysobjects as o Inner Join sysindexes as i On i.name=o.name Inner Join sysindexkeys as k On k.indid=i.indid " + "Where o.xtype = 'PK' and parent_obj=@objectid and k.id=@objectid"; /// <summary> /// 生成業(yè)務(wù)邏輯層代碼 /// </summary> /// <param name="tableName">表名</param> /// <param name="connectionString">數(shù)據(jù)庫連接字符串</param> /// <param name="paramsData">生成參數(shù)</param> /// <returns></returns> public static string CreateBLL(string tableName, string connectionString, ParamsData paramsData) { string primaryKeyName = string.Empty;//主鍵名稱 string primaryKeyTypeName = string.Empty;//主鍵數(shù)據(jù)類型 SqlHelper helper = new SqlHelper(connectionString); helper.AutoClose = false; //獲取表的主鍵列名稱 helper.CreateCommand(string.Format(GetPKeys_SQL, tableName)); object primaryKeyObj = helper.ExecuteScalar(); if (primaryKeyObj != null) { primaryKeyName = primaryKeyObj.ToString(); helper.CommandText(string.Format(Get_Type_Name_SQL, tableName, primaryKeyName)); primaryKeyTypeName = helper.ExecuteScalar().ToString(); } else { //獲取表的所有列信息，找出自增列作為主鍵字段 helper.CommandText(string.Format(SqlServer.Get_Columns_SQL, tableName)); IList<ColumnsData> columnList = helper.ExecuteReader<ColumnsData>(); ColumnsData columnsData = (from col in columnList where col.Is_identity == true select col).FirstOrDefault(); if (columnsData != null) { primaryKeyName = columnsData.Name; primaryKeyTypeName = columnsData.TypeName; } } helper.Close(); if (string.IsNullOrEmpty(primaryKeyName) || string.IsNullOrEmpty(primaryKeyTypeName)) { throw new Exception("在表“" + tableName + "”中沒有找到主鍵或自增列，無法生成BLL代碼！請設(shè)置表的主鍵或自增列后再重試！"); } string deletePrefixOfTableName = frmMain.GetDeleteThePrefixTheTableName(tableName, paramsData);//生成的類名稱 string parameterName = primaryKeyName.Substring(0, 1).ToLower() + primaryKeyName.Substring(1);//方法中傳遞的主鍵參數(shù)名稱 StringBuilder bll = new StringBuilder(); bll.Append("using System.Collections.Generic;\r\n"); if (paramsData.Architecture == Architecture.反射工廠模式) bll.Append("using " + paramsData.FactoryNamespace + ";\r\n"); bll.Append("using " + paramsData.IDALNamespace + ";\r\n"); bll.Append("using " + paramsData.ModelNamespace + ";\r\n\r\n"); bll.Append("namespace " + paramsData.BLLNamespace + "\r\n"); bll.Append("{\r\n "); bll.Append("public class " + deletePrefixOfTableName + paramsData.BLLSuffix + "\r\n "); bll.Append("{\r\n "); if (paramsData.Architecture == Architecture.反射工廠模式) { bll.Append("private static readonly I" + deletePrefixOfTableName + " dal = DataAccess.CreateInstance<I" + deletePrefixOfTableName + ">(\"" + deletePrefixOfTableName + paramsData.DALSuffix + "\");\r\n\r\n"); } else if (paramsData.Architecture == Architecture.簡單三層) { bll.Append("private static readonly " + deletePrefixOfTableName + paramsData.DALSuffix + " dal = new " + deletePrefixOfTableName + paramsData.DALSuffix + "();\r\n\r\n"); } //新增方法 if (paramsData.CreateInsertMethod) { bll.Append(" public bool " + paramsData.InsertMethodName + "(" + deletePrefixOfTableName + paramsData.ModelSuffix + " data)\r\n "); bll.Append("{\r\n "); bll.Append("return dal." + paramsData.InsertMethodName + "(data);\r\n "); bll.Append("}\r\n\r\n"); } //刪除 if (paramsData.CreateDeleteMethod) { bll.Append(" public bool " + paramsData.DeleteMethodName + "(" + ChangeToCSharpType(primaryKeyTypeName, false) + " " + parameterName + ")\r\n "); bll.Append("{\r\n "); bll.Append("return dal." + paramsData.DeleteMethodName + "(" + parameterName + ");\r\n "); bll.Append("}\r\n\r\n"); } //批量刪除 if (paramsData.CreateBatchDeleteMethod) { bll.Append(" public bool " + paramsData.BatchDeleteMethodName + "(" + ChangeToCSharpType(primaryKeyTypeName, false) + "[] " + parameterName + ")\r\n "); bll.Append("{\r\n "); bll.Append("return dal." + paramsData.BatchDeleteMethodName + "(" + parameterName + ");\r\n "); bll.Append("}\r\n\r\n"); } //修改 if (paramsData.CreateUpdateMethod) { bll.Append(" public bool " + paramsData.UpdateMethodName + "(" + deletePrefixOfTableName + paramsData.ModelSuffix + " data)\r\n "); bll.Append("{\r\n "); bll.Append("return dal." + paramsData.UpdateMethodName + "(data);\r\n "); bll.Append("}\r\n\r\n"); } //查詢 if (paramsData.CreateSelectMethod) { bll.Append(" public " + deletePrefixOfTableName + paramsData.ModelSuffix + " " + paramsData.SelectMethodName + "(" + ChangeToCSharpType(primaryKeyTypeName, false) + " " + parameterName + ")\r\n "); bll.Append("{\r\n "); bll.Append("return dal." + paramsData.SelectMethodName + "(" + parameterName + ");\r\n "); bll.Append("}\r\n\r\n"); } //查詢所有 if (paramsData.CreateSelectAllMethod) { bll.Append(" public IList<" + deletePrefixOfTableName + paramsData.ModelSuffix + "> " + paramsData.SelectAllMethodName + "()\r\n "); bll.Append("{\r\n "); bll.Append("return dal." + paramsData.SelectAllMethodName + "();\r\n "); bll.Append("}\r\n\r\n"); } bll.Append(" }\r\n"); bll.Append("}"); return bll.ToString(); } }
獲取表所有外鍵sql
SELECT object_name(b.fkeyid) 外鍵表名稱,(SELECT name FROM syscolumns WHERE colid = b.fkey AND id = b.fkeyid) 外鍵列名,
object_name(b.rkeyid) 主鍵表名,(SELECT name FROM syscolumns WHERE colid = b.rkey AND id = b.rkeyid) 主鍵列名
FROM sysobjects a JOIN sysforeignkeys b ON a.id = b.constid JOIN sysobjects c ON a.parent_obj = c.id
WHERE a.xtype = 'F' AND c.xtype = 'U'
源碼有進行大量注釋，考慮了可擴展性，如果新增其他數(shù)據(jù)庫支持，很容易方便加入。如有好的建議，歡迎提出。
V1.3.3源碼下載：http://pan.baidu.com/s/1c0pqOFY
更新日志：
2015-6-14：
1.修復(fù)生成查詢所有數(shù)據(jù)方法時，BLL層調(diào)用錯誤-ok
2.不將SQL語句作為常量時，Insert方法前注釋縮進有問題-ok
3.工廠類命名空間修改為從界面設(shè)置，默認使用Factory作為命名空間-ok
4.生成的DAL代碼有錯誤，當表沒有設(shè)置主鍵是自增列時，導(dǎo)致參數(shù)化sql的參數(shù)加上了[]，如 where [ID]=[ID]
5.修復(fù)DAL和IDAL層生成查詢所有數(shù)據(jù)方法名稱錯誤問題
2015-7-8
1.生成簡單三層時，Bll層using錯誤using IDAL修改為using DAL
using IDAL;
using Model;
2.修復(fù)注釋不整齊問題
3.修改當表沒有主鍵和自增列時，提示無法生成IDAL、DAL、BLL層問題
4.新增 DAL層新增一條數(shù)據(jù)方法的SQL語句不指定列名”功能和“生成實體類時，將表名和列名保存到常量字段加入”功能
2015-7-24
1.修復(fù)程序第一次打開時，生成代碼層后的復(fù)選框沒有按照選擇的架構(gòu)模式相應(yīng)的禁用
2016-7-24
1.修復(fù)刪除、更新、查詢對組合主鍵的支持，之前版本只提取一個主鍵列。
2.修復(fù)批量刪除方法對于字符串類型值的參數(shù)化，防止sql注入，之前版本直接拼接值。
3.修復(fù)數(shù)據(jù)庫char對應(yīng)C#char類型的轉(zhuǎn)換，之前版本全部轉(zhuǎn)換為string類型。
4.修復(fù)界面設(shè)置參數(shù)更改為空后，沒有恢復(fù)到軟件默認設(shè)置的值問題。
5.新增支持多個配置文件，不同的項目可以加載不同的配置文件。
浙公網(wǎng)安備 33010602011771號