多表查询返回多个DataTable,合并到一个Table中.

    本文意在说明一下DataSet.Merge(Table)和多个Table合并到一个里面的操作.因为是新手,代码肯定有很多问题,欢迎大家批评指正.    如果你有更好的解决方案,请告诉我.谢谢.

先说一下需求:ASP.NET+MYSQL

   

    查询连续N天的邮件发送日志.在库里,表是按天存放的,表名也是按天命名的.例如:mailsend20090302,mailstatus20090302,两张表有ID关联.为了调试简单,我把日期直接写死.在点查询按钮之后,首先根据得到的日期拼凑表名,得到两个表名数组,然后调用MySqlHelper类里的方法,下面是数据库操作的代码.因为用的是MySql,所以需要添加一个MySql.Data.dll引用.另外前两天看过string 与stringbuilder之间的区别,所以拼sql 时用了stringbuilder,然后用其ToString()赋给数组.

对数据做点说明:

mstatus:all,success,failure

mtype:jobs,email

pageindex,pagesize为分页控件的属性,分别表示第N页,和每页显示多少数据.

拼表名的方法:

 

 1 //数据库表名
 2        string[] sendTables;
 3        string[] statusTables;
 4
 5        //拼表名
 6        void GetTableName()
 7        {
 8            string dateFrom = "2009-01-07";
 9            string dateTo = "2009-01-10";
            TimeSpan ts = Convert.ToDateTime(dateTo) - Convert.ToDateTime(dateFrom);
            int counter = ts.Days + 1;
            sendTables = new string[counter];
            statusTables = new string[counter];
           
            for (int i = 0; i < counter; i++)
            {
                sendTables[i] = "mailsend" + Convert.ToDateTime(dateFrom).AddDays(i).ToString("yyyyMMdd");
                statusTables[i] = "mailstatus" + Convert.ToDateTime(dateFrom).AddDays(i).ToString("yyyyMMdd");
            }
            
        }

 

  1using System;
  2using System.Data;
  3using System.Configuration;
  4using System.Web;
  5using System.Web.Security;
  6using System.Web.UI;
  7using System.Web.UI.WebControls;
  8using System.Web.UI.WebControls.WebParts;
  9using System.Web.UI.HtmlControls;
 10using MySql.Data.MySqlClient;
 11using System.Text;
 12
 13namespace MultiTable
 14{
 15    public class MySqlHelper
 16    {
 17        private static string strConn = ConfigurationManager.AppSettings["connStr"].ToString();
 18        //多天查 询 sendTables,statusTables,muser,mdomain,mstatus,pageindex,pagesize 
 19        private static string[] PrepareSelectString2(string[] sendTables, string[] statusTables, string muser, string mdomain, string mstatus, string mtype, int pageindex, int pagesize)
 20        {
 21            string[] strSelect = new string[sendTables.Length];
 22            StringBuilder sbCommand2 = new StringBuilder();
 23            for (int i = 0; i < sendTables.Length; i++)
 24            {
 25                StringBuilder sbCommand = new StringBuilder(@"select d.rundate,d.runtime,s.status,s.failcode from " + sendTables[i] + " as d," + statusTables[i] + " as s " +
 26                                    "where d.id=s.id and d.maildomain=" + "\"" + mdomain + "\"" + " and d.mailuser=" + "\"" + muser + "\"");
 27                if (mstatus != "all")
 28                {
 29                    sbCommand.Append(" and s.status=" + "\"" + mstatus + "\"");
 30                }
 31                if (mtype == "jobs")
 32                {
 33                    sbCommand.Append(" and (d.host!='quickmail6' and d.host!='quickmail7')");
 34                }
 35                else
 36                {
 37                    sbCommand.Append(" and (d.host='quickmail6' or d.host='quickmail7')");
 38                }
 39                sbCommand.Append("order by d.runtime  asc");
 40                strSelect[i] = sbCommand.ToString();
 41            }
 42            return strSelect;
 43        }
 44        private static string[] PrepareExportString2(string[] sendTables, string[] statusTables, string muser, string mdomain, string mstatus, string mtype)
 45        {
 46            string[] strSelect = new string[sendTables.Length];
 47            StringBuilder sbCommand2 = new StringBuilder();
 48            for (int i = 0; i < sendTables.Length; i++)
 49            {
 50                StringBuilder sbCommand = new StringBuilder(@"select cast(d.rundate as char(10)) as rundate,d.runtime,s.status,s.failcode from " + sendTables[i] + " as d," + statusTables[i] + " as s " +
 51                                    "where d.id=s.id and d.maildomain=" + "\"" + mdomain + "\"" + " and d.mailuser=" + "\"" + muser + "\"");
 52                if (mstatus != "all")
 53                {
 54                    sbCommand.Append(" and s.status=" + "\"" + mstatus + "\"");
 55                }
 56                if (mtype == "jobs")
 57                {
 58                    sbCommand.Append(" and (d.host!='quickmail6' and d.host!='quickmail7')");
 59                }
 60                else
 61                {
 62                    sbCommand.Append(" and (d.host='quickmail6' or d.host='quickmail7')");
 63                }
 64                sbCommand.Append(" order by d.rundate,d.runtime asc ");
 65                strSelect[i] = sbCommand.ToString();
 66            }         
 67            return strSelect;
 68        }
 69        //多天
 70        //统计数量 
 71        private static string[] PrepareCountString2(string[] sendTables, string[] statusTables, string muser, string mdomain, string mstatus, string type)
 72        {
 73            string[] strCount = new string[sendTables.Length];
 74            for (int i = 0; i < sendTables.Length; i++)
 75            {
 76                StringBuilder sbCommand = new StringBuilder(@"select count(d.id) from " + sendTables[i] + " as d," + statusTables[i] + " as s where d.id=s.id and d.maildomain=" + "\"" + mdomain + "\"" + " and d.mailuser=" + "\"" + muser + "\"");
 77                if (mstatus != "all")
 78                {
 79                    sbCommand.Append(" and s.status=" + "\"" + mstatus + "\"");
 80                }
 81                if (type == "jobs")
 82                {
 83                    sbCommand.Append(" and (d.host!='quickmail6' and d.host!='quickmail7')");
 84                }
 85                else
 86                {
 87                    sbCommand.Append(" and (d.host='quickmail6' or d.host='quickmail7')");
 88                }
 89                strCount[i] = sbCommand.ToString();
 90            }
 91            return strCount;
 92        }
 93        //多天数量统计string[] PrepareCountString(string[] sendTable, string[] statusTables, string muser, string mdomain, string mstatus, string type)
 94
 95        public static int GetCount2(string[] sendTables, string[] statusTables, string user, string domain, string status, string type)
 96        {
 97            MySqlConnection MyConn = new MySqlConnection(strConn);
 98            try
 99            {
                OpenConnection(MyConn);
                MySqlCommand MyCommand = new MySqlCommand();
                MyCommand.Connection = MyConn;
                MyCommand.CommandType = CommandType.Text;
                string[] strCount = PrepareCountString2(sendTables, statusTables, user, domain, status, type);
                int count = 0;
                //将每个表里数据的数量加起来得到总数.
                for (int i = 0; i < strCount.Length; i++)
                {
                    MyCommand.CommandText = strCount[i];
                    count += Convert.ToInt32(MyCommand.ExecuteScalar().ToString());
                }
                return count;
                CloseConnection(MyConn);
            }
            catch (Exception)
            {
                return 0;
            }
        }
        //多天导出数据.
        public static DataTable ExportData2(string[] sendTables, string[] statusTables, string user, string domain, string status, string type)
        {
            DataSet MyDS = new DataSet();
            DataTable MyTable;
            try
            {
                MySqlConnection MyConn = new MySqlConnection(strConn);
                OpenConnection(MyConn);
                MySqlCommand MyCommand = new MySqlCommand();
                MyCommand.Connection = MyConn;
                MyCommand.CommandType = CommandType.Text;
                MyCommand.CommandTimeout = 180;
                MySqlDataAdapter MyAdapter = new MySqlDataAdapter();
                string[] strCommand = PrepareExportString2(sendTables, statusTables, user, domain, status, type);
                string[] tableName = new string[sendTables.Length];

                //按日期取出数据,分别存放到DataSet的表中.
                for (int i = 0; i < strCommand.Length; i++)
                {
                    MyCommand.CommandText = strCommand[i];
                    MyAdapter.SelectCommand = MyCommand;
                    tableName[i] = "Log" + i.ToString();
                    MyAdapter.Fill(MyDS, tableName[i]);
                    MyDS.Merge(MyDS.Tables[i]);
                }               
                //复制表结构.
                MyTable = new DataTable();
                MyTable = MyDS.Tables[0].Clone();
                
                //将DataSet中多个表的数据合并到一个新表里.
                for (int count = 0; count < MyDS.Tables.Count ; count++)
                {
                    for (int i = 0; i < MyDS.Tables[count].Rows.Count; i++)
                    {
                        DataRow dr = MyTable.NewRow();
                        for (int j = 0; j < MyDS.Tables[count].Columns.Count; j++)
                        {
                            dr[j] = MyDS.Tables[count].Rows[i][j];
                        }
                        MyTable.Rows.Add(dr);                        
                    }                    
                }
                CloseConnection(MyConn);
                return MyTable;
            }
            catch (Exception)
            {
                return new DataTable();
            }
        }
        //多天查询数据.
        public static DataTable GetData2(string[] sendTables, string[] statusTables, string user, string domain, string status, string type, int pageindex, int pagesize)
        {
            DataTable MyTable ;
            DataTable MyTable2 = new DataTable();
            try
            {
                //取出所有数据到一张表里.
                MyTable = new DataTable();
                MyTable = ExportData2(sendTables, statusTables, user, domain, status, type);

                //拿出(pageindex-1)*pagesize--pageindex*pagesize的数据.
                MyTable2= MyTable.Clone();
                for (int i = 0; i < pagesize; i++)
                {
                    int index = i + pagesize * (pageindex - 1);
                    DataRow dr = MyTable2.NewRow();
                    //MyDS.Tables[0].NewRow();
                    for (int j = 0; j < MyTable.Columns.Count; j++)
                    {
                        dr[j] = MyTable.Rows[index][j];
                    }
                    MyTable2.Rows.Add(dr);
                 }
                return MyTable2;
            }
            catch (Exception)
            {
                return new DataTable();
            }
        }
        private static void CloseConnection(MySqlConnection MyConn)
        {
            if (MyConn.State == ConnectionState.Open)
                MyConn.Close();
        }
        private static void OpenConnection(MySqlConnection MyConn)
        {
            if (MyConn.State == ConnectionState.Closed)
                MyConn.Open();
        }
    }

}