Saturday, September 11, 2010

Read data from excel file in C# & ASP.NET

In this article I will explain you how to read and display data from excel file. We will be using microsoft OLEDB provider to connect the excel file, extract data and then display the data in Grid View.
This article allows you to upload excell sheet and display in grid view. Lets start with coding!
Below code allows you to create a connection and displays all the sheets abailable in the excel file.

            string strString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\TestCode\NewExcel.xls;Extended Properties=Excel 8.0";
        // Create the connection object
        OleDbConnection oledbConn = new OleDbConnection(strString);

        oledbConn.Open();

        DataTable dtTables = oledbConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

        if (dtTables != null && dtTables.Rows.Count > 0)
        {
            for(int i = 0; i < dtTables.Rows.Count; i++)
            {
                Response.Write(dtTables.Rows[i]["TABLE_NAME"].ToString());
          Response.Write("<br/>");
            }
        }

  oledbConn.Close();

Once you have the information about the sheets in excel then you have able to read the sheets according to your requirement.
Below code allows you to open a connection to excel and read then excel sheet you want to read and display in the interface. 

  try
        {
            // Open connection
            oledbConn.Open();

// Create OleDbCommand object and select a sheet as per the requirement. Here I am using a fixed name of the sheet name.
OleDbCommand cmd = new OleDbCommand("SELECT * FROM [Sheet1$]", oledbConn);

            // Create new OleDbDataAdapter
            OleDbDataAdapter oleda = new OleDbDataAdapter();

            oleda.SelectCommand = cmd;

// Create a DataSet which will hold the data extracted from the worksheet.
            DataSet ds = new DataSet();

            // Fill the DataSet from the data extracted from the worksheet.
            oleda.Fill(ds, "Friends");

            // Bind the data to the GridView
            GridView1.DataSource = ds.Tables[0].DefaultView;
            GridView1.DataBind();
        }
        catch
        {

        }
        finally
        {
            // Close connection
            oledbConn.Close();
  }

Programming guidelines for web application developers


Introduction

Our Goal – Quality Software that is correct, robust and has extensibility.
Correctness is the ability of the code to perform to its specification—that is, its behavior in known conditions.

Robustness is its behavior in unknown conditions.

Extensibility is the ease with which the code can be modified to accommodate new and changed requirements.

These standards were adopted from different sources, but mainly from msdn.microsoft.

This article contains guidelines for producing high quality software. Although standards exist for the creation of software for specific industry sectors, there is no specific general software programming standard as such. Many organizations, particularly larger organizations and consultancies will, however, tend to have their own internal programming standards.

The most important aspect of standards is that they must be applied consistently. Ever changing business requirements and fast-approaching deadlines force projects into cutting corners, and coding standards are usually the first thing to go. To be productive, coding standards must be in place and followed all of the time, not just when time allows. If coding standards are ignored for some code, they might as well not be used for any of it.

Coding standards allow programmers to create easily maintainable code. The most expensive aspect of software development is the ongoing maintenance of code. A well-defined set of standards gives developers other than the original developer a head start on becoming familiar with the code. Valuable time is not wasted trying to come to terms with the original author’s style and conventions

 

Coding Standards and Code Reviews

A comprehensive coding standard encompasses all aspects of code construction. While developers should prudently implement a standard, it should be adhered to whenever practical. Completed source code should reflect a harmonized style, as if a single developer wrote the code in one session. At the inception of a software project, establish a coding standard to ensure that all developers on the project are working in concert. When the software project incorporates existing source code, or when performing maintenance on an existing software system, the coding standard should state how to deal with the existing code base.

The readability of source code has a direct impact on how well a developer comprehends a software system. Code maintainability refers to how easily that software system can be changed to add new features, modify existing features, fix bugs, or improve performance. Although readability and maintainability are the result of many factors, one particular facet of software development upon which all developers have an influence is coding technique. The easiest method to ensure a team of developers will yield quality code is to establish a coding standard, which is then enforced at routine code reviews.

Using solid coding techniques and good programming practices to create high-quality code plays an important role in software quality and performance. In addition, if you consistently apply a well-defined coding standard, apply proper coding techniques, and subsequently hold routine code reviews, a software project is more likely to yield a software system that is easy to comprehend and maintain.

Although the primary purpose for conducting code reviews throughout the development life cycle is to identify defects in the code, the reviews can also enforce coding standards in a uniform manner. Adherence to a coding standard is only feasible when followed throughout the software project from inception to completion. It is not practical, nor is it prudent, to impose a coding standard after the fact.

Coding Techniques

Coding techniques incorporate many facets of software development. Although they usually have no impact on the functionality of the application, they contribute to an improved comprehension of source code. All forms of source code are considered here, including programming, scripting, markup and query languages.

The coding techniques defined here are not proposed to form an inflexible set of coding standards. Rather, they are meant to serve as a guide for developing a coding standard for a specific software project.

The coding techniques are divided into three sections: 

1. Names

2. Comments

3. Format

 

1. Names

