首页 > 代码库 > 自己编写的DAL三层代码生成器

自己编写的DAL三层代码生成器

(1)创建自己的解决方案 目录结构如下:


(2)编写代码:

(要使用数据库 建议创建任意数据库即可)

创建配置文件App.config代码如下:

<?xml version="1.0"?>
<configuration>
  <connectionStrings>
    <add name="connstr" connectionString="Data Source=.; Initial Catalog=HRMSYSDB;User ID=hrmsa;Password=你的数据库密码"/>
  </connectionStrings>
  <appSettings>
    <add key="passwordSalt" value=http://www.mamicode.com/"love?P3@9"/>>MainWindow.xaml代码如下:(在MainWindow.xaml下把Grid里边的代码换一下就好了)

<Grid>
        <TextBox Height="23" HorizontalAlignment="Left" Margin="16,7,0,0" Name="txtConnStr" VerticalAlignment="Top" Width="542" />
        <Button Content="连接" Height="23" HorizontalAlignment="Left" Margin="564,7,0,0" Name="btnConnect" VerticalAlignment="Top" Width="41" Click="btnConnect_Click" />
        <ComboBox Height="23" HorizontalAlignment="Left" Margin="16,36,0,0" Name="cmbTables" VerticalAlignment="Top" Width="210" IsEnabled="False" />
        <Button Content="生成代码" Height="23" HorizontalAlignment="Left" Margin="244,36,0,0" Name="btnGenerateCode" VerticalAlignment="Top" Width="75" IsEnabled="False" Click="btnGenerateCode_Click" />
        <TextBox TextWrapping="Wrap" VerticalScrollBarVisibility="Auto" HorizontalScrollBarVisibility="Auto" Height="483" HorizontalAlignment="Left" Margin="16,66,0,0" Name="txtModelCode" VerticalAlignment="Top" Width="342" IsReadOnly="True" />
        <TextBox TextWrapping="Wrap" VerticalScrollBarVisibility="Auto" HorizontalScrollBarVisibility="Auto" Height="483" HorizontalAlignment="Left" Margin="372,66,0,0" Name="txtDALCode" VerticalAlignment="Top" Width="494" IsReadOnly="True" />
    </Grid>

MainWindow.xaml.cs代码如下:

namespace MyCodeGen //这里要把命名空间改成自己的 也就是自己生成的   只需粘贴<span style="font-family: Arial, Helvetica, sans-serif;">public partial class MainWindow : Window{  下边的内容即可</span>

{
    /// <summary>
    /// MainWindow.xaml 的交互逻辑
    /// </summary>
    public partial class MainWindow : Window
    {
        public MainWindow()
        {
            InitializeComponent();
        }

