DBUtility通用数据库访问类sql server 版本常用方法封装 - 黑马程序员技术交流社区

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data.SqlClient;
using System.Text.RegularExpressions;
using System.Data;
public class TSQLServer
{
#region 获取数据库连接字符串
static string connectionString = string.Empty;
public static string GetConnstr()
{
connectionString = ConfigurationManager.ConnectionStrings["SQLConnString"].ConnectionString;
return connectionString;
}
#endregion
#region 获取连接字符串进行数据判断
/// <summary>
/// 获取连接字符串SqlConn进行数据判断，返回连接
/// </summary>
/// <param name="connstr">数据库连接字符串</param>
/// <returns>返回数据连接字符串</returns>
public static SqlConnection SqlConn(string connstr)
{
SqlConnection conn = new SqlConnection();
if (connstr.ToString() == "")
{
connstr = GetConnstr();
}
conn.ConnectionString = connstr;
return conn;
}
#endregion
#region 创建数据库连接命令,判断数据字符数组
/// <summary>
/// 创建数据库连接命令,判断数据字符数组
/// </summary>
/// <param name="conn">连接字符串</param>
/// <param name="Sql">Sql语句</param>
/// <param name="p">字符串数组</param>
/// <param name="s">对象</param>
/// <returns>返回命令对象</returns>
public static SqlCommand SqlCmd(SqlConnection conn, string Sql, string[] p, params object[] s)
{
SqlCommand cmd = new SqlCommand(Sql, conn);
if (p == null || p.Length == 0)
{
Regex re = new Regex(@"@[a-zA-Z0-9]+", RegexOptions.IgnoreCase);
Match m = re.Match(Sql);
int i = 0;
List<string> str = new List<string>();
while (m.Success)
{
if (i == s.Length)
{
break;
}
if (!str.Contains(m.Value))
{
cmd.Parameters.AddWithValue(m.Value, s[i]);
str.Add(m.Value);
i++;
}
m = m.NextMatch();
}
}
else
{
for (int i = 0; i < p.Length; i++)
{
if (i == s.Length)
{
break;
}
cmd.Parameters.AddWithValue(p[i], s[i]);
}
}
return cmd;
}
#endregion
#region 得到数据库执行错误信息
/// <summary>
/// 得到数据库执行错误信息
/// </summary>
/// <param name="ex">错误信息</param>
/// <param name="Sql">执行Sql语句</param>
/// <param name="p"></param>
/// <param name="s"></param>
public static void getError(Exception ex, string Sql, string[] p, object[] s)
{
string msg = ex.Message + "\r\n\r\nSql(ProcedureName)：\r\n" + Sql;
if (p != null) msg += "\r\n\r\nParams：\r\n" + string.Join(",", p);
if (s != null) msg += "\r\n\r\nValues：\r\n" + string.Join("\r\n", Array.ConvertAll(s, new Converter<object, string>(c => c.ToString())));
throw new Exception(msg);
}
#endregion
#region 执行非查询ExecuteNonQuery返回受影响的行数，增加，修改，删除
public static int ExecuteNonQuery(string Sql, params object[] s)
{
return ExecuteNonQueryWithOther(Sql, "", null, s);
}
public static int ExecuteNonQuery(string Sql, string[] p, params object[] s)
{
return ExecuteNonQueryWithOther(Sql, "", p, s);
}
public static int ExecuteNonQueryWithOther(string Sql, string connstr, params object[] s)
{
return ExecuteNonQueryWithOther(Sql, connstr, null, s);
}
public static int ExecuteNonQueryWithOther(string Sql, string connstr, string[] p, params object[] s)
{
SqlConnection conn = SqlConn(connstr);
conn.Open();
SqlCommand cmd = SqlCmd(conn, Sql, p, s);
int result = 0;
try
{
result = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
getError(ex, Sql, p, s);
}
cmd.Dispose();
conn.Close();
conn.Dispose();
return result;
}
#endregion
#region 执行Sql语句返回DataRow一行数据信息
/// <summary>
/// 执行Sql语句返回DataRow一行数据信息
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="s">参数集合</param>
/// <returns></returns>
public static DataRow ExecDr(string sql, params object[] s)
{
return ExecDrWithOther(sql, "", null, s);
}
public static DataRow ExecDr(string sql, string[] p, params object[] s)
{
return ExecDrWithOther(sql, "", p, s);
}
public static DataRow ExecDrWithOther(string sql, string connstr, params object[] s)
{
return ExecDrWithOther(sql, connstr, null, s);
}
public static DataRow ExecDrWithOther(string sql, string connstr, string[] p, params object[] s)
{
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter();
SqlConnection conn = SqlConn(connstr);
conn.Open();
SqlCommand cmd = SqlCmd(conn, sql, p, s);
da.SelectCommand = cmd;
try
{
da.Fill(ds);
}
catch (Exception ex)
{
getError(ex, sql, p, s);
}
DataRow dr = null;
try
{
dr = ds.Tables[0].Rows[0];
}
catch { }
da.Dispose();
ds.Dispose();
cmd.Dispose();
conn.Close();
conn.Dispose();
return dr;
}
#endregion
#region 执行Sql语句返回DataTable数据信息
/// <summary>
/// 执行Sql语句返回DataTable数据信息
/// </summary>
/// <param name="Sql"> 执行查询Sql语句</param>
/// <param name="s">获取传递Sql参数集合</param>
/// <returns>根据条件返回一个dataTable</returns>
public static DataTable ExecDt(string Sql, params object[] s)
{
return ExecDtWithOther(Sql, "", null, s);
}
public static DataTable ExecDt(string Sql, string[] p, params object[] s)
{
return ExecDtWithOther(Sql, "", p, s);
}
public static DataTable ExecDtWithOther(string Sql, string connstr, params object[] s)
{
return ExecDtWithOther(Sql, connstr, null, s);
}
public static DataTable ExecDtWithOther(string Sql, string connstr, string[] p, params object[] s)
{
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter();
SqlConnection conn = SqlConn(connstr);
conn.Open();
SqlCommand cmd = SqlCmd(conn, Sql, p, s);
da.SelectCommand = cmd;
try
{
da.Fill(ds);
}
catch (Exception ex)
{
getError(ex, Sql, p, s);
}
DataTable dt = null;
try
{
dt = ds.Tables[0];
}
catch { }
da.Dispose();
ds.Dispose();
cmd.Dispose();
conn.Close();
conn.Dispose();
return dt;
}
#endregion
}

复制代码