net Implements a Simple General Query Data and Exports Excel Web Page

Posted by hbradshaw on Fri, 04 Oct 2019 12:13:02 +0200

Background:
Provide a simple web page temporarily for others to browse some data in the database (Oracel, MSSQL) and export Excel.
Support adding or modifying sql at any time in the configuration file.

 

Realization:
Store sql statements and other information in an xml file. The front-end page provides a drop-down box to select different types of sql statements. The query results (not required for paging) are directly bound to GridView.

 

 

Development environment: VS2015
NuGet introduces libraries: NPOI, Oracle.ManagedDataAccess.Client

I. VS Directory

├─App_Code
DataBase.cs------------------- Database Connection Class ____________
Excel Helper. CS - -------- Export Excel Tool Class
SqlEntity.cs--------------- Entity class of SQL statement
SqlEntityList.cs - --------------------------------------------------------------------------------------------------------------------------------------
│
├─App_Data
Sql. XML - - - - - - SQL statement
│
rpt.aspx - - - - - - Front-end query page
│  rpt.aspx.cs  ---------
Web.config - - - - - - Configuring database connection information

Two, code
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>
Database connection string

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 description of
/// </summary>
public class DataBase
{
    private DbConnection cnn;//Abstract type
    private DbCommand cmd;//Abstract type
    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 Executing parameterized SQL Sentence
    /// <summary>
    /// implement SQL Statement, returning the number of records affected
    /// </summary>
    public int ExecuteSQL(string sql)
    {
        return ExecuteSQL(sql, null);
    }
    /// <summary>
    /// Execute multiple SQL Statement to achieve database transactions.
    /// </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>
    /// Execute a query result statement and return the query result( object). 
    /// </summary>
    public int ExecuteScalar(string sql)
    {
        return ExecuteScalar(sql, null);
    }
    /// <summary>
    /// Execute the query statement and return DataSet
    /// </summary>
    public DataSet GetDataSet(string sql)
    {
        return GetDataSet(sql, null);
    }
    /// <summary>
    /// Execute the query statement and return DataSet
    /// </summary>
    public DataTable GetDataTable(string sql)
    {
        return GetDataSet(sql).Tables[0];
    }
    /// <summary>
    /// Execute the query statement and return DataReader(Use this method to remember to close manually DataReader And connection)
    /// </summary>
    public DbDataReader ExecuteReader(string sql)
    {
        return ExecuteReader(sql, null);
    }
    #endregion


    #region Executing parameterized SQL Sentence
    /// <summary>
    /// implement SQL Statement, returning the number of records affected
    /// </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>
    /// Execute multiple SQL Statement to achieve database transactions.
    /// </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>
    /// Execute a query result statement and return the query result( 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>
    /// Execute the query statement and return 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>
    /// Execute the query statement and return DataTable
    /// </summary>
    public DataTable GetDataTable(string sql, params DbParameter[] cmdParms)
    {
        return GetDataSet(sql, cmdParms).Tables[0];
    }
    /// <summary>
    /// Execute the query statement and return DataReader(Use this method to remember to close manually DataReader And connection)
    /// </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
}
Database Connection Class

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 description of
/// </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;
    }
}
Export Excel Tool Class

4,sql.xml 

<?xml version="1.0" encoding="utf-8" ?>
<sql>
  <item id="1" text="Statement 1" dbConnName="OracleString">
      select * from tb
   </item>
  <item id="2" text="Statement 2" dbConnName="SqlServerString">
      select * from tb
   </item>
</sql>
xml to save information such as sql statements

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

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 content to entity

7,rpt.aspx

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

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 = "Record number:" + 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);
    }
Background code for front-end pages

Topics: ASP.NET SQL Database xml Excel

Hot Topics

©2024 Programmer Think