you can use SqlBulkCopy for bulk insertion/Updation.
But if you want to filter. you can use User Defined Table Type in SQL.
In which you can pass the datatable to your stored procedure and then filter by your sql query easily.
SQL:
CREATE PROCEDURE [dbo].[Insert_Customers]
@tblCustomers CustomerType READONLY
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO Customers(CustomerId, Name, Country)
SELECT Id, Name, Country FROM @tblCustomers
END
C#:
using (SqlCommand cmd = new SqlCommand("Insert_Customers"))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = con;
cmd.Parameters.AddWithValue("@tblCustomers", dt);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
Submit an article or tip