Social Icons

Wednesday, 7 August 2013

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



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.

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.


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.


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.



No comments:

Post a comment

Please Give your Valuable Suggestions,Questions and Comments to improve my blog.