Propagating Datagrid Changes to a Database

  • Thread starter Thread starter Dennis Jelavic
  • Start date Start date
D

Dennis Jelavic

As a newbie to ADO.NET I am trying to set up a two tiered application (UI
and Data Access Layer) defining a data layer (Class: PortfolioCRUD)that has
two methods - getSectors and updateSectors to retrieve a single table
(Sector) from a database and to update a modified Sector table. getSectors
returns a dataset to the calling code in the UI which is bound to a
datagrid. After updating the dataset through the datagrid, the UI code calls
the updateSectors method in the DAL, passing to it the updated dataset.

The dataset in the updateSectors method possesses all the updated data that
were made by the UI code. However the
'PortfolioAdapter.Update(DS1,"Sector")' statement does not update the
database. However if the dataset is
modified locally in the updateSectors method (as illustrated by the change
to the 2nd col of the first row to the value "Escort") then that change is
propagated thru to the database.

I orginally thought that this was a problem with passing the dataset as a
parameter. However I have discovered that this is not the case. My UI has a
datagrid, a couple of textboxes and a few buttons to effect actions. If I
update the datagrid by changing data in the textboxes and then executing the
following code
dataGrid1[dataGrid1.CurrentRowIndex, 0] = textBox1.Text;

dataGrid1[dataGrid1.CurrentRowIndex, 1] = textBox2.Text;


I get the result described above. However if I edit the datagrid data
directly and bypass the assignment through the textboxes then the database
is updated correctly.

Can anyone throw any light onto what is going on?


_Data Access Layer Code
____________________________________________________

#region Using directives

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;


#endregion

