How to call a function in Oracle in .NET, not a stored procedure

Ever think it would be oh so simple to call a function on an Oracle Database? So did I. I am normally writing SQL code for Microsoft SQL Server, but we also integrate with other systems who run Oracle.

I needed to call a function on the Oracle database to retrieve certain data. So I plugged it in thinking I could call it with a simple select. Wrong.

It is still simple, just had a little extra to the select to make it work.

OracleCommand command = provider.Connection.CreateCommand();
command.CommandType = System.Data.CommandType.Text;

//set sp name
command.CommandText = string.Format("Select PKG_NAME.VALIDATE({0}) from dual", id) ;

int returnValue = (int)(decimal)command.ExecuteScalar();
return returnValue ==1;

Hopefully this helps you out. Pretty simple to do, just a matter of knowing how to do it.

Happy Coding!

How to use SSL transport security using a certificate in your WCF hosted service…

So you want to use some channel security in your WCF service. Maybe you even want to guarantee the server your are talking to is who they say they are.

Well, transport security is what you are looking for. It is really quite easy to implement.
Things you will need:
1) A certificate from a trusted signing authority (thawte,godaddy, verisign,etc)
2) A WCF service (duh)

You will need to make changes to the server app.config and the client app.config.

On the Server:

You will need to add this tag to the binding:
<security mode=”Transport”>
<transport clientCredentialType=”None”/>
</security>

We are basically saying here, use transport security but don’t look for a client certificate.

You will also need to create a service behavior.
<behaviors>
<serviceBehaviors>
<behavior name=”MyServiceBehavior”>
<serviceCredentials>
<serviceCertificate findValue=”CN=server.contoso.com” storeLocation=”LocalMachine” storeName=”My” x509FindType=”FindBySubjectDistinguishedName” />
<clientCertificate>
<authentication certificateValidationMode=”None” />
</clientCertificate>
</serviceCredentials>
</behavior>
</serviceBehaviors>
</behaviors>

Notice the serviceCertificate tag. This is the important one. the CN=XXXXX is the portion you need to change to your servers certificate subject.

Now the service is setup. You will need to modify the Client app.config as follows:

You need to change the bindings on the client the same way as the server. Example.
<bindings>
<netTcpBinding>
<binding  name=”TCP_Binding” …..>
<security mode=”Transport”>
<transport clientCredentialType=”None” />
</security>
</binding>
</netTcpBinding>
</bindings>

Now you have your service protected with SSL and validation.

Happy coding!

How to read the Distributed Transaction Coordinator settings from C#…

I wrote a nice helper utility class for reading the settings from the MSDTC in .NET.  My WCF service requires distributed transactions, so I want to make sure my server was able detect if it was on. If its not, I throw an error and shutdown.

So, here is the class for your pleasure.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Win32;
using System.Threading;

namespace SeekfordsNamespace
{
    /// <summary>
    /// Used to get the DTC settings on a machine
    /// </summary>
    internal static class DTCUtility
    {
        /// <summary>
        /// Reads the DTC settings.
        /// </summary>
        /// <returns></returns>
        public static DTCSettings ReadDTCSettings()
        {
            //Lets grab the DTC settings from the registry and then populate our entity
            try
            {
                //we work from the local machine key
                var localReg = Registry.LocalMachine;
                //set the root dtc key
                string rootKey = @"SoftwareMicrosoftMSDTC";
                string securityKey = rootKey +@"Security";
                var dtcReg = localReg.OpenSubKey(rootKey, false);
                var securityReg = localReg.OpenSubKey(securityKey, false);

                DTCSettings settings = new DTCSettings();
                try
                {
                    //is network dtc access even turned on at all
                    settings.NetworkDTCAccess = ((int)securityReg.GetValue("NetworkDtcAccess", 0)) == 1;
                    //default to false, then prove it is on.
                    settings.AllowInbound = false;
                    settings.AllowOutbound = false;
                    //check to see if network access is on at all and transactions allowed
                    if (settings.NetworkDTCAccess &&
                        (((int)securityReg.GetValue("NetworkDtcAccessTransactions", 0)) == 1))
                    {
                        //so we can talk to network, can we allow outbound?
                        if (settings.NetworkDTCAccess &&
                        (((int)securityReg.GetValue("NetworkDtcAccessOutbound", 0)) == 1))
                        {
                            settings.AllowOutbound = true;
                        }
                        //same as above, but inbound connections allowed
                        if (settings.NetworkDTCAccess &&
                        (((int)securityReg.GetValue("NetworkDtcAccessInbound", 0)) == 1))
                        {
                            settings.AllowInbound = true;
                        }
                    }
                    //grab authentication values
                    bool allowOnlySecureRPCCalls = (int)dtcReg.GetValue(@"AllowOnlySecureRpcCalls", 0) == 1;
                    bool fallbackToUnsecuredRPC = (int)dtcReg.GetValue(@"FallbackToUnsecureRPCIfNecessary", 0) == 1;
                    bool turnOffRpcSecurity = (int)dtcReg.GetValue(@"TurnOffRpcSecurity", 0) == 1;
                    //process the logic to determine which mode is active.
                    settings.MutualAuthenticationRequired = (allowOnlySecureRPCCalls && !fallbackToUnsecuredRPC && !turnOffRpcSecurity);
                    settings.IncomingCallerAuthenticationRequired = (!allowOnlySecureRPCCalls && fallbackToUnsecuredRPC && !turnOffRpcSecurity);
                    settings.NoAuthenticationRequired = (!allowOnlySecureRPCCalls && !fallbackToUnsecuredRPC && turnOffRpcSecurity);

                }
                finally
                {
                    try
                    {
                        //close but not a failure if there is an error
                        dtcReg.Close();
                        securityReg.Close();
                    }
                    catch { }
                }
                return settings  ;
            }
            catch
            {
                return null;
            }
        }

    }

