Update Database using Stored Procedure and DataAdapter

• Download source - 1.5 KB

Introduction

This is a typical data access layer written in C# which uses ADO.NET disconnected architecture. It uses SqlDataAdapter to update the table in database from the DataTable using stored procedure.

If we write a query in SelectCommand of SqlDataAdapter, it automatically generates the required InsertCommand, UpdateCommand and DeleteCommand in a simple scenario to update the database but if we pass name of Stored Procedure in SelectCommand, then it won't be able to generate these other commands.

There is another way of doing it by table-valued parameter where you can pass the datatable directly to stored procedure but SQL SERVER 2005 and previous version does not support it, also that requires creating User Type and enabling CLR in SQL SERVER.

I have written Stored Procedure and the way to use it in SqlDataAdapter. SqlDataAdapter will require only name of the stored procedure and the DataTable which needs to be updated.

Background

I am migrating some old VB 6 applications to .NET which have used inline SQL queries. In the migrated version, we need to use stored procedure and disconnected architecture, as our SQL version does not support table-valued parameter, we are using the explained approach.

Using the Code

SQL CODE

Create one table in SQL named Customer with the following columns:

-- Create customer table
CREATE TABLE Customer(
                        [ID] [int] IDENTITY(1,1) Primary Key,
                        [Name] [varchar](20) NULL,
                        [City] [varchar](20) NULL,
                        [DOB] [date] NULL,
                        [Married] [bit] NULL,
                        [Mobile] [int] NULL)

Create a Stored Procedure to Get the Data from Customer Table:

-- Create Get stored procedure
Create Procedure uspGetCustomer @ID int

as

IF    @ID < 1
    SELECT * FROM Customer    
Else
    SELECT * FROM Customer WHERE ID = @ID

Create another Stored Procedure to update the Customer Table.

Name of the parameter should be the same as column name of the table with prefix as @.

There will be one extra parameter named @RowState with int type to check the passed row need to be deleted, updated or inserted.

For creating this stored procedure, you can extensively use the query generated by SQL server (Right click on table in Object Explorer -> Script Table as -> use CREATE TO, UPDATE TO, INSERT TO, DELETE TO).

-- Create Update stored procedure
Create Procedure uspUpdateCustomer @ID  int    ,
                                    @Name  varchar (20)  ,
                                    @City  varchar (20)  ,
                                    @DOB  date   ,
                                    @Married  bit   ,
                                    @Mobile  int   ,
                                    @RowState int
as 

IF @RowState = 4
INSERT INTO Customer
           ([Name]
           ,[City]
           ,[DOB]
           ,[Married]
           ,[Mobile])
     VALUES
           (@Name
           ,@City
           ,@DOB
           ,@Married
           ,@Mobile)
           

IF @RowState is null and @ID is not null
DELETE FROM Customer
      WHERE ID = @ID

IF @RowState = 16 and @ID is not null
UPDATE Customer
   SET [Name] = @Name
      ,[City] = @City
      ,[DOB] = @DOB
      ,[Married] = @Married
      ,[Mobile] = @Mobile
 WHERE ID = @ID     

C# CODE

public function of data access layer which could be accessed to get or update data. This function will need the name of the stored procedure created above. This functions could be replicated for any table.

//method to retrieve data
  public DataTable GetCustomer()
    {
        command = new SqlCommand("uspGetCustomer", connection);

        command.Parameters.AddWithValue("@ID", -1);

        return GetDetails();
    }

//method to update data
  public void UpdateCustomer(DataTable dtTable)
    {
        command = new SqlCommand("uspUpdateCustomer", connection);

        UpdateDetails(dtTable);
    }

Private functions GetDetails and UpdateDetails. These function won't need replication for each table if you are updating multiple tables.

GetDetails function will use Fill functions of SqlDataAdapter to fill the DataTable (not much explanation required).

