How to access a database in C #

Question

Basically, I would like a short explanation of how I can access the SQL database in C # code. I understand that a connection and a command are required, but what happens? I guess I ask someone to puzzle the process a bit. Thanks.

For clarity, in my case I am doing web applications, things are e-commerce. All ASP.NET, C #, and SQL Databases.

I am going to go and close this topic. This is a little for general, and I am going to post some more sensitive and educational questions and answers on this.

Answer 1

Reads as a beginner's question. This is required for beginner video demos.

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

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

Answer 2

MSDN has a nice entry here:

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

You should take a look at a data reader for simple sample statements. Sample on 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 first creates an SqlConnection object, and then creates an SqlCommand object that contains the actual selection you are going to make, and a link to the connection you just created. Then it opens the connection and on the next line executes your statements and returns the SqlDataReader object.

In a while loop, it returns the values ​​from the first line in the reader. Each time "reader.Read ()" is called, the reader will contain a new line.

Then the reader then closes, and since we exit the “use” second, the connection also closes.

---

EDIT: if you are looking for information on selecting / updating data in ASP.NET, 4GuysFromRolla has a very nice Multipart series according to ASP.NET 2.0 source controls

EDIT2: As others have pointed out, if you are using a newer version of .NET, I would recommend looking at LINQ. Introduction, samples and notes can be found on this MSDN page .

Answer 3

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

Getting started with linq is ridiculously easy.

• Add a new linq-to-sql element to the project that will be placed in your App_Code folder (in this example we will call this example.dbml )
• from your server explorer, drag the table from your database to dbml (the table will be called items in this example)
• save dbml file

Now you have created several classes. You created the exampleDataContext class, which is your linq initializer, and you created the item class, which is the class for objects in the items table. All this is done automatically, and you do not need to worry about it. Now say that I want to get an entry with itemID of 3, that’s 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 to get some information from the item , you simply call it like this:

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

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

No need to learn legacy ADO when you have a more powerful and simpler tool at your disposal :)

Answer 4

topics to view:

• ADO.NET Basics
• LINQ to SQL
• Managed Database Providers

Answer 5

If this is a web application, here are some useful 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 6

To connect / perform operations on db SQL server:

 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, such as MySQL and PostgreSQL, have similar interfaces for connecting and manipulating.

Answer 7

If what you are looking for is easy to follow the course, then you should go to www.ASP.net.

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

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

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

Good luck.

Answer 8

I would also recommend using DataSets. They are very easy to use, just a few clicks, no code writing and good enough for small applications.

Answer 9

If you have Visual Studio 2008, I would recommend skipping ADO.NET and going straight to LINQ to SQL

Answer 10

@JD OConal is basically right, but you need to make sure that you delete 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(); }