/****** Object: Table [dbo].[tbltest] Script Date: 06/13/2016 00:10:30 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[tblemployee]( [ID] [INT] IDENTITY(1,1) NOT NULL, [Firstname] [nvarchar](50) NULL, [Lastname] [nvarchar](50) NULL, [Address] [nvarchar](MAX) NULL, [ContactNo] [nvarchar](50) NULL, [EmailAdd] [nvarchar](50) NULL ) ON [PRIMARY]
Note: Put using 'System.Data.SqlClient;' above the namespace to access sql server library.
//initialize all classes //Declare a variable string query; int res;
private void LoadRecords() { try { //create a query for retrieving data in the database. query = "SELECT * FROM tblemployee"; //initialize new Sql commands //hold the data to be executed. cmd.Connection = con; cmd.CommandText = query; //initialize new Sql data adapter //fetching data in the database. da.SelectCommand = cmd; //initialize 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(); } }
//connection between SQL server database to c# con.ConnectionString = "Data Source=.\\SQLEXPRESS;Database=employeedb;trusted_connection=true;"; //calling a retrieve method LoadRecords();
try { //opening connection con.Open(); //CREATE a DELETE query; query = "DELETE FROM tblemployee 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 IF the RESULT OF the executed query. IF (res > 0) { MessageBox.Show("Data has been deleted in the SQL database"); //calling a retrieve method LoadRecords(); } ELSE { MessageBox.Show("SQL QUERY ERROR"); } //closing connection con.Close(); } catch (Exception ex)//catch exeption { //displaying an error message. MessageBox.Show(ex.Message); }