另外,建议使用 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