/// <summary> /// 关闭一个数据库连接 /// </summary> /// <param name="dbConn">数据库连接对象</param> public static void CloseConnection(DbConnection dbConn) { if (dbConn == null || dbConn.State != ConnectionState.Open) { return; } dbConn.Close(); dbConn.Dispose(); dbConn = null; }
private bool CreateAnonymous(out string errorMsg) { System.Data.Common.DbConnection dbConnection = null; bool result; try { System.Data.Common.DbConnection dbConnection2; dbConnection = (dbConnection2 = new System.Data.SqlClient.SqlConnection(this.GetConnectionString())); try { System.Data.Common.DbCommand dbCommand = new System.Data.SqlClient.SqlCommand(); dbCommand.Connection = dbConnection; dbCommand.CommandType = System.Data.CommandType.Text; dbCommand.CommandText = "INSERT INTO aspnet_Users (UserName, LoweredUserName, IsAnonymous, UserRole, LastActivityDate, Password, PasswordFormat, PasswordSalt, IsApproved, IsLockedOut, CreateDate, LastLoginDate, LastPasswordChangedDate, LastLockoutDate, FailedPasswordAttemptCount, FailedPasswordAttemptWindowStart, FailedPasswordAnswerAttemptCount, FailedPasswordAnswerAttemptWindowStart) VALUES ('Anonymous', LOWER('Anonymous'), 1, @UserRole, @CreateDate, 'DVZTktxeMzDtXR7eik7Cdw==', 0, '', 1, 0, @CreateDate, @CreateDate, @CreateDate, CONVERT( datetime, '17540101', 112 ), 0, CONVERT( datetime, '17540101', 112 ), 0, CONVERT( datetime, '17540101', 112 ))"; dbCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@UserRole", UserRole.Anonymous)); dbCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@CreateDate", System.DateTime.Now)); dbConnection.Open(); dbCommand.ExecuteNonQuery(); dbConnection.Close(); } finally { if (dbConnection2 != null) { ((System.IDisposable)dbConnection2).Dispose(); } } errorMsg = null; result = true; } catch (System.Data.SqlClient.SqlException ex) { errorMsg = ex.Message; if (dbConnection != null && dbConnection.State != System.Data.ConnectionState.Closed) { dbConnection.Close(); dbConnection.Dispose(); } result = false; } return(result); }
/// <summary> /// Close a connection. /// </summary> /// <param name="Connection">The connection.</param> public void Close(DbConnection Connection) { // Check if the connection is open. if (Connection != null && Connection.State == ConnectionState.Open) { // Check if the pool has not been disposed. if (!_Disposed) { // Lock this object. lock (this) { // Check if the pool has not been disposed. if (!_Disposed) { // Add the connection to the pool. _Pool.Add(Connection); // Return from the method. return; } } } // Dispose of the connection. Connection.Dispose(); } }
public override void CloseConnection(IDbConnection conn) { base.CloseConnection(conn); conn.Dispose(); }
private bool CreateAdministrator(out int newUserId, out string errorMsg) { System.Data.Common.DbConnection dbConnection = null; System.Data.Common.DbTransaction dbTransaction = null; bool result; try { System.Data.Common.DbConnection dbConnection2; dbConnection = (dbConnection2 = new System.Data.SqlClient.SqlConnection(this.GetConnectionString())); try { dbConnection.Open(); RolesConfiguration rolesConfiguration = HiConfiguration.GetConfig().RolesConfiguration; System.Data.Common.DbCommand dbCommand = dbConnection.CreateCommand(); dbTransaction = dbConnection.BeginTransaction(); dbCommand.Connection = dbConnection; dbCommand.Transaction = dbTransaction; dbCommand.CommandType = System.Data.CommandType.Text; dbCommand.CommandText = "SELECT RoleId FROM aspnet_Roles WHERE [LoweredRoleName] = LOWER(@RoleName)"; dbCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@RoleName", rolesConfiguration.SystemAdministrator)); System.Guid guid = (System.Guid)dbCommand.ExecuteScalar(); dbCommand.Parameters["@RoleName"].Value = rolesConfiguration.Manager; System.Guid guid2 = (System.Guid)dbCommand.ExecuteScalar(); dbCommand.Parameters.Clear(); dbCommand.CommandText = "INSERT INTO aspnet_Users (UserName, LoweredUserName, IsAnonymous, UserRole, LastActivityDate, Password, PasswordFormat, PasswordSalt, IsApproved, IsLockedOut, CreateDate, LastLoginDate, LastPasswordChangedDate, LastLockoutDate, FailedPasswordAttemptCount, FailedPasswordAttemptWindowStart, FailedPasswordAnswerAttemptCount, FailedPasswordAnswerAttemptWindowStart, Email, LoweredEmail) VALUES (@Username, LOWER(@Username), 0, @UserRole, @CreateDate, @Password, @PasswordFormat, @PasswordSalt, 1, 0, @CreateDate, @CreateDate, @CreateDate, CONVERT( datetime, '17540101', 112 ), 0, CONVERT( datetime, '17540101', 112 ), 0, CONVERT( datetime, '17540101', 112 ), @Email, LOWER(@Email));SELECT @@IDENTITY"; dbCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Username", this.username)); dbCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@UserRole", UserRole.SiteManager)); dbCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@CreateDate", System.DateTime.Now)); dbCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Password", this.password)); dbCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@PasswordFormat", System.Web.Security.MembershipPasswordFormat.Clear)); dbCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@PasswordSalt", "")); dbCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Email", this.email)); newUserId = System.Convert.ToInt32(dbCommand.ExecuteScalar()); dbCommand.Parameters.Clear(); dbCommand.CommandText = "INSERT INTO aspnet_Managers(UserId) VALUES(@UserId)"; dbCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@UserId", newUserId)); dbCommand.ExecuteNonQuery(); dbCommand.CommandText = "INSERT INTO aspnet_UsersInRoles(UserId, RoleId) VALUES(@UserId, @RoleId)"; dbCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@RoleId", guid2)); dbCommand.ExecuteNonQuery(); dbCommand.Parameters["@RoleId"].Value = guid; dbCommand.ExecuteNonQuery(); dbTransaction.Commit(); dbConnection.Close(); } finally { if (dbConnection2 != null) { ((System.IDisposable)dbConnection2).Dispose(); } } errorMsg = null; result = true; } catch (System.Data.SqlClient.SqlException ex) { errorMsg = ex.Message; newUserId = 0; if (dbTransaction != null) { try { dbTransaction.Rollback(); } catch (System.Exception ex2) { errorMsg = ex2.Message; } } if (dbConnection != null && dbConnection.State != System.Data.ConnectionState.Closed) { dbConnection.Close(); dbConnection.Dispose(); } result = false; } return(result); }
protected void DisposeConnection(DbConnection connection) { if (connection != null) connection.Dispose(); }
public void CloseConnection(DbConnection conn) { if (conn != null & conn.State != ConnectionState.Closed) { try { conn.Close(); conn.Dispose(); } catch (Exception ex) { throw ex; } } }
private bool ExecuteTest() { this.errorMsgs = new System.Collections.Generic.List <string>(); System.Data.Common.DbTransaction dbTransaction = null; System.Data.Common.DbConnection dbConnection = null; string item; try { if (this.ValidateConnectionStrings(out item)) { System.Data.Common.DbConnection dbConnection2; dbConnection = (dbConnection2 = new System.Data.SqlClient.SqlConnection(this.GetConnectionString())); try { dbConnection.Open(); System.Data.Common.DbCommand dbCommand = dbConnection.CreateCommand(); dbTransaction = dbConnection.BeginTransaction(); dbCommand.Connection = dbConnection; dbCommand.Transaction = dbTransaction; dbCommand.CommandText = "CREATE TABLE installTest(Test bit NULL)"; dbCommand.ExecuteNonQuery(); dbCommand.CommandText = "DROP TABLE installTest"; dbCommand.ExecuteNonQuery(); dbTransaction.Commit(); dbConnection.Close(); goto IL_94; } finally { if (dbConnection2 != null) { ((System.IDisposable)dbConnection2).Dispose(); } } } this.errorMsgs.Add(item); IL_94 :; } catch (System.Exception ex) { this.errorMsgs.Add(ex.Message); if (dbTransaction != null) { try { dbTransaction.Rollback(); } catch (System.Exception ex2) { this.errorMsgs.Add(ex2.Message); } } if (dbConnection != null && dbConnection.State != System.Data.ConnectionState.Closed) { dbConnection.Close(); dbConnection.Dispose(); } } string folderPath = base.Request.MapPath(Globals.ApplicationPath + "/config/test.txt"); if (!Install.TestFolder(folderPath, out item)) { this.errorMsgs.Add(item); } try { Configuration configuration = System.Web.Configuration.WebConfigurationManager.OpenWebConfiguration(base.Request.ApplicationPath); if (configuration.ConnectionStrings.ConnectionStrings["HidistroSqlServer"].ConnectionString == "none") { configuration.ConnectionStrings.ConnectionStrings["HidistroSqlServer"].ConnectionString = "required"; } else { configuration.ConnectionStrings.ConnectionStrings["HidistroSqlServer"].ConnectionString = "none"; } configuration.Save(); } catch (System.Exception ex3) { this.errorMsgs.Add(ex3.Message); } folderPath = base.Request.MapPath(Globals.ApplicationPath + "/storage/test.txt"); if (!Install.TestFolder(folderPath, out item)) { this.errorMsgs.Add(item); } return(this.errorMsgs.Count == 0); }
private void CloseConnection(DbConnection dbConn) { try { if (dbConn != null && dbConn.State == ConnectionState.Open) { dbConn.Close(); dbConn.Dispose(); } } catch (Exception ex) { _logger.Error("Error closing DB connection: " + ex.Message); } }
/// <summary> /// Disposes the given database connection created by this instance. /// </summary> /// <param name="connection">The database connection to dispose.</param> public virtual void DisposeConnection(DbConnection connection) { if (connection != null) { connection.Dispose(); } }
public DbCommand getCommand(String sql) { DbCommand command = objectCommand(); if (command != null) { command.CommandText = sql; conn = getConnection(); try { command.Connection = conn; if (command.Connection != null && (command.Connection.State == ConnectionState.Broken || command.Connection.State == ConnectionState.Closed)) { command.Connection.Open(); } } catch (DataException) { conn.Dispose(); } } else command.Connection = getConnection(); return command; }
protected object ExecuteQuery(DbConnection DbConn, DbTransaction DbTrans, string spName, CommandType CmdType, Hashtable ParamTable, DataManager.ExecuteAction ExecAction) { if (this.ValidateCommandText(spName)) { try { if (DbConn.State != ConnectionState.Open) { DbConn.Open(); } DbCommand dbCommand = this.GetDbCommand(spName, DbConn); dbCommand.Transaction = DbTrans; try { dbCommand.CommandType = CmdType; if (ParamTable != null) { Array hashValue = Utils.GetHashValue(ParamTable); for (int index = 0; index < hashValue.Length; ++index) { dbCommand.Parameters.Add((object)(DbParameter)hashValue.GetValue(index)); } } if (ExecAction == DataManager.ExecuteAction.NonQuery) { long num = long.Parse(dbCommand.ExecuteNonQuery().ToString()); return (object)num; } else { object obj = dbCommand.ExecuteScalar(); return obj; } } catch (Exception ex) { throw ex; } finally { if (this.m_CloseConnection) { DbConn.Close(); dbCommand.Dispose(); } } } catch (Exception ex) { throw ex; } finally { if (this.m_CloseConnection) { DbConn.Dispose(); } } } else { throw new Exception("Command text empty"); } }
protected DataSet ExecuteDataset(DbConnection DbConn, DbTransaction DbTrans, string spName, CommandType CmdType, Hashtable ParamTable) { if (!this.ValidateCommandText(spName)) throw new Exception("Command text empty"); try { if (DbConn.State != ConnectionState.Open) { DbConn.Open(); } DbCommand dbCommand = this.GetDbCommand(spName, DbConn); dbCommand.Transaction = DbTrans; try { dbCommand.CommandType = CmdType; if (m_CommandTimeout > 0) { dbCommand.CommandTimeout = m_CommandTimeout; } if (ParamTable != null) { Array hashValue = Utils.GetHashValue(ParamTable); for (int index = 0; index < hashValue.Length; ++index) { dbCommand.Parameters.Add((object)(DbParameter)hashValue.GetValue(index)); } } DbDataAdapter dataAdapter = this.GetDataAdapter(); try { DataSet dataSet = new DataSet(); dataAdapter.SelectCommand = dbCommand; ((DataAdapter)dataAdapter).Fill(dataSet); return dataSet; } catch (Exception ex) { throw ex; } finally { dataAdapter.Dispose(); } } catch (Exception ex) { throw ex; } finally { if (this.m_CloseConnection) { DbConn.Close(); } dbCommand.Dispose(); } } catch (Exception ex) { throw ex; } finally { if (this.m_CloseConnection) { DbConn.Dispose(); } } }
/// <summary> /// Closes and disposes the given connection instance. /// </summary> /// <param name="connection">the connection isntance to be closed and disposed</param> public static void CloseDisposeConnection(DbConnection connection) { if (connection == null) { return; } try { CloseConnection(connection); connection.Dispose(); } catch (System.Exception e) { throw new PersistenceException("Could not close and dispose connection", e); } }
public static void Close(DbConnection conn) { conn.Close(); conn.Dispose(); }
private void Setup() { parametersList = new List<DbParameter>(); target = new DatabaseTraceListener_Accessor( storedProcedureName, logConnectionStringName, null, null); //target.connectionStringName = "TestConnectionString"; target.factory = MockRepository.GenerateStub<DbProviderFactory>(); target.connectionStringProvider = MockRepository.GenerateStub<IConfigurationValueProvider>(); target.connectionStringProvider.Stub((p) => p[target.connectionStringName]). Return(target.connectionStringName); connection = MockRepository.GenerateStub<DbConnection>(); command = MockRepository.GenerateStub<DbCommand>(); parameters = MockRepository.GenerateStub<DbParameterCollection>(); // Stubs for the connection and command target.factory.Stub( (f) => f.CreateConnection()).Return(connection); target.factory.Stub((f) => f.CreateCommand()).Return(command); // Definitely want to verify that Open is called connection.Stub((c) => connection.Open()); // Unfortunately can't verify Dispose got called connection.Stub((c) => connection.Dispose()); // Will verify later if ExecuteNonQuery is called command.Stub((c) => command.ExecuteNonQuery()).Return(1); command.Stub((c) => c.Dispose()); // Setup repetitive stub for the command parameters //DbParameter parameter = null; command.Stub((c) => c.Parameters).Repeat.Any().Return(parameters); for (int i = 0; i < 12; i++) { DbParameter parameter = MockRepository.GenerateStub<DbParameter>(); target.factory.Stub((f) => f.CreateParameter()).Return(parameter); parameters.Stub((p) => p.Add(parameter)).Do(new HandleParameterDelegate(HandleParameter)); } }
private void btnOpen_Click(object sender, EventArgs e) { btnClose_Click(sender, null); // main. string sFactory = cboProvider.Text.Trim(); try { m_provider = DbProviderFactories.GetFactory(sFactory); } catch (Exception ex) { OutLog(ex.Message); MessageBox.Show(this, ex.ToString(), this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (null == m_provider) return; try { // DbConnection. try { OutLog("Create DbConnection:"); m_conn = m_provider.CreateConnection(); OutLog(string.Format("\t{0}", m_conn.GetType().ToString())); } catch (Exception ex) { OutLog(ex.Message); MessageBox.Show(this, ex.ToString(), this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error); try { m_conn.Dispose(); } catch (Exception ex2) { OutLog(ex2.ToString()); } m_conn = null; return; } if (null == m_conn) return; // open. try { m_conn.ConnectionString = txtConnstr.Text.Trim(); m_conn.Open(); } catch (Exception ex) { OutLog(ex.Message); MessageBox.Show(this, ex.ToString(), this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error); try { m_conn.Dispose(); } catch (Exception ex2) { OutLog(ex2.ToString()); } m_conn = null; return; } } finally { if (null == m_conn) { m_provider = null; } } // done. bool isconn = true; btnOpen.Enabled = !isconn; btnClose.Enabled = isconn; btnExec.Enabled = isconn; cboProvider.Enabled = !isconn; txtConnstr.ReadOnly = isconn; OutLog("Db opened."); // show tables. DataTable dt = m_conn.GetSchema("Tables"); grdTable.DataSource = dt; m_CurTableName = null; tbcInput.SelectedIndex = 1; }
private void MultipleErrorHandling(DbConnection connection) { try { Console.WriteLine("MultipleErrorHandling {0}", connection.GetType().Name); Type expectedException = null; if (connection is SqlConnection) { ((SqlConnection)connection).InfoMessage += delegate (object sender, SqlInfoMessageEventArgs args) { Console.WriteLine("*** SQL CONNECTION INFO MESSAGE : {0} ****", args.Message); }; expectedException = typeof(SqlException); } connection.Open(); using (DbCommand command = connection.CreateCommand()) { command.CommandText = "PRINT N'0';\n" + "SELECT num = 1, str = 'ABC';\n" + "PRINT N'1';\n" + "RAISERROR('Error 1', 15, 1);\n" + "PRINT N'3';\n" + "SELECT num = 2, str = 'ABC';\n" + "PRINT N'4';\n" + "RAISERROR('Error 2', 15, 1);\n" + "PRINT N'5';\n" + "SELECT num = 3, str = 'ABC';\n" + "PRINT N'6';\n" + "RAISERROR('Error 3', 15, 1);\n" + "PRINT N'7';\n" + "SELECT num = 4, str = 'ABC';\n" + "PRINT N'8';\n" + "RAISERROR('Error 4', 15, 1);\n" + "PRINT N'9';\n" + "SELECT num = 5, str = 'ABC';\n" + "PRINT N'10';\n" + "RAISERROR('Error 5', 15, 1);\n" + "PRINT N'11';\n"; try { Console.WriteLine("**** ExecuteNonQuery *****"); command.ExecuteNonQuery(); } catch (Exception e) { PrintException(expectedException, e); } try { Console.WriteLine("**** ExecuteScalar ****"); command.ExecuteScalar(); } catch (Exception e) { PrintException(expectedException, e); } try { Console.WriteLine("**** ExecuteReader ****"); using (DbDataReader reader = command.ExecuteReader()) { bool moreResults = true; do { try { Console.WriteLine("NextResult"); moreResults = reader.NextResult(); } catch (Exception e) { PrintException(expectedException, e); } } while (moreResults); } } catch (Exception e) { PrintException(null, e); } } } catch (Exception e) { PrintException(null, e); } try { connection.Dispose(); } catch (Exception e) { PrintException(null, e); } }
/// <summary> /// 初始化类 /// </summary> public void InitClass() { if (_AppConn != null) { _AppConn.Dispose(); } if (_ErpConn != null) { _ErpConn.Dispose(); } /* * this.AppPath = ""; * AppTitle = "自动化立库管理系统"; * AppVersion = "1.0.0.1"; * AppConfigFile = "WMSConfig.xml"; * */ switch (_dbtApp) { case DataBaseType.dbtMSSQL: _AppConn = new System.Data.SqlClient.SqlConnection(); break; case DataBaseType.dbtOracle: _AppConn = new System.Data.OracleClient.OracleConnection(); break; case DataBaseType.dbtMySQL: _AppConn = new System.Data.OleDb.OleDbConnection(); break; case DataBaseType.dbtParadox: _AppConn = new System.Data.OleDb.OleDbConnection(); break; case DataBaseType.dbtMSAccess: _AppConn = new System.Data.OleDb.OleDbConnection(); break; } switch (_dbtErp) { case DataBaseType.dbtMSSQL: _ErpConn = new System.Data.SqlClient.SqlConnection(); break; case DataBaseType.dbtOracle: _ErpConn = new System.Data.OracleClient.OracleConnection(); break; case DataBaseType.dbtMySQL: _ErpConn = new System.Data.OleDb.OleDbConnection(); break; case DataBaseType.dbtParadox: _ErpConn = new System.Data.OleDb.OleDbConnection(); break; case DataBaseType.dbtMSAccess: _ErpConn = new System.Data.OleDb.OleDbConnection(); break; } }
/// <summary> /// Closes the connection. /// </summary> /// <param name="conn">The conn.</param> public void CloseConnection(DbConnection conn) { if (conn != null && conn.State != ConnectionState.Closed) { conn.Close(); conn.Dispose(); } }
private bool AddBuiltInRoles(out string errorMsg) { System.Data.Common.DbConnection dbConnection = null; System.Data.Common.DbTransaction dbTransaction = null; bool result; try { System.Data.Common.DbConnection dbConnection2; dbConnection = (dbConnection2 = new System.Data.SqlClient.SqlConnection(this.GetConnectionString())); try { dbConnection.Open(); System.Data.Common.DbCommand dbCommand = dbConnection.CreateCommand(); dbTransaction = dbConnection.BeginTransaction(); dbCommand.Connection = dbConnection; dbCommand.Transaction = dbTransaction; dbCommand.CommandType = System.Data.CommandType.Text; dbCommand.CommandText = "INSERT INTO aspnet_Roles(RoleName, LoweredRoleName) VALUES(@RoleName, LOWER(@RoleName))"; System.Data.Common.DbParameter value = new System.Data.SqlClient.SqlParameter("@RoleName", System.Data.SqlDbType.NVarChar, 256); dbCommand.Parameters.Add(value); RolesConfiguration rolesConfiguration = HiConfiguration.GetConfig().RolesConfiguration; dbCommand.Parameters["@RoleName"].Value = rolesConfiguration.Manager; dbCommand.ExecuteNonQuery(); dbCommand.Parameters["@RoleName"].Value = rolesConfiguration.Member; dbCommand.ExecuteNonQuery(); dbCommand.Parameters["@RoleName"].Value = rolesConfiguration.SystemAdministrator; dbCommand.ExecuteNonQuery(); dbTransaction.Commit(); dbConnection.Close(); } finally { if (dbConnection2 != null) { ((System.IDisposable)dbConnection2).Dispose(); } } errorMsg = null; result = true; } catch (System.Data.SqlClient.SqlException ex) { errorMsg = ex.Message; if (dbTransaction != null) { try { dbTransaction.Rollback(); } catch (System.Exception ex2) { errorMsg = ex2.Message; } } if (dbConnection != null && dbConnection.State != System.Data.ConnectionState.Closed) { dbConnection.Close(); dbConnection.Dispose(); } result = false; } return(result); }