Complete Solution

Export Datatable to Excel in C#

2013-06-24T07:36:00.000-07:00

First, add the "Microsoft.Office.Interop.Excel" assembly reference in your project. Please make sure your project uses .Net Framework 4.0

Microsoft.Office.Interop.Excel.Application ExcelApp =
            new Microsoft.Office.Interop.Excel.Application();
            ExcelApp.Application.Workbooks.Add(Type.Missing);

            // Change properties of the Workbook 

            ExcelApp.Columns.ColumnWidth = 20;

            // Storing header part in Excel
            for (int i = 1; i < dataGridView1.Columns.Count + 1; i++)
            {
                ExcelApp.Cells[1, i] = dataGridView1.Columns[i - 1].HeaderText;
            }

            // Storing Each row and column value to excel sheet
            for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
            {
                for (int j = 0; j < dataGridView1.Columns.Count; j++)
                {
                    ExcelApp.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString();
                }
            }
            MessageBox.Show("Excel file created , you can find the file D:\\Employee Data.xlsx");
            ExcelApp.Visible = true;
            ExcelApp.ActiveWorkbook.SaveCopyAs("D:\\Employee Data.xlsx");
 ExcelApp.ActiveWorkbook.Saved = true;
 Marshal.FinalReleaseComObject(ExcelApp);

Enjoy..

Fault contract in WCF

2013-03-14T03:01:00.001-07:00

I have created one sample project and uploaded on goggle drive. To download click the below click then open the "File" option from the menu and then you will find the "Download" option.
WCFServiceUsingFaultContract Project

What is Fault Contract:

In simple WCF Service errors/Exceptions can be passed to the Client(WCF Service Consumer) by using FaultContract How do you handle errors in ASP.NET? It's very simple just by adding the simple Try & Catch blocks. But when you come to WCF Service if any unexpected error occurred (like SQL server down/Unavailability of data/Divide By Zero) in service then error/Exception details can be passed to Client by using Fault Contract.

Predicted: (Divide By Zero/Channel Exceptions/Application exceptions etc..)

using System;
 public void Divide(float number, float divideBy) 
       {
           If(dividBy ==0)
           {
             myServiceData.Result = false;
             myServiceData.ErrorMessage = "Invalid Operation.";
             myServiceData.ErrorDetails = "Can not divide by 0.";
             throw new FaultException(myServiceData);
           }
           return number/divideBy;
       }

UnPredicted: (which I explained in this article like connection failures/SQL Server down/Transport errors/Business logic errors.)

using System;
try
            {
                SqlConnection con = new SqlConnection(StrConnectionString);
                con.Open();
                myServiceData.Result = true;
             //Your logic to retrieve data & and return it. If any exception occur while opening the connection or any other unexpected exception occur it can be thrown to Client (WCF Consumer) by below catch blocks.
           }
            catch (SqlException sqlEx)
            {
                myServiceData.Result = true;
                myServiceData.ErrorMessage = "Connection can not open this " + 
                   "time either connection string is wrong or Sever is down. Try later";
                myServiceData.ErrorDetails = sqlEx.ToString();
                throw new FaultException(myServiceData, sqlEx.ToString());
            }
            catch (Exception ex)
            {
                myServiceData.Result = false;
                myServiceData.ErrorMessage = "unforeseen error occured. Please try later.";
                myServiceData.ErrorDetails = ex.ToString();
                throw new FaultException(myServiceData, ex.ToString());
            }

Action Filters in C# (MVC)

2013-03-10T20:41:00.001-07:00

The goal of this tutorial is to explain action filters. An action filter is an attribute that you can apply to a controller action -- or an entire controller -- that modifies the way in which the action is executed. The ASP.NET MVC framework includes several action filters:

OutputCache – This action filter caches the output of a controller action for a specified amount of time.
HandleError – This action filter handles errors raised when a controller action executes.
Authorize – This action filter enables you to restrict access to a particular user or role.

