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);