DataAdapter problem

[kobus]

Hi

I am running in circles with this problem. In my C# windows application I am trying to use a three tier layout (Data access layer, Business layer, View layer).

I use DataSet and DataAdapter for the loading the data from the database. Opening the SQL Server database connection and loading the data all happens in the access layer and I pass the DataSet through the layers to the view layer and display it (No problem). The problem comes in when I need to update the database.

Here is some of my code. I have only included the relevant data for brevity.

I have a class that handles my Database access (eg connect to database, create commands and retrieve dataset).

	public class DBConn
	{
		private SqlConnection conn;
		private SqlCommand sqlCmd;


		public void OpenConnection(string dbase, string server, 
			string user, string password)
		{
			conn = new SqlConnection(
				"Data Source=" + server + ";" +
				"Initial Catalog=" + dbase + ";" +
				"UID=" + user + ";" +
				"Password=" + password);
			conn.Open();
		}


		public DataSet QueryDataSet()
		{
			try
			{
				DataSet ds = new DataSet();
				SqlDataAdapter da = new SqlDataAdapter(sqlCmd.CommandText, conn);
				da.Fill(ds);
				return ds;
			}
			catch (SqlException e)
			{
				return null;
			}
		}


		public DataSet QueryDataSet(string tableName)
		{
			try
			{
				DataSet ds = new DataSet();
				SqlDataAdapter da = new SqlDataAdapter(sqlCmd.CommandText, conn);
				da.Fill(ds, tableName);
				return ds;
			}
			catch (SqlException e)
			{
				return null;
			}
		}


		public DataSet QueryDataSet(string tableName, SqlDataAdapter da)
		{
			try
			{
				string sqlUpdate = "UPDATE Products SET ProdName = @ProdName";
				string sqlInsert = "";
				string sqlDelete = "";
				DataSet ds = new DataSet();
				da = new SqlDataAdapter(sqlCmd.CommandText, conn);
				da.UpdateCommand = new SqlCommand(sqlUpdate, conn);
				da.UpdateCommand.Parameters.Add("@ProdName", SqlDbType.VarChar, 30, "ProdName");
				
				SqlParameter workParm = da.UpdateCommand.Parameters.Add("@ProdIndex", SqlDbType.Int);
				workParm.SourceColumn = "ProdIndex";
				workParm.SourceVersion = DataRowVersion.Original;
				da.Fill(ds, tableName);

				return ds;
			}
			catch (SqlException e)
			{
				return null;
			}
		}


		public void BuildCommand(string sql)
		{
			sqlCmd = conn.CreateCommand();
			sqlCmd.CommandType = CommandType.Text;
			sqlCmd.CommandText = sql;
		}


		public void KillConnection()
		{
			conn.Close();
			conn.Dispose();
		}

	}

Then I have the class that uses the DBConn class to retrieve the dataset.

	public class GAL
	{
		public static DataSet LoadObjectsDS(string sql, string tableName, SqlDataAdapter dataAdapter)
		{
			DataSet ds = null;

			DBConn dbc = null;

			try
			{
				string database = “DatabaseName”;
				string server = “ServerName”;
				string user = "sa";
				string password = "";
				//instantiate dbc
				dbc = new DBConn();
				dbc.OpenConnection(database,server,user,password);

				ds = new DataSet();
				//build command
				dbc.BuildCommand(sql);
				//run command
				ds = dbc.QueryDataSet(tableName, dataAdapter);
			}
			catch (Exception e)
			{
				ds = null;
			}
			finally 
			{
				if (dbc != null)
				{
					dbc.KillConnection();
				}
			}
			return ds;
		}

	}

In the business layer I have a ProductsManager class:

	public class ProductManager
	{

		public static DataSet LoadProducts(SqlDataAdapter dataAdapter)
		{
			string sql = "SELECT ProdIndex, ProdName "
				+ "FROM Products";

			string tableName = "Products"; //name for table in dataset

			DataSet ds = GAL.LoadObjectsDS(sql, tableName, dataAdapter);
			return ds;
		}
	}

In my view layer I want to display the products:
I manage to display the data in the dataset with no problems and I can also add rows and update the data in the dataset. The problems is when I need to update the data in the database. When I use the dataAdapter.Update() method it gives me the following message:
The ConnectionString property has not been initialized.

	public class Form1 : System.Windows.Forms.Form
	{
		private System.Windows.Forms.ListView listView1;
		private DataSet products = null;
		private DataRow dataRow = null;
		private SqlDataAdapter dataAdapter = null;



		private void LoadData()
		{
			dataAdapter = new SqlDataAdapter();
			string messageError = string.Empty;
			if (this.LoadProducts() == false)
			{
				messageError += "Products";
			}
			else
			{
				MessageBox.Show("LOAD SUCCESSFUL");
			}
		
			if (messageError != string.Empty)
			{
				MessageBox.Show(messageError + " NOT Loaded successfully. Cannot edit Product data now.");
			}
		}


		private bool LoadProducts()
		{
			bool loadCorrect = true;

			products = new DataSet();
			products = ProductManager.LoadProducts(dataAdapter);
			if (products == null)
			{
				loadCorrect = false;
			}

			return loadCorrect;
		}


		private void Form1_Load(object sender, System.EventArgs e)
		{		
			this.LoadData();
		}


		private void btnSave_Click(object sender, System.EventArgs e)
		{
			try
			{
				if(this.products != null && this.products.HasChanges())
				{
					this.dataAdapter.Update(this.products, "Products");
				}
			}
			catch (Exception ex)
			{
				MessageBox.Show(ex.Message);
			}
		}
	}

Any help or criticism will be appreciated.

Thanks
Kobus