Example of store procedure save multiple records

CREATE Type [dbo].MaterialDetailsType as TABLE
(
[Material] [nvarchar](50) NULL,
[Action] [nvarchar](50) NULL,
[Item] [nvarchar](50) NULL,
[Description] [nvarchar](max) NULL,
[ItemNo] [nvarchar](50) NULL,
[ItemCat] [nvarchar](50) NULL,
[CompQty] [numeric](18, 3) NULL,
[SortString] [nvarchar](50) NULL,
[AIGroup] [nvarchar](50) NULL,
[UsgProb] [nvarchar](50) NULL,
[ItemText] [nvarchar](50) NULL,

)

Create PROCEDURE [dbo].[InsertEco]

(

    @EcoNumber int=null,

@EcoState nvarchar(50)=null,

@EcoPriority nvarchar(50)=null,

@RollEffect nvarchar(50)=null,

@Comments nvarchar(max)=null,

@ReasonForChange nvarchar(max)=null,

@DescriptionOfChange nvarchar(max)=null,

@DateSubmitted datetime=null,

@DateImplemented datetime=null,

@CutInDate datetime=null,

@MaterialType MaterialDetailsType readonly,

    @Error varchar(Max) Output

)

AS

BEGIN

BEGIN TRANSACTION

declare @EcoID int

    Begin Try

    Insert into EcoMaster

    (

    EcoNumber,

EcoState,

EcoPriority,

RollEffect,

Comments,

ReasonForChange,

DescriptionOfChange,

DateSubmitted,

DateImplemented,

CutInDate

    )

    values

    (

    @EcoNumber,

@EcoState,

@EcoPriority,

@RollEffect,

@Comments,

@ReasonForChange,

@DescriptionOfChange,

@DateSubmitted,

@DateImplemented,

@CutInDate

    )

    set @EcoID = Scope_Identity()

Insert into MaterialDetails

    (

[Material],

[Action],

[Item],

[Description],

[ItemNo],

[ItemCat],

[CompQty],

[SortString],

[AIGroup],

[UsgProb],

[ItemText],

[EcoID]

        )

select 

    [Material],

[Action],

[Item],

[Description],

[ItemNo],

[ItemCat],

[CompQty],

[SortString],

[AIGroup],

[UsgProb],

[ItemText],

@EcoID

from @MaterialType 

    End Try

    Begin Catch

    set @Error = Error_Message()

ROLLBACK TRANSACTION

    End Catch

COMMIT TRANSACTION

END

C# Code :

DataTable dt = new DataTable();

dt.Columns.Add("Material");

                dt.Columns.Add("Action");

                dt.Columns.Add("Item");

                dt.Columns.Add("Description");

                dt.Columns.Add("ItemNo");

                dt.Columns.Add("ItemCat");

                dt.Columns.Add("CompQty");

                dt.Columns.Add("SortString");

                dt.Columns.Add("AIGroup");

                dt.Columns.Add("UsgProb");

                dt.Columns.Add("ItemText");

                for (int i = 0; i < ecoBom.Count; i++)

                {

                    DataRow dataRow = dt.NewRow();

                    dataRow["Material"] = ecoBom[i].MATERIAL;

                    dataRow["Action"] = ecoBom[i].ACTION;

                    dataRow["Item"] = ecoBom[i].COMPONENT;

                    dataRow["Description"] = ecoBom[i].MAKTX;

                    dataRow["ItemNo"] = ecoBom[i].ITEM_NO;

                    dataRow["ItemCat"] = ecoBom[i].ITEM_CATEG;

                    dataRow["CompQty"] = ecoBom[i].COMP_QTY;

                    dataRow["SortString"] = ecoBom[i].SORTSTRING;

                    dataRow["AIGroup"] = ecoBom[i].AI_GROUP;

                    dataRow["UsgProb"] = ecoBom[i].USAGE_PROB;

                    dataRow["ItemText"] = ecoBom[i].ITEM_TEXT1;

                    dt.Rows.Add(dataRow);

                }

 using (SqlCommand cmd = new SqlCommand("InsertEco", cn))

                {

                    cmd.CommandType = CommandType.StoredProcedure;

                    cmd.Parameters.AddWithValue("@EcoNumber", Convert.ToInt32(EcoNumber));

                    cmd.Parameters.AddWithValue("@EcoState", header.WF_STEP_DESC);

                    cmd.Parameters.AddWithValue("@EcoPriority", header.ECO_PRIORITY);

                    cmd.Parameters.AddWithValue("@RollEffect", header.ECO_MD_ROLL_EFFECT);

                    cmd.Parameters.AddWithValue("@Comments", header.ECO_COMMENTS);

                    cmd.Parameters.AddWithValue("@ReasonForChange", header.ECO_REASON_FOR_CHANGE);

                    cmd.Parameters.AddWithValue("@DescriptionOfChange", header.ECO_DESCRIPTION_OF_CHANGE);

                    cmd.Parameters.AddWithValue("@DateSubmitted", header.SUBMISSION_DATE == "0000-00-00" ? Convert.ToDateTime("1753-01-01") : Convert.ToDateTime(header.SUBMISSION_DATE));

                    cmd.Parameters.AddWithValue("@DateImplemented", header.IMPLEMENTED_ON == "0000-00-00" ? Convert.ToDateTime("1753-01-01") : Convert.ToDateTime(header.IMPLEMENTED_ON));

                    cmd.Parameters.AddWithValue("@CutInDate", header.ECO_MD_CUT_IN_DATE == "0000-00-00" ? Convert.ToDateTime("1753-01-01") : Convert.ToDateTime(header.ECO_MD_CUT_IN_DATE));

                    SqlParameter dtparameter = new SqlParameter();

                    dtparameter.ParameterName = "@MaterialType";

                    dtparameter.SqlDbType = System.Data.SqlDbType.Structured;

                    dtparameter.Value = dt;

                    cmd.Parameters.Add(dtparameter);

                    SqlParameter parameter = new SqlParameter();

                    parameter.ParameterName = "@Error";

                    parameter.DbType = DbType.String;

                    parameter.Size = 5000;

                    parameter.Direction = ParameterDirection.Output;

                    cmd.Parameters.Add(parameter);

                    cn.Open();

                    cmd.ExecuteNonQuery();

                    error = cmd.Parameters["@Error"].Value.ToString();

                    cn.Close();

               }