by Shubham Shaw

Here you will learn how to perform database operations using system.data.SqlClient namespace in C#. Access to data from a database is an important aspect of all programming languages. The core operations are INSERT, UPDATE, SELECT & DELETE.

Before going through this article, I would like to recommend all the C# beginners to go through this article: Complete Beginners Guide to C#

Requirements:

• SQL Server

• Microsoft SQL Server Management Studio

• Visual Studio

Essentials of database connectivity

The following concepts should be common for all databases:

1. Connection - For working with a database, the obvious first step is the connection. The connection string to a database is usually composed of the following parameters:

• Database name - The first important parameter is the name of the database where the connection should be made. Every connection can only operate with one database at the same time.

• Credentials - The next important thing is the user name and password to use when connecting to the database.

• Parameters - For each database type you may specify parameters (that are optional) to provide more information about how dot net should manage connecting to the database.

2. Fetch Data from the SELECT query - Once the connection is established, the following step is to get back the data of the database. 

3. INSERT, UPDATE & DELETE query - C# may also be used for inserting, updating, and deleting records in the database.

SQL queries in C#

SqlCommand in C# allows the user to submit commands to the database. There are two methods, ExecuteReader for query results i.e. dataset or a data table in return(mostly for SELECT queries), and ExecuteNonQuery for SQL queries that do not require any data in return(mostly used for insert, update, and delete).

Let’s create a desktop application for login which will show the logged-in screen when user credentials are correct.

The steps are as follows:

Step 1: Create a new project in Visual Studio. For this, once Visual Studio is launched, select the “create a new project” option.

Step 2: The following step is to choose the project type as the “Windows Forms App”. You must include the name and location of your project. Click on the Next button, and select your Framework version. I have used .Net Core 3.1 for this project -> Click on the Create button.

If the above steps are followed correctly, this is the output you’ll get:

Now, Project has been created and it will contain a few things as default in solution Explorer:

• Form.cs

• Program.cs

Let’s open the designer file for Form.cs by the following steps:

• Right-click on the Form.cs file name or file code.

• Select “View Designer”.

You’ll have a blank Form to design, which you can resize as per your requirements. After resizing my form looks as shown in the following image:

Now we need to show some text in my form, to show text we need a label controller. To get a label controller Drag and Drop Label from the Toolbox Menu (Toolbox is left side of the screen) to the form. Select Properties from the right-click menu on the Label. On the Right Side of the Screen, we have Properties of the controller selected. Inside  Properties change the Text field from “label1” to “User Name” and press Enter. After these steps you should have  a label inside this form like the following image shown:

Now, just like Label, we should add a TextBox control. Resize the width of the textbox as per requirements. And rename the (Name) field for the textbox in properties as “UserNameTB”.

Create one more label for Password and edit the text for it. And create a textbox for the password, maintain the width size, rename the (Name) field for the textbox in properties as “PasswordTB” and edit the field PasswordChar in properties for the Password text box to “*”. Add one more label in the form below the last name textbox, clear the text for it in properties, rename the (Name) field for the label as “ErrorLbl” and edit the field ForeColor in properties for the error label to “Red”. Add two buttons at the bottom of the form. Change the text field for one button as “Log In” and for the other as “Clear”. Change the (Name) for the button with text ok from “button1” to “LoginBtn” and for the button with text clear change it from “button2” to “clearBtn”.

Now, double-click on anywhere in the form away from any controls. You will be in an auto-generated function named “Form1_Load” in “Form1.cs”.

Inside Form1_load function, type the following code below:

            this.LoginBtn.Enabled = false;

            this.ClearBtn.Enabled = false;

This will keep the buttons disabled to be clicked and trigger the function button has to operate. Again, double-click on textbox for the first name, and again you’ll be in an auto-generated function named “UserNameTB_TextChanged” in the form “Form1.cs”.

Inside UserNameTB_TextChanged function, type the following:

if (this.UserNameTB.Text == "" && this.PasswordTB.Text == "")

            {

                this.LoginBtn.Enabled = false;

                this.ClearBtn.Enabled = false;

            }

            else if(this.UserNameTB.Text != "" && this.PasswordTB.Text != "")

            { 

                this.LoginBtn.Enabled = true;

            }

            else

            {

                this.ClearBtn.Enabled = true;

            }

And Again, double-click on the textbox for the last name and again you’ll be in an auto-generated function named “PasswordTB_TextChanged” in the form “Form1.cs”. Inside this function also give the same code as we have included above in the function “UserNameTB_TextChanged”.

Go to Tools in the menu => select “NuGet Package Manager” => select “Manage NuGet Packages for solution…” => go to “browse” tab => search for “System.Data.SqlClient” and checkbox the project name and install it as shown in the below picture:

If the “Preview Changes” dialog box appears click on the ok button and for the “License Acceptance” dialog click on the “I Accept” button.

Open SQL Server Management Studio => Connect to server => Inside object explorer, right click on Databases folder => Click on New Databases => enter Database Name and click on Ok. In the Databases folder, your database will be added, expand your Database and Right click on Tables folder => Click New => Click “Tables…”.

Enter two column names UserName and Password, where both will have the same data type as “varchar(50)” and check allow nulls for both of the columns as shown below:

On closing this dialog, you’ll get a Dialog to ask if you want to save this table, click on yes => then it will ask to Rename or keep the default name of the table(I’ve used the name “userTbl”). Now, expand the Tables folder and right on your table => Click on “Edit Top 200 Rows”. Enter n number of records (i.e. user id and password).

Go back to visual studio, double click on login button, which will create a LoginBtn_Click function => write the following code inside the function:

var con = "Data Source=SHUBHAMDB;Initial Catalog=UsersDB;Persist Security Info=True;User ID=sa;Password=pass@123;";

            using (SqlConnection myConnection = new SqlConnection(con))

            {

                string oString = "select * from userTbl;";

                SqlCommand oCmd = new SqlCommand(oString, myConnection);

                myConnection.Open();

                using (SqlDataReader oReader = oCmd.ExecuteReader())

                {

                    while (oReader.Read())

                    {

                        if (oReader["UserName"].ToString() == this.UserNameTB.Text && oReader["Password"].ToString() == this.PasswordTB.Text)

                        {

                            this.ErrorLbl.Text = "Login Credentials are correct";

                            this.ErrorLbl.ForeColor = System.Drawing.Color.Green;

                        }

                        else

                        {

                            this.ErrorLbl.Text = "Login Credentials are wrong";

                            this.ErrorLbl.ForeColor= System.Drawing.Color.Red;

                        }

                    }

                    myConnection.Close();

                }

            }

Now, go to the top of the code and add these two lines to existing using statements, so that we can add these libraries for our database connection:

using System.Data;

using System.Data.SqlClient;

Now, again go to the designer file class and double click on clear button => ClearBtn_Click function will be generated => enter the following code inside the function:

            this.UserNameTB.Text = "";

            this.PasswordTB.Text = "";

            this.ErrorLbl.Text = "";

Now, click on the green play button above to launch the desktop application => enter your login user name and password given by you in the database. If the user name and password are correct, it will show:

But, If you give wrong user details, it will show:

Conclusion

In this article, we have learned how to create a login form for windows applications using SQL Server Database connection. Thank you for choosing this article for starting with Dot Net and Dot Net core Framework in C# language. Any Suggestions or Corrections are welcome and will help us bring more topics for you. Happy Coding.