Bulk Insert

Question

Dear All Can anybody give me an idea for the following scenario ? 1. Two Tables Master & Details called Risk & Risk_Cover . (A risk can have multiple covers) 2. An IDENTITY column called RiskID will be primary key in Risk Table 3. This RiskID will be referred as a foreign key in Risk_Cover Table (Here the RiskID not an Identity) Here how can i do the bulk insert from the front end with out any iteration ? Thank you all.

suhesh selladurai · Answer

Thank you Ahmar. But in your way we are iterating the risks. But I wanted to be the risks also inserted as bulk.

Ahmar Husain · Answer

I am considering that your DB is on SQL SERVER. well in such scenarios keep the data in datatable and pass your datatable to the stored procedure now a very obvious question is which datatype will you use in stored procedure to receive datatable answer is sql server doesnot provide any such datatype however you can end by creating a custom table type the syntax will be like this- Create Type [dbo].[RiskCoverType] as table( [RiskId] int Not Null [RiskType] varchar(40) not null ) GO Lets move to the stored procedure part :- -------------------------------------------- create procedure Sp_InsertRisk @RiskName varchar(200) @RiskDate DateTime @RiskCover [dbo].[RiskCoverType] readonly --Your custom table type as begin --Use transaction here begin tran begin try declare @RiskId bigint; insert into Risk(RiskNameRiskDate) values(@RiskName@RiskDate)0 set @RiskId=(Select @@identity) -- assign identity value of newly inserted row insert into RiskCover select @Riskid* from @riskcover commit tran end try begin catch rollback tran select Error_Message(); end catch end C# part ------------------------------------------------ SqlConnection con=new SqlConnection(ConnectionString); Con.Open(); SqlCommand=new SqlCommand( Sp_InsertRisk Con); Cmd.CommandType=CommandType.StoredProcedure; SqlParameter[] param=new SqlParameter[3]; param[0]=new SqlParameter( @RiskName txtRiskName.Text); param[1]=new SqlParameter( @ RiskDate txtRiskDate.Text); SqlParameter TableParam=new SqlParameter(); TableParam.ParameterName=@ RiskCover ; TableParam.SqlDbType=SqlDbType.Structured TableParam.value=Datatable;//make your datatable of multile rows param[2]=TableParam; foreach(SqlParameter p in param) { cmd.Parameters.add(p); } object o=cmd.ExecuteScalar(); -------------------------------------------------- Try to optimize the above written code specially the stored procedure like use output variable to return message from db  i have written the code here just to explain my answer take hint from it and convert it into a working code for youself. Happy Coding !!

Joginder Banger · Answer

Not a complete question is ...plz share your coding parts for better understanding ............