RSS

Monthly Archives: April 2015

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

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
            }
        }
Advertisements
 
Leave a comment

Posted by on April 12, 2015 in ASP.Net

 

Tags: ,

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.

/// 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;
        }
}

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

<sessionState cookieless="true" regenerateExpiredSessionId="true" timeout="100"/>
 
Leave a comment

Posted by on April 11, 2015 in ASP.Net

 

Tags: