Creating a database connection

Introduction

Microsoft Visual Studio .NET is a new revolution in computer programming. I am grateful that Microsoft developed such a great IDE for us. .NET had a lot of changes to the way we program and what we program in (especially in Visual Basic). ADO.NET had also a substantial difference from its previous versions. It's almost a completely new language! :)

I'll show how to create a DB Connection through either OLE or SQL in runtime using C# as well as VB. Drag and dropping in the IDE is really easy, but there are times when you need to do it in runtime. SQL and OLE both have their own namespaces in the .NET Framework.  They contain every single object and method that relate to that type of connection. Of course there is the namespace for all the General Data handling, which includes objects like the dataset.

OLE = System.Data.OleDB
SQL = System.Data.SqlClient
General Data = System.Data

Step #1: Declare the Connection Object

for OLE:
    VB:  Friend WithEvents OleConn as new System.Data.OleDB.OleDBConnection()
    C#:  internal System.Data.OleDB.OleDBConnection OleConn;

for SQL:
    VB:  Friend WithEvents SqlConn As New System.Data.SqlClient.SqlConnection()
    C#:  internal System.Data.SqlClient.SqlConnection SqlConn;

Some may wonder why I applied the WithEvents and Friend or internal modifiers to the objects.

Reason for WithEvents:
    -We can create procedures to handle the connection's events... like the StateChanged event

Reason for internal or Friend:
    -Make it available throughout the whole project

The Connection String

By far one of the most important properties of the Connection object would have to be the Connection String!  It specifies where to connect, who is connectiong, what database to look at, etc.  The connection string is the backbone of the Connection object.  Ole and SQL both use Connection Strings.

The connection string is formatted like this:

"keyword=value;keyword=value;keyword=value;keyword=value"

You get the idea.

Some Keywords:
  SQL:
      database - what database to look at
      server - the server that you are connecting to
      trusted_connection - whether or not the machine is trusted (don't need uid/pw)
      uid - username
      pwd - password
  OLE:
      data source - Where the file is...
      provider - the Data provider

Examples:
    SQL:  "server=tonydev;database=northwind;uid=tony;pwd=hrmmm"
             "server=tonydev;database=northwind;trusted_connection=yes"

    OLE: "Provider=Microsoft.Jet.OLEDB.4.0; Data source=C:\tony.mdb"

Both the SQL and OLE Connection object have a property called 'ConnectionString', so the first thing you do after declaring the objects is set its connection string. You can generate a sql connection string using our free tool.

Opening and Closing

After declaring your connection and setting the Connection String, the next thing to do is to open the connection, and also learn how to close it! ;)

Before you can perform any data reading or any forms of manipulation, you will have to open the connection first. Every connection object has a method we call Open as well as Close.

SQL:
   VB: SqlConn.Open()
   C#: SqlConn.Open();
OLE:
   VB: OleConn.Open()
   C#: OleConn.Open();

Wow pretty... simple isn't it?  The Close() Method is the same as the Open.  Just call it and It will close the corresponding conneciton object.... like in... OleConn.Close()

I have created a Sample application (created in VB) for you to skim over; just click 'Download Source Code' above. If there are any errors please inform me!! :) Have fun and I hope this 'tutorial' helps ya ;)

You might also like...

Comments

About the author

Tony Ho United States

Currently attending Sophmore Year at Henry Clay High School, I have recently started on .NET and created a couple of sample projects. Working on my MCAD!

Interested in writing for us? Find out more.

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“Some people, when confronted with a problem, think "I know, I’ll use regular expressions." Now they have two problems.” - Jamie Zawinski