Bulk Copy data from Data.Table Data. Set to SQL Server Table using C and VB.Net. In this article I will explain how to use Sql.Bulk. Copy to insert bulk data from Grid.View to database in ASP.Net. Sql. Bulk. Copy class as the name suggests does bulk insert from one source to another and hence multiple selected rows from the Grid.Linq-in-datatable-.png?x85039' alt='Update Rows In Datatable Using Linq Distinctby' title='Update Rows In Datatable Using Linq Distinctby' />Update Rows In Datatable Using Linq Distinct VbView can be easily read and inserted in SQL Server database table using the Sql.Bulk. Copy class.Database. I have created a simple table named Customers whose schema is shown below.Note The SQL for creating the database is provided in the attached sample code.HTML Markup. The HTML Markup consists of an ASP.Net Grid. View with Check.Box to select rows and a Button for inserting bulk data from Grid.View to SQL Server Database table.Grid. View. IDGrid.View. Auto. Generate.Columnsfalse lt Columns lt asp Template.Field lt Item.Template lt asp Check.Box. IDCheck. Box.Item. Template lt asp Template.Update Rows In Datatable Using Linq Distinct ValueField lt asp Bound.Field. Data. FieldIdHeader.Update Rows In Datatable Using Linq Distinct' title='Update Rows In Datatable Using Linq Distinct' />TextIdItem.Style Width3. Bound.Field. Data. FieldNameHeader.TextNameItem. Style Width1.Bound. Field. Data.FieldCountryHeader.TextCountryItem. Style Width1.Columns lt asp Grid.View lt br lt asp Button.IDButton. 1TextBulk InsertOn.ClickBulkInsertrunatserver Namespaces.You will need to import the following namespaces.Cusing System. Data using System.I have been trying to use OleDbDataAdapter to update a DataTable but got confused about the commands.Since I sometimes get info from diffrent tables I cant use a.Data. Sql. Client using System.Configuration VB.Net. Imports System.Data. Imports System.Data. Sql. Client.Imports System. Configuration.G06.png]];var lpix_1=pix_1.length;var p1_0= [[726' alt='Update Rows In Datatable Using Linq Distinct Syntax' title='Update Rows In Datatable Using Linq Distinct Syntax' />Populating the Grid.View. In this article I am populating the Grid.View using the following XML file in the Page Load event.Customers lt Customer lt Id 1lt Id lt Name John Hammondlt Name lt Country United Stateslt Country lt Customer lt Customer lt Id 2lt Id lt Name Mudassar Khanlt Name lt Country Indialt Country lt Customer lt Customer lt Id 3lt Id lt Name Suzanne Mathewslt Name lt Country Francelt Country lt Customer lt Customer lt Id 4lt Id lt Name Robert Schidnerlt Name lt Country Russialt Country lt Customer lt Customers Cprotectedvoid PageLoadobject sender, Event.Args e if Is. Post.Back Data.Set ds new. Data.Set ds. Read.XmlServer. Map. PathCustomers.Grid. View. 1. Data.Source ds. Tables0 Grid.View. 1. Data. Bind VB.Net. Protected. Sub PageLoadsender As.Object, e As. Event.Args Handles. Me.Load If. Not. Me.Is. Post. Back Then Dim ds As.New. Data. Set ds.Read. XmlServer. Map.PathCustomers. Grid.View. 1. Data. Source ds.Tables0 Grid.View. 1. Data. Bind End.If. End. Sub. Inserting bulk data from Grid.View to SQL Server database table in ASP.Net. The following event handler is executed on the click of the Button.Here first a Data.Table is created with column schema same as that of the destination SQL Server database table and then a loop is executed of the Grid.View rows. Inside the loop, the values of the cells of the rows in which the Check.Box is checked are fetched and are inserted into the Data.Table we had created earlier.Now a connection is established with the database and the Sql.Bulk. Copy object is initialized and the name of the Table is specified using the Destination.Table. Name property.Finally the columns are mapped and all the rows from the Data.Table are inserted into the SQL Server table.Note The mapping of columns of the Data.Table and the SQL Server table is optional and you need to do only in case where your Data.Table andor the SQL Server Table do not have same number of columns or the names of columns are different.Cprotectedvoid BulkInsertobject sender, Event.Args e Data. Table dt new.Data. Table dt.Columns. Add. Rangenew.Data. Column3 new.Data. ColumnId, typeofint, new.Data. ColumnName, typeofstring, new.Data. ColumnCountry,typeofstring foreach Grid.View. Row row in Grid.View. 1. Rows if row.Find. ControlCheck.Box. 1 as. Check.Box. Checked int id int.Parserow. Cells1.Text string name row.Cells2. Text string country row.Cells3. Text dt.Rows. Addid, name, country if dt.Rows. Count 0 string cons.String Configuration.Manager. Connection.Stringsconstr. Connection.String using Sql.Connection con new.Sql. Connectioncons.String using Sql.Bulk. Copy sql. Bulk.Copy new. Sql. Bulk.Copycon Set the database table name sql.Bulk. Copy. Destination.Table. Name dbo. Customers OPTIONAL Map the Data.Table columns with that of the database table sql.Bulk. Copy. Column.Mappings. AddId, Customer.Id sql.Bulk. Copy. Column.Mappings. AddName, Name sql.Bulk. Copy. Column.Mappings. AddCountry, Country con.Open sql.Bulk. Copy. Write.To. Serverdt con.Close VB.Net. Protected. Sub BulkInsertsender As.Object, e As. Event.Args Dim dt As.New. Data. Table dt.Columns. Add. RangeNew.Data. Column2 New.Data. ColumnId, Get.TypeInteger, New.Data. ColumnName, Get.TypeString, New. Data.ColumnCountry, Get.TypeString For.Each row As. Grid.View. Row. In Grid.View. 1. Rows If.Try. Castrow. Find.ControlCheck. Box.Check. Box. Checked Then Dim id As.Integer Integer. Parserow.Cells1. Text Dim name As.String row. Cells2.Text Dim country As.String row. Cells3.Text dt.Rows. Addid, name, country End.If Next If dt.Rows. Count 0 Then Dim cons.String As. String Configuration.Manager. Connection.Stringsconstr. Connection.String Using con As.New. Sql. Connectioncons.String Using sql.Bulk. Copy As. New.Sql. Bulk. Copycon Set the database table name sql.Bulk. Copy. Destination.Table. Name dbo. Customers OPTIONAL Map the Data.Table columns with that of the database table sql.Bulk. Copy. Column.Mappings. AddId, Customer.Id sql.Bulk. Copy. Column.Mappings. AddName, Name sql.Bulk. Copy. Column.Mappings. AddCountry, Country con.Open sql.Bulk. Copy. Write.To. Serverdt con.Close End.Using End.Using End. If. Editions Nationals Du Permis De Conduire Belgique . End. Sub. Downloads.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
October 2017
Categories |