c#根据数据库表结构生成model

学校要考核叫做项目，在做项目的过程中在写用来接收数据库数据的model的时候，由于表字段实在太多而浪费一些时间。想到了之前 用sql 查找表字段结构的而萌生出做一个可以通过表的字段结构c#字符串拼接自动生成一个model类

1.首先需要数据库新建存储过程用来查询表字段结构，我之前的博客有写到(用的是sqlserver)：https://blog.csdn.net/weixin_42084199/article/details/95031978

表的字段结构

2.代码部分我使用的c#和wpf制作，先简单搭建一个wpf页面:

<Window x:Class="自动生成Model.MainWindow"
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
        xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
        xmlns:local="clr-namespace:自动生成Model"
        mc:Ignorable="d"
        Title="MainWindow" Height="450" Width="800">
    <Grid>
        <Grid.RowDefinitions>
            <RowDefinition Height="42*"/>
            <RowDefinition Height="42*"/>
            <RowDefinition Height="42*"/>
            <RowDefinition Height="42*"/>
            <RowDefinition Height="42*"/>
            <RowDefinition Height="42*"/>
            <RowDefinition Height="331*"/>
        </Grid.RowDefinitions>
        <Grid.ColumnDefinitions>
            <ColumnDefinition Width="38*"/>
            <ColumnDefinition Width="89*"/>
            <ColumnDefinition Width="269*"/>
        </Grid.ColumnDefinitions>
        <TextBlock Grid.Row="1" Grid.Column="0" VerticalAlignment ="Center" HorizontalAlignment ="Center">数据库地址：</TextBlock>
        <TextBox Grid.Row="1" Grid.Column="1" Margin="0,5" x:Name="TxtDatabaseAddress"></TextBox>

        <TextBlock Grid.Row="2" Grid.Column="0" VerticalAlignment ="Center" HorizontalAlignment ="Center">用户名：</TextBlock>
        <TextBox Grid.Row="2" Grid.Column="1" Margin="0,5" x:Name="TxtUserName"></TextBox>

        <TextBlock Grid.Row="3" Grid.Column="0" VerticalAlignment ="Center" HorizontalAlignment ="Center">密码：</TextBlock>
        <PasswordBox Grid.Row="3" Grid.Column="1" Margin="0,5" x:Name="TxtPassword"></PasswordBox>


        <TextBlock Grid.Row="4" Grid.Column="0" VerticalAlignment ="Center" HorizontalAlignment ="Center">数据库：</TextBlock>
        <TextBox Grid.Row="4" Grid.Column="1" Margin="0,5" x:Name="TxtDatabase"></TextBox>

        <TextBlock Grid.Row="5" Grid.Column="0" VerticalAlignment ="Center" HorizontalAlignment ="Center">自定义表：</TextBlock>
        <TextBox Grid.Row="5" Grid.Column="1" Margin="0,5" x:Name="TxtTable"></TextBox>

        <Button x:Name="btnCreateModel" Content="生成" Grid.Column="1" Margin="10,27,92,181" Grid.Row="6"></Button>
        
        <TextBox Grid.Row="0" Grid.RowSpan="7" Grid.Column="2" TextWrapping="Wrap" AcceptsReturn ="True" x:Name="TxtContent"></TextBox>
    </Grid>
</Window>

效果图：

生成按钮的点击事件是获取文本框的内容然后拼成链接数据的字符串，然后来链接数据执行写好的存储过程获取表结构数据

private void BtnCreateModel_Click(object sender, RoutedEventArgs e)
{            
    string str = string.Format(@"Data Source = {0};Initial Catalog = {1}; User ID = 
    {2};Password={3};",TxtDatabaseAddress.Text.Trim(), TxtDatabase.Text.Trim(), 
    TxtUserName.Text.Trim(), TxtPassword.Password.Trim());
    SqlParameter[] newvalue = new SqlParameter[]
    {
        new SqlParameter("@TableName",SqlDbType.NVarChar)
    };
    newvalue[0].Value = TxtTable.Text.Trim();
    DataTable tableData = QueryDataTable("w", newvalue, str);
    List<M_TableStructure> lstTabInfo = GetModelFromDB<M_TableStructure>(tableData);//DataTab转model方法
    JointStr(lstTabInfo);
}
public DataTable QueryDataTable(String sql, SqlParameter[] param,string strConnect)
{
    DataTable dt = new DataTable();
    //数据库建立连接
    using (SqlConnection conn = new SqlConnection(strConnect))
    {
        onn.Open();
        SqlDataAdapter da = new SqlDataAdapter(sql, conn);
        da.SelectCommand.CommandType = CommandType.StoredProcedure;
        da.SelectCommand.Parameters.AddRange(param);
        //数据库查询
        da.Fill(dt);
        conn.Close();
    }
    return dt;
}

为了方便再写个model：

public class M_TableStructure
{
    public string TabName { get; set; }
    public string TabExplain { get; set; }
    public string FieldName { get; set; }
    public int DataLength { get; set; }
    public string FieldRemarks { get; set; }
    public string DataType { get; set; }
    public string Type { get; set; }
}

c#上网找了个DataTab转model的代码出处:https://www.cnblogs.com/kzwrcom/p/6062896.html

 

3.既然获得数据了想要的数据了就开始字符拼接：

