When it Comes to Asp.Net

Drop All Functions in SQL SERVER

2012-01-03T19:56:00.002+05:30

To DROP All function in SQL SERVER Database use following lines of codes, use it in Query window for particular database.

This code will save lots of time as DROPing All functions in SQL Server database is not a manual process, if we use this

DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects

WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT')

AND category = 0 ORDER BY [name])

WHILE @name IS NOT NULL
BEGIN
    SELECT @SQL = 'DROP FUNCTION [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped Function: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects 
WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND 
category = 0 AND [name] > @name ORDER BY [name])
END
GO

Drop All Views in SQL SERVER

2012-01-03T18:05:00.000+05:30

This is a very common scenario while updating or replacing database in SQL SERVER, we might need to delete all the views in SQL SERVER database. But to delete one view at a times is a time consuming process if database is huge with lots of Views.

To Drop all views in SQL SERVER database use following

DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects 
WHERE [type] = 'V' AND category = 0 ORDER BY [name])
WHILE @name IS NOT NULL
BEGIN
    SELECT @SQL = 'DROP VIEW [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped View: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects 
WHERE [type] = 'V' AND category = 0 AND [name] > @name 
ORDER BY [name])
END
GO

Delete All Store Procedures in SQL SERVER

2012-01-03T17:48:00.000+05:30

While working on SQL Server 2008, there was a situation when I had to delete one Store Procedure at a time :(.
After doing some research to delete all store procedures with some query, I succeeded to save 2-3 hours or labour work :P

To delete all store procedures in single we can use following code in SQL Query.

DECLARE @procedureName varchar(500)
DECLARE cur CURSOR
      FOR SELECT [name] FROM sys.objects WHERE type = 'p'
      OPEN cur

      FETCH NEXT FROM cur INTO @procedureName
      WHILE @@fetch_status = 0
      BEGIN
            EXEC('DROP PROCEDURE ' + @procedureName)
            FETCH NEXT FROM cur INTO @procedureName
      END
      CLOSE cur
      DEALLOCATE cur

This will surely save time, required to Delete All Store Procedures in SQL SERVER Database

Delete All Tables MSSQL

2011-11-22T00:34:00.000+05:30

While working on ASP.Net application, I came across a situation where I had to delete all the Tables of the MSSQL 2008 database, and it was really painful process.

In this case I had to delete all the SPs and all views as well. As I started searching like "Truncate database Sql 2008" or "Delete all tables in MSSQL" I came across two things and it worked for me.

(I am really not sure whether it works in all the cases but for Deleting all tables in SQL, this worked for me.)

Firstly we need to remove all indexs from SQL table, I found following code when I Google the term

DECLARE @indexName NVARCHAR(128)
DECLARE @dropIndexSql NVARCHAR(4000)

DECLARE tableIndexes CURSOR FOR
SELECT name FROM sysindexes
WHERE id = OBJECT_ID(N'tableName') AND
  indid > 0 AND indid < 255 AND
  INDEXPROPERTY(id, name, 'IsStatistics') = 0
ORDER BY indid DESC

OPEN tableIndexes
FETCH NEXT FROM tableIndexes INTO @indexName
WHILE @@fetch_status = 0
BEGIN
  SET @dropIndexSql = N'DROP INDEX tableName.' + @indexName
  EXEC sp_executesql @dropIndexSql

  FETCH NEXT FROM tableIndexes INTO @indexName
END

CLOSE tableIndexes
DEALLOCATE tableIndexes

After this we need to delete all the tables in SQL, for this I used following SP

EXEC sp_MSforeachtable @command1 = "DROP TABLE ?"

It seems that this is a HIDDEN stored procedure in MSSQL.

Access userName asp.net-membership without using Membership.getUser()

2011-11-20T17:58:00.000+05:30

While I was working on Membership and User Management in Asp.Net 2010
Following Scenario

After creating user, admin sets subscribtion for the user.
User logs in with id and password, after validating user, I check for subscription details.
If user is logging in with subscription period he/she has access to the application else he/she will be redirected to Subscription Expired page.

To check subscription, I had requirement to get username and check it in Subscription table.
I did not want to use getUser function, as I just wanted to get user name of currently logged in user.

To get user name I used following line of code

System.Web.HttpContext.Current.User.Identity.Name

Error : Could not find any resources appropriate for the specified culture or the neutral culture

2011-11-18T16:50:00.000+05:30

Just recieved this error:

Could not find any resources appropriate for the specified culture or the neutral culture. Make sure "AjaxControlToolkit.Properties.Resources.NET4.resources" was correctly embedded or linked into assembly "AjaxControlToolkit" at compile time, or that all the satellite assemblies required are loadable and fully signed.

Could not find any resources appropriate for the specified culture or

the neutral culture.  Make sure

"AjaxControlToolkit.Properties.Resources.NET4.resources"

was correctly embedded or linked into assembly "AjaxControlToolkit"

at compile time, or that all the satellite assemblies required are

loadable and fully signed.

Ajax Tool kit realted .Net errors look a lot more worse than they really are.
What we have to do is simply add scriptManager ;)

<ajax:ToolkitScriptManager

ID="ToolkitScriptManager1"

runat="server" />

Use Membership for user management - Basics

2011-11-08T00:23:00.000+05:30

In this article we shall discuss the membership feature in ASP.NET applications. Asp.net has reduced the development drastically by introducing this. Membership feature of Asp.Net drastically reduces the amount of code we have to write to authenticate users at our Web site. In this article we are going to develop User Management system membership class, and SqlMembershipProvider.

Membership feature of Asp.net provides a membership API that simplifies the task of validating user credentials. SqlMembershipProvider uses SQL Database for storing membership details.

Step1: Install Membership Database for SQLMembershipProvider.
To install Membership Database we have to log on to our server with an account that has authority to administer SQL server. After this open Visual Studio 2010 Command prompt.

Run following command
aspnet_regsql.exe -E -S localhost -A m

Where:
-E indicates authenticate using the Windows credentials of the currently logged on user.
-S (server) indicates the name of the server where the database will be installed or is already installed.
-A m indicates add membership support. This creates the tables and stored procedures required by the membership provider.

Above command mainly generates database naming "aspnetdb" with following schema structure and related stored procedures

* Please Note that Related stored procedures also get created with above command. These stored procedures get called internally from Membership provider.

Step2: Configure Forms Authentication
Set following authentication mode in Web.config file

<authentication mode="Forms">
    <forms loginUrl="Login.aspx" 
           protection="All" 
           timeout="30" 
           name="AppNameCookie" 
           path="/FormsAuth" 
           requireSSL="false" 
           slidingExpiration="true" 
           defaultUrl="default.aspx"
           cookieless="UseCookies"
           enableCrossAppRedirects="false"/>
</authentication>

If so many options are not required we can simply use following Tag for authentication

<authentication mode="Forms">
      <forms loginUrl="Login.aspx" timeout="2880" />
    </authentication>

Add the following <authorization> element after the element. This will allow only authenticated users to access the application. The previously established loginUrl attribute of the <authentication> element will redirect unauthenticated requests to the Login.aspx page.

<authorization> 
   <deny users="?" />
   <allow users="*" />
</authorization>

Step 3: Configuring SQLMembership Provider
In Step 1, we created SQL Database for Membership Provider, in this step we will configure SQLMembership Provider in Web.config file.

<connectionStrings>
  <add name="MyConnectionString" connectionString="Data Source=MySqlServer;Initial Catalog=aspnetdb;Integrated Security=SSPI;" />
</connectionStrings>
<system.web>
...
  <membership defaultProvider="SqlProvider" userIsOnlineTimeWindow="15">
    <providers>
      <clear />
      <add 
        name="SqlProvider" 
        type="System.Web.Security.SqlMembershipProvider" 
        connectionStringName="MyConnectionString"
        applicationName="/"
        enablePasswordRetrieval="false"
        enablePasswordReset="true"
        requiresQuestionAndAnswer="true"
        requiresUniqueEmail="true"
        passwordFormat="Hashed" />
    </providers>
  </membership>

Default passwordFormat is "Hashed", if we remove it from Configuration then by default Passwords are stored in Hashed format. We can change passwordFormat to Encrypted. (This is not the scope of this Article; I shall put another article for it.)

Step 4: Start using Membership class
Upto step 3 we were only doing configuration for using Membership class. After we done with step 3; whenever we use Membership class, if by default uses Database structure generated in Step 1. It internally manages all the Database calls.
e.g.

