自己写的代码生成器

大二这年学的的.net，开始一直用三层框架在写程序，写那个数据访问层的时候要写sql语句，开始因为玩的是小程序，表中的字段不多，但是过后写的程序比较大一点的时候，表中的字段就多了很多，写sql都写死人，而且还容易出错，想着就来看看能不能自己写个代码生成器，上网查找资料，我自己也完成了一代码生成器，

若文章在表述和代码方面如有不妥之处，欢迎批评指正。留下你的脚印，欢迎评论！希望能互相学习，

1.首先新建一个SqlHelp类，相信很多人也都是这用实现对数据库的访问的吧！！（这个类是和我这个代码生成器配套使用的，当然你想修改代码生成器生成的语句你可以下载我的源码进行修改）

  public class SqlHelp
    {
        // 在配置文件获得连接字符串
        private static string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;

        #region 执行sql语句(update,insert,delete)+ExecuteNonQuery(string sql, params SqlParameter[] paras)
        /// <summary>
        /// 执行sql语句(update,insert,delete)
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="paras">可变参数</param>
        /// <returns>返回影响的行数</returns>
        public static int ExecuteNonQuery(string sql, params SqlParameter[] paras)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                using (SqlCommand command = conn.CreateCommand())
                {
                    command.CommandText = sql;
                    command.Parameters.AddRange(paras);
                    return command.ExecuteNonQuery();
                }
            }
        } 
        #endregion
        #region 执行sql的函数语句(count，sum 等)+ExecuteScalar(string sql, params SqlParameter[] paras)
        /// <summary>
        /// 执行sql的函数语句(count，sum 等)
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="paras">可变参数</param>
        /// <returns>返回函数值</returns>
        public static object ExecuteScalar(string sql, params SqlParameter[] paras)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                using (SqlCommand command = conn.CreateCommand())
                {
                    command.CommandText = sql;
                    command.Parameters.AddRange(paras);
                    return command.ExecuteScalar();
                }
            }
        }
        #endregion
        #region 执行sql的select语句+ExecuteTable(string sql, params SqlParameter[] paras)
        /// <summary>
        /// 执行sql的select语句
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="paras">可变参数</param>
        /// <returns>返回一张数据表</returns>
        public static DataTable ExecuteTable(string sql, params SqlParameter[] paras)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                using (SqlCommand command = conn.CreateCommand())
                {
                    command.CommandText = sql;
                    command.Parameters.AddRange(paras);
                    SqlDataAdapter sda = new SqlDataAdapter(command);
                    DataSet ds = new DataSet();
                    sda.Fill(ds);
                    return ds.Tables[0];
                }
            }
        }
        #endregion
        /// <summary>
        /// 数据库接受的null：当传入的数据位null时,转换成DBNull.Value(只有这样才能插入到数据库)
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public static object toDBNull(object obj)
        {
            if (obj == null)
            {
                return DBNull.Value;
            }
            else
            {
                return obj;
            }
        }
        /// <summary>
        /// 把数据库的null值转换成程序中null值
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public static object toNull(object obj)
        {
            if (obj == DBNull.Value)
            {
                return null;
            }
            else
            {
                return obj;
            }
        }
}

