在我们的日常编程中,数据库的程序基本上都要与SQL语句打交道,SQL语句的编写不可避免的成为一个头疼的工作。且因为SQL语句是STRING类型,因此在编译阶段查不出错,只有到运行时才能发现错误。
本文的解决方案,通过自动生成SQL语句,在一定程度上降低出错的概率,从而提高编程效率。
public interface IClause
{
void Add(string name, object val);
void AddWhere(string name, object val);
void Clear();
string TableName { set;}
}
首先抽象出生成器的标准接口,Add及Addwhere,分别用增加字段及WHERE条件的,
比如说,在Insert状态下,Add("name","gates")相当于字段name的值为gates
在select状态下,Add("name","gates")相当于字段name的别名为gates即select name as gates
在update状态下,Add("name","gates")相当于字段name的值为gates即update name = "gates"
TableName属性即操作的表名
Clear()是为了一个实例多次应用,清空当前的字段及WHERE条件,表名的设置都通过TABLENAME属性设置
基类实现接口,通过基类的实现,从而有效的实现的代码屏蔽。
1 private abstract class BaseClause : IClause 2 ...{ 3 private string m_strTable = string.Empty; 4 private Where m_oWhere = new Where(); 5 6 protected BaseClause() 7 ...{ 8 this.m_strTable = string.Empty; 9 this.Clear(); 10 } 11 12 public virtual void Add(string name, object val) 13 ...{ 14 } 15 16 public void AddWhere(string name, object val) 17 ...{ 18 this.m_oWhere.Add(name, val); 19 } 20 21 public void Clear() 22 ...{ 23 this.m_oWhere.Clear(); 24 this.auxClear(); 25 } 26 27 public string TableName 28 ...{ 29 set ...{ this.m_strTable = value; } 30 protected get ...{ return this.m_strTable; } 31 } 32 33 public sealed override string ToString() 34 ...{ 35 return this.ToStr + this.m_oWhere.ToString(); 36 } 37 38 protected abstract string ToStr ...{ get;} 39 protected virtual void auxClear() ...{ } 40 } 工厂模式的应用,根据传入的枚举参数,实现动态创建接口的实例
1 public abstract class ClauseFactory 2 ...{ 3 public enum Flag 4 ...{ 5 Insert, 6 Update, 7 Select, 8 Delete, 9 } 10 11 public static IClause Create(string table, Flag category) 12 ...{ 13 BaseClause ret = null; 14 switch (category) 15 ...{ 16 case Flag.Insert: 17 ret = new Insert(); 18 break; 19 20 case Flag.Update: 21 ret = new Update(); 22 break; 23 24 case Flag.Select: 25 ret = new Select(); 26 break; 27 28 case Flag.Delete: 29 ret = new Delete(); 30 break; 31 32 default: 33 break; 34 } 35 if (ret != null) 36 ...{ 37 ret.TableName = table; 38 } 39 return ret; 40 } 41 } 各派生类的具体实现
1 private class Insert : BaseClause 2 ...{ 3 private string m_strName; 4 private string m_strValue; 5 6 public override void Add(string name, object val) 7 ...{ 8 if (val == null) 9 ...{ 10 this.Add(name, "null", false); 11 } 12 else 13 ...{ 14 this.Add(name, val.ToString(), val is string || val is DateTime); 15 } 16 } 17 18 private void Add(string name, string val, bool isref) 19 ...{ 20 if (isref) 21 ...{ 22 val = "'" + val + "'"; 23 } 24 if (this.m_strName == string.Empty) 25 ...{ 26 this.m_strName = "[" + name + "]"; 27 this.m_strValue = val; 28 } 29 else 30 ...{ 31 this.m_strName += ",[" + name + "]"; 32 this.m_strValue += "," + val; 33 } 34 } 35 36 protected override string ToStr 37 ...{ 38 get ...{ return "INSERT INTO [" + base.TableName + "] ( " + this.m_strName + " ) values ( " + this.m_strValue + " )"; } 39 } 40 41 protected override void auxClear() 42 ...{ 43 this.m_strValue = string.Empty; 44 this.m_strName = string.Empty; 45 } 46 } 47 48 private class Delete : BaseClause 49 ...{ 50 public Delete() 51 ...{ 52 } 53 54 protected override string ToStr 55 ...{ 56 get ...{ return "DELETE FROM [" + base.TableName + "]"; } 57 } 58 } 59
1 private class Update : BaseClause 2 ...{ 3 private string m_strUpdate; 4 5 public override void Add(string name, object val) 6 ...{ 7 if (val == null) 8 ...{ 9 this.Add(name, "null", false); 10 } 11 else 12 ...{ 13 this.Add(name, val.ToString(), val is string || val is DateTime); 14 } 15 } 16 17 private void Add(string name, string val, bool isref) 18 ...{ 19 if (isref) 20 ...{ 21 val = "'" + val + "'"; 22 } 23 if (this.m_strUpdate == string.Empty) 24 ...{ 25 this.m_strUpdate = "[" + name + "]=" + val; 26 } 27 else 28 ...{ 29 this.m_strUpdate += ",[" + name + "]=" + val; 30 } 31 } 32 33 protected override string ToStr 34 ...{ 35 get ...{ return "UPDATE [" + base.TableName + "] SET " + this.m_strUpdate; } 36 } 37 38 protected override void auxClear() 39 ...{ 40 this.m_strUpdate = string.Empty; 41 } 42 } 43 44 private class Select : BaseClause 45 ...{ 46 private string m_strSelect; 47 48 public override void Add(string name, object alis) 49 ...{ 50 if (alis == null) 51 ...{ 52 this.Add(name, name, false); 53 } 54 else 55 ...{ 56 this.Add(name, alis.ToString(), false); 57 } 58 } 59 60 private void Add(string name, string alis, bool isref) 61 ...{ 62 if (this.m_strSelect == string.Empty) 63 ...{ 64 this.m_strSelect = "[" + name + "] as [" + alis + "]"; 65 } 66 else 67 ...{ 68 this.m_strSelect += ",[" + name + "] as [" + alis + "]"; 69 } 70 } 71 72 protected override string ToStr 73 ...{ 74 get 75 ...{ 76 if (this.m_strSelect == string.Empty) 77 ...{ 78 this.m_strSelect = "*"; 79 } 80 return "SELECT " + this.m_strSelect + " FROM [" + base.TableName + "]"; 81 } 82 } 83 84 protected override void auxClear() 85 ...{ 86 this.m_strSelect = string.Empty; 87 } 88 } 89
1 private class Where 2 ...{ 3 private string m_strWhere; 4 5 public Where() 6 ...{ 7 this.Clear(); 8 } 9 10 public void Add(string name, object val) 11 ...{ 12 if (val == null) 13 ...{ 14 this.Add(name, "null", false); 15 } 16 else 17 ...{ 18 this.Add(name, val.ToString(), val is string || val is DateTime); 19 } 20 } 21 22 private void Add(string name, string val, bool isref) 23 ...{ 24 if (isref) 25 ...{ 26 val = "'" + val + "'"; 27 } 28 if (this.m_strWhere == string.Empty) 29 ...{ 30 this.m_strWhere = "[" + name + "]=" + val; 31 } 32 else 33 ...{ 34 this.m_strWhere += " and [" + name + "]=" + val; 35 } 36 } 37 38 public override string ToString() 39 ...{ 40 string strRet = string.Empty; 41 if (this.m_strWhere != string.Empty) 42 ...{ 43 strRet = " Where " + this.m_strWhere; 44 } 45 return strRet; 46 } 47 48 public void Clear() 49 ...{ 50 this.m_strWhere = string.Empty; 51 } 52 } 53