The naming scheme is one of the most influential aids to understanding the logical flow of an application. A name should tell "what" rather than "how." By avoiding names that expose the underlying implementation, which can change, you preserve a layer of abstraction that simplifies the complexity. For example, you could use GetNextStudent() instead of GetNextArrayElement().

A tenet of naming is that difficulty in selecting a proper name may indicate that you need to further analyze or define the purpose of an item. Make names long enough to be meaningful but short enough to avoid verbosity. Programmatically, a unique name serves only to differentiate one item from another. Expressive names function as an aid to a human reader; therefore, it makes sense to provide a name that a human reader can comprehend. However, be certain that the chosen names are in compliance with the applicable language's rules and standards.

The following points are recommended naming techniques.

 

1.1 Routines

Avoid elusive names that are open to subjective interpretation, such as AnalyzeThis() for a routine, or xxK8 for a variable. Such names contribute to ambiguity more than abstraction.
In object-oriented languages, it is redundant to include class names in the name of class properties, such as Book.BookTitle. Instead, use Book.Title.
Use the verb-noun method for naming routines that perform some operation on a given object, such as CalculateInvoiceTotal().

1.2 Variables
Append computation qualifiers (Avg, Sum, Min, Max, Index) to the end of a variable name where appropriate.

Use complementary pairs in variable names, such as min/max, begin/end, and open/close.
Since most names are constructed by concatenating several words, use mixed-case formatting to simplify reading them. In addition, to help distinguish between variables and routines, use following standards:

Use the prefix for every file names, controls, objects, variables. The prefix must contain three characters and all the prefix character should in lower case letters.

For form or control names:

frm<FormName>           Form Name
            btn<ButtonName>         Button Control
            lbl<LabelName>            Label Control

For variable or object names:     

            str<StringVariableName>           String Variable Name
            int<IntergerVariableName>         Integer Variable Name
            obj<ObjectName>                     Object Name

The control or variable name should come after the prefix. Each word in the name should start with a block letter. Please go through Table 1 and Table 2 list for detail about naming of control and variable respectively.

Table 1 : Control Naming and Style List
Control Name
Prefixes
Example
Form
frm
frmAddUser
Textbox
txt
txtFirstName
TextArea
txtarea
txtareaDescription
Label
lbl
lblFirstName
Checkbox
chk
chkMarried
Checkbox List
chklst
ChklstSubjects
Radiobutton
rad
radAddEdit
Radiobutton List
radlst
radlstMaleFemale
Dropdown
dd
ddCampus
Button
btn
btnSave
Link Button
lnkbtn
lnkbtnLogout
Hyperlink
lnk
lnkSiteView
Image Button
imgbtn
imgbtnUserImage
Panel
pan
panOptions
Place Holder
ph
phSubjectList
Table, TableRow, TableCell
tbl, tr, td
tblUserInfo, trHeading, td FirstName
Range Validator
RV
RVPercentage
Regular Expression Validator
REV
REVEmailID
Required Field Validator
RFV
RFVFirstName
Compare Validator
CV
CVStartEndDate
Validation Summary
VSUM
VSUMAddUser
Data Grid
DG
DGUserList
Calendar
cal
calDOB
File Upload
File
fileUserImage


Table 2 : Variable Naming List
Variable Name
Prefixes
Example
Object
obj
objUser (object of User Class)
Int
int
intUserID
String
str
strUserName
Boolean
bol, bool
bolActiveUser, boolActiveUser
Char
chr, char
chkMarried
Float
float
ChklstSubjects
Double
dbl
radAddEdit
Decimal
dcml
radlstMaleFemale

Even for a short-lived variable that may appear in only a few lines of code, still use a meaningful name. Use single-letter variable names, such as i, or j, for short-loop indexes only.
Do not use literal numbers or literal strings, such as For int i = 1 To 7. Instead, use named constants, such as For int i = 1 To intNumOfDays for ease of maintenance and understanding.

1.3 Tables
When naming tables, express the name in the singular form. For example, use Employee instead of Employees.

When naming columns of tables do not repeat the table name; for example, avoid a field called EmployeeLastName in a table called Employee.

Do not incorporate the data type in the name of a column. This will reduce the amount of work should it become necessary to change the data type later.

1.4 Microsoft SQL Server
 For database these are some basic standards needs to be followed:

·         Table name :
     Prefix is always tbl.
     Ex : tblUser

·         Column name :
                  Prefix letter(s) to make columns unique in the datatbase.
                  Ex : U_ID will be a column of tblUser.
 UCL_ID will be a column of tblUserCampusLink

·         Primary key :
                   Ex : PK_tblUser for tblUser table
PK_tblUserCampusLink for tblUserCampusLink table

·         Foreign key :
Ex : FK_tblUserCampusLink_tblUser (Column UCL_UserID of tblUserCampusLink table)

1.5 Miscellaneous
Minimize the use of abbreviations, but use those that you have created consistently. An abbreviation should have only one meaning and likewise, each abbreviated word should have only one abbreviation. For example, if you use min to abbreviate minimum, do so everywhere and do not use min to also abbreviate minute. 

When naming functions, include a description of the value being returned, such as GetCurrentWindowName().

