views:

282

answers:

7
+2  A: 

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]);
  }
}
Chris
Thanks, it seems like you are understanding what I'm asking. I started to ask a bunch of questions about each term in the example, but could you explain a little about each one, please. Or, do you know of a tutorial that's as basic as I need?
ChrisC
I think silky answered those better than I!
Chris
Gotcha. Thank for your replies.
ChrisC
+7  A: 

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.

Noon Silk
Thanks. I'm really looking for explanations of each command in extremely basic language. I've looked them up at MSDN, but I don't yet have a good overview of each element, ie what their role is, why it's needed, what it does exactly, etc, in beginner's language.
ChrisC
Updated with some basic definitions.
Noon Silk
nicely defined. Good example, too.
sql_mommy
Right now I'm only working on a console version (for learning and testing purposes) of a significantly larger program I am writing. I didn't realize that ORM and the questions I'm asking were totally different animals. Do I need to drop this and start using ORM now in my console version? If so, where/how do I start? Thank you.
ChrisC
While, LLBLGen costs real money. But it saves so much time it's worth it. If you are writing applications for which you will get money, I would suggest you buy it and learn it. Otherwise, if you're just learning for fun, no harm can come from understanding the basics of data access (i.e. no ORM). Heck, you could even try writing your own ORM, just for fun. I've done it. But for serious stuff: LLBLGen.
Noon Silk
Yeah, I just checked the price and, for now, I guess I'll hold off. What is the downside to not using an ORM? Also, are there other, good, free, ORM's available? Thanks again.
ChrisC
There are plenty of free ORMs, but they are all quite different. NHibernate is one, Linq-To-Sql is another, subsonic, etc. Personally I don't have much like for any of them, preferring LLBLGen any day.
Noon Silk
Gotcha. (I just read about LINQ below right before reading your comment) How would you characterize the pros and cons of (me in my situation) using an ORM.
ChrisC
@Chris: An ORM will abstract away the need for raw SQL and allows you to write queries that will error out at compile-time, not runtime (ergo, making them easier to debug). However, ORMs often have a difficult time of optimizing SQL queries, taking advantage of indices, and taking advantage of some of the more advanced database features (such as temp tables or what have you). I'm a SQL guy at heart, and ORMs abstract too much away for my personal taste, but it's really what you feel comfortable with and what type of app you're building.
Eric
Thanks for the info Eric.
ChrisC
+1  A: 

I bought a book called Pragmatic ADO.NET.

ChrisW
A: 

In general, I recommend using the Microsoft Enterprise Library for DB access. I've used it in a few projects, and am very fond of it.

See the Data Access Quickstart provided by Microsoft that should help you get started

Also, I've also grown accustomed to writing Extension Methods for extracting 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!

Pwninstein
Thanks. How does the MS Enterprise Library compare to SQLClient or ORM's? (sorry if that sounds like a dumb question)
ChrisC
+1  A: 

There's a tutorial on ADO.NET that covers a lot of the things you're looking for at http://www.csharp-station.com/Tutorials/AdoDotNet/Lesson01.aspx. Lesson 1 is mostly background but lesson 2 and onwards goes over SQL client objects.

Another tutorial at http://www.codeproject.com/KB/database/sql%5Fin%5Fcsharp.aspx covers some of the basics (SqlConnection, SqlCommand).

TLiebe
That csharp-station tutorial is a great one for getting started with ADO.Net! I read it 2 or 3 times when getting started. Before long, you get your DAL working without even thinking about it. It just seems intimidating at first, but then it becomes second nature.
MattSlay
Thanks. I will check it out.
ChrisC
+1  A: 

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.

Eric
It'll take me a little time to re-read and try to digest it. Thanks a lot.
ChrisC
A: 

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