第19课：数据库常用操作(2)

数据库中，数据的基本操作包括添加、修改、删除和查询，分别使用Insert语句、Update语句、Delete语句和Select语句，本课将讨论相关语句及其对应的接口组件，包括tInsert接口、tUpdate接口、tDelete接口和tSelect接口。

Insert语句

首先来看tInsert接口及相关组件，其功能是在数据表中添加新记录，定义如下（/app_code/data/base/tInsert.cs）。

public interface tInsert
{
    tJet Jet { get; }
    string TableName { get; }
    string IdName { get; }
    // Insert方法返回新记录ID
    long Insert(tPairList data);
    long Insert(params tPair[] data);
    long Insert(string fields, string values);
    long Insert(params string[] nameValuePair);
}

tInsert接口中，首先定义了三个只读属性，分别是tJet对象（Jet）、数据表名称（TableName）和表的ID字段名（IdName）。

执行记录插入操作时，定义了几个重载版本的Insert()方法，其中：

Insert(tPairList data)，使用tPairList对象指定需要添加的数据。
Insert(params tPair[] data)，通过参数数组指定需要添加的数据。
Insert(string fields,string values)，参数一指定插入数据的字段，多个字段使用逗号分隔；参数二指定字段对应的数据，多个数据使用逗号分隔。需要注意的是，此方法应在字段名不包含特殊字符，并且数据内容无害的情况下使用，即在保证SQL语句通用性和安全性的前提下使用。
Insert(params string[] nameValuePair)，使用字符串的参数数组指定需要添加的数据，每两个参数一组，前一个指定字段名，后一个指定数据，其中，文本、日期数据需要使用单引号。

下面是tInsert接口实现的基类（/app_code/data/base/tInsert.cs）。

public abstract class tInsertBase:tInsert
{
    public tInsertBase(tJet jet,string tableName,string idName)
    {
        Jet = jet;
        TableName = tableName;
        IdName = idName;
    }
    //
    public tJet Jet { get; private set; }
    public string TableName { get; private set; }
    public string IdName { get; private set; }
    //
    public abstract long Insert(tPairList data);
    public abstract long Insert(params tPair[] data);
    public abstract long Insert(string fields, string values);
    public abstract long Insert(params string[] nameValuePair);
}

tInsertBase类中，除了tInsert接口成员，还定义了一个构造函数，它包括三个参数，分别指定Jet、TableName和IdName属性。

tInsert接口组件用于向数据表添加记录，SQL Server数据库实现使用tSqlInsert类，基本定义如下（/app_code/data/sqlserver/tSqlInsert.cs）。

using System;
using System.Text;
using System.Data.SqlClient;

public class tSqlInsert : tInsertBase
{
    public tSqlInsert(tJet jet, string tableName, string idName)
    : base(jet, tableName, idName) { }
    // 其它代码...
}

代码中，构造函数的参数包括tJet对象，数据表名和ID字段名，并通过继承基类（tInsertBase）构造函数设置相关属性。

tInsert接口中定义了四个版本的Insert()方法，首先是Insert(tPairList)方法的实现，实现代码如下。

public override long Insert(tPairList data)
{
    try
    {
        // 生成SQL
        StringBuilder sb = new StringBuilder("insert into ", 256);
        sb.AppendFormat("[{0}]([{1}]", TableName, data[0].Name);
        StringBuilder sbValue = new StringBuilder(128);
        sbValue.AppendFormat(") output inserted.[{0}] values(", IdName);
        sbValue.Append("@data_0");
        //
        for (int i = 1; i < data.Count; i++)
        {
            sb.AppendFormat(",[{0}]", data[i].Name);
            sbValue.AppendFormat(",@data_{0}", i);
        }
        sb.Append(sbValue.ToString());
        sb.Append(")");
        //
        using (SqlConnection cnn = new SqlConnection(Jet.CnnStr))
        {
            cnn.Open();
            SqlCommand cmd = cnn.CreateCommand();
            cmd.CommandText = sb.ToString();
            for (int i = 0; i < data.Count; i++)
                cmd.Parameters.AddWithValue("@data_" + i.ToString(), data[i].Value);
            return tLng.GetValue(cmd.ExecuteScalar());
        }
    }
    catch(Exception ex)
    {
        tLog.E(ex, -1000, "tSqlInsert.Insert(tPairList)");
        return -1000;
    }
}

代码中，首先会生成Insert语句，其中的参数名并没有使用tPairList对象中数据项的名称，而是使用data_<索引>格式，添加SqlCommand对象中的参数时，同样需要使用data_<索引>格式的参数名称。创建的insert语句格式如下。

SQL

