DataHelper.ExecuteDataset for different database providers

I have a small utility .Net that was used to transform data from one database to another. Initially it was written using SQLHelper DAAB class. Then it was extended to work with OledbHelper , that was created based on SqlHelper. Now it was requested to work with ODBC provider as well.
I know, that I can use Enterprise library DAAB(e.g see here) , but I decided not  involve additional DLLs and extra settings in config file for this simple application.

The static finction to return dataset for specified SQL and nominated provider is below:


        public const string OleDBProviderInvariantName=“System.Data.OleDb”;


        public const string OdbcProviderInvariantName=“System.Data.Odbc”;


        public const string SQLProviderInvariantName = “System.Data.SqlClient”;


  


     //COnsider to use DAAB in Enterprize Library instead


        //Provider name is case sensitive, such as “System.Data.OleDb”,”System.Data.Odbc”,”System.Data.SqlClient”;


        public static DataSet ExecuteDataset(string ProviderInvariantName, string sConnString, string sSQL)


        {


            DebugHelper.TracedLine(“sConnString=” + sConnString);


            DbProviderFactory fact = DbProviderFactories.GetFactory(ProviderInvariantName);


            DbConnection conn = fact.CreateConnection();


            conn.ConnectionString = sConnString;


             DebugHelper.TracedLine(“(sSQL=” + sSQL);


            DbCommand cmdSelect = conn.CreateCommand();


            cmdSelect.CommandText = sSQL;


            DbDataAdapter da = fact.CreateDataAdapter();


            da.SelectCommand = cmdSelect;


            DataSet ds = new DataSet();


            try


            {


                da.Fill(ds);//Table


            }


            catch (Exception exc) //MNF 18/5/2005


            {


                throw new FSCSharpLib.ExceptionWithDetails(exc.Message, ” sSQL = “ + sSQL, exc);


            }


            return ds;


        }




 

CQL to RPN mapping file pqf.properties changes to support different servers

I have a z39.50 client, that uses CQL language and YAZ to communicate to Z39.50 server. Yaz distribution includes  etc/pqf.properties CQL to RPN mapping file, that I’ve used satisfactory without changes.
However one of the servers, that I wanted  to search, returned 0 to any query in CQL.
Some debugging showed that the server  doesn’t support BIB-1 COMPLETENESS ATTRIBUTES  (TYPE = 6) and any query, that included @attr 6, retuned 0. Additionally they didn’t understand support attribute 1=1016 (any).
I had to modify a few lines in
pqf.properties to work with the server.

# MNF 16/3/2007 replaced serverChoice,
index.cql.serverChoice                          = 2=3


# index.cql.serverChoice                                   = 1=1016


# MNF 16/3/2007 excluded 6=1
# position.any    = 3=3 6=1
position.any    = 3=3


# MNF 16/3/2007 excluded always
# always     = 6=1
# 6=1: completeness = incomplete subfield


The Z39.50 server  is based on the TeraText Database System, that, as I’ve been informed, doesn’t support bib-1 completeness.



In ASP.Net Application_Error avoid call to web service on the same web application.

In my ASP.NET application I had global event handler Application_Error, that called Web Service(via WS client proxy class) to log the error.
The actual web service ASMX file belongs to the same application.
I knew that it is better to call method directly, without extra layers, but function to call   Web Service were located in Business Layer DLL, that also was used by Desktop Client, so I  considered that it is acceptable overhead.


However, when an error occured on an early stage, when ASP.NET application is just started, this approach causes indefinite loop.
When the serious error occured, ASP.NET tries to report it and restart application. When during logging  Application_Error handler calls Web Service, it’s considered as a new request to the application, it restarts, failed, calls to web service to log the error, and so on.
My error logging (based on User Friendly ASP.NET Exception Handling article) included sending e-mail alerts to administrator, and hundreds of e-mails were send over a minute -like Denial Of Service attack.



So I had to refactor my application avoid call to web service in Application_Error handler. 


 

Possible errors when using Web Setup project to upgrade version, that was XCopied

I am using Web Setup project to install Web Site Project(customized version of DotNetNuke). It installs a lot of DLL with meaningless names(like App_Web_42q_drww.dll) into BIN directory.
If user upgrades later to the new version of the project MSI installer seems smart enough to delete old DLLs and install another set of DLLs with different unfriendly names.

The problem happens if site to upgrade wasn’t installed using Web Setup MSI, but was XCopied.The sample scenario is the following:
An administrator  installed web Application into virtual directory MyWebApp1. Then the administrator  made a copy of application MyWebApp1Copy.
On teh time of upgrade he/she decided to apply upgrade directly to MyWebApp1Copy virtual directory. In this case old set of DLLs in BIN is not deleted, but both old and new co-exist. It causes different errors like


Compiler Error Message: BC30456: ‘InitializeCulture’ is not a member of ‘ASP.default_aspx’.

The simplest way to workaround the mess is to rename the web apllication folder and install your application as new.


Related thread : BC30456: ‘InitializeCulture’ is not a member of…

Invoke Executable as custom action during Install.

I’ve used MS Installer class to provide custom  actions during setup in a few projects(e.g.see Using VS 2005 Web Setup Project with custom actions).
However if you have some complex logic to do as a part of setup, it is possible that some exception will occur.
I don’t like to hide exceptions, and they are shown to the user,causing installation to rollback.
It is not good, becuse sometimes user wants to ignore exception and use installation even if some custom action failed.
I found that moving code from custom action to executable gives more flexibility,as well as allow to use the same program to customize installation later just by running the same exe.
The Visual Studio SetupProject allowes to specify Custom Action as executable,not as Installer Class.
However during Uninstall,Repair etc, Executable Custom Action can be invoke not as programmer initially designed,and it is required to specify conditions for custom actions. Unfortunately, I had a lot of problems trying to set correct conditions(see Custom action condition to run for a new or upgraded product.and Intermittent execution of custom action for installation of product upgrade. )

See also Integration Hurdles for EXE Custom Actions post from Windows Installer Team Blog,

 To avoid these problems with  Executable Custom Action I desided to return back to Installer Class Custom action, but in Install method the only task is to start  executable and executable will do all required customization of installation.

The calling executable should ensure that it is running in foreground and retrieve parameters from CommandLineArgs

        ‘/ensure that it is foregrownd

        Win32Helper.ShowWindow(Me.Handle, Win32Helper.WindowShowStyle.Show)

        Win32Helper.SetForegroundWindow(Me.Handle)

        Win32Helper.BringWindowToTop(Me.Handle) ‘//13/11/2006

 

        Dim argsColl As New Arguments(Environment.GetCommandLineArgs())

 

 

The class StartProcessInstaller is shown below:

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Configuration.Install;

using System.Diagnostics;

using System.Text;

using System.Collections.Specialized;

using System.IO;

using System.Collections;

using System.Runtime.InteropServices;

 

namespace InstallStartCustomAction

{

 

    [RunInstaller(true)]

    public partial class StartProcessInstaller : Installer

    {

 

        public StartProcessInstaller()

        {

            InitializeComponent();

        }

        public override void Install(IDictionary mySavedState)

        {

//            Trace.WriteLine(“Install started “);

            try

            {

                base.Install(mySavedState);

            //    Debug.Assert(false);

       // Assume that the current assembly located at the same folder as CFSDNNInstall.exe.

                FileInfo fileInfo = new FileInfo(System.Reflection.Assembly.GetExecutingAssembly().Location);

                string sProgram = Context.Parameters[“Run”]; //”FSDNNInstall.exe”

                //    string sDir = fileInfo.DirectoryName() + “\”;

                 sProgram = Path.Combine(fileInfo.DirectoryName, sProgram);

                Trace.WriteLine(“Install sProgram= “ + sProgram);

                OpenWithStartInfo(sProgram);

            }

            catch (Exception exc)

            {

                Context.LogMessage(exc.ToString());

                throw;

            }

 

        }

        /// <summary>

        /// Uses the ProcessStartInfo class to start new processes

        /// </summary>

        void OpenWithStartInfo(string sProgram)

        {

  //          Trace.WriteLine(“OpenWithStartInfo sProgram= ” + sProgram);

            ProcessStartInfo startInfo = new ProcessStartInfo(sProgram);

            startInfo.WindowStyle = ProcessWindowStyle.Normal ;

            string[] ExcludeKeys = new string[] { “run”, “WaitForExit” };

            startInfo.Arguments = ContextParametersToCommandArguments(Context, ExcludeKeys);

            Trace.WriteLine(“run the program “ + sProgram + startInfo.Arguments);

            Process p = Process.Start(startInfo);

            //TODO it seems that I should wait until it will be fully opened http://www.xtremevbtalk.com/archive/index.php/t-230805.html

            //e.g. p.WaitForInputIdle(30) Method

            //or try BringWindowToTop

            //I have to use unmanaged ShowWindow  http://www.thescripts.com/forum/thread230693.html

            ShowWindow(p.MainWindowHandle, WindowShowStyle.Show); //otherwise it is not activated

            SetForegroundWindow(p.MainWindowHandle);

            BringWindowToTop(p.MainWindowHandle); //13/11/2006

            //p.Refresh();

            Trace.WriteLine(“the program Responding= “ + p.Responding);

            if((Context.IsParameterTrue(“WaitForExit”)) )

            {

                p.WaitForExit();//Does it required?

            }

        }

        //TODO copy to FSCSharpLib

        public static String ContextParametersToCommandArguments(InstallContext context, string[] ExcludeKeys)

        {

            ExcludeKeys = ToLower(ExcludeKeys);

            StringBuilder sb = new StringBuilder();

            foreach (DictionaryEntry de in context.Parameters)

            {

                string sKey = (string)de.Key;

                bool bAdd = true;

                if (ExcludeKeys != null)

                {

                    bAdd = (Array.IndexOf(ExcludeKeys, sKey.ToLower()) < 0);

                }

                if (bAdd)

                {

                    AppendArgument(sb, sKey, (string)de.Value);

                }

            }

            return sb.ToString();

        }

        //TODO copy to FSCSharpLib

        public static StringBuilder AppendArgument(StringBuilder sb, String Key, string value)

        {

            sb.Append(” /”);

            sb.Append(Key);

            //Note that if value is empty string, = sign is expected, e.g.”/PORT=”

            if(value!=null)

            {

                sb.Append(“=”);

                sb.Append(value);

            }

            return sb;

        }

        #region  “FS library methods”

        public static string[] ToLower(string[] Strings)

        {

            if (Strings != null)

            {

                for (int i = 0; i < Strings.Length; i++)

                {

                    Strings[i] = Strings[i].ToLower();

                }

            }

            return Strings;

        }

        #endregion  //”FS library methods”

        #region  “showWindow”

 

     // http://pinvoke.net/default.aspx/user32.BringWindowToTop

        [DllImport(“user32.dll”)]

        static extern bool BringWindowToTop(IntPtr hWnd);

       

        [DllImport(“user32.dll”)]

        [return: MarshalAs(UnmanagedType.Bool)]

        private static extern bool SetForegroundWindow(IntPtr hWnd);

       

        //from http://pinvoke.net/default.aspx/user32.SwitchToThisWindow

        [DllImport(“user32.dll”)]

        private static extern bool ShowWindow(IntPtr hWnd, WindowShowStyle nCmdShow);

 

        /// <summary>Enumeration of the different ways of showing a window using

        /// ShowWindow</summary>

        private enum WindowShowStyle : uint

        {

            /// <summary>Hides the window and activates another window.</summary>

            /// <remarks>See SW_HIDE</remarks>

            Hide = 0,

            /// <summary>Activates and displays a window. If the window is minimized

            /// or maximized, the system restores it to its original size and

            /// position. An application should specify this flag when displaying

            /// the window for the first time.</summary>

            /// <remarks>See SW_SHOWNORMAL</remarks>

            ShowNormal = 1,

            /// <summary>Activates the window and displays it as a minimized window.</summary>

            /// <remarks>See SW_SHOWMINIMIZED</remarks>

            ShowMinimized = 2,

            /// <summary>Activates the window and displays it as a maximized window.</summary>

            /// <remarks>See SW_SHOWMAXIMIZED</remarks>

            ShowMaximized = 3,

            /// <summary>Maximizes the specified window.</summary>

            /// <remarks>See SW_MAXIMIZE</remarks>

            Maximize = 3,

            /// <summary>Displays a window in its most recent size and position.

            /// This value is similar to “ShowNormal”, except the window is not

            /// actived.</summary>

            /// <remarks>See SW_SHOWNOACTIVATE</remarks>

            ShowNormalNoActivate = 4,

            /// <summary>Activates the window and displays it in its current size

            /// and position.</summary>

            /// <remarks>See SW_SHOW</remarks>

            Show = 5,

            /// <summary>Minimizes the specified window and activates the next

            /// top-level window in the Z order.</summary>

            /// <remarks>See SW_MINIMIZE</remarks>

            Minimize = 6,

            /// <summary>Displays the window as a minimized window. This value is

            /// similar to “ShowMinimized”, except the window is not activated.</summary>

            /// <remarks>See SW_SHOWMINNOACTIVE</remarks>

            ShowMinNoActivate = 7,

            /// <summary>Displays the window in its current size and position. This

            /// value is similar to “Show”, except the window is not activated.</summary>

            /// <remarks>See SW_SHOWNA</remarks>

            ShowNoActivate = 8,

            /// <summary>Activates and displays the window. If the window is

            /// minimized or maximized, the system restores it to its original size

            /// and position. An application should specify this flag when restoring

            /// a minimized window.</summary>

            /// <remarks>See SW_RESTORE</remarks>

            Restore = 9,

            /// <summary>Sets the show state based on the SW_ value specified in the

            /// STARTUPINFO structure passed to the CreateProcess function by the

            /// program that started the application.</summary>

            /// <remarks>See SW_SHOWDEFAULT</remarks>

            ShowDefault = 10,

            /// <summary>Windows 2000/XP: Minimizes a window, even if the thread

            /// that owns the window is hung. This flag should only be used when

            /// minimizing windows from a different thread.</summary>

            /// <remarks>See SW_FORCEMINIMIZE</remarks>

            ForceMinimized = 11

        }

        #endregion  //”showWindow”

    }

}

 

 

Explicitely specify Default user credentials for Web Proxy Server.

Some time ago I wrote post “Call Web Services through SQuid proxy server with authentication requested”  and hoped, that I will not have more problems with Squid web proxy server.
However teh solution wasn’t sufficient for other site. I’ve tried a lot of things, started thread “Unable to access external web sites through Squid proxy server with required authentication.” and posted suggestion to Microsoft “Add ability to specify credentials to defaultProxy configuration element“.

But the best solution, that I found for the moment is  to create common function to set web proxy credentials and call it  for each Web Services call,HttpWebRequest call and replace  dataset.ReadXml(url)  with HttpWebRequest.GetRequest.
The function created expects up to 4 properties in appSettings section of Web.config
        <!–Web Proxy Credentials to use if useDefaultCredentials=”true” doesn’t help

    TODO move to separate ConfigurationSection, different from AppSettings

    <add key=”ProxyUserName” value=””/>

    <add key=”ProxyUserPassword” value=””/>

    <add key=”ProxyAuthenticationType” value=”Basic”/>

    <add key=”ProxyUserDomain” value=””/>

    –>
and to be called like the following for web services


 

        AWSECommerceService aws = new AWSECommerceService();

         SetDefaultWebProxyCredentialsIfRequired(aws);

 

or for dataset.ReadXml

 

        IWebProxy proxyObject = CreateDefaultWebProxyWithCredentialsIfRequired(url);//WSClientHelper.

        if (proxyObject == null)

            ds.ReadXml(url);

        else

        {

            Stream strm = GetResponseStream(url, proxyObject);//HttpWebRequestHelper.

            ds.ReadXml(strm);

        }

 

The code of the SetDefaultWebProxyCredentialsIfRequired and other helper methods is the following:

 

    public static bool SetDefaultWebProxyCredentialsIfRequired(SoapHttpClientProtocol ws)

    {

        IWebProxy proxyObject = CreateDefaultWebProxyWithCredentialsIfRequired(ws.Url);

        //bool bRet =SetDefaultWebProxyCredentialsIfRequired(ws, ProxyUserName, ProxyUserPassword, sAuthType, ProxyUserDomain);

        bool bRet = SetDefaultWebProxyCredentials(ws, proxyObject);

        return bRet;

    }

    /// <summary>

    ///

    /// </summary>

    /// <param name=”ws”></param>

    /// <param name=”proxyObject”>if null, no proxy is set</param>

    /// <returns></returns>

    public static bool SetDefaultWebProxyCredentials(SoapHttpClientProtocol ws, IWebProxy proxyObject)

    {

        bool bRet = false;

        if (null == proxyObject) return false;

            ws.Proxy = proxyObject;

            ws.PreAuthenticate = true;//from http://www.awprofessional.com/articles/article.asp?p=27574&seqNum=3

            ws.AllowAutoRedirect = true;

            bRet = true;

            //ws.Credentials = cache;

        return bRet;

    }//SetDefaultWebProxyCredentialsIfRequired

    /// <summary>

    /// If configuration parameters for ProxyUserName and ProxyUserPassword are specified, returns IWebProxy object, otherwise returns null

    /// </summary>

    /// <param name=”sUrl”></param>

    /// <returns></returns>

    public static IWebProxy CreateDefaultWebProxyWithCredentialsIfRequired(String sUrl)

    {

        //TODO move to separate ConfigurationSection, different from AppSettings

        string ProxyUserName = ConfigurationManager.AppSettings[“ProxyUserName”];

        string ProxyUserPassword = ConfigurationManager.AppSettings[“ProxyUserPassword”];

        string sAuthType = ConfigurationManager.AppSettings[“ProxyAuthenticationType”];

        string ProxyUserDomain = ConfigurationManager.AppSettings[“ProxyUserDomain”];

        IWebProxy proxyObject = CreateWebProxyWithCredentials(sUrl, ProxyUserName, ProxyUserPassword, sAuthType, ProxyUserDomain);

        return proxyObject;

    }

    /// <summary>

    ///

    /// </summary>

    /// <param name=”sUrl”></param>

    /// <param name=”ProxyUserName”>if parameter is null, the function returns null</param>

    /// <param name=”ProxyUserPassword”>if parameter is null, the function returns null</param>

    /// <param name=”sAuthType”>defaults to “basic”</param>

    /// <param name=”ProxyUserDomain”>ignored for “basic” authentication type</param>

    /// <returns></returns>

    public static IWebProxy CreateWebProxyWithCredentials(String sUrl, string ProxyUserName, string ProxyUserPassword, string sAuthType, string ProxyUserDomain)

    {

        if (String.IsNullOrEmpty(ProxyUserName) || String.IsNullOrEmpty(ProxyUserPassword))

        {

            return null;

        }

        // get default proxy and assign it to the WebService. Alternatively, you can replace this with manual WebProxy creation.

        IWebProxy iDefaultWebProxy = WebRequest.DefaultWebProxy;

        Uri uriProxy = iDefaultWebProxy.GetProxy(new Uri(sUrl));

        string sProxyUrl = uriProxy.AbsoluteUri;

        if (sProxyUrl == sUrl)

        {//no proxy specified

            return null;

        }

        IWebProxy proxyObject = new WebProxy(sProxyUrl, true);

        // assign the credentials to the Proxy

        //todo do we need to add credentials to  WebService too??

        if ((!String.IsNullOrEmpty(sAuthType)) && (sAuthType.ToLower() != “basic”))

        {

            //from http://www.mcse.ms/archive105-2004-10-1165271.html

            // create credentials cache – it will hold both, the WebProxy credentials (??and the WebService credentials too??)

            System.Net.CredentialCache cache = new System.Net.CredentialCache();

            // add default credentials for Proxy (notice the authType = ‘Kerberos’ !) Other types are ‘Basic’, ‘Digest’, ‘Negotiate’, ‘NTLM’

            cache.Add(new Uri(sProxyUrl), sAuthType, new System.Net.NetworkCredential(ProxyUserName, ProxyUserPassword, ProxyUserDomain));

            proxyObject.Credentials = cache;

        }

        else//special case for Basic (from http://www.xmlwebservices.cc/index_FAQ.htm )

        {

            proxyObject.Credentials = new System.Net.NetworkCredential(ProxyUserName, ProxyUserPassword);

        }

        return proxyObject;

    }

    //Default methos is Get, do not store cookies

    public static Stream GetResponseStream(string sUrl, IWebProxy proxy)

    {// string Method, CookieContainer cntnrCookies, string sFileToSaveHtml

        //            HttpWebRequest webRequest = HttpWebRequestHelper.PrepareWebRequest(sUrl, Method, cntnrCookies);

        HttpWebRequest webRequest = WebRequest.Create(sUrl) as HttpWebRequest;

        //  webRequest.ContentType = “application/x-www-form-urlencoded”;//is good?

        webRequest.Proxy = proxy;

        //you can  change some properties if required – from http://www.codeproject.com/csharp/ClientTicket_MSNP9.asp         

        //MNF 26/5/2005 Some web servers expect UserAgent to be specified,so let’s say it’s IE6

        webRequest.UserAgent = “Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0; .NET CLR 1.1.4322)”;

       // DebugHelper.PrintHttpWebRequest(webRequest, TraceHelper.LineWithTrace(“”));

        HttpWebResponse resp = webRequest.GetResponse() as HttpWebResponse;

        Stream strm = resp.GetResponseStream();

        return strm;//’HttpWebRequestHelper.GetResponseString(resp, sFileToSaveHtml);

    }

 

 


