How To : Load Excel file and read it in a dataset in asp.net

Introduction

Now a days we have data into excel sheet and maintain data into file itself. Now we need to display those data into Dataset .and display into Gridview , Repeater or Datalist.

In this article I will show you how to read excel file. also I m covering caching concept in this article. so I will do file dependency caching. whenever you modify the excel file cache getting reset and load fresh data.

For that need to Place Gridview on aspx page. right now all the columns available in xls sheet will be available in Gridview

<asp:GridView id="gvProducts" runat="server" AutoGenerateColumns="true">   
</asp:GridView>

Now we have to place conncetion string as below. if excel file is .xls or .xlsx extenstion both connection string available below.

<connectionStrings>
   <add name="xls" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;
    Data Source=D:\my_products.xls;Extended Properties=Excel 8.0" />    
   <add name="xlsx" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;
       Data Source=D:\my_products.xlsx;Extended Properties=Excel 12.0;HDR=YES" />    
</connectionStrings>

Then place below code in code behind file

C#.NET

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

    public void BindProducts()
    {
      
        DataSet ds = new DataSet();

        if (Cache["PRODUCTS"] == null)
        {
            ds = GetDataSet();
        }
        else
        {
            ds = (DataSet)Cache["PRODUCTS"];
        }

        gvProducts.DataSource = ds;
        gvProducts.DataBind(); 
    }

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

        string connString = ConfigurationManager.ConnectionStrings["xls"].ConnectionString;
        // Create the connection object
        OleDbConnection oledbConn = new OleDbConnection(connString);
        try
        {
            // Open connection
            oledbConn.Open();

            // Create OleDbCommand object and select data from worksheet Sheet1
            OleDbCommand cmd = new OleDbCommand("SELECT * FROM [Sheet1$]", oledbConn);

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

            oleda.SelectCommand = cmd;


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

            System.Web.Caching.CacheDependency objdep = new System.Web.Caching.CacheDependency(@"D:\my_products.xls");
            Cache.Insert("PRODUCTS", ds, objdep);


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

        return ds;
    }

VB.NET

Protected Sub Page_Load(sender As Object, e As EventArgs)
    If Not Page.IsPostBack Then
        BindProducts()
    End If
End Sub

Public Sub BindProducts()

    Dim ds As New DataSet()

    If Cache("PRODUCTS") Is Nothing Then
        ds = GetDataSet()
    Else
        ds = DirectCast(Cache("PRODUCTS"), DataSet)
    End If

    gvProducts.DataSource = ds
    gvProducts.DataBind()
End Sub
 
Public Function GetDataSet() As DataSet
    ' Create a DataSet which will hold the data extracted from the worksheet.
    Dim ds As New DataSet()

    Dim connString As String = ConfigurationManager.ConnectionStrings("xls").ConnectionString
    ' Create the connection object
    Dim oledbConn As New OleDbConnection(connString)
    Try
        ' Open connection
        oledbConn.Open()

        ' Create OleDbCommand object and select data from worksheet Sheet1
        Dim cmd As New OleDbCommand("SELECT * FROM [Sheet1$]", oledbConn)

        ' Create new OleDbDataAdapter
        Dim oleda As New OleDbDataAdapter()

        oleda.SelectCommand = cmd


        ' Fill the DataSet from the data extracted from the worksheet.
        oleda.Fill(ds, "Customer")

        Dim objdep As New System.Web.Caching.CacheDependency("D:\my_products.xls")


        Cache.Insert("PRODUCTS", ds, objdep)
    Catch
    Finally
        ' Close connection
        oledbConn.Close()
    End Try

    Return ds
End Function

I hope this article was useful and I thank you for viewing it. keep visiting blog , you can get more stuff

How to : Encrypt and Decrypt string using Asp.net

Introduction

Many time we need to encrypt and decrypt values to send over the query sting  and prevent the attacks.


below are the function we can use to accomplish those task

need to add namespace as below

using System.Security.Cryptography;

