public static DataSet CallStoredProcedure(DBInstance dbInstance, string _Sql, ParameterItem[] _inParameters, ParameterItem[] _outParameters)
{
List<SqlParameter> parameters = new List<SqlParameter>();
try
{
if (_inParameters != null)
{
foreach (ParameterItem item in _inParameters)
{
if (item != null)
{
if (item.ParameterName != null && item.ParameterValue != null)
{
SqlParameter parm = new SqlParameter(item.ParameterName, SqlDbType.VarChar);
parm.Direction = ParameterDirection.Input;
parm.Value = item.ParameterValue;
parameters.Add(parm);
}
}
}
}
if (_outParameters != null)
{
foreach (ParameterItem item in _outParameters)
{
if (item != null)
{
if (item.ParameterName != null)
{
SqlParameter parm = new SqlParameter(item.ParameterName, SqlDbType.VarChar);//�д�
parm.Direction = ParameterDirection.Output;
parameters.Add(parm);
}
}
}
}
DataSet ds = SqlHelper.ExecuteQuery(dbInstance.Connection.ConnectionString, CommandType.StoredProcedure, _Sql, parameters.ToArray());
return ds;
}
catch (UtilException ex)
{
throw ex;
}
catch (Exception ex)
{
throw new UtilException(ex.Message, ex);
}
}
/// <summary>
/// Execute a select query that will return a result set
/// </summary>
public DataSet ExecuteQuery(string cmdText, ParameterItem[] commandParameters)
{
//Create the command and connection
OleDbCommand cmd = new OleDbCommand();
OleDbConnection conn = new OleDbConnection(ConnectionString);
try
{
//Prepare the command to execute
if (conn.State != ConnectionState.Open)
conn.Open();
DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string sheetName = dt.Rows[0]["TABLE_NAME"].ToString();
Regex r = new Regex(@"\[\w*\$\]", RegexOptions.IgnoreCase);
//string a = r.Match(cmdText, 0).Value;
//string b = string.Format("[{0}]", sheetName);
//string c = cmdText.Replace(a, b);
//PrepareCommand(cmd, conn, null, CommandType.Text, cmdText.Replace(r.Match(cmdText, 0).Value, string.Format("[{0}]", sheetName)), commandParameters);
PrepareCommand(cmd, conn, null, CommandType.Text, cmdText, commandParameters);
OleDbDataAdapter adp = new OleDbDataAdapter(cmd);
//Execute the query, fill dataset
DataSet ds = new DataSet();
adp.Fill(ds);
cmd.Parameters.Clear();
return ds;
}
catch
{
//If an error occurs close the connection as the reader will not be used and we expect it to close the connection
throw;
}
finally
{
conn.Close();
}
}
private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, ParameterItem[] commandParameters)
{
//Open the connection if required
if (conn.State != ConnectionState.Open)
conn.Open();
//Set up the command
cmd.Connection = conn;
cmd.CommandText = cmdText;
cmd.CommandType = cmdType;
cmd.Transaction = trans;
//Bind it to the transaction if it exists
//if (trans != null)
// cmd.Transaction = trans;
// Bind the parameters passed in
if (commandParameters != null)
{
foreach (ParameterItem item in commandParameters)
{
if (item != null) //ADD BY Andy_liu 2007-3-12 14:33:48
{
if (item.ParameterName != null && item.ParameterValue != null)
{
SqlParameter parm = new SqlParameter(item.ParameterName, item.ParameterValue);
cmd.Parameters.Add(parm);
}
}
}
}
}
public static DataSet ExecuteQuery(string cmdText, ParameterItem[] commandParameters)
{
//Create the command and connection
SqlCommand cmd = new SqlCommand();
SqlConnection conn = new SqlConnection(ConnectionString);
try
{
//Prepare the command to execute
PrepareCommand(cmd, conn, null, CommandType.Text, cmdText, commandParameters);
SqlDataAdapter adp = new SqlDataAdapter(cmd);
//Execute the query, fill dataset
DataSet ds = new DataSet();
adp.Fill(ds);
cmd.Parameters.Clear();
return ds;
}
catch
{
//If an error occurs close the connection as the reader will not be used and we expect it to close the connection
throw;
}
finally
{
conn.Close();
}
}