/// <summary>
/// SqlServer数据访问帮助类
/// </summary>
public class SqlHelper
{
private SqlHelper() { }
public static string conn = ConfigurationManager.ConnectionStrings["conn"].ToString();
public static string connJob = ConfigurationManager.ConnectionStrings["connJob"].ToString();
public static int ExecSql(string sql)
{
using (SqlConnection connection = new SqlConnection(conn))
{
using (SqlCommand cmd = new SqlCommand(sql, connection))
{
try
{
connection.Open();
int rows = cmd.ExecuteNonQuery();
connection.Close();
return rows;
}
catch (System.Data.SqlClient.SqlException E)
{
connection.Close();
throw new Exception(E.Message);
}
}
}
}
public static DataSet ExecSqlDs(string sql)
{
using (SqlConnection connection = new SqlConnection(conn))
{
DataSet ds = new DataSet();
try
{
connection.Open();
SqlDataAdapter command = new SqlDataAdapter(sql, connection);
command.Fill(ds, "ds");
connection.Close();
}
catch (System.Data.SqlClient.SqlException E)
{
throw new Exception(E.Message);
}
return ds;
}
}
private static SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters, CommandType type)
{
SqlCommand command = new SqlCommand(storedProcName, connection);
command.CommandType = type;
if (parameters != null)
{
foreach (SqlParameter parameter in parameters)
{
command.Parameters.Add(parameter);
}
}
return command;
}
public static DataSet ExecSqlDs(string sql, IDataParameter[] parameters)
{
using (SqlConnection connection = new SqlConnection(conn))
{
DataSet ds = new DataSet();
try
{
connection.Open();
SqlDataAdapter command = new SqlDataAdapter(sql, connection);
if (parameters != null)
{
foreach (SqlParameter parameter in parameters)
{
command.SelectCommand.Parameters.Add(parameter);
}
}
command.Fill(ds, "ds");
connection.Close();
}
catch (System.Data.SqlClient.SqlException E)
{
throw new Exception(E.Message);
}
return ds;
}
}
public static DataSet ExecSqlDs(CommandType cmdType, string sql, IDataParameter[] parameters)
{
using (SqlConnection connection = new SqlConnection(conn))
{
DataSet ds = new DataSet();
try
{
connection.Open();
SqlDataAdapter command = new SqlDataAdapter(sql, connection);
command.SelectCommand.CommandType = cmdType;
if (parameters != null)
{
foreach (SqlParameter parameter in parameters)
{
command.SelectCommand.Parameters.Add(parameter);
}
}
command.Fill(ds, "ds");
connection.Close();
}
catch (System.Data.SqlClient.SqlException E)
{
throw new Exception(E.Message);
}
return ds;
}
}
public static int ExecSql(string sql, IDataParameter[] parameters)
{
using (SqlConnection connection = new SqlConnection(conn))
{
using (SqlCommand sqlcommand = BuildQueryCommand(connection, sql, parameters, CommandType.Text))
{
try
{
connection.Open();
int rows = sqlcommand.ExecuteNonQuery();
connection.Close();
return rows;
}
catch (System.Data.SqlClient.SqlException E)
{
connection.Close();
throw new Exception(E.Message);
}
}
}
}
public static object ExecuteScalar(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
using (SqlConnection connection = new SqlConnection(conn))
{
PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
object val = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return val;
}
}
public static object ExecuteSql(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
using (SqlConnection connection = new SqlConnection(conn))
{
PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
object val = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return val;
}
}
/// <summary>
/// Prepare a command for execution
/// </summary>p[;[
/// <param name="cmd">SqlCommand object</param>
/// <param name="conn">SqlConnection object</param>
/// <param name="trans">SqlTransaction object</param>
/// <param name="cmdType">Cmd type e.g. stored procedure or text</param>
/// <param name="cmdText">Command text, e.g. Select * from Products</param>
/// <param name="cmdParms">SqlParameters to use in the command</param>
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)
{
foreach (SqlParameter parm in cmdParms)
cmd.Parameters.Add(parm);
}
}
public static DataSet ExecProc(string storedProcName, IDataParameter[] parameters)
{
SqlConnection connection = new SqlConnection(conn);
try
{
SqlCommand sqlcommand = BuildQueryCommand(connection, storedProcName, parameters, CommandType.StoredProcedure);
SqlDataAdapter SqlDA = new SqlDataAdapter(sqlcommand);
DataSet DS = new DataSet();
SqlDA.Fill(DS);
connection.Close();
return DS;
}
catch (System.Data.SqlClient.SqlException E)
{
connection.Close();
throw new Exception(E.Message);
}
}
public static int ExecProcMaxCode(string storedProcName, string type)
{
SqlConnection connection = new SqlConnection(conn);
try
{
connection.Open();
System.Data.SqlClient.SqlParameter p1 = new System.Data.SqlClient.SqlParameter("@type", SqlDbType.VarChar, 10);
p1.Value = type;
System.Data.SqlClient.SqlParameter p2 = new System.Data.SqlClient.SqlParameter("@r_v", SqlDbType.Int);
p2.Direction = ParameterDirection.Output;
IDataParameter[] ps = new IDataParameter[] { p1, p2 };
SqlCommand sqlcommand = BuildQueryCommand(connection, storedProcName, ps, CommandType.StoredProcedure);
sqlcommand.ExecuteNonQuery();
int maxcode = (int)sqlcommand.Parameters["@r_v"].Value;
connection.Close();
return maxcode;
}
catch (System.Data.SqlClient.SqlException E)
{
connection.Close();
throw new Exception(E.Message);
}
}
}
声明:此文系舞林cuzn(www.wulinlw.org)原创稿件,转载请保留版权
生病了,都没人关心,呜呜呜···