Tuesday, June 11, 2013

Asynchrous Save

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="AsynchrousSave.aspx.cs" Inherits="DUMMY_AsynchrousSave" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    
    <script type='text/javascript'>
        function GetMessage() {
            PageMethods.Message(OnGetMessageSuccess, OnGetMessageFailure);
        }
        function OnGetMessageSuccess(result, userContext, methodName) {
            alert(result);
        }
        function OnGetMessageFailure(error, userContext, methodName) {
            alert(error.get_message());
        }
    </script>
</head>
<body>
    <form id="form1" runat="server">
    <asp:ScriptManager ID='ScriptManager1' runat='server' EnablePageMethods='true' />
    <div>
    <input type='submit' value='Get Message' onclick='GetMessage();return false;' />
    </div>
    
    </form>
</body>
</html>


using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
public partial class DUMMY_AsynchrousSave : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
    }
    [System.Web.Services.WebMethod]
    public static string Message()
    {
        string Output = string.Empty;
        try
        {
            UserDetail objUserDetail = new UserDetail();
            objUserDetail.Id = Guid.NewGuid();
            objUserDetail.FirstName = "FirstName";
            objUserDetail.LastName = "LastName";
            objUserDetail.Email = "test@test.com";
            UserDetailProvider.Save(objUserDetail);
            Output = "Message Successfull";
        }
        catch (Exception ex)
        {
            Output = "Message Not Successfull";
        }
        return Output;//"Hello from the server-side World!";
    }
}

Sunday, June 2, 2013

Read and Import Excel Sheet into ASP.Net GridView Control

Note: Add Folder Named (Files) in the project

 

 

<%@ Page Language="C#" AutoEventWireup="true"  CodeFile="CS.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Import Excel Data into GridView</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:FileUpload ID="FileUpload1" runat="server" />
        <asp:Button ID="btnUpload" runat="server" Text="Upload" OnClick="btnUpload_Click" />
        <br />
        <asp:Label ID="Label1" runat="server" Text="Has Header ?"></asp:Label>
        <asp:RadioButtonList ID="rbHDR" runat="server">
            <asp:ListItem Text = "Yes" Value = "Yes" Selected = "True" ></asp:ListItem>
            <asp:ListItem Text = "No" Value = "No"></asp:ListItem>
        </asp:RadioButtonList>
        <asp:GridView ID="GridView1" runat="server" OnPageIndexChanging = "PageIndexChanging" AllowPaging = "true"  >
        </asp:GridView>
    </div>
    </form>
</body>
</html>


CS.aspx.cs
-----------
using System;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Configuration; 
public partial class _Default : System.Web.UI.Page 
{
    protected void Page_Load(object sender, EventArgs e)
    {
    }
    protected void btnUpload_Click(object sender, EventArgs e)
    {
        if (FileUpload1.HasFile)
        {
            string FileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
            string Extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
            string FolderPath = ConfigurationManager.AppSettings["FolderPath"];
            string FilePath = Server.MapPath(FolderPath + FileName);
            FileUpload1.SaveAs(FilePath);
            Import_To_Grid(FilePath, Extension, rbHDR.SelectedItem.Text);
        }
    }
    private void Import_To_Grid(string FilePath, string Extension, string isHDR)
    {
        string conStr="";
        switch (Extension)
        {
            case ".xls": //Excel 97-03
                conStr = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
                break;
            case ".xlsx": //Excel 07
                conStr = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;
                break;
        }
        conStr = String.Format(conStr, FilePath, isHDR);
        OleDbConnection connExcel = new OleDbConnection(conStr);
        OleDbCommand cmdExcel = new OleDbCommand();
        OleDbDataAdapter oda = new OleDbDataAdapter();
        DataTable dt = new DataTable(); 
        cmdExcel.Connection = connExcel;
        //Get the name of First Sheet
        connExcel.Open();
        DataTable dtExcelSchema;
        dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
        string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
        connExcel.Close();
        //Read Data from First Sheet
        connExcel.Open();
        cmdExcel.CommandText = "SELECT * From [" + SheetName + "]";
        oda.SelectCommand = cmdExcel;
        oda.Fill(dt);
        connExcel.Close(); 
        //Bind Data to GridView
        GridView1.Caption = Path.GetFileName(FilePath); 
        GridView1.DataSource = dt;
        GridView1.DataBind();
    }
    protected void PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        string FolderPath = ConfigurationManager.AppSettings["FolderPath"] ;
        string FileName = GridView1.Caption;
        string Extension = Path.GetExtension(FileName);
        string FilePath = Server.MapPath(FolderPath + FileName);
        Import_To_Grid(FilePath, Extension, rbHDR.SelectedItem.Text);  
        GridView1.PageIndex = e.NewPageIndex;
        GridView1.DataBind();  
    }
}


Web.Config
----------------
<appSettings>
		<add key="FolderPath" value="Files/"/>
	</appSettings>
	<connectionStrings>
		<add name="Excel03ConString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'"/>
		<add name="Excel07ConString" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'"/>
	</connectionStrings>


Read and Import Excel Sheet into ASP.Net GridView Control 2013-06-02 11-46-35



if you get the following Exception :



The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.



 



Read this Thread:
-------------------
http://social.msdn.microsoft.com/Forums/en-US/vstsdb/thread/1d5c04c7-157f-4955-a14b-41d912d50a64
trying to connect to an Access database in visual studio but keep getting this error?
try installing this first: http://www.microsoft.com/download/en/details.aspx?id=13255
 
however if, like me, that doesnt work for you, try the following method:
NOTE: this DOES work for office 2010 even though it is for 2007 office, dont ask me why it just does :)
1. download and install this: http://www.microsoft.com/download/en/confirmation.aspx?id=23734
2. in VS click add data source, follow the wizard and enjoy! :)
 
P.S thanks to everyone that helped me at my original thread: http://social.msdn.microsoft.com/Forums/en-US/csharpgeneral/thread/ebb05600-055a-44c4-8cd3-d33a79878b23