时间：2021-07-01 10:21:17 帮助过：9人阅读
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 {
