Using multiple tables from a database

[gggram2000]

Hi,
Im having problems writing a query statement to select different columns from different tables to display on an invoice i made on an excel file. The reason i have a problem is because im using c-sharp and coding is a bit complicated at first...if anyone has ran into this problem or may know how to solve it, i would love to hear ur input.
P.S.Im using visual studio 2005 in case the coding may differ

George

[aaronsandoval]

If you are looking to "JOIN" data from different tables you can do this within your stored procedure.

Here is an Example:

CREATE PROCEDURE [User_GetDetails_ByID]
(
@UserID int
)
AS
SELECT
tblA.UserID,
tblA.FavoriteColorID,
tblB.ColorName,
tblA.FirstName,
tblA.LastName
FROM
Users tblA
LEFT JOIN Colors tblB ON tblA.FavoriteColorID = tblB.ColorID
WHERE
tblA.UserID = @UserID
GO
 

Hope this helps, please let me know if you would like further explination of this solution.

- Aaron Sandoval
"remember: There's a thin line between good | Great..."

[Plater]

Hi,
Im having problems writing a query statement to select different columns from different tables to display on an invoice i made on an excel file. The reason i have a problem is because im using c-sharp and coding is a bit complicated at first...if anyone has ran into this problem or may know how to solve it, i would love to hear ur input.
P.S.Im using visual studio 2005 in case the coding may differ

George

You use SQL to query databases, not a programming language like C# or VBNET.
What are you using for your code?

[gggram2000]

You use SQL to query databases, not a programming language like C# or VBNET.
What are you using for your code?

Well I'ved tried inserting sql queries into the commands that are supposed to carry out the procedure like this for example:

SELECT GuestFirstName FROM Guest WHERE ToGuestInvoice > 0

But it gives errors on FROM and WHERE because it doesn't recognize it. If this behind code would work I wouldn't have any problems...maybe it has an assembly or something im missing but I doubt it. Maybe u can enlighten me...Would really appreciate it.

George

[aaronsandoval]

SELECT GuestFirstName FROM Guest WHERE ToGuestInvoice > 0

George

Post your code for this C# method and we may be able to look at the syntax to see if it's incorrect somewhere.

[gggram2000]

Go to Try...right there I'm really kinda lost on how to access rows from two tables in a database...so as later to display it on an outside file.

