xiaomiMes/MainForm/ClassFile/ProjectClass/SQLHelper_New.cs

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.IO;
using HslCommunication.LogNet;

namespace MainForm
{
    /// <summary>
    /// SQLHelper-数据库执行成功后关闭连接
    /// </summary>
    public class SQLHelper_New
    {
        /// <summary>
        /// 用于数据库日志记录
        /// </summary>
        private static ILogNet logNet = new LogNetDateTime(GlobalContext.SQLLogDir, GenerateMode.ByEveryDay);

        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="connStr"></param>
        /// <param name="ps"></param>
        /// <returns></returns>
        /// <exception cref="Exception"></exception>
        public static DataSet Query(string sql, params SqlParameter[] ps)
        {
            string connStr =
@"server= " + GlobalContext.Server +
";database= DB" + DateTime.Now.ToString("yyyyMM") +
";uid=" + GlobalContext.User +
";pwd=" + GlobalContext.PassWord;

            using (SqlConnection con = new SqlConnection(connStr))
            {
                try
                {
                    using (SqlDataAdapter da = new SqlDataAdapter(sql, con))
                    {
                        if (ps != null)
                        {
                            da.SelectCommand.Parameters.AddRange(ps);
                        }
                        con.Open();

                        DataSet dataSet = new DataSet();
                        da.Fill(dataSet, "ds");
                        con.Close();
                        return dataSet;
                    }
                }
                catch (System.Data.SqlClient.SqlException ex)  //  catch保持与旧sqlhelper同步
                {
                    logNet.WriteError(ex.Message.ToString());
                    throw new Exception(ex.Message);
                }
            }
        }

        /// <summary>
        /// 执行SQL语句，返回影响的记录数
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="connStr"></param>
        /// <param name="ps"></param>
        /// <returns></returns>
        public static int ExecuteSQL(string sql, params SqlParameter[] ps)
        {
            string connStr =
@"server= " + GlobalContext.Server +
";database= DB" + DateTime.Now.ToString("yyyyMM") +
";uid=" + GlobalContext.User +
";pwd=" + GlobalContext.PassWord;

            using (SqlConnection con = new SqlConnection(connStr))
            {
                try
                {
                    using (SqlCommand cmd = new SqlCommand(sql, con))
                    {
                        if (ps != null)
                        {
                            cmd.Parameters.AddRange(ps);
                        }
                        con.Open();

                        int cc = cmd.ExecuteNonQuery();
                        con.Close();
                        return cc;
                    }
                }
                catch (System.Data.SqlClient.SqlException e)
                {
                    logNet.WriteError(e.Message.ToString());
                    throw e;
                }
            }
        }

        /// <summary>
        /// 执行SQL语句，返回文字结果
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="connStr"></param>
        /// <param name="ps"></param>
        /// <returns></returns>
        public static string ExecuteNonQuery(string sql, params SqlParameter[] ps)
        {
            string connStr =
@"server= " + GlobalContext.Server +
";database= DB" + DateTime.Now.ToString("yyyyMM") +
";uid=" + GlobalContext.User +
";pwd=" + GlobalContext.PassWord;

            using (SqlConnection con = new SqlConnection(connStr))
            {
                try
                {
                    using (SqlCommand cmd = new SqlCommand(sql, con))
                    {
                        if (ps != null)
                        {
                            cmd.Parameters.AddRange(ps);
                        }
                        con.Open();

                        int cc = cmd.ExecuteNonQuery();
                        con.Close();
                        return "成功";
                    }
                }
                catch (Exception ex)
                {
                    logNet.WriteError(ex.Message.ToString());
                    return ex.Message.ToString();
                }
            }
        }
    }
}