图书 合集 文章 工具与资源 本站查询
C#开发训练营

第18课：数据库常用操作(1)

接下来的几课将继续以SQL Server数据库为例，讨论数据库的常用操作，并创建相应的操作组件，主要包括查询条件，添加、更新、删除和查询数据，以及数据的批量操作等内容。为方便使用自定义数据组件，我们先来讨论tDb接口组件的创建。

tDb接口组件

tDb接口组件作为对象构造器（Builder）的角色，用于创建数据库的各种操作组件对象，接口定义如下（/app_code/data/base/tDb.cs）。

C#
public interface tDb
{
    string CnnStr { get; }
    tJet GetJet();
    tTable GetTable(string tableName);
    tInsert GetInsert(string tableName,string idName);
    tUpdate GetUpdate(string tableName);
    tDelete GetDelete(string tableName);
    tSelect GetSelect(string source);
    //
    tBatch GetBatch(string tableName, string idName);
}

代码中，除了CnnStr属性，其它方法都会创建对应的数据操作组件，如接下来讨论的tInsert、tUpdate、tDelete、tSelect和tBatch接口组件。

tDb接口组件是其它各类数据操作组件的对象构造器，SQL Server数据库的支持类使用tSql，完整定义如下（/app_code/data/sqlserver/tSql.cs）。

C#
public class tSql : tDb
{
    //
    public tSql(string sCnnStr)
    {
        CnnStr = sCnnStr;
    }
    //
    public string CnnStr { get; private set; }
    //
    public tJet GetJet()
    { return new tSqlJet(CnnStr); }
    //
    public tTable GetTable(string tableName)
    { return new tSqlTable(GetJet(), tableName); }
    //
    public tInsert GetInsert(string tableName, string idName)
    { return new tSqlInsert(GetJet(), tableName, idName); }
    //
    public tUpdate GetUpdate(string tableName)
    { return new tSqlUpdate(GetJet(), tableName); }
    //
    public tDelete GetDelete(string tableName)
    { return new tSqlDelete(GetJet(), tableName); }
    //
    public tSelect GetSelect(string source)
    {
        return new tSqlSelect(GetJet(), source);
    }
    //
    public tBatch GetBatch(string tableName, string idName)
    { return new tSqlBatch(GetJet(), tableName, idName); }
}

代码中，tSql类直接实现了tDb接口，构造函数需要指定一个数据库连接字符串作为参数，并赋值到CnnStr属性。

GetJet()方法用于返回tDbJet接口组件对象，目前只实现了支持SQL Server数据库的tSqlJet类，而支持其它数据库的组件还没有实现；为正确测试代码，可以先让没有实现的组件对象的创建方法返回null值，组件创建后再修改为创建实际的对象。

为了测试SQL Server数据库操作组件，应确认tApp.Db字段的定义为tSql对象，如下面的代码（/app_code/app/tApp.cs）。

C#
using System;

public class tApp
{
    //
    public static string CnnStr2 =
        tSqlHelper.GetCnnStr(@".\MSSQLSERVER1", "cdb_demo");
    public static tDb Db = new tSql(CnnStr2);
    // 其它代码...
}

本书示例中连接的SQL Server数据库服务器为本机，实例名为MSSQLSERVER1，使用的数据库是cdb_demo；这里使用了“Windows 身份验证”方式进行登录。

应用中获取tJet对象时可以使用如下代码。

C#
tJet jet = tApp.Db.GetJet();

应用中获取操作user_main表的tTable接口对象时可以使用如下代码。

C#
tTable tbl = tApp.Db.GetTable("user_main");

条件

在查询、更新和删除等操作中都需要使用条件，本书代码库中，条件设置功能由tCond和tCondHelper等类型完成，其中，tCond类（/app_code/data/base/tCond.cs）定义了条件的信息，基本定义如下。

C#
using System;
using System.Collections.Generic;

public class tCond
{
    public enum R
    {
        And=1,
        Or=2
    }
    public enum CondType
    {
        Group = 0,
        Equal = 1,
        NotEqual = 2,
        Greater = 3,
        GreaterEqual = 4,
        Less = 5,
        LessEqual = 6,
        Between = 7,
        In = 8,
        Direct = 9
    }
    //
    public string Name { get; set; }
    public CondType Type { get; set; }
    public object[] Values { get; set; }
    // 条件组时使用
    public R SubRelation { get; set; }
    public List<tCond> SubCondition { get; set; }
    // 其它代码...
}

