how do you open an ODBC connection?

  • Thread starter Thread starter Mojtaba Faridzad
  • Start date Start date
M

Mojtaba Faridzad

Hi,

I am newbie in C# and I am trying to design my first database program and I
am trying to find the best solution. The database is MySQL.

how do you open your connection in a big application? as I know, I should
open a connection, set the dataset and close the connection. I should open
the connection in "try" block and close the connection in "finally" block.
is that right? I can write a service to pass the query and retreive the
dataset. but if there are many queries in a block of program, on each query,
connection should be opened and closed. is it better to open the query on
the beginning of this block and set all datasets and run all queries, then
close the connection at the end. by this I should have 2 different services,
one with opening and closing the connection, the other just uses the opened
connection.

any idea is appreciated!
 
Hi Mojtaba
For MySQL connection you would be using an OleDbConnection object ( no need
to use ODBC) .
It is a good approach to open the connection and a try block , fill the
dataset , and close the connection in the finally block .
However , if you are going to use Dataset , there is no need to leave the
connection open between queries . The dataset object should be used in what
is called a Disconnected environment you just open the connection once ,
fill the dataset with the help of a DataAdapter object . then close the
connection and do all your type of operations on the dataset ( update ,
insert , delete. The dataset don't only hold the data of your database but
also the relational model of your data source (tables structures, primary
and foreign keys, constrains) , you can think of it as in memory copy of
your database . once you are done with all your modification on the set .
open the connection and update back your data source ( for the changes that
has been made on the dataset ) of course this is only one model among many
that can be used while working with a database
Checkout this source code examples
http://www.codeproject.com/cs/database/AbdMySqlSchema.asp

also look at these links
http://www.devhood.com/tutorials/tutorial_details.aspx?tutorial_id=576
http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=1288&lng
WId=10
http://www.c-sharpcenter.com/Tutorial/DB_part3.htm
http://www.c-sharpcorner.com/Database.asp

Mohamed Mahfouz
MEA Developer Support Center
ITworx on behalf of Microsoft EMEA GTSC
 
thanks Mohamed! that was a wonderful help. by your comment and those links
I could find the answer of my questions.

as I know MySQL does not support anymore OleDb connection (version 4). I
guess I should continue to use ODBC driver.

I am using MyISAM tables in MySQL. as you know, we don't have relationship
like INNODB. but dataset support relationship. when I fill the dataset, can
I set the relationship and after changes, update the mysql?

we can set primary key in dataset. if the table in mysql is very big, does
that make sense to load all data to the dataset and use the advantages of
dataset? how do you work with big tables (many records and many fields).

thanks again
 
Thanks Mark! did you check yourself MySQLDirect? is it faster than ODBC
connection? the trial version doesn't let me to retreive huge tables to
compare the time. if you have to retrieve 150000 records with 100 fields
(for example), how long it takes time in ODBC and MySQLDirect?

thanks
 
Thanks Mark! did you check yourself MySQLDirect? is it faster than ODBC
connection? the trial version doesn't let me to retreive huge tables to
compare the time. if you have to retrieve 150000 records with 100 fields
(for example), how long it takes time in ODBC and MySQLDirect?
Click to expand...

I'm not a mySQL user myself but, based on other RDBMS, the native .NET data
provider generally gives the best performance...
 
I tested a query with both method. by ODBC it took 16 seconds; by
MySQLDirect it took 9 seconds. I guess we go by Direct. Thanks Mark!
 
Back
Top