ASP.Net Bulk Insert Excel Data using SqlBulkCopy

This code sample is for a Excel Import programmatically into SQL Server database.

Important points to note.

• Use excel data in a single sheet.


• The excel file format should match the table schema.

Excel format:

Id

Name

Description

CreatedAt

CreatedBy

ModifiedAt

ModifiedBy

Enabled

Deleted

.aspx page

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

<asp:Button ID="Button1" runat="server" Text="Import" CssClass="btn btn-warning" OnClick="ImportExcel" />

Code Behind File

[sourcecode language="csharp"]
protected void ImportExcel(object sender, EventArgs e)
        {
            if (FileUpload1.HasFile)
            {
                // SQL Server Connection String
                string sqlConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["flexi_stocky"].ConnectionString;

                // Bulk Copy to SQL Server
                SqlBulkCopy bulkInsert = new SqlBulkCopy(sqlConnectionString);
                try
                {
                  
                    string path = string.Concat(Server.MapPath("~/uploads/" + FileUpload1.FileName));
                    FileUpload1.SaveAs(path);

                    // Connection String to Excel Workbook
                    string excelConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 8.0", path);
                    OleDbConnection connection = new OleDbConnection();
                    connection.ConnectionString = excelConnectionString;
                    OleDbCommand command = new OleDbCommand("select * from [Sheet1$]", connection);
                    connection.Open();
                    // Create DbDataReader to Data Worksheet
                    DbDataReader dr = command.ExecuteReader();

                    
                    bulkInsert.DestinationTableName = "your_sqlTableName";
                    bulkInsert.WriteToServer(dr);
                    bulkInsert.Close();
                    //Show success
                }
                catch (Exception ex)
                {
                    bulkInsert.Close();
                    //Show error
                }

            }
            else
            {
                //File not set
            }
        }
[/sourcecode]

How to use Sessions in Web Services ASP.Net

Using sessions in web services is little different from its normal usage. Here we can not access sessions with Session as in Page methods. Instead we use HttpContext.Current.Session.

Sessions should be enabled for web methods.

A sample code snippet would be as follows.

[sourcecode language="csharp"]
/// Summary description for ReceivingService
    /// </summary>
    [WebService(Namespace = "http://tempuri.org/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [System.ComponentModel.ToolboxItem(false)]
    // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
     [System.Web.Script.Services.ScriptService]
    public class ReceivingService : System.Web.Services.WebService
    {

        [WebMethod(EnableSession = true)]
        [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
        public Dictionary<string, object> removeItem(int Id)
        {
            var response = new Dictionary<string, object>();
            bool found = false;

            if ( HttpContext.Current.Session["rec_cart"] != null)
            {
                List<CartItem> cartItems = (List<CartItem>)Session["rec_cart"];
                if (cartItems.Count > 0)
                {
                    foreach(var item in cartItems){
                        if(item.Id == Id){
                            cartItems.Remove(item);
                             HttpContext.Current.Session["rec_cart"] = cartItems;
                            found = true;
                            break;
                        }
                    }
                }
            }
            if (found)
            {
                response["status"] = true;
                response["total"] = GetGrandTotal();
            }
            else
            {
                response["status"] = false;
            }
            return response;
        }
}
[/sourcecode]

I also configured cookieless sessions in web.config to get web service call properly routed.

<sessionState cookieless="true" regenerateExpiredSessionId="true" timeout="100"/>