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

第16课：数据库操作基础——数据表与字段组件

应用开发中，如果需要动态操作数据表，使用通用的模型是一个不错的选择；本节将完成这项工作，主要包括字段信息的管理，以及数据表的基本操作，如创建表、添加字段、清理表、删除表、设置主键等操作。

tFieldType枚举

常用的关系型数据库系统中都支持了大量的数据类型，而这里我们只需要支持一些常用的数据类型，下面的代码（/app_code/data/base/tField.cs）定义了tFieldType枚举，其中定义了本书组件中支持的数据类型。

C#
// 字段类型
public enum tFieldType
{
    Int = 1,
    BigInt = 2,
    Char = 3,
    VarChar = 4,
    Decimal = 5,
    Text = 6,
    DateTime = 7,
    Binary = 8
}

这里支持的数据类型包括：

  • Int，一般指32位有符号整数。
  • BigInt，一般指64位有符号整数。
  • Char，定长字符类型。
  • VarChar，变长字符类型。
  • Decimal，实数，如decimal(6,2)表示整数位和小位数共有6位，其中小数位有2位。
  • Text，大文本类型。
  • DateTime，日期和时间类型。
  • Binary，字节类型。

对于大多数关系型数据库来讲，tFieldType枚举列出的成员都有相应的类型，但有些数据库的数据类型设置比较简单，如SQLite数据库只包含基本的整数、浮点数、文本、二进制等类型，但没有DateTime类型。

使用SQLite数据库时，对于Int和BigInt类型统一使用integer类型；Decimal类型则使用real类型；Char、VarChar和Text类型统一使用Text类型；Binary使用blob类型；对于DateTime类型，可以使用文本类型保存指定格式的日期和时间数据，也可以使用其它的日期和时间处理方案，如使用整数表示日期和时间数，稍后会有详细的讨论。

tField类

tField类用于定义字段相关信息，并通过一系列静态方法创建不同类型的字段对象；tField类的基本定义如下（/app_code/data/base/tField.cs）。

C#
public class tField
{
    private tField() { }
    //
    public string Name { get; set; }
    public tFieldType Type { get; set; }
    public int Length { get; set; }// 文本或实数使用
    public int DecimalPlace { get; set; } // 实数小数位
    public bool Nullable { get; set; }
    public bool Unique { get; set; }
    public string DefaultValue { get; set; }
    // 其它代码...
}

其中，字段信息相关的属性包括：

  • Name属性，定义为string类型，指定字段名。
  • Type属性，定义为tFieldType枚举类型，指定字段的数据类型。
  • Length属性，定义为int类型，指定文本的字符数或实数的总位数。
  • DecimalPlace属性，定义为int类型，指定实数的小数位数。
  • Nullable属性，定义为bool类型，指定字段数据是否允许为null值。
  • Unique属性，定义为bool类型，指定字段数据是否定义唯一约束。
  • DefaultValue属性，定义为string类型，使用字符串形式指定字段的默认值。稍后可以看到，对于数值、文本和日期时间类型的字面值，会根据需要添加单引号。

此外，代码中还定义了一个私有的构造函数，这样在tField类的外部就不能使用new关键字创建tField对象；而创建tField对象的工作会使用一系列的静态方法来实现，首先来看GetInt()和GetBigInt()方法，它们分别创建数据类型为int和bigint的字段对象，如下面的代码。

C#
//Int = 1,
public static tField GetInt(string sName,
    bool nullable = true, bool unique = false, string defval = "")
{
    return new tField()
    {
        Name = sName,
        Type = tFieldType.Int,
        Nullable = nullable,
        Unique = unique,
        DefaultValue = defval
    };
}
//BigInt = 2,
public static tField GetBigInt(string sName,
    bool nullable = true, bool unique = false, string defval = "")
{
    return new tField()
    {
        Name = sName,
        Type = tFieldType.BigInt,
        Nullable = nullable,
        Unique = unique,
        DefaultValue = defval
    };
}

GetInt()和GetBigInt()方法使用了相同的参数，分别是：

  • sName，字段名称。
  • nullable，指定字段是否允许为空值（null）。
  • unique，指定字段是否添加唯一约束，表示此字段的数据不能重复。
  • defValue，指定字段的默认值。

定长字符和变长字符类型的定义方式相同，在tField类中分别使用GetChar()和GetVarChar()方法创建字段对象，如下面的代码。

