asked
Entity Framework, Optional parameters
I have a stored procedure that takes in about 14 optional parameters... how do I correctly write the beginning statement of the data reader to take in all optional parameters? Public Function SPConProfileData(ByVal Contract As String) As List(Of Contract) Dim list As List(Of Contract) = New List(Of Contract_Profile2)() Using conn As SqlConnection = New SqlConnection("MyConnection;") conn.Open() Using dcmd As SqlCommand = New SqlCommand("dbo.ContractSearch", conn) dcmd.CommandType = CommandType.StoredProcedure Dim prms As New SqlParameter("@Contract", SqlDbType.VarChar, 11) prms.Value = Contract & "%" dcmd.Parameters.Add(prms) My service: Public Function SPConProf(Optional ByVal Contract As String = "000023", Optional ByVal ContractDesc As String = "ThisDescription", Optional ByVal ClientName As String = "ThisClient",_ Optional ByVal ProjMgrName As String = "ProjMgr", Optional ByVal Status As String = "Active", Optional ByVal BusArrangement As String = "SPC", _ Optional ByVal OfficeName As String = "Pasadena", Optional ByVal GBU As String = "PIT", _ Optional ByVal ContractTerms As String = "BID", Optional ByVal BDCodeDesc As String = "Oil", _ Optional ByVal BDCodeTypeDesc As String = "StdIndustry") As List(Of Contract_Profile2) Dim list As List(Of Contract_Profile2) = New List(Of Contract_Profile2)() Using conn As SqlConnection = New SqlConnection("MyConnection;") conn.Open() Using dcmd As SqlCommand = New SqlCommand("dbo.ContractSearch", conn) dcmd.CommandType = CommandType.StoredProcedure Dim prms As New SqlParameter("@Contract", SqlDbType.VarChar, 11) prms.Value = Contract & "%" dcmd.Parameters.Add(prms) Using reader As SqlDataReader = dcmd.ExecuteReader While reader.Read() Dim d As DateTime If reader("BeginDate") Is DBNull.Value Then d = DateTime.MinValue Else d = Convert.ToDateTime(reader("BeginDate")) End If Dim dt As DateTime If reader("EndDate") Is DBNull.Value Then dt = DateTime.MinValue Else dt = Convert.ToDateTime(reader("EndDate")) End If Dim fund As UInteger If reader("ContractFunding") Is DBNull.Value Then fund = UInt32.MinValue Else fund = Convert.ToDecimal(reader("ContractFunding")) End If Dim fund2 As UInteger If reader("ContractValue") Is DBNull.Value Then fund2 = UInt32.MinValue Else fund2 = Convert.ToDecimal(reader("ContractValue")) End If list.Add(New Contract_Profile2() With { _ .Contract = reader("Contract").ToString(), _ .ContractDesc = reader("ContractDesc").ToString(), _ .ClientName = reader("ClientName").ToString(), _ .ProjMgrName = reader("ProjMgrName").ToString(), _ .ContractValue = fund2, _ .BeginDate = d, _ .EndDate = dt, _ .EntityName = reader("EntityName").ToString(), _ .Status = reader("Status").ToString(), _ .BusArrangement = reader("BusArrangement").ToString(), _ .ContractFunding = fund, _ .OfficeName = reader("OfficeName").ToString(), _ .GBU = reader("GBU").ToString(), _ .ContractTerms = reader("ContractTerms").ToString(), _ .BDCodeDesc = reader("BDCodeDesc").ToString(), _ .BDCodeTypeDesc = reader("BDCodeTypeDesc").ToString() _ }) End While conn.Close() End Using Return list End Using End Using End Function My Stored Procedure: ALTER PROCEDURE [ContractSearch] @Contract varchar (11) = Null, @ContractDesc varchar (50) = Null, @ProjMgrName varchar (30) = Null, @EndDate varchar(11) = Null, @ClientName varchar (50) = Null, @Status varchar(10) = Null, @BusArrangement varchar (3) = Null, @ContractFunding float = Null, @OfficeName varchar (50) = Null, @GBU varchar(10) = Null, @ContractTerms varchar (4) = Null, @BDCodeDesc varchar (50) = Null, @BDCodeTypeDesc varchar (50) = Null AS BEGIN SET NOCOUNT ON SELECT DISTINCT [Contract].[Contract], [Contract].ContractDesc, JobKeys.ProjMgrName, [Contract].EndDate, JobKeys.ClientName, [Contract].Status, [Contract].BusArrangement, [Contract].ContractFunding, Office.OfficeName, [Contract].ContractValue, [Contract].BeginDate, Entity.GBU, JobKeys.ContractTerms, BDCodes.BDCodeDesc, BDCodeTypes.BDCodeTypeDesc, Entity.EntityName FROM dbo.[Contract] INNER JOIN JobKeys ON [Contract].[Contract] = JobKeys.[Contract] INNER JOIN Entity ON JobKeys.Entity = Entity.Entity INNER JOIN Office ON JobKeys.Office = Office.Office INNER JOIN JobBDCodes ON JobKeys.Job = JobBDCodes.Job INNER JOIN BDCodes ON JobBDCodes.BDCode = BDCodes.BDCode INNER JOIN BDCodeTypes ON BDCodes.BDCodeType = BDCodeTypes.BDCodeType WHERE ((@Contract is NULL)or ([Contract].[Contract] LIKE '%' + @Contract + '%'))and ((@ContractDesc is NULL) or ([Contract].ContractDesc LIKE '%' + @ContractDesc + '%'))and ((@ProjMgrName is NULL) or (JobKeys.ProjMgrName LIKE '%' + @ProjMgrName + '%'))and ((@EndDate is NULL) or ([Contract].EndDate ]]]
You already do it correctly. Optional parameter in the stored proc simply means that it has a name, type, and uses default value unless the value is passed by the calling code. So, you can simply dim only the parameters you actually need to pass to the proc and add them to the command's parameters collection. Do not supply (add to the command's collection) the ones which you don't need, and you procedure will simply use defined default values for any optional parameters which you did not supply. All you need to worry about is the order of the parameters you supply must match. For example, if you have the proc with @p1, @p2 = null, and @p3 meaning that @p2 is optional, dim 2 parameters named @p1 and @p3 and add them to the command's collection in this order. Optionally, you can always create the same number of parameters and add them to the collection regardless of whether these are optional or not, just match the default values. This is convenient due to consistency, but does require a bit more typing. <\!-- **Begin Edit** I found the sorce of the problem. As it turns out, it is in the stored procedure itself, so there is no reason to add an optional float parameter to the command's collection, fixing the proc should be enough. The offending line in the proc is this one: ((@ContractFunding is NULL) or ([Contract].ContractFunding LIKE '%' + @ContractFunding + '%')) The reason that this does not work is because the ContractFunding column is float, which has a higher than character-based types precedence, so when the '%' is attempted to be implicitly converted to float the error is raised. This means that the line above should be replaced by something like this: ((@ContractFunding is NULL) or ([Contract].ContractFunding = @ContractFunding)) Here is the small sample to reveal the problem: set nocount on; declare @c varchar(11); declare @f float; declare @t table (c varchar(11), f float); insert into @t values ('Test1', 1); insert into @t values ('Test2', 2); insert into @t values ('Test3', 3); insert into @t values ('Other', 4); set @c = 'Test'; -- @f is still null -- here is the test to reproduce the problem begin try select * from @t where (@c is null or c like '%' + @c + '%') and (@f is null or f like '%' + @f + '%'); end try begin catch select error_message() error; end catch; -- this version is better select * from @t where (@c is null or c like '%' + @c + '%') and (@f is null or f = @f); set nocount off; go -- results: c f ----------- ---------------------- error --------------------------------------------- Error converting data type varchar to float. c f ----------- ---------------------- Test1 1 Test2 2 Test3 3 **End Edit** --> Oleg
Thank you Oleg! I'm giving this a try... After doing all of the necessary code for the stored procedure then creating the service... I just don't know how to handle the asynchronous call and include all of the controls to look for the data. AddHandler ConHist.SPContractProfileDataCompleted, AddressOf SPContractProfileDataCompleted ConHist.SPContractProfileDataAsync(txtContractNum.Text) How do I add multiple controls?
@technette Why do you need asynchronous call? Does the proc take too long to execute and /or there are too many users using the app at the same time? In any case, it looks like when the data returns, the method calls back into SPContractProfileDataCompleted where you can add whatever code you need to populate the controls. Just remember that any UI control's properties should be modified by the same thread which originally created the control in WPF or WinForms app if this is what you have.
Oleg, I had to do an asynchronous call because this is a silverlight application. All data calls in silverlight are asynchronous. The IDE is accepting multiple input controls now under the asynchronous call. AddHandler ConHist.SPContractProfileDataCompleted, AddressOf SPContractProfileDataCompleted ConHist.SPContractProfileDataAsync(txtContractNum.Text, txtContractDescr.Text, txtClientName.Text, txtProjectMan.Text, acbStatus.Text, acbBusinessArrang.Text, acbOffice.Text, acbEntity.Text, acbContractTerms.Text, acbScopeOfService.Text, acbCorpMarket.Text) I'm getting an error from the data reader now because stating "Error converting data type varchar to float." Trying to figure this one out so that I can see how the data comes through.
@technette This error probably means that you fed the value from the Text property of one of the controls to one of the parameters of the command object. When ExecureReader was called, it tried to convert this value to float and could not (possibly because the value was not numeric, such as blank). Please check how your parameters are created (their type and values). Float datatype is pretty evil for most purposes as it does not really make sense in most situations. It is only applicable if you need to store huge numeric values and don't mind that the data is stored with approximate rather than exact values (distance between stars or something like this). It would be far better in most cases to consider normal decimal data type with precision and scale chosen to store data accurately enough. In any case, try to debug your code to the point just before ExecuteReader is called, copy the parameters' values and call the proc from SSMS. This way, it will be easier to see the source of the problem. Maybe you can edit your question and add a complete source of your SPConProfileData method as well as the signature of the proc (proc name and parameter list).