Membership.CreateUser("UserName","Password");
//This will create user in Database.

Check out following Membership APIs for user management in Asp.net.

Method	Parameters	Notes
CreateUser	string usernameUser name to create. string passwordPassword for new user string emailE-mail for new user. string passwordQuestion string passwordAnswer bool IsApproved object providerUserKey	Used to create a new user.
DeleteUser	string usernameUser to delete. bool removeAllRelatedData	Used to immediately remove a user identified by the supplied username. Returns true if the user was deleted or false if not found.
FindUsersByName	string usernameToMatch int pageIndex int pageSize	Returns a collection of users where the string parameter passed matches part of the username. Wildcard support depends on how each data store handles characters such as "*", "%" and "_".
FindUsersByEmail	string emailToMatch int pageIndex int pageSize	Returns a collection of users whose e-mail addreses matches any part of the string parameter passed. Wildcard support depends on how each data store handles characters such as "*", "%" and "_"
GeneratePassword	int length Int numberOfNonAlpha NumericCharacters	Returns a password of the specified length that contains the specified number of non-alphanumeric characters.
GetAllUsers	int pageIndex int pageSize	Returns a subset of users from the collection of all users. The subset is based on the pageIndex and pageSize methods.
GetNumberOfUsersOnline	None	Returns a count of all the users who are currently online The Active Directory provider does not implement this functionality
GetUsernameByEmail	string emailEmail of user to lookup.	Return a member's username.
UpdateUser	MembershipUser userMembership user to update	Updates a member's properties; for example, an e-mail address.
ValidateUser	string usernameUser name to validate. string passwordUser password to validate.	Validates a user's credentials. Returns true if the credentials are valid and false if they are not. With Active Directory, regardless of the configured connection credentials, the provider connects to the directory with the username and password parameter as the connection credentials.

(Note: Above table is picked up from MSDN)

This is how we can use Membership class for User management in Asp.net using SQLMembershipProvider.

Validation of viewstate mac failed

2011-10-07T18:12:00.001+05:30

At times when we host our web application to server, we get following error.

“Validation of viewstate MAC failed. If this application is hosted by a Web Farm or cluster, ensure that configuration specifies the same validationKey and validation algorithm. AutoGenerate cannot be used in a cluster"

The root cause of this error is, variation in the key to encrypt and decrypt viewstate data. View state data is transferred between the client and the server is always validated. This is required to ensure that viewstate data is not tempered. Viewstate data is in envrypted formate and is decrypted while validating it, unique key is used to encrypt and decrypt viewstate data. When application is hosted on single machine, there is no issue of key, as key remains same for single machine. But in case of web farm or other hosting environment in that case we have to fix this issue by providing encryption key.

Add following line of in web.config under system.web tag

  
< / machinekey>

Machine key configuration

Even we can choose not to do validation of view state by adding following line of code, this disables validation of data which is not prefered in most of the environments.

Refer this link ---> http://msdn.microsoft.com/en-us/library/w8h3skw9.aspx

About Cloud Hosting

2011-09-10T11:20:00.001+05:30

What is Cloud hosting?

Unlike traditional approach of single server hosting, a cloud-hosted website opearts on multiple connected servers; the website now has the access to multiple servers. Virtually, the processing power is unlimited as we can always add a new server which scale up the processing power.

Benefits of cloud hosting

It’s scalability and cost efficient is the commonly known advantages. Highly scalable technology (load balancing, hardware upgrades, etc), virtually no limitations on website expansion.

NO hassle of migrating website from a shared server to a dedicated server.

NO server crash when website experienced a sudden surge.

In terms of costing cloud hosting companies charge pay-per-use basis, so no need to reserve massive server powers to avoid website crash from sudden traffic surge. So Cost is another huge plus if we need a lot of processing power.

Cloud hosting companies charge their users based on the quantity of computing power consumed.

Facebook like Autosuggest in Asp.net

2011-08-06T19:25:00.003+05:30

This article is about using AjaxToolKit AutoCompleteExtender, to make Facebook like Autosuggest in asp.net. To use AutoCompleteExtender is simple, but we can modify the code little bit and make it more user friendly. Check out following Image. (Download Demo code for better understanding.)

Note: Democode is developed in VS2010 and .net framework 4.0

AutoComplete extender can be used to extend behaviour of any ASP.NET TextBox control. Ajaxtoolkit autocomplete extender associates textbox control with a popup panel to display words which begins with the prefix that is entered into the text box. We can define the minimum length of charachters, after which extender displays a popup containing words or phrases that start with that value.

</div>
</div><h2>
AutoComplete Demonstration</h2>
Type some characters in this textbox.  The web service returns names which contains
           text you have typed.
<table>
<tbody>
<tr>            <td>
<asp:textbox autocomplete="off" id="txtAutoComplete" runat="server" width="300"></asp:textbox></td>            <td><div id="divLoading" style="display: none;">
Loading...</div>
</td>            </tr>
</tbody></table>
    <ajaxtoolkit:autocompleteextender behaviorid="AutoCompleteEx" 
      completioninterval="500" 
      completionlistcssclass="completionListClass"
      completionlisthighlighteditemcssclass="CompletionListHighlightedItemClass" 
      completionlistitemcssclass="completionlistItemClass" 
      completionsetcount="0"
      delimitercharacters=";, :" 
      enablecaching="true"
      id="autoComplete1"
      minimumprefixlength="2"
      onclienthidden="ListPopulated"
      onclientitemselected="onSelection" 
      onclientpopulated="ItemSelected"  
      onclientpopulating="ListPopulating"
      runat="server" 
      servicemethod="GetCompletionList" 
      servicepath="AutoComplete.asmx"
      showonlycurrentwordincompletionlistitem="true"
      targetcontrolid="txtAutoComplete">
              
  <animations>
     <onshow>
     <sequence>
     <%-- Make the completion list transparent and then show it --%>
     <opacityaction opacity="0">
       <hideaction visible="true">
                            
      <%--Cache the original size of the completion list the first time the animation is played and then set it to zero --%>
      <scriptaction script="
      // Cache the size and setup the initial size
      var behavior = $find('AutoCompleteEx');
       if (!behavior._height)
          {
           var target = behavior.get_completionList();
           behavior._height = target.offsetHeight - 2;
           target.style.height = '0px';
           }">
                        
      <%-- Expand from 0px to the appropriate size while fading in --%>
      <parallel duration=".4">
      <fadein>
      <length endvaluescript="$find('AutoCompleteEx')._height" propertykey="height" startvalue="0">
      </length></fadein></parallel>
                        </scriptaction></hideaction></opacityaction></sequence>
                    </onshow>
                    <onhide>
                        <;%-- Collapse down to 0px and fade out --%>;
                        <parallel duration=".4">
                            <fadeout>
                            <length endvalue="0" propertykey="height" startvaluescript="$find('AutoCompleteEx')._height">
                        </length></fadeout></parallel>
                    </onhide>
                </animations>
            </ajaxtoolkit:autocompleteextender>
        
<%-- Prevent enter in textbox from causing the collapsible panel from operating --%>
 <input style="display: none;" type="submit" />
           

<div id="divAddressDetails">
</div>
</div>

To Make AutoExtender work, we need to define certain properties.

TargetControlID: The TextBox control where the user types content to be automatically completed.
ServicePath: The path to the web service that the extender will pull the word\sentence completions from. If this is not provided, the service method should be a page method.
ServiceMethod - The web service method to be called. The signature of this method must match the following:
```
[System.Web.Services.WebMethod]
[System.Web.Script.Services.ScriptMethod]
public string[] YourFunctionName(string prefixText,int count)
```
Syntax of the function should remain same.
CompletionListCssClass - Css Class that will be used to style the completion list flyout.
CompletionListItemCssClass - Css Class that will be used to style an item in the AutoComplete list flyout.
CompletionListHighlightedItemCssClass - Css Class that will be used to style a highlighted item in the AutoComplete list flyout.

(Refer DEMO CODE for better Understanding...)

In above HTML Autocompletete extender; we can display loading image; this will make user understand that something is happening behind the screen. We have used three properties to display & Hide loading image and to display YELLOW background for the text as in above screen shot...

OnClientPopulating: This adds event handler to Client side populating event. In our demo we have used ListPopulating function as event handler, we use this handler to display loading image, which is as follows.

function ListPopulating(source, e) {
   window.document.getElementById('divLoading').style.display = "";
  
   var textboxControl = window.document.getElementById(source.get_element().id);
   // Get the textbox control.
  
   textboxControl.style.background = "url(Images/loader.gif) no-repeat right";
  //Above code displays loading image inside text box...
                }

