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:?在此處添加構(gòu)造函數(shù)邏輯
????????
//
????}
????
///
?
<summary>
????
///
?獲取數(shù)據(jù)庫連接字符串
????
///
?
</summary>
????
///
?
<returns></returns>
????
private
?
static
?
string
?sqlConnectionString
????
{
????????
get
????????
{
????????????
//
直接在這里獲取數(shù)據(jù)庫聯(lián)接
????????????
//
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>
????
///
?獲取數(shù)據(jù)庫連接
????
///
?
</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>
????
///
?執(zhí)行sql語句,返回DataSet
????
///
?
</summary>
????
///
?
<param?name="sqlString">
sql語句參數(shù)
</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>
????
///
?執(zhí)行sql語句,返回DataTable
????
///
?
</summary>
????
///
?
<param?name="sqlString">
sql語句參數(shù)
</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>
????
///
?執(zhí)行存儲過程返回DataSet
????
///
?
</summary>
????
///
?
<param?name="spName">
存儲過程名稱
</param>
????
///
?
<param?name="commandParameters">
存儲過程參數(shù)
</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>
????
///
?執(zhí)行存儲過程返回DataTable
????
///
?
</summary>
????
///
?
<param?name="spName">
存儲過程名稱
</param>
????
///
?
<param?name="commandParameters">
存儲過程參數(shù)
</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>
????
///
?執(zhí)行存儲過程
????
///
?
</summary>
????
///
?
<param?name="spName">
存儲過程名稱
</param>
????
///
?
<param?name="commandParameters">
存儲過程參數(shù)
</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>
????
///
?執(zhí)行存儲過程返回一個object對象
????
///
?
</summary>
????
///
?
<param?name="spName">
存儲過程名稱
</param>
????
///
?
<param?name="commandParameters">
存儲過程參數(shù)
</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>
????
///
?執(zhí)行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>
????
///
?執(zhí)行自定義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;
????}
}
?
這個文件在網(wǎng)上應(yīng)該是很流行的。不做 解釋,記錄在案以后使用。
下邊是調(diào)用 存儲過程 的方法 帶參數(shù)的:
??????? 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);
??????? }
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索:z360901061

微信掃一掃加我為好友
QQ號聯(lián)系: 360901061
您的支持是博主寫作最大的動力,如果您喜歡我的文章,感覺我的文章對您有幫助,請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧,狠狠點擊下面給點支持吧,站長非常感激您!手機微信長按不能支付解決辦法:請將微信支付二維碼保存到相冊,切換到微信,然后點擊微信右上角掃一掃功能,選擇支付二維碼完成支付。
【本文對您有幫助就好】元