C#
//Char = 3,
public static tField GetChar(string sName, int len,
    bool nullable = true, bool unique = false, string defval = "")
{
    return new tField()
    {
        Name = sName,
        Type = tFieldType.Char,
        Length = len,
        Nullable = nullable,
        Unique = unique,
        DefaultValue = defval
    };
}
//VarChar = 4,
public static tField GetVarChar(string sName, int len,
    bool nullable = true, bool unique = false, string defval = "")
{
    return new tField()
    {
        Name = sName,
        Type = tFieldType.VarChar,
        Length = len,
        Nullable = nullable,
        Unique = unique,
        DefaultValue = defval
    };
}

方法中，参数sName指定字段名，参数len指定允许的最大字符数。

创建实数类型的字段对象时使用GetDecimal()方法，定义如下。

C#
//Decimal = 5,
public static tField GetDecimal(string sName, int len, int decPlace,
    bool nullable = true, bool unique = false, string defval = "")
{
    return new tField()
    {
        Name = sName,
        Type = tFieldType.Decimal,
        Length = len,
        DecimalPlace = decPlace,
        Nullable = nullable,
        Unique = unique,
        DefaultValue = defval
    };
}

方法中，参数sName指定字段名，参数len指定整数部分和小数部分的总位数，参数decPlace指定小数的位数。

最后是大文本、字节类型、日期和时间类型的字段对象创建，如下面的代码。

C#
//Text = 6,
public static tField GetText(string sName,
    bool nullable = true, bool unique = false, string defval = "")
{
    return new tField()
    {
        Name = sName,
        Type = tFieldType.Text,
        Nullable = nullable,
        Unique = unique,
        DefaultValue = defval
    };
}
//DateTime = 7,
public static tField GetDateTime(string sName,
    bool nullable = true, bool unique = false, string defval = "")
{
    return new tField()
    {
        Name = sName,
        Type = tFieldType.DateTime,
        Nullable = nullable,
        Unique = unique,
        DefaultValue = defval
    };
}
//Binary = 8
public static tField GetBinary(string sName,
    bool nullable = true, bool unique = false, string defval = "")
{
    return new tField()
    {
        Name = sName,
        Type = tFieldType.Binary,
        Nullable = nullable,
        Unique = unique,
        DefaultValue = defval
    };
}

代码中，GetText()方法用于创建大文本类型字段对象，GetBinary()方法用于创建二进制数据类型字段对象；GetDateTime()方法用于创建日期和时间类型字段对象。

下面，在tTable接口组件中会看到tField类的具体应用。

tTable接口组件

tTable接口及相关组件用于完成对数据表的基本操作，并可以在实际应用中根据需要进行修改和扩展。

首先是tTable接口定义，如下面的代码（/app_code/data/base/tTable.cs）。

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

public interface tTable
{
    tJet Jet { get; }
    string Name { get; }
    bool Exists();
    string PrimaryKeys { get; set; }
    List<tField> Fields { get; set; }
    bool Create();
    bool Drop();
    bool Truncate();
    bool AddFields(params tField[] fields);
}

tTable接口中定义的成员包括：

  • Jet属性，数据表操作相关的tJet对象。
  • Name属性，数据表名称。
  • Exists()方法，判断数据表是否存在。
  • PrimaryKeys属性，指定主键字段，多个主键时使用逗号分隔。
  • Fields属性，定义为List<tField>对象，指定创建表时一同创建的字段信息。
  • Create()方法，创建数据表，包含ID字段（约定为recid）和Fields属性所包含的字段。
  • Drop()方法，删除数据表。
  • Truncate()方法，重置数据表。表中的ID计数会重新开始，就像新创建的表一样。
  • AddFields()方法，向表中添加字段。

请注意，重置表或删除表都会丢失表中的所有数据，如果觉得这些操作不安全，也可以不使用Truncate()和Drop()方法。

下面的代码（/app_code/data/base/tTable.cs）定义了tTableBase类，作为实现tTable接口类型的基类。

C#
public abstract class tTableBase : tTable
{
    //
    public tTableBase(tJet jet,string sName)
    {
        Jet = jet;
        Name = sName;
    }
    //
    public tJet Jet { get; private set; }
    public string Name { get; private set; }
    //
    public string PrimaryKeys { get; set; }
    public List<tField> Fields { get; set; }
    //
    public abstract bool Exists();
    public abstract bool Create();
    public abstract bool Drop();
    public abstract bool Truncate();
    public abstract bool AddFields(params tField[] fields);
}

