public System.Data.DataSet RunProcedure(string storedProcName, System.Data.IDataParameter[] parameters, string tableName) { System.Data.DataSet result; using (System.Data.SqlClient.SqlConnection sqlConnection = new System.Data.SqlClient.SqlConnection(this.connectionString)) { System.Data.DataSet dataSet = new System.Data.DataSet(); sqlConnection.Open(); new System.Data.SqlClient.SqlDataAdapter { SelectCommand = DbHelperSQLP.BuildQueryCommand(sqlConnection, storedProcName, parameters) }.Fill(dataSet, tableName); sqlConnection.Close(); result = dataSet; } return(result); }
public void ExecuteSqlTranWithIndentity(Hashtable SQLStringList) { using (System.Data.SqlClient.SqlConnection sqlConnection = new System.Data.SqlClient.SqlConnection(this.connectionString)) { sqlConnection.Open(); using (System.Data.SqlClient.SqlTransaction sqlTransaction = sqlConnection.BeginTransaction()) { System.Data.SqlClient.SqlCommand sqlCommand = new System.Data.SqlClient.SqlCommand(); try { int num = 0; foreach (DictionaryEntry dictionaryEntry in SQLStringList) { string cmdText = dictionaryEntry.Key.ToString(); System.Data.SqlClient.SqlParameter[] array = (System.Data.SqlClient.SqlParameter[])dictionaryEntry.Value; System.Data.SqlClient.SqlParameter[] array2 = array; for (int i = 0; i < array2.Length; i++) { System.Data.SqlClient.SqlParameter sqlParameter = array2[i]; if (sqlParameter.Direction == System.Data.ParameterDirection.InputOutput) { sqlParameter.Value = num; } } DbHelperSQLP.PrepareCommand(sqlCommand, sqlConnection, sqlTransaction, cmdText, array); int num2 = sqlCommand.ExecuteNonQuery(); array2 = array; for (int i = 0; i < array2.Length; i++) { System.Data.SqlClient.SqlParameter sqlParameter = array2[i]; if (sqlParameter.Direction == System.Data.ParameterDirection.Output) { num = Convert.ToInt32(sqlParameter.Value); } } sqlCommand.Parameters.Clear(); } sqlTransaction.Commit(); } catch { sqlTransaction.Rollback(); throw; } } } }
public System.Data.SqlClient.SqlDataReader ExecuteReader(string SQLString, params System.Data.SqlClient.SqlParameter[] cmdParms) { System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(this.connectionString); System.Data.SqlClient.SqlCommand sqlCommand = new System.Data.SqlClient.SqlCommand(); System.Data.SqlClient.SqlDataReader result; try { DbHelperSQLP.PrepareCommand(sqlCommand, conn, null, SQLString, cmdParms); System.Data.SqlClient.SqlDataReader sqlDataReader = sqlCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection); sqlCommand.Parameters.Clear(); result = sqlDataReader; } catch (System.Data.SqlClient.SqlException ex) { throw ex; } return(result); }
public int ExecuteSql(string SQLString, params System.Data.SqlClient.SqlParameter[] cmdParms) { int result; using (System.Data.SqlClient.SqlConnection sqlConnection = new System.Data.SqlClient.SqlConnection(this.connectionString)) { using (System.Data.SqlClient.SqlCommand sqlCommand = new System.Data.SqlClient.SqlCommand()) { try { DbHelperSQLP.PrepareCommand(sqlCommand, sqlConnection, null, SQLString, cmdParms); int num = sqlCommand.ExecuteNonQuery(); sqlCommand.Parameters.Clear(); result = num; } catch (System.Data.SqlClient.SqlException ex) { throw ex; } } } return(result); }
public System.Data.DataSet Query(string SQLString, params System.Data.SqlClient.SqlParameter[] cmdParms) { System.Data.DataSet result; using (System.Data.SqlClient.SqlConnection sqlConnection = new System.Data.SqlClient.SqlConnection(this.connectionString)) { System.Data.SqlClient.SqlCommand sqlCommand = new System.Data.SqlClient.SqlCommand(); DbHelperSQLP.PrepareCommand(sqlCommand, sqlConnection, null, SQLString, cmdParms); using (System.Data.SqlClient.SqlDataAdapter sqlDataAdapter = new System.Data.SqlClient.SqlDataAdapter(sqlCommand)) { System.Data.DataSet dataSet = new System.Data.DataSet(); try { sqlDataAdapter.Fill(dataSet, "ds"); sqlCommand.Parameters.Clear(); } catch (System.Data.SqlClient.SqlException ex) { throw new Exception(ex.Message); } result = dataSet; } } return(result); }
private static System.Data.SqlClient.SqlCommand BuildIntCommand(System.Data.SqlClient.SqlConnection connection, string storedProcName, System.Data.IDataParameter[] parameters) { System.Data.SqlClient.SqlCommand sqlCommand = DbHelperSQLP.BuildQueryCommand(connection, storedProcName, parameters); sqlCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("ReturnValue", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue, false, 0, 0, string.Empty, System.Data.DataRowVersion.Default, null)); return(sqlCommand); }
public int ExecuteSqlTran(List <CommandInfo> cmdList) { int result; using (System.Data.SqlClient.SqlConnection sqlConnection = new System.Data.SqlClient.SqlConnection(this.connectionString)) { sqlConnection.Open(); using (System.Data.SqlClient.SqlTransaction sqlTransaction = sqlConnection.BeginTransaction()) { System.Data.SqlClient.SqlCommand sqlCommand = new System.Data.SqlClient.SqlCommand(); try { int num = 0; foreach (CommandInfo current in cmdList) { string commandText = current.CommandText; System.Data.SqlClient.SqlParameter[] cmdParms = (System.Data.SqlClient.SqlParameter[])current.Parameters; DbHelperSQLP.PrepareCommand(sqlCommand, sqlConnection, sqlTransaction, commandText, cmdParms); if (current.EffentNextType == EffentNextType.WhenHaveContine || current.EffentNextType == EffentNextType.WhenNoHaveContine) { if (current.CommandText.ToLower().IndexOf("count(") == -1) { sqlTransaction.Rollback(); result = 0; return(result); } object obj = sqlCommand.ExecuteScalar(); if (obj == null && obj == DBNull.Value) { } bool flag = Convert.ToInt32(obj) > 0; if (current.EffentNextType == EffentNextType.WhenHaveContine && !flag) { sqlTransaction.Rollback(); result = 0; return(result); } if (current.EffentNextType == EffentNextType.WhenNoHaveContine && flag) { sqlTransaction.Rollback(); result = 0; return(result); } } else { int num2 = sqlCommand.ExecuteNonQuery(); num += num2; if (current.EffentNextType == EffentNextType.ExcuteEffectRows && num2 == 0) { sqlTransaction.Rollback(); result = 0; return(result); } sqlCommand.Parameters.Clear(); } } sqlTransaction.Commit(); result = num; } catch { sqlTransaction.Rollback(); throw; } } } return(result); }
public int ExecuteSqlTran(List <CommandInfo> list, List <CommandInfo> oracleCmdSqlList) { int result; using (System.Data.SqlClient.SqlConnection sqlConnection = new System.Data.SqlClient.SqlConnection(this.connectionString)) { sqlConnection.Open(); System.Data.SqlClient.SqlCommand sqlCommand = new System.Data.SqlClient.SqlCommand(); sqlCommand.Connection = sqlConnection; System.Data.SqlClient.SqlTransaction sqlTransaction = sqlConnection.BeginTransaction(); sqlCommand.Transaction = sqlTransaction; try { foreach (CommandInfo current in list) { string commandText = current.CommandText; System.Data.SqlClient.SqlParameter[] cmdParms = (System.Data.SqlClient.SqlParameter[])current.Parameters; DbHelperSQLP.PrepareCommand(sqlCommand, sqlConnection, sqlTransaction, commandText, cmdParms); if (current.EffentNextType == EffentNextType.SolicitationEvent) { if (current.CommandText.ToLower().IndexOf("count(") == -1) { sqlTransaction.Rollback(); throw new Exception("违背要求" + current.CommandText + "必须符合select count(..的格式"); } object obj = sqlCommand.ExecuteScalar(); if (obj == null && obj == DBNull.Value) { } bool flag = Convert.ToInt32(obj) > 0; if (flag) { current.OnSolicitationEvent(); } } if (current.EffentNextType == EffentNextType.WhenHaveContine || current.EffentNextType == EffentNextType.WhenNoHaveContine) { if (current.CommandText.ToLower().IndexOf("count(") == -1) { sqlTransaction.Rollback(); throw new Exception("SQL:违背要求" + current.CommandText + "必须符合select count(..的格式"); } object obj = sqlCommand.ExecuteScalar(); if (obj == null && obj == DBNull.Value) { } bool flag = Convert.ToInt32(obj) > 0; if (current.EffentNextType == EffentNextType.WhenHaveContine && !flag) { sqlTransaction.Rollback(); throw new Exception("SQL:违背要求" + current.CommandText + "返回值必须大于0"); } if (current.EffentNextType == EffentNextType.WhenNoHaveContine && flag) { sqlTransaction.Rollback(); throw new Exception("SQL:违背要求" + current.CommandText + "返回值必须等于0"); } } else { int num = sqlCommand.ExecuteNonQuery(); if (current.EffentNextType == EffentNextType.ExcuteEffectRows && num == 0) { sqlTransaction.Rollback(); throw new Exception("SQL:违背要求" + current.CommandText + "必须有影响行"); } sqlCommand.Parameters.Clear(); } } string conStr = PEIS.DBUtility.PubConstant.GetConnectionString("ConnectionStringPPC"); if (!OracleHelper.ExecuteSqlTran(conStr, oracleCmdSqlList)) { sqlTransaction.Rollback(); throw new Exception("Oracle执行失败"); } sqlTransaction.Commit(); result = 1; } catch (System.Data.SqlClient.SqlException ex) { sqlTransaction.Rollback(); throw ex; } catch (Exception ex2) { sqlTransaction.Rollback(); throw ex2; } } return(result); }