tCond类中定义了两个内置的枚举类型，分别指定条件关系和条件类型；其中，tCond.R枚举类型定义了条件之间的关系，包括And（与）、Or（或）关系；tCond.CondType枚举类型定义条件的类型，包括：

  • Group（0），条件组，其中的各个条件关系使用tCond.R枚举定义。
  • Equal（1），等于。
  • NotEqual（2），不等于。
  • Greater（3），大于。
  • GreaterEqual（4），大于等于。
  • Less（5），小于。
  • LessEqual（6），小于等于。
  • Between（7），区间条件，对应between...and...语句。
  • In（8），对应in(...)语句。
  • Direct（9），直接使用SQL语句设置条件。

直接使用语句

对于简单的操作定义，可以直接使用SQL语句，如is null、is not null；此外，如果开发中直接使用字段名和数据值是安全的，并且是通用的SQL语句，也可以直接使用语句完成，这样可以简化很多工作，如username='Tom'、locked=1就不会有问题；但对于不能确保安全的语句，其中的数据应该使用参数进行传递。

在tCond类中，我们使用GetDirect()方法创建直接语句类型的tCond对象，方法定义如下。

C#
//    Direct = 9
public static tCond GetDirect(string sql)
{
    tCond c = new tCond();
    c.Type = CondType.Direct;
    c.Name = sql;
    return c;
}

比较条件

在tCond.CondType枚举中定义的比较条件类型成员包括：

  • Equal（1），等于。
  • NotEqual（2），不等于。
  • Greater（3），大于。
  • GreaterEqual（4），大于等于。
  • Less（5），小于。
  • LessEqual（6），小于等于。

这些条件的定义也比较简单，只需要指定字段、类型和对应的值，在tCond类（/app_code/data/base/tCond.cs）中，会使用一系列的工厂方法创建不同类型的条件，如下面的代码。

C#
//    Equal = 1,
public static tCond GetEqual(string name,object val)
{
    tCond c = new tCond();
    c.Type = CondType.Equal;
    c.Name = name;
    c.Values =new object[] { val };
    return c;
}
//    NotEqual = 2,
public static tCond GetNotEqual(string name, object val)
{
    tCond c = new tCond();
    c.Type = CondType.NotEqual;
    c.Name = name;
    c.Values = new object[] { val };
    return c;
}
//    Greater = 3,
public static tCond GetGreater(string name, object val)
{
    tCond c = new tCond();
    c.Type = CondType.Greater;
    c.Name = name;
    c.Values = new object[] { val };
    return c;
}
//    GreaterEqual = 4,
public static tCond GetGreaterEqual(string name, object val)
{
    tCond c = new tCond();
    c.Type = CondType.GreaterEqual;
    c.Name = name;
    c.Values = new object[] { val };
    return c;
}
//    Less = 5,
public static tCond GetLess(string name, object val)
{
    tCond c = new tCond();
    c.Type = CondType.Less;
    c.Name = name;
    c.Values = new object[] { val };
    return c;
}
//    LessEqual = 6,
public static tCond GetLessEqual(string name, object val)
{
    tCond c = new tCond();
    c.Type = CondType.LessEqual;
    c.Name = name;
    c.Values = new object[] { val };
    return c;
}

需要创建一个等于条件时，如name字段等于Tom的条件，可以使用如下代码。

C#
tCond cond = tCond.GetEqual("name","Tom");

代码创建的条件相当于如下SQL语句，稍后，在实现应用中也会将tCond对象转换为相应的SQL语句。

SQL
name='Tom'

区间条件

如果字段名中没有特殊符号，设置区间条件时也可以直接使用SQL语句，如:

SQL
age between 18 and 60

如果字段名包含特殊字符，需要使用数据库对象名时，可以通过tCond对象生成SQL语句。

在tCond类中，使用GetBetween()方法创建区间条件对象，如下面的代码。

C#
//    Between = 7,
public static tCond GetBetween(string name, object val1,object val2)
{
    tCond c = new tCond();
    c.Type = CondType.Between;
    c.Name = name;
    c.Values = new object[] { val1,val2 };
    return c;
}

创建age字段在18到60的区间条件时，可以使用如下代码。

C#
tCond cond = tCond.GetBetween("age",18,60);

In条件

In条件可以定义一个字段匹配的多个值，使用tCond.GetIn()方法创建相应的条件对象，如下面的代码。

C#
//    In = 8,
public static tCond GetIn(string name, params object[] val)
{
    tCond c = new tCond();
    c.Type = CondType.In;
    c.Name = name;
    c.Values = val;
    return c;
}

