?
Solved

Linq to SQL - passing string null to stored proc

Posted on 2010-09-22
4
Medium Priority
?
843 Views
Last Modified: 2013-11-11
Hi there,

I am using LINQ and have a store proc that I call through the data context. It takes the following parameters.... in the Proc itself...  ps... this looks a long question... but in essence I want to know how to pass a null string to the stored proc...

@AccountName		VARCHAR(100)	= NULL,
@FromOpeningDate		DATETIME		= NULL,
@ToOpeningDate		DATETIME		= NULL	

Open in new window


In the Linq its defined as...

		[Function(Name="dbo.Report_TradeExecutionReport")]
		public ISingleResult<Report_TradeExecutionReportResult> Report_TradeExecutionReport([Parameter(Name="AccountName", DbType="VarChar(100)")] string accountName, [Parameter(Name="FromOpeningDate", DbType="DateTime")] System.Nullable<System.DateTime> fromOpeningDate, [Parameter(Name="ToOpeningDate", DbType="DateTime")] System.Nullable<System.DateTime> toOpeningDate)
		{
			IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), accountName, fromOpeningDate, toOpeningDate);
			return ((ISingleResult<Report_TradeExecutionReportResult>)(result.ReturnValue));

Open in new window


and finally I call it like this...

            using (var dataContext = DatabaseContext.Brokerage)
            {
                DateTime? dateFrom = dteDateFrom.DateTime;
                DateTime? dateTo = dteDateTo.DateTime;
                String account = "";

                if (dateFrom.Equals(System.DateTime.MinValue))
                    dateFrom = null;

                if (dateTo.Equals(System.DateTime.MinValue))
                    dateTo = null;

                if (cboAccounts.SelectedIndex > 0)
                    account = cboAccounts.Text;

                grdReport.DataSource = dataContext.Report_TradeExecutionReport(account , dateFrom, dateTo);

            }

Open in new window


If the dates arnt set then I can set them to null but the accounts is an empty string and so the call returns no records... if I replace the account parameter with null, then I get results back...
like so..
                grdReport.DataSource = dataContext.Report_TradeExecutionReport(null, dateFrom, dateTo);

Open in new window

I tried using (account == string.empty? null : account) but the compiler doesnt like this...

Oh the stored proc works fine when I test it stand alone...

So what am I missing... thanks M
0
Comment
Question by:MickyMc
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 4

Accepted Solution

by:
rar3z earned 2000 total points
ID: 33739117
Did you try using the following?

string.IsNullOrEmpty(item2check);
0
 
LVL 11

Expert Comment

by:Larissa T
ID: 33739138
Usually you better to keep all default sin DB

depending on application it can send '', or 'null' or whatever
and datetime value of '' i think is 1901/1/1

for example add this code to you procedure. You can use some other dates that you concider invalid in you DB
if  @FromOpeningDate <'1/1/1901'  or @FromOpeningDate > '1/12050'
set @FromOpeningDate = null
0
 

Author Closing Comment

by:MickyMc
ID: 33739240
very tidy rar... I was getting some casting error the other way.. thanks...

@trof.. yes trof good point and I do this in some cases alright but just need it this way at the moment... thanks
0
 
LVL 4

Expert Comment

by:rar3z
ID: 33739308
Thanks Micky. Glad my 1 line solution was the best solution. :)
0

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question