Pass Table as Parameter, DELETE WHERE p & c are in passed table

I have a "c# codebehind table" of 1 to ~ 3,000 rows with 2 columns, ParentID and ChildID.  These represnt the rows to DELETE from a SQL table.

@ListToDelete AS TABLE

DELETE FROM table_main
WHERE table_main.col_parent = @ListToDelete.ParentID
  AND table_main.col_child = @ListToDelete.ChildID

To clarify my question.

foreach (DataRow dr in ListToDelete.Rows)
{
      pass the two fields as @parameters to a sproc to delete the one row;
}

I do not want to do the foreach design, it just seems wrong to make all those calls to the SQL server.  I should be able to just pass the table in one SQL call.

Thanks Much
Sam

PS I started building this and discovered I do not know how to pass a table as a parameter in c#.  I am using

string[,] Params = {
                   {"@UID", UID}
                 , {"@EID", EID}
                   };
object objAck = dbUtilities.GetOutputSproc(ConnectStringName, "FacilitiesDiamondDel", Params, "@isAck", System.Data.SqlDbType.Bit, RequestingPage);

and then
public class dbUtilities
    {
        internal static bool ExecSproc(string ConnectStringName, string ProcedureName, string[,] Parameters, string RequestingPage)
        {
            SqlConnection Conn = new SqlConnection(ConfigurationManager.ConnectionStrings[ConnectStringName].ToString());
            SqlCommand SqlCmd = new SqlCommand(ProcedureName, Conn);
            SqlCmd.CommandType = CommandType.StoredProcedure;

 for (int i = 0; i < Parameters.GetLength(0); i++)
            {
                SqlParameter P = new SqlParameter();
                P.ParameterName = Parameters[i, 0];
                if (Parameters[i, 1] == "")
                {
                    P.Value = DBNull.Value;
                }
                else
                {
                    P.Value = Parameters[i, 1];
                }
                SqlCmd.Parameters.Add(P);
            }

Thanks Again
Sam
SamCashAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kyle AbrahamsSenior .Net DeveloperCommented:
what I would do in this case is a concatenation:

StringBuilder sb = new StringBuilder();


foreach (DataRow dr in ListToDelete.Rows)
{
//parentRecord_ChildRecord~  Tilda is row delimiter.
 sb.Append(String.Format("{0}_{1}~", dr["ParentID"].ToString() , dr["ChildID"].ToString());
}

Open in new window


pass that as a single param to your stored proc:
alter sp
@ids varchar(max)
as

DELETE FROM table_main
WHERE convert(varchar, col_parent) + '_' + convert(varchar, col_child)
in 
(select * from dbo.fn_txt_split(@ids, '~'))

Open in new window


fn_txt_split code below:
Create Function [dbo].[fn_Txt_Split]( 
    @sInputList varchar(max) -- List of delimited items 
  , @Delimiter char(1) = ',' -- delimiter that separates items 
) 
RETURNS @list table (Item varchar(max)) 
as begin 
DECLARE @Item Varchar(max) 
  
  

WHILE CHARINDEX(@Delimiter,@sInputList,0) <> 0 
BEGIN 
SELECT 
@Item=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@Delimiter,@sInputList,0 
)-1))), 
@sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@Delimiter,@sInputList,0)+1,LEN(@sInputList)))) 
  
IF LEN(@Item) > 0 
INSERT INTO @List SELECT @Item 
  
END 

  
IF LEN(@sInputList) > 0 
INSERT INTO @List SELECT @sInputList -- Put the last item in 
  
return 
END 

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SamCashAuthor Commented:
Kyle,

Thanks Much!

Regards
Sam
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.