通用数据库SQL语句生成类.

平时一直使用数据库访问层.在每个代码里不断的写SQL语句的方法不但给别人维护性差,更换数据库类型也不能通用了.所以自己尝试写了个SQL语句生成类.支持Select,Insert,Update,Delete的语句生成.但水平有限写的并不高效和安全.希望大家一起维护添加功能.

分为四个Builder类和一个公用类,一个多数据库转换类.

 

代码公开给大家.希望大家能在使用的同时一起维护和改进这个类库 

 http://files.cnblogs.com/Vihome/DatabaseAccess.rar

 

说明:

有四个基础类:

SelectDBCommandBuilder,InsertDBCommandBuilder,DBCommandBuilder,DeleteDBCommandBuilder

构造一个类 (重载)

SelectDBCommandBuilder sql=new SelectDBCommandBuilder()

SelectDBCommandBuilder sql=new SelectDBCommandBuilder(DBCommandFactory)

    /// <summary>
    /// Database Type
    /// </summary>
    public enum DBCommandFactory
    {
        SQLServer=1,
        MySQL=2,
        Oracle=3,
        Access=4,
        DB2=5,
    }

也可以事后修改类型.不同数据库会生产不同的语法.

sql.DatabaseModule=DBCommandFactory.SQLServer;

InsertDBCommandBuilder,DBCommandBuilder,DeleteDBCommandBuilder

三个类有相同的属性 sql.TableName="Table";

在类中有四大分类的功能:属性,生成最后字符串,插入列名和值,条件.

插入列名有三个重载支持添加一列,多列,多列+别名,所有列

AddSelectColumn(List<String> ColumnName)

AddSelectColumn(String ColumnName)

AddSelectColumn(List<String> ColumnName, List<String> AliasName)

AddSelectALLColumn()

SelectDBCommandBuilder中有

AddOrderBy(String OrderColumn,SortMode Sort)

AddOrderBy(String OrderColumn)

AddGroupBy(String GroupColumn)

 

四个类的条件函数都是一样的:

AddWhere(WhereRelation Relation, String ColumnName, CommandComparison Comparison, object value)

//第一个参数是where的关系,第两个是列名,第三个是关系运算符=,>=,<等.最后一个是值.数值和字符会自动判断

AddWhere(WhereRelation Relation, String ColumnName, CommandComparison Comparison, object[] Value)

//最后一个是多值.数值和字符会自动判断,只支持 in not in运算符.

AddWhere(WhereRelation Relation, String ColumnName, CommandComparison Comparison, object value1, object value2)

//最后有两个值,只支持between运算符

AddWhereBySelect(WhereRelation Relation, String ColumnName, CommandComparison Comparison, String SelectCommand)

//最后是一个SELECT子句.

AddWhereByRelationShip(WhereRelation Relation, String LeftColumnName, CommandComparison Comparison, String RightColumnName)

//两个表的关连如:a.ID=B.ID

 AddHaving(String ColumnName, CommandComparison Comparison, object value)

AddHaving(String ColumnName, CommandComparison Comparison, object value1, object value2)

AddHaving(String ColumnName, CommandComparison Comparison, object[] Value)

AddHaving(String ColumnName, CommandComparison Comparison, String SelectCommand)

AddJoin(SelectJoinType type, String TableName, String ColumnName, CommandComparison Comparison, String FromTableName, String FromColumnName)

Join联接

    public enum SelectJoinType
    {
        InnerJoin = 0,
        Join = 1,
        LeftJoin = 2,
        RightJoin = 3,
        FullJoin=4,
        CrossJoin=5
    }

 例子:

 

View Code

 1             SelectDBCommandBuilder sql = new SelectDBCommandBuilder();
 2             sql.AddSelectTable(new List<String> { "BoardFailInfo_Table F", "BoardFailReason_Table FR"
 3             ,"BoardPartCode_Table P","RepairInfo_Table PR","RepairBoardView R","LoginUser_Table U",
 4             "FlowStatus_Table FS"});
 5             sql.AddSelectColumn(new List<String> { "min(Desp)", "min(Code)", "min(Pcs)", "min(FS.StatusName)"
 6             ,"R.SerialNumber","min(CONVERT(varchar(10),  R.FailDate,120))","min(U.UserName)"
 7             ,"min(R.AssignTime)","min(CONVERT(varchar(10),  R.RepairedTime,120))","min(F.FailName)"
 8             ,"min(FR.Name)","min(PR.PartName)","min(P.PartCodeNumber)"});
 9 
10             sql.AddWhereByRelationShip(WhereRelation.None, "R.RepairBoardID", CommandComparison.Equals
11                 , "PR.RepairBoardID");
12             sql.AddWhereByRelationShip(WhereRelation.And, "F.FailInfoID", CommandComparison.Equals
13                 , "R.FailInfoID");
14             sql.AddWhereByRelationShip(WhereRelation.And, "FR.ID", CommandComparison.Equals
15                 , "PR.FailReasonID");
16             sql.AddWhereByRelationShip(WhereRelation.And, "P.PartID", CommandComparison.Equals
17                 , "PR.PartCodeID");
18             sql.AddWhereByRelationShip(WhereRelation.And, "R.StatusID", CommandComparison.Equals
19                 , "FS.StatusID");
20 
21             sql.AddWhere(WhereRelation.And, "R.Repaired", CommandComparison.Equals, 1);
22             sql.AddWhere(WhereRelation.And, "R.TestFail", CommandComparison.Equals, 1);
23             sql.AddWhere(WhereRelation.And, "R.RepairedTime", CommandComparison.GreaterOrEquals
24                 ,StartTime.ToShortDateString() + " 08:30:00");
25             sql.AddWhere(WhereRelation.And, "R.RepairedTime", CommandComparison.LessOrEquals
26                 , EndTime.ToShortDateString() + " 08:30:00");
27             sql.AddWhere(WhereRelation.And, "Memo", CommandComparison.IsNot, "null");
28             sql.AddGroupBy("R.SerialNumber");
29             sql.BuildCommand();

最后生成的函数一样都是sql.BuildCommand(); 返回一个字符串.

 

转载于:https://www.cnblogs.com/Vihome/archive/2011/10/07/2200879.html