You also can create your own custom action filters. For example, you might want to create a custom action filter in order to implement a custom authentication system. Or, you might want to create an action filter that modifies the view data returned by a controller action.

In this tutorial, you learn how to build an action filter from the ground up. We create a Log action filter that logs different stages of the processing of an action to the Visual Studio Output window..

Using an Action Filter

An action filter is an attribute. You can apply most action filters to either an individual controller action or an entire controller.

For example, the Data controller in Listing 1 exposes an action named Index() that returns the current time. This action is decorated with the OutputCache action filter. This filter causes the value returned by the action to be cached for 10 seconds.

using System;
using System.Web.Mvc;

namespace MvcApplication1.Controllers
{
     public class DataController : Controller
     {
          [OutputCache(Duration=10)]
          public string Index()
          {
               return DateTime.Now.ToString("T");

          }
     }
}

Filtering in ASP.NET MVC

2013-03-09T19:45:00.002-08:00

In ASP.NET MVC, controllers define action methods that usually have a one-to-one relationship with possible user interactions, such as clicking a link or submitting a form. For example, when the user clicks a link, a request is routed to the designated controller, and the corresponding action method is called. Sometimes you want to perform logic either before an action method is called or after an action method runs. To support this, ASP.NET MVC provides filters. Filters are custom classes that provide both a declarative and programmatic means to add pre-action and post-action behavior to controller action methods. ASP.NET MVC Filter Types ASP.NET MVC supports the following types of:

Authorization filters: These implement IAuthorizationFilter and make security decisions about whether to execute an action method, such as performing authentication or validating properties of the request. The AuthorizeAttribute class and the RequireHttpsAttribute class are examples of an authorization filter. Authorization filters run before any other filter.
Action filters: These implement IActionFilter and wrap the action method execution. The IActionFilter interface declares two methods: OnActionExecuting and OnActionExecuted. OnActionExecuting runs before the action method. OnActionExecuted runs after the action method and can perform additional processing, such as providing extra data to the action method, inspecting the return value, or canceling execution of the action method.
Result filters: These implement IResultFilter and wrap execution of the ActionResult object. IResultFilter declares two methods: OnResultExecuting and OnResultExecuted. OnResultExecuting runs before the ActionResult object is executed. OnResultExecuted runs after the result and can perform additional processing of the result, such as modifying the HTTP response. The OutputCacheAttribute class is one example of a result filter.
Exception filters: These implement IExceptionFilter and execute if there is an unhandled exception thrown during the execution of the ASP.NET MVC pipeline. Exception filters can be used for tasks such as logging or displaying an error page. The HandleErrorAttribute class is one example of an exception filter.

The Controller class implements each of the filter interfaces. You can implement any of the filters for a specific controller by overriding the controller's On<Filter> method. For example, you can override the OnAuthorization method. The simple controller included in the downloadable sample overrides each of the filters and writes out diagnostic information when each filter runs. You can implement the following On<Filter> methods in a controller:

Reference from http://msdn.microsoft.com/en-us/library/gg416513(v=vs.98).aspx

Profiling Database activity in Entity Framework

2013-03-07T09:50:00.002-08:00

Profiling Database activity in Entity Framework

In Entity framework the LINQ to entities query can be traced by method, ToTraceString. For doing this first it needs to be converted in ObjectQuery.

var objQuery = from c in context.Customers where c.CustomerID == 3 select c;

var objectQuery=objQuery as System.Data.Objects.ObjectQuery;

Console.WriteLine(objectQuery.ToTraceString());

Granting execute permission on all stored procedure in SQL

2012-12-26T02:17:00.005-08:00

In SQL many times we need to provide execute permission to all stored procedure for specific user for any database. To achieve this you can make the execute statement by below query.

The query for it is just simple:

SELECT 'Grant Execute on ' + name +  ' SQLServerUSERName'FROM sysobjects WHERE xtype IN ('P')

For tables and views:

