473,320 Members | 2,020 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,320 developers and data experts.

How To Use A Database In Your Program

Frinavale
9,735 Expert Mod 8TB
How To Use A Database In Your Program
Many .NET solutions are database driven and so many of us often wonder how to access the database. To help you understand the answer to this question I've provided the following as a quick example of how to retrieve data from a database.

In order to connect to a SQL Server Database using .NET you will need to import the System.Data.SqlClient package into your program.

If you are not connecting to a SQL Server Database, take a look at InsertAlias's article named How to use a database in your program Part II for information on how to connect to other databases.


Connection String
The first thing you'll need to do (after you've created the database) is connect to it. In order to do this you have to define something called a Connection String.

The Connection String is used as a means to locate the database your program will be using. It also contains the information on the type of authentication to use in order to access the database. This means that the connection string may contain a user name and password.

A Connection String is typically formatted as follows:

VB Code Example:
Expand|Select|Wrap|Line Numbers
  1. Dim connectionString As String ="data source=<nameOfDatabaseServer>;initial catalog=<nameOfDatabase>;user id=<databaseUserId>;password=<passwordForDatabaseUser>" 



C# Code Example:

Expand|Select|Wrap|Line Numbers
  1. String connectionString = "data source=<nameOfDatabaseServer>;initial catalog=<nameOfDatabase>;user id=<databaseUserId>;password=<passwordForDatabaseUser>"; 

Since Connection Strings contain very sensitive information on how to access the database so it is crucial that you protect it (by encryption or other means). If you are implementing a web application, it is a good idea to store this information in the web.config file so that it is easily protected.

SqlConnection
Once you have created a connection string you have to create a SqlConnection object. This object uses the connection string to form a connection to the database for you.

VB Code Example:
Expand|Select|Wrap|Line Numbers
  1. dim dbCon As SqlConnection
  2. dbCon = New SqlConnection(connectionString) 


C# Code Example:
Expand|Select|Wrap|Line Numbers
  1. SqlConnetion dbCon = new SqlConnection(connectionString); 


SqlCommand
After you have created an instance of a SqlConnection object, you need to create a SqlCommand. This object is used to carry out the sql commands that will be executed by the database.

VB Code Example:
Expand|Select|Wrap|Line Numbers
  1. Dim sqlCom As New SqlCommand 


C# Code Example:
Expand|Select|Wrap|Line Numbers
  1. SqlCommand sqlCom = new SqlCommand(); 


Once we have an instance of this object we need to set some of its properties.

First of all you have to specify what type of SqlCommand you are going to be executing. This is where you specify if you are using a store procedure or if you'll be supplying a SQL statement to be carried out. In this example we'll cover how to supply your own SQL statement.

You'll have to set the SqlCommand's "CommandText" property to be the SQL commands that need to be carried out by the function you're creating.

Once you've set the "CommandText" property, you'll have to add the values of any parameters used in the SQL command statement. You do this by setting the SqlCommand's "Parameters" property.

You also have to set the SqlCommand object's "Connection" property to the SqlConnection object you created earlier in order to connect to the database.

For example, if you would like to insert a new contact entry into a table you would set the SqlCommand's "CommandText" to be something like:

VB Code Example:
Expand|Select|Wrap|Line Numbers
  1. sqlCom.Connection = dbCon
  2. sqlcom.CommandType = CommandType.Text
  3. sqlCom.CommandText = "INSERT INTO CONTACT(cID, cAddress, cPhoneNumber) " _ +
  4.                      "VALUES(@cID,@cAddress,@cPhoneNumber)"
  5. sqlCom.Parameters.Add("@cID", SqlDbType.Int).Value = 1234567
  6. sqlCom.Parmaeters.Add("@cAddress", SqlDbType.VarChar).Value = "123 Hypoth. Street..."
  7. sqlCom.Parameters.Add("@cPhoneNumber",SqlDbType.VarChar).Value="1-800-999-99999" 



C# Code Example:
Expand|Select|Wrap|Line Numbers
  1. sqlCom.Connection = dbCon;
  2. sqlcom.CommandType = CommandType.Text;
  3. sqlCom.CommandText = "INSERT INTO CONTACT(cID, cAddress, cPhoneNumber) " +
  4.                      "VALUES(@cID,@cAddress,@cPhoneNumber)";
  5. sqlCom.Parameters.Add("@cID", SqlDbType.Int).Value = 1234567;
  6. sqlCom.Parmaeters.Add("@cAddress", SqlDbType.VarChar).Value = "123 Hypoth. Street...";
  7. sqlCom.Parameters.Add("@cPhoneNumber",SqlDbType.VarChar).Value="1-800-999-99999"; 


Executing Your SQL Commands
After you've set up everything you can carry out your SQL commands on the database.
To do this you have to:
  • first open a connection to the the database
  • and then execute the SQL command
  • and finally close the connection

VB Code Example:
Expand|Select|Wrap|Line Numbers
  1. Try
  2.   dbCon.Open()
  3.   sqlcom.ExecuteNonQuery
  4.   dbCon.Close()
  5. Catch ex As Exception
  6. End Try 



