using
System;
using
System.Collections.Generic;
using
System.Linq;
using
System.Text;
/**/
using
System.Data.SqlClient;
using
System.Data;
using
System.Configuration;
/*
*******************************************************************************
** 創建人：
** 創始時間：2012-11-27
** 修改人：
** 修改時間：
** 描述：
** 數據庫操作基類
********************************************************************************
*/
namespace
DAL
{
public
class
SqlHelper
{
public
static
SqlConnection connection;
#region
打開數據庫
///
<summary>
///
打開數據庫
///
</summary>
public
static
SqlConnection Conn
{
get
{
string
connStr = ConfigurationManager.ConnectionStrings[
"
ConnectionString
"
].ConnectionString;
if
(connection ==
null
)
{
connection
=
new
SqlConnection(connStr);
connection.Open();
}
else
if
(connection.State==
System.Data.ConnectionState.Closed){
connection
=
new
SqlConnection(connStr);
connection.Open();
}
else
if
(connection.State==
System.Data.ConnectionState.Broken){
connection.Close();
connection.Open();
}
return
connection;
}
}
#endregion
#region
增，刪，改ExecuteNonQuery
///
<summary>
///
單個數據增，刪，改
///
</summary>
///
<param name="sql"></param>
///
<returns></returns>
public
static
int
ExecuteNonQuery(
string
sql)
{
try
{
using
(SqlCommand cmd=
new
SqlCommand(sql,Conn))
{
int
result =
cmd.ExecuteNonQuery();
return
result;
}
}
catch
(SqlException ex)
{
throw
ex;
}
}
#endregion
#region
帶參數的增，刪，改ExecuteNonQuery
///
<summary>
///
帶多個參數的增，刪，改
///
</summary>
///
<param name="sql"></param>
///
<param name="type"></param>
///
<param name="values"></param>
///
<returns></returns>
public
static
int
ExecuteNonQuery(
string
sql,CommandType type,
params
SqlParameter[] values)
{
try
{
using
(SqlCommand cmd=
new
SqlCommand(sql,Conn))
{
cmd.CommandType
=
type;
cmd.Parameters.AddRange(values);
//
int
result =
cmd.ExecuteNonQuery();
return
result;
}
}
catch
(SqlException ex)
{
throw
ex;
}
}
#endregion
#region
查詢語句ExecuteScalar
///
<summary>
///
查單個值
///
</summary>
///
<param name="sql"></param>
///
<returns></returns>
public
static
int
ExecuteScalar(
string
sql)
{
try
{
using
(SqlCommand cmd=
new
SqlCommand(sql,Conn))
{
int
result =
Convert.ToInt32(cmd.ExecuteScalar());
return
result;
}
}
catch
(SqlException ex)
{
throw
ex;
}
}
#endregion
#region
帶參數的查詢語句ExecuteScalar
///
<summary>
///
帶執行類型的ExecuteScalar
///
</summary>
///
<param name="sql"></param>
///
<param name="type"></param>
///
<param name="values"></param>
///
<returns></returns>
public
static
int
ExecuteScalar(
string
sql,CommandType type,
params
SqlParameter[] values)
{
try
{
using
(SqlCommand cmd=
new
SqlCommand(sql,Conn))
{
cmd.CommandType
=
type;
cmd.Parameters.AddRange(values);
int
result =
Convert.ToInt32(cmd.ExecuteScalar());
return
result;
}
}
catch
(SqlException ex)
{
throw
ex;
}
}
#endregion
#region
查詢,返回DataReader
///
<summary>
///
查詢表,獲取多個記錄
///
</summary>
///
<param name="sql"></param>
///
<returns></returns>
public
static
SqlDataReader ExecuteReader(
string
sql)
{
try
{
using
(SqlCommand cmd=
new
SqlCommand(sql,Conn))
{
SqlDataReader dtr
=
cmd.ExecuteReader();
return
dtr;
}
}
catch
(SqlException ex)
{
throw
ex;
}
}
#endregion
#region
帶參數的查詢,返回DataReader
///
<summary>
///
查詢表,獲取多個記錄
///
</summary>
///
<param name="sql"></param>
///
<param name="type"></param>
///
<param name="values"></param>
///
<returns></returns>
public
static
SqlDataReader ExecuteReader(
string
sql,CommandType type,
params
SqlParameter[] values)
{
try
{
using
(SqlCommand cmd=
new
SqlCommand(sql,Conn))
{
cmd.CommandType
=
type;
cmd.Parameters.AddRange(values);
SqlDataReader dtr
=
cmd.ExecuteReader();
return
dtr;
}
}
catch
(SqlException ex)
{
throw
ex;
}
}
#endregion
#region
查詢,返回datatable
///
<summary>
///
返回datatable
///
</summary>
///
<param name="sql"></param>
///
<returns></returns>
public
static
DataTable dataTable(
string
sql)
{
try
{
DataSet dst
=
new
DataSet();
SqlCommand cmd
=
new
SqlCommand(sql,Conn);
SqlDataAdapter dad
=
new
SqlDataAdapter(cmd);
dad.Fill(dst);
//
在 DataSet 中添加或刷新行
return
dst.Tables[
0
];
}
catch
(SqlException ex)
{
throw
ex;
}
}
#endregion
#region
帶參數的查詢, 返回dataTable
///
<summary>
///
返回dataTable
///
</summary>
///
<param name="sql"></param>
///
<param name="values"></param>
///
<returns></returns>
public
static
DataTable datatable(
string
sql,
params
SqlParameter[] values)
{
DataSet dst
=
new
DataSet();
SqlCommand cmd
=
new
SqlCommand(sql,Conn);
cmd.Parameters.AddRange(values);
SqlDataAdapter dad
=
new
SqlDataAdapter(cmd);
dad.Fill(dst);
//
在 DataSet 中添加或刷新行
return
dst.Tables[
0
];
}
#endregion
}
}
?
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