public static string EncryptText(string Text)
        {
            string Key = "@()$*#-=1";
            return Encrypt(Text, Key);
        }
        public static string DecryptText(string Text)
        {
            string Key = "@()$*#-=1";
            return Decrypt(Text, Key);
        }
        private static string Encrypt(string strText, string strEncrKey)
        {
            byte[] byKey = { };
            byte[] IV = { 18, 52, 86, 120, 144, 171, 205, 239 };

            byKey = System.Text.Encoding.UTF8.GetBytes(strEncrKey.Substring(0, 8));
            DESCryptoServiceProvider des = new DESCryptoServiceProvider();
            byte[] inputByteArray = Encoding.UTF8.GetBytes(strText);
            MemoryStream ms = new MemoryStream();
            CryptoStream cs = new CryptoStream(ms, des.CreateEncryptor(byKey, IV), CryptoStreamMode.Write);
            cs.Write(inputByteArray, 0, inputByteArray.Length);
            cs.FlushFinalBlock();
            return Convert.ToBase64String(ms.ToArray());



        }
        private static string Decrypt(string strText, string sDecrKey)
        {
            byte[] byKey = { };
            byte[] IV = { 18, 52, 86, 120, 144, 171, 205, 239 };
            byte[] inputByteArray = new byte[strText.Length + 1];

            byKey = System.Text.Encoding.UTF8.GetBytes(sDecrKey.Substring(0, 8));
            DESCryptoServiceProvider des = new DESCryptoServiceProvider();
            inputByteArray = Convert.FromBase64String(strText);
            MemoryStream ms = new MemoryStream();
            CryptoStream cs = new CryptoStream(ms, des.CreateDecryptor(byKey, IV), CryptoStreamMode.Write);
            cs.Write(inputByteArray, 0, inputByteArray.Length);
            cs.FlushFinalBlock();
            System.Text.Encoding encoding = System.Text.Encoding.UTF8;
            return encoding.GetString(ms.ToArray());

        }

I hope this article was useful and I thank you for viewing it. keep visiting blog , you can get more stuff

How to : Get datatable values using jquery from client to server side.

Introduction

First you need to make call from client side to server using below javascript funtion

$.ajax({
                   type: "get",
                   url: "?ajax_mode=GetMyData,
                   dataType: "json",
                   async: false,
                   cache: false,
                   success: function(data, textStatus) {
                       if (data.status == "success") {
                          for (var row, i = 0; row = data.obj[i]; i++) {                               
                                // Process row var for as per usage.
                }
                       }
                       else {

                       }
                   }
               });

Now You need to create class for datatable and you need to define Structure for BasicOuputObject. object of this BasicOuputObject

is sent over from server side to client. as below

VB.net

Class MyData
    Public ID As Integer
    Public Name As String
    Public ContactNo As String
End Class

Public Structure BasicOuputObject
    Dim status As String
    Dim errorMessage As String
    Dim strMessage As String
    Dim obj As Object
End Structure

C#.net

class MyData
{
    public int ID;
    public string Name;
    public string ContactNo;
}

public struct BasicOuputObject
{
    public string status;
    public string errorMessage;
    public string strMessage;
    public object obj;
}

Now you have to use below function for getting datatable in code behind file.

VB.NET

Public Sub GetMyData()

        Dim objOutput As New BasicOuputObject
        Dim objCommand As New SqlCommand
        Dim lstMyData As New List(Of MyData)
        Dim objMyData As MyData
        Dim objConn As New SqlConnection("Pass Connection String")
        objCommand.CommandText = "select ID ,Name ,ContactNo from Customers"
        objCommand.Connection = objConn

        objConn.Open()
        Dim m_rdrReader As SqlDataReader = objCommand.ExecuteReader()
        If m_rdrReader.HasRows Then
            While m_rdrReader.Read()
                objMyData = New MyData
                objMyData.ID = m_rdrReader(0)
                objMyData.Name = m_rdrReader(1)
                objMyData.ContactNo = m_rdrReader(2)
                lstMyData.Add(objMyData)
            End While
        End If
        m_rdrReader.Close()

        objOutput.errorMessage = ""
        objOutput.obj = lstMyData
        objOutput.strMessage = "success"

        Dim objSerialiser As New System.Web.Script.Serialization.JavaScriptSerializer
        HttpContext.Current.Response.Write(objSerialiser.Serialize(objOutput))
        HttpContext.Current.Response.End()

    End Sub


