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 basic tutorials on connecting with and using a db from a C# console program. I haven't been able to find anything basic enough yet and I hope people here can help me find the info I need. I've read the material on MSDN, but MSDN assumes basic knowledge about these things that I am still looking for.

I have created a db within VS Express in my project, created tables, and written some starter records into the tables. I'm trying to find out exactly what each of these things is, and how to determine how to apply them in my project:

SQLConnection

SQLConnection class

SQLCommand

SQLDataAdapter

DataSets

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

-- Edit:

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

-- Edit:

SqlConnection

The thing through which you communicate to the database. This will hold the name of the server, the username, password, and other misc things.

SqlCommand

Something that holds the sql statement you want to send to the server. This may be an 'update' or 'insert' or 'select' or anything. Depending on what it is, you use a different method to execute it, to possible get data back.

SqlDataAdapter

A strange one; it's used specifically to fill a 'DataSet'. It basically does a bit of work for you, adding the information it finds to the set.

DataSet

Not sure how simple you want this. It's just a collection of returned data, in a table-like format, that you can iterate over. It contains DataTables, because some queries can return more than one table. Typically, though, you'll only have one table, and you can bind to it, or whatever.

Answer 2

Create a sqlconnection, Open it, Create a sqlcommand, execute it to get a sqldatareader, voila. You won't need a dataadapter for a simple example.

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