-
DataAdapter problem
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).
Code:
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.
Code:
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:
Code:
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.
Code:
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
-
Re: DataAdapter problem