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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 |
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. 🙂