public class SqlHelp    {        // 在配置文件获得连接字符串        private static string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;        #region 执行sql语句(update,insert,delete)+ExecuteNonQuery(string sql, params SqlParameter[] paras)        /// <summary>        /// 执行sql语句(update,insert,delete)        /// </summary>        /// <param name="sql">sql语句</param>        /// <param name="paras">可变参数</param>        /// <returns>返回影响的行数</returns>        public static int ExecuteNonQuery(string sql, params SqlParameter[] paras)        {            using (SqlConnection conn = new SqlConnection(connStr))            {                conn.Open();                using (SqlCommand command = conn.CreateCommand())                {                    command.CommandText = sql;                    command.Parameters.AddRange(paras);                    return command.ExecuteNonQuery();                }            }        }         #endregion        #region 执行sql的函数语句(count，sum 等)+ExecuteScalar(string sql, params SqlParameter[] paras)        /// <summary>        /// 执行sql的函数语句(count，sum 等)        /// </summary>        /// <param name="sql">sql语句</param>        /// <param name="paras">可变参数</param>        /// <returns>返回函数值</returns>        public static object ExecuteScalar(string sql, params SqlParameter[] paras)        {            using (SqlConnection conn = new SqlConnection(connStr))            {                conn.Open();                using (SqlCommand command = conn.CreateCommand())                {                    command.CommandText = sql;                    command.Parameters.AddRange(paras);                    return command.ExecuteScalar();                }            }        }        #endregion        #region 执行sql的select语句+ExecuteTable(string sql, params SqlParameter[] paras)        /// <summary>        /// 执行sql的select语句        /// </summary>        /// <param name="sql">sql语句</param>        /// <param name="paras">可变参数</param>        /// <returns>返回一张数据表</returns>        public static DataTable ExecuteTable(string sql, params SqlParameter[] paras)        {            using (SqlConnection conn = new SqlConnection(connStr))            {                conn.Open();                using (SqlCommand command = conn.CreateCommand())                {                    command.CommandText = sql;                    command.Parameters.AddRange(paras);                    SqlDataAdapter sda = new SqlDataAdapter(command);                    DataSet ds = new DataSet();                    sda.Fill(ds);                    return ds.Tables[0];                }            }        }        #endregion        /// <summary>        /// 数据库接受的null：当传入的数据位null时,转换成DBNull.Value(只有这样才能插入到数据库)        /// </summary>        /// <param name="obj"></param>        /// <returns></returns>        public static object toDBNull(object obj)        {            if (obj == null)            {                return DBNull.Value;            }            else            {                return obj;            }        }        /// <summary>        /// 把数据库的null值转换成程序中null值        /// </summary>        /// <param name="obj"></param>        /// <returns></returns>        public static object toNull(object obj)        {            if (obj == DBNull.Value)            {                return null;            }            else            {                return obj;            }        }}

