I would like to have a small access database on our network drive with the purpose of sharing data over our business. It will contain a few tables, at the moment it has two, one for products and one for batches. The database is simple, no relationships or rules, but it takes over 10 minutes to update the database from an empty state. If I copy of the database to a local drive it takes about a minute.
I am filling two empty data tables and then updating them to the database.
In a previous question which has been deleted, it was suggested that maybe my way of handling this could be better. I am using System.Data.OleDbAdapter for the data adapter, and using the Jet provider as that way no extra software needs to be installed on the computers.
Using a SQL server is not suitable due to restrictions from our head office.
Is there a better solution to what I am trying to do?
Please let me know if I am not clear with my query
My Code
private ClsProducts Products;
private ClsBatches Batches;
private ClsReportHeader ReportHeader;
private Database database;
public static void Main(string[] args)
{
Program Importer = new Program();
Importer.database = new Database();
Importer.SetupDataTables();
Importer.ReadFile();
Importer.database.Save();
Console.Write("Press any key to continue . . . ");
Console.ReadKey(true);
}
public void SetupDataTables()
{
Products = new ClsProducts(database.products_Table);
Batches = new ClsBatches(database.batches_Table);
}
public void ReadFile()
{
}
}
public class ClsProducts
{
private DataTable table;
public DataColumn Code;
public DataColumn Description;
public DataColumn Group;
public DataColumn Supplier;
public DataColumn Brand;
public DataColumn HOMaintained;
public DataColumn Active;
public DataColumn Qty;
public DataColumn TagCost;
public DataColumn Retail;
public ClsProducts(DataTable Table)
{
table = Table;
Code = table.Columns["PCode"];
Description = table.Columns["PDescription"];
Group = table.Columns["PGroup"];
Supplier = table.Columns["PSupplier"];
Brand = table.Columns["PBrand"];
HOMaintained = table.Columns["PHOMaintained"];
Active = table.Columns["PActive"];
Qty = table.Columns["PQty"];
TagCost = table.Columns["PTagCost"];
Retail = table.Columns["PRetail"];
}
public int AddRow()
{
table.Rows.Add(table.NewRow());
return table.Rows.Count - 1;
}
public int Count
{
get
{
return table.Rows.Count;
}
}
public object this[int RowNumber, DataColumn DC]
{
get
{
return this.table.Rows[RowNumber][DC];
}
set
{
this.table.Rows[RowNumber][DC] = value;
}
}
}
public class ClsBatches
{
private DataTable table;
public DataColumn Code;
public DataColumn Batch;
public DataColumn Date;
public DataColumn Qty;
public DataColumn Cost;
public DataColumn Rebate;
public DataColumn Freight;
public ClsBatches(DataTable Table)
{
table = Table;
Code = table.Columns["BCode"];
Batch = table.Columns["BBatch"];
Date = table.Columns["BDate"];
Qty = table.Columns["BQty"];
Cost = table.Columns["BCost"];
Rebate = table.Columns["BRebate"];
Freight = table.Columns["BFreight"];
}
public int AddRow()
{
table.Rows.Add(table.NewRow());
return table.Rows.Count - 1;
}
public int Count
{
get
{
return table.Rows.Count;
}
}
public object this[int RowNumber, DataColumn DC]
{
get
{
return this.table.Rows[RowNumber][DC];
}
set
{
this.table.Rows[RowNumber][DC] = value;
}
}
}
public class Database
{
public OleDbDataAdapter products_Adapter;
public OleDbDataAdapter batches_Adapter;
public DataTable products_Table;
public DataTable batches_Table;
public OleDbCommandBuilder builder;
public OleDbConnection connection;
public Database()
{
connection = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\grant.mcconville\Database.mdb");
connection.Open();
products_Table = new DataTable();
batches_Table = new DataTable();
products_Adapter = new OleDbDataAdapter("SELECT * FROM TblProducts", connection);
products_Adapter.Fill(products_Table);
builder = new OleDbCommandBuilder(products_Adapter);
products_Adapter.UpdateCommand = builder.GetUpdateCommand();
products_Adapter.InsertCommand = builder.GetInsertCommand();
products_Adapter.DeleteCommand = builder.GetDeleteCommand();
batches_Adapter = new OleDbDataAdapter("SELECT * FROM TblBatches", connection);
batches_Adapter.Fill(batches_Table);
builder = new OleDbCommandBuilder(batches_Adapter);
batches_Adapter.UpdateCommand = builder.GetUpdateCommand();
batches_Adapter.InsertCommand = builder.GetInsertCommand();
batches_Adapter.DeleteCommand = builder.GetDeleteCommand();
}
public void Save()
{
products_Adapter.Update(products_Table);
batches_Adapter.Update(batches_Table);
}
}