    /// <summary>
    /// DTC Settings
    /// </summary>
    internal class DTCSettings
    {
        /// <summary>
        /// Determines whether DTC on the local computer is allowed to access the network.
        /// This setting must be enabled in combination with one of the other settings to
        /// enable network DTC transactions.
        /// </summary>
        /// <value><c>true</c> if [network DTC access]; otherwise, <c>false</c>.</value>
        public bool NetworkDTCAccess { get; set; }
        /// <summary>
        /// Allows a distributed transaction that originates from a remote computer to run on this computer.
        /// Default setting: Off
        /// </summary>
        /// <value><c>true</c> if [allow inbound]; otherwise, <c>false</c>.</value>
        public bool AllowInbound { get; set; }
        /// <summary>
        /// Allows the local computer to initiate a transaction and run it on a remote computer.
        /// </summary>
        /// <value><c>true</c> if [allow outbound]; otherwise, <c>false</c>.</value>
        public bool AllowOutbound { get; set; }
        /// <summary>
        /// Adds support for mutual authentication in future versions and is the highest secured
        /// communication mode. In the current versions of Windows and Windows Server, it is
        /// functionally equivalent to the Incoming Caller Authentication Required setting.
        /// This is the recommended transaction mode for clients running Windows XP SP2 and
        /// servers running a member of the Windows Server 2003 family.
        /// </summary>
        /// <value>
        /// 	<c>true</c> if [mutual authentication required]; otherwise, <c>false</c>.
        /// </value>
        public bool MutualAuthenticationRequired { get; set; }
        /// <summary>
        /// Requires the local DTC to communicate with a remote DTC using only encrypted messages
        /// and mutual authentication. This setting is recommended for servers running Windows Server
        /// 2003 that are operating in a cluster.
        /// Only Windows Server 2003 and Windows XP SP2 support this feature, so you should only use
        /// this if you know that the DTC on the remote computer runs either the Windows Server 2003
        /// or Windows XP SP2 operating system.
        /// </summary>
        /// <value>
        /// 	<c>true</c> if [incoming caller authentication required]; otherwise, <c>false</c>.
        /// </value>
        public bool IncomingCallerAuthenticationRequired { get; set; }
        /// <summary>
        /// Provides system compatibility between previous versions of the Windows operating system.
        /// When enabled, communication on the network between DTCs can fall back to a non-authentication
        /// or non-encrypted communication if a secure communication channel cannot be established.
        /// This setting should be used if the DTC on the remote computer runs a Windows 2000 operating system
        /// or a Windows XP operating system earlier than SP2. This setting is also useful when the DTCs
        /// that are involved are located on computers that are in domains that do not have an established
        /// trust relationship or if the computers are part of a Windows workgroup.
        /// </summary>
        /// <value>
        /// 	<c>true</c> if [no authentication required]; otherwise, <c>false</c>.
        /// </value>
        public bool NoAuthenticationRequired { get; set; }

        /// <summary>
        /// Returns a <see cref="T:System.String"/> that represents the current <see cref="T:System.Object"/>.
        /// </summary>
        /// <returns>
        /// A <see cref="T:System.String"/> that represents the current <see cref="T:System.Object"/>.
        /// </returns>
        public override string ToString()
        {
            StringBuilder state = new StringBuilder();
            state.AppendFormat("NetworkDTCAccess: {0}rn", NetworkDTCAccess);
            state.AppendFormat("AllowInbound: {0}rn", AllowInbound);
            state.AppendFormat("AllowOutbound: {0}rn", AllowOutbound);
            state.AppendFormat("MutualAuthenticationRequired: {0}rn", MutualAuthenticationRequired);
            state.AppendFormat("IncomingCallerAuthenticationRequired: {0}rn", IncomingCallerAuthenticationRequired);
            state.AppendFormat("NoAuthenticationRequired: {0}rn", NoAuthenticationRequired);
            return state.ToString();
        }

    }

}

Happy coding!

How to make your own SSL test certificate for IIS or WCF

I had to create a test ssl certificate to use with my WCF service.

Here is the simple way to do it in 2 Easy Steps!

1) Open the Visual Studio 2008 Command Prompt

2) Fire off this command. change bseekford00111 to your computer name or whatever name you want the certificate to be.

makecert -r -pe -n "CN=bseekford00111" -b 01/01/2000 -e 01/01/2050 -eku 1.3.6.1.5.5.7.3.1 -ss my -sr CurrentUser -sky exchange -sp "Microsoft RSA SChannel Cryptographic Provider" -sy 12

That is all there is to it.
It will drop it in the current user MY store. If you want it under local machine store, change CurrentUser to LocalMachine

Copy one List to another List in one line of code using Linq and lambdas!

I find it pretty useful to try and incorporate time and code line saving features where it is beneficial to my productivity and the code readability. One thing I need to do often is convert lists from one type to another.

Here is a one liner to convert an object with a VendorID property on it into a List of integers.

var list = (List<VendorInvoice>)Context.Items[“VendorInvoices”];
List<int> intList = new List<int>();
list.ForEach(l => intList.Add(l.VendorInvoiceID));

See the Foreach processes each element of the List using the Lambda. The l variable (you can name it something more useful) is then fed to the function body which is simply an Add to the other list. More complex operations are easily accomplished using the same pattern.

Less code, easy to read.

Really cool and free screen shot clipping tool. How handy!

I imagine everyone writing on the web or developing software needs to do a screen shot every once in a while. I also don’t want to pay $30 for something I only use every once in a while. So I ran across this cool project from CodePlex.

It is called Cropper. http://cropper.codeplex.com/

You can give it a shot. It’s free and it works. It drops the area of the screen you pick to the disk or clipboard in the format of your choice.

Share the knowledge, as knowledge is freedom!

Enterprise Library Caching Block. Contains or to not Contains, that is the question!

I am working on a project where a few developers decided to use the Caching block in an interesting way.

The code I saw was basically:

if(cache.Contains[MyKey])
     return cache.GetData(MyKey);

fill cache code....

Contains….hmmm. Sounds innocent, right? You do it with a Dictionary.

This code will work, granted not optimally, but works OK if you always use never-expiring caching. i.e. the default Add. (Of course the scavenger could run between the Contain and GetData and you get screwed) If however you decide to actually use caching with AbsoluteTimeout or SlidingTimeout or other cache timeouts; you get false positives.

Why? Well, the Contains does exactly as you might expect, it see if the key is valid. What it doesn’t do though, is check to see if the cached value is actually expired. Since the scavenger runs only every XX seconds, it is possible your key was expired.

So you do the GetData call on the cache and you get back a null. Gee, why a null? Well, if the cache item was expired, which the GetData enforces before returning, then you get back a null.

So Brian, how should I use the the caching block’s cache manager?

Well, fellow reader, you should use the following. Amazingly simple, eh?

var myValue = cache.GetData(MyKey);
if(myValue != null)
     return myValue;

Do fill and add code here............

A lot easier than you expected, and it even works every time. Now, of course you will need to incorporate your own locking strategy around this if you are thread re-entrant.

And yes….GetData is the same as the indexer. cache[MyKey] = cache.GetData[MyKey] Use whatever way you prefer.

Happy Coding!

ATLRX.H missing, oh where have you gone? At least in Visual Studio 2008

I pulled out some old code to work on a legacy project of mine and lo and behold I get “Error 29 fatal error C1083: Cannot open include file: ‘atlrx.h’: No such file or directory”. Not exactly a pretty error and worse, this file was part of the standard ATL includes.

So what the heck happened to my good ole’ friend? Well, the geniuses at Microsoft decided it would be better to sit on CodePlex. So, you can download it from here.