1. UpdateDetails function will first use GetChanges to get the updated, inserted or deleted rows so that we pass only those rows which need to changed in the database
2. Add one extra column RowState to dtChanges to pass the RowState of the changed row.
3. For loop will create SqlParameter using the name of columns in datatable and add it in the SqlCommand.
4. Add common command to SqlDataAdapter for each INSERT, UPDATE, DELETE and its constructor.
5. Get Inserted, updated and deleted rows and For loop will fill the RowState column for each row for added and modified rows. As we cannot call RowState on deleted row, no need to set RowState it will become null for each deleted row.
6. Next, call Update function of SqlDataAdapter to update the database.

//Method to get details 
private DataTable GetDetails()
        {
            command.CommandType = CommandType.StoredProcedure;

            adapter = new SqlDataAdapter(command);
            DataTable dtTable = new DataTable();

            try
            {
                // Fill from database
                adapter.Fill(dtTable);
            }
            catch (InvalidOperationException ioEx)
            {

            }
            catch (Exception ex)
            {

            }
            return dtTable;
        }

   //method to update details 
   private void UpdateDetails(DataTable dtTable)
        {
            SqlParameter parameter;

            command.CommandType = CommandType.StoredProcedure;

            //Get the changes
            DataTable dtChanges = dtTable.GetChanges();

            //No change return back
            if (dtChanges == null)
                return;

            //Add new column rowstate to hold row
            dtChanges.Columns.Add("RowState", typeof(int));

            // Add parameter for each column
            for (int i = 0; i < dtChanges.Columns.Count; i++)
            {
                parameter = new SqlParameter();
                
                // Set parameter name
                parameter.ParameterName = "@" + dtChanges.Rows[i].Field<string>("name");

                //Set source column
                parameter.SourceColumn = dtChanges.Rows[i].Field<string>("name");

                command.Parameters.Add(parameter);
            }

            //Update Insert, Update, Delete command to adapter
            adapter = new SqlDataAdapter(command);
            adapter.InsertCommand = command;
            adapter.UpdateCommand = command;
            adapter.DeleteCommand = command;

            //Get the rows Inserted, updated and deleted
            DataTable dtDeletedRows = dtChanges.GetChanges(DataRowState.Deleted);
            DataTable dtModifiedRows = dtChanges.GetChanges(DataRowState.Modified);
            DataTable dtAddedRows = dtChanges.GetChanges(DataRowState.Added);

            // for updated rows set RowState = 16
            if (dtModifiedRows != null)
            {
                for (int i = 0; i < dtModifiedRows.Rows.Count; i++)
                {
                    dtModifiedRows.Rows[i]["RowState"] = 16;
                }
            }

            // For inserted rows set RowState = 4
            if (dtAddedRows != null)
            {
                for (int i = 0; i < dtAddedRows.Rows.Count; i++)
                {
                    dtAddedRows.Rows[i]["RowState"] = 4;
                }
            }

            try
            {
                //Update Database
                if (dtAddedRows != null)
                {
                    adapter.Update(dtAddedRows);
                }
                if (dtDeletedRows != null)
                {
                    adapter.Update(dtDeletedRows);
                }
                if (dtModifiedRows != null)
                {
                    adapter.Update(dtModifiedRows);
                }
            }
            catch (Exception exception)
            {
            }
        }

The complete Data Access Layer using the above function will look like this. ConnString is the name of connection string in configuration file.

public class DataAccessLayer
{
    SqlConnection connection;
    SqlCommand command;
    SqlDataAdapter adapter;

    public DataAccessLayer()
    {
        ConnectionStringSettingsCollection settings = ConfigurationManager.ConnectionStrings;

        string connectionString = settings["ConnString"].ConnectionString;

        connection = new SqlConnection(connectionString);
    }

     public DataTable GetCustomer(){...}

     public void UpdateCustomer(DataTable dtTable){...}

     private DataTable GetDetails(){...}

     private void UpdateDetails(DataTable dtTable){...}
}

History

• Initial version
• Updated UpdateDetails method to handle deleted rows, no exception handling required now