OnClientHidden: This adds event handler to Client side hidden event of AutoExtentender. This handler gets fired when Autocomplete popup goes hidden. We have used ListPopulated function to handle this event. We use this event to hide loading image.

function ListPopulated(source, e) {

  window.document.getElementById('divLoading').style.display = "none";
                    
  var textboxControl = window.document.getElementById(source.get_element().id); 
  // Get the textbox control.
                    
  textboxControl.style.background = "";
  }

OnClientPopulated: This event handler can be used when Autosuggest list is populated. We have used this event handler to display YELLOW background in typed text. Check out above screenshot. Function name for this event is ItemSelected, this JavaScript function is as follows.

function ItemSelected(source, e) {
   window.document.getElementById('divLoading').style.display = "none";
   var customers = source.get_completionList().childNodes;
   var searchText = source.get_element().value;

   for (var i = 0; i < customers.length; i++) {

   var customer;//  eval('(' + customers[i]._value + ')');

   customers[i].innerHTML = customers[i].innerHTML.replace(new RegExp('(' + searchText + ')', 'gi'), "$1");

   //We can modify the innerHTML if we want to display more information.
   // e.g. some logo or image
        }  
   }

Apart from HTML and javascript we used a webmethod naming GetCompletionList which is as follows

[WebMethod]
    public string[] GetCompletionList(string prefixText, int count)
    {

        //string sql = "WRITE SQL QUERY with Like Statement and use prefix text e.g. PartnerName like '%" + prefixText + "%'";

        //SqlDataAdapter da = new SqlDataAdapter(sql, "ConnectionString");
       
        DataTable dt = DT;// new DataTable();
        DataRow [] dr = dt.Select("Name LIKE '%" + prefixText + "%'");
        //    da.Fill(dt);
        //    da.Dispose();
        
        if (count == 0)
        {
            count = dr.Length;
            //count = dt.Rows.Count;
        }

       
        List items = new List(count);
        for (int i = 0; i < count; i++)
        {
            string value = dr[i]["City"].ToString() + "|" + dr[i]["State"].ToString() + "|" + dr[i]["Country"].ToString() + "|" + dr[i]["PostalCode"].ToString();
            var Items = AjaxControlToolkit.AutoCompleteExtender.CreateAutoCompleteItem(dr[i]["Name"].ToString(), value);
            //Using this we can send Name, Value pair client side. 
            //Value can be used to display related data of selected Name.

            //string value = dt.Rows[i]["City"].ToString() + "|" + dt.Rows[i]["State"].ToString() + "|" + dt.Rows[i]["Country"].ToString() + "|" + dt.Rows[i]["PostalCode"].ToString();
            //var items1 = AjaxControlToolkit.AutoCompleteExtender.CreateAutoCompleteItem(dt.Rows[i]["Name"].ToString(), value);

            items.Add(Items);
        }
        

        return items.ToArray();
    }

//Ignore above string string tag it is coming due to some editor issue of auto completing tags, some syntax issue.

Check out follwing screen shot, on selection of User we are displaying his/her information. (Note that we have created Name-Value pair in above code to display the information in following screen shot format.)

Now to display Selected User related address information as in the Screen shot, we used event handler of AutoCompleteExtender naming OnClientItemSelected

OnClientItemSelected: Handler to attach to the client-side itemSelected event. We used function naming onSelection to display Address details, this JavaScript function is as follows

function onSelection(source, e) {

var htmlString = "Address Details " +
"



City " + e._value.split("|")[0] + "
State " + e._value.split("|")[1] + "
Country " + e._value.split("|")[2] + "
Postal Code " + e._value.split("|")[3] + "
";

// Above alignement might disorder due to HTML rules of blogger, make sure in coding you handle it.

    window.document.getElementById('divAddressDetails').innerHTML = htmlString;
    var customers = source.get_completionList().childNodes;
                    
     //Following is used to put value in Textbox, as we are overriding it to display address.
     if (document.all) {
     window.document.getElementById(source.get_element().id).value = e._item.innerText;
        }
     else window.document.getElementById(source.get_element().id).value = e._item.textContent;

      }

We are done with development of Facebook Like Autosuggest in Asp.net.
Donwload demo code here --> Facebook Like AutoSuggest in Asp.net.zip

Send Email In Asp.net Using Gmail

2011-07-19T15:31:00.001+05:30

We can use Gmail smtp server to send email, using Asp.Net. Before we start developing Asp.net code, make sure POP is enabled in your Gmail's mail setting.

Once you done with this step SAVE your Gmail settings....

To do Email sending in Asp.net we need "System.Net.Mail" namespace.

public static Boolean SendEmail()
{
  MailMessage mail = new MailMessage();

mail.From = new MailAddress("YOUR FROM EMAIL ADDRESS");
mail.To.Add(new MailAddress("YOUR TO EMAIL ADDRESS"));            
mail.Bcc.Add(new MailAddress("YOUR BCC EMAIL"));
            
  mail.Subject = "Email using Gmail";

  string Body = "Put your Email Body TEXT here"; 
// Here you can put HTML string if you want email to be sent in HTML format.
  mail.Body = Body;

  mail.IsBodyHtml = true;
  SmtpClient smtp = new SmtpClient();
  smtp.Host = "smtp.gmail.com"; //Or Your SMTP Server Address
  smtp.Credentials = new System.Net.NetworkCredential
       ("Username@gmail.com","GmailPassword");
//Or your Smtp Email ID and Password
  smtp.EnableSsl = true;
  smtp.Send(mail);
}

This is simple function to send email in Asp.net using Gmail account.
You can also find Copy of this email in Your SENT items.

Disable Text Selection using Javascript

2011-05-05T16:07:00.000+05:30

In the web development cycle, at times we come to a requirement where we need to disable text selection for some HTML Tags i.e. "Do not allow to select text present in some particular Div tag, or span tag or text present in some table" in that case we can use following function. Actually this JavaScript function can be used to Disable Text selection for whole body of the HTML.

function disableSelection(target) {
        if (typeof target.onselectstart != "undefined") //IE route
            target.onselectstart = function () { return false }
        else if (typeof target.style.MozUserSelect != "undefined") //Firefox route
            target.style.MozUserSelect = "none"
        else //All other route (ie: Opera)
            target.onmousedown = function () { return false }
        target.style.cursor = "default"
    }

Define above function in HEAD tag and call this function either on some form event or directly at the bottom of the HTML page.

//disableSelection(document.body) //Disable text selection on entire body

disableSelection(document.getElementById("mydiv")) //Disable text selection on element with id="mydiv"

This is very small function but it was very useful for one of my project.

Add Update Delete in Gridview Asp.net

2011-04-30T16:35:00.000+05:30

This article is about simple way to Add Update and Delete records using asp.net GridView using Dataset or Datatable. We shall update the database table naming (Download Demo)

"UserTable".

Schema for UserTable is as follows

Note:I have used SqlServer 2008

For this article we are going to fill GridView by data from database.

We are going to make perform following operations on data using GridView

Add New record into database; here data will directly be added into database table
Update Records into database, using edit link.
Delete Records using delete link

To make Add Update Delete in Gridview more user friendly; make sure your website is Ajax Enabled and Gridview is put under UpdatePanel. This will avoid unnecessary

postback and Gridview control will look smart.

Before we develop GridView, lets work out main functions for database operations. Create a Class File naming "ManageUsers" and add following functions one by one

Fetch Data from Database.

public DataTable Fetch()
    {
        string sql = "Select * From UserTable";
        SqlDataAdapter da = new SqlDataAdapter(sql, cnstr);
        DataTable dt = new DataTable();
        da.Fill(dt);
        return dt;
        // Write your own Logic for fetching data, this method should return a DataTable
    }

Update Data into Database

public void Update(int id, string FirstName, string LastName, string EmailAddress, string LoginId, string Password, string StartDate, string EndDate)
    {
        string sql = "UPDATE UserTable SET [First Name] = '"+ FirstName + "',[Last Name] = '" + LastName + "',[Login Id] = '" + LoginId +"' ,[Password] = '" + Password 

+ "'";
             sql += ",[Start Date] = '" + StartDate +"',[End Date] = '" + EndDate +"',[Email Address] = '" + EmailAddress + "' WHERE Id=" + id;

        SqlConnection conn = new SqlConnection(cnstr);
        conn.Open();
        SqlCommand cmd = new SqlCommand(sql, conn);
        cmd.ExecuteNonQuery();
        conn.Close();
        conn.Dispose();

    }

