Sandip's Programming Zen

An attempt to share tech/coding experiences

Generate SQL script with schema and data for a database

leave a comment »

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.

 

Written by Sandip

December 17, 2011 at 2:30 pm

Posted in MS SQL

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

with 3 comments

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();

	

Written by Sandip

August 6, 2011 at 5:03 pm

Posted in .Net, Programming, Softwares

Running 32 bit applications on 64 bit OS

with one comment

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.

target_platform

Written by Sandip

January 9, 2011 at 10:44 pm

Posted in .Net, Programming, Windows

Tagged with , , ,

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

leave a comment »

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.

Written by Sandip

January 6, 2011 at 4:12 pm

Posted in .Net

Tagged with

MS SQL: Using dynamic sql in Cursor

leave a comment »

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

Written by Sandip

April 22, 2010 at 7:36 am

Posted in MS SQL

Tagged with ,

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

leave a comment »

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

Written by Sandip

April 9, 2010 at 11:52 am

Posted in MS SQL

Tagged with

A Basic SQLite DAL class in C#

leave a comment »

 

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;
    }

}

Written by Sandip

April 6, 2010 at 8:51 am

Posted in .Net

Tagged with ,