The Self-Taught Programmer

Recipes from real-world experience with asp.net.

Archive for the ‘How-To’ Category

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 ,

How to Convert a Generic List to a Comma-Delimited List

leave a comment »

The Situation:

You have a List<string> and need to convert it to a comma-delimited list (for display or storage).

A Solution:

This is a great example of how C# has improved over the years. I found this solution in this StackOverflow discussion and don’t want to lose it, so I am copying it here for reference.

List<string> strings = <code>...;

// .NET 2.0:
string joined = string.Join(",", new List<string>(strings).ToArray());

// .NET 3.5:
string joined = string.Join(",", strings.ToArray());

// .NET 4.0:
string joined = string.Join(",", strings);

Thanks to Jon Skeet, Xavier Poinas and the rest of the guys that participated in that discussion. Gotta love StackOverflow!

Written by Jim McMullen

March 25, 2013 at 11:32 am

Posted in How-To

Tagged with ,