c# 通用数据库类

1、using System;using System.Collections;using System.Collections.Specialized;using System.Data;using System.Data.SqlClient;using System.Data.OleDb;using System.Data.OracleClient;using System.Configuration;using System.Reflection;namespace NurSoft.DAL /*/ <summary> / All rights reserved / 数据访问基础类

2、 / 用户可以修改满足自己项目的需要。 / </summary> public class DataBaseLayer /数据库连接字符串(web.config来配置) /<add key="ConnectionString" value="server=;database=DATABASE;uid=sa;pwd=" /> private string connectionString; public string ConntionString get return connectionString ; set

3、connectionString = value; public DataBaseLayer(string strConnect,string dataType) this.ConntionString = strConnect; this.DbType = dataType; public DataBaseLayer() / this.connectionString = ConfigurationSettings.AppSettings"ConnectionString" ; / this.dbType = ConfigurationSettings.AppSettin

4、gs"DataType" ; /this.connectionString = "data source=3;user id=sa;pwd=sa;database=temphrdb" /this.dbType = "SqlServer" this.connectionString = DBHelper.connstr; this.dbType = "SqlServer" /*/ <summary> / 数据库类型 / </summary> private string

5、dbType; public string DbType get if ( dbType = string.Empty | dbType = null ) return "Access" else return dbType; set if ( value != string.Empty && value != null ) dbType = value; if (dbType =string.Empty | dbType = null) dbType = ConfigurationSettings.AppSettings"DataType&quo

6、t; if ( dbType = string.Empty | dbType = null ) dbType = "Access" #region 转换参数 private System.Data.IDbDataParameter iDbPara(string ParaName,string DataType) switch(this.DbType) case "SqlServer": return GetSqlPara(ParaName,DataType); case "Oracle": return GetOleDbPara(Pa

7、raName,DataType); case "Access": return GetOleDbPara(ParaName,DataType); default : return GetSqlPara(ParaName,DataType); private System.Data.SqlClient.SqlParameter GetSqlPara( string ParaName , string DataType) switch(DataType) case "Decimal": return new System.Data.SqlClient.Sql

8、Parameter ( ParaName, System.Data.SqlDbType.Decimal ); case "Varchar": return new System.Data.SqlClient.SqlParameter ( ParaName, System.Data.SqlDbType.VarChar ); case "DateTime": return new System.Data.SqlClient.SqlParameter ( ParaName, System.Data.SqlDbType.DateTime ); case &quo

9、t;Iamge": return new System.Data.SqlClient.SqlParameter ( ParaName, System.Data.SqlDbType.Image ); case "Int": return new System.Data.SqlClient.SqlParameter ( ParaName, System.Data.SqlDbType.Int ); case "Text": return new System.Data.SqlClient.SqlParameter ( ParaName, System

10、.Data.SqlDbType.NText ); default : return new System.Data.SqlClient.SqlParameter ( ParaName, System.Data.SqlDbType.VarChar ); private System.Data.OracleClient.OracleParameter GetOraclePara( string ParaName , string DataType) switch(DataType) case "Decimal": return new System.Data.OracleCli

11、ent.OracleParameter( ParaName, System.Data.OracleClient.OracleType.Double); case "Varchar": return new System.Data.OracleClient.OracleParameter ( ParaName, System.Data.OracleClient.OracleType.VarChar ); case "DateTime": return new System.Data.OracleClient.OracleParameter ( ParaNa

12、me, System.Data.OracleClient.OracleType.DateTime ); case "Iamge": return new System.Data.OracleClient.OracleParameter ( ParaName, System.Data.OracleClient.OracleType.BFile ); case "Int": return new System.Data.OracleClient.OracleParameter ( ParaName, System.Data.OracleClient.Orac

13、leType.Int32 ); case "Text": return new System.Data.OracleClient.OracleParameter ( ParaName, System.Data.OracleClient.OracleType.LongVarChar ); default: return new System.Data.OracleClient.OracleParameter ( ParaName, System.Data.OracleClient.OracleType.VarChar ); private System.Data.OleDb.

14、OleDbParameter GetOleDbPara( string ParaName , string DataType) switch(DataType) case "Decimal": return new System.Data.OleDb.OleDbParameter( ParaName, System.Data.DbType.Decimal); case "Varchar": return new System.Data.OleDb.OleDbParameter ( ParaName, System.Data.DbType.String )

15、; case "DateTime": return new System.Data.OleDb.OleDbParameter ( ParaName, System.Data.DbType.DateTime ); case "Iamge": return new System.Data.OleDb.OleDbParameter( ParaName, System.Data.DbType.Binary ); case "Int": return new System.Data.OleDb.OleDbParameter ( ParaName

16、, System.Data.DbType.Int32 ); case "Text": return new System.Data.OleDb.OleDbParameter ( ParaName, System.Data.DbType.String ); default: return new System.Data.OleDb.OleDbParameter ( ParaName, System.Data.DbType.String ); #endregion / 创建 Connection 和 Command #region 创建 Connection 和 Command

17、 private IDbConnection GetConnection() switch(this.DbType) case "SqlServer": return new System.Data.SqlClient.SqlConnection(this.ConntionString); case "Oracle": return new System.Data.OracleClient.OracleConnection(this.ConntionString); case "Access": return new System.D

18、ata.OleDb.OleDbConnection(this.ConntionString); default: return new System.Data.SqlClient.SqlConnection(this.ConntionString); private IDbCommand GetCommand(string Sql,IDbConnection iConn) switch(this.DbType) case "SqlServer": return new System.Data.SqlClient.SqlCommand(Sql,(SqlConnection)i

19、Conn); case "Oracle": return new System.Data.OracleClient.OracleCommand(Sql,(OracleConnection)iConn); case "Access": return new System.Data.OleDb.OleDbCommand(Sql,(OleDbConnection)iConn); default: return new System.Data.SqlClient.SqlCommand(Sql,(SqlConnection)iConn); private IDbC

