自己写代码生成器之生成Dal层代码(获取数据库所有表名称)
来源：互联网 发布：人民邮电 算法谜题 编辑：程序博客网 时间：2024/04/24 07:18
自己写代码生成器之生成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代码。"); } }- 自己写代码生成器之生成Dal层代码(获取数据库所有表名称)
- 自己写代码生成器之生成Model层(获取数据库所有表名称)
- [C#]一步一步开发自己的自动代码生成工具之五：DAL层模板
- DAL-自动生成数据访问层代码
- DAL层的代码
- 自己写的代码生成器
- 动软代码生成器 修改源码 (2010-3-13 更新 修改了DAL 层生成插件和BLL层代码生成插件 )
- 用.xsd （数据集）生成数据层(DAL)代码
- 尝试着写自己的代码生成器
- Mybatis 生成Oracle 数据库代码生成器
- (1).net c# 一步一步自己写三层代码生成器(主界面及连接数据库界面)
- C# 如何获取本地数据库SQL Server的数据库服务器名称和数据库名称？然后连接，代码怎么写？
- 自己动手写代码生成器
- Mybatis生成器生成代码
- 自己写的.Net(C#)代码自动生成器
- 自己写的.Net(C#)代码自动生成器
- 为自己的C# ORM 写一个代码自动生成器
- 自己写的.Net(C#)代码自动生成器
- 很好的讲解螺旋队列问题
- Python version 2.7 required, which was not found in the registry
- shell编程学习之道：shell按行读取文件的方法
- Android移动view动画问题
- oracle归档日志超出了恢复闪回恢复区（flash_recovery_area）限制的解决方法
- 自己写代码生成器之生成Dal层代码(获取数据库所有表名称)
- python python-eggs 异常解决方法
- 成功人士永不会做的一件事
- 调用堆栈恢复实操
- atoi, _atoi_l, _wtoi, _wtoi_l
- 迪杰斯特拉算法秒杀单源最短路径！！！
- httpclient双向认证
- 关于工作中遇到的诡异之事
- 如何使数据库中大的浮点数正常显示，不变成科学计数法显示