private void button2_Click(object sender, EventArgs e)
{
//Get configuration settings for the database conection
ResortApplication.conf configdb = new conf();
//this is creating the connection string
string xmldb = configdb.fnGetKeyConfig("xmldb");
//this is creating the database connection
SqlConnection dbConnection = new SqlConnection(xmldb);
//this is creating the database command
SqlCommand myCommand = new SqlCommand();
//this is declaring a transaction variable with sqltransaction which is a default type in .net
SqlTransaction myTrans;
//open connection
dbConnection.Open();
// Begin the transaction.
myTrans = dbConnection.BeginTransaction();
// Assign the connection property.
myCommand.Connection = dbConnection;
// Assign transaction object for a pending local transaction
myCommand.Transaction = myTrans;

try
{

var rows = from e in RESORT.Guest where e.ToGuestInvoice = true select e;
foreach( var e in rows )
{
string name = e.LastName;
int id = e.GuestID;
Console.WriteLine( id + ": " + name );
}

myCommand.ExecuteNonQuery(); //execute a non query
myTrans.Commit(); //commit your transaction
}

[Plater]

A transaction is ment to be of update/insert/delete (I believe) which can only be to one table.
If you are doing a query, you should just use the SqlCommand object.

Take a look at this article:
http://www.thescripts.com/forum/thread635615.html

[gggram2000]

A transaction is ment to be of update/insert/delete (I believe) which can only be to one table.
If you are doing a query, you should just use the SqlCommand object.

Take a look at this article:
http://www.thescripts.com/forum/thread635615.html

Hey thanks Plater...makes sense...so when i use only the sqlcommand object can i use like a join on the behind code for the specific button procedure?

[Plater]

Hey thanks Plater...makes sense...so when i use only the sqlcommand object can i use like a join on the behind code for the specific button procedure?

Aye, you can change the CommandText to any valid SQL statement.

For example:

string sqlq="";
sqlq+=" SELECT ";
sqlq+=" tblA.UserID, ";
sqlq+=" tblA.FavoriteColorID, ";
sqlq+=" tblB.ColorName, ";
sqlq+=" tblA.FirstName, ";
sqlq+=" tblA.LastName ";
sqlq+=" FROM ";
sqlq+=" Users tblA ";
sqlq+=" LEFT JOIN Colors tblB ON tblA.FavoriteColorID = tblB.ColorID ";
sqlq+=" WHERE ";
sqlq+=" tblA.UserID = @UserID ";
sqlCom.CommandText = sqlq;
sqlCom.Parameters.Add("@UserID ", SqlDbType.Int).Value = 12345
 

[gggram2000]

Kool, thanks. Appreciate your help!

[gggram2000]

If you are looking to "JOIN" data from different tables you can do this within your stored procedure.

Here is an Example:

CREATE PROCEDURE [User_GetDetails_ByID]
(
@UserID int
)
AS
SELECT
tblA.UserID,
tblA.FavoriteColorID,
tblB.ColorName,
tblA.FirstName,
tblA.LastName
FROM
Users tblA
LEFT JOIN Colors tblB ON tblA.FavoriteColorID = tblB.ColorID
WHERE
tblA.UserID = @UserID
GO
 

Hope this helps, please let me know if you would like further explination of this solution.

- Aaron Sandoval
"remember: There's a thin line between good | Great..."

Yeah a little further explanation wouldnt' hurt...

[aaronsandoval]

To begin, there are many ways to accomplish this task. This "JOIN" feature in SQL is very useful and can be used in many instances to "SELECT" data from multiple tables. Here is a small example of how we would use a "JOIN" statement and how we would use it with our C# ASP.NET application.

For this example, we have created 1 SQL Stored Procedure, 1 ASP.NET 2.0 Page with C# Code Behind Page. We are using the Northwind Database that is found in any copy of SQL. We are going to bind the results (from dataset) to a GridView control on our page.


SQL STORED PROCEDURE

CREATE PROCEDURE [CustomerOrders_GetAll_ByCountry]
(
@Country nvarchar(15)
)
AS
SELECT
tblA.OrderID,
tblB.ContactName,
tblB.CompanyName,
tblA.ShippedDate
FROM
Orders tblA
LEFT JOIN Customers tblB ON tblA.CustomerID = tblB.CustomerID
WHERE
tblA.ShipCountry = @Country
GO
 

NOTE: I personally use "SQL Helper" class which can be found on Microsofts website when you download DAAB (Data Access Application Block) for 2.0. This is a very easy way for you to work with your database and use Stored Procedures in your code. Another great way would be to use strongly-typed datasets to access this data.

IMPORTANT: If you want to use this SQL Helper class don't forget to add it into the App_Code folder in your project.


ASP.NET 2.0 HTML CODE

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="Default" %>
 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
<title>GridView Example</title>
</head>
<body>
<form id="form1" runat="server">
<asp:ScriptManager ID="ScriptManager1" runat="server" />
<div>
<asp:GridView ID="GridView1" runat="server" CellPadding="4" ForeColor="#333333" GridLines="None" AllowPaging="True" PageSize="10" OnPageIndexChanging="GridView1_PageIndexChanging">
<FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
<EditRowStyle BackColor="#999999" />
<SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
<PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
<HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<AlternatingRowStyle BackColor="White" ForeColor="#284775" />
</asp:GridView>
&nbsp;</div>
</form>
</body>
</html>
 

ASP.NET 2.0 C# CODE BEHIND PAGE

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
// DON'T FORGET TO ADD THIS DataAccessLayer NAMESPACE SO YOU CAN USE THE SQLHelper CLASS
using DataAccessLayer;
 
public partial class Default : System.Web.UI.Page
{
// OF COURSE THIS SHOULD BE IN YOUR WEB.CONFIG FILE & ENCRYPTED
string conString = "Server=YourServerName;Database=Northwind;User ID=MyUserName;Password=MyUserPassword;";
 
protected void Page_Load(object sender, EventArgs e)
{
Bind_GridView1();
}
 
public void Bind_GridView1()
{
// we are going to set the country equal to USA
string country = "USA";
DataSet ds = new DataSet();
ds = SqlHelper.ExecuteDataset(conString, "CustomerOrders_GetAll_ByCountry", country);
GridView1.DataSource = ds;
GridView1.DataBind();
// THIS WOULD BE ONE WAY YOU COULD IMPLIMENT THE FOREACH STATEMENT WITH THE RETURNED DATA
foreach(DataRow aRow in ds.Tables[0].Rows)
{
Response.Write(aRow["ContactName"].ToString() + "<br />");
}
}
 
public void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
GridView1.PageIndex = e.NewPageIndex;
Bind_GridView1();
}
 
}
 

CONCLUSION:
You can create a new ASP.NET 2.0 C# WebSite and add this code into a page called "Default.aspx". Don't forget to add in the stored procedure into your Northwind Database, so you can successfully run example.

I hope this code helps. You used the "foreach" loop statement in your code and in this Bind method it writes out the data on the page as well as binds to the gridview control.


- Aaron Sandoval
"remember: Greatness can easily be achieved, as long as you never stop wanting to learn from others..."

[gggram2000]

Thanks Aaron, your code really helped. Appreciate the assistance.