在一个GridView控件asp.net多个sqlcommands [英] asp.net multiple sqlcommands in one GridView
问题描述
我有问题,在一个GridView中显示多个SQL命令。也许我并不需要两个sqlcommands从两个表中显示,但我不知道该怎么办。
第一个命令是让有两个日期之间的假期所有员工。
我用它来检索ID日期第二个命令。但我不知道如何将它们都绑定一个GridView控件显示为附加的图像。谢谢你在前进。
我得到现在是结果
阿尔贝2016年3月16日结果
阿尔贝2016年3月17日搜索结果
阿尔贝2016年3月18日结果
约翰娜2016年3月17日搜索结果
约翰娜2016年3月18日结果
埃里克2016年3月18日结果
而不是结果的
阿尔贝2016年3月16日,2016年3月17日,2016年3月18日结果
约翰娜2016年3月17日,2016年3月18日结果
埃里克2016年3月18日结果
我觉得我有循环两眼同时说明书,也许一个的SqlCommand之间?结果
我的code是:
使用(SqlConnection的CON =新的SqlConnection(连接))
{
con.Open();
CMD的SqlCommand =新的SqlCommand(SELECT DISTINCT E.EmployeeId,E.FirstName
FROM员工ËINNER JOIN度假v在E.EmployeeId = V.EmployeeId+
WHERE((V.Dates> = @启动和V.Dates< = @End)),CON);
cmd.Parameters.AddWithValue(@开始,(Calendar1.SelectedDates [0])Date.ToShortDateString());
cmd.Parameters.AddWithValue(@终结,(Calendar1.SelectedDates [Calendar1.SelectedDates.Count - 1])Date.ToShortDateString());
使用(SqlDataReader的博士= cmd.ExecuteReader())
{
而(dr.Read())
{ 的Response.Write((DR [1])的ToString()+); // Cheack如果retrivs Employeename
//现在按ID我想要得到的所有日期属于specifik员工的SqlCommand CMD2 =新的SqlCommand(选择休假V V.Dates+
WHERE((V.Dates> = @启动和V.Dates< = @End)),CON);
cmd2.Parameters.AddWithValue(@开始,(Calendar1.SelectedDates [0])Date.ToShortDateString());
cmd2.Parameters.AddWithValue(@终结,(Calendar1.SelectedDates [Calendar1.SelectedDates.Count - 1])Date.ToShortDateString());
cmd2.Parameters.AddWithValue(@雇员,Convert.ToInt32(博士[0]));
使用(SqlDataReader的DR2 = cmd2.ExecuteReader())
{
而(dr2.Read())
{
//Response.Write(Convert.ToDateTime(dr2[0]));
GridView7.DataSource = cmd2.ExecuteReader();
GridView7.DataBind();
} }
的Response.Write(< BR />中);
}
}
con.close();
}
GridView7.DataSource = cmd.ExecuteReader();
GridView7.DataBind();
在FOR XML PATH语法允许您查询组值在一个单一的一种:
SELECT
E.EmployeeId,
E.FirstName,
REPLACE(STUFF((
选择
COALESCE('¶'+ V.Dates,'')
从
假期V
哪里
V.EmployeeId = E.EmployeeId和V.Dates> = @启动和V.Dates< = @End
FOR XML路径('')),1,1,''),'¶',',')AS VacationDates
从
员工Ë
您可以通过,如果你想别的东西代替,
分隔符。
请注意:对不起,多发性编辑。我只是不知道如何连接员工,休假和日期。这件作品code基本上显示了FOR XML路径语法的想法。
I have problem to show multiple sql command in one GridView. Maybe I don't need two sqlcommands to show from two tables but I don't know how to do. The first command is to get all employees that have vacation between two dates. The second command I am using it to retrieve dates by ID. But I don't know how to Bind them both to one GridView to show as attached image. Thank you in advance.
What I get Now is
Albert 2016-03-16
Albert 2016-03-17
Albert 2016-03-18
Johanna 2016-03-17
Johanna 2016-03-18
Eric 2016-03-18
Instead of
Albert 2016-03-16, 2016-03-17, 2016-03-18
Johanna 2016-03-17, 2016-03-18
Eric 2016-03-18
I think I have to loop between two While statment and maybe with one sqlcommand?
My code is:
using (SqlConnection con = new SqlConnection(connection))
{
con.Open();
SqlCommand cmd = new SqlCommand(" SELECT distinct E.EmployeeId, E.FirstName
FROM Employee E INNER JOIN Vacation V ON E.EmployeeId = V.EmployeeId " +
" WHERE ((V.Dates >= @Start AND V.Dates <= @End) ) ", con);
cmd.Parameters.AddWithValue("@Start", (Calendar1.SelectedDates[0]).Date.ToShortDateString());
cmd.Parameters.AddWithValue("@End", (Calendar1.SelectedDates[Calendar1.SelectedDates.Count - 1]).Date.ToShortDateString());
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
Response.Write((dr[1]).ToString() + " "); // Cheack if retrivs Employeename
// Now By Id I want to get all dates belong to specifik employee
SqlCommand cmd2 = new SqlCommand(" SELECT V.Dates FROM Vacation V " +
" WHERE ((V.Dates >= @Start AND V.Dates <= @End) ) ", con);
cmd2.Parameters.AddWithValue("@Start", (Calendar1.SelectedDates[0]).Date.ToShortDateString());
cmd2.Parameters.AddWithValue("@End", (Calendar1.SelectedDates[Calendar1.SelectedDates.Count - 1]).Date.ToShortDateString());
cmd2.Parameters.AddWithValue("@EmployeeId", Convert.ToInt32(dr[0]));
using (SqlDataReader dr2 = cmd2.ExecuteReader())
{
while (dr2.Read())
{
//Response.Write(Convert.ToDateTime(dr2[0]));
GridView7.DataSource = cmd2.ExecuteReader();
GridView7.DataBind();
}
}
Response.Write("<br/>");
}
}
con.close();
}
GridView7.DataSource = cmd.ExecuteReader();
GridView7.DataBind();
The FOR XML PATH syntax allows your query to group several values in a single one:
SELECT
E.EmployeeId,
E.FirstName,
REPLACE(STUFF((
SELECT
COALESCE('¶' + V.Dates, '')
FROM
Vacation V
WHERE
V.EmployeeId = E.EmployeeId AND V.Dates >= @Start AND V.Dates <= @End
FOR XML PATH('')), 1, 1, ''), '¶', ', ') AS VacationDates
FROM
Employee E
You can replace the ', '
separator by something else if you want.
Note: Sorry for the multipe edits. I am just not sure how you connect the employees, vacations and dates. This piece of code basically shows the idea for the FOR XML PATH syntax.
这篇关于在一个GridView控件asp.net多个sqlcommands的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!