黑马程序员C#学习笔记⑩ 简单的代码生成器

---------------------- ASP.Net+Unity开发、.Net培训、期待与您交流！ ----------------------

代码生成器利用既定的规则拼接字符串来生成代码, 规则越多生成器越复杂.

 

创建一个简单的代码生成器:

1 通过连接字符串连接数据库

2 连接后, 列出数据库中的所有表

3 可以生成Model层和DAL层中的代码

 

主要技术:

获取数据库中的系统表Table_Name信息获取表的名称

Select TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'

 

获取列的名称

Select top 0 * from T_Employee

获取列的架构信息

SqlAdapter adapter = new SqlAdapter(cmd);

DataSet ds = new DataSet();

adapter.FillSchema(ds, SchemaType.Source);

adapter.Fill(ds);

DataTable table = ds.Table[0];

这样就可以使用ds.Columns得到表中列的集合以及关于列的各种属性, 比如AllowDBNull.

 

将连接字符串存储在connConfig.txt中, 以便下次打开程序直接读取不用再次输入

File.WriteAllText([FilePath],connStr);

FilePath 使用System.IO.Path.Combine(currentDirectory,"connConfig.txt");进行拼接.

 

代码生成

使用StringBuilder类进行拼接

StringBuilder sb = newStringBuilder();

sb.Append("1").Append("2");

sb.AppendLine("124");

拼接的时候需要注意在数据库中得到的可空类型, 需要进行判断并在类型名后加上?

使用反射column.DataType.IsValueType来判断列名在C#中是否是值类型

既是可空类型又是值类型就要加上?

创建代码

使用StringBuilder类来拼接字符串

string.Join(",", string[])

将string数组各个元素间加上","

拼接成一个字符串.

MainWindow.xaml

<Window x:Class="简单的代码生成器.MainWindow"
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        Title="代码生成器" Height="598.507" Width="800"
        MinHeight="598" MinWidth="800"
        WindowStartupLocation="CenterScreen" Loaded="Window_Loaded_1">
    <Grid>
        <Grid>
            <Grid.RowDefinitions>
                <RowDefinition Height="17*"></RowDefinition>
                <RowDefinition Height="17*"></RowDefinition>
                <RowDefinition Height="251*"></RowDefinition>
            </Grid.RowDefinitions>
            <Grid.ColumnDefinitions>
                <ColumnDefinition></ColumnDefinition>
                <ColumnDefinition></ColumnDefinition>
            </Grid.ColumnDefinitions>
            <TextBlock HorizontalAlignment="Left" Margin="10,10,0,0" TextWrapping="Wrap" Text="数据库地址" VerticalAlignment="Top" Height="16" Width="70"/>
            <TextBox Name="txtConnStr" HorizontalAlignment="Stretch" Grid.ColumnSpan="2" Height="23" Margin="80,10,100,0" TextWrapping="Wrap" Text=""  VerticalAlignment="Top" />
            <Button Name="btnConn" Content="连接" Grid.Column="1" HorizontalAlignment="Right" Margin="0,12,10,0" VerticalAlignment="Top" Width="70" Click="btnConn_Click"/>
            <ComboBox Name="cbTable" IsEnabled="False" HorizontalAlignment="Left" Margin="10,10,0,0" Grid.Row="1" VerticalAlignment="Top" Width="156"/>
            <Button Name="btnGener" IsEnabled="False" Content="生成" HorizontalAlignment="Left" Margin="171,10,0,0" Grid.Row="1" VerticalAlignment="Top" Width="75" Click="btnGener_Click"/>
            <TextBox Name="txtModel" Grid.Row="2" Grid.Column="0" Margin="10"  IsReadOnly="True" ScrollViewer.CanContentScroll="True" VerticalScrollBarVisibility="Auto" HorizontalScrollBarVisibility="Auto"/>
            <TextBox Name="txtDAL" Grid.Row="2" Grid.Column="1" Margin="10" IsReadOnly="True" ScrollViewer.CanContentScroll="True" VerticalScrollBarVisibility="Auto" HorizontalScrollBarVisibility="Auto"></TextBox>
            <TextBlock Name="tb" HorizontalAlignment="Left" Margin="277,13,0,0" Grid.Row="1" TextWrapping="Wrap" Text="" VerticalAlignment="Top"/>

        </Grid>
    </Grid>

</Window>

使用Grid分隔界面, 保证改变窗口大小时, 界面不会变形

