1、c#NET万能数据库访问封装类ACCESSSQLServerOracleusing 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 SystemFramework.DAL /*/ / A
2、ll rights reserved / 数据访问基础类 / 用户可以修改满足自己项目的需要。 / public class DataBaseLayer /数据库连接字符串(web.config来配置) / private string connectionString; public string ConntionString get return connectionString ; set connectionString = value; public DataBaseLayer(string strConnect,string dataType) this.ConntionStrin
3、g = strConnect; this.DbType = dataType; 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 = S
4、qlServer; /*/ / 数据库类型 / 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) dbType = ConfigurationSettings.AppSetting
5、sDataType; 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(ParaName,DataType); case
6、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 ( ParaName, System.Data.SqlDbType.Dec
7、imal ); 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.SqlParameter ( ParaName, System.Dat
8、a.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.SqlParameter ( ParaName, System.Data.Sq
9、lDbType.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 Varchar: return new System.Data.Oracle
10、Client.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.OracleParameter ( ParaName, System.Data.Ora
11、cleClient.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.LongVarChar ); default: return new Syste
12、m.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( ParaName, System.Data.DbType.Decimal);
13、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, System.Data.DbType.Binary ); cas
14、e 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.DbType.String ); #endregion 创建 Conne
15、ction 和 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.ConntionString); case Access: return ne
16、w 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.SqlClient.SqlCommand(Sql,(SqlConnection)iCo
17、nn); 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 IDbCommand GetCommand() switch
18、(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(); private IDataAdapter GetAdapater(string Sql
19、,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.Data.OleDb.OleDbDataAdapter(Sql,(OleDbC
20、onnection)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.OracleClient.OracleDataAdapter(); case A
21、ccess: 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)iCmd); case Oracle: return new System.D
22、ata.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语句,返回影响的记录数 / / SQL语句 / 影响的记录数 public int
23、 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 Exception(E.Message); finally if(iConn.Stat
24、e != 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() iCmd.Connection=iConn; using(System.Data.I
25、DbTransaction 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.State != ConnectionState.Closed) iConn.Cl
26、ose(); /*/ / 执行带一个存储过程参数的的SQL语句。 / / SQL语句 / 参数内容,比如一个字段是格式复杂的文章,有特殊符号,可以通过这个方式添加 / 影响的记录数 public int ExecuteSql(string SqlString,string content) using (System.Data.IDbConnection iConn = this.GetConnection() using(System.Data.IDbCommand iCmd = GetCommand(SqlString,iConn) System.Data.IDataParameter m
27、yParameter = 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 ExecuteSqlInsertImg(string SqlString,byte fs)
copyright@ 2008-2022 冰豆网网站版权所有
经营许可证编号:鄂ICP备2022015515号-1