.net實現一個簡單的通用查詢數據、導出Excel的網頁

背景：臨時提供一個簡單的網頁，供其他人瀏覽資料庫（Oracel、MSSQL）的某些數據，並導出Excel。 ...

背景：
臨時提供一個簡單的網頁，供其他人瀏覽資料庫（Oracel、MSSQL）的某些數據，並導出Excel。
支持在配置文件中隨時添加或修改sql。

實現：
把sql語句等信息保存一個xml文件中，前端頁面提供一個下拉框，可以選擇不同類型的sql語句，查詢結果（暫沒分頁需要）直接綁定到GridView。

開發環境：VS2015
NuGet引入庫：NPOI、Oracle.ManagedDataAccess.Client

一、VS目錄

├─App_Code
│      DataBase.cs    ---------資料庫連接類
│      ExcelHelper.cs ---------導出Excel工具類
│      SqlEntity.cs     ---------sql語句的實體類
│      SqlEntityList.cs---------把sql.xml轉化為實體類
│
├─App_Data
│      sql.xml    ---------sql語句
│
│  rpt.aspx      ---------前端查詢頁面
│  rpt.aspx.cs  ---------
│  Web.config ---------配置資料庫連接信息

二、代碼
1、Web.config

  <connectionStrings>
    <add name="OracleString" connectionString="Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=testdb)));Persist Security Info=True;User ID=dev;Password=123456" providerName="Oracle.ManagedDataAccess.Client"/>
    <add name="SqlServerString" connectionString="user id=sa; password=123456; database=test; server=localhost" providerName="System.Data.SqlClient"/>
  </connectionStrings>

資料庫連接字元串

2、DataBase.cs

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.Configuration;

/// <summary>
/// DataBase 的摘要說明
/// </summary>
public class DataBase
{
    private DbConnection cnn;//抽象類型
    private DbCommand cmd;//抽象類型
    private DbProviderFactory provider;
    private string providerName;
   
    public DataBase(String connectionName)
    {
        providerName = WebConfigurationManager.ConnectionStrings[connectionName].ProviderName;
        provider = DbProviderFactories.GetFactory(providerName);
        cnn = provider.CreateConnection();
        cnn.ConnectionString = WebConfigurationManager.ConnectionStrings[connectionName].ConnectionString;
        cmd = provider.CreateCommand();
        cmd.Connection = cnn;
    }
    #region 執行不帶參數的SQL語句
    /// <summary>
    /// 執行SQL語句，返回影響的記錄數
    /// </summary>
    public int ExecuteSQL(string sql)
    {
        return ExecuteSQL(sql, null);
    }
    /// <summary>
    /// 執行多條SQL語句，實現資料庫事務。
    /// </summary>
    public int ExecuteSqlTran(List<string> sqlList)
    {
        int count = -1;
        cnn.Open();
        DbTransaction tx = cnn.BeginTransaction();
        try
        {
            cmd.Transaction = tx;
            for (int n = 0; n < sqlList.Count; n++)
            {
                string strsql = sqlList[n].ToString();
                if (strsql.Trim().Length > 1)
                {
                    cmd.CommandText = strsql;
                    count = cmd.ExecuteNonQuery();
                }
            }
            tx.Commit();
        }
        catch (SqlException e)
        {
            tx.Rollback();
            cnn.Close();
            throw new Exception(e.Message);
        }
        return count;
    }

    /// <summary>
    /// 執行一條計算查詢結果語句，返回查詢結果（object）。
    /// </summary>
    public int ExecuteScalar(string sql)
    {
        return ExecuteScalar(sql, null);
    }
    /// <summary>
    /// 執行查詢語句，返回DataSet
    /// </summary>
    public DataSet GetDataSet(string sql)
    {
        return GetDataSet(sql, null);
    }
    /// <summary>
    /// 執行查詢語句，返回DataSet
    /// </summary>
    public DataTable GetDataTable(string sql)
    {
        return GetDataSet(sql).Tables[0];
    }
    /// <summary>
    /// 執行查詢語句，返回DataReader(使用該方法切記要手工關閉DataReader和連接)
    /// </summary>
    public DbDataReader ExecuteReader(string sql)
    {
        return ExecuteReader(sql, null);
    }
    #endregion


