C#: Retrieving and Deleting with SQL Sever

Submitted by janobe on Monday, July 4, 2016 - 09:10.
In this tutorial, I will teach you how to delete and retrieve the data in C#.net and SQL server 2005. With this, you can retrieve and delete the data in the database. This method is very helpful when developing a system because you can delete the test data that you have saved in the database.

So, let’s get started:

1. Create a database and name it “employeedb”. 2. Do the following query for creating a table in the SQL Server database. 
  1. /****** Object:  Table [dbo].[tbltest]    Script Date: 06/13/2016 00:10:30 ******/
  2. SET ANSI_NULLS ON
  3. GO
  4. SET QUOTED_IDENTIFIER ON
  5. GO
  6. CREATE TABLE [dbo].[tblemployee](
  7. 	[ID] [INT] IDENTITY(1,1) NOT NULL,
  8. 	[Firstname] [nvarchar](50) NULL,
  9. 	[Lastname] [nvarchar](50) NULL,
  10. 	[Address] [nvarchar](MAX) NULL,
  11. 	[ContactNo] [nvarchar](50) NULL,
  12. 	[EmailAdd] [nvarchar](50) NULL
  13. ) ON [PRIMARY]
3. Open Microsoft Visual Studio and create new Windows Form Application for c#. 4. Do the Form just like this. fig 1 5. Go to the Solution Explorer, hit the “View Code” to fire the code editor. fig 2 6. Declare all the classes and variable that are needed.
Note: Put using 'System.Data.SqlClient;' above the namespace to access sql server library. 
  1. //initialize all classes
  2.  SqlConnection con = new SqlConnection();
  3.  SqlCommand cmd = new SqlCommand();
  4.  SqlDataAdapter da = new SqlDataAdapter();
  5.  DataTable dt = new DataTable();
  6.  //Declare a variable
  7.  string query;
  8.  int res;
7. Create a method for displaying records in the dataGridview from SQL database. 
  1.  private void LoadRecords()
  2.         {
  3.             try
  4.             {
  5.                 //create a query for retrieving data in the database.
  6.                 query = "SELECT * FROM tblemployee";
  7.                 //initialize new Sql commands
  8.                 cmd = new SqlCommand();
  9.                 //hold the data to be executed.
  10.                 cmd.Connection = con;
  11.                 cmd.CommandText = query;
  12.                 //initialize new Sql data adapter
  13.                 da = new SqlDataAdapter();
  14.                 //fetching data in the database.
  15.                 da.SelectCommand = cmd;
  16.                 //initialize new datatable
  17.                 dt = new DataTable();
  18.                 //refreshes the rows in specified range in the datasource. 
  19.                 da.Fill(dt);
  20.                 //Get and set the data source of a Datagridview
  21.                 dataGridView1.DataSource = dt;
  22.  
  23.             }
  24.             catch (Exception ex)
  25.             {
  26.                 MessageBox.Show(ex.Message);
  27.             }
  28.             finally
  29.             {
  30.                 da.Dispose();
  31.  
  32.             }
  33.         }
8. Go back to the Design View, double-click the Form and do the following codes for the connection between C# and SQL server 2005 express edition . 
  1.   //connection between SQL server database to c#
  2.             con.ConnectionString = "Data Source=.\\SQLEXPRESS;Database=employeedb;trusted_connection=true;";
  3.             //calling a retrieve method
  4.             LoadRecords();
9. Go back to the Design View again, double-click the button and do the following codes in the method for deleting the records in the SQL database. 
  1.  try
  2.             {
  3.                 //opening connection
  4.                 con.Open();
  5.                 //CREATE a DELETE query;
  6.                 query = "DELETE FROM tblemployee WHERE ID=" + dataGridView1.CurrentRow .Cells[0].FormattedValue ;
  7.                 //it holds the DATA TO be executed.
  8.                 cmd.Connection = con;
  9.                 cmd.CommandText = query;
  10.                 //EXECUTE the DATA.
  11.                 INT RESULT = cmd.ExecuteNonQuery();
  12.                 //validate IF the RESULT OF the executed query.
  13.                 IF (res > 0)
  14.                 {
  15.                     MessageBox.Show("Data has been deleted in the SQL database");
  16.                     //calling a retrieve method
  17.                     LoadRecords();
  18.  
  19.  
  20.                 }
  21.                 ELSE
  22.                 {
  23.                     MessageBox.Show("SQL QUERY ERROR");
  24.                 }
  25.                 //closing connection
  26.                 con.Close();
  27.  
  28.             }
  29.             catch (Exception ex)//catch exeption
  30.             {
  31.                 //displaying an error message.
  32.                 MessageBox.Show(ex.Message);
  33.             }
  34.  
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
  • 43 views