Insert new records into Database

public void Insert(string FirstName, string LastName, string EmailAddress, string LoginId, string Password, string StartDate, string EndDate)
    {
       string sql = "INSERT INTO UserTable ([First Name],[Last Name],[Login Id],[Password],[Start Date],[End Date],[Email Address]) ";
       sql +=" VALUES ('"+ FirstName +"','" + LastName + "','" + LoginId +"','" + Password + "','" + StartDate + "','" + EndDate + "','" + EmailAddress + "')";
           
        SqlConnection conn = new SqlConnection(cnstr);
        conn.Open();
        SqlCommand cmd = new SqlCommand(sql, conn);
        cmd.ExecuteNonQuery();
        conn.Close();
        conn.Dispose();
    }

Delete records from Database

public void Delete(int id)
    {
        string sql = "DELETE FROM UserTable WHERE Id=" + id;
        SqlConnection conn = new SqlConnection(cnstr);
        conn.Open();
        SqlCommand cmd = new SqlCommand(sql, conn);
        cmd.ExecuteNonQuery();
        conn.Close();
        conn.Dispose();
        // Write your own Delete statement blocks.
    }

In above code cnstr is the connection string for our

database.

Now we shall make changes in GridView (Note: Make sure GridView is added on page and under updatepanel) (Download Demo)

Once we add gridview inside updatepanel, do following things

set AutoGenerateColumns as False.
Change the ShowFooter Flag to True
set the DataKeyNames your column name for Id. (This field can have multiple values depending on requirement, we are going to use Id, as it is primary key of our datatable. These values are available in GridView events. We can access them using. GridView.DataKeys[e.RowIndex].Values[0]
Smart Navigation Tag of the GridView control, choose Add New Column

Now add 8 BoundField columns with DataField values as "Id","First Name","Last Name","Email Address", "Login Id", "Password", "Start Date", "End Date"; also add 2 CommandField columns with one for Edit/Update and another for Delete functions. Now we can see our Grid View control is ready. Above data fields are column names of the DataTable we are using. (To bind correct columns, our datatable column names and gridview bound column's "DataField" should match.
We shall also give facility to add new records, for that we will put controls in Footer row. To do this we need to convert all above BoundField columns to template field columns. Click on the Smart Navigation Tag on the GridView choose Edit Columns, the Field’s property window will open. Select column by column from Id, include also Edit column, and select ‘Convert this field into a TemplateField’

Except "delete" column all the BoundField columns are converted in to Template Field Column.
Now one by one we will controls to Footer of the GridView. Right click on the GridView control, select Edit Template.
Column[0] – Id: select Edit Template, choose column[0] – Id, you can view a label placed in the ItemTemplate section and a TextBox placed in the EditItemTemplate section (Id is our Primary key and it should not be editable). In edit Item Section template, put Label. Do not add anything in Footer Template.

Column[1] - First Name
Now again select Edit Template, choose column[1] - First Name, Add another TextBox in the FooterTemplate section and name it as NewFirstName.

Column[2] - Last Name
Choose column[2] - Last Name, Add another TextBox in the FooterTemplate section and name it as NewLastName.

Column[3] - Email Address
Choose column[3] - Email Address, Add another TextBox in the FooterTemplate section and name it as NewEmailAddress.

Column[4] - Login Id
Choose column[4] - Login Id, Add another TextBox in the FooterTemplate section and name it as NewLoginId.

Column[5] - Password
Choose column[5] - Password, Add another TextBox in the FooterTemplate section and name it as NewPassword.

Column[6] - Start Date
Choose column[6] - Start Date, Add another TextBox in the FooterTemplate section and name it as NewStartDate. (Check following source view, we have added, Calender Extender of Ajax Toolkit)

Column[7] - End Date
Choose column[7] - End Date, Add another TextBox in the FooterTemplate section and name it as NewEndDate. (Check following source view, we have added, Calender Extender of Ajax Toolkit)

Column[8] - Edit
Just add a link button into the FooterTemplate section, specify its CommandName property as ‘AddNew’.

Source of the Gridview control changes to following, check we have added Calender Extender for date selection. (Download Demo)

Now we shall develop code in code behind of web page i.e. in C#. We have already developed ManageUsers Class in start. We will develop code for handing Edit,Update and Insert operations of GridView. Add events as shown in following image.

Now check out following code for each event we have added for GridView.

Create object of the Class "ManageUsers"; and write function to Bind Customer details to the GridView.

private void BindCustomers()
    {
        DataTable CustomerTable = customer.Fetch();

        if (CustomerTable.Rows.Count > 0)
        {
            gridUserManagement.DataSource = CustomerTable;
            gridUserManagement.DataBind();
        }
        else
        {
            CustomerTable.Rows.Add(CustomerTable.NewRow());
            gridUserManagement.DataSource = CustomerTable;
            gridUserManagement.DataBind();

            int TotalColumns = gridUserManagement.Rows[0].Cells.Count;
            gridUserManagement.Rows[0].Cells.Clear();
            gridUserManagement.Rows[0].Cells.Add(new TableCell());
            gridUserManagement.Rows[0].Cells[0].ColumnSpan = TotalColumns;
            gridUserManagement.Rows[0].Cells[0].Text = "No Record Found";
        }
    }

Initializing the GridView control on Page load:

protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            BindCustomers();
        }
    }

GridView RowEditing Event

protected void gridUserManagement_RowEditing(object sender, GridViewEditEventArgs e)
    {
        gridUserManagement.EditIndex = e.NewEditIndex;
        BindCustomers(); 
    }

GridView RowCancelingEdit Event

protected void gridUserManagement_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
    {
        gridUserManagement.EditIndex = -1;
        BindCustomers(); 
    }

Updating Records (GridView RowUpdating Event): Update the data to the UserTable, by adding the following lines of code in the GridView’s RowUpdating event

protected void gridUserManagement_RowUpdating(object sender, GridViewUpdateEventArgs e)
    {
        TextBox txtNewFirstName = (TextBox)gridUserManagement.Rows[e.RowIndex].FindControl("TextBox2");
        TextBox txtNewLastName = (TextBox)gridUserManagement.Rows[e.RowIndex].FindControl("TextBox3");
        TextBox txtNewEmailAddress = (TextBox)gridUserManagement.Rows[e.RowIndex].FindControl("TextBox4");
        TextBox txtNewLoginId = (TextBox)gridUserManagement.Rows[e.RowIndex].FindControl("TextBox5");
        TextBox txtNewPassword = (TextBox)gridUserManagement.Rows[e.RowIndex].FindControl("TextBox6");
        TextBox txtNewStartDate = (TextBox)gridUserManagement.Rows[e.RowIndex].FindControl("TextBox8");
        TextBox txtNewEndDate = (TextBox)gridUserManagement.Rows[e.RowIndex].FindControl("TextBox9");

        customer.Update(Convert.ToInt32(gridUserManagement.DataKeys[e.RowIndex].Values[0].ToString()), txtNewFirstName.Text, txtNewLastName.Text, txtNewEmailAddress.Text, txtNewLoginId.Text, txtNewPassword.Text, txtNewStartDate.Text, txtNewEndDate.Text);
        gridUserManagement.EditIndex = -1;
        BindCustomers(); 
    }

The above block of codes in RowUpdating event, finds the control in the GridView, takes those values in pass it to the ManageUsers class Update method. The first parameter GridView1.DataKeys[e.RowIndex].Values[0].ToString() will return the Id of the Customer. That is the unique id for each customer to perform update function.

Delete In GridView:

protected void gridUserManagement_RowDeleting(object sender, GridViewDeleteEventArgs e)
    {
        customer.Delete(Convert.ToInt32(gridUserManagement.DataKeys[e.RowIndex].Values[0].ToString()));
        BindCustomers(); 
    }

Add New Records from GridView control

