.net实现一个简单的通用查询数据、导出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;
    }
}

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>

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;
    }

}

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);
    }