Database Operations in C# – SQL Insertion and Reading in C#

Database Operations in C# – SQL Insertion and Reading in C#

This tutorial series (language: Urdu PK) will help you understating basic operations of SQL Database with C#. In the first tutorial, we will learn how to perform SQL insertion and reading in C#.

Here is the video:


Watch our Tutorial Series: Learn C# Essentials

Two main functions are used in the video whose code is given below. First of all, we have to create a database in the project and then connect to it. for this purpose, we use a connection string. The most efficient way is to save it as a setting in project properties. To get connection string follow these steps.

  1. Open the database in server explorer by simply double-clicking the database in solution explorer. it will automatically create a connection in server explorer.
  2. Go to the properties of the connection. right click on the connection and click on properties.
  3. copy the connection string from properties.
  4. Go to properties of the project.
  5. Go to the settings tab.
  6. Create a new setting, with the name of your own choice.
  7. set its type to connection string and scope to Application.
  8. Save the file

SQL Insertion and Reading

To move forward, go to the form and access the SQL library by adding following line in the header:

using System.Data.SqlClient;

 

After that create an object of SqlConnection. here is an example of using properties, setting method.

SqlConnection conn = new SqlConnection(Properties.Settings.Default.Connection);

Insertion

Copy the following function in your form code and call it in the click event of the button by which you want to insert data into the database.

SQL Insert Query Syntax.

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

C# Code to insert data.

/// <summary>
        /// Insert Dataq into Database
        /// </summary>
        /// <param name="_query">The sql insertion query.</param>
        private void InsertToDB(string _query)
        { 

            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(_query, conn);
                cmd.ExecuteNonQuery();
                MessageBox.Show("New User have ben added successfully.", "Added Successfully.", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

            finally
            {
                conn.Close();
            }
        }

Reading

In a form insert dataGridView from the toolbar. Paste the following function in the form.

Also Read: 11 Promising Tips To Start A Startup

SQL SELECT Query Syntax

SELECT * FROM table_name;
 
SELECT column1, column2, ...
FROM table_name;

C# Read from Database Code

/// <summary>
        /// Read a table from database to a datagridview.
        /// </summary>
        /// <param name="q">The SELECT query of SQL.</param>
        /// <param name="DataGridView">Name of the dataGridView</param>
        private void ReadToGrid(string q, DataGridView DataGridView)
        {
            try
            {
                var dataAdapter = new SqlDataAdapter(q, conn);
                var cmd = new SqlCommandBuilder(dataAdapter);
                var ds = new DataSet();
                dataAdapter.Fill(ds);
                dataGridView.ReadOnly = true;
                dataGridView.DataSource = ds.Tables[0];
                dataGridView.ClearSelection();
            }
            catch (Exception ex)
            { MessageBox.Show(ex.Message); }
            

        }