先说一下需求:ASP.NET+MYSQL
对数据做点说明:
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