Consulting Group

We would be soon publishing articles from our Consulting Group.

 7VZ5R9M2VKG4

What are Extension Methods

Extension methods allows you to add new functionality for existing data types Without modifying the code of the type itself  This allows you to create new methods for a class which you have no access or those classes which are sealed and you can’t inherit. Extension Methods were introduced in C# 3.0 .
In order to create a extension method you need to create a static class with a static method and use the this keyword.
eg:


public static class ExtensionMethod
{

public static void OutputToConsole(this string s)

    {

        Console.WriteLine(s);

    }

}
so now its use
string s ="Extension mEthod";

s.OutputToConsole();

so now you see string does not have any method called OutputToConsole but when you specify the this keyword all the string varibles will be able to access this function

So now a thing which .net uses is Extension Methods Along with Generics In Linqu

this is what it does

eg:
public static class ExtensionMethod
{

public static void OutputToConsole<T>(this T s)

    {

        Console.WriteLine(s.toString());

    }

}
now this function is accessible to all types
so now its use
string s ="Extension mEthod";
s.OutputToConsole();
int i=1;
i.OutputToConsole();


About Author:
Steven Pinto is technology geek and loves to write on technology. He works in Systems Plus and actively contributes to technology. To more of interesting topics written by Steven, follow http://mad4teck.blogspot.in/

Bulk Insertion of Excel data into Table through Data Type

When we insert data into database from excel file mostly we tend to use one by one reading approach. But if records are more then it will take more time for insertion. So, to avoid that we can import excel data as data table into Database which consumes very less time.   

How we can achieve this?

1.      Make SQL Type

•                     Create an SQL Type for storing bulk data into SQL table having columns same as column in excel.

•                     Make sure data types which we are passing should be same as SQL table data type.  This is more important think in making SQL type for bulk insertion

The Process for the above activities as follows:

CREATE TYPE [dbo].[TYPE NAME] AS TABLE

(

      [Column1] <Data Type>  NULL,

      [Column2] <Data Type>  NULL,

      [Column3] <Data Type>  NULL,

      [Column4] <Data Type>  NULL,

      [Column5] <Data Type>  NULL,

[Column6] <Data Type>  NULL,

[Column7] <Data Type>  NULL,

[Column8] <Data Type>  NULL,

[Column9] <Data Type>  NULL,

      .

      .

      .

      .

.

.

.

      [Column'N'] <Data Type>  NULL

)

2.     Make Store Procedure for bulk insertion

Create store procedure which actually inserts bulk data into table in which we can pass the data table as SQL Type which we created above and do the bulk insertion in table.

The Process for the above activities as follows:

CREATE PROCEDURE [dbo].[<Procedure Name>]

(

      @DataTable AS dbo.<Type Name> READONLY

)

AS

      /* SET NOCOUNT ON */

INSERT INTO  <Table Name>

(

      [Column1],[Column2],[Column3],[Column4],[Column5],

      ................,

      [Column'N']

)

SELECT

            [Column1],[Column2],[Column3],[Column4],[Column5],

            ................,

            [Column'N']

From  @DataTable

3.     Get data from excel and pass this data to Store procedure as data table.

•                     Open the connection string for excel and read the data from excels.

•              Pass this data as data tables to store procedure, Column names (header) from excel are must be same as column in data  Table and SQL Table.

The Process for the above activities as follows:

string xConnStr = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" +<excel file path>+ ";" + "Extended Properties=Excel 12.0;";

OleDbDataAdapter oda;

using (OleDbConnection connection = new OleDbConnection(xConnStr))

 {

connection.Open();

oda = new OleDbDataAdapter

(

"Select Column1, Column2,Column3,Column4,Column5,....,Column'N' FROM [SheetName$] where <Condition>", connection

);

     DataTable tblName = new DataTable();

     Application.DoEvents();

     oda.Fill(tblName);

     connection.Close();

SqlConnection con1 = new

SqlConnection((ConfigurationManager.ConnectionStrings

["<Conn String>"]).ToString());

con1.Open();

Application.DoEvents();

cmd1 = new SqlCommand("<SPNameForInsertion>", con1);

cmd1.CommandType = CommandType.StoredProcedure;

SqlParameter tvparam =

cmd1.Parameters.AddWithValue("@dt", tblName);

tvparam.SqlDbType = SqlDbType.Structured;

cmd1.CommandTimeout = 0;

cmd1.ExecuteNonQuery();

 }