protected void gridUserManagement_RowCommand(object sender, GridViewCommandEventArgs e)
    {
        if (e.CommandName.Equals("AddNew"))
        {
            TextBox txtNewFirstName = (TextBox)gridUserManagement.FooterRow.FindControl("NewFirstName");
            TextBox txtNewLastName = (TextBox)gridUserManagement.FooterRow.FindControl("NewLastName");
            TextBox txtNewEmailAddress = (TextBox)gridUserManagement.FooterRow.FindControl("NewEmailAddress");
            TextBox txtNewLoginId = (TextBox)gridUserManagement.FooterRow.FindControl("NewLoginId");
            TextBox txtNewPassword = (TextBox)gridUserManagement.FooterRow.FindControl("NewPassword");
            TextBox txtNewStartDate = (TextBox)gridUserManagement.FooterRow.FindControl("NewStartDate");
            TextBox txtNewEndDate = (TextBox)gridUserManagement.FooterRow.FindControl("NewEndDate");

            customer.Insert(txtNewFirstName.Text, txtNewLastName.Text, txtNewEmailAddress.Text, txtNewLoginId.Text, txtNewPassword.Text, txtNewStartDate.Text, txtNewEndDate.Text);
            BindCustomers();
        } 
    }

This is how we can develop a simple GridView control in Asp.Net with Add Update Delete functionality.

Download Demo Code here --> Add Update Delete in Gridview Asp.net

Javascript String Replace All Function

2011-04-29T18:52:00.000+05:30

JavaScript String Replace function only replaces the first occurrence in the string. Replace function takes two simple parameters, The first parameter is the pattern to find and the second parameter is the string to replace the pattern with when found. The javascript function does not Replace All...

str = str.replace(”Pattern”,”ReplaceString”)

To ReplaceAll you have to do it a little differently. To replace all occurrences in the string, use the g modifier like this:
str = str.replace(/Pattern/g,”ReplaceString”)

Active Directory Authentication Asp.net

2011-03-16T15:02:00.000+05:30

Very offen in web development, we come across a requirement where we need to use Active Directory Authentication in Asp.net; i .e. we need to use Active Directory to Authenticate user. The flow for the same can be assumed as following point. In this case we shall not have User table in Database unless required, we can verify the Authenticity of user with Active Directory.

User logs in with Active Directoyl Login and Password.
System verifies Login and Password with Active Directory
If we get Success we will proceed the login else will display error.

We shall use following C# class naming AuthenticateUser to implement ActiveDirectory Authentication in Asp.net

public class AuthenticateUser
{
    public AuthenticateUser()
    {
        //
        // TODO: Add constructor logic here
        //
    }
    private string _path;
    private string _filterAttribute;

    public AuthenticateUser(string path)
    {
        _path = path;
    }

    public bool IsAuthenticated(string domain, string username, string pwd)
    {
        string domainAndUsername = domain + @"\" + username;
        DirectoryEntry entry = new DirectoryEntry(_path, domainAndUsername, pwd);

        try
        {
            //Bind to the native AdsObject to force authentication.
            object obj = entry.NativeObject;

            DirectorySearcher search = new DirectorySearcher(entry);

            search.Filter = "(SAMAccountName=" + username + ")";
            search.PropertiesToLoad.Add("cn");
            SearchResult result = search.FindOne();

            if (null == result)
            {
                return false;
            }

            //Update the new path to the user in the directory.
            _path = result.Path;
            _filterAttribute = (string)result.Properties["cn"][0];
        }
        catch (Exception ex)
        {
            throw new Exception("Error authenticating user. " + ex.Message);
        }

        return true;
    }


}

Remeber we have added "System.DirectoryServices" name space.

We can simply use following code to Authenticate user using ActiveDirectory in asp.net

Note: In following code we use Authentication on Page Load event, you can implement this on Login Page.

protected void Page_Load(object sender, EventArgs e)
    {
        AuthenticateUser cls = new AuthenticateUser("LDAP://YourActiveDirectoryPath"); //Set Active Directory Path
        bool flag = cls.IsAuthenticated("ZESTORMTPL", "UserLoginID", "Password");
        //Put your UserLoginID and Password
    }

Active Directory Authentication Asp.net is as simple as copy pasting above code and use it as it is.

Stock Market Ticker For Website - Getting Historical Data

2011-02-11T14:30:00.000+05:30

To get Historical Data from Yahoo Finance, we can use certain parameters in Query String. Check out following query string for NASDAQ (i.e. Symbol "^IXIC");

http://ichart.finance.yahoo.com/table.csv?s=^IXIC&a=01&b=5&c=1971&d=01&e=8&f=2011&g=m&ignore=.csv

If we analyze above link we can see some parameters varying from a to g. We can use these parameters to filter HISTORICAL data as per our requirement. Each of above parameter (i.e. from a to g) indicates following things

s - Ticker symbol.(This parameter is not optional; other parameters are optional)
a - Month number, starting with 0 for January.
b - Day number, eg, 1 for the first of the month.
c - Year.

End date for historical prices (default is the most current available closing price):
d - Month number, starting with 0 for January.
e - Day number, eg, 1 for the first of the month.
f - Year.
g - Possible values are 'd' for daily (the default), 'w' for weekly, and 'm' for monthly.

We can download csv file and modify its URL run time as per our requirements.

Stock Market Ticker For Website - Yahoo Finance API

2011-02-01T10:08:00.000+05:30

In My Previous article Stock Market Ticker For Website; we have seen how to USE YAHOO FINANCE URL, i.e. remote CSV file available for Download to use in Stock Market Ticker.

Suppose we want to download and CSV file with Market Indices
But to display various details related to stock market YAHOO Finance has allowed us to take advantage of live data. Do check out my article about Stock Market Ticker For Website

In previous article we use following link to get market data

http://download.finance.yahoo.com/d/quotes.csv?s=^DJI,^IXIC,^GSPC,^FTSE,^GDAXI,^FCHI,^N225,^HSI,^STI,^BSESN,^NSEI&f=sl1c1&e=.csv

Check out "f=sl1c1" this is where we need to apply following code for getting desired details. e.g. if we want to get "day-low,day-high, 52-week low & 52-week high" along with symbol(s), last trade(price)(l1) and change(c1) from YAHOO FINANCE, our "f=sl1c1" will change to "f=sl1c1ghjk". This is very simple, use following table for getting desired data about Stock exchanges or for equities. Yes we can also get Cross Currency rates from YAHOO FINANCE (e.g. Symbol "USDINR=X,USDEUR=X")

a	Ask	a2	Average Daily Volume	a5	Ask Size
b	Bid	b2	Ask (Real-time)	b3	Bid (Real-time)
b4	Book Value	b6	Bid Size	c	Change & Percent Change
c1	Change	c3	Commission	c6	Change (Real-time)
c8	After Hours Change (Real-time)	d	Dividend/Share	d1	Last Trade Date
d2	Trade Date	e	Earnings/Share	e1	Error Indication (returned for symbol changed / invalid)
e7	EPS Estimate Current Year	e8	EPS Estimate Next Year	e9	EPS Estimate Next Quarter
f6	Float Shares	g	Day’s Low	h	Day’s High
j	52-week Low	k	52-week High	g1	Holdings Gain Percent
g3	Annualized Gain	g4	Holdings Gain	g5	Holdings Gain Percent (Real-time)
g6	Holdings Gain (Real-time)	i	More Info	i5	Order Book (Real-time)
j1	Market Capitalization	j3	Market Cap (Real-time)	j4	EBITDA
j5	Change From 52-week Low	j6	Percent Change From 52-week Low	k1	Last Trade (Real-time) With Time
k2	Change Percent (Real-time)	k3	Last Trade Size	k4	Change From 52-week High
k5	Percebt Change From 52-week High	l	Last Trade (With Time)	l1	Last Trade (Price Only)
l2	High Limit	l3	Low Limit	m	Day’s Range
m2	Day’s Range (Real-time)	m3	50-day Moving Average	m4	200-day Moving Average
m5	Change From 200-day Moving Average	m6	Percent Change From 200-day Moving Average	m7	Change From 50-day Moving Average
m8	Percent Change From 50-day Moving Average	n	Name	n4	Notes
o	Open	p	Previous Close	p1	Price Paid
p2	Change in Percent	p5	Price/Sales	p6	Price/Book
q	Ex-Dividend Date	r	P/E Ratio	r1	Dividend Pay Date
r2	P/E Ratio (Real-time)	r5	PEG Ratio	r6	Price/EPS Estimate Current Year
r7	Price/EPS Estimate Next Year	s	Symbol	s1	Shares Owned
s7	Short Ratio	t1	Last Trade Time	t6	Trade Links
t7	Ticker Trend	t8	1 yr Target Price	v	Volume
v1	Holdings Value	v7	Holdings Value (Real-time)	w	52-week Range
w1	Day’s Value Change	w4	Day’s Value Change (Real-time)	x	Stock Exchange
y	Dividend Yield

