分享最近抽空写的一个代码生成器，集成EasyDBUtility数据库访问帮助类

一直想写一个自己的代码生成器，但是因为工作事情多，一直搁置下来，最近下决心终于利用下班时间写完了，现在分享给有需要的朋友，代码生成器集成EasyDBUtility数据库访问帮助类，暂时只支持sqlserver数据库，界面如下

部分代码如下

    /// <summary>

    /// 生成sqlserver相关代码

    /// </summary>

    public class SqlServer

    {

        //获取所有数据库名称

        public const string Get_DataBaseName_SQL = "SELECT Name FROM Master..SysDatabases where Name not in('master','model','msdb','tempdb') ORDER BY Name";

        //获取指定数据库中所有表名

        public const string Get_Tables_SQL = "SELECT Name FROM [{0}]..SysObjects Where XType='U' ORDER BY Name";

        //获取指定数据库中所有视图

        public const string Get_Views_SQL = "SELECT Name FROM [{0}]..SysObjects Where XType='V' ORDER BY Name";

        //获取指定数据库中所有存储过程

        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";

        //获取指定列的数据类型

        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>

        /// 生成业务逻辑层代码

        /// </summary>

        /// <param name="tableName">表名</param>

        /// <param name="connectionString">数据库连接字符串</param>

        /// <param name="paramsData">生成参数</param>

        /// <returns></returns>

        public static string CreateBLL(string tableName, string connectionString, ParamsData paramsData)

        {

            string primaryKeyName = string.Empty;//主键名称

            string primaryKeyTypeName = string.Empty;//主键数据类型

            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代码！请设置表的主键或自增列后再重试！");

            }

            string deletePrefixOfTableName = frmMain.GetDeleteThePrefixTheTableName(tableName, paramsData);//生成的类名称

            string parameterName = primaryKeyName.Substring(, ).ToLower() + primaryKeyName.Substring();//方法中传递的主键参数名称

            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'

源码有进行大量注释，考虑了可扩展性，如果新增其他数据库支持，很容易方便加入。如有好的建议，欢迎提出。

V1.3.3源码下载：http://pan.baidu.com/s/1c0pqOFY

更新日志：

2015-6-14：

1.修复生成查询所有数据方法时，BLL层调用错误-ok

2.不将SQL语句作为常量时，Insert方法前注释缩进有问题-ok

3.工厂类命名空间修改为从界面设置，默认使用Factory作为命名空间-ok

4.生成的DAL代码有错误，当表没有设置主键是自增列时，导致参数化sql的参数加上了[]，如 where [ID]=[ID]

5.修复DAL和IDAL层生成查询所有数据方法名称错误问题

2015-7-8

1.生成简单三层时，Bll层using错误using IDAL修改为using DAL
using IDAL;
using Model;

2.修复注释不整齐问题

3.修改当表没有主键和自增列时，提示无法生成IDAL、DAL、BLL层问题

4.新增 DAL层新增一条数据方法的SQL语句不指定列名”功能和“生成实体类时，将表名和列名保存到常量字段加入”功能

2015-7-24

1.修复程序第一次打开时，生成代码层后的复选框没有按照选择的架构模式相应的禁用

2016-7-24

1.修复删除、更新、查询对组合主键的支持，之前版本只提取一个主键列。

2.修复批量删除方法对于字符串类型值的参数化，防止sql注入，之前版本直接拼接值。

3.修复数据库char对应C#char类型的转换，之前版本全部转换为string类型。

4.修复界面设置参数更改为空后，没有恢复到软件默认设置的值问题。

5.新增支持多个配置文件，不同的项目可以加载不同的配置文件。

巴特西

分享最近抽空写的一个代码生成器，集成EasyDBUtility数据库访问帮助类

最新文章

热门文章