About Author

Vishal Kudale works with Systems Plus and is working on Dot Net technology projects.

He can be contacted at: vishal.kudale@spluspl.com

Trigger an Email of an SSRS Report from an SSIS Package

There are situations in which it would be useful to send a report to interested parties when an underlying table is updated by an SSIS package. This can be accomplished by creating a subscription to the report and triggering the subscription from the SSIS package via SQL Server Agent and the ReportServer.dbo.AddEvent stored procedure.

For the purposes of this tutorial, we will presume that the SSIS package and SSRS report already exist, and that the report has been published to SQL Server Reporting Services..

Step 1: Create an SSRS subscription

Enter the edit mode of the report from SQL Server Reporting Services.

Add the "To" email address(es) and choose the desired "Render Format," then click on the "Select Schedule" button.

Set the report to run only once, and change the beginning and end dates to dates that have already passed (the end date must be greater than the start date).

Creation of the new subscription is complete.

Step 2: Determine the SQL Server Agent Job ID of the new subscription from its URL in "Edit" mode

Click on the "Edit" node of the above screen and search for the SubscriptionID string in the URL as highlighted below.

Copy and paste the SubscriptionID to Notepad for safekeeping.

Step 3: Add an Execute SQL Task component to the SSIS package

Drag-and-drop an Execute SQL Task component into the SSIS package.

Add a precedence constraint to the Execute SQL Task and change its label to "Trigger report."

Step 4: Add the execute procedure SQL code to the Execute SQL Task component

Double-click on the "Trigger report" task to bring up the edit screen.

Click on the button and add the following code, pasting in the SubscriptionID from Notepad.

Click the OK button and save the SSIS package.

About Author:

Sameer Kothari works in Systems Plus and actively contributes to technology. To read more interesting articles from him , please follow: http://samk2010.blogspot.in/

ASP.NET Impersonation

ASP.NET Impersonation controls the application identity of Web application.

This is a security element

This element can be declared at any level (machine, site, application, subdirectory, or page).

Impersonation is disabled by default.

Impersonation is disabled. This is the default setting. For backward compatibility with ASP, you must enable impersonation and change the ASP.NET process identity to use the Local System account. In this instance, the ASP.NET thread runs using the process token of the application worker process regardless of which combination of IIS and ASP.NET authentication is used. By default, the process identity of the application worker process is the ASPNET account. For more information, see ASP.NETProcess Identity.

<identity impersonate="false" />

Impersonation enabled. In this instance, ASP.NET impersonates the token passed to it by IIS, which is either an authenticated user or the anonymous Internet user account (IUSR_machinename).

<identity impersonate="true" />

Impersonation enabled for a specific identity. Above example is to impersonate a specific user for all the requests on all pages of an ASP.NET application, you can specify the userName and password attributes in the <identity> tag of the Web.config file for that application. For example

<identity impersonate="true"           userName="domain\user"           password="password" />

However sometimes this is risky to impersonate any user to your sites, especially when your application is embedded in SharePoint sites. Because if you do so; then all users who logs in to the SharePoint site will be treated as impersonated user which you have mentioned in the identity element.

For example: if you have a system account used in identity element of a SharePoint site, then after any log in to site, user will be considered as System Account only, hence user will get full access to your site.

Since we cannot add all users in Web.config based on their access rights. To avoid such scenario, we can authenticate logged in users programmatically.

Below is the small example in C# to authenticate user programmatically

System.Security.Principal.WindowsImpersonationContext impersonationContext; impersonationContext = ((System.Security.Principal.WindowsIdentity)User.Identity).Impersonate(); \\Insert your code that runs under the security context of the authenticating user here. impersonationContext.Undo();

About Author:

Harshad Pednekar is budding technology geek, who helps Systems Plus with his creativity and research on technology. He works in Systems Plus and actively contributes to technology. To read more interesting articles from him , please follow: http://harshadpednekar.blogspot.in