ASP.Net C# Retrieving values to a list from SQL server

Adding values to a ASP.Net ComboBox(No data binding) and Retrieving data to a List from the database

This problem started when I had a problem data binding a DataTable object to a ComboBox in C# WinForms application. I have been using Java Swing framework for a while and I always used a List or an ArrayList and loops to add values in to "JComboBox"es.

So I wanted to do the same thing with WinForms :D

This tutorial discuss the following

• Connecting to the database
• Retrieving DB values to a list
• Loading List values to a ComboBox without data binding
• Loading selection values of ComboBox from database
• Getting values according to the selected value in ComboBox

Instead of adding some few code chunks. I will add a full project, so that you can build it from scratch yourself.

To demonstrate a common problem I will take 2 ComboBoxes and 2 Labels added on to a WinFrom.

1. Create a new Windows Forms Application. Make sure you give it a proper name and save it in a nice location :D. After that you will see some thing close to this 

also you will be able to see a blank form like this happily sitting on the designer

Double click on the from so the From_Load method can be automatically generated.
(or just add the damn thing manually)

Rebuild and run the project so that you can be happy about no errors ...


2. OK, since we are happy with the Form, lets add two Labels and 2 ComboBoxes in to the from.

3. Its DB time now. Fire up the SQL server and create the following table...
(Passion is the database, and sampleTable is the table name. The IDENTITY(2, 1) make the table auto increment table values. 2 is the starting point and 1 means increments one by one )



CREATE TABLE Passion..sampleTable
(
sampleID INT PRIMARY KEY IDENTITY(2,1),
sampleValue VARCHAR(50)
)

lets add the following data in to the table 

INSERT INTO Passion..sampleTable(sampleValue) VALUES('SampleValues0');

INSERT INTO Passion..sampleTable(sampleValue) VALUES('SampleValues1');

INSERT INTO Passion..sampleTable(sampleValue) VALUES('SampleValues2');

INSERT INTO Passion..sampleTable(sampleValue) VALUES('SampleValues3');

INSERT INTO Passion..sampleTable(sampleValue) VALUES('SampleValues4');

INSERT INTO Passion..sampleTable(sampleValue) VALUES('SampleValues5');

INSERT INTO Passion..sampleTable(sampleValue) VALUES('SampleValues6');

INSERT INTO Passion..sampleTable(sampleValue) VALUES('SampleValues7');

INSERT INTO Passion..sampleTable(sampleValue) VALUES('SampleValues8');

INSERT INTO Passion..sampleTable(sampleValue) VALUES('SampleValues9');

INSERT INTO Passion..sampleTable(sampleValue) VALUES('SampleValues10');

4. Its time for the crazy connection string now(this always drives me nuts). So to add the connection string, right click on the project and add an "Application Configuration File". If you keep the default name(App.config), the project will look like this. :D

5. Double click the App.config file and add a connection string to this file.
The code looks like the below...


<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <connectionStrings>
    <add name="PassionConnectionString" connectionString="Data Source=localhost\SQLEXPRESS;Initial Catalog=Passion;Integrated Security=True"
     providerName="System.Data.SqlClient" />
    </connectionStrings>
</configuration>

here's the breakdown of the above code :

• name="PassionConnectionString" is the name that we can use to access this connection string from the c# code. Also, since we can add multiple connection strings, we can identify each connection string with this distinct name.
• Data Source=localhost\SQLEXPRESS is the server path localhost\SQLEXPRESS is the default locally running server path.
• Initial Catalog=Passion Passion is the name of the database, the relevant table exist in.

