Hi aliyuusman,
Add Identity column in HouseHold table.
Refer updated code.
HTML
<asp:Button ID="Button1" runat="server" Text="Upload JSON file To DB" />
Namespaces
Imports System.Data
Imports System.Data.SqlClient
Imports System.IO
Imports Newtonsoft.Json
Code
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim json As String = File.ReadAllText(Server.MapPath("~/beneficiaries.json"))
Dim Records = JsonConvert.DeserializeObject(Of List(Of HouseHold))(json)
Dim TRSaved As Integer = 0
For Each record As HouseHold In Records
TRSaved += SaveRecords(record)
Next
Response.Write(String.Format("Invocies saved {0}", TRSaved))
End Sub
Public Function SaveRecords(ByVal inv As HouseHold) As Integer
Dim Count As Integer = 0
Using conn As New SqlConnection(ConfigurationManager.ConnectionStrings("sultan").ConnectionString)
conn.Open()
Dim HouseHoldCmd As New SqlCommand("InsertHouseHold", conn)
With HouseHoldCmd
.CommandType = CommandType.StoredProcedure
.Parameters.Add("@HouseHoldID", SqlDbType.VarChar).Value = inv.HouseHoldID
.Parameters.Add("@HouseHoldRefNo", SqlDbType.VarChar).Value = inv.HouseHoldRefNo
.Parameters.Add("@Zone", SqlDbType.VarChar).Value = inv.Zone
.Parameters.Add("@State", SqlDbType.VarChar).Value = inv.State
.Parameters.Add("@LGA", SqlDbType.VarChar).Value = inv.LGA
.Parameters.Add("@Ward", SqlDbType.VarChar).Value = inv.Ward
.Parameters.Add("@Community", SqlDbType.VarChar).Value = inv.Community
.Parameters.Add("@HH_Size", SqlDbType.Int).Value = inv.HH_Size
End With
'Get InvoiceId
Dim HouseHoldID As String = HouseHoldCmd.ExecuteScalar()
For Each MemberRecord In inv.members
Dim MembersCmd As New SqlCommand("InsertMembers", conn)
With MembersCmd
.CommandType = CommandType.StoredProcedure
.Parameters.Add("@HouseHoldID", SqlDbType.Int).Value = HouseHoldID
.Parameters.Add("@FullName", SqlDbType.VarChar).Value = MemberRecord.FullName
.Parameters.Add("@Age", SqlDbType.Int).Value = MemberRecord.Age
If IsNothing(MemberRecord.marital_status) Then
.Parameters.Add("@marital_status", SqlDbType.Int).Value = DBNull.Value
Else
.Parameters.Add("@marital_status", SqlDbType.Int).Value = MemberRecord.marital_status
End If
.Parameters.Add("@NSRRefNo", SqlDbType.VarChar).Value = MemberRecord.NSRRefNo
.Parameters.Add("@Gender", SqlDbType.Int).Value = MemberRecord.Gender
.Parameters.Add("@alternate", SqlDbType.Bit).Value = MemberRecord.alternate
.Parameters.Add("@caregiver", SqlDbType.Bit).Value = MemberRecord.caregiver
.Parameters.Add("@livelihood", SqlDbType.Bit).Value = MemberRecord.livelihood
.Parameters.Add("@top_up", SqlDbType.Bit).Value = MemberRecord.top_up
.Parameters.Add("@picture", SqlDbType.VarChar).Value = MemberRecord.picture
End With
Dim result As Integer = MembersCmd.ExecuteNonQuery()
MembersCmd.Parameters.Clear()
Next
HouseHoldCmd.Parameters.Clear()
Count = Count + 1
conn.Close()
End Using
Return Count
End Function
Public Class HouseHold
Public Property HouseHoldID As Integer
Public Property HouseHoldRefNo As String
Public Property Zone As String
Public Property State As String
Public Property LGA As String
Public Property Ward As String
Public Property Community As String
Public Property HH_Size As Integer
Public Property members As List(Of Member)
End Class
Public Class Member
Public Property FullName As String
Public Property Age As Integer
Public Property marital_status As Integer?
Public Property NSRRefNo As String
Public Property Gender As Integer
Public Property alternate As Boolean
Public Property caregiver As Boolean
Public Property livelihood As Boolean
Public Property top_up As Boolean
Public Property picture As String
End Class
SQL
CREATE TABLE [dbo].[HouseHold](
[ID] [int] IDENTITY(1,1) NOT NULL,
[HouseHoldID] [int] NOT NULL,
[HouseHoldRefNo] [varchar](max) NULL,
[Zone] [varchar](max) NULL,
[State] [varchar](max) NULL,
[LGA] [varchar](max) NULL,
[Ward] [varchar](max) NULL,
[Community] [varchar](max) NULL,
[HH_Size] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [dbo].[Member] Script Date: 4/24/2021 1:44:29 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Member](
[MemberID] [int] IDENTITY(1,1) NOT NULL,
[HouseHoldID] [int] NULL,
[FullName] [varchar](max) NULL,
[Age] [int] NULL,
[marital_status] [int] NULL,
[NSRRefNo] [varchar](max) NULL,
[Gender] [int] NULL,
[alternate] [bit] NULL,
[caregiver] [bit] NULL,
[livelihood] [bit] NULL,
[top_up] [bit] NULL,
[picture] [image] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: StoredProcedure [dbo].[InsertHouseHold] Script Date: 4/24/2021 1:44:29 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[InsertHouseHold]
(
@HouseHoldID int,
@HouseHoldRefNo VARCHAR(MAX),
@Zone VARCHAR(MAX),
@State VARCHAR(MAX),
@LGA VARCHAR(MAX),
@Ward VARCHAR(MAX),
@Community VARCHAR(MAX),
@HH_Size INT
)
AS
BEGIN
INSERT INTO HouseHold
(
HouseHoldID,
HouseHoldRefNo,
Zone,
State,
LGA,
Ward,
Community,
HH_Size
)
VALUES
(
@HouseHoldID,
@HouseHoldRefNo,
@Zone,
@State,
@LGA,
@Ward,
@Community,
@HH_Size
)
SELECT SCOPE_IDENTITY()
END
GO
/****** Object: StoredProcedure [dbo].[InsertMembers] Script Date: 4/24/2021 1:44:29 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[InsertMembers]
(
@HouseHoldID int,
@FullName varchar(max),
@Age int,
@marital_status int,
@NSRRefNo varchar(max),
@Gender int,
@alternate bit,
@caregiver bit,
@livelihood bit,
@top_up bit,
@picture image
)
AS
BEGIN
INSERT INTO Member
(
HouseHoldID,
FullName,
Age,
marital_status,
NSRRefNo,
Gender,
alternate,
caregiver,
livelihood,
top_up,
picture
)
VALUES
(
@HouseHoldID,
@FullName,
@Age,
@marital_status,
@NSRRefNo,
@Gender,
@alternate,
@caregiver,
@livelihood,
@top_up,
@picture
)
END
GO
