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

显示全部楼层 · 2019-9-12 16:22:27

配景：
临时提供一个简单的网页，供其他人浏览数据库（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