如果设置查询username等于Tom、Jerry、John的条件，可以使用如下代码。

C#
tCond cond = tCond.GetIn("username","Tom","Jerry","John");

代码添加的条件如下。

SQL
username in('Tom','Jerry','John')

在稍后的实际应用中，列表中的数据会通过参数进行传递。

条件组

创建条件组时，使用tCond.GetGroup()方法，实现代码如下（/app_code/data/base/tCond.cs）。

C#
//Group = 0,
    public static tCond GetGroup(R relation,params tCond[] conds)
    {
        List<tCond> lc = new List<tCond>();
        lc.AddRange(conds);
        //
        tCond c = new tCond();
        c.Type = CondType.Group;
        c.SubRelation = relation;
        c.SubCondition = lc;
        return c;
    }

条件组可以由多个条件组成，这些条件的关系是与（And）或者是或（Or）关系，如创建name字段等于Tom，并且age字段小于30的条件，可以使用如下代码。

C#
tCond cond = tCond.GetGroup(tCond.R.And,
    tCond.GetEqual("name","Tom"),
    tCond.GetDirect("age<30"));

tCondHelper类

tCondHelper类的主要功能是将tCond对象中的条件转换为相应的SQL语句，相关操作定义如下（/app_code/data/base/tCondHelper.cs）。

C#
using System;
using System.Text;
using System.Text.RegularExpressions;

public class tCondHelper
{
    //
    public static string GetSql(tJet jet,tCond cond,out tPairList args)
    {
        tCondHelper ch = new tCondHelper();
        string sql = ch.GetSql(jet, cond);
        args = ch.args;
        return sql;
    }
    
    // 分割字符串，先用空白字符分割，再用逗号分割，返回分割后的字符串数组
    public static string[] StrSplit(string s)
    {
        string s1= Regex.Replace(s, @"\s+", ",");
        return s1.Split(new char[]{ ','} , StringSplitOptions.RemoveEmptyEntries);
    }
    //
    private int counter = 0;
    private tPairList args;
    //
    private tCondHelper() {
        counter = 0;
        args = new tPairList();
    }
    //
    private string GetSql(tJet jet, tCond cond)
    {
        if (cond == null) return "";
        StringBuilder sb = new StringBuilder(128);
                sb.Append("(");
        switch (cond.Type)
        {
            case tCond.CondType.Group:
                if (cond.SubCondition == null ||
                    cond.SubCondition.Count == 0) break;
                string rexp = cond.SubRelation == tCond.R.And ? " and " : " or ";
                sb.Append(GetSql(jet, cond.SubCondition[0]));
                for(int i = 1; i < cond.SubCondition.Count; i++)
                {
                    sb.Append(rexp);
                    sb.Append(GetSql(jet,cond.SubCondition[i]));
                }
                break;
            case tCond.CondType.Equal:
                sb.AppendFormat(@"{0}={1}",
                    jet.GetObjName(cond.Name),
                    jet.GetParamName("cond_"+counter.ToString()));
                args.Add("cond_" + counter.ToString(), cond.Values[0]);
                counter++;
                break;
            case tCond.CondType.NotEqual:
                sb.AppendFormat(@"{0}<>{1}",
                    jet.GetObjName(cond.Name),
                    jet.GetParamName("cond_" + counter.ToString()));
                args.Add("cond_"+ counter.ToString(),cond.Values[0]);
                counter++;
                break;
            case tCond.CondType.Greater:
                sb.AppendFormat(@"{0}>{1}",
                    jet.GetObjName(cond.Name),
                    jet.GetParamName("cond_" + counter.ToString()));
                args.Add("cond_" + counter.ToString(), cond.Values[0]);
                counter++;
                break;
            case tCond.CondType.GreaterEqual:
                sb.AppendFormat(@"{0}>={1}",
                    jet.GetObjName(cond.Name),
                    jet.GetParamName("cond_" + counter.ToString()));
                args.Add("cond_"+counter.ToString(),cond.Values[0]);
                counter++;
                break;
            case tCond.CondType.Less:
                sb.AppendFormat(@"{0}<{1}",
                    jet.GetObjName(cond.Name),
                    jet.GetParamName("cond_" + counter.ToString()));
                args.Add("cond_"+counter.ToString(),cond.Values[0]);
                counter++;
                break;
            case tCond.CondType.LessEqual:
                sb.AppendFormat(@"{0}<={1}",
                    jet.GetObjName(cond.Name),
                    jet.GetParamName("cond_" + counter.ToString()));
                args.Add("cond_"+counter.ToString(),cond.Values[0]);
                counter++;
                break;
            case tCond.CondType.Between:
                sb.AppendFormat(@"{0} between {1} and {2}",
                    jet.GetObjName(cond.Name),
                    jet.GetParamName("cond_" + counter.ToString()),
                    jet.GetParamName("cond_"+(counter+1).ToString()));
                args.Add("cond_" + counter.ToString(),cond.Values[0]);
                args.Add("cond_" + (counter + 1).ToString(),cond.Values[1]);
                counter += 2;
                break;
            case tCond.CondType.In:
                sb.AppendFormat(@"{0} in ({1}",
                    jet.GetObjName(cond.Name),
                    jet.GetParamName("cond_"+counter.ToString()));
                args.Add("cond_" + counter.ToString(), cond.Values[0]);
                for (int i = 1; i < cond.Values.Length; i++)
                {
        sb.AppendFormat(@",{0}",jet.GetParamName("cond_"+(counter+i).ToString()));
                    args.Add("cond_" + (counter + i).ToString(), cond.Values[i]);
                }
                sb.Append(")");
                counter += cond.Values.Length;
                break;
            case tCond.CondType.Direct:
                sb.Append(cond.Name);
                break;
        }
        sb.Append(")");
        return sb.ToString();
    }
    // 其它代码...
}

