lovenjoy的专栏

Choose What you like,Like what you Choose!

用户操作
[即时聊天] [发私信] [加为好友]
lovenjoyID：lovenjoy
共3153次访问，排名2万外好友0人，关注者0人
lovenjoy的文章
原创 31 篇
翻译 0 篇
转载 2 篇
评论 1 篇
最近评论
mldstk：wow power leveling
文章分类
    收藏
      相册
      图片
      存档
      软件项目交易
      订阅我的博客
      XML聚合  FeedSky
      订阅到鲜果
      订阅到Google
      订阅到抓虾
      订阅到BlogLines
      订阅到Yahoo
      订阅到GouGou
      订阅到飞鸽
      订阅到Rojo
      订阅到newsgator
      订阅到netvibes

      原创 SqlServerHelper.cs数据库操作收藏

      新一篇: 养老保险 | 旧一篇: 向DataSet中追加列
      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)