本文意在说明一下DataSet.Merge(Table)和多个Table合并到一个里面的操作.因为是新手,代码肯定有 很多问题,欢迎大家批评指正. 如果你有更好的解决方案,请告诉我.谢谢.
先说一下需求:ASP.NET+MYSQL
查询连续N天的邮件发送日志.在库里,表是按天存放的,表名也是按天命名的.例 如:send20090302,status20090302,两张表有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,
|