代码中，主要的工作由实例方法GetSql()完成，它需要两个参数，分别是tJet对象和tCond对象，方法会通过递归调用完成所有条件的SQL转换。生成的条件语句中，所有参数命名使用cond_0、cond_1、cond_2、……格式。

应用时，可以直接使用静态方法GetSql()生成条件语句和所需的参数数据，下面的代码演示了tCondHelper.GetSql()方法的应用。

C#
using System;

public partial class Test : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        tCond cond = tCond.GetGroup(tCond.R.And,
            tCond.GetBetween("age", 18, 60),
            tCond.GetEqual("sex", 1),
            tCond.GetDirect("name like 'J%'"));
        //
        tPairList args;
        string condSql = tCondHelper.GetSql(tApp.Db.GetJet(), cond, out args);
        //
        tWeb.WriteLine(condSql);
        for (int i = 0; i < args.Count; i++)
            tWeb.WriteLine(string.Format("{0} = {1}", args[i].Name, args[i].Value));
    }
}

代码执行结果如图1。

图1

本例生成的条件语句基于SQL Server数据库，区间条件（Between）和等于条件（Equal）中的字段名已转换为使用一对中括号定义的对象格式；直接使用SQL语句指定的name字段查询条件则保持原样。此外，通过tCondHelper.GetSql()方法中的输出参数还获取了条件语句中所需参数的名称和数据，即cond_0参数值为18、cond_1参数的值为60、cond_2参数的值为1，分别对应了区间条件中的两个参数和等于条件中的一个参数。

Like条件

在应用开发中使用数据库，需要大量使用SQL语句，那么，在代码中动态生成SQL语句就是一件很常见的工作。系统安全方面，SQL注入是常见的数据库入侵方法，所以，在生成SQL语句时就需要注意安全问题，比如，客户端提交的文本内容不应直接组合到SQL语句中，而是进行一些相应的处理。

Like条件一般用于文本内容的模糊查询，为了安全起见，对于客户端提交的文本（如查询内容），不应直接组合到Like条件中；本书采取的方案是将客户端提交的文本分解为不包括空白字符和逗号的一个或多个查询内容。

下面的代码（/app_code/data/base/tCondHelper.cs），我们使用tCondHelper.StrSplit()方法完成文本的分割操作，如下面的代码。

C#
public static string[] StrSplit(string s)
{
    string s1 = Regex.Replace(s, @"\s+", ",");
    return s1.Split(new char[] { ',' }, 
        StringSplitOptions.RemoveEmptyEntries);
}

代码中，首先使用正则表达式将文本中的空白字符替换为逗号，然后通过逗号分割文本，结果只保留非空的文本内容。下面的代码演示了tCondHelper.StrSplit()方法的使用。

C#
using System;

public partial class Test : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        string s = "C#   String,StringBuilder";
        string[] ss = tCondHelper.StrSplit(s);
        for (int i = 0; i < ss.Length; i++)
            tWeb.WriteLine(ss[i]);
    }
}

代码执行结果如图2。

图2

将用户提交的文本分割后，就可以组合为Like查询条件了，此功能由tCondHelper.GetLikeSql()方法完成，方法定义如下。