SqlHelper.cs将与数据库交互的方法封装在这个类中

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace 简单的代码生成器
{
    public static class SqlHelper
    {

        public static DataTable ExecuteDataSet(string connStr, string sql, params SqlParameter[] sqlParameters)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.Parameters.AddRange(sqlParameters);
                    cmd.CommandText = sql;
                    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                    DataSet dt = new DataSet();
                    adapter.FillSchema(dt, SchemaType.Source);  //可以获取表的架构信息, 如果需要查询列信息则必须得有
                    adapter.Fill(dt);
                    DataTable table = dt.Tables[0];
                    return table;
                }
            }
        }

        //获取数据库中表的名称信息
        public static string[] GetTableNames(string connStr)
        {
            DataTable table=
             ExecuteDataSet(connStr, "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'");
            string[] tableNames = new string[table.Rows.Count];
            for (int i = 0; i < table.Rows.Count; i++)
            {
                DataRow row = table.Rows[i];
                tableNames[i] = (string)row["TABLE_NAME"];
            }
            return tableNames;
        }

        //获取表中的列信息
        public static DataColumn[] GetColumns(string connStr, string tableName)
        {
            string sql = "Select top 0 * from " + tableName;    //拼接获取表中列的信息的字符串
            DataTable table = ExecuteDataSet(connStr, sql);
            DataColumn[] columns = new DataColumn[table.Columns.Count]; //注意是Columns.Count, 不要习惯写成Rows.Count
            for (int i = 0; i < table.Columns.Count; i++)               //同样
            {
                columns[i] = table.Columns[i];
            }
            return columns;
        }
    }
}

MainWindow.xaml.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;

namespace 简单的代码生成器
{
    /// <summary>
    /// MainWindow.xaml 的交互逻辑
    /// </summary>
    public partial class MainWindow : Window
    {
        private string connStr;
        string filePath = System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "connConfig.txt");  //给定文件路径
        public MainWindow()
        {
            InitializeComponent();
        }

        private void btnGener_Click(object sender, RoutedEventArgs e)
        {
            if (cbTable.SelectedItem == null)
            {
                MessageBox.Show("请选择一张表");
                return;
            }
            string tableName = (string)cbTable.SelectedItem;
            
            //根据列信息生成Model和DAL
            txtModel.Text = CreateModel(tableName);
            txtDAL.Text = CreateDAL(tableName);
            tb.Text = "生成成功";
        }

        //生成DAL的字符串, 增删查改和ToModel
        private string CreateDAL(string tableName)
        {
            DataColumn[] columns = SqlHelper.GetColumns(connStr, tableName);   //通过表名获取表的架构信息
            StringBuilder sb = new StringBuilder();
            string DALName = tableName.Remove(0, 2);
            //拼接public class的开头
            sb.Append("public class ").Append(DALName).AppendLine("DAL {");
            
            /*拼接ToModel()方法 
             * private [Model] ToModel(DataRow row)
             * {
             *      [Model] model = new [Model]();
             *      model.Id = (Guid)SqlHepler.FromDbValue(row["Id"]);
             *      model.Name = (string)SqlHelper.FromDbValue(row["Name"]);
             *      ...
             *      return model;
             * }   
             */
            string ModelName = tableName.Remove(0, 2);
            sb.Append("private ").Append(ModelName).AppendLine(" ToModel(DataRow row) {")
                .Append(ModelName).Append(" model = new ").Append(ModelName).AppendLine("();"); 
            foreach (DataColumn column in columns)
            {
                sb.Append("model.").Append(column.ColumnName).Append(" = (")
                    .Append(GetDataType(column)).Append(")SqlHelper.FromDbValue(row[\"")
                    .Append(column.ColumnName).AppendLine("\"]);");
            }
            sb.AppendLine("return model;");
            sb.AppendLine("}");
            //ToModel()结束

            /* 拼接lisall
             * public List<[Model]> ListAll()
             * {
             *      List<[Model]> listModel = new List<[Model]>;
             *      DataTable dt = SqlHelper.ExecuteDataSet("Select * from [tableName]");
             *      foreach(DataRow row in dt.Rows)
             *      {
             *          listModel.Add(ToModel(row));
             *      }
             *      return listModel;
             * }
             */
            sb.Append("public List<").Append(ModelName).AppendLine(">ListALL(){");
            sb.Append("List<").Append(ModelName).Append("> listModel = new List<")
                .Append(ModelName).AppendLine(">();");
            sb.Append("DataTable dt = SqlHelper.ExecuteDataSet(\"Select * from ")
                .Append(tableName).AppendLine("\");");
            sb.AppendLine("foreach(DataRow row in dt.Rows)").AppendLine("{\nlistModel.Add(ToModel(row));")
                .AppendLine("}\nreturn listModel;");
            sb.AppendLine("}"); //ListAll的末尾

            /* 拼接Insert
             * public void Insert([Model] model)
             * {
             *      SqlHelper.ExecuteNonQuery(@"Insert into [tableName]([parameterList])
             *      Values(@parameterList)", new SqlParameter("@parameterList"), model.parameter);
             * }
             */
            sb.Append("public void Insert(").Append(ModelName).AppendLine(" model)")
                .Append("{\nSqlHelper.ExecuteNonQuery(@\"Insert into ").Append(tableName).Append("(");
            string[] colNames = GetColumnNames(columns);
            string columnNames = string.Join(",", colNames);    //逗号分隔的列名
            sb.Append(columnNames).AppendLine(")Values(");
            string[] parNames = GetParamColumnNames(columns);
            string paramNames = string.Join(",", parNames);     //逗号分隔的@+列名
            sb.Append(paramNames).AppendLine(")\",");
            for (int i = 0; i < parNames.Length; i++)
            {
                sb.Append("new SqlParameter(\"").Append(parNames[i])
                    .Append("\", SqlHelper.ToDbValue(model.").Append(colNames[i]).Append("))");
                if (i != parNames.Length - 1)
                {
                    sb.AppendLine(",");
                }
                else
                {
                    sb.AppendLine(");");
                }
            }
            sb.AppendLine("}"); 
            //Insert结束

            //拼接public class的末尾
            sb.AppendLine("}");
 
            return sb.ToString();
        }