 /// <summary>    /// MainWindow.xaml 的交互逻辑    /// </summary>    public partial class MainWindow : Window    {        public MainWindow()        {            InitializeComponent();        }
　　　　 //窗体加载事件
　　　　 private void Window_Loaded(object sender, RoutedEventArgs e)
        {
            TablecomboBox.IsEnabled = false;
            btnCreateCode.IsEnabled = false;
            string path = AppDomain.CurrentDomain.BaseDirectory;
            //把每次连接的字符串存储到1.txt
            string Newpath = System.IO.Path.Combine(path, "ConnStr.txt");
            txtConnstr.Text = File.ReadAllText(Newpath);
        }
　　　　　//点击连接按钮        private void btnConn_Click(object sender, RoutedEventArgs e)        {            TablecomboBox.IsEnabled = false;            btnCreateCode.IsEnabled = false;            DataTable datatable;            try            {                //获得选择数据库的所有表名                string sql = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE=‘BASE TABLE‘";                datatable = ResultDataTable(sql);            }            catch (SqlException se)            {                MessageBox.Show("数据库连接错误！" + se.Message);                return;            }            TablecomboBox.IsEnabled = true;            string[] tables = new string[datatable.Rows.Count];            for (int i = 0; i < datatable.Rows.Count; i++)            {                DataRow row = datatable.Rows[i];                tables[i] = (string)row["TABLE_NAME"];            }            //把表名放在下拉框中            TablecomboBox.ItemsSource = tables;            TablecomboBox.SelectedIndex = 0;            btnCreateCode.IsEnabled = true;            string path = AppDomain.CurrentDomain.BaseDirectory;            string Newpath = System.IO.Path.Combine(path, "ConnStr.txt");//这样拼接成的路径较为安全;            File.WriteAllText(Newpath, txtConnstr.Text);//把字符串存到根文件夹下面;        }　　　　　//返回数据库表
        public DataTable ResultDataTable(string sql, params SqlParameter[] parameters)
        {
            using (SqlConnection connstr = new SqlConnection(txtConnstr.Text))
            {
                //打开数据库连接
                connstr.Open();
                using (SqlCommand command = connstr.CreateCommand())
                {

                    command.CommandText = sql;
                    command.Parameters.AddRange(parameters);
                    SqlDataAdapter sda = new SqlDataAdapter(command);
                    DataSet ds = new DataSet();
                    //获取表中的全部信息(如填充每个表中的主键和外键等)
                    sda.FillSchema(ds, SchemaType.Source);
                    sda.Fill(ds);
                    return ds.Tables[0];
                }
            }
        }
　　　　　　//生成代码按钮事件        private void btnCreateCode_Click(object sender, RoutedEventArgs e)        {            if (TablecomboBox.SelectedIndex <0)            {                MessageBox.Show("请选择一个表");                return;            }            DataColumnCollection columns = Returncolumns(TablecomboBox.SelectedItem.ToString());            //生成实体类            CreateModelCode(columns);            //生成数据访问代码            CreateDALCode(columns);                    }
　　　　　　//生成实体类        public void CreateModelCode(DataColumnCollection columns)        {            StringBuilder sb = new StringBuilder();//用这种方式连接字符串效率更高;            sb.Append("public class ").Append(TablecomboBox.SelectedItem).AppendLine("\n{");            foreach (DataColumn column in columns)            {                sb.Append("    public ").Append(IsNull(column)).Append(" ").Append(column.ColumnName).Append("{set;get;}\n");            }            sb.Append("}");            txtModel.Text = sb.ToString();        }        public string IsNull(DataColumn column)        {            //如果列允许为null，并且列在C#中的类型是不可为空的（值类型ValueType）            if (column.AllowDBNull&&column.DataType.IsValueType)            {                return column.DataType + "?";            }            else            {                return column.DataType.ToString();            }        }
　　　　//生成数据访问代码        public void CreateDALCode(DataColumnCollection columns)        {            ///生成代码,这里就生成比较常用的几个方法，你也可以自己添加自己常用的方法            CreateToModel(columns);            CreateGetAll(columns);            CreateInsert(columns);            CreateUpdate(columns);            CreateGetById(columns);        }        #region 生成 把DataRow 转换成实体类型的方法        /// <summary>        /// 生成 把DataRow 转换成实体类型的方法        /// </summary>        /// <param name="columns"></param>        public void CreateToModel(DataColumnCollection columns)        {            StringBuilder sb = new StringBuilder();            sb.Append("public static ").Append(TablecomboBox.SelectedItem).Append(" To").Append(TablecomboBox.SelectedItem);            sb.AppendLine("(DataRow row)\n{").Append("    ").Append(TablecomboBox.SelectedItem);            sb.Append(" model =new ").AppendLine(TablecomboBox.SelectedItem + "();");            foreach (DataColumn column in columns)            {                sb.Append("    model.").Append(column.ColumnName).Append("=(").Append(column.DataType);                sb.Append(")SqlHelp.toNull(row[\"" + column.ColumnName + "\"]);\n");            }            sb.AppendLine("    return model;");            sb.AppendLine("}");            sb.AppendLine("    ");            txtDAL.Text += "" + sb.ToString();        }         #endregion        #region 生成 返回所有数据方法        /// <summary>        /// 生成 返回所有数据方法        /// </summary>        /// <param name="columns"></param>        public void CreateGetAll(DataColumnCollection columns)        {            StringBuilder sb = new StringBuilder();            sb.Append("public static ").Append(TablecomboBox.SelectedItem).AppendLine("[] GetAll() \n{");            sb.Append("    DataTable datatable= SqlHelp.ExecuteTable(\"select * from ").Append(TablecomboBox.SelectedItem).AppendLine("\");");            sb.Append("    ").Append(TablecomboBox.SelectedItem).Append("[] ").Append("ArrayModel=new ").                Append(TablecomboBox.SelectedItem).AppendLine("[datatable.Rows.Count];");            sb.AppendLine("    for (int i=0; i < datatable.Rows.Count; i++)\n    {").Append("        ArrayModel[i] =").Append("To")                .Append(TablecomboBox.SelectedItem).AppendLine("(datatable.Rows[i]);");            sb.AppendLine("    }").AppendLine("    return ArrayModel;");            sb.AppendLine("}");            sb.AppendLine("    ");            txtDAL.Text += sb.ToString();        }         #endregion        #region 生成数据插入方法        /// <summary>        /// 生成数据插入方法        /// </summary>        /// <param name="columns"></param>        public void CreateInsert(DataColumnCollection columns)        {            string TableName = TablecomboBox.SelectedItem.ToString();            StringBuilder sb = new StringBuilder();            sb.Append("public static int InsertData(").Append(TableName);            sb.AppendLine(" newData)\n{");            sb.Append("    int i=SqlHelp.ExecuteNonQuery(@\"insert ").Append(TableName);            sb.Append("(");            string columnname = string.Join(",", GetColumnAtName(TableName, false));            sb.Append(columnname).Append(")\n                     values(");            string columnAtname = string.Join(",", GetColumnAtName(TableName, true));            sb.Append(columnAtname);            sb.Append(")\",");//values结束;            string ParameterStr = string.Join("                     ,", ResultParameter(TableName));            sb.Append("\n                     ").Append(ParameterStr);            sb.Append("    );\n").AppendLine("    return i;").AppendLine("}").AppendLine("    ");//Insert语句结束;            txtDAL.Text += "" + sb.ToString();        }         #endregion        #region 生成数据更新方法        /// <summary>        /// 生成数据更新方法        /// </summary>        /// <param name="columns"></param>        public void CreateUpdate(DataColumnCollection columns)        {            string TableName = TablecomboBox.SelectedItem.ToString();            if (cbPrimaryKey.Items.Count < 1)            {                MessageBox.Show("不存在主键，无法生成UpData代码和Delete代码");                return;            }            StringBuilder sb = new StringBuilder();            sb.Append("public static int UpDate(").Append(TableName);            sb.AppendLine(" newData)\n{");            sb.Append("    int i=SqlHelp.ExecuteNonQuery(@\"update ").Append(TableName).Append(" set ");            string UpdateStr = string.Join(",", ResultUpdateStr(TableName));            sb.Append("\n                     ").Append(UpdateStr).Remove(sb.Length - 1, 1).Append(" where ").Append(cbPrimaryKey.SelectedValue).Append("=@").Append(cbPrimaryKey.SelectedValue);            sb.Append("\",\n                     ");//Sql语句结束;            string ParameterStr = string.Join("                     ,", ResultParameter(TableName));            sb.Append(ParameterStr);            sb.AppendLine("    );").AppendLine("    return i;").AppendLine("}").AppendLine("    ");            txtDAL.Text += "" + sb.ToString();            //生成删除方法            CreateDelete(columns);        }         #endregion        #region 生成数据删除方法        /// <summary>        /// 生成数据删除方法        /// </summary>        /// <param name="columns"></param>        public void CreateDelete(DataColumnCollection columns)        {            string TableName = TablecomboBox.SelectedItem.ToString();            StringBuilder sb = new StringBuilder();
　　　　　　　　//我这里使用的主键Guid类型,所以传递的Guid类型,当然你修改主键类型，你就可以修改下面的字符串Guid为其他类型，后面的CreateGetById也是通过Guid来获取，若想改变类型自行修改代码就可以了            sb.Append("public static int DeleteById(Guid id)").AppendLine("{");            sb.Append("    int i = SqlHelp.ExecuteNonQuery(\"delete ").Append(TableName);            sb.Append(" where ").Append(cbPrimaryKey.SelectedValue).Append("=@Id\",");            sb.AppendLine("\n                     new SqlParameter(\"@Id\", id));");            sb.Append("\n     return i;");            sb.AppendLine("}");            sb.AppendLine("    ");            txtDAL.Text += sb.ToString();        }        #endregion        #region 生成 通过SID获取实体类的方法+CreateGetById(DataColumnCollection columns)        /// <summary>        /// 生成 通过SID获取实体类的方法        /// </summary>        /// <param name="columns"></param>        public void CreateGetById(DataColumnCollection columns)        {            string TableName = TablecomboBox.SelectedItem.ToString();            StringBuilder sb = new StringBuilder();            sb.Append("public static ").Append(TableName).Append(" GetById(Guid id)").AppendLine("{");            sb.Append("    DataTable datatable = SqlHelp.ExecuteTable(\"select * from ").Append(TableName);            sb.Append(" where ").Append(cbPrimaryKey.SelectedValue).Append("=@Id\",");            sb.AppendLine("\n                     new SqlParameter(\"@Id\", id));").AppendLine("    if(datatable.Rows.Count<0)\n    {")                .AppendLine("        return null;");            sb.AppendLine("    }").AppendLine("    else if(datatable.Rows.Count==1)\n    {").Append("        return To").Append(TableName);            sb.AppendLine("(datatable.Rows[0]);").AppendLine("    }").AppendLine("    else\n    {").AppendLine("        throw new Exception();");            sb.AppendLine("    }");            sb.AppendLine("}");            sb.AppendLine("    ");            txtDAL.Text += sb.ToString();        }         #endregion        #region 返回数据表的数组列名+ GetColumnAtName(string tablename, bool flag)        /// <summary>        /// 返回数据表的列名        /// </summary>        /// <param name="tablename">数据表名</param>        /// <param name="flag">为true时就只返回数据表的列名,为true是在就返回 “@”+数据表列名(为了传递可变参数)</param>        /// <returns>返回值:把数据表列名按数组返回</returns>        public string[] GetColumnAtName(string tablename, bool flag)        {            DataColumnCollection columns = Returncolumns(tablename);            string[] ColumnName = new string[columns.Count];            for (int i = 0; i < columns.Count; i++)            {                if (flag)                {                    ColumnName[i] = "@" + columns[i].ColumnName;                }                else                {                    ColumnName[i] = columns[i].ColumnName;                }            }            return ColumnName;        }         #endregion        #region 返回数据表列名+Returncolumns(string tablename)        /// <summary>        /// 返回数据表列名        /// </summary>        /// <param name="tablename">表名</param>        /// <returns></returns>        public DataColumnCollection Returncolumns(string tablename)        {            DataTable datatable = ResultDataTable("select top 0 * from " + tablename);            DataColumnCollection columns = datatable.Columns;            return columns;        }         #endregion        #region 返回数据表可变参数字符串 如:new SqlParameter("@Sid",SqlHelp.toDBNull(Model.Sid))+ ResultParameter(string tablename)        /// <summary>        /// 返回数据表可变参数字符串 如:new SqlParameter("@Sid",SqlHelp.toDBNull(Model.Sid))        /// </summary>        /// <param name="tablename"></param>        /// <returns></returns>        public string[] ResultParameter(string tablename)        {            DataColumnCollection columns = Returncolumns(tablename);            string[] ParameterStr = new string[columns.Count];            for (int i = 0; i < columns.Count; i++)            {                ParameterStr[i] = "new SqlParameter(\"@" + columns[i].ColumnName + "\"," + "SqlHelp.toDBNull(newData." + columns[i].ColumnName + "))\n";            }            return ParameterStr;        }         #endregion        #region 返回更新字符串+ResultUpdateStr(string tablename)        /// <summary>        /// 返回更新字符串        /// </summary>        /// <param name="tablename"></param>        /// <returns></returns>        public string[] ResultUpdateStr(string tablename)        {            DataColumnCollection columns = Returncolumns(tablename);            string[] UpdateStr = new string[columns.Count];            for (int i = 0; i < columns.Count; i++)            {                if (cbPrimaryKey.SelectedValue.ToString() != columns[i].ToString())                {                    //当检测到主键时;这里返回的更新字符串就不对主键进行赋值,                    //应该在where后面赋值(这里在CreateUpdata(DataColumnCollection columns)方法中生成了主键字符串) ;                    UpdateStr[i] = columns[i].ColumnName + "=@" + columns[i].ColumnName;                }            }            //把主键影响UpdateStr数组的Null覆盖掉；            for (int i = 0; i < UpdateStr.Length; i++)            {                if (UpdateStr[i] == null)                {                    for (int j = i + 1; j < UpdateStr.Length; j++)                    {                        UpdateStr[j - 1] = UpdateStr[j];                    }                }                UpdateStr[UpdateStr.Length - 1] = null;            }            return UpdateStr;        }         #endregion        #region 返回表的主键集合        /// <summary>        /// 返回表的主键集合        /// </summary>        /// <param name="tablename"></param>        /// <returns></returns>        public DataColumn[] GetTablePrimaryKey1(string tablename)        {            DataTable datatable = ResultDataTable("select top 0 * from " + tablename);            DataColumn[] PrimaryKey = datatable.PrimaryKey;            if (PrimaryKey.Length <= 0)            {                return null;            }            return PrimaryKey;        }         #endregion        #region 表改变时发生事件        /// <summary>        /// 表改变时发生事件        /// </summary>        /// <param name="sender"></param>        /// <param name="e"></param>        private void TablecomboBox_SelectionChanged(object sender, SelectionChangedEventArgs e)        {            txtModel.Text = " ";//清空实体模型层代码            txtDAL.Text = "";//清空数据访问层代码            DataColumn[] dc = GetTablePrimaryKey1(TablecomboBox.SelectedValue.ToString());            List<string> list = new List<string>();            for (int i = 0; dc != null && i < dc.Length; i++)            {                list.Add(dc[i].ToString());            }            //加载数据表主键            cbPrimaryKey.ItemsSource = list;            cbPrimaryKey.SelectedIndex = 0;        }         #endregion          }