MySql Query To Get Important Column Properties of A Table

MySql Query :

SELECT  COLUMN_NAME
      , DATA_TYPE
      , CHARACTER_MAXIMUM_LENGTH
      , NUMERIC_PRECISION
      , NUMERIC_SCALE
      , EXTRA
      , COLUMN_KEY
      , COLUMN_DEFAULT
      , IS_NULLABLE
      , COLUMN_COMMENT
FROM    INFORMATION_SCHEMA.COLUMNS
WHERE   table_name = 'YourTableName'
        AND TABLE_SCHEMA = 'YourDatabaseName'

Example OutPut:

MySql Query To List All The Tables in Perticular Database

MySql  Query:

 
SELECT  TABLE_NAME
FROM    INFORMATION_SCHEMA.TABLES
WHERE   TABLE_SCHEMA = 'test'
 

Example OutPut:

Understanding StopWatch Class in c#

A Stopwatch instance can measure elapsed time for one interval, or the total of elapsed time across multiple intervals. In a typical Stopwatch scenario, you call the Startmethod, then eventually call the Stop method, and then you check elapsed time using the Elapsed property. (From MSDN)


Let say i want to find out Total Time a Process has been Running. 
Like
1. How many Minutes or Second it took to insert 1000 record in Database?
2. How many time it took to update the Database ? etc ..


I provide a very Basic Example with a Console Application ..


The Stopwatch Class has two Primary Methods 1. Start() 2. Stop() and One Property that is Elapsed .


The Code:

using System;
using System.Collections.Generic;
using System.Text;
using System.Diagnostics;

namespace StopWatchExample
{
    class Program
    {
        static void Main(string[] args)
        {
            Stopwatch sw = new Stopwatch();
            sw.Start();
            Console.WriteLine("Stop Watch Start....\n");
            for (int i = 1; i <= 5; i++)
            {
                Console.WriteLine(i + "\n");
            }
            sw.Stop();
            Console.WriteLine("Stop Watch Stopped:");
            Console.WriteLine("Total Time: {0}", sw.Elapsed);
            Console.ReadLine();
        }
    }
}

As You can See I have started our Stopwatch Just Before My loop has started.
Now the Loop will Do its work ..
After That As Soon As we are out sw.Stop has been Called.

I have Just Recorded The Timing The Loop Took To Complete.

The Output Will Look SomeThing Like These:

Embeding Firefox Brower (Gecko Engine) in C# using GeckoFX

Ever Thought if it was possible to not WebBrower control and use the Firefox Browser in your c# window Application...??

Yes ,, It is possible using GeckoFx wrapper class for  C#.net and vb also.

You will need the Followings:

1. GeckoFx Wrapper [Download]
2. Xul Runner [Download] (Download the zip file from here)

How to:

1.   Now Extract Skybound.GeckoFX.bin.v1.9.1.0.zip (First download).
      You will find Skybound.Gecko.dll and that's what we need.
2.   Now create a Project and name is GeckoFxcsharpBorwer or anything you like.
3.   Now  add the reference of the above dll and also add that to your toolbox.
4.   Now Exctract the Second downloaded file (xulrunner-1.9.1.2.en-US.win32.zip). and you will find xulrunner . This is the path to xulrunner runtime.
5.   Create a Form Layout Like The Figure Below:

Now The Coding Part:

Our Form's Constructor:(Providing the Path to XulRunner Runtime)

public Form1()
        {
            InitializeComponent();
            Skybound.Gecko.Xpcom.Initialize("Path To the xulrunner directory [Extracted from the Second Downloaded File]"); // for example "c:\\xulrunner\\"
        }

Form's Load Event:

private void Form1_Load(object sender, EventArgs e)
        {
            geckoWebBrowser1.Navigate("www.google.com");
        }

Stop Button click Event:

private void btnStop_Click(object sender, EventArgs e)
        {
            geckoWebBrowser1.Stop();
        }

Refresh Button Click Event:

private void btnRefresh_Click(object sender, EventArgs e)
        {
            geckoWebBrowser1.Refresh();
        }

Save Page Button Click Event:

