2 Replies - 457 Views - Last Post: 04 September 2009 - 09:51 AM

#1 oldSwede   User is offline

  • D.I.C Regular
  • member icon

Reputation: 4
  • View blog
  • Posts: 464
  • Joined: 08-January 16

Write multiple rows to SQLserver

Posted 12 November 2008 - 01:11 PM

Description: A function that writes several strings to records in a SQL server, one string = one record. Uses transaction handling and some error handling.
    ''=================================================================================
    '' Purpose of this example is to show one way of inserting several values 
    '' into DB in a controlled way. The downside to what's shown here is that 
    ''  if you insert MANY rows you migt get into trouble with logfiles and such.
    '' In that case look at SqlBulkCopy, BULK INSERT or the bcp command line utility.
    ''
    '' This function takes a list of strings as argument. It inserts these strings
    '' into a database (DB) with transaction handling. By using BeginTransaction(),
    '' Commit() and Rollback() I ensure that either all or none of the values
    '' are written to the DB, i.e. if anything goes wrong nothing happens to the DB.
    ''
    '' Requires:
    '' Imports System.Data.SqlClient
    '' Imports System.Data
    ''
    '' Returns: error texts and/or number of rows inserted
    ''
    '' Search words: VB.NET SQL MULTIPLE ROWS INSERT TRANSACTION COMMIT ROLLBACK
    ''---------------------------------------------------------------------------------
    Private Function WriteErrLog(ByRef lista As Collections.ArrayList) As String ''
        Dim cno As New SqlConnection
        Dim cmd As New SqlCommand
        Dim SQLtrans As SqlTransaction
        Dim num, i As Integer
        Dim msg As String = ""

        'Hard coding the connString this way is bad, but hopefully informative here.
        cno.ConnectionString = "Data Source = DATASERVER; Initial Catalog = LogDB; UID = DBwriterJens; Password = JensPW"
        cmd.Connection = cno
        cmd.Parameters.Add("@errText", SqlDbType.VarChar, 100)
        cmd.CommandText = "INSERT INTO errLog VALUES(@errText)"

        num = 0
        Try
            cno.Open()
            'Must open connection before starting transaction.
            SQLtrans = cno.BeginTransaction()
            cmd.Transaction = SQLtrans
            Try
                'Ok, this is where the inserts really take place. All the stuff around
                'is just to prepare for this and handle errors that may occur.
                For i = 0 To lista.Count - 1
                    cmd.Parameters("@errText").Value = lista.Item(i).ToString.Trim
                    num += cmd.ExecuteNonQuery
                Next i
                'We are done. Now commit the transaction - actually change the DB.
                SQLtrans.Commit()
            Catch e1 As Exception
                'If anything went wrong attempt to rollback transaction
                Try
                    SQLtrans.Rollback()
                Catch e2 As Exception
                    'This is where you will be if the write went wrong AND the rollback failed.
                    'It's a bad place to be: Unable to rollback transaction - this REALLY hurts...
                    msg += "Unable to rollback transaction. " & e2.Message
                End Try
                msg += "Insert failed, transaction rolled back. " & e1.Message
            End Try
        Catch e3 As Exception
            msg += "Insert failed, might be unable to open connection. " & e3.Message
        Finally
            Try
                'Whatever happens, you will land here and attempt to close the connection.
                cno.Close()
            Catch e4 As Exception
                'If closing the connection goes wrong...
                msg += "I can't close connection. " & e4.Message
            End Try
        End Try

        msg += num.ToString  ' += because then I get the number of rows inserted if all went well and the error otherwise.
        Return msg

    End Function



Is This A Good Question/Topic? 0
  • +

Replies To: Write multiple rows to SQLserver

#2 VAISHNAV_RAJKUMAR   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 0
  • Joined: 06-July 09

Re: Write multiple rows to SQLserver

Posted 08 July 2009 - 04:31 AM

good
Was This Post Helpful? 0
  • +
  • -

#3 ghendric   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 0
  • Joined: 04-September 09

Re: Write multiple rows to SQLserver

Posted 04 September 2009 - 09:51 AM

What if you have three connections to three different databases and you have to roll them all back if one of them fails.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1