Simple DataBase connection

I need working code and explications about how to connect to a simple database such as MS SQL server DB or MS ACCESS. As I understood C# allows to connect directly to 4 database types. How about the other databases? For example if I want to connect to a Informix database what would I have to do to achieve this?
I hope someone can explain me from a delphi programmer point of view.
LVL 11
Who is Participating?
drichardsConnect With a Mentor Commented:
It depends on how you want to display the data.  If you want to display one of the columns in a listbox, for example, the easiest thing to do is to use the DataAdapter and a DataSet.  In Visual Studio, go to the server explorer and add a new data connection.  Use the Microsoft OLE DB Provider for ODBC and then select the DSN name.  Test the connection and make sure it works (there's a "Test Connection" button on the wizard page where you enter the DSN).  Once that is working, open the for in the editor and drag the new connection onto your form.  It will create the connection object for you.  Then go to the Toolbox and drag an OdbcDataAdapter onto the form.  Fill in the appropriate information in the wizard.  Finally, right click on the DataAdapter it just created and select "Generate DataSet..." and fill in the information.  Now select the listbox and in the properties, set the 'DataSource' and 'DisplayMember' properties.

Finally, to make it all work, go into the form constructor (code view now) and add:

    odbcDataAdapter1.Fill(dataSet1);  // Use correct names, of course

When you fire up the form, the listbox will have the appropriate data.

If you want more direct control over what's displayed, here's a way to read the data directly:

    System.Data.Odbc.OdbcConnection cxn = new System.Data.Odbc.OdbcConnection("DSN=adaos");
    System.Data.Odbc.OdbcCommand cmd = cxn.CreateCommand();
    cmd.CommandText = "SELECT * FROM Names;";
    cmd.CommandType = System.Data.CommandType.Text;
        System.Data.Odbc.OdbcDataReader rdr = cmd.ExecuteReader();
        while (rdr.Read())
            // Access the column data and populate form:  rdr.GetValue(<column_number>).ToString() or rdr.Get???
    catch (System.Data.Odbc.OdbcException ex)
        foreach ( System.Data.Odbc.OdbcError er in ex.Errors)
C# (.NET in general) lets you connect to any database with an OleDB or ODBC driver as well as Oracle and Sql Server via the System.Data.OleDb, System.Data.Odbc, System.Data.OracleClient, and System.Data.SqlClient namespaces, respectively.  If you have an ODBC or OleDb driver for informix, then you can use it.

You start with a Connection, and then your usage depends on what you are trying to achieve.  Each of the namespaces has a connection object:


You need an appropriate connection string to initialize the connection and then you Open it.  Then you can create commands or use the data adapters to populate DataSets
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

calinutzAuthor Commented:
"You need an appropriate connection string to initialize the connection and then you Open it."

Please let me know exactly what steps I need to do to connect to my informix database:
hostname = srv_ix
server = servername
database = adaos
(ODBC) DSN = adaos
username = informix
password = pass

Next: what components should I place on my "form" and what properties do I need to set? Especially I need to know how to build that connection string.
Suppose I only need to query a table from the database... say : "select * from table1"

A piece of code would be also nice.
If you've got a DSN defined, your connection string will be:


and you should use the OdbcConnection.

I'll send you a couple of code options for reading the database when I get back in the office tomorrow.
calinutzAuthor Commented:
You will receive the points as soon as I succeed to build a small test app. And your answer looks very nice . I hope I will handle it well.
Sorry for the delayed reply but I was out of town for a couple of days.
I don't give you the points yet ... hoping to solve my problem first.
Maybe later today.
Thanks again.
calinutzAuthor Commented:
First of all I appologize for the delay. Then I will increase the points to 500 to show you that I appreciate your help. Then I must say that I did not succeed yet to build an app based on your input. My main problem may be that I use Borland C# Builder and not Microsoft's C#.
   I will try again these days, but if there is anything else you could add to help me I would appreciate it. Anyways seems like there's noone else interested in helping a begginer not even for points. If the question would have been placed in the Delphi area with a delphi subject there would be about 5 to 10 guys anxious to respond.
Thanks again.
I will close this question as soon as I will succeed building this silly application. In Delphi this is actual child play. I don't understand why are there no programers willing to share their knowledge. Because I am quite sure that most of you use databases in your applications...

 There are a lot of places for C# programmers on the web but I actuallly am involved in an important project right now and don't have the time to investigate myself the problem.
 So please give me a direction... something to start with in my C# Builder
What problem(s) do you have?  The code I posted is working C# code.  It shouldn't make a difference what environment you use to compile it.  You just need to put in a valid DSN name and command text.

Do you get an exception?  If so, what's the error message?  If not, what is happening?
calinutzAuthor Commented:
In order to thank you for your assistance I will accept your answer an probabely re-ask this question again sometime later (in the next few weeks). I still didn't get to test your code because I am involved in a very important and time eating project in Delphi, so I haven't hat the chance to start C# ever since I replyied you last.
Thanks again for answering me in the first place. As I mentioned above, the experts in this area (C#) are not very comunicative
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.