Friday, 3 August 2012

Beginning .Net : Delete data into SQL Server Database table with C# Examples and VB.Net Examples

You can delete data or records into SQL Server Database tables using SqlCommand Class.
You can use "ExecuteNonQuery" method of SqlCommand Class.
You can delete all or particular criteria records using where clause.
This article is very useful for .Net Beginners

Here is example for this.
In this example we delete record from "product_master" table. In this table we have two columns. First product_id it's data type is bigint and this is an Identity column, and Second is product_name it's datatype is nvarchar(500). "product_id" is an identity column.
We delete record by it's product_id, so only one record is deleted.
"ExecuteNonQuery" method also returns affrected records count as integer. so you can check that records is deleted or not and also get how many records are deleted.

C# Examples :
        DataTable objTable = new DataTable();
        int intAffectedRecordsCount = 0;
        SqlConnection objConn = new SqlConnection();
        objConn.ConnectionString = @"Data Source=.\SQLEXPRESS;" +
                                   "Initial Catalog=TempDatabase;" +
                                   "User ID=sa;Password=sa;";  

        SqlCommand objcmd = new SqlCommand();
        objcmd.CommandText = "DELETE FROM product_master WHERE product_id=@product_id";
        objcmd.Parameters.AddWithValue("@product_id", 12);
        objcmd.CommandType = CommandType.Text;
        objcmd.Connection = objConn;

        Response.Write("<b>Affrected Records Count : </b> " + intAffectedRecordsCount);

        objConn.Dispose(); Examples :
        Dim objTable As New DataTable()
        Dim intAffectedRecordsCount As Integer = 0
        Dim objConn As New SqlConnection()
        objConn.ConnectionString = "Data Source=.\SQLEXPRESS;" & _
                                   "Initial Catalog=TempDatabase;" & _
                                   "User ID=sa;Password=sa;"

        Dim objcmd As New SqlCommand()
        objcmd.CommandText = "DELETE FROM product_master WHERE product_id=@product_id"
        objcmd.Parameters.AddWithValue("@product_id", 12)
        objcmd.CommandType = CommandType.Text
        objcmd.Connection = objConn


        intAffectedRecordsCount = objcmd.ExecuteNonQuery()
        Response.Write("<b>Affrected Records Count : </b> " & intAffectedRecordsCount)



Output :

Learn other ADO.Net Examples over here. Click Here...

Note : Give Us your valuable feedback in comments. Give your suggestions in this article so we can update our articles accordingly that.


No comments:

Post a Comment