C#
public static string GetLikeSql(tJet jet, string fields, string keywords)
{
    string[] fld = StrSplit(fields);
    string[] key = StrSplit(keywords);
    //
    if (fld.Length == 0) return "";
    if (key.Length == 0) key = new string[] { "" };
    //
    StringBuilder sb = new StringBuilder(256);
    sb.AppendFormat(@"{0} like '{2}{1}{2}'",
        jet.GetObjName(fld[0]), key[0], jet.MatchChar);
    for (int i = 1; i < key.Length; i++)
    {
        sb.AppendFormat(@" or {0} like '{2}{1}{2}'",
            jet.GetObjName(fld[0]), key[i], jet.MatchChar);
    }
    //
    for (int i = 1; i < fld.Length; i++)
    {
        sb.AppendFormat(@" or {0} like '{2}{1}{2}'", 
            jet.GetObjName(fld[i]), key[0], jet.MatchChar);
        for (int j = 1; j < key.Length; j++)
        {
            sb.AppendFormat(@" or {0} like '{2}{1}{2}'",
                jet.GetObjName(fld[i]), key[j], jet.MatchChar);
        }
    }
    return sb.ToString();
}

由于tApp.Db字段设置的是tSql，所以，下面的代码会生成用于SQL Server数据库中的Like查询条件。

C#
using System;

public partial class Test : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        string s = "C#    String,StringBuilder";
        string likeSql =
            tCondHelper.GetLikeSql(tApp.Db.GetJet(), "bookname,keyword", s);
        tWeb.WriteLine(likeSql);
    }
}

代码生成语句内容如下。

SQL
[bookname] like '%C#%' or [bookname] like '%String%' or 
[bookname] like '%StringBuilder%' or [keyword] like '%C#%' or 
[keyword] like '%String%' or [keyword] like '%StringBuilder%'

此外，如果只需要前匹配或后匹配查询，可以分别使用tCondHelper类中的GetPrefixSql()和GetSuffixSql()方法，方法定义如下。

C#
// 前匹配
public static string GetPrefixSql(tJet jet, string fields, string keywords)
{
    string[] fld = StrSplit(fields);
    string[] key = StrSplit(keywords);
    //
    if (fld.Length == 0) return "";
    if (key.Length == 0) key = new string[] { "" };
    //
    StringBuilder sb = new StringBuilder(256);
    sb.AppendFormat(@"{0} like '{1}{2}'", 
        jet.GetObjName(fld[0]), key[0], jet.MatchChar);
    for (int i = 1; i < key.Length; i++)
    {
        sb.AppendFormat(@" or {0} like '{1}{2}'", 
            jet.GetObjName(fld[0]), key[i], jet.MatchChar);
    }
    //
    for (int i = 1; i < fld.Length; i++)
    {
        sb.AppendFormat(@" or {0} like '{1}{2}'", 
            jet.GetObjName(fld[i]), key[0], jet.MatchChar);
        for (int j = 1; j < key.Length; j++)
        {
            sb.AppendFormat(@" or {0} like '{1}{2}'", 
                jet.GetObjName(fld[i]), key[j], jet.MatchChar);
        }
    }
    return sb.ToString();
}
// 后匹配
public static string GetSuffixSql(tJet jet, string fields, string keywords)
{
    string[] fld = StrSplit(fields);
    string[] key = StrSplit(keywords);
    //
    if (fld.Length == 0) return "";
    if (key.Length == 0) key = new string[] { "" };
    //
    StringBuilder sb = new StringBuilder(256);
    sb.AppendFormat(@"{0} like '{2}{1}'", 
        jet.GetObjName(fld[0]), key[0], jet.MatchChar);
    for (int i = 1; i < key.Length; i++)
    {
        sb.AppendFormat(@" or {0} like '{2}{1}'", 
            jet.GetObjName(fld[0]), key[i], jet.MatchChar);
    }
    //
    for (int i = 1; i < fld.Length; i++)
    {
        sb.AppendFormat(@" or {0} like '{2}{1}'", 
            jet.GetObjName(fld[i]), key[0], jet.MatchChar);
        for (int j = 1; j < key.Length; j++)
        {
            sb.AppendFormat(@" or {0} like '{2}{1}'", 
                jet.GetObjName(fld[i]), key[j], jet.MatchChar);
        }
    }
    return sb.ToString();
}

稍后，在Update、Delete和Select语句的操作中还会有更多关于条件设置的应用。

豫ICP备11002860-1

公安机关备案41152102000002号

caohuayu.com    本站内容均为原创，转载请注明出处