Bulk insert using table value parameter hangs my application

Malikdanish

I am using table value parameter for insertion into four table and four table value parameters are used but after the insertion my application gets hangs on any one can assist.

jkirkerx

I'm not able to visual that. Perhaps you should post the code you wrote for this. Do you read the How to get an answer to your question? If it's a database update or write, it's usually because an error occurred and you didn't close your connection. Try Catch Finally

Malikdanish

Private Sub btnSave_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSave.Click If Not Page.IsValid Then Return Dim blnIsUpdate As Boolean = False Dim hasedBytes As Byte() Dim strPassword As String = String.Empty Dim md5Hasher As New MD5CryptoServiceProvider() If Not litUserId.Text.Trim().Length.Equals(0) Then blnIsUpdate = True Dim cnnContact As New SqlConnection(AppSettings("DbSqlPortal")) Dim cmdContact As New SqlCommand("spAdminSaveUsers", cnnContact) Dim IDs As Integer = Nothing Dim trnContact As SqlTransaction = Nothing cmdContact.CommandType = CommandType.StoredProcedure hasedBytes = md5Hasher.ComputeHash(New UTF8Encoding().GetBytes((txtPassword.Text.Trim() & txtEmail.Text.Trim()))) ' GetDistrictID() 'If GetDistrictId() > 0 Then ' DisplayMessage(Me.Master.MessageBox, "A User with same District and Same user role already exists.", "Record Exists", MessageBoxTypes.Warning) 'Else Try With cmdContact.Parameters If blnIsUpdate Then .Add("@UserId", SqlDbType.BigInt).Value = CInt(litUserId.Text.Trim()) Else .Add("@UserId", SqlDbType.BigInt).Value = DBNull.Value End If .Add("@FullName", SqlDbType.NVarChar).Value = txtFullName.Text.Trim() .Add("@Email", SqlDbType.NVarChar).Value = txtEmail.Text.Trim() .Add("@Password", SqlDbType.VarChar).Value = base64Encode(txtPassword.Text) 'txtPassword.Text.Trim() 'Convert.ToBase64String(hasedBytes) .Add("@SecurityQuestion", SqlDbType.VarChar).Value = ddlSecurityQuestions.SelectedItem.Text .Add("@SecurityAnswer", SqlDbType.VarChar).Value = txtAnswer.Text.Trim() .Add("@UserTypes", SqlDbType.TinyInt).Value = ddlUserTypes.SelectedValue .Add("@IsActive", SqlDbType.Bit).Value = chkActive.Checked .Add("@DistrictId", SqlDbType.TinyInt).Value = CheckBoxList1.SelectedValue .Add("@IsAdmin", SqlDbType.Bit).Value = chkAdmin.Checked .Add("@IPAddress", SqlDbType.VarChar).Value = Request.UserHostAddress .Add("@Result", SqlDbType.Char, 1).Direction = ParameterDirection.Output .Add("@ReturnId", SqlDbType.BigInt).Direction = ParameterDirec

jkirkerx

How do you know this works?

cnnContact.Open()
trnContact = cnnContact.BeginTransaction()

cmdContact.Transaction = trnContact
cmdContact.ExecuteNonQuery()

Usually you try to keep your SQL more compact in a separate clean function. Consider a simple request for an ID This example opens the connection, reads the reader, closes the reader, With the Try Catch Finally, if the connection or command fails, then the finally will close the connection.

Dim connStr As string = ConfigurationManager.ConnectionStrings("DefaultConnection").ConnectionString
Dim query as string = "SELECT ID FROM TABLE"

Dim conn as New SqlConnection(query)
Dim cmd as New SqlCommand(query, conn)

Try

conn.open()
Dim reader as SQLDataReader = cmd.ExecuteReader()
while reader.Read()
  pValue = reader.GetInt32(0)
End While

reader.Close()

Catch ex as SQLException
'If there is an error, this will tell you why, so fix the error
' but without the finally, the conn will be left open,
'So if you get too many conn open to the sql server, the sql server will hang

Finally
conn.Close()

End Try

Malikdanish

Dear I am using finally at the end to dispose all the objects after transaction commit , what you suggest how to modify my code ? which i have wrote above

jkirkerx

Well in order to find your problem, you have to track it down first. My previous post suggested using the Try Catch and check for any exceptions thrown. So you have to walk the code line by line using F5 debug and check for an exception. As far as modifying the code, instead of a large block of code, you want to break the code down into a couple of reusable function. I like taking all the database calls, and placing them in several separate functions. Sort of like having a Data Access Layer, and Business Logic Layer, in which the 2 are separated. Then it's easier to just go the suspected function and debug it, saves time in the long run. But that's another subject. Like I said, your connection may not be closing after and error, and your opening a new one over and over, causing a stall in execution.

Malikdanish

Dear Sir, Thanks for your time and response , I have used seperate function and while debugging it does not show any error and after the end sub of the function it struck in loop and if i insert smaller records like 2-3 villages just it works fine .. Again Thanks

jkirkerx

Go through it with a fine tooth comb and keep checking Here's an error I overlooked, the checkbox is boolean true or false, so it wrote an bit to the parameter of 0 or 1 Or perhaps it's a dropdown list since you used selected value.

.Add("@DistrictId", SqlDbType.TinyInt).Value = CheckBoxList1.SelectedValue

That's a lot data to insert all at once. You know that data writes and updates take longer to perform on the SQL server, in terms of time. And then you called a stored procedure after that. You have an error somewhere. finding the error is easier if your code is more organized. Maybe instead of so many partial writes, you gather all your data first, and package it up in a class, structure or model, and then do 1 write to the database. You just pass the class or structure to the database function, and do the write. On multiple records, you just loop the database write function. https://msdn.microsoft.com/en-us/library/aa289521%28v=vs.71%29.aspx[^] http://www.functionx.com/visualbasic/Lesson19.htm[^] In a seperate file

Public Structure structure_accountInfo
Public m_firstName as string
Public m_lastName as string
End Structure

In a seperate file

Public Shared Function account_db_update(ByVal sAI as structure_accountInfo) as Integer

Dim dwExitCode As Integer = 2
Dim connStr As String = ConfigurationManager.ConnectionStrings("DefaultConnection").ConnectionString
Dim conn As New SqlConnection(connStr)

Dim query as string = \_
"UPDATE TABLE " & \_
" SET " & \_
"  firstName = @firstName " & \_
", lastName = @lastName " & \_
" WHERE ID = @ID "

Dim cmd As New SqlCommand(query, conn)


Dim param\_\_FirstName As SqlParameter
param\_\_FirstName = New SqlParameter("@FirstName", SqlDbType.VarChar, 80)
param\_\_FirstName.Value = sAI.m\_firstName
cmd.Parameters.Add(param\_\_FirstName)

Dim param\_\_LastName As SqlParameter
param\_\_LastName = New SqlParameter("@LastName", SqlDbType.VarChar, 80)
param\_\_LastName.Value = sAI.m\_lastName
cmd.Parameters.Add(param\_\_LastName)

Try
    conn.Open()
    cmd.ExecuteNon