Full-house

private static void DemonstrateMergeTable()
{
DataTable table1 = new DataTable(“Items”);

// Add columns
DataColumn column1 = new DataColumn(“id”, typeof(System.Int32));
DataColumn column2 = new DataColumn(“item”, typeof(System.Int32));
table1.Columns.Add(column1);
table1.Columns.Add(column2);

// Set the primary key column.
table1.PrimaryKey = new DataColumn[] { column1 };

// Add RowChanged event handler for the table.
table1.RowChanged +=
new System.Data.DataRowChangeEventHandler(Row_Changed);

// Add some rows.
DataRow row;
for (int i = 0; i <= 3; i++)
{
row = table1.NewRow();
row["id"] = i;
row["item"] = i;
table1.Rows.Add(row);
}

// Accept changes.
table1.AcceptChanges();
PrintValues(table1, “Original values”);

// Create a second DataTable identical to the first.
DataTable table2 = table1.Clone();

// Add three rows. Note that the id column can’t be the
// same as existing rows in the original table.
row = table2.NewRow();
row["id"] = 14;
row["item"] = 774;
table2.Rows.Add(row);

row = table2.NewRow();
row["id"] = 12;
row["item"] = 555;
table2.Rows.Add(row);

row = table2.NewRow();
row["id"] = 13;
row["item"] = 665;
table2.Rows.Add(row);

// Merge table2 into the table1.
Console.WriteLine(“Merging”);
table1.Merge(table2);
PrintValues(table1, “Merged With table1″);

}

private static void Row_Changed(object sender,
DataRowChangeEventArgs e)
{
Console.WriteLine(“Row changed {0}\t{1}”,
e.Action, e.Row.ItemArray[0]);
}

private static void PrintValues(DataTable table, string label)
{
// Display the values in the supplied DataTable:
Console.WriteLine(label);
foreach (DataRow row in table.Rows)
{
foreach (DataColumn col in table.Columns)
{
Console.Write(“\t ” + row[col].ToString());
}
Console.WriteLine();
}
}

我是这样使用的，请看：

void bind()
{
BLL.Ts_Planning bll = new BLL.Ts_Planning();
Capsugel.BLL.Users bll2 = new BLL.Users();
DataTable dt = bll2.GetTsList().Tables[0];
DataSet ds = new DataSet();
DataTable dt2;
dt2 = bll.GetListByUid_All_Planning_Count(dt.Rows[0]["user id"].ToString(), TextBox2.Text.ToString(), TextBox3.Text.ToString()).Tables[0];
for (int i = 1; i < dt.Rows.Count – 1; i++)
{
dt2.Merge(bll.GetListByUid_All_Planning_Count(dt.Rows[i]["user id"].ToString(), TextBox2.Text.ToString(), TextBox3.Text.ToString()).Tables[0]);
}
GridView1.DataSource = dt2;
GridView1.DataBind();
}

下面是存储过程，有没有高人来指点一下，虽然问题已解决，谢谢DataTable的Merge :-)，但是好像很烦：

ALTER PROCEDURE [dbo].[Ts_Planning_Count]
@Uid varchar(20),
@date1 smalldatetime,
@date2 smalldatetime

AS

