数据库数据库应用系统开发.docx

1、数据库 数据库应用系统开发第13章 数据库应用系统开发Public Sub CreateSqlConnection() Dim Strconn As String 定义连接字符串 Strconn = Data Source=localhost;Initial Catalog=Sales;User ID=sa;Password=csulibtd; Dim cnn As New SqlConnection() 创建连接对象实例 cnn.ConnectionString = Strconn 设置连接字符串属性 cnn.Open() 打开连接cnn.Close() 关闭连接End SubPublic

2、Sub CreateSqlCommand() Dim Strconn As String Strconn = Data Source=localhost;Initial Catalog=Sales;User ID=sa;Password=csulibtd; Dim cnn As New SqlConnection() cnn.ConnectionString = Strconn cnn.Open() Dim Mycommand As SqlCommand 声明SqlCommand类型变量 Mycommand = New SqlCommand(Select count(*) from emplo

3、yee) 创建SqlCommand类的实例 Mycommand.Connection = cnn 设置变量的Connection属性 Mycommand.CommandTimeout = 15 设置变量的CommandTimeout属性 Dim Recordcount = CInt(Mycommand.ExecuteScalar() 执行Mycommand对象并放回一个单一值 MsgBox(Recordcount) 显示结果 cnn.Close()End SubPublic Sub CreateSqlDataReader() Dim Strconn As String Strconn = Da

4、ta Source= localhost;Initial Catalog=Sales;User ID=sa;Password=csulibtd; Dim cnn As New SqlConnection() cnn.ConnectionString = Strconn cnn.Open() Dim Mycommand As SqlCommand Mycommand = New SqlCommand(Select Employee_Name,Sex from employee) Mycommand.Connection = cnn Dim StrResult As String 声明一个字符串变

5、量 Dim Mydatareader As SqlDataReader 声明一个SqlDataReader类型的变量创建一个SqlDataReader实例 Mydatareader = Mycommand.ExecuteReader(CommandBehavior.CloseConnection) Do While Mydatareader.Read = True 循环读取结果记录 获取列数据StrResult = Mydatareader.GetString(0) & & Mydatareader.GetString(1) Console.WriteLine(StrResult) 输出结果

6、Loop cnn.Close()End SubPrivate Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim Strconn As String Strconn = Data Source=localhost;Initial Catalog=Sales;User ID=sa;Password=csulibtd; Dim cnn As New SqlConnection() cnn.ConnectionString = Strconn c

7、nn.Open() Dim Mycommand As SqlCommand = New SqlCommand(Select * from employee) Mycommand.Connection = cnn Dim da As SqlDataAdapter = New SqlDataAdapter() 创建SqlDataAdapter对象 Dim ds As DataSet = New DataSet() 创建DataSet对象 da.SelectCommand = Mycommand 它设置了SqlDataAdapter对象的SelectCommand属性 da.Fill(ds, emp

8、loyee) 调用SqlDataAdapter对象的Fill方法从数据源读取数据并将其填充到数据集中。employee是数据集中的表的名称 End Sub（1）创建书商图书基本信息表bookseller_bookinfoCREATE TABLE bookseller_bookinfo(rec_id bigint identity(1,1) not null,isbn varchar(20) null,bookname varchar(200) null,author varchar(50) null,publisher_date varchar(50) null,publisher varch

9、ar(50) null,class_name varchar(50) null,book_price numeric DEFAULT 0,book_num int DEFAULT 0,provider varchar(50) null)（2）创建图书馆图书馆藏基本信息表library_bookinfoCREATE TABLE library_bookinfo (rec_id bigint identity(1,1) not null,isbn varchar(20) null,bookname varchar(200) null,author varchar(50) null,publishe

10、r_date varchar(50) null,publisher varchar(50) null,class_name varchar(50) null,book_price numeric DEFAULT 0,book_num int DEFAULT 0,provider varchar(50) null)（3）创建用户注册表userenroll_infoCREATE TABLE userenroll_info (rec_id bigint identity(1,1) not null,user_name varchar(50) not null,user_password varcha

11、r(50) nulluser_memo varchar(200) null)Private Sub ExitMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ExitMenuItem.Click Application.Exit()End SubSQL Server 2005服务器连接字符串函数Public Function ConnectString() As String ConnectString = Data Source=202.197.77.1;Initial Cat

12、alog=Sales;User ID=sa;Password=csulibtd; 设置SQL Server2005数据库链接字符串，此字符串也可保存在目录文件中End FunctionSQL命令执行函数 Public txtSQL As String 存放SQL语句 Public DBSet As DataSet 查询得到的记录集 Public ErrorMsg As String 存放错误信息 Public Function ExecuteSQL(ByVal strSQL As String, ByRef errMsg As String) As Integer 函数执行SQL的INSERT

13、、DELETE、UPDATE和SELECT语句 对于INSERT、DELETE、UPDATE语句，ExecuteSQL返回更新的记录数：-1表示程序异常；表示更新失败；大于表示操作成功，更新的记录数 对于SELECT语句：DBSet为返回的数据集；ExecuteSQL为返回的查询记录数。 Dim cnn As SqlClient.SqlConnection Dim cmd As New SqlClient.SqlCommand() Dim adpt As SqlClient.SqlDataAdapter Dim rst As New DataSet() Dim SplitSQL() As St

14、ring errMsg = Try SplitSQL = Split(strSQL) cnn = New SqlClient.SqlConnection(ConnectString() If InStr(INSERT,DELETE,UPDATE, UCase$(SplitSQL(0) Then cmd.Connection = cnn cmd.Connection.Open() cmd.CommandText = strSQL ExecuteSQL = cmd.ExecuteNonQuery() 返回更新数据记录条数 Else adpt = New SqlClient.SqlDataAdapt

15、er(strSQL, cnn) adpt.Fill(rst) ExecuteSQL = rst.Tables(0).Rows.Count 返回查询记录条数 DBSet = rst End If Catch ex As Exception errMsg = ex.Message ExecuteSQL = -1 表示执行SQL失败 Finally rst = Nothing cnn = Nothing End Try End Function启动函数SubMainSub main() Dim mf As New Register mf.ShowDialog()End Sub Private Sub

16、 cmdOK_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) If TextBox1.Text = Or TextBox2.Text = Then MsgBox(请输入用户名或密码！) Exit Sub End If Dim cnn As New SqlConnection() Dim Mycommand As SqlCommand cnn.ConnectionString = ConnectString() cnn.Open() txtSQL = select count(*) from userenroll

17、_info where user_name= & TextBox1.Text & and user_password= & TextBox2.Text & Mycommand = New SqlCommand(txtSQL) Mycommand.Connection = cnn Dim Recordcount = CInt(Mycommand.ExecuteScalar() 返回记录条数 If Recordcount = 1 Then 如找到用户名和密码相匹配的记录则登录成功 MdiForm.Show() 显示主窗体 Finalize() 释放登录窗体的资源 Else MsgBox(用户名或密

18、码错误！) Exit Sub End If End Sub Private Sub cmdCancel_Click() 退出系统Application.Exit() 退出应用程序End SubPrivate Sub cmdAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Dim Recordnum As Integer If TextBox1.Text = Then MsgBox(请输入账号！) Exit Sub End If If TextBox2.Text = Then MsgBox(请输入密码！)

19、Exit Sub End If If TextBox3.Text = Then MsgBox(请输入用户说明！) Exit Sub End If 查看用户名在用户表userenroll_info中已存在 txtSQL = select * from userenroll_info where user_name= & TextBox1.Text & Recordnum = ExecuteSQL(txtSQL, ErrorMsg) 返回值为SQL检索记录数 If Recordnum 0 Then 用户已存在，退出 MsgBox(用户已存在！) Exit Sub Else 用户不存在，新增 txt

20、SQL = insert into userenroll_info(user_name,user_password,user_memo) values( & TextBox1.Text & , & TextBox2.Text & , & TextBox3.Text & ) Recordnum = ExecuteSQL(txtSQL, ErrorMsg) If Recordnum = 1 Then MsgBox(新增用户成功！) Else MsgBox(新增用户失败！) End If End If End SubPrivate Sub cmdExit_Click(ByVal sender As

21、System.Object, ByVal e As System.EventArgs) Me.Close() 关闭此窗口End SubPrivate Sub UserModifyForm_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load Dim cn As New SqlConnection 定义一个到数据库的连接 Dim searchsql As String 定义SQL语句字符串 Dim cmd As SqlCommand 定义一个数据库操作命令 Dim dr As Sq

22、lDataReader 定义一个数据集读写器 searchsql = select user_name from userenroll_info Try cn.ConnectionString = ConnectString() cn.Open() cmd = New SqlCommand(searchsql, cn) dr = cmd.ExecuteReader() Do While dr.Read() 在ComboBox控件中添加数据库中检索到的用户账号 Me.ComboBox1.Items.Add(dr.GetValue(0) Loop dr.Close() Catch ex As Ex

23、ception MsgBox(ex.Message) End Try cn.Dispose()End SubPrivate Sub ComboBox1_SelectionChangeCommitted(ByVal sender As Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectionChangeCommitted Dim cn As New SqlConnection 定义一个到数据库的连接 Dim searchsql As String 定义SQL语句字符串 Dim cmd As SqlCommand 定义一个数据

24、库操作命令 Dim dr As SqlDataReader 定义一个数据集读写器 searchsql = select user_name,user_password,user_memo from userenroll_info where user_name= & ComboBox1.SelectedItem & Try cn.ConnectionString = ConnectString() cn.Open() cmd = New SqlCommand(searchsql, cn) dr = cmd.ExecuteReader() Do While dr.Read() 读取数据库中的账户

25、信息，并将其写入到显示控件中 TextBox1.Text = dr.GetString(1) TextBox2.Text = dr.GetString(2) Loop dr.Close() Catch ex As Exception MsgBox(ex.Message) End Try cn.Dispose() End SubPrivate Sub cmdModify_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Dim cn As New SqlConnection 定义一个到数据库的连接 Dim sear

26、chsql As String 定义SQL语句字符串 Dim cmd As SqlCommand 定义一个数据库操作命令 searchsql = update userenroll_info set user_password= & TextBox1.Text & , user_memo= & TextBox2.Text & where user_name= & ComboBox1.SelectedItem & Try cn.ConnectionString = ConnectString() cn.Open() cmd = New SqlCommand(searchsql, cn) 创建Sq

27、lCommand类实例 cmd.ExecuteNonQuery() 执行cmd对象 MsgBox(记录修改成功!) Catch ex As Exception MsgBox(ex.Message) End Try cn.Dispose() End SubPrivate Sub cmdDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Dim cn As New SqlConnection 定义一个到数据库的连接 Dim searchsql As String 定义SQL语句字符串 Dim cmd As

28、 SqlCommand 定义一个数据库操作命令 searchsql = delete userenroll_info where user_name= & ComboBox1.SelectedItem & Try cn.ConnectionString = ConnectString() cn.Open() cmd = New SqlCommand(searchsql, cn) 创建SqlCommand类实例 cmd.ExecuteNonQuery() 执行cmd对象 GetUser() 更新下拉列表框中的用户列表 MsgBox(记录删除成功!) Catch ex As Exception M

29、sgBox(ex.Message) End Try cn.Dispose() End Sub Private Sub GetUser() 取账号信息，添加到ComboBox1下拉列表框 Dim cn As New SqlConnection 定义一个到数据库的连接 Dim searchsql As String 定义SQL语句字符串 Dim cmd As SqlCommand 定义一个数据库操作命令 Dim dr As SqlDataReader 定义一个数据集读写器 searchsql = select user_name from userenroll_info Try cn.Connec

30、tionString = ConnectString() cn.Open() cmd = New SqlCommand(searchsql, cn) dr = cmd.ExecuteReader() ComboBox1.Items.Clear() TextBox1.Text = TextBox2.Text = Do While dr.Read() 在ComboBox控件中添加数据库中检索到的用户账号 Me.ComboBox1.Items.Add(dr.GetValue(0) Loop dr.Close() Catch ex As Exception MsgBox(ex.Message) End Try cn.Dispose() End Sub Private Sub cmdxjAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) 判断是否输入了ISBN号 If Trim(TextBox1.Tex