博客

C#语言，根据数据库字段生成实体类（MS SQL Server）

1：获取数据库表信息

        /// <summary>
        /// 根据参数，获取数据表信息
        /// </summary>
        /// <param name="tabname"></param>
        public static DataTable GetDbTableInfo(string tabname,string constr)
        {
            string str = string.Format(@"select 
            sys.columns.column_id as [ColumnId],
            sys.columns.name as [Name],
            sys.types.name as [Type], 
            sys.columns.is_nullable [IsNullable], 
            [IsIdentity]=CONVERT(BIT, (select count(*) from sys.identity_columns where sys.identity_columns.object_id = sys.columns.object_id and sys.columns.column_id = sys.identity_columns.column_id)),
              (select value from sys.extended_properties where sys.extended_properties.major_id = sys.columns.object_id and sys.extended_properties.minor_id = sys.columns.column_id and name='MS_Description') as [Description],
              [IsKey] =CONVERT(bit,(case when sys.columns.name in (select b.column_name
            from information_schema.table_constraints a
            inner join information_schema.constraint_column_usage b
            on a.constraint_name = b.constraint_name
            where a.constraint_type = 'PRIMARY KEY' and a.table_name = '{0}') then 1 else 0 end))
              from sys.columns, sys.views, sys.types where sys.columns.object_id = sys.views.object_id and sys.columns.system_type_id=sys.types.system_type_id and sys.views.name='{0}' and sys.types.name !='sysname' 
            union
            select
            sys.columns.column_id as [ColumnId],
            sys.columns.name as [Name],
            sys.types.name as [Type], 
            sys.columns.is_nullable [IsNullable], 
            [IsIdentity]=CONVERT(BIT, (select count(*) from sys.identity_columns where sys.identity_columns.object_id = sys.columns.object_id and sys.columns.column_id = sys.identity_columns.column_id)),
              (select value from sys.extended_properties where sys.extended_properties.major_id = sys.columns.object_id and sys.extended_properties.minor_id = sys.columns.column_id and name='MS_Description') as [Description],
              [IsKey] =CONVERT(bit,(case when sys.columns.name in (select b.column_name
            from information_schema.table_constraints a
            inner join information_schema.constraint_column_usage b
            on a.constraint_name = b.constraint_name
            where a.constraint_type = 'PRIMARY KEY' and a.table_name = '{0}') then 1 else 0 end))
              from sys.columns, sys.tables, sys.types where sys.columns.object_id = sys.tables.object_id and sys.columns.system_type_id=sys.types.system_type_id and sys.tables.name='{0}' and sys.types.name !='sysname' 
            order by sys.columns.column_id asc",tabname);

            var dt = GetDataTable(str,constr);
            return dt;
        }

2：定义数据库类型与C#类型质检的转换关系

      /// <summary>
        /// 类型转换枚举
        /// </summary>
        protected  Dictionary<string, Type> DbTypeDic { get; } = new Dictionary<string, Type>()
        {
            { "int", typeof(Int32) },
            { "text", typeof(string) },
            { "bigint", typeof(Int64) },
            { "binary", typeof(byte[]) },
            { "bit", typeof(bool) },
            { "char", typeof(string) },
            { "date", typeof(DateTime) },
            { "datetime", typeof(DateTime) },
            { "datetime2", typeof(DateTime) },
            { "decimal", typeof(decimal) },
            { "float", typeof(double) },
            { "image", typeof(byte[]) },
            { "money", typeof(decimal) },
            { "nchar", typeof(string) },
            { "ntext", typeof(string) },
            { "numeric", typeof(decimal) },
            { "nvarchar", typeof(string) },
            { "real", typeof(Single) },
            { "smalldatetime", typeof(DateTime) },
            { "smallint", typeof(Int16) },
            { "smallmoney", typeof(decimal) },
            { "timestamp", typeof(DateTime) },
            { "tinyint", typeof(byte) },
            { "varbinary", typeof(byte[]) },
            { "varchar", typeof(string) },
            { "variant", typeof(object) },
            { "uniqueidentifier", typeof(Guid) },
        };

3：类型转换枚举方法

  /// <summary>
        /// 获取字段类型
        /// </summary>
        /// <param name="dbTypeStr"></param>
        /// <returns></returns>
        public virtual Type DbTypeStr_To_CsharpType(string dbTypeStr)
        {
            string _dbTypeStr = dbTypeStr.ToLower();
            Type type = null;
            if (DbTypeDic.ContainsKey(_dbTypeStr))
                type = DbTypeDic[_dbTypeStr];
            else
                type = typeof(string);

            return type;
        }

4：调用方法。

        private void button1_Click(object sender, EventArgs e)
        {
            string constr = txt_constr.Text.Trim();//数据库链接字符串
            string tbname = txt_tablename.Text.Trim();//要生成的表名
            var dt = DbHelper.GetDbTableInfo(tbname,constr);
            var list = new List<string>();
            for (int i=0;i<dt.Rows.Count;i++)
            {
                var Name = dt.Rows[i]["Name"].ToString();
                var Type = dt.Rows[i]["Type"].ToString();
                var IsNullable = dt.Rows[i]["IsNullable"].ToString();
                var IsIdentity = dt.Rows[i]["IsIdentity"].ToString();
                var Description = dt.Rows[i]["Description"].ToString();
                var IsKey = dt.Rows[i]["IsKey"].ToString();
                Type type = DbTypeStr_To_CsharpType(Type);
                txt_ret.Text += $@"public {type.Name} {Name} {{ get; set; }}"+"\n";
            }
        }