Post
Ask Question

Data Fetching From Microsoft SQL To C#

Introduction

This article is about how to fetch data from Microsoft SQL to C#. Microsoft SQL has basic functionality to retrieve data as required by other applications. It can be used as a backend to create an application, and the steps desired to fetch data are as follows. 

Step 1

Open Microsoft SQL and create a database (here I created a database named MYDB1) after creating a table and putting records in it.

Microsoft SQL Server Management Studio

Step 2

Open Visual Studio and create a new Windows form application.

Step 3

Create a form using buttons (insert ,search,update,delete and navigation), textbox, labels, and datagridview.

Step 4

After creating the form, connect the database to the form. For that right click on the form and it opens Form1.cs. It contains the namespace System.Data.SqlClient which applies SQL connection, SQLdataadopter, SQL command, and dataTable.for SQL data connection. Write the connection string given below and write a select query for the SQL command.

using System;
using System.Data;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace WindowsFormsApp1 {
    public partial class Form1: Form {
        public Form1() {
            InitializeComponent();
        }
        SqlConnection con;
        SqlCommand cmd;
        DataTable table = new DataTable();
        private void Form1_Load(object sender, EventArgs e) {
            con = new SqlConnection(@ "data source=DESKTOP-LH8CQ3P\SQLEXPRESS02;initial  catalog=MYDB1;integrated security=true");
            cmd = new SqlCommand("select * from custom", con);
            string selectquery = "select * from custom";
            SqlDataAdapter adpt = new SqlDataAdapter(selectquery, con);
            DataTable table = new DataTable();
            adpt.Fill(table);
            dataGridView1.DataSource = table;
        }
    }
}

Step 5

Write code for search, insert, delete, and update buttons.

SEARCH BUTTON

private void button1_Click(object sender, EventArgs e) {
    con.Open();
    string selectquery = "select * from cust6 where c_id =" + int.Parse(textID.Text);
    SqlCommand cmd = new SqlCommand(selectquery, con);
    SqlDataReader reader1;
    reader1 = cmd.ExecuteReader();
    if (reader1.Read()) {
        textNAME.Text = reader1.GetValue(1).ToString();
        textAGE.Text = reader1.GetValue(2).ToString();
        textDEPTID.Text = reader1.GetValue(3).ToString();
    } else {
        MessageBox.Show("NO DATA FOUND");
    }
    con.Close();
}

For searching the data first open the connection then write the select query and uses the SQL command as it sends the query to the database and SQL DataReader as it reads the data from the SQL database then assigns the column values to textboxes and last creates a message box of "NO DATA FOUND" and closes the connection.

INSERT BUTTON

private void button2_Click(object sender, EventArgs e) {
    String INSERTQUERY = "insert into cust6(c_id,c_name,C_age,dept_id)values('" + textID.Text + "','" + textNAME.Text + "','" + textAGE.Text + "'," + textDEPTID.Text + ")";
    executemyquery(INSERTQUERY);
    SHOWALL();
}
public void executemyquery(string query) {
    con.Open();
    cmd = new SqlCommand(query, con);
    cmd.CommandText = query;
    if (cmd.ExecuteNonQuery() == 1) {
        MessageBox.Show("DATA INSERTED SUCCESSFULLY");
    } else {
        MessageBox.Show("DATA NOT INSERTED SUCCESSFULLY");
    }
    con.Close();
}

For inserting the data connect the database with a connection string and then write the insert query and create a method named executemyquery in this method first open the connection and write a command and create message boxes of data inserted successfully and data not inserted successfully and finally close the connection.

DELETE BUTTON

private void button3_Click(object sender, EventArgs e) {
    String DELETEQUERY = "DELETE FROM CUST6 WHERE C_ID =" + textID.Text;
    DELETEDATA(DELETEQUERY);
    SHOWALL();
}
public void DELETEDATA(string query) {
    con.Open();
    cmd = new SqlCommand(query, con);
    cmd.CommandText = query;
    if (cmd.ExecuteNonQuery() == 1) {
        MessageBox.Show("DATA DELETED SUCCESSFULLY");
    } else {
        MessageBox.Show("DATA NOT DELETED SUCCESSFULLY");
    }
    con.Close();
}

For deleting the data first write the delete query and create a method named delete data. In this method first open the connection and write the command and create message boxes of data deleted successfully and data not deleted successfully and lastly close the connection.

UPDATE BUTTON

private void button4_Click(object sender, EventArgs e) {
    String UPDATEQUERY = "Update cust6 SET c_name = '" + textNAME.Text + "', c_age = '" + textAGE.Text + "', dept_id = '" + textDEPTID.Text + "' WHERE c_id = " + textID.Text + ";";
    UPDATEmyquery(UPDATEQUERY);
    SHOWALL();
}
public void UPDATEmyquery(string query) {
    con.Open();
    cmd = new SqlCommand(query, con);
    cmd.CommandText = query;
    if (cmd.ExecuteNonQuery() == 1) {
        MessageBox.Show("DATA UPDATED SUCCESSFULLY");
    } else {
        MessageBox.Show("DATA NOT UPDATED SUCCESSFULLY");
    }
    con.Close();
}

For updating the data first write the update query and create a method named update query. In this method first open the connection and write the command and create message boxes of data updated successfully and data not updated successfully and lastly close the connection.

NAVIGATION BUTTON

  1. First navigation
  2. Next navigation
  3. Previous navigation
  4. Last navigation
private void button5_Click(object sender, EventArgs e) {
    position = 0;
    showdata(position);
}
public void showdata(int index) {
    textID.Text = table.Rows[index][0].ToString();
    textNAME.Text = table.Rows[index][1].ToString();
    textAGE.Text = table.Rows[index][2].ToString();
    textDEPTID.Text = table.Rows[index][3].ToString();
}
private void button6_Click(object sender, EventArgs e) {
    posititon++;
    if (position < table.Rows.Count) {
        showdata(position);
    } else {
        MessageBox.Show("NO RECORDS");
    }
}
private void button7_Click(object sender, EventArgs e) {
    position--;
    if (position >= 0) {
        showdata(index: position);
    } else {
        MessageBox.Show("NO RECORDS");
        position = table.Rows.Count - 1;
    }
}
private void button8_Click(object sender, EventArgs e) {
    position = table.Rows.Count - 1;
    showdata(position);
}
}

For the navigation first, last, next, and the previous button above the code is written. In this code, create a method named show data that includes text boxes that read the data from rows and convert it to string datatype and for the next button, int variable name position which applies operator increment and position variable should be greater than the rows of the table. If all the records have been shown then show the message box of no records and for the previous button use variable name position and apply operator decrement. For that, the variable should be greater than and equal to zero and if all the records have been shown then show the message box of no records and for the last navigation variable is equal to one less than the number of counts in the table.

Conclusion

In this article, we have created a form that includes an insert button, an update button, a delete button, a search button, and a navigation button. 


Similar Articles