c#生成数据库实体类

一个生成数据库实体类的代码
在项目中可以很容易生成数据库实体类,提高开发的效率

  1using System;
  2using System.Drawing;
  3using System.Collections;
  4using System.Collections.Specialized;
  5using System.ComponentModel;
  6using System.Windows.Forms;
  7using System.Data;
  8using System.IO;
  9using System.Text;
 10using System.Data.SqlClient;
 11using System.Data.SqlTypes;
 12
 13namespace WindowsApplication1
 14{
 15    /**//// <summary>
 16    /// Form1 的摘要说明。
 17    /// </summary>
 18    public class Form1 : System.Windows.Forms.Form
 19    {
 20        private System.Windows.Forms.ListBox listBox1;
 21        private System.Windows.Forms.Button button1;
 22        private System.Windows.Forms.ListView listView1;
 23        private System.Windows.Forms.Button button2;
 24        private System.Windows.Forms.ComboBox comboBox1;
 25        private System.Windows.Forms.TextBox textBox1;
 26        private System.Windows.Forms.FolderBrowserDialog folderBrowserDialog1;
 27        private System.Windows.Forms.Label label1;
 28        private System.Windows.Forms.ComboBox comboBox2;
 29        private System.Windows.Forms.Label label2;
 30        private System.Windows.Forms.Label label3;
 31        
 32        /**//// <summary>
 33        /// 必需的设计器变量。
 34        /// </summary>
 35        private System.ComponentModel.Container components = null;
 36
 37        public Form1()
 38        {
 39            //
 40            // Windows 窗体设计器支持所必需的
 41            //
 42            InitializeComponent();
 43            this.MaximizeBox = false;
 44            this.Text = "Create Tabel Class Tools";
 45            this.button1.Text = "Import";
 46            this.button2.Text = "Create";
 47            listView1.View = View.Details;
 48            listView1.GridLines = true;
 49            listView1.Columns.Add("NO", -2, HorizontalAlignment.Left);
 50            listView1.Columns.Add("ColumnName", -2, HorizontalAlignment.Left);
 51            listView1.Columns.Add("Type", -2, HorizontalAlignment.Left);
 52            listView1.Columns.Add("Length", -2, HorizontalAlignment.Left);
 53            this.comboBox2.Items.Add("local");
 54            this.comboBox2.Text = "local";
 55            SQLDMO.NameList strServerNames;
 56            SQLDMO.ApplicationClass sqldmoApp = new SQLDMO.ApplicationClass();
 57            try
 58            {
 59                strServerNames = sqldmoApp.ListAvailableSQLServers();
 60                for(int i= 0 ;i<strServerNames.Count;i++)
 61                    this.comboBox2.Items.Add(strServerNames.Item(i));
 62            }
 63            catch
 64            {
 65                MessageBox.Show("Can not connect to other services");
 66            }
 67            finally
 68            {
 69            sqldmoApp.Quit();
 70            }
 71            
 72            ComBoxListDataLoad(this.comboBox2.Text);
 73            this.textBox1.Text = string.Empty;
 74            this.label1.Text = string.Empty;
 75            this.label2.Text = "Servers:";
 76            this.label3.Text = "DataBase:";
 77            this.label1.AutoSize = true;
 78            this.label2.AutoSize = true;
 79            this.label3.AutoSize = true;
 80
 81            //
 82            // TODO: 在 InitializeComponent 调用后添加任何构造函数代码
 83            //
 84        }
 85
 86        /**//// <summary>
 87        /// 清理所有正在使用的资源。
 88        /// </summary>
 89        protected override void Dispose( bool disposing )
 90        {
 91            if( disposing )
 92            {
 93                if (components != null) 
 94                {
 95                    components.Dispose();
 96                }
 97            }
 98            base.Dispose( disposing );
 99        }
100
101        Windows 窗体设计器生成的代码#region Windows 窗体设计器生成的代码
102        /**//// <summary>
103        /// 设计器支持所需的方法 - 不要使用代码编辑器修改
104        /// 此方法的内容。
105        /// </summary>
106        private void InitializeComponent()
107        {
108            this.listBox1 = new System.Windows.Forms.ListBox();
109            this.button1 = new System.Windows.Forms.Button();
110            this.listView1 = new System.Windows.Forms.ListView();
111            this.button2 = new System.Windows.Forms.Button();
112            this.comboBox1 = new System.Windows.Forms.ComboBox();
113            this.textBox1 = new System.Windows.Forms.TextBox();
114            this.folderBrowserDialog1 = new System.Windows.Forms.FolderBrowserDialog();
115            this.label1 = new System.Windows.Forms.Label();
116            this.comboBox2 = new System.Windows.Forms.ComboBox();
117            this.label2 = new System.Windows.Forms.Label();
118            this.label3 = new System.Windows.Forms.Label();
119            this.SuspendLayout();
120            // 
121            // listBox1
122            // 
123            this.listBox1.ItemHeight = 12;
124            this.listBox1.Location = new System.Drawing.Point(8, 40);
125            this.listBox1.Name = "listBox1";
126            this.listBox1.Size = new System.Drawing.Size(128, 208);
127            this.listBox1.TabIndex = 0;
128            this.listBox1.SelectedIndexChanged += new System.EventHandler(this.listBox1_SelectedIndexChanged);
129            // 
130            // button1
131            // 
132            this.button1.Location = new System.Drawing.Point(424, 8);
133            this.button1.Name = "button1";
134            this.button1.Size = new System.Drawing.Size(64, 23);
135            this.button1.TabIndex = 1;
136            this.button1.Text = "button1";
137            this.button1.Click += new System.EventHandler(this.button1_Click);
138            // 
139            // listView1
140            // 
141            this.listView1.Location = new System.Drawing.Point(144, 40);
142            this.listView1.Name = "listView1";
143            this.listView1.Size = new System.Drawing.Size(376, 240);
144            this.listView1.TabIndex = 3;
145            this.listView1.ColumnClick += new System.Windows.Forms.ColumnClickEventHandler(this.listView1_ColumnClick);
146            // 
147            // button2
148            // 
149            this.button2.Location = new System.Drawing.Point(8, 256);
150            this.button2.Name = "button2";
151            this.button2.Size = new System.Drawing.Size(56, 23);
152            this.button2.TabIndex = 4;
153            this.button2.Text = "button2";
154            this.button2.Click += new System.EventHandler(this.button2_Click);
155            // 
156            // comboBox1
157            // 
158            this.comboBox1.Location = new System.Drawing.Point(288, 8);
159            this.comboBox1.Name = "comboBox1";
160            this.comboBox1.Size = new System.Drawing.Size(128, 20);
161            this.comboBox1.TabIndex = 5;
162            this.comboBox1.Text = "comboBox1";
163            // 
164            // textBox1
165            // 
166            this.textBox1.Location = new System.Drawing.Point(8, 312);
167            this.textBox1.Multiline = true;
168            this.textBox1.Name = "textBox1";
169            this.textBox1.ScrollBars = System.Windows.Forms.ScrollBars.Both;
170            this.textBox1.Size = new System.Drawing.Size(512, 136);
171            this.textBox1.TabIndex = 6;
172            this.textBox1.Text = "textBox1";
173            // 
174            // label1
175            // 
176            this.label1.Location = new System.Drawing.Point(8, 288);
177            this.label1.Name = "label1";
178            this.label1.Size = new System.Drawing.Size(512, 23);
179            this.label1.TabIndex = 7;
180            this.label1.Text = "label1";
181            // 
182            // comboBox2
183            // 
184            this.comboBox2.Location = new System.Drawing.Point(80, 8);
185            this.comboBox2.Name = "comboBox2";
186            this.comboBox2.Size = new System.Drawing.Size(121, 20);
187            this.comboBox2.TabIndex = 8;
188            this.comboBox2.Text = "comboBox2";
189            // 
190            // label2
191            // 
192            this.label2.Location = new System.Drawing.Point(8, 8);
193            this.label2.Name = "label2";
194            this.label2.Size = new System.Drawing.Size(48, 23);
195            this.label2.TabIndex = 9;
196            this.label2.Text = "label2";
197            // 
198            // label3
199            // 
200            this.label3.Location = new System.Drawing.Point(216, 8);
201            this.label3.Name = "label3";
202            this.label3.Size = new System.Drawing.Size(48, 23);
203            this.label3.TabIndex = 10;
204            this.label3.Text = "label3";
205            // 
206            // Form1
207            // 
208            this.AutoScaleBaseSize = new System.Drawing.Size(6, 14);
209            this.ClientSize = new System.Drawing.Size(552, 453);
210            this.Controls.Add(this.label3);
211            this.Controls.Add(this.label2);
212            this.Controls.Add(this.comboBox2);
213            this.Controls.Add(this.label1);
214            this.Controls.Add(this.textBox1);
215            this.Controls.Add(this.comboBox1);
216            this.Controls.Add(this.button2);
217            this.Controls.Add(this.listView1);
218            this.Controls.Add(this.button1);
219            this.Controls.Add(this.listBox1);
220            this.Name = "Form1";
221            this.Text = "Form1";
222            this.Load += new System.EventHandler(this.Form1_Load);
223            this.ResumeLayout(false);
224
225        }
226        #endregion
227
228        /**//// <summary>
229        /// 应用程序的主入口点。
230        /// </summary>
231        [STAThread]
232        static void Main() 
233        {
234            Application.Run(new Form1());
235        }
236
237        public void SqlConn()
238        {
239            SqlConnection v_SqlConnection = new SqlConnection(ConnectString());
240           
241            try
242            {
243                v_SqlConnection.Open();
244                string strSql = "SELECT name AS tabelName FROM dbo.sysobjects WHERE (type = 'U') AND (name <> N'dtproperties') Order By tabelName"; 
245                SqlCommand v_SqlCommand = new SqlCommand(strSql,v_SqlConnection);
246                SqlDataReader v_SqlDataReader =v_SqlCommand.ExecuteReader();
247                while(v_SqlDataReader.Read())
248                {
249                    this.listBox1.Items.Add(v_SqlDataReader["tabelName"].ToString());
250                }
251            }
252            catch(SqlException se)
253            {
254                MessageBox.Show(se.ToString());
255            }
256            finally
257            {
258                v_SqlConnection.Close(); 
259            }
260        }
261
262        public string ConnectString()
263        {
264            return  string.Format("Integrated Security=SSPI;Initial Catalog={0};Data Source=({1})",this.comboBox1.SelectedItem,this.comboBox2.Text);
265        }
266        private void Form1_Load(object sender, System.EventArgs e)
267        {
268        }
269
270        private void button1_Click(object sender, System.EventArgs e)
271        {
272            this.listBox1.Items.Clear();  
273            SqlConn();
274        }
275
276        private void listBox1_SelectedIndexChanged(object sender, System.EventArgs e)
277        {
278            SqlConnection v_SqlConnection = new SqlConnection(ConnectString());
279            try
280            {
281                v_SqlConnection.Open();
282                string strSql = string.Format("SELECT dbo.syscolumns.name AS ColumnName, dbo.systypes.name AS TypeName, dbo.syscolumns.length FROM dbo.syscolumns INNER JOIN dbo.sysobjects ON dbo.syscolumns.id = dbo.sysobjects.id INNER JOIN dbo.systypes ON dbo.syscolumns.xtype = dbo.systypes.xtype WHERE (dbo.sysobjects.name = '{0}') and (dbo.systypes.name <> N'sysname') Order By ColumnName",this.listBox1.SelectedItem); 
283                SqlCommand v_SqlCommand = new SqlCommand(strSql,v_SqlConnection);
284                SqlDataReader v_SqlDataReader =v_SqlCommand.ExecuteReader();
285                int i= 0;
286                this.listView1.Items.Clear();
287                while(v_SqlDataReader.Read())
288                {
289                    ListViewItem v_ListViewItem = new ListViewItem();
290                    v_ListViewItem.SubItems[0].Text = i.ToString();
291                    v_ListViewItem.SubItems.Add(v_SqlDataReader[0].ToString());
292                    v_ListViewItem.SubItems.Add(v_SqlDataReader[1].ToString());
293                    v_ListViewItem.SubItems.Add(v_SqlDataReader[2].ToString());
294                    this.listView1.Items.Add(v_ListViewItem);
295                    i++;
296                }
297            }
298            catch(SqlException se)
299            {
300                MessageBox.Show(se.ToString());
301            }
302            finally
303            {
304                v_SqlConnection.Close(); 
305            }
306        }
307
308        private void button2_Click(object sender, System.EventArgs e)
309        {
310            
311            if(this.listBox1.SelectedItem == null)
312            {
313                MessageBox.Show("Please select one table,then try again","Opreat wrong");
314                return ;
315            }
316            this.folderBrowserDialog1.ShowDialog();
317            this.label1.Text = folderBrowserDialog1.SelectedPath +"\\" +  this.listBox1.SelectedItem.ToString() + ".cs";
318           
319            StreamWriter v_StreamWriter = new StreamWriter(folderBrowserDialog1.SelectedPath +"\\" +  this.listBox1.SelectedItem.ToString() + ".cs",false,Encoding.Unicode);
320            
321            string strText = "using System;\r\n using System.Data;\r\n namespace BE \r\n{ public class " + this.listBox1.SelectedItem + "\r\n {" ;       
322           
323            strText += "public " + this.listBox1.SelectedItem + "(){} \r\n";
324            foreach(ListViewItem v_ListViewItem in this.listView1.Items)
325            {
326                strText += "private " + StrSqlType(v_ListViewItem.SubItems[2].Text) + " _" + v_ListViewItem.SubItems[1].Text + ";\r\n";
327            }
328            strText += "\r\n\r\n";
329
330            foreach(ListViewItem v_ListViewItem in this.listView1.Items)
331            {
332                strText += "/// <summary> \r\n" + "///"+ v_ListViewItem.SubItems[1].Text  + " " +  v_ListViewItem.SubItems[2].Text + "\r\n/// </summary>\r\n";
333                strText += "public " + StrSqlType(v_ListViewItem.SubItems[2].Text)  + " " + v_ListViewItem.SubItems[1].Text + "\r\n{ get{return _" + v_ListViewItem.SubItems[1].Text +";}\r\n" + "set{ _" + v_ListViewItem.SubItems[1].Text +  "=value; }}\r\n" ;
334                strText += "\r\n";
335            }
336
337            strText += "}\r\n}";
338            v_StreamWriter.Write(strText);
339            v_StreamWriter.Close();
340            this.textBox1.Text = strText;
341        }
342        public string StrSqlType(string strType)
343        {
344            switch (strType)
345            {
346                case "bigint":
347                    return "long";
348                case "int ":
349                    return "int";
350                case "smallint":
351                    return "short";
352                case "tinyint":
353                    return "short";
354                case "bit":
355                    return "boolean";
356                case "decimal":
357                    return "decimal";
358                case "numeric":
359                    return "decimal";
360                case "money":
361                    return "string";
362                case "smallmoney":
363                    return "string";
364                case "float":
365                    return "float";
366                case "real":
367                    return "float";
368                case "datetime":
369                    return "DateTime";
370                case "smalldatetime":
371                    return "DateTime";
372                case "char":
373                    return "char";
374                case "varchar":
375                    return "string";
376                case "text":
377                    return "string";
378                case "nchar":
379                    return "string";
380                case "nvarchar":
381                    return "string";
382                case "ntext":
383                    return "string";
384                case "binary":
385                    return "byte";
386                case "varbinary":
387                    return "byte";
388                case "image":
389                    return "string";
390                case "uniqueidentifier":
391                    return "Guid";
392                default :
393                    return "string";
394            }
395        }
396
397        public void ComBoxListDataLoad(string strServerName)
398        {
399            SqlConnection v_SqlConnection = new SqlConnection(string.Format("Integrated Security=SSPI;Initial Catalog=Master;Data Source=({0})",strServerName));
400            try
401            {
402                v_SqlConnection.Open();
403                string strSql = "SELECT TOP 100 PERCENT name AS DBName FROM dbo.sysdatabases ORDER BY name DESC"; 
404                SqlCommand v_SqlCommand = new SqlCommand(strSql,v_SqlConnection);
405                SqlDataReader v_SqlDataReader =v_SqlCommand.ExecuteReader();
406                this.comboBox1.Text = "";
407                while(v_SqlDataReader.Read())
408                {
409                    this.comboBox1.Items.Add(v_SqlDataReader["DBName"]);
410                }
411            }
412            catch(SqlException se)
413            {
414                MessageBox.Show(se.ToString());
415            }
416            finally
417            {
418                v_SqlConnection.Close(); 
419            }
420        
421        }
422
423        private void listView1_ColumnClick(object sender, System.Windows.Forms.ColumnClickEventArgs e)
424        {
425            this.listView1.Sort();
426        }
427    }
428}
429