SQL server is one of the popular database management system to store the large amount of data that will be used in the application. Multiple ways exist in C# to make connection with the SQL server database. The way of making new database with a table in SQL Server and creating connection with the database by using C#.NET has been shown in the tutorial.
Create a new web project like the previous desktop application. First of all, a new database with a table will be created in the SQL server. Open the SQL Server Object Explorer window from the the View menu to create a new database.
Create a database with the name, testdb in the SQL server.
Right click on the table and select Add New Table option to create a new table.
Create a table named Book with the four fields, Id, Title, Publication, and Price. Click on the Update tab to create the table.
Right click on the Book table and click on the View Data option from the pop-up menu to add records in the table.
Add two records in the table.
Open the window of the form and add three labels in the form. The names of the labels are label1, label2, and label3.
Open the Form1.cs file and add the following code to make connection with the database and read the records of the Book table where the id value is 1.
using System;
using System.Data.SqlClient;
using System.Windows.Forms;
namespace Desktop2
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e)
{
//Declare connection variables
string connetionString;
SqlConnection cnn;
connetionString = @"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=testdb";
cnn = new SqlConnection(connetionString);
cnn.Open();
//Define the select query
SqlCommand command = new SqlCommand("SELECT * FROM Book WHERE Id = 1", cnn);
command.ExecuteNonQuery();
//Read the returned values of the table of the particular record
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
//Display the values in the form
label1.Text = "Book Title: " + reader["Title"].ToString();
label2.Text = "Publication: " + reader["Publication"].ToString();
label3.Text = "Price: " + reader["Price"].ToString();
}
}
//Close the connection
cnn.Close();
}
}
}
The following window will appear after executing the code.
Exercise:
Create a desktop application and do the following tasks.
A. Create a user table in the SQL sever database with the fields, username, email, and password.
B. Add some records in the User table.
C. Design a login form with two fields, Username/Email and Password.
D. Make the database connection and check the input values of the form with the entries of the User table. If any matching value is found then print a message, “Valid user“, otherwise print the message, “Invalid user“.