连接数据库通用类
- 1、下载文档前请自行甄别文档内容的完整性,平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益,请联系客服反馈,我们会尽快为您处理(人工客服工作时间:9:00-18:30)。
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]);
}
mandText = sqlList[i];
executeCount += cmd.ExecuteNonQuery();
}
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);
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;
}
}
}