Click here to Skip to main content
15,844,009 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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
C#
	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()
	{
            // Unimportant code
	}
}

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);
	}
}
Posted
Comments
Sergey Alexandrovich Kryukov 3-Nov-14 22:17pm    
As to the network, the best way is not using Access, because it does not even support client-server network model. There are other reasons not to use Access...
—SA
Grant Mc 3-Nov-14 22:20pm    
Would the only other alternative be SQL?
Sergey Alexandrovich Kryukov 3-Nov-14 22:30pm    
Sure, please see my answer.
—SA

1 solution

Grant Mc asked:

Would the only other alternative be SQL?
If you want SQL, yes, certainly. You can consider a good set of decent SQL-based database servers, proprietary or open source:
http://en.wikipedia.org/wiki/List_of_relational_database_management_systems[^],
http://en.wikipedia.org/wiki/Comparison_of_relational_database_management_systems[^].

—SA
 
Share this answer
 
Comments
Grant Mc 3-Nov-14 22:38pm    
Many thanks Sergey
Sergey Alexandrovich Kryukov 3-Nov-14 23:32pm    
No problem.
Good luck, call again.
—SA

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900