namespace Portfolio_DAL
{
public class PortfolioCRUD
{
public PortfolioCRUD()
{

}


public SqlDataAdapter PortfolioAdapter;
public SqlConnection SharesConnection;
public SqlCommand SqlSectorGet;
public String SqlCmd;
public SqlCommandBuilder PortfolioCommandBuilder;

public DataSet getSectors()
{
DataSet SectorDS = new DataSet();
SqlCmd = "SELECT ID, Description FROM Sector ORDER BY ID";
SharesConnection = new SqlConnection("Data Source =
Dennis02\\sqlExpress;Database=Shares;User ID=application;password=xxxx");
SqlSectorGet = new SqlCommand(SqlCmd,SharesConnection);
PortfolioAdapter = new SqlDataAdapter(SqlSectorGet);
PortfolioCommandBuilder = new
SqlCommandBuilder(PortfolioAdapter);
PortfolioAdapter.MissingSchemaAction =
MissingSchemaAction.AddWithKey;
PortfolioAdapter.Fill(SectorDS, "Sector");
return SectorDS;
}



public string updateSectors(DataSet DS1)
{
String s;
try
{
DS1.Tables["Sector"].Rows[0][1]="Escort";
s = DS1.Tables["Sector"].Rows[0][1].ToString() + " "+
DS1.Tables["Sector"].Rows[2][1].ToString();
PortfolioAdapter.Update(DS1,"Sector");
return s;
}
catch
{
s = "Exception Occurred";
return s;
}
}

}
}


UI Layer Code
_______________________________________________________


PortDAL = new PortfolioCRUD();
dataSet1 = PortDAL.getSectors();
DataGridTableStyle tableStyle = new DataGridTableStyle();
tableStyle.MappingName = "Sector";
DataGridTextBoxColumn Col1 = new DataGridTextBoxColumn();
Col1.MappingName = "ID";
Col1.HeaderText = "ID";
Col1.Width = 60;
tableStyle.GridColumnStyles.Add(Col1);
DataGridTextBoxColumn Col2 = new DataGridTextBoxColumn();
Col2.MappingName = "Description";
Col2.HeaderText = "Description";
Col2.Width = 308;
tableStyle.GridColumnStyles.Add(Col2);
dataGrid1.TableStyles.Add(tableStyle);
dataGrid1.SetDataBinding(dataSet1, "Sector");
string st;
st=PortDAL.updateSectors(dataSet1);
MessageBox.Show(st,"Information", MessageBoxButtons.OK);

Dennis Jelavic
 
Did not examine the code due to time (apologies in advance). When you use the
DataAdapter method, you have to ensure that you actually pass the data you
have change back into update. If you simply refill the adapter, you are
working with the same old data and not changes. Look at Dino Espisitos
articles on msdn.microsoft.com (in the MSDN magazine section). He has some
great articles on using Update and even handling concurrency at the same time
(data change while you had the data "checked out").

---

Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************

Dennis Jelavic said:
As a newbie to ADO.NET I am trying to set up a two tiered application (UI
and Data Access Layer) defining a data layer (Class: PortfolioCRUD)that has
two methods - getSectors and updateSectors to retrieve a single table
(Sector) from a database and to update a modified Sector table. getSectors
returns a dataset to the calling code in the UI which is bound to a
datagrid. After updating the dataset through the datagrid, the UI code calls
the updateSectors method in the DAL, passing to it the updated dataset.

The dataset in the updateSectors method possesses all the updated data that
were made by the UI code. However the
'PortfolioAdapter.Update(DS1,"Sector")' statement does not update the
database. However if the dataset is
modified locally in the updateSectors method (as illustrated by the change
to the 2nd col of the first row to the value "Escort") then that change is
propagated thru to the database.

I orginally thought that this was a problem with passing the dataset as a
parameter. However I have discovered that this is not the case. My UI has a
datagrid, a couple of textboxes and a few buttons to effect actions. If I
update the datagrid by changing data in the textboxes and then executing the
following code
dataGrid1[dataGrid1.CurrentRowIndex, 0] = textBox1.Text;

dataGrid1[dataGrid1.CurrentRowIndex, 1] = textBox2.Text;


I get the result described above. However if I edit the datagrid data
directly and bypass the assignment through the textboxes then the database
is updated correctly.

Can anyone throw any light onto what is going on?


_Data Access Layer Code
____________________________________________________

#region Using directives

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;


#endregion

namespace Portfolio_DAL
{
public class PortfolioCRUD
{
public PortfolioCRUD()
{

}


public SqlDataAdapter PortfolioAdapter;
public SqlConnection SharesConnection;
public SqlCommand SqlSectorGet;
public String SqlCmd;
public SqlCommandBuilder PortfolioCommandBuilder;

public DataSet getSectors()
{
DataSet SectorDS = new DataSet();
SqlCmd = "SELECT ID, Description FROM Sector ORDER BY ID";
SharesConnection = new SqlConnection("Data Source =
Dennis02\\sqlExpress;Database=Shares;User ID=application;password=xxxx");
SqlSectorGet = new SqlCommand(SqlCmd,SharesConnection);
PortfolioAdapter = new SqlDataAdapter(SqlSectorGet);
PortfolioCommandBuilder = new
SqlCommandBuilder(PortfolioAdapter);
PortfolioAdapter.MissingSchemaAction =
MissingSchemaAction.AddWithKey;
PortfolioAdapter.Fill(SectorDS, "Sector");
return SectorDS;
}



public string updateSectors(DataSet DS1)
{
String s;
try
{
DS1.Tables["Sector"].Rows[0][1]="Escort";
s = DS1.Tables["Sector"].Rows[0][1].ToString() + " "+
DS1.Tables["Sector"].Rows[2][1].ToString();
PortfolioAdapter.Update(DS1,"Sector");
return s;
}
catch
{
s = "Exception Occurred";
return s;
}
}

}
}


UI Layer Code
_______________________________________________________


PortDAL = new PortfolioCRUD();
dataSet1 = PortDAL.getSectors();
DataGridTableStyle tableStyle = new DataGridTableStyle();
tableStyle.MappingName = "Sector";
DataGridTextBoxColumn Col1 = new DataGridTextBoxColumn();
Col1.MappingName = "ID";
Col1.HeaderText = "ID";
Col1.Width = 60;
tableStyle.GridColumnStyles.Add(Col1);
DataGridTextBoxColumn Col2 = new DataGridTextBoxColumn();
Col2.MappingName = "Description";
Col2.HeaderText = "Description";
Col2.Width = 308;
tableStyle.GridColumnStyles.Add(Col2);
dataGrid1.TableStyles.Add(tableStyle);
dataGrid1.SetDataBinding(dataSet1, "Sector");
string st;
st=PortDAL.updateSectors(dataSet1);
MessageBox.Show(st,"Information", MessageBoxButtons.OK);

Dennis Jelavic
Click to expand...
 
U¿ytkownik "Dennis Jelavic" <[email protected]> napisa³ w wiadomo¶ci
(...)
The dataset in the updateSectors method possesses all the updated data
that were made by the UI code. However the
'PortfolioAdapter.Update(DS1,"Sector")' statement does not update the
database. However if the dataset is
modified locally in the updateSectors method (as illustrated by the change
to the 2nd col of the first row to the value "Escort") then that change is
propagated thru to the database. (...)

dataGrid1.SetDataBinding(dataSet1, "Sector");
string st;
Click to expand...

Before sending dataset to update finish current edit - for example add two
lines:
CurrencyManager myCm = (CurrencyManager) BindingContext[dataSet1, "Sector"];
myCm.EndCurrentEdit();
st=PortDAL.updateSectors(dataSet1);
MessageBox.Show(st,"Information", MessageBoxButtons.OK);
Click to expand...

Hope that it helps.
Regards,
Grzegorz
 
Back
Top