End Class

I hope this article was useful and I thank you for viewing it. keep visiting blog , you can get more stuff

How to : Get asp:label value using jquery from client to server side

Introduction

 You can access label value using any event like button client click

<asp:Label ID="lbltext" runat="server" CssClass="cssTextLabel" Text="Test"></asp:Label>
<asp:Button ID="btnGetLabelData" runat="server" OnClientClick="GetData()" /> 

define javascript function like below.

<script type="text/javascript">
   function GetData() {
            var lbltxt = $.find('span.cssTextLabel')[0].innerHTML            
            __doPostBack('GET_DATA', lbltxt);

        }
</script>

handle postback in page load of page as below.

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim strLblData As String = String.Empty
        If Request("__EVENTTARGET") = "GET_DATA" Then
            strLblData = Request("__EVENTARGUMENT").ToString()
            Response.Write(strLblData)
        End If
    End Sub

C#.Net

protected void Page_Load(object sender, System.EventArgs e)
{
    string strLblData = string.Empty;
    if (Request("__EVENTTARGET") == "GET_DATA") {
        strLblData = Request("__EVENTARGUMENT").ToString();
        Response.Write(strLblData);
    }
}

I hope this article was useful and I thank you for viewing it. keep visiting blog , you can get more stuff.

How To : Send Email using SMTPClient in asp.net

Introduction
Now a days most of the web application provide features for sending emails to customers or users for different purpose. In this article I will show you how to sent an email using smtpclient in asp.net.

we need to use System.Net namespace for that.MailMessage class object is used for collects To, CC, Bcc addresses as MailAddressCollection.

Before using mail send function we need to include below code in web.config file.

<system.net>
    <mailSettings>
      <smtp >
        <network host="host name" port="25" userName="userid" password="pwd"/>
      </smtp>
    </mailSettings>
  </system.net>

if you want attachements in email , need to pass arraylist of attachments which is list of set of files objects.

C#.Net 

public bool SendMail(string FromEmailID, string ToEmailID, string CCEmailID, string 
BCCEmailID, string Subject, string Body, Boolean IsBodyHtml, ArrayList _Attachments)
    {
        MailMessage mm;
        SmtpClient smtp;
        try
        {
            mm = new MailMessage(FromEmailID, ToEmailID, Subject, Body);
            mm.IsBodyHtml = IsBodyHtml;
            if (!string.IsNullOrEmpty(CCEmailID)) mm.CC.Add(CCEmailID);
            if (!string.IsNullOrEmpty(BCCEmailID)) mm.Bcc.Add(BCCEmailID);
            if (_Attachments != null)
            {
                for (int i = 0; i < _Attachments.Count; i++)
                {
                    mm.Attachments.Add((Attachment)_Attachments[i]);
                }
            }
            smtp = new SmtpClient();
            smtp.Send(mm);
            return true;
        }
       
        catch
        {
            return false;
        }
    }

VB.Net

Public Function SendMail(FromEmailID As String, ToEmailID As String, CCEmailID As String, _
BCCEmailID As String, Subject As String, Body As String, _
    IsBodyHtml As [Boolean], _Attachments As ArrayList) As Boolean
    Dim mm As MailMessage
    Dim smtp As SmtpClient
    Try
        mm = New MailMessage(FromEmailID, ToEmailID, Subject, Body)
        mm.IsBodyHtml = IsBodyHtml
        If Not String.IsNullOrEmpty(CCEmailID) Then
            mm.CC.Add(CCEmailID)
        End If
        If Not String.IsNullOrEmpty(BCCEmailID) Then
            mm.Bcc.Add(BCCEmailID)
        End If
        If _Attachments IsNot Nothing Then
            For i As Integer = 0 To _Attachments.Count - 1
                mm.Attachments.Add(DirectCast(_Attachments(i), Attachment))
            Next
        End If
        smtp = New SmtpClient()
        smtp.Send(mm)
        Return True

    Catch
        Return False
    End Try
