0

I have a question as to how to send multiple table parameters to a stored procedure.

create table dbtest.Company_verified([CompanyAddr] varchar NULL, [ID] [int] NULL primary key, [Address1] varchar NULL, [city] varchar NULL, [state] varchar NULL, [zip] varchar NULL, [country] varchar NULL, [updDate] [datetime] NULL )

create table dbtest.detailaddrTypes([ID] [int] NULL foreign key references dbtest.Company_verified(ID), [updDate] [datetime] NULL, [type] [int] NULL ) Create Procedure dbo.usp_Insert_test @MainObj as dbtest.Info_Verified readonly, @DetailObj as dbtest.DetailInfo_Verified readonly

CREATE TYPE dbtest.Info_Verified AS TABLE(
    [CompanyAddr] [varchar](1000) NULL,
                [ID] [int] NULL,
    [Address1] [varchar](200) NULL,
    [city] [varchar](100) NULL,
    [state] [varchar](100) NULL,
    [zip] [varchar](50) NULL,
    [country] [varchar](50) NULL,
    [updDate] [datetime] NULL
)
CREATE TYPE [dbtest].[AddrTypes] AS TABLE(
    [ID] [int] NULL,
    [updDate] [datetime] NULL,
    [type] [int] NULL
)

C# console program is trying to insert 10000 Company_verified records at a time by calling dbo.usp_Insert_test procedure. Each Company_verified record may have multiple detailaddrTypes records. To do this I have created these classes

public class AddressVerifiedTbl
    {
        public string CompanyAddr { get; set; }
        public string id { get; set; }
        public string Address1 { get; set; }
        public string city { get; set; }
        public string state { get; set; }
        public string zip { get; set; }
        public string country { get; set; }
        public DateTime updDate { get; set; }
    }
 public class AddrComponentsTbl
    {
        public string id { get; set; }
        public DateTime updDate { get; set; }
        public string type { get; set; }
    }
public class Program
{
 private static void insertAddrVerifiedComp(List<VerifiedTbl> newMaintbl, (List<DetailTbl> newDetailtbl)
         {

            DataSet ds = new DataSet();
            DataTable dtAddrVerified = CreateDatatableAddrVerified();
            DataTable dtAddrVerified1 = dtAddrVerified.Clone();
            foreach (AddressVerifiedTbl tmp in newMaintbl)
            {
                DataRow drAddr = dtAddrVerified1.NewRow();
                drAddr["CompanyAddr"] = tmp.CompanyAddr;
                drAddr["id"] = tmp.id;
                drAddr["Address1"] = tmp.Address1;
                drAddr["city"] = tmp.city;
                drAddr["state"] = tmp.state;
                drAddr["zip"] = tmp.zip;
                drAddr["country"] = tmp.country;
                drAddr["updDate"] = tmp.updDate;
                dtAddrVerified1.ImportRow(drAddr);
                dtAddrVerified1.Rows.Add(drAddr);

            }
                string connectionString = GetConnectionString();
                SqlConnection connection =
                            new SqlConnection(connectionString);
                connection.Open();
                SqlCommand cmd = new SqlCommand(
                    "dbo.usp_Insert_test", connection);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandTimeout = 120;
                SqlParameter parameter1 = new SqlParameter(); 
                parameter1.ParameterName = "@MainObj";
                SqlParameter parameter2 = new SqlParameter(); 
                parameter2.ParameterName = "@DetailObj";
                parameter1.SqlDbType = System.Data.SqlDbType.Structured;
                parameter1.Value = dtAddrVerified;
                parameter1.TypeName = "dbtest.Info_Verified";
                cmd.Parameters.Add(parameter1); 
               connection.Close();
            }
private static DataTable CreateDatatableAddrVerified()
        {
            DataTable myDataTable = new DataTable();
            DataColumn myDataColumn;
            myDataColumn = new DataColumn();
            myDataColumn.DataType = Type.GetType("System.String");
            myDataColumn.ColumnName = "CompanyAddr";
            myDataTable.Columns.Add(myDataColumn);
           // added other columns)

}

In the method insertAddrVerifiedComp for loop I am kinda lost as to how to proceed. Any suggestions.

Thanks R

newbieCSharp
  • 181
  • 2
  • 22
  • I'd be lost too with this code. It's hard to tell what's supposed to go in which table, but in any case, it might be less confusing if you make it work with 1 TVP, and then just copy the needed code for the 2nd TVP. You'll need all the same things... fill a datatable, set the SQL parameter, etc. I think this separation will help you not to be so lost. Your code is confusing and I don't see you doing anything with the 2nd TVP at all except setting the Parameter for it, partially. – Dmitriy Khaykin Apr 16 '14 at 19:11
  • Also your 2 table types do not seem to be related... are you missing a cross reference table? – Dmitriy Khaykin Apr 16 '14 at 19:13
  • @DavidKhaykin, sorry about that, I have edited – newbieCSharp Apr 16 '14 at 19:20

0 Answers0