Programing Solutions on OOPS-Concept

Missing records in CSV file while importing from DB(C# code)

2013-01-10T12:56:00.002+05:30

Yesterday I faced a very strange problem in CSV files the data was mismatching from the db.

I have a small application which is used to pull data from db and write into a flat file csv . In other words its import data from db to CSV file. All of sudden yesterday user has raised a P1 tickets regarding the data in the csv file is missing some data is not found in the csv my colleague check is that data present in the db or not but in the query result it was available and we find that the record count in the db and csv is miss matching almost 700 records are not present in csv files. They have done all their effort but still they are not able to find out where actually the problem is ? Then it is come to me. And I have started debugging of code and checking every condition very carefully after a couple of hours I find that everything s are fine and record writing in the file is absolutely same. But still I have open the csv file the records are missing and getting the same value as they are getting. I surprised to see this then I have change the extension of the csv file to txt and open it with excel now what I see that record count is absolutely fine and matching with db. I surprised in normal cases in csv file data increase but this time data is missing. I made changes in the query and replace all commas with blank space but still the problem is same. I have also made some other changes like changing the delimiter “,” to “|” its solve the issue but I am not able to separate the columns as in excel. After couple of hourse struggle and analyzing the data carefully I found that in some date there was some “(double quotes) and that’s why when in csv file all the data between the “ ” treated as one record. After remove the “ quotes from data It solved the problem.

Hope my experience may guide you in such kind of problem.

Happy learning

Asp.net:secure web-service in Ajax 1.0

2012-08-10T19:55:00.002+05:30

This article can be used for .net 1.0(Vs2003) to secure webservices which is called on aspx page throgh core AJAX like xmlhttp. in this article no .net ajax component is used but it can be used for .net2.5 and 3.0 2005 and 2008 with a little bit trick which I will give in my next post.

on aspx page.

you have set the header. and call the web service using get method.

hope this help

if you find any difficulties  please post your comment.

xmlhttp.setRequestHeader('securityTicket' , SECURITY_TICKET );

on aspx.vb page

Private Sub GenerateSecurityTicket()
        Dim cacheKey As String = User.Identity.Name + ":securityTicket"
        Dim securityTicket As String = Guid.NewGuid().ToString()

        Cache(cacheKey) = securityTicket

        Dim script As String = "<script type=""text/javascript"">" + String.Format("SECURITY_TICKET = '{0}';", securityTicket) + "</script>"

        Page.RegisterClientScriptBlock("securityKey", script)
    End Sub

page_load()
GenerateSecurityTicket()
end sub


asmx Page

Private Sub EnsureTicket()
        Dim context As HttpContext = HttpContext.Current

        Dim headerTicket As String = context.Request.Headers("securityTicket")

        If headerTicket Is Nothing Or headerTicket = String.Empty Then
            Throw New System.Exception("Security ticket must be present.")
        End If

        Dim cacheKey As String = context.User.Identity.Name + ":securityTicket"
        Dim cacheTicket As String = DirectCast(context.Cache(cacheKey), String)

        If String.Compare(headerTicket, cacheTicket, False) <> 0 Then
            Throw New System.Exception("Security ticket mismatched.")
        End If
    End Sub


call EnsureTicket()

from every webmethod

Sohel's Blog: SharePoint 2010: Add/Delete/update/search list ite...

2012-03-15T13:59:00.001+05:30

Sohel's Blog: SharePoint 2010: Add/Delete/update/search list ite...: Managed client Object model is a rich extension to SharePoint 2010. You can almost perform all kinds operations against SharePoint using Cli...

Sudoku list generation program in C-sharp

2012-02-24T15:00:00.000+05:30

This is really a very good program , fast and optimize

class Program
{
static void Main(string[] args)
{

const int n = 3;

for (int i = 0; i < n * n; i++)
{
for (int j = 0; j < n * n; j++)
Console.Write((i * n + i / n + j) % (n * n) + 1);
Console.WriteLine();
} Console.ReadLine();
}
}

Abstract Factory Pattern in .Net with Example (C#)...

2012-02-22T15:22:00.000+05:30

Abstract Factory Pattern in .Net with Example (C#)...: Abstract Factory Pattern Abstract Factory pattern is used to create concrete class instances without specifying the exact class type (ie, to...

Asp.net: Binding Image in gridview using Handler from image column in databse sql2005/2008

2012-02-21T17:05:00.000+05:30

In my pervious article

Asp.net Saving Imagein binary into database table in Sql 2005/2008

you have learn how to save image file in binary format in a database column.

In this post I going to fetch those (Northwind databse product table) record and bind into a grid view. For this I have created a generic handler for binding the image on gridview image control here the code below

HTML PART

<h2>Show the images in the GridView</h2>

<asp:GridView ID="grdViewProducts" runat="server"

AllowPaging="True" AutoGenerateColumns="False" TabIndex="1"

DataKeyNames="ProductID" Width="100%" BackColor="White"

CellPadding="3" BorderStyle="Solid" BorderWidth="1px"

BorderColor="Black" GridLines="Horizontal" PageSize="10"

onpageindexchanging="grdViewProducts_PageIndexChanging">

<asp:TemplateField HeaderText="Photo">

<img id="imgPhoto" src="GetDBImage.ashx?ProductID=<%# Eval("ProductID") %>"

width="125px" height="125px" title="<%# Eval("ProductName") %>" />

</ItemTemplate>

</asp:TemplateField>

<asp:BoundField DataField="ProductName" HeaderText="Product Name" >

</asp:BoundField>

<asp:BoundField DataField="CompanyName" HeaderText="Supplier" >

</asp:BoundField>

<asp:BoundField DataField="CategoryName" HeaderText="Category" >

</asp:BoundField>

<asp:BoundField DataField="QuantityPerUnit" HeaderText="Quantity Per Unit">

</asp:BoundField>

<asp:BoundField DataField="UnitPrice" HeaderText="UnitPrice" DataFormatString="{0:#0.00}">

</asp:BoundField>

</Columns>

</asp:GridView>

C# Code

protected void Page_Load(object sender, EventArgs e)

{

if (!Page.IsPostBack)

BindGrid();

}

private void BindGrid()

{

using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLConnection"].ConnectionString))

{

SqlCommand command = new SqlCommand(

"SELECT ProductID, ProductName, CompanyName, CategoryName, " +

"QuantityPerUnit, UnitPrice FROM Products " +

"JOIN Suppliers ON Products.SupplierID = Suppliers.SupplierID " +

"JOIN Categories ON Products.CategoryID = Categories.CategoryID ", connection);

connection.Open();

SqlDataReader dr = command.ExecuteReader(CommandBehavior.CloseConnection);

IList<ProductView> productViewList = new List<ProductView>();

while (dr.Read())

{

ProductView productView = new ProductView();

productView.ProductID = Convert.ToInt32(dr["ProductID"].ToString());

productView.ProductName = dr["ProductName"].ToString();

productView.CompanyName = dr["CompanyName"].ToString();

productView.CategoryName = dr["CategoryName"].ToString();

productView.QuantityPerUnit = dr["QuantityPerUnit"].ToString();

productView.UnitPrice = Convert.ToDouble(dr["UnitPrice"].ToString());

productViewList.Add(productView);

}

grdViewProducts.DataSource = productViewList;

grdViewProducts.DataBind();

}

protected void grdViewProducts_PageIndexChanging(object sender, GridViewPageEventArgs e)

{

grdViewProducts.PageIndex = e.NewPageIndex;

BindGrid();

}

public class ProductView

{

public int ProductID { get; set; }

public string ProductName { get; set; }

public string CompanyName { get; set; }

public string CategoryName { get; set; }

public string QuantityPerUnit { get; set; }

public double UnitPrice { get; set; }

}

Handler Code for Image Creation from database

public class GetDBImage : IHttpHandler

{

public void ProcessRequest(HttpContext context)

{

using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLConnection"].ConnectionString))

{

SqlCommand command = new SqlCommand("Select ProductImage from Products where ProductID = '" + context.Request.QueryString["ProductID"] + "'", connection);

connection.Open();

SqlDataReader dr = command.ExecuteReader(CommandBehavior.CloseConnection);

while (dr.Read())

{

if (dr["ProductImage"].ToString().Length > 0)

{

context.Response.BinaryWrite((byte[])dr["ProductImage"]);

}

public bool IsReusable

{

get

{

return false;

}

}

here is the output

Downloadfull code from here

File or folder uploading using FTP in c#.net

2012-02-16T14:46:00.002+05:30

In this post I am using FTP to upload or download a folder or file into server

FtpWebRequest Class

using System;

using System.Net;

using System.Threading;

using System.IO;

namespace Examples.System.Net

{

public class FtpState

{

private ManualResetEvent wait;

private FtpWebRequest request;

private string fileName;

private Exception operationException = null;

string status;

public FtpState()

{

wait = new ManualResetEvent(false);

}

public ManualResetEvent OperationComplete

{

get {return wait;}

}

public FtpWebRequest Request

{

get {return request;}

set {request = value;}

}

public string FileName

{

get {return fileName;}

set {fileName = value;}

}

public Exception OperationException

{

get {return operationException;}

set {operationException = value;}

}

public string StatusDescription

{

get {return status;}

set {status = value;}

}

public class AsynchronousFtpUpLoader

{

// Command line arguments are two strings:

// 1. The url that is the name of the file being uploaded to the server.

// 2. The name of the file on the local machine.

public static void Main(string[] args)

{

// Create a Uri instance with the specified URI string.

// If the URI is not correctly formed, the Uri constructor

// will throw an exception.

ManualResetEvent waitObject;

Uri target = new Uri (args[0]);

string fileName = args[1];

FtpState state = new FtpState();

FtpWebRequest request = (FtpWebRequest)WebRequest.Create(target);

request.Method = WebRequestMethods.Ftp.UploadFile;

// This example uses anonymous logon.

// The request is anonymous by default; the credential does not have to be specified.

// The example specifies the credential only to

// control how actions are logged on the server.

request.Credentials = new NetworkCredential ("anonymous","janeDoe@contoso.com");

// Store the request in the object that we pass into the

// asynchronous operations.

state.Request = request;

state.FileName = fileName;

// Get the event to wait on.

waitObject = state.OperationComplete;

// Asynchronously get the stream for the file contents.

request.BeginGetRequestStream(

new AsyncCallback (EndGetStreamCallback),

state

);

// Block the current thread until all operations are complete.

waitObject.WaitOne();

// The operations either completed or threw an exception.

if (state.OperationException != null)

{

throw state.OperationException;

}

else

{

Console.WriteLine("The operation completed - {0}", state.StatusDescription);

}

private static void EndGetStreamCallback(IAsyncResult ar)

{

FtpState state = (FtpState) ar.AsyncState;

Stream requestStream = null;

// End the asynchronous call to get the request stream.

try

{

requestStream = state.Request.EndGetRequestStream(ar);

// Copy the file contents to the request stream.

const int bufferLength = 2048;

byte[] buffer = new byte[bufferLength];

int count = 0;

int readBytes = 0;

FileStream stream = File.OpenRead(state.FileName);

{

readBytes = stream.Read(buffer, 0, bufferLength);

requestStream.Write(buffer, 0, readBytes);

count += readBytes;

}

while (readBytes != 0);

Console.WriteLine ("Writing {0} bytes to the stream.", count);

// IMPORTANT: Close the request stream before sending the request.

requestStream.Close();

// Asynchronously get the response to the upload request.

state.Request.BeginGetResponse(

new AsyncCallback (EndGetResponseCallback),

state

);

}

// Return exceptions to the main application thread.

catch (Exception e)

{

Console.WriteLine("Could not get the request stream.");

state.OperationException = e;

state.OperationComplete.Set();

return;

}

// The EndGetResponseCallback method

// completes a call to BeginGetResponse.

private static void EndGetResponseCallback(IAsyncResult ar)

{

FtpState state = (FtpState) ar.AsyncState;

FtpWebResponse response = null;

try

{

response = (FtpWebResponse) state.Request.EndGetResponse(ar);

response.Close();

state.StatusDescription = response.StatusDescription;

// Signal the main application thread that

// the operation is complete.

state.OperationComplete.Set();

}

// Return exceptions to the main application thread.

catch (Exception e)

{

Console.WriteLine ("Error getting response.");

state.OperationException = e;

state.OperationComplete.Set();

}

public static bool DeleteFileOnServer(Uri serverUri)
{
    // The serverUri parameter should use the ftp:// scheme.
    // It contains the name of the server file that is to be deleted.
    // Example: ftp://contoso.com/someFile.txt.
    // 

    if (serverUri.Scheme != Uri.UriSchemeFtp)
    {
        return false;
    }
    // Get the object used to communicate with the server.
    FtpWebRequest request = (FtpWebRequest)WebRequest.Create(serverUri);
    request.Method = WebRequestMethods.Ftp.DeleteFile;

    FtpWebResponse response = (FtpWebResponse) request.GetResponse();
    Console.WriteLine("Delete status: {0}",response.StatusDescription);  
    response.Close();
    return true;
}

Ref Microsoft

Anonymous Type IN C# .NET3.0/4.0 Part2

2012-02-15T15:18:00.000+05:30

Anonymous Functions

An anonymous function is an "inline" statement or expression that can be used wherever a delegate type is expected. You can use it to initialize a named delegate or pass it instead of a named delegate type as a method parameter.

There are two kinds of anonymous functions

1) Lambda Expressions

A lambda expression is an anonymous function that can contain expressions and statements, and can be used to create delegates or expression tree types.

All lambda expressions use the lambda operator =>, which is read as "goes to". The left side of the

lambda operator specifies the input parameters (if any) and the right side holds the expression or statement block. The lambda expression x => x * x is read "x goes to x times x." This expression can

be assigned to a delegate type as follows:

delegate int del(int i);

static void Main(string[] args)

{

del myDelegate = x => x * x;

int j = myDelegate(5); //j = 25

}

using System.Linq.Expressions;

namespace ConsoleApplication1

{

class Program

{

static void Main(string[] args)

{

Expression<del> myET = x => x * x;

}

Expression Lambdas

A lambda expression with an expression on the right side is called an expression lambda. Expression lambdas are used extensively in the construction of Expression Trees. An expression lambda returns the result of the expression and takes the following basic form:

(x, y) => x == y

In the above example its take two parameter and compare and return a Boolean value

Statement Lambdas

A statement lambda resembles an expression lambda except that the statement(s) is enclosed in braces:

delegate void TestDelegate(string s);

TestDelegate myDel = n => { string s = n + " " + "World"; Console.WriteLine(s); };

myDel("Hello");

note: Statement lambdas, like anonymous methods, cannot be used to create expression trees.

The general rules for lambdas are as follows:

· The lambda must contain the same number of parameters as the delegate type.

· Each input parameter in the lambda must be implicitly convertible to its corresponding delegate parameter.

· The return value of the lambda (if any) must be implicitly convertible to the delegate's return type.

Lambdas with the Standard Query Operators

A standard query operator, the Count method

int[] numbers = { 5, 4, 1, 3, 9, 8, 6, 7, 2, 0 };

int oddNumbers = numbers.Count(n => n % 2 == 1);

The compiler can infer the type of the input parameter, or you can also specify it explicitly. This particular lambda expression counts those integers (n) which when divided by two have a remainder of 1.

The following method will produce a sequence that contains all the elements in the numbers array that are to the left of the 9, because that is the first number in the sequence that does not meet the condition:

var firstNumbersLessThan6 = numbers.TakeWhile(n => n < 6);

Anonymous Type IN C# .NET3.0/4.0 Part1

Anonymous Type IN C# .NET3.0/4.0 Part1

2012-02-15T12:42:00.000+05:30

Anonymous Types

Anonymous types provide a convenient way to encapsulate a set of read-only properties into a single object without having to explicitly define a type first. The type name is generated by the compiler and is not available at the source code level. The type of each property is inferred by the compiler.

In typical C# you will always carefully spell out your definitions:

string MyString = “Rashmi Kant”;

From the right side of the declaration it is obvious that only one type (string) will ever match this definition. So instead of us doing the work, why not let the compiler figure this out?

var MyString = “Rashmi Kant”;

The above definition will also create a string variable named “MyString”. It is important to note that C# is still strongly typed — unlike scripted languages such as Visual Basic (or PHP) once you have assigned the variable type it sticks. The following will not work:

var MyString2 = “Rashmi Kant”;
MyString2 = 123; //error

The compiler will throw an implicit conversion error as 123 cannot be assigned to a string.

The above was an impressive (if somewhat pointless) example of what an anonymous type is. For simple types such as strings, integer etc anonymous types offer little benefits.

You can create an array of anonymously typed elements by combining an implicitly typed local variable and an implicitly typed array, as shown in the following example.

var anonArray = new[] { new { name = "apple", diam = 4 }, new { name = "grape", diam = 1 }};

Other Example of anonymous class

var person0 = new

{

FirstName = "Rashmi",

LastName = "Kant",

Height = 172

};

var person1 = new

{

FirstName = "John",

LastName = "Doe",

Height = 165

};

var person2 = new

{

LastName = "Doe",

FirstName = "John",

Height = 175

};

var Employee = new { ID = 5, Name= "Rashmi" };

Projection in Anonymous types

Anonymous types also support Projection. So, if we have a declaration as shown below

var LastName = "Nurk";

var FirstName = "Fred";

var person4 = new { LastName, FirstName};

then a new Anonymous type will be created with the read only properties LastName and FirstName. C# automatically projects the names of the variables to the names of properties in the anonymous class.

This also works while using objects, as shown below –

Person personObject = new Person

{

LastName = "Doe",

FirstName = "Jane",

Height = 156

};

var projectionFromClass = new

{

personObject.FirstName,

personObject.LastName

};

In this case, the object projectionFromClass will have an anonymous type that picks up the property names FirstName and LastName, which will hold the values “Jane” and “Doe”.

Usage rules/restrictions

The properties in Anonymous types are all read only and therefore cannot be modified once they are created.

Anonymous types cannot have methods.

Anonymous types are always assigned to vars. This allows the compiler to assign the right type. But, if Anonymous types are used as return values or as parameters in a function, they will have to be passed in as Objects, as var is not a proper type.

Asp.net Saving Image in binary into database table in Sql 2005/2008

2012-02-14T15:35:00.000+05:30

In this article, I am going to explain how to store image file in database. for this I am using Northwind database and product table.

In this implementation, I have added a column ProductImage of image datatype for storing the image of each product.

ALTER TABLE Products ADD ProductImage IMAGE

Html Code in aspx part

The code is very simple - the web page contains a text box for getting the Product Id of the record and a file upload control in the page and A button.

<h2>

Save Image in Database

</h2>

<div>

<table>

<tr>

<td style="vertical-align:top">Product ID</td>

</tr>

<tr>

<td>Image</td>

</tr>

<tr>

<asp:Button ID="btnSave" runat="server" Width="80px" Text="Save"

onclick="btnSave_Click" />

</td>

</tr>

</table>

</div>

On click of Save button on the page, below code executed

protected void btnSave_Click(object sender, EventArgs e)

{

if ((fuImage.PostedFile.FileName.Trim().Length > 0) &&

(fuImage.PostedFile != null))

{

byte[] image = new byte[fuImage.PostedFile.ContentLength];

fuImage.PostedFile.InputStream.Read(image, 0, (int)fuImage.PostedFile.ContentLength);

SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLConnection"].ConnectionString);

SqlCommand command = new SqlCommand();

command.CommandText = "Update Products set ProductImage = @Image where ProductID = '" + txtProductID.Text + "'";

command.CommandType = CommandType.Text;

command.Connection = connection;

PrepareSQLParameter(command, "@Image", SqlDbType.Image, image.Length, image);

connection.Open();

int result = command.ExecuteNonQuery();

connection.Close();

txtProductID.Text = "";

}

private SqlParameter PrepareSQLParameter(SqlCommand command, string parameterName, SqlDbType parameterType, int parameterLength, object parameterValue)

{

SqlParameter parameter = new SqlParameter(parameterName, parameterType, parameterLength);

parameter.Value = parameterValue;

command.Parameters.Add(parameter);

return parameter;

}

New Fearure in .net C-sharp4.0

2012-02-09T15:44:00.000+05:30

What's new in .net 4.0 c#

Overall there are four main features that are introduced in the upcoming C# 4.0:

1. Dynamic Typed Objects

2. Optional and Named Parameters

3. Improved COM Interoperability

4. Co- and Contra-Variance

1. Dynamic Typed Objects

After the introduction of "var" in C# 3.5, the use of anonymous types increased rapidly. But as C# doesn't truly supporting the dynamic types, which means the types will be determined during the runtime and any error that is produced will not affect compilation of the project, there were lots of limitations. var is determined during compile time, and it is implicitly typed variable so it can’t be a return type of a method. Dynamic on the other hand can be a part of return type or argument of a method and will act during runtime when actual object is set to it.

The dynamic keyword is new to C# 4.0, and is used to tell the compiler that a variable's type can change or that it is not known until runtime. Think of it as being able to interact with an Object without having to cast it.

dynamic cust = GetCustomer();

cust.FirstName = "foo"; // works as expected

cust.Process(); // works as expected

cust.MissingMethod(); // No method found!

Notice we did not need to cast nor declare cust as type Customer. Because we declared it dynamic, the runtime takes over and then searches and sets the FirstName property for us. Now, of course, when you are using a dynamic variable, you are giving up compiler type checking. This means the call cust.MissingMethod() will compile and not fail until runtime. The result of this operation is a RuntimeBinderException because MissingMethod is not defined on the Customer class.

The example above shows how dynamic works when calling methods and properties. Another powerful (and potentially dangerous) feature is being able to reuse variables for different types of data. I'm sure the Python, Ruby, and Perl programmers out there can think of a million ways to take advantage of this, but I've been using C# so long that it just feels "wrong" to me.

dynamic foo = 123;

foo = "bar";

OK, so you most likely will not be writing code like the above very often. There may be times, however, when variable reuse can come in handy or clean up a dirty piece of legacy code. One simple case I run into often is constantly having to cast between decimal and double.

decimal foo = GetDecimalValue();

foo = foo / 2.5; // Does not compile

foo = Math.Sqrt(foo); // Does not compile

string bar = foo.ToString("c");

The second line does not compile because 2.5 is typed as a double and line 3 does not compile because Math.Sqrt expects a double. Obviously, all you have to do is cast and/or change your variable type, but there may be situations where dynamic makes sense to use.

dynamic foo = GetDecimalValue(); // still returns a decimal

foo = foo / 2.5; // The runtime takes care of this for us

foo = Math.Sqrt(foo); // Again, the DLR works its magic

string bar = foo.ToString("c");

Update

After some great questions and feedback, I realized I need to clarify a couple points I made above. When you use the dynamic keyword, you are invoking the new Dynamic Language Runtime libraries (DLR) in the .NET framework. There is plenty of information about the DLR out there, and I am not covering it in this article. Also, when possible, you should always cast your objects and take advantage of type checking. The examples above were meant to show how dynamic works and how you can create an example to test it. Over time, I'm sure best practices will emerge; I am making no attempt to create recommendations on the use of the DLR or dynamic.

Also, since publishing the initial version of this article, I have learned that if the object you declared as dynamic is a plain CLR object, Reflection will be used to locate members and not the DLR. Again, I am not attempting to make a deep dive into this subject, so please check other information sources if this interests you.

Switching Between Static and Dynamic

It should be apparent that 'switching' an object from being statically typed to dynamic is easy. After all, how hard is it to 'lose' information? Well, it turns out that going from dynamic to static is just as easy.

Customer cust = new Customer();

dynamic dynCust = cust; // static to dynamic, easy enough

dynCust.FirstName = "foo";

Customer newCustRef = dynCust; // Works because dynCust is a Customer

Person person = dynCust; // works because Customer inherits from Person

SalesRep rep = dynCust; // throws RuntimeBinderException exception

Note that in the example above, no matter how many different ways we reference it, we only have one Customer object (cust).

Functions

When you return something from a dynamic function call, indexer, etc., the result is always dynamic. Note that you can, of course, cast the result to a known type, but the object still starts out dynamic.

dynamic cust = GetCustomer();

string first = cust.FirstName; // conversion occurs

dynamic id = cust.CustomerId; // no conversion

object last = cust.LastName; //conversion occurs

There are, of course, a few missing features when it comes to dynamic types. Among them are:

Extension methods are not supported

Anonymous functions cannot be used as parameters

We will have to wait for the final version to see what other features get added or removed.

2. Named and Optional Parameters

We'll start off with one of the easier features to explain. In fact, if you have ever used Visual Basic, then you are probably already familiar with it.

Optional Parameters

Support for optional parameters allows you to give a method parameter a default value so that you do not have to specify it every time you call the method. This comes in handy when you have overloaded methods that are chained together.

The Old Way

public void Process( string data )

{

Process( data, false );

}

public void Process( string data, bool ignoreWS )

{

Process( data, ignoreWS, null );

}

public void Process( string data, bool ignoreWS, ArrayList moreData )

{

// Actual work done here

}

The reason for overloading Process in this way is to avoid always having to include "false, null" in the third method call. Suppose 99% of the time there will not be 'moreData' provided. It seems ridiculous to type and pass null so many times.

// These 3 calls are equivalent

Process( "foo", false, null );

Process( "foo", false );

Process( "foo" );

The New Way

public void Process( string data, bool ignoreWS = false, ArrayList moreData = null )

{

// Actual work done here

}

// Note: data must always be provided because it does not have a default value

Now we have one method instead of three, but the three ways we called Process above are still valid and still equivalent.

Process( "foo" ); // valid

Process( "foo", true ); // valid

Process( "foo", false, myArrayList ); // valid

Process( "foo", myArrayList ); // Invalid! See next section

Awesome, one less thing VB programmers can brag about having to themselves. I haven't mentioned it up to this point, but Microsoft has explicitly declared that VB and C# will be "co-evolving" so the number of disparate features is guaranteed to shrink over time. I would like to think this will render the VB vs. C# question moot, but I'm sure people will still find a way to argue about it. ;-)

Named Parameters

In the last example, we saw that the following call was invalid:

Process( "foo", myArrayList ); // Invalid!

But if the boolean ignoreWS is optional, why can't we just omit it? Well, one reason is for readability and maintainability, but primarily because it can become impossible to know what parameter you are specifying. If you had two parameters of the same type, or if one of the parameters was "object" or some other base class or interface, the compiler would not know which parameter you are sending. Imagine a method with ten optional parameters and you give it a single ArrayList. Since an ArrayList is also an object, an IList, and an IEnumerable, it is impossible to determine how to use it. Yes, the compiler could just pick the first valid option for each parameter (or a more complex system could be used), but this would become impossible for people to maintain and would cause countless programming mistakes.

Named parameters provide the solution:

ArrayList myArrayList = new ArrayList();

Process( "foo", true ); // valid, moreData omitted

Process( "foo", true, myArrayList ); // valid

Process( "foo", moreData: myArrayList); // valid, ignoreWS omitted

Process( "foo", moreData: myArrayList, ignoreWS: false ); // valid, but silly

As long as a parameter has a default value, it can be omitted, and you can just supply the parameters you want via their name. Note in the second line above, the 'true' value for ignoreWS did not have to be named since it is the next logical parameter.

3. COM Interop

This is by far the area in which I have the least experience; however, I'm sure we have all had to interact with Microsoft Office at one point and make calls like this:

// Code simplified for this example

using Microsoft.Office.Interop;

using Microsoft.Office.Interop.Word;

object foo = "MyFile.txt";

object bar = Missing.Value;

object optional = Missing.Value;

Document doc = (Document)Application.GetDocument(ref foo, ref bar, ref optional);

doc.CheckSpelling(ref optional, ref optional, ref optional, ref optional);

There are (at least) three problems with the code above. First, you have to declare all your variables as objects and pass them with the ref keyword. Second, you can't omit parameters and must also pass the Missing.Value even if you are not using the parameter. And third, behind the scenes, you are using huge (in file size) interop assemblies just to make one method call.

C# 4.0 will allow you to write the code above in a much simpler form that ends up looking almost exactly like 'normal' C# code. This is accomplished by using some of the features already discussed; namely dynamic support and optional parameters.

// Again, simplified for example.

using Microsoft.Office.Interop.Word;

var doc = Application.GetDocument("MyFile.txt");

doc.CheckSpelling();

What will also happen behind the scenes is that the interop assembly that is generated will only include the interop code you are actually using in your application. This will cut down on application size tremendously. My apologies in advance for this weak COM example, but I hope it got the point across.

4. Variance

Is the following legal in .NET?

// Example stolen from the whitepaper

IList<string> strings = new List<string>();

IList<object> objects = strings;

I think most of us, at first, would answer 'yes' because a string is an object. But the question we should be asking ourselves is: Is a -list- of strings a -list- of objects? To take it further: Is a -strongly typed- list of strings a -strongly typed- list of objects? When phrased that way, it's easier to understand why the answer to the question is 'no'. If the above example was legal, that means the following line would compile:

objects.Add(123);

Oops, we just inserted the integer value 123 into a List<string>. Remember, the list contents were never copied; we simply have two references to the same list. There is a case, however, when casting the list, this should be allowed. If the list is read-only, then we should be allowed to view the contents any (type legal) way we want.

Co and Contra Variance

From Wikipedia:

Within the type system of a programming language, a type conversion operator is:

covariant if it preserves the ordering, =, of types, which orders types from more specific to more generic;

contravariant if it reverses this ordering, which orders types from more generic to more specific;

invariant if neither of these apply.

C# is, of course, covariant, meaning a Customer is a Person and can always be referenced as one. There are lots of discussions on this topic, and I will not cover it here. The changes in C# 4.0 only involve typed (generic) interfaces and delegates in situations like in the example above. In order to support co and contra variance, typed interfaces are going to be given 'input' and 'output' sides. So, to make the example above legal, IList must be declared in the following manner:

public interface IList<out T> : ICollection<T>, IEnumerable<T>, IEnumerable

{

...

}

Notice the use of the out keyword. This is essentially saying the IList is readonly and it is safe to refer to a List<string> as a List<object>. Now, of course, IList is not going to be defined this way; it must support having items added to it. A better example to consider is IEnumerable which should be, and is, readonly.

public interface IEnumerable<out T> : IEnumerable

{

IEnumerator<T> GetEnumerator();

}

Using out to basically mean 'read only' is straightforward, but when does using the in keyword to make something 'write only' useful? Well, it actually becomes useful in situations where a generic argument is expected and only used internally by the method. IComparer is the canonical example.

public interface IComparer<in T>

{

public int Compare(T left, T right);

}

As you can see, we can't get back an item of type T. Even though the Compare method could potentially act on the left and right arguments, it is kept within the method so it is a 'black hole' to clients that use the interface.

To continue the example above, this means that an IComparer<object> can be used in the place of an IComparer<string>. The C# 4.0 whitepaper sums the reason up nicely: 'If a comparer can compare any two objects, it can certainly also compare two strings'. This is counter-intuitive (or maybe contra-intuitive) because if a method expects a string, you can't give it an object.

Putting it Together

OK, comparing strings and objects is great, but I think a somewhat realistic example might help clarify how the new variance keywords are used. This first example demonstrates the effects of the redefined IEnumerable interface in C# 4.0. In .NET 3.5, line 3 below does not compile with an the error: 'can not convert List<Customer> to List<Person>'. As stated above, this seems 'wrong' because a Customer is a Person. In .NET 4.0, however, this exact same code compiles without any changes because IEnumerable is now defined with the out modifier.

MyInterface<Customer> customers = new MyClass<Customer>();

List<Person> people = new List<Person>();

people.AddRange(customers.GetAllTs()); // no in 3.5, yes in 4.0

people.Add(customers.GetAllTs()[0]); // yes in both

...

interface MyInterface<T>

{

List<T> GetAllTs();

}

public class MyClass<T> : MyInterface<T>

{

public List<T> GetAllTs()

{

return _data;

}

private List<T> _data = new List<T>();

}

This next example demonstrates how you can take advantage of the out keyword. In .NET 3.5, line 3 compiles, but line 4 does not with the same 'cannot convert' error. To make this work in .NET 4.0, simply change the declaration of MyInterface to interface MyInterface<out T>. Notice that in line 4, T is Person, but we are passing the Customer version of the class and interface.

MyInterface<Person> people = new MyClass<Person>();

MyInterface<Customer> customers = new MyClass<Customer>();

FooClass<Person>.GetThirdItem(people);

FooClass<Person>.GetThirdItem(customers);

...

public class FooClass<T>

{

public static T GetThirdItem(MyInterface<T> foo)

{

return foo.GetItemAt(2);

}

public interface MyInterface<out T>

{

T GetItemAt(int index);

}

public class MyClass<T> : MyInterface<T>

{

public T GetItemAt(int index)

{

return _data[index];

}

private List<T> _data = new List<T>();

}

This final example demonstrates the wacky logic of contravariance. Notice that we put a SalesRep 'inside' our Person interface. This isn't a problem because a SalesRep is a Person. Where it gets interesting is when we pass the MyInterface<Person> to FooClass<Customer>. In essence, we have 'inserted' a SalesRep into an interface declared to work with only Customers! In .NET 3.5, line 5 does not compile; as expected. By adding the in keyword to our interface declaration in .NET 4.0, everything works fine because we are 'agreeing' to treat everything as a Person internally and not expose the internal data (which might be that SalesRep).

MyInterface<Customer> customer = new MyClass<Customer>();

MyInterface<Person> person = new MyClass<Person>();

person.SetItem(new SalesRep());

FooClass<Customer>.Process(customer);

FooClass<Customer>.Process(person);

...

public class FooClass<T>

{

public static void Process(MyInterface<T> obj)

{

}

public interface MyInterface<in T>

{

void SetItem(T obj);

void Copy(T obj);

}

public class MyClass<T> : MyInterface<T>

{

public void SetItem(T obj)

{

_item = obj;

}

private T _item;

public void Copy(T obj)

{

}

Asp.net export GridView selected columns to Excel with style applied

2012-02-06T15:31:00.000+05:30

ASP.Net GridView Export only selected columns to Excel Sheet

In this article I will describe how to export only those columns in ASP.Net GridView control that are selected or checked by the user.

Database

I am using Microsoft’s Northwind Sample Database for this article. You can download the same using the link below

Download Northwind Database

HTML Markup:

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="ExportToExcel.aspx.cs" enableEventValidation ="false" Inherits="ExcelApplication.ExportToExcel" ValidateRequest = "false"%>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" Font-Names="Arial"
            Font-Size="11pt" AlternatingRowStyle-BackColor="#C2D69B" HeaderStyle-BackColor="green"
            AllowPaging="true" OnPageIndexChanging="OnPaging">
            <Columns>
                <asp:TemplateField>
                    <HeaderTemplate>
                        <asp:CheckBox ID="chkCol0" runat="server" Checked="true" />
                        <asp:Label ID="lblCol0" runat="server" Text="CustomerID" />
                    </HeaderTemplate>
                    <ItemTemplate>
                        <asp:Label ID="lblCustomerID" runat="server" Text='<%# Eval("CustomerID")%>' />
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField>
                    <HeaderTemplate>
                        <asp:CheckBox ID="chkCol1" runat="server" Checked="true" />
                        <asp:Label ID="lblCol1" runat="server" Text="ContactName" />
                    </HeaderTemplate>
                    <ItemTemplate>
                        <asp:Label ID="lblContactName" runat="server" Text='<%# Eval("ContactName")%>' />
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField>
                    <HeaderTemplate>
                        <asp:CheckBox ID="chkCol2" runat="server" Checked="true" />
                        <asp:Label ID="lblCol2" runat="server" Text="City"></asp:Label>
                    </HeaderTemplate>
                    <ItemTemplate>
                        <asp:Label ID="lblCity" runat="server" Text='<%# Eval("City")%>' />
                    </ItemTemplate>
                </asp:TemplateField>
            </Columns>
        </asp:GridView></div>
        <br />
        <asp:Button ID="Button1" runat="server" onclick="Button1_Click"
            Text="Export To Excel" />

    </form>
</body>
</html>

Designer File :

//------------------------------------------------------------------------------
// <auto-generated>
//     This code was generated by a tool.
//
//     Changes to this file may cause incorrect behavior and will be lost if
//     the code is regenerated.
// </auto-generated>
//------------------------------------------------------------------------------

namespace ExcelApplication {


    public partial class ExportToExcel {

        /// <summary>
        /// form1 control.
        /// </summary>
        /// <remarks>
        /// Auto-generated field.
        /// To modify move field declaration from designer file to code-behind file.
        /// </remarks>
        protected global::System.Web.UI.HtmlControls.HtmlForm form1;

        /// <summary>
        /// GridView1 control.
        /// </summary>
        /// <remarks>
        /// Auto-generated field.
        /// To modify move field declaration from designer file to code-behind file.
        /// </remarks>
        protected global::System.Web.UI.WebControls.GridView GridView1;

        /// <summary>
        /// Button1 control.
        /// </summary>
        /// <remarks>
        /// Auto-generated field.
        /// To modify move field declaration from designer file to code-behind file.
        /// </remarks>
        protected global::System.Web.UI.WebControls.Button Button1;
    }
}
Code Behind:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Configuration;

namespace ExcelApplication
{
    public partial class ExportToExcel : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (IsPostBack)
                GetCheckBoxStates();

            BindGrid();
        }

        //Exporting the GridView with selected columns to Excel Sheet
        protected void Button1_Click(object sender, EventArgs e)
        {
            Response.Clear();
            Response.Buffer = true;
            Response.AddHeader("content-disposition","attachment;filename=GridViewExport.xls");
            Response.Charset = "";
            Response.ContentType = "application/vnd.ms-excel";
            StringWriter sw = new StringWriter();
            HtmlTextWriter hw = new HtmlTextWriter(sw);
            GridView1.AllowPaging = false;
            GridView1.DataBind();

            GridView1.HeaderRow.Style.Add("background-color", "#FFFFFF");
            GridView1.HeaderRow.Cells[0].Style.Add("background-color", "green");
            GridView1.HeaderRow.Cells[1].Style.Add("background-color", "green");
            GridView1.HeaderRow.Cells[2].Style.Add("background-color", "green");

            ArrayList arr = (ArrayList)ViewState["States"];
            GridView1.HeaderRow.Cells[0].Visible = Convert.ToBoolean(arr[0]);
            GridView1.HeaderRow.Cells[1].Visible = Convert.ToBoolean(arr[1]);
            GridView1.HeaderRow.Cells[2].Visible = Convert.ToBoolean(arr[2]);

            GridView1.HeaderRow.Cells[0].FindControl("chkCol0").Visible = false;
            GridView1.HeaderRow.Cells[1].FindControl("chkCol1").Visible = false;
            GridView1.HeaderRow.Cells[2].FindControl("chkCol2").Visible = false;

            for (int i = 0; i < GridView1.Rows.Count; i++)
            {
                GridViewRow row = GridView1.Rows[i];
                row.Cells[0].Visible = Convert.ToBoolean(arr[0]);
                row.Cells[1].Visible = Convert.ToBoolean(arr[1]);
                row.Cells[2].Visible = Convert.ToBoolean(arr[2]);
                row.BackColor = System.Drawing.Color.White;
                row.Attributes.Add("class", "textmode");

                if (i % 2 != 0)
                {
                    row.Cells[0].Style.Add("background-color", "#C2D69B");
                    row.Cells[1].Style.Add("background-color", "#C2D69B");
                    row.Cells[2].Style.Add("background-color", "#C2D69B");
                }
            }

            GridView1.RenderControl(hw);

            string style = @"<style> .textmode { mso-number-format:\@; } </style>";
            Response.Write(style);
            Response.Output.Write(sw.ToString());
            Response.End();
        }

        public override void VerifyRenderingInServerForm(Control control)
        {

            /* Verifies that the control is rendered */

        }

        //You’ll notice I am calling one more function GetCheckBoxStates() in the Page Load event of the Page.
        //The job of this function is to maintain the states of the GridView header checkboxes in ViewState.
        //The function is described below
        private void GetCheckBoxStates()
        {

            CheckBox chkCol0 = (CheckBox)GridView1.HeaderRow.Cells[0].FindControl("chkCol0");
            CheckBox chkCol1 = (CheckBox)GridView1.HeaderRow.Cells[0].FindControl("chkCol1");
            CheckBox chkCol2 = (CheckBox)GridView1.HeaderRow.Cells[0].FindControl("chkCol2");

            ArrayList arr;

            if (ViewState["States"] == null)
            {
                arr = new ArrayList();
            }
            else
            {
                arr = (ArrayList)ViewState["States"];
            }

            arr.Add(chkCol0.Checked);
            arr.Add(chkCol1.Checked);
            arr.Add(chkCol2.Checked);
            ViewState["States"] = arr;
        }

        //Binding Data
        private void BindGrid()
        {

            string strQuery = "select CustomerID,City,ContactName from customers";
            DataTable dt = new DataTable();
            String strConnString = ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString;
            SqlConnection con = new SqlConnection(strConnString);
            SqlDataAdapter sda = new SqlDataAdapter();
            SqlCommand cmd = new SqlCommand(strQuery);
            cmd.CommandType = CommandType.Text;
            cmd.Connection = con;

            try
            {
                con.Open();
                sda.SelectCommand = cmd;
                sda.Fill(dt);
                GridView1.DataSource = dt;
                GridView1.DataBind();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
                sda.Dispose();
                con.Dispose();
            }
        }

        //Call when page index changed
        protected void OnPaging(object sender, GridViewPageEventArgs e)
        {
            GridView1.PageIndex = e.NewPageIndex;
            BindGrid();
        }

    }
}

As you’ll notice above everything is same as the normal GridView to Excel export the only difference is that I am hiding the GridView cells based on the values of their respective checkboxes in the Header row

ASP.Net GridView with ability to select/unselect columns

Exported Excel sheet with selected columns

Hope this will help you.......

Ref: try this link

Search a keyword in Sql2005/2008 in all tables

2012-02-03T13:17:00.001+05:30

How to search all columns of all tables in a database for a keyword?

While browsing the SQL Server newsgroups, every once in a while, I see a request for a script that can search all the columns of all the tables in a given database for a specific keyword. I never took such posts seriously. But then recently, one of my network administrators was troubleshooting a problem with Microsoft Operations Manager (MOM). MOM uses SQL Server for storing all the computer, alert and performance related information. He narrowed the problem down to something specific, and needed a script that can search all the MOM tables for a specific string. I had no such script handy at that time, so we ended up searching manually.

That's when I really felt the need for such a script and came up with this stored procedure "SearchAllTables". It accepts a search string as input parameter, goes and searches all char, varchar, nchar, nvarchar columns of all tables (only user created tables. System tables are excluded), owned by all users in the current database. Feel free to extend this procedure to search other datatypes.

The output of this stored procedure contains two columns:

- 1) The table name and column name in which the search string was found
- 2) The actual content/value of the column (Only the first 3630 characters are displayed)

Here's a word of caution, before you go ahead and run this procedure. Though this procedure is quite quick on smaller databases, it could take hours to complete, on a large database with too many character columns and a huge number of rows. So, if you are trying to run it on a large database, be prepared to wait (I did use the locking hint NOLOCK to reduce any locking). It is efficient to use Full-Text search feature for free text searching, but it doesn't make sense for this type of ad-hoc requirements.

Create this procedure in the required database and here is how you run it:

--To search all columns of all tables in Pubs database for the keyword "Computer"
EXEC SearchAllTables 'Computer'
GO

Here is the complete stored procedure code:


CREATE PROC SearchAllTables
(
 @SearchStr nvarchar(100)
)
AS
BEGIN




 CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

 SET NOCOUNT ON

 DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
 SET  @TableName = ''
 SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

 WHILE @TableName IS NOT NULL
 BEGIN
  SET @ColumnName = ''
  SET @TableName = 
  (
   SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
   FROM  INFORMATION_SCHEMA.TABLES
   WHERE   TABLE_TYPE = 'BASE TABLE'
    AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
    AND OBJECTPROPERTY(
      OBJECT_ID(
       QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
        ), 'IsMSShipped'
             ) = 0
  )

  WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
  BEGIN
   SET @ColumnName =
   (
    SELECT MIN(QUOTENAME(COLUMN_NAME))
    FROM  INFORMATION_SCHEMA.COLUMNS
    WHERE   TABLE_SCHEMA = PARSENAME(@TableName, 2)
     AND TABLE_NAME = PARSENAME(@TableName, 1)
     AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
     AND QUOTENAME(COLUMN_NAME) > @ColumnName
   )
 
   IF @ColumnName IS NOT NULL
   BEGIN
    INSERT INTO #Results
    EXEC
    (
     'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
     FROM ' + @TableName + ' (NOLOCK) ' +
     ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
    )
   END
  END 
 END

 SELECT ColumnName, ColumnValue FROM #Results
END

ref:-by check this

Interview Question and Answer on OOPs in C#

2012-02-02T12:44:00.000+05:30

OOPs Interview Questions

Class

A user-defined data structure that groups properties and methods. Class doesn’t occupy memory.

Object

Instance of Class is called object. An object is created in memory using keyword “new”.

Difference between Struct and Class

Struct are Value type and are stored on stack, while Class are Reference type and are stored on heap.
Struct “do not support” inheritance, while class supports inheritance. However struct can implements interface.
Struct should be used when you want to use a small data structure, while Class is better choice for complex data structure.

What is the difference between instantiating structures with and without using the new keyword?
When a structure is instantiated using the new keyword, a constructor (no-argument or custom, if provided) is called which initializes the fields in the structure. When a structure is instantiated without using the new keyword, no constructor is called. Hence, one has to explicitly initialize all the fields of the structure before using it when instantiated without the new keyword.

ABSTRACTION:-

Abstraction defines way to abstract or hide your data and members from outside world. Simply speaking Abstraction is hiding the complexities of your class or struct or in a generic term Type from outer world. This is achieved by means of access specifiers.
In short : - Hiding the complexities of your type from outside world.
How to Abstract: - By using Access Specifiers
.Net has five access specifiers:-
Public -- Accessible outside the class through object reference.
Private -- Accessible inside the class only through member functions.
Protected -- Just like private but Accessible in derived classes also through member functions.
Internal -- Visible inside the assembly. Accessible through objects.
Protected Internal -- Visible inside the assembly through objects and in derived classes outside the assembly through member functions.
Let’s try to understand by a practical example:-
Interview Tip:-
The default access specifier for a class in internal. Mind it I mean class not class’s data members.

Encapsulation

Wrapping up of data and function into a single unit is known as Encapsulation.

Properties

Attribute of object is called properties. Eg1:- A car has color as property.

Eg2:

private string m_Color;;

public string Color

{
get

{

return m_Color;

}

set

{

m_Color = value;

}

Car Maruti = new Car();

Maruti.Color= “White”;

Console.Write(Maruti.Color);

Isn't it better to make a field public than providing its property with both set { } and get { } block? After all the property will allow the user to both read and modify the field so why not use public field instead? Motivate your answer

not always! Properties are not just to provide access to the fields; rather, they are supposed to provide controlled access to the fields of our class. As the state of the class depends upon the values of its fields, using properties we can assure that no invalid (or unacceptable) value is assigned to the fields.

Eg:

private int age;

public int Age

{

get

{

return age;

}

set

{

if(value <> 100)

//throw exception

else

age = value;

}

this Keyword

Each object has a reference “this” which points to itself.

Two uses of this keyword.

o Can be used to refer to the current object.

o It can also be used by one constructor to explicitly invoke another constructor of the same class.

Eg1:

class Student

{

private string name;

private int age;

Student(string name, int age)

{

this.name = name;

this.age = age;

}

Eg2:

class Circle

{

double x,y,radius;

Circle(double x){

this(x,0,1);

}

Circle(double x, double y){

this(x,y,1);

}

Circle(double x, double y, double radius){

this.x = x;

this.y = y;

this.radius = radius;

}

Constructor

A constructor is a special method whose task is to initialize the object of its class.
It is special because its name is the same as the class name.
They do not have return types, not even void and therefore they cannot return values.
They cannot be inherited, though a derived class can call the base class constructor.
Constructor is invoked whenever an object of its associated class is created.
Note: There is always at least one constructor in every class. If you do not write a constructor, C# automatically provides one for you, this is called default constructor. Eg: class A, default constructor is A().

Static Members of the class

Static members belong to the whole class rather than to individual object

Static members are accessed with the name of class rather than reference to objects.

Eg:

class Test

{

public int rollNo;

public int mathsMarks;

public static int totalMathMarks;

}

class TestDemo

{

public static void main()

{

Test stud1 = new Test();

stud1.rollNo = 1;

stud1.mathsMarks = 40;

stud2.rollNo = 2;

stud2.mathsMarks = 43;

Test.totalMathsMarks = stud1.mathsMarks + stud2.mathsMarks;

}

Static Method of the class

Methods that you can call directly without first creating an instance of a class. Eg: Main() Method, Console.WriteLine()

You can use static fields, methods, properties and even constructors which will be called before any instance of the class is created.

As static methods may be called without any reference to object, you cannot use instance members inside static methods or properties, while you may call a static member from a non-static context. The reason for being able to call static members from non-static context is that static members belong to the class and are present irrespective of the existence of even a single object.

Static Constructor

In C# it is possible to write a static no-parameter constructor for a class. Such a class is executed once, when first object of class is created.

One reason for writing a static constructor would be if your class has some static fields or properties that need to be initialized from an external source before the class is first used.

Eg:

Class MyClass

{

static MyClass()

{

//Initialization Code for static fields and properties.

}

Finalize() Method of Object classEach class in C# is automatically (implicitly) inherited from the Object class which contains a method Finalize(). This method is guaranteed to be called when your object is garbage collected (removed from memory). You can override this method and put here code for freeing resources that you reserved when using the object.

For example

Protected override void Finalize()

{

try

{

Console.WriteLine(“Destructing Object….”);

//put some code here.

}

finally

{

base.Finalize();

}

Destructor

A destructor is just opposite to constructor.

It has same as the class name, but with prefix ~ (tilde).

They do not have return types, not even void and therefore they cannot return values.

Destructor is invoked whenever an object is about to be garbage collected

Eg:

class person

{

//constructor

person()

{

}

//destructor

~person()

{

//put resource freeing code here.

}

What is the difference between the destructor and the Finalize() method? When does the Finalize() method get called?
Finalize() corresponds to the .Net Framework and is part of the System.Object class. Destructors are C#'s implementation of the Finalize() method. The functionality of both Finalize() and the destructor is the same, i.e., they contain code for freeing the resources when the object is about to be garbage collected. In C#, destructors are converted to the Finalize() method when the program is compiled. The Finalize() method is called by the .Net Runtime and we cannot predict when it will be called. It is guaranteed to be called when there is no reference pointing to the object and the object is about to be garbage collected.

Garbage Collection

Garbage collection is the mechanism that reclaims the memory resources of an object when it is no longer referenced by a variable.
.Net Runtime performs automatically performs garbage collection, however you can force the garbage collection to run at a certain point in your code by callingSystem.GC.Collect().
Advantage of Garbage collection: It prevents programming error that could otherwise occur by incorrectly deleting or failing to delete objects.

Enumeration

Enumeration improves code readability. It also helps in avoiding typing mistake.

Concept of Heap and Stack

The Program Instruction and Global and Static variables are stored in a region known as permanent storage area and the local variables are stored in another area called stack. The memory space located between these two regions is available for dynamic memory allocation during execution of program. This free memory region is called heap. The size of heap keeps on changing when program is executed due to creation and death of variables that are local to functions and blocks. Therefore, it is possible to encounter memory “overflow” during dynamic allocation process.

Value Type and Reference Type

A variable is value type or reference type is solely determined by its data type.

Eg: int, float, char, decimal, bool, decimal, struct, etc are value types, while object type such as class, String, Array, etc are reference type.

Value Type

As name suggest Value Type stores “value” directly.

For eg:

//I and J are both of type int

I = 20;

J = I;

int is a value type, which means that the above statements will results in two locations in memory.

For each instance of value type separate memory is allocated.

Stored in a Stack.

It Provides Quick Access, because of value located on stack.

Reference Type

As name suggest Reference Type stores “reference” to the value.

For eg:

Vector X, Y; //Object is defined. (No memory is allocated.)

X = new Vector(); //Memory is allocated to Object. //(new is responsible for allocating memory.)

X.value = 30; //Initialising value field in a vector class.

Y = X; //Both X and Y points to same memory location. //No memory is created for Y.

Console.writeline(Y.value); //displays 30, as both points to same memory

Y.value = 50;

Console.writeline(X.value); //displays 50.

Note: If a variable is reference it is possible to indicate that it does not refer to any object by setting its value to null;

Reference type are stored on Heap.

It provides comparatively slower access, as value located on heap.

ref keyword

Passing variables by value is the default. However, we can force the value parameter to be passed by reference. Note: variable “must” be initialized before it is passed into a method.

out keyword

out keyword is used for passing a variable for output purpose. It has same concept as ref keyword, but passing a ref parameter needs variable to be initialized while out parameter is passed without initialized.

It is useful when we want to return more than one value from the method.

Note: You must assigned value to out parameter in method body, otherwise the method won’t compiled.

Boxing and Un-Boxing

Boxing: means converting value-type to reference-type.

Eg:

int I = 20;

string s = I.ToSting();

UnBoxing: means converting reference-type to value-type.

Eg:

int I = 20;

string s = I.ToString(); //Box the int

int J = Convert.ToInt32(s); //UnBox it back to an int.

Note: Performance Overheads due to boxing and unboxing as the boxing makes a copy of value type from stack and place it inside an object of type System.Object in the heap.

Inheritance

The process of sub-classing a class to extend its functionality is called Inheritance.

It provides idea of reusability.

Order of Constructor execution in Inheritance

constructors are called in the order from the top to the bottom (parent to child class) in inheritance hierarchy.

Order of Destructor execution in Inheritance

The destructors are called in the reverse order, i.e., from the bottom to the top (child to parent class) in the inheritance hierarchy.

What are Sealed Classes in C#?The sealed modifier is used to prevent derivation from a class. A compile-time error occurs if a sealed class is specified as the base class of another class. (A sealed class cannot also be an abstract class)

Can you prevent your class from being inherited by another class?Yes. The keyword “sealed” will prevent the class from being inherited.
Can you allow a class to be inherited, but prevent the method from being over-ridden?Yes. Just leave the class public and make the method sealed.

Fast Facts of Inheritance

Multiple inheritance of classes is not allowed in C#.

In C# you can implements more than one interface, thus multiple inheritance is achieved through interface.

The Object class defined in the System namespace is implicitly the ultimate base class of all the classes in C# (and the .NET framework)

Structures (struct) in C# does not support inheritance, it can only implements interfaces.

Polymorphism

Polymorphism means same operation may behave differently on different classes.

Eg:

Method Overloading is an example of Compile Time Polymorphism.

Method Overriding is an example of Run Time Polymorphism

Does C#.net supports multiple inheritance?

No. A class can inherit from only one base class, however a class can implements many interface, which servers some of the same purpose without increasing complexity.

How many types of Access Modifiers.

1) Public – Allows the members to be globally accessible.

2) Private – Limits the member’s access to only the containing type.

3) Protected – Limits the member’s access to the containing type and all classes derived from the containing type.

4) Internal – Limits the member’s access to within the current project.

Method Overloading

Method with same name but with different arguments is called method overloading.

Method Overloading forms compile-time polymorphism.

Eg:

class A1

{

void hello()

{ Console.WriteLine(“Hello”); }

void hello(string s)

{ Console.WriteLine(“Hello {0}”,s); }

}

Method Overriding

Method overriding occurs when child class declares a method that has the same type arguments as a method declared by one of its superclass.

Method overriding forms Run-time polymorphism.

Note: By default functions are not virtual in C# and so you need to write “virtual” explicitly. While by default in Java each function are virtual.

Eg1:

Class parent

{

virtual void hello()

{ Console.WriteLine(“Hello from Parent”); }

}

Class child : parent

{

override void hello()

{ Console.WriteLine(“Hello from Child”); }

}

static void main()

{

parent objParent = new child();

objParent.hello();

}

//Output

Hello from Child.

Virtual Method

By declaring base class function as virtual, we allow the function to be overridden in any of derived class.

Eg:

Class parent

{

virtual void hello()

{ Console.WriteLine(“Hello from Parent”); }

}

Class child : parent

{

override void hello()

{ Console.WriteLine(“Hello from Child”); }

}

static void main()

{

parent objParent = new child();

objParent.hello();

}

//Output

Hello from Child.

Concept of Interface

What is Interface

An Interface is a group of constants and method declaration.
.Net supports multiple inheritance through Interface.
Interface states “what” to do, rather than “how” to do.
An interface defines only the members that will be made available by an implementing object. The definition of the interface states nothing about the implementation of the members, only the parameters they take and the types of values they will return. Implementation of an interface is left entirely to the implementing class. It is possible, therefore, for different objects to provide dramatically different implementations of the same members.
Example1, the Car object might implement the IDrivable interface (by convention, interfaces usually begin with I), which specifies the GoForward, GoBackward, and Halt methods. Other classes, such as Truck, Aircraft, Train or Boat might implement this interface and thus are able to interact with the Driver object. The Driver object is unaware of which interface implementation it is interacting with; it is only aware of the interface itself.
Example2, an interface named IShape, which defines a single method CalculateArea. A Circle class implementing this interface will calculate its area differently than a Square class implementing the same interface. However, an object that needs to interact with an IShape can call the CalculateArea method in either a Circle or a Square and obtain a valid result.
Practical Example

public interface IDrivable
{
void GoForward(int Speed);
}

public class Truck : IDrivable
{
public void GoForward(int Speed)
{
// Implementation omitted
}
}

public class Aircraft : IDrivable
{
public void GoForward(int Speed)
{
// Implementation omitted
}
}

public class Train : IDrivable
{
public void GoForward(int Speed)
{
// Implementation omitted
}
}

Extra

Each variable declared in interface must be assigned a constant value.
Every interface variable is implicitly public, static and final.
Every interface method is implicitly public and abstract.
Interfaces are allowed to extends other interfaces, but sub interface cannot define the methods declared in the super interface, as sub interface is still interface and not class.
If a class that implements an interface does not implements all the methods of the interface, then the class becomes an abstract class and cannot be instantiated.
Both classes and structures can implement interfaces, including multiple interfaces.

Making choice between Interface and Abstract Class

In which Scenario you will go for Interface or Abstract Class?
Interfaces, like classes, define a set of properties, methods, and events. But unlike classes, interfaces
do not provide implementation. They are implemented by classes, and defined as separate entities from
classes. Even though class inheritance allows your classes to inherit implementation from a base class, it
also forces you to make most of your design decisions when the class is first published.

Abstract classes are useful when creating components because they allow you specify an invariant level
of functionality in some methods, but leave the implementation of other methods until a specific
implementation of that class is needed. They also version well, because if additional functionality is
needed in derived classes, it can be added to the base class without breaking code.

Difference between Interface and Abstract Class


Feature	Interface	Abstract class
Multiple inheritance	A class may implement several interfaces.	A class may extend only one abstract class.
Default implementation	An interface cannot provide any code at all, much less default code.	An abstract class can provide complete code, default code, and/or just stubs that have to be overridden.
Constants	Static final constants only, can use them without qualification in classes that implement the interface. On the other paw, these unqualified names pollute the namespace. You can use them and it is not obvious where they are coming from since the qualification is optional.	Both instance and static constants are possible. Both static and instance initialize code are also possible to compute the constants.
Third party convenience	An interface implementation may be added to any existing third party class.	A third party class must be rewritten to extend only from the abstract class.
Is-a vs -able or can-do	Interfaces are often used to describe the peripheral abilities of a class, not its central identity, e.g. an Automobile class might implement the Recyclable interface, which could apply to many otherwise totally unrelated objects.	An abstract class defines the core identity of its descendants. If you defined a Dog abstract class then Damamation descendants are Dogs, they are not merely dogable. Implemented interfaces enumerate the general things a class can do, not the things a class is.
Plug-in	You can write a new replacement module for an interface that contains not one stick of code in common with the existing implementations. When you implement the interface, you start from scratch without any default implementation. You have to obtain your tools from other classes; nothing comes with the interface other than a few constants. This gives you freedom to implement a radically different internal design.	You must use the abstract class as-is for the code base, with all its attendant baggage, good or bad. The abstract class author has imposed structure on you. Depending on the cleverness of the author of the abstract class, this may be good or bad. Another issue that's important is what I call "heterogeneous vs. homogeneous." If implementors/subclasses are homogeneous, tend towards an abstract base class. If they are heterogeneous, use an interface. (Now all I have to do is come up with a good definition of hetero/homogeneous in this context.) If the various objects are all of-a-kind, and share a common state and behavior, then tend towards a common base class. If all they share is a set of method signatures, then tend towards an interface.
Homogeneity	If all the various implementations share is the method signatures, then an interface works best.	If the various implementations are all of a kind and share a common status and behavior, usually an abstract class works best.
Maintenance	If your client code talks only in terms of an interface, you can easily change the concrete implementation behind it, using a factory method.	Just like an interface, if your client code talks only in terms of an abstract class, you can easily change the concrete implementation behind it, using a factory method.
Speed	Slow, requires extra indirection to find the corresponding method in the actual class. Modern JVMs are discovering ways to reduce this speed penalty.	Fast
Terseness	The constant declarations in an interface are all presumed public static final, so you may leave that part out. You can't call any methods to compute the initial values of your constants. You need not declare individual methods of an interface abstract. They are all presumed so.	You can put shared code into an abstract class, where you cannot into an interface. If interfaces want to share code, you will have to write other bubblegum to arrange that. You may use methods to compute the initial values of your constants and variables, both instance and static. You must declare all the individual methods of an abstract class abstract.
Adding functionality	If you add a new method to an interface, you must track down all implementations of that interface in the universe and provide them with a concrete implementation of that method.	If you add a new method to an abstract class, you have the option of providing a default implementation of it. Then all existing code will continue to work without change.

Cross domain useing of silverlight plugins on html page

2012-01-27T16:47:00.000+05:30

In this article I am going to show you how to bind or consume a silver light plugin or xap file hosted on some other domain on your html or aspx page

using the following code you can easly load a xap file on your html or php or aspx page like a flash file

<div>
        
        <object data="data:application/x-silverlight-2," type="application/x-silverlight-2" id="silverlight-plugin"
            style="height:300px; width: 300px;">
            <param name="source" value="http://203.190.133.56/silverlight/ClientBin/SilverlightApplication1.xap"/> url of the server where xap file is hosted
            <param name="windowless" value="true"/>

<param name="onError"
             value="onSilverlightError" />
      <param name="background"
             value="Black" />
      <param name="minRuntimeVersion"
             value="4.0.50524.0" />
      <param name="autoUpgrade"
             value="True" />
<param name="enablehtmlaccess" value="true" />

      
          <param name="initParams" value="MyParameter=123,Value=123abc," />


            <p>
                You need to install Microsoft Silverlight to view this content. <a href="http://go.microsoft.com/fwlink/?LinkID=149156&v=4.0.50401.0"
                    style="text-decoration: none;">Get Silverlight!<br />

<img src="http://go.microsoft.com/fwlink/?LinkID=108181" alt="Get Microsoft Silverlight"
                        style="border-style: none;" /></a></p>
        </object>
        <iframe style="visibility: hidden; height: 0; width: 0; border-width: 0;"></iframe>
    </div>

Issue facing while consuming a xap file

if you are using the above code and you are not able to bind the component only an area is highlighted of the above mentioned height and width

in my case  style="height:300px; width: 300px;" .

you should check the following thing

1) the url of the xap file is ok and when you paste in browser , it should start download like pdf or any other doc file.

if its not like that check for the url.

2) if the above line is ok check your firewall for that particular IP. it should not be block on your network.

3)You must have silverlight installed on your browser

if all the case are fine and still you are facing the same problem then problem is only with your hosting server. any googling is not going to help you out any more

B) if your component are loaded successfully and you are not able to use any jquey or javascript on your silver light component check the following line is added or not if not place the belo line inside your <object> tag

<param name="enablehtmlaccess" value="true" />

In my next article i will show how to code and deploy a xap file and solve the above issue of cross domain hosting

since then

Happy programming.

Pagination and sorting dynamically in SQLserver 2005/2008

2012-01-23T17:38:00.000+05:30

Hi this article show you how to do pagination and dynamically sorting the result ascending or descending from the selection list in sql 2005/2008

Create PROCEDURE [dbo].[usp_get_all_groups] 
    -- Add the parameters for the stored procedure here
    @pStartIndex smallint,
    @pPageSize tinyint,
    @pOrderBy varchar(15)
AS
BEGIN

 SELECT *
 FROM
  (SELECT ROW_NUMBER() OVER (ORDER BY 
      CASE WHEN @pOrderBy='GroupId ASC' THEN UserGroups._id END ASC,  
      CASE WHEN @pOrderBy='GroupId DESC' THEN UserGroups._id END DESC,             
      CASE WHEN @pOrderBy='GroupCode ASC' THEN UserGroups.GroupCode END ASC,
      CASE WHEN @pOrderBy='GroupCode DESC' THEN UserGroups.GroupCode END DESC) AS Row, 
      * FROM UserGroups) AS StudentsWithRowNumbers
  WHERE Row>= @pStartIndex AND Row <= @pStartIndex + @pPageSize
  ORDER BY Row      END

Here to execute the procedure 
DECLARE @return_value int
EXEC    @return_value = [dbo].[usp_get_all_groups]
        @pStartIndex = 0,
        @pPageSize = 15,
        @pOrderBy = N'GroupCode ASC'
SELECT  'Return Value' = @return_value

Result
Row _id GroupCode   Description Type    IsActive1   1   CS2009  CS 2009 Batch   S   1
2   2   IT2009  IT 2009 Batch   S   1
3   3   ME2009  ME 2009 Batch   S   1
4   4   EC2009  EC 2009 Batch   S   1
5   5   EE2009  EE 2009 Batch   S   1
6   8   CS_F    CS Faculties    F   1
7   9   IT_F    IT Faculties    F   1
8   10  ME_F    ME Faculties    F   1
9   11  EC_F    EC Faculties    F   1
10  12  EE_F    EE Faculties    F   1
11  13  BSC_F   Basic Science Faculties F   1
12  14  Accounts    Accounts    A   1
13  15  Mgmt    Management  M   1
14  16  Lib Library B   1
15  17  TnP Training & Placement    T   1

Convert dataset into List using reflection in C#

2012-01-23T15:25:00.000+05:30

This article , I am going to explain you how to convert a dataset or datatable into a generic list of your class property or you may say another way out for entity frame work design of object to database relation mapping and using LINQ on the item collection list
First you need to add the reference of the dll, "System.Reflection".

Then I have created a static function in my class that will convert my datatable to a generic list:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Reflection;
using System.Data;
using BusinessEntity;

namespace DataLayer.Entitymapper
{
    public static class EntityBinder
    {
        public static List<T> ToCollection<T>(this DataTable dt)
        {
            List<T> lst = new System.Collections.Generic.List<T>();
            Type tClass = typeof(T);
            PropertyInfo[] pClass = tClass.GetProperties();
            List<DataColumn> dc = dt.Columns.Cast<DataColumn>().ToList();
            T cn;
            foreach (DataRow item in dt.Rows)
            {
                cn = (T)Activator.CreateInstance(tClass);
                foreach (PropertyInfo pc in pClass)
                {

                   DataColumn d = dc.Find(c => c.ColumnName == pc.Name);
                   if (d != null)
                   {
                       if (item[pc.Name] != DBNull.Value)
                       {
                           pc.SetValue(cn, (item[pc.Name]), null);
                       }
                   }
                }
                lst.Add(cn);
            }
            return lst;
        }
    }
}

How to call: In my DataLayer Project I have created a class ResourceDAL.

Now i will create a function where i will pass the datatable and EntityBinder class ToCollection method will convert it into generic list of resources.

public List<ResourceBE> GetResource()
        {
             try
             {
                 DataSet DS = SqlHelper.ExecuteDataset(RTSSqlConnection.GetConnectionString, CommandType.StoredProcedure, "usp_GetResources");
                 DataTable dt = DS.Tables[0];

                 //return dt;
                 List<ResourceBE> listResource = EntityBinder.ToCollection<ResourceBE>(dt);
                 return listResource;
             }
            catch(Exception ex)
            {
                throw new Exception(ex.ToString());
            }
        }

So this function will return now a list of resources......

Find the leap year using UDF function in SQL server 2005/2008

2012-01-23T15:19:00.000+05:30

This function will return , the passed year is a leap year or not

CREATE FUNCTION dbo.fn_IsLeapYear (@year int)
RETURNS BIT
as
BEGIN
    RETURN(SELECT CASE DATEPART(MM, DATEADD(DD, 1, CAST((CAST(@YEAR AS VARCHAR(4)) + '0228') AS DATETIME)))
    WHEN 2 THEN 1
    ELSE 0
    END)
END
GO
Ref:http://ashiskrdas.blogspot.com/

ASHIS KUMAR DAS: Creating dynamic collapsible panel and mass update...

2011-12-20T17:02:00.000+05:30

Creating dynamic collapsible panel and mass update...: In my earlier post Read and Display Data From an Given Sheet of Excel File (.xsl or .xlsx) and Save in SQL Database in ASP.NET I have descr...

All about Gridview control-asp.net

2011-12-06T12:06:00.003+05:30

Introduction

I have seen lot of beginners struggle with GridView and it is not working. To solve this to some extent, I have created an application with most of the events and properties of GridView. This may be helpful for getting a clear idea about working with GridView. This application also has a few JavaScript code snippets and stylesheets which may be useful to add some values to the GridView. Hope this article will be simple and sufficient.

What is a GridView?

The DataGrid or GridView control displays data in tabular form, and it also supports selecting, sorting, paging, and editing the data inside the grid itself. The DataGrid or GridView generates a BoundColumn for each field in the data source (AutoGenerateColumns=true). By directly assigning the data source to the GridView, the data can be rendered in separate columns, in the order it occurs in the data source. By default, the field names appear in the grid's column headers, and values are rendered in text labels. A default format is applied to non-string values and it can be changed.

GridView Fields

Column Name	Description
`BoundColumn`	To control the order and rendering of columns.
`HyperLinkColumn`	Presents the bound data in HyperLink controls
`ButtonColumn`	Bubbles a user command from within a row to the grid event handler
`TemplateColumn`	Controls which controls are rendered in the column
`CommandField`	Displays Edit, Update, and Cancel links in response to changes in the `DataGrid`control's `EditItemIndex` property.

BoundColumn

By explicitly creating a BoundColumn in the grid's Columns collection, the order and rendering of each column can be controlled. In the BoundField properties, when the DataField and the SortExpressions are given, sorting and rendering the data can be easily done.

HyperLinkColumn

A HyperLinkColumn presents the bound data in HyperLink controls. This is typically used to navigate from an item in the grid to a Details view on another page by directly assigning the page URL in NavigationUrl or by rendering it from the database.

TemplateColumn

With a TemplateColumn, the controls which are rendered in the column and the data fields bound to the controls can be controlled. By using the TemplateColumn, any type of data control can be inserted.

By clicking the Edit Templates option, it opens the Template field where we can see controls that can be added likeLabel, CheckBox, and even a GridView.

CommandField

The EditCommandColumn is a special column type that supports in-place editing of the data in one row in the grid.EditCommandColumn interacts with another property of the grid: EditItemIndex. By default, the value ofEditItemIndex is -1, meaning none of the rows (items) in the grid are being edited. If EditItemIndex is -1, an "edit" button is displayed in the EditCommandColumn for each of the rows in the grid.

When the "edit" button is clicked, the grid's EditCommand event is thrown. It's up to the programmer to handle this event in the code. The typical logic sets EditItemIndex to the selected row, and then rebinds the data to the grid.

When EditItemIndex is set to a particular row, the EditCommandColumn displays "update" and "cancel" buttons for that row ("edit" is still displayed for the other rows). These buttons cause the UpdateCommand andCancelCommand events to be thrown, respectively.

Different Types of Events

Name	Description
`DataBinding`	Occurs when the server control binds to a data source. (Inherited from`Control`.)
`DataBound`	Occurs after the server control binds to a data source. (Inherited from`BaseDataBoundControl`.)
`Disposed`	Occurs when a server control is released from memory, which is the last stage of the server control lifecycle when an ASP.NET page is requested. (Inherited from `Control`.)
`Init`	Occurs when the server control is initialized, which is the first step in its lifecycle. (Inherited from `Control`.)
`Load`	Occurs when the server control is loaded into the `Page` object. (Inherited from`Control`.)
`PageIndexChanged`	Occurs when one of the pager buttons is clicked, but after the `GridView`control handles the paging operation.
`PageIndexChanging`	Occurs when one of the pager buttons is clicked, but before the `GridView`control handles the paging operation.
`PreRender`	Occurs after the `Control` object is loaded but prior to rendering. (Inherited from `Control`.)
`RowCancelingEdit`	Occurs when the `Cancel` button of a row in edit mode is clicked, but before the row exits edit mode.
`RowCommand`	Occurs when a button is clicked in a `GridView` control.
`RowCreated`	Occurs when a row is created in a `GridView` control.
`RowDataBound`	Occurs when a data row is bound to data in a `GridView` control.
`RowDeleted`	Occurs when a row's Delete button is clicked, but after the `GridView` control deletes the row.
`RowDeleting`	Occurs when a row's Delete button is clicked, but before the `GridView`control deletes the row.
`RowEditing`	Occurs when a row's Edit button is clicked, but before the `GridView` control enters edit mode.
`RowUpdated`	Occurs when a row's Update button is clicked, but after the `GridView` control updates the row.
`RowUpdating`	Occurs when a row's Update button is clicked, but before the `GridView`control updates the row.
`SelectedIndexChanged`	Occurs when a row's Select button is clicked, but after the `GridView` control handles the select operation.
`SelectedIndexChanging`	Occurs when a row's Select button is clicked, but before the `GridView`control handles the select operation.
`Sorted`	Occurs when the hyperlink to sort a column is clicked, but after the `GridView`control handles the sort operation.
`Sorting`	Occurs when the hyperlink to sort a column is clicked, but before the`GridView` control handles the sort operation.
`Unload`	Occurs when the server control is unloaded from memory. (Inherited from`Control`.)

These events are all listed in the MSDN, and here I have given a few events which are frequently used in our day to day projects:

PageIndexChanging

This event occurs when the property of the grid AllowPaging is set to true, and in the code behind thePageIndexChanging event is fired.

Paging

The DataGrid provides the means to display a group of records from the data source (for example, the first 10), and then navigates to the "page" containing the next 10 records, and so on through the data.

Paging in DataGrid is enabled by setting AllowPaging to true. When enabled, the grid will display page navigation buttons either as "next/previous" buttons or as numeric buttons. When a page navigation button is clicked, the PageIndexChanged event is thrown. It's up to the programmer to handle this event in the code.

//AllowPaging="True"

//fires when the property AllowPaging is set to True
GridView2.PageIndex = e.NewPageIndex;
//Datasource method
TempTable();

RowCommand

The DataGrid fires the RowCommand event when any button is pressed. We can given any name as command name, and based on that, the check will be done and the loop will be executed.

//OnRowCommand="GridView6_RowCommand"
//CommandName="view" CommandArgument='<%# Bind("id") %>'

protected void GridView6_RowCommand(object sender, GridViewCommandEventArgs e)
{
  //this will check whether the command name is equal to view. If it is
  //equal to view the it will go inside the loop.
  if (e.CommandName == "view")
  {
    //to find which row is clicked
    int row = int.Parse(e.CommandArgument.ToString());
    StringBuilder sb = new StringBuilder();
    sb.Append("<script language="'javascript'">\r\n");
    //javascript to navigate to view page.
    sb.Append(" window.open('View.aspx?id=" + row + "',
        null,'width=350,height=300,scrollbars=yes,left=350,top=200,right=5,bottom=5');");
    sb.Append("</script>");
    if (!this.IsClientScriptBlockRegistered("BindScript"))
    {
      RegisterClientScriptBlock("openScript", sb.ToString());
    }
  }
}

RowCreated

The DataGrid fires the RowCreate event when a new row is created.

//OnRowCreated="GridView3_RowCreated" 

protected void GridView3_RowCreated(object sender, GridViewRowEventArgs e)
{
  //When a child checkbox is unchecked then the header checkbox will also be unchecked.
  if (e.Row.RowType == DataControlRowType.DataRow && (e.Row.RowState == 
    DataControlRowState.Normal || e.Row.RowState == DataControlRowState.Alternate))
  {
    CheckBox chkBxSelect = (CheckBox)e.Row.Cells[1].FindControl("chkselect");
    CheckBox chkBxHeader = (CheckBox)this.GridView3.HeaderRow.FindControl("chkHeader");
    chkBxSelect.Attributes["onclick"] = string.Format("javascript:ChildClick(
        this,'{0}');", chkBxHeader.ClientID);
  }
}

RowDeleting

The DataGrid fires the RowDeleting event when the command name is given as Delete.

//OnRowDeleting="GridView3_RowDeleting"
//CommandName="delete"

protected void GridView3_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
  //finding the row index of which row is selected.
  int row = e.RowIndex;
  //deleteing the temp row(or your delete query that will delete the row from
  //the database
  Temp.Rows[row].Delete();
  //reassigning the datasource
  GridView3.DataSource = Temp;
  GridView3.DataBind();
}

RowUpdating

The DataGrid fires the RowUpdating event when the command name is given as Update.

//OnRowUpdating="GridView3_RowUpdating"

CommandName="update"
protected void GridView3_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
  //maintaining the row index to intial position
  GridView3.EditIndex = e.RowIndex;
  //reassigning the datasource
  GridView3.DataSource = Temp;
  GridView3.DataBind();
}

RowEditing

The DataGrid fires the RowEditing event when the command name is given as Edit.

/OnRowEditing="GridView3_RowEditing"
//CommandName="edit"

protected void GridView3_RowEditing(object sender, GridViewEditEventArgs e)
{
  //changing the index of the grid.
  GridView3.EditIndex = e.NewEditIndex - 1;
  //finding the row of the grid
  int row = e.NewEditIndex;
  //updating the rows inside the grid
  Temp.Rows[row]["name"] = ((TextBox)GridView3.Rows[e.NewEditIndex].FindControl(
      "txtname")).Text;
  Temp.Rows[row]["gender"] = ((DropDownList)GridView3.Rows[e.NewEditIndex].FindControl(
      "ddlgender")).Text;
  Temp.Rows[row]["email"] = ((TextBox)GridView3.Rows[e.NewEditIndex].FindControl(
      "txtemail")).Text;
  Temp.Rows[row]["salary"] = ((TextBox)GridView3.Rows[e.NewEditIndex].FindControl(
      "txtsalary")).Text;
  Temp.AcceptChanges();
  //total of the salary and displaying it in footer.
  Total = decimal.Parse(Temp.Compute("sum(Salary)", "Salary>=0").ToString());
  //maintaining the total value in the session
  Session["Salary"] = Total;
  //reassigning the datasource
  GridView3.DataSource = Temp;
  GridView3.DataBind();
}

RowDatabound

The DataGrid fires the RowDatabound event when a data row is bound to data in a GridView control.

//OnRowDataBound="GridView4_RowDataBound"

protected void GridView4_RowDataBound(object sender, GridViewRowEventArgs e)
{
  //Example for grid inside a grid
  //to find the row index to the selected row.
  int index = e.Row.RowIndex;
  //check whether the row type is equal
  if (e.Row.RowType == DataControlRowType.DataRow)
  {
    //assiging datasource for the grid inside the grid.
    DataTable tempdt = new DataTable();
    tempdt.Columns.Add("Id");
    tempdt.Columns.Add("Language", typeof(String));
    DataRow dr = tempdt.NewRow();
    dr["Id"] = 1;
    dr["Language"] = "C";
    tempdt.Rows.Add(dr);
    DataRow dr1 = tempdt.NewRow();
    dr1["Id"] = 2;
    dr1["Language"] = "C++";
    tempdt.Rows.Add(dr1);
    GridView gr = (GridView)(e.Row.FindControl("GridView5"));
    if (gr != null)
    {
      gr.DataSource = tempdt;
      gr.DataBind();
    }
  }
}

Sorting

Data in a grid is commonly sorted by clicking the header of the column to sort. Sorting in a DataGrid can be enabled by setting AllowSorting to true. When enabled, the grid renders LinkButton controls in the header for each column. When the button is clicked, the grid's SortCommand event is thrown. It's up to the programmer to handle this event in the code. Because DataGrid always displays the data in the same order it occurs in the data source, the typical logic sorts the data source, and then rebinds the data to the grid.

//AllowSorting="True"
//OnSorting="GridView2_Sorting"
//SortExpression="name"

protected void GridView2_Sorting(object sender, GridViewSortEventArgs e)
{
  //to check whether to display in ascending order or descending order
  if (e.SortExpression.Trim() == this.SortField)
    this.SortDirection = (this.SortDirection == "D" ? "A" : "D");
  else
    this.SortDirection = "A";
  this.SortField = e.SortExpression;
  TempTable();
}

Header and Footer of the Grid

This is a JavaScript to use when there is a necessity to check all the checkboxes in a column by checking a single checkbox in the grid header. While we are checking the grid header, all the checkboxes inside the grid will be selected, and if any child column is unchecked, then the header will also be unchecked.

<script type="text/javascript">
var TotalChkBx;
var Counter;
window.onload = function()
{
    TotalChkBx = parseInt('<%= this.GridView3.Rows.Count %>');
    Counter = 0;
}
function SelectAll(CheckBox)
{
    var TargetBaseControl = document.getElementById('<%= this.GridView3.ClientID %>');
    var TargetChildControl = "chkselect";
    var Inputs = TargetBaseControl.getElementsByTagName("input");
    for(var n = 0; n < Inputs.length; ++n)
    if(Inputs[n].type == 'checkbox' && Inputs[n].id.indexOf(TargetChildControl,0) >= 0)
    Inputs[n].checked = CheckBox.checked;
    Counter = CheckBox.checked ? TotalChkBx : 0;
}
function ChildClick(CheckBox, HCheckBox)
{
    var HeaderCheckBox = document.getElementById(HCheckBox);
    if(CheckBox.checked && Counter < TotalChkBx)
    Counter++;
    else if(Counter > 0)
    Counter--;
    if(Counter < TotalChkBx)
    HeaderCheckBox.checked = false;
    else if(Counter == TotalChkBx)
    HeaderCheckBox.checked = true;
}
</script>
onclick="javascript:SelectAll(this);"

Maintaining the values in the footer is done by the following code:

protected void GridView3_RowDataBound(object sender, GridViewRowEventArgs e)
{
  //total in the footer
  if (e.Row.RowType == DataControlRowType.Footer)
  {
    e.Row.Cells[4].Text = this.Total.ToString();
  }
}

To maintain the grid header in a fixed state, a style sheet is used in the source code:

<style type="text/css">.DataGridFixedHeader { 
BACKGROUND: url(Images/grid_header.jpg) repeat-x; 
POSITION: relative; TOP: expression(this.offsetParent.scrollTop); HEIGHT: 27px }

</style>
<div style="width:80%; height:250px; overflow:auto;" >
<asp:GridView ID="GridView1" runat="server" 
    Width="100%" AutoGenerateColumns="False" 
    CellPadding="4" DataKeyNames="id" 
    ForeColor="#333333" GridLines="None"
    OnRowCreated="GridView1_RowCreated" 
    AllowPaging="True" AllowSorting="True"
    OnPageIndexChanging="GridView1_PageIndexChanging" OnSorting="GridView1_Sorting"
    PageSize="2" OnRowCommand="GridView1_RowCommand"
    OnRowDeleting="GridView1_RowDeleting" 
    OnRowEditing="GridView1_RowEditing">
....
....
<HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White"
    CssClass="gridHeader DataGridFixedHeader" />
.....
.....
</asp:GridView>
</div>

Export GridView to Word, Excel, and PDF

Word

protected void btnword_Click(object sender, EventArgs e)
{
    Response.AddHeader("content-disposition", 
                       "attachment;filename=Information.doc");
    Response.Cache.SetCacheability(HttpCacheability.NoCache);
    Response.ContentType = "application/vnd.word";
    System.IO.StringWriter stringWrite = new System.IO.StringWriter();
    System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
    // Create a form to contain the grid
    HtmlForm frm = new HtmlForm();
    GridView1.Parent.Controls.Add(frm);
    frm.Attributes["runat"] = "server";
    frm.Controls.Add(GridView1);
    frm.RenderControl(htmlWrite);
    //GridView1.RenderControl(htw);
    Response.Write(stringWrite.ToString());
    Response.End();
}

Excel

protected void btnexcel_Click(object sender, EventArgs e)
{
    string attachment = "attachment; filename=Information.xls";
    Response.ClearContent();
    Response.AddHeader("content-disposition", attachment);
    Response.ContentType = "application/ms-excel";
    StringWriter sw = new StringWriter();
    HtmlTextWriter htw = new HtmlTextWriter(sw);
    // Create a form to contain the grid
    HtmlForm frm = new HtmlForm();
    GridView1.Parent.Controls.Add(frm);
    frm.Attributes["runat"] = "server";
    frm.Controls.Add(GridView1);
    frm.RenderControl(htw);
    //GridView1.RenderControl(htw);
    Response.Write(sw.ToString());
    Response.End();
}

PDF

protected void btnpdf_Click(object sender, EventArgs e)
{
    Response.Clear();
    StringWriter sw = new StringWriter();
    HtmlTextWriter htw = new HtmlTextWriter(sw);
    GridView1.RenderControl(htw);
    Response.ContentType = "application/pdf";
    Response.AddHeader("content-disposition", 
             "attachment; filename=Information.pdf");
    Response.Write(sw.ToString());
    Response.End();
}

Difference between DataGrid, DataList, and Repeater

A DataGrid, DataList, and Repeater are all ASP.NET data Web controls.

They have many things in common like the DataSource property, the DataBind method, and the ItemDataBoundand ItemCreated events.

When you assign the DataSource property of a DataGrid to a DataSet, each DataRow present in the DataRowcollection of the DataTable is assigned to a corresponding DataGridItem, and this is the same for the rest of the two controls. But The HTML code generated for a DataGrid has an HTML TABLE <ROW> element created for the particular DataRow and it is a table form representation with columns and rows. For a DataList, it is an array of rows, and based on the template selected and the RepeatColumn property value, we can specify how manyDataSource records should appear per HTML <table> row. In short, in a DataGrid, we have one record per row, but in a DataList, we can have five or six rows per row. For a Repeater control, the data records to be displayed depend upon the templates specified, and the only HTML generated is the due to the templates.

In addition to these, DataGrid has in-built support for sorting, filtering, and paging the data, which is not possible when using a DataList, and for a Repeater control, we would require to write explicit code to do paging.

source and reference is codeproject

Display Data from a Given Sheet of Excel File (.xsl or .xlsx) in Grid View and Save in SQL Database -ASP.NET

2011-12-01T13:14:00.000+05:30

Importing data from Excel file into database is repeatedly and frequently used task by an end user.

But from database side its required a technical expertise of DBA, we can achieve it by DTS or SSIS package in SQL Server 2005/2008.

In this article I am going to create a small web application using ASP.net for end user to browse a excel file on his/her system and upload it to back end database table, which is going to help a lot the developer as well as the end user, here I am going to explain step by step procedure to performed the above scenario.

Scenario:

Imports excel file sheet or desired sheet to database and display it on browser;

Solution:

My application has some fileupload control through which the excel file is loaded into the server. Then clicking on button will transfer the Excel file sheet into the Database table.

To transfer the data from excel to database it can be achieved by the following way

1.) Using the sql Bulk Copy command and

2.) Using the Dataset and XML ADO.net feature

Step 1

Create your Table like this.

Create you excel file

Step 2;

Create your web application :- Create a new Web application project in you VS2005/2008 and paste the following code in you default. Aspx page;

<%@ Page Title="Home Page" Language="C#" AutoEventWireup="true"CodeBehind="Default.aspx.cs"

Inherits="ExcelApplication._Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN""http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<title>Read and Display Data From an Excel File (.xsl or .xlsx) in ASP.NET</title>

tr.sectiontableentry1 td, tr.sectiontableentry2 td

{

padding: 4px;

}

tr.sectiontableentry1 td

{

padding: 8px 5px;

background: url(hline.gif) repeat-x bottom;

}

tr.sectiontableentry2 td

{

padding: 8px 5px;

background: url(hline.gif) repeat-x bottom #F2F2F2;

}

</style>

</head>

<body>

<div>

<tbody>

<tr>

<td>

<strong>Please Select Excel File Containing The Details…</strong>

</td>

</tr>

<tr>

<td>

<div style="background: url(hline.gif) repeat-xbottom #F2F2F2; padding: 8px 5px;

border-bottom: 1px solid #ccc;">

<asp:FileUpload ID="txtFilePath" runat="server"></asp:FileUpload>  

<asp:Label ID="lblSheet" Text="Sheet Name: "Height="20px" runat="server"></asp:Label>  

<asp:TextBox ID="txtSheetName" runat="server"MaxLength="100"></asp:TextBox>  <asp:RequiredFieldValidator

ID="RequiredFieldValidator1" runat="server"ControlToValidate="txtSheetName"

ErrorMessage="*" ForeColor="Red"SetFocusOnError="True"></asp:RequiredFieldValidator>

 <asp:Label ID="lblProject" Text="Project: " Height="20px" runat="server"></asp:Label> 

<asp:DropDownList ID="ddlProject" runat="server"Height="20px">

</asp:DropDownList>

<asp:RequiredFieldValidator ID="rfvProject"runat="server" ControlToValidate="ddlProject"

ErrorMessage="*" ForeColor="Red"SetFocusOnError="true" InitialValue="0"></asp:RequiredFieldValidator>

<asp:Button ID="btnUpload" runat="server"Text="Upload" OnClick="btnUpload_Click" /><br />

<asp:Label ID="lblMessage" runat="server"Visible="False" Font-Bold="True" ForeColor="#009933"></asp:Label>

</div>

</td>

</tr>

<tr>

<td>

<asp:GridView ID="grvExcelData" runat="server"EmptyDataText="No Record Found!!">

<AlternatingRowStyleCssClass="sectiontableentry1" />

</asp:GridView>

</td>

</tr>

</tbody>

</table>

</div>

</form>

</body>

</html>

Here I am going to explain

1.) Using the Dataset and XML ADO.net feature

The advantage of using this method over sqlbulkcopy is

We can call a procedure and add extra value to it at runtime we can shuffle the table field at run time.

It’s a faster technique and we can do it any database because it’s using xml;

Step 3:

Create your procedure like this.

USE [RND]

/****** Object: StoredProcedure [dbo].[usp_AddExcelData] Script Date: 12/01/2011 12:48:25 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

ALTER Procedure [dbo].[usp_AddExcelData]

(

@ExcelData varchar(max),

@Createdby varchar(50),

@dcmVer varchar(5),

@ProjID Varchar(10)

)

BEGIN

DECLARE @idoc int

DECLARE @MyTableVar table(

ID INT identity(1,1),

[Question_Description] nvarchar(255),

[Category] nvarchar(255),

[Reviewed] Bit,

[ReviewerComments] nvarchar(255),

[DevelopmentStatus]Bit,

[DeveloperComments] nvarchar(255),

CreatedBy Nvarchar(50),

dcmVer nvarchar(5),

ProjID int

)

EXEC sp_xml_preparedocument @idoc OUTPUT, @ExcelData

print @idoc

INSERT INTO @MyTableVar([Question_Description],[Category],[ReviewerComments],[DeveloperComments])

SELECT[Question_Description],[Category],[ReviewerComments],[DeveloperComments]

FROM OPENXML (@idoc, '/NewDataSet/Table', 1)

WITH

(Question_Description nvarchar(255) './Question_Description',

Category nvarchar(255) './Category',

ReviewerComments nvarchar(255) './ReviewerComments',

DeveloperComments nvarchar(255) './DeveloperComments'

)

EXEC sp_xml_removedocument @idoc

Declare @TotalCount AS INT

Declare @i as INT

Declare @MaxID as INT

Update @MyTableVar setCreatedBy=@Createdby,dcmVer=@dcmVer,ProjID=@ProjID,[Reviewed]=0,[DevelopmentStatus]=0

SET @i=1

SET @TotalCount=(select count(*) from @MyTableVar)

SET @MaxID=(select max(QID) from CodeReview)

WHILE @i<=@TotalCount

BEGIN

Insert CodeReview SELECT [Question_Description]

,[Category]

,[Reviewed]

,[ReviewerComments]

,[DevelopmentStatus]

,[DeveloperComments],GETDATE(),CreatedBy,dcmVer,ProjID from@MyTableVar where id=@i

SET @i = @i + 1

END

Select * from CodeReview where QID > ISNULL(@MaxID,0)

END

Step 4:

The code behind part to upload the excel file to the server and transfer the data in the table.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Data.OleDb;

using System.Data;

using System.IO;

using Excel;

using System.Data.SqlClient;

using System.Configuration;

namespace ExcelApplication

{

public partial class _Default : System.Web.UI.Page

{

string SheetNameExcel = null;

protected void Page_Load(object sender, EventArgs e)

{

if(!IsPostBack)

BindProject();

}

protected void btnUpload_Click(object sender, EventArgs e)

{

if ((txtFilePath.HasFile))

{

OleDbConnection conn = new OleDbConnection();

OleDbCommand cmd = new OleDbCommand();

OleDbDataAdapter da = new OleDbDataAdapter();

DataSet ds = new DataSet();

string query = null;

string connString = "";

string strFileName =DateTime.Now.ToString("ddMMyyyy_HHmmss");

string strFileType = System.IO.Path.GetExtension(txtFilePath.FileName).ToString().ToLower();

//Check file type

if (strFileType == ".xls" || strFileType == ".xlsx")

{

//save the file on server

txtFilePath.SaveAs(Server.MapPath("~/UploadedExcel/" + strFileName + strFileType));

}

else

{

lblMessage.Text = "Only excel files allowed";

lblMessage.ForeColor = System.Drawing.Color.Red;

lblMessage.Visible = true;

return;

}

//Fetch the path of the file

string strNewPath = Server.MapPath("~/UploadedExcel/" + strFileName + strFileType);

//Connection String to Excel Workbook

if (strFileType.Trim() == ".xls")

{

connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strNewPath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";

}

else if (strFileType.Trim() == ".xlsx")

{

connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strNewPath + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";

}

//query = "SELECT * FROM [Sheet1$]";

//query = "SELECT [Country],[Capital] FROM [Sheet1$] WHERE [Currency]=’Rupee’"

//query = "SELECT [Country],[Capital] FROM [Sheet1$]"

string SheetName = txtSheetName.Text.Trim() + "$";

//Create the connection object

conn = new OleDbConnection(connString);

//Open connection

if (conn.State == ConnectionState.Closed) conn.Open();

//Check sheet name

if (FindExcelSheetName(conn, SheetName))

{

//Create the query dynamically

query = "SELECT * FROM [" + SheetNameExcel + "]";

//Create the command object

cmd = new OleDbCommand(query, conn);

da = new OleDbDataAdapter(cmd);

ds = new DataSet();

da.Fill(ds);

//Get the xmldata in string

string excelData = ds.GetXml().ToString();

ds=AddExcelData(excelData);

if (ds.Tables[0].Rows.Count >0)

{

grvExcelData.DataSource = ds.Tables[0];

grvExcelData.DataBind();

lblMessage.Text = "Data retrieved successfully! Total Records:" + ds.Tables[0].Rows.Count;

lblMessage.ForeColor = System.Drawing.Color.Green;

lblMessage.Visible = true;

//Destroy the adapter and close the connection

da.Dispose();

conn.Close();

conn.Dispose();

//Delete the file from server

if (File.Exists(strNewPath))

File.Delete(strNewPath);

}

else

{

lblMessage.Text = "Sheet Does Not Contain Any Records!!";

lblMessage.ForeColor = System.Drawing.Color.Red;

lblMessage.Visible = true;

//Destroy the adapter and close the connection

da.Dispose();

conn.Close();

conn.Dispose();

//Delete the file from server

if (File.Exists(strNewPath))

File.Delete(strNewPath);

}

else

{

lblMessage.Text = "Sheet Name Does Not Exists!";

lblMessage.ForeColor = System.Drawing.Color.Red;

lblMessage.Visible = true;

//Destroy the adapter and close the connection

da.Dispose();

conn.Close();

conn.Dispose();

//Delete the file from server

if (File.Exists(strNewPath))

File.Delete(strNewPath);

}

else

{

lblMessage.Text = "Please select an excel file first!!";

lblMessage.ForeColor = System.Drawing.Color.Red;

lblMessage.Visible = true;

}

protected DataSet AddExcelData(string excelData)

{

SqlCommand myCommand = new SqlCommand("usp_AddExcelData");

myCommand.CommandType = CommandType.StoredProcedure;

//Creat and add the parameters

SqlParameter param = new SqlParameter();

param.ParameterName = "@excelData";

param.Value = excelData;

myCommand.Parameters.Add(param);

SqlParameter param1 = new SqlParameter();

param1.ParameterName = "@Createdby";

param1.Value = "Ashis";

myCommand.Parameters.Add(param1);

SqlParameter param2 = new SqlParameter();

param2.ParameterName = "@dcmVer";

param2.Value = "1";

myCommand.Parameters.Add(param2);

SqlParameter param3 = new SqlParameter();

param3.ParameterName = "@ProjID";

param3.Value = ddlProject.SelectedValue;

myCommand.Parameters.Add(param3);

// create SqlConnection

SqlConnection myConnection = newSqlConnection(ConfigurationManager.ConnectionStrings["ConnectionInfo"].ConnectionString);

myConnection.Open();

myCommand.Connection = myConnection;

SqlDataAdapter da = new SqlDataAdapter(myCommand);

DataSet aus = new DataSet();

da.Fill(aus);

//Destroy the adapter and close the connection

da.Dispose();

myConnection.Close();

myConnection.Dispose();

return aus;

}

protected bool FindExcelSheetName(OleDbConnection conn,stringsheetName)

{

//Get the all sheet name

DataTable dt = null;

bool find=false;

//Get the sheet name from schema

dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

if (dt != null)

{

foreach (DataRow row in dt.Rows)

{

string excelSheetName = row["TABLE_NAME"].ToString();

//Matching the sheetname with input and schema

if (excelSheetName.ToUpper() == sheetName.ToUpper())

{

find = true;

//Setting the sheetname from schema

SheetNameExcel = excelSheetName;

}

return find;

}

protected void BindProject()

{

SqlCommand myCommand = new SqlCommand("usp_GetProjects");

myCommand.CommandType = CommandType.StoredProcedure;

// create SqlConnection

SqlConnection myConnection = newSqlConnection(ConfigurationManager.ConnectionStrings["ConnectionInfo"].ConnectionString);

myConnection.Open();

myCommand.Connection = myConnection;

SqlDataAdapter da = new SqlDataAdapter(myCommand);

DataSet aus = new DataSet();

da.Fill(aus);

//Destroy the adapter and close the connection

da.Dispose();

myConnection.Close();

myConnection.Dispose();

if (aus.Tables[0].Rows.Count > 0)

{

foreach (DataRow Dr in aus.Tables[0].Rows)

{

ddlProject.Items.Add(newListItem(Dr["ProjectName"].ToString(), Dr["ProjectID"].ToString()));

}

ddlProject.Items.Insert(0, "---Select---");

ddlProject.Items[0].Value = "0";

}

protected DataTable ReadExcel(string filePath, string fileExtension)

{

FileStream stream = File.Open(filePath, FileMode.Open,FileAccess.Read);

IExcelDataReader excelReader;

if (fileExtension == ".xls")

{

//1. Reading from a binary Excel file ('97-2003 format; *.xls)

excelReader = ExcelReaderFactory.CreateBinaryReader(stream);

}

else

{

//2. Reading from a OpenXml Excel file (2007 format; *.xlsx)

excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);

}

//...

//3. DataSet - The result of each spreadsheet will be created in the result.Tables

//DataSet result = excelReader.AsDataSet();

//...

//4. DataSet - Create column names from first row

excelReader.IsFirstRowAsColumnNames = true;

DataSet result = excelReader.AsDataSet();

////5. Data Reader methods

//while (excelReader.Read())

//{

// //excelReader.GetInt32(0);

//}

//6. Free resources (IExcelDataReader is IDisposable)

excelReader.Close();

return result.Tables[0];

}

download code in VS2010

End-to-End IT Systems | Services | Solutions Provider: Minimizing costs / Maximizing returns while Going Mobile in Retail Environment

2011-11-18T14:17:00.000+05:30

End-to-End IT Systems | Services | Solutions Provider: Minimizing costs / Maximizing returns while Going Mobile in Retail Environment

File Operation-Copy , move , rename and delete a file or folder in SSIS

2011-11-18T13:10:00.001+05:30

SSIS File Operations – Copy File, Move File, Rename File and Delete File

Create SSIS Project

First we need to create an SSIS Project before starting with SSIS File Operations, you can create a SSIS Project . Let’s say we created an SSIS project as FileOperations.

Copy File:

We can copy file from one place to another with the help of SSIS. We will look into the whole process of copy file from one place to another in this section, for this you need a New Package in your project which you can create easily . Now we have a SSIS project namedFIleOperations, it is the time to create a package in our SSIS project. Add a new package and name it as CopyFile.

Adding File System Task in Your Package

Now we have a package and it is time to add File System Task to your package, to add File System Task control to the package go to control flow times in toolbox and double click on File System Tasks or drag and drop it in your package(See the image below).

Configuring File System Task

Now we have File System Task control added to your package and we can configure it. To configure right click on the control and choose edit(See the image below).

After that you will be navigated to a new window named File System Task Editor in which you have to configure the File System Task(See the image below).

The options on File system task editor are as below.

IsDestinationPathVariable : Destination path is stored as variable or not.
DestinationConnection : File Connection or vairable.
OverwriteDestination : Overwrite if file exists with same name. True/False
Name : name you want to give.
Description : Little description
Operation : Choose copy file.
IsSourcePathVariable : Source path is stored as variable or not.
SourceConnection : File Connetion or variable.

Here we have to choose operation as Copy File choose OverwriteDestination as True and IsDestinationPathVariable and IsSourcePathVariable as False. Now we have to configure Source and destination connections.

Configuring Source and Destination Connections

Now we have to configure source and destination connections.

Source Connection : In File System Task Editor navigate to SourceConnection Tab and choose New Connection(See the image below).

Now you will be navigated to new window named File Connection Manager Editor where you have to configure your source connection. Choose Existing FIle asUsage Type and provide fully qualified path for file which you want to copy to other location(See the image below).

Destination Connection : Once you done with your SourceConnection you have to configure Destination Connection. Go to DestinationConnection tab and choose New Connection(See the image below).

Now you will be navigated to new window named File Connection Manager Editor where you have to configure your destination connection(See the image below).

Here we also have to provide Usage Type & Folder as in Usage Type we choose Existing Folder so we have to provide the destination folder where we want to have a copy of file.

Now you done with both Source and Destination connection and your final File System Task Editor looks like this(See the image below).

Click OK and you are done with your Copy File Task. Save it and execute package as we did in previous post and this will copy specific file from one place to another.

Rename File:

You done with Copy File, we will see the Rename File operation next. This operation will rename any file to the another name you want, for this we need to create a package named Rename. Once you have package added to your project open it and drag and drop File System Task control to your package as we did earlier in this post. Now we will configure that file system task to rename files. For this you have to right click on the package and choose Edit as we did earlier in this post. This will open up File System Task Editor, we have to configure that editor to rename files(See the image below).

We will choose Operation as Rename File(See the image below).

Now we have to configure Source and Destination Connection for Rename File System Task.

Configure Source and Destination Connection :

Source Connection : In File System Task Editor navigate to SourceConnection Tab and choose New Connection(See the image below).

Now you will be navigated to a new window named File Connection Manager Editor and here you have to configure your SourceConnection choose Existing Fileas Usage Type and provide Fully Qualified Path in File(See the image below).

click Ok and your are done with your SourceConnection.

Destination Connection : Once you are done with your SourceConnection you have to configure Destination Connection where you want your renamed file, for this navigate to DestinationConnection Tab and choose New Connection(See the image below).

Now you will be navigated to new window named File Connection Manager Editor and here you have to configure your DestinationConnection choose Create Fileas Usage Type and provide Fully Qualified Path in File(See the image below).

Click Ok and you are done with your DestinationConnection.

Now you will be navigated back to File System Task Editor here click ok and you are done with your Rename File

Move File :

In this Move File Task we will see how to move files from one location to another location. For this we have to add a package name Move in our project and addFile System Task Control to your package by Drag & Drop from ToolBox. Once you have File System Task added to your project you have to configure this to move file by right click and choose edit on File System Task as we did earlier in this post(See the image below).

Now after this we need to configure Source and Destination Connection as we did earlier in this post.

Configure Source and Destination Connection :

Source Connection : In File System Task Editor navigate to SourceConnection Tab and choose New Connection(See the image below).

click Ok and your are done with your SourceConnection.

Destination Connection : Once you are done with your SourceConnection you have to configure Destination Connection where you want to move file, for this navigate to DestinationConnection Tab and choose New Connection(See the image below).

Now you will be navigated to new window named File Connection Manager Editor and here you have to configure your DestinationConnection choose Existing Folder as Usage Type and provide Folder Path in File(See the image below).

Click Ok and you are done with your DestinationConnection.

Now you will be navigated back to File System Task Editor here click ok and you are done with your Move File Task.

Delete File:

Delete File Task is used to delete specific file from the specific location. For this we need to create a new package by name delete in our sample project and add File System Task control to your package by Drag & Drop it from ToolBox. Once you have File System Task added to your package Right Click on File System Task and choose Edit to configure it to delete files from a specific location. Now we get File System Task Editor window on our screen choose Delete File as operation in File System Task Editor window(See the image below).

Now we have to configure Source Connection for this from where we like to delete files.

Configure Source Connection :

Go to SourceConnection tab in File System Task Editor and choose New Connection(See the image below).

SQL Server:How to import comma delimited or CSV file into table?

2011-11-17T17:40:00.001+05:30

Load CSV file in sql server 2005/2008 using bulk insert
This a very common question asked by the developer .
How to load CSV file into SQL Server Database Table? How to load comma delimited file into SQL Server?
Here I am giving you the step for that

step1
create a table as per your need

step2
create your csv file from where you want to import or load data into your table
and save it on some path like c:\employee.csv

step3
now use the following sql command in query analyzer
BULK
INSERT employee
FROM 'c:\employee.csv'WITH(FIELDTERMINATOR = ',',ROWTERMINATOR = '\n')GO

hope this help you a lot

Saving changes is not permitted (SQL Server 2008)

2011-11-15T13:00:00.000+05:30

Saving changes is not permitted (SQL Server 2008)

Published 11/4/2008 by Henry in SQL

Tags: SQL

While creating and editing a database in SQL server 2008 I soon ran into a brick wall, or a 'by design' feature of the Database Management Studio.
When you design a table in a database and then try to make a change to a table structure that requires the table to be recreated, the Database Management Studio will not allow you to save the changes.
This is caused by a configuration setting that default results in the following dialog:

Error dialog

This dialog states: "Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can't be re-created or enabled the option Prevent saving changes that require the table to be re-created."

You can only react clicking the Cancel button.
And than Management Studio shows this dialog:

So what you want to do to configure the setting with the value you want (IMHO should be the default setting!).
Go to: Tools > Options

Tools menu choose Options

Select the tab Designers and choose Tables and designers

And uncheck the option: "Prevent saving changes that require table re-creation".
Once you now save changes it will work.