通用数据库SQL语句生成类.
平时一直使用数据库访问层.在每个代码里不断的写SQL语句的方法不但给别人维护性差,更换数据库类型也不能通用了.所以自己尝试写了个SQL语句生成类.支持Select,Insert,Update,Delete的语句生成.但水平有限写的并不高效和安全.希望大家一起维护添加功能.
分为四个Builder类和一个公用类,一个多数据库转换类.
代码公开给大家.希望大家能在使用的同时一起维护和改进这个类库
https://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
}
例子:
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(); 返回一个字符串.