        private DataTable ExecuteDataTable(string sql)
        {
            using (SqlConnection conn = new SqlConnection(txtConnStr.Text))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    //只获得表的架构信息(列信息)
                    cmd.CommandText = sql;
                    DataSet ds = new DataSet();
                    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                    adapter.FillSchema(ds, SchemaType.Source);//获得表信息必须要写
                    adapter.Fill(ds);
                    return ds.Tables[0];
                }
            }
        }



        private void btnConnect_Click(object sender, RoutedEventArgs e)
        {
            DataTable table;
            try
            {
                table = ExecuteDataTable(@"SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES 
                        WHERE TABLE_TYPE = 'BASE TABLE'");
            }
            catch (SqlException sqlex)
            {
                MessageBox.Show("连接数据库出错!错误消息:" + sqlex.Message);
                return;
            }
            string[] tables = new string[table.Rows.Count];
            for (int i = 0; i < table.Rows.Count; i++)
            {
                DataRow row = table.Rows[i];
                tables[i] = (string)row["TABLE_NAME"];
            }
            cmbTables.ItemsSource = tables;
            cmbTables.IsEnabled = true;
            btnGenerateCode.IsEnabled = true;

            //把连接字符串记录到文件中,避免用户每次都需要输入连接字符串
            //File.WriteAllText("connstr.txt", txtConnStr.Text); //默认保存为自己工作目录下

            //string currenctDir = AppDomain.CurrentDomain.BaseDirectory//获得当前程序的文件夹,最稳定
            //string configFile = currenctDir + "connstr.txt";

            //将连接字符串保存起来
            string configFile = GetConfigFilePath();
            File.WriteAllText(configFile, txtConnStr.Text);

            //除非真的有捕获异常的需要,否则不要try...catch
        }

        //为例避免每次都输入链接数据库字符串 将保存的代码封装到函数中
        private static string GetConfigFilePath()
        {
            string currenctDir = AppDomain.CurrentDomain.BaseDirectory;
            string configFile = System.IO.Path.Combine(currenctDir, "connstr.txt");
            return configFile;
        }

        private void Window_Loaded(object sender, RoutedEventArgs e)
        {
            //在每次加载的时候都要加载  保存在文件夹内的 连接字符串
            string configFile = GetConfigFilePath();
            txtConnStr.Text = File.ReadAllText(configFile);
        }

        private void btnGenerateCode_Click(object sender, RoutedEventArgs e)
        {
            string tablename = (string)cmbTables.SelectedItem;
            if (tablename == null)
            {
                MessageBox.Show("请选择要生成的表");
                return;
            }
            CreateModelCode(tablename);
            CreateDALCode(tablename);
        }

        private void CreateModelCode(string tablename)
        {
            //bool b = true;//bool和System.Boolean是同一个东西
            //System.Boolean b1 = true;//CTS
            //System.String s1 = "";
            //string s2 = "";    

            DataTable table = ExecuteDataTable("select top 0 * from "
                + tablename);
            //在大量的字符串拼接的时候使用stringbuilder()
            StringBuilder sb = new StringBuilder();
            sb.Append("public class ").AppendLine(tablename).AppendLine("{");
            foreach (DataColumn column in table.Columns)
            {
                string columnDataType = GetDataTypeName(column); //判断类型是否可空
                sb.Append("    ").Append("public ").Append(columnDataType).Append(" ")
                    .Append(column.ColumnName).AppendLine("{get;set;}");
            }
            sb.AppendLine("}");
            txtModelCode.Text = sb.ToString();
        }

        //进行可空类型的处理
        private static string GetDataTypeName(DataColumn column)
        {
            //如果列允许为null,并且列在C#中的类型是不可为空的(值类型ValueType)
            if (column.AllowDBNull && column.DataType.IsValueType)
            {
                return column.DataType + "?";
            }
            else
            {
                return column.DataType.ToString();
            }
        }




        //下边是生成DALcode部分
        private void CreateDALCode(string tablename)
        {
            DataTable table = ExecuteDataTable("select top 0 * from "
                + tablename);
            StringBuilder sb = new StringBuilder();
            sb.Append("public class ").Append(tablename).AppendLine("DAL").AppendLine("{");

            //tomodel开始
            sb.Append("    ").Append("private ").Append(tablename)
                .AppendLine(" ToModel(DataRow row)").Append("    ").AppendLine("{");
            sb.Append("        ").Append(tablename).AppendLine(" model = new " + tablename + "();");
            foreach (DataColumn column in table.Columns)
            {
                //无论列是否允许为空,都进行判断DbNull的处理(省事)
                //model.Id = (Guid)SqlHelper.FromDbValue(row["Id"]);
                sb.Append("        ").Append("model.").Append(column.ColumnName).Append("=(")
                    .Append(GetDataTypeName(column)).Append(")SqlHelper.FromDbValue(row[\"")
                   .Append(column.ColumnName).AppendLine("\"]);");
            }
            sb.Append("        ").AppendLine("return model;");
            sb.AppendLine("}");
            //tomodel的结束

            //listall开始
            //public IEnumerable<Department> ListAll()
            sb.Append("public IEnumerable<").Append(table)
                .AppendLine("> ListAll()").AppendLine("{");
            //List<Department> list = new List<Department>();
            sb.Append("    ").Append("List<").Append(tablename).Append("> list=new List<")
                .Append(tablename).AppendLine(">();");
            // DataTable dt = SqlHelper.ExecuteDataTable
            //("select * from T_Department");
            sb.Append("DataTable dt = SqlHelper.ExecuteDataTable(\"")
                .Append("select * from " + tablename).AppendLine("\");");
            sb.AppendLine("foreach (DataRow row in dt.Rows)");
            //Department dept = ToModel(row);
            sb.Append(tablename).AppendLine(" model=ToModel(row);");
            //list.Add(model);
            sb.AppendLine("list.Add(model);}");

            sb.AppendLine("}");
            //listall结束

            //GetById();
            //DeleteById();

            //生成器要求列名必须是Id,类型必须是Guid

            //Insert开始
            //public void Insert(Operator op)
            sb.Append("public void Insert(")
                .Append(tablename).AppendLine(" model){");
            //SqlHelper.ExecuteNonQuery(@"insert into T_Operator(
            sb.Append("SqlHelper.ExecuteNonQuery(@\"")
                .Append("insert into ").Append(tablename).AppendLine("(");
            string[] colNames = GetColumnNames(table);
            sb.AppendLine(string.Join(",", colNames));
            string[] colParamNames = GetParamColumnNames(table);
            sb.Append("values(").AppendLine(string.Join(",", colParamNames));
            sb.AppendLine("}");
            //Insert结束

            sb.AppendLine("}");
            txtDALCode.Text = sb.ToString();
        }

        //以数组形式返回列名
        private static string[] GetColumnNames(DataTable table)
        {
            string[] colnames = new string[table.Columns.Count];
            for (int i = 0; i < table.Columns.Count; i++)
            {
                DataColumn dataCol = table.Columns[i];
                colnames[i] = dataCol.ColumnName;
            }
            return colnames;
        }

        //以数组形式返回@列名
        private static string[] GetParamColumnNames(DataTable table)
        {
            string[] colnames = new string[table.Columns.Count];
            for (int i = 0; i < table.Columns.Count; i++)
            {
                DataColumn dataCol = table.Columns[i];
                colnames[i] = "@" + dataCol.ColumnName;
            }
            return colnames;
        }
    }
}

