Find Code:
All Words
Any of the Words
Exact Phrase
Home
:
Code
:
Forums
:
Submit
:
Mailing List
:
About
:
Contact
Code
All
VB.NET
ASP.NET
C#
VB Classic
ASP Classic
Snippets
Popular
Resources
Submit Code
Forums
Articles
Tips
Links
Books
Contest
Link to us
DataTableToMSSQL
Author:
Zvonimir Begic
E-mail:
Click to e-mail author
Submitted:
10/27/2011
Version:
VB 2010
Compatibility:
VB 2010
Category:
Databases
Views:
8407
This is short but very powerfull code snipet for inserting in mssql table from datatable object. Use this for very large inserts in table. SqlBulkCopy class is much slower in my test(on 10 000 000 record results: SqlBulkCopy was slower 40 seconds). Enjoy!
Declarations:
Example: Dim n As New DataTableToMSSql("data source=zbegic\sqlexpress;initial catalog=test;integrated security=Yes") n.setColumnNames("tekst", "tekst1", "broj") n.setColumnTypes("string", "string", "integer") n.setTableName("tablica") n.saveDataTableToMSSql(tabl) MessageBox.Show("data saved!")
Code:
Imports System.Text Imports System.Data.SqlClient Public Class DataTableToMSSql Private connection As SqlConnection Private columnNames As String() Private columnTypes As String() Private tableName As String Sub New(ByVal connString As String) connection = New SqlConnection(connString) End Sub Public Sub setColumnNames(ByVal ParamArray tableColumnNames As String()) columnNames = tableColumnNames End Sub Public Sub setColumnTypes(ByVal ParamArray tableColumnTypes As String()) columnTypes = tableColumnTypes End Sub Public Sub setTableName(ByVal tblName As String) tableName = tblName End Sub Sub saveDataTableToMSSql(ByVal table As DataTable) If table.Rows.Count = 0 Then MessageBox.Show("There is not any row in the table!") Exit Sub End If Dim slozicomm As New StringBuilder Dim colNames As New StringBuilder For Each s As String In columnNames colNames.Append(s + ", ") Next colNames = colNames.Remove(colNames.Length - 2, 2) slozicomm.Append("insert into " + tableName + "(" + colNames.ToString + ") values ") For n As Integer = 0 To table.Rows.Count - 1 Dim r As DataRow = table.Rows(n) slozicomm.Append("(") Dim counter As Integer = -1 For Each s As String In columnNames counter += 1 If columnTypes(counter).ToLower = "integer" Then slozicomm.Append(r(s).ToString + ",") ElseIf columnTypes(counter).ToLower = "string" Then slozicomm.Append("'" + r(s).ToString + "',") End If Next slozicomm = slozicomm.Remove(slozicomm.Length - 1, 1) slozicomm.Append("),") If (n <> 0 And (n Mod 999 = 0 Or n = table.Rows.Count - 1)) Then Dim ind As Integer = slozicomm.ToString.LastIndexOf(",") slozicomm = slozicomm.Remove(ind, 1) Dim insertData As New SqlCommand(slozicomm.ToString, connection) Try connection.Open() insertData.ExecuteNonQuery() connection.Close() Catch ex As Exception MessageBox.Show("insert error!" + vbCrLf + ex.Message) End Try slozicomm = New StringBuilder() slozicomm.Append("insert into " + tableName + "(" + colNames.ToString + ") values ") End If Next End Sub End Class
Home
|
Forums
|
Submit
|
Books
|
Mailing List
|
Advertising
|
About
|
Contact
© 2024 A1VBCode. All rights reserved.
Legal disclaimer & terms of use
Privacy statement