|
Subject:
|
Select Command
|
|
Posted By:
|
jaucourt
|
Post Date:
|
1/14/2004 5:52:23 AM
|
I'm using the sqlclient namespace to access data in a sql server database. I set up the sqlcommand with two parameters; this is because the command text may vary (I want to use the same sqlcommand object for two different SELECT statements, so I don't end up with a huge multiplicity of commands, with slightly varying command texts):
Dim condb As New sqlconnection(connectionString)
Dim da As New SqlDataAdapter
Dim ds As New Dataset1
Dim cmdSelect As New SqlCommand
With cmdSelect
.Connection = condb
.CommandText = _
"SELECT * FROM DeliveryDetails " & _
"WHERE (JobNumber = @pJobNumber))"
.CommandType = CommandType.Text
End With
With cmdSelect.Parameters
.Add("@pDeliveryNumber", SqlDbType.BigInt, _
8, "DeliveryNumber")
.Add("@pJobNumber", SqlDbType.VarChar, 5, "JobNumber")
End With
cmdSelect.Parameters("@pJobNumber").Value = "35000"
da.SelectCommand = cmdSelect
da.Fill(ds, "DeliveryDetails")
Dim dr As DataRow
For Each dr In ds.Tables("DeliveryDetails").Rows
MessageBox.Show(dr.Item("DeliveryNumber"))
Next
However, this code throws a 'system error' (very helpful!) However, if I specify a value for the @pDeliveryNumber parameter, the code runs through smoothly. Is it truly the case that if you do not specify values for every single parameter belonging to a command then it will throw an exception, even if the valueless parameter in question is not used in the command text?
|
|
Reply By:
|
planoie
|
Reply Date:
|
1/14/2004 9:13:28 AM
|
Yes.
Why do you add a parameter you aren't using?
Peter
------------------------------------------------------
Work smarter, not harder.
|
|
Reply By:
|
jaucourt
|
Reply Date:
|
1/14/2004 9:47:31 AM
|
The code I showed is basically a simplified version of what I'm doing. What I am doing is setting up a load of command objects in a 'databaseobjects' class, which will be references by a 'databaseoperations' class. I have three different select queries I want to carry out against one table - one to select by a job number, one to select by job number/delivery date, and one to select by delivery number. Rather than create three separate commands, it would be useful if I could create just one command, that had the three required parameters ('@pJobNumber', '@pDeliveryDate', '@pDeliveryNumber'). I could then change the command text in the command when a request was made to the databaseobjects object for a DeliverySelectCommand according to the 'CommandType' value passed in (which could be one of 'JobNumber', 'JobNumberAndDeliveryDate', 'DeliveryNumber') and serve it to the client.
|
|
Reply By:
|
planoie
|
Reply Date:
|
1/14/2004 9:59:03 AM
|
What is the method in your "databaseobjects" class returning? A command object or the query results?
Peter
------------------------------------------------------
Work smarter, not harder.
|
|
Reply By:
|
jaucourt
|
Reply Date:
|
1/14/2004 10:00:31 AM
|
databaseobjects simply returns the command object - databaseoperations actually uses that command to run the query and return a filled dataset back to its client
|
|
Reply By:
|
planoie
|
Reply Date:
|
1/14/2004 11:57:29 AM
|
I recommend setting up a case statement to handle the different types of queries you are going to use, and build each query and its parameters based on the type. Everything else can be the same. In your case, you can just append the appropriate where condition based on the type.
Dim sSql As New System.Text.StringBuilder()
...
sSql.Append("SELECT * FROM DeliveryDetails WHERE ")
Select Case <Command Type>
Case JobNumber
sSql.Append("JobNumber = @pJobNumber")
cmdSelect.Parameters.Add("@pJobNumber", SqlDbType.VarChar, 5).Value = <search value>
...
End Select
cmdSelect.CommandText = sSql.ToString()
cmdSelect.CommandType = CommandType.Text
...
Peter
------------------------------------------------------
Work smarter, not harder.
|
|
Reply By:
|
jaucourt
|
Reply Date:
|
1/26/2004 9:56:03 AM
|
I've actually found a much neater way of doing this.
* The 'objects' class, initialises a collection with all of the parameters I will need.
* I then have a 'GenerateCommand' function, which accepts one parameter, that of the command text. It passes this into an 'Identify Parameters' function, that examines the command text for all parameters, identified by starting characters of '@p'.
* Once 'GenerateCommand' has an array of all parameters, it adds them to a new command object, using the collection initialised earlier. It also sets the connection and the command type, then passes back the dynamically generated command.
This has meant that when I require a new command, I don't have to worry about initialising parameters for it - the object class just returns the 'GenerateCommand' return value.
If anyone wants the code for this then let me know via email. It's fairly simple to do, but no point in duplicating effort.
|
|
Reply By:
|
rwalker
|
Reply Date:
|
1/26/2004 9:59:07 AM
|
Hi,
I'm interested in seeing your code.
Thanks
Renee C. Walker
|
|
Reply By:
|
jaucourt
|
Reply Date:
|
1/28/2004 10:34:35 AM
|
Ok, just didn't want to post if no-one was interested...
One thing to note is that when writing your SQL commands, you must have spaces around any comparison operators. For example,
SELECT * FROM pubs WHERE pubid = @pPubId
would work, whereas
SELECT * FROM pubs WHERE pubid=@pPubId
wouldn't. This is because when parameters are being located, a split command is used to separate out all words in the sql statement.
Any probs, just ask.
Code as follows ( I've formatted as best as I can for this box, but still some overflows):
Imports System.Data
Imports System.Data.SqlClient
' a class to set up all connections and commands and serve them to the
' operations class.
Public Class Objects
' procedure for adding a new command:
' 1. Add the new sql statement into GlobalVariables.
' 2. Add a new element to the relevant enumeration.
' 3. Check the parameter is initialised in InitialiseParameters()
' 4. Add the additional 'case' statement to the relevant read-only
' property (or create a new read only property to serve up the
' command.) and serve up the command using the 'GenerateCommand'
' function.
#Region "Class Level Declarations"
' connection string is picked up from an application configuration
' file...
Dim mConDespatch As New SqlConnection( _
Configuration.ConfigurationSettings.AppSettings( _
"Connection String"))
' collection to hold all parameter objects...
Dim mDespatchParameters As New Collection
' created various enumerations here for purposes of returning similar
' commands...
Public Enum ExampleCommandEnum
SelectCommand1
SelectCommand2
End Enum
#End Region
Public Sub New()
InitialiseParameters()
End Sub
#Region "General Database Objects"
Public ReadOnly Property DespatchConnection()
Get
Return mConDespatch
End Get
End Property
#End Region
#Region "Dynamic Command Generation"
Private Function GenerateCommand(ByVal CommandText As String) As _
SqlCommand
' dimension and set up command...
Dim cmdNew As New SqlCommand(CommandText)
With cmdNew
.Connection = mConDespatch
.CommandType = CommandType.Text
End With
' get necessary parameters...
Dim Params() As String = GetParameters(CommandText)
Dim Param As String
If Not (Params Is Nothing) Then
For Each Param In Params
' check parameter not already present...
If cmdNew.Parameters.IndexOf(Param) = -1 Then
Try
cmdNew.Parameters.Add( _
mDespatchParameters(Param).ParameterName, _
mDespatchParameters(Param).SqlDbType, _
mDespatchParameters(Param).Size, _
mDespatchParameters(Param).SourceColumn)
Catch ex As Exception
Throw New Exception("Parameter " & Param & _
" is not part of DespatchParameters " & _
"collection")
End Try
End If
Next
End If
Return cmdNew
End Function
Private Function GetParameters(ByVal CommandText As String) _
As String()
Dim words() As String
Dim params() As String
Dim cursor As Integer = 0, counter As Integer = 0
Dim remove() As Char = {",", ")", "("}
' split text into an array, each element containing one
' word...
words = CommandText.Split(" ")
' go through array, and add all parameters(beginning with
' '@p') to params array...
counter = 0
For cursor = 0 To (words.GetLength(0) - 1)
If words(cursor).TrimStart(remove).StartsWith("@p") Then
ReDim Preserve params(counter)
' remove leading and trailing commas and opening and
' closing brackets...
params(counter) = _
words(cursor).TrimEnd(remove).TrimStart(remove)
counter += 1
End If
Next
Return params
End Function
Private Sub InitialiseParameters()
' initialise mDespatchParameters...
' initialise all required parameters (I have one parameter
' for each field in my database).
' As I add them to the collection, they get a string key
' which is the same as the parameter name, which allows them
' to be easily called by the GenerateCommand function...
mDespatchParameters.Add(New SqlParameter("@pFieldName", _
SqlDbType.Int, 4, "FieldName"), "@pFieldName")
End Sub
#End Region
Public ReadOnly Property ExampleCommand(ByVal Type As _
ExampleCommandEnum) As SqlCommand
Get
Select Case Type
Case 0
' I define all sql commands in a separate module,
' following added just for readability
Dim ExampleSQLCommand As String = _
"SELECT * FROM PUBS"
Return GenerateCommand(ExampleSQLCommand)
Case 1
Dim ExampleSQLCommand As String = _
"SELECT TOP 1 * " & _
"FROM PUBS"
Return GenerateCommand(ExampleSQLCommand)
End Select
End Get
End Property
End Class
|
