AdolphYang

  博客园  :: 首页  :: 新随笔  :: 联系 :: 订阅 订阅  :: 管理
 1 using Oracle.DataAccess.Client;
 2 using System;
 3 using System.Collections.Generic;
 4 using System.ComponentModel;
 5 using System.Data;
 6 using System.Drawing;
 7 using System.Linq;
 8 using System.Text;
 9 using System.Threading.Tasks;
10 using System.Windows.Forms;
11 
12 namespace WinForm_CreateGenerate_ForOracle
13 {
14     public partial class Form1 : Form
15     {
16         public Form1()
17         {
18             InitializeComponent();
19         }

 

 1         /// <summary>
 2         /// 窗口加载
 3         /// </summary>
 4         /// <param name="sender"></param>
 5         /// <param name="e"></param>
 6         private void Form1_Load(object sender, EventArgs e)
 7         {
 8             txtNamespaceProfix.Text = "WinForm_UserInfo";
 9             txtdbconnStr.Text = @"Data Source=(DESCRIPTION =
10 (ADDRESS_LIST =
11 (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
12 )
13 (CONNECT_DATA =
14 (SERVER = DEDICATED)
15 (SERVICE_NAME = orcl)
16 )
17 );User Id=scott;Password=XXXXXX";
18         }
窗口加载

 

 1         /// <summary>
 2         ///点击 链接 链接Oracle数据库
 3         /// </summary>
 4         /// <param name="sender"></param>
 5         /// <param name="e"></param>
 6         private void btnConnect_Click(object sender, EventArgs e)
 7         {
 8             string connStr = txtdbconnStr.Text.Trim();
 9             string sql = "select * from user_tables"; //查询Oracle数据库中当前用户的所有表的信息
10             try
11             {
12                 using (OracleConnection conn = new OracleConnection(connStr))
13                 using (OracleCommand cmd = new OracleCommand(sql, conn))
14                 {
15                     conn.Open();
16                     using (OracleDataReader reader = cmd.ExecuteReader())
17                     {
18                         while (reader.Read())
19                         {
20                             string tableName = reader[0].ToString();
21                             cmbTables.Items.Add(tableName);
22                         }
23                     }
24                 }
25             }
26             catch (Exception ex)
27             {
28                 throw new Exception("出错:" + ex.Message.ToString());
29             }
30         }
点击 链接 链接Oracle数据库

 

 1         /// <summary>
 2         /// 如果当前列可Null,并且数据类型不是String,需要拼接"?"
 3         /// </summary>
 4         /// <param name="isNull"></param>
 5         /// <param name="cType"></param>
 6         /// <returns></returns>
 7         private string CheckJoinWenHao(string isNull,string cType)
 8         { 
 9             if(isNull=="Y" && cType!="String")
10             {
11                 return cType + "?";
12             }
13             else
14             {
15                 return cType;
16             }
17         }
如果当前列可Null,并且数据类型不是String,需要拼接"?"

 

 1         /// <summary>
 2         /// 获得列名连接字符串、参数连接字符串、Model连接字符串、ToModel连接字符串
 3         /// </summary>
 4         /// <param name="tableName">表名</param>
 5         /// <param name="newClassName">新类名</param>
 6         /// <param name="columnNameWithConsListStr">列名连接字符串</param>
 7         /// <param name="paraListStr">参数连接字符串</param>
 8         private void GetColumnListStrAndParaListStr(string tableName, string newClassName, out string columnNameWithConsListStr, out string columnNameWithEqualListStr, out string paraListStr, out string rowtomodelStr, out string modelStr)
 9         {
10             //查询当前表所有列名
11             string sql = "Select column_name,data_type,Nullable From user_tab_columns where table_name = :table_name"; //在当前用户的所有列中查询指定表的列信息
12             List<string> columnNameList = new List<string>();
13             List<string> columnNameWithConsList = new List<string>(); //带':'的列名集合 
14             List<string> columnNameWithEqualList = new List<string>(); //带'='的列名集合
15             List<string> paraList = new List<string>(); //参数集合
16             rowtomodelStr = "";
17             StringBuilder sbModel = new StringBuilder();
18             
19             try
20             {
21                 using (OracleConnection conn = new OracleConnection(txtdbconnStr.Text.Trim()))
22                 {
23                     conn.Open();
24                     using (OracleCommand cmd = new OracleCommand(sql, conn))
25                     {
26                         cmd.Parameters.Add(new OracleParameter(":table_name", tableName));
27                         using (OracleDataReader reader = cmd.ExecuteReader())
28                         {
29                             long count = reader.RowSize; //行数
30 
31                             while (reader.Read())
32                             {
33                                 string columnName = reader[0].ToString();
34                                 string typeName = reader[1].ToString();
35                                 string isDbNull = reader[2].ToString();
36                                 string lowClassName = newClassName.ToLower(); //小写类名,表示实例
37                                 #region 拼接Model中的列字符串
38                                 //public string Address { get; set; }
39                                 //拼接Model中的列字符串
40                                 sbModel.Append("public ").Append(CheckJoinWenHao(isDbNull, DbTypeToCtype(typeName))).Append(" ").Append(columnName).AppendLine(" { get; set; }\n"); 
41                                 #endregion
42                                 #region 拼接RowToModel中的行转换
43                                 #region 冗余
44                                 //user.Id = Convert.ToInt32(row["Id"]);
45                                 //user.UserName = (string)row["UserName"]; //1 根据列的类型返回一个C#类型,2 判断是否为DbNull,3 判断类型是否为string
46                                 //user.Email = row["Email"] == DBNull.Value ? null : Convert.ToString(row["Email"]);
47                                 //拼接RowToModel 
48                                 #endregion
49                                 //拼接RowToModel中的行转换
50                                 StringBuilder sb = new StringBuilder();
51                                 if (reader[2].ToString() == "Y")
52                                 {
53                                     sb.Append(lowClassName).Append(".").Append(columnName).Append(" = row[\"").Append(columnName).Append("\"] == DBNull.Value ? null : Convert.To").Append(DbTypeToCtype(typeName)).Append("(row[\"").Append(columnName).AppendLine("\"]);");
54                                 }
55                                 else
56                                 {
57                                     sb.Append(newClassName.ToLower()).Append(".").Append(reader[0].ToString()).Append(" = Convert.To").Append(DbTypeToCtype(reader[1].ToString())).Append("(row[\"").Append(reader[0].ToString()).AppendLine("\"]);");
58                                 }
59                                 rowtomodelStr += sb.ToString(); //累积RowToModel字符串 
60                                 #endregion
61                                 #region 拼接列名和参数字符串
62                                 //拼接列名和参数字符串
63                                 if (reader[0].ToString() != "ID") //去除ID项
64                                 {
65                                     columnNameList.Add(reader[0].ToString());
66                                     columnNameWithConsList.Add(":" + reader[0].ToString());
67                                     columnNameWithEqualList.Add(columnName + "=:" + columnName); //USERNAME=:USERNAME
68                                     //new OracleParameter(":Email", user.Email==null?(object)DBNull.Value:user.Email),
69                                     if (reader[2].ToString() == "Y")
70                                     {
71                                         paraList.Add("new OracleParameter(\":" + columnName + "\", " + lowClassName + "." + columnName + "==null?(object)DBNull.Value:" + lowClassName + "." + columnName + ")");
72                                     }
73                                     else
74                                     {
75                                         paraList.Add("new OracleParameter(\":" + columnName + "\", " + lowClassName + "." + columnName + ")");
76                                     }
77                                 } 
78                                 #endregion
79                             }
80                         }
81                     }
82                 }
83             }
84             catch (Exception ex)
85             {
86                 throw new Exception("出错:" + ex.Message.ToString());
87             }
88             columnNameWithConsListStr = string.Join(",", columnNameWithConsList);
89             columnNameWithEqualListStr = string.Join(",", columnNameWithEqualList);
90             paraListStr = string.Join(",", paraList);
91             modelStr = sbModel.ToString(); //model中的列字符串
92         }
获得列名连接字符串、参数连接字符串、Model连接字符串、ToModel连接字符串

 

 1         /// <summary>
 2         /// 把Oracle中的数据类型转C#中的数据类型
 3         /// </summary>
 4         /// <param name="dbType">Oracle中的数据类型 字符串</param>
 5         /// <returns>C#中的数据类型 字符串</returns>
 6         private string DbTypeToCtype(string dbType)
 7         {
 8             string ctype = "";
 9             switch (dbType)
10             {
11                 case "NUMBER": ctype = "Int32";  //未考虑小数点
12                     break;
13                 case "VARCHAR2": ctype = "String"; 
14                     break;
15                 case "DATE": ctype = "DateTime";
16                     break;
17                 default: throw new Exception("未知类型,需要添加新的类型");
18             }
19             return ctype;
20         }
把Oracle中的数据类型转C#中的数据类型

 

 1         /// <summary>
 2         /// 获得表名、新类名、命名空间前缀
 3         /// </summary>
 4         /// <param name="tabelName">表名</param>
 5         /// <param name="newClassName">新类名</param>
 6         /// <param name="nameSpaceProfix">命名空间前缀</param>
 7         private void GetTableNameAndClassNameAndSpcaeName(out string tabelName, out string newClassName, out string nameSpaceProfix)
 8         {
 9             //表名
10             tabelName = cmbTables.SelectedItem.ToString();
11             //新类名
12             string typeProfix = txtTypeProfix.Text.Trim();
13             if (typeProfix.Length <= 0)
14             {
15                 newClassName = tabelName;
16             }
17             else
18             {
19                 newClassName = tabelName.Replace(typeProfix, "");  //去掉前缀 或后缀
20             }
21             nameSpaceProfix = txtNamespaceProfix.Text.Trim(); //当前类型所在命名空间
22             if (nameSpaceProfix.Length <= 0)
23             {
24                 MessageBox.Show("请填命名空间");
25                 return;
26             }
27         }
获得表名、新类名、命名空间前缀

 

  1         /// <summary>
  2         /// 点击创建DAL
  3         /// </summary>
  4         /// <param name="sender"></param>
  5         /// <param name="e"></param>
  6         private void tsmiDll_Click(object sender, EventArgs e)
  7         {
  8             #region 冗余
  9             ////表名
 10             //string tabelName=cmbTables.SelectedItem.ToString();
 11             ////新类名
 12             //string typeProfix=txtTypeProfix.Text.Trim();
 13             //string newClassName;
 14             //if (typeProfix.Length <= 0)
 15             //{
 16             //    newClassName = tabelName;
 17             //}
 18             //else
 19             //{
 20             //    newClassName = tabelName.Replace(typeProfix, "");  //去掉前缀 或后缀
 21             //}
 22             //string nameSpaceProfix = txtNamespaceProfix.Text.Trim(); //当前类型所在命名空间
 23             //if (nameSpaceProfix.Length <= 0)
 24             //{
 25             //    MessageBox.Show("请填命名空间");
 26             //    return;
 27             //} 
 28             #endregion
 29             string tabelName, newClassName, nameSpaceProfix;
 30             GetTableNameAndClassNameAndSpcaeName(out tabelName,out newClassName,out nameSpaceProfix);
 31             string lowClassName = newClassName.ToLower();
 32             string columnNameWithConsListStr, paraListStr, rowtomodelStr, columnNameWithEqualListStr, modelStr;
 33             GetColumnListStrAndParaListStr(tabelName, newClassName, out columnNameWithConsListStr, out columnNameWithEqualListStr, out paraListStr, out rowtomodelStr, out modelStr);
 34             //拼接DAL
 35             StringBuilder sb = new StringBuilder();
 36             #region 冗余
 37             //using Oracle.DataAccess.Client;
 38             //using System;
 39             //using System.Collections.Generic;
 40             //using System.Data;
 41             //using System.Linq;
 42             //using System.Text;
 43             //using System.Threading.Tasks;
 44             //using WinForm_UserInfo.Model; 
 45             #endregion
 46             sb.AppendLine("using Oracle.DataAccess.Client;");
 47             sb.AppendLine("using System;");
 48             sb.AppendLine("using System.Collections.Generic;");
 49             sb.AppendLine("using System.Data;");
 50             sb.AppendLine("using System.Linq;");
 51             sb.AppendLine("using System.Text;");
 52             sb.AppendLine("using System.Threading.Tasks;");
 53             sb.Append("using ").Append(nameSpaceProfix).AppendLine(".Model;");
 54             #region 冗余
 55             //
 56             //namespace WinForm_UserInfo.DAL
 57             //{
 58             //    public class UserInfoDAL
 59             //    {
 60             //        /// <summary>
 61             //        /// 根据用户名获取用户实例
 62             //        /// </summary>
 63             //        /// <param name="userName">用户名</param>
 64             //        /// <returns>一个用户实例</returns> 
 65             #endregion
 66             sb.AppendLine("");
 67             sb.Append("namespace ").Append(nameSpaceProfix).AppendLine(".DAL");
 68             sb.AppendLine("{");
 69             sb.Append("    public class ").Append(newClassName).AppendLine("DAL");
 70             sb.AppendLine("    {");
 71 
 72             #region 根据对象ID获取实例
 73             sb.AppendLine("        /// <summary>");
 74             sb.AppendLine("        /// 根据对象ID获取实例");
 75             sb.AppendLine("        /// </summary>");
 76             sb.AppendLine("        /// <param name=\"id\">ID</param>");
 77             sb.AppendLine("       /// <returns>一个实例</returns>");
 78             #region 冗余
 79             //public UserInfo GetUserInfoById(int id)
 80             //{
 81             //    string sql = "SELECT * FROM T_USERINFO WHERE ID=:ID";
 82             //    DataTable dt = OracleHelper.ExecuteReader(sql, new OracleParameter(":ID", id));
 83             //    if (dt.Rows.Count <= 0)
 84             //    {
 85             //        return null;
 86             //    }
 87             //    else if (dt.Rows.Count == 1)
 88             //    {
 89             //        return RowToModel(dt.Rows[0]);
 90             //    }
 91             //    else
 92             //    {
 93             //        throw new Exception("数据重复,重复数据为:" + id);
 94             //    }
 95             //} 
 96             #endregion
 97             sb.Append("\tpublic ").Append(newClassName).Append(" Get").Append(newClassName).AppendLine("ById(int id)");
 98             sb.AppendLine("\t{");
 99             sb.Append("\t    string sql = \"SELECT * FROM ").Append(tabelName).AppendLine(" WHERE ID=:ID\";");
100             sb.AppendLine("\t    DataTable dt = OracleHelper.ExecuteReader(sql, new OracleParameter(\":ID\", id));");
101             sb.AppendLine("\t    if (dt.Rows.Count <= 0)");
102             sb.AppendLine("\t    {");
103             sb.AppendLine("\t        return null;");
104             sb.AppendLine("\t    }");
105             sb.AppendLine("\t    else if (dt.Rows.Count == 1)");
106             sb.AppendLine("\t    {");
107             sb.AppendLine("\t        return RowToModel(dt.Rows[0]);");
108             sb.AppendLine("\t    }");
109             sb.AppendLine("\t    else");
110             sb.AppendLine("\t    {");
111             sb.AppendLine("\t        throw new Exception(\"数据重复,重复数据为:\" + id);");
112             sb.AppendLine("\t    }");
113             sb.AppendLine("\t}"); 
114             #endregion
115 
116             #region 获得所有实例
117             #region 冗余
118             //    
119             ///// <summary>
120             ///// 获得所有用户
121             ///// </summary>
122             ///// <returns>返回所有用户</returns>
123             //public List<UserInfo> GetAllUserInfoes()
124             //{
125             //    List<UserInfo> users=new List<UserInfo>();
126             //    string sql = "SELECT * FROM T_USERINFO";
127             //    DataTable dt = OracleHelper.ExecuteReader(sql);
128             //    if (dt.Rows.Count <= 0)
129             //    {
130             //        return null;
131             //    }
132             //    else
133             //    {
134             //        foreach(DataRow row in dt.Rows)
135             //        {
136             //            users.Add(RowToModel(row));
137             //        }
138             //        return users;
139             //    }
140             //} 
141             #endregion
142             sb.AppendLine();
143             sb.AppendLine("\t/// <summary>");
144             sb.AppendLine("\t/// 获得所有实例");
145             sb.AppendLine("\t/// </summary>");
146             sb.AppendLine("\t/// <returns>返回所有实例</returns>");
147             sb.Append("\tpublic List<").Append(newClassName).Append("> GetAll").Append(newClassName).AppendLine("es()");
148             sb.AppendLine("\t{");
149             sb.Append("\t    List<").Append(newClassName).Append("> ").Append(newClassName.ToLower()).Append("s=new List<").Append(newClassName).AppendLine(">();");
150             sb.Append("\t    string sql = \"SELECT * FROM ").Append(tabelName).AppendLine("\";");
151             sb.AppendLine("\t    DataTable dt = OracleHelper.ExecuteReader(sql);");
152             sb.AppendLine("\t    if (dt.Rows.Count <= 0)");
153             sb.AppendLine("\t    {");
154             sb.AppendLine("\t        return null;");
155             sb.AppendLine("\t    }");
156             sb.AppendLine("\t    else");
157             sb.AppendLine("\t    {");
158             sb.AppendLine("\t        foreach(DataRow row in dt.Rows)");
159             sb.AppendLine("\t        {");
160             sb.Append("\t            ").Append(newClassName.ToLower()).AppendLine("s.Add(RowToModel(row));");
161             sb.AppendLine("\t        }");
162             sb.Append("\t        return ").Append(newClassName.ToLower()).AppendLine("s;");
163             sb.AppendLine("\t    }");
164             sb.AppendLine("\t}"); 
165             #endregion
166 
167             #region 根据实例ID删除实例
168             #region 冗余
169             //   
170             ///// <summary>
171             ///// 根据用户ID删除用户
172             ///// </summary>
173             ///// <param name="id">用户ID</param>
174             ///// <returns>受影响行数</returns>
175             //public int DeleteUserInfoById(int id)
176             //{
177             //    string sql = "DELETE FROM T_USERINFO WHERE ID=:ID";
178             //    return OracleHelper.ExecuteNonQuery(sql, new OracleParameter(":ID", id));
179             //} 
180             #endregion
181             sb.AppendLine();
182             sb.AppendLine("\t/// <summary>");
183             sb.AppendLine("\t/// 根据实例ID删除实例");
184             sb.AppendLine("\t/// </summary>");
185             sb.AppendLine("\t/// <param name=\"id\">实例ID</param>");
186             sb.AppendLine("\t/// <returns>受影响行数</returns>");
187             sb.Append("\tpublic int Delete").Append(newClassName).AppendLine("ById(int id)");
188             sb.AppendLine("\t{");
189             sb.Append("\t    string sql = \"DELETE FROM ").Append(tabelName).AppendLine(" WHERE ID=:ID\";");
190             sb.AppendLine("\t    return OracleHelper.ExecuteNonQuery(sql, new OracleParameter(\":ID\", id));");
191             sb.AppendLine("\t}"); 
192             #endregion
193 
194             #region 新增实例
195             #region 冗余
196             //
197             ///// <summary>
198             ///// 新增用户
199             ///// </summary>
200             ///// <param name="user">用户实例</param>
201             ///// <returns>受影响行数</returns>
202             //public int InsertUserInfo(UserInfo user)
203             //{
204             //    //SE_T_USERINFO.NEXTVAL
205             //    string sql = "INSERT INTO T_USERINFO VALUES(SE_T_USERINFO.NEXTVAL,:USERNAME,:PWD,:MOBILE,:EMAIL,:ADDRESS,:GENDER)";
206             //    return OracleHelper.ExecuteNonQuery(sql,
207             //                                                            new OracleParameter(":USERNAME", user.UserName),
208             //                                                        new OracleParameter(":PWD", user.Pwd),
209             //                                                        new OracleParameter(":MOBILE", user.Mobile),
210             //                                                        new OracleParameter(":EMAIL", user.Email),
211             //                                                        new OracleParameter(":ADDRESS", user.Address),
212             //                                                        new OracleParameter(":GENDER", user.Gender)
213             //                                                        );
214             //} 
215             #endregion
216             sb.AppendLine();
217             sb.AppendLine("\t/// <summary>");
218             sb.AppendLine("\t/// 新增实例");
219             sb.AppendLine("\t/// </summary>");
220             sb.Append("\t/// <param name=\"").Append(newClassName.ToLower()).AppendLine("\">实例</param>");
221             sb.AppendLine("\t/// <returns>受影响行数</returns>");
222             sb.Append("\tpublic int Insert").Append(newClassName).Append("(").Append(newClassName).Append(" ").Append(newClassName.ToLower()).AppendLine(")");
223             sb.AppendLine("\t{");
224             sb.Append("\t    string sql = \"INSERT INTO ").Append(tabelName).Append(" VALUES(SE_T_USERINFO.NEXTVAL,").Append(columnNameWithConsListStr).AppendLine(")\";");
225             sb.AppendLine("\t    return OracleHelper.ExecuteNonQuery(sql,");
226             sb.Append("\t                                                            ").AppendLine(paraListStr);
227             sb.AppendLine("\t                                                        );");
228             sb.AppendLine("\t}"); 
229             #endregion
230 
231             #region 更新实例
232             #region 冗余
233             ///// <summary>
234             ///// 更新用户
235             ///// </summary>
236             ///// <param name="user">用户实例</param>
237             ///// <returns>受影响行数</returns>
238             //public int UpdateUserInfo(UserInfo user)
239             //{
240             //    string sql = "UPDATE T_USERINFO SET USERNAME=:USERNAME,PWD=:PWD,MOBILE=:MOBILE,EMAIL=:EMAIL,ADDRESS=:ADDRESS,GENDER=:GENDER WHERE ID=:ID";
241             //    return OracleHelper.ExecuteNonQuery(sql, new OracleParameter(":USERNAME", user.UserName),
242             //                                                        new OracleParameter(":PWD", user.Pwd),
243             //                                                        new OracleParameter(":MOBILE", user.Mobile),
244             //                                                        new OracleParameter(":EMAIL", user.Email),
245             //                                                        new OracleParameter(":ADDRESS", user.Address),
246             //                                                        new OracleParameter(":GENDER", user.Gender),
247             //                                                        new OracleParameter(":ID", user.Id)
248             //                                                        );
249             //} 
250             #endregion
251             sb.AppendLine();
252             sb.AppendLine("\t/// <summary>");
253             sb.AppendLine("\t/// 更新实例");
254             sb.AppendLine("\t/// </summary>");
255             sb.Append("\t/// <param name=\"").Append(lowClassName).AppendLine("\">实例</param>");
256             sb.AppendLine("\t/// <returns>受影响行数</returns>");
257             sb.Append("\tpublic int Update").Append(newClassName).Append("(").Append(newClassName).Append(" ").Append(lowClassName).AppendLine(")");
258             sb.AppendLine("\t{");
259             sb.Append("\t    string sql = \"UPDATE ").Append(tabelName).Append(" SET ").Append(columnNameWithEqualListStr).AppendLine(" WHERE ID=:ID\";");
260             sb.AppendLine("\t    return OracleHelper.ExecuteNonQuery(sql,");
261             sb.Append("\t                                                        ").AppendLine(paraListStr);
262             sb.Append("\t\t,new OracleParameter(\":ID\",").Append(lowClassName).AppendLine(".ID)");
263             sb.AppendLine("\t                                                        );");
264             sb.AppendLine("\t}"); 
265             #endregion
266 
267             #region Row转Model
268             #region 冗余
269             ///// <summary>
270             ///// DataRow转Model
271             ///// </summary>
272             ///// <param name="row">表中一行数据</param>
273             ///// <returns>一个对象实例</returns>
274             //private UserInfo RowToModel(DataRow row)
275             //{
276             //    //Id UserName Pwd Mobile Email Address Gender
277             //    UserInfo user = new UserInfo();
278             //    user.Id = Convert.ToInt32(row["Id"]);
279             //    user.UserName = (string)row["UserName"];
280             //    user.Pwd = (string)row["Pwd"];
281             //    user.Mobile = (string)row["Mobile"];
282             //    user.Email = row["Email"] == DBNull.Value ? null : (string)row["Email"];
283             //    user.Address = row["Address"] == DBNull.Value ? null : (string)row["Email"];
284             //    user.Gender = Convert.ToInt32(row["Gender"]);
285             //    return user;
286             //} 
287             #endregion
288             sb.AppendLine();
289             sb.AppendLine("\t/// <summary>");
290             sb.AppendLine("\t/// DataRow转Model");
291             sb.AppendLine("\t/// </summary>");
292             sb.AppendLine("\t/// <param name=\"row\">表中一行数据</param>");
293             sb.AppendLine("\t/// <returns>一个对象实例</returns>");
294             sb.Append("\tprivate ").Append(newClassName).AppendLine(" RowToModel(DataRow row)");
295             sb.AppendLine("\t{");
296             sb.Append("\t    ").Append(newClassName).Append(" ").Append(lowClassName).Append(" = new ").Append(newClassName).AppendLine("();");
297             sb.Append("\t    ").AppendLine(rowtomodelStr);
298             sb.Append("\t    return ").Append(lowClassName).AppendLine(";");
299             sb.AppendLine("\t}"); 
300             #endregion
301 
302             sb.AppendLine("\t}");
303             sb.AppendLine("}");
304             txt.Text = sb.ToString();
305         }
点击创建DAL

 

  1         /// <summary>
  2         /// 点击创建BLL
  3         /// </summary>
  4         /// <param name="sender"></param>
  5         /// <param name="e"></param>
  6         private void tsmiBll_Click(object sender, EventArgs e)
  7         {
  8             string tabelName, newClassName, nameSpaceProfix;
  9             GetTableNameAndClassNameAndSpcaeName(out tabelName, out newClassName, out nameSpaceProfix);
 10             string lowClassName=newClassName.ToLower();
 11             #region 冗余
 12             //using System;
 13             //using System.Collections.Generic;
 14             //using System.Linq;
 15             //using System.Text;
 16             //using System.Threading.Tasks;
 17             //using WinForm_UserInfo.DAL;
 18             //using WinForm_UserInfo.Model; 
 19             #endregion
 20             StringBuilder sb = new StringBuilder();
 21             sb.AppendLine("using System;");
 22             sb.AppendLine("using System.Collections.Generic;");
 23             sb.AppendLine("using System.Linq;");
 24             sb.AppendLine("using System.Text;");
 25             sb.AppendLine("using System.Threading.Tasks;");
 26             sb.Append("using ").Append(nameSpaceProfix).AppendLine(".DAL;");
 27             sb.Append("using ").Append(nameSpaceProfix).AppendLine(".Model;");
 28             #region 冗余
 29         //namespace WinForm_UserInfo.BLL
 30 //{
 31 //    public class UserInfoBLL
 32 //    {
 33 //        UserInfoDAL userDal = new UserInfoDAL();
 34 
 35 //        /// <summary>
 36 //        /// 根据用户名获取用户实例
 37 //        /// </summary>
 38 //        /// <param name="userName">用户名</param>
 39 //        /// <returns>一个用户实例</returns> 
 40     #endregion
 41             sb.AppendLine();
 42             sb.Append("namespace ").Append(nameSpaceProfix).AppendLine(".BLL");
 43             sb.AppendLine("{");
 44             sb.Append("    public class ").Append(newClassName).AppendLine("BLL");
 45             sb.AppendLine("    {");
 46             sb.Append("\t").Append(newClassName).Append("DAL ").Append(lowClassName).Append("Dal = new ").Append(newClassName).AppendLine("DAL();");
 47 
 48             #region 根据ID获得实例
 49             sb.AppendLine();
 50             sb.AppendLine("\t/// <summary>");
 51             sb.AppendLine("\t/// 根据ID获得实例");
 52             sb.AppendLine("\t/// </summary>");
 53             sb.AppendLine("\t/// <param name=\"id\">实例ID</param>");
 54             sb.AppendLine("\t/// <returns>一个实例</returns>");
 55             #region 冗余
 56             //public UserInfo GetUserInfoById(int id)
 57             //{
 58             //    return userDal.GetUserInfoById(id);
 59             //} 
 60             #endregion
 61             sb.Append("\tpublic ").Append(newClassName).Append(" Get").Append(newClassName).AppendLine("ById(int id)");
 62             sb.AppendLine("\t{");
 63             sb.Append("\t    return ").Append(lowClassName).Append("Dal.Get").Append(newClassName).AppendLine("ById(id);");
 64             sb.AppendLine("\t}"); 
 65             #endregion
 66 
 67             #region 获得所有实例
 68             #region 冗余
 69             ///// <summary>
 70             ///// 获得所有用户
 71             ///// </summary>
 72             ///// <returns>返回所有用户</returns>
 73             //public List<UserInfo> GetAllUserInfoes()
 74             //{
 75             //    return userDal.GetAllUserInfoes();
 76             //} 
 77             #endregion
 78             sb.AppendLine();
 79             sb.AppendLine("\t/// <summary>");
 80             sb.AppendLine("\t/// 获得所有实例");
 81             sb.AppendLine("\t/// </summary>");
 82             sb.AppendLine("\t/// <returns>返回所有实例</returns>");
 83             sb.Append("\tpublic List<").Append(newClassName).Append("> GetAll").Append(newClassName).AppendLine("es()");
 84             sb.AppendLine("\t{");
 85             sb.Append("\t    return ").Append(lowClassName).Append("Dal.GetAll").Append(newClassName).AppendLine("es();");
 86             sb.AppendLine("\t}"); 
 87             #endregion
 88 
 89             #region 根据实例ID删除实例
 90             #region 冗余
 91             // /// <summary>
 92             ///// 根据用户ID删除用户
 93             ///// </summary>
 94             ///// <param name="id">用户ID</param>
 95             ///// <returns>是否删除成功</returns>
 96             //public bool DeleteUserInfoById(int id)
 97             //{
 98             //    int i = userDal.DeleteUserInfoById(id);
 99             //    return i > 0;
100             //} 
101             #endregion
102             sb.AppendLine();
103             sb.AppendLine("\t /// <summary>");
104             sb.AppendLine("\t/// 根据实例ID删除实例");
105             sb.AppendLine("\t/// </summary>");
106             sb.AppendLine("\t/// <param name=\"id\">实例ID</param>");
107             sb.AppendLine("\t/// <returns>是否删除成功</returns>");
108             sb.Append("\tpublic bool Delete").Append(newClassName).AppendLine("ById(int id)");
109             sb.AppendLine("\t{");
110             sb.Append("\t    int i = ").Append(lowClassName).Append("Dal.Delete").Append(newClassName).AppendLine("ById(id);");
111             sb.AppendLine("\t    return i > 0;");
112             sb.AppendLine("\t}"); 
113             #endregion
114 
115             #region 新增实例
116             #region 冗余
117             ///// <summary>
118             ///// 新增用户
119             ///// </summary>
120             ///// <param name="user">用户实例</param>
121             ///// <returns>是否新增成功</returns>
122             //public bool InsertUserInfo(UserInfo user)
123             //{
124             //    int i = userDal.InsertUserInfo(user);
125             //    return i > 0;
126             //} 
127             #endregion
128             sb.AppendLine();
129             sb.AppendLine("\t/// <summary>");
130             sb.AppendLine("\t/// 新增实例");
131             sb.AppendLine("\t/// </summary>");
132             sb.Append("\t/// <param name=\"").Append(lowClassName).AppendLine("\">实例</param>");
133             sb.AppendLine("\t/// <returns>是否新增成功</returns>");
134             sb.Append("\tpublic bool Insert").Append(newClassName).Append("(").Append(newClassName).Append(" ").Append(lowClassName).AppendLine(")");
135             sb.AppendLine("\t{");
136             sb.Append("\t    int i = ").Append(lowClassName).Append("Dal.Insert").Append(newClassName).Append("(").Append(lowClassName).AppendLine(");");
137             sb.AppendLine("\t    return i > 0;");
138             sb.AppendLine("\t}"); 
139             #endregion
140 
141             #region 更新实例
142             #region 冗余
143             ///// <summary>
144             ///// 更新用户
145             ///// </summary>
146             ///// <param name="user">用户实例</param>
147             ///// <returns>师傅更新成功</returns>
148             //public bool UpdateUserInfo(UserInfo user)
149             //{
150             //    int i = userDal.UpdateUserInfo(user);
151             //    return i > 0;
152             //} 
153             #endregion
154             sb.AppendLine();
155             sb.AppendLine("\t/// <summary>");
156             sb.AppendLine("\t/// 更新实例");
157             sb.AppendLine("\t/// </summary>");
158             sb.Append("\t/// <param name=\"").Append(lowClassName).AppendLine("\">实例</param>");
159             sb.AppendLine("\t/// <returns>是否更新成功</returns>");
160             sb.Append("\tpublic bool Update").Append(newClassName).Append("(").Append(newClassName).Append(" ").Append(lowClassName).AppendLine(")");
161             sb.AppendLine("\t{");
162             sb.Append("\t    int i = ").Append(lowClassName).Append("Dal.Update").Append(newClassName).Append("(").Append(lowClassName).AppendLine(");");
163             sb.AppendLine("\t    return i > 0;");
164             sb.AppendLine("\t}"); 
165             #endregion
166 
167             sb.AppendLine("    }");
168             sb.AppendLine("}");
169             txt.Text = sb.ToString();
170         }
点击创建BLL

 

 1         /// <summary>
 2         /// 点击创建Model
 3         /// </summary>
 4         /// <param name="sender"></param>
 5         /// <param name="e"></param>
 6         private void tsmiModel_Click(object sender, EventArgs e)
 7         {
 8             string tabelName, newClassName, nameSpaceProfix;
 9             GetTableNameAndClassNameAndSpcaeName(out tabelName, out newClassName, out nameSpaceProfix);
10             string columnNameWithConsListStr, paraListStr, rowtomodelStr, columnNameWithEqualListStr, modelStr;
11             GetColumnListStrAndParaListStr(tabelName, newClassName, out columnNameWithConsListStr, out columnNameWithEqualListStr, out paraListStr, out rowtomodelStr, out modelStr);
12             #region 冗余
13         //using System;
14 //using System.Collections.Generic;
15 //using System.Linq;
16 //using System.Text;
17 //using System.Threading.Tasks; 
18     #endregion
19             StringBuilder sb = new StringBuilder();
20             sb.AppendLine("using System;");
21             sb.AppendLine("using System.Collections.Generic;");
22             sb.AppendLine("using System.Linq;");
23             sb.AppendLine("using System.Text;");
24             sb.AppendLine("using System.Threading.Tasks;");
25             #region 冗余
26             //namespace WinForm_UserInfo.Model
27             //{
28             //    public class UserInfo
29             //    {
30             //        //Id UserName Pwd Mobile Email Address Gender
31             //        public int Id { get; set; }
32             //        public string UserName { get; set; }
33             //        public string Pwd { get; set; }
34             //        public string Mobile { get; set; }
35             //        public string Email { get; set; }
36             //        public string Address { get; set; }
37             //        public int Gender { get; set; }
38             //    }
39             //} 
40             #endregion
41             sb.AppendLine();
42             sb.Append("namespace ").Append(nameSpaceProfix).AppendLine(".Model");
43             sb.AppendLine("{");
44             sb.Append("    public class ").AppendLine(newClassName);
45             sb.AppendLine("    {");
46             sb.AppendLine(modelStr);
47             sb.AppendLine("    }");
48             sb.AppendLine("}");
49             txt.Text = sb.ToString();
50         }
点击创建Model

 

1     }
2 }

 



  1 using Oracle.DataAccess.Client;
  2 using System;
  3 using System.Collections.Generic;
  4 using System.ComponentModel;
  5 using System.Data;
  6 using System.Drawing;
  7 using System.Linq;
  8 using System.Text;
  9 using System.Threading.Tasks;
 10 using System.Windows.Forms;
 11 
 12 namespace WinForm_CreateGenerate_ForOracle
 13 {
 14     public partial class Form1 : Form
 15     {
 16         public Form1()
 17         {
 18             InitializeComponent();
 19         }
 20 
 21         /// <summary>
 22         /// 窗口加载
 23         /// </summary>
 24         /// <param name="sender"></param>
 25         /// <param name="e"></param>
 26         private void Form1_Load(object sender, EventArgs e)
 27         {
 28             txtNamespaceProfix.Text = "WinForm_UserInfo";
 29             txtdbconnStr.Text = @"Data Source=(DESCRIPTION =
 30 (ADDRESS_LIST =
 31 (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
 32 )
 33 (CONNECT_DATA =
 34 (SERVER = DEDICATED)
 35 (SERVICE_NAME = orcl)
 36 )
 37 );User Id=scott;Password=abcd5226584";
 38         }
 39 
 40         /// <summary>
 41         ///点击 链接 链接Oracle数据库
 42         /// </summary>
 43         /// <param name="sender"></param>
 44         /// <param name="e"></param>
 45         private void btnConnect_Click(object sender, EventArgs e)
 46         {
 47             string connStr = txtdbconnStr.Text.Trim();
 48             string sql = "select * from user_tables"; //查询Oracle数据库中当前用户的所有表的信息
 49             try
 50             {
 51                 using (OracleConnection conn = new OracleConnection(connStr))
 52                 using (OracleCommand cmd = new OracleCommand(sql, conn))
 53                 {
 54                     conn.Open();
 55                     using (OracleDataReader reader = cmd.ExecuteReader())
 56                     {
 57                         while (reader.Read())
 58                         {
 59                             string tableName = reader[0].ToString();
 60                             cmbTables.Items.Add(tableName);
 61                         }
 62                     }
 63                 }
 64             }
 65             catch (Exception ex)
 66             {
 67                 throw new Exception("出错:" + ex.Message.ToString());
 68             }
 69         }
 70 
 71         /// <summary>
 72         /// 如果当前列可Null,并且数据类型不是String,需要拼接"?"
 73         /// </summary>
 74         /// <param name="isNull"></param>
 75         /// <param name="cType"></param>
 76         /// <returns></returns>
 77         private string CheckJoinWenHao(string isNull,string cType)
 78         { 
 79             if(isNull=="Y" && cType!="String")
 80             {
 81                 return cType + "?";
 82             }
 83             else
 84             {
 85                 return cType;
 86             }
 87         }
 88 
 89         /// <summary>
 90         /// 获得列名连接字符串、参数连接字符串、Model连接字符串、ToModel连接字符串
 91         /// </summary>
 92         /// <param name="tableName">表名</param>
 93         /// <param name="newClassName">新类名</param>
 94         /// <param name="columnNameWithConsListStr">列名连接字符串</param>
 95         /// <param name="paraListStr">参数连接字符串</param>
 96         private void GetColumnListStrAndParaListStr(string tableName, string newClassName, out string columnNameWithConsListStr, out string columnNameWithEqualListStr, out string paraListStr, out string rowtomodelStr, out string modelStr)
 97         {
 98             //查询当前表所有列名
 99             string sql = "Select column_name,data_type,Nullable From user_tab_columns where table_name = :table_name"; //在当前用户的所有列中查询指定表的列信息
100             List<string> columnNameList = new List<string>();
101             List<string> columnNameWithConsList = new List<string>(); //带':'的列名集合 
102             List<string> columnNameWithEqualList = new List<string>(); //带'='的列名集合
103             List<string> paraList = new List<string>(); //参数集合
104             rowtomodelStr = "";
105             StringBuilder sbModel = new StringBuilder();
106             
107             try
108             {
109                 using (OracleConnection conn = new OracleConnection(txtdbconnStr.Text.Trim()))
110                 {
111                     conn.Open();
112                     using (OracleCommand cmd = new OracleCommand(sql, conn))
113                     {
114                         cmd.Parameters.Add(new OracleParameter(":table_name", tableName));
115                         using (OracleDataReader reader = cmd.ExecuteReader())
116                         {
117                             long count = reader.RowSize; //行数
118 
119                             while (reader.Read())
120                             {
121                                 string columnName = reader[0].ToString();
122                                 string typeName = reader[1].ToString();
123                                 string isDbNull = reader[2].ToString();
124                                 string lowClassName = newClassName.ToLower(); //小写类名,表示实例
125                                 #region 拼接Model中的列字符串
126                                 //public string Address { get; set; }
127                                 //拼接Model中的列字符串
128                                 sbModel.Append("public ").Append(CheckJoinWenHao(isDbNull, DbTypeToCtype(typeName))).Append(" ").Append(columnName).AppendLine(" { get; set; }\n"); 
129                                 #endregion
130                                 #region 拼接RowToModel中的行转换
131                                 #region 冗余
132                                 //user.Id = Convert.ToInt32(row["Id"]);
133                                 //user.UserName = (string)row["UserName"]; //1 根据列的类型返回一个C#类型,2 判断是否为DbNull,3 判断类型是否为string
134                                 //user.Email = row["Email"] == DBNull.Value ? null : Convert.ToString(row["Email"]);
135                                 //拼接RowToModel 
136                                 #endregion
137                                 //拼接RowToModel中的行转换
138                                 StringBuilder sb = new StringBuilder();
139                                 if (reader[2].ToString() == "Y")
140                                 {
141                                     sb.Append(lowClassName).Append(".").Append(columnName).Append(" = row[\"").Append(columnName).Append("\"] == DBNull.Value ? null : Convert.To").Append(DbTypeToCtype(typeName)).Append("(row[\"").Append(columnName).AppendLine("\"]);");
142                                 }
143                                 else
144                                 {
145                                     sb.Append(newClassName.ToLower()).Append(".").Append(reader[0].ToString()).Append(" = Convert.To").Append(DbTypeToCtype(reader[1].ToString())).Append("(row[\"").Append(reader[0].ToString()).AppendLine("\"]);");
146                                 }
147                                 rowtomodelStr += sb.ToString(); //累积RowToModel字符串 
148                                 #endregion
149                                 #region 拼接列名和参数字符串
150                                 //拼接列名和参数字符串
151                                 if (reader[0].ToString() != "ID") //去除ID项
152                                 {
153                                     columnNameList.Add(reader[0].ToString());
154                                     columnNameWithConsList.Add(":" + reader[0].ToString());
155                                     columnNameWithEqualList.Add(columnName + "=:" + columnName); //USERNAME=:USERNAME
156                                     //new OracleParameter(":Email", user.Email==null?(object)DBNull.Value:user.Email),
157                                     if (reader[2].ToString() == "Y")
158                                     {
159                                         paraList.Add("new OracleParameter(\":" + columnName + "\", " + lowClassName + "." + columnName + "==null?(object)DBNull.Value:" + lowClassName + "." + columnName + ")");
160                                     }
161                                     else
162                                     {
163                                         paraList.Add("new OracleParameter(\":" + columnName + "\", " + lowClassName + "." + columnName + ")");
164                                     }
165                                 } 
166                                 #endregion
167                             }
168                         }
169                     }
170                 }
171             }
172             catch (Exception ex)
173             {
174                 throw new Exception("出错:" + ex.Message.ToString());
175             }
176             columnNameWithConsListStr = string.Join(",", columnNameWithConsList);
177             columnNameWithEqualListStr = string.Join(",", columnNameWithEqualList);
178             paraListStr = string.Join(",", paraList);
179             modelStr = sbModel.ToString(); //model中的列字符串
180         }
181 
182         /// <summary>
183         /// 把Oracle中的数据类型转C#中的数据类型
184         /// </summary>
185         /// <param name="dbType">Oracle中的数据类型 字符串</param>
186         /// <returns>C#中的数据类型 字符串</returns>
187         private string DbTypeToCtype(string dbType)
188         {
189             string ctype = "";
190             switch (dbType)
191             {
192                 case "NUMBER": ctype = "Int32";  //未考虑小数点
193                     break;
194                 case "VARCHAR2": ctype = "String"; 
195                     break;
196                 case "DATE": ctype = "DateTime";
197                     break;
198                 default: throw new Exception("未知类型,需要添加新的类型");
199             }
200             return ctype;
201         }
202 
203         /// <summary>
204         /// 获得表名、新类名、命名空间前缀
205         /// </summary>
206         /// <param name="tabelName">表名</param>
207         /// <param name="newClassName">新类名</param>
208         /// <param name="nameSpaceProfix">命名空间前缀</param>
209         private void GetTableNameAndClassNameAndSpcaeName(out string tabelName, out string newClassName, out string nameSpaceProfix)
210         {
211             //表名
212             tabelName = cmbTables.SelectedItem.ToString();
213             //新类名
214             string typeProfix = txtTypeProfix.Text.Trim();
215             if (typeProfix.Length <= 0)
216             {
217                 newClassName = tabelName;
218             }
219             else
220             {
221                 newClassName = tabelName.Replace(typeProfix, "");  //去掉前缀 或后缀
222             }
223             nameSpaceProfix = txtNamespaceProfix.Text.Trim(); //当前类型所在命名空间
224             if (nameSpaceProfix.Length <= 0)
225             {
226                 MessageBox.Show("请填命名空间");
227                 return;
228             }
229         }
230 
231         /// <summary>
232         /// 点击创建DAL
233         /// </summary>
234         /// <param name="sender"></param>
235         /// <param name="e"></param>
236         private void tsmiDll_Click(object sender, EventArgs e)
237         {
238             #region 冗余
239             ////表名
240             //string tabelName=cmbTables.SelectedItem.ToString();
241             ////新类名
242             //string typeProfix=txtTypeProfix.Text.Trim();
243             //string newClassName;
244             //if (typeProfix.Length <= 0)
245             //{
246             //    newClassName = tabelName;
247             //}
248             //else
249             //{
250             //    newClassName = tabelName.Replace(typeProfix, "");  //去掉前缀 或后缀
251             //}
252             //string nameSpaceProfix = txtNamespaceProfix.Text.Trim(); //当前类型所在命名空间
253             //if (nameSpaceProfix.Length <= 0)
254             //{
255             //    MessageBox.Show("请填命名空间");
256             //    return;
257             //} 
258             #endregion
259             string tabelName, newClassName, nameSpaceProfix;
260             GetTableNameAndClassNameAndSpcaeName(out tabelName,out newClassName,out nameSpaceProfix);
261             string lowClassName = newClassName.ToLower();
262             string columnNameWithConsListStr, paraListStr, rowtomodelStr, columnNameWithEqualListStr, modelStr;
263             GetColumnListStrAndParaListStr(tabelName, newClassName, out columnNameWithConsListStr, out columnNameWithEqualListStr, out paraListStr, out rowtomodelStr, out modelStr);
264             //拼接DAL
265             StringBuilder sb = new StringBuilder();
266             #region 冗余
267             //using Oracle.DataAccess.Client;
268             //using System;
269             //using System.Collections.Generic;
270             //using System.Data;
271             //using System.Linq;
272             //using System.Text;
273             //using System.Threading.Tasks;
274             //using WinForm_UserInfo.Model; 
275             #endregion
276             sb.AppendLine("using Oracle.DataAccess.Client;");
277             sb.AppendLine("using System;");
278             sb.AppendLine("using System.Collections.Generic;");
279             sb.AppendLine("using System.Data;");
280             sb.AppendLine("using System.Linq;");
281             sb.AppendLine("using System.Text;");
282             sb.AppendLine("using System.Threading.Tasks;");
283             sb.Append("using ").Append(nameSpaceProfix).AppendLine(".Model;");
284             #region 冗余
285             //
286             //namespace WinForm_UserInfo.DAL
287             //{
288             //    public class UserInfoDAL
289             //    {
290             //        /// <summary>
291             //        /// 根据用户名获取用户实例
292             //        /// </summary>
293             //        /// <param name="userName">用户名</param>
294             //        /// <returns>一个用户实例</returns> 
295             #endregion
296             sb.AppendLine("");
297             sb.Append("namespace ").Append(nameSpaceProfix).AppendLine(".DAL");
298             sb.AppendLine("{");
299             sb.Append("    public class ").Append(newClassName).AppendLine("DAL");
300             sb.AppendLine("    {");
301 
302             #region 根据对象ID获取实例
303             sb.AppendLine("        /// <summary>");
304             sb.AppendLine("        /// 根据对象ID获取实例");
305             sb.AppendLine("        /// </summary>");
306             sb.AppendLine("        /// <param name=\"id\">ID</param>");
307             sb.AppendLine("       /// <returns>一个实例</returns>");
308             #region 冗余
309             //public UserInfo GetUserInfoById(int id)
310             //{
311             //    string sql = "SELECT * FROM T_USERINFO WHERE ID=:ID";
312             //    DataTable dt = OracleHelper.ExecuteReader(sql, new OracleParameter(":ID", id));
313             //    if (dt.Rows.Count <= 0)
314             //    {
315             //        return null;
316             //    }
317             //    else if (dt.Rows.Count == 1)
318             //    {
319             //        return RowToModel(dt.Rows[0]);
320             //    }
321             //    else
322             //    {
323             //        throw new Exception("数据重复,重复数据为:" + id);
324             //    }
325             //} 
326             #endregion
327             sb.Append("\tpublic ").Append(newClassName).Append(" Get").Append(newClassName).AppendLine("ById(int id)");
328             sb.AppendLine("\t{");
329             sb.Append("\t    string sql = \"SELECT * FROM ").Append(tabelName).AppendLine(" WHERE ID=:ID\";");
330             sb.AppendLine("\t    DataTable dt = OracleHelper.ExecuteReader(sql, new OracleParameter(\":ID\", id));");
331             sb.AppendLine("\t    if (dt.Rows.Count <= 0)");
332             sb.AppendLine("\t    {");
333             sb.AppendLine("\t        return null;");
334             sb.AppendLine("\t    }");
335             sb.AppendLine("\t    else if (dt.Rows.Count == 1)");
336             sb.AppendLine("\t    {");
337             sb.AppendLine("\t        return RowToModel(dt.Rows[0]);");
338             sb.AppendLine("\t    }");
339             sb.AppendLine("\t    else");
340             sb.AppendLine("\t    {");
341             sb.AppendLine("\t        throw new Exception(\"数据重复,重复数据为:\" + id);");
342             sb.AppendLine("\t    }");
343             sb.AppendLine("\t}"); 
344             #endregion
345 
346             #region 获得所有实例
347             #region 冗余
348             //    
349             ///// <summary>
350             ///// 获得所有用户
351             ///// </summary>
352             ///// <returns>返回所有用户</returns>
353             //public List<UserInfo> GetAllUserInfoes()
354             //{
355             //    List<UserInfo> users=new List<UserInfo>();
356             //    string sql = "SELECT * FROM T_USERINFO";
357             //    DataTable dt = OracleHelper.ExecuteReader(sql);
358             //    if (dt.Rows.Count <= 0)
359             //    {
360             //        return null;
361             //    }
362             //    else
363             //    {
364             //        foreach(DataRow row in dt.Rows)
365             //        {
366             //            users.Add(RowToModel(row));
367             //        }
368             //        return users;
369             //    }
370             //} 
371             #endregion
372             sb.AppendLine();
373             sb.AppendLine("\t/// <summary>");
374             sb.AppendLine("\t/// 获得所有实例");
375             sb.AppendLine("\t/// </summary>");
376             sb.AppendLine("\t/// <returns>返回所有实例</returns>");
377             sb.Append("\tpublic List<").Append(newClassName).Append("> GetAll").Append(newClassName).AppendLine("es()");
378             sb.AppendLine("\t{");
379             sb.Append("\t    List<").Append(newClassName).Append("> ").Append(newClassName.ToLower()).Append("s=new List<").Append(newClassName).AppendLine(">();");
380             sb.Append("\t    string sql = \"SELECT * FROM ").Append(tabelName).AppendLine("\";");
381             sb.AppendLine("\t    DataTable dt = OracleHelper.ExecuteReader(sql);");
382             sb.AppendLine("\t    if (dt.Rows.Count <= 0)");
383             sb.AppendLine("\t    {");
384             sb.AppendLine("\t        return null;");
385             sb.AppendLine("\t    }");
386             sb.AppendLine("\t    else");
387             sb.AppendLine("\t    {");
388             sb.AppendLine("\t        foreach(DataRow row in dt.Rows)");
389             sb.AppendLine("\t        {");
390             sb.Append("\t            ").Append(newClassName.ToLower()).AppendLine("s.Add(RowToModel(row));");
391             sb.AppendLine("\t        }");
392             sb.Append("\t        return ").Append(newClassName.ToLower()).AppendLine("s;");
393             sb.AppendLine("\t    }");
394             sb.AppendLine("\t}"); 
395             #endregion
396 
397             #region 根据实例ID删除实例
398             #region 冗余
399             //   
400             ///// <summary>
401             ///// 根据用户ID删除用户
402             ///// </summary>
403             ///// <param name="id">用户ID</param>
404             ///// <returns>受影响行数</returns>
405             //public int DeleteUserInfoById(int id)
406             //{
407             //    string sql = "DELETE FROM T_USERINFO WHERE ID=:ID";
408             //    return OracleHelper.ExecuteNonQuery(sql, new OracleParameter(":ID", id));
409             //} 
410             #endregion
411             sb.AppendLine();
412             sb.AppendLine("\t/// <summary>");
413             sb.AppendLine("\t/// 根据实例ID删除实例");
414             sb.AppendLine("\t/// </summary>");
415             sb.AppendLine("\t/// <param name=\"id\">实例ID</param>");
416             sb.AppendLine("\t/// <returns>受影响行数</returns>");
417             sb.Append("\tpublic int Delete").Append(newClassName).AppendLine("ById(int id)");
418             sb.AppendLine("\t{");
419             sb.Append("\t    string sql = \"DELETE FROM ").Append(tabelName).AppendLine(" WHERE ID=:ID\";");
420             sb.AppendLine("\t    return OracleHelper.ExecuteNonQuery(sql, new OracleParameter(\":ID\", id));");
421             sb.AppendLine("\t}"); 
422             #endregion
423 
424             #region 新增实例
425             #region 冗余
426             //
427             ///// <summary>
428             ///// 新增用户
429             ///// </summary>
430             ///// <param name="user">用户实例</param>
431             ///// <returns>受影响行数</returns>
432             //public int InsertUserInfo(UserInfo user)
433             //{
434             //    //SE_T_USERINFO.NEXTVAL
435             //    string sql = "INSERT INTO T_USERINFO VALUES(SE_T_USERINFO.NEXTVAL,:USERNAME,:PWD,:MOBILE,:EMAIL,:ADDRESS,:GENDER)";
436             //    return OracleHelper.ExecuteNonQuery(sql,
437             //                                                            new OracleParameter(":USERNAME", user.UserName),
438             //                                                        new OracleParameter(":PWD", user.Pwd),
439             //                                                        new OracleParameter(":MOBILE", user.Mobile),
440             //                                                        new OracleParameter(":EMAIL", user.Email),
441             //                                                        new OracleParameter(":ADDRESS", user.Address),
442             //                                                        new OracleParameter(":GENDER", user.Gender)
443             //                                                        );
444             //} 
445             #endregion
446             sb.AppendLine();
447             sb.AppendLine("\t/// <summary>");
448             sb.AppendLine("\t/// 新增实例");
449             sb.AppendLine("\t/// </summary>");
450             sb.Append("\t/// <param name=\"").Append(newClassName.ToLower()).AppendLine("\">实例</param>");
451             sb.AppendLine("\t/// <returns>受影响行数</returns>");
452             sb.Append("\tpublic int Insert").Append(newClassName).Append("(").Append(newClassName).Append(" ").Append(newClassName.ToLower()).AppendLine(")");
453             sb.AppendLine("\t{");
454             sb.Append("\t    string sql = \"INSERT INTO ").Append(tabelName).Append(" VALUES(SE_T_USERINFO.NEXTVAL,").Append(columnNameWithConsListStr).AppendLine(")\";");
455             sb.AppendLine("\t    return OracleHelper.ExecuteNonQuery(sql,");
456             sb.Append("\t                                                            ").AppendLine(paraListStr);
457             sb.AppendLine("\t                                                        );");
458             sb.AppendLine("\t}"); 
459             #endregion
460 
461             #region 更新实例
462             #region 冗余
463             ///// <summary>
464             ///// 更新用户
465             ///// </summary>
466             ///// <param name="user">用户实例</param>
467             ///// <returns>受影响行数</returns>
468             //public int UpdateUserInfo(UserInfo user)
469             //{
470             //    string sql = "UPDATE T_USERINFO SET USERNAME=:USERNAME,PWD=:PWD,MOBILE=:MOBILE,EMAIL=:EMAIL,ADDRESS=:ADDRESS,GENDER=:GENDER WHERE ID=:ID";
471             //    return OracleHelper.ExecuteNonQuery(sql, new OracleParameter(":USERNAME", user.UserName),
472             //                                                        new OracleParameter(":PWD", user.Pwd),
473             //                                                        new OracleParameter(":MOBILE", user.Mobile),
474             //                                                        new OracleParameter(":EMAIL", user.Email),
475             //                                                        new OracleParameter(":ADDRESS", user.Address),
476             //                                                        new OracleParameter(":GENDER", user.Gender),
477             //                                                        new OracleParameter(":ID", user.Id)
478             //                                                        );
479             //} 
480             #endregion
481             sb.AppendLine();
482             sb.AppendLine("\t/// <summary>");
483             sb.AppendLine("\t/// 更新实例");
484             sb.AppendLine("\t/// </summary>");
485             sb.Append("\t/// <param name=\"").Append(lowClassName).AppendLine("\">实例</param>");
486             sb.AppendLine("\t/// <returns>受影响行数</returns>");
487             sb.Append("\tpublic int Update").Append(newClassName).Append("(").Append(newClassName).Append(" ").Append(lowClassName).AppendLine(")");
488             sb.AppendLine("\t{");
489             sb.Append("\t    string sql = \"UPDATE ").Append(tabelName).Append(" SET ").Append(columnNameWithEqualListStr).AppendLine(" WHERE ID=:ID\";");
490             sb.AppendLine("\t    return OracleHelper.ExecuteNonQuery(sql,");
491             sb.Append("\t                                                        ").AppendLine(paraListStr);
492             sb.Append("\t\t,new OracleParameter(\":ID\",").Append(lowClassName).AppendLine(".ID)");
493             sb.AppendLine("\t                                                        );");
494             sb.AppendLine("\t}"); 
495             #endregion
496 
497             #region Row转Model
498             #region 冗余
499             ///// <summary>
500             ///// DataRow转Model
501             ///// </summary>
502             ///// <param name="row">表中一行数据</param>
503             ///// <returns>一个对象实例</returns>
504             //private UserInfo RowToModel(DataRow row)
505             //{
506             //    //Id UserName Pwd Mobile Email Address Gender
507             //    UserInfo user = new UserInfo();
508             //    user.Id = Convert.ToInt32(row["Id"]);
509             //    user.UserName = (string)row["UserName"];
510             //    user.Pwd = (string)row["Pwd"];
511             //    user.Mobile = (string)row["Mobile"];
512             //    user.Email = row["Email"] == DBNull.Value ? null : (string)row["Email"];
513             //    user.Address = row["Address"] == DBNull.Value ? null : (string)row["Email"];
514             //    user.Gender = Convert.ToInt32(row["Gender"]);
515             //    return user;
516             //} 
517             #endregion
518             sb.AppendLine();
519             sb.AppendLine("\t/// <summary>");
520             sb.AppendLine("\t/// DataRow转Model");
521             sb.AppendLine("\t/// </summary>");
522             sb.AppendLine("\t/// <param name=\"row\">表中一行数据</param>");
523             sb.AppendLine("\t/// <returns>一个对象实例</returns>");
524             sb.Append("\tprivate ").Append(newClassName).AppendLine(" RowToModel(DataRow row)");
525             sb.AppendLine("\t{");
526             sb.Append("\t    ").Append(newClassName).Append(" ").Append(lowClassName).Append(" = new ").Append(newClassName).AppendLine("();");
527             sb.Append("\t    ").AppendLine(rowtomodelStr);
528             sb.Append("\t    return ").Append(lowClassName).AppendLine(";");
529             sb.AppendLine("\t}"); 
530             #endregion
531 
532             sb.AppendLine("\t}");
533             sb.AppendLine("}");
534             txt.Text = sb.ToString();
535         }
536 
537         /// <summary>
538         /// 点击创建BLL
539         /// </summary>
540         /// <param name="sender"></param>
541         /// <param name="e"></param>
542         private void tsmiBll_Click(object sender, EventArgs e)
543         {
544             string tabelName, newClassName, nameSpaceProfix;
545             GetTableNameAndClassNameAndSpcaeName(out tabelName, out newClassName, out nameSpaceProfix);
546             string lowClassName=newClassName.ToLower();
547             #region 冗余
548             //using System;
549             //using System.Collections.Generic;
550             //using System.Linq;
551             //using System.Text;
552             //using System.Threading.Tasks;
553             //using WinForm_UserInfo.DAL;
554             //using WinForm_UserInfo.Model; 
555             #endregion
556             StringBuilder sb = new StringBuilder();
557             sb.AppendLine("using System;");
558             sb.AppendLine("using System.Collections.Generic;");
559             sb.AppendLine("using System.Linq;");
560             sb.AppendLine("using System.Text;");
561             sb.AppendLine("using System.Threading.Tasks;");
562             sb.Append("using ").Append(nameSpaceProfix).AppendLine(".DAL;");
563             sb.Append("using ").Append(nameSpaceProfix).AppendLine(".Model;");
564             #region 冗余
565         //namespace WinForm_UserInfo.BLL
566 //{
567 //    public class UserInfoBLL
568 //    {
569 //        UserInfoDAL userDal = new UserInfoDAL();
570 
571 //        /// <summary>
572 //        /// 根据用户名获取用户实例
573 //        /// </summary>
574 //        /// <param name="userName">用户名</param>
575 //        /// <returns>一个用户实例</returns> 
576     #endregion
577             sb.AppendLine();
578             sb.Append("namespace ").Append(nameSpaceProfix).AppendLine(".BLL");
579             sb.AppendLine("{");
580             sb.Append("    public class ").Append(newClassName).AppendLine("BLL");
581             sb.AppendLine("    {");
582             sb.Append("\t").Append(newClassName).Append("DAL ").Append(lowClassName).Append("Dal = new ").Append(newClassName).AppendLine("DAL();");
583 
584             #region 根据ID获得实例
585             sb.AppendLine();
586             sb.AppendLine("\t/// <summary>");
587             sb.AppendLine("\t/// 根据ID获得实例");
588             sb.AppendLine("\t/// </summary>");
589             sb.AppendLine("\t/// <param name=\"id\">实例ID</param>");
590             sb.AppendLine("\t/// <returns>一个实例</returns>");
591             #region 冗余
592             //public UserInfo GetUserInfoById(int id)
593             //{
594             //    return userDal.GetUserInfoById(id);
595             //} 
596             #endregion
597             sb.Append("\tpublic ").Append(newClassName).Append(" Get").Append(newClassName).AppendLine("ById(int id)");
598             sb.AppendLine("\t{");
599             sb.Append("\t    return ").Append(lowClassName).Append("Dal.Get").Append(newClassName).AppendLine("ById(id);");
600             sb.AppendLine("\t}"); 
601             #endregion
602 
603             #region 获得所有实例
604             #region 冗余
605             ///// <summary>
606             ///// 获得所有用户
607             ///// </summary>
608             ///// <returns>返回所有用户</returns>
609             //public List<UserInfo> GetAllUserInfoes()
610             //{
611             //    return userDal.GetAllUserInfoes();
612             //} 
613             #endregion
614             sb.AppendLine();
615             sb.AppendLine("\t/// <summary>");
616             sb.AppendLine("\t/// 获得所有实例");
617             sb.AppendLine("\t/// </summary>");
618             sb.AppendLine("\t/// <returns>返回所有实例</returns>");
619             sb.Append("\tpublic List<").Append(newClassName).Append("> GetAll").Append(newClassName).AppendLine("es()");
620             sb.AppendLine("\t{");
621             sb.Append("\t    return ").Append(lowClassName).Append("Dal.GetAll").Append(newClassName).AppendLine("es();");
622             sb.AppendLine("\t}"); 
623             #endregion
624 
625             #region 根据实例ID删除实例
626             #region 冗余
627             // /// <summary>
628             ///// 根据用户ID删除用户
629             ///// </summary>
630             ///// <param name="id">用户ID</param>
631             ///// <returns>是否删除成功</returns>
632             //public bool DeleteUserInfoById(int id)
633             //{
634             //    int i = userDal.DeleteUserInfoById(id);
635             //    return i > 0;
636             //} 
637             #endregion
638             sb.AppendLine();
639             sb.AppendLine("\t /// <summary>");
640             sb.AppendLine("\t/// 根据实例ID删除实例");
641             sb.AppendLine("\t/// </summary>");
642             sb.AppendLine("\t/// <param name=\"id\">实例ID</param>");
643             sb.AppendLine("\t/// <returns>是否删除成功</returns>");
644             sb.Append("\tpublic bool Delete").Append(newClassName).AppendLine("ById(int id)");
645             sb.AppendLine("\t{");
646             sb.Append("\t    int i = ").Append(lowClassName).Append("Dal.Delete").Append(newClassName).AppendLine("ById(id);");
647             sb.AppendLine("\t    return i > 0;");
648             sb.AppendLine("\t}"); 
649             #endregion
650 
651             #region 新增实例
652             #region 冗余
653             ///// <summary>
654             ///// 新增用户
655             ///// </summary>
656             ///// <param name="user">用户实例</param>
657             ///// <returns>是否新增成功</returns>
658             //public bool InsertUserInfo(UserInfo user)
659             //{
660             //    int i = userDal.InsertUserInfo(user);
661             //    return i > 0;
662             //} 
663             #endregion
664             sb.AppendLine();
665             sb.AppendLine("\t/// <summary>");
666             sb.AppendLine("\t/// 新增实例");
667             sb.AppendLine("\t/// </summary>");
668             sb.Append("\t/// <param name=\"").Append(lowClassName).AppendLine("\">实例</param>");
669             sb.AppendLine("\t/// <returns>是否新增成功</returns>");
670             sb.Append("\tpublic bool Insert").Append(newClassName).Append("(").Append(newClassName).Append(" ").Append(lowClassName).AppendLine(")");
671             sb.AppendLine("\t{");
672             sb.Append("\t    int i = ").Append(lowClassName).Append("Dal.Insert").Append(newClassName).Append("(").Append(lowClassName).AppendLine(");");
673             sb.AppendLine("\t    return i > 0;");
674             sb.AppendLine("\t}"); 
675             #endregion
676 
677             #region 更新实例
678             #region 冗余
679             ///// <summary>
680             ///// 更新用户
681             ///// </summary>
682             ///// <param name="user">用户实例</param>
683             ///// <returns>师傅更新成功</returns>
684             //public bool UpdateUserInfo(UserInfo user)
685             //{
686             //    int i = userDal.UpdateUserInfo(user);
687             //    return i > 0;
688             //} 
689             #endregion
690             sb.AppendLine();
691             sb.AppendLine("\t/// <summary>");
692             sb.AppendLine("\t/// 更新实例");
693             sb.AppendLine("\t/// </summary>");
694             sb.Append("\t/// <param name=\"").Append(lowClassName).AppendLine("\">实例</param>");
695             sb.AppendLine("\t/// <returns>是否更新成功</returns>");
696             sb.Append("\tpublic bool Update").Append(newClassName).Append("(").Append(newClassName).Append(" ").Append(lowClassName).AppendLine(")");
697             sb.AppendLine("\t{");
698             sb.Append("\t    int i = ").Append(lowClassName).Append("Dal.Update").Append(newClassName).Append("(").Append(lowClassName).AppendLine(");");
699             sb.AppendLine("\t    return i > 0;");
700             sb.AppendLine("\t}"); 
701             #endregion
702 
703             sb.AppendLine("    }");
704             sb.AppendLine("}");
705             txt.Text = sb.ToString();
706         }
707 
708         /// <summary>
709         /// 点击创建Model
710         /// </summary>
711         /// <param name="sender"></param>
712         /// <param name="e"></param>
713         private void tsmiModel_Click(object sender, EventArgs e)
714         {
715             string tabelName, newClassName, nameSpaceProfix;
716             GetTableNameAndClassNameAndSpcaeName(out tabelName, out newClassName, out nameSpaceProfix);
717             string columnNameWithConsListStr, paraListStr, rowtomodelStr, columnNameWithEqualListStr, modelStr;
718             GetColumnListStrAndParaListStr(tabelName, newClassName, out columnNameWithConsListStr, out columnNameWithEqualListStr, out paraListStr, out rowtomodelStr, out modelStr);
719             #region 冗余
720         //using System;
721 //using System.Collections.Generic;
722 //using System.Linq;
723 //using System.Text;
724 //using System.Threading.Tasks; 
725     #endregion
726             StringBuilder sb = new StringBuilder();
727             sb.AppendLine("using System;");
728             sb.AppendLine("using System.Collections.Generic;");
729             sb.AppendLine("using System.Linq;");
730             sb.AppendLine("using System.Text;");
731             sb.AppendLine("using System.Threading.Tasks;");
732             #region 冗余
733             //namespace WinForm_UserInfo.Model
734             //{
735             //    public class UserInfo
736             //    {
737             //        //Id UserName Pwd Mobile Email Address Gender
738             //        public int Id { get; set; }
739             //        public string UserName { get; set; }
740             //        public string Pwd { get; set; }
741             //        public string Mobile { get; set; }
742             //        public string Email { get; set; }
743             //        public string Address { get; set; }
744             //        public int Gender { get; set; }
745             //    }
746             //} 
747             #endregion
748             sb.AppendLine();
749             sb.Append("namespace ").Append(nameSpaceProfix).AppendLine(".Model");
750             sb.AppendLine("{");
751             sb.Append("    public class ").AppendLine(newClassName);
752             sb.AppendLine("    {");
753             sb.AppendLine(modelStr);
754             sb.AppendLine("    }");
755             sb.AppendLine("}");
756             txt.Text = sb.ToString();
757         }
758     }
759 }
完整三层源代码

 

posted on 2015-08-21 11:55  AdolphYang  阅读(469)  评论(0编辑  收藏  举报