Sudipta K Paik on .NET

Upload Excel to SQL Server using OleDbConnection

importExcel.aspx Page

=====================================================================

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="importExcel.aspx.cs" Inherits="Eisk.Web.admin.importExcel" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<title>Upload Excel File to Database </title>

</head>

<body>

<asp:FileUpload ID="fluInput" runat="server" />

<asp:Button ID="btnImport" Text="Import" runat="server" OnClick="btnUploadFile_Click"

CssClass="button medium green" />

</form>

</body>

</html>

=====================================================================

Code Behind importExcel.aspx.cs

=====================================================================

using System;

using System.Collections.Generic;

using System.Data;

using System.Data.OleDb;

using System.IO;

using Eisk.BusinessEntities;

using Eisk.BusinessLogicLayer;

namespace Eisk.Web.admin

{

public partial class importExcel : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

}

protected void btnUploadFile_Click(object sender, EventArgs e)

{

ConvertFile();

}

protected void ConvertFile()

{

if (fluInput.HasFile)

{

string strFileName = Server.HtmlEncode(fluInput.FileName);

string strExtension = Path.GetExtension(strFileName);

if (strExtension != ".xls" && strExtension != ".xlsx")

{

Response.Write("<script>alert('Please select a Excel spreadsheet to import!');</script>");

return;

}

string strUploadFileName = "~/admin/Excell/" + DateTime.Now.ToString("yyyyMMddHHmmss") + strExtension;

strUploadFileName = Server.MapPath(strUploadFileName);

fluInput.SaveAs(strUploadFileName);

string connectionString = "";

if (strExtension == ".xls")

{

connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strUploadFileName + ";Extended Properties='Excel 8.0;HDR=YES;'";

}

else

{

connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strUploadFileName + ";Extended Properties='Excel 12.0 Xml;HDR=YES;'";

}

try

{

OleDbConnection connection = new OleDbConnection(connectionString);

connection.Open();

string sheetName = "Sheet1";

string query = "SELECT * FROM [" + sheetName + "$]";

OleDbDataAdapter adapter = new OleDbDataAdapter(query, connection);

DataSet ds = new DataSet();

DataTable dt = new DataTable();

adapter.Fill(ds);

dt = ds.Tables[0];

adapter.Fill(dt);

connection.Close();

int number_of_columns = dt.Columns.Count;

int number_of_rows = ds.Tables[0].Rows.Count / 2;

string[,] sheetEntries = new string[number_of_rows, number_of_columns];

string[] columnNames = new string[number_of_columns];

int startingColumn = 1;

for (int i = startingColumn; i < number_of_rows; i++)

{

for (int j = 0; j < number_of_columns; j++)

sheetEntries[i, j] = dt.Rows[i].ItemArray.GetValue(j).ToString();

}

List<ActivationCode> activationCodeList = CovertToList(sheetEntries, number_of_columns, number_of_rows);

ExportToSQLServerActivationCode(activationCodeList);

if (File.Exists(strUploadFileName))

{

File.Delete(strUploadFileName);

}

catch (Exception ex)

{

}

private List<ActivationCode> CovertToList(string[,] sheetEntries, int number_of_columns, int number_of_rows)

{

List<ActivationCode> aclist = new List<ActivationCode>();

ActivationCode aCode = null;

DateTime toDay = DateTime.Now;

for (int i = 0; i < number_of_rows; i++)

{

aCode = new ActivationCode();

string activationCodeName = StringNullOrEmptyChecking(sheetEntries[i, 2]);

string servicePlanSKU = StringNullOrEmptyChecking(sheetEntries[i, 5]);

if (!string.IsNullOrEmpty(activationCodeName) && !string.IsNullOrEmpty(servicePlanSKU))

{

aCode.ActivationCodeName = activationCodeName;

aCode.ServicePlanSKU = servicePlanSKU;

aCode.MinProductsNo = 1;

string maxProductsNo = StringNullOrEmptyChecking(sheetEntries[i, 11]);

if (!string.IsNullOrEmpty(maxProductsNo))

{

aCode.MaxProductsNo = Convert.ToInt16(maxProductsNo);

}

else

{

aCode.MaxProductsNo = 1;

}

aCode.IsUsed = false;

aCode.UploadDate = toDay;

aclist.Add(aCode);

}

return aclist;

}

private string StringNullOrEmptyChecking(object value)

{

if (value == null)

return null;

string returnValue = null;

if (value != null)

{

returnValue = value.ToString();

}

if (value == "")

{

returnValue = null;

}

return returnValue;

}

private void ExportToSQLServerActivationCode(List<ActivationCode> activationCodeList)

{

ActivationCodeBLL activationCodeBLL = new ActivationCodeBLL();

ActivationCodeRejectedBLL activationCodeRejectedBLL = new ActivationCodeRejectedBLL();

List<ActivationCode> allActivationCodeList = activationCodeBLL.GetAllActivationCodes();

List<ActivationCode> allActivationCodeListSelected = new List<ActivationCode>();

List<ActivationCodeRejected> activationCodeRejectedList = new List<ActivationCodeRejected>();

foreach (ActivationCode item in activationCodeList)

{

ActivationCode isExists = allActivationCodeList.Find(x => x.ActivationCodeName == item.ActivationCodeName);

if (isExists == null)

{

allActivationCodeListSelected.Add(item);

}

else

{

ActivationCodeRejected activationCodeRejected = new ActivationCodeRejected();

activationCodeRejected.ActivationCodeName = item.ActivationCodeName;

activationCodeRejected.ServicePlanSKU = item.ServicePlanSKU;

activationCodeRejected.MinProductsNo = item.MinProductsNo;

activationCodeRejected.MaxProductsNo = item.MaxProductsNo;

activationCodeRejected.IsUsed = item.IsUsed;

activationCodeRejected.UploadDate = item.UploadDate;

activationCodeRejectedList.Add(activationCodeRejected);

}

foreach (var item in allActivationCodeListSelected)

{

activationCodeBLL.CreateNewActivationCode(item);

}

foreach (var item in activationCodeRejectedList)

{

activationCodeRejectedBLL.CreateNewActivationCodeRejected(item);

}

asp:GridView UI Databind

Backend CS File

------------------------------------------------------------------------------------------------------------------------------------------------------------

List<ActivationCodeRejected> activationCodeRejectedList = new ActivationCodeRejectedBLL().GetAllActivationCodeRejecteds();

GridViewActivationCodeRejected.DataSource = activationCodeRejectedList;

GridViewActivationCodeRejected.DataBind();

==========================================================================================

Front end .aspx Page

------------------------------------------------------------------------------------------------------------------------------------------------------------

<asp:GridView ID="GridViewActivationCodeRejected" runat="server" AutoGenerateColumns="false"

AllowPaging="false" GridLines="Both" SkinID="GridView" Visible="true">

<asp:TemplateField HeaderText="Activation Code Name">

<asp:Literal ID="ActivationCodeName" runat="server" Text='<%# Bind("ActivationCodeName") %>'></asp:Literal>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Service Plan SKU">

<asp:Literal ID="ServicePlanSKU" runat="server" Text='<%# Bind("ServicePlanSKU") %>'></asp:Literal>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Min Products No">

<asp:Literal ID="MinProductsNo" runat="server" Text='<%# Bind("MinProductsNo") %>'></asp:Literal>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Max Products No">

<asp:Literal ID="MaxProductsNo" runat="server" Text='<%# Bind("MaxProductsNo") %>'></asp:Literal>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Upload Date">

<asp:Literal ID="UploadDate" runat="server" Text='<%# Bind("UploadDate") %>'></asp:Literal>

</ItemTemplate>

</asp:TemplateField>

</Columns>

</asp:GridView>

Return URL in ASP.NET

----------------------------------------------------------------------------------------------------------------------------------------------

if (!HttpContext.Current.User.Identity.IsAuthenticated)

Response.Redirect(SiteSettings.BaseUrl + "admin/login.aspx?ReturnUrl="+Page.AppRelativeVirtualPath.ToString());

=========================================================================

----------------------------------------------------------------------------------------------------------------------------------------------

Admin_User admin = new Admin_UserBLL().GetAdminUserByUserNamePassword(txtUserName.Text.Trim(), txtPassword.Text.Trim());

if (admin != null)

{

System.Web.Security.FormsAuthentication.RedirectFromLoginPage(admin.User_Name, true);

string ReturnUrl = Request.QueryString["ReturnUrl"];

if (!string.IsNullOrEmpty(ReturnUrl))

{

Response.Redirect(ReturnUrl);

}

else

{

Response.Redirect(SiteSettings.BaseUrl + "admin/Default.aspx");

}

else

{

divMessage.Visible = true;

divMessage.Attributes.Add("class", "error");

divMessage.InnerHtml = "Please provide user name and password correctly and try again.";

}

Image Upload in Database(FileBytes and Location Store)

.aspx PAGE

===================================================================================

Upload Photos<br />

<asp:FileUpload ID="FileUploadImages" runat="server" class="multi " />

<br />

<asp:Button ID="ButtonAddAlbum" runat="server" Text=" Upload"

OnClick="ButtonAddAlbum_Click" />

===================================================================================

C# Code(Back End)

-------------------------------------------

if (FileUploadImages.HasFile)

{

UploadedImage upImage = new UploadedImage();

upImage.FullImages = FileUploadImages.FileBytes;

UploadedImageBLL uIBll = new UploadedImageBLL();

uIBll.CreateNewUploadedImage(upImage);

string location = Server.MapPath("UploadFile") + "\\" + FileUploadImages.FileName;

FileUploadImages.SaveAs(location);

}

--------------------

N.B.:

-------------------------

Database Filed name will be 'images' for 'FileBytes'

and 'varchar' for 'location'

Grid View (With Back end DataBound)

.aspx PAGE

==============================================================================

<asp:GridView ID="GridViewActiveCampaigns" runat="server" AutoGenerateColumns="False" DataKeyNames="CampaignId"

OnRowDataBound="GridViewActiveCampaigns_OnDataBound" GridLines="Both">

<asp:BoundField DataField="CampaignName" HeaderText="Campaign Name" />

<asp:TemplateField HeaderText="Raised">

<asp:Literal ID="LiteralGoalAmount" runat="server"></asp:Literal>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Days left">

<asp:Literal ID="LiteralDaysLeft" runat="server"></asp:Literal>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Donars">

<asp:Literal ID="LiteraltotalDonars" runat="server"></asp:Literal>

</ItemTemplate>

</asp:TemplateField>

</Columns>

</asp:GridView>

========================================================

C# Code(Back end)

List<Campaign> CampaignList = new CampaignBLL().GetAllCampaigns();

GridViewActiveCampaigns.DataSource = CampaignListSelected;

protected void GridViewActiveCampaigns_OnDataBound(object sender, GridViewRowEventArgs e)

{

if (e.Row.RowType == DataControlRowType.DataRow)

{

Campaign campaign = e.Row.DataItem as Campaign;

Literal LiteralGoalAmount = e.Row.FindControl("LiteralGoalAmount") as Literal;

LiteralGoalAmount.Text = campaign.GoalAmount.Value.ToString("C");

Literal LiteralDaysLeft = e.Row.FindControl("LiteralDaysLeft") as Literal;

//LiteralDaysLeft.Text = campaign.GoalAmount.Value.ToString("C");

int differenceInDays = GetDifferenceOfDate(campaign);

LiteralDaysLeft.Text = differenceInDays.ToString() + " Days";

int totalDonars = 0;

decimal calculateRaised = CalculateRaised(campaign.CampaignId, out totalDonars);

Literal LiteraltotalDonars = e.Row.FindControl("LiteraltotalDonars") as Literal;

LiteraltotalDonars.Text = totalDonars.ToString();

}

========================================================

State Dropdownlist

internal static void GetStateDropdownlist(DropDownList ddlState)

{

ddlState.Items.Clear();

ddlState.Items.Add(new ListItem("Alabama", "AL"));

ddlState.Items.Add(new ListItem("Alaska", "AK"));

ddlState.Items.Add(new ListItem("Arizona", "AZ"));

ddlState.Items.Add(new ListItem("Arkansas", "AR"));

ddlState.Items.Add(new ListItem("California", "CA"));

ddlState.Items.Add(new ListItem("Colorado", "CO"));

ddlState.Items.Add(new ListItem("Connecticut", "CT"));

ddlState.Items.Add(new ListItem("Delaware", "DE"));

ddlState.Items.Add(new ListItem("District of Columbia", "DC"));

ddlState.Items.Add(new ListItem("Florida", "FL"));

ddlState.Items.Add(new ListItem("Georgia", "GA"));

ddlState.Items.Add(new ListItem("Hawaii", "HI"));

ddlState.Items.Add(new ListItem("Idaho", "ID"));

ddlState.Items.Add(new ListItem("Illinois", "IL"));

ddlState.Items.Add(new ListItem("Indiana", "IN"));

ddlState.Items.Add(new ListItem("Iowa", "IA"));

ddlState.Items.Add(new ListItem("Kansas", "KS"));

ddlState.Items.Add(new ListItem("Kentucky", "KY"));

ddlState.Items.Add(new ListItem("Louisiana", "LA"));

ddlState.Items.Add(new ListItem("Maine", "ME"));

ddlState.Items.Add(new ListItem("Maryland", "MD"));

ddlState.Items.Add(new ListItem("Massachusetts", "MA"));

ddlState.Items.Add(new ListItem("Michigan", "MI"));

ddlState.Items.Add(new ListItem("Minnesota", "MN"));

ddlState.Items.Add(new ListItem("Mississippi", "MS"));

ddlState.Items.Add(new ListItem("Missouri", "MO"));

ddlState.Items.Add(new ListItem("Montana", "MT"));

ddlState.Items.Add(new ListItem("Nebraska", "NE"));

ddlState.Items.Add(new ListItem("Nevada", "NV"));

ddlState.Items.Add(new ListItem("New Hampshire", "NH"));

ddlState.Items.Add(new ListItem("New Jersey", "NJ"));

ddlState.Items.Add(new ListItem("New Mexico", "NM"));

ddlState.Items.Add(new ListItem("New York", "NY"));

ddlState.Items.Add(new ListItem("North Carolina", "NC"));

ddlState.Items.Add(new ListItem("North Dakota", "ND"));

ddlState.Items.Add(new ListItem("Ohio", "OH"));

ddlState.Items.Add(new ListItem("Oklahoma", "OK"));

ddlState.Items.Add(new ListItem("Oregon", "OR"));

ddlState.Items.Add(new ListItem("Pennsylvania", "PA"));

ddlState.Items.Add(new ListItem("Rhode Island", "RI"));

ddlState.Items.Add(new ListItem("South Carolina", "SC"));

ddlState.Items.Add(new ListItem("South Dakota", "SD"));

ddlState.Items.Add(new ListItem("Tennessee", "TN"));

ddlState.Items.Add(new ListItem("Texas", "TX"));

ddlState.Items.Add(new ListItem("Utah", "UT"));

ddlState.Items.Add(new ListItem("Vermont", "VT"));

ddlState.Items.Add(new ListItem("Virginia", "VA"));

ddlState.Items.Add(new ListItem("Washington", "WA"));

ddlState.Items.Add(new ListItem("West Virginia", "WV"));

ddlState.Items.Add(new ListItem("Wisconsin", "WI"));

ddlState.Items.Add(new ListItem("Wyoming", "WY"));

}

Country Dropdownlist

internal static void GetCountryDropdownlist(DropDownList ddList)

{

List<ListItem> ddlCountryList = new List<ListItem>();

ListItem aListItem = null;

aListItem = new ListItem();

aListItem.Value = "NA";

aListItem.Text = "Select Country";