Getting SqlConnection from EntityConnection

I love ADO.NET Entity Framework. The problem I ran into is I wanted to call a stored procedure, but I can’t really do that unless the stored procedure returns entity information, etc. I really just wanted to call a stored procedure, though it could have been embedded SQL also (if you do that sort of thing :), but I needed a SqlConnection to do that. All I had was a DbConnection which can be cast to and EntityConnection since I am using the EF. The question was how do I get to the SqlConnection that EF uses. Sure, I could have created another entry in my web.config or app.config and added another connection string. I don’t like to have some many connection strings though. A little looking around in the debugger and the solution became obvious.

Below is a simple method that takes my DbConnection and casts it to a EntityConnection. It then accesses the StoreConnection which returns a DbConnection and is cast to a SqlConnection. It then just gets the connection string from the SqlConnection,. From that point I can open a new connection of my own. Alternatively, I could have checked the state of that connection and opened and closed it appropriately. I didn’t want to worry about the state of the connection and messing up the EF, so I just create a new connection. Though both seem to work for my basic testing.

using System.Data.EntityClient;
using System.Data.SqlClient;
...
private string GetADOConnectionString()
{
    SalesSyncEntities ctx = new SalesSyncEntities();
    EntityConnection ec = (EntityConnection)ctx.Connection;
    SqlConnection sc = (SqlConnection)ec.StoreConnection;
    string adoConnStr = sc.ConnectionString;
    return adoConnStr;
}

How to use the Ajax Toolkit AutoCompleteExtender

I can never seem to remember the basics of how to use the AJAX Toolkit AutoCompleteExtender. It isn’t that it is all that difficult, it is just that there are things that I forget. The sample page is actually quite useful.

Step 1: Create a Web Service that you can call.

First be sure to uncomment or add the ScriptService attribute to your web service. It should look like this and be above the class declaration.

[System.Web.Script.Services.ScriptService]

The second thing to know is when you create your web method you MUST use very specific parameters for your web method. The one exception (sort of) is if you use the ContextKey property, but even then you are only adding a parameter. The name of the method is NOT important because you specify that in the extender properties. Your method must have two parameters named EXACTLY as shown below and in the order below. The return type can be string[] or List<string> because they both end up as string[] when sent as xml.

[WebMethod]
public List<string> GetEmails(string prefixText, int count)
{
…
}

Step 2: Add the Extender to the page or user control where you want to use it.

I would start by pasting the below on your page or user control.

<ajaxToolkit:AutoCompleteExtender
runat="server" 
ID="myAutoComplete" 
TargetControlID="txtSomeField"
ServicePath="~/MyWebService.asmx" 
ServiceMethod="GetEmails"
MinimumPrefixLength="3" 
CompletionInterval="300"
EnableCaching="true"
CompletionSetCount="50"
DelimiterCharacters=";, :" />

Then paste the following under your <%@ Control… > or <%@ Page …> tags (see the first line in the file).

<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="ajaxToolkit" %>

You will notice that my example doesn’t contain any animations, javascript, etc. I personally like it with no animation. The default seems zippier when I type. It also has the big advantage of making it very simple to put this in a user control and have that user control used multiple times on a page without issue. You can apparently still get it to work, but I don’t think it is worth it. Check out the solution posted here to see how to make it work. The sample shown on the sample page provided with the toolkit just won’t work if you use the extender multiple times on a page.

You may also notice I removed the stylessheet references, and the BehaviorID (which is in the sample project, but not in the documentation) is also not there. You do NOT want to set this if you use this multiple times on a page.

You can tweak the other parameters if you like. The ~ in the ServicePath only works for Web Sites, not Web Applications from what I understand. Please let me know if I am wrong since I have not actually done so. I did find that I needed the ~ if everthing is not at the top level in the web site. For example, I used the extender on a user control that is in a Controls directory under the root. The MyWebService.asmx is a the top root, so some kind path is needed. http://….MyWebService.asmx will also work, but that is difficult to make production and dev difficult to work with.

Step 3: Add ScriptManager

You will need either the ToolkitScriptManager or the ScriptManager on the page or a master page, etc for any AJAX stuff to work. Just remember, you can only have one of these on a page when the page is rendered. That means you can’t have it on a master page and on a user control.

DropDownList in a GridView with no code-behind / events handling needed

So, you have a GridView and you want to add a DropDownList to one of the columns. Typically, I would have populated the data and selected the proper item in the DropDownList. This code always seemed silly to me. They I saw in a Microsoft article how they did it. No code-behind or event handling needed. One note, I didn’t use the SqlDataSource and instead used the ObjectDataSource.

I could not do step 20 where they bind using the Field Binding radio button for City. I was however able to set the Custom Binding. Just typed it in there. Or you can do it from .aspx/.ascx page directly.

I did add my ObjectDataSource (you only need to worry about the select method) to the page and wired it up to the DropDownList.

