The Self-Taught Programmer

Recipes from real-world experience with asp.net.

How to Set a Default Value on a Configurable Variable in a SQL Server Stored Procedure Using IsNull()

leave a comment »

The Situation

You have a SQL Server stored procedure that needs variable for use in a query, but you don’t want to pass it into the stored procedure as a parameter, and you don’t want to hard-code it. Instead you have the value stored in a table and want to retrieve it from there. There is the possibility that the value in the table could be NULL.

I ran into this situation when creating some dynamic sql in a stored procedure. In some instances, the stored proc needed to access a table in Server1, but in other situations, it needed to access a table with the same name on Server2. Because servers are sometimes retired or replaced, hard-coding the server name would cause errors at a later date. The server names were made configurable by adding a value to an ApplicationSettings table:

CREATE TABLE [ApplicationSettings](
	[SettingName] [nvarchar](50) NOT NULL,
	[SettingValue] [nvarchar](max) NULL
);
INSERT INTO [ApplicationSettings] (SettingName, SettingValue)
VALUES ('GeneralInfoServer', 'SERVER2')

Retrieving that value so it can be added to the dynamic sql is simple:

DECLARE @Server VARCHAR(50) = 'Server1'; -- Default value
SET @Server = (SELECT SettingValue 
		FROM ApplicationSettings 
		WHERE SettingName = 'GeneralInfoServer');

 

The problem is that someone could come along and delete that record from ApplicationSettings. In that case, the SELECT statement above would return NULL, and the default value of @Server would be set to NULL, causing an error when running the stored proc.

A Solution

T-SQL has the ISNULL(expression, alternative_value) function, which is usually used like this:

DECLARE @Server VARCHAR(50) = 'Server1'; -- Default value
SET @Server = (SELECT ISNULL(SettingValue, 'SERVER1') 
		FROM ApplicationSettings 
		WHERE SettingName = 'GeneralInfoServer');

 

However, this only works if the record exists and the SettingValue is NULL. It still returns a null value if the record doesn’t exist.

There are more complicated ways of writing the query, but simplest way to get the default value is to move ISNULL to the outside of the select query like this:

DECLARE @Server VARCHAR(50) = 'Server1'; -- Default value
SET @Server = ISNULL((SELECT SettingValue 
			FROM ApplicationSettings 
			WHERE SettingName = 'GeneralInfoServer'), @Server);

 

This statement says, “Retrieve the value of ‘GeneralInfoServer’, but if that record doesn’t exist, return the default value of @Server.”

But we can make one more improvement. What if the record exists but the value is NULL? Combine the last 2 queries like this:

DECLARE @Server VARCHAR(50) = 'Server1'; -- Default value
SET @Server = ISNULL((SELECT ISNULL(SettingValue, @Server) 
			FROM ApplicationSettings 
			WHERE SettingName = 'GeneralInfoServer'), @Server);

 

Of course this could STILL fail if the default value is not valid.

Advertisements

Written by Jim McMullen

January 10, 2015 at 12:28 am

Posted in How-To

Tagged with , ,

How to Sort a Generic List of Objects

leave a comment »

The Situation

You are trying to sort a Generic List of Objects based on a property of the Objects.

A Solution

There are two easy options for sorting a Generic List populated with Objects: in place and sorting to a new List. Use Linq.

“In Place” sorting of an existing List (the code below only works with text fields):

using System.Linq;

Users.Sort((a, b) => string.Compare(a.LastName, b.LastName));

Sort a List to a new list:

List<Users> UserList = Users.OrderBy(o => o.LastName).ToList();

Written by Jim McMullen

January 2, 2014 at 5:08 pm

Posted in How-To

Tagged with ,

How To Extend the Timeout Period on an Entity Framework Query

with 3 comments

The Situation:

You have a long running Entity Framework query that often creates a “System.Data.SqlClient.SqlException” with the message “Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.”

A Solution:

As near as I can find, the default timeout for EF queries using the SQL Server 2008 connection provider is 30 seconds. If your query runs longer than that, you are going to get the error above.

If you are using dbContext, the solution to this is to add the following code before your query to set the timeout to a longer value:

((IObjectContextAdapter)Context).ObjectContext.CommandTimeout = 180;

Of course, you can use any number of seconds you need. This line of code moves you down from dbContext to the underlying ObjectContext to make the change.

I’m not sure why Microsoft didn’t keep the simpler syntax from the older version of ObjectContext for dbContext. If you are using ObjectContext, you can do without the adapter, like this:

this.Context.CommandTimeout = 180;

To keep from having to put this code in every query, you could call it when you instantiate the dbContext. You may find it helpful to keep the default timeout for most queries and catch the error to give you insight into database problems.

Written by Jim McMullen

