ITSourceCode.com

We Exist to Provide 100% Free Source Code and Tutorials

Custom Search

How to Delete and Retrieve Data in C# and SQL Server 2005

deleteandretrieveSQLOPIThis time, I will teach you how to delete and retrieve the data in C#.net and SQL server 2005. Deleting records are not advisable to a system, however, in developing a system, you have to put a “delete” method so that, it will be easy for you to remove whatever data that you have saved in the database for the input testing.

 

So, let’s get started:

 

  • Create a database and name it “testdb”.
  • Open Microsoft Visual Studio 2008 and create new Windows Form Application.
  • Do the Form as follows.

deleteandretrieveSQLFig.1

  • Go to the Solution Explorer, click the “View Code”  to display the code editor.

deleteandretrieveSQLFig.2

  • Declare all the classes and variable that are needed.
//initialize all classes
 SqlConnection con = new SqlConnection();
 SqlCommand cmd = new SqlCommand();
 SqlDataAdapter da = new SqlDataAdapter();
 DataTable dt = new DataTable();
 //Declare a variable
 string query;
 int result;

Note: Put using System.Data.SqlClient; above the namespace to access sql server library.

  • Go back to the design view, double-click the Form and do the following codes for the connection between C# and SQL server.
 private void Form1_Load(object sender, EventArgs e)
 {
 //connection between sql server to c#
 con.ConnectionString = "Data Source=.\\SQLEXPRESS;Database=testdb;trusted_connection=true;";
 //calling a retrieve method
 retrievedtg();
 }
  • Create a method for retrieving the records in the database to the DataGridView.
private void retrievedtg()
 {
 try
 {
 //create a query for retrieving data in the database.
 query = "SELECT * FROM tbltest";
 //initialize new Sql commands
 cmd = new SqlCommand();
 //hold the data to be executed.
 cmd.Connection = con;
 cmd.CommandText = query;
 //initialize new Sql data adapter
 da = new SqlDataAdapter();
 //fetching query in the database.
 da.SelectCommand = cmd;
 //initialize new datatable
 dt = new DataTable();
 //refreshes the rows in specified range in the datasource. 
 da.Fill(dt);
 //Get and set the data source of a Datagridview
 dataGridView1.DataSource = dt;
 
 }
 catch (Exception ex)
 {
 MessageBox.Show(ex.Message);
 }
 finally
 {
 da.Dispose();

 }
 }
  • Go back to the design view, double-click a button and do the following codes for deleting the records in the SQL database.
 private void button1_Click(object sender, EventArgs e)
 {
 try
 {
 //opening connection
 con.Open();
 //create a delete query;
 query = "DELETE FROM tbltest WHERE ID=" + dataGridView1.CurrentRow .Cells[0].FormattedValue ;
 //it holds the data to be executed.
 cmd.Connection = con;
 cmd.CommandText = query;
 //execute the data.
 int result = cmd.ExecuteNonQuery();
 //validate the result of the executed query.
 if (result > 0)
 {
 MessageBox.Show("Data has been deleted in the SQL database");
 //calling a retrieve method
 retrievedtg();
 
 
 }
 else
 {
 MessageBox.Show("SQL QUERY ERROR");
 }
 //closing connection
 con.Close();

 }
 catch (Exception ex)//catch exeption
 {
 //displaying error message.
 MessageBox.Show(ex.Message);
 }
 }

 

Output:

deleteandretrieveSQLOPI

 

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 – jannopalacios@gmail.com
Mobile No. – 09305235027 – tnt

 

Download Sourcecode

Facebook Comments

Check Your Domain Ranking

Leave a Reply

Your email address will not be published. Required fields are marked *

ITSourceCode.com © 2016 Frontier Theme

Subscribe For Latest Updates

Signup for our newsletter and get notified when we publish new articles for free!