-
September 23rd, 2005, 01:59 AM
#1
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
Last edited by kobus; September 23rd, 2005 at 07:45 AM.
Reason: Adding code tags
-
September 23rd, 2005, 06:50 AM
#2
Re: DataAdapter problem
If you think you CAN, you can, If you think you CAN'T, you are probably right.
Have some nice Idea to share? Write an Article Online or Email to us and You may WIN a Technical Book from CG.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|