How to Import Excel File into SQL Server using SQLBULK in ASP.net

This example explains how to upload excel file, read Excel file data,  save Excel file data and import into SQL Server using SQLBULK in ASP.Net.

Step:1 Create a Excel file like:

Step:2 Create a Sql table in database like:

Step:3 Now, add the code in "Default.aspx"

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

<asp:Button ID="btnImport" Font-Bold="true" ForeColor="White"

BackColor="#136671" Height="23px" runat="server" Text="Import Excel Data"
onclick="btnImport_Click" />


Step:4  Add the code in "Default.aspx.cs"

Add these NameSpace

using System.IO;
using System.Data.OleDb;
using System.Data;


Write the code in Click Event of Import Button

protected void btnImport_Click(object sender, EventArgs e)
{
 string strFilepPath;
 DataSet ds = new DataSet();
 string strConnection = ConfigurationManager.ConnectionStrings
                          ["connectionString"].ConnectionString;
 if (fupUpload.HasFile)
 {
  try
  {
    FileInfo fi = new FileInfo(fupUpload.PostedFile.FileName);
    string ext = fi.Extension;
    if (ext == ".xls" || ext == ".xlsx")
    {
     string filename = Path.GetFullPath(fupUpload.PostedFile.FileName);
     string DirectoryPath = Server.MapPath("~/UploadExcelFile//");
     strFilepPath = DirectoryPath + fupUpload.FileName;      
     Directory.CreateDirectory(DirectoryPath);
     fupUpload.SaveAs(strFilepPath);   
     string strConn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" 
                      + strFilepPath + ";Extended Properties=\"Excel 12.0 
                      Xml;HDR=YES;IMEX=1\"";
     OleDbConnection conn = new OleDbConnection(strConn);
     conn.Open();     
     OleDbCommand cmd = new OleDbCommand("SELECT * FROM [Sheet1$]", conn);
     OleDbDataAdapter da = new OleDbDataAdapter(cmd);
     da.Fill(ds);
     DeleteExcelFile(fupUpload.FileName); // Delete File Log
     SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection, 
                                   SqlBulkCopyOptions.KeepIdentity);
     sqlBulk.DestinationTableName = "Table_1";
     sqlBulk.WriteToServer(ds.Tables[0]);
     conn.Close();
     sqlBulk.Close();      
     ScriptManager.RegisterStartupScript(Page, GetType(), "script1",  
        "alert('Excel file successfully imported into DB');", true);
     return;
    }     
    else
    {
      ScriptManager.RegisterStartupScript(Page, GetType(), "script1",   
                    "alert('Please upload excel file only');", true);
     return;
    }
  }   
  catch (Exception ex)
   {
    DeleteExcelFile(fupUpload.FileName);       
    ScriptManager.RegisterStartupScript(Page, GetType(), "script1",   
      "alert('error occured: " + ex.Message.ToString() + "');", true);
    return;
   }
  }  
 else
  { 
    ScriptManager.RegisterStartupScript(Page, GetType(), "script1",   
                        "alert('Please upload excel file');", true);
   return;
  }
}

protected void DeleteExcelFile(string Name)
{              
 if (Directory.Exists(Request.PhysicalApplicationPath +   
                                           "UploadExcelFile\\"))
   {      
    string[] logList = Directory.GetFiles(Request.PhysicalApplicationPath 
                       + "UploadExcelFile\\", "*.xls");
     foreach (string log in logList)
      {         
        FileInfo logInfo = new FileInfo(log);
        string logInfoName = logInfo.Name.Substring(0,  
                             logInfo.Name.LastIndexOf('.'));
        if (logInfoName.Length >= Name.Length)
         {            
          if (Name.Equals(logInfoName.Substring(0, Name.Length)))
           {
             logInfo.Delete();
           }
         }
      }
   }
}





and the result will be be like as:

How to Expand & Rotate Image Using JQUERY

In this article, we will create an expanding image by using slider and also rotate that image by JQUERY.

So, Let's get started:

The HTML

<div id="container">

<div id="slider">

</div>

<br />

<div id="images">

<img id="img" alt="" class="img_main" src="Images/1.jpg" />

<img id="img1" alt="" class="img" src="Images/2.jpg" />

<img id="img7" alt="" class="img" src="Images/3.jpg" />

<img id="img3" alt="" class="img" src="Images/4.jpg" />

<img id="img4" alt="" class="img" src="Images/5.jpg" />

<img id="img5" alt="" class="img" src="Images/6.jpg" />

<img id="img2" alt="" class="img" src="Images/7.jpeg" />

</div>

</div>

Now, Let's look at the style

The CSS

<style type="text/css">

#container

{

padding: 50px;

margin-top: 20px;

}

#images

{

padding: 40px;

}

#images img

