小谈代码自动生成工具（sqlserver）

前两天写了个代码自动生成工具，感觉从构思到实现应该没啥大的问题，现在分享给大家。

构思 一步步来：

首先 代码生成工具基本上都是生成相似东西的，比如实体类以及根据实体类产生相关代码，物以类聚，就先说如何根据表生成实体类吧，后面想生成啥都成。

先分解类：简单的是类名以及类属性。

如何根据表生成对应的类呢？ 最简单的是表名 --》 类名  ；表的所有列--》类的属性   。那就开始找吧！

  1. 首先连接数据库
  2. 查找系统视图找寻表名：  select * from sys.all_objects  where type in ('U')
  3. 查找表的所有列　　

  • select sys.all_columns.name,sys.types.name from sys.all_columns                  --所有列
    inner join sys.all_objects on sys.all_objects.object_id=sys.all_columns.object_id --所有表
    inner join sys.types on sys.types.user_type_id=sys.all_columns.user_type_id       --所有类型
    where sys.all_objects.name='TableName'

     悄悄插一下这个视图其实什么都有 [INFORMATION_SCHEMA].[COLUMNS] 我们可以翻看下 数据库--》视图--》系统视图 有很多都有我们想用的数据，实现不止一个！

　　4.拼接字符串。

分析应该ok了，下面就着手做吧！

 

 

 

 1 DBHelper dbhelper = new DBHelper();
 2         public Form1()
 3         {
 4             InitializeComponent();
 5             
 6             List<string> dbTables = dbhelper.GetDBTalbles();
 7             cbTableNames.DataSource = dbTables;
 8             cbTableNames.DisplayMember = "string";
 9            // cbTableNames.ValueMember = "string";
10         }
11 
12         private void ultraButton1_Click(object sender, EventArgs e)
13         {
14             string tableName = cbTableNames.SelectedValue.ToString();
15             string entityName = txtEntityName.Text;
16             txtEntityCode.Text = dbhelper.GetEntityString(tableName, entityName);
17             txtXMLCode.Text = dbhelper.GetXMLString(tableName);
18         }
Form的代码
  1 class DBHelper
  2     {
  3         const string ConfigConnName = "DbString";
  4         string dbString;
  5         SqlConnection sqlConn;
  6         SqlCommand sqlCMD;
  7         public DBHelper() //偷懒了下，初始化的时候直接生成了个SQLConnection
  8         {
  9             dbString=ConfigurationManager.ConnectionStrings[ConfigConnName].ConnectionString;
 10             sqlConn = new SqlConnection(dbString);
 11         }
 12         //按照分析 获取用户自定义的表名
 13         public List<string> GetDBTalbles()
 14         {
 15             sqlCMD = new SqlCommand();
 16             sqlCMD.CommandText = "select * from sys.objects where type='U'";
 17             sqlCMD.Connection = sqlConn;
 18             SqlDataReader sqlReader;
 19             List<string> result = new List<string>();
 20             try
 21             {
 22                 sqlConn.Open();
 23                 sqlReader = sqlCMD.ExecuteReader();
 24 
 25                 while (sqlReader.Read())
 26                 {
 27                     result.Add(sqlReader["name"].ToString());
 28                 }
 29                 sqlReader.Close();
 30             }
 31             catch (Exception)
 32             {
 33 
 34                 throw;
 35             }
 36             finally
 37             {    
 38                     
 39                 if (sqlConn.State != ConnectionState.Open)
 40                 {
 41                     sqlConn.Close();
 42                 }
 43                 
 44             }
 45             return result;
 46         }
 47         // 这个是根据表名 获取所有列，基本上我们仅关注 列名及列类型 其他的想+的话 还是可以+的
 48         public Dictionary<string, string> GetDBColumnsByTableName(string tableName)
 49         {
 50             Dictionary<string, string> result = new Dictionary<string, string>();
 51             sqlCMD = new SqlCommand("SELECT A.name,c.name FROM sys.all_columns A   INNER JOIN sys.objects B ON A.object_id=B.object_id inner join sys.systypes c on A.user_type_id=c.xusertype WHERE B.name=@TableName", sqlConn);
 52             sqlCMD.Parameters.Add(new SqlParameter("TableName", tableName));
 53 
 54             try
 55             {
 56                 if (sqlConn.State != ConnectionState.Open)
 57                 {
 58                     sqlConn.Open();
 59                 }
 60                 SqlDataReader sqlReader = sqlCMD.ExecuteReader();
 61                 while (sqlReader.Read())
 62                 {
 63                     result.Add(sqlReader[0].ToString(), sqlReader[1].ToString());
 64                 }
 65 
 66             }
 67             catch (Exception ex)
 68             {
 69 
 70                 throw;
 71             }
 72             finally
 73             {
 74                 if (sqlConn.State == ConnectionState.Open)
 75                 {
 76                     sqlConn.Close();
 77                 }
 78             }
 79             return result;
 80 
 81         }
 82         //关键时刻 生成实体类 多描述些
 83         public string GetEntityString(string tableName,string entityName)
 84         {
 85 
 86             string result="";
 87             if (string.IsNullOrEmpty(entityName))
 88             {
 89                 entityName = tableName;
 90             }
 91             StringBuilder strb = new StringBuilder();//可能 多次变更 用stringbuilder 比较好些（个人爱好） 也可以string
 92             strb.Append("///Please  Add using System.Runtime.Serialization; \n [DataContract]\n"); //加入了可序列号 不想加 也可以取消
 93             strb.Append("public class " + entityName + "{\n"); //类头 
 94 
 95             Dictionary<string, string> dicColumn = GetDBColumnsByTableName(tableName);
 96              
 97             foreach (var o in dicColumn)
 98             {
 99                 strb.Append("private " + SqlTypeName2DotNetType(o.Value) + " _" + o.Key + ";\n");//给类加了个类的field
100                 strb.Append("[DataMember]\n"); //上面提到了的序列号特性
101                 //多了个类型转换  SqlTypeName2DotNetType
102                 strb.Append("public " + SqlTypeName2DotNetType(o.Value) + " " + o.Key + "{\nget {return _"+o.Key+";}\nset {_"+ o.Key+"=value;}\n}\n");
103             }
104             strb.Append("}\n\r");//好了，类ok 了，可以返回啦！
105             result = strb.ToString();  
106              
107 
108             return result;
109         }
110 
111         public string GetXMLString(string tableName)//这个是生成实体对应的xml 为了后面做ORM 准备的，可以不看滴
112         {
113             string result = "";
114             XmlDocument xmlDoc = new XmlDocument();
115             //创建一个XML文档声明，并添加到文档  
116             XmlDeclaration declare = xmlDoc.CreateXmlDeclaration("1.0", "utf-8",  "yes");
117             xmlDoc.AppendChild(declare);
118             //创建并添加UserList结点  
119             XmlElement entity = xmlDoc.CreateElement("Entity");
120             xmlDoc.AppendChild(entity);
121 
122             XmlElement sqls = xmlDoc.CreateElement("Sqls");
123             entity.AppendChild(sqls);
124 
125             
126 
127             Dictionary<string, string> dicColumn = GetDBColumnsByTableName(tableName);
128             #region GetObjects 
129             XmlElement sql = xmlDoc.CreateElement("Sql");
130             sqls.AppendChild(sql);
131             XmlAttribute sqlName = xmlDoc.CreateAttribute("Name");
132             sqlName.InnerText = "GetObjects";
133 
134             sql.Attributes.Append(sqlName);
135 
136             XmlElement sqlString = xmlDoc.CreateElement("SqlString");
137             sql.AppendChild(sqlString);
138 
139             XmlElement str = xmlDoc.CreateElement("String");
140             sqlString.AppendChild(str);
141 
142             string getObj = "select ";
143             foreach (var o in dicColumn)
144             {
145                 getObj += o.Key +" ,";
146             }
147             getObj=getObj.TrimEnd(',',' ');
148             getObj += " from " + tableName + " Where @Where;";
149 
150             str.InnerText = getObj;
151 
152             // Name = "@Where" Column = "Where" TypeName = "System.String" 
153             XmlElement param = xmlDoc.CreateElement("Param");
154             param.SetAttribute("Name", "@Where");
155             param.SetAttribute("Column", "Where");
156             param.SetAttribute("TypeName", "System.String");
157             sqlString.AppendChild(param);
158             #endregion
159             result = xmlDoc.OuterXml; 
160             return result;
161         }
162 
163         public void AddChildNOde(XmlDocument xmldoc,XmlElement xmlNode, string childNodeName)
164         {
165             XmlNode childNode = xmldoc.CreateElement(childNodeName);
166             xmlNode.AppendChild(childNode);
167         }
168         /// <summary> 
169         /// 将SQLServer数据类型（如：varchar）转换为.Net类型（如：String） 
170         /// </summary> 
171         /// <param name="sqlTypeString">SQLServer数据类型</param> 
172         /// <returns></returns> 
173         public  string SqlTypeName2DotNetType(string sqlTypeString)
174         {
175             string[] SqlTypeNames = new string[] { "int", "varchar","bit" ,"datetime","decimal","float","image","money",
176    "ntext","nvarchar","smalldatetime","smallint","text","bigint","binary","char","nchar","numeric",
177    "real","smallmoney", "sql_variant","timestamp","tinyint","uniqueidentifier","varbinary"};
178 
179             string[] DotNetTypes = new string[] {"int", "string","bool" ,"DateTime","Decimal","Double","Byte[]","Single",
180    "string","string","DateTime","Int16","string","Int64","Byte[]","string","string","Decimal",
181    "Single","Single", "Object","Byte[]","Byte","Guid","Byte[]"};
182 
183             int i = Array.IndexOf(SqlTypeNames, sqlTypeString.ToLower());
184             return DotNetTypes[i];
185         }
186     }
核心代码

有什么不对的地方，可以给我指点下，共同进步！

posted @ 2013-11-15 22:46  HansonYao  阅读(253)  评论(0)    收藏  举报