- 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
- }
复制代码
|
|