生成数据库表对应的实体

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.IO;

namespace CodeGenerator
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        /// <summary>
        /// 执行ExecuteDataTable()，得到DataTable
        /// </summary>
        /// <param name="cmdText"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public  DataTable ExecuteDataTable(string cmdText,
            params SqlParameter[] parameters)
        {
            using (SqlConnection conn=new SqlConnection(txtConnStr.Text))
            {
                conn.Open();
                using(SqlCommand cmd=conn.CreateCommand())
                {
                    cmd.CommandText = cmdText;
                    cmd.Parameters.AddRange(parameters);
                    using (SqlDataAdapter adapter=new SqlDataAdapter (cmd))
                    {
                        DataTable dt = new DataTable();
                        adapter.Fill(dt);
                        return dt;
                    }
                }
            }

        }

        private void Form1_Load(object sender, EventArgs e)
        {
            txtConnStr.Text = @"Data Source=EYESSQLEXPRESS;Initial Catalog=SanCeng;Integrated Security=True";
        }

        private void btnConnStr_Click(object sender, EventArgs e)
        {

            //清空
            clbTables.Items.Clear();
            //查询系统试图
            string sql = "select * from INFORMATION_SCHEMA.TABLES";
            DataTable dt = ExecuteDataTable(sql);
            //根据系统视图取得TABLE_NAME
            foreach (DataRow row in dt.Rows)
            {
                string tablename = Convert.ToString(row["TABLE_NAME"]);
                clbTables.Items.Add(tablename);
            }

        }

        private void btnGo_Click(object sender, EventArgs e)
        {
            //连接字符串
            //方法AppendLine()追加字符串且自动执行换行

            foreach (string tableName in clbTables.CheckedItems)
            {
                string sql = "select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME=@TABLE_NAME ";
                DataTable dt = ExecuteDataTable(sql,new SqlParameter("TABLE_NAME",tableName));

                #region  生成Model
                CreatModel(tableName, dt);
                #endregion

                #region 生成DAL
          

                CreatDAL(tableName, dt);
                #endregion
                #region 生成BLL
                CreatBLL(tableName, dt);
                #endregion
            }
        }

        private static void CreatDAL(string tableName, DataTable dt)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("using System;");
            sb.AppendLine("using System.Collections.Generic;");
            sb.AppendLine("using System.Linq;");
            sb.AppendLine("using System.Text;");
            sb.AppendLine("using 三层架构Demo.Model;");
            sb.AppendLine("using System.Data.SqlClient;");
            sb.AppendLine("using System.Data;");
            sb.AppendLine("namespace 三层架构Demo.DAL");
            sb.AppendLine("{");
            sb.AppendLine("class " + tableName + "DAL");
            sb.AppendLine("{");
            //去掉Id
            sb.AppendLine("    public int Addnew(" + tableName + " model)");
            sb.AppendLine("{");
            List<String> cols = new List<string>();
            List<String> parameters = new List<string>();
            foreach (DataRow row in dt.Rows)
            {
                string col = Convert.ToString(row["COLUMN_NAME"]);
                string parameter = "";
                if (col.ToLower()!="id")
                {
                        parameter= "@" + Convert.ToString(row["COLUMN_NAME"]);
                        cols.Add(col);
                        parameters.Add(parameter);
                }
                //parameters.Add(parameter)放外面加上一个NULL，所以会多出一个逗号
               // parameters.Add(parameter);
                
            }

            sb.AppendLine("string sql = "insert into " + tableName + "(" + String.Join(",", cols) + ") output inserted.Id values(" + String.Join(",", parameters) + ")";");
            sb.AppendLine("object obj= SQLHelper.ExecuteScalar(sql");

            foreach (DataRow row in dt.Rows)
            {
                string col = Convert.ToString(row["COLUMN_NAME"]);
                if (col.ToLower() != "id")
                {
                    sb.AppendLine(",new SqlParameter("" + col + "",model." + col + ")");
                }
                
            }
            sb.AppendLine(");");
            sb.AppendLine("return Convert.ToInt32(obj);");
            sb.AppendLine("}");
            //Delete方法
          
            sb.AppendLine("   public int Delete(int id)");
            sb.AppendLine("{");
            sb.AppendLine(" string sql = "delete from " + tableName + " where Id=@Id";");
            sb.AppendLine("return SQLHelper.ExecuteNonQuery(sql,new SqlParameter("Id",id));");
            sb.AppendLine("}");

            //Update方法
            sb.AppendLine("public int Update("+tableName+" model)");
            sb.AppendLine("{");
            string[] uParams1=(from col in cols select col+"=@"+col).ToArray();
         
            sb.AppendLine(" string sql = "update "+tableName+" set "+String.Join(",",uParams1)+" where Id=@Id";");

            string[] uParams2 = (from col in cols select  "new SqlParameter("" + col + "",model." + col + ")").ToArray();
            sb.AppendLine("  return SQLHelper.ExecuteNonQuery(sql, " + String.Join(",", uParams2) + " ,new SqlParameter("Id",model.Id));");
            sb.AppendLine("}");
          
            //GetId方法
            sb.AppendLine(" public "+tableName+" Get(int id)");
            sb.AppendLine("{");
            sb.AppendLine("string sql="select * from "+tableName+" where Id=@Id";");
            sb.AppendLine(" DataTable dt=SQLHelper.ExecuteDataTable(sql,new SqlParameter("Id",id));");
            sb.AppendLine("if (dt.Rows.Count<=0)");
            sb.AppendLine("{");
            sb.AppendLine(" return null;");
            sb.AppendLine("}");
            sb.AppendLine("    else if (dt.Rows.Count==1)");
            sb.AppendLine("{");
            sb.AppendLine(""+tableName+" model1 = new "+tableName+"();");
            foreach (DataRow row in dt.Rows)
            {
                string col = Convert.ToString(row["COLUMN_NAME"]);
                string dataType = Convert.ToString(row["data_TYPe"]);
                sb.AppendLine("model1." + col + " = Convert." + Get(GetType(dataType).ToString()) + "(dt.Rows[0]["" + col + ""]);");
                
            }
            sb.AppendLine("return model1;");
            sb.AppendLine("}");
            sb.AppendLine("else");
            sb.AppendLine("{");
            sb.AppendLine("  throw new Exception("数据库中有两条及以上重复数据");");
            sb.AppendLine("}");
            sb.AppendLine("}");

            //IEnumerable()方法
            sb.AppendLine(" public IEnumerable<"+tableName+"> GetAll()");
            sb.AppendLine("{");
            sb.AppendLine("  string sql = "select * from "+tableName+"";");
            sb.AppendLine("DataTable dt = SQLHelper.ExecuteDataTable(sql);");
            sb.AppendLine("       List<"+tableName+"> list = new List<"+tableName+">();");
            sb.AppendLine(" foreach (DataRow row in dt.Rows)");
            sb.AppendLine("{");
            sb.AppendLine("" + tableName + " model = new " + tableName + "();");
            foreach (DataRow row in dt.Rows)
            {
                string col = Convert.ToString(row["COLUMN_NAME"]);
                string dataType = Convert.ToString(row["data_TYPE"]);
                sb.AppendLine("model." + col + " = Convert." + Get(GetType(dataType).ToString()) + "(row["" + col + ""]);");
                
            }
            sb.AppendLine("  list.Add(model);");
            sb.AppendLine("}");
            sb.AppendLine("return list;");
            sb.AppendLine("}");
            sb.AppendLine("}");
            sb.AppendLine("}");
            File.WriteAllText(@"d:"+tableName+"DAL.cs",sb.ToString());

            
        }
        /// <summary>
        /// 数据库类型转换为C#类型
        /// </summary>
        /// <param name="dataType"></param>
        /// <returns></returns>
        private static Type GetType(string dataType)
        {
            switch (dataType.ToLower())
            {
                case "nvarchar":
                case "varchar":
                case "nchar":
                case "char":
                    return typeof(string);
                case "int" :
                    return typeof(int);
                case "bigint":
                    return typeof(long);
                case "bit":
                    return typeof(bool);
                case "datetime":
                    return typeof(DateTime);
                default:
                    return typeof(object);
            }
        
        }

        private static string Get(string dataType)
        {
         
            switch (dataType.ToLower())
            {
                case "system.string":
                    return "ToString";
                case "system.int32":
                    return "ToInt32";
                case "system.int64":
                    return "ToInt64";
                case "system.datetime":
                    return "ToDateTime";
                case "system.boolean":
                    return "ToBoolean";
             
                default:
                    throw new Exception("找不到匹配的数据类型");
                    
            }
        }
        private static void CreatModel(string tableName, DataTable dt)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("using System;");
            sb.AppendLine("using System.Collections.Generic;");
            sb.AppendLine("using System.Linq;");
            sb.AppendLine("using System.Text;");
            sb.AppendLine("namespace 三层架构Demo.Model");
            sb.AppendLine("{");
            sb.AppendLine("");
            sb.AppendLine("class " + tableName);
            sb.AppendLine("{");

            foreach (DataRow row in dt.Rows)
            {
                string dataType = Convert.ToString(row["DATA_TYPE"]);
                
                string columnName = Convert.ToString(row["COLUMN_NAME"]);

                sb.AppendLine("public " + GetType(dataType) + " " + columnName + " { get;set;}");
            }
            sb.AppendLine("}");
            sb.AppendLine("}");
            File.WriteAllText(@"d:" + tableName + ".cs", sb.ToString());
            //MessageBox.Show(sb.ToString());
            
        }

        private static void CreatBLL(string tableName, DataTable dt)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("using System;");
            sb.AppendLine("using System.Collections.Generic;");
            sb.AppendLine("using System.Linq;");
            sb.AppendLine("using System.Text;");
            sb.AppendLine("using  三层架构Demo.Model;");
            sb.AppendLine("using 三层架构Demo.DAL;");
            sb.AppendLine("using System.Data.SqlClient;");
            sb.AppendLine("using System.Data;");
            sb.AppendLine("namespace 三层架构Demo.BLL");
            sb.AppendLine("{");
            sb.AppendLine("class " + tableName+"BLL");
            sb.AppendLine("{");
            sb.AppendLine("public int Addnew("+tableName+" model)");
            sb.AppendLine("{");
            sb.AppendLine(" return new "+tableName+"DAL().Addnew(model);");
            sb.AppendLine("}");
            sb.AppendLine("   public int Delete(int id)");
            sb.AppendLine("{");
            sb.AppendLine(" return new "+tableName+"DAL().Delete(id);");
            sb.AppendLine("}");
            sb.AppendLine(" public int Update("+tableName+" model)");
            sb.AppendLine("{");
            sb.AppendLine(" return new " + tableName + "DAL().Update(model);");
            sb.AppendLine("}");
            sb.AppendLine(" public "+tableName+" Get(int id)");
            sb.AppendLine("{");
            sb.AppendLine(" return new "+tableName+"DAL().Get(id);");
            sb.AppendLine("}");
            sb.AppendLine(" public IEnumerable<"+tableName+"> GetAll()");
            sb.AppendLine("{");
            sb.AppendLine("  return new "+tableName+"DAL().GetAll();");
            sb.AppendLine("}");
            sb.AppendLine("}");
            sb.AppendLine("}");
            File.WriteAllText(@"d:" + tableName + "BLL.cs", sb.ToString());
        }
    }
}
复制代码