是否可以将数据库中两个或多个表的数据链接到一个数据网格视图？ [英] Is it possible to link data from two or more tables in a database to one data grid view?
问题描述
大家好，
基本上我想将Visual Studio 2010中数据库中两个表的数据显示到一个数据网格视图中。只是我项目的简要背景 - 这是一个用户可以上传内容（图像和视频）的网站。
我有三张桌子 - 一张桌子'事件'存储内容的文件路径和文件名，上传日期和时间以及用户有哪些上传了它。
另一个表User_Acc存储用户的用户名，电子邮件和密码。
第三表业务存储有关用户所属的特定业务/组织的基本信息。
但是事件表中的用户是ID的形式，而我想显示而是来自User_Acc表的用户名。同样，Business在事件表中以ID的形式显示，而我想在Business Table中显示Business_Name。
这是我的代码：
Welcome.aspx：
Hi everyone,
Basically I want to display data from two tables in my database within Visual studio 2010 into one data gridview. Just a brief background to my project - it is a website where users can upload content(Images and video).
I have three tables - one table 'Incident' stores the file path of the content and name of the file, the date and time it was uploaded and what user has uploaded it.
The other table User_Acc stores the usernames,emails and passwords of users.
The third table Business stores basic information about the particular business/organisation the user belongs to.
However the user in the Incident Table is in the form of an ID whereas I want to display the 'Username' from the User_Acc table instead. Similarly the Business is in the form of an ID in the Incident Table whereas I want to display the Business_Name from the Business Table.
Here is my code:
Welcome.aspx:
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" EmptyDataText = "No files uploaded">
<Columns>
<asp:BoundField DataField = "Incident_ID" HeaderText = "Incident_ID" />
<asp:BoundField DataField = "Description" HeaderText = "Description" />
<asp:ImageField DataImageUrlField="File_Path" ControlStyle-Width="100"
ControlStyle-Height = "100" HeaderText = "Preview Image"/>
</Columns>
</asp:GridView>
Welcome.aspx.cs
protected void Page_Load（object sender，EventArgs e）
{
Label1.Text =Welcome+ Session [User];
Label2.Text =Business：+ Session [BusinessName];
SqlConnection conn;
SqlCommand comm;
String connectionString = ConfigurationManager.ConnectionStrings [ApplicationServices]。ConnectionString;
conn = new SqlConnection（connectionString）;
comm = new SqlCommand（SELECT * from Business a，User_Acc c where c.Username = @Username AND a.Business_ID = c.Business_ID，conn）;
comm.Parameters.Add（@ Username，System.Data.SqlDbType.VarChar）.Value = Session [User];
conn.Open（）;
SqlDataReader reader = comm.ExecuteReader（）;
while（reader.Read（））
{
lblOwner.Text + = reader [Owner_Name];
lblBusiness.Text + = reader [Business_Name];
lblAddress.Text + = reader [Address_Line_1] + - + reader [Address_Line_2] + - + reader [Address_Line_3];
lblProvince.Text + =读者[普罗旺斯];
lblCounty.Text + =读者[县];
lblVATNo.Text + =读者[Vat_No];
lblEmail.Text + =读者[电子邮件];
lblTelephone.Text + =读者[电话];
}
SqlConnection connWrite;
connWrite = new SqlConnection（connectionString）;
DataTable dt = new DataTable（）;
SqlDataAdapter sda = new SqlDataAdapter（）;
using（SqlCommand comm1 = new SqlCommand（通过Incident_ID选择* FROM事件顺序，connWrite））
尝试
{
connWrite.Open（）;
sda.SelectCommand = comm1;
sda.Fill（dt）;
GridView1.DataSource = dt;
GridView1.DataBind（） ;
}
catch（例外情况）
{
Response.Write（ex.Message）; < br $>
}
终于
{
connWrite.Close（）;
sda。 Dispose（）;
connWrite.Dispose（）;
}
reader.Close（）;
conn.Close（）;
}
}
Welcome.aspx.cs
protected void Page_Load(object sender, EventArgs e)
{
Label1.Text = "Welcome " + Session["User"];
Label2.Text = "Business: " + Session["BusinessName"];
SqlConnection conn;
SqlCommand comm;
String connectionString = ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString;
conn = new SqlConnection(connectionString);
comm = new SqlCommand("SELECT * from Business a, User_Acc c Where c.Username = @Username AND a.Business_ID = c.Business_ID", conn);
comm.Parameters.Add("@Username", System.Data.SqlDbType.VarChar).Value = Session["User"];
conn.Open();
SqlDataReader reader = comm.ExecuteReader();
while (reader.Read())
{
lblOwner.Text += reader["Owner_Name"];
lblBusiness.Text += reader["Business_Name"];
lblAddress.Text += reader["Address_Line_1"] + " - " + reader["Address_Line_2"] + " - " + reader["Address_Line_3"];
lblProvince.Text += reader["Provence"];
lblCounty.Text += reader["County"];
lblVATNo.Text += reader["Vat_No"];
lblEmail.Text += reader["Email"];
lblTelephone.Text += reader["Telephone"];
}
SqlConnection connWrite;
connWrite = new SqlConnection(connectionString);
DataTable dt = new DataTable();
SqlDataAdapter sda = new SqlDataAdapter();
using (SqlCommand comm1 = new SqlCommand("SELECT * FROM Incident Order by Incident_ID", connWrite))
try
{
connWrite.Open();
sda.SelectCommand = comm1;
sda.Fill(dt);
GridView1.DataSource = dt;
GridView1.DataBind();
}
catch (Exception ex)
{
Response.Write(ex.Message);
}
finally
{
connWrite.Close();
sda.Dispose();
connWrite.Dispose();
}
reader.Close();
conn.Close();
}
}
推荐答案
这里有几个选项 -
1.如果你有链接在表之间，使用带有连接的查询并创建一个数据表以在一个网格视图中查看它
2.如果表之间没有任何关系，我认为没有任何理由可以使用一个网格视图显示所有3个表。在顶部创建一个链接并使用链接随机播放视图。
或者只显示3个表格的3个网格。
祝你好运！
干杯
Sandip
You have several options here -
1. If you have links between the tables, use a query with join and make a one datatable to view it in one grid view
2. If there are no relations at all between tables, I don't see any reason to use one grid view to show all the 3 tables. Create a link at the top and shuffle the views using the link.
Or simply show 3 grids for 3 tables.
Good Luck!
Cheers
Sandip
我想你要显示用户名（在user_acc表中可用） ）和业务名称（在业务表中可用）沿着一个gridview中事件表的侧面相关数据。假设您在事件表中有user_id字段和business_id字段，那么您必须加入这样的3个表：
I figured that you want to display username (available in user_acc table) and business name (available in business table) along side related data from incident table in one gridview. then you got to join the 3 tables like this, assuming that you have a user_id field and business_id field in the incident table:
SELECT user_acc.username, business.business_name, [other fields] FROM
user_acc JOIN incident ON user_acc.id=incident.user_id
JOIN business ON incident.business_id=business.id
WHERE condition...
至于参数化查询的构造，请查看： SQL注入和参数化查询 [ ^ ]
这篇关于是否可以将数据库中两个或多个表的数据链接到一个数据网格视图？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
