[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2006-05-03
4
Medium Priority
?
338 Views
Last Modified: 2008-03-10
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?
0
Comment
Question by:jedistar
  • 3
4 Comments
 
LVL 25

Accepted Solution

by:
dstanley9 earned 2000 total points
ID: 16596509
private void LoadDataExamples()
{
    // Example of a SQL Connection using Windows Authentication (via "Integrated Security=SSPI")
    using (SqlConnection conn = new SqlConnection("Integrated Security=SSPI;Data Source=localhost;Initial Catalog=Northwind;"))
    {
        using (SqlCommand command = new SqlCommand("SELECT * FROM products WHERE ProductID = 5",conn))
        {
            // Example using a SqlDataReader
            conn.Open();
            using (SqlDataReader reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    Console.WriteLine("Product ID: {0}   Product Name: {1}", reader["ProductID"], reader["ProductName"]);
                }
            }

            // Example using a SqlDataAdapter
            SqlDataAdapter da = new SqlDataAdapter(command);
            DataSet dataSet = new  DataSet();
            da.Fill(dataSet);


        }
    }

}
0
 
LVL 25

Expert Comment

by:dstanley9
ID: 16596563
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.
0
 

Author Comment

by:jedistar
ID: 16664199
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='';
0
 
LVL 25

Expert Comment

by:dstanley9
ID: 16667512
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.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Najam
Having new technologies does not mean they will completely replace old components.  Recently I had to create WCF that will be called by VB6 component.  Here I will describe what steps one should follow while doing so, please feel free to post any qu…
Introduction Hi all and welcome to my first article on Experts Exchange. A while ago, someone asked me if i could do some tutorials on object oriented programming. I decided to do them on C#. Now you may ask me, why's that? Well, one of the re…
Integration Management Part 2
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Suggested Courses
Course of the Month18 days, 14 hours left to enroll

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question