How to perform select, insert,update,delete operations using SqlCommand Object C#.Net and Vb.Net

Select,Insert,Update,Delete Operations on Database:

Introduction:

In this article you will learn how to use SqlCommand Object in Ado.Net for doing Select, Insert, Update, Delete operations against database.

              You will also learn different overloading methods for instantiation of SqlCommand Object, How to set CommandText as insert, update,delete sql statements,  what are the different methods available for execution of queries against database, How to use ExecuteScalar( ), ExecuteReader( ), ExecuteNonQuery( ) methods.

Description:

In the previous articles i explained Introduction to Ado.Net Part1, Part2 and How to establish connection to database. In Ado.Net next step is to learn about SqlCommand Object.

SqlCommand:

             While doing database operations using Ado.Net , after establishing connection to database next step is to Set the CommandText in SqlCommand Object.

         SqlCommand Object is exist in System.Data.SqlClient namespace which is defined in System.Data.dll assembly.  SqlCommand object has four overloads exist i.e

SqlCommand obj = new SqlCommand(); SqlCommand obj = new SqlCommand(string CmdText); SqlCommand obj=new SqlCommand(string CmdText,SqlConnection Con); SqlCommand obj=new SqlCommand(stringCmdText,SqlConnection Con,SqlTransaction Tran);

As you saw we can instantiate the SqlCommand Object in four ways.

SqlCommand Methods:

 SqlCommand Class provides three methods to execute queries against database.

These are

• ExecuteReader( ) for Select Operation
• ExecuteNonQuery( ) for Insert, Update, Delete operations
• ExecuteScalar( ) for Getting Single Value.

ExecuteReader( ):

 If you want to perform select operation on database that returns data as rows and columns then you have to call ExecuteReader( ) method after setting CommandText as Sql Select Statement. This method returns object of the class DataReader which holds data as rows and columns.

           Below code contains how to set the CommandText as Sql Select Statement in C#.Net and Vb.Net.  I am using Dept table which have Id,Dname,Loc columns.

C#.Net Code SqlDataReader ObjRdr = null; try { SqlConnection ObjCon = new SqlConnection("Database=cs9db; data source=(local); integrated security=sspi;"); // Opening the connection ObjCon.Open(); // Instantiation of SqlCommand Object with a query and Connection reference SqlCommand ObjCmd = new SqlCommand("Select DName from Dept", ObjCon); // Calling ExecuteReader() method ObjRdr = ObjCmd.ExecuteReader(); // Print the Deptname for each record while (ObjRdr.Read()) { Console.WriteLine(ObjRdr[0]); } } catch (Exception ex) { Console.WriteLine(ex.Message); } finally { // if reader is open then closing the reader if (ObjRdr != null) { ObjRdr.Close(); } // If Connection is open then closing the conneciton if (ObjCon.State != ConnectionState.Closed) { ObjCon.Close(); } } Vb.Net Code Dim ObjRdr As SqlDataReader = Nothing Try Dim ObjCon As New SqlConnection("Database=cs9db; data source=(local); integrated security=sspi;") ' Opening the connection ObjCon.Open() ' Instantiation of SqlCommand Object with a query and Connection reference Dim ObjCmd As New SqlCommand("Select DName from Dept", ObjCon) ' Calling ExecuteReader() method ObjRdr = ObjCmd.ExecuteReader() ' Print the Deptname for each record While ObjRdr.Read() Console.WriteLine(ObjRdr(0)) End While Catch ex As Exception Console.WriteLine(ex.Message) Finally ' if reader is open then closing the reader If ObjRdr IsNot Nothing Then ObjRdr.Close() End If ' If Connection is open then closing the conneciton If ObjCon.State <> ConnectionState.Closed Then ObjCon.Close() End If End Try

ExecuteNonQuery( ):

If you want to perform any DML operation like insert, update, delete then you have to use ExecuteNonQuery( ) methods. It returns an integer specifying that number of rows affected. If any error occur it returns -1.

Below code contains how to set the CommandText as DML statement and How to call ExecuteNonQuery() method in C#.Net and VB.Net.

