多表查询返回多个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页,和每页显示多少数据.

拼表名的方法:

 

Code
 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";
10            TimeSpan ts = Convert.ToDateTime(dateTo) - Convert.ToDateTime(dateFrom);
11            int counter = ts.Days + 1;
12            sendTables = new string[counter];
13            statusTables = new string[counter];
14           
15            for (int i = 0; i < counter; i++)
16            {
17                sendTables[i] = "mailsend" + Convert.ToDateTime(dateFrom).AddDays(i).ToString("yyyyMMdd");
18                statusTables[i] = "mailstatus" + Convert.ToDateTime(dateFrom).AddDays(i).ToString("yyyyMMdd");
19            }
20            
21        }
22

 

Code
  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            {
100                OpenConnection(MyConn);
101                MySqlCommand MyCommand = new MySqlCommand();
102                MyCommand.Connection = MyConn;
103                MyCommand.CommandType = CommandType.Text;
104                string[] strCount = PrepareCountString2(sendTables, statusTables, user, domain, status, type);
105                int count = 0;
106                //将每个表里数据的数量加起来得到总数.
107                for (int i = 0; i < strCount.Length; i++)
108                {
109                    MyCommand.CommandText = strCount[i];
110                    count += Convert.ToInt32(MyCommand.ExecuteScalar().ToString());
111                }
112                return count;
113                CloseConnection(MyConn);
114            }
115            catch (Exception)
116            {
117                return 0;
118            }
119        }
120        //多天导出数据.
121        public static DataTable ExportData2(string[] sendTables, string[] statusTables, string user, string domain, string status, string type)
122        {
123            DataSet MyDS = new DataSet();
124            DataTable MyTable;
125            try
126            {
127                MySqlConnection MyConn = new MySqlConnection(strConn);
128                OpenConnection(MyConn);
129                MySqlCommand MyCommand = new MySqlCommand();
130                MyCommand.Connection = MyConn;
131                MyCommand.CommandType = CommandType.Text;
132                MyCommand.CommandTimeout = 180;
133                MySqlDataAdapter MyAdapter = new MySqlDataAdapter();
134                string[] strCommand = PrepareExportString2(sendTables, statusTables, user, domain, status, type);
135                string[] tableName = new string[sendTables.Length];
136
137                //按日期取出数据,分别存放到DataSet的表中.
138                for (int i = 0; i < strCommand.Length; i++)
139                {
140                    MyCommand.CommandText = strCommand[i];
141                    MyAdapter.SelectCommand = MyCommand;
142                    tableName[i] = "Log" + i.ToString();
143                    MyAdapter.Fill(MyDS, tableName[i]);
144                    MyDS.Merge(MyDS.Tables[i]);
145                }               
146                //复制表结构.
147                MyTable = new DataTable();
148                MyTable = MyDS.Tables[0].Clone();
149                
150                //将DataSet中多个表的数据合并到一个新表里.
151                for (int count = 0; count < MyDS.Tables.Count ; count++)
152                {
153                    for (int i = 0; i < MyDS.Tables[count].Rows.Count; i++)
154                    {
155                        DataRow dr = MyTable.NewRow();
156                        for (int j = 0; j < MyDS.Tables[count].Columns.Count; j++)
157                        {
158                            dr[j] = MyDS.Tables[count].Rows[i][j];
159                        }
160                        MyTable.Rows.Add(dr);                        
161                    }                    
162                }
163                CloseConnection(MyConn);
164                return MyTable;
165            }
166            catch (Exception)
167            {
168                return new DataTable();
169            }
170        }
171        //多天查询数据.
172        public static DataTable GetData2(string[] sendTables, string[] statusTables, string user, string domain, string status, string type, int pageindex, int pagesize)
173        {
174            DataTable MyTable ;
175            DataTable MyTable2 = new DataTable();
176            try
177            {
178                //取出所有数据到一张表里.
179                MyTable = new DataTable();
180                MyTable = ExportData2(sendTables, statusTables, user, domain, status, type);
181
182                //拿出(pageindex-1)*pagesize--pageindex*pagesize的数据.
183                MyTable2= MyTable.Clone();
184                for (int i = 0; i < pagesize; i++)
185                {
186                    int index = i + pagesize * (pageindex - 1);
187                    DataRow dr = MyTable2.NewRow();
188                    //MyDS.Tables[0].NewRow();
189                    for (int j = 0; j < MyTable.Columns.Count; j++)
190                    {
191                        dr[j] = MyTable.Rows[index][j];
192                    }
193                    MyTable2.Rows.Add(dr);
194                 }
195                return MyTable2;
196            }
197            catch (Exception)
198            {
199                return new DataTable();
200            }
201        }
202        private static void CloseConnection(MySqlConnection MyConn)
203        {
204            if (MyConn.State == ConnectionState.Open)
205                MyConn.Close();
206        }
207        private static void OpenConnection(MySqlConnection MyConn)
208        {
209            if (MyConn.State == ConnectionState.Closed)
210                MyConn.Open();
211        }
212    }
213
214}
215

 

 

转载于:https://www.cnblogs.com/zhangkun-w/archive/2009/03/11/1408697.html