Retrieving and Deleting Data in the Database Using C# and SQL Server 2005

Submitted by janobe on Wednesday, July 27, 2016 - 10:20.
In this tutorial, I will show you how to retrieve and delete the data in the database using C#.net and SQL server 2005. This method has the ability to display the records that has been saved in the database to the Datagridview. It will also delete the existing records in the table of the database. Follow the step by step guide below.

So, let’s get started:

Step 1. Create a database and name it “testdb”. Step 2. Open Microsoft Visual Studio 2008 and create new Windows Form Application. Then, do the Form as follows. fig 1 Step 3. Go to the Solution Explorer, click the “View Code” to fire the code editor. fig 2 Step 4. Declare all the classes and variables that are needed.
Note: Put "using System.Data.SqlClient;" above the namespace to access sql server library.
  1. //initialize all classes
  2.         SqlConnection strcon = new SqlConnection();
  3.         SqlCommand sql_cmd = new SqlCommand();
  4.         SqlDataAdapter sql_da = new SqlDataAdapter();
  5.         DataTable dt = new DataTable();
  6.         //Declare a variable
  7.         string strquery;
  8.         int res;
Step 5. Create a method for displaying records from the database to the DataGridView. 
  1. private void LoadRecords()
  2.         {
  3.             try
  4.             {
  5.                 strquery = "SELECT ID, Firstname, Lastname,Address, ContactNo,EmailAdd FROM tbltest";
  6.                 sql_cmd = new SqlCommand();
  7.                 sql_cmd.Connection = strcon;
  8.                 sql_cmd.CommandText = strquery;
  9.                 sql_da = new SqlDataAdapter();
  10.                 sql_da.SelectCommand = sql_cmd;
  11.                 dt = new DataTable();
  12.                 sql_da.Fill(dt);
  13.                 dataGridView1.DataSource = dt;
  14.  
  15.             }
  16.             catch (Exception ex)
  17.             {
  18.                 MessageBox.Show(ex.Message);
  19.             }
  20.             finally
  21.             {
  22.                 sql_da.Dispose();
  23.  
  24.             }
  25.         }
Step 6. Go back to the design view, double-click the Form and do the following codes for establishing the connection between C# and SQL server. 
  1.  //establish a connection between sql server 2005 express edition to c#.net
  2.             strcon.ConnectionString = "Data Source=.\\SQLEXPRESS;Database=testdb;trusted_connection=true;";
  3.             //call a method to retrieve it on the first loa of the form
  4.             LoadRecords();
Step 7. Go back to the design view again, double-click a button and do the following codes for deleting Employee's Records in the SQL database. 
  1.  try
  2.             {
  3.                 strcon.Open();
  4.  
  5.                 strquery  = "DELETE FROM tbltest WHERE ID=" + dataGridView1.CurrentRow .Cells[0].FormattedValue ;
  6.  
  7.                 sql_cmd.Connection = strcon;
  8.  
  9.                 sql_cmd.CommandText = strquery;
  10.  
  11.                  int result = sql_cmd.ExecuteNonQuery();
  12.  
  13.                  if (res > 0)
  14.                 {
  15.                     MessageBox.Show("Data has been deleted in the SQL database");
  16.  
  17.                     LoadRecords ();
  18.  
  19.  
  20.                 }
  21.                 else
  22.                 {
  23.                     MessageBox.Show("SQL QUERY ERROR");
  24.                 }
  25.                   strcon.Close();
  26.  
  27.             }
  28.             catch (Exception ex)//catch exeption
  29.             {
  30.                  MessageBox.Show(ex.Message);
  31.             }
For all students who need a programmer for your thesis system or anyone who needs a source code in any programming languages. You can contact me @ : Email – [email protected] Mobile No. – 09305235027 – TNT

Tags

Add new comment

About text formats
  • 76 views