C# – Datagrid View – C# connect application to DB in Visual Studio

Today I was looking a bit over SQL Server and Visual Studio and I have somehow liked the nice cooperation between these two. Thus, I have come up with the idea to build a simple application, just to demonstrate how to connect db to app in Visual Studio. Nothing fancy, even the CRUD is not implemented. Something like this:

We have a datagrid view, getting and displaying information from the DB. Then we have a few labels, which give information for each line of the database, based on Next and Previous. That’s all. Let’s start.

First the DB – this is the sql code for the beautiful table:

Use VitoshAcademyCom

CREATE TABLE Footballers
(
ID int IDENTITY(1,1) PRIMARY KEY,
FirstName varchar(255) NOT NULL,
LastName varchar(255),
Club varchar(255),
City varchar(255)
)

INSERT INTO Footballers (FirstName,LastName,Club,City) VALUES
('Peter','The Keeper','NorthClub','Sofia'),
('Ivan','Mitov','EastClub','Plovdiv'),
('Stoyan','Mitkov','EastClub','Varna'),
('Vitosh','Doynov','EastClub','Sofia'),
('Pesho','Stoyanov','SouthClub','Varna'),
('Gosho','Kiriyakov','SouthClub','Sofia'),
('Haralampi','Iliev','SouthClub','Sofia');

Once the table was generated, from Visual Studio I have selected a new Windows Application. Then I simply added DataGridView from the toolbox. In the DataGridView, there is a nice way to bind a db, using the small arrow in the top. Then selecting a db and clicking next:

The Server name is actually a must – Windows could not find mine localhost, so I had to copy and paste it – as you see (localdb)\MSSQLLocalDB. The rest was quite easy – just clicking next.

Once the dataset was ready, I was able to view it in the DataGridView just by running Ctrl+F5. But I wanted to have a little more fancy application – thus I have started to build a few labels and buttons, showing the various db entries.

And then the coding started – pretty much, on the loading of the application I read the database and save its data into a few public list variables. Then I simply read data from these variables, whenever the buttons “Next” and “Previous” are touched. That’s the short story. The long story is here:

using System;
using System.Collections.Generic;
using System.Windows.Forms;
using System.Data.SqlClient;    //Here is the SqlConnection

namespace InfoFootballers
{
    public partial class Form1 : Form
    {
        const string str_conn = @"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=VitoshAcademyCom;Integrated Security=True";
        public List<int> int_id = new List<int>();
        public List<string> str_first_name = new List<string>();
        public List<string> str_last_name = new List<string>();
        public List<string> str_club = new List<string>();
        public List<string> str_city = new List<string>();
        public int counter = 0;

        public Form1()
        {
            InitializeComponent();

            SqlConnection my_connection = null;
            SqlCommand my_command = null;
            SqlDataReader my_reader = null;

            try
            {
                my_connection = new SqlConnection(str_conn);
                my_connection.Open();
                my_command = my_connection.CreateCommand();
                my_command.CommandText = "SELECT * FROM Footballers;";
                my_reader = my_command.ExecuteReader();

                while (my_reader.Read())
                {
                    int_id.Add(my_reader.GetInt32(0));
                    str_first_name.Add(my_reader.GetString(1));
                    str_last_name.Add(my_reader.GetString(2));
                    str_club.Add(my_reader.GetString(3));
                    str_city.Add(my_reader.GetString(4));
                    counter++;
                }

                lbl_id.Text = int_id[0].ToString();
                lbl_fn.Text = str_first_name[0].ToString();
                lbl_ln.Text = str_last_name[0].ToString();
                lbl_club.Text = str_club[0].ToString();
                lbl_city.Text = str_city[0].ToString();

            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }
            finally
            {
                if (my_reader!=null)
                {
                    my_reader.Close();
                }
                if (my_connection!=null)
                {
                    my_connection.Close();
                }
                counter = 0;
            }
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            // TODO: This line of code loads data into the 'vitoshAcademyComDataSet.Footballers' table. You can move, or remove it, as needed.
            this.footballersTableAdapter.Fill(this.vitoshAcademyComDataSet.Footballers);
        }

        private void btn_next_Click(object sender, EventArgs e)
        {
            //MessageBox.Show(counter.ToString());
            if (counter >= int_id.Count-1)
            {
                lbl_info.Text = "No next...";
                return;
            }
            lbl_info.Text = "";
            counter++;

            lbl_id.Text = int_id[counter].ToString();
            lbl_fn.Text = str_first_name[counter];
            lbl_ln.Text = str_last_name[counter];
            lbl_club.Text = str_club[counter];
            lbl_city.Text = str_city[counter];
        }

        private void btn_previous_Click(object sender, EventArgs e)
        {
            if (counter==0)
            {
                lbl_info.Text = "No previous...";
                return;
            }
            lbl_info.Text = "";
            counter--;

            lbl_id.Text = int_id[counter].ToString();
            lbl_fn.Text = str_first_name[counter];
            lbl_ln.Text = str_last_name[counter];
            lbl_club.Text = str_club[counter];
            lbl_city.Text = str_city[counter];
        }
    }
}

There are some repetitions in the code and places where I could have followed better syntax or etc., but the idea of the article is to show how to connect DB to C# app and nothing more. 🙂

Here is the whole project in GitHub.Com.