C# sqlhelper

底层sqlHelper

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Text;

namespace WX_Card.CardDAL
{
    public class D_Helper
    {
        #region 日志信息
        /// <summary>
        /// 记录日志信息
        /// </summary>
        /// <param name="message">日志信息</param>
        public static void WriteBarcodesLog(string message)
        {
            if (string.IsNullOrWhiteSpace(message))
                return;
            message = "【" + DateTime.Now.ToString("HH:mm:ss") + "】" + message;
            try
            {
                string path = "";
                if (path == "")
                {
                    path = "D:/LogFile/DAL_Log/";
                }

                if (!Directory.Exists(path))
                {
                    Directory.CreateDirectory(path);
                }

                //文件名  //按天记录日志
                string fileName = DateTime.Now.ToString("yyyyMMdd") + ".txt";

                path += fileName;

                if (File.Exists(path))
                {

                    System.IO.FileStream fs = new System.IO.FileStream(path, System.IO.FileMode.Append);
                    System.IO.StreamWriter sw = new System.IO.StreamWriter(fs, System.Text.Encoding.Default);
                    try
                    {
                        sw.WriteLine(message);
                        sw.Close();
                        fs.Close();
                    }
                    catch
                    {
                        sw.Close();
                        fs.Close();
                    }
                }
                else
                {
                    System.IO.FileStream fs = new System.IO.FileStream(path, System.IO.FileMode.Create);
                    System.IO.StreamWriter sw = new System.IO.StreamWriter(fs, System.Text.Encoding.Default);
                    try
                    {
                        sw.WriteLine(message);
                        sw.Close();
                        fs.Close();
                    }
                    catch
                    {
                        sw.Close();
                        fs.Close();
                    }
                }
            }
            catch
            {
            }
        }

        public static void WriteException(string msg)
        {
            WriteBarcodesLog(msg, "exception");
        }

        /// <summary>
        /// 记录日志信息
        /// </summary>
        /// <param name="message">日志信息</param>
        public static void WriteBarcodesLog(string message, string fname)
        {
            if (string.IsNullOrWhiteSpace(message))
                return;
            message = "【" + DateTime.Now.ToString("HH:mm:ss") + "】" + message;
            try
            {
                string path = "";
                if (path == "")
                {
                    path = "D:/LogFile/DAL_Log/";
                }

                if (!Directory.Exists(path))
                {
                    Directory.CreateDirectory(path);
                }

                //文件名  //按天记录日志
                string fileName = fname + DateTime.Now.ToString("yyyyMMdd") + ".txt";

                path += fileName;

                if (File.Exists(path))
                {
                    System.IO.FileStream fs = new System.IO.FileStream(path, System.IO.FileMode.Append);
                    System.IO.StreamWriter sw = new System.IO.StreamWriter(fs, System.Text.Encoding.Default);
                    try
                    {
                        sw.WriteLine(message);
                        sw.Close();
                        fs.Close();
                    }
                    catch
                    {
                        sw.Close();
                        fs.Close();
                    }
                }
                else
                {
                    System.IO.FileStream fs = new System.IO.FileStream(path, System.IO.FileMode.Create);
                    System.IO.StreamWriter sw = new System.IO.StreamWriter(fs, System.Text.Encoding.Default);
                    try
                    {
                        sw.WriteLine(message);
                        sw.Close();
                        fs.Close();
                    }
                    catch
                    {
                        sw.Close();
                        fs.Close();
                    }
                }
            }
            catch
            {
            }
        }
        #endregion

