Using table valued parameters to insert multiple rows in VB.NET

Dim Table1 As DataTable
'create a table named tmptblTable1 = New DataTable("tmptbl")
Dim Row1, Row2, Row3, Row4, Row5 As DataRow
'establishing connection. you need to provide password for SQL serverDim sConn As New SqlConnection()
Dim sSqlcommand As New SqlCommand()

Try'declare a column named Ptno Dim intPtno As DataColumn = New DataColumn("Ptno")
'setting the datatype for the columnintPtno.DataType = System.Type.GetType("System.Int32")
'adding the column to tableTable1.Columns.Add(intPtno)
Dim intUserId As DataColumn = New DataColumn("UserId")intUserId.DataType = System.Type.GetType(
"System.Int32")Table1.Columns.Add(intUserId)

Dim intDocType As DataColumn = New DataColumn("DocTypeId")intDocType.DataType = System.Type.GetType(
"System.Int32")Table1.Columns.Add(intDocType)

Dim strSessionId As DataColumn = New DataColumn("UserSessionId")strSessionId.DataType = System.Type.GetType(
"System.String")Table1.Columns.Add(strSessionId)


If Request.QueryString("Doc1") <> "0" Then'declaring a new rowRow1 = Table1.NewRow()
'filling the row with values. Item property is used to set the field value.Row1.Item("Ptno") = ptnoRow1.Item(
"UserId") = userIdRow1.Item(
"DocTypeId") = Doc1Row1.Item(
"UserSessionId") = appSessionIdTable1.Rows.Add(Row1)

End IfIf Request.QueryString("Doc2") <> "0" Then'declaring a new rowRow2 = Table1.NewRow()
'filling the row with values. Item property is used to set the field value.Row2.Item("Ptno") = ptnoRow2.Item(
"UserId") = userIdRow2.Item(
"DocTypeId") = Doc2Row2.Item(
"UserSessionId") = appSessionIdTable1.Rows.Add(Row2)

End IfIf Request.QueryString("Doc3") <> "0" Then'declaring a new rowRow3 = Table1.NewRow()
'filling the row with values. Item property is used to set the field value.Row3.Item("Ptno") = ptnoRow3.Item(
"UserId") = userIdRow3.Item(
"DocTypeId") = Doc3Row3.Item(
"UserSessionId") = appSessionIdTable1.Rows.Add(Row3)

End IfIf Request.QueryString("Doc4") <> "0" Then'declaring a new rowRow4 = Table1.NewRow()
'filling the row with values. Item property is used to set the field value.Row4.Item("Ptno") = ptnoRow4.Item(
"UserId") = userIdRow4.Item(
"DocTypeId") = Doc4Row4.Item(
"UserSessionId") = appSessionIdTable1.Rows.Add(Row4)

End IfIf Request.QueryString("Doc5") <> "0" Then'declaring a new rowRow5 = Table1.NewRow()
'filling the row with values. Item property is used to set the field value.Row5.Item("Ptno") = ptnoRow5.Item(
"UserId") = userIdRow5.Item(
"DocTypeId") = Doc5Row5.Item(
"UserSessionId") = appSessionIdTable1.Rows.Add(Row5)

End If

sConn.ConnectionString = ConfigurationManager.ConnectionStrings("StatusUpdatesConnectionString").ToString()sConn.Open()
sSqlcommand = sConn.CreateCommand()
sSqlcommand.CommandType = CommandType.StoredProcedure
sSqlcommand.CommandText =
"PROC_PV_ACCOUNT_DOCTYPE_INSERT"sSqlcommand.CommandTimeout = 3600
sSqlcommand.Connection = sConn
sSqlcommand.Parameters.Add("@DocTypeTbl", SqlDbType.Structured).Value = Table1sSqlcommand.ExecuteNonQuery()



Catch ex As Exception
Throw New Exception(ex.Message)
FinallysSqlcommand.Dispose()
If sConn.State = ConnectionState.Open ThensConn.Close()
sConn = NothingEnd IfEnd Try

MSDTC error while using Transactionscope

