C# NET 多数据库支持解决方案, By shawl.qiu

时间：2007-12-02 来源：btbtd

C# NET 多数据库支持解决方案, By shawl.qiu

说明:
实现环境: .NET 1.X.
实现数据库: Access, MsSql
要支持其他数据库请自己扩展具体类(目前鄙人不需使用其他数据库, 就没弄了).

具体实现方法就是一个抽象类+N个具体数据库类.
使用类似工厂方法模式实现.

实现多库支持在 .NET 中就是一堆接口和实现具体类时的接口转换.

至于实现多库支持的价值显然比不实现多库来的更具弹性, 总不能费一堆功夫写各个不同数据库的支持版本吧.

抽象类中定义了几个在具体类中必需实现的方法.
除返回系统接口的几个必备方法外, 还必须写几个功能方法:

Cmd: 用于返回结果的数据操作
Execute: 用于不必返回结果的数据操作
Query: 用于返回最末插入数据的ID
Type: 用于数据类型兼容.

数据类型对照表请搜索关键词 "ADO 数据类型转换表" 查找相关资料.

抽象类静态方法 Instance 用于动态更改数据库支持.

抽象类的其他几个静态方法用于常用数据操作.

进行数据操作时一律使用系统的数据接口进行操作, 如:
IDbConnection
IDbCommand
IDbDataAdapter
IDbDataParameter

具体操作请直接看附件中的示例.

示例中几个需要手工设置的文件:
XDb/config.aspx 与 XDb/config_mssql.aspx 需要设置其中的文件路径.
XDb/Demo(MsSql)/config.aspx 设置 MsSql 数据库连接字串.

MsSql 示例数据库在 XDb/Demo(MsSql)/ 目录下的:
TestDb.mdf 和 TestDb_log.LDF, 需自己手工附加数据库

shawl.qiu
2007-12-02
http://btbtd.cublog.cn/

下载:
http://blogimg.chinaunix.net/blog/upfile2/071202001343.zip

类 XDb 及 XOleDb, XSqlDb:

namespace SqNs
{ // SqNs.XDb by shawl.qiu

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

public abstract class XDb
{ // shawl.qiu code

public abstract IDbConnection Connection(params object[] List);
public abstract IDbCommand Command(params object[] List);
public abstract IDbDataAdapter DataAdapter();
public abstract IDbDataParameter DataParameter(params object[] List);
public abstract DataTable Cmd(params object[] List);
public abstract object Type(object oType);

public abstract void Execute(params object[] List);
public abstract string Query(params object[] List);

//-------------------------------------------------------entity method

public static XDb Instance(params object[] List)
{
  int Len = List.Length;

  XDb Xdb;

  if(Len==0)
  {
   Xdb = new XOleDb();
  }
  else
  {
   string sInstance = List[0] as string;

   switch(sInstance)
   {
    case "XSqlDb":
     Xdb = new XSqlDb();
     break;

    default:
     Xdb = new XOleDb();
     break;
   }
  }
  return Xdb;
} // end public static XDb Instance(params object[] List)

//-------------------------------

public static string[] GetColumn(DataTable Dt, string Row)
{
  int Len = Dt.Rows.Count;
  string[] Ar = new string[Len];
  for(int i=0; i<Len; i++)
  {
   Ar[i] = Dt.Rows[i][Row].ToString();
  }

  return Ar;
} // end public static string[] GetColumn(DataTable Dt, string Row)

//-------------------------------

public static string FixQuery(string Query)
{
  return Query.Replace("'", "_");
}

//-------------------------------

public static string GetString(params object[] List)
{ // shawl.qiu code, Param: DataTable Dt, string sCol, string sRow, string sNull

  int ParamLen = List.Length;

  if(ParamLen<1||ParamLen>4)
  {
   throw new Exception("GetString 参数出错!");
  }

  DataTable Dt = List[0] as DataTable;
  string sCol = "##";
  string sRow = "##@";
  string sNull = "";

  if(ParamLen>1) sCol = List[1] as string;
  if(ParamLen>2) sRow = List[2] as string;
  if(ParamLen>3) sNull = List[3] as string;

  StringBuilder Sb = new StringBuilder();

  int Len = Dt.Rows.Count;

  if(Len==0) return "";
  int SubLen = Dt.Rows[0].ItemArray.Length;
  string TempString = "";
  for(int i=0; i<Len; i++)
  {
   for(int j=0; j<SubLen; j++)
   {
    TempString = Dt.Rows[i].ItemArray[j].ToString();

    if(TempString=="")
    {
     Sb.Append(sNull);
    }
    else
    {
     Sb.Append(TempString);
    }

    if(j<SubLen-1)
    {
     Sb.Append(sCol);
    }

   } // end for(int j=0; j<SubLen; j++)

   if(i<Len-1)
   {
    Sb.Append(sRow);
   }

  } // end for(int i=0; i<Len; i++)

  return Sb.ToString();
} // end public static string GetString(params object[] List)

public static string StringToSqlInText(string sText, char cSplit)
{
  sText = sText.Replace("'", "_");
  string[] Ar = sText.Split(cSplit);
  int Len = Ar.Length;
  if(Len==0) return "''";
  for(int i=0; i<Len; i++)
  {
   Ar[i] = "'"+Ar[i].Trim()+"'";
  }
  return String.Join(",", Ar);
} // end public static string StringToSqlInText(string sText, char cSplit)

} // end public abstract class XDb

} // end namespace SqNs

