C# Database Connection Example Program

We were given a programming assignment in my network programming class that is defined as below. This is my solution to the assignment. I know it isn’t the best code in the world, but it works and will hopefully help someone else who is in need of knowing how to connect to a MSQL database using C#.

Write a program that will connect to the Image Database, list the categories from the category table then when the user selects a category in the list, show the name of all images in that category in another list box. To access the database, you will need to be logged into CSEVPN.

Form Design

  • Add an Open button that will connect to the database.
  • Add a Category ListBox.
  • Add an Image Title ListBox.

Open Button Event Routine

  • You will need to use System.Data.SqlClient name space.
  • You will need to define the fillowing database objects:
    • SqlConnection connct = null;
    • SqlCommand cmd = null;
    • SqlDataReader dr = null;
  • Please click here and read about the SQL select statement.
  • Please read about the SqlConnection, SqlCommand and SqlDataReader in your IDE helps. You should also be able to use ODBC drivers and objects.
  • You will need the following database connection string with your connection object:
    “user id=ImageDB;password=1mag3Database;data source=CNS-LOVE.csevpn.local;persist security info=False;initial catalog=ImageArchive”
    This database has two tables you will be using: Categories and Images.
  • The following code will select all categories and add them to the first list box.
    connct = new SqlConnection(MY_CONNECTION_STRING);
    connct.Open();
    cmd = new SqlCommand("Select * from Categories", connct);
    dr = cmd.ExecuteReader();
    while (dr.Read())
    {
    	lstCategory.Items.Add(dr["Category"].ToString());
    }
  • YOU MUST CLOSE OUT ALL DB OBJECTS WHEN YOU FINISH WITH THEM:
    if (dr != null)
    {
    	dr.Close();
    	dr.Dispose();
    	dr = null;
    }
    if (cmd != null)
    {
    	cmd.Dispose();
    	cmd = null;
    }
    if (connct != null && connct.State == ConnectionState.Open)
    {
    	connct.Close();
    	connct.Dispose();
    	connct = null;
    }
  • You will need to use the MouseDoubleClick event for your selection in the Category Listbox:
    private void lstCategory_MouseDoubleClick(object sender, MouseEventArgs e)
    {
    
    }
  • To get the correct image, you need to use a select with a WHERE clause:
    SELECT CIndex, Description FROM Images WHERE CIndex = n
    where n = the category number. Do not select all fields from the Image table, the picture field values are very large.
    So, where do you get a value for n? One simple solution is, when you read a category record, create an object that has the “Category” text and the “CIndex” value from the table. You can then override the ToString() method and add your new object’s reference to the category listbox items list.
  • The value you will add to the image list box will be from the table field “Description&quot.

Here is a screen shot of my program actually running:

Database Connection Program

Here is a copy of my code:

FormMain.cs:
 1 using System;
 2 using System.Collections.Generic;
 3 using System.ComponentModel;
 4 using System.Data;
 5 using System.Drawing;
 6 using System.Text;
 7 using System.Windows.Forms;
 8 using System.Data.SqlClient;
 9 
10 namespace DatabaseConnect
11 {
12     public partial class FormMain : Form
13     {
14         SqlConnection connct = null;
15         SqlCommand cmd = null;
16         SqlDataReader dr = null;
17         String MY_CONNECTION_STRING = "Data Source=CNS-LOVE.csevpn.local;Initial Catalog=ImageArchive;Persist Security Info=True;User ID=ImageDB;Password=MyPassword";
18         System.Collections.ArrayList categories;
19 
20         public FormMain()
21         {
22             InitializeComponent();
23         }
24 
25         private void buttonOpen_Click(object sender, EventArgs e)
26         {
27             connct = new SqlConnection(MY_CONNECTION_STRING);
28             connct.Open();
29             cmd = new SqlCommand("Select * from Categories", connct);
30             dr = cmd.ExecuteReader();
31             categories = new System.Collections.ArrayList();
32             while (dr.Read())
33             {
34                 categories.Add(new Category(dr["Category"].ToString(), Int16.Parse(dr["CIndex"].ToString())));
35                 listCategories.Items.Add(dr["Category"].ToString());
36             }
37             if (dr != null)
38             {
39                 dr.Close();
40                 dr.Dispose();
41                 dr = null;
42             }
43             if (cmd != null)
44             {
45                 cmd.Dispose();
46                 cmd = null;
47             }
48             if (connct != null && connct.State == ConnectionState.Open)
49             {
50                 connct.Close();
51                 connct.Dispose();
52                 connct = null;
53             }
54         }
55 
56         private void listCategories_DoubleClick(object sender, EventArgs e)
57         {
58             listImages.Items.Clear();
59             connct = new SqlConnection(MY_CONNECTION_STRING);
60             connct.Open();
61             cmd = new SqlCommand("Select * from Images WHERE cIndex =@n", connct);
62             cmd.Parameters.AddWithValue("@n", ((Category)(categories[listCategories.SelectedIndex])).getId());
63             dr = cmd.ExecuteReader();
64             while (dr.Read())
65             {
66                 listImages.Items.Add(dr["FileName"].ToString());
67             }
68             if (dr != null)
69             {
70                 dr.Close();
71                 dr.Dispose();
72                 dr = null;
73             }
74             if (cmd != null)
75             {
77                 cmd.Dispose();
77                 cmd = null;
78             }
79             if (connct != null && connct.State == ConnectionState.Open)
80             {
81                 connct.Close();
82                 connct.Dispose();
83                 connct = null;
84             }
85         }
86 
87     }
88 }



Category.cs
 1 using System;
 2 using System.Collections.Generic;
 3 using System.Text;
 4 
 5 namespace DatabaseConnect
 6 {
 7     class Category
 8     {
 9         private String name;
10         private Int16 id;
11 
12         public Category(String n, Int16 i)
13         {
14             name = n;
15             id = i;
16         }
17 
18         public String getName()
19         {
20             return name;
21         }
22 
23         public Int16 getId()
24         {
25             return id;
26         }
27     }
28 }

8 Responses to “C# Database Connection Example Program”

  1. :*- I am really thankful to this topic because it really gives great information ,,*

  2. Nayan says:

    Thanks. it is highly helpfull

  3. Alberta says:

    Great blog here! Also your site loads up fast!

    What web host are you using? Can I get your affiliate link to your
    host? I wish my website loaded up as fast as yours lol

  4. dota 2 says:

    Love the site– really user friendly and whole lots to see!

  5. dota 2 says:

    Unbelievably individual pleasant website. Great info readily available on couple of clicks

  6. Hwa Authur says:

    In the awesome design of things you actually receive a B- with regard to hard work. Exactly where you confused everybody was first on the specifics. You know, people say, details make or break the argument.. And that couldn’t be much more true in this article. Having said that, permit me reveal to you just what did deliver the results. Your article (parts of it) is certainly really engaging which is most likely why I am making an effort to opine. I do not make it a regular habit of doing that. Next, while I can easily notice a leaps in reason you make, I am not convinced of exactly how you appear to unite your ideas which in turn make your conclusion. For the moment I will, no doubt subscribe to your point but wish in the foreseeable future you actually link the dots better.

  7. Felix says:

    Hi there to all, since I am really eager of reading this web
    site’s post to be updated regularly. It consists of nice information.

Leave a Reply


Switch to our mobile site