6. We can call this connection string any time we like. But its a good idea to use this connection string and create a connection object class to get a connection object instead of writing connection code every single time (this was taught to me in the uni, I did'mt come up with idea).

So, lets add a new class called ConnectionManager to the project. After that the solution should look like the following.

7. Now that we have created the class, we need to add some code in to it. 

Notice the part with the Connection string name :D 

class ConnectionManager

    {

        public static SqlConnection NewCon;

        public static string ConStr = System.Configuration.ConfigurationManager.ConnectionStrings["PassionConnectionString"].ConnectionString;

        public static SqlConnection GetConection()

        {

            NewCon = new SqlConnection(ConStr);

            return NewCon;

        }

    }

When you create the class like this, you will see some parts with errors. To get rid of them you will need to right click the project and add a reference to System.Configuration under the .NET tab first and also import System.Data.SqlClient

Clean and build the project and check to see if there are errors. No errors ? happyyyyyyyyyyyy :D 

8. Lets create a data access class in this project. The data access class can contain all the code and queries to access the actual data from the database. 

It should be noted that, some of the methods I use (for example, like writing sql in a C# file) might not be the best way of doing something, if you feel that you have a better way of doing it, well go nuts...)

So, lets create the class DBAccess

since we are work with data and also going to work with lists import the following in to the class

• System.Collections.Generic;
• System.Data;
• System.Data.SqlClient;

9. Les leave the DBAccess class for a little bit. As we need to load up some objects to the list. Lets create a class for the object we are going to load.
Lets call it the SampleClass

10. Add the following code the the SampleClass

 class SampleClass
    {
        public int ID { get; set; }
        public string Variable { get; set; }

        public override string ToString()
        {
            return Variable.ToString();
        }
    }

I try to match the data types with the value types retrieving from the database. At the end of the day, we are going to assign the values from database to this object.

Notice the override, this is used like this, because, when the values and IDs are assigned to the ComboBox and when a particular item from the ComboBox is selected, we can get this ID. So, we can retrieve data according to a selected ComboBox item.

11. OK, Lest get back to DBAccess class. Now I'm going to add 2 methods retrieving 2 lists. Then we can do some experiments with this :D
Change the DBAccess class to look like this


class DBAccess
    {
//we can assign this the object from connection manager
        SqlConnection conn;

//assigning the connection object
        public DBAccess()
        {
            conn = ConnectionManager.GetConection();
        }

//heres the method for the first list
        public List<SampleClass> getList()
        {
            if (conn.State.ToString() == "Closed")
            {
                conn.Open();
            }

            SqlCommand cmd = conn.CreateCommand();
            cmd.Connection = conn;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "select * from Passion..sampleTable";
            SqlDataReader dr = cmd.ExecuteReader();

//the list to store the objects we retrieve
            List<SampleClass> outputList = new List<SampleClass>();
            while (dr.Read())
            {
                SampleClass c1 = new SampleClass();
//[" <--->"] contains table column name
                c1.ID = Convert.ToInt32(dr["sampleID"]);
                c1.Variable = Convert.ToString(dr["sampleValue"]);
                outputList.Add(c1);
            }

//DON'T forget to close the connection
            conn.Close();
            return outputList;
        }

        public List<SampleClass> getList2()
        {
            if (conn.State.ToString() == "Closed")
            {
                conn.Open();
            }

            SqlCommand cmd = conn.CreateCommand();
            cmd.Connection = conn;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "select * from Passion..sampleTable";
            SqlDataReader dr = cmd.ExecuteReader();

            List<SampleClass> outputList = new List<SampleClass>();
            while (dr.Read())
            {
                SampleClass c1 = new SampleClass();
                c1.ID = Convert.ToInt32(dr["sampleID"]);
                c1.Variable = Convert.ToString(dr["sampleValue"]);
                outputList.Add(c1);
            }

//DON'T forget to close the connection
            conn.Close();
            return outputList;
        }      
    }




12. Since we have the lists working. lets add the code to make the front end work.
So, double click Both ComboBoxes to generate box selection methods. Then fill the code to match with the following


public partial class Form1 : Form
    {
        DBAccess dba = new DBAccess();
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            FillBombo1();
            FillBombo2();
        }

        /// <summary>
        /// loads items from the list to the ComboBox
        /// </summary>
        private void FillBombo1()
        {
            List<SampleClass> lisSet = dba.getList();

            foreach (SampleClass item in lisSet)
            {
                comboBox1.Items.Add(item);
            }
        }

        private void FillBombo2()
        {
            List<SampleClass> lisSet = dba.getList2();

            foreach (SampleClass item in lisSet)
            {
                comboBox2.Items.Add(item);
            }
        }


        private void comboBox2_SelectedIndexChanged(object sender, EventArgs e)
        {
            //Add the selected items ID to the label
            label2.Text = (comboBox2.SelectedItem as SampleClass).ID.ToString();
        }

        private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            label1.Text = (comboBox1.SelectedItem as SampleClass).ID.ToString();
        }
    }

Remember to change the variable names according to yours and also to import things you need, like the collections and generics.

Clean build and run the project

then you will see the form working fine :D

That's the end of our project

Now lets have some fun.
Comment the conn.Close() method of the first list class.
Now, run the project and notice that the ComboBox relevant to OTHER list, is not working properly (its empty).
So, I think you can get some idea about the connection and how data pass ;)

Cheers