Solved

Linq to SQL - passing string null to stored proc

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…

920 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

16 Experts available now in Live!

Get 1:1 Help Now