Query Microsoft Access Mdb Database Using Linq and C#

Query Microsoft Access MDB Database using LINQ and C#

What you want is a LINQ to ODBC provider, or a LINQ to JET/OLEDB provider.

Out of the box, MS doesn't make one. There may be a 3rd party who does.

query MS access database with LINQ in c#

Unfortunately LINQ does not support access database. As work around you could use Ado.net DataSet to pull out the data from your database

//create the connection string
string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\myDatabase.mdb";

//create the database query
string query = "SELECT * FROM MyTable";

//create an OleDbDataAdapter to execute the query
OleDbDataAdapter dAdapter = new OleDbDataAdapter(query, connString);

//create a command builder
OleDbCommandBuilder cBuilder = new OleDbCommandBuilder(dAdapter);

//create a DataTable to hold the query results
DataTable dTable = new DataTable();

//fill the DataTable
dAdapter.Fill(dTable);

Then you can use LINQ to object to do your query (even if I won’t recommend you to use this approach because of the performance are not very good)

var results = from myRow in dTable.AsEnumerable()
where myRow.Field<int>("RowNo") == 1
select myRow;

use Linq with access database

check following thread
Query Microsoft Access MDB Database using LINQ and C#

do check folowing thread too
http://forums.asp.net/t/1542443.aspx

How to use LINQ2SQL to connect to Access 2010

Linq to SQL is just that, a Linq provider for SQL Server. It does not support Access databases. Check out this question for more details. It looks like you might be able to use Entity Framework for Access though.

LINQ asp.net page against MS Access .

LINQ to SQL doesn't support Access (that is, there's no Access/Jet provider for LINQ), but you can query a DataSet with LINQ. This means that you fill your DataSet with any possible data from your database that you might need in your results, and then you filter on the client side. After you have a typed DataSet, and you Fill() it with a TableAdapter, you do something like this:

var year = 1995;  // you can pass the year into a method so you can filter on any year
var results = from row in dsQuotes
where row.QuoteDate > year
select row;

You'll have to decide whether this is worth it. You'd have to fill your DataSet with all the quotes, then use LINQ to filter on just those quotes that are after 1995. For a small amount of data, sure, why not? But for a very large amount of data, you'll need to make sure it won't be too slow.

If you're using a DataSet, though, you can write custom queries that become new TableAdapter methods. So you can put the correct SQL for your query in a FillByYear() method in your TableAdapter and use that to fill your typed DataTable. That way you're only getting back the data you need.

If you go this route, remember that Access/Jet uses positional parameters, not named parameters. So instead of

SELECT * FROM Quotes WHERE Year(QuoteDate) > @Year

you'd use something like this:

SELECT * FROM Quotes WHERE Year(QuoteDate) > ?

How to query MS Access database with C# application?

I'm not going to repeat everything that's in the MSDN, there's a great walk through right here: http://msdn.microsoft.com/en-us/library/ms971485.aspx

However, do note that you don't need to use the interop assemblies, they are horribly slow, difficult to use and well just a PIA in general.

As the MSDN article shows, everything you want to do can be done using ADO.NET.

How to connect to a MS Access file (mdb) using C#?

The simplest way to connect is through an OdbcConnection using code like this

using System.Data.Odbc;

using(OdbcConnection myConnection = new OdbcConnection())
{
myConnection.ConnectionString = myConnectionString;
myConnection.Open();

//execute queries, etc

}

where myConnectionString is something like this

myConnectionString = @"Driver={Microsoft Access Driver (*.mdb)};" + 
"Dbq=C:\mydatabase.mdb;Uid=Admin;Pwd=;

See ConnectionStrings

In alternative you could create a DSN and then use that DSN in your connection string

  • Open the Control Panel - Administrative Tools - ODBC Data Source
    Manager
  • Go to the System DSN Page and ADD a new DSN
  • Choose the Microsoft Access Driver (*.mdb) and press END
  • Set the Name of the DSN (choose MyDSN for this example)
  • Select the Database to be used
  • Try the Compact or Recover commands to see if the connection works

now your connectionString could be written in this way

myConnectionString = "DSN=myDSN;"


Related Topics



Leave a reply



Submit