My Standard Way For Manipulating Sql Server Tables Using Table Value Parameters

STEP 1: Create a regular table in Sql Server 2008. Use designer.

CREATE TABLE [dbo].[Users_TAB]
(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](50) NOT NULL,
    [Country] [varchar](50) NOT NULL,
 CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED 
(
 [ID] ASC 
)
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,  ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

STEP 2: Create a User-Defined Table Type, exactly like the regular table above except:

        (a) ID column should not be IDENTITY column
        (b) Allow NULL in ID column
        (c) Remove all constraints

CREATE TYPE [dbo].[Users_UDT] AS TABLE
(
    [ID] [int] NULL,
    [Name] [varchar](50) NOT NULL,
    [Country] [varchar](50) NOT NULL
)
GO

STEP 3: Make a single all-purpose stored procedure for the regular table. Use this stored procedure for all four data operations: select, insert, update, delete. Give options for all, single record, group of records (depending on table value parameter) for all the four data operations.

CREATE PROCEDURE [dbo].[Users_SP]
    @ActionID smallint = 0,
    @TaskID smallint = 0,
    @ID smallint = 0,
    @Users Users_UDT readonly
AS
BEGIN 
    BEGIN TRY
  
        BEGIN TRANSACTION
 
            IF(@ActionID = 0) --select
            BEGIN
                IF(@TaskID = 0) --select all
                BEGIN
                    SELECT * FROM Users_TAB
                END
  
                IF(@TaskID = 1) --select record
                BEGIN
                    SELECT * FROM Users_TAB where ID = @ID
                END
  
                IF(@TaskID = 2) --select group
                BEGIN
                    SELECT * FROM Users_TAB WHERE ID IN (SELECT ID FROM @Users)
                END
            END
 
            IF(@ActionID = 1) --insert
            BEGIN
                IF(@TaskID = 2) --insert group
                BEGIN
                    INSERT INTO dbo.Users_TAB(Name, Country)
                    SELECT Name,Country FROM @Users
                END
            END
 
           IF(@ActionID = 2) --update
           BEGIN
               IF(@TaskID = 2) --update group
               BEGIN    
                   UPDATE Users_TAB SET Name = UDT.Name, Country=UDT.Country 
                   FROM Users_TAB TAB INNER JOIN @Users UDT ON TAB.ID = UDT.ID 
               END
           END
 
           IF(@ActionID = 3)--delete
           BEGIN
               IF(@TaskID = 0) --delete all
               BEGIN
                   DELETE FROM Users_TAB
               END
  
               IF(@TaskID = 1) --delete record
               BEGIN
                   DELETE FROM Users_TAB WHERE ID = @ID
               END
  
               IF(@TaskID = 2) --delete group
               BEGIN
                   DELETE FROM Users_TAB WHERE ID IN (SELECT ID FROM @Users)
               END
           END

        COMMIT TRANSACTION   
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMessage varchar(4000)
        SET @ErrorMessage = Error_Message();
        ROLLBACK TRANSACTION
        RAISERROR(@ErrorMessage, 10, 1)
    END CATCH
END

STEP 4: Make a Data-Access-Layer (DAL) in C# to do the four data operations on the regular table.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;

public static class Users
{
    private readonly static SqlConnection connShop = 
        new SqlConnection (
    ConfigurationManager.ConnectionStrings ["Shop"].ConnectionString
                          );
        
    public static DataTable LoadAll()
    {
        SqlCommand cmd = new SqlCommand("SP_Users", connShop);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@Users", null);
        
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataTable dtUsers = new DataTable();
        da.Fill(dtUsers);

        return dtUsers;
    }

    public static DataTable LoadSpecificRecord(short _ID)
    {
        SqlCommand cmd = new SqlCommand("SP_Users", connShop);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@TaskID", 1);
        cmd.Parameters.AddWithValue("@ID", _ID);
        cmd.Parameters.AddWithValue("@Users", null);
        
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataTable dtUsers = new DataTable();
        da.Fill(dtUsers);

        return dtUsers;
    }

    public static DataTable LoadSpecificGroup(DataTable _dt)
    {
        SqlCommand cmd = new SqlCommand("SP_Users", connShop);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@TaskID", 2);
        cmd.Parameters.AddWithValue("@Users", _dt);
        
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataTable dtUsers = new DataTable();
        da.Fill(dtUsers);

        return dtUsers;
    }

    public static void AddGroup(DataTable _dt)
    {
        SqlCommand cmd = new SqlCommand("SP_Users", connShop);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@ActionID", 1);
        cmd.Parameters.AddWithValue("@TaskID", 2);
        cmd.Parameters.AddWithValue("@Users", _dt);

        cmd.ExecuteNonQuery();
    }

    public static void EditGroup(DataTable _dt)
    {
        SqlCommand cmd = new SqlCommand("SP_Users", connShop);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@ActionID", 2);
        cmd.Parameters.AddWithValue("@TaskID", 2);
        cmd.Parameters.AddWithValue("@Users", _dt);

        cmd.ExecuteNonQuery();
    }

    public static void RemoveAll()
    {
        SqlCommand cmd = new SqlCommand("SP_Users", connShop);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@ActionID", 3);
        cmd.Parameters.AddWithValue("@Users", null);

        cmd.ExecuteNonQuery();
    }

    public static void RemoveSpecificRecord(short _ID)
    {
        SqlCommand cmd = new SqlCommand("SP_Users", connShop);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@ActionID", 3);
        cmd.Parameters.AddWithValue("@TaskID", 1);
        cmd.Parameters.AddWithValue("@ID", _ID);
        cmd.Parameters.AddWithValue("@Users", null);

        cmd.ExecuteNonQuery();
    }

    public static void RemoveSpecificGroup(DataTable _dt)
    {
        SqlCommand cmd = new SqlCommand("SP_Users", connShop);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@ActionID", 3);
        cmd.Parameters.AddWithValue("@TaskID", 2);
        cmd.Parameters.AddWithValue("@Users", _dt);

        cmd.ExecuteNonQuery();
    }
}

STEP 5: Make more regular tables. Make more user-defined table types according to that regular table. Make more stored procedures, one each for the regular table, just replace the name of regular table and names of columns in that table and add for columns of that table. Finally in the DAL, you just have to replace the name of the table and no other changes are required.