ITSourceCode.com

We Exist to Provide 100% Free Source Code and Tutorials

Custom Search

Multiple Delete Using C# and SQL Server

MultiDELSQLfig.3This time, I will teach you how to delete multiple data using C# and SQL server 2005. With this, you can delete multiple data in just a click. This is the smart way to delete test data that have been saved in the database.

 

So Lets get started.

 

Create a database and name it “persondb”.

After that, create a table in the database that you have created.

/****** Object: Table [dbo].[tblemployee] Script Date: 06/14/2016 15:29:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblemployee](
 [ID] [int] IDENTITY(1,1) NOT NULL,
 [Name] [nvarchar](50) NULL,
 [Address] [nvarchar](50) NULL,
 [Contact] [nvarchar](50) NULL,
 [Emailadd] [nvarchar](50) NULL,
 CONSTRAINT [PK_tblemployee] PRIMARY KEY CLUSTERED 
(
 [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Now , open Microsoft Visual Studio 2008 and create new Windows Form Application for C#. Then do the following design of a Form as shown below.

MultiDELSQLfig.1

After that, go to the Solution Explorer, double click the “View Code”  to display the code editor.

MultiDELSQLfig.2

In the code editor, declare all the classes that are needed.

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

 //initialize all classes
 SqlConnection conn = new SqlConnection();
 SqlCommand cmd = new SqlCommand();
 SqlDataAdapter da = new SqlDataAdapter();
 DataTable dt = new DataTable();

 

After declaring the classes, establish a connection betweenSQL server and C#.net and retrieve the data in the database in the first load of the Form. 

 private void Form1_Load(object sender, EventArgs e)
 {
 //set a connection between SQL server and Visual C#
 con.ConnectionString = "Data Source=.\\SQLEXPRESS;Database=persondb;trusted_connection=true;";
 
 dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
 dataGridView1.AllowUserToAddRows = false;

 DataGridViewCheckBoxColumn chkbox = new DataGridViewCheckBoxColumn(); 
 //'set the width of the column in the DataGridView
 chkbox.Width = 30;
 //'Adding the checkbox column in the DataGridView
 dataGridView1.Columns.Add(chkbox);
 //'set the rows header to invisible
 dataGridView1.RowHeadersVisible = false;

 
 //opening connection
 con.Open();
 try
 {
 //initialize a new instance of sqlcommand
 cmd = new SqlCommand();
 //set a connection used by this instance of sqlcommand
 cmd.Connection = con;
 //set the sql statement to execute at the data source
 cmd.CommandText = "Select * FROM tblemployee";

 //initialize a new instance of sqlDataAdapter
 da = new SqlDataAdapter();
 //set the sql statement or stored procedure to execute at the data source
 da.SelectCommand = cmd;
 //initialize a new instance of DataTable
 dt = new DataTable();
 //add or resfresh rows in the certain range in the datatable to match those in the data source.
 da.Fill(dt);
 //set the data source to display the data in the datagridview
 dataGridView1.DataSource = dt;
 }
 catch (Exception ex)
 {
 MessageBox.Show(ex.Message);
 }
 da.Dispose();
 con.Close();
 }

 

After that, double click the “Delete” button and do the following code in the method. This code is for deleting multiple data in just a click.

private void button1_Click(object sender, EventArgs e)
 {
 //'opening the connection
 con.Open();
 int result;
 //'DataGridViewRow represents a row in the DataGridView Control
 //'Rows is to get the collection of rows in the DataGridView control
 //'the syntax is , getting the rows one by one
 foreach(DataGridViewRow row in dataGridView1.Rows){
 //'Cells is to get the collection of cell that populate the row
 //'FormattedValue is to get the value of the cell as formtted for display
 //'the condition is, if the checkbox is checked then it delete the data in the database
 if((bool)row.Cells[0].FormattedValue==true) {
 
 //initialize a new instance of sqlcommand
 cmd = new SqlCommand();
 //set a connection used by this instance of sqlcommand
 cmd.Connection = con;
 //set the sql statement to execute at the data source
 cmd.CommandText = "DELETE FROM tblemployee WHERE id = '" +
 (string)row.Cells[1].FormattedValue + "'";

 //'Execute the Data
  result = cmd.ExecuteNonQuery();
 }
 }
 //'the condition is, if the result is equals to zero
 //'then the message will appear and says "No Deleted Record."
 //'and if not the message will appear and says "The Record(s) has been deleted."
 if(result > 0){
 MessageBox.Show("The Record(s) has been deleted.");
 
 //initialize a new instance of sqlcommand
 cmd = new SqlCommand();
 //set a connection used by this instance of sqlcommand
 cmd.Connection = con;
 //set the sql statement to execute at the data source
 cmd.CommandText = "Select * FROM tblemployee";

 //initialize a new instance of sqlDataAdapter
 da = new SqlDataAdapter();
 //set the sql statement or stored procedure to execute at the data source
 da.SelectCommand = cmd;
 //initialize a new instance of DataTable
 dt = new DataTable();
 //add or resfresh rows in the certain range in the datatable to match those in the data source.
 da.Fill(dt);
 //set the data source to display the data in the datagridview
 dataGridView1.DataSource = dt;
 }else{
 MessageBox.Show("No Deleted Record.","Error",MessageBoxButtons.OK,MessageBoxIcon.Error);
 }
 }

Output:

MultiDELSQLfig.3

For all students who need programmer for your thesis system or anyone who needs a sourcecode in any programming languages. You can contact me @ :
Email – jannopalacios@gmail.com
Mobile No. – 09305235027 – tnt

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!