Notes about Visual Studio solution structure.

I’ve researched recommendations about best structure of solutions in Visual Studio, when you have multple projects.

I found the best article “SSW Rules to Better Large Builds in Visual Studio.NET“.
See also:
Structuring Solutions and Projects and Managing Dependencies from Microsoft patterns & practices Team Development with Visual Studio® .NET and Visual SourceSafe guide.

Project structure best practices” from  searchwebservices.techtarget.com.
Use a Consistent Folder Structure for Solutions and Projects from “Software Configuration and Management Using Visual SourceSafe and VS .NET” article in code-magazine.com

Structuring Solutions in Visual Studio and Team Foundation  suggest to have separate solutions for each deployable target.

Explicitely specify Default user credentials for Proxy Server.

Some time ago I wrote post “Call Web Services through SQuid proxy server with authentication requested”  and hoped, that I will not have more problems with Squid web proxy server.
However the solution wasn’t sufficient for other site. I’ve tried a lot of things, started thread “Unable to access external web sites through Squid proxy server with required authentication.” and posted suggestion to Microsoft “Add ability to specify credentials to defaultProxy configuration element“.


But the best solution, that I found for the moment is  to create common function to set web proxy credentials and call it  for each Web Services call,HttpWebRequest call and replace  dataset.ReadXml(url)  with HttpWebRequest.GetRequest.
The function created expects up to 4 properties in appSettings section of Web.config
        <!–Web Proxy Credentials to use if useDefaultCredentials=”true” doesn’t help


    TODO move to separate ConfigurationSection, different from AppSettings


    <add key=”ProxyUserName” value=””/>


    <add key=”ProxyUserPassword” value=””/>


    <add key=”ProxyAuthenticationType” value=”Basic”/>


    <add key=”ProxyUserDomain” value=””/>


    –>
