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 : 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.