[笔记系列] C# with MSSQL of DataHelper.cs

using System;using System.Collections.Generic;using System.Data;using System.Data.SqlClient;using System.Diagnostics;using System.Text.RegularExpressions;using System.Web.Configuration;public class DataHelper{    private string connstr = string.Empty;    private TimeSpan mExecutionTime;    public TimeSpan ExecutionTime    {        get { return mExecutionTime; }        set { mExecutionTime = value; }    }    public DataHelper(string connectionString)    {        connstr = connectionString;    }    public DataHelper()    {        connstr = WebConfigurationManager.ConnectionStrings["conStr"].ConnectionString.ToString();    }    public DataTable getDataTable(string SQLString, Dictionary<string, object> SQLParameter, List<Dictionary<string, object>> arr, int type)    {        Stopwatch sw = new Stopwatch();        DataTable dtable = new DataTable();        List<string> sqllist = new List<string>();        try        {            using (SqlConnection conn = new SqlConnection(connstr))            {                conn.Open();                if (type == 1)                {                    using (SqlDataAdapter da = new SqlDataAdapter(SQLString, conn))                    {                        sw.Start();                        if (SQLParameter != null)                        {                            foreach (KeyValuePair<string, object> tempParameter in SQLParameter)                            {                                da.SelectCommand.Parameters.AddWithValue(tempParameter.Key, tempParameter.Value);                            }                        }                        sqllist.Add(getRealSql(da.SelectCommand));                        da.Fill(dtable);                        sw.Stop();                        ExecutionTime = sw.Elapsed;                    }                }                else                {                    using (SqlCommand cmd = new SqlCommand(SQLString, conn))                    {                        if (arr != null)                        {                            sw.Start();                            var trn = cmd.Connection.BeginTransaction();                            cmd.Transaction = trn;                            try                            {                                foreach (Dictionary<string, object> dic in arr)                                {                                    foreach (KeyValuePair<string, object> tempParameter in dic)                                    {                                        cmd.Parameters.AddWithValue(tempParameter.Key, tempParameter.Value);                                    }                                    sqllist.Add(getRealSql(cmd));                                    cmd.ExecuteNonQuery();                                }                                trn.Commit();                            }                            catch (Exception ex)                            {                                trn.Rollback();                                throw ex;                            }                            sw.Stop();                            ExecutionTime = sw.Elapsed;                        }                        else if (SQLParameter != null)                        {                            sw.Start();                            foreach (KeyValuePair<string, object> tempParameter in SQLParameter)                            {                                cmd.Parameters.AddWithValue(tempParameter.Key, tempParameter.Value);                            }                            sqllist.Add(getRealSql(cmd));                            cmd.ExecuteNonQuery();                            sw.Stop();                            ExecutionTime = sw.Elapsed;                        }                    }                }                conn.Close();            }        }        catch (Exception ex)        {            throw ex;        }        return dtable;    }    //查询数量    public int queryCount(string SQLString)    {        return Convert.ToInt32(getDataTable(SQLString, null, null, 1).Rows[0][0]);    }    //查询    public DataTable queryData(string SQLString, Dictionary<string, object> SQLParameter)    {        return getDataTable(SQLString, SQLParameter, null, 1);    }    //新增    public void executeNonQuery(string SQLString, Dictionary<string, object> SQLParameter)    {        try        {            getDataTable(SQLString, SQLParameter, null, 2);        }        catch (Exception ex)        {            throw ex;        }    }    public string getRealSql(SqlCommand sqlcmd)    {        var sql = sqlcmd.CommandText;        for (int i = 0; i < sqlcmd.Parameters.Count - 1; i++)        {            var tmp_op = sqlcmd.Parameters[i];            var tmp_p = ":" + tmp_op.ParameterName.ToString().Replace(":", "");            //age < 20 ? "What's up?" : "Hello";            string name =                tmp_op.ParameterName.StartsWith(":")                    ? tmp_op.ParameterName                    : ":" + tmp_op.ParameterName;            string value =                tmp_op.Value is DateTime    //obj is SuperHero                    ? String.Format(                        "TO_DATE('{0}', '{1}')",                        Convert.ToDateTime(tmp_op.Value).ToString("yyyy/MM/dd HH:mm:ss"), "yyyy/mm/dd hh24:mi:ss"                     )                     : String.Format("'{0}'", tmp_op.Value);            string pattern = string.Format("{0}(?=[/W])|{0}$", name);            sql = Regex.Replace(sql, pattern, value, RegexOptions.IgnoreCase);        }        return sql;    }    //public string Info    //{    //    get { return this._Info; }    //    set { this._Info = value; }    //}}//===================================================================//使用方法//===================================================================            //Select服用,取得DataTable            Dictionary<string, object> dic = new Dictionary<string, object>();            sql = @"SELECT * FROM ActivityRecord                     WHERE CustomerId = @CustomerId                    AND ActivityId = @ActivityId";            dic.Clear();            dic.Add("@CustomerId", sCustId);            dic.Add("@ActivityId", sActivityId);            DataHelper dh = new DataHelper();            DataTable dt = dh.queryData(sql, dic);                    //Insert Delete Update服用,不会回传东西            Dictionary<string, object> dic = new Dictionary<string, object>();            sql = @"INSERT INTO ActivityRecord                     ( CustomerId, ActivityId, Id )                    VALUES                     (@CustomerId, @ActivityId, @Id)";            dic.Clear();            dic.Add("@CustomerId", sCustId);            dic.Add("@ActivityId", sActivityId);            dic.Add("@Id", newGuid);            DataHelper dh = new DataHelper();            dh.executeNonQuery(sql, dic);