How to loop through object and insert into SQL Server 2008

Question

I'm using VB.NET AND SQL SERVER 2008

I've Array of object that I need to INSERT into SQL SERVER 2008 this is the object enter image description here

this object will be Inserted in to three tables :

First table will have the report information : [ id ,reportName , description , style ] after inserting this information i'll use the @@IDENTITY of this Row as FK for the second Table
SECOND table : [id , id_first_table, comment,complete,finished,name,started,outcome]

Question will come with the third table How can i loop through the checklist Element and inserting it to the third table using the second table id till the end of element

like :

 [id , id_second_table, 101 ,820] 
 [id , id_second_table'sameid', 101,831]

Here is what i have tried :

 DECLARE @reportId INT , @fieldId INT  

 INSERT INTO    
 dbo.tblReport(strReportName,strReportDescription,strReportStyle,
 strScource,dtmCreated)
 VALUES ('caustic' , 'titration caustic', 'simple table' ,'user' ,  getdate()) 
 SELECT @reportId = @@IDENTITY

 INSERT INTO dbo.tblReportFields   
 (lngReports,strFieldName,bolComment,bolComplete,bolFinished,
 bolOutCome,bolStarted,bolUser)
 VALUES (@reportId ,'caustic titration' , 1,0,1,0,0,0)
 SELECT @fieldId = @@IDENTITY 

 --LOOP AND INSERT ALL THE checklist Element 

  INSERT INTO dbo.tblReportTask (lngReportFields,lngChecklist,lngTask)
  VALUES (@fieldId, 814 , 1443)

any ideas on the best way of doing this will be also considered

i don't have any ideas yet , so far i found the best way of doing this is to conver this object to data table — Mina Gabriel
– Mina Gabriel, Commented Aug 17, 2012 at 14:15
So, you have this object in code but have no code to get it into the database, correct? — Holger Brandt
– Holger Brandt, Commented Aug 17, 2012 at 14:29

Holger Brandt · Accepted Answer · 2012-08-17 15:06:28Z

Okay, your info was a little hard to work with since your Object and SQL Statements don't quite match up. However, here is basically how you do it. I hope you can relate it back to your problem.

This is how you insert the Report and ReportFields tables. I didn't include the SQL for the ReportFields table since it is similar to the Report one.

  Dim reportID As Integer = 0
  Dim fieldID As Integer = 0
  Using tmpCONN As New SqlConnection(tmpConnStr)
    Dim tmpSQL As New StringBuilder
    tmpSQL.AppendLine("INSERT INTO dbo.tblReport(strReportName,strReportDescription,strReportStyle,strScource,dtmCreated) ")
    tmpSQL.AppendLine(" VALUES ")
    tmpSQL.AppendLine("(@NAME, @DESC, @STYLE, @SOURCE,@CREATED); ")
    tmpSQL.AppendLine("SELECT SCOPE_IDENTITY() AS ScopeID; ")
    Using tmpCMD As New SqlCommand(tmpSQL.ToString, tmpCONN)
      tmpCMD.Parameters.AddWithValue("@NAME", Obj.Name)
      tmpCMD.Parameters.AddWithValue("@DESC", Obj.Description)
      tmpCMD.Parameters.AddWithValue("@STYLE", Obj.Stype)
      tmpCMD.Parameters.AddWithValue("@SOURCE", Obj.Source)
      tmpCMD.Parameters.AddWithValue("@CREATED", Date.Now)
      reportID = tmpCMD.ExecuteScalar
    End Using

    'Do same type of insert here for the ReportFields table.  Instead of reportID,'
    'it would return fieldID.'


    'This is the loop to insert to Tasks'
    For Each chk As chkObj In Obj.Checklist
      tmpSQL = New StringBuilder
      tmpSQL.AppendLine("INSERT INTO dbo.tblReportTask (lngReportFields,lngChecklist,lngTask) ")
      tmpSQL.AppendLine(" VALUES ")
      tmpSQL.AppendLine("(@FIELD_ID, @CHECK_ID , @TASK) ")
      Using tmpCMD As New SqlCommand(tmpSQL.ToString, tmpCONN)
        tmpCMD.Parameters.AddWithValue("@FIELD_ID", fieldID)
        tmpCMD.Parameters.AddWithValue("@FIELD_ID", chk.cl_id)
        tmpCMD.Parameters.AddWithValue("@FIELD_ID", chk.ts_id)
        tmpCMD.ExecuteNonQuery()
      End Using
    Next
  End Using

Collectives™ on Stack Overflow

How to loop through object and insert into SQL Server 2008

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related