数据库通用连接类
using System; using System.Data; using System.Data.SqlClient; namespace DataProviders { /// <summary> /// SqlDataProvider 的摘要说明。 /// </summary> internal class SqlDataProvider : IDataProvider { private System.Data.SqlClient.SqlConnection sqlConnection; private System.Data.SqlClient.SqlCommand sqlCommand; private string connectionString; public SqlDataProvider() : this(null) { // // TODO: 在此处添加构造函数逻辑 // } public SqlDataProvider(string connectionString) { if (connectionString == null || connectionString.Trim() == string.Empty) { System.Configuration.AppSettingsReader configurationAppSettings = new System.Configuration.AppSettingsReader(); this.connectionString = (string)(configurationAppSettings.GetValue("sqlConnectionString", typeof(string))); } else { this.connectionString = connectionString; } } /// <summary> /// SQL 连接字符串 /// </summary> public string ConnectionString { get { return this.connectionString; } set { this.connectionString = value; } } /// <summary> /// 返回一个带有连接字符串的SQL Connection. /// </summary> /// <returns>OracleConnection</returns> private SqlConnection GetSqlConnection() { try { return new SqlConnection(this.connectionString); } catch (Exception ex) { #if DEBUG System.Diagnostics.Debug.WriteLine(ex.ToString()); #endif return null; } } /// <summary> /// 对于 UPDATE、INSERT 和 DELETE 语句,返回值为该命令所影响的行数。对于其他所有类型的语句,返回值为 -1 /// </summary> /// <param name="Sql">UPDATE、INSERT 和 DELETE 语句</param> public int ExecuteNonQuery(string sql) { using(sqlConnection = this.GetSqlConnection()) { if (sqlConnection == null) return -1; try { if (sqlConnection.State == System.Data.ConnectionState.Closed) sqlConnection.Open(); sqlCommand = new SqlCommand(sql, sqlConnection); return sqlCommand.ExecuteNonQuery(); } catch (Exception ex) { #if DEBUG System.Diagnostics.Debug.WriteLine(ex.ToString()); #endif return -1; } } } /// <summary> /// 执行查询,并将查询返回的结果集中第一行的第一列作为 .NET Framework 数据类型返回。忽略额外的列或行。 /// </summary> /// <param name="sql">SELECT 语句</param> /// <returns>.NET Framework 数据类型形式的结果集第一行的第一列;如果结果集为空或结果为 REF CURSOR,则为空引用</returns> public object ExecuteScalar(string sql) { using(sqlConnection = this.GetSqlConnection()) { if (sqlConnection == null) return null; try { if (sqlConnection.State == System.Data.ConnectionState.Closed) sqlConnection.Open(); sqlCommand = new SqlCommand(sql, sqlConnection); return sqlCommand.ExecuteScalar(); } catch (Exception ex) { #if DEBUG System.Diagnostics.Debug.WriteLine(ex.ToString()); #endif return null; } } } /// <summary> /// 执行单Sql语句查询,并将查询返回的结果作为一个数据集返回 /// </summary> /// <param name="sql">SELECT 语句</param> /// <returns>数据集 DataSet</returns> public DataSet RetriveDataSet(string sql) { if (sql == null || sql == string.Empty) { #if DEBUG System.Diagnostics.Debug.WriteLine("sql 为空"); #endif return null; } using(sqlConnection = this.GetSqlConnection()) { if (sqlConnection == null) return null; using(SqlDataAdapter da = new SqlDataAdapter(sql, sqlConnection)) { DataSet ds = new DataSet(); try { da.Fill(ds); } catch (Exception ex) { #if DEBUG System.Diagnostics.Debug.WriteLine(ex.ToString()); #endif } return ds; } } } /// <summary> /// 执行Sql数组语句查询,并将查询返回的结果作为一个数据集返回 /// </summary> /// <param name="sql">Select 语句数组</param> /// <param name="tableName">TableName</param> /// <returns>数据集 DataSet</returns> public DataSet RetriveDataSet(string[] sql, params string[] tableName) { int sqlLength; sqlLength = sql.Length; if ( sqlLength == 0) { #if DEBUG System.Diagnostics.Debug.WriteLine("sql 为空"); #endif return null; } using(sqlConnection = this.GetSqlConnection()) { if (sqlConnection == null) return null; DataSet ds = new DataSet(); int tableNameLength = tableName.Length; for (int i = 0; i < sqlLength; i++) { using(SqlDataAdapter da = new SqlDataAdapter(sql[i], sqlConnection)) { try { if (i < tableNameLength) da.Fill(ds, tableName[i]); else da.Fill(ds, "table" + i); } catch (Exception ex) { #if DEBUG System.Diagnostics.Debug.WriteLine(ex.ToString()); #endif return null; } } } return ds; } } public DataSet UpdateDataSet(string sql, DataSet hasChangesDataSet) { return null; } public void Dispose() { this.connectionString = null; this.sqlCommand.Dispose(); this.sqlConnection.Dispose(); } } } OdbcDataProvider.cs 提供ODBC连接访问的类 #define DEBUG using System; using System.Data; using System.Data.Odbc; namespace FenceKing.DataProviders { /// <summary> /// OdbcDataProvider 的摘要说明。 /// </summary> internal class OdbcDataProvider : IDataProvider { private System.Data.Odbc.OdbcConnection odbcConnection; private System.Data.Odbc.OdbcCommand odbcCommand; private string connectionString; public OdbcDataProvider() : this(null) { // // TODO: 在此处添加构造函数逻辑 // } public OdbcDataProvider(string connectionString) { if (connectionString == null || connectionString.Trim() == string.Empty) { System.Configuration.AppSettingsReader configurationAppSettings = new System.Configuration.AppSettingsReader(); this.connectionString = (string)(configurationAppSettings.GetValue("odbcConnectionString", typeof(string))); } else { this.connectionString = connectionString; } } /// <summary> /// Oracle 连接字符串 "User Id=southfence;Data Source=FENCEORA;Password=southfence;Persist Security Info=true;" /// </summary> public string ConnectionString { get { return this.connectionString; } set { this.connectionString = value; } } /// <summary> /// 返回一个带有连接字符串的Odbc Connection. /// </summary> /// <returns>OracleConnection</returns> private OdbcConnection GetOdbcConnection() { try { return new OdbcConnection(this.connectionString); } catch (Exception ex) { #if DEBUG System.Diagnostics.Debug.WriteLine(ex.ToString()); #endif return null; } } /// <summary> /// 对于 UPDATE、INSERT 和 DELETE 语句,返回值为该命令所影响的行数。对于其他所有类型的语句,返回值为 -1 /// </summary> /// <param name="Sql">UPDATE、INSERT 和 DELETE 语句</param> public int ExecuteNonQuery(string sql) { using(odbcConnection = this.GetOdbcConnection()) { if (odbcConnection == null) return -1; try { if (odbcConnection.State == System.Data.ConnectionState.Closed) odbcConnection.Open(); odbcCommand = new OdbcCommand(sql, odbcConnection); return odbcCommand.ExecuteNonQuery(); } catch (Exception ex) { #if DEBUG System.Diagnostics.Debug.WriteLine(ex.ToString()); #endif return -1; } } } /// <summary> /// 执行查询,并将查询返回的结果集中第一行的第一列作为 .NET Framework 数据类型返回。忽略额外的列或行。 /// </summary> /// <param name="sql">SELECT 语句</param> /// <returns>.NET Framework 数据类型形式的结果集第一行的第一列;如果结果集为空或结果为 REF CURSOR,则为空引用 </returns> public object ExecuteScalar(string sql) { using(odbcConnection = this.GetOdbcConnection()) { if (odbcConnection == null) return null; try { if (odbcConnection.State == System.Data.ConnectionState.Closed) odbcConnection.Open(); odbcCommand = new OdbcCommand(sql, odbcConnection); return odbcCommand.ExecuteScalar(); } catch (Exception ex) { #if DEBUG System.Diagnostics.Debug.WriteLine(ex.ToString()); #endif return null; } } } /// <summary> /// 执行单Sql语句查询,并将查询返回的结果作为一个数据集返回 /// </summary> /// <param name="selectSql">SELECT 语句</param> /// <returns>数据集 DataSet</returns> public DataSet RetriveDataSet(string sql) { if (sql == null || sql == string.Empty) { #if DEBUG System.Diagnostics.Debug.WriteLine("sql 为空"); #endif return null; } using(odbcConnection = this.GetOdbcConnection()) { if (odbcConnection == null) return null; using( OdbcDataAdapter da = new OdbcDataAdapter(sql, odbcConnection)) { DataSet ds = new DataSet(); try { da.Fill(ds); } catch (Exception ex) { #if DEBUG System.Diagnostics.Debug.WriteLine(ex.ToString()); #endif return null; } return ds; } } } /// <summary> /// 执行Sql数组语句查询,并将查询返回的结果作为一个数据集返回 /// </summary> /// <param name="sql">Select 语句数组</param> /// <param name="tableName">TableName</param> /// <returns>数据集 DataSet</returns> public DataSet RetriveDataSet(string[] sql, params string[] tableName) { int sqlLength; sqlLength = sql.Length; if ( sqlLength == 0) { #if DEBUG System.Diagnostics.Debug.WriteLine("sql 为空"); #endif return null; } using(odbcConnection = this.GetOdbcConnection()) { if (odbcConnection == null) return null; DataSet ds = new DataSet(); int tableNameLength = tableName.Length; for (int i = 0; i < sqlLength; i++) { using(OdbcDataAdapter da = new OdbcDataAdapter(sql[i], odbcConnection)) { try { if (i < tableNameLength) da.Fill(ds, tableName[i]); else da.Fill(ds, "table" + i); } catch (Exception ex) { #if DEBUG System.Diagnostics.Debug.WriteLine(ex.ToString()); #endif } } } return ds; } } public DataSet UpdateDataSet(string sql, DataSet hasChangesDataSet) { return null; } public void Dispose() { this.connectionString = null; this.odbcCommand.Dispose(); this.odbcConnection.Dispose(); } } }
联盟快卖 商人,生意人,待创业人士在此可以共赢互利 期待你的加入 群号:140809277
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· AI与.NET技术实操系列:基于图像分类模型对图像进行分类
· go语言实现终端里的倒计时
· 如何编写易于单元测试的代码
· 10年+ .NET Coder 心语,封装的思维:从隐藏、稳定开始理解其本质意义
· .NET Core 中如何实现缓存的预热?
· 25岁的心里话
· 闲置电脑爆改个人服务器(超详细) #公网映射 #Vmware虚拟网络编辑器
· 零经验选手,Compose 一天开发一款小游戏!
· 通过 API 将Deepseek响应流式内容输出到前端
· AI Agent开发,如何调用三方的API Function,是通过提示词来发起调用的吗