    #region 執行帶參數的SQL語句
    /// <summary>
    /// 執行SQL語句，返回影響的記錄數
    /// </summary>
    public int ExecuteSQL(string sql, params DbParameter[] cmdParms)
    {
        try
        {
            CreateCommand(sql, cmdParms);
            int rows = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            return rows;
        }
        catch (SqlException e)
        {
            cnn.Close();
            throw new Exception(e.Message);
        }
    }
    /// <summary>
    /// 執行多條SQL語句，實現資料庫事務。
    /// </summary>
    public int ExecuteSqlTran(Hashtable sqlList)
    {
        int count = -1;
        cnn.Open();
        DbTransaction tx = cnn.BeginTransaction();
        try
        {
            cmd.Transaction = tx;
            foreach (DictionaryEntry myDE in sqlList)
            {
                string cmdText = myDE.Key.ToString();
                DbParameter[] cmdParms = (DbParameter[])myDE.Value;
                CreateCommand(cmdText, cmdParms);
                count = cmd.ExecuteNonQuery();
            }
            tx.Commit();
        }
        catch (SqlException e)
        {
            tx.Rollback();
            cnn.Close();
            throw new Exception(e.Message);
        }
        return count;
    }

    /// <summary>
    /// 執行一條計算查詢結果語句，返回查詢結果（object）。
    /// </summary>
    public int ExecuteScalar(string sql, params DbParameter[] cmdParms)
    {
        try
        {
            CreateCommand(sql, cmdParms);
            object o = cmd.ExecuteScalar();
            return int.Parse(o.ToString());
        }
        catch (SqlException e)
        {
            cnn.Close();
            throw new Exception(e.Message);
        }
    }
    /// <summary>
    /// 執行查詢語句，返回DataSet
    /// </summary>
    public DataSet GetDataSet(string sql, params DbParameter[] cmdParms)
    {
        DataSet ds = new DataSet();
        try
        {
            CreateCommand(sql, cmdParms);
            DbDataAdapter adapter = provider.CreateDataAdapter();
            adapter.SelectCommand = cmd;
            adapter.Fill(ds);
        }
        catch (SqlException e)
        {
            cnn.Close();
            throw new Exception(e.Message);
        }
        return ds;
    }
    /// <summary>
    /// 執行查詢語句，返回DataTable
    /// </summary>
    public DataTable GetDataTable(string sql, params DbParameter[] cmdParms)
    {
        return GetDataSet(sql, cmdParms).Tables[0];
    }
    /// <summary>
    /// 執行查詢語句，返回DataReader(使用該方法切記要手工關閉DataReader和連接)
    /// </summary>
    public DbDataReader ExecuteReader(string sql, params DbParameter[] cmdParms)
    {
        try
        {
            CreateCommand(sql, cmdParms);
            DbDataReader myReader = cmd.ExecuteReader();
            return myReader;
        }
        catch (SqlException e)
        {
            cnn.Close();
            throw new Exception(e.Message);
        }
    }
    public DbParameter MakeParam(string ParamName, DbType DbType, Int32 Size, object Value)
    {
        DbParameter Param = cmd.CreateParameter();
        Param.ParameterName = ParamName;
        Param.DbType = DbType;
        if (Size > 0)
            Param.Size = Size;
        if (Value != null)
            Param.Value = Value;
        return Param;
    }
    private DbCommand CreateCommand(string cmdText, DbParameter[] Prams)
    {
        return CreateCommand(CommandType.Text, cmdText, Prams);
    }
    private DbCommand CreateCommand(CommandType cmdType, string cmdText, DbParameter[] Prams)
    {
        if (cnn.State != ConnectionState.Open)
            cnn.Open();
        cmd.CommandType = cmdType;
        cmd.CommandText = cmdText;
        if (Prams != null)
        {
            cmd.Parameters.Clear();
            foreach (DbParameter Parameter in Prams)
                cmd.Parameters.Add(Parameter);
        }
        return cmd;
    }
    public DataSet GetDataSetByProc(string ProcName, DbParameter[] Params)
    {
        cnn.Open();
        DbCommand cmd = CreateCommand(CommandType.StoredProcedure, ProcName, Params);
        DbDataAdapter adapter = provider.CreateDataAdapter();
        adapter.SelectCommand = cmd;
        DataSet ds = new DataSet();
        adapter.Fill(ds);
        cnn.Close();
        return ds;
    }
    #endregion
}

資料庫連接類

3、ExcelHelper.cs

using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Web;

