SQL 工具类
![](https://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif)
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Data.SqlClient;
/// <summary>
///sql Server 数据库操作
/// </summary>
public class CMS_SqlHelp
{
private static string sqlconstr = Convert.ToString(ConfigurationManager.ConnectionStrings["sqlconstr"]);
public CMS_SqlHelp()
{
//
//TODO: 在此处添加构造函数逻辑
//
}
/// <summary>
/// 自定义分页
/// </summary>
/// <param name="tblName">表名</param>
/// <param name="strGetFields">需要返回的列</param>
/// <param name="fldName">排序字段名</param>
/// <param name="PageSize">每页显示的条数</param>
/// <param name="PageIndex">页码</param>
/// <param name="doCount">返回记录总数,非0值则返回</param>
/// <param name="OrderType">设置排序类型,非0值则降序</param>
/// <param name="strWhere">查询条件,不加where</param>
/// <returns>datatable</returns>
public static DataTable GetData(string tblName, string strGetFields, string fldName, int PageSize, int PageIndex, int doCount, int OrderType, string strWhere)
{
string strSQL = "", strTmp = "", strOrder = "";
if (doCount != 0)
{
if (strWhere != "")
{
strSQL = "select count(*) as Total from " + tblName + " where " + strWhere;
}
else
{
strSQL = "select count(*) as Total from " + tblName;
}
}
else
{
if (OrderType != 0)
{
strTmp = "<(select min";
strOrder = " order by " + fldName + " desc";
}
else
{
strTmp = ">(select max";
strOrder = " order by " + fldName + " asc";
}
if (PageIndex == 1)
{
if (strWhere != "")
{
strSQL = "select top " + PageSize + " " + strGetFields + " from " + tblName + " where " + strWhere + " " + strOrder;
}
else
{
strSQL = "select top " + PageSize + " " + strGetFields + " from " + tblName + " " + strOrder;
}
}
else
{
if (strWhere != "")
{
strSQL = "select top " + PageSize + " " + strGetFields + " from " + tblName + " where " + fldName + " " + strTmp + "(" + fldName + ") from (select top " + (PageIndex - 1) * PageSize + " " + fldName + " from " + tblName + " where " + strWhere + " " + strOrder + ") as tblTmp) and " + strWhere + " " + strOrder;
}
else
{
strSQL = "select top " + PageSize + " " + strGetFields + " from " + tblName + " where " + fldName + " " + strTmp + "(" + fldName + ") from (select top " + (PageIndex - 1) * PageSize + " " + fldName + " from " + tblName + "" + strOrder + ") as tblTmp)" + strOrder;
}
}
}
DataTable dt = CMS_SqlHelp.getDataTable(strSQL);
return dt;
}
/// <summary>
/// 执行无返回的SQL语句
/// </summary>
/// <param name="sqlStr">SQL语句</param>
/// <returns></returns>
public static bool ExcuteSqlServer(string sqlStr)
{
SqlConnection con = new SqlConnection(sqlconstr);
SqlCommand sqlcom = new SqlCommand();
sqlcom.Connection = con;
sqlcom.CommandText = sqlStr;
con.Open();
try
{
sqlcom.ExecuteNonQuery();
return true;
}
catch (Exception ex)
{
errorCollecting.getError(ex);
return false;
}
finally {
con.Close();
}
}
#region ExecuteScalar
/// <summary>
/// 返回所查结果第一列第一行
/// </summary>
/// <param name="sqlStr"></param>
/// <returns></returns>
public static object ExecuteScalar(string sqlStr)
{
SqlConnection con = new SqlConnection(sqlconstr);
SqlCommand sqlcom = new SqlCommand();
sqlcom.Connection = con;
sqlcom.CommandText = sqlStr;
object obj = null;
con.Open();
try
{
obj= sqlcom.ExecuteScalar();
return obj;
}
catch (Exception ex)
{
errorCollecting.getError(ex);
return false;
}
finally
{
con.Close();
}
}
#endregion
public static SqlDataReader ExcuteSqlDataReader(string sqlStr)
{
SqlConnection con = new SqlConnection(sqlconstr);
SqlCommand sqlcom = new SqlCommand();
sqlcom.Connection = con;
sqlcom.CommandText = sqlStr;
SqlDataReader sdr = null;
con.Open();
sdr = sqlcom.ExecuteReader(CommandBehavior.CloseConnection);
return sdr;
}
/// <summary>
/// 返回DaTable
/// </summary>
/// <param name="sqlStr"></param>
/// <returns></returns>
public static DataTable getDataTable(string sqlStr)
{
SqlConnection con = new SqlConnection(sqlconstr);
DataTable dt = new DataTable();
con.Open();
try
{
SqlDataAdapter da = new SqlDataAdapter(sqlStr, con);
da.Fill(dt);
}
catch (Exception e)
{
errorCollecting.getError(e);
}
finally
{
con.Close();
}
return dt;
}
#region ExcuteProc
/// <summary>
/// 执行无返回值Proc
/// </summary>
/// <param name="sqlProc"></param>
public static void ExecuteProcedureNonQurey(string sqlProc)
{
SqlConnection con = new SqlConnection(sqlconstr);
SqlCommand com = new SqlCommand();
com.Connection = con;
com.CommandText = sqlProc;
com.CommandType = CommandType.StoredProcedure;
con.Open();
try
{
com.ExecuteNonQuery();
com.Dispose();
}
catch (Exception ex)
{
errorCollecting.getError(ex);
}
finally
{
con.Close();
}
}
#endregion
/// <summary>
/// 执行存储过程,不返回任何值
/// </summary>
/// <param name="storedProcedureName">存储过程名</param>
/// <param name="parameters">参数</param>
/*
SqlParameter sp = new SqlParameter("@job_desc", SqlDbType.VarChar, 50);
SqlParameter sp2 = new SqlParameter("@job_id",SqlDbType.SmallInt);
IDataParameter[] Idp = new IDataParameter[]{sp,sp2 };
Idp[0].Value="adff";
Idp[1].Value=6;
CMS_sqlProc.ExecuteProcedureNonQurey("updateMy", Idp);
*/
public static void ExecuteProcedureNonQurey(string storedProcedureName, IDataParameter[] parameters)
{
SqlConnection connection = new SqlConnection(sqlconstr);
SqlCommand command = new SqlCommand(storedProcedureName, connection);
command.CommandType = CommandType.StoredProcedure;
if (parameters != null)
{
foreach (SqlParameter parameter in parameters)
{
command.Parameters.Add(parameter);
}
}
connection.Open();
try
{
command.ExecuteNonQuery();
connection.Close();
}
catch (Exception ex)
{
errorCollecting.getError(ex);
}
}
/// <summary>
/// 执行存储,并返回SqlDataReader
/// </summary>
/// <param name="storedProcedureName">存储过程名</param>
/// <param name="parameters">参数</param>
/// <returns>包含查询结果的SqlDataReader</returns>
public static SqlDataReader ExecuteProcedureReader(string storedProcedureName, IDataParameter[] parameters)
{
SqlConnection connection = new SqlConnection(sqlconstr);
SqlCommand command = new SqlCommand(storedProcedureName, connection);
command.CommandType = CommandType.StoredProcedure;
if (parameters != null)
{
foreach (SqlParameter parameter in parameters)
{
command.Parameters.Add(parameter);
}
}
connection.Open();
SqlDataReader sqlReader = command.ExecuteReader(CommandBehavior.CloseConnection);
return sqlReader;
}
/// <summary>
/// 执行存储,并返回DataTable
/// </summary>
/// <param name="storedProcedureName">存储过程名</param>
/// <param name="parameters">参数</param>
/// <returns>包含查询结果的SqlDataReader</returns>
public static DataTable ExecuteProcedureDataTable(string storedProcedureName, IDataParameter[] parameters)
{
SqlConnection connection = new SqlConnection(sqlconstr);
SqlCommand command = new SqlCommand(storedProcedureName, connection);
command.CommandType = CommandType.StoredProcedure;
if (parameters != null)
{
foreach (SqlParameter parameter in parameters)
{
command.Parameters.Add(parameter);
}
}
connection.Open();
DataTable dt = new DataTable();
try
{
SqlDataAdapter da = new SqlDataAdapter(command);
da.Fill(dt);
}
catch (Exception e)
{
throw e;
}
finally
{
connection.Close();
}
return dt;
}
//可以尽量避免sqlconnection.open()操作
public static DataSet ExecuteProcedureDataset(string storedProcedureName, IDataParameter[] parameters)
{
SqlConnection connection = new SqlConnection(sqlconstr);
SqlCommand command = new SqlCommand(storedProcedureName, connection);
command.CommandType = CommandType.StoredProcedure;
if (parameters != null)
{
foreach (SqlParameter parameter in parameters)
{
command.Parameters.Add(parameter);
}
}
connection.Open();
DataSet ds = new DataSet();
DataTable dt = new DataTable();
try
{
SqlDataAdapter da = new SqlDataAdapter(command);
da.Fill(ds);
}
catch (Exception e)
{
throw e;
}
finally
{
connection.Close();
}
return ds;
}
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Data.SqlClient;
/// <summary>
///sql Server 数据库操作
/// </summary>
public class CMS_SqlHelp
{
private static string sqlconstr = Convert.ToString(ConfigurationManager.ConnectionStrings["sqlconstr"]);
public CMS_SqlHelp()
{
//
//TODO: 在此处添加构造函数逻辑
//
}
/// <summary>
/// 自定义分页
/// </summary>
/// <param name="tblName">表名</param>
/// <param name="strGetFields">需要返回的列</param>
/// <param name="fldName">排序字段名</param>
/// <param name="PageSize">每页显示的条数</param>
/// <param name="PageIndex">页码</param>
/// <param name="doCount">返回记录总数,非0值则返回</param>
/// <param name="OrderType">设置排序类型,非0值则降序</param>
/// <param name="strWhere">查询条件,不加where</param>
/// <returns>datatable</returns>
public static DataTable GetData(string tblName, string strGetFields, string fldName, int PageSize, int PageIndex, int doCount, int OrderType, string strWhere)
{
string strSQL = "", strTmp = "", strOrder = "";
if (doCount != 0)
{
if (strWhere != "")
{
strSQL = "select count(*) as Total from " + tblName + " where " + strWhere;
}
else
{
strSQL = "select count(*) as Total from " + tblName;
}
}
else
{
if (OrderType != 0)
{
strTmp = "<(select min";
strOrder = " order by " + fldName + " desc";
}
else
{
strTmp = ">(select max";
strOrder = " order by " + fldName + " asc";
}
if (PageIndex == 1)
{
if (strWhere != "")
{
strSQL = "select top " + PageSize + " " + strGetFields + " from " + tblName + " where " + strWhere + " " + strOrder;
}
else
{
strSQL = "select top " + PageSize + " " + strGetFields + " from " + tblName + " " + strOrder;
}
}
else
{
if (strWhere != "")
{
strSQL = "select top " + PageSize + " " + strGetFields + " from " + tblName + " where " + fldName + " " + strTmp + "(" + fldName + ") from (select top " + (PageIndex - 1) * PageSize + " " + fldName + " from " + tblName + " where " + strWhere + " " + strOrder + ") as tblTmp) and " + strWhere + " " + strOrder;
}
else
{
strSQL = "select top " + PageSize + " " + strGetFields + " from " + tblName + " where " + fldName + " " + strTmp + "(" + fldName + ") from (select top " + (PageIndex - 1) * PageSize + " " + fldName + " from " + tblName + "" + strOrder + ") as tblTmp)" + strOrder;
}
}
}
DataTable dt = CMS_SqlHelp.getDataTable(strSQL);
return dt;
}
/// <summary>
/// 执行无返回的SQL语句
/// </summary>
/// <param name="sqlStr">SQL语句</param>
/// <returns></returns>
public static bool ExcuteSqlServer(string sqlStr)
{
SqlConnection con = new SqlConnection(sqlconstr);
SqlCommand sqlcom = new SqlCommand();
sqlcom.Connection = con;
sqlcom.CommandText = sqlStr;
con.Open();
try
{
sqlcom.ExecuteNonQuery();
return true;
}
catch (Exception ex)
{
errorCollecting.getError(ex);
return false;
}
finally {
con.Close();
}
}
#region ExecuteScalar
/// <summary>
/// 返回所查结果第一列第一行
/// </summary>
/// <param name="sqlStr"></param>
/// <returns></returns>
public static object ExecuteScalar(string sqlStr)
{
SqlConnection con = new SqlConnection(sqlconstr);
SqlCommand sqlcom = new SqlCommand();
sqlcom.Connection = con;
sqlcom.CommandText = sqlStr;
object obj = null;
con.Open();
try
{
obj= sqlcom.ExecuteScalar();
return obj;
}
catch (Exception ex)
{
errorCollecting.getError(ex);
return false;
}
finally
{
con.Close();
}
}
#endregion
public static SqlDataReader ExcuteSqlDataReader(string sqlStr)
{
SqlConnection con = new SqlConnection(sqlconstr);
SqlCommand sqlcom = new SqlCommand();
sqlcom.Connection = con;
sqlcom.CommandText = sqlStr;
SqlDataReader sdr = null;
con.Open();
sdr = sqlcom.ExecuteReader(CommandBehavior.CloseConnection);
return sdr;
}
/// <summary>
/// 返回DaTable
/// </summary>
/// <param name="sqlStr"></param>
/// <returns></returns>
public static DataTable getDataTable(string sqlStr)
{
SqlConnection con = new SqlConnection(sqlconstr);
DataTable dt = new DataTable();
con.Open();
try
{
SqlDataAdapter da = new SqlDataAdapter(sqlStr, con);
da.Fill(dt);
}
catch (Exception e)
{
errorCollecting.getError(e);
}
finally
{
con.Close();
}
return dt;
}
#region ExcuteProc
/// <summary>
/// 执行无返回值Proc
/// </summary>
/// <param name="sqlProc"></param>
public static void ExecuteProcedureNonQurey(string sqlProc)
{
SqlConnection con = new SqlConnection(sqlconstr);
SqlCommand com = new SqlCommand();
com.Connection = con;
com.CommandText = sqlProc;
com.CommandType = CommandType.StoredProcedure;
con.Open();
try
{
com.ExecuteNonQuery();
com.Dispose();
}
catch (Exception ex)
{
errorCollecting.getError(ex);
}
finally
{
con.Close();
}
}
#endregion
/// <summary>
/// 执行存储过程,不返回任何值
/// </summary>
/// <param name="storedProcedureName">存储过程名</param>
/// <param name="parameters">参数</param>
/*
SqlParameter sp = new SqlParameter("@job_desc", SqlDbType.VarChar, 50);
SqlParameter sp2 = new SqlParameter("@job_id",SqlDbType.SmallInt);
IDataParameter[] Idp = new IDataParameter[]{sp,sp2 };
Idp[0].Value="adff";
Idp[1].Value=6;
CMS_sqlProc.ExecuteProcedureNonQurey("updateMy", Idp);
*/
public static void ExecuteProcedureNonQurey(string storedProcedureName, IDataParameter[] parameters)
{
SqlConnection connection = new SqlConnection(sqlconstr);
SqlCommand command = new SqlCommand(storedProcedureName, connection);
command.CommandType = CommandType.StoredProcedure;
if (parameters != null)
{
foreach (SqlParameter parameter in parameters)
{
command.Parameters.Add(parameter);
}
}
connection.Open();
try
{
command.ExecuteNonQuery();
connection.Close();
}
catch (Exception ex)
{
errorCollecting.getError(ex);
}
}
/// <summary>
/// 执行存储,并返回SqlDataReader
/// </summary>
/// <param name="storedProcedureName">存储过程名</param>
/// <param name="parameters">参数</param>
/// <returns>包含查询结果的SqlDataReader</returns>
public static SqlDataReader ExecuteProcedureReader(string storedProcedureName, IDataParameter[] parameters)
{
SqlConnection connection = new SqlConnection(sqlconstr);
SqlCommand command = new SqlCommand(storedProcedureName, connection);
command.CommandType = CommandType.StoredProcedure;
if (parameters != null)
{
foreach (SqlParameter parameter in parameters)
{
command.Parameters.Add(parameter);
}
}
connection.Open();
SqlDataReader sqlReader = command.ExecuteReader(CommandBehavior.CloseConnection);
return sqlReader;
}
/// <summary>
/// 执行存储,并返回DataTable
/// </summary>
/// <param name="storedProcedureName">存储过程名</param>
/// <param name="parameters">参数</param>
/// <returns>包含查询结果的SqlDataReader</returns>
public static DataTable ExecuteProcedureDataTable(string storedProcedureName, IDataParameter[] parameters)
{
SqlConnection connection = new SqlConnection(sqlconstr);
SqlCommand command = new SqlCommand(storedProcedureName, connection);
command.CommandType = CommandType.StoredProcedure;
if (parameters != null)
{
foreach (SqlParameter parameter in parameters)
{
command.Parameters.Add(parameter);
}
}
connection.Open();
DataTable dt = new DataTable();
try
{
SqlDataAdapter da = new SqlDataAdapter(command);
da.Fill(dt);
}
catch (Exception e)
{
throw e;
}
finally
{
connection.Close();
}
return dt;
}
//可以尽量避免sqlconnection.open()操作
public static DataSet ExecuteProcedureDataset(string storedProcedureName, IDataParameter[] parameters)
{
SqlConnection connection = new SqlConnection(sqlconstr);
SqlCommand command = new SqlCommand(storedProcedureName, connection);
command.CommandType = CommandType.StoredProcedure;
if (parameters != null)
{
foreach (SqlParameter parameter in parameters)
{
command.Parameters.Add(parameter);
}
}
connection.Open();
DataSet ds = new DataSet();
DataTable dt = new DataTable();
try
{
SqlDataAdapter da = new SqlDataAdapter(command);
da.Fill(ds);
}
catch (Exception e)
{
throw e;
}
finally
{
connection.Close();
}
return ds;
}
一只站在树上的鸟儿,从来不会害怕树枝会断裂,因为它相信的不是树枝,而是它自己的翅膀。与其每天担心未来,不如努力做好现在。