思享工具箱

.net实现一个简单的通用查询数据、导出Excel的网页

摘要：
它支持在配置文件中随时添加或修改sql。

背景：
临时提供一个简单的网页，供其他人浏览数据库（Oracel、MSSQL）的某些数据，并导出Excel。
支持在配置文件中随时添加或修改sql。

实现：
把sql语句等信息保存一个xml文件中，前端页面提供一个下拉框，可以选择不同类型的sql语句，查询结果（暂没分页需要）直接绑定到GridView。

.net实现一个简单的通用查询数据、导出Excel的网页第1张

开发环境：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

.net实现一个简单的通用查询数据、导出Excel的网页第2张.net实现一个简单的通用查询数据、导出Excel的网页第3张
  <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

.net实现一个简单的通用查询数据、导出Excel的网页第2张.net实现一个简单的通用查询数据、导出Excel的网页第5张
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

.net实现一个简单的通用查询数据、导出Excel的网页第2张.net实现一个简单的通用查询数据、导出Excel的网页第7张
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 

.net实现一个简单的通用查询数据、导出Excel的网页第2张.net实现一个简单的通用查询数据、导出Excel的网页第9张
<?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

.net实现一个简单的通用查询数据、导出Excel的网页第2张.net实现一个简单的通用查询数据、导出Excel的网页第11张
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

.net实现一个简单的通用查询数据、导出Excel的网页第2张.net实现一个简单的通用查询数据、导出Excel的网页第13张
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

.net实现一个简单的通用查询数据、导出Excel的网页第2张.net实现一个简单的通用查询数据、导出Excel的网页第15张
        <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

.net实现一个简单的通用查询数据、导出Excel的网页第2张.net实现一个简单的通用查询数据、导出Excel的网页第17张
    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);
    }
前端页的后台代码

标签：#sql数据库 | 浏览：174 | 发布日期：

免责声明：文章转载自《.net实现一个简单的通用查询数据、导出Excel的网页》仅用于学习参考。如对内容有疑问，请及时联系本站处理。

上篇Django项目中使用celery做异步任务经验风险最小化与结构风险最小化下篇

宿迁高防，2C2G15M，22元/月；香港BGP，2C5G5M，25元/月 雨云优惠码：MjYwNzM=

相关文章

Cypress web自动化35-cy.exec()执行python命令操作数据库

前言 cy.exec()可以执行系统命令，获取到stdout内容，当我们要操作数据库，准备测试数据的时候，通常用python连数据库操作会非常方便。 我们可以先把操作数据库的方法封装到一个py文件，这样执行的结果print到控制台输出上，通过执行cy.exec()获取到stdout内容就可以了。 python操作数据库 使用python清理测试数据，如需要...

[原创]Spring JdbcTemplate 使用总结与经验分享

引言 近期开发的几个项目，均是基于Spring boot框架的web后端项目，使用JdbcTemplate执行数据库操作，实际开发过程中，掌握了一些有效的开发经验，踩过一些坑，在此做个记录及总结，与各位读者分享。 欢迎留言与我交流。 正确使用JdbcTemplate执行数据库操作 1、Bean声明 新增类型DatabaseConfiguration，添加注...

Kali学习笔记39：SQL手工注入（1）

终于到了SQL注入 最大的、最经典的、最常见的Web漏洞就是SQL注入漏洞 SQL注入的原理这里就不说了，百度 打开DVWA，SQL注入测试模块 测试单引号，发现出错，于是想到测试语句： 1' or '1'='1 成功： 测试是否存在漏洞： 1' and '1'='1 如果返回数据，但是1' and '1'='0 不返回数据，代表存在sql注入 或者简...

面面俱到:SQL SERVER 2008主数据管理

SQLSERVER 2008R2中提供了主数据服务。很多人对主数据服务比较陌生。我们先介绍主数据服务的应用范畴。 主数据服务是当企业的规模达到一定的规模后，因企业信息化战略发展的需要。需要将企业内信息进行统一规划、形成制定统一的标准。建立一套统一的公共数据标准及维护体系、一个统一的公共数据平台。使其各子系统均按照统一的标准使用公共数据。使企业在信息化形成一...

细聊Oracle通过ODBC数据源连接SQL Server数据库

    类似文章搜索引擎上有很多，内容大致相同，今天所谓细聊是因为我在借鉴这些文章时候走了些弯路，所以写此文，为自己备忘，同时如果能为初涉此处知识点的小伙伴提供些帮助就更好了,文章结尾处的一些扩展有一定实战意义，废话不多说。     我的应用场景：     项目系统使用oracle数据库，与客户的第三方系统供应商实现资源共享，对方为sql server数据...

原生JDBC

JDBCJava DataBase Connectivity,java数据库连接,是一种用于执行SQL语句的Java API。JDBC是Java访问数据库的标准规范，可以为不同的关系型数据库提供统一访问，它由一组用Java语言编写的接口和类组成。 驱动JDBC需要连接驱动，驱动是两个设备要进行通信，满足一定通信数据格式，数据格式由设备提供商规定，设备提供商...

最新文章

随机推荐

思享工具箱导航