Solved

Linq to SQL - passing string null to stored proc

Posted on 2010-09-22
4
835 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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…

772 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