tTableBase类中首先定义了构造函数，它需要两个参数，分别指定tJet对象和数据表名称。此外，除了Jet、Name、PrimaryKeys和Fields属性，其它成员都定义为抽象的，需要在tTableBase类的子类中重写。

SQL Server数据库的实现类为tSqlTable，定义为tTableBase类的子类，基本定义如下面的代码（/app_code/data/sqlserver/tSqlTable.cs）。

C#
using System;
using System.Text;

public class tSqlTable : tTableBase
{
    //
    public tSqlTable(tJet jet, string sName)
        : base(jet, sName) { }
    // 其它代码...
}

tSqlTable类的构造函数通过继承基类构造函数分别指定了Jet和Name属性。接下来讨论其它成员的实现。

首先创建辅助方法GetFieldSql()，其功能是将tField对象的字段信息转换为字段定义语句，tSqlTable类中的实现如下。

C#
// 生成字段定义SQL
protected string GetFieldSql(tField fld)
{
    StringBuilder sb = new StringBuilder(Jet.GetObjName(fld.Name),32);
    // 字段类型
    if (fld.Type == tFieldType.Int)
        sb.Append(" int");
    else if (fld.Type == tFieldType.BigInt)
        sb.Append(" bigint");
    else if (fld.Type == tFieldType.Decimal)
        sb.AppendFormat(" decimal({0},{1})", fld.Length,fld.DecimalPlace);
    else if (fld.Type == tFieldType.VarChar)
        sb.AppendFormat(" nvarchar({0})", fld.Length);
    else if (fld.Type == tFieldType.Char)
        sb.AppendFormat(" nchar({0})", fld.Length);
    else if (fld.Type == tFieldType.DateTime)
        sb.Append(" datetime");
    else if (fld.Type == tFieldType.Binary)
        sb.Append(" varbinary(max)");
    else
        sb.Append(" nvarchar(max)");
    // 是否为空
    if (fld.Nullable == false)
        sb.Append(" not null");
    // 是否唯一约束
    if (fld.Unique == true)
        sb.Append(" unique");
    // 默认值
    if (fld.DefaultValue != null && fld.DefaultValue.Trim() != "")
    {
        if (fld.Type == tFieldType.Int || fld.Type == tFieldType.BigInt ||
            fld.Type == tFieldType.Decimal)
            sb.AppendFormat(" default {0}", fld.DefaultValue);
        else
            sb.AppendFormat(" default '{0}'", fld.DefaultValue);
    }
    //
    return sb.ToString();
}

代码并不难理解，大部分的SQL语法是通用的，需要注意的是SQL Server中的数据类型名称，这里使用了SQL Server 2005之后的新名称，如nchar类型表示Unicode字符集的定长字符类型，nvarcahr表示Unicode字符集的变长字符类型，大文本类型则使用nvarchar(max)定义等。

Exists()方法用于判断数据表是否存在，tSqlTable类中的实现如下。

C#
public override bool Exists()
{
    string sql = "if object_id(@tblname) is null select 0 else select 1;";
    return Jet.GetValue(sql, tPair.Get("tblname", Name)).GetLng() == 1;
}

代码中使用了SQL Server数据库中的object_id()函数判断表是否存在，当数据表存在时，函数返回对象的ID，当数据表不存在时返回null值。语句中使用if语句判断，当object_id()返回null值时数据返回0，否则返回1，以此判断数据表是否存在。生成语句后，会调用tJet.GetValue()方法返回查询结果，如果等于1则Exists()方法返回true，即数据表存在，否则返回false，表示数据表不存在。

Create()方法用于创建数据表，并包含ID字段和Fields属性包含的字段。tSqlTable类中的实现如下。

