using System;
using ;
using ;
using ;
using ;
using ;
using ;
namespace
{
class SQLHelper
{
#region General Method
// Data connection pool
private SqlConnection con;
/// <summary>
/// Return the database connection string
/// </summary>
/// <returns></returns>
public static String GetSqlConnection()
{
String conn = ["connectionString"].ToString();
return conn;
}
#endregion
#region Execute SQL string
/// <summary>
/// Execute SQL statements without parameters
/// </summary>
/// <param name="Sqlstr"></param>
/// <returns></returns>
public static int ExecuteSql(String Sqlstr)
{
String ConnStr = GetSqlConnection();
using (SqlConnection conn = new SqlConnection(ConnStr))
{
SqlCommand cmd = new SqlCommand();
= conn;
= Sqlstr;
();
();
();
return 1;
}
}
/// <summary>
/// Execute SQL statements with parameters
/// </summary>
/// <param name="Sqlstr">SQL statement</param>
/// <param name="param">Array of Parameter Objects</param>
/// <returns></returns>
public static int ExecuteSql(String Sqlstr, SqlParameter[] param)
{
String ConnStr = GetSqlConnection();
using (SqlConnection conn = new SqlConnection(ConnStr))
{
SqlCommand cmd = new SqlCommand();
= conn;
= Sqlstr;
(param);
();
();
();
return 1;
}
}
/// <summary>
/// Return to DataReader
/// </summary>
/// <param name="Sqlstr"></param>
/// <returns></returns>
public static SqlDataReader ExecuteReader(String Sqlstr)
{
String ConnStr = GetSqlConnection();
SqlConnection conn = new SqlConnection(ConnStr);//When returning DataReader, using() cannot be used
try
{
SqlCommand cmd = new SqlCommand();
= conn;
= Sqlstr;
();
return ();//Close the associated Connection
}
catch //(Exception ex)
{
return null;
}
}
/// <summary>
/// Execute SQL statement and return data table
/// </summary>
/// <param name="Sqlstr">SQL statement</param>
/// <returns></returns>
public static DataTable ExecuteDt(String Sqlstr)
{
String ConnStr = GetSqlConnection();
using (SqlConnection conn = new SqlConnection(ConnStr))
{
SqlDataAdapter da = new SqlDataAdapter(Sqlstr, conn);
DataTable dt = new DataTable();
();
(dt);
();
return dt;
}
}
/// <summary>
/// Execute SQL statement and return DataSet
/// </summary>
/// <param name="Sqlstr">SQL statement</param>
/// <returns></returns>
public static DataSet ExecuteDs(String Sqlstr)
{
String ConnStr = GetSqlConnection();
using (SqlConnection conn = new SqlConnection(ConnStr))
{
SqlDataAdapter da = new SqlDataAdapter(Sqlstr, conn);
DataSet ds = new DataSet();
();
(ds);
();
return ds;
}
}
#endregion
#region Operating stored procedures
/// <summary>
/// Run stored procedure (overloaded)
/// </summary>
/// <param name="procName">Stored procedure name</param>
/// <returns>Return value of stored procedure</returns>
public int RunProc(string procName)
{
SqlCommand cmd = CreateCommand(procName, null);
();
();
return (int)["ReturnValue"].Value;
}
/// <summary>
/// Run stored procedure (overloaded)
/// </summary>
/// <param name="procName">Stored procedure name</param>
/// <param name="prams">Input parameter list of stored procedures</param>
/// <returns>Return value of stored procedure</returns>
public int RunProc(string procName, SqlParameter[] prams)
{
SqlCommand cmd = CreateCommand(procName, prams);
();
();
return (int)[0].Value;
}
/// <summary>
/// Run stored procedure (overloaded)
/// </summary>
/// <param name="procName">Stored procedure name</param>
/// <param name="dataReader">Result Set</param>
public void RunProc(string procName, out SqlDataReader dataReader)
{
SqlCommand cmd = CreateCommand(procName, null);
dataReader = ();
}
/// <summary>
/// Run stored procedure (overloaded)
/// </summary>
/// <param name="procName">Stored procedure name</param>
/// <param name="prams">Input parameter list of stored procedures</param>
/// <param name="dataReader">Result Set</param>
public void RunProc(string procName, SqlParameter[] prams, out SqlDataReader dataReader)
{
SqlCommand cmd = CreateCommand(procName, prams);
dataReader = ();
}
/// <summary>
/// Create Command object for accessing stored procedures
/// </summary>
/// <param name="procName">Stored procedure name</param>
/// <param name="prams">Input parameter list of stored procedures</param>
/// <returns>Command object</returns>
private SqlCommand CreateCommand(string procName, SqlParameter[] prams)
{
// Make sure the connection is open
Open();
//command = new SqlCommand( sprocName, new SqlConnection( ) );
SqlCommand cmd = new SqlCommand(procName, con);
= ;
// Add a list of input parameters for stored procedures
if (prams != null)
{
foreach (SqlParameter parameter in prams)
(parameter);
}
// Return the Command object
return cmd;
}
/// <summary>
/// Create input parameters
/// </summary>
/// <param name="ParamName">param name</param>
/// <param name="DbType">parameter type</param>
/// <param name="Size">parameter size</param>
/// <param name="Value">parameter value</param>
/// <returns>New parameter object</returns>
public SqlParameter MakeInParam(string ParamName, SqlDbType DbType, int Size, object Value)
{
return MakeParam(ParamName, DbType, Size, , Value);
}
/// <summary>
/// Create output parameters
/// </summary>
/// <param name="ParamName">param name</param>
/// <param name="DbType">parameter type</param>
/// <param name="Size">parameter size</param>
/// <returns>New parameter object</returns>
public SqlParameter MakeOutParam(string ParamName, SqlDbType DbType, int Size)
{
return MakeParam(ParamName, DbType, Size, , null);
}
/// <summary>
/// Create stored procedure parameters
/// </summary>
/// <param name="ParamName">param name</param>
/// <param name="DbType">parameter type</param>
/// <param name="Size">parameter size</param>
/// <param name="Direction">Direction of the parameter (input/output)</param>
/// <param name="Value">parameter value</param>
/// <returns>New parameter object</returns>
public SqlParameter MakeParam(string ParamName, SqlDbType DbType, Int32 Size, ParameterDirection Direction, object Value)
{
SqlParameter param;
if (Size > 0)
{
param = new SqlParameter(ParamName, DbType, Size);
}
else
{
param = new SqlParameter(ParamName, DbType);
}
= Direction;
if (!(Direction == && Value == null))
{
= Value;
}
return param;
}
#endregion
#region Database connection and closing
/// <summary>
/// Open the connection pool
/// </summary>
private void Open()
{
// Open the connection pool
if (con == null)
{
//Not only needs to use; it also needs to be added in the reference directory
con = new SqlConnection(GetSqlConnection());
();
}
}
/// <summary>
/// Close the connection pool
/// </summary>
public void Close()
{
if (con != null)
();
}
/// <summary>
/// Release the connection pool
/// </summary>
public void Dispose()
{
// Make sure the connection is closed
if (con != null)
{
();
con = null;
}
}
#endregion
}
}