Generate SQL script with schema and data for a database

When we need to quickly send an app which is under development for testing to someone and don’t need to worry which version of SQL server he may have, we should send SQL script for entire database instead of sending database backup file (because if SQL server version will not match, it won’t be restored, which happens often).

To do this easily there is a hidden tool which comes when you install any version of SQL server (developer, enterprise or express). It’s called Database Publishing Wizard, which lets you generate SQL script for your entire database with schema and data. The receiver of this SQL script file just needs to create an empty database and run the script.

Location of the wizard is normally here (in case of SQL server 2008 Express):

C:\Program Files\Microsoft SQL Server\90\Tools\Publishing\1.4\SqlPubWiz.exe

Hope this helps.

 

Resolved: “Access denied” error on System.Diagnostics.Process.Start

Wrote a piece of code which would launch an application exe using Process.Start, on development machine it worked without a problem but when deployed on client machine it started giving “Access Denied” error.

It turned out that when the code runs in domain controlled user environement Process.Start needs a set of security credentials by default to run. Now, in practical scenario we can not just hard code any credentials or even prompt for input.

The solution, there is an attribute called UseShellExecute which needs to be set False , this will allow the exe to be launched without credentials.

Sample code goes like this :

System.Diagnostics.Process p = new System.Diagnostics.Process();
p.StartInfo.FileName = _path;
p.StartInfo.Arguments = string.Format(“\”{0}\” \”{1}\””, arg1, arg2);
p.StartInfo.UseShellExecute = false;
p.Start();
p.WaitForExit();

Running 32 bit applications on 64 bit OS

Lately I ran into a problem where I created a feature where user can view PDF within a winform application. To do that I first used a web browser control since it was a simple solution where you just need to provide pdf path to the navigate property. That works without problem on Win XP (32bit) , but as soon as user runs it on his Vista (64 bit) OS  , it behaved different , that said, pdf opens but in a separate window, which looks bit odd, so must be fixed.

To solve that problem , I have removed web browser control and placed Acrobat Reader control (assuming that all of my users will have at least Adobe reader installed). Now that again worked well on Win XP but simply crashed on Vista (64 bit).  Reason was Acrobat reader control is an ActiveX control which is 32 bit component , that won’t work on a 64 bit OS. After looking around for some time and find out there is no 64 bit version of Acrobat reader, I discovered that I have to make sure that my win form app runs under 32 bit process on a 64 bit OS. To, do that you have to change Target Platform property of your project (See screenshot below).

Bottom line is if you have any project dependencies which only works on 32 bit environment, you have to change your project’s target platform to run only under 32 bit process. That will make your app work fine on both 32 bit and 64 bit OSes.

Solution: Problem of focus when opening a Winform using Treeview node click

I must blog this , since I could not find the fix as quickly as I should. So…
When you try to open a winform using treeview node selection (using any event like mouse_click on after_select) , the form opens, but it remains minimized even if you have set the focus and windowstate properties.  That is because treeview regains control after opening form, however there is no direct fix.  You can fix it by delaying the action of the event.

        private void treeView1_AfterSelect(object sender, TreeViewEventArgs e)
        {
            this.BeginInvoke(new TreeViewEventHandler(delayedClick), sender, e);
        }

        private void delayedClick(object sender, TreeViewEventArgs e)
        {
             // your code goes here…
        }

Original solution here.

MS SQL: Using dynamic sql in Cursor

Here is sample code for using dynamic sql in cursor , you have to set two parameters @tablename and @fieldname and it will build a select query for the given field and table.

declare @sql nvarchar(1000)
declare @tablename nvarchar(50)
declare @fieldname nvarchar(50)

set @tablename = ‘MyTableName’
set @fieldname = ‘MyFieldName’

select @sql = N’set @setcodeid =  cursor for select ‘ + @fieldname + N’ from ‘ + @tablename + N’; OPEN @setcodeid’;

declare @codeid varchar(100)
declare @setcodeid cursor
exec sp_executesql @sql,N’@setcodeid cursor OUTPUT’,@setcodeid OUTPUT

fetch next
    from @setcodeid into @codeid
        while @@FETCH_STATUS = 0
            begin
                — do you stuff here…

                fetch next from @setcodeid into @codeid
            end
close @setcodeid
deallocate @setcodeid

MS SQL: Alternative way to get the table’s row count

A very effective way if you just want to get total row count of table. Check full article here.

To get the total row count in a table, we usually use the following select statement:

SELECT count(*) FROM table_name

