Data(base) access in C# / .Net

[ViperXP]

In VB6, there's a DataGrid control which I can use to easily hook up to a MS Access database, and I can read and display the contents of a table in the grid. I know database access in C#, or even .Net in general is different from back then.. I have even read the basics of data access in .Net, so I have rough ideas on things like DataSet and DataAdapters.

My question is: how can I do the same thing with C# specifically, and .Net in general? And what if my database is a Mysql database residing on localhost? I have MySQL 3.23.54 & IIS 5.1 installed.

Wherever possible, I want to work only with Access / Mysql databases, since I am not familiar with MS SQL server.

Right now, I have only managed to establish connection to an Access DB with ODBC drivers, but even with that, I cannot display / manipulate it from my program written in C#.

Help needed. Thanks in advance.

[Gumboot]

You need:

MDAC 2.7 (or higher)

ODBC.NET

MySQL

MyODBC (find it on the MySQL home page)

Then use the following code:

using Microsoft.Data.Odbc;

...

OdbcConnection connection = new OdbcConnection("Driver={MySQL};SERVER=localhost;UID=<username>;PWD=<passwor d>;DATABASE=<databasename>;");

connection.Open();

...

This uses the ODBC drivers to communicate with the database. There is apparently an effort to create a native data provider for MySQL (like MS has done with SQL server). But I'm pretty sure it's not ready yet.

Hope this helps.

[ViperXP]

thanks for the reply, Gumboot. But the link to ODBC.NET seems dead - page not found. If I already have visual studio.net installed, do i still need it?

i will try getting MyODBC first; will keep u posted.

how about manipulating Access DB in a grid then?

[[C#]]

Yes you still need it because ODBC.NET was released after NET Framweork v1.0 went RTM.

You can d/l from <a href='http://msdn.microsoft.com/downloads/default.asp?url=/downloads/sample.asp?url=/msdn-f iles /027/001/668/msdncompositedoc.xml' target='_blank'>http://msdn.microsoft.com/downloads/defaul...ompositedoc.xml</a&g t;

Related to the Access question:

1. You connect to the DB:

string scon = @"Provider=Microsoft.Jet.OLEDB.4.0 ;Data Source=c:\My Documents\krp.mdb";

OleDbConnection con = new OleDbConnection(scon);

con.Open();

2. Create the Data adapter

OleDbDataAdapter odb = new OleDbDataAdapter("SELECT * FROM MyTable", con);

DataSet ds = new DatSet();

//fill dataset

odb.Fill(ds);

Note : If you select data from only one table you can use a data table instead

of a dataset. It's lighter.

3. You bind the data set to grid

this.DataGrid.DataSource = this.ds.Tables[0].DefaultView;

4. You can edit now .....watch for the data set's events ;) :yes:

[ViperXP]

Gumboot: OK. I have successfully installed MyODBC, and made the necessary config in the ODBC Admin tool. But the code snippet u provided doesn't really get me anywhere. I am new to C#. Can u lead me to a sample code - that demos manipulation of mysql / access DBs in C#, like in some sort of data grid?

Many thanks.

[ViperXP]

Thanx [C#], i'll try to figure something out of the code chunks u posted. I have oso found a sample relating to Access ... will look at it later.

Pls keep checking this thread man, I'll need further help ;)

[Gumboot]

Did you manage to get ODBC.NET? Go to msdn.microsoft.com and search for ODBC.NET if you haven't (it should be the first hit). [ BTW, anyone else having problems posting links? They seem to have spaces inserted into them! ]

Don't have any experience with datagrids, sorry. But if you want to use MySQL, replace OleDbConnection with OdbcConnection and OleDbDataAdapter with OdbcDataAdapter. Also, make sure you add Microsoft.Data.Odbc.dll to your project references. And add "using Microsoft.Data.Odbc;" to the top of your source code.

Edited January 7, 2003 by Gumboot