SELECT 'Grant select,insert,update,delete on ' + name + ' SQLServerUSERName''
from sysobjects where xtype in ('U','V')'

Enjoy.

Getting Month name from any date

2012-12-26T02:02:00.000-08:00

Getting Month name from any date in SQL:

Just use the below query and you will get the result.

SELECT DATENAME(MONTH, DATEADD(MONTH, MONTH(GETDATE()), -1 ))

Example with result:

Done.

Getting Date from Date Time column

2012-12-24T09:47:00.001-08:00

While using date time fields in WHERE clause you can face one common problem. Most of time you can't get the records for which you have used the datetime in where clause. For example:

SELECT * FROM tTable WHERE DateTimeColumn='12/24/2012'

In this I don't get the records which have date 12/24/2012, it is because in table the column contains the time also. Then how to GET these records. Its very simple just convert your date time column with below code:







CAST(FLOOR( CAST( SM.DateDownloaded AS FLOAT ) )AS DATETIME) AS DownloadedDate

Converting rows to columns

2012-12-24T09:23:00.000-08:00

Using Pivot you can easily convert rows to columns.

USE AdventureWorks
GO
SELECT VendorID, [164] AS Emp1, [198] AS Emp2, [223] AS Emp3, [231] AS Emp4, [233] AS Emp5FROM (SELECT PurchaseOrderID, EmployeeID, VendorIDFROM Purchasing.PurchaseOrderHeader) p
PIVOT(COUNT (PurchaseOrderID)FOR EmployeeID IN( [164], [198], [223], [231], [233] )
) AS 
ORDER BY VendorID

Enjoy.

Finding foriegn key table names which primary key is using in other tables

2012-12-24T09:10:00.002-08:00

Finding table names in which a primary key of any table is used as foriegn key.



SELECT
    ConstraintName = fk.name,
    TableName = t.name,
    ColumnName = c.nameFROM
    sys.foreign_keys fkINNER JOIN 
    sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.OBJECT_IDINNER JOIN 
    sys.tables t ON fk.parent_object_id = t.OBJECT_IDINNER JOIN 
    sys.columns c ON fkc.parent_object_id = c.OBJECT_ID AND fkc.parent_column_id = c.column_idINNER JOIN 
    sys.tables tref ON fk.referenced_object_id = tref.OBJECT_IDINNER JOIN 
    sys.columns cref ON fkc.referenced_object_id = cref.OBJECT_ID AND fkc.referenced_column_id = cref.column_idWHERE
    tref.Name = 'tCurrencyCode'
    AND cref.Name = 'CurrencyId'

Enjoy.

Problem while installing office 2007 / office 2010

2012-08-30T22:08:00.002-07:00

Hi,

I just faced one problem while installing MS Office 2010 in my laptop. There is office 2001 already installed in m laptop and that was corrupted and I am to install or upgrade office.
I got the following error during set up:

Microsoft Office suite_name encountered an error during setup.

First you have to unistall previous version of office by Microsoft Fix it:

For uninstalling Office 2003: Fix it 2003

For uninstalling Office 2007: Fix it 2007

For uninstalling Office 2010: Fix it 2010

Now if you try to install again error will be occured. So one more step to go.

In Windows XP

Click Start, click Run, type cmd, and then click OK.
Type cd "%allusersprofile%\Application Data\Microsoft Help", and then press ENTER.
Type attrib -h rgstrtn.lck, and then press ENTER.
Type del rgstrtn.lck, and then press ENTER.
Type dir /b /od /ad, and then press ENTER.
Type rd /q /s "<folder names>", and then press ENTER.

Note The <folder names> are the folder names that are listed after you run the command in step 5. There should be a space between each folder-name. For example: rd /q /s "foldername1" "foldername2" "foldername3".
Type exit, and then press ENTER.

In Windows Vista and Windows 7

Click Start, and then type cmd in the Start Search box, and then click OK.
Type cd "%allusersprofile%\Microsoft Help", and then press ENTER,
Type attrib -h rgstrtn.lck, and then press ENTER.
Type del rgstrtn.lck, and then press ENTER.
Type dir /b /od /ad, and then press ENTER.
Type rd /q /s "<folder names>", and then press ENTER.

Note The <folder names> are the folder names that are listed after you run the command in step 5. There should be a space between each folder-name. For example: rd /q /s "foldername1" "foldername2" "foldername3"
Type exit, and then press ENTER.

Thats it!!
Now you can instally any version of Office in your pc. Enjoy!!!!!

Machine Key for Load Balancing

2012-03-31T08:40:00.000-07:00

In my project there is requirement to change the field length eg CompanyCode. Problem is with this field name there are many tables who not created the foriegn keys and this field is used in many procedures and functions. To find the text company code I found this simple code:

/// <summary>
/// Version arguments to MachineKey.Generate() method.
/// </summary>
public enum MachineKeyVersion
{
    /// <summary>
    /// .NET version 1.1.
    /// </summary>
    Net1,

    /// <summary>
    /// .NET version 2.0 and up.
    /// </summary>
    Net2,
}

public class MachineKey
{
    /// <summary>
    /// Generates the contents of a machineKey element suitable for use in
    /// an ASP.NET web.config file.
    /// </summary>
    /// <param name="version">Indicates if keys should be generated for
    /// ASP.NET 1.1 or 2.0 and later.</param>
    public static string Generate(MachineKeyVersion version)
    {
        // Generate keys
        string validationKey = GenerateKey(64);
        string decryptionKey;
        if (version == MachineKeyVersion.Net1)
            decryptionKey = GenerateKey(24);
        else
            decryptionKey = GenerateKey(32);

        // Construct <machineKey> tag
        StringBuilder builder = new StringBuilder();
        builder.Append("<machineKey");
        builder.AppendFormat(" validationKey=\"{0}\"", validationKey);
        builder.AppendFormat(" decryptionKey=\"{0}\"", decryptionKey);
        builder.Append(" validation=\"SHA1\"");
        if (version == MachineKeyVersion.Net2)
            builder.Append(" decryption=\"AES\"");
        builder.Append(" />");
        return builder.ToString();
    }

    /// <summary>
    /// Generates a string of random hex digits of the specified
    /// number of bytes.
    /// </summary>
    /// <param name="length">Number of bytes to generate</param>
    protected static string GenerateKey(int length)
    {
        RNGCryptoServiceProvider rngCsp = new RNGCryptoServiceProvider();
        byte[] buff = new byte[length];
        rngCsp.GetBytes(buff);
        StringBuilder sb = new StringBuilder(buff.Length * 2);
        for (int i = 0; i < buff.Length; i++)
            sb.Append(string.Format("{0:X2}", buff[i]));
        return sb.ToString();
    }
}

Now just call the MachineKey.Generate method

private void btnGenerate_Click(object sender, EventArgs e)
{
    MachineKeyVersion version;

    if (radNet1.Checked)
        version = MachineKeyVersion.Net1;
    else
        version = MachineKeyVersion.Net2;

    txtMachineKey.Text = MachineKey.Generate(version);
}

Reference: http://www.blackbeltcoder.com/Articles/asp/generating-a-machinekey-element

Searching Text in Procedures, Tables and Functions

2012-03-22T22:51:00.001-07:00

CREATE PROCEDURE adhoc_SearchText(@text VARCHAR(1024))
AS
BEGIN
 -- tables
 SELECT
  TABLE_NAME
 FROM
  INFORMATION_SCHEMA.TABLES T
 WHERE
  charindex(@text, T.TABLE_NAME)>0
  
 -- columns
 SELECT 
  C.TABLE_NAME, C.COLUMN_NAME
 FROM
  INFORMATION_SCHEMA.COLUMNS C
 WHERE
  charindex(@text, C.COLUMN_NAME)>0

 -- views
 SELECT 
  V.TABLE_NAME AS VIEW_NAME
 FROM 
  information_schema.VIEWS V
 WHERE 
  charindex(@text, V.VIEW_DEFINITION)>0
  
 -- stored procs
 SELECT 
  R.ROUTINE_NAME 
 FROM 
  information_schema.routines r 
 WHERE 
  charindex(@text, r.ROUTINE_DEFINITION)>0
