sqlserver访问类

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Utils.SQL
{
public class DBHelper
{
private string ConnectionString = "";

private readonly static string ReadOnlyConnectionString =
ConfigurationManager.ConnectionStrings["DxConnectionString"].ConnectionString;
public DBHelper(string connstr="")
{
if (string.IsNullOrEmpty(connstr))
{
ConnectionString = ReadOnlyConnectionString;
}
else
{
ConnectionString = connstr;
}
}
/// <summary>
/// 执行查询语句，返回DataSet
/// </summary>
/// <param name="SQLString">查询语句</param>
/// <returns>DataSet</returns>
public DataSet GetDataSet(string SQLString,out string msg)
{
msg = "";
try
{

SqlConnection conn = new SqlConnection(ConnectionString);
conn.Open();

try
{

DataSet ds = new DataSet();

SqlDataAdapter command = new SqlDataAdapter(SQLString, conn);
command.Fill(ds, "ds");

msg = "操作成功";
return ds;
}
catch (Exception e)
{
msg = e.Message;
return null;
}
finally
{

conn.Close();
}
}
catch(Exception ex1)
{
msg = ex1.Message;
return null;
}

}
public int ExecuteCommand(string sql,out string msg)
{

msg = "";

try
{
if (string.IsNullOrEmpty(sql))
{
msg = "语句为空！";
return -1;
}
SqlConnection conn = new SqlConnection(ConnectionString);
conn.Open();
try
{
SqlTransaction myTran;
myTran = conn.BeginTransaction();
try
{
SqlCommand com = new SqlCommand(sql, conn);
com.Transaction = myTran;
com.CommandTimeout = 600;//超时设置//
int result = com.ExecuteNonQuery();
myTran.Commit();
msg = "操作成功";
return 0;
}
catch (Exception E)
{
myTran.Rollback();
msg = E.Message;
return -1;
}
}
catch (Exception E)
{
msg = E.Message;
return -1;
}
finally
{
conn.Close();
}

}
catch(Exception ex)
{
msg = ex.Message;
return -1;
}

}

/// 存储过程
public System.Data.DataSet GetPDataSet(string procName, Dictionary<string, string> dicParams ,out string omsg)
{
omsg = "";

try
{
if (string.IsNullOrEmpty(procName))
{
omsg = "存储过程名为空！";
return null;
}
List<SqlParameter> lsParms = new List<SqlParameter>();
if (dicParams != null && dicParams.Count > 0)
{
foreach (string key in dicParams.Keys)
{
if (string.IsNullOrEmpty(key))
{
continue;
}
if (key.StartsWith("@"))
{
lsParms.Add(new SqlParameter(key, dicParams[key]));
}
else
{
lsParms.Add(new SqlParameter("@" + key, dicParams[key]));
}
}
}
SqlConnection conn = new SqlConnection(ConnectionString);
conn.Open();

if (conn == null)
{
omsg = "数据库连接异常，可能配置信息不正确";
return null;
}
try
{
SqlTransaction tran = conn.BeginTransaction();
try
{
DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand();

cmd.CommandTimeout = 600;
cmd.Connection = conn;
cmd.CommandText = procName;
cmd.CommandTimeout = 0;
cmd.Transaction = tran;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
if (lsParms != null)
{
if (lsParms.Count > 0)
{
foreach (SqlParameter spram in lsParms)
{
cmd.Parameters.Add(spram);
}
}
}
SqlDataAdapter da = new SqlDataAdapter(cmd);

da.Fill(ds);
tran.Commit();
omsg = "操作成功";
return ds;
}
catch (Exception E)
{
omsg = E.Message;
tran.Rollback();
return null;
}
}
catch (Exception E)
{
omsg = E.Message;
return null;
}
finally
{
conn.Close();
}
}
catch(Exception ex)
{
omsg = ex.Message;
return null;
}

}
//public System.Data.DataSet GetPTDataSet(string procName, Dictionary<string, string> dicParams)
//{
// if (string.IsNullOrEmpty(procName))
// {
// return null;
// }
// List<SqlParameter> lsParms = new List<SqlParameter>();
// if (dicParams != null && dicParams.Count > 0)
// {
// foreach (string key in dicParams.Keys)
// {
// if (string.IsNullOrEmpty(key))
// {
// continue;
// }
// if (key.StartsWith("@"))
// {
// lsParms.Add(new SqlParameter(key, dicParams[key]));
// }
// else
// {
// lsParms.Add(new SqlParameter("@" + key, dicParams[key]));
// }
// }
// }
// DBConnectionSingletion pool = DBConnectionSingletion.Instance;
// SqlConnection conn = pool.BorrowDBConnection();
// try
// {
// SqlTransaction tran = conn.BeginTransaction();
// try
// {
// DataSet ds = new DataSet();
// SqlCommand cmd = new SqlCommand();

// cmd.CommandTimeout = 600;
// cmd.Connection = conn;
// cmd.CommandText = procName;
// cmd.CommandTimeout = 0;
// cmd.Transaction = tran;
// cmd.CommandType = CommandType.StoredProcedure;
// cmd.CommandType = System.Data.CommandType.StoredProcedure;
// if (lsParms != null)
// {
// if (lsParms.Count > 0)
// {
// foreach (SqlParameter spram in lsParms)
// {
// cmd.Parameters.Add(spram);
// }
// }
// }
// SqlDataAdapter da = new SqlDataAdapter(cmd);

// da.Fill(ds);
// tran.Commit();
// return ds;
// }
// catch (Exception E)
// {
// tran.Rollback();
// return null;
// }
// }
// catch (Exception E)
// {
// return null;
// }
// finally
// {
// //Return the Connection to the pool after using it
// pool.ReturnDBConnection(conn);
// }
//}
}
}