Kevin,

I don't know if you've seen the stuff I've posted before for DataAccess questions:

First, you have a base DataAccess class that all of your other DataAccess classes should inherit from, and get the connection in it's constructor:

public class MyDataAccess
{
	protected SqlConnection oConnection;

	public MyDataAccess
	{
		// Note that I wouldn't actually hard-code the connection string like this. It should be in
		// your config settings. All the examples I currently have, use the old 1.1 way of doing this,
		// which is this:
		// string MyConnString = ConfigurationSettings.AppSettings["ConnectionString"];

		this.oConnection = new SqlConnection("server=(local);database=MyDataBase;uid=sa;pwd=MyPassword");
	}
}

Depending on how complicated your application is, you may want to have a separate DataAccess class for different functionality, all sub-classing from the MyDataAccess base class.


Retrieving the data is easy. Note that this DataAccess class returns a DataSet:

public DataSet GetMyData()
{
	// Note that a Fill does not require a Connection Open/Close. The Fill leaves
	// the connection in the state it was found (leaves it open if it was open, 
	// and if it was closed, it opens it, Fills, then closes again).

	SqlDataAdapter da = new SqlDataAdapter("select * from bob", this.oConnection);
	DataSet ds = new DataSet();
	da.Fill(ds, "MyTable");

	return ds;
}

There are a few more options when updating the database.

First, you can use the Update method of the DataAdapter. In order for this to work, your DataSet must have a PrimaryKey defined.

You can do it using the CommandBuilder, which will generate update commands for you (note: if you use a Stored Proc, the CommandBuilder only generates the proper insert/update/delete commands for the first table retreived from the Stored Proc):

public void UpdateMyData(DataSet ds)
{
	// The same applies for the Update. It's not necessary to Open/Close the connection.
	SqlDataAdapter da = new SqlDataAdapter("select * from bob", this.oConnection);
	SqlCommandBuilder sb = new SqlCommandBuilder(da);

	da.Update(ds);
}

Or you can create the various update commands yourself instead of using the CommandBuilder:

public void UpdateMyData(DataSet ds)
{

	SqlCommand sc = new SqlCommand();
	sc.Connection = this.oConnection;
	da = new SqlDataAdapter(sc);

	da.InsertCommand = new SqlCommand("Insert into bob (xyz, abc) VALUES ( @xyz, @abc )", sc.Connection);
	da.InsertCommand.Parameters.Add("@xyz", SqlDbType.Int, 8, "xyz");
	da.InsertCommand.Parameters.Add("@abc", SqlDbType.VarChar, 50, "abc");

	// do the same for da.DeleteCommand & da.UpdateCommand

	da.Update(ds);
}

Or, you can take total control, not use the da.Update() and do it all yourself (this is basically the same code that gets done behind the scenes by the da.Update() method:

public void UpdateMyData(DataSet ds)
{
	SqlCommand sc = new SqlCommand();
	sc.Connection = this.oConnection;
	sc.Connection.Open();
	foreach (DataRow Row in ds.Tables[0].Rows)
	{
		switch (Row.RowState)
		{
			case DataRowState.Added :
				sc.CommandText = "Insert into bob (xyz, abc) VALUES ( @xyz, @abc )";
				sc.Parameters.Clear();
				sc.Parameters.Add("@xyz", Row["xyz"]);
				sc.Parameters.Add("@abc", Row["abc"]);
				sc.ExecuteNonQuery();
				break;
				
			// Do the same for DataRowState Deleted and Modified
			case DataRowState.Deleted :
				break;
			case DataRowState.Modified :
				break;
		}
	}
	sc.Connection.Close();
}

HTH,
~~Bonnie