In one of my project while writing a client application in VB.NET I had to make a call to two different databases on different servers and in case of error encountered both the transaction had to roll back. I used transactionscope to achieve this. I could use this since the database server is SQL 2008. Transactionscope ( a very cool thing) can be used on in SQL 2005 or higher. However I was running into the following error whenever while trying to open connection2 ( second conection ).

Network access for Distributed Transaction Manager (MSDTC) has been disabled. Please enable DTC for network access in the security configuration for MSDTC using the Component Services Administrative tool.



Here is a snippet of my code (in VB.NET)


While debugging through the code, it thre error as soon as it was trying to open the oConn2 ( server2)
I checked in the database server if DTC was enabled. It was. What I had missed was the MSDTC had to be configured in the client server as well. Once the MSDTC was configured on the client server Viola it worked like a charm.



Imports System.Transactions  'you will have to add reference to System.Transactions first

Using scope As New TransactionScope()
Using oconn1 As New SqlConnection(strconn1)


    ocmd1 = New SqlClient.SqlCommand()
    ProgressBar1.PerformStep()
    ocmd1.Connection = oconn1
    ocmd1.CommandText = "PROC_SKIPTRACE_RESULTS_INSERT"
    ocmd1.CommandType = CommandType.StoredProcedure
    ocmd1.CommandTimeout = 600
    Dim paramPTNO As New SqlParameter("@ptno", Convert.ToInt32(newitems(0).ToString))
    paramPTNO.Direction = ParameterDirection.Input
    ocmd1.Parameters.Add(paramPTNO)
   Try
   If oconn1.State = ConnectionState.Closed Then


       oconn1.Open()
   End If
   returnValue = ocmd1.ExecuteNonQuery()
  Catch ex As Exception
    MsgBox(ex.Message.ToString())
  Finally
    oconn1.Close()
  End Try


Using oConn2 As New SqlConnection(strconn2)
 ocmd2 = New SqlClient.SqlCommand()
 ocmd2.Connection = oConn2
 ocmd2.CommandText = "PROC_SKIPTRACE_PATIENT_TABLE_SUSPENSE_UPDATE"
 ocmd2.CommandType = CommandType.StoredProcedure
 ocmd2.CommandTimeout = 600
 Dim paramintPTNO As New SqlParameter("@ptno", Convert.ToInt32(newitems(0).ToString))
 paramintPTNO.Direction = ParameterDirection.Input
 ocmd2.Parameters.Add(paramintPTNO)


Try
 If oConn2.State = ConnectionState.Closed Then
 oConn2.Open()
 End If
 returnValue = ocmd2.ExecuteNonQuery()
Catch ex As Exception
 MsgBox(ex.Message.ToString())
Finally
 oConn2.Close()
End Try


End Using 'oConn2
End Using 'oConn1
scope.Complete()
End Using ' transactionScope

Creating a XML file from SQL data using VB.net

The project involved creating a xml file in the following format with the data from the SQLserver 2008 tables.

File Sample
<claimTrackingRequest>
<Target>
<identifier>1015100236</identifier>
<Type>RCV</Type>
<Comment>
<Code />
<Text>07/29 - comment test1 </Text>
</Comment>
</Target>
<Target>
<identifier>1015700095</identifier>
<Type>RCV</Type>
<Comment>
<Code />
<Text>07/25 - comment test1</Text>
</Comment>
<Comment>
<Code />
<Text>07/26 - comment test2 </Text>
</Comment>
<Comment>
<Code />
<Text>07/26 - comment test2 </Text>
</Comment>
</Target>
</claimTrackingRequest>

The above result was returned by the stored proc PROC_NOTES_STATUSCHANGENOTES_BY_HOSCODE_SELECT_CNA_newformat
( The stored procedure details is listed at the end of the post)
I am using VB.NET to write the result returned by the stored proc to a file with extension XML
I am using a DATASET to pull the result and then using the STREAMWRITER to writer the dataset result to the file.