C#
//
public override bool Create()
{
    string sql = GetCreateSql();
    return Jet.Execute(sql);
}
// 生成表创建SQL
protected string GetCreateSql()
{
    StringBuilder sb = new StringBuilder("create table",256);
    sb.AppendFormat(" [{0}](recid bigint identity(1,1) not null unique",Name);
    // 字段定义
    for (int i = 0; i < Fields.Count; i++)
    {
        sb.Append(",");
        sb.Append(GetFieldSql(Fields[i]));
    }
    // 主键
    if (PrimaryKeys != null)
    {
        string[] pk = PrimaryKeys.Split(new char[] { ',' },
            StringSplitOptions.RemoveEmptyEntries);
        if (pk.Length > 0)
        {
            sb.AppendFormat(",primary key({0}",Jet.GetObjName(pk[0].Trim()));
            for (int i = 1; i < pk.Length; i++)
                sb.AppendFormat(",{0}",Jet.GetObjName(pk[i].Trim()));
            sb.Append(")");
        }
    }
    //
    sb.Append(")");
    return sb.ToString();
}

代码中，使用GetCreateSql()方法创建create table语句，然后在Create()方法中执行。SQL Server数据库创建数据表的语句中，ID字段使用identity定义，默认是从1开始，每次增加1，不使用参数和使用identity(1,1)定义的效果是相同的。应用中，也可以指定开始数值和步长，如identity(2,2)就是指定ID从2开始，每次增加2，即ID值都是偶数。

向已存在的数据表中添加字段时使用AddFields()方法，tSqlTable类中的实现如下。

C#
public override bool AddFields(params tField[] fields)
{
    if (fields.Length == 0) return false;
    StringBuilder sb = new StringBuilder(256);
    sb.AppendFormat("alter table [{0}] add ", Name);
    sb.Append(GetFieldSql(fields[0]));
    for (int i = 1; i < fields.Length; i++)
    {
        sb.Append(",");
        sb.Append(GetFieldSql(fields[i]));
    }
    return Jet.Execute(sb.ToString());
}

在SQL Server中向表中添加字段使用alter table语句和add子句，添加的多个字段定义使用逗号分隔，语句格式如下。

SQL Server
alter table <表> add <字段定义1>,<字段定义2>,...

AddFields()方法中，首先创建了alter table语句，然后使用tJet.Execute()方法执行语句；执行成功时AddFields()方法返回true，否则返回false。

重置表和删除的表操作分别使用truncate table和drop table语句，tSqlTable类中的实现如下。

C#
//
public override bool Truncate()
{
    return Jet.Execute("truncate table [" + Name + "]");
}
//
public override bool Drop()
{
    return Jet.Execute("drop table [" + Name + "]");
}

truncate table语句和drop table语句都比较简单，代码中直接使用tJet.Execute()方法执行相应的语句，执行成功返回true，否则返回false。

综合测试

下面的代码，我们使用tField和tTable接口组件创建user_main1表。

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

public partial class Test : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        string cnnstr = 
            tSqlHelper.GetCnnStr(@".\MSSQLSERVER1", "cdb_demo");
        tJet jet =new tSqlJet(cnnstr);
        //
        List<tField> fld = new List<tField>();
        fld.Add(tField.GetVarChar("username", 30, false, false));
        fld.Add(tField.GetChar("userpwd", 64));
        fld.Add(tField.GetVarChar("email", 30));
        fld.Add(tField.GetInt("locked", false, false, "1"));
        //
        tTable tbl = new tSqlTable(jet, "user_main1");
        tbl.PrimaryKeys = "username";
        tbl.Fields = fld;
        //
        tWeb.WriteLine(tbl.Create());
    }
}

如果页面显示True，则表示代码成功执行。代码的功能是在cdb_demo数据库中创建user_main1表，其中的字段包括：

  • recid，默认的记录ID字段。
  • username，主键，保存用户名信息。
  • userpwd，密码，定义为64字符的定长文本类型。
  • email，保存用户的E-mail地址。
  • locked，用户是否锁定，默认值为1（锁定状态）。

下面的代码会在user_main1表中添加sex字段。

C#
using System;

public partial class Test : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        string cnnstr = 
            tSqlHelper.GetCnnStr(@".\MSSQLSERVER1", "cdb_demo");
        tJet jet =new tSqlJet(cnnstr);

        //
        tTable tbl = new tSqlTable(jet, "user_main1");
        bool result = tbl.AddFields(tField.GetInt("sex", false, false, "0"));
        tWeb.WriteLine(result);
    }
}

页面显示True时表示成功在user_main1表中添加了sex字段，其定义为int类型，默认值为0。

豫ICP备11002860-1

公安机关备案41152102000002号

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