新建一个.net项目新建类库，添加EntityBuilder.aspx 文件，用于生成存储过程，实体类，数据层，业务层，生成字段，生成程序

顺序：

1.点visual studio 文件---新建--网站

2.在新弹出的框里选择已安装--模板--visual c# ,在右侧选择asp.net Web 窗体网站，web位置处选择文件系统和文件存储路径。

3.新建类库。点文件---新建---项目

4.在新窗口中左侧选择已安装---模板--Visual C#,在右侧窗体选择类库，填写类库名称、存放位置，解决方案选择添加到解决方案。

5.最后在解决方案里看见的就是这个的结构了。

6.修改web.config文件，添加连接数据库的地址，数据库用户名、密码，数据库名，注销掉会报错的地方。

6.为了封装的高性能，可扩展的最佳做法SqlClient在数据访问层建立一个SQLHelper类，用于管理连接数据库

  1 /// <summary>
  2     /// 在SqlHelper类是为了封装的高性能，可扩展的最佳做法SqlClient的常见用途。
  3     /// </summary>
  4     public static class SqlHelper
  5     {  
  6         //数据库连接字符串
  7         public static readonly string ConnectionString = "server=" + ConfigurationSettings.AppSettings["DBServer"].ToString() + ";uid=" + ConfigurationSettings.AppSettings["DBUserName"].ToString() + ";pwd=" + ConfigurationSettings.AppSettings["DBPassword"].ToString() + ";database=" + ConfigurationSettings.AppSettings["DBName"].ToString();
  8   
  9 
 10         // Hashtable来存储缓存参数
 11         private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());
 12 
 13 
 14         /// <summary>
 15         /// 执行存储过程
 16         /// </summary>
 17         /// <param name="connectionString">数据库连接字符串</param>
 18         /// <param name="cmdType">命令类型</param>
 19         /// <param name="cmdText">存储过程名称</param>
 20         /// <param name="commandParameters">存储过程所有的参数</param>
 21         /// <returns></returns>
 22         public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
 23         {
 24             SqlCommand cmd = new SqlCommand();
 25             int val = 0;
 26             using (SqlConnection conn = new SqlConnection(connectionString))
 27             {
 28                 PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
 29                 val = cmd.ExecuteNonQuery();
 30                 cmd.Parameters.Clear();
 31                 conn.Dispose();
 32                 conn.Close();
 33             }
 34             return val;
 35         }
 36 
 37 
 38         /// <summary>
 39         /// 执行存储过程
 40         /// </summary>
 41         /// <param name="connection">SqlConnection对像</param>
 42         /// <param name="cmdType">命令类型</param>
 43         /// <param name="cmdText">存储过程名称</param>
 44         /// <param name="commandParameters">存储过程所有的参数</param>
 45         /// <returns></returns>
 46         public static int ExecuteNonQuery(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
 47         {
 48             SqlCommand cmd = new SqlCommand();
 49             PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
 50             int val = cmd.ExecuteNonQuery();
 51             cmd.Parameters.Clear();
 52             return val;
 53         }
 54 
 55 
 56         /// <summary>
 57         /// 返回DataSet数据集
 58         /// </summary>
 59         /// <param name="connectionString">数据库连接字符串</param>
 60         /// <param name="cmdType">命令类型</param>
 61         /// <param name="cmdText">存储过程名称</param>
 62         /// <param name="commandParameters">存储过程所有的参数</param>
 63         /// <returns></returns>
 64         public static DataSet ExecuteDataSet(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
 65         {
 66             SqlCommand cmd = new SqlCommand();
 67             DataSet ds = new DataSet();
 68             using (SqlConnection conn = new SqlConnection(connectionString))
 69             {
 70                 PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
 71                 SqlDataAdapter adp = new SqlDataAdapter(cmd);
 72                 adp.Fill(ds);
 73                 cmd.Parameters.Clear();
 74                 conn.Dispose();
 75                 conn.Close();
 76             }
 77             return ds;
 78         }
 79 
 80 
 81         /// <summary>
 82         /// 返回DataSet数据集
 83         /// </summary>
 84         /// <param name="connection">SqlConnection对像</param>
 85         /// <param name="cmdType">命令类型</param>
 86         /// <param name="cmdText">存储过程名称</param>
 87         /// <param name="commandParameters">存储过程所有的参数</param>
 88         /// <returns></returns>
 89         public static DataSet ExecuteDataSet(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
 90         {
 91             SqlCommand cmd = new SqlCommand();
 92 
 93             PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
 94             SqlDataAdapter adp = new SqlDataAdapter(cmd);
 95             DataSet ds = new DataSet();
 96             adp.Fill(ds);
 97             cmd.Parameters.Clear();
 98             return ds;
 99         }
100 
101 
102         /// <summary>
103         /// 
104         /// </summary>
105         /// <param name="trans">SqlTransaction事务对像</param>
106         /// <param name="cmdType">命令类型</param>
107         /// <param name="cmdText">存储过程名称</param>
108         /// <param name="commandParameters">存储过程所有的参数</param>
109         /// <returns></returns>
110         public static DataSet ExecuteDataSet(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
111         {
112             SqlCommand cmd = new SqlCommand();
113             PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
114             DataSet ds = new DataSet();
115             SqlDataAdapter adp = new SqlDataAdapter();
116             adp.Fill(ds);
117             cmd.Parameters.Clear();
118             return ds;
119         }
120 
121 
122         /// <summary>
123         /// 返回DataSet数据集
124         /// </summary>
125         /// <param name="connectionString">数据库连接字符串</param>
126         /// <param name="cmdType">命令类型</param>
127         /// <param name="cmdText">存储过程名称</param>
128         /// <param name="curPage">当前页</param>
129         /// <param name="pageSize">页的大小</param>
130         /// <param name="commandParameters">存储过程所有的参数</param>
131         /// <returns></returns>
132         public static DataSet ExecuteDataSet(string connectionString, CommandType cmdType, string cmdText, int curPage, int pageSize, params SqlParameter[] commandParameters)
133         {
134             SqlCommand cmd = new SqlCommand();
135             DataSet ds = new DataSet();
136             using (SqlConnection conn = new SqlConnection(connectionString))
137             {
138                 PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
139                 SqlDataAdapter adp = new SqlDataAdapter(cmd);
140                 if (curPage < 1)
141                     curPage = 1;
142                 adp.Fill(ds, (curPage - 1) * pageSize, pageSize, "table0");
143                 cmd.Parameters.Clear();
144                 conn.Dispose();
145                 conn.Close();
146             }
147             return ds;
148         }
149 
150 
151         /// <summary>
152         /// 返回DataSet数据集
153         /// </summary>
154         /// <param name="connection">SqlConnection对像</param>
155         /// <param name="cmdType">命令类型</param>
156         /// <param name="cmdText">存储过程名称</param>
157         /// <param name="curPage">当前页</param>
158         /// <param name="pageSize">页的大小</param>
159         /// <param name="commandParameters">存储过程所有的参数</param>
160         /// <returns></returns>
161         public static DataSet ExecuteDataSet(SqlConnection connection, CommandType cmdType, string cmdText, int curPage, int pageSize, params SqlParameter[] commandParameters)
162         {
163             SqlCommand cmd = new SqlCommand();
164 
165             PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
166             SqlDataAdapter adp = new SqlDataAdapter(cmd);
167             DataSet ds = new DataSet();
168             if (curPage < 1)
169                 curPage = 1;
170             adp.Fill(ds, (curPage - 1) * pageSize, pageSize, "table0");
171             cmd.Parameters.Clear();
172             return ds;
173         }
174 
175 
176         /// <summary>
177         /// 
178         /// </summary>
179         /// <param name="trans">SqlTransaction事务对像</param>
180         /// <param name="cmdType">命令类型</param>
181         /// <param name="cmdText">存储过程名称</param>
182         /// <param name="curPage">当前页</param>
183         /// <param name="pageSize">页的大小</param>
184         /// <param name="commandParameters">存储过程所有的参数</param>
185         /// <returns></returns>
186         public static DataSet ExecuteDataSet(SqlTransaction trans, CommandType cmdType, string cmdText, int curPage, int pageSize, params SqlParameter[] commandParameters)
187         {
188             SqlCommand cmd = new SqlCommand();
189             PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
190             DataSet ds = new DataSet();
191             SqlDataAdapter adp = new SqlDataAdapter();
192             if (curPage < 1)
193                 curPage = 1;
194             adp.Fill(ds, (curPage - 1) * pageSize, pageSize, "table0");
195             cmd.Parameters.Clear();
196             return ds;
197         }
198 
199 
200         /// <summary>
201         /// 执行一句（不返回结果集）使用现有的SQL事务
202         /// </summary>
203         /// <param name="trans">SqlTransaction事务对像</param>
204         /// <param name="cmdType">命令类型</param>
205         /// <param name="cmdText">存储过程名称</param>
206         /// <param name="commandParameters">存储过程所有的参数</param>
207         /// <returns></returns>
208         public static int ExecuteNonQuery(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
209         {
210             SqlCommand cmd = new SqlCommand();
211             PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
212             int val = cmd.ExecuteNonQuery();
213             cmd.Parameters.Clear();
214             return val;
215         }
216 
217 
218         /// <summary>
219         /// 获取详细信息
220         /// </summary>
221         /// <param name="connectionString">数据库连接字符串</param>
222         /// <param name="cmdType">命令类型</param>
223         /// <param name="cmdText">存储过程名称</param>
224         /// <param name="commandParameters">存储过程所有的参数</param>
225         /// <returns>SqlDataReader</returns>
226         public static SqlDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
227         {
228             SqlCommand cmd = new SqlCommand();
229             SqlConnection conn = new SqlConnection(connectionString);
230 
231             // we use a try/catch here because if the method throws an exception we want to 
232             // close the connection throw code, because no datareader will exist, hence the 
233             // commandBehaviour.CloseConnection will not work
234             try
235             {
236                 PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
237                 SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
238                 cmd.Parameters.Clear();
239                 return rdr;
240             }
241             catch
242             {
243                 conn.Dispose();
244                 conn.Close();
245                 throw;
246             }
247         }
248 
249 
250         /// <summary>
251         /// 
252         /// </summary>
253         /// <param name="connectionString">数据库连接字符串</param>
254         /// <param name="cmdType">命令类型</param>
255         /// <param name="cmdText">存储过程名称</param>
256         /// <param name="commandParameters">存储过程所有的参数</param>
257         /// <returns>返回第一行、第一列</returns>
258         public static object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
259         {
260             SqlCommand cmd = new SqlCommand();
261             object val = new object();
262             using (SqlConnection connection = new SqlConnection(connectionString))
263             {
264                 PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
265                 val = cmd.ExecuteScalar();
266                 cmd.Parameters.Clear();
267                 connection.Dispose();
268                 connection.Close();
269             }
270             return val;
271         }
272 
273 
274         /// <summary>
275         /// 
276         /// </summary>
277         /// <param name="connection">SqlConnection对像</param>
278         /// <param name="cmdType">命令类型</param>
279         /// <param name="cmdText">存储过程名称</param>
280         /// <param name="commandParameters">存储过程所有的参数</param>
281         /// <returns>返回第一行、第一列</returns>
282         public static object ExecuteScalar(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
283         {
284             SqlCommand cmd = new SqlCommand();
285 
286             PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
287             object val = cmd.ExecuteScalar();
288             cmd.Parameters.Clear();
289             return val;
290         }
291 
292 
293         /// <summary>
294         /// 参数数组添加到缓存中
295         /// </summary>
296         /// <param name="cacheKey">关键参数缓存</param>
297         /// <param name="cmdParms">一个SqlParamters数组缓存</param>
298         public static void CacheParameters(string cacheKey, params SqlParameter[] commandParameters)
299         {
300             parmCache[cacheKey] = commandParameters;
301         }
302 
303 
304         /// <summary>
305         /// 检索缓存的参数
306         /// </summary>
307         /// <param name="cacheKey">主要用于查找参数</param>
308         /// <returns>缓存SqlParamters阵列</returns>
309         public static SqlParameter[] GetCachedParameters(string cacheKey)
310         {
311             SqlParameter[] cachedParms = (SqlParameter[])parmCache[cacheKey];
312 
313             if (cachedParms == null)
314                 return null;
315 
316             SqlParameter[] clonedParms = new SqlParameter[cachedParms.Length];
317 
318             for (int i = 0, j = cachedParms.Length; i < j; i++)
319                 clonedParms[i] = (SqlParameter)((ICloneable)cachedParms[i]).Clone();
320 
321             return clonedParms;
322         }
323 
324 
325         /// <summary>
326         /// 准备一个用于执行命令
327         /// </summary>
328         /// <param name="cmd">SqlCommand 对象</param>
329         /// <param name="conn">SqlConnection 对象</param>
330         /// <param name="trans">SqlTransaction 对象</param>
331         /// <param name="cmdType">命令类型</param>
332         /// <param name="cmdText">存储过程名称</param>
333         /// <param name="cmdParms">存储过程所有的参数</param>
334         private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
335         {
336             if (conn.State != ConnectionState.Open)
337                 conn.Open();
338 
339             cmd.Connection = conn;
340             cmd.CommandText = cmdText;
341 
342             if (trans != null)
343                 cmd.Transaction = trans;
344 
345             cmd.CommandType = cmdType;
346 
347             //后修改的
348             if (cmdParms != null)
349             {
350                 cmd.Parameters.AddRange(cmdParms);
351             }
352 
353             //之前的单个值相加
354             ////if (cmdParms != null)
355             ////{
356             ////    foreach (SqlParameter parm in cmdParms)
357             ////        cmd.Parameters.Add(parm);
358             ////}
359         }
360     }

6.在Webui层添加一个tools文件夹，添加EntityBuilder.aspx 文件，用于生成存储过程，实体类，数据层，业务层，生成字段，生成程序。

EntityBuilder.aspx文件内容

<head runat="server">
<title>Notfind程式生成工具</title>
</head>
<body>
<form id="form1" runat="server">
<div>
数据表名:<asp:TextBox ID="DB_TableName" runat="server"></asp:TextBox>&nbsp; 模块名称:<asp:TextBox ID="Entity_Title" runat="server"></asp:TextBox><br /><asp:Button ID="Button1" runat="server" Text="生成存储过程" OnClick="Button1_Click" />
<asp:Button ID="DB_Submit" runat="server" Text="生成实体类" OnClick="DB_Submit_Click" />
<asp:Button ID="Entity_Data" runat="server" Text="生成数据层" OnClick="Entity_Data_Click" />
<asp:Button ID="Button2" runat="server" Text="生成业务层" OnClick="Button2_Click" />
<asp:Button ID="Button3" runat="server" Text="生成字段" OnClick="Button21_Click" />
<asp:Button ID="Button4" runat="server" Text="生成程序" OnClick="DB_Submit5_Click" /><br />
[注]本程序仅应用于NotFind架构的系统上,如生成存储过程请确保您的第一个字段为SN.<br />
生成结果为程序自动生成,请在使用前人工检测,以免发生程式错误.
<br />
<br />
<br />
生成结果<br />
<asp:TextBox ID="Entity_Result" runat="server" Height="641px" TextMode="MultiLine"
Width="1133px"></asp:TextBox></div>
</form>
</body>

EntityBuilder.aspx.cs文件内容

  1   protected void DB_Submit_Click(object sender, EventArgs e)
  2     {
  3         StringBuilder result = new StringBuilder();
  4         String TableName = this.DB_TableName.Text;
  5         DataTable TableList = MethodLibrary.ExecuteDIYSql("select top 0 * from " + TableName);
  6         String title = this.Entity_Title.Text + "Info";
  7         result.Append("public class " + title + "\n");
  8         result.Append("{\n");
  9         for (int k = 0; k < TableList.Columns.Count; k++)
 10         {
 11             result.Append("private " + TableList.Columns[k].DataType.ToString().Replace("System.", "") + " _" + TableList.Columns[k].ColumnName + " " + TableList.Columns[k].DataType.ToString().Replace("System.", "").Replace("Int32", "= 0").Replace("Decimal", "= 0").Replace("Boolean", "= false").Replace("String", "= \"\"").Replace("DateTime", "= DateTime.Now") + ";//\n");
 12         }
 13         result.Append("\npublic " + title + "()\n");
 14         result.Append("{\n");
 15         for (int k = 0; k < TableList.Columns.Count; k++)
 16         {
 17             result.Append("this._" + TableList.Columns[k].ColumnName + " = " + TableList.Columns[k].ColumnName + ";\n");
 18         }
 19         result.Append("}\n");
 20         for (int k = 0; k < TableList.Columns.Count; k++)
 21         {
 22             result.Append("/// <summary>\n");
 23             result.Append("/// \n");
 24             result.Append("/// </summary>\n");
 25             result.Append("public " + TableList.Columns[k].DataType.ToString().Replace("System.", "") + " " + TableList.Columns[k].ColumnName + "\n");
 26             result.Append("{\n");
 27             result.Append("get { return this._" + TableList.Columns[k].ColumnName + "; }\n");
 28             result.Append("set { this._" + TableList.Columns[k].ColumnName + " = value; }\n");
 29             result.Append("}\n");
 30         }
 31         result.Append("}\n");
 32 
 33         this.Entity_Result.Text = result.ToString();
 34 
 35     }
 36 
 37     protected void Button1_Click(object sender, EventArgs e)
 38     {
 39         StringBuilder result = new StringBuilder();
 40         String TableName = this.DB_TableName.Text;
 41         DataTable TableList = MethodLibrary.ExecuteDIYSql("select column_name,data_type,character_maximum_length from information_schema.columns where table_name = '" + TableName + "'");
 42 
 43         #region 添加
 44         result.Append("/*\n");
 45         result.Append("*Name: [" + TableName + "_Add]\n");
 46         result.Append("*Description: 添加\n");
 47         result.Append("*Author: C#\n");
 48         result.Append("*/\n");
 49         result.Append("IF EXISTS (SELECT NAME FROM sysobjects WHERE id = OBJECT_ID(N'[" + TableName + "_Add]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)\n");
 50         result.Append("DROP PROCEDURE [" + TableName + "_Add]\n");
 51         result.Append("GO\n");
 52         result.Append("CREATE procedure [" + TableName + "_Add]\n");
 53         for (int k = 1; k < TableList.Rows.Count; k++)
 54         {
 55             if (k + 1 == TableList.Rows.Count)
 56             {
 57                 if (TableList.Rows[k]["character_maximum_length"].ToString() == "")
 58                 {
 59                     result.Append("@" + TableList.Rows[k]["column_name"].ToString() + " " + TableList.Rows[k]["data_type"].ToString() + "\n");
 60                 }
 61                 else if (TableList.Rows[k]["character_maximum_length"].ToString() == "1073741823")
 62                 {
 63                     result.Append("@" + TableList.Rows[k]["column_name"].ToString() + " " + TableList.Rows[k]["data_type"].ToString() + "\n");
 64                 }
 65                 else
 66                 {
 67                     result.Append("@" + TableList.Rows[k]["column_name"].ToString() + " " + TableList.Rows[k]["data_type"].ToString() + "(" + TableList.Rows[k]["character_maximum_length"].ToString() + ")\n");
 68                 }
 69             }
 70             else
 71             {
 72                 if (TableList.Rows[k]["character_maximum_length"].ToString() == "")
 73                 {
 74                     result.Append("@" + TableList.Rows[k]["column_name"].ToString() + " " + TableList.Rows[k]["data_type"].ToString() + ",\n");
 75                 }
 76                 else if (TableList.Rows[k]["character_maximum_length"].ToString() == "1073741823")
 77                 {
 78                     result.Append("@" + TableList.Rows[k]["column_name"].ToString() + " " + TableList.Rows[k]["data_type"].ToString() + ",\n");
 79                 }
 80                 else
 81                 {
 82                     result.Append("@" + TableList.Rows[k]["column_name"].ToString() + " " + TableList.Rows[k]["data_type"].ToString() + "(" + TableList.Rows[k]["character_maximum_length"].ToString() + "),\n");
 83                 }
 84             }
 85         }
 86         result.Append("AS\n");
 87         result.Append("Insert into " + TableName + "\n");
 88         result.Append("(\n");
 89         for (int k = 1; k < TableList.Rows.Count; k++)
 90         {
 91             if (k + 1 == TableList.Rows.Count)
 92             {
 93                 result.Append(TableList.Rows[k]["column_name"].ToString() + "\n");
 94             }
 95             else
 96             {
 97                 result.Append(TableList.Rows[k]["Column_Name"].ToString() + ",\n");
 98             }
 99         }
100         result.Append(")\n");
101         result.Append("Values\n");
102         result.Append("(\n");
103         for (int k = 1; k < TableList.Rows.Count; k++)
104         {
105             if (k + 1 == TableList.Rows.Count)
106             {
107                 result.Append("@" + TableList.Rows[k]["Column_Name"].ToString() + "\n");
108             }
109             else
110             {
111                 result.Append("@" + TableList.Rows[k]["Column_Name"].ToString() + ",\n");
112             }
113         }
114         result.Append(")\n");
115         result.Append("\n");
116         result.Append("\n");
117         result.Append("\n");
118         result.Append("\n");
119         #endregion
120 
121         #region 删除
122         result.Append("/*\n");
123         result.Append("*Name: [" + TableName + "_Delete]\n");
124         result.Append("*Description: 删除\n");
125         result.Append("*Author: C#\n");
126         result.Append("*/\n");
127         result.Append("IF EXISTS (SELECT NAME FROM sysobjects WHERE id = OBJECT_ID(N'[" + TableName + "_Delete]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)\n");
128         result.Append("DROP PROCEDURE [" + TableName + "_Delete]\n");
129         result.Append("GO\n");
130         result.Append("CREATE procedure [" + TableName + "_Delete]\n");
131         result.Append("@" + TableList.Rows[0]["Column_Name"].ToString() + " " + TableList.Rows[0]["data_type"].ToString() + "\n");
132         result.Append("AS\n");
133         result.Append("Delete from " + TableName + " where " + TableList.Rows[0]["Column_Name"].ToString() + "=@" + TableList.Rows[0]["Column_Name"].ToString() + "\n");
134         result.Append("\n");
135         result.Append("\n");
136         result.Append("\n");
137         result.Append("\n");
138         #endregion
139 
140         #region 获取
141         result.Append("/*\n");
142         result.Append("*Name: [" + TableName + "_Get]\n");
143         result.Append("*Description: 获取\n");
144         result.Append("*Author: C#\n");
145         result.Append("*/\n");
146         result.Append("IF EXISTS (SELECT NAME FROM sysobjects WHERE id = OBJECT_ID(N'[" + TableName + "_Get]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)\n");
147         result.Append("DROP PROCEDURE [" + TableName + "_Get]\n");
148         result.Append("GO\n");
149         result.Append("CREATE procedure [" + TableName + "_Get]\n");
150         result.Append("@" + TableList.Rows[0]["Column_Name"].ToString() + " " + TableList.Rows[0]["data_type"].ToString() + "\n");
151         result.Append("AS\n");
152         result.Append("select top 1 * from " + TableName + " where " + TableList.Rows[0]["Column_Name"].ToString() + "=@" + TableList.Rows[0]["Column_Name"].ToString() + "\n");
153         result.Append("\n");
154         result.Append("\n");
155         result.Append("\n");
156         result.Append("\n");
157         #endregion
158 
159         #region 列表
160         result.Append("/*\n");
161         result.Append("*Name: [" + TableName + "_List]\n");
162         result.Append("*Description: 列表\n");
163         result.Append("*Author: C#\n");
164         result.Append("*/\n");
165         result.Append("IF EXISTS (SELECT NAME FROM sysobjects WHERE id = OBJECT_ID(N'[" + TableName + "_List]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)\n");
166         result.Append("DROP PROCEDURE [" + TableName + "_List]\n");
167         result.Append("GO\n");
168         result.Append("CREATE procedure [" + TableName + "_List]\n");
169         result.Append("AS\n");
170         result.Append("select * from " + TableName + "\n");
171         result.Append("\n");
172         result.Append("\n");
173         result.Append("\n");
174         result.Append("\n");
175         #endregion
176 
177         #region 设置
178         result.Append("/*\n");
179         result.Append("*Name: [" + TableName + "_Set]\n");
180         result.Append("*Description: 设置\n");
181         result.Append("*Author: C#\n");
182         result.Append("*/\n");
183         result.Append("IF EXISTS (SELECT NAME FROM sysobjects WHERE id = OBJECT_ID(N'[" + TableName + "_Set]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)\n");
184         result.Append("DROP PROCEDURE [" + TableName + "_Set]\n");
185         result.Append("GO\n");
186         result.Append("CREATE procedure [" + TableName + "_Set]\n");
187         for (int k = 0; k < TableList.Rows.Count; k++)
188         {
189             if (k + 1 == TableList.Rows.Count)
190             {
191                 if (TableList.Rows[k]["character_maximum_length"].ToString() == "")
192                 {
193                     result.Append("@" + TableList.Rows[k]["column_name"].ToString() + " " + TableList.Rows[k]["data_type"].ToString() + "\n");
194                 }
195                 else if (TableList.Rows[k]["character_maximum_length"].ToString() == "1073741823")
196                 {
197                     result.Append("@" + TableList.Rows[k]["column_name"].ToString() + " " + TableList.Rows[k]["data_type"].ToString() + "\n");
198                 }
199                 else
200                 {
201                     result.Append("@" + TableList.Rows[k]["column_name"].ToString() + " " + TableList.Rows[k]["data_type"].ToString() + "(" + TableList.Rows[k]["character_maximum_length"].ToString() + ")\n");
202                 }
203             }
204             else
205             {
206                 if (TableList.Rows[k]["character_maximum_length"].ToString() == "")
207                 {
208                     result.Append("@" + TableList.Rows[k]["column_name"].ToString() + " " + TableList.Rows[k]["data_type"].ToString() + ",\n");
209                 }
210                 else if (TableList.Rows[k]["character_maximum_length"].ToString() == "1073741823")
211                 {
212                     result.Append("@" + TableList.Rows[k]["column_name"].ToString() + " " + TableList.Rows[k]["data_type"].ToString() + ",\n");
213                 }
214                 else
215                 {
216                     result.Append("@" + TableList.Rows[k]["column_name"].ToString() + " " + TableList.Rows[k]["data_type"].ToString() + "(" + TableList.Rows[k]["character_maximum_length"].ToString() + "),\n");
217                 }
218             }
219         }
220         result.Append("AS\n");
221         result.Append("update " + TableName + " Set\n");
222         for (int k = 1; k < TableList.Rows.Count; k++)
223         {
224             if (k + 1 == TableList.Rows.Count)
225             {
226                 result.Append(TableList.Rows[k]["column_name"].ToString() + "=@" + TableList.Rows[k]["column_name"].ToString() + "\n");
227             }
228             else
229             {
230                 result.Append(TableList.Rows[k]["column_name"].ToString() + "=@" + TableList.Rows[k]["column_name"].ToString() + ",\n");
231             }
232         }
233         result.Append("where " + TableList.Rows[0]["column_name"].ToString() + "=@" + TableList.Rows[0]["column_name"].ToString() + "\n");
234         #endregion
235 
236         this.Entity_Result.Text = result.ToString();
237     }
238 
239     protected void Entity_Data_Click(object sender, EventArgs e)
240     {
241         StringBuilder result = new StringBuilder();
242         String TableName = this.DB_TableName.Text;
243         DataTable TableList = MethodLibrary.ExecuteDIYSql("select top 0 * from " + TableName);
244         String title = this.Entity_Title.Text + "Data";
245         result.Append("public class " + title + "\n");
246         result.Append("{\n");
247         result.Append("#region 添加\n");
248         result.Append("/// <summary>\n");
249         result.Append("/// 添加\n");
250         result.Append("/// </summary>\n");
251         result.Append("/// <param name=\"_UserInfo\">实体类</param>\n");
252         result.Append("/// <returns></returns>\n");
253         result.Append("public static Boolean Add(" + this.Entity_Title.Text + "Info _" + this.Entity_Title.Text + "Info)\n");
254         result.Append("{\n");
255         result.Append("SqlParameter[] parm ={\n");
256         for (int t = 1; t < TableList.Columns.Count; t++)
257         {
258             result.Append("                                     new SqlParameter(\"" + TableList.Columns[t].ColumnName + "\",_" + this.Entity_Title.Text + "Info." + TableList.Columns[t].ColumnName + "),\n");
259         }
260         result.Append("                                  };\n");
261         result.Append("SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionString5, CommandType.StoredProcedure, \"" + TableName + "_Add\", parm);\n");
262         result.Append("return true;\n");
263         result.Append("}\n");
264         result.Append("#endregion\n\n");
265 
266 
267         result.Append("#region 删除\n");
268         result.Append("/// <summary>\n");
269         result.Append("/// 删除\n");
270         result.Append("/// </summary>\n");
271         result.Append("/// <param name=\"" + TableList.Columns[0].ColumnName + "\">SN</param>\n");
272         result.Append("/// <returns></returns>\n");
273         result.Append("public static Boolean Delete(" + TableList.Columns[0].DataType.ToString().Replace("System.", "") + " " + TableList.Columns[0].ColumnName + ")\n");
274         result.Append("{\n");
275         result.Append("SqlParameter[] parm ={");
276         result.Append("new SqlParameter(\"" + TableList.Columns[0].ColumnName + "\"," + TableList.Columns[0].ColumnName + "),");
277         result.Append("};\n");
278         result.Append("SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionString5, CommandType.StoredProcedure, \"" + TableName + "_Delete\", parm);\n");
279         result.Append("return true;\n");
280         result.Append("}\n");
281         result.Append("#endregion\n\n");
282 
283 
284         result.Append("#region 获取\n");
285         result.Append("/// <summary>\n");
286         result.Append("/// 获取\n");
287         result.Append("/// </summary>\n");
288         result.Append("/// <param name=\"" + TableList.Columns[0].ColumnName + "\">SN</param>\n");
289         result.Append("/// <returns></returns>\n");
290         result.Append("public static " + this.Entity_Title.Text + "Info Get(" + TableList.Columns[0].DataType.ToString().Replace("System.", "") + " " + TableList.Columns[0].ColumnName + ")\n");
291         result.Append("{\n");
292         result.Append(this.Entity_Title.Text + "Info _" + this.Entity_Title.Text + "Info = new " + this.Entity_Title.Text + "Info();\n");
293         result.Append("SqlParameter[] parm ={");
294         result.Append("new SqlParameter(\"" + TableList.Columns[0].ColumnName + "\"," + TableList.Columns[0].ColumnName + "),");
295         result.Append("};\n");
296         result.Append("using (SqlDataReader _SDR = SqlHelper.ExecuteReader(SqlHelper.ConnectionString5, CommandType.StoredProcedure, \"" + TableName + "_Get\", parm))\n");
297         result.Append("{\n");
298         result.Append("if (_SDR.Read())\n");
299         result.Append("{\n");
300         for (int t = 0; t < TableList.Columns.Count; t++)
301         {
302             result.Append("_" + this.Entity_Title.Text + "Info." + TableList.Columns[t].ColumnName + " = Convert.To" + TableList.Columns[t].DataType.ToString().Replace("System.", "") + "(_SDR[\"" + TableList.Columns[t].ColumnName + "\"]);\n");
303         }
304         result.Append("}\n");
305         result.Append("_SDR.Close();\n");
306         result.Append("_SDR.Dispose();\n");
307         result.Append("}\n");
308         result.Append("return _" + this.Entity_Title.Text + "Info;\n");
309         result.Append("}\n");
310         result.Append("#endregion\n\n");
311 
312 
313         result.Append("#region 列表\n");
314         result.Append("/// <summary>\n");
315         result.Append("/// \n");
316         result.Append("/// </summary>\n");
317         result.Append("public static DataTable List()\n");
318         result.Append("{\n");
319         result.Append("DataSet _DataSet = SqlHelper.ExecuteDataSet(SqlHelper.ConnectionString5, CommandType.StoredProcedure, \"" + TableName + "_List\", null);\n");
320         result.Append("return _DataSet.Tables[0];\n");
321         result.Append("}\n");
322         result.Append("#endregion\n\n");
323 
324 
325         result.Append("#region 设置\n");
326         result.Append("/// <summary>\n");
327         result.Append("/// 设置\n");
328         result.Append("/// </summary>\n");
329         result.Append("/// <param name=\"_UserInfo\">实体类</param>\n");
330         result.Append("/// <returns></returns>\n");
331         result.Append("public static Boolean Set(" + this.Entity_Title.Text + "Info _" + this.Entity_Title.Text + "Info)\n");
332         result.Append("{\n");
333         result.Append("SqlParameter[] parm ={\n");
334         for (int t = 0; t < TableList.Columns.Count; t++)
335         {
336             result.Append("                                     new SqlParameter(\"" + TableList.Columns[t].ColumnName + "\",_" + this.Entity_Title.Text + "Info." + TableList.Columns[t].ColumnName + "),\n");
337         }
338         result.Append("                                  };\n");
339         result.Append("SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionString5, CommandType.StoredProcedure, \"" + TableName + "_Set\", parm);\n");
340         result.Append("return true;\n");
341         result.Append("}\n");
342         result.Append("#endregion\n");
343 
344         result.Append("}\n");
345 
346         this.Entity_Result.Text = result.ToString();
347     }
348 
349     protected void Button2_Click(object sender, EventArgs e)
350     {
351         StringBuilder result = new StringBuilder();
352         String TableName = this.DB_TableName.Text;
353         DataTable TableList = MethodLibrary.ExecuteDIYSql("select top 0 * from " + TableName);
354         String title = "My" + this.Entity_Title.Text;
355         result.Append("public class " + title + "\n");
356         result.Append("{\n");
357         result.Append("#region 添加\n");
358         result.Append("/// <summary>\n");
359         result.Append("/// 添加\n");
360         result.Append("/// </summary>\n");
361         result.Append("/// <param name=\"_UserInfo\">实体类</param>\n");
362         result.Append("/// <returns></returns>\n");
363         result.Append("public static Boolean Add(" + this.Entity_Title.Text + "Info _" + this.Entity_Title.Text + "Info)\n");
364         result.Append("{\n");
365         result.Append("return " + this.Entity_Title.Text + "Data.Add(_" + this.Entity_Title.Text + "Info);\n");
366         result.Append("}\n");
367         result.Append("#endregion\n\n");
368 
369 
370         result.Append("#region 删除\n");
371         result.Append("/// <summary>\n");
372         result.Append("/// 删除\n");
373         result.Append("/// </summary>\n");
374         result.Append("/// <param name=\"" + TableList.Columns[0].ColumnName + "\">SN</param>\n");
375         result.Append("/// <returns></returns>\n");
376         result.Append("public static Boolean Delete(" + TableList.Columns[0].DataType.ToString().Replace("System.", "") + " " + TableList.Columns[0].ColumnName + ")\n");
377         result.Append("{\n");
378         result.Append("return " + this.Entity_Title.Text + "Data.Delete(" + TableList.Columns[0].ColumnName + ");\n");
379         result.Append("}\n");
380         result.Append("#endregion\n\n");
381 
382 
383         result.Append("#region 获取\n");
384         result.Append("/// <summary>\n");
385         result.Append("/// 获取\n");
386         result.Append("/// </summary>\n");
387         result.Append("/// <param name=\"" + TableList.Columns[0].ColumnName + "\">SN</param>\n");
388         result.Append("/// <returns></returns>\n");
389         result.Append("public static " + this.Entity_Title.Text + "Info Get(" + TableList.Columns[0].DataType.ToString().Replace("System.", "") + " " + TableList.Columns[0].ColumnName + ")\n");
390         result.Append("{\n");
391         result.Append("return " + this.Entity_Title.Text + "Data.Get(" + TableList.Columns[0].ColumnName + ");\n");
392         result.Append("}\n");
393         result.Append("#endregion\n\n");
394 
395 
396         result.Append("#region 列表\n");
397         result.Append("/// <summary>\n");
398         result.Append("/// \n");
399         result.Append("/// </summary>\n");
400         result.Append("public static DataTable List()\n");
401         result.Append("{\n");
402         result.Append("return " + this.Entity_Title.Text + "Data.List();\n");
403         result.Append("}\n");
404         result.Append("#endregion\n\n");
405 
406 
407         result.Append("#region 设置\n");
408         result.Append("/// <summary>\n");
409         result.Append("/// 设置\n");
410         result.Append("/// </summary>\n");
411         result.Append("/// <param name=\"_UserInfo\">实体类</param>\n");
412         result.Append("/// <returns></returns>\n");
413         result.Append("public static Boolean Set(" + this.Entity_Title.Text + "Info _" + this.Entity_Title.Text + "Info)\n");
414         result.Append("{\n");
415         result.Append("return " + this.Entity_Title.Text + "Data.Set(_" + this.Entity_Title.Text + "Info);\n");
416         result.Append("}\n");
417         result.Append("#endregion\n");
418 
419         result.Append("}\n");
420 
421         this.Entity_Result.Text = result.ToString();
422     }
423 
424     protected void Button21_Click(object sender, EventArgs e)
425     {
426         StringBuilder result = new StringBuilder();
427         String TableName = this.DB_TableName.Text;
428         DataTable TableList = MethodLibrary.ExecuteDIYSql("select column_name,data_type,character_maximum_length from information_schema.columns where table_name = '" + TableName + "'");
429 
430         #region 添加
431         for (int k = 0; k < TableList.Rows.Count; k++)
432         {
433             if (k + 1 == TableList.Rows.Count)
434             {
435                 result.Append(TableList.Rows[k]["column_name"].ToString() + "\n\n");
436             }
437             else
438             {
439                 result.Append(TableList.Rows[k]["Column_Name"].ToString() + ",");
440             }
441         }
442         #endregion
443 
444         this.Entity_Result.Text = result.ToString();
445     }
446 
447     protected void DB_Submit5_Click(object sender, EventArgs e)
448     {
449         StringBuilder result = new StringBuilder();
450         String TableName = this.DB_TableName.Text;
451         DataTable TableList = MethodLibrary.ExecuteDIYSql("select top 0 * from " + TableName);
452         String title = this.Entity_Title.Text + "Info";
453 
454         result.Append("<table>\n");
455         for (int k = 0; k < TableList.Columns.Count; k++)
456         {
457             string _ColumnName = TableList.Columns[k].ColumnName.Split('_')[1];
458             if (TableList.Columns[k].ColumnName.Split('_').Length > 2)
459             {
460                 _ColumnName = TableList.Columns[k].ColumnName.Split('_')[1] + TableList.Columns[k].ColumnName.Split('_')[2];
461             }
462             result.Append("    <tr>\n");
463             result.Append("        <td class=\"infozuo\">" + TableList.Columns[k].ColumnName + "：</td>\n");
464             result.Append("        <td class=\"infoyou\"><asp:TextBox ID=\"Texx_" + _ColumnName + "\" MaxLength=\"50\" runat=\"server\" CssClass=\"class\"></asp:TextBox></td>\n");
465             result.Append("    </tr>\n\n");
466         }
467         result.Append("</table>");
468         result.Append("\n\n\n\n\n");
469 
470 
471         result.Append(title + " _" + title + " = new " + title + "();\n\n");
472         for (int k = 0; k < TableList.Columns.Count; k++)
473         {
474             string _ColumnName = TableList.Columns[k].ColumnName.Split('_')[1];
475             if (TableList.Columns[k].ColumnName.Split('_').Length > 2)
476             {
477                 _ColumnName = TableList.Columns[k].ColumnName.Split('_')[1] + TableList.Columns[k].ColumnName.Split('_')[2];
478             }
479             result.Append("this.Texx_" + _ColumnName + ".Text = _" + title + "." + TableList.Columns[k].ColumnName + ";\n");
480         }
481 
482         result.Append("\n\n\n\n\n");
483 
484 
485         result.Append(title + " _" + title + " = new " + title + "();\n\n");
486         for (int k = 0; k < TableList.Columns.Count; k++)
487         {
488             string _ColumnName = TableList.Columns[k].ColumnName.Split('_')[1];
489             if (TableList.Columns[k].ColumnName.Split('_').Length > 2)
490             {
491                 _ColumnName = _ColumnName + TableList.Columns[k].ColumnName.Split('_')[2];
492             }
493             result.Append("_" + title + "." + TableList.Columns[k].ColumnName + " = this.Texx_" + _ColumnName + ".Text;\n");
494         }
495 
496         this.Entity_Result.Text = result.ToString();
497 
498     }

方便使用连接数据库执行数据库，还需要在业务层建立一个MethodLibrary类，在类里添加MethodLibrary.ExecuteDIYSql方法。

 1  #region 执行指定SQL
 2         /// <summary>
 3         /// 执行指定SQL
 4         /// </summary>
 5         /// <param name="_DIYSql">SQL语句</param>
 6         /// <returns></returns>
 7         public static DataTable ExecuteDIYSql(String _DIYSql)
 8         {
 9             DataSet _TempSet = SqlHelper.ExecuteDataSet(SqlHelper.ConnectionString, CommandType.Text, _DIYSql, null);
10             return _TempSet.Tables[0];
11         }
12         #endregion

posted @ 2018-06-18 14:26 张三道阅读(333) 评论(0) 收藏举报

刷新页面返回顶部