C# Sql Command Delete Statement Source Code Example

Posted by on Monday, December 7, 2009 · Leave a Comment 

C# Sql Command Delete Statement Source Code Example

Purpose: – Illustrates using C# Sql Command Delete statement and checking the result.
Prerequistes:

  1. Install C# (Express or Standard Edition)
  2. Install SQL Server Express
  3. Download Northwind Database
  4. Attach Northwind Database to Databases in Sql Express

Notes:

  • Console Application is used to simplify things, but Windows Forms or Web Forms could also be used
  • You can build a library of syntax examples by using same project over and over and just commenting out what you do not want to execute in Program.cs

Instructions:

  1. Use C# 2008 (Express or Standard) Edition
  2. Create new project; select Console Application; name of Project could be CSharp_Syntax.
  3. Right-click project name in solution explorer; add new folder; name of folder could be DatabaseADONET
  4. Right-click folder; add class; class name could be clsSqlCommandDelete.cs
  5. Copy code into clsSqlCommandDelete.cs
  6. Copy code into Program.cs
  7. Click green arrow to start with debugging

Step 1: Use View Plain to Cut-n-paste code into clsSqlCommandDelete.cs

using System;
using System.Data;
using System.Data.SqlClient;
public class clsSqlCommandDelete
{
public void Main()
{
SqlConnection thisConnection = new SqlConnection("server=(local)\\SQLEXPRESS;" + "integrated security=sspi;database=Northwind");
//First insert some records
//Create Command object
SqlCommand nonqueryCommand = thisConnection.CreateCommand();
 
try
{
// Open Connection
thisConnection.Open();
Console.WriteLine("Connection Opened");
 
// Create INSERT statement with named parameters
nonqueryCommand.CommandText = "INSERT  INTO Employees (FirstName, LastName) VALUES (@FirstName, @LastName)";
 
// Add Parameters to Command Parameters collection
nonqueryCommand.Parameters.Add("@FirstName", SqlDbType.VarChar, 10);
nonqueryCommand.Parameters.Add("@LastName", SqlDbType.VarChar, 20);
 
// Prepare command for repeated execution
nonqueryCommand.Prepare();
 
            // Data to be inserted
string[] names = { "Wade", "David", "Charlie" };
for (int i = 0; i < = 2; i++)
            {
                nonqueryCommand.Parameters["@FirstName"].Value = names[i];
                nonqueryCommand.Parameters["@LastName"].Value = names[i];
 
                Console.WriteLine("Executing {0}", nonqueryCommand.CommandText);
                Console.WriteLine("Number of rows affected : {0}", nonqueryCommand.ExecuteNonQuery());
            }
        }
        catch (SqlException ex)
        {
            // Display error
            Console.WriteLine("Error: " + ex.ToString());
        }
        finally
        {
            // Close Connection
            thisConnection.Close();
            Console.WriteLine("Connection Closed");
 
        }
 
        try
        {
            // Open Connection
            thisConnection.Open();
            Console.WriteLine("Connection Opened");
 
            //Create Command objects
            SqlCommand scalarCommand = new SqlCommand("SELECT COUNT(*) FROM Employees", thisConnection);
 
            // Execute Scalar Query
            Console.WriteLine("Before Delete, Number of Employees = {0}", scalarCommand.ExecuteScalar());
 
            // Set up and execute DELETE Command
            nonqueryCommand.CommandText = "DELETE FROM Employees WHERE " + "Firstname='Wade' AND Lastname='Wade' or " + "Firstname='Charlie' AND Lastname='Charlie' or " + "Firstname='David' AND Lastname='David' ";
            Console.WriteLine("Executing {0}", nonqueryCommand.CommandText);
            Console.WriteLine("Number of rows affected : {0}", nonqueryCommand.ExecuteNonQuery());
 
            // Execute Scalar Query
            Console.WriteLine("After Delete, Number of Employee = {0}", scalarCommand.ExecuteScalar());
        }
 
        catch (SqlException ex)
        {
            // Display error
            Console.WriteLine("Error: " + ex.ToString());
        }
        finally
        {
            // Close Connection
            thisConnection.Close();
            Console.WriteLine("Connection Closed");
 
        }
        Console.ReadLine();
    }
}

Step 2: Use View Plain to Cut-n-paste code into Program.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace CSharp_Syntax
{
class Program
{
static void Main(string[] args)
{
//DatabaseADONET
 
clsSqlCommandDelete mySqlCommandDelete = new clsSqlCommandDelete();
mySqlCommandDelete.Main();
 
}
}
}

Filed under Code Samples · Tagged with , , , ,