Download Demo code here --> Stock Market Ticker for Websites(MarketWatch.zip)
Above demo code is associated with my previous article.

Stock Market Ticker For Website

2011-01-19T17:34:00.000+05:30

In this article we shall work out on Live stock market ticker for website. To develop live stock market ticker for website the basic requirement is to have live data. Free live data is available at some cost, but to display tickers on website we can always go for two to three minutes delayed data, which we can download from Yahoo Finance. Yahoo Finance is giving enable use to download CSV file in the manner we want. Once we get the CSV file logic which remains is to display data and Polling of data at certain interval

Check out folder structure of our Demo Code

(Note: Demo contains Cross Currency Data as well; which will be same as Stock market data.)

In this article we shall be considering major stock exchanges, and in demo code we shall see updates related to stock exchanges.

DOW (Symbol: ^DJI)

Nasdaq (Symbol: ^IXIC)

S&P 500 (Symbol: ^GSPC)

FTSE 100 (Symbol: ^FTSE)

DAX (Symbol: ^GDAXI)

CAC 40 (Symbol: ^FCHI)

Hang Seng (Symbol: ^HSI)

NIKKEI 225 (Symbol: ^N225)

Straits Times (Symbol: ^STI)

BSE India (Symbol: ^BSESN)

NSE India (Symbol: ^NSEI)

If you go to Yahoo Finance and put any of the Symbol you can check all related data. Also that data is allowed to download, we are going to use this downloaded data to display live (little delayed) data to develop Stock Market Ticker for Website. (Note: If you are not aware about ICALLBack Event handler, please refer this article ---> ICallback Event Handler Article

We are going to get data from Yahoo Finance in CSV format. So we shall write logic accordingly. Following is the function to convert CSV in to Data Table.

public static DataTable csvToDataTable(string strData, bool isRowOneHeader)
    {
        DataTable csvDataTable = new DataTable();
        //no try/catch - add these in yourselfs or let exception happen
        String[] csvData = strData.Replace("\r", "").Replace("=X", "").Split('\n');
        //if no data in file ‘manually’ throw an exception
        if (csvData.Length == 0)
        {
            throw new Exception("CSV File Appears to be Empty");
        }

        String[] headings = csvData[0].Split(',');
        int index = 0; //will be zero or one depending on isRowOneHeader
        if (isRowOneHeader) //if first record lists headers
        {
            index = 1; //so we won’t take headings as data
            //for each heading
            for (int i = 0; i < headings.Length; i++)
            {
                //replace spaces with underscores for column names
                headings[i] = headings[i].Replace("", "_");
                //add a column for each heading
                csvDataTable.Columns.Add(headings[i], typeof(string));
            }
        }
        else //if no headers just go for col1, col2 etc.
        {
            for (int i = 0; i < headings.Length; i++)
            {
                //create arbitary column names
                csvDataTable.Columns.Add("col" + (i + 1).ToString(), typeof(string));
            }
        }
        //populate the DataTable
        for (int i = index; i < csvData.Length - 1; i++)
        {
            //create new rows
            DataRow row = csvDataTable.NewRow();
            for (int j = 0; j < headings.Length; j++)
            {
                //fill them
                row[j] = csvData[i].Split(',')[j];
            }
            //add rows to over DataTable
            csvDataTable.Rows.Add(row);
        }

        //return the CSV DataTable
        return csvDataTable;
    }

Another Important Function for our development of Stock Market Ticker for website is, the function which will call Yahoo Finance to fetch desired data.

We shall user WebRequest Class to call remove CSV file i.e. Read a downloadable CSV file from remote server. Check out following function.

 public DataTable CallYahooFinance()
    {
        WebRequest req = WebRequest.Create(StaticVariables.Markets);
        WebResponse result = req.GetResponse();
        Stream ReceiveStream = result.GetResponseStream();
        Encoding encode = System.Text.Encoding.GetEncoding("utf-8");
        StreamReader sr = new StreamReader(ReceiveStream, encode);
        string output = string.Empty;
        Char[] read = new Char[256];
        int count = sr.Read(read, 0, read.Length);
        while (count > 0)
        {
            String str = new String(read, 0, count);
            output += str;
            count = sr.Read(read, 0, read.Length);
        }
        output = output.Replace("^DJI", "DOW").Replace("^IXIC", "NASDAQ").Replace("^GSPC", "S&P500");
        output = output.Replace("^FTSE", "FTSE 100").Replace("^GDAXI", "DAX").Replace("^FCHI", "CAC 40");
        output = output.Replace("^HSI", "HANG SENG").Replace("^N225", "NIKKEI 225").Replace("^STI", "STRAITS TIMES");
        output = output.Replace("^BSESN", "SENSEX").Replace("^NSEI", "NIFTY");
        DataTable DT = csvToDataTable(output, false);
        DT.Columns[0].ColumnName = "Market";
        DT.Columns[1].ColumnName = "Last Trade";
        DT.Columns[2].ColumnName = "Change (in %)";
        return DT;
    }

In above code "output" is the string i.e. CSV string and we have replaced Yahoo Finance Symbols with the Name of the Stock Index.

If you check above function Webrequest.Create Method contains URL. To get CSV File we are using following URL, if you simple copy paste the URL you can download CSV file. URL for Stock Market Ticker is set into Static variable.

public static class StaticVariables
{
public static string Markets = "http://download.finance.yahoo.com/d/quotes.csv?s=%5EDJI,%5EIXIC,%5EGSPC,%5EFTSE,%5EGDAXI,%5EFCHI,%5EN225,%5EHSI,%5ESTI,%5EBSESN,%5ENSEI&f=sl1c1&e=.csv";
}

Thats it now we got the relevant data from yahoo finance which we can display the way we want. We are using ICallback Event handler to poll data from Yahoo Server. Now if you are aware about ICallback Simply check out following functions. Where we are not doing anything special.

protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            String cbReference =
                Page.ClientScript.GetCallbackEventReference(this, "arg", "ReceiveServerData", "context");
            String callbackScript;
            callbackScript = "function CallServer(arg, context)" +
                "{ " + cbReference + ";}";
            Page.ClientScript.RegisterClientScriptBlock(this.GetType(), "CallServer", callbackScript, true);
        }
    }

Also check Region for ICallback Implementation

public void RaiseCallbackEvent(String eventArgument)
    {
    
    }
    public String GetCallbackResult()
    {
        GridView GridView1 = new GridView();
        GridView1.DataSource = CallYahooFinance();
        GridView1.DataBind();

        return getHTML(GridView1);
    }


public string getHTML(GridView _grid)
    {
        string result = string.Empty;
        using (StringWriter sw = new StringWriter())
        {
            HtmlTextWriter htw = new HtmlTextWriter(sw);
            _grid.RenderControl(htw);
            htw.Flush();
             result = sw.ToString();
        }
        return result;
    }
//getHTML returns HTMLString for GRIDView

Now in the HTML of our page we have only following tags.


Polling from server...

We use following JavaScript to Fetch live data from server side at the interval of 1000mili second.

function call()
    {
    window.document.getElementById('_polling').style.display="";
    CallServer("", "");
    }
    
     function ReceiveServerData(retValue)
    {   
        document.getElementById("result").innerHTML = retValue;
        window.document.getElementById('_polling').style.display="none";
        setTimeout("call()",1000); //Set polling to 1000mili second
    }

Implementation of above code is very simple check out Demo code for the same.
Download Demo Code here --> Stock Market Ticker For Website.zip

Display Loading Image in Asp.net

2011-01-14T16:00:00.000+05:30

To display loading image we shall use following JavaScript. There are many ways to display loading, but I feel this is the simple to use and implement. We just need one div tag with position set to absolute, and put loading image inside the div tag. Check out following code.

Loading...

(Make sure you set z-index if required.)
In above I have just used text "Loading...", instead of this you can put loading images.Put this div tag as first element of FORM tag. And after above div tag immediately put following JavaScript

And call function "init()", on "onload" event of body tag.

Done, now as your page loads you can see "Loading..."...

Asp.Net Reading File From Url

2011-01-11T18:42:00.000+05:30

In this article is about Reading file from URL specially Text of CSV files, which we can directly read for the source and convert them into table. By doing this we generally avoid file handling, saving the file and then reading it again. WebRequest class of System.Net library is used to read remotely hosted text file or csv file.

We simply need to use following function which will read remote file and put it in a string format. String is named as output string.

