自动生成SQL语句的工具类

大量字段的修改，插入操作的SQL语句拼写起来实在令人挠头，项目又没有用HIBERNATE之类的ORM框架，怎么办呢？我写了几个类简单应付一下，献丑了 。

表字段类：

using System; using System.Collections.Generic; using System.Linq; using System.Text; namespace Common.db { public class FieldTypeValue { public FieldTypeValue(string fieldName, string fieldValue, bool isNum) { this.fieldName = fieldName; this.fieldValue = fieldValue; this.isNum = isNum; } public FieldTypeValue(string fieldName, string fieldValue) { this.fieldName = fieldName; this.fieldValue = fieldValue; } private string fieldName; public string FieldName { get { return fieldName; } set { fieldName = value; } } private bool isNum = false; public bool IsNum { get { return isNum; } set { isNum = value; } } private string fieldValue; public string FieldValue { get { return fieldValue; } set { fieldValue = value; } } } }

 数据类：

using System; using System.Collections.Generic; using System.Linq; using System.Text; namespace Common.db { public class DataMaterial { private string tableName; private List<FieldTypeValue> ftvlist; public DataMaterial(string tableName, List<FieldTypeValue> ftvlist) { this.tableName = tableName; this.ftvlist = ftvlist; } public List<FieldTypeValue> Ftvlist { get { return ftvlist; } set { ftvlist = value; } } public string TableName { get { return tableName; } set { tableName = value; } } } }  

生成SQL语句的工具类：

using System; using System.Collections.Generic; using System.Linq; using System.Text; namespace Common.db { public class SqlBuilder { private DataMaterial dm; private string pkName; private string pkValue; public SqlBuilder(DataMaterial dm) { this.dm = dm; } public string createInsertSql() { StringBuilder sb = new StringBuilder(); sb.Append("insert into "); sb.Append(dm.TableName); sb.Append("("); List<FieldTypeValue> ftvlist = dm.Ftvlist; for (int i = 0; i < ftvlist.Count; i++) { FieldTypeValue ftv = (FieldTypeValue)ftvlist[i]; if (i != ftvlist.Count - 1) { sb.Append(ftv.FieldName + ","); } else { sb.Append(ftv.FieldName); } } sb.Append(") values("); for (int i = 0; i < ftvlist.Count; i++) { FieldTypeValue ftv = (FieldTypeValue)ftvlist[i]; if (ftv.IsNum) { if (i != ftvlist.Count - 1) { sb.Append(ftv.FieldValue + ","); } else { sb.Append(ftv.FieldValue); } } else { if (i != ftvlist.Count - 1) { sb.Append("'" + ftv.FieldValue + "',"); } else { sb.Append("'" + ftv.FieldValue + "'"); } } } sb.Append(")"); return sb.ToString(); } public string createUpdateSql(string pkName, string pkValue) { StringBuilder sb = new StringBuilder(); sb.Append("update "); sb.Append(dm.TableName); sb.Append(" set"); List<FieldTypeValue> ftvlist = dm.Ftvlist; for (int i = 0; i < ftvlist.Count; i++) { FieldTypeValue ftv = (FieldTypeValue)ftvlist[i]; if (i != ftvlist.Count - 1) { if (ftv.IsNum) { sb.Append(" " + ftv.FieldName + "=" + ftv.FieldValue + ","); } else { sb.Append(" " + ftv.FieldName + "='" + ftv.FieldValue + "',"); } } else { if (ftv.IsNum) { sb.Append(" " + ftv.FieldName + "=" + ftv.FieldValue + ""); } else { sb.Append(" " + ftv.FieldName + "='" + ftv.FieldValue + "'"); } } } sb.Append(" where " + pkName + "=" + pkValue); return sb.ToString(); } } }

 增加例子：

public void addCustomer(int id, string code, string photo_building, string xingming, string cover, string size, string pages, string inpage, string receivedtime, string registertime, string finishtime, string special, string cover_special, int inprint, int outprint, string again, string onlycover,string reCover,string reInpage) { List<FieldTypeValue> ftvlist = new List<FieldTypeValue>(); ftvlist.Add(new FieldTypeValue("cid", id.ToString(), true)); ftvlist.Add(new FieldTypeValue("code", code)); ftvlist.Add(new FieldTypeValue("photo_building", photo_building, true)); ftvlist.Add(new FieldTypeValue("xingming", xingming)); ftvlist.Add(new FieldTypeValue("cover", cover, true)); ftvlist.Add(new FieldTypeValue("size", size, true)); ftvlist.Add(new FieldTypeValue("pages", pages)); ftvlist.Add(new FieldTypeValue("inpage", inpage, true)); ftvlist.Add(new FieldTypeValue("receivedtime", receivedtime == "" ? DateTime.Now.ToString() : receivedtime)); ftvlist.Add(new FieldTypeValue("registertime", registertime == "" ? DateTime.Now.ToString() : registertime)); ftvlist.Add(new FieldTypeValue("finishtime", finishtime == "" ? DateTime.Now.ToString() : finishtime)); ftvlist.Add(new FieldTypeValue("special", special)); ftvlist.Add(new FieldTypeValue("cover_special", cover_special)); ftvlist.Add(new FieldTypeValue("inprint", inprint.ToString(), true)); ftvlist.Add(new FieldTypeValue("outprint", outprint.ToString(), true)); ftvlist.Add(new FieldTypeValue("again", again, true)); ftvlist.Add(new FieldTypeValue("onlycover", onlycover, true)); ftvlist.Add(new FieldTypeValue("ReCover", reCover)); ftvlist.Add(new FieldTypeValue("ReInpage", reInpage)); DataMaterial dm = new DataMaterial("photo_customer", ftvlist); SqlBuilder sqlBuilder = new SqlBuilder(dm); string sql = sqlBuilder.createInsertSql(); DbUtil du = new DbUtil(); du.updateData(sql); } 