void JointStr(List<M_TableStructure> lstTabInfo)
{
    string text = string.Empty;
    string tabBlank = "    ";
    text = "using System;\n\n";
    text += "namespace " + "ServerModels" + "\n{\n";
    text += tabBlank + "public class " + lstTabInfo[0].TabName + ": M_BaseModel\n" + tabBlank + "{\n";
    foreach (var _item in lstTabInfo)
    {
        if (_item.FieldName.ToLower() != "id")
        {
        string oneValue = _item.FieldName.Substring(0, 1).ToLower();
        string fullValue = oneValue + _item.FieldName.Substring(1);
        string dataType = changetocsharptype(_item.DataType);//根据数据库数据类型转换成c#对应数据类型
        string dataTypeValue = DefaultForType(dataType);//根据c#数据类型获取初始值
        text += tabBlank + tabBlank + "private " + dataType + " _" + fullValue + " = " + dataTypeValue + ";\n";
        }
    }
    foreach (var item in lstTabInfo)
    {
        if (item.FieldName.ToLower() != "id")
        {
            string dataType = string.Empty;
            dataType = changetocsharptype(item.DataType);
            text += tabBlank + tabBlank + "/// <summary>\n" + tabBlank + tabBlank + "/// " + item.FieldRemarks + "\n" + tabBlank + tabBlank + "/// </summary>\n";
            text += tabBlank + tabBlank + "public " + dataType + " " + item.FieldName + "\n";
            string oneValue = item.FieldName.Substring(0, 1).ToLower();
            string fullValue = oneValue + item.FieldName.Substring(1);
            text += tabBlank + tabBlank + "{\n" + tabBlank + tabBlank + tabBlank + "get { return " + " _" + fullValue + "; }\n";
            text += tabBlank + tabBlank + tabBlank + "set { " + " _" + fullValue + " = value; }\n" + tabBlank + tabBlank + "}\n";
         }
     }
     text += tabBlank + "}\n" + "}";
     TxtContent.Text = text;

        }
public static List<T> GetModelFromDB<T>(DataTable dt)
{
    List<T> data = new List<T>();
    foreach (DataRow row in dt.Rows)
    {
        T item = DataRowsConversion<T>(row);
        data.Add(item);
    }
    return data;
}
public static T DataRowsConversion<T>(DataRow dr)
{
    try
     {
         Type temp = typeof(T);
         T obj = Activator.CreateInstance<T>();
         foreach (DataColumn column in dr.Table.Columns)
         {
             foreach (PropertyInfo pro in temp.GetProperties())
             {
                 if (pro.Name.ToLower() == column.ColumnName.ToLower())
                 {
                     if (dr[column.ColumnName] == DBNull.Value)
                     {
                         pro.SetValue(obj, " ", null);
                         break;
                     }
                     else
                     {
                         pro.SetValue(obj, dr[column.ColumnName], null);
                          break;
                     }
                  }
              }
         }
         return obj;
      }
      catch (Exception ex)
      {
          throw new Exception(ex.Message);
      }
}
        //数据库对应类型
        public static string changetocsharptype(string type)
        {
            string reval = string.Empty;
            switch (type.ToLower())
            {
                case "int":
                    reval = "Int32";
                    break;
                case "text":
                    reval = "string";
                    break;
                case "bigint":
                    reval = "Int64";
                    break;
                case "binary":
                    reval = "byte[]";
                    break;
                case "bit":
                    reval = "boolean";
                    break;
                case "char":
                    reval = "string";
                    break;
                case "datetime":
                    reval = "DateTime";
                    break;
                case "decimal":
                    reval = "decimal";
                    break;
                case "float":
                    reval = "double";
                    break;
                case "image":
                    reval = "byte[]";
                    break;
                case "money":
                    reval = "decimal";
                    break;
                case "nchar":
                    reval = "string";
                    break;
                case "ntext":
                    reval = "string";
                    break;
                case "numeric":
                    reval = "decimal";
                    break;
                case "nvarchar":
                    reval = "string";
                    break;
                case "real":
                    reval = "single";
                    break;
                case "smalldatetime":
                    reval = "DateTime";
                    break;
                case "smallint":
                    reval = "Int16";
                    break;
                case "smallmoney":
                    reval = "decimal";
                    break;
                case "timestamp":
                    reval = "DateTime";
                    break;
                case "tinyint":
                    reval = "byte";
                    break;
                case "uniqueidentifier":
                    reval = "guid";
                    break;
                case "varbinary":
                    reval = "byte[]";
                    break;
                case "varchar":
                    reval = "string";
                    break;
                case "variant":
                    reval = "object";
                    break;
                default:
                    reval = "string";
                    break;
            }
            return reval;
        }
        /// <summary>
        /// 各数据类型初始化
        /// </summary>
        /// <param name="type"></param>
        /// <returns></returns>
        public static string DefaultForType(string type)
        {
            string value = string.Empty;
            type = type.ToLower();
            switch (type)
            {
                case "string":
                    value = "string.Empty";
                    break;
                case "int32":
                    value = "-1";
                    break;
                case "int16":
                    value = "-1";
                    break;
                case "int64":
                    value = "-1";
                    break;
                case "object":
                    value = "null";
                    break;
                case "datetime":
                    value = "DateTime.Now";
                    break;
                case "decimal":
                    value = "0.0m";
                    break;
                case "single":
                    value = "-1";
                    break;
                case "byte[]":
                    value = "null";
                    break;
                case "double":
                    value = "-1";
                    break;
                case "boolean":
                    value = "false";
                    break;
                case "byte":
                    value = "0";
                    break;
            }
            return value;
        }

最终效果：