insert into user_main(username,userpwd,email,locked)
output inserted.userid
values('user101','123456','123456@xxx.yyy',0)

生成的代码可用于SQL Server 2005及以后的版本，使用inserted表获取新添加记录的数据。通过inserted表特性，可以使用一条语句就完成添加记录和返回新记录ID数据的工作。如果需要获取新记录的所有数据，可以使用output inserted.*子句。

下面的代码，我们使用Insert(tPairList)方法向user_main表添加一条记录。

tInsert ins = new tSqlInsert(tApp.Db.GetJet(), "user_main", "userid");
tPairList pl = tPairList.Get(
        tPair.Get("username","user11"),
        tPair.Get("userpwd",tStr.Sha256("123456")),
        tPair.Get("email","user11@aaa.bbb"),
        tPair.Get("locked",1)
        );
tWeb.WriteLine(ins.Insert(pl));

执行代码后，如果显示为一个大于0的整数，表示记录已成功添加，此整数就是新记录的ID字段数据，即新记录中userid字段的数据。

Insert(params tPair[] data)方法的实现和Insert(tPairList data)方法比较相似，定义如下。

public override long Insert(params tPair[] data)
{
    try
    {
        // 生成SQL
        StringBuilder sb = new StringBuilder("insert into ", 256);
        sb.AppendFormat("[{0}]([{1}]", TableName, data[0].Name);
        StringBuilder sbValue = new StringBuilder(128);
        sbValue.AppendFormat(") output inserted.[{0}] values(", IdName);
        sbValue.Append("@data_0");
        //
        for (int i = 1; i < data.Length; i++)
        {
            sb.AppendFormat(",[{0}]", data[i].Name);
            sbValue.AppendFormat(",@data_{0}", i);
        }
        sb.Append(sbValue.ToString());
        sb.Append(")");
        //
        using (SqlConnection cnn = new SqlConnection(Jet.CnnStr))
        {
            cnn.Open();
            SqlCommand cmd = cnn.CreateCommand();
            cmd.CommandText = sb.ToString();
            for (int i = 0; i < data.Length; i++)
                cmd.Parameters.AddWithValue("@data_" + i.ToString(), data[i].Value);
            return tLng.GetValue(cmd.ExecuteScalar());
        }
    }
    catch(Exception ex)
    {
        tLog.E(ex, -1000, "tSqlInsert.Insert(tPair[])");
        return -1000;
    }
}

Insert(string,string)方法的两个参数分别指定字段列表和对应的数据列表，方法定义如下。

public override long Insert(string fields, string values)
{
    StringBuilder sb = new StringBuilder(256);
    sb.AppendFormat("insert into [{0}]({1}) output inserted.[{2}] values({3});",
        TableName,fields,IdName,values);
    //
    return Jet.GetValue(sb.ToString()).GetLng();
}

Insert(string,string)方法中，会在创建insert语句后直接使用tJet.GetValue()方法执行语句，并返回执行结果。

最后是Insert(parmas string[] nameValuePair)方法的实现，如下面的代码。

public override long Insert(params string[] nameValuePair)
{
    //
    if (nameValuePair.Length < 2 || nameValuePair.Length % 2 != 0) 
        return -1001;
    //
    StringBuilder sb = new StringBuilder("insert into ", 256);
    sb.AppendFormat("[{0}]([{1}]", TableName, nameValuePair[0]);
    StringBuilder sbValue = new StringBuilder(128);
    sbValue.AppendFormat(") output inserted.[{0}] values({1}", 
        IdName,nameValuePair[1]);
    for(int i = 2; i < nameValuePair.Length; i+=2)
    {
        sb.AppendFormat(",[{0}]",nameValuePair[i]);
        sbValue.AppendFormat(",{0}", nameValuePair[i + 1]);
    }
    //
    sb.Append(sbValue.ToString());
    sb.Append(")");
    //
    return Jet.GetValue(sb.ToString()).GetLng();
}

代码中，参数指定的字符串数组成员必须为偶数，即必须是字段名和数据直接量一一对应的形式。使用此方法时，应注意数据的形式，如ins.Insert("username","'userx1'","locked","1")中，文本数据'userx1'使用单引号定义。

完成tSqlInsert类的创建后，可以修改tSql类的定义，在GetInsert()方法中返回tSqlInsert对象，这样就可以使用tApp.Db对象直接创建tInsert组件了，如下面的代码，使用Insert(string,string)方法向user_main表中添加一条记录。

tInsert ins = tApp.Db.GetInsert("user_main", "userid");
long result = ins.Insert(
    "username,userpwd,email,locked",
    "'user12','"+tStr.Sha256("123456")+"','user12@xxx.yyy',0");
tWeb.WriteLine(result);