C# Code Example:
Expand|Select|Wrap|Line Numbers
  1. try
  2. {
  3.   dbCon.Open();
  4.   sqlcom.ExecuteNonQuery();
  5.   dbCon.Close();
  6. }
  7. catch(Exception ex)
  8. {} 

In the above code we used the ExecuteNonQuery method to execute the SQL command because a SQL command that updates a table does not return any results. The ExecuteNonQuery method returns the number of rows that were updated. You can use this to determine if any rows were updated by checking to see if sqlcom.ExecuteNonQuery > 0.

If you are executing a SQL command that returns a result, such as executing a SELECT statement you will have to use a different method. The SqlCommand's ExecuteReader method returns a SqlDataReader object that contains all of the records retrieved after executing the SQL command.

The following snippet of code shows you how to store the results into a SqlDataReader object and how to access the data.

VB Code Example:
Expand|Select|Wrap|Line Numbers
  1. Try
  2. Dim dr As SqlDataReader
  3. dbCon.Open()
  4. dr = sqlcom.ExecuteReader
  5. If dr.HasRows = True Then
  6.   txt_clientID.Text = CType(dr("cID"),Integer).ToString()
  7.   txt_clientAddress.Text = CType( dr("cAddress"),String)
  8.   txt_clientPhoneNumber.Text = CType(dr("cPhoneNumber"),String)
  9. End If
  10. dr.Close()
  11. dbCon.Close()
  12. Catch ex As Exception
  13. End Try 


C# Code Example:
Expand|Select|Wrap|Line Numbers
  1. try
  2. {
  3.   SqlDataReader dr;
  4.   dbCon.Open();
  5.   dr = sqlcom.ExecuteReader();
  6.   if(dr.HasRows == True)
  7.   {
  8.     txt_clientID.Text = ((Integer) dr["cID"]).ToString();
  9.     txt_clientAddress.Text = (String) dr["cAddress"];
  10.     txt_clientPhoneNumber.Text = (String) dr["cPhoneNumber"];
  11.   }
  12.   dr.Close();
  13.   dbCon.Close();
  14. }
  15. catch(Exception ex)
  16. {} 
Once you are finished with your SqlDataReader object be sure to close it to clean up.

If your Sql command returns more than one row, you will have to loop through the data reader to retrieve all of the information.

For example the following will add all of the "clientID's" to the TextBox txt_clientID:

VB Code Example:
Expand|Select|Wrap|Line Numbers
  1. While dr.Read
  2.   txt_clientID.Text =  txt_clientID.Text + CType(dr("cID"),Integer).ToString()
  3. End While 



C# Code Example:
Expand|Select|Wrap|Line Numbers
  1. while(dr.Read())
  2. {
  3.   txt_clientID.Text =  txt_clientID.Text + ((Integer)dr["cID"]).ToString();
  4. }

Other Notes
The objects used to connect to a database implement the iDisposable interface. Because of this it is Strongly advisable to define a Dispose() method that will properly handle the disposal of any connections. Be sure to look up how to implement this method to properly clean up your memory. It really helps to manage your resources.

Hope you find this useful!

-Frinny
Apr 20 '07 #1
22 60074
Niheel
2,460 Expert Mod 2GB
This is a good article. Very detailed.
Nov 21 '07 #2
Friend
I know how to connect to a database and it's connection string also

but this error not from the connection string.

give a good solution.

Thanx for the reply
could you tell me for one example . because it is first time for me to write
i know in VB.net but i don't know in C# by using Console
Dec 3 '07 #3
coul you tell me it is first time for me i know how i connect in VB.net i have so many how work next week .
just for one example in C# by using Console
Dec 3 '07 #4
Frinavale
9,735 Expert Mod 8TB
The example is almost exactly the same for C#.
You just have to change the syntax from VB.NET to C#.

For example, you'd declare your variables like this in VB:
Expand|Select|Wrap|Line Numbers
  1.       Dim dbCon As SqlConnection
  2.  
And in C# you would declare your variables like this:
Expand|Select|Wrap|Line Numbers
  1.       SqlConnection dbCon;
  2.  
Since both C# and VB.NET use the same Framework, you will have access to the same data types in both languages.

Try to follow the example and when you run into problems post your question about the specific problem in the .Net forum.

-Frinny
Dec 3 '07 #5
ssknov
40
hi frinav
fine article.
it ll be good if u can add examples for Executescalar, Datatable.

thnk u
kssk
Jan 2 '08 #6
Frinavale
9,735 Expert Mod 8TB
hi frinav
fine article.
it ll be good if u can add examples for Executescalar, Datatable.

thnk u
kssk
I've never heard of ExecuteScalar, DataTable...
Do you mean a GridView (or DataGrid)??

-Frinny
Jan 2 '08 #7
If you're using .NET 3.5, consider using LINQ (Language Integrated Query).

Assume that you have a customer database. The following query would select the customers in Oregon:

Customer[] Customers = GetCustomers();
var query =
from c in Customers
where c.State == "Oregon"
select c;

Quick and easy!
John
Mar 3 '08 #8
Frinavale
9,735 Expert Mod 8TB
If you're using .NET 3.5, consider using LINQ (Language Integrated Query).