July 28, 2013 at 3:16 pm

Posted in How-To

Tagged with , ,

How To Process a Portion of a Form via Callback and Display a Results Message Using DevExpress Controls

leave a comment »

The Situation:

You have an ASP.NET page with multiple form sections, and you want to process only one section when an ASPxButton control is clicked. You also want to display a status message after processing.

A Solution:

This should be basic Ajax-type stuff, but as I am a self-taught programmer, I came to this late. Also, I am using DevExpress controls in my current application, which function a bit differently than standard Ajax. Without Ajax, you would just process the page on postback and set the results label in the code-behind. However, using a callback rather than postback is a great way to process only a portion of the page.

Many of the DevExpress controls have an CallbackComplete client-side event, so you would handle the callback event, return a result to the control, and use the CallbackComplete event to set the results message label’s text value.

However, the ASPxButton does NOT have an CallbackComplete event that you can use (inconvenient!). The way around this is to use an ASPxCallback control.

1) Add an ASPxCallback control to your page. Add text to the control’s client-side CallbackComplete event to update the results label.

<dx:ASPxCallback ID="cbSaveResults" runat="server" ClientInstanceName="cbSaveResults" OnCallback="cbSaveResults_Callback">
	<ClientSideEvents CallbackComplete="function(s, e) {
				lblResults.SetText(e.result);
			}" />
</dx:ASPxCallback>

 
2) Add a method to the code-behind to handle the callback:

protected void cbSaveResults_Callback(object source, DevExpress.Web.ASPxCallback.CallbackEventArgs e)
{
	string returnMessage = "";

	try
	{
		// PUT CODE HERE TO PROCESS THE FORM

		returnMessage = "Your Changes have been saved.";
	}
	catch (Exception ex)
	{
		returnMessage = String.Format("There was a problem saving your changes. The error message was '{0}'", ex.Message);
	}

	e.Result = returnMessage;
}

 
3) Finally, add an ASPxButton to your page and add code to its client-side Click event to call the ASPxCallback control’s server-side OnCallback event (and blank the results label). Don’t forget to set the ASPxButton’s AutoPostBack=”False”!

<dx:ASPxButton ID="btnSaveResults" runat="server" Text="Save" AutoPostBack="False">
	<ClientSideEvents Click="function(s, e) {
				lblStatus.SetText('');
				cbSaveResults.PerformCallback();
			}" />
</dx:ASPxButton>

 
That’s all. When your user clicks the button, the ASPxButton actually throws the processing over to the ASPxCallback control, which then finishes and updates the results label. ALMOST as good as if DevExpress had included the functionality into the ASPxButton itself.

Written by Jim McMullen

July 18, 2013 at 4:20 pm

How To Perform a Callback on an ASPxGridLookup Control

with one comment

The Situation:

You have a DevExpress ASPxGridLookup control on your page and need to update its contents when some event occurs (such as a parent drop-down value changes). The problem is that the ASPxGridLookup control does not implement an OnCallback event.

A Solution:

First of all, this is not my solution. I found it on the DevExpress support site. It took me a while to find it, and to decode it, so I am putting it here so that I will be able to find it easier next time. I am also going to outline what is happening and why it works. The original solution was posted here.

There are two main ways to go about this. The first is to embed the ASPxGridLookup in an ASPxCallbackPanel. This is the first one that comes to mind, but there is an even simpler solution that involves adding a custom callback event to the instance of the ASPxGridLookup control.

Here is an ASPxGridLookup control on an aspx page:

<dx:ASPxGridLookup ID="ASPxGridLookup1" runat="server" ClientInstanceName="ASPxGridLookup1"
	TextFormatString="{0}" KeyFieldName="ProdId"
	AutoGenerateColumns="False" oninit="ASPxGridLookup1_Init">
	<GridViewProperties>
		<SettingsBehavior AllowFocusedRow="True" AllowSelectSingleRowOnly="True" />
		<SettingsPager Mode="ShowAllRecords">
		</SettingsPager>
	</GridViewProperties>
	<Columns>
		<dx:GridViewDataTextColumn VisibleIndex="0" FieldName="ProdId" Width="85px" Caption="ID">
		</dx:GridViewDataTextColumn>
		<dx:GridViewDataTextColumn VisibleIndex="1" FieldName="Description" Width="250px" Caption="Description">
		</dx:GridViewDataTextColumn>
	</Columns>
	<ClientSideEvents Validation="function(s,e) {if(s.GetValue()=='0') { e.isValid=false; e.errorText='Please select a Network ID.';}}" />
	<ValidationSettings SetFocusOnError="True" ErrorDisplayMode="ImageWithTooltip" ValidationGroup="Profile">
		<RequiredField IsRequired="True" ErrorText="Network ID is required" />
	</ValidationSettings>
