using ?System;
using ?System.Data;
using ?System.Data.SqlClient;
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;

/**/ /// ? <summary>
/// ?SqlHelper?的摘要說明
/// ? </summary>
public ? class ?SqlHelper
... {
???? public ?SqlHelper()
???? ... {
???????? //
???????? // ?TODO:?在此處添加構造函數邏輯
???????? //
????}
???? /**/ /// ? <summary>
???? /// ?獲取數據庫連接字符串
???? /// ? </summary>
???? /// ? <returns></returns>
???? private ? static ? string ?sqlConnectionString
???? ... {
???????? get
???????? ... {
???????????? // 直接在這里獲取數據庫聯接
???????????? // return??"Data?Source?=?(local);?Initial?Catalog=window;Integrated?Security=SSPI;";
???????????? // VS.NET2003下的使用方式
???????????? // return?System.Configuration.ConfigurationSettings.AppSettings["ConnectDataBase"];
???????????? // VS.NET2005下的使用方式
???????????? return ?System.Configuration.ConfigurationManager.AppSettings[ " ConnectDataBase " ];
????????}
????}
???? /**/ /// ? <summary>
???? /// ?獲取數據庫連接
???? /// ? </summary>
???? /// ? <returns> Conn </returns>
???? private ? static ?SqlConnection?sqlConn
???? ... {
???????? get
???????? ... {
???????????? // VS.NET2003下的使用方式
???????????? // return?System.Configuration.ConfigurationSettings.AppSettings["ConnectDataBase"];
???????????? // VS.NET2005下的使用方式
???????????? // ConnStrings?=?System.Configuration.ConfigurationManager.AppSettings["ConnectDataBase"];
???????????? string ?ConnStrings? = ? " Data?Source?=?(local);?Initial?Catalog=window;Integrated?Security=SSPI; " ;
????????????SqlConnection?Conn? = ? new ?SqlConnection();
????????????Conn.ConnectionString? = ?ConnStrings;
???????????? return ?Conn;
????????}
????}
???? /**/ /// ? <summary>
???? /// ?執行sql語句，返回DataSet
???? /// ? </summary>
???? /// ? <param?name="sqlString"> sql語句參數 </param>
???? /// ? <returns> DataSet </returns>
???? public ? static ?DataSet?ExecuteDataSet( string ?sqlString)
???? ... {
????????DataSet?dsSet? = ? new ?DataSet();
????????SqlDataAdapter?adp? = ? new ?SqlDataAdapter(sqlString,?sqlConnectionString);
???????? try
???????? ... {
????????????adp.Fill(dsSet);
????????}
???????? catch ?(Exception?e)
???????? ... {
???????????? throw ?(e);
????????}
???????? finally
???????? ... {
????????????adp.Dispose();
????????}
???????? return ?dsSet;
????}
???? /**/ /// ? <summary>
???? /// ?執行sql語句，返回DataTable
???? /// ? </summary>
???? /// ? <param?name="sqlString"> sql語句參數 </param>
???? /// ? <returns> DataTable </returns>
???? public ? static ?DataTable?ExecuteDataTable( string ?sqlString)
???? ... {
????????DataTable?dt? = ? new ?DataTable();
????????SqlDataAdapter?adp? = ? new ?SqlDataAdapter(sqlString,?sqlConnectionString);
???????? try
???????? ... {
????????????adp.Fill(dt);
????????}
???????? catch ?(Exception?e)
???????? ... {
???????????? throw ?(e);
????????}
???????? finally
???????? ... {
????????????adp.Dispose();
????????}
???????? return ?dt;
????}
???? /**/ /// ? <summary>
???? /// ?執行存儲過程返回DataSet
???? /// ? </summary>
???? /// ? <param?name="spName"> 存儲過程名稱 </param>
???? /// ? <param?name="commandParameters"> 存儲過程參數 </param>
???? /// ? <returns> DataSet </returns>
???? public ? static ?DataSet?ExecuteDataSet( string ?spName,?SqlParameter[]?commandParameters)
???? ... {
????????SqlConnection?conn? = ?sqlConn;
????????conn.Open();
????????SqlCommand?sqlcommand? = ? new ?SqlCommand();
????????SqlDataAdapter?adapter? = ? new ?SqlDataAdapter();
????????DataSet?dataSet? = ? new ?DataSet();
????????sqlcommand.Connection? = ?conn;
????????sqlcommand.CommandText? = ?spName;
????????sqlcommand.CommandType? = ?CommandType.StoredProcedure;
???????? if ?(commandParameters? != ? null )
???????? ... {
???????????? foreach ?(SqlParameter?p? in ?commandParameters)
???????????? ... {
???????????????? if ?((p.Direction? == ?ParameterDirection.InputOutput)? && ?(p.Value? == ? null ))
???????????????? ... {
????????????????????p.Value? = ?DBNull.Value;
????????????????}
????????????????sqlcommand.Parameters.Add(p);
????????????}
????????}
????????adapter.SelectCommand? = ?sqlcommand;
???????? try
???????? ... {
????????????adapter.Fill(dataSet);
????????}
???????? catch ?(Exception?e)
???????? ... {
???????????? throw ?(e);
????????}
???????? finally
???????? ... {
????????????sqlcommand.Parameters.Clear();
????????????sqlcommand.Dispose();
????????????adapter.Dispose();
????????????conn.Close();
????????}
???????? return ?dataSet;
????}
???? /**/ /// ? <summary>
???? /// ?執行存儲過程返回DataTable
???? /// ? </summary>
???? /// ? <param?name="spName"> 存儲過程名稱 </param>
???? /// ? <param?name="commandParameters"> 存儲過程參數 </param>
???? /// ? <returns> DataTable </returns>
???? public ? static ?DataTable?ExecuteDataTable( string ?spName,?SqlParameter[]?commandParameters)
???? ... {
????????SqlConnection?conn? = ?sqlConn;
????????conn.Open();
????????SqlCommand?sqlcommand? = ? new ?SqlCommand();
????????SqlDataAdapter?adapter? = ? new ?SqlDataAdapter();
????????DataTable?dataTable? = ? new ?DataTable();
????????sqlcommand.Connection? = ?conn;
????????sqlcommand.CommandText? = ?spName;
????????sqlcommand.CommandType? = ?CommandType.StoredProcedure;
???????? if ?(commandParameters? != ? null )
???????? ... {
???????????? foreach ?(SqlParameter?p? in ?commandParameters)
???????????? ... {
???????????????? if ?((p.Direction? == ?ParameterDirection.InputOutput)? && ?(p.Value? == ? null ))
???????????????? ... {
????????????????????p.Value? = ?DBNull.Value;
????????????????}
????????????????sqlcommand.Parameters.Add(p);
????????????}
????????}
????????adapter.SelectCommand? = ?sqlcommand;
???????? try
???????? ... {
????????????adapter.Fill(dataTable);
????????}
???????? catch ?(Exception?e)
???????? ... {
???????????? throw ?(e);
????????}
???????? finally
???????? ... {
????????????sqlcommand.Parameters.Clear();
????????????sqlcommand.Dispose();
????????????adapter.Dispose();
????????????conn.Close();
????????}
???????? return ?dataTable;
????}
???? /**/ /// ? <summary>
???? /// ?執行存儲過程
???? /// ? </summary>
???? /// ? <param?name="spName"> 存儲過程名稱 </param>
???? /// ? <param?name="commandParameters"> 存儲過程參數 </param>
???? /// ? <returns> true?or?false </returns>
???? public ? static ? bool ?ExecuteProcedure( string ?spName,?SqlParameter[]?commandParameters)
???? ... {
???????? bool ?result? = ? false ;
????????SqlConnection?conn? = ?sqlConn;
????????conn.Open();
????????SqlCommand?sqlcommand? = ? new ?SqlCommand();
????????sqlcommand.Connection? = ?conn;
????????sqlcommand.CommandText? = ?spName;
????????sqlcommand.CommandType? = ?CommandType.StoredProcedure;
???????? if ?(commandParameters? != ? null )
???????? ... {
???????????? foreach ?(SqlParameter?p? in ?commandParameters)
???????????? ... {
???????????????? if ?((p.Direction? == ?ParameterDirection.InputOutput)? && ?(p.Value? == ? null ))
???????????????? ... {
????????????????????p.Value? = ?DBNull.Value;
????????????????}
????????????????sqlcommand.Parameters.Add(p);
????????????}
????????}
???????? try
???????? ... {
????????????sqlcommand.ExecuteNonQuery();
????????????result? = ? true ;
????????}
???????? catch ?(Exception?e)
???????? ... {
???????????? throw ?(e);
????????}
???????? finally
???????? ... {
????????????sqlcommand.Parameters.Clear();
????????????sqlcommand.Dispose();
????????????conn.Close();
????????}
???????? return ?result;
????}
???? /**/ /// ? <summary>
???? /// ?執行存儲過程返回一個object對象
???? /// ? </summary>
???? /// ? <param?name="spName"> 存儲過程名稱 </param>
???? /// ? <param?name="commandParameters"> 存儲過程參數 </param>
???? /// ? <returns> object </returns>
???? public ? static ? object ?ExecuteProcedures( string ?spName,?SqlParameter[]?commandParameters)
???? ... {
???????? object ?ret? = ? new ? object ();
????????ret? = ?DBNull.Value;
????????SqlConnection?conn? = ?sqlConn;
????????conn.Open();
????????SqlCommand?sqlcommand? = ? new ?SqlCommand();

????????? sqlcommand.Connection = conn;//2009/2/27丟掉的Connection連接
????????sqlcommand.CommandText? = ?spName;
????????sqlcommand.CommandType? = ?CommandType.StoredProcedure;
???????? if ?(commandParameters? != ? null )
???????? ... {
???????????? foreach ?(SqlParameter?p? in ?commandParameters)
???????????? ... {
???????????????? if ?((p.Direction? == ?ParameterDirection.InputOutput)? && ?(p.Value? == ? null ))
???????????????? ... {
????????????????????p.Value? = ?DBNull.Value;
????????????????}
????????????????sqlcommand.Parameters.Add(p);
????????????}
????????}
???????? try
???????? ... {
????????????ret? = ?sqlcommand.ExecuteScalar();
????????}
???????? catch ?(Exception?e)
???????? ... {
???????????? throw ?(e);
????????}
???????? finally
???????? ... {
????????????sqlcommand.Parameters.Clear();
????????????sqlcommand.Dispose();
????????????conn.Close();
????????}
???????? return ?ret;
????}
???? /**/ /// ? <summary>
???? /// ?執行sql語句，返回一個object對象
???? /// ? </summary>
???? /// ? <param?name="sqlString"> 自定義sql語句 </param>
???? /// ? <returns> object </returns>
???? public ? static ? object ?ExecuteScalar( string ?sqlString)
???? ... {
???????? object ?ret? = ? new ? object ();
????????ret? = ?DBNull.Value;
????????SqlConnection?conn? = ?sqlConn;
????????SqlCommand?sqlcommand? = ? new ?SqlCommand(sqlString,?conn);
???????? try
???????? ... {
????????????ret? = ?sqlcommand.ExecuteScalar();
????????}
???????? catch ?(Exception?e)
???????? ... {
???????????? throw ?(e);
????????}
???????? finally
???????? ... {
????????????sqlcommand.Dispose();
????????????conn.Close();
????????}
???????? return ?ret;
????}
???? /**/ /// ? <summary>
???? /// ?執行自定義sql語句
???? /// ? </summary>
???? /// ? <param?name="sqlString"> 自定sql語句 </param>
???? /// ? <returns> true?or?false </returns>
???? public ? static ? bool ?ExecuteNoQueryString( string ?sqlString)
???? ... {
???????? bool ?result? = ? false ;
????????SqlCommand?sqlcommand? = ? new ?SqlCommand();
????????SqlConnection?conn? = ? new ?SqlConnection();
????????conn.Open();
????????sqlcommand.Connection? = ?conn;
???????? try
???????? ... {
????????????sqlcommand.ExecuteScalar();
????????????result? = ? true ;
????????}
???????? catch
???????? ... {
????????????result? = ? false ;
????????}
???????? finally
???????? ... {
????????????sqlcommand.Dispose();
????????????conn.Close();
????????}
???????? return ?result;
????}
}

?

這個文件在網上應該是很流行的。不做 解釋，記錄在案以后使用。

下邊是調用 存儲過程 的方法 帶參數的：

??????? public bool InsertUsers(Users user)
??????? {
??????????? SqlParameter[] parms = {
??????????????? new SqlParameter("@UserName",user.userName),
??????????????? new SqlParameter("@UserPass",user.userPass),
??????????????? new SqlParameter("@UserRole",user.userRole),
??????????????? new SqlParameter("@UserEmail",user.userEmail),
??????????????? new SqlParameter("@Remark",user.remark)
??????????? };
??????????? return SQLHelper.ExecuteProcedure("tfwk_InsertUser", parms);
??????? }

簡單C#數據庫操作類