基于VB.net的书店管理系统
1.管理员功能菜单
用户信息管理：用户列表
书籍信息管理：书籍列表
系统管理：修改密码，系统设置，退出系统
2.用户功能管理
注册新用户：用户
忘记密码：用户
系统管理：个人信息，修改密码，注销，退出系统
3.开发平台
系统环境：Windows
开发工具：Visual Studio 2012
数据库：SQL Server 2012
系统采用技术：三层架构（dao层，service层，view层）
数据库设计
系统各模块部分代码
- 登录界面各功能
部分源码
Dim user As New User()
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
Dim username As String = TextBox1.Text
Dim password As String = TextBox2.Text
user.Set_用户名(username)
user.Set_用户密码(password)
If RadioButton1.Checked = True Then
user.Set_table("系统用户表")
UserSystemIndex(user)
ElseIf RadioButton2.Checked = True Then
user.Set_table("普通用户表")
UserIndex(user)
Else
MsgBox("请选择角色！")
End If
End Sub
Private Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click
Dim username As String = TextBox1.Text
Dim password As String = TextBox2.Text
If username = "" Then
MsgBox("请输入用户名！")
Else
user.Set_用户名(username)
user.Set_用户密码(password)
Dim update As New UserUpdate(user)
update.ShowDialog()
End If
End Sub
- 注册界面：
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
Dim user As New User()
CountUserid(user)
Dim userid As String = "PT" & user.Get_count + 1
Dim username As String = TextBox1.Text.Trim
Dim passworld As String = TextBox2.Text.Trim
Dim userFrom As String = TextBox3.Text.Trim
Dim iphone As String = TextBox4.Text.Trim
If (userid = "") Then
MsgBox("用户编号不能为空！")
Else
If (username = "") Then
MsgBox("用户名不能为空！")
Else
If (passworld = "") Then
MsgBox("密码不能为空！")
Else
If (RadioButton1.Checked = False And RadioButton2.Checked = False) Then
MsgBox("请选择性别！")
Else
If (iphone = "") Then
MsgBox("电话号码不能为空！")
Else
If (userFrom = "") Then
MsgBox("籍贯不能为空！")
Else
If (RadioButton1.Checked = True) Then
user.Set_性别("男")
End If
If (RadioButton2.Checked = True) Then
user.Set_性别("女")
End If
user.Set_用户编号(userid)
user.Set_用户名(username)
user.Set_用户密码(passworld)
user.Set_性别(user.Get_性别())
user.Set_电话号码(iphone)
user.Set_籍贯(userFrom)
user.Set_table("普通用户表")
userLoginindex(user)
Me.Close()
End If
End If
End If
End If
End If
End If
End
- 修改密码界面：
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
If TextBox1.Text = "" Then
MsgBox("请输入用户名！")
Else
user.Set_用户密码(TextBox1.Text)
userupdatapassworld(user)
Me.Close()
End If
End Sub
系统管理员登录
-
系统管理员登录界面
-
用户查询（此处按性别查询）
-
用户修改
Public Function userSelect(username As String)
Dim user As New User()
Try
Dim objconn As New OleDbConnection(conn) '创建连接对象
Dim objAdap As OleDbDataAdapter '创建适配器对象
Dim objdataSet As New DataSet
Dim strsql As String = "select * from 普通用户表 where 用户名='" & username & "'"
objAdap = New OleDbDataAdapter(strsql, objconn)
objdataSet.Reset()'清除数据集
objAdap.Fill(objdataSet, "user")
user.Set_用户编号(objdataSet.Tables("user").Rows(0).Item(0))
user.Set_用户名(objdataSet.Tables("user").Rows(0).Item(1))
user.Set_用户密码(objdataSet.Tables("user").Rows(0).Item(2))
user.Set_性别(objdataSet.Tables("user").Rows(0).Item(3))
user.Set_电话号码(objdataSet.Tables("user").Rows(0).Item(4))
user.Set_籍贯(objdataSet.Tables("user").Rows(0).Item(5))
'MsgBox(user.Get_性别())
Return user
Catch ex As Exception
MsgBox("userSelect" + ex.Message)
End Try
Return 0
End Function
- 用户添加
添加成功界面
Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
CountUserid(user)
Dim userid As String = "PT" & user.Get_count + 1
Dim username As String = TextBox2.Text.Trim
Dim userpassworld As String = TextBox3.Text.Trim
Dim iphone As String = TextBox4.Text.Trim
Dim userfrom As String = TextBox5.Text.Trim
If (username = "") Then
MsgBox("用户名不能为空！")
Else
If (userpassworld = "") Then
MsgBox("密码不能为空！")
Else
If (RadioButton1.Checked = False And RadioButton2.Checked = False) Then
MsgBox("请选择性别！")
Else
If (iphone = "") Then
MsgBox("电话号码不能为空！")
Else
If (userfrom = "") Then
MsgBox("籍贯不能为空！")
Else
If (RadioButton1.Checked = True) Then
user.Set_性别("男")
End If
If (RadioButton2.Checked = True) Then
user.Set_性别("女")
End If
user.Set_用户编号(userid)
user.Set_用户名(username)
user.Set_用户密码(userpassworld)
user.Set_性别(user.Get_性别())
user.Set_电话号码(iphone)
user.Set_籍贯(userfrom)
user.Set_table("普通用户表")
manageConsumer_Insert(user)
End If
End If
End If
End If
End If
End Sub
- 用户删除
书籍管理 - 书籍查询
'多条件查询
Private Sub Button5_Click(sender As Object, e As EventArgs) Handles Button5.Click
linenum = 0
Try
Dim bookid As String = TextBox4.Text
Dim bookname As String = TextBox1.Text
Dim bookgenres As String = ComboBox1.Text
Dim bookauthor As String = TextBox2.Text
Dim bookprice As String = TextBox3.Text
book.Set_bookId(bookid)
book.Set_bookGenres(bookgenres)
book.Set_bookName(bookname)
book.Set_bookAuthor(bookauthor)
book.Set_bookPrice(bookprice)
objdataSet = manageBookAll_Select(book)
DataGridView1.DataSource = objdataSet.Tables("book")
DataGridView1.AllowUserToAddRows = False 'DataGridView不显示最后空白的行
linenum = DataGridView1.CurrentRow.Index
DataGridView1.CurrentCell = DataGridView1.Rows(linenum).Cells(0) '移动游标
DataGridView1.Rows(linenum).Selected = True '设置选中行
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
-
修改书籍信息
-
添加书籍信息
-
删除书籍信息
Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
Dim userid As String = TextBox3.Text
user.Set_用户编号(userid)
If TextBox3.Text = "" Then
MsgBox("没有选择记录不能删除！", 64, "提示")
Else
Dim yn As Integer
yn = MsgBox("真的要删除吗?", 16 + 4, "提示")
If yn = 6 Then
manageConsumer_Delete(user)
MsgBox("删除成功！")
Call consumer1()
End If
End If
End Sub
- 更新书籍照片
Private Sub Button7_Click(sender As Object, e As EventArgs) Handles Button7.Click
Dim bookid As String = TextBox4.Text
Picture_Insert(OpenFileDialog1, bookid)
MsgBox("照片存储成功!", MsgBoxStyle.DefaultButton3, "提示")
'objdataSet = pictureSelect(bookid)
If IsDBNull(pictureSelect(TextBox4.Text)) = False Then
Dim bb() As Byte = pictureSelect(TextBox4.Text)
Dim ss As New System.IO.MemoryStream(bb)
Me.PictureBox1.Image = System.Drawing.Image.FromStream(ss)
Else
Me.PictureBox1.Image = System.Drawing.Image.FromFile("aaa.jpg")
End If
End Sub
- 删除照片
Private Sub Button8_Click(sender As Object, e As EventArgs) Handles Button8.Click
Dim i As Integer
i = MsgBox("是否删除？", MsgBoxStyle.YesNo, "提示")
If i = 6 Then
pictureDelete(TextBox4.Text)
MsgBox("照片删除成功!", MsgBoxStyle.DefaultButton3, "提示")
If IsDBNull(pictureSelect(TextBox4.Text)) = False Then
Dim bb() As Byte = pictureSelect(TextBox4.Text)
Dim ss As New System.IO.MemoryStream(bb)
Me.PictureBox1.Image = System.Drawing.Image.FromStream(ss)
Else
Me.PictureBox1.Image = Image.FromFile("aaa.jpg")
End If
End If
End Sub
- 使用AForge照相机拍照更新照片
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
'拍照
Try
Dim image1 As Bitmap = VideoSourcePlayer1.GetCurrentVideoFrame
PictureBox1.Height = VideoSourcePlayer1.Height
PictureBox1.Width = VideoSourcePlayer1.Width
PictureBox1.Image = image1
image1.Save("pic.jpg", System.Drawing.Imaging.ImageFormat.Bmp)
sfzx = 1
Catch ex As Exception
MsgBox("没有图像！")
sfzx = 0
End Try
End Sub
Private Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click
'开启摄像头
If videoDevice IsNot Nothing Then
PictureBox1.Height = VideoSourcePlayer1.Height
PictureBox1.Width = VideoSourcePlayer1.Width
videoDevice.VideoResolution = videoDevice.VideoCapabilities(0)
VideoSourcePlayer1.VideoSource = videoDevice
VideoSourcePlayer1.Start()
End If
End Sub
- 统计各类书籍的数量
Imports System.Data.SqlClient
Imports System.Drawing.Drawing2D
Imports System.Math
Public Class 统计图形
Dim conn As String = sqlconn()
Dim conn2 As String = sqlconn2()
Dim dwsm As Integer
Dim yxmc(60) As String
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
Dim xx(1, dwsm) As String
Dim i As Integer
For i = 1 To dwsm
Dim n1 As Integer
Dim objconn As New SqlConnection(conn2)
Dim strsql As String = "select count(*) from Book where bookGenres = '" & yxmc(i) & "'"
objconn.Open()
Dim objcmd As New SqlCommand(strsql, objconn)
n1 = objcmd.ExecuteScalar
xx(0, i) = yxmc(i)
xx(1, i) = n1
objconn.Close()
objcmd.Dispose()
Next
ListBox1.Items.Clear()
For i = 1 To dwsm
ListBox1.Items.Add(xx(0, i) + ": " + xx(1, i))
Next
'下面是画柱状图
PictureBox1.CreateGraphics.Clear(Color.WhiteSmoke)
Dim g As Graphics
g = PictureBox1.CreateGraphics
Dim p As New Pen(Color.Black)
Dim sb(10) As Object
Dim sb1 As New SolidBrush(Color.Red)
Dim sb2 As New SolidBrush(Color.Blue)
Dim sb3 As New SolidBrush(Color.Yellow)
Dim sb4 As New SolidBrush(Color.Green)
Dim sb5 As New SolidBrush(Color.Orange)
Dim sb6 As New SolidBrush(Color.Black)
Dim sb7 As New SolidBrush(Color.Chocolate)
Dim sb8 As New SolidBrush(Color.Gold)
Dim sb9 As New SolidBrush(Color.Red)
Dim sb10 As New SolidBrush(Color.Blue)
Dim sb11 As New SolidBrush(Color.Yellow)
Dim sb12 As New SolidBrush(Color.Green)
Dim sb13 As New SolidBrush(Color.Orange)
Dim sb14 As New SolidBrush(Color.Black)
Dim sb15 As New SolidBrush(Color.Chocolate)
Dim sb16 As New SolidBrush(Color.Gold)
Dim f As New Font("宋体", 9, FontStyle.Regular)
Dim f11 As New Font("宋体", 9, FontStyle.Regular)
g.DrawLine(p, 30, 10, 30, PictureBox1.Height - 20) '画坐标
g.DrawLine(p, 30, PictureBox1.Height - 20, PictureBox1.Width - 150, PictureBox1.Height - 20)
Dim j As Integer '写图例
For j = 1 To dwsm
g.DrawString(yxmc(j), f, sb6, PictureBox1.Width - 100, 20 + (j - 1) * 20)
Next
g.FillRectangle(sb1, PictureBox1.Width - 10, 20, 10, 10)
g.FillRectangle(sb2, PictureBox1.Width - 10, 40, 10, 10)
g.FillRectangle(sb3, PictureBox1.Width - 10, 60, 10, 10)
g.FillRectangle(sb4, PictureBox1.Width - 10, 80, 10, 10)
g.FillRectangle(sb5, PictureBox1.Width - 10, 100, 10, 10)
g.FillRectangle(sb6, PictureBox1.Width - 10, 120, 10, 10)
g.FillRectangle(sb7, PictureBox1.Width - 10, 140, 10, 10)
g.FillRectangle(sb8, PictureBox1.Width - 10, 160, 10, 10)
g.FillRectangle(sb9, PictureBox1.Width - 10, 180, 10, 10)
g.FillRectangle(sb10, PictureBox1.Width - 10, 200, 10, 10)
g.FillRectangle(sb11, PictureBox1.Width - 10, 220, 10, 10)
g.FillRectangle(sb12, PictureBox1.Width - 10, 240, 10, 10)
g.FillRectangle(sb13, PictureBox1.Width - 10, 260, 10, 10)
g.FillRectangle(sb14, PictureBox1.Width - 10, 280, 10, 10)
g.FillRectangle(sb15, PictureBox1.Width - 10, 300, 10, 10)
g.FillRectangle(sb16, PictureBox1.Width - 10, 320, 10, 10)
Dim sjj(dwsm) As Integer
For j = 1 To dwsm
sjj(j) = PictureBox1.Height - Int(xx(1, j) / 2) - 20
Next
g.FillRectangle(sb1, 50, sjj(1), 10, PictureBox1.Height - 20 - sjj(1))
g.FillRectangle(sb2, 80, sjj(2), 10, PictureBox1.Height - 20 - sjj(2))
g.FillRectangle(sb3, 110, sjj(3), 10, PictureBox1.Height - 20 - sjj(3))
g.FillRectangle(sb4, 140, sjj(4), 10, PictureBox1.Height - 20 - sjj(4))
g.FillRectangle(sb5, 170, sjj(5), 10, PictureBox1.Height - 20 - sjj(5))
g.FillRectangle(sb6, 200, sjj(6), 10, PictureBox1.Height - 20 - sjj(6))
g.FillRectangle(sb7, 230, sjj(7), 10, PictureBox1.Height - 20 - sjj(7))
g.FillRectangle(sb8, 260, sjj(8), 10, PictureBox1.Height - 20 - sjj(8))
g.FillRectangle(sb9, 290, sjj(9), 10, PictureBox1.Height - 20 - sjj(9))
g.FillRectangle(sb10, 320, sjj(10), 10, PictureBox1.Height - 20 - sjj(10))
g.FillRectangle(sb11, 350, sjj(11), 10, PictureBox1.Height - 20 - sjj(11))
g.FillRectangle(sb12, 380, sjj(12), 10, PictureBox1.Height - 20 - sjj(12))
g.FillRectangle(sb13, 410, sjj(13), 10, PictureBox1.Height - 20 - sjj(13))
g.FillRectangle(sb14, 440, sjj(14), 10, PictureBox1.Height - 20 - sjj(14))
g.FillRectangle(sb15, 470, sjj(15), 10, PictureBox1.Height - 20 - sjj(15))
g.FillRectangle(sb16, 500, sjj(16), 10, PictureBox1.Height - 20 - sjj(16))
g.DrawString("书籍类别统计图", f11, sb6, PictureBox1.Width - 350, PictureBox1.Height - 15)
'画刻度
For j = 1 To 10
g.DrawLine(p, 30, PictureBox1.Height - (j - 1) * 50 - 20, PictureBox1.Width - 110, PictureBox1.Height - (j - 1) * 50 - 20)
Next
'数据在0-800，Str((j - 1) * 200 每200写一个刻度
'数据在0-1200，Str((j - 1) * 300 每300写一个刻度
For j = 1 To 10
g.DrawString(Str((j - 1) * 100), f, sb6, 3, PictureBox1.Height - (j - 1) * 100 - 25)
Next
f.Dispose() : sb1.Dispose() : p.Dispose() : g.Dispose() : sb2.Dispose() : sb3.Dispose() : sb4.Dispose() : sb5.Dispose()
End Sub
Private Sub 统计图形_Load(sender As Object, e As EventArgs) Handles MyBase.Load
PictureBox1.Height = 430 '设PictureBox1的高为 430
PictureBox1.Width = 610
Dim stt As String
FileOpen("1", "书类名称.txt", OpenMode.Input)
dwsm = 0
Do While Not EOF(1)
stt = LineInput(1)
dwsm = dwsm + 1
yxmc(dwsm) = stt
Loop
FileClose(1)
End Sub
Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
Me.Close()
End Sub
End Class
- 打印全部书籍信息
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
objdataSet = Statistics_Book()
DataGridView1.DataSource = objdataSet.Tables("book")
DataGridView1.AllowUserToAddRows = False 'DataGridView不显示
Me.ReportViewer1.RefreshReport()
ReportViewer1.LocalReport.ReportPath = "C:\Users\HP\Desktop\书店管理系统\书店管理系统\Web\Report1.rdlc"
ReportViewer1.LocalReport.DataSources.Clear()
ReportViewer1.LocalReport.DataSources.Add(New Microsoft.Reporting.WinForms.ReportDataSource("DataSet1", objdataSet.Tables(0)))
ReportViewer1.RefreshReport()
End Sub
-
导出全部书籍信息的pdf文件
-
生成.CHM格式的帮助文档
-
说明文档
-
退出管理员登录
普通用户登录
-
普通用户登录界面
-
用户详细信息
-
书籍信息查询
-
书籍购买
-
打印凭证信息
-
各类别书籍信息查询
-
帮助文档
数据库中对全部书籍的操作
Imports System.Data.SqlClient
Imports System.Data.OleDb
Imports System.IO
Imports AForge.Video.DirectShow
Module BookDao
Dim conn As String = sqlconn()
Public Sub DeleteBookid(book As Book)
Try
Dim strsql As String = "delete from Book where bookid ='" & book.Get_bookId() & "'"
Dim objconn As New SqlConnection(conn2)
Dim objcmd As New SqlCommand(strsql, objconn)
objconn.Open()
objcmd.ExecuteNonQuery()
MsgBox("删除书籍成功！")
objconn.Close()
Catch ex As Exception
MsgBox("DeleteBookname:" + ex.Message)
End Try
End Sub
Public Sub InsertBook(book As Book)
Try
Dim strsql As String = "insert into Book(bookId ,bookName ,bookGenres ,bookAuthor ,bookPrice,bookPress ) values('" & book.Get_bookId() & "','" & book.Get_bookName() & "','" & book.Get_bookGenres() & "','" & book.Get_bookAuthor() & "','" & book.Get_bookPrice() & "','" & book.Get_bookPress() & "')"
Dim objconn As New SqlConnection(conn2)
Dim objcmd As New SqlCommand(strsql, objconn)
objconn.Open()
objcmd.ExecuteNonQuery()
MsgBox("添加书籍成功！")
objconn.Close()
Catch ex As Exception
MsgBox("InsertBook:" + ex.Message)
End Try
End Sub
Public Sub UpdateBook(book As Book)
Try
Dim strsql As String = "update Book set bookName = '" & book.Get_bookName() & "', bookGenres = '" & book.Get_bookGenres() & "',bookAuthor = '" & book.Get_bookAuthor() & "',bookPrice = '" & book.Get_bookPrice() & "',bookPress = '" & book.Get_bookPress() & "' where bookId ='" & book.Get_bookId() & "'"
Dim objconn As New SqlConnection(conn2)
Dim objcmd As New SqlCommand(strsql, objconn)
objconn.Open()
objcmd.ExecuteNonQuery()
objconn.Close()
Catch ex As Exception
MsgBox("UpdateBook:" + ex.Message)
End Try
End Sub
Public Function SelectBookTable(book As Book)
Try
Dim sqlConnection As New SqlConnection(conn2)
sqlConnection.Open()
Dim strsql As String = "select * from Book where bookGenres = '" & book.Get_bookGenres() & "'"
Dim da As New SqlDataAdapter(strsql, sqlConnection)
sqlConnection.Close()
Dim objdataSet As New DataSet
da.Fill(objdataSet, "book")
Return objdataSet
Catch ex As Exception
Return 0
End Try
End Function
Public Function SelectBookauthorTable(book As Book)
Try
Dim sqlConnection As New SqlConnection(conn2)
sqlConnection.Open()
Dim strsql As String = "select * from Book where bookAuthor = '" & book.Get_bookAuthor() & "'"
Dim da As New SqlDataAdapter(strsql, sqlConnection)
sqlConnection.Close()
Dim objdataSet As New DataSet
da.Fill(objdataSet, "book")
Return objdataSet
Catch ex As Exception
MsgBox("SelectBookauthorTable" + ex.Message)
End Try
Return 0
End Function
Public Function SelectBookAllTable(book As Book)
Try
Dim sqlConnection As New SqlConnection(conn2)
sqlConnection.Open()
Dim strsql As String = "select * from Book where bookId = '" & book.Get_bookId() & "'and bookName = '" & book.Get_bookName() & "' and bookGenres = '" & book.Get_bookGenres() & "' and bookAuthor = '" & book.Get_bookAuthor() & "'"
Dim da As New SqlDataAdapter(strsql, sqlConnection)
sqlConnection.Close()
Dim objdataSet As New DataSet
da.Fill(objdataSet, "book")
Return objdataSet
Catch ex As Exception
MsgBox("SelectBookAllTable" + ex.Message)
End Try
Return 0
End Function
Public Sub Picture_Insert(OpenFileDialog1 As OpenFileDialog, bookid As String)
Dim apppath2 As String
apppath2 = Application.StartupPath + "\照片"
OpenFileDialog1.InitialDirectory = apppath2
OpenFileDialog1.Filter = "pictures(*.jpg)|*.jpg"
OpenFileDialog1.FileName = ""
OpenFileDialog1.ShowDialog()
Dim SqlConnection As New SqlConnection(conn2)
SqlConnection.Open()
Dim objfile As New FileStream(OpenFileDialog1.FileName, FileMode.Open, FileAccess.Read)
Dim wj() As Byte
ReDim wj(objfile.Length)
objfile.Read(wj, 0, objfile.Length)
objfile.Close()
Dim strsql As String = "update Book set picture =@img where bookId ='" & bookid & "'"
Dim da As New SqlDataAdapter(strsql, SqlConnection)
Dim objcmd1 As New SqlCommand(strsql, SqlConnection)
objcmd1.Parameters.Add("@img", SqlDbType.Binary).Value = wj
objcmd1.ExecuteNonQuery()
objcmd1 = Nothing
SqlConnection.Close()
MsgBox("写入成功!")
End Sub
Public Function Picture_Select(bookid As String)
Try
Dim sqlConnection As New SqlConnection(conn2)
sqlConnection.Open()
Dim strsql As String = "select picture from Book where bookId='" & bookid & "'"
Dim da As New SqlDataAdapter(strsql, sqlConnection)
sqlConnection.Close()
Dim objdataSet As New DataSet
da.Fill(objdataSet, "picture")
Return objdataSet.Tables("picture").Rows(0).Item(0)
Catch ex As Exception
MsgBox("SelectBookTable15" + ex.Message)
End Try
Return 0
End Function
'摄像头相关
Public sfzx As Integer = 0 '判断是否照像了
Public videoDevices As FilterInfoCollection '数组
Public videoDevice As VideoCaptureDevice
Public VideoCapabilities() As VideoCapabilities
'枚举所有摄像头
Public Function getVideoDevices() As FilterInfoCollection
Dim Devices As FilterInfoCollection = New FilterInfoCollection(FilterCategory.VideoInputDevice)
Return Devices
End Function
Public Sub Picture_Insert2(bookid As String)
If sfzx = 1 Then '如果照像了就用新照片写入
Try
Dim objconn As New SqlConnection(conn2)
Dim fjm As String = "pic.jpg"
Dim objfile As New FileStream(fjm, FileMode.Open, FileAccess.Read)
Dim wj() As Byte
ReDim wj(objfile.Length)
objfile.Read(wj, 0, objfile.Length)
objfile.Close()
objconn.Open()
Dim zp As String = "update Book set picture =@img where bookId ='" & bookid & "'"
Dim objcmd1 As New SqlCommand(zp, objconn)
objcmd1.Parameters.Add("@img", SqlDbType.Binary).Value = wj
objcmd1.ExecuteNonQuery()
objcmd1 = Nothing
objconn.Close()
MsgBox("写入成功!")
Catch ex As Exception
MsgBox("没有写入成功！")
End Try
Else
MsgBox("美金来")
End If
End Sub
Public Sub Picture_Delete(bookid As String)
Try
Dim strsql As String = "update Book set picture =NULL where bookId ='" & bookid & "'"
Dim objconn As New SqlConnection(conn2)
Dim objcmd As New SqlCommand(strsql, objconn)
objconn.Open()
objcmd.ExecuteNonQuery()
objconn.Close()
Catch ex As Exception
MsgBox("Picture_Delete:" + ex.Message)
End Try
End Sub
End Module
数据库中对用户的基本操作
Imports System.Data.SqlClient
Imports System.Data.OleDb
Module UserDao
Dim conn As String = sqlconn()
Public Function userSelect(username As String)
Dim user As New User()
Try
Dim objconn As New OleDbConnection(conn) '创建连接对象
Dim objAdap As OleDbDataAdapter '创建适配器对象
Dim objdataSet As New DataSet
Dim strsql As String = "select * from 普通用户表 where 用户名='" & username & "'"
objAdap = New OleDbDataAdapter(strsql, objconn)
objdataSet.Reset()'清除数据集
objAdap.Fill(objdataSet, "user")
user.Set_用户编号(objdataSet.Tables("user").Rows(0).Item(0)) '第一行第一个单元格
user.Set_用户名(objdataSet.Tables("user").Rows(0).Item(1)) '第一行第一个单元格
user.Set_用户密码(objdataSet.Tables("user").Rows(0).Item(2))
user.Set_性别(objdataSet.Tables("user").Rows(0).Item(3))
user.Set_电话号码(objdataSet.Tables("user").Rows(0).Item(4))
user.Set_籍贯(objdataSet.Tables("user").Rows(0).Item(5))
'MsgBox(user.Get_性别())
Return user
Catch ex As Exception
MsgBox("userSelect" + ex.Message)
End Try
Return 0
End Function
Public Sub userInsert(user As User)
Try
Dim strsql As String = "insert into 普通用户表(用户编号,用户名 ,用户密码 ,性别 ,电话号码,籍贯) values ('" & user.Get_用户编号() & "','" & user.Get_用户名() & "','" & user.Get_用户密码() & "','" & user.Get_性别() & "','" & user.Get_电话号码() & "','" & user.Get_籍贯() & "')"
Dim objconn As New SqlConnection(conn2)
Dim objcmd As New SqlCommand(strsql, objconn)
objconn.Open()
objcmd.ExecuteNonQuery()
objconn.Close()
Catch ex As Exception
MsgBox("添加失败：" + ex.Message)
End Try
End Sub
Public Sub userUpdatepassworld(user As User)
Try
Dim strsql As String = "update 普通用户表 set 用户密码 = '" & user.Get_用户密码() & "' where 用户名 = '" & user.Get_用户名() & "'"
Dim objconn As New SqlConnection(conn2)
Dim objcmd As New SqlCommand(strsql, objconn)
objconn.Open()
objcmd.ExecuteNonQuery()
objconn.Close()
Catch ex As Exception
MsgBox("userUpdata" + ex.Message)
End Try
End Sub
Public Function userconsumer_Update(user As User)
Try
Dim strsql As String = "update 普通用户表 set 用户名 = '" & user.Get_用户名() & "',性别 = '" & user.Get_性别() & "',电话号码 = '" & user.Get_电话号码() & "',籍贯 = '" & user.Get_籍贯() & "' where 用户编号 = '" & user.Get_用户编号() & "'"
Dim objconn As New SqlConnection(conn2)
Dim objcmd As New SqlCommand(strsql, objconn)
objconn.Open()
objcmd.ExecuteNonQuery()
objconn.Close()
Return user
Catch ex As Exception
MsgBox("userconsumer_Update" + ex.Message)
End Try
Return 0
End Function
Public Sub userconsumer_Delete(user As User)
Try
Dim strsql As String = "delete from 普通用户表 where 用户编号 = '" & user.Get_用户编号() & "'"
Dim objconn As New SqlConnection(conn2)
Dim objcmd As New SqlCommand(strsql, objconn)
objconn.Open()
objcmd.ExecuteNonQuery()
objconn.Close()
Catch ex As Exception
MsgBox("userconsumer_Delete" + ex.Message)
End Try
End Sub
Public Function SelectUseridTable(user As User)
Try
Dim sqlConnection As New SqlConnection(conn2)
sqlConnection.Open()
Dim strsql As String = "select 用户编号,用户名,性别,电话号码,籍贯 from 普通用户表 where 用户编号 = '" & user.Get_用户编号() & "'"
Dim da As New SqlDataAdapter(strsql, sqlConnection)
sqlConnection.Close()
Dim objdataSet As New DataSet
da.Fill(objdataSet, "user")
Return objdataSet
Catch ex As Exception
MsgBox("SelectUseridTable" + ex.Message)
End Try
Return 0
End Function
Public Function SelectUsernameTable(user As User)
Try
Dim sqlConnection As New SqlConnection(conn2)
sqlConnection.Open()
Dim strsql As String = "select 用户编号,用户名,性别,电话号码,籍贯 from 普通用户表 where 用户名 = '" & user.Get_用户名() & "'"
Dim da As New SqlDataAdapter(strsql, sqlConnection)
sqlConnection.Close()
Dim objdataSet As New DataSet
da.Fill(objdataSet, "user")
Return objdataSet
Catch ex As Exception
MsgBox("SelectUsernameTable" + ex.Message)
End Try
Return 0
End Function
Public Function SelectUsersexTable(user As User)
Try
Dim sqlConnection As New SqlConnection(conn2)
sqlConnection.Open()
Dim strsql As String = "select 用户编号,用户名,性别,电话号码,籍贯 from 普通用户表 where 性别 = '" & user.Get_性别() & "'"
Dim da As New SqlDataAdapter(strsql, sqlConnection)
sqlConnection.Close()
Dim objdataSet As New DataSet
da.Fill(objdataSet, "user")
Return objdataSet
Catch ex As Exception
MsgBox("SelectUsersexTable" + ex.Message)
End Try
Return 0
End Function
Public Function SelectUserfrom(user As User)
Dim f As Integer
Try
Dim objconn As New SqlConnection(conn2)
Dim strsql As String = "select count(*) from 普通用户表 where 籍贯 = '" & user.Get_籍贯() & "'"
objconn.Open()
Dim objcmd As New SqlCommand(strsql, objconn)
f = objcmd.ExecuteScalar
objconn.Close()
objcmd.Dispose()
Catch ex As Exception
MsgBox("SelectUserfrom" + ex.Message)
End Try
If (f > 0) Then
Return 1
Else
Return 0
End If
End Function
Public Function SelectUserfromTable(user As User)
Try
Dim sqlConnection As New SqlConnection(conn2)
sqlConnection.Open()
Dim strsql As String = "select用户编号,用户名,性别,电话号码,籍贯 from 普通用户表 where 籍贯 = '" & user.Get_籍贯() & "'"
Dim da As New SqlDataAdapter(strsql, sqlConnection)
sqlConnection.Close()
Dim objdataSet As New DataSet
da.Fill(objdataSet, "user")
Return objdataSet
Catch ex As Exception
MsgBox("SelectUserfromTable" + ex.Message)
End Try
Return 0
End Function
Public Function SelectUserAllTable(user As User)
Try
Dim sqlConnection As New SqlConnection(conn2)
sqlConnection.Open()
Dim strsql As String = "select 用户编号,用户名,性别,电话号码,籍贯 from 普通用户表"
Dim da As New SqlDataAdapter(strsql, sqlConnection)
sqlConnection.Close()
Dim objdataSet As New DataSet
da.Fill(objdataSet, "user")
Return objdataSet
Catch ex As Exception
MsgBox("SelectUserAllTable" + ex.Message)
End Try
Return 0
End Function
Public Function SelectConsumerAllTable(user As User)
Try
Dim sqlConnection As New SqlConnection(conn2)
sqlConnection.Open()
Dim strsql As String = "select 用户编号,用户名,性别,电话号码,籍贯 from 普通用户表 where 用户名 = '" & user.Get_用户名() & "' and 性别 = '" & user.Get_性别() & "'"
Dim da As New SqlDataAdapter(strsql, sqlConnection)
sqlConnection.Close()
Dim objdataSet As New DataSet
da.Fill(objdataSet, "user")
Return objdataSet
Catch ex As Exception
MsgBox("SelectConsumerAllTable" + ex.Message)
End Try
Return 0
End Function
Public Sub DeleteManageConsumer(user As User)
Try
Dim strsql As String = "delete from 普通用户表 where 用户编号 = '" & user.Get_用户编号() & "'"
Dim objconn As New SqlConnection(conn2)
Dim objcmd As New SqlCommand(strsql, objconn)
objconn.Open()
objcmd.ExecuteNonQuery()
objconn.Close()
Catch ex As Exception
MsgBox("DeleteManageConsumer" + ex.Message)
End Try
End Sub
Public Sub UpdateManageConsumer(user As User)
Try
Dim strsql As String = "update 普通用户表 set 用户名 = '" & user.Get_用户名() & "',性别 = '" & user.Get_性别() & "',电话号码 = '" & user.Get_电话号码() & "',籍贯 = '" & user.Get_籍贯() & "' where 用户编号 = '" & user.Get_用户编号() & "'"
Dim objconn As New SqlConnection(conn2)
Dim objcmd As New SqlCommand(strsql, objconn)
objconn.Open()
objcmd.ExecuteNonQuery()
objconn.Close()
'Return user
Catch ex As Exception
MsgBox("UpdateManageConsumer" + ex.Message)
End Try
End Sub
Public Sub InsertManageConsumer(user As User)
Try
Dim strsql As String = "insert into 普通用户表(用户编号,用户名 ,用户密码 ,性别 ,电话号码,籍贯) values ('" & user.Get_用户编号() & "','" & user.Get_用户名() & "','" & user.Get_用户密码() & "','" & user.Get_性别() & "','" & user.Get_电话号码() & "','" & user.Get_籍贯() & "')"
Dim objconn As New SqlConnection(conn2)
Dim objcmd As New SqlCommand(strsql, objconn)
objconn.Open()
objcmd.ExecuteNonQuery()
objconn.Close()
'Return user
Catch ex As Exception
MsgBox("InsertManageConsumer" + ex.Message)
End Try
End Sub
Public Function CountUserid(user As User)
Try
Dim objconn As New OleDbConnection(conn) '创建连接对象
Dim objAdap As OleDbDataAdapter '创建适配器对象
Dim objdataSet As New DataSet
Dim strsql As String = "select count(用户编号) from 普通用户表"
objAdap = New OleDbDataAdapter(strsql, objconn)
objdataSet.Reset() '清除数据集
objAdap.Fill(objdataSet, "user") '第二个参数就是给这个虚拟表起个名字
user.Set_count(objdataSet.Tables("user").Rows(0).Item(0)) '第一行第一个单元格
Return user
Catch ex As Exception
MsgBox("CountUserid" + ex.Message)
End Try
Return 0
End Function
End Module