c# 通用数据库类解析.docx

1、c# 通用数据库类解析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 /*/ / All rights reserved / 数据访问基础类 / 用户

2、可以修改满足自己项目的需要。 / public class DataBaseLayer /数据库连接字符串(web.config来配置) / private string connectionString; public string ConntionString get return connectionString ; set connectionString = value; public DataBaseLayer(string strConnect,string dataType) this.ConntionString = strConnect; this.DbType = dat

3、aType; public DataBaseLayer() / this.connectionString = ConfigurationSettings.AppSettingsConnectionString ; / this.dbType = ConfigurationSettings.AppSettingsDataType ; /this.connectionString = data source=192.168.1.43;user id=sa;pwd=sa;database=temphrdb; /this.dbType = SqlServer; this.connectionStri

4、ng = DBHelper.connstr; this.dbType = SqlServer; /*/ / 数据库类型 / private string 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) dbT

5、ype = ConfigurationSettings.AppSettingsDataType; 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 GetO

6、leDbPara(ParaName,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.SqlParameter ( P

7、araName, 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 Iamge: return new System.Data.SqlClient.S

8、qlParameter ( 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.Data.SqlDbType.NText ); default : return new System.Data.SqlClient.SqlPa

9、rameter ( ParaName, System.Data.SqlDbType.VarChar ); private System.Data.OracleClient.OracleParameter GetOraclePara( string ParaName , string DataType) switch(DataType) case Decimal: return new System.Data.OracleClient.OracleParameter( ParaName, System.Data.OracleClient.OracleType.Double); case Varc

10、har: return new System.Data.OracleClient.OracleParameter ( ParaName, System.Data.OracleClient.OracleType.VarChar ); case DateTime: return new System.Data.OracleClient.OracleParameter ( ParaName, System.Data.OracleClient.OracleType.DateTime ); case Iamge: return new System.Data.OracleClient.OraclePar

11、ameter ( ParaName, System.Data.OracleClient.OracleType.BFile ); case Int: return new System.Data.OracleClient.OracleParameter ( ParaName, System.Data.OracleClient.OracleType.Int32 ); case Text: return new System.Data.OracleClient.OracleParameter ( ParaName, System.Data.OracleClient.OracleType.LongVa

12、rChar ); default: return new System.Data.OracleClient.OracleParameter ( ParaName, System.Data.OracleClient.OracleType.VarChar ); private System.Data.OleDb.OleDbParameter GetOleDbPara( string ParaName , string DataType) switch(DataType) case Decimal: return new System.Data.OleDb.OleDbParameter( ParaN

13、ame, System.Data.DbType.Decimal); case Varchar: return new System.Data.OleDb.OleDbParameter ( ParaName, System.Data.DbType.String ); case DateTime: return new System.Data.OleDb.OleDbParameter ( ParaName, System.Data.DbType.DateTime ); case Iamge: return new System.Data.OleDb.OleDbParameter( ParaName

14、, System.Data.DbType.Binary ); case Int: return new System.Data.OleDb.OleDbParameter ( ParaName, 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.Db

15、Type.String ); #endregion / 创建 Connection 和 Command #region 创建 Connection 和 Command 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.Co

16、nntionString); case Access: return new System.Data.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.SqlCli

17、ent.SqlCommand(Sql,(SqlConnection)iConn); 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); p

18、rivate IDbCommand 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.SqlClient.SqlCommand(); privat

19、e 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,(OracleConnection)iConn); case Access: return new System.Da

20、ta.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.Data.SqlClient.SqlDataAdapter(); case Oracle: return new System.Data.Ora

21、cleClient.OracleDataAdapter(); case Access: return new System.Data.OleDb.OleDbDataAdapter(); default: return new System.Data.SqlClient.SqlDataAdapter(); private IDataAdapter GetAdapater(IDbCommand iCmd) switch(this.DbType) case SqlServer: return new System.Data.SqlClient.SqlDataAdapter(SqlCommand)iC

22、md); case Oracle: return new System.Data.OracleClient.OracleDataAdapter(OracleCommand)iCmd); case Access: return new System.Data.OleDb.OleDbDataAdapter(OleDbCommand)iCmd); default: return new System.Data.SqlClient.SqlDataAdapter(SqlCommand)iCmd); #endregion / 执行简单SQL语句 #region 执行简单SQL语句 /*/ / 执行SQL语

23、句，返回影响的记录数 / / SQL语句 / 影响的记录数 public int ExecuteSql(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 Exc

24、eption(E.Message); finally if(iConn.State != ConnectionState.Closed) iConn.Close(); /*/ / 执行多条SQL语句，实现数据库事务。 / / 多条SQL语句 public void ExecuteSqlTran(ArrayList SQLStringList) using (System.Data.IDbConnection iConn = this.GetConnection() iConn.Open(); using(System.Data.IDbCommand iCmd = GetCommand() iC

25、md.Connection=iConn; using(System.Data.IDbTransaction iDbTran = iConn.BeginTransaction() iCmd.Transaction=iDbTran; try for(int n=0;n1) iCmd.CommandText = strsql; iCmd.ExecuteNonQuery(); iDbTran.Commit(); catch(System.Exception E) iDbTran.Rollback(); throw new Exception(E.Message); finally if(iConn.S

26、tate != ConnectionState.Closed) iConn.Close(); /*/ / 执行带一个存储过程参数的的SQL语句。 / / SQL语句 / 参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加 / 影响的记录数 public int ExecuteSql(string SqlString,string content) using (System.Data.IDbConnection iConn = this.GetConnection() using(System.Data.IDbCommand iCmd = GetCommand(SqlSt

27、ring,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.Message); finally if(iConn.State != ConnectionState.Closed) iConn.Close(); /*/ / 向数据库里插入图像格式的字段(和上面情况类似的另一种实例) / / SQL语句 / 图像字节,数据库的字段类型为image的情况 / 影响的记录数 public int Execu