Insert database data into listbox C#

Question

Disclaimer: I have no prior experience with querying databases from c# code (so go easy on me)

I am trying to insert data from my SQL Server database into my listbox. Right now I am trying this in the form of an array. I first connect to the database, and then insert the "state" from the database into the index of the array. I want all 50 states to be put into my array and then this information to be put into my listbox. Right now, my data is being inserted but when I view it in the list box it shows System.Data.SqlClient.SqlCommand.

public string connString = "Not displaying this for security reasons, it is set up correctly though."; //Setting up the connection to my DB

public frmState()
{
        InitializeComponent();

        this.FormClosed += new System.Windows.Forms.FormClosedEventHandler(this.frmState_FormClosed);

        using (SqlConnection dbConn = new SqlConnection(connString))
        {
            dbConn.Open();
            string cmdString = "select State_Name from [State]";

            SqlCommand cmd = new SqlCommand(cmdString, dbConn);

            SqlDataReader reader = cmd.ExecuteReader();

            try
            {
                while (reader.Read())
                {
                        string[] stateList = new string[50];

                        for (int i = 1; i <= 50; i++)
                        {
                            stateList[i - 1] = cmd.ToString();
                        }

                        for (int i = 0; i < stateList.Length; i++)
                        {
                            lbStates.Items.Add(stateList[i].ToString());
                        }
                }
            }
            finally
            {
                reader.Close();
            }
        }
    }

Also, I am aware that as of right now I will be showing the same state 50 times. I am trying to figure out how to insert one state at a time. Is this an efficient way of doing this? Also, any tips on working with databases in c#? I am on Visual Studio 2017 and Microsoft SQL Server 2016.

yekanchi · Accepted Answer · 2017-12-03T14:44:11.737

The problem comes from where you did:

stateList[i - 1] = cmd.ToString();

It's wrong because you are converting an SqlCommand object to string and putting it inside an array of type of string to retrieve data from your SqlCommand.

Changing the above line as below will fix your problem:

tateList[i - 1] = reader.GetString(0);

any tips on working with databases in c#?

for a beginner with C# and SQL, I suggest you to keep learning basic database access tools of ADO.net like using SqlDataReader, SqlDataReader, SqlDataAdapter, ... . but to have professional and of course secure application witch also needs to be simple; you have to move toward using ORM tool (witch are medium to access database securely) like "Entity Framework", linq, ... witch will make talking to database much more convenient.

Complementary:

I suggest you to reading this tutorial about how to use SqlDataReader.

I never got back to you on this, but this worked for me! Only had to tweak a few things after making the change for it to work how I want, if anyone was wondering, after making the change I also needed to drop my for loops otherwise my listbox had all 50 states but it showed each one 50 times. Also thank you for the references, and suggestions. Your help is much appreciated. — Aric Peters, Dec 03 '17 at 23:38

Insert database data into listbox C#

1 Answers1