sqlhelper 匯總



    #region 数据库连接
    private static SqlConnection conn;
    public static SqlConnection Conn
    {
        get
        {
            if (conn == null)
            {
                conn = new SqlConnection(ConfigurationManager.ConnectionStrings["costr"].ConnectionString);
                conn.Open();
            }
            else if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            else if (conn.State == ConnectionState.Broken)
            {
                conn.Close();
                conn.Open();
            }
            return conn;
        }
    }
    #endregion

    #region 執行sql語句 返回SqlDataReader
    /// <summary>  
    /// 執行sql語句返回SqlDataReader (使用该方法切记要手工关闭SqlDataReader和连接)
    /// </summary> 
    /// <param name="sql">需要執行的sql語句</param>
    public static SqlDataReader GetReader(string StrSql)
    {
        SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["costr"].ConnectionString);
        SqlCommand cmd = new SqlCommand(StrSql, connection);
        try
        {
            connection.Open();
            SqlDataReader myReader = cmd.ExecuteReader();
            return myReader;
        }
        catch (Exception e)
        {
            throw e;
        }
    }
    #endregion

    #region 執行sql語句 返回DataTable
    /// <summary>  
    /// 執行sql語句返回DataTable
    /// </summary> 
    /// <param name="sql">需要執行的sql語句</param>
    public static DataTable GetTable(string StrSql)
    {
        try
        {
            DataSet ds = new DataSet();
            SqlDataAdapter sda = new SqlDataAdapter(StrSql, Conn);
            sda.Fill(ds);
            conn.Close();
            return ds.Tables[0];
        }
        catch (Exception e)
        {
            throw e;
        }
        finally
        {
            conn.Close();
        }
    }
    #endregion

    #region 執行sql語句 返回指定值
    /// <summary>  
    /// 執行sql語句返回指定字段第一行值
    /// </summary> 
    /// <param name="sql">需要執行的sql語句</param>
    /// <param name="parm">字段名稱的字符串</param>
    public static String endid(string sql, string parm)
    {
        try
        {
            string a = "0";
            DataSet ds = new DataSet();
            SqlDataAdapter sda = new SqlDataAdapter(sql, Conn);
            sda.Fill(ds);
            if (ds.Tables[0].Rows.Count > 0)
            {
                a = ds.Tables[0].Rows[0][parm].ToString();
            }
            else
            {
                a = "-1";
            }
            conn.Close();
            return a;
        }
        catch (Exception e)
        {
            throw e;
        }
        finally
        {
            conn.Close();
        }
    }
    #endregion

    #region 新增數據返回響應條數
    /// <summary>  
    /// 新增數據返回響應條數
    /// </summary> 
    /// <param name="str">需要執行的sql語句</param>
    public static int adddate(string str)
    {
        int i = 0;
        SqlCommand cmd = new SqlCommand(str, Conn);
        i = cmd.ExecuteNonQuery();
        conn.Close();
        return i;
    }
    #endregion

    #region 修改數據返回響應條數
    /// <summary>  
    /// 修改數據返回響應條數
    /// </summary> 
    /// <param name="str">需要執行的sql語句</param>
    public static int update(string str)
    {
        int i = 0;
        SqlCommand cmd = new SqlCommand(str, Conn);
        i = cmd.ExecuteNonQuery();
        conn.Close();
        return i;
    }
    #endregion

    #region 刪除數據返回響應條數
    /// <summary>  
    /// 刪除數據返回響應條數
    /// </summary> 
    /// <param name="str">需要執行的sql語句</param>
    public static int deletedate(string str)
    {
        int i = 0;
        SqlCommand cmd = new SqlCommand(str, Conn);
        i = cmd.ExecuteNonQuery();
        conn.Close();
        return i;
    }
    #endregion

    #region 執行不帶參數的存儲過程
    /// <summary>  
    /// 執行不帶參數的存儲過程
    /// </summary> 
    /// <param name="cmdText">存儲過程名稱</param>
    public static void Pro_FromSource(string cmdText)
    {
        SqlCommand cmd = new SqlCommand(cmdText, Conn);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.ExecuteNonQuery();
        conn.Close();
    }
    #endregion

    #region 執行帶參數的存儲過程,返回DataTable
    /// <summary>  
    /// 執行帶參數的存儲過程
    /// </summary> 
    /// <param name="cmdText">需要執行存儲過程的名稱</param>
    /// <param name="para">SqlParameter[]對象參數集合</param>
    public static DataTable StoredReturnDataTable(string cmdText, SqlParameter[] para)
    {
        try
        {
            SqlCommand cmd = new SqlCommand(cmdText, Conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddRange(para);
            cmd.ExecuteNonQuery();
            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(ds);
            conn.Close();
            return ds.Tables[0];
        }
        catch (Exception e)
        {
            throw e;
        }
        finally
        {
            conn.Close();
        }
    }
    #endregion

    #region 執行帶參數的存儲過程 返回DataSet 并回傳一個參數
    /// <summary>  
    /// 執行帶參數的存儲過程
    /// </summary> 
    /// <param name="cmdText">需要執行存儲過程的名稱</param>
    /// <param name="para">SqlParameter[]對象參數集合</param>
    /// <param name="returnName">返回參數的名稱,此參數需要包含在para裡面</param>
    /// <param name="returnValue">返回參數的值</param>
    public static DataSet StoredReturnDataSetPara(string cmdText, SqlParameter[] para, string returnName, out string returnValue)
    {
        try
        {
            SqlCommand cmd = new SqlCommand(cmdText, Conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddRange(para);
            cmd.ExecuteNonQuery();
            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(ds);
            conn.Close();
            returnValue = cmd.Parameters[returnName].Value.ToString();
            return ds;
        }
        catch (Exception e)
        {
            throw e;
        }
        finally
        {
            conn.Close();
        }
    }
    #endregion

    #region 執行帶參數的存儲過程 返回DataSet
    /// <summary>  
    /// 執行帶參數的存儲過程
    /// </summary> 
    /// <param name="cmdText">需要執行存儲過程的名稱</param>
    /// <param name="para">SqlParameter[]對象參數集合</param>
    public static DataSet StoredReturnDataSet(string cmdText, SqlParameter[] para)
    {
        try
        {
            SqlCommand cmd = new SqlCommand(cmdText, Conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddRange(para);
            cmd.ExecuteNonQuery();
            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(ds);
            conn.Close();
            return ds;
        }
        catch (Exception e)
        {
            throw e;
        }
        finally
        {
            conn.Close();
        }
    }
    #endregion

    #region 執行帶參數的存儲過程,返回SqlDataReader 
    /// <summary>  
    /// 執行帶參數的存儲過程 返回SqlDataReader 此方法不能使用dr.Read()循環輸出
    /// </summary> 
    /// <param name="cmdText">需要執行存儲過程的名稱</param>
    /// <param name="para">SqlParameter[]對象參數集合</param>
    public static SqlDataReader StoredReturnDataReader(string cmdText, SqlParameter[] para)
    {
        SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["costr"].ConnectionString);
        try
        {
            SqlCommand cmd = new SqlCommand(cmdText, Conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddRange(para);
            SqlDataReader read = cmd.ExecuteReader();
            conn.Close();
            return read;
        }
        catch (Exception e)
        {
            throw e;
        }
        finally
        {
            conn.Close();
        }
    }
    #endregion

    #region 執行帶參數的存儲過程,并返回一個參數
    /// <summary>  
    /// 執行帶參數的存儲過程,并返回一個參數
    /// </summary> 
    /// <param name="cmdText">需要執行存儲過程的名稱</param>
    /// <param name="para">SqlParameter[]對象參數集合</param>
    /// <param name="returnName">返回參數的名稱,此參數需要包含在para裡面</param>
    public static String StoredReturnPara(string cmdText, SqlParameter[] para, string returnName)
    {
        SqlCommand cmd = new SqlCommand(cmdText, Conn);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddRange(para);
        cmd.ExecuteNonQuery();
        string a = cmd.Parameters[returnName].Value.ToString();
        conn.Close();
        return a;
    }
    #endregion

    #region 執行帶參數的存儲過程不返回任何數據
    /// <summary>  
    /// 執行帶參數的存儲過程不返回任何數據
    /// </summary> 
    /// <param name="cmdText">需要執行存儲過程的名稱</param>
    /// <param name="para">SqlParameter[]對象參數集合</param>
    public static int StoredReturn(string cmdText, SqlParameter[] para)
    {
        SqlCommand cmd = new SqlCommand(cmdText, Conn);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddRange(para);
        int i=cmd.ExecuteNonQuery();
        conn.Close();
        return i;
    }
    #endregion

    #region 執行帶參數的存儲過程，返回true
    /// <summary>  
    /// 執行帶參數的存儲過程不返回任何數據
    /// </summary> 
    /// <param name="cmdText">需要執行存儲過程的名稱</param>
    /// <param name="para">SqlParameter[]對象參數集合</param>
    public static bool StoredReturn1(string cmdText, SqlParameter[] para)
    {
        SqlCommand cmd = new SqlCommand(cmdText, Conn);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddRange(para);
        cmd.ExecuteNonQuery();
        conn.Close();
        return true;
    }
    #endregion

    #region 构建 SqlCommand 对象(用来返回一个结果集，而不是一个整数值)
    /// <summary>
    /// 构建 SqlCommand 对象(用来返回一个结果集，而不是一个整数值)
    /// </summary>
    /// <param name="connection">数据库连接</param>
    /// <param name="storedProcName">存储过程名</param>
    /// <param name="parameters">存储过程参数</param>
    /// <returns>SqlCommand</returns>
    public static SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
    {
        SqlCommand command = new SqlCommand(storedProcName, connection);
        command.CommandType = CommandType.StoredProcedure;
        foreach (SqlParameter parameter in parameters)
        {
            if (parameter != null)
            {
                // 检查未分配值的输出参数,将其分配以DBNull.Value.
                if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                    (parameter.Value == null))
                {
                    parameter.Value = DBNull.Value;
                }
                command.Parameters.Add(parameter);
            }
        }

        return command;
    }
    #endregion

    #region 使用事務執行sql語句
    /// <summary>  
    /// 使用事務執行sql語句,返回string字符串
    /// </summary> 
    /// <param name="data">JArray數據</param>
    public static String TransactionSql(JArray data)
    {
        SqlTransaction trans = Conn.BeginTransaction();//創建
        string str = "";
        try
        {
            if (data.Count > 0)
            {
                foreach (JObject jo in data)
                {
                    string sql = (string)jo["sql"];
                    SqlCommand cmd = new SqlCommand(sql, Conn, trans);
                    int mun=cmd.ExecuteNonQuery();
                    str += mun.ToString()+",";
                }
            }
            trans.Commit();//提交事務
        }
        catch (Exception err)
        {
            trans.Rollback();// 發生異常,回事務
            str= err.Message;
        }
        finally
        {
            conn.Close();
            trans.Dispose();
        }
        return str;
    }
    #endregion

    #region 使用事務執行sql語句二
    /// <summary>  
    /// 使用事務執行sql語句,返回bool值
    /// </summary> 
    /// <param name="data">JArray數據</param>
    public static bool TransactionSqltf(JArray data)
    {
        bool tf = false;
        SqlTransaction trans = Conn.BeginTransaction();//創建
        try
        {
            if (data.Count > 0)
            {
                foreach (JObject jo in data)
                {
                    string sql = (string)jo["sql"];
                    SqlCommand cmd = new SqlCommand(sql, Conn, trans);
                    int mun = cmd.ExecuteNonQuery();
                }
            }
            trans.Commit();//提交事務
            tf = true;
        }
        catch (Exception)
        {
            trans.Rollback();// 發生異常,回事務
            tf = false;
        }
        finally
        {
            conn.Close();
            trans.Dispose();
        }
        return tf;
    }
    #endregion

    #region 返回JObject類型數據
    /// <summary>  
    /// 以JObject格式輸出數據
    /// </summary> 
    /// <param name="success">為ture則輸出數據,false則以message為提示信息</param>
    /// <param name="total">數據總條數</param>
    /// <param name="message">錯誤的提示信息</param>
    /// <param name="data">需要傳給前臺的數據</param>
    public static JObject WriteJObjectResult(bool success, int total, string message, JArray data)
    {
        return new JObject
        {
            new JProperty("success",success),
            new JProperty("total",total),
            new JProperty("Msg",message),
            new JProperty("data",data)
        };
    }
    #endregion

    #region 返回JObject類型單條數據
    /// <summary>  
    /// 以JObject格式輸出單條數據 用以填充form表單
    /// </summary> 
    /// <param name="success">為ture則輸出數據,false則以message為提示信息</param>
    /// <param name="total">數據總條數</param>
    /// <param name="message">錯誤的提示信息</param>
    /// <param name="data">需要傳給前臺的數據</param>
    public static JObject WriteJObjectForm(bool success, int total, string message, JObject data)
    {
        return new JObject
        {
            new JProperty("success",success),
            new JProperty("total",total),
            new JProperty("Msg",message),
            new JProperty("data",data)
        };
    }
    #endregion

    #region 使用流 導出Datatable數據到Excel
    /// <summary>  
    /// 導出Datatable數據到Excel 
    /// </summary> 
    /// <param name="dt">要导出的DataTable</param>
    /// <param name="FileName">默认文件名带.xls</param>
    public static void DataTableExcel(System.Data.DataTable dt, string FileName)
    {
        StringWriter stringWriter = new StringWriter();
        HtmlTextWriter htmlWriter = new HtmlTextWriter(stringWriter);
        DataGrid excel = new DataGrid();
        TableItemStyle headerStyle = new TableItemStyle();
        headerStyle.BackColor = System.Drawing.Color.LightGray;
        headerStyle.Font.Bold = true;
        headerStyle.HorizontalAlign = HorizontalAlign.Center;
        excel.HeaderStyle.MergeWith(headerStyle);
        excel.GridLines = GridLines.Both;
        excel.HeaderStyle.Font.Bold = true;
        excel.ItemStyle.Wrap = false;//Excel單元格內容是否自動換行
        excel.DataSource = dt.DefaultView; //输出DataTable的内容
        excel.DataBind();
        excel.RenderControl(htmlWriter);
        HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
        HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=" + System.Web.HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8) + "");
        HttpContext.Current.Response.Charset = "UTF-8";
        HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;
        HttpContext.Current.Response.Write(stringWriter.ToString());
        HttpContext.Current.Response.End();
    }
    #endregion

    #region 使用Excel组件 導出Datatable數據到Excel
    /// <summary>  
    /// 导出Excel 
    /// </summary> 
    /// <param name="dt">要导出的DataTable</param>
    /// <param name="filepath">默认文件名带.xls</param>
    /// <returns>返回是原因</returns>
    public static string ExportExcel(System.Data.DataTable dt, string filepath)
    {
        string str = "";
        try
        {
            if (dt == null)
            {
                str = "{'success':false,'msg':'或許此條件下無數據'}";
            }
            else
            {
                Excel.Application xlApp = new Excel.Application();
                if (xlApp == null)
                {
                    str = "{'success':false,'msg':'无法创对象,可能您的机子未安装Excel'}";
                }
                else
                {
                    Excel.Workbooks workbooks = xlApp.Workbooks;
                    Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
                    Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];//取得sheet1   
                    Excel.Range range = null;
                    long totalCount = dt.Rows.Count;
                    long rowRead = 0;
                    float percent = 0;
                    //写入标题  
                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;
                        range = (Excel.Range)worksheet.Cells[1, i + 1];
                        range.Interior.ColorIndex = 15;//背景颜色      
                        range.Font.Bold = true;//粗体    
                        range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;//居中  
                        //加边框          
                        range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null);
                        range.EntireColumn.AutoFit();//自动调整列宽     
                        //r1.EntireRow.AutoFit();//自动调整行高   
                    }
                    //写入内容     
                    for (int r = 0; r < dt.Rows.Count; r++)
                    {
                        for (int i = 0; i < dt.Columns.Count; i++)
                        {
                            worksheet.Cells[r + 2, i + 1] = dt.Rows[r][i];
                            range = (Excel.Range)worksheet.Cells[r + 2, i + 1];
                            range.Font.Size = 10;//字体大小          
                            //加边框           
                            range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null);
                            range.EntireColumn.AutoFit();//自动调整列宽      
                        }
                        rowRead++;
                        percent = ((float)(100 * rowRead)) / totalCount;
                    }
                    range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight = Excel.XlBorderWeight.xlThin;
                    workbook.Saved = false;
                    workbook.SaveCopyAs(System.Web.HttpRuntime.AppDomainAppPath + "FileManager\\DownLoad\\" + filepath + "");
                    xlApp.Quit();
                    GC.Collect();//强行销毁
                    bool abc = ResponseFile(HttpContext.Current.Request, HttpContext.Current.Response, System.Web.HttpUtility.UrlEncode(filepath, System.Text.Encoding.UTF8), System.Web.HttpRuntime.AppDomainAppPath + "FileManager\\DownLoad\\" + filepath + "", 2048000);
                    if (abc)
                    {
                        str = "{'success':true,'msg':'完成下載'}";
                    }
                    else
                    {
                        str = "{'success':false,'msg':'連接斷開,請檢查網絡連接'}";
                    }
                }
            }
        }
        catch (Exception ex)
        {
            str = "{'success':false,'msg':'" + ex.Message + "'}";
        }
        return str;
    }
    /// <summary>
    /// 输出硬盘文件，提供下载
    /// </summary>
    /// <param name="_Request">Page.Request对象</param>
    /// <param name="_Response">Page.Response对象</param>
    /// <param name="_fileName">下载文件名</param>
    /// <param name="_fullPath">带文件名下载路径</param>
    /// <param name="_speed">每秒允许下载的字节数</param>
    /// <returns>返回是否成功</returns>
    public static bool ResponseFile(HttpRequest _Request, HttpResponse _Response, string _fileName, string _fullPath, long _speed)
    {
        try
        {
            FileStream myFile = new FileStream(_fullPath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
            BinaryReader br = new BinaryReader(myFile);
            try
            {
                _Response.AddHeader("Accept-Ranges", "bytes");
                _Response.Buffer = false;
                long fileLength = myFile.Length;
                long startBytes = 0;

                int pack = 10240; //10K bytes
                //int sleep = 200;   //每秒5次   即5*10K bytes每秒
                int sleep = (int)Math.Floor((decimal)1000 * pack / _speed) + 1;
                if (_Request.Headers["Range"] != null)
                {
                    _Response.StatusCode = 206;
                    string[] range = _Request.Headers["Range"].Split(new char[] { '=', '-' });
                    startBytes = Convert.ToInt64(range[1]);
                }
                _Response.AddHeader("Content-Length", (fileLength - startBytes).ToString());
                if (startBytes != 0)
                {
                    _Response.AddHeader("Content-Range", string.Format(" bytes {0}-{1}/{2}", startBytes, fileLength - 1, fileLength));
                }
                _Response.AddHeader("Connection", "Keep-Alive");
                _Response.ContentType = "application/octet-stream";
                _Response.Charset = "UTF-8";
                _Response.ContentEncoding = Encoding.GetEncoding("UTF-8");
                _Response.AddHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(_fileName, System.Text.Encoding.UTF8));

                br.BaseStream.Seek(startBytes, SeekOrigin.Begin);
                int maxCount = (int)Math.Floor((decimal)(fileLength - startBytes) / pack) + 1;

                for (int i = 0; i < maxCount; i++)
                {
                    if (_Response.IsClientConnected)
                    {
                        _Response.BinaryWrite(br.ReadBytes(pack));
                        Thread.Sleep(sleep);
                    }
                    else
                    {
                        i = maxCount;
                    }
                }
                _Response.End();
            }
            catch
            {
                return false;
            }
            finally
            {
                br.Close();
                myFile.Close();
            }
        }
        catch
        {
            return false;
        }
        return true;
    }
    #endregion

    #region DataTable導入數據庫
    /// <summary>  
    /// DataTable導入數據庫
    /// </summary> 
    /// <param name="dt">要导入的DataTable</param>
    /// <param name="TableName">目标数据库的表名</param>
    public static void Bulk(DataTable dt, string TableName)
    {
        //声明SqlBulkCopy ,using释放非托管资源
        using (SqlBulkCopy sqlBC = new SqlBulkCopy(Conn))
        {
            //一次批量的插入的数据量
            sqlBC.BatchSize = 1000;
            //超时之前操作完成所允许的秒数，如果超时则事务不会提交 ，数据将回滚，所有已复制的行都会从目标表中移除
            sqlBC.BulkCopyTimeout = 60;

            //設定 NotifyAfter 属性，以便在每插入10000 条数据时，呼叫相应事件。
            sqlBC.NotifyAfter = 10000;
            // sqlBC.SqlRowsCopied += new SqlRowsCopiedEventHandler(OnSqlRowsCopied);

            //设置要批量写入的表
            sqlBC.DestinationTableName = TableName;

            //批量写入
            sqlBC.WriteToServer(dt);//将数据拷贝到目标数据库，在使用WriteToServer之前必须指定DestinationTablename(目标数据库的表名)
        }
        conn.Close();
    }
    #endregion

    #region DataSet獲取Excel的數據
    /// <summary>  
    /// DataSet獲取Excel的數據
    /// </summary> 
    /// <param name="FilePath">FilePath指Excel在本服務器的路徑</param>
    public static DataSet GetDataSet(string FilePath)//
    {
        string OledbConnectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + FilePath + "';Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'");
        OleDbConnection conn = new OleDbConnection(OledbConnectionString);
        ArrayList SheetNameList = new ArrayList();
        try
        {
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            DataTable dtExcelSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
            string SheetName = "";
            for (int i = 0; i < dtExcelSchema.Rows.Count; i++)
            {
                SheetName = dtExcelSchema.Rows[i]["TABLE_NAME"].ToString();
                SheetNameList.Add(SheetName);
            }

        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            conn.Close();
        }
        DataSet dsExcel = new DataSet();

        try
        {
            string strSql = "";

            for (int i = 0; i < SheetNameList.Count; i++)
            {
                strSql = "select* from [" + SheetNameList[i] + "]";
                OleDbDataAdapter oleExcelDataAdapter = new OleDbDataAdapter(strSql, conn);
                DataTable dtExcel = new DataTable((string)SheetNameList[i]);
                oleExcelDataAdapter.Fill(dtExcel);
                dsExcel.Tables.Add(dtExcel);
            }
            return dsExcel;
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }
    #endregion