This blog is about the dotnet.all types of codes,news about dotnet including asp.net,vb.net,c# and know about new dotnet technology.programing in asp.net,vb.net,c#, ajax, AJAX tech support for .net and discuss the new technology in dotnet.ncluding asp.net,vb.net,c# and know about new dotnet technology.programing in asp.net,vb.net,c#, ajax, AJAX tech support for .net and discuss the new technology in dotnet.asp.net programming,dot net programming,dotnet programs,dotnet source code,source code.

Free Hosting

Free Hosting

Tuesday, November 11, 2008

MS Access Application With C#

What lead me to write this application is when i wanted to access the MSAccess database using c sharp I couldn't get any information material. All the material available on the net is partial to sql, and hence the purpose we will develop this application in 2 phase First we will see how to make the database connection to the MSAccess and see what the intricacies of it. And then we will finish with the application.
Enough of the talking and let us move towards the main topic. The connection to the database is rather modified as compared with the ADO connection that we had earlier. The following figure shows the sequence properly (i hope)
OleDbConnection--> OleDbCommand? --> OleDbDataReader?
now those who are familiar with ado will obiviously recognise the simillarity but for some clarification and for those who are not well versed with ado here is little explanation.


OleDbConnection --> represents single connection to the database, and depending upon the capabilites of the underlying database it gives you the power to manipulate the database. The point to remember here is even though oledbconnection object goes out of scope it does not get closed. And therefore you will have to explicitely call the close() method of the object.

OleDbCommand --> this is our normal command object as we had in ado. You can call sql stored procedures and sql queries through this object.

OleDbDataReader --> Now this class is of paramount importance since it gives actual access to the underlying dataset of the database. Once you call the ExecuteReader? method of the OleDbCommand? it gets created the dotnet beta 2 sdk says not to create the object of this class directly.

Now you can see more about these main object in .net beta 2 documentation and here is the source code of how to make the program access the database.


using System;
using System.Data.OleDb;

class OleDbTest{

public static void Main()
{
//create the database connection
OleDbConnection? aConnection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\\db1.mdb");

//create the command object and store the sql query
OleDbCommand? aCommand = new OleDbCommand("select * from emp_test", aConnection);
try
{
aConnection.Open();

//create the datareader object to connect to table
OleDbDataReader? aReader = aCommand.ExecuteReader();
Console.WriteLine("This is the returned data from emp_test table");

//Iterate throuth the database
while(aReader.Read())
{
Console.WriteLine(aReader.GetInt32(0).ToString());
}

//close the reader
aReader.Close();

//close the connection Its important.
aConnection.Close();
}

//Some usual exception handling
catch(OleDbException e)
{
Console.WriteLine("Error: {0}", e.Errors0.Message);
}
}
}


The steps involved in running this application successfully
1.create a data base in msaccess called db1.mdb
2.make a table in it called emp_test
3.let it have fields like
emp_code int
emp_name text
emp_ext text
4.save the above code in the sample.cs file
5. make sure the database is on the c:\ and mdac2.6 or later is installed(available in the ms site)
6. compile and run.
Now lets talk about various details of what we have learned in the constructor of the oledbconnection you have seen "provider=" stuff. there are following types of drivers which are compatible with ado.net.
sqlolddb --> Microsoft OLE DB Provider for SQL Server,
msdaora --> Microsoft OLE DB Provider for Oracle,
Microsoft.Jet.OLEDB.4.0 --> OLE DB Provider for Microsoft Jet
you can choose any of then but they will demand different parameters to be passed to then for example the jet.oledb.. needs the name of the mdb file and sqloledb need the name of the user and its password.

These all drivers are located in System.Data.OleDb namespace and hence you must include it, again they are not compatible with oledb provider for odbc. i.e. you can't use these drivers and try to access database thru you vb6.0 application so don't go finding the references of these files in c: :-)

Following guidelines are given by Microsoft while choosing the providers SQL Server:

.NET Data Provider Recommended for middle-tier applications using Microsoft SQL Server 7.0 or Later.
Recommended for single-tier applications using Microsoft Data Engine (MSDE) or Microsoft SQL Server 7.0 orlater.
Recommended over use of the OLE DB Provider for SQL Server (SQLOLEDB) with the OLE DB .NET Data Provider.
For Microsoft SQL Server 6.5 and earlier, you must use the OLE DB Provider for SQL Server with the OLE DB.NET Data Provider.
OLE DB .NET Data Provider Recommended for middle-tier applications using Microsoft SQL Server 6.5 or earlier, or Oracle.
For Microsoft SQL Server 7.0 or later, the SQL Server .NET Data Provider is recommended.
Recommended for single-tier applications using Microsoft Access databases.
Use of the OLE DB .NET Data Provider with a Microsoft Access database for a middle-tier application is notrecommended.
Support for the OLE DB Provider for ODBC (MSDASQL) is disabled.

I think i will stop for now and will continue in the (may be) next session the details of the dotnet Please let me know if code does not run, if it runs :-)

0 comments:

dotnet(.Net) Project Source code Downloads and Tutorials

Email Subscrption



Enter your email address:

Delivered by FeedBurner

Feedburner Count

Blog Archive

Unique Visitor

Design by araba-cı | MoneyGenerator Blogger Template by GosuBlogger