Simple Data Layer Common Class

//************************************************************************
/**//// 改版内容             
/// Copyright (C) 2008 All Rights Reserved
//************************************************************************
namespace Common
{
    using System;
    using System.IO;
    using System.Data;
    using System.Data.Common;
    using System.Data.Sql;
    using System.Data.SqlClient;
    using System.Configuration;
    using System.Collections; 

    public class ClsDBOPro
    {
        private field#region private field

        private string strConn = ConfigurationManager.AppSettings["connStr"];
        #endregion

        public method#region public method
        public Boolean QueryWithDataSet(string strSql,
                                        string strTableName,
                                        ref DataSet dataSet)
        {
            using (SqlConnection conn = new SqlConnection(strConn))
            {
                Boolean blnFlag = false;

                SqlDataAdapter sda = new SqlDataAdapter();
                SqlCommand comm = new SqlCommand();

                try
                {
                    conn.Open();

                    comm.Connection = conn;
                    comm.CommandType = CommandType.Text;
                    comm.CommandText = strSql;

                    sda.SelectCommand = comm;

                    sda.Fill(dataSet, strTableName);

                    blnFlag = true;

                    return blnFlag;
                }
                catch (SqlException ex)
                {
                    Log.WriteLog(ex.Message.ToString());

                    blnFlag = false;
                    return blnFlag;
                }
                finally
                {
                    sda.Dispose();
                    comm.Dispose();
                }
            }
        }

        public Boolean QueryWithReader(string strSql,
                                       ref ArrayList recordHolder)
        {
            using (SqlConnection conn = new SqlConnection(strConn))
            {
                Boolean blnFlag = false;

                SqlCommand comm = new SqlCommand();
                SqlDataReader dr = null;

                try
                {
                    conn.Open();

                    comm.Connection = conn;
                    comm.CommandType = CommandType.Text;
                    comm.CommandText = strSql;

                    dr = comm.ExecuteReader(CommandBehavior.CloseConnection);

                    if (dr.HasRows)
                    {
                        foreach (DbDataRecord rec in dr)
                        {
                            recordHolder.Add(rec);
                        }
                    }

                    blnFlag = true;

                    return blnFlag;
                }
                catch (SqlException ex)
                {
                    Log.WriteLog(ex.Message.ToString());

                    blnFlag = false;
                    return blnFlag;
                }
                finally
                {
                    comm.Dispose();
                }
            }
        }

        public Boolean ExecuteSQL(ArrayList arrSql)
        {
            using (SqlConnection conn = new SqlConnection(strConn))
            {
                SqlTransaction st =null;
                SqlCommand comm = null;

                try
                {
                    conn.Open();

                    st = conn.BeginTransaction();

                    comm = new SqlCommand();
                    comm.Connection = conn;
                    comm.CommandType = CommandType.Text;
                    comm.Transaction = st;

                    foreach (string strSql in arrSql)
                    {
                        comm.CommandText = strSql;
                        comm.ExecuteNonQuery();
                    }

                    st.Commit();

                    return true;
                }
                catch (SqlException ex)
                {
                    st.Rollback();

                    Log.WriteLog(ex.Message.ToString());

                    return false;
                }
                finally
                {
                    st.Dispose();
                    comm.Dispose();
                }
            }
        }

        public Boolean ExecuteSQL(ArrayList arrSql, string strFile, bool blnflag)
        {
            using (SqlConnection conn = new SqlConnection(strConn))
            {
                SqlTransaction st = null;
                SqlCommand comm = null;

                try
                {
                    conn.Open();

                    st = conn.BeginTransaction();

                    comm = new SqlCommand();
                    comm.Connection = conn;
                    comm.CommandType = CommandType.Text;
                    comm.Transaction = st;

                    foreach (string strSql in arrSql)
                    {
                        comm.CommandText = strSql;
                        comm.ExecuteNonQuery();
                    }

                    if (strFile != "" && blnflag == true)
                    {
                        File.Delete(strFile);
                    }

                    st.Commit();

                    return true;
                }
                catch (Exception ex)
                {
                    st.Rollback();

                    Log.WriteLog(ex.Message.ToString());

                    return false;
                }
                finally
                {
                    st.Dispose();
                    comm.Dispose();
                }
            }
        }

