Imports System.Data.SqlClient Imports System.Data.OleDb PublicClass Form1Class Form1 '执行SQL查询语句 PrivateSub Button1_Click()Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Try Dim MyConnection AsNew OleDbConnection("Provider= Microsoft.Jet.OLEDB.4.0;Data Source="+ Application.StartupPath +"Northwind.mdb") Dim MyCommand AsNew OleDbCommand(Me.TextBox1.Text, MyConnection) MyConnection.Open() Dim MyReader As OleDbDataReader = MyCommand.ExecuteReader() Dim MyTable =New DataTable() MyTable.Load(MyReader) DataGridView1.DataSource = MyTable MyConnection.Close() Catch ex As Exception MessageBox.Show(ex.Message, "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information) EndTry End Sub '导入SQL Server数据库 PrivateSub Button2_Click()Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click Try Dim MyString AsString="Data Source=(local);Integrated Security=SSPI; Database=Northwind" Dim MyConnection AsNew SqlConnection(MyString) MyConnection.Open() Dim MyBulkCopy AsNew SqlBulkCopy(MyConnection) MyBulkCopy.DestinationTableName =Me.TextBox2.Text MyBulkCopy.WriteToServer(CType(DataGridView1.DataSource, DataTable)) MyConnection.Close() MessageBox.Show("数据导出导入操作完成!", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information) Catch ex As Exception MessageBox.Show(ex.Message, "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information) EndTry End Sub End Class
三、用户登陆验证
'较安全的用户校验 PrivateSub Button2_Click()Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click Dim MyUserName AsString=Me.TextBox1.Text Dim MyPassword AsString=Me.TextBox2.Text Dim MySQL AsString="Select * From MyUsers Where UserName LIKE '"+ MyUserName.Replace("'", "''") +"' And Password LIKE'"+ MyPassword.Replace("'", "''") +"'" Try Dim MyConnection AsNew SqlConnection("Data Source=.;Integrated Security=SSPI; Database=Northwind;Asynchronous Processing=true") Dim MyCommand AsNew SqlCommand(MySQL, MyConnection) MyConnection.Open() Dim MyReader As SqlDataReader = MyCommand.ExecuteReader() If MyReader.Read() Then MessageBox.Show("登录成功!", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information) Else MessageBox.Show("登录失败!", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information) EndIf Catch ex As Exception MessageBox.Show(ex.Message, "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information) EndTry End Sub
三、筛选记录
PublicClass Form1Class Form1 '显示客户数据表 PrivateSub Button1_Click()Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Try Me.DataSet1 =New DataSet() Me.OleDbConnection1.ConnectionString ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+ Application.StartupPath +"Northwind.mdb" Me.OleDbDataAdapter1.SelectCommand.CommandText ="SELECT * FROM 客户" Me.OleDbDataAdapter1.SelectCommand.Connection =Me.OleDbConnection1 Me.OleDbDataAdapter1.Fill(Me.DataSet1, "客户") Me.DataGrid1.DataSource =Me.DataSet1.Tables(0) Catch ex As Exception MessageBox.Show(ex.Message, "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information) EndTry End Sub '筛选排序数据信息 PrivateSub Button2_Click()Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click Try Dim MyFilter AsString=Me.TextBox1.Text '如:城市 like '%北京%' Dim MySorter AsString=Me.TextBox2.Text '如:公司名称 desc Dim MyRows() As DataRow =Me.DataSet1.Tables(0).Select(MyFilter, MySorter) Dim MyDataSet AsNew DataSet() MyDataSet =Me.DataSet1.Clone() MyDataSet.Tables(0).Rows.Clear() ForEach MyRow As DataRow In MyRows Dim NewRow As DataRow = MyDataSet.Tables(0).NewRow NewRow.Item("客户ID") = MyRow.Item("客户ID") NewRow.Item("公司名称") = MyRow.Item("公司名称") NewRow.Item("联系人姓名") = MyRow.Item("联系人姓名") NewRow.Item("联系人头衔") = MyRow.Item("联系人头衔") NewRow.Item("地址") = MyRow.Item("地址") NewRow.Item("城市") = MyRow.Item("城市") NewRow.Item("地区") = MyRow.Item("地区") NewRow.Item("邮政编码") = MyRow.Item("邮政编码") NewRow.Item("国家") = MyRow.Item("国家") NewRow.Item("电话") = MyRow.Item("电话") NewRow.Item("传真") = MyRow.Item("传真") MyDataSet.Tables(0).Rows.Add(NewRow) Next Me.DataGrid1.DataSource = MyDataSet.Tables(0) Catch ex As Exception MessageBox.Show(ex.Message, "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information) EndTry End Sub End Class
一、数据库链接1、使用OleDbConnection、OleDbCommand、OleDbDataReader连接读取数据库 Dim MyConnection As New OleDbConnection("Provider= Microsoft.Jet.OLEDB.4.0;Data Source=" + Application.StartupPath + "Nor