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 {
