How do I access a database in C#


How do I access a database in C#



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.




Sanitizing Database Return Data

1:



Reloading configuration without restarting application using ConfigurationManager.RefreshSection


Issue with localizing html title of a page


In a layered design with a separate DataAccess layer in .NET where should connection string be managed?

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..


3:


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 :).


4:


topics to look at:.
  1. ADO.NET basics
  2. LINQ to SQL
  3. Managed database providers


5:


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.


6:


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..


7:


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.

.


8:


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..


9:


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


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(); } 



82 out of 100 based on 82 user ratings 1282 reviews