Very basic database concepts in C #

Question

I am writing a console program in C # and I need to use a database.

I am looking for very simple lessons on connecting and using db from the C # command console. I have not yet been able to find anything enough, and I hope that the people here will help me find the information I need. I read the material on MSDN, but MSDN assumes a basic knowledge of these things that I am still looking for.

I created db in VS Express in my project, created tables and wrote some start records to tables. I am trying to figure out what each of these things is, and how to determine how to apply them in my project:

SQLConnection

SQLConnection Class

SQLCommand

SqlDataAdapter

Dataset

Thanks.

Answer 1

Something like:

using System.Data; using System.Data.SqlClient; using(SqlConnection connection = new SqlConnection("")){ SqlCommand command = new SqlCommand(@" insert into tblFoo ( col1, col2 ) values ( @val1, @val2 )", connection ); SqlParameter param = new SqlParameter("@val1", SqlDbType.NVarChar); param.Value = "hello"; command.Parameters.Add(param); param = new SqlParameter("@val2", SqlDbType.NVarChar); param.Value = "there"; command.Parameters.Add(param); command.ExecuteNonQuery(); connection.Close(); } 

- Change:

Although, of course, when you start doing serious things, I recommend ORM. I use LLBLGen (it costs money, but definitely worth it).

- Change:

SqlConnection

The thing you are contacting the database with. This will contain the name of the server, username, password and other various things.

Sqlcommand

Something that contains the sql statement that you want to send to the server. It could be “update” or “insert” or “choice” or something else. Depending on what it is, you use a different method to execute it to get the data back.

SqlDataAdapter

Strange; it is specifically used to populate the "DataSet". It basically does a bit of work for you, adding the information that it finds into the set.

Dataset

Not sure how simple you want it. This is just a set of returned data in a table format that you can iterate over. It contains DataTables, because some queries can return more than one table. Usually, however, you will only have one table, and you can bind to it or something else.

Answer 2

Create sqlconnection, open it, create sqlcommand, run it to get sqldatareader, voila. For a simple example, you do not need a dataadapter.

 string connectionString = "..."; using (SqlConnection conn = new SqlConnection(connectionString)) { conn.Open(); string sql = "select field from mytable"; SqlCommand cmd = new SqlCommand(sql, conn); SqlDataReader rdr = cmd.ExecuteReader(); while (rdr.Read()) { Console.WriteLine(rdr[0]); } } 

Answer 3

There's an ADO.NET tutorial that covers many of the things you are looking for at http://www.csharp-station.com/Tutorials/AdoDotNet/Lesson01.aspx . Lesson 1 is basically the background, but lesson 2 onwards goes through SQL client objects.

Another tutorial at http://www.codeproject.com/KB/database/sql_in_csharp.aspx covers some of the basics (SqlConnection, SqlCommand).

Answer 4

I bought a book called Pragmatic ADO.NET .

Answer 5

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

Since the release of .NET 3.0, we have had access to LINQ, which is a pretty impressive ORM and way to handle collections and lists. There are two different ways that LINQ can work with a database. It:

• LINQ to SQL
• LINQ to Entities

Scott Gu has a good LINQ to SQL tutorial. I would recommend LINQ to SQL just for starters, and you can use a lot of LINQ to Entities in the future.

Sample select to capture all clients in New York:

 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 access the SQL Server database (pre-.NET 3.0) was through the SqlClient library . Essentially, you create an SqlConnection to open a database connection. If you need help with connection strings, check ConnectionStrings.com .

After you connect to your database, you will use the SqlCommand object to interact with it. The most important property for this object is CommandText . It accepts SQL as its language and will run raw SQL statements in the database.

If you insert / update / delete, you use the ExecuteNonQuery SqlCommand method. However, if you make a choice, you will use ExecuteReader and return SqlDataReader . You can then iterate through SqlDataReader to get the results.

Below is the code to capture 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(); 

Hope this gives you a good idea of ​​what each approach does and how to learn more.

Answer 6

In general, I recommend using the Microsoft Enterprise Library to access the database. I used it in several projects and really love it.

See Microsoft Quick Access to Help Get You Started

In addition, I am also used to writing extension methods to retrieve data from DataRows. For example, I can do something like this:

  //Create an extension method, Value, //to extract a certain type from a DataRow, //supplying a default value to be used if DbNull.Value is encountered DateTime someDateValue = dr["SomeDatabaseField"].Value(new DateTime()); 

Hope this helps!

Answer 7

See ADO.NET Sample Application

Examples cover

Sqlclient

 using System; using System.Data; using System.Data.SqlClient; class Sample { public static void Main() { SqlConnection nwindConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind"); SqlCommand catCMD = nwindConn.CreateCommand(); catCMD.CommandText = "SELECT CategoryID, CategoryName FROM Categories"; nwindConn.Open(); SqlDataReader myReader = catCMD.ExecuteReader(); while (myReader.Read()) { Console.WriteLine("\t{0}\t{1}", myReader.GetInt32(0), myReader.GetString(1)); } myReader.Close(); nwindConn.Close(); } } 

Oledb

 using System; using System.Data; using System.Data.OleDb; class Sample { public static void Main() { OleDbConnection nwindConn = new OleDbConnection("Provider=SQLOLEDB;Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind"); OleDbCommand catCMD = nwindConn.CreateCommand(); catCMD.CommandText = "SELECT CategoryID, CategoryName FROM Categories"; nwindConn.Open(); OleDbDataReader myReader = catCMD.ExecuteReader(); while (myReader.Read()) { Console.WriteLine("\t{0}\t{1}", myReader.GetInt32(0), myReader.GetString(1)); } myReader.Close(); nwindConn.Close(); } } 

Odbc

 using System; using System.Data; using System.Data.Odbc; class Sample { public static void Main() { OdbcConnection nwindConn = new OdbcConnection("Driver={SQL Server};Server=localhost;" + "Trusted_Connection=yes;Database=northwind"); OdbcCommand catCMD = new OdbcCommand("SELECT CategoryID, CategoryName FROM Categories", nwindConn); nwindConn.Open(); OdbcDataReader myReader = catCMD.ExecuteReader(); while (myReader.Read()) { Console.WriteLine("\t{0}\t{1}", myReader.GetInt32(0), myReader.GetString(1)); } myReader.Close(); nwindConn.Close(); } }