Solved

Linq to SQL - passing string null to stored proc

Posted on 2010-09-22
4
830 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
  • 2
4 Comments
 
LVL 4

Accepted Solution

by:
rar3z earned 500 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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

759 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now