SqlServerDBHelper类
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace DAL
{
/// <summary>
/// 连接数据库类
/
// </summary>
public class SqlServerDBHelper
{
/// <summary>
/// 连接数据库
/// </summary>
/// <returns></returns>
public static SqlConnection GetConnection()
{
string conString = System.Configuration.ConfigurationManager.ConnectionStrings["connstring"].ToString();//定义连接数据库字符集            return new SqlConnection(conString);//返回连接数据库对象
}
/// <summary>
/// ⽤数据填充的⽅法查询
/// </summary>
/// <param name="type"></param>
/// <param name="sql"></param>
/// <param name="pares"></param>
/// <returns></returns>
public DataSet GetDataSet(CommandType type, string sql, params SqlParameter[] pares)
{
SqlConnection con = GetConnection();
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(sql, con);
da.SelectCommand.CommandType = type;
if (pares != null)
{
foreach (SqlParameter pare in pares)
{
da.SelectCommand.Parameters.Add(pare);
}
}
da.Fill(ds);
return ds;
}
public DataSet GetDataSet(string sql, params SqlParameter[] pares)
{
return GetDataSet(CommandType.Text, sql, pares);
}
public SqlDataReader GetSqlDataReader(CommandType type, string sql,out SqlConnection con, params SqlParameter[] pares)
{
con = GetConnection();
SqlCommand cmd = new SqlCommand(sql, con);
cmd.CommandType = type;
con.Open();
if (pares != null)
{
foreach (SqlParameter pare in pares)
{
cmd.Parameters.Add(pare);
}
}
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
public SqlDataReader GetSqlDataReader(string sql,out SqlConnection con, params SqlParameter[] pares)        {
return GetSqlDataReader(CommandType.Text, sql,out con, pares);
}
public bool MidfyDB(CommandType type, string sql, params SqlParameter[] pares)
{
bool falg = false;
SqlConnection con = GetConnection();
SqlCommand cmd = new SqlCommand(sql, con);
cmd.CommandType = type;
if (pares != null)
{
foreach (SqlParameter pare in pares)
{
cmd.Parameters.Add(pare);
}
}
SqlTransaction trans = null;
try
{
con.Open();
trans = con.BeginTransaction();
cmd.Transaction = trans;
cmd.ExecuteNonQuery();
trans.Commit();
falg = true;
}
catch (Exception e)
{
trans.Rollback();
throw e;
}
finally
{
con.Close();
}
return falg;
}
public bool MidfyDB(string sql, params SqlParameter[] pares)
{
return MidfyDB(CommandType.Text, sql, pares);
}
/// <summary>
/// 查询聚合函数类
/// </summary>
/// <param name="type">数据库操作的类型如:StoredProcedure(存储过程)、Text(⽂本)</param>
/
// <param name="sql">数据库操作字符集</param>
/// <param name="paras">查询数据库时所⽤的参数</param>
/// <returns>object(⼀般为单个值)</returns>
public object GetScalar(CommandType type, string sql, params SqlParameter[] paras)
{
SqlConnection con = GetConnection();//创建数据集对象
SqlCommand cmd = new SqlCommand(sql, con);//操作数据库对象
cmd.CommandType = type;//数据库操作的类型
//如果参数不为空
if (paras != null)
{
/
/遍历参数数组
foreach (SqlParameter para in paras)
{
cmd.Parameters.Add(para);//给操作数据库对象加上参数
}
}
con.Open();//打开数据库连接
object obj = cmd.ExecuteScalar();//返回⼀个⼀⾏⼀列的植
con.Close();//关闭数据库连接
return obj;
}
public object GetScalar(string sql, params SqlParameter[] paras)
{
return GetScalar(CommandType.Text, sql, paras);
}
#region 数据填充DataTable
public static DataTable DataFill(SqlCommand cmd)
{
DataTable dt = new DataTable();
cmd.Connection = GetConnection();
SqlDataAdapter _da = new SqlDataAdapter(cmd);
_da.Fill(dt);
return dt;
}
#endregion
//public DataTable DateTel(string sql)
//{
//    SqlConnection con = GetConnection();
//    SqlCommand cmd = new SqlCommand(sql, con);
//    SqlDataAdapter da = new SqlDataAdapter(cmd);
//    DataTable dt = new DataTable();
//    da.Fill(dt);
//    return dt;
/
/}
internal DataSet GetDataSet(string sql, int UID)
{
sqltransaction什么意思throw new NotImplementedException();
}
public static SqlCommand GetCommand(string text)
{
SqlCommand cmdObject = new SqlCommand();
cmdObject.CommandText = text;
return cmdObject;
}
public static void PreparedCommand(SqlCommand cmd, SqlParameter[] pars)        {
foreach (SqlParameter p in pars)
{
cmd.Parameters.Add(p);
}
}
}
}

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。