数据库访问注意事项之一

转载

nkthinker 2009-06-04 00:45:11 博主文章分类：CSharp

另外，建议使用 System.Data.Common.DbDataReader，
而不要使用 System.Data.SQLite.SQLiteDataReader。
--------------------------------------------------------------------
SQLiteDataReader继承了DbDataReader
public sealed class SQLiteDataReader : DbDataReader

正是因为 SQLiteDataReader 是从 DbDataReader 中派生出来的，我才建议你使用 DbDataReader 代替 SQLiteDataReader 。
同理，如果可能的话，应该始终只使用 System.Data.Common 命名空间中的类，而不要使用 System.Data.SQLite 命名空间中的类。
先创建一个抽象基类 DbObject：

C# code

using System;
using System.Data;
using System.Data.Common;

namespace Skyiv.Ben.ProvidentFund.Db
{
  public abstract class DbObject
  {
    public string DbName { get; private set; }
    protected string dsn { get { return "Data Source=" + DbName; } }
    protected DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.SQLite");

    protected DbObject(string dbName)
    {
      DbName = dbName;
    }

    protected DbParameter AddParmeter(DbCommand comm, string name, DbType type)
    {
      var p = comm.CreateParameter();
      p.ParameterName = name;
      p.DbType = type;
      comm.Parameters.Add(p);
      return p;
    }

  }
}

然后，对数据库中的每张表，创建一个从 DbObject 派生的类，如下所示：

C# code

using System;
using System.IO;
using System.Data;
using System.Data.Common;
using Skyiv.Ben.Common;

namespace Skyiv.Ben.ProvidentFund.Db
{
  partial class Passwd : DbObject
  {
    static readonly int bytes = 160 / 8; // SHA1 has 160 bits

    public Passwd(string dbName)
      : base(dbName)
    {
    }

    byte[] GetValue(string accno)
    {
      using (var conn = factory.CreateConnection())
      {
        conn.ConnectionString = dsn;
        conn.Open();
        var comm = conn.CreateCommand();
        comm.CommandText = "SELECT Value FROM Passwd WHERE Accno=@Accno";
        AddParmeter(comm, "Accno", DbType.String).Value = accno;
        var bs = comm.ExecuteScalar() as byte[];
        if (bs == null) return null;
        if (bs.Length != bytes) throw new Exception("密码的密文长度错");
        return bs;
      }
    }

    void Insert(string accno, byte[] value)
    {
      using (var conn = factory.CreateConnection())
      {
        conn.ConnectionString = dsn;
        conn.Open();
        var comm = conn.CreateCommand();
        comm.CommandText = "INSERT INTO Passwd VALUES (@Accno,@Value)";
        AddParmeter(comm, "Accno", DbType.String).Value = accno;
        AddParmeter(comm, "Value", DbType.Binary).Value = value;
        comm.ExecuteNonQuery();
      }
    }

    void Upate(string accno, byte[] value)
    {
      using (var conn = factory.CreateConnection())
      {
        conn.ConnectionString = dsn;
        conn.Open();
        var comm = conn.CreateCommand();
        comm.CommandText = "UPDATE Passwd SET Value=@Value WHERE Accno=@Accno";
        AddParmeter(comm, "Value", DbType.Binary).Value = value;
        AddParmeter(comm, "Accno", DbType.String).Value = accno;
        if (comm.ExecuteNonQuery() != 1) throw new ArgumentOutOfRangeException("accno", "无此键值");
      }
    }

    public int WriteData(BinaryReader br)
    {
      var recs = 0;
      using (var conn = factory.CreateConnection())
      {
        conn.ConnectionString = dsn;
        conn.Open();
        var comm = conn.CreateCommand();
        CreateTable(comm);
        comm.CommandText = "INSERT INTO Passwd VALUES (@Accno,@Value)";
        var pAccno = AddParmeter(comm, "Accno", DbType.String);
        var pValue = AddParmeter(comm, "Value", DbType.Binary);
        comm.Transaction = conn.BeginTransaction();
        try
        {
          for (; ; recs++)
          {
            pAccno.Value = br.ReadDecimal();
            pValue.Value = br.ReadBytes(bytes);
            if (bytes != (pValue.Value as byte[]).Length) throw new InvalidDataException("输入文件长度错");
            comm.ExecuteNonQuery();
          }
        }
        catch (EndOfStreamException) { }
        comm.Transaction.Commit();
      }
      return recs;
    }

    void CreateTable(DbCommand comm)
    {
      comm.CommandText =
        "CREATE TABLE Passwd (" +
        "  Accno TEXT PRIMARY KEY," +
        "  Value BLOB NOT NULL" +
        ")";
      comm.ExecuteNonQuery();
    }
  }
}