Save Insert Export Import Excel Data Into Sql Server SqlBulkCopy ASP.NET

This post explains How To Save Insert Or Export Import Excel Data In to Sql Server Database Table Using SqlBulkCopy In ASP.NET

First of all create a Excel workbook as shown in image below and insert some data into it.



Create a table in SQL database with following schema 



Now write this code to insert data into SQL table

public partial class _Default : System.Web.UI.Page
{
string strConnection = ConfigurationManager.ConnectionStrings
["ConnectionString"].ConnectionString;
protected void Page_Load(object sender, EventArgs e)
{

}
protected void Button1_Click(object sender, EventArgs e)
{
//Create connection string to Excel work book
string excelConnectionString =
@"Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=C:\Details.xls;
Extended Properties=""Excel 8.0;HDR=YES;""";

//Create Connection to Excel work book
OleDbConnection excelConnection =
new OleDbConnection(excelConnectionString);

//Create OleDbCommand to fetch data from Excel
OleDbCommand cmd = new OleDbCommand
("Select [ID],[Name],[Location] from [Detail$]",
excelConnection);

excelConnection.Open();
OleDbDataReader dReader;
dReader = cmd.ExecuteReader();

SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection);
sqlBulk.DestinationTableName = "Details";
//sqlBulk.ColumnMappings.Add("ID", "ID");
//sqlBulk.ColumnMappings.Add("Name", "Name");
sqlBulk.WriteToServer(dReader);
}
}

If there are more columns in your database table or excel workbook and you want to insert data in some of them than you need to add ColumnMappings like this

sqlBulk.ColumnMappings.Add("ID", "ID");
sqlBulk.ColumnMappings.Add("Name", "Name");

End result will be like this

Read More

Drag Drop GridView Rows With JQuery Asp.Net

This Example explains How To Implement Drag And Drop GridView Rows Functionality Using JQuery JavaScript In Asp.Net 2.0 3.5 4.0 To Rearrange Row On Client Side.

You need to download and add JQuery and TableDnD plugin in your application.

GridView is populated with Northwind database using SqlDataSource.

Add Script references and css style in head section of page.

   1:  <style type="text/css">

   2:     .highlight

   3:      {

   4:          color : White !important;

   5:          background-color : Teal !important;

   6:      }

   7:  </style>

   8:  <script src="jquery-1.7.1.js" type="text/javascript"/>

   9:  <script src="jquery.tablednd.0.7.min.js" type="text/javascript"/>

Call tableDnD function of drag and drop plugin by passing Gridview Id.

   1:  <script type="text/javascript" language="javascript">

   2:  $(document).ready(function() 

   3:  {

   4:  $("#<%=GridView1.ClientID%>").tableDnD(

   5:              {

   6:                  onDragClass: "highlight"

   7:              });

   8:  });

   9:  </script>

  10:  </head>

   1:  <asp:GridView ID="GridView1" runat="server" AllowPaging="True" 

   2:                AutoGenerateColumns="False" DataKeyNames="OrderID" 

   3:                DataSourceID="SqlDataSource1">

   4:  <Columns>

   5:  <asp:BoundField DataField="OrderID" HeaderText="OrderID"/>

   6:  <asp:BoundField DataField="Freight" HeaderText="Freight"/>

   7:  <asp:BoundField DataField="ShipName" HeaderText="ShipName"/>

   8:  <asp:BoundField DataField="ShipCity" HeaderText="ShipCity"/>

   9:  <asp:BoundField DataField="ShipCountry" HeaderText="ShipCountry"/>

  10:  </Columns>

  11:  </asp:GridView>

Read More

Print GridView Data In Asp.Net Using C# VB JavaScript

To Print GridView Data In Asp.Net Using C# And VB.NET, I have placed GridView inside a Div and this Div will be called for printing using javascript.

Print window will be opened in onclick event of Button. Javascript to print data is written in Page Load event of page and registered with RegisterStartupScript.

HTML SOURCE OF GRIDVIEW

---

   1:  <div id="gvDiv">

   2:   

   3:  <asp:GridView ID="gvPrint" runat="server" 

   4:                DataSourceID="SqlDataSource1">

   5:  <Columns>

   6:  <asp:BoundField DataField="CategoryID" 

   7:                  HeaderText="CategoryID"/>

   8:  <asp:BoundField DataField="CategoryName" 

   9:                  HeaderText="CategoryName"/>

  10:  </Columns>

  11:  </asp:GridView>

  12:  </div>

  13:     

  14:  <asp:Button ID="btnPrint" runat="server" 

  15:              Text="Print Data"/>

C# CODE

---

01protected void Page_Load(object sender, EventArgs e)

02    {

03        string printScript =

04        @"function PrintGridView()

05         {

06            var gridInsideDiv = document.getElementById('gvDiv');

07            var printWindow = window.open('gview.htm','PrintWindow','letf=0,top=0,width=150,height=300,toolbar=1,scrollbars=1,status=1');

08            printWindow.document.write(gridInsideDiv.innerHTML);

09            printWindow.document.close();

10            printWindow.focus();

11            printWindow.print();

12            printWindow.close();}";

13        this.ClientScript.RegisterStartupScript(Page.GetType(), "PrintGridView", printScript.ToString(), true);

14        btnPrint.Attributes.Add("onclick", "PrintGridView();");      

15    }

VB.NET

---

01Protected Sub Page_Load(sender As Object, e As EventArgs)

02 Dim printScript As String = "function PrintGridView()

03         {

04            var gridInsideDiv = document.getElementById('gvDiv');

05            var printWindow = window.open('gv.htm','PrintWindow','letf=0,top=0,width=150,height=300,toolbar=1,scrollbars=1,status=1');

06            printWindow.document.write(gridInsideDiv.innerHTML);

07            printWindow.document.close();

08            printWindow.focus();

09            printWindow.print();

10            printWindow.close();}"

11 Me.ClientScript.RegisterStartupScript(Page.[GetType](), "PrintGridView", printScript.ToString(), True)

12 btnPrint.Attributes.Add("onclick", "PrintGridView();")

13End Sub

Read More