Imports System.Data.SqlClient
Imports System.Data.OleDb
Imports System.Data.Odbc
'这个方法已经老了
Namespace DBConfig
'Connection类-------------------------------------------------------
Public Class DBConnection
Private _strConn As String
Public Property strConn As String
Get
Return _strConn
End Get
Set(ByVal value As String)
_strConn = value
End Set
End Property
#If DB_OLEDB Then
protected conn as oleDbConnection
#ElseIf DB_ODBC Then
protected conn as OdbcConnecton
#Else
Protected conn As SqlConnection
#End If
Protected Sub open()
If strConn Is Nothing Or strConn = "" Then
MsgBox("请指定连接字符串")
Return
End If
#If DB_OLEDB Then
conn=New oleDbConnection(strConn)
#ElseIf DB_ODBC Then
conn=New OdbcConnection(strConn)
#Else
conn = New SqlConnection(strConn)
#End If
conn.Open()
End Sub
Protected Sub Close()
conn.Close()
End Sub
End Class
'Command类-----------------------------------------------------------------
Public Class DBCommand
Inherits DBConnection
Public Sub New(ByVal str As String)
strConn = str
End Sub
Public Function Insert(ByVal strSql As String) As Integer
open()
#If DB_OLEDB Then
dim cmd as OledbCommand=new OleDbCommand(strSql,conn)
#ElseIf DB_ODBC Then
dim cmd as OdbcCommand=new odbcCommand(strSql,conn)
#Else
Dim cmd As SqlCommand = New SqlCommand(strSql, conn)
#End If
Dim count As Integer
count = cmd.ExecuteNonQuery
Close()
Return count
End Function
Public Function Delete(ByVal table As String, ByVal row As String, ByVal value As String) As Integer
open()
Dim strSql As String = "Delete from " & table + "Where " & row & "=" & value
#If DB_OLEDB Then
dim cmd as OledbCommand=new OleDbCommand(strSql,conn)
#ElseIf DB_ODBC Then
dim cmd as ObdcCommand=new ObdcCommand(strSql,conn)
#Else
Dim cmd As SqlCommand = New SqlCommand(strSql, conn)
#End If
Dim count As Integer = 0
count = cmd.ExecuteNonQuery
Close()
Return count
End Function
Public Function Update(ByVal table As String, ByVal strContent As String, ByVal row As String, ByVal value As Integer) As Integer
open()
Dim strSql As String = "Update " & table & " Set " & strContent & " Where " & row & "=" & value & ""
#If DB_OLEDB Then
dim cmd as OledbCommand=new OledbCommand(strSql,conn)
#ElseIf DB_ODBC Then
dim cmd as OdbcCommand=new OdbcCommand(strSql,conn)
#Else
Dim cmd As SqlCommand = New SqlCommand(strSql, conn)
#End If
Dim count As Integer = 0
count = cmd.ExecuteNonQuery
Close()
Return count
End Function
End Class
End Namespace
Imports MyNameSpace.DBConfig
Imports System.Data.SqlClient
Public Class Form1
Dim conn As SqlConnection
Dim da As SqlDataAdapter
Dim ds As DataSet '必须加“NEW”关键字
Dim dt As DataTable
Public Function GetConncetion1() As String
Dim myDbConfig As New DBConnection
myDbConfig.strConn = My.Settings.SalesConnectionString
Return myDbConfig.strConn
End Function
Private Function GetConnection() As SqlConnection
Return New SqlConnection(My.Settings.SalesConnectionString)
End Function
Private Sub DisplayData1(ByVal str As String, ByVal datagridview1 As DataGridView) '用DataReader对象
datagridView1.DataSource = Nothing
conn = GetConnection()
conn.Open()
Dim comm As New SqlCommand(str, conn)
Dim dr As SqlDataReader = comm.ExecuteReader
Dim dt As New DataTable
dt.Load(dr)
conn.Close()
DataGridView1.DataSource = dt
End Sub
Private Sub DisplayData2(ByVal str As String, ByVal datagridview1 As DataGridView) '用DataSet对象和Adapter的Fill方法
DataGridView1.DataSource = Nothing
conn = GetConnection()
conn.Open()
da = New SqlDataAdapter(str, conn)
da.Fill(ds, "dt")
conn.Close()
DataGridView1.DataSource = ds.Tables("dt")
End Sub
Private Sub DisplayData3(ByVal str As String, ByVal datagridview1 As DataGridView) '用DataSet对象和Adapter的Fill方法
DataGridView1.DataSource = Nothing
conn = GetConnection()
Dim comm As New SqlCommand(str, conn)
da = New SqlDataAdapter()
da.SelectCommand = comm
conn.Open()
ds = New DataSet
da.Fill(ds, "grade")
conn.Close()
DataGridView1.DataSource = ds.Tables("grade")
End Sub
Private Sub DisplayData4(ByVal bds As BindingSource, ByVal dgv As DataGridView)
conn = GetConnection()
conn.Open()
da = New SqlDataAdapter("Select * from grade", conn)
Dim customerTable As New DataTable
da.Fill(customerTable)
bds.DataSource = customerTable
dgv.DataSource = bds
dgv.ColumnHeadersVisible = True
dgv.RowHeadersVisible = True
conn.Close()
End Sub
Private Sub DataReaderData(ByVal str As String)
conn = GetConnection()
conn.Open()
Dim comm As New SqlCommand(str, conn)
Dim dr As SqlDataReader
dr = comm.ExecuteReader
Dim strOutput As String = ""
Try
While dr.Read
strOutput += dr("学号").ToString.PadLeft(10) + Space(5)
strOutput += dr("姓名").ToString.PadLeft(10) + Space(5)
strOutput += dr("语文").ToString.PadLeft(10) + Space(5)
strOutput += dr("数学").ToString.PadLeft(10) + Space(5)
strOutput += dr("英语").ToString.PadLeft(10) + Space(5)
strOutput += vbCrLf
End While
Catch ex As Exception
MsgBox("出现异常")
Finally
dr.Close()
conn.Close()
End Try
'Label2.Text = strOutput
End Sub
Private Sub UpdateData()
conn = GetConnection()
conn.Open()
Dim comm As New SqlCommand
Dim trans As SqlTransaction
trans = conn.BeginTransaction
comm.Connection = conn
comm.Transaction = trans
Try
comm.CommandText = "Update grade set 数学=95 Where 姓名 like '%周%'"
comm.ExecuteNonQuery()
comm.CommandText = "Update grade set 数学=65 Where 姓名 like '%张%'"
comm.ExecuteNonQuery()
trans.Commit()
'Label1.Text = "事务运行成功"
Catch ex As Exception
trans.Rollback()
'Label1.Text = "有错"
Finally
conn.Close()
End Try
End Sub
Private Sub InsertRecord1()
'conn = GetConnection()
'conn.Open()
'Dim strSql As String = "Insert into grade(学号,姓名,语文,数学,英语) " & _
'"Values('" & txtId.Text & "','" & txtName.Text & "','" & _
'txtChinese.Text & "','" & txtMaths.Text & "','" & txtEnglish.Text & "')"
'Dim comm As New SqlCommand(strSql, conn)
'comm.ExecuteNonQuery()
'conn.Close()
End Sub
Private Sub InserRecord2()
conn = GetConnection()
Dim strInsertSql As String = "Insert into grade(学号,姓名,数学) Values(27,'于谦',59)"
Dim strUpdateSql As String = "Update grade Set 数学=60 where 学号=27"
Dim strSelectSql As String = "Select * From grade where 学号='27'"
Dim InsertComm As New SqlCommand(strInsertSql, conn)
Dim UpdateComm As New SqlCommand(strUpdateSql, conn)
Dim SelectComm As New SqlCommand(strSelectSql, conn)
da = New SqlDataAdapter
conn.Open()
da.InsertCommand = InsertComm
da.UpdateCommand = UpdateComm
da.SelectCommand = SelectComm
ds = New DataSet
da.InsertCommand.ExecuteNonQuery()
da.Fill(ds, "grade1")
da.UpdateCommand.ExecuteNonQuery()
da.Fill(ds, "grade2")
da.Fill(ds, "grade3")
'显示插入结果
'DataGridView2.DataSource = ds.Tables("grade1")
'显示更新结果
'DataGridView3.DataSource = ds.Tables("grade2")
'显示查询结果
'DataGridView4.DataSource = ds.Tables("grade3")
conn.Close()
End Sub
Private Sub RunningCreateDataSet(ByVal dgv As DataGridView)
Dim myds As New DataSet
Dim mydt As New DataTable("Squares")
Dim mydr As DataRow
Dim i As Integer
mydt.Columns.Add(New DataColumn("数字", GetType(Integer)))
mydt.Columns.Add(New DataColumn("平方", GetType(Integer)))
For i = 0 To 10
mydr = mydt.NewRow
mydr(0) = i
mydr(1) = i * i
mydt.Rows.Add(mydr)
Next
myds.Tables.Add(mydt)
dgv.DataSource = myds.Tables("Squares")
myds.WriteXml("Squares.xml")
End Sub
Private Sub SortDataView(ByVal dgv As DataGridView)
conn = GetConnection()
conn.Open()
da = New SqlDataAdapter
ds = New DataSet
Dim strSql As String = "Select * from grade"
Dim comm As New SqlCommand(strSql, conn)
da.SelectCommand = comm
da.Fill(ds, "grade")
Dim dv As DataView
dv = New DataView(ds.Tables("grade"))
dv.RowFilter = "语文>60"
dv.Sort = "学号 asc,语文 desc"
Label1.Text = "满足条件的记录有:" + dv.Count.ToString + "条"
dgv.DataSource = dv
conn.Close()
End Sub
Private Sub SearchfromDataView()
conn = GetConnection()
conn.Open()
Dim strSql As String = "Select * from Grade"
Dim comm As New SqlCommand(strSql, conn)
da = New SqlDataAdapter()
da.SelectCommand = comm
ds = New DataSet
da.Fill(ds, "grade")
Dim dv As DataView
dv = New DataView(ds.Tables("grade"), "", "姓名", DataViewRowState.CurrentRows)
'Dim rowIndex As Integer = dv.Find(TextBox1.Text)
'If rowIndex = -1 Then
'Label2.Text = "没找到"
'Else
'Label2.Text = "姓名=" & dv(rowIndex)("姓名").ToString & Space(5) & _
'"语文=" & dv(rowIndex)("语文").ToString & Space(5) & _
' "数学=" & dv(rowIndex)("数学").ToString & Space(5) & _
'"英语=" & dv(rowIndex)("英语").ToString
'End If
conn.Close()
End Sub
Private Sub myFilter(ByVal bds As BindingSource, ByVal dgv As DataGridView)
conn = GetConnection()
conn.Open()
da = New SqlDataAdapter("Select * from grade order by 数学 asc", conn)
Dim customerTable As New DataTable
da.Fill(customerTable)
bds.DataSource = customerTable
dgv.DataSource = bds
conn.Close()
End Sub
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
DisplayData1("select * from grade", DataGridView1)
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim cmd As New DBCommand(GetConncetion1)
cmd.Update("grade", "数学=90", "学号", 14)
DisplayData1("select * from grade", DataGridView2)
End Sub
End Class