数据库访问注意事项之一

另外，建议使用 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();
}
}
}

这样，我们的代码就可以独立了数据库了，随时可以从 SQLite 切换到 MySQL 等数据库。

转载于:https://blog.51cto.com/nkthinker/162783