dbhelpersql.cs

【原创】很多情况下

using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Collections.Specialized;
using System.IO;
using System.Runtime.InteropServices;
using System.Xml;
using System.Windows.Forms;
using System.Text;

namespace DBUtility
{
    /// <summary>
    /// 数据访问基础类，基于SQL 08-01-20更新
    /// </summary>
    public abstract class DbHelperSQL 
    {
        #region 数据库连接字符串

        #region 从配置文件中读取数据库连接字符串到私有变量
        private static string _SQLConString = "";//System.Configuration.ConfigurationManager.ConnectionStrings["strCon"].ToString();
        private static string _SQLConString_master = "";//System.Configuration.ConfigurationManager.ConnectionStrings["strCon_master"].ToString();        
        #endregion

        #region 默认数据库连接字符串
        /// <summary>
        /// 数据库连接字符串（默认采用Config文件里的数据库连接字符串，也可进行赋值后使用）
        /// </summary>
        public static string SQLConString
        {
            get
            {
                if (_SQLConString.Replace(" ", "").Trim().ToLower().Contains("password="))
                {
                    return _SQLConString;
                }
                else
                {
                    #region 如果查询字符串，无密码关键字"password="，则插入默认密码
                    if (_SQLConString.EndsWith(";"))
                    {
                        return _SQLConString + "password = wgppymcsl";
                    }
                    else
                    {
                        return _SQLConString + ";password = wgppymcsl";
                    }
                    #endregion
                }
            }
            set
            {
                _SQLConString = value;
            }
        }
        #endregion

        #region 系统数据库连接字符串
        /// <summary>
        /// 数据库连接字符串-master（默认采用Config文件里的数据库连接字符串，也可进行赋值后使用）
        /// </summary>
        public static string SQLConString_master
        {
            get
            {
                if (_SQLConString_master.Replace(" ", "").Trim().ToLower().Contains("password="))
                {
                    return _SQLConString_master;
                }
                else
                {
                    #region 如果查询字符串，无密码关键字"password="，则插入默认密码
                    if (_SQLConString_master.EndsWith(";"))
                    {
                        return _SQLConString_master + "password = wgppymcsl";
                    }
                    else
                    {
                        return _SQLConString_master + ";password = wgppymcsl";
                    }
                    #endregion
                }
            }
            set
            {
                _SQLConString_master = value;
            }
        }
        #endregion

        #endregion

        #region 本地配置数据：ShopID
        private static string _ShopID = "";//System.Configuration.ConfigurationManager.AppSettings ["ShopID"].ToString();
        /// <summary>
        /// 本地配置：本店店号ID
        /// </summary>
        public static string ShopID
        {
            get
            {
                return _ShopID;
            }
            set
            {
                value = _ShopID;
            }
        }
        #endregion

        #region 执行简单SQL语句

        #region 执行SQL语句，返回影响的记录数
        /// <summary>
        /// 执行SQL语句，返回影响的记录数
        /// </summary>
        /// <param name="strSQL">SQL语句</param>
        /// <returns>影响的记录数</returns>
        public static int ExecuteSql(string strSQL)
        {
            using (SqlConnection Conn = new SqlConnection(SQLConString))
            {
                using (SqlCommand Cmd = new SqlCommand(strSQL, Conn))
                {
                    try
                    {
                        Conn.Open();
                        int intRows = Cmd.ExecuteNonQuery();
                        return intRows;
                    }
                    catch (System.Data.SqlClient.SqlException E)
                    {
                        Conn.Close();
                        throw new Exception(E.Message);
                    }
                }
            }
        }
        #endregion

        #region 执行多条SQL语句，实现数据库事务，返回事务中全部语句共影响的行数
        /// <summary>
        /// 执行多条SQL语句，实现数据库事务，返回事务中全部语句共影响的行数 （等于0成功但无影响记录，大于0成功，小于0失败）
        /// </summary>
        /// <param name="SQLStringList">多条SQL语句</param>	
        /// <returns>返回事务中全部语句共影响的行数</returns>
        public static int ExecuteSqlTran(ArrayList SQLStringList)
        {
            int intResult = 0;
            using (SqlConnection Conn = new SqlConnection(SQLConString))
            {
                using (SqlCommand Cmd = new SqlCommand())
                {
                    Conn.Open();
                    Cmd.Connection = Conn;
                    SqlTransaction DbTrans = Conn.BeginTransaction();
                    Cmd.Transaction = DbTrans;
                    try
                    {
                        for (int i = 0; i < SQLStringList.Count; i++)
                        {
                            string strSQL = SQLStringList[i].ToString();
                            if (strSQL.Trim().Length > 1)
                            {
                                Cmd.CommandText = strSQL;
                                intResult += Cmd.ExecuteNonQuery();
                            }
                        }
                        //事务提交
                        DbTrans.Commit();
                    }
                    catch (System.Data.SqlClient.SqlException E)
                    {
                        intResult = -1;
                        DbTrans.Rollback();                        
                        throw new Exception(E.Message);
                    }
                    finally
                    {
                        Conn.Close();
                    }
                }
            }
            return intResult;
        }

       
        #endregion