WebRequest req = WebRequest.Create(YOURURL);
        WebResponse result = req.GetResponse();
        Stream ReceiveStream = result.GetResponseStream();
        Encoding encode = System.Text.Encoding.GetEncoding("utf-8");
        StreamReader sr = new StreamReader(ReceiveStream, encode);
        string output = string.Empty;
        Char[] read = new Char[256];
        int count = sr.Read(read, 0, read.Length);
        while (count > 0)
        {
            String str = new String(read, 0, count);
            output += str;
            count = sr.Read(read, 0, read.Length);
        }

We are following simple steps in Asp.net for Reading file from URL.

Identify the URL and create WebRequest Object
Get the Response Stream
Encoding it in Appropriate Unicode format
Reading the Data Stream.
Finally getting the whole text string in one string variable i.e. "output" in this case.

It is very easy to in Asp.net to Read file from Url, as we have seen in above code. We can write our logic to manipulate the string.

Get Excel Sheet Names In Asp.net

2010-12-20T12:08:00.000+05:30

In Previous article named Reading Excel File in Asp.net , we discussed about Uploading excel file with One sheet, where we assumed that we know the name of the sheet i.e. Reading from "Sheet1" of uploaded excel file.(Demo Code)

In this article we will discuss to read data from excel file where are not sure about the number of Sheets present in Excel file and name of those excel sheet. This is very common requirement for developers who are playing with Excel Data. We will keep logic of Uploading file similar to previous article. In this article we assume that we have uploaded the file and saved it at predefined location. Now we will only consider reading from excel file which contains number of excel sheets with names not known to us.

Just check out following code, where we consume excel file and read all Sheet names as Table names in one Data Table, and then display all Excel sheets one by one.

public DataSet GetExcelData(string ExcelFilePath)
    {
        string OledbConnectionString = string.Empty;
        OleDbConnection objConn = null;
        OledbConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ExcelFilePath + ";Extended Properties=Excel 8.0;";
        objConn = new OleDbConnection(OledbConnectionString);

        if (objConn.State == ConnectionState.Closed)
        {
            objConn.Open();
        }
        DataSet objDataset = new DataSet();

        DataTable dtName = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
        
        for (int _rowCount = 0; _rowCount < dtName.Rows.Count; _rowCount++)
        {
            Response.Write(dtName.Rows[_rowCount]["TABLE_NAME"].ToString() + "
");

            OleDbCommand objCmdSelect = new OleDbCommand("Select * from [" + dtName.Rows[_rowCount]["TABLE_NAME"].ToString() + "]", objConn);
            OleDbDataAdapter objAdapter = new OleDbDataAdapter();
            objAdapter.SelectCommand = objCmdSelect;
            objAdapter.Fill(objDataset, dtName.Rows[_rowCount]["TABLE_NAME"].ToString());
            objAdapter.Dispose();
            objCmdSelect.Dispose();
        }

        objConn.Close();
        return objDataset;

    }

We are simply reading all excel sheets present in the Excel file and adding them in Data Set.
Check out Demo code for the reference. Getting Excel sheet Names in Asp.net is an easy task. We can simply bind this data to grids and display it or push this data directly to database.

We need to note one thing, that sheet names in Excel are read as "SheetName + $", where "$" does not belong to sheet name.

If we need to do operations related to Range of Excel sheet Cells, refer my previous article

Download Demo Code here --> GetExcelSheetNamesInAsp.zip

Reading Excel File in Asp.net

2010-12-08T12:25:00.000+05:30

One of the very common requirement in asp.net is end user uploaded Excel file and in Code behind, we need to Read the uploaded Excel file and Make sense out of data, i.e. Reading Excel File in Asp.net (Demo Code).

Reading Excel File in Asp.net is Very simple task, but if there are standard operations need to be done, then we have to specify the Excel format and ask end user to upload file in certain format only.

Below we will discuss how to read and Excel File. (Demo Code)

Before we go ahead check out folder structure of Read Excel File in Asp.net

Also after we upload excel file it gets bind to a grid view, all these operations are done in Default.aspx

Html of the Default.aspx page is as below


         Select Date :                   
         Upload Excel File :

On Click of Upload Button we upload excel file and Save it in UploadedFiles folder. We then Read file from UploadedFiles folder.

C# code on Upload Button click is as below, code is very easy to understand, you can also download Demo Code for reference.

protected void btnUpload_Click(object sender, EventArgs e)
    {
        
        if (FileUpload1.FileName.ToString() != "" && FileUpload1.ToString().Contains("."))
        {
            String filepath = Server.MapPath("UploadedFiles");
            FileUpload1.SaveAs(filepath + "\\" + FileUpload1.FileName.Split('\\')[FileUpload1.FileName.Split('\\').Length - 1]);
            string excelPath = (filepath + "\\" + FileUpload1.FileName.Split('\\')[FileUpload1.FileName.Split('\\').Length - 1]);

            ExcelGridView.DataSource = GetExcelData(excelPath);
            ExcelGridView.DataBind();
        }
    }

In above code we pass Excel Path to "GetExcelData" function which returns DataTable which we in turn bind to GridView
GetExcelData function is as below

public DataTable GetExcelData(string ExcelFilePath)
    {
        string OledbConnectionString = string.Empty;
        OleDbConnection objConn = null;
        OledbConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ExcelFilePath + ";Extended Properties=Excel 8.0;";
        objConn = new OleDbConnection(OledbConnectionString);

        if (objConn.State == ConnectionState.Closed)
        {
            objConn.Open();
        }

        OleDbCommand objCmdSelect = new OleDbCommand("Select * from [Sheet1$]", objConn);
        OleDbDataAdapter objAdapter = new OleDbDataAdapter();
        objAdapter.SelectCommand = objCmdSelect;
        DataSet objDataset = new DataSet();
        objAdapter.Fill(objDataset, "ExcelDataTable");
        objConn.Close();
        return objDataset.Tables[0];

    }

In above Code check out OleDbCommand; we are just writing a select query which fetch data from Sheet1 of uploaded Excel, similarly if we can give Sheet Name instead of Sheet1. If your Excel Data starts from some other cell then change select query like "Select * from [Sheet1$B7:F200]"; first row of this range will come as columns in Data Table

Now we are done with Excel Upload and Reading the file, there are certain things which we needs to be take care before uploading Excel file which are as below

It certain column in Excel contains number do format that Column for number and make sure all the entries in that column are number, if some entries are in text format then, in Data Table we get those entries as blank entries (i.e. DataTable has empty cell for that respective excel cell.)
Define the Range of Operation if your data does not start from A1 cell.
All cells in 1st row (i.e. First row of the range you specify) are considered as column name, and in C# Data Table they appear as exact.
To Specify the Range modify select query as Select * from [Sheet2$B7:F200] ; you can use your own sheet name instead of Sheet1 or Sheet2

Download Ready to Use Demo Code -- > ReadExcelFileInAsp.Net.zip

Google Charts API for ASP.NET

2010-11-22T12:44:00.000+05:30

Google Visualization has provided a powerful set of JavaScripts which allows us to develop various graphs and charts. I think if we are developing something like dashboard or something like Google Analytic dashboard, then using Google Visualizations is amazing idea. In case of Asp.net we can simply develop a logic to use DataTables as input and Graph will be generated automatically. Again we can capture the click events for the graph and display alerts or run some JavaScript as per our logic (DemoCode).

In this article we will simply check out different graphs and its integration with asp.net. Check out following Screen shots.

Google Charts API can be implemented effectively in Asp.net, this is just a small part of actual assignment I did for the development of a dashboard.
Step 1: Understanding Table structure of Google API.Google Charts API accepts table as input and generate graphs accordingly. It takes Table in Following format. (for more information check out Google References )

We should be careful while generating Table in HTML, i.e. while adding Rows and Columns in Google DataTable. Google Data Table creation looks as follows

var data = new google.visualization.DataTable();
        data.addColumn('string', 'Name');
        data.addColumn('number', 'Salary');
        data.addRows(4);

There are many things we can do with Google Table which I am not including in this article.

Check out following JavaScript code for creating Google Table and Adding data in it

var data = new google.visualization.DataTable();
   function setDataforGraph()
        {
         // Create and populate the data table.
       var raw_data =  [['INFOSYS', 3077.15, 3081.65, 3015.9, 3059.80, 3082.65, 3054.65, 2997.85, 3030.05, 2970.95, 3003.95]];
            var xAxis = ['05-Nov-10', '08-Nov-10', '09-Nov-10', '10-Nov-10', '11-Nov-10', '12-Nov-10', '15-Nov-10', '16-Nov-10', '18-Nov-10','19-Nov-10'];

            data.addColumn('string', 'Date');
            for (var i = 0; i < raw_data.length; ++i) {
                data.addColumn('number', raw_data[i][0]);
            }
            
            data.addRows(xAxis.length);
            for (var j = 0; j < xAxis.length; ++j) {
                data.setValue(j, 0, xAxis[j].toString());
            }
            for (var i = 0; i < raw_data.length; ++i) {
                for (var j = 1; j < raw_data[i].length; ++j) {
                    data.setValue(j - 1, i + 1, raw_data[i][j]);
                }
            }
          
        }

We also need to include JavaScript sources, there are some packages Google Provides, as we are going to use this for Charts we include coreCharts
Add script file "http://www.google.com/jsapi"

Once we done with DataTable creation we go for the logic to display Charts. We need to call Google Function

function drawVisualization(Type) {

            // Create and draw the visualization.
            var targetDiv = document.getElementById('visualization');
            var chart;
            if(Type == "ColumnChart") chart = new google.visualization.ColumnChart(targetDiv);
            else if(Type == "PieChart") chart = new google.visualization.PieChart(targetDiv);
            else if (Type == "AreaChart") chart = new google.visualization.AreaChart(targetDiv);
            else if (Type == "BarChart") chart = new google.visualization.BarChart(targetDiv);
            else if(Type == "LineChart") chart = new google.visualization.LineChart(targetDiv);
            chart.draw(data);
            new google.visualization.events.addListener(chart, 'select', selectionHandler);

            function selectionHandler(e) {
                selection = chart.getSelection();
                for (var i = 0; i < selection.length; i++) {
                    var item = selection[i]; 
                    alert(data.getValue(item.row, 1));
                }
            }
        }

Above function can be called on any event. Which will generate Chart of Particular Type. Refer Demo Code. We can add selectionHandler to capture events on click of chart. Above function will give us value of the Item i.e. Data. Check out following image.

HTML Structure of form is something like below, where we have Div tag for Google Visualization.

At this point we are done with the Google Charts API usage in HTML. Now to make Google Charts work in asp.net we simply need to develop a logic in code behind which will enable us to Create GoogleDataTable.

To generate data in code behind we use string elements which we can simple get in JavaScript on page render.

public string x_Axis = string.Empty;
    public string graphTable = string.Empty;

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            graphTable = getGoogleVisualizationStr(DemoDataTable);
            x_Axis = "'05-Nov-10', '08-Nov-10', '09-Nov-10', '10-Nov-10', '11-Nov-10', '12-Nov-10', '15-Nov-10', '16-Nov-10', '18-Nov-10','19-Nov-10'";
        }
    }

