 |
| ADO.NET For discussion about ADO.NET.  Topics such as question regarding the System.Data namespace are appropriate.  Questions specific to a particular application should be posted in a forum specific to the application . |
Welcome to the p2p.wrox.com Forums.
You are currently viewing the ADO.NET section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
|
|
|
|
November 13th, 2003, 03:01 AM
|
|
Registered User
|
|
Join Date: Nov 2003
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Transaction in ADO.NET
Hi,
Pls see the below source code & stored procedure.
/************************************************** ****************/
'Source Code
/************************************************** ****************/
Dim conObject As SqlConnection
Dim cmdObject As SqlCommand
Dim tranObject As SqlTransaction
Dim strSQL As String, intAffectedRows As Integer
Dim ParamList As New ArrayList()
Dim cmdBuilder As New SqlCommandBuilder()
Dim arrayIndex As Integer
'Opening the connection
conObject = New SqlConnection(Session(conString))
conObject.Open()
'Beginning the Transaction
tranObject = conObject.BeginTransaction
Try
'Deleting the record
strSQL = "DELETE FROM CM_Control"
cmdObject = New SqlCommand(strSQL, conObject)
cmdObject.Transaction = tranObject
cmdObject.CommandType = CommandType.Text
cmdObject.ExecuteNonQuery()
'Inserting a new record thru a Stored Procedure
strSQL = "CS_M_Control_Insert"
ParamList.Add(15)
ParamList.Add(200)
cmdObject.CommandText = strSQL
cmdObject.CommandType = CommandType.StoredProcedure
'Getting the Parameter information dynamically for the ' above sp and assigning the values for each Parameter.
cmdBuilder.DeriveParameters(cmdObject) 'Getting the below mentioned error here
For arrayIndex = 0 To (ParamList.Count - 1)
cmdObject.Parameters(arrayIndex + 1).Value = ParamList(arrayIndex)
Next
cmdBuilder = Nothing
cmdObject.ExecuteNonQuery()
tranObject.Commit()
Catch ex As Exception
tranObject.Rollback()
Response.Write(ex.Message)
Finally
cmdObject = Nothing
conObject.Close()
conObject = Nothing
End Try
/************************************************** ****************/
'Stored Procedure
/************************************************** ****************/
CREATE PROCEDURE CS_M_Control_Insert
@i_Ctrl_Grace_Period AS INT,
@i_Ctrl_Payout_Status AS INT
AS
BEGIN
INSERT INTO CM_Control (Ctrl_Grace_Period, Ctrl_Payout_Status)
VALUES (@i_Ctrl_Grace_Period, @i_Ctrl_Payout_Status)
END
The above code is giving the below error while executing "cmdBuilder.DeriveParameters(cmdObject)":
Execute requires the command to have a transaction object when the connection assigned to the command is in a pending local transaction. The Transaction property of the command has not been initialized.
The same code is working fine without using the Transaction. Any idea abt this error?
Thks & Rgds.,
A. Ragu Nath.
|
|
December 8th, 2003, 03:57 AM
|
|
Registered User
|
|
Join Date: Dec 2003
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Hi
I have such problem when using ado.net transaction in client .
It is appear a bug or a restriction in useing DeriveParameters method . If you find any solution for this please inform me at
[email protected] .
And if I find any solution ,I will propound here .
|
|
December 9th, 2003, 03:12 AM
|
|
Registered User
|
|
Join Date: Nov 2003
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Hi,
I didn't get any solution for the same. I chgd the logic to pass all input parameter details explicity to the stored procedure.
Thks & Rgds.,
A. Ragu Nath.
|
|
December 11th, 2003, 12:32 PM
|
|
Registered User
|
|
Join Date: Dec 2003
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
The problem when using the deriveparamters and transactions is that even when you assign a transaction to the command you are going to use, the deriveparameters function only takes the connection, commandtext, and commandtype. the solution is to modify the code in deriveparameters to include an option for a transaction. Here is a class that will help you get around the problem:
Code:
Imports System.Data.SqlClient
Public Class AlternativeDeriveParameters
Public Sub DeriveParameters(ByRef cmd As SqlCommand, Optional ByVal trnSQL as SQLTransaction = Nothing)
Dim cmdText As String
Dim array1() As String
Dim command1 As SqlCommand
Dim reader1 As SqlDataReader
Dim list1 As ArrayList
Dim parameter1 As SqlParameter
Dim obj1 As Object
Dim obj2 As Object
Dim type1 As CommandType
Dim enumerator1 As IEnumerator
Dim disposable1 As IDisposable
type1 = CommandType.StoredProcedure
If (type1 <> 1) Then
If (type1 = 4) Then
GoTo L_0037
End If
If (type1 = 512) Then
GoTo L_0024
End If
GoTo L_002B
End If
' Throw ADP.DeriveParametersNotSupported(Me)
L_0024:
'Throw ADP.DeriveParametersNotSupported(Me)
L_002B:
'Throw ADP.InvalidCommandType(Me.CommandType)
L_0037:
'Me.ValidateCommand("DeriveParameters", 0)
array1 = ParseProcedureName(cmd.CommandText)
command1 = Nothing
If (Not (array1(1)) Is Nothing) Then
cmdText = String.Concat("[", array1(1), "]..sp_procedure_params_rowset")
If (Not (array1(0)) Is Nothing) Then
cmdText = String.Concat(array1(0), ".", cmdText)
End If
If not trnSQL is nothing Then
command1 = New SqlCommand(cmdText, cmd.Connection, trnSQL)
Else
command1 = New SqlCommand(cmdText, cmd.Connection)
End If
Else
If not trnSQL is nothing Then
command1 = New SqlCommand("sp_procedure_params_rowset", cmd.Connection, trnSQL)
Else
command1 = New SqlCommand("sp_procedure_params_rowset", cmd.Connection)
End If
End If
command1.CommandType = 4
command1.Parameters.Add(New SqlParameter("@procedure_name", 12, 255))
command1.Parameters(0).Value = array1(3).Substring(4) 'here I had to substract the "dbo." that is before the store procedure name. you might have to change it to fit the naming convetion you use or fic the ParseProcedureName function to parse correctly.
reader1 = Nothing
list1 = New ArrayList()
Try
reader1 = command1.ExecuteReader
parameter1 = Nothing
Do While reader1.Read
parameter1 = New SqlParameter()
parameter1.ParameterName = CType(reader1("PARAMETER_NAME"), String)
parameter1.SqlDbType = GetSqlDbTypeFromOleDbType(CType(reader1("DATA_TYPE"), Short), CType(reader1("TYPE_NAME"), String))
obj1 = reader1("CHARACTER_MAXIMUM_LENGTH")
If (Not obj1 Is DBNull.Value) AndAlso (obj1 <> 0) Then
parameter1.Size = CType(obj1, Integer)
End If
parameter1.Direction = Me.ParameterDirectionFromOleDbDirection(CType(reader1("PARAMETER_TYPE"), Short))
If (parameter1.SqlDbType = 5) Then
parameter1.Scale = CType((CType(reader1("NUMERIC_SCALE"), Short) And 255), Byte)
parameter1.Precision = CType((CType(reader1("NUMERIC_PRECISION"), Short) And 255), Byte)
End If
list1.Add(parameter1)
Loop
Finally
If (Not (reader1) Is Nothing) Then
reader1.Close()
End If
command1.Connection = Nothing
End Try
If (list1.Count = 0) Then
Throw New Exception("your s**t out of luck....Store Procedure does not exist: " + cmd.CommandText)
End If
cmd.Parameters.Clear()
enumerator1 = list1.GetEnumerator
Try
Do While enumerator1.MoveNext
obj2 = enumerator1.Current
cmd.Parameters.Add(obj2)
Loop
Finally
'disposable1 = (CType(enumerator1, IDisposable))
'If (Not (disposable1) Is Nothing) Then
'disposable1.Dispose()
enumerator1 = Nothing
'End If
End Try
End Sub
Private Function ParseProcedureName(ByVal procedure As String) As String()
Dim array1() As String
Dim num1 As Integer
Dim num2 As Integer
Dim num3 As Integer
Dim num4 As Integer
array1 = New String((4) - 1) {}
If IsEmpty(procedure) Then
GoTo L_00B2
End If
num1 = 0
num2 = 0
num3 = 0
Do While (num1 < 4)
num3 = InStr(procedure, ".") 'procedure.IndexOf(46, num2)
If (-1 <> num3) Then
array1(num1) = procedure.Substring(num2)
GoTo L_0056
End If
array1(num1) = procedure.Substring(num2, (num3 - num2))
num2 = (num3 + 1)
If (procedure.Length <= num2) Then
GoTo L_0056
End If
num1 = (num1 + 1)
Loop
L_0056:
num4 = num1
Select Case num4
Case 0
GoTo L_00A0
Case 1
array1(3) = array1(1)
array1(2) = array1(0)
array1(1) = Nothing
array1(0) = Nothing
GoTo L_00B2
Case 2
array1(3) = array1(2)
array1(2) = array1(1)
array1(1) = array1(0)
array1(0) = Nothing
GoTo L_00B2
Case 3
GoTo L_00B2
End Select
GoTo L_00B2
L_00A0:
array1(3) = array1(0)
array1(2) = Nothing
array1(1) = Nothing
array1(0) = Nothing
L_00B2:
Return array1
End Function
Private Function IsEmpty(ByVal str As String) As Boolean
If Not str Is Nothing Then
Return (0 = str.Length)
End If
Return True
End Function
Private Function GetSqlDbTypeFromOleDbType(ByVal dbType As Short, ByVal typeName As String) As SqlDbType
Dim type1 As SqlDbType
Dim type2 As System.Data.OleDb.OleDbType
type1 = 23
type2 = dbType
If (type2 <= 64) Then
Select Case type2 - 2
Case 0
GoTo L_013D
Case 1
type1 = 8
GoTo L_0175
Case 2
type1 = 13
GoTo L_0175
Case 3
type1 = 6
GoTo L_0175
Case 4
type1 = IIf((typeName = "money"), 9, 17)
GoTo L_0175
Case 5
GoTo L_0104
Case 6
GoTo L_0161
Case 7
GoTo L_0175
Case 8
GoTo L_0175
Case 9
type1 = 2
GoTo L_0175
Case 10
type1 = 23
GoTo L_0175
Case 11
GoTo L_0175
Case 12
GoTo L_0119
Case 13
GoTo L_0175
Case 14
GoTo L_0142
Case 15
GoTo L_0142
Case 16
GoTo L_013D
Case 17
GoTo L_0175
Case 18
type1 = 0
GoTo L_0175
End Select
If (type2 = 64) Then
GoTo L_0104
End If
GoTo L_0175
End If
If (type2 = 72) Then
GoTo L_0121
End If
Select Case type2 - 128
Case 0
GoTo L_0147
Case 1
GoTo L_00D6
Case 2
GoTo L_0161
Case 3
GoTo L_0119
Case 4
GoTo L_0175
Case 5
GoTo L_0104
Case 6
GoTo L_0104
Case 7
GoTo L_0104
End Select
Select Case type2 - 200
Case 0
GoTo L_00D6
Case 1
type1 = 18
GoTo L_0175
Case 2
GoTo L_0161
Case 3
type1 = 11
GoTo L_0175
Case 4
GoTo L_0147
Case 5
type1 = 7
GoTo L_0175
End Select
GoTo L_0175
L_00D6:
type1 = IIf((typeName = "char"), 3, 22)
GoTo L_0175
L_0104:
type1 = IIf((typeName = "datetime"), 4, 15)
GoTo L_0175
L_0119:
type1 = 5
GoTo L_0175
L_0121:
type1 = 14
GoTo L_0175
L_013D:
type1 = 16
GoTo L_0175
L_0142:
type1 = 20
GoTo L_0175
L_0147:
type1 = IIf((typeName = "binary"), 1, 21)
GoTo L_0175
L_0161:
type1 = IIf((typeName = "nchar"), 10, 12)
L_0175:
Return type1
End Function
Private Function ParameterDirectionFromOleDbDirection(ByVal oledbDirection As Short) As ParameterDirection
Dim num1 As Short
num1 = oledbDirection
Select Case num1 - 2
Case 0
GoTo L_0018
Case 1
GoTo L_001A
Case 2
GoTo L_001C
End Select
GoTo L_001E
L_0018:
Return 3
L_001A:
Return 2
L_001C:
Return 6
L_001E:
Return 1
End Function
Sincerely,
Karim
P.S.
My friend Jay and I got this working yesterday, good luck.
|
|
April 6th, 2005, 08:35 AM
|
|
Registered User
|
|
Join Date: Apr 2005
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
What does IIf((typeName = "datetime"), 4, 15) do in the function
GetSqlDbTypeFromOleDbType?
There are others like IIf((typeName = "binary"), 1, 21)
|
|
April 10th, 2005, 03:46 PM
|
 |
Friend of Wrox
|
|
Join Date: Aug 2003
Posts: 5,407
Thanks: 0
Thanked 16 Times in 16 Posts
|
|
IIf we look at IIf() in "blown-up" view:
If expression arg1 == true
return arg2
else
return arg3
- Peter
|
|
|