and to be called like the following for web services




        AWSECommerceService aws = new AWSECommerceService();


         SetDefaultWebProxyCredentialsIfRequired(aws);


 


or for dataset.ReadXml


 


        IWebProxy proxyObject = CreateDefaultWebProxyWithCredentialsIfRequired(url);//WSClientHelper.


        if (proxyObject == null)


            ds.ReadXml(url);


        else


        {


            Stream strm = GetResponseStream(url, proxyObject);//HttpWebRequestHelper.


            ds.ReadXml(strm);


        }



The code of the SetDefaultWebProxyCredentialsIfRequired and other helper methods can be found here.


 

Web Browser control in MS Access form craches when Internet Explorer 7 is installed.

We have MS Access XP application that has form with embedded Web Browser control, that is used to show links.
The application was build on development machine with IE 6 installed.


  It was found that if  Internet Explorer 7 is installed on a client machine, attempt to open URL in embedded Web Browser control causes Access to crach with C0000005 error.
We came out with the simplest solution( may be not acceptable for others) to completely exclude  Access form with embedded control and  open links in the full browser, installed on client.

Search Access database source code

If you have MS Access database(or project ADP) and it is used SourceSafe for version control,

the simplest way to search VBA source code is to use Visual Studio “Search in Files” function pointed to <path>YourDatabase.scc folder.


Note that it will also search form and reports properties and controls declarations, which can be quite handy.
I am using it with Access XP.
May be, latest virsions of Access have integrated search, similar to VS.