Solved

Linq to SQL - passing string null to stored proc

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

Stressed Out?

Watch some penguins on the livecam!

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard 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.
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

705 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