namespace SqNs
{ // SqNs.XDb by shawl.qiu

using System;
using System.Data;
using System.Data.OleDb;

public class XOleDb:XDb
{ // shawl.qiu code
public override IDbConnection Connection(params object[] List)
{
  int Len = List.Length;

  IDbConnection IDbConn = new OleDbConnection();

  switch(Len)
  {
   case 0:
    break;

   case 1:
    IDbConn.ConnectionString = List[0] as string;
    IDbConn.Open();
    break;

   case 2:
    IDbConn.ConnectionString = List[0] as string;
    bool bOpen = (bool)List[1];
    if(bOpen)
    {
     IDbConn.Open();
    }
    break;
  }

  return IDbConn;
} // end public override IDbConnection GetConn(string sConn)

//-------------------------------------------------

public override IDbCommand Command(params object[] List)
{
  OleDbCommand Comd = new OleDbCommand();
  int Len = List.Length;

  if(Len>1)
  {
   Comd.Connection = List[0] as OleDbConnection;
   Comd.CommandText = List[1] as string;
  }

  if(Len>2||Len==1)
  {
   throw new Exception("Command 参数出错!");
  }

  return Comd;
} // end public override IDbCommand Command()

public override IDbDataAdapter DataAdapter()
{
  return new OleDbDataAdapter();
} // end public override IDbDataAdapter DataAdapter()

public override IDbDataParameter DataParameter(params object[] List)
{
  int Len = List.Length;
  OleDbParameter Param = new OleDbParameter();

  if(Len==1||Len>4)
  {
   throw new Exception("DataParameter 参数出错!");
  }

  Param.ParameterName = List[0] as string;

  switch(Len)
  {
   case 2:
    Param.OleDbType = (OleDbType)Type(List[1]);
    break;

   case 3:
    Param.OleDbType = (OleDbType)Type(List[1]);
    Param.Value = List[2] as string;
    break;

   case 4:
    Param.OleDbType = (OleDbType)Type(List[1]);
    Param.Value = List[2] as string;
    Param.Size = (int)List[3];
    break;
  }

  return Param;
} // end public override IDbDataParameter DataParameter()

//-------------------------------------------------

public override DataTable Cmd(params object[] List)
{ // shawl.qiu code, p1:conn, p2:query, p3:table name
  DataTable Dt;
  int Len = List.Length;

  if(Len<2||Len>3)
  {
   throw new Exception("Cmd 参数出错!");
  }

  IDbConnection Conn;

  if(List[0] is string)
  {
   Conn = Connection(List[0] as string, false);
  }
  else
  {
   Conn = List[0] as OleDbConnection;
  }

  string sTblName = "TempTbl";
  if(Len>2)
  {
   sTblName = List[2] as string;
  }

  string sQuery = List[1] as string;

  DataSet ds = new DataSet();
  OleDbDataAdapter oDa;

  oDa = new OleDbDataAdapter(sQuery, Conn as OleDbConnection);
  oDa.Fill(ds, sTblName);
  Dt = ds.Tables[sTblName];

  return Dt;
} // end public override DataTable Cmd(params object[] List)

//-------------------------------------------------

public override object Type(object oType)
{
  int iType = (int)oType;

  switch(iType)
  {
   case 22:
    iType = 200;
    break;

   case 21:
    iType = 200;
    break;
  }

  return iType;
}

public override void Execute(params object[] List)
{ // shawl.qiu code, param: string sConn, string sQuery
  IDbConnection Conn;
  bool bClose = true;
  int Len = List.Length;

  if(Len<2||Len>2)
  {
   throw new Exception("Execute 参数出错!");
  }

  if(List[0] is string)
  {
   Conn = Connection(List[0] as string, false);
  }
  else
  {
   Conn = List[0] as OleDbConnection;
  }

  string sQuery = List[1] as string;

  if(Conn.State+""=="Closed")
  {
   Conn.Open();
   bClose = true;
  }

  OleDbCommand oCmd = new OleDbCommand(sQuery, Conn as OleDbConnection);
  oCmd.ExecuteNonQuery();

  if(bClose) Conn.Close();
} // end public override void Execute(params object[] List)

//-------------------------------------------------

public override string Query(params object[] List)
{ // shawl.qiu code, param: string sConn, string sQuery
  IDbConnection Conn;

  int Len = List.Length;
  if(List[0] is string)
  {
   Conn = Connection(List[0] as string, false);
  }
  else
  {
   Conn = List[0] as IDbConnection;
  }

  string sQuery = List[1] as string;

  bool bClose = true;
  string sFirst = "";
  if(Conn.State+""=="Closed")
  {
   Conn.Open();
   bClose = true;
  }

  OleDbCommand oCmd = new OleDbCommand(sQuery, Conn as OleDbConnection);
  sFirst = oCmd.ExecuteScalar()+"";

  if(bClose) Conn.Close();
  return sFirst;
}
} // end public override class XOleDb:XDb

} // end namespace SqNs

