[置顶] 自己写代码生成器之生成Dal层代码(获取数据库所有表名称)

时间：2024-10-21 17:36:20

自己写代码生成器之生成Dal层代码(获取数据库所有表名称)

--得到数据库birthday所有表名称
select name from sysobjects where [type]='U'
--select [TABLE_NAME] from  INFORMATION_SCHEMA.TABLES where [TABLE_TYPE]='BASE TABLE'

--获取列信息，不获取数据
select top 0 * from userInfo

思路：拼接字符串

        /// <summary>

        /// 加载

        /// </summary>

        /// <param name="sender"></param>

        /// <param name="e"></param>

        private void Index_Load(object sender, EventArgs e)

        {

            string sql = "select [TABLE_NAME] from  INFORMATION_SCHEMA.TABLES where [TABLE_TYPE]='BASE TABLE'";

            DataTable data = SqlHelper.ExeccutDataTable(sql);

            if (data.Rows.Count > 0)

            {

                this.cmb_tableName.Items.Clear();

                foreach (DataRow row in data.Rows)

                {

                    string name = row["TABLE_NAME"].ToString();

                    this.cmb_tableName.Items.Add(name);

                }

                this.cmb_tableName.SelectedIndex = 0;

            }

        }

        /// <summary>

        /// 生成代码

        /// </summary>

        /// <param name="sender"></param>

        /// <param name="e"></param>

        private void btn_generate_Click(object sender, EventArgs e)

        {

            string tableName = this.cmb_tableName.SelectedItem.ToString();

            string sql = "select top 0 * from " + tableName;

            DataTable data = SqlHelper.ExeccutDataTable(sql);

            ///生成Dal层代码

            GenerateDalCode(tableName, data);

        }

        #region Dal

        /// <summary>

        /// 生成Dal层代码

        /// </summary>

        /// <param name="tableName">表名称</param>

        /// <param name="data">数据源</param>

        private void GenerateDalCode(string tableName, DataTable data)

        {

            StringBuilder str = new StringBuilder();

            //引用信息

            str.AppendLine("using System;");

            str.AppendLine("using System.Collections.Generic;");

            str.AppendLine("using System.Data;");

            str.AppendLine("using System.Data.SqlClient;");

            str.AppendLine("using System.Linq;");

            str.AppendLine("using System.Text;");

            str.AppendLine("\r");

            //命名空间

            str.AppendLine("namespace AutoCodeKldder");

            str.AppendLine("{");

            //注释信息

            str.AppendLine("\t/// <summary>");

            str.AppendLine("\t/// " + tableName + "Service");

            str.AppendLine("\t/// <summary>");

            str.AppendLine("\t[Serializable]");

            str.AppendLine("\tpublic class " + tableName + "Service");

            str.AppendLine("\t{");

            //GetModel

            GetModel(tableName, data, str);

            //Add

            Add(tableName, data, str);

            //AddOutPutField

            AddOutPutField(tableName, data, str);

            //Update

            Update(tableName, data, str);

            //Delete

            Delete(tableName, data, str);

            //FullDataTable

            FullDataTable(tableName, data, str);

            //FullDataSet

            FullDataSet(tableName, data, str);

            //FullDataRow

            FullDataRow(tableName, data, str);

            //GetRecordCount

            GetRecordCount(tableName, data, str);

            str.AppendLine("\t}");

            str.AppendLine("}");

            this.txt_dalcode.Text = str.ToString().Trim();

        }

        /// <summary>

        /// 得到一条实体

        /// </summary>

        /// <param name="tableName">表名称</param>

        /// <param name="data">数据源</param>

        /// <param name="str">字符串</param>

        private static void GetModel(string tableName, DataTable data, StringBuilder str)

        {

            //方法一

            str.AppendLine("\t\t#region GetModel");

            //方法注释

            str.AppendLine("\t\t/// <summary>");

            str.AppendLine("\t\t///得到一条" + tableName + "实体");

            str.AppendLine("\t\t/// </summary>");

            str.AppendLine("\t\t/// <param name=\"_row\">DataRow</param>");

            str.AppendLine("\t\t /// <returns>" + tableName + "实体</returns>");

            str.AppendLine("\t\tpublic " + tableName + " GetModel(DataRow _row)");

            str.AppendLine("\t\t{");

            str.AppendLine("\t\t\t" + tableName + " _" + tableName + " = new " + tableName + "();");

            foreach (DataColumn dc in data.Columns)

            {

                //字段名称

                string _name = dc.ColumnName;

                //字段类型

                string _type = dc.DataType.ToString();

                str.AppendLine("\t\t\tif (_row[\"" + _name + "\"] != null)");

                str.AppendLine("\t\t\t{");

                str.AppendLine("\t\t\t\t_" + tableName + "." + _name + " = (" + _type + ")_row[\"" + _name + "\"];");

                str.AppendLine("\t\t\t}");

            }

            str.AppendLine("\t\t\treturn _" + tableName + ";");

            str.AppendLine("\t\t}");

            str.AppendLine("");

            //方法二

            //方法注释

            str.AppendLine("\t\t/// <summary>");

            str.AppendLine("\t\t///得到一条" + tableName + "实体");

            str.AppendLine("\t\t/// </summary>");

            str.AppendLine("\t\t/// <param name=\"sql\">T-sql语句</param>");

            str.AppendLine("\t\t /// <returns>" + tableName + "实体</returns>");

            str.AppendLine("\t\tpublic " + tableName + " GetModel(string sql)");

            str.AppendLine("\t\t{");

            str.AppendLine("\t\t\treturn GetModel(SqlHelper.ExecuteDataRow(sql));");

            str.AppendLine("\t\t}");

            str.AppendLine("\t\t#endregion");

        }

        /// <summary>

        /// 添加

        /// <param name="tableName">表名称</param>

        /// <param name="data">数据源</param>

        /// <param name="str">字符串</param>

        private static void Add(string tableName, DataTable data, StringBuilder str)

        {

            str.AppendLine("");

            str.AppendLine("\t\t#region Add");

            str.AppendLine("\t\t/// <summary>");

            str.AppendLine("\t\t/// 添加");

            str.AppendLine("\t\t/// </summary>");

            str.AppendLine("\t\t/// <param name=\"_" + tableName + "\">" + tableName + "实体</param>");

            str.AppendLine("\t\t/// <returns>是否成功</returns>");

            str.AppendLine("\t\tpublic bool Add(" + tableName + " _" + tableName + ")");

            str.AppendLine("\t\t{");

            //拼接T-sql语句和SqlParameter[]

            string str_sql_1 = "\"insert into [dbo].[userInfo](";

            string str_sql_2 = "\r\n\t\t\t_sql+= \"vlaues(";

            string str_param = "SqlParameter[] _param = {";

            int idx = 0;

            foreach (DataColumn dc in data.Columns)

            {

                string _name = dc.ColumnName;

                string _dh = ",";

                //最后一个字段没有逗号

                if (idx == data.Columns.Count - 1)

                {

                    _dh = "";

                }

                //不是自增长字段

                if (!dc.AutoIncrement)

                {

                    str_sql_1 += "[" + _name + "]" + _dh;

                    str_sql_2 += "@" + _name + _dh;

                    //str_param += "\r\n\t\t\t\t\t\t\tnew SqlParameter(\"@" + _name + "\", System.Data.SqlDbType.Int) { Value = _" + tableName + "." + _name + " }" + _dh;

                    str_param += "\r\n\t\t\t\t\t\t\tnew SqlParameter(\"@" + _name + "\",_userInfo." + _name + ")" + _dh;

                }

                idx++;

            }

            str_sql_1 += ")\";";

            str_sql_2 += ")\";";

            str_param += "\r\n\t\t\t\t\t\t};";

            str.AppendLine("\t\t\tstring _sql = " + str_sql_1 + str_sql_2);

            str.AppendLine("\t\t\t" + str_param);

            str.AppendLine("\t\t\treturn SqlHelper.ExecuteNonQuery(_sql,_param) > 0;");

            str.AppendLine("\t\t}");

            str.AppendLine("\t\t#endregion");

        }

        /// <summary>

        /// 添加并返回指定列

        /// </summary>

        /// <param name="tableName">表名称</param>

        /// <param name="data">数据源</param>

        /// <param name="str">字符串</param>

        private static void AddOutPutField(string tableName, DataTable data, StringBuilder str)

        {

            str.AppendLine("");

            str.AppendLine("\t\t#region AddOutPutField");

            str.AppendLine("\t\t/// <summary>");

            str.AppendLine("\t\t/// 添加并返回指定列");

            str.AppendLine("\t\t/// </summary>");

            str.AppendLine("\t\t/// <param name=\"_" + tableName + "\">" + tableName + "实体</param>");

            str.AppendLine("\t\t/// <param name=\"_field\">指定列</param>");

            str.AppendLine("\t\t/// <returns>指定列</returns>");

            str.AppendLine("\t\tpublic object AddOutPutField(" + tableName + " _" + tableName + ", object _field)");

            str.AppendLine("\t\t{");

            //拼接T-sql语句和SqlParameter[]

            string str_sql_1 = "\"insert into [dbo].[userInfo](";

            string str_sql_2 = "\r\n\t\t\t_sql+= \" output inserted.\" + _field;\r\n\t\t\t_sql+= \" vlaues(";

            string str_param = "SqlParameter[] _param = {";

            int idx = 0;

            foreach (DataColumn dc in data.Columns)

            {

                string _name = dc.ColumnName;

                string _dh = ",";

                //最后一个字段没有逗号

                if (idx == data.Columns.Count - 1)

                {

                    _dh = "";

                }

                //不是自增长字段

                if (!dc.AutoIncrement)

                {

                    str_sql_1 += "[" + _name + "]" + _dh;

                    str_sql_2 += "@" + _name + _dh;

                    str_param += "\r\n\t\t\t\t\t\t\tnew SqlParameter(\"@" + _name + "\",_userInfo." + _name + ")" + _dh;

                }

                idx++;

            }

            str_sql_1 += ")\";";

            str_sql_2 += ")\";";

            str_param += "\r\n\t\t\t\t\t\t};";

            str.AppendLine("\t\t\tstring _sql = " + str_sql_1 + str_sql_2);

            str.AppendLine("\t\t\t" + str_param);

            str.AppendLine("\t\t\treturn SqlHelper.ExecuteScalar(_sql,_param);");

            str.AppendLine("\t\t}");

            str.AppendLine("\t\t#endregion");

        }

        /// <summary>

        /// 根据主键修改

        /// </summary>

        /// <param name="tableName">表名称</param>

        /// <param name="data">数据源</param>

        /// <param name="str">字符串</param>

        private static void Update(string tableName, DataTable data, StringBuilder str)

        {

            str.AppendLine("");

            str.AppendLine("\t\t#region Update");

            str.AppendLine("\t\t/// <summary>");

            str.AppendLine("\t\t/// 根据主键修改");

            str.AppendLine("\t\t/// </summary>");

            str.AppendLine("\t\t/// <param name=\"_" + tableName + "\">" + tableName + "实体</param>");

            str.AppendLine("\t\t/// <returns>是否成功</returns>");

            str.AppendLine("\t\tpublic bool Update(" + tableName + " _" + tableName + ")");

            str.AppendLine("\t\t{");

            //拼接T-sql语句和SqlParameter[]

            string str_sql = "\"update [dbo].[userInfo] set ";

            string str_param = "SqlParameter[] _param = {";

            int idx = 0;

            //标识字段

            string _AutoIncrement = "";

            foreach (DataColumn dc in data.Columns)

            {

                string _name = dc.ColumnName;

                string _dh = ",";

                //最后一个字段没有逗号

                if (idx == data.Columns.Count - 1)

                {

                    _dh = "";

                }

                //不是自增长字段

                if (!dc.AutoIncrement)

                {

                    str_sql += "[" + _name + "]" + "=@" + _name + _dh;

                }

                else

                {

                    _AutoIncrement = dc.ColumnName;

                }

                str_param += "\r\n\t\t\t\t\t\t\tnew SqlParameter(\"@" + _name + "\",_userInfo." + _name + ")" + _dh;

                idx++;

            }

            str_sql += "\";\r\n\t\t\t_sql += \" where " + _AutoIncrement + "=@" + _AutoIncrement + "\";";

            str_param += "\r\n\t\t\t\t\t\t};";

            str.AppendLine("\t\t\tstring _sql = " + str_sql);

            str.AppendLine("\t\t\t" + str_param);

            str.AppendLine("\t\t\treturn SqlHelper.ExecuteNonQuery(_sql, _param) > 0;");

            str.AppendLine("\t\t}");

            str.AppendLine("\t\t#endregion");

        }

        /// <summary>

        /// 根据主键删除

        /// </summary>

        /// <param name="tableName">表名称</param>

        /// <param name="data">数据源</param>

        /// <param name="str">字符串</param>

        private static void Delete(string tableName, DataTable data, StringBuilder str)

        {

            str.AppendLine("");

            str.AppendLine("\t\t#region Delete");

            str.AppendLine("\t\t/// <summary>");

            str.AppendLine("\t\t/// 根据主键删除");

            str.AppendLine("\t\t/// </summary>");

            str.AppendLine("\t\t/// <param name=\"id\">主键</param>");

            str.AppendLine("\t\t/// <returns>是否成功</returns>");

            str.AppendLine("\t\tpublic bool Delete(int id)");

            str.AppendLine("\t\t{");

            str.AppendLine("\t\t\tstring _sql = \"delete from [dbo].[userInfo] where [id]=@id\";");

            str.AppendLine("\t\t\tSqlParameter[] _param = {");

            str.AppendLine("\t\t\t\t\t\t\t\t\t\tnew SqlParameter(\"@id\",id)");

            str.AppendLine("\t\t\t\t\t\t\t\t\t};");

            str.AppendLine("\t\t\treturn SqlHelper.ExecuteNonQuery(_sql, _param) > 0;");

            str.AppendLine("\t\t}");

            str.AppendLine("\t\t#endregion");

        }

        /// <summary>

        /// 填充DataTable

        /// </summary>

        /// <param name="tableName">表名称</param>

        /// <param name="data">数据源</param>

        /// <param name="str">字符串</param>

        private static void FullDataTable(string tableName, DataTable data, StringBuilder str)

        {

            str.AppendLine("");

            str.AppendLine("\t\t#region FullDataTable");

            str.AppendLine("\t\t/// <summary>");

            str.AppendLine("\t\t/// 填充DataTable");

            str.AppendLine("\t\t/// </summary>");

            str.AppendLine("\t\t/// <param name=\"sql\">T-sql语句</param>");

            str.AppendLine("\t\t/// <returns>DataTable</returns>");

            str.AppendLine("\t\tpublic DataTable FullDataTable(string _sql)");

            str.AppendLine("\t\t{");

            str.AppendLine("\t\t\treturn SqlHelper.ExeccutDataTable(_sql);");

            str.AppendLine("\t\t}");

            str.AppendLine("");

            str.AppendLine("\t\t/// <summary>");

            str.AppendLine("\t\t/// 填充DataTable");

            str.AppendLine("\t\t/// </summary>");

            str.AppendLine("\t\t/// <param name=\"sql\">T-sql语句</param>");

            str.AppendLine("\t\t/// <returns>DataTable</returns>");

            str.AppendLine("\t\tpublic DataTable FullDataTable(string _sql, SqlParameter[] _para)");

            str.AppendLine("\t\t{");

            str.AppendLine("\t\t\treturn SqlHelper.ExeccutDataTable(_sql, _para);");

            str.AppendLine("\t\t}");

            str.AppendLine("\t\t#endregion");

        }

        /// <summary>

        /// 填充DataSet

        /// </summary>

        /// <param name="tableName">表名称</param>

        /// <param name="data">数据源</param>

        /// <param name="str">字符串</param>

        private static void FullDataSet(string tableName, DataTable data, StringBuilder str)

        {

            str.AppendLine("");

            str.AppendLine("\t\t#region FullDataSet");

            str.AppendLine("\t\t/// <summary>");

            str.AppendLine("\t\t/// 填充DataSet");

            str.AppendLine("\t\t/// </summary>");

            str.AppendLine("\t\t/// <param name=\"sql\">T-sql语句</param>");

            str.AppendLine("\t\t/// <returns>DataSet</returns>");

            str.AppendLine("\t\tpublic DataSet FullDataSet(string _sql)");

            str.AppendLine("\t\t{");

            str.AppendLine("\t\t\treturn SqlHelper.ExeccutDataSet(_sql);");

            str.AppendLine("\t\t}");

            str.AppendLine("");

            str.AppendLine("\t\t/// <summary>");

            str.AppendLine("\t\t/// 填充DataSet");

            str.AppendLine("\t\t/// </summary>");

            str.AppendLine("\t\t/// <param name=\"sql\">T-sql语句</param>");

            str.AppendLine("\t\t/// <returns>DataSet</returns>");

            str.AppendLine("\t\tpublic DataSet FullDataSet(string _sql, SqlParameter[] _para)");

            str.AppendLine("\t\t{");

            str.AppendLine("\t\t\treturn SqlHelper.ExeccutDataSet(_sql, _para);");

            str.AppendLine("\t\t}");

            str.AppendLine("\t\t#endregion");

        }

        /// <summary>

        /// 填充DataRow

        /// </summary>

        /// <param name="tableName">表名称</param>

        /// <param name="data">数据源</param>

        /// <param name="str">字符串</param>

        private static void FullDataRow(string tableName, DataTable data, StringBuilder str)

        {

            str.AppendLine("");

            str.AppendLine("\t\t#region FullDataRow");

            str.AppendLine("\t\t/// <summary>");

            str.AppendLine("\t\t/// 填充DataRow");

            str.AppendLine("\t\t/// </summary>");

            str.AppendLine("\t\t/// <param name=\"sql\">T-sql语句</param>");

            str.AppendLine("\t\t/// <returns>DataRow</returns>");

            str.AppendLine("\t\tpublic DataRow FullDataRow(string _sql)");

            str.AppendLine("\t\t{");

            str.AppendLine("\t\t\treturn SqlHelper.ExecuteDataRow(_sql);");

            str.AppendLine("\t\t}");

            str.AppendLine("");

            str.AppendLine("\t\t/// <summary>");

            str.AppendLine("\t\t/// 填充DataRow");

            str.AppendLine("\t\t/// </summary>");

            str.AppendLine("\t\t/// <param name=\"sql\">T-sql语句</param>");

            str.AppendLine("\t\t/// <returns>DataRow</returns>");

            str.AppendLine("\t\tpublic DataRow FullDataRow(string _sql, SqlParameter[] _para)");

            str.AppendLine("\t\t{");

            str.AppendLine("\t\t\treturn SqlHelper.ExecuteDataRow(_sql, _para);");

            str.AppendLine("\t\t}");

            str.AppendLine("\t\t#endregion");

        }

        /// <summary>

        /// 返回数据记录条数

        /// </summary>

        /// <param name="tableName">表名称</param>

        /// <param name="data">数据源</param>

        /// <param name="str">字符串</param>

        private static void GetRecordCount(string tableName, DataTable data, StringBuilder str)

        {

            str.AppendLine("");

            str.AppendLine("\t\t#region GetRecordCount");

            str.AppendLine("\t\t/// <summary>");

            str.AppendLine("\t\t/// 返回数据记录条数");

            str.AppendLine("\t\t/// </summary>");

            str.AppendLine("\t\t/// <param name=\"sql\">T-sql语句</param>");

            str.AppendLine("\t\t/// <returns>记录条数</returns>");

            str.AppendLine("\t\tpublic int GetRecordCount(string _sql)");

            str.AppendLine("\t\t{");

            str.AppendLine("\t\t\treturn SqlHelper.GetRecordCount(_sql);");

            str.AppendLine("\t\t}");

            str.AppendLine("");

            str.AppendLine("\t\t/// <summary>");

            str.AppendLine("\t\t/// 返回数据记录条数");

            str.AppendLine("\t\t/// </summary>");

            str.AppendLine("\t\t/// <param name=\"sql\">T-sql语句</param>");

            str.AppendLine("\t\t/// <param name=\"_where\">过滤条件</param>");

            str.AppendLine("\t\t/// <returns>记录条数</returns>");

            str.AppendLine("\t\tpublic int GetRecordCount(string _sql, string _where)");

            str.AppendLine("\t\t{");

            str.AppendLine("\t\t\treturn SqlHelper.GetRecordCount(_sql, _where);");

            str.AppendLine("\t\t}");

            str.AppendLine("\t\t#endregion");

        }

        #endregion
        /// <summary>

        /// 复制代码

        /// </summary>

        /// <param name="sender"></param>

        /// <param name="e"></param>

        private void btn_copy_Click(object sender, EventArgs e)

        {

            Clipboard.Clear();

            if (this.tabControl.SelectedIndex == 0)

            {

                if (string.IsNullOrWhiteSpace(this.txt_modelcode.Text))

                {

                    return;

                }

                else

                {

                    Clipboard.SetText(this.txt_modelcode.Text);

                    MessageBox.Show("成功复制Models代码。");

                }

            }

            else

            {

                if (string.IsNullOrWhiteSpace(this.txt_dalcode.Text))

                {

                    return;

                }

                else

                {

                    Clipboard.SetText(this.txt_dalcode.Text);

                    MessageBox.Show("成功复制Dal代码。");

                }

            }

相关文章