END

Hope this helps you!!!!!

Password in C# Console Application

2012-03-20T01:52:00.001-07:00

Today my friend ask me how to make password field in C# Console Application.
I found the simple solution:




class PasswordExample

    {

        static void Main(string[] args)

        {

            Console.WriteLine("Pls key in your Login ID");

            var
loginid = Console.ReadLine();

            Console.WriteLine("Pls key in your Password");

            var
password = ReadPassword();



            Console.Write("Your Password is:" + password);

            Console.ReadLine();
        }




      

     public static string
ReadPassword()

        {

            string
password = "";

            ConsoleKeyInfo
info = Console.ReadKey(true);

            while
(info.Key != ConsoleKey.Enter)

            {

                if
(info.Key != ConsoleKey.Backspace)

                {

                    Console.Write("*");

                    password += info.KeyChar;

                }

                else
if (info.Key == ConsoleKey.Backspace)

                {

                    if
(!string.IsNullOrEmpty(password))

                    {

                        // remove one character from the list of password characters

                        password =
password.Substring(0, password.Length - 1);

                        // get the location of the cursor

                        int pos = Console.CursorLeft;

                        // move the cursor to the left by one character

                        Console.SetCursorPosition(pos - 1, Console.CursorTop);

                        // replace it with space

                        Console.Write(" ");

                        // move the cursor to the left by one character again

                        Console.SetCursorPosition(pos - 1, Console.CursorTop);

                    }

                }

                info = Console.ReadKey(true);

            }





            // add a
new line because user pressed enter at the end of their password

            Console.WriteLine();

            return
password;

        } 

    }

That's it.

Bulk Insert From XML To Table

2012-03-19T05:39:00.002-07:00

If you want to insert large number of data in table with just one shot then you can do it by this article. While doing my project I came to scenario where I want to send the large number to data to SQL and then insert into tables. I found this very useful. What I did is I passed all records in form of XML in SQL Procedure.

Below is the example: Open Query analyzer and paste the below code

1) Declared the XML Variable first and set the value to it.


DECLARE @idoc int




DECLARE @doc varchar(8000)

SET @doc ='<ROOT><student>

<id>1</id>

<name>Prashant</name>

<age>32</age>

</student>

<student>

<id>2</id>

<name>Swami</name>

<age>42</age>

</student>

<student>

<id>3</id>

<name>Ash</name>

<age>23</age>

</student>

<student>

<id>4</id>

<name>Kris</name>

<age>12</age>

</student>

<student>

<id>5</id>

<name>Derek</name>

<age>75</age>

</student>

</ROOT>'

2) Now Include this:





--Create an internal representation of the XML document.

EXEC sp_xml_preparedocument @idoc

OUTPUT, @doc

-- Execute a SELECT statement that uses the OPENXML rowset provider.

Insert into Students SELECT id, name, age from 

OPENXML (@idoc, '/ROOT/student',2) 

WITH
(id  int, name varchar(50), age int)




Select * from
@Students

Enjoy!!!!!!

Retrieving Last Inserted Identity of Record

2012-03-19T04:58:00.001-07:00

While writing procedure in SQL somewhere I want the Identity value of inserted record but confused which way is the best to use and what's the difference between them. I found very useful article on this which I am sharing.

There are 3 ways to get the Identity value of last inserted record in SQL Server procedures:
@@IDENTITY, SCOPE_IDENTITY, IDENT_CURRENT

SELECT @@IDENTITY
It returns the last IDENTITY value produced on a connection, regardless of the table that produced the value, and regardless of the scope of the statement that produced the value.
@@IDENTITY will return the last identity value entered into a table in your current session. While @@IDENTITY is limited to the current session, it is not limited to the current scope. If you have a trigger on a table that causes an identity to be created in another table, you will get the identity that was created last, even if it was the trigger that created it.

