OleDb DataAdapter and Reading .bin File
I am a hobbyist in c++ programming, and have been fighting this issue for days. I am using an OleDb DataAdapter to fill a DataTable from an AS/400 data source. There is over 8,000 records in the Table and was playing with the idea of first loading the DataTable from a .bin or .xml file and then refreshing the rows with the DataAdapter.Fill method (only a few rows change from day to day). Unfortunately every time the DataAdapter fills the DataTable it duplicates every row it reads from the .bin file and I have not been able to successfully map it to the DataAdapter.Fill method.
Code:
void Form1::loadData()
{
String^ myConnString = "Provider=provider;Data Source=datasource;";
String^ myQuery = "SELECT IMINV# FROM SourceTableName WHERE IMINV# = '00' ORDER BY 1";
OleDbDataAdapter^ myAdapter = gcnew OleDbDataAdapter(myQuery, myConnString);
// Create DataTable and define Primary Key
invList = gcnew DataTable("invList");
invList->PrimaryKey = gcnew array<DataColumn^> {invList->Columns["MINV#"]};
// Load DataTable from disk
Form1::readSerialize();
DataTableMapping^ tableMapping = gcnew DataTableMapping("Table", "invList");
tableMapping->ColumnMappings->Add("IMINV#","MINV#");
myAdapter->TableMappings->Add(tableMapping);
// Have also tried MissingSchemaAction::AddWithKey
myAdapter->FillSchema(invList, SchemaType::Mapped);
myAdapter->Fill(invList);
// Save DataTable to disk, normally would be done when user exits the program
Form1::writeSerialize();
// Binds DataTable to a dataGridView
mySource = gcnew BindingSource();
mySource->DataSource = invList;
dataGridView1->DataSource = mySource;
}
And the code for the read and write, originally I just used DataTable.ReadXml, because it was easier, but after searching a few posts this was suggested.
Code:
void Form1::readSerialize()
{
FileStream^ fs;
try
{
// Opens the file containing the data to deserialize.
fs = gcnew FileStream( "Inventory.bin",FileMode::Open, FileAccess::Read);
try
{
IFormatter^ formatter = gcnew BinaryFormatter;
// Deserialize the hashtable from the file and
// assign the reference to our local variable.
invList = (DataTable^)formatter->Deserialize(fs);
}
catch ( SerializationException^ e )
{
MessageBox::Show(String::Format("Failed to deserialize. Reason: {0}", e->Message));
throw;
}
finally
{
fr->Close();
}
}
catch (FileNotFoundException^ e)
{
MessageBox::Show(e->ToString());
}
}
Code:
void Form1::writeSerialize()
{
FileStream^ fs = gcnew FileStream( "Inventory.bin",FileMode::OpenOrCreate );
// Construct a BinaryFormatter and use it to serialize the data to the stream.
IFormatter^ formatter = gcnew BinaryFormatter;
try
{
formatter->Serialize( fs, invList );
}
catch ( SerializationException^ e )
{
MessageBox::Show(String::Format("Failed to serialize. Reason: {0}", e->Message));
throw;
}
finally
{
fs->Close();
}
}
This Select statement should only return a single column with a single row, which it does, but adds a duplicate row every time the program runs. I have tried several of my ideas, but nothing really works. Any help on my confusion would be much appreciated, thank you.
Re: OleDb DataAdapter and Reading .bin File
Nevermind I figured it out, aslong as I set the primary key after reading the .bin file everything works fine. I quess I was assuming that the key would be included when the DataTable was serialized. Thank you to anyone who took the time to look at my post.