这里我是用wpf来写的代码生成器

 /// <summary>
    /// MainWindow.xaml 的交互逻辑
    /// </summary>
    public partial class MainWindow : Window
    {
        public MainWindow()
        {
            InitializeComponent();
        }
　　　　 //窗体加载事件
　　　　 private void Window_Loaded(object sender, RoutedEventArgs e)
        {
            TablecomboBox.IsEnabled = false;
            btnCreateCode.IsEnabled = false;
            string path = AppDomain.CurrentDomain.BaseDirectory;
            //把每次连接的字符串存储到1.txt
            string Newpath = System.IO.Path.Combine(path, "ConnStr.txt");
            txtConnstr.Text = File.ReadAllText(Newpath);
        }
　　　　　//点击连接按钮
        private void btnConn_Click(object sender, RoutedEventArgs e)
        {
            TablecomboBox.IsEnabled = false;
            btnCreateCode.IsEnabled = false;
            DataTable datatable;
            try
            {
                //获得选择数据库的所有表名
                string sql = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'";
                datatable = ResultDataTable(sql);
            }
            catch (SqlException se)
            {
                MessageBox.Show("数据库连接错误！" + se.Message);
                return;
            }
            TablecomboBox.IsEnabled = true;
            string[] tables = new string[datatable.Rows.Count];
            for (int i = 0; i < datatable.Rows.Count; i++)
            {
                DataRow row = datatable.Rows[i];
                tables[i] = (string)row["TABLE_NAME"];
            }
            //把表名放在下拉框中
            TablecomboBox.ItemsSource = tables;
            TablecomboBox.SelectedIndex = 0;
            btnCreateCode.IsEnabled = true;
            string path = AppDomain.CurrentDomain.BaseDirectory;
            string Newpath = System.IO.Path.Combine(path, "ConnStr.txt");//这样拼接成的路径较为安全;
            File.WriteAllText(Newpath, txtConnstr.Text);//把字符串存到根文件夹下面;

        }
　　　　　//返回数据库表
        public DataTable ResultDataTable(string sql, params SqlParameter[] parameters)
        {
            using (SqlConnection connstr = new SqlConnection(txtConnstr.Text))
            {
                //打开数据库连接
                connstr.Open();
                using (SqlCommand command = connstr.CreateCommand())
                {

                    command.CommandText = sql;
                    command.Parameters.AddRange(parameters);
                    SqlDataAdapter sda = new SqlDataAdapter(command);
                    DataSet ds = new DataSet();
                    //获取表中的全部信息(如填充每个表中的主键和外键等)
                    sda.FillSchema(ds, SchemaType.Source);
                    sda.Fill(ds);
                    return ds.Tables[0];
                }
            }
        }
　　　　　　//生成代码按钮事件
        private void btnCreateCode_Click(object sender, RoutedEventArgs e)
        {

            if (TablecomboBox.SelectedIndex <0)
            {
                MessageBox.Show("请选择一个表");
                return;
            }
            DataColumnCollection columns = Returncolumns(TablecomboBox.SelectedItem.ToString());
            //生成实体类
            CreateModelCode(columns);
            //生成数据访问代码
            CreateDALCode(columns);
            
        }
　　　　　　//生成实体类
        public void CreateModelCode(DataColumnCollection columns)
        {
            StringBuilder sb = new StringBuilder();//用这种方式连接字符串效率更高;
            sb.Append("public class ").Append(TablecomboBox.SelectedItem).AppendLine("\n{");
            foreach (DataColumn column in columns)
            {
                sb.Append("    public ").Append(IsNull(column)).Append(" ").Append(column.ColumnName).Append("{set;get;}\n");
            }
            sb.Append("}");
            txtModel.Text = sb.ToString();

        }
        public string IsNull(DataColumn column)
        {
            //如果列允许为null，并且列在C#中的类型是不可为空的（值类型ValueType）
            if (column.AllowDBNull&&column.DataType.IsValueType)
            {
                return column.DataType + "?";
            }
            else
            {
                return column.DataType.ToString();
            }
        }
　　　　//生成数据访问代码
        public void CreateDALCode(DataColumnCollection columns)
        {
            ///生成代码,这里就生成比较常用的几个方法，你也可以自己添加自己常用的方法
            CreateToModel(columns);
            CreateGetAll(columns);
            CreateInsert(columns);
            CreateUpdate(columns);
            CreateGetById(columns);
        }
        #region 生成 把DataRow 转换成实体类型的方法
        /// <summary>
        /// 生成 把DataRow 转换成实体类型的方法
        /// </summary>
        /// <param name="columns"></param>
        public void CreateToModel(DataColumnCollection columns)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("public static ").Append(TablecomboBox.SelectedItem).Append(" To").Append(TablecomboBox.SelectedItem);
            sb.AppendLine("(DataRow row)\n{").Append("    ").Append(TablecomboBox.SelectedItem);
            sb.Append(" model =new ").AppendLine(TablecomboBox.SelectedItem + "();");
            foreach (DataColumn column in columns)
            {
                sb.Append("    model.").Append(column.ColumnName).Append("=(").Append(column.DataType);
                sb.Append(")SqlHelp.toNull(row[\"" + column.ColumnName + "\"]);\n");
            }
            sb.AppendLine("    return model;");
            sb.AppendLine("}");
            sb.AppendLine("    ");
            txtDAL.Text += "" + sb.ToString();

        } 
        #endregion

        #region 生成 返回所有数据方法
        /// <summary>
        /// 生成 返回所有数据方法
        /// </summary>
        /// <param name="columns"></param>
        public void CreateGetAll(DataColumnCollection columns)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("public static ").Append(TablecomboBox.SelectedItem).AppendLine("[] GetAll() \n{");
            sb.Append("    DataTable datatable= SqlHelp.ExecuteTable(\"select * from ").Append(TablecomboBox.SelectedItem).AppendLine("\");");
            sb.Append("    ").Append(TablecomboBox.SelectedItem).Append("[] ").Append("ArrayModel=new ").
                Append(TablecomboBox.SelectedItem).AppendLine("[datatable.Rows.Count];");
            sb.AppendLine("    for (int i=0; i < datatable.Rows.Count; i++)\n    {").Append("        ArrayModel[i] =").Append("To")
                .Append(TablecomboBox.SelectedItem).AppendLine("(datatable.Rows[i]);");
            sb.AppendLine("    }").AppendLine("    return ArrayModel;");
            sb.AppendLine("}");
            sb.AppendLine("    ");
            txtDAL.Text += sb.ToString();
        } 
        #endregion

        #region 生成数据插入方法
        /// <summary>
        /// 生成数据插入方法
        /// </summary>
        /// <param name="columns"></param>
        public void CreateInsert(DataColumnCollection columns)
        {
            string TableName = TablecomboBox.SelectedItem.ToString();
            StringBuilder sb = new StringBuilder();
            sb.Append("public static int InsertData(").Append(TableName);
            sb.AppendLine(" newData)\n{");
            sb.Append("    int i=SqlHelp.ExecuteNonQuery(@\"insert ").Append(TableName);
            sb.Append("(");
            string columnname = string.Join(",", GetColumnAtName(TableName, false));
            sb.Append(columnname).Append(")\n                     values(");
            string columnAtname = string.Join(",", GetColumnAtName(TableName, true));
            sb.Append(columnAtname);
            sb.Append(")\",");//values结束;
            string ParameterStr = string.Join("                     ,", ResultParameter(TableName));
            sb.Append("\n                     ").Append(ParameterStr);
            sb.Append("    );\n").AppendLine("    return i;").AppendLine("}").AppendLine("    ");//Insert语句结束;
            txtDAL.Text += "" + sb.ToString();
        } 
        #endregion

        #region 生成数据更新方法
        /// <summary>
        /// 生成数据更新方法
        /// </summary>
        /// <param name="columns"></param>
        public void CreateUpdate(DataColumnCollection columns)
        {
            string TableName = TablecomboBox.SelectedItem.ToString();

            if (cbPrimaryKey.Items.Count < 1)
            {
                MessageBox.Show("不存在主键，无法生成UpData代码和Delete代码");
                return;
            }
            StringBuilder sb = new StringBuilder();
            sb.Append("public static int UpDate(").Append(TableName);
            sb.AppendLine(" newData)\n{");
            sb.Append("    int i=SqlHelp.ExecuteNonQuery(@\"update ").Append(TableName).Append(" set ");
            string UpdateStr = string.Join(",", ResultUpdateStr(TableName));
            sb.Append("\n                     ").Append(UpdateStr).Remove(sb.Length - 1, 1).Append(" where ").Append(cbPrimaryKey.SelectedValue).Append("=@").Append(cbPrimaryKey.SelectedValue);
            sb.Append("\",\n                     ");//Sql语句结束;
            string ParameterStr = string.Join("                     ,", ResultParameter(TableName));
            sb.Append(ParameterStr);
            sb.AppendLine("    );").AppendLine("    return i;").AppendLine("}").AppendLine("    ");
            txtDAL.Text += "" + sb.ToString();
            //生成删除方法
            CreateDelete(columns);
        } 
        #endregion

        #region 生成数据删除方法
        /// <summary>
        /// 生成数据删除方法
        /// </summary>
        /// <param name="columns"></param>
        public void CreateDelete(DataColumnCollection columns)
        {
            string TableName = TablecomboBox.SelectedItem.ToString();
            StringBuilder sb = new StringBuilder();
　　　　　　　　//我这里使用的主键Guid类型,所以传递的Guid类型,当然你修改主键类型，你就可以修改下面的字符串Guid为其他类型，后面的CreateGetById也是通过Guid来获取，若想改变类型自行修改代码就可以了
            sb.Append("public static int DeleteById(Guid id)").AppendLine("{");
            sb.Append("    int i = SqlHelp.ExecuteNonQuery(\"delete ").Append(TableName);
            sb.Append(" where ").Append(cbPrimaryKey.SelectedValue).Append("=@Id\",");
            sb.AppendLine("\n                     new SqlParameter(\"@Id\", id));");
            sb.Append("\n     return i;");
            sb.AppendLine("}");
            sb.AppendLine("    ");
            txtDAL.Text += sb.ToString();
        }
        #endregion

        #region 生成 通过SID获取实体类的方法+CreateGetById(DataColumnCollection columns)
        /// <summary>
        /// 生成 通过SID获取实体类的方法
        /// </summary>
        /// <param name="columns"></param>
        public void CreateGetById(DataColumnCollection columns)
        {
            string TableName = TablecomboBox.SelectedItem.ToString();
            StringBuilder sb = new StringBuilder();
            sb.Append("public static ").Append(TableName).Append(" GetById(Guid id)").AppendLine("{");
            sb.Append("    DataTable datatable = SqlHelp.ExecuteTable(\"select * from ").Append(TableName);
            sb.Append(" where ").Append(cbPrimaryKey.SelectedValue).Append("=@Id\",");
            sb.AppendLine("\n                     new SqlParameter(\"@Id\", id));").AppendLine("    if(datatable.Rows.Count<0)\n    {")
                .AppendLine("        return null;");
            sb.AppendLine("    }").AppendLine("    else if(datatable.Rows.Count==1)\n    {").Append("        return To").Append(TableName);
            sb.AppendLine("(datatable.Rows[0]);").AppendLine("    }").AppendLine("    else\n    {").AppendLine("        throw new Exception();");
            sb.AppendLine("    }");
            sb.AppendLine("}");
            sb.AppendLine("    ");
            txtDAL.Text += sb.ToString();
        } 
        #endregion

        #region 返回数据表的数组列名+ GetColumnAtName(string tablename, bool flag)
        /// <summary>
        /// 返回数据表的列名
        /// </summary>
        /// <param name="tablename">数据表名</param>
        /// <param name="flag">为true时就只返回数据表的列名,为true是在就返回 “@”+数据表列名(为了传递可变参数)</param>
        /// <returns>返回值:把数据表列名按数组返回</returns>
        public string[] GetColumnAtName(string tablename, bool flag)
        {
            DataColumnCollection columns = Returncolumns(tablename);
            string[] ColumnName = new string[columns.Count];
            for (int i = 0; i < columns.Count; i++)
            {
                if (flag)
                {
                    ColumnName[i] = "@" + columns[i].ColumnName;
                }
                else
                {
                    ColumnName[i] = columns[i].ColumnName;
                }
            }
            return ColumnName;
        } 
        #endregion

        #region 返回数据表列名+Returncolumns(string tablename)
        /// <summary>
        /// 返回数据表列名
        /// </summary>
        /// <param name="tablename">表名</param>
        /// <returns></returns>
        public DataColumnCollection Returncolumns(string tablename)
        {
            DataTable datatable = ResultDataTable("select top 0 * from " + tablename);
            DataColumnCollection columns = datatable.Columns;
            return columns;
        } 
        #endregion

        #region 返回数据表可变参数字符串 如:new SqlParameter("@Sid",SqlHelp.toDBNull(Model.Sid))+ ResultParameter(string tablename)
        /// <summary>
        /// 返回数据表可变参数字符串 如:new SqlParameter("@Sid",SqlHelp.toDBNull(Model.Sid))
        /// </summary>
        /// <param name="tablename"></param>
        /// <returns></returns>
        public string[] ResultParameter(string tablename)
        {
            DataColumnCollection columns = Returncolumns(tablename);
            string[] ParameterStr = new string[columns.Count];
            for (int i = 0; i < columns.Count; i++)
            {
                ParameterStr[i] = "new SqlParameter(\"@" + columns[i].ColumnName + "\"," + "SqlHelp.toDBNull(newData." + columns[i].ColumnName + "))\n";
            }
            return ParameterStr;
        } 
        #endregion

        #region 返回更新字符串+ResultUpdateStr(string tablename)
        /// <summary>
        /// 返回更新字符串
        /// </summary>
        /// <param name="tablename"></param>
        /// <returns></returns>
        public string[] ResultUpdateStr(string tablename)
        {
            DataColumnCollection columns = Returncolumns(tablename);
            string[] UpdateStr = new string[columns.Count];
            for (int i = 0; i < columns.Count; i++)
            {
                if (cbPrimaryKey.SelectedValue.ToString() != columns[i].ToString())
                {
                    //当检测到主键时;这里返回的更新字符串就不对主键进行赋值,
                    //应该在where后面赋值(这里在CreateUpdata(DataColumnCollection columns)方法中生成了主键字符串) ;
                    UpdateStr[i] = columns[i].ColumnName + "=@" + columns[i].ColumnName;
                }
            }
            //把主键影响UpdateStr数组的Null覆盖掉；
            for (int i = 0; i < UpdateStr.Length; i++)
            {
                if (UpdateStr[i] == null)
                {
                    for (int j = i + 1; j < UpdateStr.Length; j++)
                    {
                        UpdateStr[j - 1] = UpdateStr[j];
                    }
                }
                UpdateStr[UpdateStr.Length - 1] = null;
            }
            return UpdateStr;
        } 
        #endregion

        #region 返回表的主键集合
        /// <summary>
        /// 返回表的主键集合
        /// </summary>
        /// <param name="tablename"></param>
        /// <returns></returns>
        public DataColumn[] GetTablePrimaryKey1(string tablename)
        {
            DataTable datatable = ResultDataTable("select top 0 * from " + tablename);
            DataColumn[] PrimaryKey = datatable.PrimaryKey;
            if (PrimaryKey.Length <= 0)
            {
                return null;
            }
            return PrimaryKey;

        } 
        #endregion

        #region 表改变时发生事件
        /// <summary>
        /// 表改变时发生事件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void TablecomboBox_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {
            txtModel.Text = " ";//清空实体模型层代码
            txtDAL.Text = "";//清空数据访问层代码

            DataColumn[] dc = GetTablePrimaryKey1(TablecomboBox.SelectedValue.ToString());
            List<string> list = new List<string>();
            for (int i = 0; dc != null && i < dc.Length; i++)
            {
                list.Add(dc[i].ToString());
            }
            //加载数据表主键
            cbPrimaryKey.ItemsSource = list;
            cbPrimaryKey.SelectedIndex = 0;
        } 
        #endregion

      
    }

截图：

这里是源码有兴趣的可以下载看看http://pan.baidu.com/s/1o6uRVtw