asked on 5/3/2006

SQL server, connection, command, reader, adapter etc.

Hi,

How do i create a sqlconnection using windows authentication?
How do i write the sqlcommand to retrieve data from the table "products" where id = 5?
How do i use a data reader to read data from the above?
How do i use a dataadapter to read from table "products", passing it into a Dataset?
How do i write stored procedures using VS.NET 2003 or SQL SERVER?

Lastly, what is a typed dataaset and whats the diff between a normal ds and typed ds?

Last Comment

dstanley9

5/12/2006

ASKER CERTIFIED SOLUTION

dstanley9

5/3/2006

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

dstanley9

5/3/2006

Basic SQL Stored Procedure:

CREATE PROCEDURE GetProduct
@argProductID int
AS

SELECT * FROM Products WHERE ProductID = @argProductID

A Typed DataSet is a class that derives from DataSet that has defenitions for tables and type-safe properties for the columns of each table.

For example, a TypeDataSet for the Northwind database might have the following defenition for the Products table along with a bunch of other code:

public class NorthwindDataSet : DataSet
{

// Lost of DataSet management code
public class Products : DataTable
{
public int ProuctID
{
get
{
//
}
set
{
//
}
}
public string ProductName
{
get
{
//
}
set
{
//
}

}
// other properties for each column
}
}

These classes can be generated by adding a DataSet item to your project and dragging tables into it from the Server Explorer.

jedistar

5/12/2006

ASKER

Thanks, just my first qn, you used:
"Integrated Security=SSPI;Data Source=localhost;Initial Catalog=Northwind;"

can i use this -> Server=localhost;Database=Northwind;Integrated Security=SSPI;

Or must they be used in groups
eg:

Group 1-> "Integrated Security=SSPI;Data Source=localhost;Initial Catalog=Northwind;"
Group 2-> "Server=localhost;Database=Northwind;User ID=sa;Password='';

dstanley9

5/12/2006

For the second syntax, use "Trusted_Connection=True" instead of "Integrated Security=SSPI"

So you can choose:

"Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI;"
- or -
"Server=localhost;Database=Northwind;Trusted_Connection=True;"
(both connection strings produces the same result)

The first syntax is what VS generates when you create a connection, so that's what I typically use.