摘要:VB.NET連接字串 模組化
全文使用模組化 使程式簡化
Imports System.Data.OleDb
以下是模組程式碼 主要還是以SQL為主
Module selectModule
Function selectfunction(ByVal connect_string As System.String, ByVal selectcon As System.String)
'insdelupcomm(資料庫來源,SQL指令)
'return(一個資料表) 例如:DataGridView1.DataSource = selectfunction(connect_string, cs)
Dim connx As New OleDbConnection(connect_string)
Try
connx.Open()
Dim dataAdapter As New OleDb.OleDbDataAdapter(selectcon, connx)
Dim wdataset As New DataSet
dataAdapter.Fill(wdataset, "人員")
Dim MAN As Integer = wdataset.Tables("人員").Rows.Count
Return wdataset.Tables("人員")
connx.Close()
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Function
Function insdelupcomm(ByVal connStr As System.String, ByVal insdelupcom As System.String)
'insdelupcomm(資料庫來源,SQL指令)
'return()
Dim conn As OleDbConnection, cmd As OleDbCommand
Dim testbol As Boolean
conn = New OleDbConnection(connStr)
Try
conn.Open()
cmd = New OleDbCommand(insdelupcom, conn)
testbol = cmd.ExecuteNonQuery()
conn.Close()
Catch ex As Exception
MsgBox(ex.Message)
End Try
Return testbol
End Function
End Module
下方則是使用模組的物件
SELECT
Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
Dim connect_string As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source =" + "..\..\App_Data" + "\db2.mdb"
Dim cs As String = "select 員工編號,姓名,名 from man"
DataGridView1.DataSource = selectfunction(connect_string, cs)
End Sub
INSERT
Private Sub Button6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button6.Click
Dim connStr, insertCmd As String
connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source =" + "..\..\App_Data" + "\db2.mdb"
insertCmd = "Insert into man(員工編號,姓名,名,職稱,稱呼)Values(" + TextBox1.Text + ",'" + TextBox2.Text + "','" + TextBox3.Text + "','" + TextBox4.Text + "','" + TextBox5.Text + "')"
MsgBox(insdelupcomm(connStr, insertCmd))
End Sub
UPDATE
Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click
Dim connStr, updataCmd As String
connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source =" + "..\..\App_Data" + "\db2.mdb"
updataCmd = "update man set 姓名='" + TextBox2.Text + "',"
updataCmd += "名='" + TextBox3.Text + "',"
updataCmd += "職稱='" + TextBox4.Text + "',"
updataCmd += "稱呼='" + TextBox5.Text + "'"
updataCmd += " where 員工編號=" + TextBox1.Text + ""
MsgBox(insdelupcomm(connStr, updataCmd))
'DataGridView1.Refresh()
Button4_Click(sender, e)
End Sub
DELETE