Sub Main()　
Dim sqlConn As New SqlConnection
sqlConn.ConnectionString = "Data Source=sqlsvr1;Initial Catalog=db01;Integrated Security=SSPI;"Dim sqlCmd As New SqlCommandsqlCmd.CommandText = "PROC_NOTES_STATUSCHANGENOTES_BY_HOSCODE_SELECT_CNA_newformat"sqlCmd.CommandType = CommandType.StoredProcedure
sqlCmd.Connection = sqlConn
sqlCmd.CommandTimeout = 120
sqlCmd.Parameters.Add("@DTBEGIN", SqlDbType.Date).Value = "07/26/2011"sqlCmd.Parameters.Add("@DTEND", SqlDbType.Date).Value = "07/29/2011"Dim ds As New DataSetTrysqlConn.Open()
Dim a As System.Data.SqlClient.SqlDataAdaptera = New SqlDataAdapter(sqlCmd)
a.Fill(ds)
sqlConn.Close()

Using writer As StreamWriter = New StreamWriter("c:\test\notes.xml", True)
writer.Flush()
For Each _row As DataRow In ds.Tables(0).Rows
writer.Write(_row.Item(0).ToString())
Nextwriter.Close()
End Using
Catch ex As ExceptionEnd Try　
End Sub

 /*The stored proc returning the XML result is as follows */

--The proc is using XML Query

USE [db01]GO/****** Object: StoredProcedure [dbo].[PROC_NOTES_STATUSCHANGENOTES_BY_HOSCODE_SELECT_CNA_newformat] Script Date: 08/03/2011 12:48:43 ******/SET
GO
SET
GO
CREATE ANSI_NULLS ON QUOTED_IDENTIFIER ON PROCEDURE [dbo].[PROC_NOTES_STATUSCHANGENOTES_BY_HOSCODE_SELECT_CNA_newformat] (@DTBEGIN SMALLDATETIME, @DTEND SMALLDATETIME)AS/*turn these off so that it wont error if it trys to divide by Zero when doing calcs*/SET
SETARITHABORT OFF ANSI_WARNINGS OFF/*Create a temp table*/CREATE TABLE #TEMPTBLCNA (
RECORD_NUM
C1 INT NULL, VARCHAR(1) NULL,COMMENT_CODE VARCHAR(100) NULL,STATUS_CODE INT NULL,STATUS_DESC VARCHAR(200) NULL,COMMENT VARCHAR(1500) NULL,ACCOUNT VARCHAR(12) NULL,HOSPITAL INT NULL,DATEENTERED DATETIME NULL,PTNO INT NULL,NOTEID INT IDENTITY(1,1) )/*GET 100's*/INSERT INTO #TEMPTBLCNA (RECORD_NUM, C1,COMMENT_CODE,STATUS_CODE,STATUS_DESC,COMMENT,ACCOUNT,HOSPITAL,DATEENTERED,PTNO)SELECTMIN(C.RECORD_NUM), LEFT(COMMENT_CODE,1),C.COMMENT_CODE,LEFT(C.COMMENT_CODE, 3),m.[STATUS],c.COMMENT,RIGHT(DBO.STRIP_NONNUMERIC_12(P.ACCOUNT), 12),p.HOSPITAL, CAST(CONVERT(VARCHAR(10),C.DATEENTERED,101) AS DATETIME),P.PTNOFROMCOMMENTS C INNER JOIN PATIENT_TABLE P ON C.PTNO = P.PTNOINNER JOIN MasterCodeList mON m.NewCode = LEFT(C.COMMENT_CODE, 3)
WHERE CAST(CONVERT(VARCHAR,C.DATEENTERED,101) AS DATETIME) BETWEEN '07/22/2011' AND '07/29/2011'
GROUP BY P.PTNO,P.ACCOUNT,C.Comment,C.COMMENT_CODE,p.HOSPITAL,C.DATEENTERED,LEFT(COMMENT_CODE,1),m.[status]ORDERBY P.PTNO--select * from #TEMPTBLCNASELECT
( c.ACCOUNT as 'identifier', 'RCV' as 'Type', SELECT '' AS 'Code',a.comment AS 'Text'
FROM #TEMPTBLCNA aWHERE a.ptno = c.ptnoFOR XML PATH(''), TYPE) AS 'Comment'
FROM #TEMPTBLCNA cgroup by c.ptno,c.ACCOUNTFOR XML PATH('Target'), ROOT('claimTrackingRequest')
DROP TABLE #TEMPTBLCNASET
SET ARITHABORT ON ANSI_WARNINGS ON