471,093 Members | 1,976 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes and contribute your articles to a community of 471,093 developers and data experts.

Insight: Creating database connections without using wizards

MrMancunian
569 Expert 512MB
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.

Expand|Select|Wrap|Line Numbers
  1. 'VB.NET
  2. Imports System.Data
  3. Imports System.Data.SqlClient
Expand|Select|Wrap|Line Numbers
  1. //C#
  2. using System.Data;
  3. using System.Data.SqlClient;
Next, we create the Connection, the DataSet and the DataAdapters. For each table in your database, you create a DataAdapter.

Expand|Select|Wrap|Line Numbers
  1. 'VB.NET
  2. Public cnnConnection As SqlConnection = New SqlConnection("Data Source=<Server>;Initial Catalog=<database>;User ID=<UserName>;Password=<Password>")
  3. Public dsDataSet As DataSet = New DataSet(“dsDataSet”)
  4. Public daDataAdapter1 As SqlDataAdapter = New SqlDataAdapter(“table1”)
  5. Public daDataAdapter2 As SqlDataAdapter = New SqlDataAdapter(“table2”)
  6. Public daDataAdapter3 As SqlDataAdapter = New SqlDataAdapter(“table3”)
Expand|Select|Wrap|Line Numbers
  1. //C#
  2. public Connection cnnConnection = new SqlConnection("Data Source=<Server>;Initial Catalog=<database>;User ID=<UserName>;Password=<Password>");
  3. public DataSet dsDataSet = new DataSet("dsDataSet");
  4. public SqlDataAdapter daDataAdapter1 = new SqlDataAdapter();
  5. public SqlDataAdapter daDataAdapter2 = new SqlDataAdapter();
  6. 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:

Expand|Select|Wrap|Line Numbers
  1. 'VB.NET
  2. Dim strFirstSelectQuery As String = “SELECT * FROM table1”
  3. Dim cmdFirstSelectCommand As New SqlCommand(strFirstSelectQuery, cnnConnection)
  4. daDataAdapter1.SelectCommand = cmdFirstSelectCommand
  5. cnnConnection.Open()
  6. daDataAdapter1.Fill(dsDataSet, “table1”)
  7. cnnConnection.Close()
Expand|Select|Wrap|Line Numbers
  1. //C#
  2. string strFirstSelectQuery = "SELECT * FROM table1";
  3. SqlCommand cmdFirstSelectCommand = new SqlCommand(strFirstSelectQuery, this.cnnConnection);
  4. daDataAdapter1.SelectCommand = cmdFirstSelectCommand;
  5. cnnConnection.Open();
  6. daDataAdapter1.Fill(this.dsDataSet, "table1");
  7. 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:

Expand|Select|Wrap|Line Numbers
  1. 'VB.NET
  2. daDataAdapter1.InsertCommand.ExecuteNonQuery()
Expand|Select|Wrap|Line Numbers
  1. //C#
  2. daDataAdapter1.InsertCommand.ExecuteNonQuery();
Or
Expand|Select|Wrap|Line Numbers
  1. 'VB.NET
  2. daDataAdapter1.UpdateCommand.ExecuteNonQuery()
Expand|Select|Wrap|Line Numbers
  1. //C#
  2. daDataAdapter1.UpdateCommand.ExecuteNonQuery();
I hope this will answer some questions. If not, post your questions in the .NET forum!

Steven
Apr 7 '09 #1
0 4440

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Martin Horn | last post: by
35 posts views Thread by Terry Jolly | last post: by
4 posts views Thread by atiq | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.