数据访问基础类(基于MYSQL)

     ///   <summary>
     ///  Copyright (C) 2006-2010 Kolee
     ///  数据访问基础类(基于MYSQL)
    ///   </summary>
     public   class  MySqlHelper2
    {
         public  MySqlHelper2()
        {
        }

         #region  公用方法

         public   static   int  GetMaxId( string  fieldName,  string  tableName,  string  connStr)
        {
             string  strsql  =   " select max( "   +  fieldName  +   " )+1 from  "   +  tableName;
             object  obj  =  GetSingle(strsql, connStr);
             if  (obj  ==   null )
                 return   1 ;
             return   int .Parse(obj.ToString());
        }

         public   static   bool  Exists( string  sqlStr,  string  connStr,  params  MySqlParameter[] cmdParms)
        {
             object  obj  =  GetSingle(sqlStr, connStr, cmdParms);
             int  cmdresult;
             if  ((Equals(obj,  null ))  ||  (Equals(obj, DBNull.Value)))
            {
                cmdresult  =   0 ;
            }
             else
            {
                cmdresult  =   int .Parse(obj.ToString());
            }
             if  (cmdresult  ==   0 )
                 return   false ;
             return   true ;
        }

         #endregion

         #region   执行简单SQL语句

         ///   <summary>
         ///  执行SQL语句，返回影响的记录数
         ///   </summary>
         ///   <param name="sqlStr"> SQL语句 </param>
         ///   <param name="connStr"></param>
         ///   <returns> 影响的记录数 </returns>
         public   static   int  ExecuteSql( string  sqlStr,  string  connStr)
        {
             using  (var connection  =   new  MySqlConnection(connStr))
            {
                 using  (var cmd  =   new  MySqlCommand(sqlStr, connection))
                {
                     try
                    {
                        connection.Open();
                         int  rows  =  cmd.ExecuteNonQuery();
                         return  rows;
                    }
                     catch  (Exception E)
                    {
                        connection.Close();
                         throw  E;
                    }
                }
            }
        }

         ///   <summary>
         ///  执行多条SQL语句，实现数据库事务。
         ///   </summary>
         ///   <param name="sqlStringList"> 多条SQL语句 </param>         
         ///   <param name="connStr"></param>
         public   static   void  ExecuteSqlTran(ArrayList sqlStringList,  string  connStr)
        {
             using  (var conn  =   new  MySqlConnection(connStr))
            {
                conn.Open();
                var cmd  =   new  MySqlCommand();
                cmd.Connection  =  conn;
                MySqlTransaction tx  =  conn.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();
                }
                 catch  (Exception e)
                {
                    tx.Rollback();
                     throw  e;
                }
            }
        }
         ///   <summary>
         ///  执行带一个存储过程参数的的SQL语句。
         ///   </summary>
         ///   <param name="sqlStr"> SQL语句 </param>
         ///   <param name="content"> 参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加 </param>
         ///   <param name="connStr"></param>
         ///   <returns> 影响的记录数 </returns>
         public   static   int  ExecuteSql( string  sqlStr,  string  content,  string  connStr)
        {
             using  (var connection  =   new  MySqlConnection(connStr))
            {
                var cmd  =   new  MySqlCommand(sqlStr, connection);
                var myParameter  =   new  System.Data.SqlClient.SqlParameter( " @content " , SqlDbType.NText) { Value  =  content };
                cmd.Parameters.Add(myParameter);
                 try
                {
                    connection.Open();
                     int  rows  =  cmd.ExecuteNonQuery();
                     return  rows;
                }
                 catch  (Exception E)
                {
                     throw  E;
                }
                 finally
                {
                    cmd.Dispose();
                    connection.Close();
                }
            }
        }
         ///   <summary>
         ///  向数据库里插入图像格式的字段(和上面情况类似的另一种实例)
         ///   </summary>
         ///   <param name="sqlStr"> SQL语句 </param>
         ///   <param name="fs"> 图像字节,数据库的字段类型为image的情况 </param>
         ///   <param name="connStr"></param>
         ///   <returns> 影响的记录数 </returns>
         public   static   int  ExecuteSqlInsertImg( string  sqlStr,  byte [] fs,  string  connStr)
        {
             using  (var connection  =   new  MySqlConnection(connStr))
            {
                var cmd  =   new  MySqlCommand(sqlStr, connection);
                var myParameter  =   new  System.Data.SqlClient.SqlParameter( " @fs " , SqlDbType.Image) { Value  =  fs };
                cmd.Parameters.Add(myParameter);
                 try
                {
                    connection.Open();
                     int  rows  =  cmd.ExecuteNonQuery();
                     return  rows;
                }
                 catch  (Exception E)
                {
                     throw  E;
                }
                 finally
                {
                    cmd.Dispose();
                    connection.Close();
                }
            }
        }

         ///   <summary>
         ///  执行一条计算查询结果语句，返回查询结果（object）。
         ///   </summary>
         ///   <param name="sqlStr"> 计算查询结果语句 </param>
         ///   <param name="connStr"></param>
         ///   <returns> 查询结果（object） </returns>
         public   static   object  GetSingle( string  sqlStr,  string  connStr)
        {
             using  (var connection  =   new  MySqlConnection(connStr))
            {
                 using  (var cmd  =   new  MySqlCommand(sqlStr, connection))
                {
                     try
                    {
                        connection.Open();
                         object  obj  =  cmd.ExecuteScalar();
                         if  ((Equals(obj,  null ))  ||  (Equals(obj, DBNull.Value)))
                        {
                             return   null ;
                        }
                         return  obj;
                    }
                     catch  (Exception e)
                    {
                        connection.Close();
                         throw  e;
                    }
                }
            }
        }
         ///   <summary>
         ///  执行查询语句，返回SqlDataReader
         ///   </summary>
         ///   <param name="sqlStr"> 查询语句 </param>
         ///   <param name="sqlStr"></param>
         ///   <param name="connStr"></param>
         ///   <returns> SqlDataReader </returns>
         public   static  MySqlDataReader ExecuteReader( string  sqlStr,  string  connStr)
        {
            var connection  =   new  MySqlConnection(connStr);
            var cmd  =   new  MySqlCommand(sqlStr, connection);
             try
            {
                connection.Open();
                MySqlDataReader myReader  =  cmd.ExecuteReader(CommandBehavior.CloseConnection);
                 return  myReader;
            }
             catch  (Exception e)
            {
                 throw  e;
            }
             finally
            {
                connection.Close();
            }
        }
         ///   <summary>
         ///  执行查询语句，返回DataSet
         ///   </summary>
         ///   <param name="sqlString"> 查询语句 </param>
         ///   <param name="connStr"></param>
         ///   <returns> DataSet </returns>
         public   static  DataSet Query( string  sqlString,  string  connStr)
        {
             using  (var connection  =   new  MySqlConnection(connStr))
            {
                var ds  =   new  DataSet();
                 try
                {
                    connection.Open();
                    var command  =   new  MySqlDataAdapter(sqlString, connection);
                    command.Fill(ds,  " ds " );
                }
                 catch  (Exception ex)
                {
                     throw  ex;
                }
                 return  ds;
            }
        }

         #endregion

         #region  执行带参数的SQL语句
         ///   <summary>
         ///  执行SQL语句，返回影响的记录数
         ///   </summary>
         ///   <param name="sqlStr"> SQL语句 </param>
         ///   <param name="cmdParms"></param>
         ///   <param name="connStr"></param>
         ///   <returns> 影响的记录数 </returns>
         public   static   int  ExecuteSql( string  sqlStr,  string  connStr,  params  MySqlParameter[] cmdParms)
        {
             using  (var connection  =   new  MySqlConnection(connStr))
            {
                 using  (var cmd  =   new  MySqlCommand())
                {
                     try
                    {
                        PrepareCommand(cmd, connection,  null , sqlStr, cmdParms);
                         int  rows  =  cmd.ExecuteNonQuery();
                        cmd.Parameters.Clear();
                         return  rows;
                    }
                     catch  (Exception e)
                    {
                         throw  e;
                    }
                }
            }
        }

         ///   <summary>
         ///  执行多条SQL语句，实现数据库事务。
         ///   </summary>
         ///   <param name="sqlStrList"> SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]） </param>
         ///   <param name="connStr"></param>
         public   static   void  ExecuteSqlTran(Hashtable sqlStrList,  string  connStr)
        {
             using  (var conn  =   new  MySqlConnection(connStr))
            {
                conn.Open();
                 using  (MySqlTransaction trans  =  conn.BeginTransaction())
                {
                    var cmd  =   new  MySqlCommand();
                     try
                    {
                         // 循环
                         foreach  (DictionaryEntry myDe  in  sqlStrList)
                        {
                             string  cmdText  =  myDe.Key.ToString();
                            var cmdParms  =  (MySqlParameter[])myDe.Value;
                            PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
                             int  val  =  cmd.ExecuteNonQuery();
                            cmd.Parameters.Clear();

                            trans.Commit();
                        }
                    }
                     catch  (Exception ex)
                    {
                        trans.Rollback();
                         throw  ex;
                    }
                }
            }
        }

         ///   <summary>
         ///  执行一条计算查询结果语句，返回查询结果（object）。
         ///   </summary>
         ///   <param name="sqlStr"> 计算查询结果语句 </param>
         ///   <param name="connStr"></param>
         ///   <param name="cmdParms"></param>
         ///   <returns> 查询结果（object） </returns>
         public   static   object  GetSingle( string  sqlStr,  string  connStr,  params  MySqlParameter[] cmdParms)
        {
             using  (var connection  =   new  MySqlConnection(connStr))
            {
                 using  (var cmd  =   new  MySqlCommand())
                {
                     try
                    {
                        PrepareCommand(cmd, connection,  null , sqlStr, cmdParms);
                         object  obj  =  cmd.ExecuteScalar();
                        cmd.Parameters.Clear();
                         if  ((Equals(obj,  null ))  ||  (Equals(obj, DBNull.Value)))
                        {
                             return   null ;
                        }
                         return  obj;
                    }
                     catch  (Exception e)
                    {
                         throw  e;
                    }
                }
            }
        }

         ///   <summary>
         ///  执行查询语句，返回SqlDataReader
         ///   </summary>
         ///   <param name="sqlStr"> 查询语句 </param>
         ///   <param name="connStr"></param>
         ///   <param name="cmdParms"></param>
         ///   <returns> SqlDataReader </returns>
         public   static  MySqlDataReader ExecuteReader( string  sqlStr,  string  connStr,  params  MySqlParameter[] cmdParms)
        {
            var connection  =   new  MySqlConnection(connStr);
            var cmd  =   new  MySqlCommand();
             try
            {
                PrepareCommand(cmd, connection,  null , sqlStr, cmdParms);
                MySqlDataReader myReader  =  cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                 return  myReader;
            }
             catch  (Exception e)
            {
                 throw  e;
            }
             finally
            {
                connection.Close();
            }

        }

         ///   <summary>
         ///  执行查询语句，返回DataSet
         ///   </summary>
         ///   <param name="sqlStr"> 查询语句 </param>
         ///   <param name="connStr"></param>
         ///   <param name="cmdParms"></param>
         ///   <returns> DataSet </returns>
         public   static  DataSet Query( string  sqlStr,  string  connStr,  params  MySqlParameter[] cmdParms)
        {
             using  (var connection  =   new  MySqlConnection(connStr))
            {
                var cmd  =   new  MySqlCommand();
                PrepareCommand(cmd, connection,  null , sqlStr, cmdParms);
                 using  (var da  =   new  MySqlDataAdapter(cmd))
                {
                    var ds  =   new  DataSet();
                     try
                    {
                        da.Fill(ds,  " ds " );
                        cmd.Parameters.Clear();
                    }
                     catch  (Exception ex)
                    {
                         throw  ex;
                    }
                     return  ds;
                }
            }
        }

         private   static   void  PrepareCommand(MySqlCommand cmd, MySqlConnection conn, MySqlTransaction trans,  string  cmdText, MySqlParameter[] cmdParms)
        {
             if  (conn.State  !=  ConnectionState.Open)
                conn.Open();
            cmd.Connection  =  conn;
            cmd.CommandText  =  cmdText;
             if  (trans  !=   null )
                cmd.Transaction  =  trans;
            cmd.CommandType  =  CommandType.Text; // cmdType;
             if  (cmdParms  !=   null )
            {
                 foreach  (MySqlParameter parm  in  cmdParms)
                    cmd.Parameters.Add(parm);
            }
        }

         #endregion

         #region  存储过程操作

         ///   <summary>
         ///  执行存储过程
         ///   </summary>
         ///   <param name="storedProcName"> 存储过程名 </param>
         ///   <param name="connStr"></param>
         ///   <param name="parameters"> 存储过程参数 </param>
         ///   <returns> SqlDataReader </returns>
         public   static  MySqlDataReader RunProcedure( string  storedProcName,  string  connStr, IDataParameter[] parameters)
        {
            var connection  =   new  MySqlConnection(connStr);
            MySqlDataReader returnReader;
            connection.Open();
            MySqlCommand command  =  BuildQueryCommand(connection, storedProcName, parameters);
            command.CommandType  =  CommandType.StoredProcedure;
            returnReader  =  command.ExecuteReader(CommandBehavior.CloseConnection);
             return  returnReader;
        }


         ///   <summary>
         ///  执行存储过程
         ///   </summary>
         ///   <param name="storedProcName"> 存储过程名 </param>
         ///   <param name="parameters"> 存储过程参数 </param>
         ///   <param name="tableName"> DataSet结果中的表名 </param>
         ///   <returns> DataSet </returns>
         public   static  DataSet RunProcedure( string  storedProcName,  string  connStr, IDataParameter[] parameters,  string  tableName)
        {
             using  (var connection  =   new  MySqlConnection(connStr))
            {
                var dataSet  =   new  DataSet();
                connection.Open();
                var sqlDa  =   new  MySqlDataAdapter();
                sqlDa.SelectCommand  =  BuildQueryCommand(connection, storedProcName, parameters);
                sqlDa.Fill(dataSet, tableName);
                connection.Close();
                 return  dataSet;
            }
        }


         ///   <summary>
         ///  构建 SqlCommand 对象(用来返回一个结果集，而不是一个整数值)
         ///   </summary>
         ///   <param name="connection"> 数据库连接 </param>
         ///   <param name="storedProcName"> 存储过程名 </param>
         ///   <param name="parameters"> 存储过程参数 </param>
         ///   <returns> SqlCommand </returns>
         private   static  MySqlCommand BuildQueryCommand(MySqlConnection connection,  string  storedProcName, IEnumerable < IDataParameter >  parameters)
        {
            var command  =   new  MySqlCommand(storedProcName, connection) {CommandType  =  CommandType.StoredProcedure};
             foreach  (MySqlParameter parameter  in  parameters)
            {
                command.Parameters.Add(parameter);
            }
             return  command;
        }

         ///   <summary>
         ///  执行存储过程，返回影响的行数        
         ///   </summary>
         ///   <param name="storedProcName"> 存储过程名 </param>
         ///   <param name="connStr"></param>
         ///   <param name="parameters"> 存储过程参数 </param>
         ///   <param name="rowsAffected"> 影响的行数 </param>
         ///   <returns></returns>
         public   static   int  RunProcedure( string  storedProcName,  string  connStr, IDataParameter[] parameters,  out   int  rowsAffected)
        {
             using  (var connection  =   new  MySqlConnection(connStr))
            {
                 int  result;
                connection.Open();
                MySqlCommand command  =  BuildIntCommand(connection, storedProcName, parameters);
                rowsAffected  =  command.ExecuteNonQuery();
                result  =  ( int )command.Parameters[ " ReturnValue " ].Value;
                 // Connection.Close();
                 return  result;
            }
        }

         ///   <summary>
         ///  创建 SqlCommand 对象实例(用来返回一个整数值)    
         ///   </summary>
         ///   <param name="connection"></param>
         ///   <param name="storedProcName"> 存储过程名 </param>
         ///   <param name="parameters"> 存储过程参数 </param>
         ///   <returns> SqlCommand 对象实例 </returns>
         private   static  MySqlCommand BuildIntCommand(MySqlConnection connection,  string  storedProcName, IEnumerable < IDataParameter >  parameters)
        {
            MySqlCommand command  =  BuildQueryCommand(connection, storedProcName, parameters);
            command.Parameters.Add( new  MySqlParameter( " ReturnValue " ,
                    MySqlDbType.Int32,  4 , ParameterDirection.ReturnValue,
                     false ,  0 ,  0 ,  string .Empty, DataRowVersion.Default,  null ));
             return  command;
        }
         #endregion

    }