        public int ExecuteWithStoredProcedure(string spName, params SqlParameter[] cmdParams)
        {
            using (SqlConnection conn = new SqlConnection(strConn))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    try
                    {
                        conn.Open();
                        cmd.Connection = conn;
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.CommandText = spName;

                        if (cmdParams != null)
                        {
                            foreach (SqlParameter param in cmdParams)
                            {
                                cmd.Parameters.Add(param);
                            }
                        }

                        cmd.ExecuteNonQuery();

                        return Convert.ToInt32(cmd.Parameters["@output"].Value);
                    }
                    catch (SqlException ex)
                    {
                        Log.WriteLog(ex.Message.ToString());
                        return -10;
                    }
                    finally
                    {
                        cmd.Dispose();
                    }
                }
            }
        }

        public bool getMiuntePateData(string strSql, int intPage,
                                      int intPerPage, string strId,
                                      string strSort, string strOrder,
                                      ref string strRowCount, ref DataSet myDataset)
        {
            using (SqlConnection conn = new SqlConnection(strConn))
            {
                try
                {
                    SqlDataAdapter myAdapter = new SqlDataAdapter("Minutepage", conn);
                    myAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;
                    myAdapter.SelectCommand.Parameters.Add
                        (new SqlParameter("@Sql", SqlDbType.NVarChar, 4000));
                    myAdapter.SelectCommand.Parameters["@Sql"].Value = strSql;
                    myAdapter.SelectCommand.Parameters.Add
                        (new SqlParameter("@Page", SqlDbType.Int));
                    myAdapter.SelectCommand.Parameters["@Page"].Value = intPage;
                    myAdapter.SelectCommand.Parameters.Add
                        (new SqlParameter("@Perpage", SqlDbType.Int));
                    myAdapter.SelectCommand.Parameters["@Perpage"].Value = intPerPage;
                    myAdapter.SelectCommand.Parameters.Add
                        (new SqlParameter("@DistinctId", SqlDbType.NVarChar, 100));
                    myAdapter.SelectCommand.Parameters["@DistinctId"].Value = strId;
                    myAdapter.SelectCommand.Parameters.Add
                        (new SqlParameter("@Sort", SqlDbType.NVarChar, 100));
                    myAdapter.SelectCommand.Parameters["@Sort"].Value = strSort;
                    myAdapter.SelectCommand.Parameters.Add
                        (new SqlParameter("@Order", SqlDbType.NVarChar, 5));
                    myAdapter.SelectCommand.Parameters["@Order"].Value = strOrder;
                    myAdapter.SelectCommand.Parameters.Add
                        (new SqlParameter("@Return", SqlDbType.Int));
                    myAdapter.SelectCommand.Parameters["@Return"].Direction
                        = ParameterDirection.Output;
                    myAdapter.SelectCommand.Parameters.Add
                        (new SqlParameter("@Rowcount", SqlDbType.Int));
                    myAdapter.SelectCommand.Parameters["@Rowcount"].Direction
                        = ParameterDirection.Output;
                    myAdapter.Fill(myDataset);

                    string strReturn;
                    strReturn = myAdapter.SelectCommand.Parameters["@Return"].Value.ToString();
                    strRowCount = myAdapter.SelectCommand.Parameters["@Rowcount"].Value.ToString();
                    if (strReturn == "1")
                    {
                        return true;
                    }
                    else
                    {
                        return false;
                    }
                }
                catch(Exception ex)
                {
                    Log.WriteLog(ex.Message.ToString());
                    return false;
                }
            }
        }
        #endregion
    }
}