{

margin-left: -100px;

background: #e9e9e9;

padding: 10px;

cursor: pointer;

}

#images img:hover

{

background: #333;

}

#slider

{

width: 350px;

}

</style>

The JavaScript

<link href="js/theme/jquery.ui.all.css" rel="stylesheet" type="text/css" />

<script src="js/Jquery.js" type="text/javascript"></script>

<script src="js/jquery-ui-1.8.7.custom.min.js" type="text/javascript"></script>

<script src="js/Base.js" type="text/javascript"></script>

<script type="text/javascript" src="js/rotate3Di.js"></script>

<script type="text/javascript">

$(function () {

$("#slider").slider({

value: -100,

min: -100,

max: 0,

step: 1,

slide: function (event, ui) {

$(".img").css("margin-left", ui.value + "px");

}

});

$("img").rotate3Di(45);

$('img').click(function () { $(this).rotate3Di('toggle', 1000); });

});

</script>

and That's it. Hope, you enjoyed this article and like it.

Download Files:

JqueryTest

How to Add Scripts to Header Dynamically in ASP.Net

Dynamically adding controls to header is really pretty straightforward. In order to add JavaScript to a master page’s head section. We can just go ahead and dynamically build some HtmlGenericControls to contain our JavaScript, then inject them.

So here’s the markup for your master page’s head section:

<!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>

</head>

Note:- head is set to runat the server. That’s because, if it is not running at the server, we can’t access the master page’s Page.Header property, which we need to be able to access in order to directly add our script tags to the page.

Now,You then just add the following code to the page’s Page_Load event handler:

string strJScript = string.Empty;

strJScript = @"<script type='text/javascript'>

…......// Add your script code //.......................

</script>";

LiteralControl jsScript = new LiteralControl(strJScript);

Page.Header.Controls.Add(jsScript);

That's it! Javascript, CSS files, Meta Tags and anything else can be inserted to the header dynamically!!

Entity Framework

What is Entity Framework ?

• Entity Framework based on ORM (object relation mapping).

• Entity Framework enables developers to work with relational data, eliminate the need for most of the data access plumbing code that developers usually need to write.

• It uses LINQ (Language Integrated Query) to retrieve and manipulate data as strongly typed objects.

 

Advantages - 

• One common syntax "LINQ" for all object queries.
• Fast
• Easy to implement.
• Less coding required.

Now, take an example for How to use it:

Insertion, Updation and Deletion with Entity Framework

Step:1 First, create a ASP.Net empty web application named "WebAppEntity". Right click on solution, add new item > add ADO.NET Data Entity Model named it as "BusinessObjects.edmx"

Now check your Web.config file, it will create connection string automatically. And check your BusinessObjects.designer.cs also, It looks like:

Open/Expand Contexts,

Step:2 File > Add > New Project > ASP.NET Empty Web Application named "Entity Framework".

• Copy connection string of "WebApp Entity Web.config file" to "Entity Framework Web.config file".

• Add two "Web Refrences" in "Entity Framework"

                       1. Projects > "WebAppEntity"
                       2. System.Web.Entity

• Create a aspx page named "EntityFirst.aspx" for create, update and delete the records.

Now, Solution looks like:

Step:3 Now Add a code into "EntityFirst.aspx" and  "EntityFirst.aspx.cs"     

Add three button for save, update and delete in EntityFirst.aspx like:

<asp:Button ID="btnsave" runat="server" Text="Save" OnClick="btnsave_Click" />
<asp:Button ID="btnupdate" runat="server" Text="Update" OnClick="btnupdate_Click" />
<asp:Button ID="btndelete" runat="server" Text="Delete" OnClick="btndelete_Click" />


Now, add code in code behind like:

using  WebAppEntity;


LeadManagementTestEntities LeadDB = new LeadManagementTestEntities();

protected void btnsave_Click(object sender, EventArgs e)

{
aaa objaa = new aaa();

objaa.user = "Name";

objaa.city = "Gurgaon";

objaa.designation = "Software Engineer";

LeadDB.aaas.AddObject(objaa);
LeadDB.SaveChanges();
lblmsg.Text = "Record saved successfully";

}

protected void btnupdate_Click(object sender, EventArgs e)
{
aaa objaa = LeadDB.aaas.SingleOrDefault(p => p.user == "Ruchi");
objaa.city = "Delhi";
LeadDB.SaveChanges();
lblmsg.Text = "Record updated successfully";
}

protected void btndelete_Click(object sender, EventArgs e)
{
aaa objaa = LeadDB.aaas.SingleOrDefault(p => p.user == "Name");
LeadDB.aaas.DeleteObject(objaa);
LeadDB.SaveChanges();
lblmsg.Text = "Record deleted successfully";
}

Now, we are done. Run the application and test it.