This query performs full table scan to get the row count. You can check it by setting SET SHOWPLAN ON for SQL Server 6.5 or SET SHOWPLAN_TEXT ON for SQL Server 7.0/2000. So, if the table is very big, it can take a lot of time

There is another way to determine the total row count in a table. You can use thesysindexes system table for this purpose. There is ROWS column in the sysindexes table. This column contains the total row count for each table in your database. So, you can use the following select statement instead of above one:

SELECT rows FROM sysindexes WHERE id = OBJECT_ID(‘table_name’) AND indid < 2

There are physical read and logical read operations. A logical read occurs if the page is currently in the cache. If the page is not currently in the cache, a physical read is performed to read the page into the cache. To see how many logical or physical read operations were made, you can use SET STATISTICS IO ON command

A Basic SQLite DAL class in C#

 

You have to first download ADO.Net provider for SQLite from here and use it’s reference in your project.

The class has enough functions for basic DAL operations but you are free to enhance it as per your need.

public static class SqliteDal
{
    public static string cnstr = "Data Source =" + "yourdb.db3";

    /// <summary>
    /// Returns datatbale for given sql query.
    /// </summary>
    /// <param name="sql"></param>
    /// <returns></returns>
    public static DataTable getData(string sql)
    {
        SQLiteConnection cn = new SQLiteConnection(cnstr);
        cn.Open();

        try
        {
            SQLiteCommand cm = new SQLiteCommand(sql, cn);
            SQLiteDataReader dr = cm.ExecuteReader();
            DataTable dt = new DataTable();
            dt.Load(dr);
            cn.Close();

            return dt;
        }
        catch (Exception ex)
        {
            cn.Close();
            throw ex;
        }
    }

    /// <summary>
    /// Returns count of executed insert, update, delete statement.
    /// </summary>
    /// <param name="sql"></param>
    /// <returns></returns>
    public static int execNQ(string sql)
    {
        SQLiteConnection cn = new SQLiteConnection(cnstr);
        cn.Open();
        SQLiteCommand cm = new SQLiteCommand(sql, cn);
        int rows;
        rows = cm.ExecuteNonQuery();
        cn.Close();
        return rows;
    }

    /// <summary>
    /// Returns scalar for given sql query.
    /// </summary>
    /// <param name="sql"></param>
    /// <returns></returns>
    public static object execSC(string sql)
    {
        SQLiteConnection cn = new SQLiteConnection(cnstr);
        cn.Open();
        SQLiteCommand cm = new SQLiteCommand(sql, cn);
        object rows;
        rows = cm.ExecuteScalar();
        cn.Close();
        if (rows == null)
        {
            rows = 0;
        }
        return rows;
    }

}

SQLite Administrator: Great SQLite Windows client

I have tried out few commercial and freeware SQLite client apps but none is better than SQLite Administrator, It’s simple and easy to use and all the core feature works flawlessly. I have used it couple of times before and this time I had simply forgotten the name and looked all over my PC to find out this program but since this is not an installable software (directly launched from exe) it’s difficult to locate because when you need it you simple download it and launch it from there and forget it thereafter. But this time I decided to write this post to make it searchable for me and of course others 😉

Convert scanned pdf into image in C#

I needed to convert scanned pdf into any image format so that I can run OCR operation for one of the DMS solutions I am working on. Actually I am using Tesseract (Open source OCR solution from Google) for OCR and it only takes image format as input. After spending few hours searching and trying few solution I zeroed in on this codeproject article which is making use of Ghostscript to achieve the core functionality.
So far this is the only solution I think works seamlessly on most windows platforms including Win7 (already tested on XP, Win2003, Win7).

C#: Accessing resources on network

Accessing files and other resources on network from within code is achieved in following way in C# (of course there are other ways but this seems neat):

Take following assembly reference first:

using System.Runtime.InteropServices;

[DllImport(“advapi32.dll”, SetLastError = true, CharSet = CharSet.Unicode)]
public static extern bool LogonUser(String lpszUsername, String lpszDomain, String lpszPassword, int dwLogonType, int dwLogonProvider, ref IntPtr phToken);

Private void YourFunction()

{

WindowsImpersonationContext wic;
IntPtr tokenHandle = new IntPtr(0);
bool returnValue = LogonUser(f.cUserName, f.cDomain, f.cPassword, 2, 0, ref tokenHandle);
WindowsIdentity ImpersonatedIdentity = new WindowsIdentity(tokenHandle);
WindowsImpersonationContext MyImpersonation = ImpersonatedIdentity.Impersonate();

wic = MyImpersonation;

// do some file IO activity here on server…

wic.Undo();

}