20、ommand GetCommand() switch(this.DbType) case "SqlServer": return new System.Data.SqlClient.SqlCommand(); case "Oracle": return new System.Data.OracleClient.OracleCommand(); case "Access": return new System.Data.OleDb.OleDbCommand(); default: return new System.Data.SqlCl

21、ient.SqlCommand(); private IDataAdapter GetAdapater(string Sql,IDbConnection iConn) switch(this.DbType) case "SqlServer": return new System.Data.SqlClient.SqlDataAdapter(Sql,(SqlConnection)iConn); case "Oracle": return new System.Data.OracleClient.OracleDataAdapter(Sql,(OracleCon

22、nection)iConn); case "Access": return new System.Data.OleDb.OleDbDataAdapter(Sql,(OleDbConnection)iConn); default: return new System.Data.SqlClient.SqlDataAdapter(Sql,(SqlConnection)iConn); private IDataAdapter GetAdapater() switch(this.DbType) case "SqlServer": return new System

23、.Data.SqlClient.SqlDataAdapter(); case "Oracle": return new System.Data.OracleClient.OracleDataAdapter(); case "Access": return new System.Data.OleDb.OleDbDataAdapter(); default: return new System.Data.SqlClient.SqlDataAdapter(); private IDataAdapter GetAdapater(IDbCommand iCmd)

24、switch(this.DbType) case "SqlServer": return new System.Data.SqlClient.SqlDataAdapter(SqlCommand)iCmd); case "Oracle": return new System.Data.OracleClient.OracleDataAdapter(OracleCommand)iCmd); case "Access": return new System.Data.OleDb.OleDbDataAdapter(OleDbCommand)iC

25、md); default: return new System.Data.SqlClient.SqlDataAdapter(SqlCommand)iCmd); #endregion / 执行简单SQL语句 #region 执行简单SQL语句 /*/ <summary> / 执行SQL语句，返回影响的记录数 / </summary> / <param name="SQLString">SQL语句</param> / <returns>影响的记录数</returns> public int ExecuteS

26、ql(string SqlString) using (System.Data.IDbConnection iConn = this.GetConnection() using (System.Data.IDbCommand iCmd = GetCommand(SqlString,iConn) iConn.Open(); try int rows=iCmd.ExecuteNonQuery(); return rows; catch(System.Exception E) throw new Exception(E.Message); finally if(iConn.State != Conn

27、ectionState.Closed) iConn.Close(); /*/ <summary> / 执行多条SQL语句，实现数据库事务。 / </summary> / <param name="SQLStringList">多条SQL语句</param> public void ExecuteSqlTran(ArrayList SQLStringList) using (System.Data.IDbConnection iConn = this.GetConnection() iConn.Open(); using(Sys

28、tem.Data.IDbCommand iCmd = GetCommand() iCmd.Connection=iConn; using(System.Data.IDbTransaction iDbTran = iConn.BeginTransaction() iCmd.Transaction=iDbTran; try for(int n=0;n<SQLStringList.Count;n+) string strsql = SQLStringListn.ToString(); if ( strsql.Trim().Length>1) iCmd.CommandText = strs

29、ql; iCmd.ExecuteNonQuery(); iDbTran.Commit(); catch(System.Exception E) iDbTran.Rollback(); throw new Exception(E.Message); finally if(iConn.State != ConnectionState.Closed) iConn.Close(); /*/ <summary> / 执行带一个存储过程参数的的SQL语句。 / </summary> / <param name="SQLString">SQL语句<

30、;/param> / <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param> / <returns>影响的记录数</returns> public int ExecuteSql(string SqlString,string content) using (System.Data.IDbConnection iConn = this.GetConnection() using(System.Data.IDbCommand iCmd = GetC

31、ommand(SqlString,iConn) System.Data.IDataParameter myParameter = this.iDbPara( "content", "Text"); myParameter.Value = content ; iCmd.Parameters.Add(myParameter); iConn.Open(); try int rows = iCmd.ExecuteNonQuery(); return rows; catch( System.Exception e ) throw new Exception(e.M

32、essage); finally if(iConn.State != ConnectionState.Closed) iConn.Close(); /*/ <summary> / 向数据库里插入图像格式的字段(和上面情况类似的另一种实例) / </summary> / <param name="strSQL">SQL语句</param> / <param name="fs">图像字节,数据库的字段类型为image的情况</param> / <returns>影响的记录数&

33、lt;/returns> public int ExecuteSqlInsertImg(string SqlString,byte fs) using (System.Data.IDbConnection iConn = this.GetConnection() using(System.Data.IDbCommand iCmd = GetCommand(SqlString,iConn) System.Data.IDataParameter myParameter = this.iDbPara( "content", "Image"); myPar

34、ameter.Value = fs ; iCmd.Parameters.Add(myParameter); iConn.Open(); try int rows = iCmd.ExecuteNonQuery(); return rows; catch( System.Exception e ) throw new Exception(e.Message); finally if(iConn.State != ConnectionState.Closed) iConn.Close(); /*/ <summary> / 执行一条计算查询结果语句，返回查询结果（object）。 / &l

35、t;/summary> / <param name="SQLString">计算查询结果语句</param> / <returns>查询结果（object）</returns> public object GetSingle(string SqlString) using (System.Data.IDbConnection iConn = GetConnection() using (System.Data.IDbCommand iCmd = GetCommand(SqlString,iConn) iConn.Open(); try object obj = iCmd.ExecuteScalar(); if(Object.Equals(obj,null)|(Object.Equals(obj,System.DBNull.Value) return null; else