        /// <summary>
        /// 运行SQL语句,返回DataSet
        /// </summary>
        /// <param name="SqlStr"></param>
        /// <returns></returns>
        public DataSet runSQLDataSet(string SqlStr)
        {
            string connstr = WX_Card.CardDAL.Properties.Settings.Default.lswxConnectionString;
            SqlCommand cm = new SqlCommand();
            cm.Connection = new SqlConnection(connstr);
            cm.Connection.Open();
            cm.CommandText = SqlStr;
            cm.CommandType = CommandType.Text;
            DataSet DS = null;
            try
            {
                SqlDataAdapter da = new SqlDataAdapter(cm);
                DS = new DataSet();
                da.Fill(DS);
                da.Dispose();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                cm.Connection.Close();
                cm.Dispose();
            }
            return DS;
        }
        /// <summary>
        /// 执行sql语句返回受影响行数【放注入】
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public  int GetDataRow(string sql, params SqlParameter[] paras)
        {
            string connstr = WX_Card.CardDAL.Properties.Settings.Default.lswxConnectionString;
            DataTable dt = null;
            using (SqlConnection conn = new SqlConnection(connstr))
            {
                SqlCommand command = new SqlCommand(sql, conn);
                command.Parameters.AddRange(paras);
                SqlDataAdapter adapter = new SqlDataAdapter(command);
                dt = new DataTable();
                adapter.Fill(dt);
            }
                return dt.Rows.Count;
        }
        /// <summary>
        /// 运行SQL语句,返回DataTable
        /// </summary>
        /// <param name="SqlStr"></param>
        /// <returns></returns>
        public DataTable runSQLDataTable(string SqlStr)
        {
            string connstr = Properties.Settings.Default.lswxConnectionString;
            SqlCommand cm = new SqlCommand();
            cm.Connection = new SqlConnection(connstr);
            cm.Connection.Open();
            cm.CommandText = SqlStr;
            cm.CommandType = CommandType.Text;
            cm.CommandTimeout = 120;//超时时间设置为2分钟
            DataSet DS = null;
            try
            {
                SqlDataAdapter da = new SqlDataAdapter(cm);
                DS = new DataSet();
                da.Fill(DS);
                da.Dispose();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                cm.Connection.Close();
                cm.Dispose();
            }

            if (DS != null && DS.Tables.Count > 0)
                return DS.Tables[0];
            else
                return null;
        }
        /// <summary>
        /// 执行sql语句,返回受影响行数
        /// </summary>
        /// <param name="SqlStr"></param>
        /// <returns></returns>
        public int runSQLNoQuery(string SqlStr)
        {
            int reint = 0;
            string connstr = Properties.Settings.Default.lswxConnectionString;
            SqlCommand cm = new SqlCommand();
            cm.Connection = new SqlConnection(connstr);
            cm.Connection.Open();
            cm.CommandText = SqlStr;
            cm.CommandType = CommandType.Text;
            cm.CommandTimeout = 60;
            try
            {
                reint = cm.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                WriteBarcodesLog(string.Format("执行sql时发生异常[{0}]:{1}", SqlStr, ex.Message), "runSQLNoQuery");
            }
            finally
            {
                cm.Connection.Close();
                cm.Dispose();
            }
            return reint;
        }


        /// <summary>
        /// 执行sql语句,返回指定值
        /// </summary>
        /// <param name="SqlStr"></param>
        /// <returns></returns>
        public int runSQLScalar(string SqlStr)
        {
            int reint = 0;
            string connstr = Properties.Settings.Default.lswxConnectionString;
            SqlCommand cm = new SqlCommand();
            cm.Connection = new SqlConnection(connstr);
            cm.Connection.Open();
            cm.CommandText = SqlStr;
            cm.CommandType = CommandType.Text;
            try
            {
                reint = Convert.ToInt32(cm.ExecuteScalar());
            }
            catch (Exception ex)
            {
                WriteBarcodesLog(string.Format("执行sql时发生异常[{0}]:{1}", SqlStr, ex.Message));
            }
            finally
            {
                cm.Connection.Close();
                cm.Dispose();
            }
            return reint;
        }


        /// <summary>
        /// 运行存储过程,返回DataSet
        /// </summary>
        /// <param name="StoredProcedureName"></param>
        /// <returns></returns>
        public static DataSet runProcedure(string ProcedureName, List<sp_param> sps)
        {
            string connstr = WX_Card.CardDAL.Properties.Settings.Default.lswxConnectionString;
            SqlCommand cm = new SqlCommand();
            cm.Connection = new SqlConnection(connstr);
            cm.Connection.Open();
            cm.CommandText = ProcedureName;

            if (sps != null && sps.Count > 0)
            {
                foreach (var sp in sps)
                {
                    SqlParameter sparam = new SqlParameter(sp.param_name, sp.param_value);
                    cm.Parameters.Add(sparam);
                }
            }

            cm.CommandType = CommandType.StoredProcedure;
            DataSet DS = null;
            try
            {
                SqlDataAdapter da = new SqlDataAdapter(cm);
                DS = new DataSet();
                da.Fill(DS);
                da.Dispose();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                cm.Connection.Close();
                cm.Dispose();
            }
            return DS;
        }

