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

topics to look at:

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

Answer 2

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 3

If it is a web application here are some good resources for getting started with data access in .NET:

http://weblogs.asp.net/scottgu/archive/2007/04/14/working-with-data-in-asp-net-2-0.aspx

Answer 4

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 5

To connect/perform operations on an SQL server db:

using System.Data;
using System.Data.SqlClient;

string connString = "Data Source=...";
SqlConnection conn = new SqlConnection(connString); // you can also use ConnectionStringBuilder
connection.Open();

string sql = "..."; // your SQL query
SqlCommand command = new SqlCommand(sql, conn);

// if you're interested in reading from a database use one of the following methods

// method 1
SqlDataReader reader = command.ExecuteReader();

while (reader.Read()) {
    object someValue = reader.GetValue(0); // GetValue takes one parameter -- the column index
}

// make sure you close the reader when you're done
reader.Close();

// method 2
DataTable table;
SqlDataAdapter adapter = new SqlDataAdapter(command);
adapter.Fill(table);

// then work with the table as you would normally

// when you're done
connection.Close();

Most other database servers like MySQL and PostgreSQL have similar interfaces for connection and manipulation.

Answer 6

If what you are looking for is an easy to follow tutorial, then you should head over to the www.ASP.net website.

Here is a link to the starter video page: http://www.asp.net/learn/videos/video-49.aspx

Here is the video if you want to download it: video download

and here is a link to the C# project from the video: download project

Good luck.

Answer 7

I would also recommend using DataSets. They are really easy to use, just few mouse clicks, without writing any code and good enough for small apps.

Answer 8

If you have Visual Studio 2008 I would recommend skipping ADO.NET and leaping right in to LINQ to SQL

Answer 9

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 10

@J D OConal is basically right, but you need to make sure that you dispose of your connections:

string connString = "Data Source=...";
string sql = "..."; // your SQL query

//this using block
using( SqlConnection conn = new SqlConnection(connString) )
using( SqlCommand command = new SqlCommand(sql, conn) )
{
    connection.Open();

    // if you're interested in reading from a database use one of the following methods

    // method 1
    SqlDataReader reader = command.ExecuteReader();

    while (reader.Read()) {
        object someValue = reader.GetValue(0); // GetValue takes one parameter -- the column index
    }

    // make sure you close the reader when you're done
    reader.Close();

    // method 2
    DataTable table;
    SqlDataAdapter adapter = new SqlDataAdapter(command);
    adapter.Fill(table);

    // then work with the table as you would normally

    // when you're done
    connection.Close();
}