Here is my ObjectDataSource

<asp:ObjectDataSource ID="dsMyDataSource" runat="server" SelectMethod="GetDropDownListItems" TypeName="DataModel.DAL" />

Let’s assume you have converted your field to a template already (if you haven’t you’ll have to). This means you have something similar to this.

<asp:TemplateField HeaderText="City">
    <ItemTemplate>
        <asp:Label ID="txtCity" runat="server" Text='<%# Bind("City") %>'/> 
    </ItemTemplate>
</asp:TemplateField>

Now you want to make that into a DropDownList of Cities. The process change is very minimal actually. Just make it look like the following:

<asp:TemplateField HeaderText="City">
    <ItemTemplate>
        <asp:DropDownList ID="ddlCity" runat="server" SelectedValue='<%# Bind("txtCity") %>'
        DataSourceID="dsMyDataSource" 
        DataTextField="City"
        DataValueField="City"/> 
    </ItemTemplate>
</asp:TemplateField>

So, you can go through all that document says, or you can make a few changes as noted here. Of course, the really crazy way now is to use code-behind. In all honesty, there is always a reason for different ways. This is a quite and powerful way to do this.

Creating a Decimal Dynamic Data control that allows commas

I love the Dynamic Data architecture. I won’t get into how it all works, and assume you understand how to use the Decimal Dynamic Data control. For the most part, you never have to worry about it except maybe decorate your Entity with [UIHint (“Decimal”)].

Just open up your project that is a Dynamic Data project in Visual Studio 2008 and go to the DynamicData directory it created for you. Download the my two files (DecimalAllowingCommas_Edit.ascx.cs and DecimalAllowingCommas_edit.ascx) and put them in the FieldTemplates directory. Don’t forget to change your namespace and references to it.

The two files are slightly modified versions of the Decimal_Edit.ascx and Decimal_Edit.ascx.cs files that are already in that directory.

The difference is that I removed the CompareValidator and replaced it with a RegularExpressionValidator since the CompareValidator doesn’t allow for commas in the textfield, but my regular expression does. I also add a line in the Page_Load for this control that sets the ValidationGroup to the same as the other validators in that control. That gives us basically the same validation as we had before, but with commas allowed.

That handled the JavaScript validation and even server side validation, but when the values are automatically pushed to the properties on the entity which are of type double, .Net throws an exception that basically says it can’t convert the string to a double because it has commas in it. Seems a bit silly to me that it can’t do that, but we’ll fix that. I just removed the contents of the ExtractValues method and replaced them with my own that use the Decimal.TryParse() method to do the parsing. This method happily accepts the commas.

Now that we have support for our new type of Dynamic Data control, we need to use it. That is easy. In your entity where you had [UIHint(“Decimal”)], just replace that with [UIHint(“DecimalAllowingCommas”)]. Now when the UI renders editable version of that field it will use the DecimalAllowingCommas_Edit instead of the Decimal_Edit Dynamic Data Control.

On last thing, you will need make sure the ApplyFormatInEditMode property is set to true for the declaration of the DynamicControl in your parent control or page. Here is a the idea of what I am saying talking about.

<asp:FormView>
<EditItemTemplate>
<asp:DynamicControl 
    ID="CostPerYearDynamicControl" runat="server" 
    DataField="CostPerYear" Mode="Edit" 
    ValidationGroup="Edit"  
    ApplyFormatInEditMode="True"/>
</EditItemTemplate>
</asp:FormView>

 

Here is the source for the .ascx in case you don’t want to download the file.

<%@ Control Language="C#" CodeFile="DecimalAllowingCommas_Edit.ascx.cs" Inherits="MyApp.DecimalAllowingCommas_EditField" %>

<asp:TextBox ID="TextBox1" runat="server" CssClass="droplist" Text='<%# FieldValueEditString %>' Columns="10"></asp:TextBox>

<asp:RequiredFieldValidator runat="server" ID="RequiredFieldValidator1" CssClass="droplist" ControlToValidate="TextBox1" Display="Dynamic" Enabled="false" />
<asp:RegularExpressionValidator runat="server" ID="RegularExpressionValidator1" CssClass="droplist" ControlToValidate="TextBox1" Display="Dynamic" Enabled="false" />
<asp:RangeValidator runat="server" ID="RangeValidator1" CssClass="droplist" ControlToValidate="TextBox1" Type="Double"
    Enabled="false" EnableClientScript="true" MinimumValue="0" MaximumValue="100" Display="Dynamic" />