Select U.Name,AAAAA.* From
[Users] U,
(Select * From
(Select count(Pid) as [Explor/Penetra] from Ts_Planning
where
Purpose = ‘Explor/Penetra’ and UID=@Uid and (Date between @date1 and @date2)) A,

(Select count(Pid) as [Relation buliding] from Ts_Planning
where
Purpose = ‘Relation buliding’ and UID=@Uid and (Date between @date1 and @date2)) B,

(Select count(Pid) as [Prevent service] from Ts_Planning
where
Purpose = ‘Prevent service’ and UID=@Uid and (Date between @date1 and @date2)) C,

(Select count(Pid) as [Complaint handing] from Ts_Planning
where
Purpose = ‘Complaint handing’ and UID=@Uid and (Date between @date1 and @date2)) D,

(Select count(Pid) as [Reactive service] from Ts_Planning
where
Purpose = ‘Reactive service’ and UID=@Uid and (Date between @date1 and @date2)) E,

(Select count(Pid) as [Trial run] from Ts_Planning
where
Purpose = ‘Trial run’ and UID=@Uid and (Date between @date1 and @date2)) F,

(Select count(Pid) as [Key] from Ts_Planning
where
Class = ‘Key’ and UID=@Uid and (Date between @date1 and @date2)) AA,

(Select count(Pid) as [Core] from Ts_Planning
where
Class = ‘Core’ and UID=@Uid and (Date between @date1 and @date2)) BB,

(Select count(Pid) as [Target] from Ts_Planning
where
Class = ‘Target’ and UID=@Uid and (Date between @date1 and @date2)) CC,

(Select count(Pid) as [Potential] from Ts_Planning
where
Class = ‘Potential’ and UID=@Uid and (Date between @date1 and @date2)) DD,

(Select count(Pid) as [WM] from Ts_Planning
where
Segment = ‘WM’ and UID=@Uid and (Date between @date1 and @date2)) AAA,

(Select count(Pid) as [TCM] from Ts_Planning
where
Segment = ‘TCM’ and UID=@Uid and (Date between @date1 and @date2)) BBB,

(Select count(Pid) as [DS] from Ts_Planning
where
Segment = ‘DS’ and UID=@Uid and (Date between @date1 and @date2)) CCC,

(Select count(Pid) as Visit from Ts_Planning
where
Activity = ‘Visit’ and UID=@Uid and (Date between @date1 and @date2)) AAAA,

(Select count(Pid) as Travel from Ts_Planning
where
Activity = ‘Travel’ and UID=@Uid and (Date between @date1 and @date2)) BBBB,

(Select count(Pid) as [O/HO] from Ts_Planning
where
Activity = ‘O/HO’ and UID=@Uid and (Date between @date1 and @date2)) CCCC,

(Select count(Pid) as [Meeting/Training] from Ts_Planning
where
Activity = ‘Meeting/Training’ and UID=@Uid and (Date between @date1 and @date2)) DDDD,

(Select count(Pid) as [CFM&Expo] from Ts_Planning
where
Activity = ‘CFM&Expo’ and UID=@Uid and (Date between @date1 and @date2)) EEEE,

(Select count(Pid) as [H&V] from Ts_Planning
where
Activity = ‘H&V’ and UID=@Uid and (Date between @date1 and @date2)) FFFF,

(Select SUM(ActivityTimes) as [ActivityTimes] from Ts_Planning
where
UID=@Uid and (Date between @date1 and @date2)) AAAAA
) AAAAA
where U.Uid = @Uid

void bind()
{
DateTime dt1 = Convert.ToDateTime(this.DropDownList1.SelectedValue + “-” + this.DropDownList2.SelectedValue + “-1″);
DateTime dt2 = Convert.ToDateTime(this.DropDownList3.SelectedValue + “-” + this.DropDownList4.SelectedValue + “-1″);
int i = 0;
int j = 0;
j = Convert.ToInt32(DB.GetFirstValue(“Select Datediff(m,’” + dt1 + “‘,’” + dt2 + “‘) AS J;”, DB.connectionString)) + 1;
DataTable dt;
‘定义每一列的属性
DataTable Dt = new DataTable(“GetSummary”);
Dt.Columns.Add(“Date”, Type.GetType(“System.String”));
Dt.Columns.Add(“SORTING100″, Type.GetType(“System.Int32″));
Dt.Columns.Add(“HCMNOBYPASS”, Type.GetType(“System.Int32″));
Dt.Columns.Add(“PCMNOBYPASS”, Type.GetType(“System.Int32″));
Dt.Columns.Add(“REWORK”, Type.GetType(“System.Int32″));
Dt.Columns.Add(“VCAPS”, Type.GetType(“System.Int32″));
Dt.Columns.Add(“SUM”, Type.GetType(“System.Int32″));
Dt.Columns.Add(“Total”, Type.GetType(“System.Int32″));
Dt.Columns.Add(“Percent”, Type.GetType(“System.String”));
Dt.Columns.Add(“HCMNOBYPASSCC”, Type.GetType(“System.Int32″));
Dt.Columns.Add(“PCMNOBYPASSCC”, Type.GetType(“System.Int32″));
‘填充
for (i = 0; i < j; i++)
{
string date = dt1.AddMonths(i).ToShortDateString();
string tsql = “SELECT Count(f_LC_LotUID) AS Total FROM [t_Rel_LotCar] where Datediff(m,f_LC_ProdCalDate,’” + date + “‘)=0 and f_LC_CarStatus=’ST01/OK’”;

dt = NoPrintClass.GetGetSummary(date);
‘将从数据库中得到的表的各个列的值，填充到我们定义的变量Dt中，注意这里的 Dt 和 dt 是不一样的。这里是C#程序
Dt.Rows.Add(new object[] { dt.Rows[0][0], dt.Rows[0][1], dt.Rows[0][2], dt.Rows[0][3], dt.Rows[0][4], dt.Rows[0][5], dt.Rows[0][8], Convert.ToInt32(DB.GetFirstValue(tsql, DB.connWIP)), ‘2′, dt.Rows[0][6], dt.Rows[0][7] });
GridView1.DataSource = Dt;
GridView1.DataBind();
}
}

时间较紧，没有写注释，业内人应该都可以看懂