VSTO WEBBROWSER

Imports Microsoft.Office.Tools.Ribbon
Imports System.Data.SqlClient
Public Class MENU
DELAY MODULE
Private Sub MENU_Load(ByVal sender As System.Object, ByVal e As RibbonUIEventArgs) Handles MyBase.Load

End Sub

Private Sub Button1_Click(sender As Object, e As RibbonControlEventArgs) Handles Button1.Click
‘Excel Defination
Dim NativeWorksheet As Microsoft.Office.Interop.Excel.Worksheet = Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet
Dim Excel_Range As Excel.Range
‘Data Source
Dim SQLCon As New SqlConnection With {.ConnectionString = “Server=foxtube.com.au;Database=e2s;User=sa;Pwd=xxx;”}
Dim sqltxt As String = “select * from dbo.uuu”
Dim sqlcount As String = “select COUNT(*) from dbo.uuu”
Dim sqlcmd As New SqlCommand(sqltxt, SQLCon)
Dim sqlct As New SqlCommand(sqlcount, SQLCon)
Dim userReader As SqlDataReader
Dim sqlReader As SqlDataReader
‘Data Connection
SQLCon.Open()
sqlReader = sqlct.ExecuteReader
sqlReader.Read()
Dim Total_Rows As Integer = sqlReader.Item(0)
sqlReader.Close()
sqlct.Dispose()
userReader = sqlcmd.ExecuteReader
For rows As Integer = 1 To Total_Rows
userReader.Read()
Excel_Range = NativeWorksheet.Cells(rows, 1)
Excel_Range.Value = userReader.Item(0)
Excel_Range = NativeWorksheet.Cells(rows, 2)
Excel_Range.Value = userReader.Item(1)
Excel_Range = NativeWorksheet.Cells(rows, 3)
Excel_Range.Value = userReader.Item(2)
Excel_Range = NativeWorksheet.Cells(rows, 4)
Excel_Range.Value = userReader.Item(3)
Excel_Range = NativeWorksheet.Cells(rows, 5)
Excel_Range.Value = userReader.Item(4)
Next
userReader.Close()
sqlcmd.Dispose()
SQLCon.Close()
End Sub

Private Sub Button2_Click(sender As Object, e As RibbonControlEventArgs) Handles Button2.Click
‘Excel Defination
Dim NativeWorksheet As Microsoft.Office.Interop.Excel.Worksheet = Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet
Dim Excel_Range As Excel.Range
Dim rows_count_content As Integer = NativeWorksheet.Range(“A65536:E65536”).End(Excel.XlDirection.xlUp).Row
‘Into the Array
Dim Excel_Array(rows_count_content + 1, 5 + 1) As String
For i As Integer = 1 To rows_count_content
Excel_Range = NativeWorksheet.Cells(i, 1)
Excel_Array(i, 1) = Excel_Range.Value
Excel_Range = NativeWorksheet.Cells(i, 2)
Excel_Array(i, 2) = Excel_Range.Value
Excel_Range = NativeWorksheet.Cells(i, 3)
Excel_Array(i, 3) = Excel_Range.Value
Excel_Range = NativeWorksheet.Cells(i, 4)
Excel_Array(i, 4) = Excel_Range.Value
Excel_Range = NativeWorksheet.Cells(i, 5)
Excel_Array(i, 5) = Excel_Range.Value
Next

‘Data Source
Dim SQLCon As New SqlConnection With {.ConnectionString = “Server=foxtube.com.au;Database=e2s;User=sa;Pwd=xxx;”}
Dim sqlcount As String = “select COUNT(*) from dbo.ok”
Dim sqlct As New SqlCommand(sqlcount, SQLCon)
Dim sqlReader As SqlDataReader
‘Data Connection
SQLCon.Open()
sqlReader = sqlct.ExecuteReader
sqlReader.Read()
Dim Total_Rows As Integer = sqlReader.Item(0)
sqlReader.Close()
sqlct.Dispose()
For j As Integer = 1 To rows_count_content
Dim sqlinsert As String = “insert into dbo.ok values(‘” + Excel_Array(j, 1) + “‘,'” + Excel_Array(j, 2) + “‘,'” + Excel_Array(j, 3) + “‘,'” + Excel_Array(j, 4) + “‘,'” + Excel_Array(j, 5) + “‘);”
Dim sqlit As New SqlCommand(sqlinsert, SQLCon)
Dim sqliter As SqlDataReader
sqliter = sqlit.ExecuteReader
sqliter.Close()
sqlit.Dispose()
Next
SQLCon.Close()
End Sub