File and folder names, like procedure names, should accurately describe their purpose.
Avoid reusing names for different elements, such as a routine called ProcessSales() and a variable called iProcessSales.

Avoid homonyms, such as write and right, when naming elements to prevent confusion during code reviews.

When naming elements, avoid commonly misspelled words. Also, be aware of differences that exist between regional spellings, such as color/colour and check/cheque.

Avoid typographical marks to identify data types, such as $ for strings or % for integers.

 

2. Comments

Software documentation exists in two forms, external and internal. External documentation, such as specifications, help files, and design documents, is maintained outside of the source code. Internal documentation is comprised of comments that developers write within the source code at development time.

Despite the availability of external documentation, source code listings should be able to stand on their own because hard-copy documentation can be misplaced. External documentation should consist of specifications, design documents, change requests, bug history, and the coding standard used.

One challenge of internal software documentation is ensuring that the comments are maintained and updated in parallel with the source code. Although properly commenting source code serves no purpose at run time, it is invaluable to a developer who must maintain a particularly intricate or cumbersome piece of software.

The following points are recommended commenting techniques.
While modifying code which is not written by you or some other developer written the code and you are fixing the bugs or editing the logic. Please remember always keep the commenting around it up to date.

Example:

If a block of code edited:

/*Vasudeva Chinni Start*/

Block of Code

/*End of Vasudeva Chinni*/

If a single line of code edited:

/*Edited by Vasudeva Chinni*/ before the line edited (recommended) or at the end of the line

At the beginning of every routine, it is helpful to provide standard, boilerplate comments, indicating the routine's purpose, assumptions, and limitations. A boilerplate comment should be a brief introduction that explains why it exists and what it can do.

Avoid adding comments at the end of a line of code; end-line comments make code more difficult to read. However, end-line comments are appropriate when annotating variable declarations, in which case, align all end-line comments at a common tab stop.

Avoid clutter comments, such as an entire line of asterisks. Instead, use white space to separate comments from code.

Avoid surrounding a block comment with a typographical frame. It may look attractive, but it is difficult to maintain.

Prior to deployment, remove all temporary or extraneous comments to avoid confusion during future maintenance work.

If you need comments to explain a complex section of code, examine the code to determine if you should rewrite it. If at all possible, do not document bad code — rewrite it. Although performance should not typically be sacrificed to make the code simpler for human consumption, a balance must be maintained between performance and maintainability.
Use complete sentences when writing comments. Comments should clarify the code, not add ambiguity.

Comment as you code because you will not likely have time to do it later. Also, should you get a chance to revisit code you have written, that which is obvious today probably will not be obvious six weeks from now.

Avoid superfluous or inappropriate comments, such as humorous sidebar remarks.
Use comments to explain the intent of the code. They should not serve as inline translations of the code.

Comment anything that is not readily obvious in the code.
To prevent recurring problems, always use comments on bug fixes and work-around code, especially in a team environment.

Use comments on code that consists of loops and logic branches. These are key areas that will assist source code readers.

Throughout the application, construct comments using a uniform style with consistent punctuation and structure.

Separate comments from comment delimiters with white space. Doing so will make comments obvious and easy to locate when viewed without color clues.

Example:

/*
Your comments here for multiline comments
*/

//  Your comment here for single line comments

3. Format

Formatting makes the logical organization of the code obvious. Taking the time to ensure that the source code is formatted in a consistent, logical manner is helpful to you and to other developers who must decipher the source code.
The following points are recommended formatting techniques.
Establish a standard size for an indent, such as four spaces, and use it consistently. Align sections of code using the prescribed indentation.
Use a monotype font when publishing hard-copy versions of the source code.
Align open and close braces vertically where brace pairs align, such as:

for (I = 0; I < 100; i++)
{
            …
}

Indent code along the lines of logical construction. Without indenting, code becomes difficult to follow, such as:

If …
If …
else

Establish a maximum line length for comments and code to avoid having to scroll the source code editor and to allow for clean hard-copy presentation.

Use spaces before and after most operators when doing so does not alter the intent of the code
Use white space to provide organizational clues to source code. Doing so creates "paragraphs" of code, which aid the reader in comprehending the logical segmenting of the software.

When a line is broken across several lines, make it obvious that it is incomplete without the following line by placing the concatenation operator at the end of each line instead of at the beginning.

Where appropriate, avoid placing more than one statement per line. An exception is a loop in C# or JScript, such as for (i = 0; i < 100; i++).

When writing HTML, establish a standard format for tags and attributes, such as all uppercase for tags and all lowercase for attributes. As an alternative, adhere to the XHTML specification to ensure all HTML documents are valid. Although there are file size trade-offs to consider when creating Web pages, use quoted attribute values and closing tags to ease maintainability.
Divide source code logically between physical files.

Put each major SQL clause on a separate line so statements are easier to read and edit, for example:

            SELECT U_FirstName, U_LastName
            FROM tblUser
            WHERE U_State = ‘Orissa’;

Break large, complex sections of code into smaller, comprehensible modules.
Try to keep the block of code in Region Tags and provide the comments about the code.

Example:

#region [Your comments]

Block of code

#endregion