SqlHelper.cs代码如下:(命名空间里边的代码)

static class SqlHelper
    {
        //app.config文件的继承:

        public static readonly string connstr =
            ConfigurationManager.ConnectionStrings["connstr"].ConnectionString;//别忘了添加引用 System.configuration 然后在解析到<pre name="code" class="csharp"><span style="white-space:pre">	</span>//ConfigurationManager   步骤:右键点击引用--添加引用--.NET--找到<span style="font-family: Arial, Helvetica, sans-serif;">System.configuration 确定即可</span>
public static int ExecuteNonQuery(string sql, params SqlParameter[] parameters) { using (SqlConnection conn = new SqlConnection(connstr)) { conn.Open(); using (SqlCommand cmd = conn.CreateCommand()) { cmd.CommandText = sql; cmd.Parameters.AddRange(parameters); return cmd.ExecuteNonQuery(); } } } public static object ExecuteScalar(string sql, params SqlParameter[] parameters) { using (SqlConnection conn = new SqlConnection(connstr)) { conn.Open(); using (SqlCommand cmd = conn.CreateCommand()) { cmd.CommandText = sql; cmd.Parameters.AddRange(parameters); return cmd.ExecuteScalar(); } } } public static DataTable ExecuteDataTable(string sql, params SqlParameter[] parameters) { using (SqlConnection conn = new SqlConnection(connstr)) { conn.Open(); using (SqlCommand cmd = conn.CreateCommand()) { cmd.CommandText = sql; cmd.Parameters.AddRange(parameters); DataSet dataset = new DataSet(); SqlDataAdapter adapter = new SqlDataAdapter(cmd); adapter.Fill(dataset); return dataset.Tables[0]; } } //try //{ //} //catch //{ // DataTable dt = new DataTable(); // return dt; //} } public static object FromDbValue(object value) { if (value =http://www.mamicode.com/= DBNull.Value) { return null; } else { return value; } } public static object ToDbValue(object value) { if (value == null) { return DBNull.Value; } else"shadow-none p-3 mb-5 bg-light rounded">

好了 全部代码都好了!点击运行效果如下:



另外需要完整项目的请点击:

http://download.csdn.net/detail/u010870518/7837691


如果导入数据库mdf不熟悉的请参考:

http://blog.csdn.net/xlgen157387/article/details/38844315


自己编写的DAL三层代码生成器