Insight: Creating database connections without using wizards

[MrMancunian]

How to create a database connection without using wizards

Introduction

I've seen a lot of questions on the net about getting data from, and saving data to databases. Here's a little insight how to do that using .NET code. I prefer using code instead of wizards, because you are completely in charge of connections. This article is written for both Visual Basic.NET 2005 and C# 2005 and will discuss how to use an MS Sql database. OleDB works just the same, but uses a different namespace and components. For more information please see how to use a database in your program part II.

Before you start yelling that this code could be much more effective: I know! This article has been written so people who are new to this can see how it works in code, step by step. Otherwise, you might as well use a wizard.

There are several ways of interacting with your database. This article explains how to interact with it using a DataSet and a DataAdapter. You can also use components like a DataReader. For more information please see how to use a database in your program part I.

Overview

To connect with your database and exchange data with it, you will need three components:

• A Connection
• A DataSet
• A DataAdapter

The Connection is the component that represents an open connection to the SQL Server database. The Connection control requires that you provide it with a string that contains information about the database connection. This string is called a ConnectionString. It contains special syntax that indicates the location of the database, which catalog to open, the authentication type to use to connect to the database and the credentials.

The DataSet is a local representation of your database. The DataSet consists of DataTable Objects, which are a representation of the tables in the database. A lot of people think that the DataSet itself holds the data, but it doesn’t. The actual data is stored within the DataTable Objects. The data in the DataTables are inserted, updated and deleted by commands that are stored in a DataAdapter.

Getting started

First, we include the namespaces that contain the classes we're going to be using. For the DataSet, we need System.Data, the Sql Components require System.Data.SqlClient.

'VB.NET
Imports System.Data
Imports System.Data.SqlClient

//C#
using System.Data;
using System.Data.SqlClient;

Next, we create the Connection, the DataSet and the DataAdapters. For each table in your database, you create a DataAdapter.

'VB.NET
Public cnnConnection As SqlConnection = New SqlConnection("Data Source=<Server>;Initial Catalog=<database>;User ID=<UserName>;Password=<Password>")
Public dsDataSet As DataSet = New DataSet(“dsDataSet”)
Public daDataAdapter1 As SqlDataAdapter = New SqlDataAdapter(“table1”)
Public daDataAdapter2 As SqlDataAdapter = New SqlDataAdapter(“table2”)
Public daDataAdapter3 As SqlDataAdapter = New SqlDataAdapter(“table3”)

//C#
public Connection cnnConnection = new SqlConnection("Data Source=<Server>;Initial Catalog=<database>;User ID=<UserName>;Password=<Password>");
public DataSet dsDataSet = new DataSet("dsDataSet");
public SqlDataAdapter daDataAdapter1 = new SqlDataAdapter();
public SqlDataAdapter daDataAdapter2 = new SqlDataAdapter();
public SqlDataAdapter daDataAdapter3 = new SqlDataAdapter();

Getting to the data

Now we declared all necessities, it’s time to fill the DataTables with data. In order to do this, we need to tell the DataAdapters what data we want. This is done with a SQL query. First, you create a string holding the SQL query. Next, we create the SqlCommand to select the data and after that, we tell the DataAdapter to use that command. It will look something like this:

'VB.NET
Dim strFirstSelectQuery As String = “SELECT * FROM table1”
Dim cmdFirstSelectCommand As New SqlCommand(strFirstSelectQuery, cnnConnection)
daDataAdapter1.SelectCommand = cmdFirstSelectCommand
cnnConnection.Open()
daDataAdapter1.Fill(dsDataSet, “table1”)
cnnConnection.Close()

//C#
string strFirstSelectQuery = "SELECT * FROM table1";
SqlCommand cmdFirstSelectCommand = new SqlCommand(strFirstSelectQuery, this.cnnConnection);
daDataAdapter1.SelectCommand = cmdFirstSelectCommand;
cnnConnection.Open();
daDataAdapter1.Fill(this.dsDataSet, "table1");
cnnConnection.Close();

Inserting and updating

Inserting and updating your database is pretty simple. In fact, it looks a lot like above code. The only difference is that you use a different SQL query and instead of using a SelectCommand, you use either an InsertCommand or an UpdateCommand. The last difference is that you don’t fill the DataAdapter when you open the Connection. Instead, you use the following code:

'VB.NET
daDataAdapter1.InsertCommand.ExecuteNonQuery()

//C#
daDataAdapter1.InsertCommand.ExecuteNonQuery();

Or

'VB.NET
daDataAdapter1.UpdateCommand.ExecuteNonQuery()

//C#
daDataAdapter1.UpdateCommand.ExecuteNonQuery();

I hope this will answer some questions. If not, post your questions in the .NET forum!

Steven