Private Sub Button3_Click(sender As Object, e As RibbonControlEventArgs) Handles Button3.Click
‘Excel Defination
Dim NativeWorksheet As Microsoft.Office.Interop.Excel.Worksheet = Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet
Dim Excel_Range As Excel.Range

Dim frm As New Form1
frm.WebBrowser1.ScriptErrorsSuppressed = True
frm.WebBrowser1.Navigate(“http://news.baidu.com/”)
Do While frm.WebBrowser1.ReadyState <> 4
Delay1(0.5)
Loop
‘MsgBox(“loaded”)
‘Delay1(10)
Dim rows As Integer = 2
Dim get_item_span1 As System.Windows.Forms.HtmlElementCollection = frm.WebBrowser1.Document.GetElementsByTagName(“a”)
For Each get_item_span1_ As System.Windows.Forms.HtmlElement In get_item_span1
If get_item_span1_.GetAttribute(“classname”) = “a3” Then
Excel_Range = NativeWorksheet.Cells(rows, 2)
Excel_Range.Value = get_item_span1_.GetAttribute(“innerText”)
rows += 1
End If
Next
End Sub

Private Sub Button4_Click(sender As Object, e As RibbonControlEventArgs) Handles Button4.Click
‘Excel Defination
Dim NativeWorksheet As Microsoft.Office.Interop.Excel.Worksheet = Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet
Dim Excel_Range As Excel.Range
Dim client As System.Net.WebClient = New System.Net.WebClient
Dim html As String = client.DownloadString(“http://www.nasdaq.com/zh/symbol/acn”)
Dim t1 = InStr(html, “qwidget-dollar”)
html = Mid(html, t1 + 16, html.Length)
t1 = InStr(html, “<“)
html = Mid(html, 1, t1 – 1)
‘MsgBox(html)
Excel_Range = NativeWorksheet.Cells(2, 2)
Excel_Range.Value = html

End Sub
End Class

EXCEL GET FROM SQL AND SUBMIT TO SQL

Imports Microsoft.Office.Tools.Ribbon
Imports System.Data.SqlClient
Public Class MENU

Private Sub MENU_Load(ByVal sender As System.Object, ByVal e As RibbonUIEventArgs) Handles MyBase.Load

End Sub

Private Sub Button1_Click(sender As Object, e As RibbonControlEventArgs) Handles Button1.Click
‘Excel Defination
Dim NativeWorksheet As Microsoft.Office.Interop.Excel.Worksheet = Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet
Dim Excel_Range As Excel.Range
‘Data Source
Dim SQLCon As New SqlConnection With {.ConnectionString = “Server=foxtube.com.au;Database=e2s;User=sa;Pwd=xxxxxxxx;”}
Dim sqltxt As String = “select * from dbo.uuu”
Dim sqlcount As String = “select COUNT(*) from dbo.uuu”
Dim sqlcmd As New SqlCommand(sqltxt, SQLCon)
Dim sqlct As New SqlCommand(sqlcount, SQLCon)
Dim userReader As SqlDataReader
Dim sqlReader As SqlDataReader
‘Data Connection
SQLCon.Open()
sqlReader = sqlct.ExecuteReader
sqlReader.Read()
Dim Total_Rows As Integer = sqlReader.Item(0)
sqlReader.Close()
sqlct.Dispose()
userReader = sqlcmd.ExecuteReader
For rows As Integer = 1 To Total_Rows
userReader.Read()
Excel_Range = NativeWorksheet.Cells(rows, 1)
Excel_Range.Value = userReader.Item(0)
Excel_Range = NativeWorksheet.Cells(rows, 2)
Excel_Range.Value = userReader.Item(1)
Excel_Range = NativeWorksheet.Cells(rows, 3)
Excel_Range.Value = userReader.Item(2)
Excel_Range = NativeWorksheet.Cells(rows, 4)
Excel_Range.Value = userReader.Item(3)
Excel_Range = NativeWorksheet.Cells(rows, 5)
Excel_Range.Value = userReader.Item(4)
Next
userReader.Close()
sqlcmd.Dispose()
SQLCon.Close()
End Sub

Private Sub Button2_Click(sender As Object, e As RibbonControlEventArgs) Handles Button2.Click
‘Excel Defination
Dim NativeWorksheet As Microsoft.Office.Interop.Excel.Worksheet = Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet
Dim Excel_Range As Excel.Range
Dim rows_count_content As Integer = NativeWorksheet.Range(“A65536:E65536”).End(Excel.XlDirection.xlUp).Row
‘Into the Array
Dim Excel_Array(rows_count_content + 1, 5 + 1) As String
For i As Integer = 1 To rows_count_content
Excel_Range = NativeWorksheet.Cells(i, 1)
Excel_Array(i, 1) = Excel_Range.Value
Excel_Range = NativeWorksheet.Cells(i, 2)
Excel_Array(i, 2) = Excel_Range.Value
Excel_Range = NativeWorksheet.Cells(i, 3)
Excel_Array(i, 3) = Excel_Range.Value
Excel_Range = NativeWorksheet.Cells(i, 4)
Excel_Array(i, 4) = Excel_Range.Value
Excel_Range = NativeWorksheet.Cells(i, 5)
Excel_Array(i, 5) = Excel_Range.Value
Next

‘Data Source
Dim SQLCon As New SqlConnection With {.ConnectionString = “Server=foxtube.com.au;Database=e2s;User=sa;Pwd=xxxxxxx;”}
Dim sqlcount As String = “select COUNT(*) from dbo.ok”
Dim sqlct As New SqlCommand(sqlcount, SQLCon)
Dim sqlReader As SqlDataReader
‘Data Connection
SQLCon.Open()
sqlReader = sqlct.ExecuteReader
sqlReader.Read()
Dim Total_Rows As Integer = sqlReader.Item(0)
sqlReader.Close()
sqlct.Dispose()
For j As Integer = 1 To rows_count_content
Dim sqlinsert As String = “insert into dbo.ok values(‘” + Excel_Array(j, 1) + “‘,'” + Excel_Array(j, 2) + “‘,'” + Excel_Array(j, 3) + “‘,'” + Excel_Array(j, 4) + “‘,'” + Excel_Array(j, 5) + “‘);”
Dim sqlit As New SqlCommand(sqlinsert, SQLCon)
Dim sqliter As SqlDataReader
sqliter = sqlit.ExecuteReader
sqliter.Close()
sqlit.Dispose()
Next
SQLCon.Close()
End Sub
End Class

VBA CONNECT SQL SERVER

Sub GetDataFromADO()
Dim Cn As ADODB.Connection
Dim Server_Name As String
Dim Database_Name As String
Dim User_ID As String
Dim Password As String
Dim SQLStr As String
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

Server_Name = “foxtube.com.au” ‘ Enter your server name here
Database_Name = “SQLTEST” ‘ Enter your database name here
User_ID = “sa” ‘ enter your user ID here
Password = “yxxxxx” ‘ Enter your password here
SQLStr = “SELECT * FROM dbo.SQLTB” ‘ Enter your SQL here

Set Cn = New ADODB.Connection
Cn.Open “Driver={SQL Server};Server=” & Server_Name & “;Database=” & Database_Name & _
“;Uid=” & User_ID & “;Pwd=” & Password & “;”

rs.Open SQLStr, Cn, adOpenStatic
‘ Dump to spreadsheet
With Worksheets(“sheet1”).Range(“a1:z500”) ‘ Enter your sheet name and range here
.ClearContents
.CopyFromRecordset rs
End With
‘ Tidy up
rs.Close
Set rs = Nothing
Cn.Close
Set Cn = Nothing
End Sub

添加 工具-引用 microsoft activex data 2.0

DLL SAMPLE

Public Class test1
Public Function test(ByVal a As Long, ByVal b As Long) As Long
Return a + b
End Function
End Class

Imports ClassLibrary1
Public Class Form1
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
Me.Show()
Dim localtest As New ClassLibrary1.test1
MsgBox(localtest.test(2, 5))
End Sub
End Class

TCP SOCKETS SERVER

Imports System.Net
Imports System.IO
Imports System.Net.Sockets
Public Class Form1
Dim Listning As TcpListener
Dim Allclient As TcpClient
Dim clientList As New List(Of ClassforClient)
Dim pReader As StreamReader
Dim pClient As ClassforClient
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
Listning = New TcpListener(IPAddress.Any, 3818)
Listning.Start()
UpdateList(“Server Starting”, False)
Listning.BeginAcceptTcpClient(New AsyncCallback(AddressOf AcceptClient), Listning)
End Sub
‘ create a delegate
Delegate Sub _cUpdate(ByVal str As String, ByVal relay As Boolean)
Sub UpdateList(ByVal str As String, ByVal relay As Boolean)
On Error Resume Next
If InvokeRequired Then
Invoke(New _cUpdate(AddressOf UpdateList), str, relay)
Else
TextBox1.AppendText(str & vbNewLine)
‘ if relay we will send a string
If relay Then send(str)
End If
End Sub
Sub send(ByVal str As String)
For x As Integer = 0 To clientList.Count – 1
Try
clientList(x).Send(str)
Catch ex As Exception
clientList.RemoveAt(x)
End Try
Next
End Sub
Sub AcceptClient(ByVal ar As IAsyncResult)
pClient = New ClassforClient(Listning.EndAcceptTcpClient(ar))
AddHandler(pClient.getMessage), AddressOf MessageReceived
AddHandler(pClient.clientLogout), AddressOf ClientExited
clientList.Add(pClient)
UpdateList(“New Client Joined!”, True)
Listning.BeginAcceptTcpClient(New AsyncCallback(AddressOf AcceptClient), Listning)
End Sub
Sub MessageReceived(ByVal str As String)
UpdateList(str, True)
End Sub
Sub ClientExited(ByVal client As ClassforClient)
clientList.Remove(client)
UpdateList(“Client Exited!”, True)
End Sub
Private Sub TextBox2_KeyDown(sender As Object, e As KeyEventArgs) Handles TextBox2.KeyDown
If e.KeyCode = Keys.Enter Then
e.SuppressKeyPress = True
UpdateList(“Server says : ” & TextBox2.Text, True)
TextBox2.Clear()
End If
End Sub
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
UpdateList(“Server says : ” & TextBox2.Text, True)
TextBox2.Clear()
End Sub
End Class

TCP SOCKETS CLIENT

Imports System.Net
Imports System.IO
Imports System.Net.Sockets
Public Class Form1
Dim client As TcpClient
Dim sWriter As StreamWriter
Dim NIckFrefix As Integer = New Random().Next(1111, 9999)
Sub xLoad() Handles Me.Load
Me.Text &= ” ” & NIckFrefix
End Sub
Delegate Sub _xUpdate(ByVal str As String)
Sub xUpdate(ByVal str As String)
If InvokeRequired Then
Invoke(New _xUpdate(AddressOf xUpdate), str)
Else
TextBox3.AppendText(str & vbNewLine)
End If
End Sub

Sub read(ByVal ar As IAsyncResult)
Try
xUpdate(New StreamReader(client.GetStream).ReadLine)
client.GetStream.BeginRead(New Byte() {0}, 0, 0, AddressOf read, Nothing)

Catch ex As Exception
xUpdate(“You have disconnecting from server”)
Exit Sub
End Try
End Sub
Private Sub send(ByVal str As String)
Try
sWriter = New StreamWriter(client.GetStream)
sWriter.WriteLine(str)
sWriter.Flush()
Catch ex As Exception
xUpdate(“You’re not server”)
End Try
End Sub
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
If Button1.Text = “Connect” Then
Try
client = New TcpClient(TextBox1.Text, CInt(TextBox2.Text))
client.GetStream.BeginRead(New Byte() {0}, 0, 0, New AsyncCallback(AddressOf read), Nothing)
Button1.Text = “Disconnect”
Catch ex As Exception
xUpdate(“Can’t connect to the server!”)
End Try
Else
client.Client.Close()
client = Nothing
Button1.Text = “Connect”
End If
End Sub
Private Sub TextBox4_KeyDown(sender As Object, e As KeyEventArgs) Handles TextBox4.KeyDown
If e.KeyCode = Keys.Enter Then
e.SuppressKeyPress = True
send(NIckFrefix & ” says : ” & TextBox4.Text)
TextBox4.Clear()
End If
End Sub
Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
send(NIckFrefix & ” says : ” & TextBox4.Text)
TextBox4.Clear()
End Sub
End Class

SAVE&CLICK

Public Class Form1
Private Declare Auto Function FindWindowEx Lib “user32” (ByVal parentHandle As Integer, _
ByVal childAfter As Integer, _
ByVal lclassName As String, _
ByVal windowTitle As String) As Integer
Private Declare Function SetForegroundWindow Lib “user32” (ByVal hwnd As IntPtr) As Long
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
Dim handle As Integer = FindWindowEx(0, 0, Nothing, “文件下载”)
While True
‘MsgBox(handle)
If handle <> 0 Then
SetForegroundWindow(handle)
SendKeys.Send(“{S}”)
‘SendKeys.Send(“{LEFT}”)
‘Delay(500)
‘SendKeys.Send(“{ENTER}”)
‘Delay(500)
End If
Delay(500)
handle = FindWindowEx(0, 0, Nothing, “文件下载”)
End While
End Sub

End Class

EXCEL TO SQL(server)

Imports System.Data.SqlClient
‘Imports System.Data.Sql
‘Imports System.Data.OleDb
Public Class Form1
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
Me.Show()
‘Read From Excel
Dim connectionString As String ‘ Used to store the connection string
Dim customerList As New DataSet ‘ Used to store the temp records readed from the Excel file
Dim excelData As OleDb.OleDbDataAdapter
Dim fileName As String = “c:\test\n.xlsx”
connectionString = String.Format(“Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=””Excel 12.0;HDR=YES””;”, fileName)
excelData = New OleDb.OleDbDataAdapter(“SELECT * FROM [Sheet1$]”, connectionString)
excelData.Fill(customerList)
DataGridView1.ColumnHeadersVisible = False
DataGridView1.DataSource = customerList.Tables(0)
‘Export to Datatable
Dim dt As New DataTable()
dt = customerList.Tables(0)
Dim strConnString As String = “Server=192.168.1.3;Database=database_alan;User=sa;Pwd=xxxxxxxx;”
‘Data Type
‘MsgBox(dt.Columns(0).DataType.ToString)

Try
‘Data Structure
Dim sql As String
sql = “CREATE TABLE Comics2(MOBILE_NO numeric(18, 0) NULL,CUS_NAME nchar(50) NULL,L1 nchar(50) NULL,L2 nchar(50) NULL,L3 varchar(100) NULL)”
Dim cmd As New SqlCommand(sql, New SqlConnection(strConnString))
cmd.Connection.Open()
cmd.ExecuteNonQuery()
cmd.Connection.Close()
Catch ex As Exception
End Try
‘Data Output
Dim xRowsLoaded As Long = 0
Try
Using xSqlCon As New SqlConnection(strConnString)
Dim xBcp As New SqlBulkCopy(xSqlCon, SqlBulkCopyOptions.[Default], Nothing)
xBcp.DestinationTableName = “Comics2”
xBcp.BatchSize = dt.Rows.Count
xBcp.BulkCopyTimeout = 0
If xSqlCon.State = ConnectionState.Closed Then
xSqlCon.Open()
End If
xBcp.WriteToServer(dt)
xRowsLoaded = dt.Rows.Count
xBcp.Close()
xSqlCon.Close()
End Using
Catch Ex As Exception
MsgBox(Ex.ToString)
End Try
MessageBox.Show(String.Format(“Inserted {0} Rows at a Time!”, xRowsLoaded))

End Sub
End Class

EXCEL TO ACCESS

Imports System.Data.OleDb
Imports System.Data.SqlClient
Imports System.Data.Sql
Imports System.Data
Public Class Form1
Dim ds1 As New DataSet
Dim ds2 As New DataSet
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
Dim connectionString As String ‘ Used to store the connection string
Dim customerList As New DataSet ‘ Used to store the temp records readed from the Excel file
Dim excelData As OleDb.OleDbDataAdapter
Dim fileName As String = “c:\test\test.xlsx”
connectionString = String.Format(“Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=””Excel 12.0;HDR=YES””;”, fileName)
excelData = New OleDb.OleDbDataAdapter(“SELECT * FROM [Sheet1$]”, connectionString)
excelData.Fill(customerList)
DataGridView1.ColumnHeadersVisible = False
DataGridView1.DataSource = customerList.Tables(0)
‘Export to Datatable
Dim dt As New DataTable()
dt = customerList.Tables(0)
‘Creat Data Structure
Dim strConnString As String = “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\test\test.accdb;Persist Security Info=False;”
Dim con As New OleDbConnection(strConnString)
‘Try
‘ con.Open()
‘ Dim cmd As New OleDb.OleDbCommand(“CREATE TABLE test_t ([Field1] TEXT(10), [Field2] TEXT(10))”, con)
‘ cmd.ExecuteNonQuery()
‘ con.Close()
‘Catch ex As Exception
‘End Try

‘Data Output
con.Open()
Dim zd1 As String, zd2 As String
For s = 0 To customerList.Tables(0).Rows.Count – 1
zd1 = customerList.Tables(0).Rows(s)(0).ToString
zd2 = customerList.Tables(0).Rows(s)(1).ToString

Dim Sql As String = “INSERT INTO test_t(Field1,Field2) VALUES(‘” + zd1 + “‘,'” + zd2 + “‘)”
Dim MyCommand As New OleDbCommand(Sql, con)
MyCommand.ExecuteNonQuery()
Next
con.Close()
MsgBox(“导入成功!”)

End Sub

Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click

End Sub
Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click

End Sub
End Class

EXCEL TO SQL SERVER

Imports System.Data.SqlClient
‘Imports System.Data.Sql
‘Imports System.Data.OleDb
Public Class Form1
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
Me.Show()
‘Read From Excel
Dim connectionString As String ‘ Used to store the connection string
Dim customerList As New DataSet ‘ Used to store the temp records readed from the Excel file
Dim excelData As OleDb.OleDbDataAdapter
Dim fileName As String = “c:\test\n.xlsx”
connectionString = String.Format(“Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=””Excel 12.0;HDR=YES””;”, fileName)
excelData = New OleDb.OleDbDataAdapter(“SELECT * FROM [Sheet1$]”, connectionString)
excelData.Fill(customerList)
DataGridView1.ColumnHeadersVisible = False
DataGridView1.DataSource = customerList.Tables(0)
‘Export to Datatable
Dim dt As New DataTable()
dt = customerList.Tables(0)
Dim strConnString As String = “Server=192.168.1.101;Database=database_alan;User=sa;Pwd=yXXXXXXX1;”
‘Data Type
‘MsgBox(dt.Columns(0).DataType.ToString)

‘Data Structure
Dim sql As String
sql = “CREATE TABLE User_Info(MOBILE_NO numeric(18, 0) NULL,CUS_NAME varchar(50) NULL)”
Dim cmd As New SqlCommand(sql, New SqlConnection(strConnString))
cmd.Connection.Open()
cmd.ExecuteNonQuery()
cmd.Connection.Close()
‘Data Output
Dim xRowsLoaded As Long = 0
Try
Using xSqlCon As New SqlConnection(strConnString)
Dim xBcp As New SqlBulkCopy(xSqlCon, SqlBulkCopyOptions.[Default], Nothing)
xBcp.DestinationTableName = “User_Info”
xBcp.BatchSize = dt.Rows.Count
xBcp.BulkCopyTimeout = 0
If xSqlCon.State = ConnectionState.Closed Then
xSqlCon.Open()
End If
xBcp.WriteToServer(dt)
xRowsLoaded = dt.Rows.Count
xBcp.Close()
xSqlCon.Close()
End Using
Catch Ex As Exception
End Try
MessageBox.Show(String.Format(“Inserted {0} Rows at a Time!”, xRowsLoaded))

End Sub
End Class

SQL SIMPLE CONNECTION

Me.Show()
Dim SQLCon As New SqlConnection With {.ConnectionString = “Server=192.168.1.101;Database=database_alan;User=sa;Pwd=yXXXXXXX;”}

Dim sqltxt As String = “select * from User_Info” ‘定义sql语句

Dim sqlcmd As New SqlCommand(sqltxt, SQLCon) ‘定义sqlCommand
Dim userReader As SqlDataReader ‘定义sqldatareader
Try
SQLCon.Open() ‘打开连接
userReader = sqlcmd.ExecuteReader ‘生成sqldatareader
userReader.Read() ‘读取数据
MsgBox(userReader.Item(3)) ‘显示读取的第四个数据
userReader.Close() ‘关闭阅读
Catch ex As Exception
MsgBox(ex.Message)
Finally
sqlcmd.Dispose() ‘ 释放sqlCommand
End Try

SQL FIREWALL

在运行SQL Server的服务器上,我们要找到哪些是SQL Server
正在侦听的端口,并将其添加到Windows防火墙的入站例外。
首先,我们需要添加 SQL Server 服务侦听 Windows
防火墙的入站例外,以便客户端可以通过 Windows 防火墙建立 SQL Server 连接的 TCP 端口。
其次,如果我们不在连接字符串中指定 TCP 端口,SQL Server 不侦听
TCP 1433 端口,我们就需要添加SQL Server Brower Services侦听的UDP 1434 端口到
Windows 防火墙的入站例外。
如何找到SQL Server正在侦听的TCP端口,可以按一下步骤:
1. 打开 SQL Server 配置管理器中,从开始->所有程序-> Microsoft
SQL Server 2005/2008/2008 R2 ->配置工具
; 2. 在SQL Server 配置管理器窗口 , 左面板中的SQL
服务器网络配置节点下单击协议的 双击TCP/IP在右面板中,切换到IP
地址选项卡,可以找出所有 SQL Server 侦听的每个 IP 地址的端口或指定端口的所有
IP 地址。
如何在Windows
防火墙进行端口的配置以允许远程访问,比如:数据库引擎的默认实例端口是1433。
1.在“开始”菜单上,单击“运行”,键入 WF.msc,然后单击“确定”。
2.在“高级安全 Windows 防火墙”的左窗格中,右键单击“入站规则”,然后在操

作窗格中单击“新建规则”。
3.在“规则类型”对话框中,选择“端口”,然后单击“下一步”。
4.在“协议和端口”对话框中,选择 TCP。选择“特定本地端口”
,然后键入数据库引擎实例的端口号,例如默认实例的端口号 1433。单击“下一步”。
5.在“操作”对话框中,选择“允许连接”,然后单击“下一步”。
6.在“配置文件”
对话框中,选择在你想要连接到数据库引擎时描述计算机连接环境的任何配置文件,然后单击“下一步”。
7.在“名称”对话框中,输入此规则的名称和说明,再单击“完成”。