        #region 执行多条SQL语句,没有事务处理，多条语句串行执行，失败返回-1 ExecSampleSQL
        /// <summary>
        /// 执行单条SQL语句，失败返回-1 (多条也按单条执行，没有事务处理)
        /// </summary>
        /// <returns>失败返回-1,成功返回已成功执行的SQL条数</returns>
        public static int ExecSampleSQL(ArrayList SQLStringList)
        {
            int Val = 0;
            using (System.Data.SqlClient.SqlConnection Conn = new SqlConnection(SQLConString))
            {
                using (System.Data.SqlClient.SqlCommand Cmd = new SqlCommand())
                {
                    Cmd.Connection = Conn;
                    try
                    {
                        Conn.Open();
                        for (int iCnt = 0; iCnt < SQLStringList.Count; iCnt++)
                        {
                            if (SQLStringList[iCnt].ToString() == "")
                            {
                                continue;
                            }
                            Cmd.CommandText = SQLStringList[iCnt].ToString().Trim();
                            Cmd.ExecuteNonQuery();
                            Val++;
                        }
                    }
                    catch (Exception e)
                    {
                        Val = -1;
                        throw new Exception(e.Message);
                    }
                    finally
                    {
                        Conn.Close();
                    }
                }
            }
            return Val;
        }

        /// <summary>
        /// 执行单条SQL语句，失败返回-1 (多条也按单条执行，没有事务处理)
        /// </summary>
        /// <returns>失败返回-1,成功返回已成功执行的SQL条数</returns>
        public static int ExecSampleSQL(string[] SQLStringList)
        {
            int Val = 0;
            using (System.Data.SqlClient.SqlConnection Conn = new SqlConnection(SQLConString))
            {
                using (System.Data.SqlClient.SqlCommand Cmd = new SqlCommand())
                {
                    Cmd.Connection = Conn;
                    try
                    {
                        Conn.Open();
                        for (int iCnt = 0; iCnt < SQLStringList.Length; iCnt++)
                        {
                            if (SQLStringList[iCnt].ToString() == "")
                            {
                                continue;
                            }
                            Cmd.CommandText = SQLStringList[iCnt].ToString().Trim();
                            Cmd.ExecuteNonQuery();
                            Val++;
                        }
                    }
                    catch (Exception e)
                    {
                        Val = -1;
                        throw new Exception(e.Message);
                    }
                    finally
                    {
                        Conn.Close();
                    }
                }
            }
            return Val;
        }
        #endregion

        #region 执行带一个存储过程参数的的SQL语句
        /// <summary>
        /// 执行带一个存储过程参数的的SQL语句
        /// </summary>
        /// <param name="strSQL">SQL语句</param>
        /// <param name="ParContent">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>
        /// <returns>影响的记录数</returns>
        public static int ExecuteSql(string strSQL, string ParContent)
        {
            int intRows = -1;
            using (SqlConnection Conn = new SqlConnection(SQLConString))
            {
                using (SqlCommand Cmd = new SqlCommand(strSQL, Conn))
                {
                    System.Data.SqlClient.SqlParameter Par = new System.Data.SqlClient.SqlParameter("@ParContent", SqlDbType.NText);

                    Par.Value = ParContent;
                    Cmd.Parameters.Add(Par);
                    try
                    {
                        Conn.Open();
                        intRows = Cmd.ExecuteNonQuery();
                    }
                    catch (System.Data.SqlClient.SqlException E)
                    {
                        throw new Exception(E.Message);
                    }
                    finally
                    {
                        Conn.Close();
                    }

                }
            }
            return intRows;
        }
        #endregion

        #region 向数据库里插入图像格式的字段
        /// <summary>
        /// 向数据库里插入图像格式的字段
        /// </summary>
        /// <param name="strSQL">SQL语句</param>
        /// <param name="fs">图像字节,数据库的字段类型为Image的情况</param>
        /// <returns>影响的记录数</returns>
        public static int ExecuteSqlInsertImg(string strSQL, byte[] fs)
        {
            int intRows = -1;
            using (SqlConnection Conn = new SqlConnection(SQLConString))
            {
                using (SqlCommand Cmd = new SqlCommand(strSQL, Conn))
                {
                    System.Data.SqlClient.SqlParameter Par = new System.Data.SqlClient.SqlParameter("@fs", SqlDbType.Image);
                    Par.Value = fs;
                    Cmd.Parameters.Add(Par);
                    try
                    {
                        Conn.Open();
                        intRows = Cmd.ExecuteNonQuery();
                    }
                    catch (System.Data.SqlClient.SqlException E)
                    {
                        throw new Exception(E.Message);
                    }
                    finally
                    {
                        Conn.Close();
                    }
                }
            }
            return intRows;
        }       
        #endregion

        #region  执行一条计算查询结果语句，返回查询结果（object）
        /// <summary>
        /// 执行一条计算查询结果语句，返回查询结果（object）。
        /// </summary>
        /// <param name="strSQL">计算查询结果语句</param>
        /// <returns>查询结果（object）</returns>
        public static object GetSingle(string strSQL)
        {
            object obj = null;
            using (SqlConnection Conn = new SqlConnection(SQLConString))
            {
                using (SqlCommand Cmd = new SqlCommand(strSQL, Conn))