C# ASP.NET Sql Command Delete Statement Source Code Example
Sql Command Delete Statement
Purpose: – Illustrates using Sql Command Delete Statement in C-Sharp ASP.NET.
Prerequistes:
- Install C# (Express or Standard Edition)
- Install SQL Server Express
- Download Northwind and Pubs Databases
- Attach Northwind Database to Databases in Sql Express
- Attach pubs Database to Databases in Sql Express
Notes:
- You can build your own library of syntax examples by using same web site over and over and just add new web forms to it.
Instructions:
- Use Visual Web Developer 2008
- Create new web site;
- Click File/New Web Site
- Select ASP.NET Website Template
- Select C-Sharp for Language
- name of Web Site could be CSharp_ASPNET_Syntax.
- Add New folder named “Database_ADONET”
- Right-click project name in solution explorer;
- add new folder;
- name of folder could be: Database_ADONET
- Add Web Form Named SqlCommandDelete to Database_ADONET folder
- Right-click Database_ADONET folder;
- add new item;
- Select Web Form
- Check place code behind in separate file
- Web Form name could be SqlCommandDelete
- Click on copy code in code below to copy code into web form SqlCommandDelete.aspx
- Click on copy code in second set of code below to copy code into code-behind SqlCommandDelete.aspx.cs
- Right-click on SqlCommandDelete.aspx in solution explorer and select view in browser
Step 1: Click on Copy Code to Cut-n-paste code into SqlCommandDelete.aspx
< %@ Page Language="C#" AutoEventWireup="false" CodeFile="SqlCommandDelete.aspx.cs" Inherits="Database_ADONET_SqlCommandDelete" %> < !DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head id="Head1" runat="server"> <title></title> </head> <body> <form id="form1" runat="server"> <div> <table> <tr> <td>First Name: </td><td> <asp :TextBox ID="txtFirstName" runat="server"></asp> </td> </tr> <tr> <td>Last Name: </td><td> <asp :TextBox ID="txtLastName" runat="server"></asp> </td> </tr> </table><br /> <asp :Button ID="btnDelete" runat="server" Text="Delete" onClick="btnDelete_Click" /><br /> <asp :Label ID="lblErrMsg" runat="server" Text="lblErrMsg" ForeColor="#FF3300" Visible="False"></asp><br /> <asp :GridView ID="GridView1" runat="server" DataSourceID="sdsEmployees"> </asp> <asp :SqlDataSource ID="sdsEmployees" runat="server" ConnectionString="<%$ ConnectionStrings:Northwind_ConnectionString %>" SelectCommand="SELECT [FirstName], [LastName] FROM [Employees]"> </asp> </div> </form> </body> </html> |
Step 2: Click on Copy Code to Cut-n-paste code into SqlCommandDelete.aspx.cs
using System; using System.Data; using System.Data.SqlClient; using System.Configuration; partial class Database_ADONET_SqlCommandDelete : System.Web.UI.Page { protected void btnDelete_Click(object sender, System.EventArgs e) { SqlConnection thisConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["Northwind_ConnectionString"].ConnectionString); //Create Command object SqlCommand nonqueryCommand = thisConnection.CreateCommand(); try { // Open Connection thisConnection.Open(); // Create INSERT statement with named parameters nonqueryCommand.CommandText = "DELETE Employees WHERE FirstName = @FirstName and LastName = @LastName"; // Add Parameters to Command Parameters collection nonqueryCommand.Parameters.Add("@FirstName", SqlDbType.VarChar, 10); nonqueryCommand.Parameters.Add("@LastName", SqlDbType.VarChar, 20); nonqueryCommand.Parameters["@FirstName"].Value = txtFirstName.Text; nonqueryCommand.Parameters["@LastName"].Value = txtLastName.Text; nonqueryCommand.ExecuteNonQuery(); } catch (SqlException ex) { // Display error lblErrMsg.Text = ex.ToString(); lblErrMsg.Visible = true; } finally { // Close Connection thisConnection.Close(); } GridView1.DataBind(); } protected void Page_Load(object sender, System.EventArgs e) { lblErrMsg.Visible = false; } } |
Step 3: Click on Copy Code to Cut-n-paste code into web.config right after the appSettings section
<connectionstrings> <add name="Northwind_ConnectionString" connectionString="Server=(local)\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=SSPI" /> <add name="Pubs_ConnectionString" connectionString="Server=(local)\SQLEXPRESS;Initial Catalog=pubs;Integrated Security=SSPI" /> </connectionstrings> |