自己写代码生成器之生成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("
");
//命名空间
str.AppendLine("namespace AutoCodeKldder");
str.AppendLine("{");
//注释信息
str.AppendLine(" /// <summary>");
str.AppendLine(" /// " + tableName + "Service");
str.AppendLine(" /// <summary>");
str.AppendLine(" [Serializable]");
str.AppendLine(" public class " + tableName + "Service");
str.AppendLine(" {");
//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(" }");
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(" #region GetModel");
//方法注释
str.AppendLine(" /// <summary>");
str.AppendLine(" ///得到一条" + tableName + "实体");
str.AppendLine(" /// </summary>");
str.AppendLine(" /// <param name="_row">DataRow</param>");
str.AppendLine(" /// <returns>" + tableName + "实体</returns>");
str.AppendLine(" public " + tableName + " GetModel(DataRow _row)");
str.AppendLine(" {");
str.AppendLine(" " + tableName + " _" + tableName + " = new " + tableName + "();");
foreach (DataColumn dc in data.Columns)
{
//字段名称
string _name = dc.ColumnName;
//字段类型
string _type = dc.DataType.ToString();
str.AppendLine(" if (_row["" + _name + ""] != null)");
str.AppendLine(" {");
str.AppendLine(" _" + tableName + "." + _name + " = (" + _type + ")_row["" + _name + ""];");
str.AppendLine(" }");
}
str.AppendLine(" return _" + tableName + ";");
str.AppendLine(" }");
str.AppendLine("");
//方法二
//方法注释
str.AppendLine(" /// <summary>");
str.AppendLine(" ///得到一条" + tableName + "实体");
str.AppendLine(" /// </summary>");
str.AppendLine(" /// <param name="sql">T-sql语句</param>");
str.AppendLine(" /// <returns>" + tableName + "实体</returns>");
str.AppendLine(" public " + tableName + " GetModel(string sql)");
str.AppendLine(" {");
str.AppendLine(" return GetModel(SqlHelper.ExecuteDataRow(sql));");
str.AppendLine(" }");
str.AppendLine(" #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(" #region Add");
str.AppendLine(" /// <summary>");
str.AppendLine(" /// 添加");
str.AppendLine(" /// </summary>");
str.AppendLine(" /// <param name="_" + tableName + "">" + tableName + "实体</param>");
str.AppendLine(" /// <returns>是否成功</returns>");
str.AppendLine(" public bool Add(" + tableName + " _" + tableName + ")");
str.AppendLine(" {");
//拼接T-sql语句和SqlParameter[]
string str_sql_1 = ""insert into [dbo].[userInfo](";
string str_sql_2 = "
_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 += "
new SqlParameter("@" + _name + "", System.Data.SqlDbType.Int) { Value = _" + tableName + "." + _name + " }" + _dh;
str_param += "
new SqlParameter("@" + _name + "",_userInfo." + _name + ")" + _dh;
}
idx++;
}
str_sql_1 += ")";";
str_sql_2 += ")";";
str_param += "
};";
str.AppendLine(" string _sql = " + str_sql_1 + str_sql_2);
str.AppendLine(" " + str_param);
str.AppendLine(" return SqlHelper.ExecuteNonQuery(_sql,_param) > 0;");
str.AppendLine(" }");
str.AppendLine(" #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(" #region AddOutPutField");
str.AppendLine(" /// <summary>");
str.AppendLine(" /// 添加并返回指定列");
str.AppendLine(" /// </summary>");
str.AppendLine(" /// <param name="_" + tableName + "">" + tableName + "实体</param>");
str.AppendLine(" /// <param name="_field">指定列</param>");
str.AppendLine(" /// <returns>指定列</returns>");
str.AppendLine(" public object AddOutPutField(" + tableName + " _" + tableName + ", object _field)");
str.AppendLine(" {");
//拼接T-sql语句和SqlParameter[]
string str_sql_1 = ""insert into [dbo].[userInfo](";
string str_sql_2 = "
_sql+= " output inserted." + _field;
_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 += "
new SqlParameter("@" + _name + "",_userInfo." + _name + ")" + _dh;
}
idx++;
}
str_sql_1 += ")";";
str_sql_2 += ")";";
str_param += "
};";
str.AppendLine(" string _sql = " + str_sql_1 + str_sql_2);
str.AppendLine(" " + str_param);
str.AppendLine(" return SqlHelper.ExecuteScalar(_sql,_param);");
str.AppendLine(" }");
str.AppendLine(" #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(" #region Update");
str.AppendLine(" /// <summary>");
str.AppendLine(" /// 根据主键修改");
str.AppendLine(" /// </summary>");
str.AppendLine(" /// <param name="_" + tableName + "">" + tableName + "实体</param>");
str.AppendLine(" /// <returns>是否成功</returns>");
str.AppendLine(" public bool Update(" + tableName + " _" + tableName + ")");
str.AppendLine(" {");
//拼接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 += "
new SqlParameter("@" + _name + "",_userInfo." + _name + ")" + _dh;
idx++;
}
str_sql += "";
_sql += " where " + _AutoIncrement + "=@" + _AutoIncrement + "";";
str_param += "
};";
str.AppendLine(" string _sql = " + str_sql);
str.AppendLine(" " + str_param);
str.AppendLine(" return SqlHelper.ExecuteNonQuery(_sql, _param) > 0;");
str.AppendLine(" }");
str.AppendLine(" #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(" #region Delete");
str.AppendLine(" /// <summary>");
str.AppendLine(" /// 根据主键删除");
str.AppendLine(" /// </summary>");
str.AppendLine(" /// <param name="id">主键</param>");
str.AppendLine(" /// <returns>是否成功</returns>");
str.AppendLine(" public bool Delete(int id)");
str.AppendLine(" {");
str.AppendLine(" string _sql = "delete from [dbo].[userInfo] where [id]=@id";");
str.AppendLine(" SqlParameter[] _param = {");
str.AppendLine(" new SqlParameter("@id",id)");
str.AppendLine(" };");
str.AppendLine(" return SqlHelper.ExecuteNonQuery(_sql, _param) > 0;");
str.AppendLine(" }");
str.AppendLine(" #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(" #region FullDataTable");
str.AppendLine(" /// <summary>");
str.AppendLine(" /// 填充DataTable");
str.AppendLine(" /// </summary>");
str.AppendLine(" /// <param name="sql">T-sql语句</param>");
str.AppendLine(" /// <returns>DataTable</returns>");
str.AppendLine(" public DataTable FullDataTable(string _sql)");
str.AppendLine(" {");
str.AppendLine(" return SqlHelper.ExeccutDataTable(_sql);");
str.AppendLine(" }");
str.AppendLine("");
str.AppendLine(" /// <summary>");
str.AppendLine(" /// 填充DataTable");
str.AppendLine(" /// </summary>");
str.AppendLine(" /// <param name="sql">T-sql语句</param>");
str.AppendLine(" /// <returns>DataTable</returns>");
str.AppendLine(" public DataTable FullDataTable(string _sql, SqlParameter[] _para)");
str.AppendLine(" {");
str.AppendLine(" return SqlHelper.ExeccutDataTable(_sql, _para);");
str.AppendLine(" }");
str.AppendLine(" #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(" #region FullDataSet");
str.AppendLine(" /// <summary>");
str.AppendLine(" /// 填充DataSet");
str.AppendLine(" /// </summary>");
str.AppendLine(" /// <param name="sql">T-sql语句</param>");
str.AppendLine(" /// <returns>DataSet</returns>");
str.AppendLine(" public DataSet FullDataSet(string _sql)");
str.AppendLine(" {");
str.AppendLine(" return SqlHelper.ExeccutDataSet(_sql);");
str.AppendLine(" }");
str.AppendLine("");
str.AppendLine(" /// <summary>");
str.AppendLine(" /// 填充DataSet");
str.AppendLine(" /// </summary>");
str.AppendLine(" /// <param name="sql">T-sql语句</param>");
str.AppendLine(" /// <returns>DataSet</returns>");
str.AppendLine(" public DataSet FullDataSet(string _sql, SqlParameter[] _para)");
str.AppendLine(" {");
str.AppendLine(" return SqlHelper.ExeccutDataSet(_sql, _para);");
str.AppendLine(" }");
str.AppendLine(" #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(" #region FullDataRow");
str.AppendLine(" /// <summary>");
str.AppendLine(" /// 填充DataRow");
str.AppendLine(" /// </summary>");
str.AppendLine(" /// <param name="sql">T-sql语句</param>");
str.AppendLine(" /// <returns>DataRow</returns>");
str.AppendLine(" public DataRow FullDataRow(string _sql)");
str.AppendLine(" {");
str.AppendLine(" return SqlHelper.ExecuteDataRow(_sql);");
str.AppendLine(" }");
str.AppendLine("");
str.AppendLine(" /// <summary>");
str.AppendLine(" /// 填充DataRow");
str.AppendLine(" /// </summary>");
str.AppendLine(" /// <param name="sql">T-sql语句</param>");
str.AppendLine(" /// <returns>DataRow</returns>");
str.AppendLine(" public DataRow FullDataRow(string _sql, SqlParameter[] _para)");
str.AppendLine(" {");
str.AppendLine(" return SqlHelper.ExecuteDataRow(_sql, _para);");
str.AppendLine(" }");
str.AppendLine(" #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(" #region GetRecordCount");
str.AppendLine(" /// <summary>");
str.AppendLine(" /// 返回数据记录条数");
str.AppendLine(" /// </summary>");
str.AppendLine(" /// <param name="sql">T-sql语句</param>");
str.AppendLine(" /// <returns>记录条数</returns>");
str.AppendLine(" public int GetRecordCount(string _sql)");
str.AppendLine(" {");
str.AppendLine(" return SqlHelper.GetRecordCount(_sql);");
str.AppendLine(" }");
str.AppendLine("");
str.AppendLine(" /// <summary>");
str.AppendLine(" /// 返回数据记录条数");
str.AppendLine(" /// </summary>");
str.AppendLine(" /// <param name="sql">T-sql语句</param>");
str.AppendLine(" /// <param name="_where">过滤条件</param>");
str.AppendLine(" /// <returns>记录条数</returns>");
str.AppendLine(" public int GetRecordCount(string _sql, string _where)");
str.AppendLine(" {");
str.AppendLine(" return SqlHelper.GetRecordCount(_sql, _where);");
str.AppendLine(" }");
str.AppendLine(" #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代码。");
}
}