Assume that you have a customer database. The following query would select the customers in Oregon:

Customer[] Customers = GetCustomers();
var query =
from c in Customers
where c.State == "Oregon"
select c;

Quick and easy!
John
Thanks for the info John :)
I'm going to have to look up LINQ!

-Frinny
Mar 4 '08 #9
dwadish
129 100+
I know the connectivity with framework 2.0 and other previous versions but 3.5
LINQ
Wow really fantastic
Mar 7 '08 #10
reese
8
How About If Im Going To Use A Database To Connect In A Program Through Php???
How Should It Be Done???
Mar 16 '08 #11
Frinavale
9,735 Expert Mod 8TB
How About If Im Going To Use A Database To Connect In A Program Through Php???
How Should It Be Done???
That is a good question to ask the PHP experts. This article was intended for a .NET audience. Check out the PHP how-to articles, specifically the one about Creating a Data Abstraction Layer in PHP, and also the one on Uploading files into a MySql Database using PHP.

-Frinny
Mar 17 '08 #12
hey Frinny,

the article you posted about limking vb.net with sql database was informative but i'm a newbie and i need some further assistance to be able to make a project i have been assigned. if you have some other articles or a code bank or something of that fashion kindly advise....

regards,

gaurav
Jun 20 '08 #13
Frinavale
9,735 Expert Mod 8TB
hey Frinny,

the article you posted about limking vb.net with sql database was informative but i'm a newbie and i need some further assistance to be able to make a project i have been assigned. if you have some other articles or a code bank or something of that fashion kindly advise....

regards,

gaurav
At this time I don't have any other materials on using databases.
You should post your specific questions in the .Net Forum. You should get the help you need there.

-Frinny
Jun 20 '08 #14
r035198x
13,262 8TB
Checking the returned value of ExecuteNonQuery with 1 to determine success is not correct. The method returns the number of rows updated.
Nov 19 '08 #15
Frinavale
9,735 Expert Mod 8TB
Checking the returned value of ExecuteNonQuery with 1 to determine success is not correct. The method returns the number of rows updated.
Thanks r0.
I fixed it.
Nov 19 '08 #16
michaeldebruin
134 100+
Heey Frinavale,

I've this article about how to connect to a microsoft SQL server. But how about creating a program with C# and connecting that one to a microsoft access Database? Is it the same way or are there some differences? Especially looking to the way to put some data into the database and getting data out of it.

Kind regards,

Michael
Dec 16 '11 #17
Frinavale
9,735 Expert Mod 8TB
Using an Access Database in your C# application isn't much different than what this article covers.

Instead of using a SqlConnection, you will use an OleDbConnection. There are a few syntax changes that need to be made to get this to work (ie: your SQL commands and the characters you supply for using parameters etc.) but the concept is the same. Check out this MSDN article about Creating Connections to Access Databases.

-Frinny
Dec 20 '11 #18
First time posting here - please excuse my ignorance.

Could you help output the results from SQL? The result if there is at least one row in the table is sitting in memory. I would like to be able to show those results on an ASP.NET page, in a table or something.

As I follow along with the tutorial I can look at my database and table within to see the result of successful inserts. I don't have the equivalent for successful selects. It would greatly enhance this example to add a method of seeing that you are successfully inserting and selecting - what better way than sending the results to a dynamically generated table.
Feb 1 '12 #19
Mudassir
85 64KB
exactly, you can append the label by looping through the dataset you retrieved from the sql server
Jun 21 '12 #20
How can i browse database name before connect?
Apr 3 '13 #21
r035198x
13,262 8TB
If you have questions then ask them in the questions area.
Apr 3 '13 #22
pod
298 100+
You can use ExecuteScalar when you expect only one value as "Get the Maximum ID"
Expand|Select|Wrap|Line Numbers
  1. SELECT MAX(ID) as maxid FROM tblData 
Apr 3 '13 #23

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

Similar topics

10
by: NotGiven | last post by:
I have never used a config file - my config will be a database record. I know how to get the record for that user. Is it simply using the elements of the recordset array to set page by page...
5
by: Mike | last post by:
Does python support named semaphores for Linux? I saw that ActivePython for Win32 does. Can I get a list of currently running processes? I already posted in the GTK forum about looking for the...
51
by: John Baker | last post by:
Hi: Some time ago I developed a program in Access, and separated the database and the program itself (using the normal access tools).We have the db on our server and the programin the desktop...
2
by: Patrick Glenn | last post by:
I am looking for an online zip code program / database, that will aloo me at a minimum to be able to type in a zip code, a distance (5mile, 10miles, 50miles, etc) and have the process return back...
2
by: andreas | last post by:
Hi all! I'm wondering if you have any good suggestions on program to create an ER diagram of a database... (sort of like the one shipped with MS SQL (sorry for cursing in this group :))) ...
1
Curtis Rutland
by: Curtis Rutland | last post by:
How To Use A Database In Your Program Part II This article is intended to extend Frinny’s excellent article: How to Use a Database in Your Program. Frinny’s article defines the basic concepts...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
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: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
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...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

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.