SELECT SCOPE_IDENTITY()
It returns the last IDENTITY value produced on a connection and by a statement in the same scope, regardless of the table that produced the value.
SCOPE_IDENTITY(), like @@IDENTITY, will return the last identity value created in the current session, but it will also limit it to your current scope as well. In other words, it will return the last identity value that you explicitly created, rather than any identity that was created by a trigger or a user defined function.

SELECT IDENT_CURRENT(‘tablename’)
It returns the last IDENTITY value produced in a table, regardless of the connection that created the value, and regardless of the scope of the statement that produced the value.
IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the identity value generated for a specific table in any session and any scope.

To avoid the potential problems associated with adding a trigger later on, always use SCOPE_IDENTITY() to return the identity of the recently added row in your T SQL Statement or Stored Procedure.

Enjoy!!!!!!

Thanks to Pinal Dave where I found this useful topic.
Reference: http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/

Finding Nth row in SQL Server

2012-03-19T04:34:00.001-07:00

Finding nth row in SQL Server. Here is the simple query using ROW_NUMBER() function. In below example I am finding the 2 row:


SELECT * FROM


    (SELECT ROW_NUMBER()

        OVER (ORDER BY id) AS Row,

        id

    FROM Table) AS EMP

WHERE Row = 2

Enjoy!!!!!!

Deleting duplicate records in SQL Server

2012-03-19T04:30:00.000-07:00

Deleting duplicate records in SQL Server:

WITH emp AS (SELECT ROW_NUMBER ( ) OVER ( PARTITION BY id, firstname ORDER BY id ) AS RNUM FROMtable1 )

--select * from emp

DELETE FROM Emp WHERE RNUM > 1

Enjoy!!!!!!

Using Nested Transactions In SQL Server 2005

2011-09-15T19:38:00.000-07:00

Recently I have done Nested transaction in SQL Server 2005.
In this post, I will show you how to use Nested Transactions.

Below is the inner procedure which will be called from another procedure.

CREATE PROCEDURE pInnerProcdureName1
 AS
 BEGIN
   BEGIN try
     BEGIN TRANSACTION
        PRINT 'pProcdureName1 : ' + cast(@@TRANCOUNT as varchar(max))     
        DELETE FROM tablename
     
        COMMIT TRANSACTION
      PRINT 'pProcdureName1 Completed : ' + cast(@@TRANCOUNT as varchar(max)) 
     END try
     BEGIN catch
       if @@ERROR > 0
       begin
         PRINT error_message()
       end
       rollback transaction
       print 'Rollback Transaction :' + cast(@@TRANCOUNT as varchar(max)) 
     end catch    
   end 
GO

Now creating the outer transaction:

CREATE PROCEDURE pOuterProcedureName
    as
    begin
      begin try
        begin transaction
            print 'pOuterProcedureName : ' + cast(@@TRANCOUNT as varchar(max)) 
                
               exec pInnerProcdureName1
               commit transaction
           print 'pOuterProcedureName Transaction Committed : ' + cast(@@TRANCOUNT as varchar(max)) 
     end try
     begin catch
       if @@TRANCOUNT > 0
       begin
           rollback transaction
       end
      print 'Rollback pOuterProcedureName  : ' + cast(@@TRANCOUNT as varchar(max))
       if @@ERROR > 0
       begin
           SELECT error_message()
       end 
     end catch    
   end

Now just execute your Outer Transaction and Done.

EXEC pOuterProcedureName

Enjoy!!!!!!

Inserting Multiple Rows in One Insert Statement In SQL SERVER 2008

2011-09-14T23:40:00.000-07:00

You can insert multiple records with one insert statement. Syntax is: 

Insert Into TableName (field1,field2)
VALUES 

(value1,value2),

(value3,value4)