</dx:ASPxGridLookup>

 

Then attach javascript like this to a calling control, for example when the selected item of an ASPxComboBox is changed:

<script language="javascript" type="text/javascript">
	function OnComboBoxChangedEventHandler() {
		ASPxGridLookup1.GetGridView().PerformCallback();
	}
</script>

 

Now to make this work, you have to do two things in the the code behind.

(1) The ASPxGridLookup control contains an ASPxGridView inside it (that’s what makes it such a valuable control). So you need to attach a ASPxGridViewCustomCallbackEventHandler to the ASPxGridView in the ASPxGridLookup control on init. Do that like this:

using DevExpress.Web.ASPxGridLookup;
using DevExpress.Web.ASPxGridView;

protected void ASPxGridLookup1_Init(object sender, EventArgs e)
{
	ASPxGridLookup lookup = (ASPxGridLookup)sender;
	ASPxGridView gridView = lookup.GridView;
	gridView.CustomCallback += new ASPxGridViewCustomCallbackEventHandler(ASPxGridLookup1_gridView_CustomCallback);
}

 

(2) The final step is to add the code to handle your new custom callback event handler. Do that like this:

void ddlCompProdId_gridView_CustomCallback(object sender, ASPxGridViewCustomCallbackEventArgs e)
{
	ASPxGridView gridView = (ASPxGridView)sender;
	//Put code here to requery/rebind the datasource for the ASPxGridLookup control
	...
	gridView.DataBind();
}

 

That’s all. When your user triggers the callback, it should act just the same as if DevExpress had built callback functionality into the control.

Written by Jim McMullen

May 31, 2013 at 12:50 am

How to Split a String into a Generic List Rather Than an Array

leave a comment »

The Situation

You want to split a string, but the C# split() function returns a string[] array. You want a List<string> instead.

A Solution

If you are using .Net 3.5 or later, use Linq extensions:

using System.Linq;

string csvString = "a,b,c,d,e";
List<string> items = csvString.Split(',').ToList();

Written by Jim McMullen

May 29, 2013 at 5:08 pm

Posted in How-To

Tagged with ,

Programming Zealots

leave a comment »

I was reading a blog post the other day — well, it was more of a rant, actually — about the evils of using regions in your .NET code. The guy had joined a team that used regions as one of their programming conventions, and he refused (rudely, I thought) to abide by the team’s policy. I thought some of the authors reasons were valid, but some not so much. What bothered me about the post was that he not only told why he didn’t use regions, but also why no one else on the planet ever should, and that if you do, you are a “bad programmer“.

This took me back to my early days in programming. I had been working in ColdFusion for a while and the guys at Allaire and then Macromedia had started to add some semi-object-oriented constructs to the CF language. Up to this point, I had only worked with procedural languages and had no education in OOP, so I was trying to teach myself how to use the new constructs AND how to program in an object-oriented way while still trying to earn a living.

The CF programming community is not large, and there are some real “rock stars” there — opinion leaders that everyone looks to for help and advice. They spend a significant amount of time blogging and answering questions on the forums, and I sought their advice many times. Many of these gurus are very nice and patient with self-taught programmers, thank goodness.

There were a few, however, that I called “zealots”. To these guys, regardless of language used (CF, Java, C++, etc.), there is a “right” way programming, and God help anyone who does things differently or disagrees with their “orthodox” way.

Contrary to what every good teacher has said to me, the zealots believe there ARE stupid questions. Look through any forum site where programmers go to seek advice. You won’t have to view too many threads before you come across a question asked by a programmer who is trying to solve a problem but is being attacked because the very premise of the question offends a zealot in some way.

So after getting flamed a few times with my own “stupid” questions, I became afraid to ask anything. Not the best way to learn, right? I resorted to “lurking” in the forums and studying 3-inch-thick books. And doing. Lots of doing.

And one thing I learned is that the “zealots” are wrong. There is best practice, of course, that we should all strive to follow, and there are plenty of great and helpful design patterns. But these are tools that have to be applied in the right situations, and ignored when another way works better. What works for one person or team or project may be the wrong approach for another. I believe practicality trumps orthodoxy.

The “regions” rant I mentioned earlier doesn’t have anything to do with design patterns, but it is still a good lesson here, because it is an example of how people’s brains function differently from each other. In my case, I am much more effective if I can hide portions of the code that are not directly and immediately being worked on. I can focus so much better if it isn’t showing. I also find it easier to dive back into a class when I come back to it after several weeks or months and it is structurally organized into functional regions.

Zealots like the blogger writing that rant can only see the way they think, and their way is the “right” way, dammit. So if I do something different, I must be a “bad programmer”.

Written by Jim McMullen

May 23, 2013 at 10:10 am