Using LINQ to SQL with MS Access databases

As you probably know the LINQ to SQL functionality in .NET 3.5 is only supported with SQL Server databases, as the SQL commands generated by the DataContext are targetted at SQL Server.

However the DataContext does not enfore it's connection to be a SQL server connection, and you can in fact pass an OleDbConnection object. I personally find Access very capable for some projects - so obviously it makes sense to be able to use LINQ to SQL with Access databases. Searching on this topic returns very little info, especially since most articles refer to "data access layer".

Anyway - if you have classes decorated with the [Column] attributes etc corresponding to your tables, you CAN in fact query the Access database with LINQ queries. I'm not saying *all* LINQ queries will work - I am still to test how many. But many types should, since the Access SQL syntax flavour is not that different from SQL server's.

However what I really wanted was to be able to use the SubmitChanges() feature to push object data back to the database. Unfortunately the SQL generated by the create & update functions are NOT compatible with Access.

The INSERT gives an error of a "missing semicolon" (due to a second query to retrieve the identity value), and the UPDATE gives an error saying "row not found or changed" (the reason still eludes me, after seeing the query it generates).

The solution is to create a class that inherits from DataContext, and implement Update & Insert methods that it will call instead. If you use a customisable data-layer generator, this class can be automated. Basically you need to execute custom INSERT & UPDATE queries. If you follow this format you could save yourself a lot of time and banging-head-against-desk:




Now you should be able to create/update rows the usual LINQ way:

-------------
Customer newCust = new Customer() { Name = "n", Description = "d" };
db.Customers.InsertOnSubmit(newCust);

try
{
db.SubmitChanges();
}
catch (Exception err)
{
MessageBox.Show(err.Message);
}

MessageBox.Show(newCust.ID.ToString());
-------------



Here is the full code for the DataContext-inherited class:

[System.Data.Linq.Mapping.Provider(typeof(System.Data.Linq.SqlClient.Sql2000Provider))]
[System.Data.Linq.Mapping.DatabaseAttribute()]
public partial class Db : System.Data.Linq.DataContext
{
void UpdateCustomer(Customer c)
{
this.ExecuteCommand("UPDATE [Customers] SET [Name]=@p1, [Description]=@p2, [Version]=@p3 WHERE [ID]=@p0", c.ID, c.Name);
}

void InsertCustomer(Customer c)
{
IDbCommand cmd;

string query = "INSERT INTO [Customers] ([Name], [Description], [Version]) VALUES (@p0, @p1, @p2)";

cmd = this.Connection.CreateCommand();
cmd.Transaction = this.Transaction;
cmd.CommandText = query;
cmd.Parameters.Add(new OleDbParameter("p0", c.Name));
cmd.Parameters.Add(new OleDbParameter("p1", c.Description));
cmd.Parameters.Add(new OleDbParameter("p2", c.Version));
cmd.ExecuteNonQuery();

cmd = this.Connection.CreateCommand();
cmd.Transaction = this.Transaction;
cmd.CommandText = "SELECT @@IDENTITY";
c.ID = Convert.ToInt32(cmd.ExecuteScalar());
}


private static System.Data.Linq.Mapping.MappingSource mappingSource = new AttributeMappingSource();


public Db() :
base(global::LinqExp1.Properties.Settings.Default.cnwin_mainConnectionString, mappingSource)
{
OnCreated();
}

public Db(string connection) :
base(connection, mappingSource)
{
OnCreated();
}

public Db(System.Data.IDbConnection connection) :
base(connection, mappingSource)
{
OnCreated();
}

public Db(string connection, System.Data.Linq.Mapping.MappingSource mappingSource) :
base(connection, mappingSource)
{
OnCreated();
}

public Db(System.Data.IDbConnection connection, System.Data.Linq.Mapping.MappingSource mappingSource) :
base(connection, mappingSource)
{
OnCreated();
}


void OnCreated()
{ }


public System.Data.Linq.Table Customers
{
get
{
return this.GetTable();
}
}
}