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
}
}
