C#Mysql批量查询、自定义DataTable存储数据_c# datatable 查询

批量查询、多表查询、查询结果使用自定义DataTable存储，可以实现自己想要的结果展示。

//输入查询Item 批量

<asp:TextBox ID="txb_input" runat="server" Text="" Width="200px" Height="300px" TextMode="MultiLine"></asp:TextBox>

//点击按钮查询

<asp:Button ID="btn_search" runat="server" Text="Search" Height="40px" Width="100px" OnClick="btn_search_Click" />

//导出查询结果

<asp:Button ID="btn_export" runat="server" Text="导出" Width="100px" Height="25px" BorderStyle="None" BackColor="White" ForeColor="Black" OnClick="btn_export_Click" />

//存储查询结果
 <asp:DataGrid ID="dg_result" runat="server" HeaderStyle-BackColor="CadetBlue" Width="100%" ></asp:DataGrid>

//===================后端==============================

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.IO;

//定义全局DataTable方便全局调整、导出

private static DataTable tb_result=new DataTable();

protected void Page_Load(object sender, EventArgs e)
    {      

                //页面加载执行代码... ....

    }

//点击查询按钮触发查询事件

protected void btn_search_Click(object sender, EventArgs e)
{

        if (txb_input.Text.Trim() == "")
        {
            return;
        }

//通过回车换行符分割输入数据

    string [] inputs=txb_input.Text.Trim().Split(new string[] { "\r\n" },StringSplitOptions.None);

        //根据自己展示需要进行DataTable自定义

        tb_result=new DataTable();
        tb_result.Columns.Add("字段1", Type.GetType("System.String"));
        tb_result.Columns.Add("字段2", Type.GetType("System.String"));
        tb_result.Columns.Add("字段3", Type.GetType("System.String"));
        tb_result.Columns.Add("字段4", Type.GetType("System.String"));
        tb_result.Columns.Add("字段5", Type.GetType("System.String"));
        tb_result.Columns.Add("字段6", Type.GetType("System.String"));

        //....字段7...

        //拼接Mysql批量查询所需字符格式

        string inputlist = "";
        for (int i = 0; i < inputs.Length; i++)
        {
            inputlist+="'"+inputs[i]+"'";
            if (i != inputs.Length - 1)
            {
                inputlist += ",";
            }
        }

        //查询填充字段1、2、3
        DataTable tb_tmp = Sqlhelp.LoadData("select test1,test2,test3 from testTable1 where input in ("+inputlist+") order by test1" );
        string tests = "";
        for (int i = 0; i < tb_tmp.Rows.Count; i++)
        {
            DataRow dr = tb_result.NewRow();
            dr["字段1"] = tb_tmp.Rows[i]["test1"].ToString().Trim();
            dr["字段2"]= tb_tmp.Rows[i]["test2"].ToString().Trim();
            tests+= "'" + tb_tmp.Rows[i]["test1"].ToString().Trim()+ "'";
            if (i != tb_tmp.Rows.Count - 1)
            {
                tests += ",";
            }
            dr["字段3"] = tb_tmp.Rows[i]["test3"].ToString().Trim();
            tb_result.Rows.Add(dr);
        }

        // 通过上述查询出来的tests集合去testTable2进行查询（如果需要），以此类推进行多表结合
        tb_tmp = Sqlhelp.LoadData("select 字段1，test4 from testTable2 where 字段1 in (" + tests + ")");
        for (int i = 0; i <tb_result.Rows.Count; i++)
        {

            //从tb_tmp获取所需字段填充到tb_result中
            DataRow[] dr_tmp = tb_tmp.Select("test4='" + tb_result.Rows[i]["字段1"].ToString().Trim() + "'");

            //对tb_result字段4进行赋值
            if (dr_tmp.Length > 0)
            {
                tb_result.Rows[i]["字段4"] = dr_tmp[0]["test4"].ToString().Trim();
            }
            else
            {
                tb_result.Rows[i]["字段4"] = "-";
            }
            
        }

        //重复上述动作、进行其他所需查询并对字段5、字段6...进行赋值

        //....

        //展示最终结果

        dg_result.DataSource = tb_result;
        dg_result.DataBind();

}

//导出最终结果

protected void btn_export_Click(object sender, EventArgs e)
    {
        if (tb_result.Rows.Count != 0)
        {
            DataTableToExcel(tb_result, "OrderStatus");
        }
    }
    protected static void DataTableToExcel(DataTable dt, string ExportFileName)
    {
        DataGrid dgExcel = new DataGrid();
        dgExcel.DataSource = dt;
        dgExcel.DataBind();

        HttpContext.Current.Response.Charset = "GB2312";
        string fileName = HttpUtility.UrlEncode(ExportFileName, System.Text.Encoding.UTF8);
        string str = "attachment;filename=" + fileName + ".xls";
        HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;
        HttpContext.Current.Response.ContentType = "application/ms-excel";
        HttpContext.Current.Response.AppendHeader("content-disposition", str);

        StringWriter sw = new StringWriter();
        HtmlTextWriter htmTextWriter = new HtmlTextWriter(sw);
        dgExcel.RenderControl(htmTextWriter);
        // Response.Write(sw);
        HttpContext.Current.Response.Write("<meta http-equiv=\"content-type\" content=\"application/vnd.ms-excel; charset=utf-8\"/>" + sw.ToString());

        HttpContext.Current.Response.End();
    }