Well, there are two ways to interact with a SQL Server database in C#. The first is with LINQ, and the second is with the SqlClient library.
LINQ
Ever since .NET 3.0, we've had access to LINQ, which is a pretty impressive ORM and way to deal with collections and lists. There are two different ways that LINQ can work with a database. They are:
Scott Gu has a pretty good tutorial on LINQ to SQL, as well. I'd recommend LINQ to SQL for just getting started, and you can use a lot of that in LINQ to Entities going forward.
A sample select
to grab all customers in New York would be:
var Custs = from c in Customers
where c.State = 'NY'
select c;
foreach(var Cust in Custs)
{
Console.WriteLine(Cust.Name);
}
SqlClient
The traditional C# way to hit a SQL Server database (pre-.NET 3.0) has been via the SqlClient library. Essentially, you create a SqlConnection to open up a connection to the database. If you need help with your connection strings, check out ConnectionStrings.com.
After you've connected to your database, you will use the SqlCommand
object to interact with it. The most important property for this object is the CommandText
. This accepts SQL as its language, and will run raw SQL statements against the database.
If you're doing an insert/update/delete, you will use the ExecuteNonQuery
method of SqlCommand
. However, if you're doing a select, you will use ExecuteReader
and return a SqlDataReader
. You can then iterate through the SqlDataReader to get your results.
The following is the code to grab all customers in New York, again:
using System.Data;
using System.Data.SqlClient;
//...
SqlConnection dbConn = new
SqlConnection("Data Source=localhost;Initial Catalog=MyDB;Integrated Security=SSPI");
SqlCommand dbComm = new SqlCommand();
SqlDataReader dbRead;
dbConn.Open();
dbComm.Connection = dbConn;
dbComm.CommandText = "select name from customers where state = @state";
dbComm.Parameters.Add("@state", System.Data.SqlDbType.VarChar);
dbComm.Parameters["@state"].Value = "NY";
dbRead = dbComm.ExecuteReader();
if(dbRead.HasRows)
{
while(dbRead.Read())
{
Console.WriteLine(dbRead[0].ToString());
}
}
dbRead.Close();
dbConn.Close();
Hopefully this gives you a good intro to what each approach does and how to learn more.