We keep Demo Table in c# code, with same data values used in above table.

private DataTable DemoDataTable
    {
        get
        {
            DataTable DT = new DataTable();
            DT.TableName = "GENERAL";
            DT.Columns.Add(new DataColumn("Name", typeof(string)));
            DT.Columns.Add(new DataColumn("Data1", typeof(long)));
            DT.Columns.Add(new DataColumn("Data2", typeof(long)));
            DT.Columns.Add(new DataColumn("Data3", typeof(long)));
            DT.Columns.Add(new DataColumn("Data4", typeof(long)));
            DT.Columns.Add(new DataColumn("Data5", typeof(long)));
            DT.Columns.Add(new DataColumn("Data6", typeof(long)));
            DT.Columns.Add(new DataColumn("Data7", typeof(long)));
            DT.Columns.Add(new DataColumn("Data8", typeof(long)));
            DT.Columns.Add(new DataColumn("Data9", typeof(long)));
            DT.Columns.Add(new DataColumn("Data10", typeof(long)));
            DT.Rows.Add(new object[] { "INFOSYS", 3077.15, 3081.65, 3015.9, 3059.80, 3082.65, 3054.65, 2997.85, 3030.05, 2970.95, 3003.95 });
            return DT;
        }
    }

We write a simple code to generate rowData used in above lines of code.

private string getGoogleVisualizationStr(DataTable DT)
    {
        string googleVisualTable = string.Empty;
        googleVisualTable = "[";
        for (int i = 0; i < DT.Rows.Count; i++)
        {
            for (int j = 0; j < DT.Columns.Count; j++)
            {
                if (j == 0)
                {
                    googleVisualTable += "[ '" + DT.Rows[i][j] + "'";
                }
                else if (j == DT.Columns.Count - 1) googleVisualTable += "," + DT.Rows[i][j] + "]";
                else googleVisualTable += "," + DT.Rows[i][j];

            }
            if (i != DT.Rows.Count - 1) googleVisualTable += ",";
        }
        googleVisualTable += "]";
        return googleVisualTable;
    }

We replace our JavaScript code with

var raw_data = <%= graphTable %>
var xAxis = [<%=x_Axis %>];

Thats it we are done with development of Google Charts API for ASP.NET. Above code used data of only one company to keep things simple. To add more companies simply add it in data table. So that our graphs will look like

Download Democode for reference --> GoogleVisualizationDemo.zip

Generate captcha image in asp.net - simple way

2010-11-03T11:32:00.000+05:30

When it comes to captcha images simple question pops in i.e. how to generate captcha images?
This article is about generating Captcha image in asp.net, this is very simple logic which can be implemented immediately for any website. You can directly Download Demo and use it.
We have used following simple steps to Generate Captcha Image in asp.net

Create Captcha Image from random numbers and store that number in session variable, we have used Captcha.aspx page for the same.
Use Captcha.aspx as source for the captcha image in Default.aspx page, i.e. for image tag give Captcha.aspx as SRC.
On Submit click simply verify captcha entered by user and captcha in session.

Our folder structure is very simple, check out following image

To understand the way we have checked captcha, have look on HTML of Default.aspx page (Check out DemoCode, for better understanding.)

Check out the way we have used Captcha.aspx page in image source.

On submit click we simply checks whether Captcha entered in "txtCaptcha" is valid or not, we have following code for submit button clicked.

protected void btnSubmit_Click(object sender, EventArgs e)
    {
        if (Session["Captcha"] != null)
        {
            if (txtCaptcha.Text == Session["Captcha"].ToString())
            {
                Response.Redirect("RedirectPage.aspx");
            }
            else
            {
                Response.Write("Please Enter Valid Captcha code");
                txtCaptcha.Text = "";
            }
        }
        else
        {
            Response.Write("Session Expired, please re-enter Captcha.");
        }
    }

Now what remains is the logic for Generate captcha image, we don't have any HTML tags on captcha.aspx, we simply write following code for Binary Image Generation and write it. We have used Random number; but we can have our different logic for the same.

protected void Page_Load(object sender, EventArgs e)
    {
        Response.ContentType = "text/plain";
        Random random = new Random(DateTime.Now.Millisecond);

        int number = random.Next(100000);

        Session.Add("Captcha",number.ToString());

        System.Drawing.Bitmap bmpOut = new Bitmap(100, 50);

        Graphics graphics = Graphics.FromImage(bmpOut);

        graphics.InterpolationMode = System.Drawing.Drawing2D.InterpolationMode.Bilinear;

        graphics.FillRectangle(Brushes.Aquamarine, 0, 0, 100, 50);

        graphics.DrawString(number.ToString(), new Font("TimesNewRoman", 20), new SolidBrush(Color.Coral), 0, 0);

        MemoryStream memorystream = new MemoryStream();

        bmpOut.Save(memorystream, System.Drawing.Imaging.ImageFormat.Png);

        byte[] bmpBytes = memorystream.GetBuffer();

        bmpOut.Dispose();

        memorystream.Close();

        Response.BinaryWrite(bmpBytes);

        Response.End();

    }

Thats it we are done with our Generate captcha image in asp.net. Very simple code to use, download democode and use it directly in your web application.

Download Democode here---> CaptchaInAsp.net.zip

Display your logo on address bar

2010-10-28T13:50:00.000+05:30

Many times to make our site standout, we need to display our logo near address bar. There is very simple JavaScript which takes care of this. We need to put following code in tag HEAD tag.

For each page of the website we need to add above code.
demoico.ico is an icon file which we can use to display near address bar.
We can also manipulate Header Section Dynamically, and change the icon for different pages.

City	" + e._value.split("\|")[0] + "
State	" + e._value.split("\|")[1] + "
Country	" + e._value.split("\|")[2] + "
Postal Code	" + e._value.split("\|")[3] + "