连接数据库通用类

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace MyDAL
{
public class SqlDBHelper
{
string strCn = "server=.;database=MySchool;uid=sa";

///

/// 获得单个值
///

/// 获取值的SQL语句（max,min,sum,avg,count...）
/// 参数数组
/// 执行结果
public object GetSingleValue(string sql, params SqlParameter[] pa)
{
//string strCn = "server=.\\sqlexpress;database=MySchool;integrated security=true;";
SqlConnection cn = new SqlConnection(strCn);
SqlCommand cmd = new SqlCommand(sql, cn);
object obj = null;
try
{
cn.Open();
foreach (SqlParameter p in pa)
cmd.Parameters.Add(p);
obj = cmd.ExecuteScalar();
}
catch
{
return null;
}
finally
{
cn.Close();
}
return obj;
}
//public object GetSingleValue(string sql)
//{

//}
//public object GetSingleValue(string sql, SqlParameter pa)
//{

//}
//获得读取器
public SqlDataReader GetDataReader(string sql, params SqlParameter[] pa)
{
//string strCn = "server=.\\sqlexpress;database=MySchool;integrated security=true;";
SqlConnection cn = new SqlConnection(strCn);
SqlCommand cmd = new SqlCommand(sql, cn);
cn.Open();
SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
//cn.Close();
return dr;
//dr.Close();
//cn.Close();
}
//获得数据表
public DataTable GetDataTable(string sql, params SqlParameter[] pa)
{
//string strCn = "server=.\\sqlexpress;database=MySchool;integrated security=true;";
SqlConnection cn = new SqlConnection(strCn);
SqlCommand cmd = new SqlCommand(sql, cn);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
return dt;
}
///

/// 用SQL语句执行增删改
///

/// SQL语句(INSERT,UPDATE,DELETE|CREATE,DROP,ALTER)
/// 参数数组
/// 向数据库提交的记录数
public int SetDataBySql(string sql, params SqlParameter[] pa)
{
SqlConnection cn = new SqlConnection(strCn);
SqlCommand cmd = new SqlCommand(sql, cn);
foreach (SqlParameter p in pa)

cmd.Parameters.Add(p);
int executeCount = 0;
try
{
cn.Open();
executeCount = cmd.ExecuteNonQuery();
}
catch
{
executeCount = 0;
}
finally
{
cn.Close();
}
return executeCount;
}
//用SQL语句带事务执行增删改
public int SetDataBySqlWithTrans(string[] sqlList, SqlParameter[][] paList)
{
SqlConnection cn = new SqlConnection(strCn);
SqlCommand cmd = cn.CreateCommand();
cn.Open();
SqlTransaction ta = cn.BeginTransaction();
cmd.Transaction = ta;
int executeCount = 0;
try
{
for (int i = 0; i < sqlList.Length; i++)
{
for (int j = 0; j < paList[i].Length; j++)
{
cmd.Parameters.Add(paList[i][j]);
}
https://www.360docs.net/doc/7416378633.html,mandText = sqlList[i];
executeCount += cmd.ExecuteNonQuery();
}
https://www.360docs.net/doc/7416378633.html,mit();
}
catch
{
ta.Rollback();
executeCount = 0;
}
finally
{
cn.Close();
}
return executeCount;
}

//用存储过程执行增删改
public int SetDataByProc(out object returnValue, string procName, params SqlParameter[] pa)
{
SqlConnection cn = new SqlConnection(strCn);
SqlCommand cmd = new SqlCommand(procName, cn);
https://www.360docs.net/doc/7416378633.html,mandType = CommandType.StoredProcedure;
foreach (SqlParameter p in pa)
cmd.Parameters.Add(p);
SqlParameter pr = new SqlParameter("@count", SqlDbType.Int);
pr.Direction = ParameterDirection.Output;
cmd.Parameters.Add(pr);
int executeCount = 0;
try
{
cn.Open();
executeCount = cmd.ExecuteNonQuery();
returnValue = pr.Value;
}
catch (SqlException)
{
returnValue = null;
executeCount = 0;
}
finally
{
cn.Close();
}
return executeCount;
}

}
}