        //将列名转成string数组
        private string[] GetColumnNames(DataColumn[] columns)
        {
            string[] columnNames = new string[columns.Length];
            for (int i = 0; i < columns.Length; i++)
            {
                columnNames[i] = columns[i].ColumnName;
            }
            return columnNames;           
        }

        //将列名转成带@字符的string数组
        private string[] GetParamColumnNames(DataColumn[] columns)
        {
            string[] columnNames = new string[columns.Length];
            for (int i = 0; i < columns.Length; i++)
            {
                columnNames[i] = "@"+columns[i].ColumnName;
            }
            return columnNames;
        }

        //生成Model的字符串, 使用StringBuilder
        private string CreateModel(string tableName)
        {
            DataColumn[] columns = SqlHelper.GetColumns(connStr, tableName);   //通过表名获取表的架构信息
            StringBuilder sb = new StringBuilder();
            //拼接public class的开头
            string ModelName = tableName.Remove(0, 2);
            sb.Append("public class ").Append(ModelName).AppendLine(" {");
            //根据列的架构拼接Model中属性
            foreach (DataColumn column in columns)
            {
                sb.Append("public ").Append(GetDataType(column)).Append(" ").Append(column.ColumnName)
                    .AppendLine(" { get; set;}");
            }
            //拼接末尾
            sb.AppendLine("}");

            return sb.ToString();
        }

        //对列的类型进行判断, 是否需要转换成可空类型的形式. 如:int?
        private string GetDataType(DataColumn column)
        {
            string dataType;
            if (column.AllowDBNull && column.DataType.IsValueType)  //该列是否可空, 并且类型是不是值类型
            {
                dataType = column.DataType.ToString() + "?";
            }
            else
            {
                dataType = column.DataType.ToString();
            }
            return dataType;
        }

        private void btnConn_Click(object sender, RoutedEventArgs e)
        {
            if (txtConnStr.Text.Trim().Length <= 0)
            {
                MessageBox.Show("数据库连接字符串不可为空");
                return;
            }
            tb.Text = "连接中";
            connStr = txtConnStr.Text;
            string[] tableNames = SqlHelper.GetTableNames(connStr); //获得数据库中所有表的名称
            cbTable.ItemsSource = tableNames;   //绑定cbTable的数据源
            btnGener.IsEnabled = true;
            cbTable.IsEnabled = true;
            tb.Text = "连接成功";

            //将connStr写入文本文档, 方便下次取用
            
            File.WriteAllText(filePath, connStr);
        }
        

        private void Window_Loaded_1(object sender, RoutedEventArgs e)
        {
            if (File.Exists(filePath))
            {
                connStr = File.ReadAllText(filePath, Encoding.Default);
                txtConnStr.Text = connStr;
            }
            else
            {
                txtConnStr.Text = "";
            }    

        }
    }

}

效果图

---------------------- ASP.Net+Unity开发、 .Net培训、期待与您交流！ ----------------------详细请查看： www.itheima.com