Saturday, 28 July 2012

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

You can update data or records into SQL Server Database tables using SqlCommand Class.
You can use "ExecuteNonQuery" method of SqlCommand Class.
You can update 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 update record in "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 update product name by it's product_id column, so only one record is updated.
"ExecuteNonQuery" method also returns affected records count as integer. so you can check that records is updated or not and also get how many records are updated.

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 = "UPDATE product_master SET product_name =@product_name WHERE product_id=@product_id";
        objcmd.Parameters.AddWithValue("@product_name", "Cabinet");
        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 = "UPDATE product_master SET product_name =@product_name WHERE product_id=@product_id"
        objcmd.Parameters.AddWithValue("@product_name", "Cabinet")
        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.