using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Collections;
///
/// SqlHelper 的摘要说明
///
public class SQLHelper
{
// Fields
public static readonly string CONN_STRING;
private static Hashtable parmCache;
static SQLHelper()
{
//definition the Database Connection;
SQLHelper.CONN_STRING = "Data Source=.;Initial Catalog=two_company;Persist Security Info=True;User ID=sa;Password=sa";
SQLHelper.parmCache = Hashtable.Synchronized(new Hashtable());
}
public static void CacheParameters(string cacheKey, params SqlParameter[] cmdParms)
{
SQLHelper.parmCache[cacheKey] = cmdParms;
}
//clear the SqlCommand Params Set it Values is Null
public static void ClearParameterValues(params SqlParameter[] cmdParms)
{
SqlParameter[] parameterArray1 = cmdParms;
for (int num1 = 0; num1 < parameterArray1.Length; num1++)
{
SqlParameter parameter1 = parameterArray1[num1];
parameter1.Value = DBNull.Value;
}
}
public static SqlParameter CreateReturnParameter(string parameterName)
{
return new SqlParameter(parameterName, SqlDbType.Int, 4, ParameterDirection.ReturnValue, false, 0, 0, string.Empty, DataRowVersion.Default, null);
}
public static DataRow ExecuteDataRow(string cmdText)
{
DataSet set1 = SQLHelper.ExecuteDataset(cmdText);
if ((set1.Tables.Count > 0) && (set1.Tables[0].Rows.Count > 0))
{
//why Return First Row
return set1.Tables[0].Rows[0];
}
return null;
}
public static DataSet ExecuteDataset(string cmdText)
{
DataSet set2;
SqlCommand command1 = new SqlCommand();
SqlConnection connection1 = new SqlConnection(SQLHelper.CONN_STRING);
DataSet set1 = new DataSet();
try
{
//Often need to determine
if (connection1.State != ConnectionState.Open)
{
connection1.Open();
}
command1.Connection = connection1;
command1.CommandText = cmdText;
command1.CommandType = CommandType.Text;
SqlDataAdapter adapter1 = new SqlDataAdapter();
adapter1.SelectCommand = command1;
adapter1.Fill(set1);
set2 = set1;
}
catch
{
throw;
}
finally
{
connection1.Close();
}
return set2;
}
public static DataSet ExecuteDataset(SqlConnection connection, CommandType commandType, string commandText)
{
return SQLHelper.ExecuteDataset(connection, commandType, commandText, (SqlParameter[])null);
}
public static DataSet ExecuteDataset(SqlTransaction transaction, CommandType commandType, string commandText)
{
return SQLHelper.ExecuteDataset(transaction, commandType, commandText, (SqlParameter[])null);
}
public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText)
{
return SQLHelper.ExecuteDataset(connectionString, commandType, commandText, (SqlParameter[])null);
}
public static DataSet ExecuteDataset(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
SqlCommand command1 = new SqlCommand();
SQLHelper.PrepareCommand(command1, connection, null, commandType, commandText, commandParameters);
SqlDataAdapter adapter1 = new SqlDataAdapter(command1);
DataSet set1 = new DataSet();
adapter1.Fill(set1);
command1.Parameters.Clear();
return set1;
}
public static DataSet ExecuteDataset(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
SqlCommand command1 = new SqlCommand();
SQLHelper.PrepareCommand(command1, transaction.Connection, transaction, commandType, commandText, commandParameters);
SqlDataAdapter adapter1 = new SqlDataAdapter(command1);
DataSet set1 = new DataSet();
adapter1.Fill(set1);
command1.Parameters.Clear();
return set1;
}
public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
DataSet set1;
using (SqlConnection connection1 = new SqlConnection(connectionString))
{
connection1.Open();
set1 = SQLHelper.ExecuteDataset(connection1, commandType, commandText, commandParameters);
}
return set1;
}
public static DataSet ExecuteDataset(string connString, CommandType commandType, string commandText, SqlParameter[] commandParameters, int startRecord, int maxRecords, string srcTable)
{
SqlConnection connection1 = new SqlConnection(connString);
SqlCommand command1 = new SqlCommand();
SQLHelper.PrepareCommand(command1, connection1, null, commandType, commandText, commandParameters);
SqlDataAdapter adapter1 = new SqlDataAdapter(command1);
DataSet set1 = new DataSet();
adapter1.Fill(set1, startRecord, maxRecords, srcTable);
//Remember These
command1.Parameters.Clear();
return set1;
}
public static DataTable ExecuteDataTable(string cmdText)
{
DataSet set1 = SQLHelper.ExecuteDataset(cmdText);
if (set1.Tables.Count > 0)
{
return set1.Tables[0];
}
return null;
}
public static int ExecuteNonQuery(string cmdText)
{
int num2;
SqlCommand command1 = new SqlCommand();
using (SqlConnection connection1 = new SqlConnection(SQLHelper.CONN_STRING))
{
SQLHelper.PrepareCommand(command1, connection1, null, CommandType.Text, cmdText, null);
int num1 = command1.ExecuteNonQuery();
command1.Parameters.Clear();
num2 = num1;
}
return num2;
}
//If SqlCommand.ExecuteNonQuery implementation of successful,It Return 1 or >1 else it Return -1
public static int ExecuteNonQuery(SqlConnection conn, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
{
SqlCommand command1 = new SqlCommand();
SQLHelper.PrepareCommand(command1, conn, null, cmdType, cmdText, cmdParms);
int num1 = command1.ExecuteNonQuery();
command1.Parameters.Clear();
return num1;
}
public static int ExecuteNonQuery(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
{
SqlCommand command1 = new SqlCommand();
SQLHelper.PrepareCommand(command1, trans.Connection, trans, cmdType, cmdText, cmdParms);
int num1 = command1.ExecuteNonQuery();
command1.Parameters.Clear();
return num1;
}
public static int ExecuteNonQuery(string connString, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
{
int num2;
SqlCommand command1 = new SqlCommand();
using (SqlConnection connection1 = new SqlConnection(connString))
{
SQLHelper.PrepareCommand(command1, connection1, null, cmdType, cmdText, cmdParms);
int num1 = command1.ExecuteNonQuery();
command1.Parameters.Clear();
num2 = num1;
}
return num2;
}
public static SqlDataReader ExecuteReader(string connString, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
{
SqlDataReader reader2;
SqlCommand command1 = new SqlCommand();
SqlConnection connection1 = new SqlConnection(connString);
try
{
SQLHelper.PrepareCommand(command1, connection1, null, cmdType, cmdText, cmdParms);
SqlDataReader reader1 = command1.ExecuteReader(CommandBehavior.CloseConnection);
command1.Parameters.Clear();
reader2 = reader1;
}
catch
{
connection1.Close();
throw;
}
return reader2;
}
public static SqlDataReader ExecuteReader(SqlConnection cn, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
{
SqlDataReader reader2;
SqlCommand command1 = new SqlCommand();
try
{
SQLHelper.PrepareCommand(command1, cn, null, cmdType, cmdText, cmdParms);
SqlDataReader reader1 = command1.ExecuteReader(CommandBehavior.CloseConnection);
command1.Parameters.Clear();
reader2 = reader1;
}
catch
{
cn.Close();
throw;
}
return reader2;
}
public static object ExecuteScalar(string cmdText)
{
object obj2;
SqlCommand command1 = new SqlCommand();
using (SqlConnection connection1 = new SqlConnection(SQLHelper.CONN_STRING))
{
SQLHelper.PrepareCommand(command1, connection1, null, CommandType.Text, cmdText, null);
object obj1 = command1.ExecuteScalar();
command1.Parameters.Clear();
obj2 = obj1;
}
return obj2;
}
public static object ExecuteScalar(SqlConnection conn, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
{
SqlCommand command1 = new SqlCommand();
SQLHelper.PrepareCommand(command1, conn, null, cmdType, cmdText, cmdParms);
object obj1 = command1.ExecuteScalar();
command1.Parameters.Clear();
return obj1;
}
public static object ExecuteScalar(string connString, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
{
object obj2;
SqlCommand command1 = new SqlCommand();
using (SqlConnection connection1 = new SqlConnection(connString))
{
SQLHelper.PrepareCommand(command1, connection1, null, cmdType, cmdText, cmdParms);
object obj1 = command1.ExecuteScalar();
command1.Parameters.Clear();
obj2 = obj1;
}
return obj2;
}
public static SqlParameter[] GetCachedParameters(string cacheKey)
{
SqlParameter[] parameterArray1 = (SqlParameter[])SQLHelper.parmCache[cacheKey];
if (parameterArray1 == null)
{
return null;
}
SqlParameter[] parameterArray2 = new SqlParameter[parameterArray1.Length];
int num1 = 0;
int num2 = parameterArray1.Length;
while (num1 < num2)
{
parameterArray2[num1] = (SqlParameter)((ICloneable)parameterArray1[num1]).Clone();
num1++;
}
return parameterArray2;
}
//perform SqlCommand
private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
cmd.Connection = conn;
cmd.CommandText = cmdText;
if (trans != null)
{
cmd.Transaction = trans;
}
cmd.CommandType = cmdType;
if (cmdParms != null)
{
SqlParameter[] parameterArray1 = cmdParms;
for (int num1 = 0; num1 < parameterArray1.Length; num1++)
{
SqlParameter parameter1 = parameterArray1[num1];
cmd.Parameters.Add(parameter1);
}
}
}
public static SqlCommand CreateCommand(SqlConnection conn, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
{
if (conn == null) throw new ArgumentNullException("conn");
if (cmdText == null || cmdText.Length == 0) throw new ArgumentNullException("cmdText");
// Create a SqlCommand
SqlCommand cmd = new SqlCommand();
cmd.CommandText = cmdText;
cmd.Connection = conn;
cmd.CommandType = cmdType;
if (cmdParms != null)
{
SqlParameter[] parameterArray1 = cmdParms;
for (int num1 = 0; num1 < parameterArray1.Length; num1++)
{
SqlParameter parameter1 = parameterArray1[num1];
cmd.Parameters.Add(parameter1);
}
}
return cmd;
}
public static SqlCommand CreateCommand(string connString, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
{
SqlCommand cmd = new SqlCommand();
using (SqlConnection cn = new SqlConnection(connString))
{
cmd.Connection = cn;
cmd.CommandText = cmdText;
cmd.CommandType = cmdType;
if (cmdParms != null)
{
SqlParameter[] parameterArray1 = cmdParms;
for (int num1 = 0; num1 < parameterArray1.Length; num1++)
{
SqlParameter parameter1 = parameterArray1[num1];
cmd.Parameters.Add(parameter1);
}
}
}
return cmd;
}
}
0 评论:
发表评论