SqlServerHelper.cs数据库操作

using  System;
using  System.Collections.Generic;
using  System.Text;
using  System.Data.SqlClient;
using  System.Data;
using  System.Collections;
using  System.Collections.Specialized;
using  System.Drawing;
using  System.IO;
namespace  BasicAppClass
... {
    public enum SqlExceptionType
    ...{
        UniqueKey = 1,//重复键
        ForeignKey=2  //外键
    }
  public  class SqlServerHelper
    ...{
        //数据库连接字符串
        private static string connectionString = "Data Source=192.168.1.51;initial catalog=NBSM_ERP;user id=sa;password=haike;Connect Timeout=30;min pool size=15;max pool size=2000";
        IniOperation io = new IniOperation("Sys_File.ini");
        //private static string connectionString = "Data Source=./SQLExpress;AttachDbFilename=D:/慈溪神马/NBSM_ERP.mdf;Integrated Security=True;User Instance=True";
        //公用连接
        private static SqlConnection connection;
        //错误代码
        private static int ErrorCode = -1;


        private void SetConnectString()
        ...{
            io.IniReadValue("SQLCONNECTSTRING1", "IP");
            connectionString = "Data Source=192.168.1.51;initial catalog=NBSM_ERP;user id=sa;password=haike;Connect Timeout=30;min pool size=15;max pool size=2000";
        }
        
        /**//// <summary>
        /// 创建公用连接
        /// </summary>
        /// <returns></returns>
        private static void GetConnection()
        ...{
            connection = new SqlConnection(connectionString);
            if (connection.State != ConnectionState.Open)
                connection.Open();
        }
        /**//// <summary>
        /// 关闭公用连接
        /// </summary>
        private static void CloseConnection()
        ...{
            if (connection.State == ConnectionState.Open)
                connection.Close();

        }
        /**//// <summary>
        /// 执行查询语句，返回DataSet对象
        /// </summary>
        /// <param name="SQLString">查询语句</param>
        /// <returns>DataSet</returns>
        public static DataSet Query(string SQLString)
        ...{
            GetConnection();
            DataSet ds = new DataSet();
            try
            ...{
                if (connection.State != ConnectionState.Open)
                    connection.Open();
                SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);
                command.Fill(ds, "ds");
                CloseConnection();

            }
            catch (System.Data.SqlClient.SqlException ex)
            ...{
               throw new Exception(ex.Message);
            }
            return ds;
        }
        /**//// <summary>
        /// 执行存储过程，返回DataSet对象
        /// </summary>
        /// <param name="SQLString">查询语句</param>
        /// <returns>DataSet</returns>
      public static DataSet Query(string StoredProcedureStr, SqlParameter[] parameters)
        ...{
            GetConnection();
            DataSet ds = new DataSet();
            try
            ...{
                if (connection.State != ConnectionState.Open)
                    connection.Open();
                SqlDataAdapter command = new SqlDataAdapter(StoredProcedureStr, connection);
                command.SelectCommand.CommandType = CommandType.StoredProcedure;
                foreach (SqlParameter parameter in parameters)
                ...{
                    command.SelectCommand.Parameters.Add(parameter);
                }
                command.Fill(ds, "ds");
                CloseConnection();

            }
            catch (System.Data.SqlClient.SqlException ex)
            ...{
                throw new Exception(ex.Message);
            }
            return ds;
        }
        /**//// <summary>
        /// 执行SQL语句，返回影响的记录数
        /// </summary>
        /// <param name="SQLString">SQL语句</param>
        /// <returns>影响的记录数,若有异常则返回错误代码</returns>
        public static int ExecuteSql(string SQLString)
        ...{
            GetConnection();
            using (SqlCommand cmd = new SqlCommand(SQLString, connection))
            ...{
                try
                ...{
                    if (connection.State != ConnectionState.Open)
                        connection.Open();
                    int rows = cmd.ExecuteNonQuery();
                    CloseConnection();
                    return rows;
                }
                catch (System.Data.SqlClient.SqlException E)
                ...{
                    CloseConnection();
                    if (E.Number == 2627)//重复键
                    ...{
                        throw new Exception(SqlExceptionType.UniqueKey.ToString());
                    }
                    else if (E.Number == 547)//外键
                    ...{
                        throw new Exception(SqlExceptionType.ForeignKey.ToString());
                    }
                    else
                    ...{
                        throw new Exception(E.Message);
                    }
                    
                    return ErrorCode;
                }
            }

        }
        /**//// <summary>
        /// 执行存储过程，返回影响的记录数
        /// </summary>
        /// <param name="SQLString">SQL语句</param>
        /// <returns>影响的记录数,若有异常则返回错误代码</returns>
      public static int ExecuteStoredProcedure(string StoredProcedureStr, SqlParameter[] parameters)
        ...{
            GetConnection();
            using (SqlCommand cmd = new SqlCommand(StoredProcedureStr, connection))
            ...{
                try
                ...{
                    if (connection.State != ConnectionState.Open)
                    ...{
                        connection.Open();
                    }
                    foreach (SqlParameter parameter in parameters)
                    ...{
                        cmd.Parameters.Add(parameter);
                    }
                    cmd.CommandType = CommandType.StoredProcedure;
                    int rows = cmd.ExecuteNonQuery();
                    CloseConnection();
                    return rows;
                }
                catch (System.Data.SqlClient.SqlException E)
                ...{
                    CloseConnection();
                    if (E.Number == 2601)//重复键
                    ...{
                        throw new Exception(SqlExceptionType.UniqueKey.ToString());
                    }
                    else
                    ...{
                        throw new Exception(E.Message);
                    }

                    return ErrorCode;
                }
            }

        }
        /**//// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">多条SQL语句</param>        
        public static void ExecuteSqlTran(ArrayList SQLStringList)
        ...{
            GetConnection();
            if (connection.State != ConnectionState.Open)
                connection.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = connection;
            SqlTransaction tx = connection.BeginTransaction();
            cmd.Transaction = tx;
            try
            ...{
                for (int n = 0; n < SQLStringList.Count; n++)
                ...{
                    string strsql = SQLStringList[n].ToString();
                    if (strsql.Trim().Length > 1)
                    ...{
                        cmd.CommandText = strsql;
                        cmd.ExecuteNonQuery();
                    }
                }
                tx.Commit();
                CloseConnection();
            }
            catch (System.Data.SqlClient.SqlException E)
            ...{
                tx.Rollback();
                throw new Exception(E.Message);
            }
        }

        /**//// <summary>
        /// 向数据库里插入图像格式的字段
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="path">图像路径</param>
        /// <returns>影响的记录数</returns>
      public static int imgToDB(string sql,string path)
      ...{   
          //参数sql中要求保存的imge变量名称为@fs
          
          FileStream fs = File.OpenRead(path);
          byte[] imageb = new byte[fs.Length];
          fs.Read(imageb, 0, imageb.Length);
          fs.Close();
          GetConnection();
          SqlCommand com3 = new SqlCommand(sql, connection);
          com3.Parameters.Add("@fs", SqlDbType.Image).Value = imageb;

         
          try
          ...{
              GetConnection();
              int rows = com3.ExecuteNonQuery();
              CloseConnection();
              return rows;
          }
          catch (System.Data.SqlClient.SqlException E)
          ...{
              throw new Exception(E.Message);
          }
          finally
          ...{
              com3.Dispose();
              CloseConnection();
          }
         
      } 

      /**//// <summary>
      /// 读取图片
      /// </summary>
      /// <param name="Sql">SQL语句</param>
      /// <returns>Bitmap对象</returns>
      public static Bitmap QueryImg(string Sql)
      ...{
          Bitmap bm = null;
          
          GetConnection();
          try
          ...{
              GetConnection();
              SqlCommand cmd = new SqlCommand(Sql, connection);
              byte[] blob = (byte[])cmd.ExecuteScalar();
              if (blob.Length > 0)
              ...{
                  MemoryStream stream = new MemoryStream(blob,true);
                  stream.Write(blob, 0, blob.Length);
                  bm = new Bitmap(stream);
                  stream.Close();
                  CloseConnection();
              }
              return bm;

          }
          catch(Exception e)
          ...{
              return null;
          }
      }

    }
}