C#.Net Code try { SqlConnection ObjCon = new SqlConnection("Database=cs9db; data source=(local); integrated security=sspi;"); // Opening the Connection ObjCon.Open(); // Insertion // Prepare Command String string InsertStmt=@"Insert into Dept(DName,Loc) values('Sales Dept',30)"; // Instantiation of SqlCommand Object with CommandText SqlCommand ObjCmd = new SqlCommand(InsertStmt); // Updation // Instantiation of SqlCommmand Object string UpdateStmt = @"Update Dept set DName='IT Dept' where Deptno=30"; SqlCommand ObjCmd = new SqlCommand(); ObjCmd.CommandText = UpdateStmt; // Deletion string DeleteStmt = @" Delete from Dept where Deptno=30"; SqlCommand objCmd = new SqlCommand(); objCmd.CommandText = DeleteStmt; // Adding Connection object to Command Object ObjCmd.Connection = ObjCon; // Calling ExecuteNonQuery() method int Count=ObjCmd.ExecuteNonQuery(); if (Count > 0) { Console.WriteLine("DML statement Executed Successfully"); } else { Console.WriteLine("Error Occured in Executing DML Statement"); } } catch (Exception ex) { Console.WriteLine(ex.Message); } finally { // If Connection is open then closing the conneciton if (ObjCon.State != ConnectionState.Closed) { ObjCon.Close(); } } VB.Net Code Try Dim ObjCon As New SqlConnection("Database=cs9db; data source=(local); integrated security=sspi;") ' Opening the Connection ObjCon.Open() ' Insertion ' Prepare Command String Dim InsertStmt As String = "Insert into Dept(DName,Loc) values('Sales Dept',30)" ' Instantiation of SqlCommand Object with CommandText Dim ObjCmd As New SqlCommand(InsertStmt) ' Updation ' Instantiation of SqlCommmand Object Dim UpdateStmt As String = "Update Dept set DName='IT Dept' where Deptno=30" Dim ObjCmd As New SqlCommand() ObjCmd.CommandText = UpdateStmt ' Deletion Dim DeleteStmt As String = " Delete from Dept where Deptno=30" Dim objCmd As New SqlCommand() objCmd.CommandText = DeleteStmt ' Adding Connection object to Command Object ObjCmd.Connection = ObjCon ' Calling ExecuteNonQuery() method Dim Count As Integer = ObjCmd.ExecuteNonQuery() If Count > 0 Then Console.WriteLine("DML statement Executed Successfully") Else Console.WriteLine("Error Occured in Executing DML Statement") End If Catch ex As Exception Console.WriteLine(ex.Message) Finally ' If Connection is open then closing the conneciton If ObjCon.State <> ConnectionState.Closed Then ObjCon.Close() End If End Try

ExecuteScalar( ):

           If you want to execute any query that returns single value then you have to call ExecuteScalar( ) method of Command Object. It returns the single value in the form of object type, you have to cast it to use the value.

Below code contains how to call ExecuteScalar( ) method for Getting Single value result in C#.Net and VB.Net.

C#.Net Code try { string SelectStmt = "Select Count(*) from dept"; SqlConnection ObjCon = new SqlConnection("Database=cs9db; data source=(local); integrated security=sspi;"); // Opening the Connection ObjCon.Open(); //Instantiation SqlCommand Object SqlCommand objCmd = new SqlCommand(); objCmd.Connection = ObjCon; objCmd.CommandText = SelectStmt; //Calling ExecuteNonQuery() method Count=(int)objCmd.ExecuteScalar(); } catch (Exception ex) { Console.WriteLine(ex.Message); } finally { // If Connection is open then closing the conneciton if (ObjCon.State != ConnectionState.Closed) { ObjCon.Close(); } } VB.Net Code Try Dim SelectStmt As String = "Select Count(*) from dept" Dim ObjCon As New SqlConnection("Database=cs9db; data source=(local); integrated security=sspi;") ' Opening the Connection ObjCon.Open() 'Instantiation SqlCommand Object Dim objCmd As New SqlCommand() objCmd.Connection = ObjCon objCmd.CommandText = SelectStmt 'Calling ExecuteNonQuery() method Count = CInt(objCmd.ExecuteScalar()) Catch ex As Exception Console.WriteLine(ex.Message) Finally ' If Connection is open then closing the conneciton If ObjCon.State <> ConnectionState.Closed Then ObjCon.Close() End If End Try

Conclusion:

I hope you know the importance of SqlCommand Object in Ado.Net, How to set the CommandText , how to call ExecuteScalar( ), ExecuteReader( ), ExecuteNonQuery( ).

     In next articles will see how to pass parameters to SqlCommand Object, How to use stored Procedures in SqlCommand .

Don't forget to Comment and Share if you like this post for our friends. Thank You.