<asp:DynamicValidator runat="server" ID="DynamicValidator1" CssClass="droplist" ControlToValidate="TextBox1" Display="Dynamic" />
<asp:RegularExpressionValidator ID="moneyRegexValidator" runat="server"
    ControlToValidate="TextBox1" ErrorMessage="Must be of the format #,###.##."  
    ValidationExpression="^([1-9]{1}[0-9]{0,2}(\,[0-9]{3})*(\.[0-9]{0,2})?|[1-9]{1}[0-9]{0,}(\.[0-9]{0,2})?|0(\.[0-9]{0,2})?|(\.[0-9]{1,2})?)$"></asp:RegularExpressionValidator>

Here is the code for the .ascx.cs

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Collections.Specialized;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Xml.Linq;
using System.Web.DynamicData;

namespace MyApp
{
    public partial class DecimalAllowingCommas_EditField : System.Web.DynamicData.FieldTemplateUserControl
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            TextBox1.ToolTip = Column.Description;

            SetUpValidator(RequiredFieldValidator1);
            SetUpValidator(RegularExpressionValidator1);
            SetUpValidator(RangeValidator1);
            SetUpValidator(DynamicValidator1);

            // copy it from one of the validators that knows what group we are in. :)
            moneyRegexValidator.ValidationGroup = DynamicValidator1.ValidationGroup;

        }

        protected override void ExtractValues(IOrderedDictionary dictionary)
        { 
            decimal result;
            bool successful = Decimal.TryParse(TextBox1.Text, out result);
            if (successful)
            {
                dictionary[Column.Name] = result;
            }   
        }

        public override Control DataControl
        {
            get
            {
                return TextBox1;
            }
        }
    }
}

Use Reflection to Get and Set Properties on a class

Sometimes it is useful to access a property on a class that you may or may not have created. Use the code below to set or get a value by name for any class.

using System;
using System.Data;
using System.Configuration;
using System.Reflection;

namespace MyApp
{

    public class ReflectionHelper
    {
        public ReflectionHelper()
        {

        }

        public static Object GetProperty(System.Object obj, System.String propertyName)
        {

            if (propertyName == null)
            {
                throw new System.ArgumentException("No name specified");
            }

            PropertyInfo pi = obj.GetType().GetProperty(propertyName);

            if (pi == null)
            {
                throw new Exception("Object does not have a property named: " + propertyName);
            }

            return pi.GetValue(obj, null);

        }

        public static void SetProperty(System.Object obj, System.String propertyName, System.Object propertyValue)
        {
            if (obj == null)
            {
                throw new System.ArgumentException("No target specified");
            }
            if (propertyName == null)
            {
                throw new System.ArgumentException("No name specified");
            }

            PropertyInfo pi = obj.GetType().GetProperty(propertyName);

            if (pi == null)
            {
                throw new Exception("Object does not have a property named: " + propertyName);
            }

            pi.SetValue(obj, propertyValue, null);

        }
    }
}

Make Windows Offline Files faster over slower connections

Here is the scenario I have to work with. I have a laptop running Windows XP. My profile is roaming and My Documents is mapped to a network drive. My Documents and the network drive are synchronized using a Windows feature called Offline Files. I personally don’t like it in my scenario, but understand it has it’s place. IMHO, roaming profiles have no place when I am the ONLY one that uses the computer and never log onto another computer or if I did, I don’t need my user profile. Regardless, I work in corporate America and there is nothing I can do to change that.

Well, there is something I can do about it.

Here is what I found out.

The big thing figured out is that I needed to find a way to tell the Offline Files feature that I want it to consider my laptop to be disconnect / offline so that it will not try to access the files directly on the network. The solution to this is to use a tool called CSCCMD.exe 1.1. It is a WONDERFUL command line tool for working with Offline Files. It allows you to do everything you can in the UI and more.

To get to the point, to tell the Offline Files feature to not use the network and instead use the local copy of offline files, you need CSCMD.exe 1.1 (1.0 is missing lots of features). You can get it from the link below. Please not the Windows Server 2003 Resource Kit Tools only has version 1.0.

Download

Now that you have the file, unzip it and put it in your system32 or other location that is in your executable path.

Type csccmd.exe /DISCONNECT:”\\server\share”

Be sure to adjust to point to your network share that is being used by Offline Files.

If you don’t know what that path should be, you are in luck, the tool will tell you that. The following command actually shows all the files that are being cached, but you can see what the share path is.

csccmd.exe  /RESID

Type csccmd.exe /RESID

For me, the first line it returned was the path. You’ll see the pattern after you look at all the lines. The on that they all have in common is probably the one you want. Basically, you need to find \\server\share where server is the name of the server and share is the name of the share.

For more information, check of the references at the bottom of this page or try csccmd.exe /help.

Please note that you must manually synchronize now. For example, when I get back to the office, I’ll synchronize.

Additional Speed boost

I also found out that if disconnect the network that shows in My Computer that My Computer and other things work much faster as well. You can do that via the command line. In the example below the network share is mapped to the h drive.

net use h: /delete

Reference:

CSCCMD : Tool to manage Offline files in Windows

Working with network files when you are offline