Very basic database questions

[cchris]

I'm writing my first program. It's a C# console program. I need to connect to a database I've created as part of the project in VS Express. I've added the tables as Data Sources for the project.

I'm writing to ask about the basics of dealing with databases. Like what do these things mean?:
SQLConnection
SQLConnection class
SQLCommand
SQLDataAdapter
DataSets

I haven't yet been able to find tutorials that deal with these things on the level I need.
Thanks for your help.

[sknake]

Ignore the Data adapter. The connection open a connection to the database and the command handles your "Select" or "Insert" queries. The IDE generated typed DataSets use the DataAdapters but if you try to use them manually you're just creating a lot more work for yourself. Here is an example of calling them with parameters. Always use parameters!

public static string BuildSqlNativeConnStr(string server, string database)
    {
      return string.Format("Data Source={0};Initial Catalog={1};Integrated Security=True;", server, database);
    }
    private void simpleButton1_Click(object sender, EventArgs e)
    {
      const string query = "Insert Into Employees (RepNumber, HireDate) Values (@RepNumber, @HireDate)";

      string connStr = BuildSqlNativeConnStr("apex2006sql", "Leather");
      DataTable dt;
      try
      {
        using (SqlConnection conn = new SqlConnection(connStr))
        {
          conn.Open();
          using (SqlCommand cmd = new SqlCommand(query, conn))
          {
            cmd.Parameters.Add(new SqlParameter("@RepNumber", 50));
            cmd.Parameters.Add(new SqlParameter("@HireDate", DateTime.Today));
            using (SqlDataReader dr = cmd.ExecuteReader())
            {
              dt = new DataTable();
              dt.Load(dr);
            }
          }
        }
        System.Diagnostics.Debugger.Break(); //At this point you have the populated datatable
      }
      catch (SqlException)
      {
        System.Diagnostics.Debugger.Break();
      }
    }

[cchris]

Thanks for your reply. I think I understand your advice in the beginning (although it does remind me that I don't know what "datasets" means either-edited my first post to add "datasets"), but I'm sorry, your example is over my head. I'm sure your example works, but I don't understand what the terms I mentioned mean, or how their values are determined.

[sknake]

Well start out small and work from there. I will tell you one thing -- and this is only *my* opinion so take it for just that --- don't use DataSets as commonly as you will see done on many code articles. They are very heavy (computationally expensive) at runtime and compilation time because of the amount of code they generate. They also do odd things like throw exceptions for null fields if you attempt to get the field's value without checking to see if it is null first, make it very cumbersome for them to use.

This these will make sense in time so stick with it. It would be in your benefit to use DataSets, adapters, etc. so you can have experience with them and make your own decisions. Stick with it and if you have any questions start up a thread or post back on this one and one of our C# solvers will help you out.

The two biggest data-related concepts I strive for in my life are:
* Use parameterized SQL. This has a performance and security benefit.
* Don't use DataSets all over the place. This makes projects big and clunky.

If I change one persons mind then mission accomplished :)

[cchris]

Thanks for the tips, Scott. I will keep them in mind. I must say, however, that I still don't get what DataSets are.