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;


????}