ADO.NET With Insert Update, Delete and Select Statements

What is ADO.NET?

ADO.NET is abbrivated as ActiveX Data Object. ADO.NET is a softare library  .NET technology. Which consisting of softaware components that are distributing services for Data Access.
ADO.NET provides the connected access to database.ADO.NET is designed to write managed code for obtaining disconnected access to data sources, which can be relational or non-relational (such as XML or application data). This feature of ADO.NET helps to create data-sharing, distributed applications.

DataSet is a component for disconnected access. Dataset is the component helping to store the persistent data in memory to provide disconnected access for using the database resource efficiently and with better scalability.

.NET Framework provides the dataaccess with following components for data maipulation.

Connection: This will provides  connectivity to data source.
Command: This will executes the database statements like retrieve data, modify data or execute stored procedures.
DataReader: This will retrieves data in forward and read-only form.
DataAdapter: This will acts as bridge between dataset and data source to load the dataset and reconcile changes made in dataset back to the source.

Sample C# Code

Insert Statement:


using System;
using System.Data;
using System.Data.SqlClient;

namespace UnitTesting.Web.DataAccess
{
    public class User
    {
        public int UserId { get; set; }
        public string FullName { get; set; }
        public string Email { get; set; }
        public string Address { get; set; }
    }
    public class UserDataAccess
    {
       // Note: "XXXXX" : Connection String. 
        public void AddUser()
        {
            User user = new User { FullName = "P Mahesh", Email = "maheshp@veesm.com", Address = "Hyderabad" };

            using (SqlConnection con = new SqlConnection("XXXXX"))
            {
                SqlCommand cmd = new SqlCommand("insert into Users(FullName, Email, Address) values('" + user.FullName + "','" + user.Email + "','" + user.Address + "'); SELECT CAST(scope_identity() AS int)", con);
                cmd.CommandType = System.Data.CommandType.Text;
                con.Open();
                Int32 id = Convert.ToInt32(cmd.ExecuteScalar());
                con.Close();
            }
        }
        public void UpdateUser()
        {
            User updateUser = new User { UserId = 1, FullName = "Mahesh P", Email = "pmahesh@veesm.com", Address = "Irala" };
            using (SqlConnection con = new SqlConnection("XXXXX"))
            {
                SqlCommand cmd = new SqlCommand("Update Users set FullName='" + updateUser.FullName + "', Email='" + updateUser.Email + "', Address='" + updateUser.Address + "' where UserId= " + updateUser.UserId + "", con);
                cmd.CommandType = System.Data.CommandType.Text;
                con.Open();
                cmd.ExecuteNonQuery();
                con.Close();
            }
        }

        public DataSet GetUsers()
        {
            using (SqlConnection con = new SqlConnection("XXXXX"))
            {
                SqlCommand cmd = new SqlCommand("Select Id, FullName, Email, Address from Users", con);
                cmd.CommandType = System.Data.CommandType.Text;
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds);
                con.Close();
                return ds;
            }
        }

        public void DeleteUser(int userid)
        {
            using (SqlConnection con = new SqlConnection("XXXXX"))
            {
                SqlCommand cmd = new SqlCommand("Sp_DeleteUserById", con);
                con.Open();
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.Parameters.Add("@UserId", SqlDbType.Int);
                cmd.ExecuteNonQuery();
                con.Close();
            }
        }

    }
}

My Stpre Procedure from DB is 

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE Sp_DeleteuserById 
@UserId Int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

    
DELETE  FROM User WHERE Userid= @UserId
END
GO


Then What is 
cmd.ExecuteNonQuery(): Executes a Transcat-SQl statement againest the connection and return the number of rows affected.
cmd.ExecuteScalar(): Executes the query that returns the first column of the first row in the result set returned by the query. Additionla columns or rows are ingored.

Popular Posts