dotnetspider.com


 


TutorialsForumResourcesReviewsJobsInterviewVideosCommunitiesProjectsTraining

Subscribe to Subscribers


Online MembersSunitha
Falguni
Prachi Kulkarni
Prasad kulkarni
Gokula
Prabu Thangavelu
chaminda
sravan
cloud
Shesh Kumar Mishra
Gajanan
More...




Resources » Code Snippets » ASP.NET WebForms


Upload excel file content to SQl server using SQL bulk copy method


Posted Date:     Category: ASP.NET WebForms    Rating: 1 out of 5
Author: Member Level: Gold    Points: 10 (Rs 10)


This code shows how to upload excel file content to SQL Server


protected void btnImport_Click(object sender, EventArgs e)
{
lblError.Visible = false;

string xlsFile = fileImport.FileName; //fileImport is the file upload control

if (xlsFile == "" || xlsFile.EndsWith(".xls") != true)
{
lblError.Text = "Please select an Excel File";

lblError.Visible = true;

return;
}

string targetFileName = MapPath("~/_OrgData/Excel/" + xlsFile);

fileImport.PostedFile.SaveAs(targetFileName);

string excelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + targetFileName + ";Extended Properties='Excel 4.0;HDR=YES;'";

OleDbConnection xlsConn = new OleDbConnection(excelConnectionString);

xlsConn.Open();

try
{
OleDbCommand command = new OleDbCommand("Select * FROM [Sheet1$] WHERE Username <> NULL", xlsConn);

DbDataReader dr = command.ExecuteReader();

string sqlConnectionString = TAData.Common.GetConnectionString();

using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString))
{
bulkCopy.DestinationTableName = "tblPeopleTemp";

bulkCopy.ColumnMappings.Add("Username", "UserName");
bulkCopy.ColumnMappings.Add("Password", "Password");
bulkCopy.ColumnMappings.Add("Surname", "LName");
bulkCopy.ColumnMappings.Add("First Name", "FName");

bulkCopy.WriteToServer(dr);
}
}

catch (Exception ex)
{
lblError.Text = "The Excel content is not valid";

lblError.Visible = true;
}

xlsConn.Close();

File.Delete(targetFileName);
}


Did you like this resource? Share it with your friends and show your love!





Responses to "Upload excel file content to SQl server using SQL bulk copy method"

No responses found. Be the first to respond...

Feedbacks      

Post Comment:




  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:   Sign In to fill automatically.
    Email: (Will not be published, but required to validate comment)



    Type the numbers and letters shown on the left.


    Next Resource: Merge two GridView Header column in ASP.NET
    Previous Resource: Set Configuration Settings for a particular folder/page in Web.config
    Return to Resources
    Post New Resource
    Category: ASP.NET WebForms


    Post resources and earn money!
     
    More Resources
    Popular Tags   Tag posting guidelines   Search Tags  
    Upload excel file to SQl server - bulk copy  .  

    My Profile

    Active Members
    TodayLast 7 Daysmore...


    Awards & Gifts


    Email subscription
  • .NET Jobs
  • .NET Articles
  • .NET Forums
  • Articles Rss Feeds
    Forum Rss Feeds



    About Us    Trademark Disclaimer    Contact Us    Copyright    Privacy Policy    Terms Of Use    Revenue Sharing sites   Advertise   Talk to Tony John
    Copyright © SpiderWorks Technologies Pvt Ltd., Kochi, India
    2005 - 2012 All Rights Reserved.
    .NET and other trademarks mentioned in this site belong to Microsoft and other respective trademark owners.
    Articles, tutorials and all other content offered here is for educational purpose only.
    We are not associated with Microsoft or its partners.