Sandip's Programming Zen

An attempt to share tech/coding experiences

Archive for April 2010

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 ,

SQLite Administrator: Great SQLite Windows client

leave a comment »

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 😉

Written by Sandip

April 6, 2010 at 3:44 am

Posted in productivity, Softwares

Tagged with