bulk upload

Database:

1. create  programmatility-->Types-->User-Defined Table Types: Create one temp table:

CREATE TYPE AccountInfoList AS TABLE
( account_id uniqueidentifier
, is_billable bit);

2. create store prod:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
 
Alter PROCEDURE  [dbo].[update_account_isbillable]
     @tvpAccountInfoList dbo.AccountBillableInfoList READONLY ,<---- put the the get-datatable to temp table in the database
     @create_user UNIQUEIDENTIFIER
AS
BEGIN

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    SET NOCOUNT ON

    update acc
    set acc.is_billable = tvpAccBill.is_billable,
        acc.last_mod_user = @create_user,
        acc.last_mod_date = GETDATE()
    from rv_acc_account acc
    inner join @tvpAccountBillableInfoList tvpAccBill on tvpAccBill.account_id = acc.account_id

END

Code:

1. Datalayer:

   public static bool updateBillable(DataTable accountBillableInfoList, Guid createUser)
        {
            SqlDatabase db = (SqlDatabase)DatabaseFactory.CreateDatabase();
            DbCommand cmd = db.GetStoredProcCommand("update_account_isbillable");

            try
            {
                db.AddInParameter(cmd, "@tvpAccountInfoLis", SqlDbType.Structured, accountBillableInfoList);
                db.AddInParameter(cmd, "@create_user", DbType.Guid, createUser);

                db.ExecuteNonQuery(cmd);

                return true;
            }
            catch (Exception e)
            {
                return false;
                throw (e);
            }
            finally
            {
                if (cmd != null && cmd.Connection != null)
                    cmd.Connection.Close();
            }
        }   

Use it in the UI

  LMS.Data.Account.updateBillable(dtAccountUpdate, UserId);