The library is called the ATL Server Library. Just download it, and set your include path in Visual Studio to point to it. Or get creative and copy the files into an existing include path in the program files folder(under the visual studio folder of course.

Happy Coding!

The cost of a code freeze and maybe a better way of doing it.

The cost of a code freeze

So you’re thinking about doing a code freeze in your company, or maybe you already are. This article discusses the pros and cons and potential solutions to the common pitfalls.

A “code freeze” is general the period of time in which developers of a team based software development project are barred from the check-in of code into the source code control repository. Code freezes are used as a way of obtaining a clean version of code for preparation of a release. A lot of companies use these for internal build releases as well that are generally provided for the Quality Assurance department.

Is a code freeze a good thing? Well, maybe. Conceptually it sounds like a great idea; tell all developers to hold off on the check-in of code for a set period of time while a build is prepared off of the active development branch of the source control system. Proponents of the standard code freeze argue that the lock down period of time is necessary in order to properly create a build that is functional. The issue that arises is really around the length of time the code freeze is truly in effect. If the standard build and assurance process lasts a few hours, the impact is relatively negligible. If in turn the process takes a day or longer, then the impact to the development cycle is truly felt.

The impact of the denial of code checkins on an active project is dependent on a number of factors; the size of the team, the lack of source code isolation of the distributed tasks, and the velocity of the tasks.  These variables interact in a non-definitive but potentially exponential way. The higher any of the values are, the more the value of the other variables affects the cost. For example, the more developers there are on a project the greater the impact of higher velocity and lack of source code isolation on the tasks.

Say you have X developers, they work on code with an isolation level that has a probability of interaction of Y (likelihood a developer is working on a section of code another developer is also working on), and they are working on tasks at a velocity of Z. Let us also assume the code freeze occurs for H hours. The variable interaction would be similar to H * ((X2 *  Z) * Y. While the math on this particular equation is very loose and up for debate, the potential impact is obvious. We can determine the true impact if we were to take on an actual experimental situation (or really put a lot more thought to it than I did) to find the actual impact.

What the above is basically showing is that the cost of the code freeze increases the longer it lasts and is dramatically more when a larger number of potential interactions (developer activities) are introduced.

What can be done about this?

The code freeze employed by some companies is the hold all code and wait until a valid build is created from the development/active branch of the source control system. The implied benefit is that developers can check in code ONLY to fix the actual build process and get the initial assurance verification tests to complete successfully. The cost of this particular method is generally not worth its benefit.

An alternative to this particular cycle is to use a form of the Branch by Purpose pattern (“The Importance of Branching Models in SCM.” IEEE Computing Practices. 0018-9162/02.) recommended by Walrad and Strom which dictates that you create source branches when the code in the branch will be used for a particular purpose; a bit self explanatory in nature. This method allows you to have a code freeze for only the period of time it takes to snapshot the branch. You can then build your releases of the branch. The developers will encounter minimal impact due to the brevity of the code freeze and defects can easily be fixed in the secondary branch and merged back into the main branch if necessary.

The utilization of my interpretation of this pattern is quite simple. The source control is created with a main/active development folder to house the current iteration of source code. You then create a quality assurance branch which you use to send copies of the main/active branch when you need to create builds to send off to the quality assurance team. This format allows the development team to continue on with work while the build team uses the QA branch to produce the QA builds. Any code changes to fix the process would be made in this branch and merged back to the main branch.

Using a LINQ compiled query to determine if a record exists with the Entity Framework

I am writing an application that requires high performance on the server layer. The method I am calling will be invoked a large number of times, so I don’t want the overhead of the LINQ compile every time I invoke it.

Yes, the compile is very expensive. It is especially so when called a large number of times in a small timeframe. So, I decided to write a compiled query. It is really quite scary looking at first, but don’t worry. It does get easier once its dissected.

Let’s start by looking at my compiled query.
//precompiling my query to reduce the overhead. grab one field from db, and only first. Should be fastest.
private static readonly Func<MyDataContext,Guid,bool> _CompiledRecordExists =
CompiledQuery.Compile<MyDataContext,Guid,bool>(
(context,myTableID) => ((from record in context.MyTable
where record.myTableId == myTableID
select record.MyTableId
).FirstOrDefault() != null)
);

So I have  a compiled query called _CompiledRecordExists. This query is compiled on when the class static are all instantiated.  We are creating a query which takes in a data context from the Entity Framework, a Guid for the records primary key, and a return value of a bool.

You will notice the Func template/generic which allows us to do this. The last parameter, you can have up to 6 parameters, is the return value of the function.

My linq query is then created in my lambda function. You can see I use the variables context and myTableID. You can name these whatever you want, the order is what is important. The first one is the first type you used in the Func declaration. The second one is the second, so on and so on. Notice there is no bool. That is because its our return value.

In order to actually use our compiled function in code, we need to simply call it. It is really simple now.

using (var context = factory.CreateContext())
            {
                return _CompiledRecordExists (context, recordID);
            }

Notice that we can call it just like a function. Now you won’t have any of the overhead of the sql query generation. The hit was taken up front.

Try running a tight loop on your linq queries and the compiled queries and let me know how it works for you.

Happy Coding!