/// <summary>
/// ExcelHelper 的摘要說明
/// </summary>
public class ExcelHelper
{
    public static void ExportXlsx(DataTable dt, string strFileName)
    {
        HttpContext curContext = HttpContext.Current;
        MemoryStream ms = ExportXlsx(dt);
        curContext.Response.AppendHeader("Content-Disposition",
            "attachment;filename=" + HttpUtility.UrlEncode(strFileName, Encoding.UTF8) + ".xlsx");
        curContext.Response.AddHeader("Content-Length", ms.ToArray().Length.ToString());
        curContext.Response.ContentEncoding = Encoding.UTF8;

        curContext.Response.BinaryWrite(ms.ToArray());
        ms.Close();
        ms.Dispose();
        curContext.Response.End();

    }
    private static MemoryStream ExportXlsx(DataTable dt)
    {
        XSSFWorkbook workbook = new XSSFWorkbook();
        ISheet sheet = null;

        int headRowIndex = 0;
        string sheetName = "Sheet1";
        if (!string.IsNullOrEmpty(dt.TableName))
        {
            sheetName = dt.TableName;
        }
        sheet = workbook.CreateSheet(sheetName);
        int rowIndex = 0;
       
        XSSFRow headerRow = (XSSFRow)sheet.CreateRow(headRowIndex);

        ICellStyle headStyle = workbook.CreateCellStyle();
        headStyle.Alignment = HorizontalAlignment.Center;
        IFont font = workbook.CreateFont();
        font.FontHeightInPoints = 10;
        font.Boldweight = 700;
        headStyle.SetFont(font);

        foreach (DataColumn column in dt.Columns)
        {
            headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
            headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
        }
             
        foreach (DataRow row in dt.Rows)
        {
            rowIndex++;
            XSSFRow dataRow = (XSSFRow)sheet.CreateRow(rowIndex);
            foreach (DataColumn column in dt.Columns)
            {
                dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
            }
        }

        MemoryStream ms = new MemoryStream();
        workbook.Write(ms);
        ms.Flush();

        return ms;
    }
}

導出Excel工具類

4、sql.xml

<?xml version="1.0" encoding="utf-8" ?>
<sql>
  <item id="1" text="報表1" dbConnName="OracleString">
      select * from tb
   </item>
  <item id="2" text="報表2" dbConnName="SqlServerString">
      select * from tb
   </item>
</sql>

保存sql語句等信息的xml

5、SqlEntity.cs

public class SqlEntity
{
    public SqlEntity()
    {      
    }
    public int Id { get; set; }
    public string text { get; set; }
    public string sql { get; set; }    
    public string dbConnName { get; set; }
}

實體類

6、SqlEntityList.cs

public class SqlEntityList
{
    public List<SqlEntity> GetXmlData(String xmlPath)
    {
        var list = new List<SqlEntity>();
        XmlDocument xmlDoc = new XmlDocument();
        xmlDoc.Load(xmlPath);
        XmlNodeList xnl = xmlDoc.SelectSingleNode("sql").ChildNodes;
        for (int i = 0; i < xnl.Count; i++)
        {
            int id = int.Parse(xnl.Item(i).Attributes["id"].Value);
            string text = xnl.Item(i).Attributes["text"].Value;            
            string dbConnName = xnl.Item(i).Attributes["dbConnName"].Value;
            string sql = xnl.Item(i).InnerText;          

            var model = new SqlEntity()
            {
                Id = id,
                text = text,               
                dbConnName = dbConnName.ToLower(),
                sql = sql
            };
            list.Add(model);
        }
        return list;
    }

}

xml內容轉實體

7、rpt.aspx

        <div> 
            <asp:DropDownList ID="ddlType" DataTextField="text" DataValueField="id" runat="server">               
            </asp:DropDownList>
            <asp:Button runat="server" ID="btnQuery" Text="查詢" OnClick="btnQuery_Click"/>
            <asp:Literal runat="server" ID="ltlInfo"></asp:Literal>
            <asp:Button runat="server" ID="btnExport" Text="導出" OnClick="btnExport_Click" />            
        </div>
        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="true" Width="100%"></asp:GridView>

前端頁

8、rpt.aspx.cs

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            ddlType.DataSource = getSqlList();
            ddlType.DataBind();
        }
    }

    private List<SqlEntity> getSqlList()
    {
        String path = Server.MapPath("~/App_Data/sql.xml");
        SqlEntityList sqlEntityList = new SqlEntityList();
        List<SqlEntity> list = sqlEntityList.GetXmlData(path);
        return list;
    }

    private DataSet getDataSet(int type)
    {
        DataSet ds = new DataSet();
        List <SqlEntity> list = getSqlList();
        var m = list.FirstOrDefault(t => t.Id == type); 
        DataBase db = new DataBase(m.dbConnName);
        ds = db.GetDataSet(m.sql);        
        ltlInfo.Text = "記錄數:" + ds.Tables[0].Rows.Count.ToString();
        return ds;
    }

    private void BindData(DataSet ds)
    {
        GridView1.DataSource = ds;
        GridView1.DataBind();
    }


    protected void btnQuery_Click(object sender, EventArgs e)
    {
        int type = int.Parse(ddlType.SelectedValue);
        DataSet ds = getDataSet(type);
        BindData(ds);
    }

    protected void btnExport_Click(object sender, EventArgs e)
    {
        int type = int.Parse(ddlType.SelectedValue);
        DataSet ds = getDataSet(type);
        DataTable dt = ds.Tables[0];
        String fileName = ddlType.SelectedItem.Text;
        ExcelHelper.ExportXlsx(dt, fileName);
    }

前端頁的後臺代碼