/// <summary> /// 根据数据库类型获得某种数据库系列对象创建工厂 /// </summary> /// <param name="databaseType">数据库类型</param> /// <returns>创建工厂</returns> public static DbProviderFactory GetDbProviderFactory(AgileDatabaseType databaseType = AgileDatabaseType.SqlServer) { DbProviderFactory instance = null; switch (databaseType) { case AgileDatabaseType.SqlServer: instance = SqlClientFactory.Instance; break; /*case AgileDatabaseType.OleDb: * instance = OleDbFactory.Instance; * break;*/ case AgileDatabaseType.Oracle: instance = OracleClientFactory.Instance; break; case AgileDatabaseType.MySql: instance = MySqlClientFactory.Instance; break; } return(instance); }
/// <summary> /// 执行SELECT以及返回数据集的存储过程,返回读取器。读取器关闭时,连接会自动关闭。不建议在BS项目中使用,因为传统的DataSet方式要更容易控制缓存。 /// </summary> /// <param name="connectionString">数据库连接字符串</param> /// <param name="sentence">SQL命令或存储过程名</param> /// <param name="parameters">参数数组</param> /// <param name="databaseType">数据库类型</param> /// <returns>读取器</returns> public static DbDataReader ExecuteReader(string connectionString, string sentence, DbParameter[] parameters = null, AgileDatabaseType databaseType = AgileDatabaseType.SqlServer) { DbProviderFactory factory = GetDbProviderFactory(databaseType); DbConnection dbConnection = factory.CreateConnection(); dbConnection.ConnectionString = connectionString; try { using (DbCommand dbCommand = dbConnection.CreateCommand()) { dbCommand.CommandText = sentence; dbCommand.CommandTimeout = 0; if (parameters != null) { dbCommand.Parameters.AddRange(parameters); } dbConnection.Open(); return(dbCommand.ExecuteReader(CommandBehavior.CloseConnection)); } } catch (Exception exception) { //如果发生异常才关闭连接,然后抛出异常信息。如果未发生异常,不能关闭连接,连接是由客户端程序员控制。 dbConnection.Close(); throw exception; } }
/// <summary> /// 执行INSERT、UPDATE、DELETE以及不返回数据集的存储过程 /// </summary> /// <param name="connectionString">数据库连接字符串</param> /// <param name="sentence">SQL命令或存储过程名</param> /// <param name="parameters">参数数组</param> /// <param name="smartDatabaseType">数据库类型</param> /// <returns>影响的行数</returns> public static int ExecuteNonQuery(string connectionString, string sentence, DbParameter[] parameters = null, AgileDatabaseType databaseType = AgileDatabaseType.SqlServer) { DbProviderFactory factory = GetDbProviderFactory(databaseType); using (DbConnection dbConnection = factory.CreateConnection()) { dbConnection.ConnectionString = connectionString; using (DbCommand dbCommand = dbConnection.CreateCommand()) { dbCommand.CommandText = sentence; //操作超时时间,目前设置为3分钟 dbCommand.CommandTimeout = 180; if (parameters != null) { dbCommand.Parameters.AddRange(parameters); } dbConnection.Open(); return(dbCommand.ExecuteNonQuery()); } } }
/// <summary> /// 按事务方式执行SmartSqlCommandLine命令列表 /// </summary> /// <param name="connectionString">数据库连接字符串</param> /// <param name="smartSqlCommandLineList">命令列表</param> /// <param name="smartDatabaseType">数据库类型</param> public static void ExecuteWithTransaction(string connectionString, AgileSQLCommand sqlCommand, AgileDatabaseType databaseType = AgileDatabaseType.SqlServer) { DbProviderFactory factory = GetDbProviderFactory(databaseType); using (DbConnection dbConnection = factory.CreateConnection()) { dbConnection.ConnectionString = connectionString; dbConnection.Open(); using (DbTransaction dbTransaction = dbConnection.BeginTransaction()) { try { using (DbCommand dbCommand = dbConnection.CreateCommand()) { dbCommand.Transaction = dbTransaction; dbCommand.CommandText = sqlCommand.SqlSentence; //操作超时时间,目前设置为3分钟 dbCommand.CommandTimeout = 180; if (sqlCommand.Parameters != null) { dbCommand.Parameters.AddRange(sqlCommand.Parameters); } //认为需要回滚的事务都是不需要返回结果的命令 dbCommand.ExecuteNonQuery(); if (sqlCommand.RollbackAction != null) { try { sqlCommand.RollbackAction(); } catch (Exception error) { dbTransaction.Rollback(); if (!string.IsNullOrWhiteSpace(sqlCommand.Error)) { error = new Exception(sqlCommand.Error); } throw error; } } } dbTransaction.Commit(); } catch (Exception err) { dbTransaction.Rollback(); throw err; } } } }
/// <summary> /// 填充数据集 /// </summary> /// <param name="dataTable">数据表(可以是强类型的数据表)</param> /// <param name="connectionString">数据库连接字符串</param> /// <param name="sentence">SQL语句或存储过程名</param> /// <param name="parameters">参数数组</param> public static void Fill(string connectionString, string sentence, DataTable dataTable, DbParameter[] parameters = null, AgileDatabaseType databaseType = AgileDatabaseType.SqlServer) { DbProviderFactory factory = GetDbProviderFactory(databaseType); DbConnection dbConnection = factory.CreateConnection(); dbConnection.ConnectionString = connectionString; using (DbCommand dbCommand = dbConnection.CreateCommand()) { dbCommand.CommandText = sentence; dbCommand.CommandTimeout = 0; if (parameters != null) { dbCommand.Parameters.AddRange(parameters); } using (DbDataAdapter dbDataAdapter = factory.CreateDataAdapter()) { dbDataAdapter.SelectCommand = dbCommand; dbDataAdapter.Fill(dataTable); } } }
/// <summary> /// 返回数据集的第一行第一列。数据库中为Null或空,都返回Null /// </summary> /// <param name="connectionString">数据库连接字符串</param> /// <param name="sentence">SQL命令或存储过程名</param> /// <param name="parameters">参数数组</param> /// <returns>数据集的第一行第一列</returns> public static object ExecuteScalar(string connectionString, string sentence, DbParameter[] parameters = null, AgileDatabaseType databaseType = AgileDatabaseType.SqlServer) { object result = null; DbProviderFactory factory = GetDbProviderFactory(databaseType); using (DbConnection dbConnection = factory.CreateConnection()) { dbConnection.ConnectionString = connectionString; using (DbCommand dbCommand = dbConnection.CreateCommand()) { dbCommand.CommandText = sentence; dbCommand.CommandTimeout = 0; if (parameters != null) { dbCommand.Parameters.AddRange(parameters); } dbConnection.Open(); result = dbCommand.ExecuteScalar(); } } //如果返回的是DBNull类型或者是空,则返回null if (result == DBNull.Value || result.ToString().Trim().Length == 0) { result = null; } return(result); }