private void btnSavePage_Click(object sender, EventArgs e)
        {
            SaveFileDialog sfd = new SaveFileDialog();
            sfd.Filter = " Html File | *.html";
            if (sfd.ShowDialog() == DialogResult.OK)
            {
                geckoWebBrowser1.SaveDocument(sfd.FileName);
            }
        }

And Now Finally the Output:

That's All.

Complete Project : GeckoNet

ps: You can easily Add an Addressbar and other stuff

Parsing Comma Delemeted String in Sql

I will just go with the Example as its Self Exclamatory.

Sql Query:

declare @string varchar(500)
set @string = 'ABC,DEF,GHIJK,LMNOPQRS,T,UV,WXY,Z'

declare @pos INT
declare @piece varchar(500)

-- Need to tack a delimiter onto the end of the input string if one doesn’t exist
if right(rtrim(@string),1) <> ','
 set @string = @string  + ','

set @pos =  patindex('%,%' , @string)
while @pos <> 0
begin
 set @piece = left(@string, @pos - 1)
 
 -- You have a piece of data, so insert it, print it, do whatever you want to with it.
 print cast(@piece as varchar(500))

 set @string = stuff(@string, 1, @pos, '')
 set @pos =  patindex('%,%' , @string)
end

Output:

A stored Procedure to Count Numbers of Rows in Table

This a simple Stored procedure to count the total numbers of rows in each table of your Database.
I assume you have Northwind database installed.

The Stored Procedure

IF EXISTS ( SELECT  *
            FROM    sysobjects
            WHERE   id = OBJECT_ID(N'[dbo].[sp_GetRowsCountForAllTables]')
                    AND OBJECTPROPERTY(id, N'IsProcedure') = 1 ) 
    DROP PROCEDURE [dbo].[sp_GetRowsCountForAllTables]
GO

CREATE PROCEDURE sp_GetRowsCountForAllTables
    @DBName VARCHAR(128) = NULL
AS 
    SET nocount ON
    IF @DBName IS NULL 
        SET @DBName = DB_NAME()

    CREATE TABLE #a
        (
          TableName VARCHAR(128)
        , norows INT NULL
        , id INT IDENTITY(1, 1)
        )
 
    DECLARE @id INT
      , @maxID INT
      , @TableName VARCHAR(128)
      , @FKName VARCHAR(128)
      , @cmd NVARCHAR(1000)
      , @rc INT
      , @spcmd VARCHAR(1000)
 
    SET @cmd = 'exec ' + @DBName + '..sp_executesql N''insert #a (TableName) 
   select TABLE_NAME from information_schema.tables
   where TABLE_TYPE = ''''BASE TABLE'''' ''
  '
    EXEC (@cmd)
 
    SELECT  @id = 0
          , @maxID = MAX(id)
    FROM    #a
 
    WHILE @id < @maxID 
        BEGIN
            SELECT  @id = MIN(id)
            FROM    #a
            WHERE   id > @id
  
            SELECT  @TableName = TableName
            FROM    #a
            WHERE   id = @id
  
            SET @cmd = 'exec ' + @DBName
                + '..sp_executesql N''update #a set norows = (select rows from sysindexes where indid in (0,1) and id = object_id('''''
                + @TableName + '''''))'
            SET @cmd = @cmd + ' where #a.id = ' + CONVERT(VARCHAR(10), @id)
                + ''''
  
            EXEC  (@cmd)
            IF @rc <> 0
                OR @@error <> 0 
                BEGIN
                    RAISERROR('failed %s',16,-1,@TableName)
                    RETURN
                END
        END

    SELECT  *
    FROM    #a ORDER BY norows desc

    DROP TABLE #a
GO

The Output:

EXEC sp_GetRowsCountForAllTables

Finding Duplicates in a Table 's Column

I assume You have Northwind Database.

I will be using The Employee Table.

As Show in the figure there are 6 Selected cells which are the Duplicates( Just assume it)
I want to find this number ( i mean 6.)

The query for the above is:

SELECT  Title
      , COUNT(Title) AS NumOccurrences
FROM    dbo.Employees
GROUP BY Title
HAVING  ( COUNT(Title) > 1 )

SELECT  *
FROM    dbo.Employees