End Function

I hope this article was useful and I thank you for viewing it. keep visiting blog , you can get more stuff.

How To : Set Meta Description , Meta Keyword in asp.net

Introduction

Meta Description and Meta keywords play a major role in terms of making web page search engine friendly. search engine always looks for meta description and meta keywords from the page contents. Here I m showing how to set meta description and meta keywords in web page.

 ASP.Net 4.0 provided new two properties at page level for setting meta description and meta keywords.we can add these two properties with page class in Code behind or in Page Directives.

Asp.Net 4.0

 C#.Net

protected void Page_Load(object sender, EventArgs e)
{
    Page.MetaKeywords = "Test Page";
    Page.MetaDescription = "Test Page";
}

VB.net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Me.Page.MetaKeywords = "Test Page"
        Me.Page.MetaDescription = "Test Page"
End Sub

Earlier Version of Asp.Net

Need to create some function which will set meta description and meta keywords. here it use HtmlMeta to do the same.

C#.Net

protected void Page_Load(object sender, System.EventArgs e)
{
    SetMetaDescription(this.Page, "Test Page");
    SetMetaKeyword(this.Page, "Test Page");
}

public void SetMetaDescription(Page PageName, string strMetaDesc)
{
    HtmlMeta metaDesc = new HtmlMeta();
    HtmlHead head = PageName.Header;
    metaDesc.Name = "description";
    metaDesc.Content = strMetaDesc;
    head.Controls.Add(metaDesc);

}
public void SetMetaKeyword(Page PageName, string strMetaKeyword)
{
    HtmlMeta metaKeys = new HtmlMeta();
    HtmlHead head = PageName.Header;
    metaKeys.Name = "keyword";
    metaKeys.Content = strMetaKeyword;
    head.Controls.Add(metaKeys);
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
       
        SetMetaDescription(Me.Page, "Test Page")
        SetMetaKeyword(Me.Page, "Test Page")
End Sub    

Public Sub SetMetaDescription(ByVal PageName As Page, ByVal strMetaDesc As String)
        Dim metaDesc As New HtmlMeta()
        Dim head As HtmlHead = PageName.Header
        metaDesc.Name = "description"
        metaDesc.Content = strMetaDesc
        head.Controls.Add(metaDesc)

End Sub
Public Sub SetMetaKeyword(ByVal PageName As Page, ByVal strMetaKeyword As String)
        Dim metaKeys As New HtmlMeta()
        Dim head As HtmlHead = PageName.Header
        metaKeys.Name = "keyword"
        metaKeys.Content = strMetaKeyword
        head.Controls.Add(metaKeys)
End Sub

 I hope this article was useful and I thank you for viewing it. keep visiting blog , you can get more stuff.

How to : Get Image from SQL database table's image filed in asp.net

Introduction

MS SQL has provided many new data types.Image data type is one of that. which store for example images in any format (.jpg,.bmp,.gif) or any document or file in binary format data. in this article I will show you how to store data into image data type and vice verse how to retrieve those.

For saving image into database we have aspx code as like below.

<asp:FileUpload ID="fl" runat="server" />
<asp:Button ID="btn" runat="server" Text="Save Image" />

Now we have handle button click event for save image.

C#.Net

protected void btn_Click(object sender, System.EventArgs e)
{
    SaveImage();
}

public void SaveImage()
{
    if (fl.HasFile) {
        byte[] productImage = fl.FileBytes;

        string strQuery = null;
        SqlConnection objConnDBConnection = null;
        SqlCommand objCommand = null;
       
        try {
            string connectionString = ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString;
            objConnDBConnection = new SqlConnection(connectionString);
            objConnDBConnection.Open();

            strQuery = "insert into customers_details(customer_id,customer_logo)values(@customer_id,@customer_logo)";
            objCommand = new SqlCommand(strQuery, objConnDBConnection);
            objCommand.CommandType = CommandType.Text;
            objCommand.Parameters.AddWithValue("@customer_id", 1);
            objCommand.Parameters.AddWithValue("@customer_logo", productImage);

            int i = objCommand.ExecuteNonQuery();

        } catch (Exception ex) {
        } finally {
            objCommand = null;
            objConnDBConnection.Close();
        }

    }
}

VB.Net

Protected Sub btn_Click(sender As Object, e As System.EventArgs)
    SaveImage()
End Sub

Public Sub SaveImage()
    If fl.HasFile Then
        Dim productImage As Byte() = fl.FileBytes

        Dim strQuery As String = Nothing
        Dim objConnDBConnection As SqlConnection = Nothing
        Dim objCommand As SqlCommand = Nothing
      
        Try
            Dim connectionString As String = ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString
            objConnDBConnection = New SqlConnection(connectionString)
            objConnDBConnection.Open()

            strQuery = "insert into customers_details(customer_id,customer_logo)values(@customer_id,@customer_logo)"
            objCommand = New SqlCommand(strQuery, objConnDBConnection)
            objCommand.CommandType = CommandType.Text
            objCommand.Parameters.AddWithValue("@customer_id", 1)
            objCommand.Parameters.AddWithValue("@customer_logo", productImage)

            Dim i As Integer = objCommand.ExecuteNonQuery()
        Catch ex As Exception
        Finally
            objCommand = Nothing
            objConnDBConnection.Close()

        End Try
    End If
End Sub

Half the way we have done. now image is stored in binary format in database. now I m showing how to get that image from database and display in image control.


Here is the code for aspx page for image control

<asp:Image ID="img" runat="server" ImageUrl="ImgHandler.ashx?imgid=1" />

Here is some tricky part . As we know for display image normally we need physical file at location.  Over here we need to create handler for getting image binary data , so need to create ashx file and pass id for customer for which you need to display image. in ashx file you need to create below function.



C#.Net

public void GetImageFromDatabase(HttpContext context)
{
    SqlDataReader rdr = default(SqlDataReader);
    SqlConnection conn = default(SqlConnection);
    SqlCommand selcmd = default(SqlCommand);
    try {
        conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString);
        selcmd = new SqlCommand("select customer_logo from customers_details where customer_id=" + context.Request.QueryString("imgid"), conn);
        conn.Open();
        rdr = selcmd.ExecuteReader();
        while (rdr.Read()) {
            context.Response.ContentType = "image/jpg";
            context.Response.BinaryWrite((byte[])rdr("customer_logo"));
        }
        if (rdr != null) {
            rdr.Close();
        }
    } finally {
        if (conn != null) {
            conn.Close();
        }
    }
}

VB.Net

Public Sub GetImageFromDatabase(context As HttpContext)
    Dim rdr As SqlDataReader = Nothing
    Dim conn As SqlConnection = Nothing
    Dim selcmd As SqlCommand = Nothing
    Try
        conn = New System.Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
        selcmd = New System.Data.SqlClient.SqlCommand("select customer_logo from customers_details where customer_id=" & context.Request.QueryString("imgid"), conn)
        conn.Open()
        rdr = selcmd.ExecuteReader()
        While rdr.Read()
            context.Response.ContentType = "image/jpg"
            context.Response.BinaryWrite(DirectCast(rdr("customer_logo"), Byte()))
        End While
        If rdr IsNot Nothing Then
            rdr.Close()
        End If
    Finally
        If conn IsNot Nothing Then
            conn.Close()
        End If
    End Try
End Sub

 I hope this article was useful and I thank you for viewing it. keep visiting blog , you can get more stuff.