namespace SqNs
{ // SqNs.XDb by shawl.qiu

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

public class XSqlDb:XDb
{ // shawl.qiu code
public override IDbConnection Connection(params object[] List)
{
  int Len = List.Length;

  IDbConnection IDbConn = new SqlConnection();

  switch(Len)
  {
   case 0:
    break;

   case 1:
    IDbConn.ConnectionString = List[0] as string;
    IDbConn.Open();
    break;

   case 2:
    IDbConn.ConnectionString = List[0] as string;
    bool bOpen = (bool)List[1];
    if(bOpen)
    {
     IDbConn.Open();
    }
    break;
  }

  return IDbConn;
} // end public override IDbConnection GetConn(string sConn)

//-------------------------------------------------

public override IDbCommand Command(params object[] List)
{
  SqlCommand Comd = new SqlCommand();
  int Len = List.Length;

  if(Len>1)
  {
   Comd.Connection = List[0] as SqlConnection;
   Comd.CommandText = List[1] as string;
  }

  if(Len>2||Len==1)
  {
   throw new Exception("Command 参数出错!");
  }

  return Comd;
} // end public override IDbCommand Command()

public override IDbDataAdapter DataAdapter()
{
  return new SqlDataAdapter();
} // end public override IDbDataAdapter DataAdapter()

public override IDbDataParameter DataParameter(params object[] List)
{
  int Len = List.Length;
  SqlParameter Param = new SqlParameter();

  if(Len==1||Len>4)
  {
   throw new Exception("DataParameter 参数出错!");
  }

  Param.ParameterName = List[0] as string;

  switch(Len)
  {
   case 2:
    Param.SqlDbType = (SqlDbType)Type(List[1]);
    break;

   case 3:
    Param.SqlDbType = (SqlDbType)Type(List[1]);

    if((SqlDbType)Type(List[1])==SqlDbType.VarBinary)
    {
     Param.Value = System.Text.Encoding.Unicode.GetBytes((string)List[2]);
    }
    else
    {
     Param.Value = List[2] as string;
    }

    break;

   case 4:
    Param.SqlDbType = (SqlDbType)Type(List[1]);
    Param.Value = List[2] as string;
    Param.Size = (int)List[3];
    break;
  }

  return Param;
} // end public override IDbDataParameter DataParameter()

//-------------------------------------------------

public override DataTable Cmd(params object[] List)
{ // shawl.qiu code, p1:conn, p2:query, p3:table name
  DataTable Dt;
  int Len = List.Length;

  if(Len<2||Len>3)
  {
   throw new Exception("Cmd 参数出错!");
  }

  IDbConnection Conn;

  if(List[0] is string)
  {
   Conn = Connection(List[0] as string, false);
  }
  else
  {
   Conn = List[0] as SqlConnection;
  }

  string sTblName = "TempTbl";
  if(Len>2)
  {
   sTblName = List[2] as string;
  }

  string sQuery = List[1] as string;

  DataSet ds = new DataSet();
  SqlDataAdapter oDa;

  oDa = new SqlDataAdapter(sQuery, Conn as SqlConnection);
  oDa.Fill(ds, sTblName);
  Dt = ds.Tables[sTblName];

  return Dt;
} // end public override DataTable Cmd(params object[] List)

//-------------------------------------------------

public override object Type(object oType)
{
  int iType = (int)oType;

  switch(iType)
  {
   case 200:
    iType = 22;
    break;

   case 205:
    iType = 21;
    break;
  }

  return iType;
}

public override void Execute(params object[] List)
{ // shawl.qiu code, param: string sConn, string sQuery
  IDbConnection Conn;
  bool bClose = true;
  int Len = List.Length;

  if(Len<2||Len>2)
  {
   throw new Exception("Execute 参数出错!");
  }

  if(List[0] is string)
  {
   Conn = Connection(List[0] as string, false);
  }
  else
  {
   Conn = List[0] as SqlConnection;
  }

  string sQuery = List[1] as string;

  if(Conn.State+""=="Closed")
  {
   Conn.Open();
   bClose = true;
  }

  SqlCommand oCmd = new SqlCommand(sQuery, Conn as SqlConnection);
  oCmd.ExecuteNonQuery();

  if(bClose) Conn.Close();
} // end public override void Execute(params object[] List)

//-------------------------------------------------

public override string Query(params object[] List)
{ // shawl.qiu code, param: string sConn, string sQuery
  IDbConnection Conn;

  int Len = List.Length;
  if(List[0] is string)
  {
   Conn = Connection(List[0] as string, false);
  }
  else
  {
   Conn = List[0] as IDbConnection;
  }

  string sQuery = List[1] as string;

  bool bClose = true;
  string sFirst = "";
  if(Conn.State+""=="Closed")
  {
   Conn.Open();
   bClose = true;
  }

  SqlCommand oCmd = new SqlCommand(sQuery, Conn as SqlConnection);
  sFirst = oCmd.ExecuteScalar()+"";

  if(bClose) Conn.Close();
  return sFirst;
}
} // end public override class XSqlDb:XDb

} // end namespace SqNs