How do I access a database in C#

Question

Basically, I would like a brief explanation of how I can access a SQL database in C# code. I gather that a connection and a command is required, but what's going on? I guess what I'm asking is for someone to de-mystify the process a bit. Thanks.

For clarity, in my case I'm doing web apps, e-commerce stuff. It's all ASP.NET, C#, and SQL databases.

I'm going to go ahead and close this thread. It's a little to general and I am going to post some more pointed and tutorial-esque questions and answers on the subject.

Answer 1

MSDN has a pretty good writeup here:

http://msdn.microsoft.com/en-us/library/s7ee2dwt(VS.71).aspx

You should take a look at the data-reader for simple select-statements. Sample from the MSDN page:

private static void ReadOrderData(string connectionString)
{
    string queryString = 
        "SELECT OrderID, CustomerID FROM dbo.Orders;";
    using (SqlConnection connection = new SqlConnection(
               connectionString))
    {
        SqlCommand command = new SqlCommand(
            queryString, connection);
        connection.Open();
        SqlDataReader reader = command.ExecuteReader();
        try
        {
            while (reader.Read())
            {
                Console.WriteLine(String.Format("{0}, {1}",
                    reader[0], reader[1]));
            }
        }
        finally
        {
            // Always call Close when done reading.
            reader.Close();
        }
    }
}

It basicly first creates a SqlConnection object and then creates the SqlCommand-object that holds the actual select you are going to do, and a reference to the connection we just created. Then it opens the connection and on the next line, executes your statements and returns a SqlDataReader object.

In the while-loop it then outputs the values from the first row in the reader. Every time "reader.Read()" is called the reader will contain a new row.

Then the reader is then closed, and because we are exiting the "using"-secret, the connection is also closed.

---

EDIT: If you are looking for info on selecting/updating data in ASP.NET, 4GuysFromRolla has a very nice Multipart Series on ASP.NET 2.0's Data Source Controls

EDIT2: As others have pointed out, if you are using a newer version of .NET i would recommend looking into LINQ. An introduction, samples and writeup can be found on this MSDN page.

Answer 2

The old ADO.Net (sqlConnection, etc.) is a dinosaur with the advent of LINQ. LINQ requires .Net 3.5, but is backwards compatible with all .Net 2.0+ and Visual Studio 2005, etc.

To start with linq is ridiculously easy.

• Add a new item to your project, a linq-to-sql file, this will be placed in your App_Code folder (for this example, we'll call it example.dbml)
• from your server explorer, drag a table from your database into the dbml (the table will be named items in this example)
• save the dbml file

You now have built a few classes. You built the exampleDataContext class, which is your linq initializer, and you built the item class which is a class for objects in the items table. This is all done automatically and you don't need to worry about it. Now say I want to get record with the itemID of 3, this is all I need to do:

exampleDataContext db = new exampleDataContext(); // initializes your linq-to-sql
item item_I_want = (from i in db.items where i.itemID == 3 select i).First(); // using the 'item' class your dbml made

And that's all it takes. Now you have a new item named item_I_want... now, if you want some information from the item you just call it like this:

int intID = item_I_want.itemID;
string itemName = item_I_want.name;

Linq is very simple to use! And this is just the tip of the iceberg.

No need to learn antiquated ADO when you have a more powerful, easier tool at your disposal :)

Answer 3

Reads like a beginner question. That calls for beginner video demos.

http://www.asp.net/learn/data-videos/

They are ASP.NET focused, but pay attention to the database aspects.

Answer 4

topics to look at:

1. ADO.NET basics
2. LINQ to SQL
3. Managed database providers