dataadapter.update using DAAB

Topics: Data Access Application Block
Jul 15, 2009 at 9:05 PM

All,

I'm very new to the DAAB. I have this exception coming out of my app when call adapter.update

Update requires a valid InsertCommand when passed DataRow collection with new rows.

Here is my code.


string XMLPath_UserInfo = "C:\\my_data\\Visual Studio 2008\\Websites\\EntLibTest3\\XMLFiles\\UserXML_06172009.xml";

DatabaseSettings settings = DatabaseSettings.GetDatabaseSettings(new SystemConfigurationSource());

string defaultConnectionStringName = settings.DefaultDatabase;
Database db = null;
try
{
    db = DatabaseFactory.CreateDatabase(defaultConnectionStringName);
}
catch (Exception ex)
{
    Response.Write(ex.Message);
}

string sql = "Select NAME, LOGIN, EMAIL, USERID, ACCOUNT_ID, TYPE From TEMP_USER_INFO1";
DbCommand dbCommand = db.GetSqlStringCommand(sql);

// DataSet that will hold the returned results.
DataSet myDSfromDB = null;

DataSet myDSfromXML = new DataSet();

DataAdapter myDAfromXML = db.GetDataAdapter();
//myDAfromXML.ContinueUpdateOnError = true; // when this is set to true, I dont see any error, but the table in the db doesn't update. so commented to see what the error is

DbCommandBuilder dbcb;

// Just a test to see if the DAAB works for me..
myDSfromDB = db.ExecuteDataSet(dbCommand);
GridView0.DataSource = myDSfromDB;
GridView0.DataBind();

myDSfromXML.ReadXml(XMLPath_UserInfo);
myDAfromXML.AcceptChangesDuringFill = false;

foreach (DataRow principal_dr in myDSfromXML.Tables["principal"].Rows)
{
    WriteToLog(principal_dr.RowState.ToString(), fpath);// The rowstate noted in the log file is always "Added"
}

myDAfromXML.TableMappings.Add("Table", "principal");

myDAfromXML.TableMappings.Add("Table", "principal");
try
{
    myDAfromXML.Update(myDSfromXML);
}
catch (Exception ex)
{
    Response.Write(ex.Message);
}


Can someone plz help me?

I have all this working when I'm not using DAAB. After starting to use DAAB, I'm unable to put it to work.

Thanks

Bobbie

 

Jul 16, 2009 at 4:35 AM

Umm, obvious first question - did you give the DataAdapter a valid InsertCommand?

-Chris

 

Jul 16, 2009 at 2:42 PM

Thanks Chris,

I understand that now.

Here is my updated code. Can someone tell me, is there a better way to do it? Or is my code a performance expensive one?


string XMLPath_UserInfo = "C:\\my_data\\Visual Studio 2008\\Websites\\EntLibTest3\\XMLFiles\\UserXML_06172009.xml";

DatabaseSettings settings = DatabaseSettings.GetDatabaseSettings(new SystemConfigurationSource());

string defaultConnectionStringName = settings.DefaultDatabase;
Database db = null;

db = DatabaseFactory.CreateDatabase(defaultConnectionStringName);

DataSet myDSfromXML = new DataSet();
DbDataAdapter myDAfromXML = db.GetDataAdapter();

myDAfromXML.ContinueUpdateOnError = true;

string sql1 = "Select RowID, NAME, LOGIN, EMAIL, USERID, ACCOUNT_ID, TYPE From TEMP_USER_INFO1";
DbCommand selectCommand = db.GetSqlStringCommand(sql1);
myDAfromXML.SelectCommand = selectCommand;

myDSfromXML.ReadXml(XMLPath_UserInfo);

myDAfromXML.AcceptChangesDuringFill = false;

DbCommandBuilder dbCmdBuilder = db.DbProviderFactory.CreateCommandBuilder();

selectCommand.Connection = db.CreateConnection();
dbCmdBuilder.DataAdapter = myDAfromXML;

myDAfromXML.DeleteCommand = dbCmdBuilder.GetDeleteCommand();
myDAfromXML.InsertCommand = dbCmdBuilder.GetInsertCommand();
myDAfromXML.UpdateCommand = dbCmdBuilder.GetUpdateCommand();

myDAfromXML.TableMappings.Add("Table", "principal");

try
{
    myDAfromXML.Update(myDSfromXML);
}
catch (Exception ex)
{
    Response.Write(ex.Message);
}


OK, Now my question is why do I need to give the DbDataAdapter an Insert Command, while I dont give the same to regular DataAdapter. Here is my code with the regular Adapter which was developed in non DAAB approach.


string ora_constr = "XXXXXXX";
string ora_cmdstr = "SELECT NAME, LOGIN, EMAIL, USERID, ACCOUNT_ID, TYPE FROM TEMP_USER_INFO";

OracleDataAdapter ora_adapter = new OracleDataAdapter(ora_cmdstr, ora_constr);
 ora_adapter.ContinueUpdateOnError = true;

// Create the builder for the adapter to automatically generate
// the Command when needed
 OracleCommandBuilder ora_builder = new OracleCommandBuilder(ora_adapter);

string XMLPath_UserInfo = "C:\\my_data\\Visual Studio 2008\\Projects\\PopulateUserInfo\\XMLFiles\\UserXML_06252009.xml";
 DataSet dataset_UserInfo = new DataSet();

ora_adapter.AcceptChangesDuringFill = false;

try
 {
     ora_adapter.RowUpdating += new OracleRowUpdatingEventHandler(OnRowUpdating);
     ora_adapter.RowUpdated += new OracleRowUpdatedEventHandler(OnRowUpdated);

     ora_adapter.Update(dataset_UserInfo.Tables["principal"]);

 }
 catch (Exception ex)
 {
     WriteToLog(ex.Message, fpath);

 }


Can someone guide me?

 

Thanks

Bobbie

Jul 17, 2009 at 3:24 AM
Edited Jul 17, 2009 at 3:51 AM

First, you can just call DatabaseFactory.CreateDatabase() without passing any parameter since you want to get the default one.  You can then avoid gettting an instance of databasesettings just to get the default connectionstring. 

In your original code, I didn't see the part where you associated your dataadapter to your commandbuilder

 

Sarah Urmeneta
Global Technology and Solutions
Avanade, Inc.
entlib.support@avanade.com