更新例子：

public void updateCustomer(string cid, string building, string cover, string inpage, string size, string pages, string receivedtime, string registertime, string finishtime, string special, string cover_special, string inprint, string outprint, string again, string onlycover) //更新用户信息 { List<FieldTypeValue> ftvlist = new List<FieldTypeValue>(); ftvlist.Add(new FieldTypeValue("photo_building", building, true)); ftvlist.Add(new FieldTypeValue("cover", cover, true)); ftvlist.Add(new FieldTypeValue("size", size, true)); ftvlist.Add(new FieldTypeValue("pages", pages)); ftvlist.Add(new FieldTypeValue("inpage", inpage, true)); ftvlist.Add(new FieldTypeValue("receivedtime", receivedtime == "" ? DateTime.Now.ToString() : receivedtime)); ftvlist.Add(new FieldTypeValue("registertime", registertime == "" ? DateTime.Now.ToString() : registertime)); ftvlist.Add(new FieldTypeValue("finishtime", finishtime == "" ? DateTime.Now.ToString() : finishtime)); if (special != null) { ftvlist.Add(new FieldTypeValue("special", special)); } if (cover_special != null) { ftvlist.Add(new FieldTypeValue("cover_special", cover_special)); } if (!string.IsNullOrEmpty(inprint)) { ftvlist.Add(new FieldTypeValue("inprint", inprint, true)); } if (!string.IsNullOrEmpty(outprint)) { ftvlist.Add(new FieldTypeValue("outprint", outprint, true)); } ftvlist.Add(new FieldTypeValue("again", again, true)); ftvlist.Add(new FieldTypeValue("onlycover", onlycover, true)); DataMaterial dm = new DataMaterial("photo_customer", ftvlist); SqlBuilder sqlBuilder = new SqlBuilder(dm); string sql = sqlBuilder.createUpdateSql("cid", cid); dbutil.updateData(sql); } 

数据库操作类：

using System; using System.Data; using System.Configuration; using System.Web; using System.Data.SqlClient; namespace Common.db { public class DbUtil { public SqlConnection strcon; public DbUtil() { strcon = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["connStr"].ConnectionString); } /* * 执行增删改操作 * 参数 sql语句 * * */ public void updateData(string sql) { openConn(); SqlCommand cmd = new SqlCommand(sql, strcon); cmd.CommandTimeout = 0; try { cmd.ExecuteNonQuery(); } catch (Exception e) { throw e; } closeConn(); } public object ExecuteScalar(string sql) { openConn(); SqlCommand cmd = new SqlCommand(sql,strcon); cmd.CommandTimeout = 0; object obj = cmd.ExecuteScalar(); closeConn(); return obj; } public DataSet getDataSet(string sql) { openConn(); SqlDataAdapter sda = new SqlDataAdapter(sql, strcon); sda.SelectCommand.CommandTimeout = 0; DataSet ds = new DataSet(); sda.Fill(ds); closeConn(); return ds; } public DataSet getDataSet(string sql, SqlParameter[] paras, bool proc) { if (proc) { openConn(); SqlCommand cmd = new SqlCommand(sql, strcon); cmd.CommandTimeout = 0; cmd.CommandType = CommandType.StoredProcedure; if (paras != null && paras.Length > 0) { cmd.Parameters.AddRange(paras); } SqlDataAdapter sda = new SqlDataAdapter(cmd); DataSet ds = new DataSet(); sda.Fill(ds); closeConn(); return ds; } else { return getDataSet(sql); } } public SqlParameter CreateSqlParameter(string name, SqlDbType type, object value) { SqlParameter p = new SqlParameter(name,type); p.Value = value; return p; } /* * 关闭连接 * */ public void closeConn() { if (strcon.State != ConnectionState.Closed) { strcon.Close(); } } private void openConn() { strcon.Open(); } } }