473,287 Members | 3,295 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes and contribute your articles to a community of 473,287 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 4701

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

Similar topics

16
by: noah | last post by:
Does PHP have a feature to associate Cookie sessions with a persistent database connection that will allow a single transaction across multiple HTTP requests? Here is how I imagine my process: I...
6
by: MAB71 | last post by:
There should be a way to copy an access database ( .mdb file ) without closing connections to it. Unfortunately the FileCopy statement in VB gives an error if users are connected to the db. But I...
4
by: Frank | last post by:
Hi I am a poor student studying towards my MCSD. I have managed to get a cheapish student version of vb.net. Problem is that this version does not connect to sql databases and I need it to...
1
by: Martin Horn | last post by:
Hi, I'm using VB Express 2005 and I want to write an application that uses an access database, but the location of the database file will be on a remote PC on a LAN and so I need to be able to...
35
by: Terry Jolly | last post by:
Web Solution Goal: Have a global database connection Why: (There will be 30+ tables, represented by 30+ classes) I only want to reference the database connection once. I put the connection...
17
Motoma
by: Motoma | last post by:
This article is cross posted from my personal blog. You can find the original article, in all its splendor, at http://motomastyle.com/creating-a-mysql-data-abstraction-layer-in-php/. Introduction:...
11
by: Jeff | last post by:
Hello everyone. I've searched through the archives here, and it seems that questions similar to this one have come up in the past, but I was hoping that I could pick your Pythonic brains a bit. ...
4
by: atiq | last post by:
I basically have a mail to form on my website. When the form is filled and submitted by the user it processes the form using the file feedback.php. What i want is a Pop up window which says "thank...
9
by: Gordon | last post by:
I want to add a feature to a project I'm working on where i have multiple users set up on my Postgres database with varying levels of access. At the bare minimum there will be a login user who...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...

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.