        /// <summary>
        /// 运行存储过程,无参数
        /// </summary>
        /// <param name="StoredProcedureName"></param>
        /// <returns></returns>
        public static void runProcedure(string ProcedureName)
        {
            string connstr = WX_Card.CardDAL.Properties.Settings.Default.lswxConnectionString;
            SqlCommand cm = new SqlCommand();
            cm.Connection = new SqlConnection(connstr);
            cm.Connection.Open();
            cm.CommandText = ProcedureName;
            cm.CommandType = CommandType.StoredProcedure;
            DataSet DS = null;
            try
            {
                SqlDataAdapter da = new SqlDataAdapter(cm);
                DS = new DataSet();
                da.Fill(DS);
                da.Dispose();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                cm.Connection.Close();
                cm.Dispose();
            }
        }

        /// <summary>
        /// 大批量数据插入
        /// </summary>
        public static void InsertTable(DataTable dt, string tableName)
        {
            string connstr = Properties.Settings.Default.lswxConnectionString;
            using (SqlConnection conn = new SqlConnection(connstr))
            {
                Stopwatch sw = new Stopwatch();
                SqlBulkCopy bulkCopy = new SqlBulkCopy(conn);
                bulkCopy.DestinationTableName = tableName;
                bulkCopy.BatchSize = dt.Rows.Count;
                conn.Open();
                sw.Start();
                if (dt != null && dt.Rows.Count > 120)//条目数必须大于120才更新库存,防止库存查询失败,平台库存被清空
                {
                    bulkCopy.WriteToServer(dt);
                    sw.Stop();
                }
                conn.Close();
            }
        }

        /// <summary>
        /// 根据时间戳获取时间
        /// </summary>
        /// <param name="timestamp"></param>
        /// <returns></returns>
        public static DateTime gettime(long timestamp)
        {
            if (timestamp.Equals(0))
            {
                return DateTime.Now;
            }

            var start = new DateTime(1970, 1, 1, 8, 0, 0, DateTimeKind.Utc);
            return start.AddSeconds(timestamp);
        }

        /// <summary>
        /// 从指定时间开始
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        public static long GetTime()
        {
            DateTime DateStart = new DateTime(1970, 1, 1, 8, 0, 0);
            return Convert.ToInt64((DateTime.Now - DateStart).TotalSeconds);
        }

        /// <summary>
        /// 实体类转换(只支持单个类转换,不支持List)
        /// </summary>
        /// <typeparam name="TIn"></typeparam>
        /// <typeparam name="TOut"></typeparam>
        /// <param name="tIn"></param>
        /// <returns></returns>
        public static TOut Trans<TIn, TOut>(TIn tIn)
        {
            TOut tOut = Activator.CreateInstance<TOut>();
            foreach (var outfield in tOut.GetType().GetFields())
            {
                foreach (var infield in tIn.GetType().GetFields())
                {
                    if (outfield.Name.Equals(infield.Name))
                    {
                        try
                        {
                            outfield.SetValue(tOut, infield.GetValue(tIn));
                        }
                        catch
                        {

                        }
                        break;
                    }
                }
            }
            foreach (var outProperty in tOut.GetType().GetProperties())
            {
                foreach (var inProperty in tIn.GetType().GetProperties())
                {
                    if (outProperty.Name.Equals(inProperty.Name))
                    {
                        try
                        {
                            outProperty.SetValue(tOut, inProperty.GetValue(tIn, null), null);
                        }
                        catch
                        {

                        }
                        break;
                    }
                }

            }
            return tOut;
        }

        /// <summary>
        /// 大批量数据插入，
        /// </summary>
        public static void InsertBatchData(DataTable dt, string tableName)
        {
            string connstr = WX_Card.CardDAL.Properties.Settings.Default.lswxConnectionString;
            using (SqlConnection conn = new SqlConnection(connstr))
            {
                Stopwatch sw = new Stopwatch();
                SqlBulkCopy bulkCopy = new SqlBulkCopy(conn);
                bulkCopy.DestinationTableName = tableName;
                bulkCopy.BatchSize = dt.Rows.Count;
                conn.Open();
                sw.Start();
                if (dt != null && dt.Rows.Count > 120)//条目数必须大于120才更新库存,防止库存查询失败,平台库存被清空
                {
                    bulkCopy.WriteToServer(dt);
                    sw.Stop();
                    runProcedure("proc_CompareInventory");
                }
                conn.Close();
            }
        }
    }
    /// <summary>
    /// 执行存储过程附带的参数对象
    /// </summary>
    public class sp_param
    {
        /// <summary>
        /// 参数名
        /// </summary>
        public string param_name { get; set; }
        /// <summary>
        /// 参数值
        /// </summary>
        public string param_value { get; set; }
    }
}