后台

Imports System.Data.SqlClient
Namespace tableclass'用户表类Public Class userPrivate mName As StringPrivate mPassword As StringPublic Property Name As StringGetReturn mNameEnd GetSet(ByVal value As String)mName = valueEnd SetEnd PropertyPublic Property Password As StringGetReturn mPasswordEnd GetSet(ByVal value As String)mPassword = valueEnd SetEnd PropertyPublic Function GetAllUser() As DataTableDim conn As SqlConnection = GetConnection()conn.Open()Dim strSql As String = "Select * from usertable"Dim da As New SqlDataAdapter(strSql, conn)Dim dt As New DataTableda.Fill(dt)conn.Close()Return dtEnd FunctionPublic Function GetByValue(ByVal strName As String, ByVal strpwd As String) As DataTableDim conn As SqlConnection = GetConnection()conn.Open()Dim strSql As String = "Select * from usertable where name='{0}' and password='{1}'"strSql = String.Format(strSql, strName, strpwd)Dim da As New SqlDataAdapter(strSql, conn)Dim dt As New DataTableda.Fill(dt)conn.Close()Return dtEnd Function'添加Public Sub Add()Dim conn As SqlConnection = GetConnection()Tryconn.Open()Dim sql As String = "INSERT INTO usertable (name,password) VALUES (@name,@password)"Dim comm As SqlCommand = New SqlCommand(sql, conn)Dim p1 As SqlParameter = New SqlParameter("@name", Name)Dim p2 As SqlParameter = New SqlParameter("@phone", Password)comm.Parameters.Add(p1)comm.Parameters.Add(p2)comm.ExecuteNonQuery()Finallyconn.Close()End TryEnd Sub'更新Public Sub Update()Dim conn As SqlConnection = GetConnection()Tryconn.Open()Dim sql As String = "UPDATE usertable SET password=@password WHERE username=" + NameDim comm As SqlCommand = New SqlCommand(sql, conn)Dim p1 As SqlParameter = New SqlParameter("@name", Name)Dim p2 As SqlParameter = New SqlParameter("@phone", Password)comm.Parameters.Add(p1)comm.Parameters.Add(p2)comm.ExecuteNonQuery()Catch ex As ExceptionFinallyconn.Close()End TryEnd Sub'删除Public Sub Delete(ByVal nm As String)Dim conn As SqlConnection = GetConnection()Tryconn.Open()Dim sql As String = String.Format("DELETE FROM usertable WHERE name={0}", Name)Dim comm As SqlCommand = New SqlCommand(sql, conn)comm.ExecuteNonQuery()Catch ex As ExceptionFinallyconn.Close()End TryEnd SubEnd Class'进货表类Public Class JinHuoPrivate mjhID As StringPrivate mjhDate As DatePrivate mjhZhongLei As StringPrivate mjhHPID As StringPrivate mjhMingCheng As StringPrivate mjhDanWei As StringPrivate mjhDanJia As DecimalPrivate mjhCount As IntegerPrivate mjhJinE As DecimalPrivate mjhJinShouRen As StringPrivate mjhGongYingShang As StringPublic Property jhID As StringGetReturn mjhIDEnd GetSet(ByVal value As String)mjhID = valueEnd SetEnd PropertyPublic Property jhDate As DateGetReturn mjhDateEnd GetSet(ByVal value As Date)mjhDate = valueEnd SetEnd PropertyPublic Property jhZhongLei As StringGetReturn mjhZhongLeiEnd GetSet(ByVal value As String)mjhZhongLei = valueEnd SetEnd PropertyPublic Property jhHPID As StringGetReturn mjhHPIDEnd GetSet(ByVal value As String)mjhHPID = valueEnd SetEnd PropertyPublic Property jhMingCheng As StringGetReturn mjhMingChengEnd GetSet(ByVal value As String)mjhMingCheng = valueEnd SetEnd PropertyPublic Property jhDanWei As StringGetReturn mjhDanWeiEnd GetSet(ByVal value As String)mjhDanWei = valueEnd SetEnd PropertyPublic Property jhDanJia As DecimalGetReturn mjhDanJiaEnd GetSet(ByVal value As Decimal)mjhDanJia = valueEnd SetEnd PropertyPublic Property jhCount As IntegerGetReturn mjhCountEnd GetSet(ByVal value As Integer)mjhCount = valueEnd SetEnd PropertyPublic Property jhJinE As DecimalGetReturn mjhJinEEnd GetSet(ByVal value As Decimal)mjhJinE = valueEnd SetEnd PropertyPublic Property jhJingShouRen As StringGetReturn mjhJinShouRenEnd GetSet(ByVal value As String)mjhJinShouRen = valueEnd SetEnd PropertyPublic Property jhGongYingShang As StringGetReturn mjhGongYingShangEnd GetSet(ByVal value As String)mjhGongYingShang = valueEnd SetEnd PropertyPublic Function GetAllJinHouTable() As DataTableDim conn As SqlConnection = GetConnection()conn.Open()Dim strSql As String = "Select * from jhTable"Dim jhda As New SqlDataAdapter(strSql, conn)Dim jhdt As New DataTablejhda.Fill(jhdt)conn.Close()Return jhdtEnd FunctionPublic Sub Add()Dim conn As SqlConnection = GetConnection()Tryconn.Open()Dim sql As String = _"INSERT INTO jhTable" _& "(票据号,进货日期,货品种类,货品编号,货品名称,单位,进货单价,进货数量,金额,经手人,供应商) " _& "VALUES " _& "(@票据号,@进货日期,@货品种类,@货品编号,@货品名称,@单位,@进货单价,@进货数量,@金额,@经手人,@供应商)"'MsgBox(sql)'检查语法用的Dim cmd As SqlCommand = New SqlCommand(sql, conn)Dim p1 As SqlParameter = New SqlParameter("@票据号", jhID)Dim p2 As SqlParameter = New SqlParameter("@进货日期", jhDate)Dim p3 As SqlParameter = New SqlParameter("@货品种类", jhZhongLei)Dim p4 As SqlParameter = New SqlParameter("@货品编号", jhHPID)Dim p5 As SqlParameter = New SqlParameter("@货品名称", jhMingCheng)Dim p6 As SqlParameter = New SqlParameter("@单位", jhDanWei)Dim p7 As SqlParameter = New SqlParameter("@进货单价", jhDanJia)Dim p8 As SqlParameter = New SqlParameter("@进货数量", jhCount)Dim p9 As SqlParameter = New SqlParameter("@金额", jhJinE)Dim p10 As SqlParameter = New SqlParameter("@经手人", jhJingShouRen)Dim p11 As SqlParameter = New SqlParameter("@供应商", jhGongYingShang)cmd.Parameters.Add(p1)cmd.Parameters.Add(p2)cmd.Parameters.Add(p3)cmd.Parameters.Add(p4)cmd.Parameters.Add(p5)cmd.Parameters.Add(p6)cmd.Parameters.Add(p7)cmd.Parameters.Add(p8)cmd.Parameters.Add(p9)cmd.Parameters.Add(p10)cmd.Parameters.Add(p11)cmd.ExecuteNonQuery()Finallyconn.Close()End TryEnd Sub'更新Public Sub Update()Dim conn As SqlConnection = GetConnection()Tryconn.Open()Dim strsql As String = _"UPDATE jhTable " _& "SET 票据号=@票据号,进货日期=@进货日期,货品种类=@货品种类," _& "货品编号=@货品编号,货品名称=@货品名称,单位=@单位,进货单价=@进货单价," _& "进货数量=@进货数量,金额=@金额,经手人=@经手人,供应商=@供应商 " _& "WHERE 票据号='" + jhID + "'"MsgBox(strsql)Dim cmd As SqlCommand = New SqlCommand(strsql, conn)Dim p1 As SqlParameter = New SqlParameter("@票据号", jhID)Dim p2 As SqlParameter = New SqlParameter("@进货日期", jhDate)Dim p3 As SqlParameter = New SqlParameter("@货品种类", jhZhongLei)Dim p4 As SqlParameter = New SqlParameter("@货品编号", jhHPID)Dim p5 As SqlParameter = New SqlParameter("@货品名称", jhMingCheng)Dim p6 As SqlParameter = New SqlParameter("@单位", jhDanWei)Dim p7 As SqlParameter = New SqlParameter("@进货单价", jhDanJia)Dim p8 As SqlParameter = New SqlParameter("@进货数量", jhCount)Dim p9 As SqlParameter = New SqlParameter("@金额", jhJinE)Dim p10 As SqlParameter = New SqlParameter("@经手人", jhJingShouRen)Dim p11 As SqlParameter = New SqlParameter("@供应商", jhGongYingShang)cmd.Parameters.Add(p1)cmd.Parameters.Add(p2)cmd.Parameters.Add(p3)cmd.Parameters.Add(p4)cmd.Parameters.Add(p5)cmd.Parameters.Add(p6)cmd.Parameters.Add(p7)cmd.Parameters.Add(p8)cmd.Parameters.Add(p9)cmd.Parameters.Add(p10)cmd.Parameters.Add(p11)cmd.ExecuteNonQuery()Catch ex As ExceptionFinallyconn.Close()End TryEnd Sub'删除Public Sub Delete(ByVal jhid As String)MsgBox(jhid)Dim conn As SqlConnection = GetConnection()Tryconn.Open()Dim sql As String = String.Format("DELETE FROM jhTable WHERE 票据号='{0}'", jhid)Dim cmd As SqlCommand = New SqlCommand(sql, conn)cmd.ExecuteNonQuery()Catch ex As ExceptionFinallyconn.Close()End TryEnd SubEnd Class'价格类Public Class jiagePrivate mjgZhongLei As StringPrivate mjgMingCheng As StringPrivate mjgBianHao As StringPrivate mjgJinJia As DecimalPrivate mjgShoujia As DecimalPublic Property jgZhongLei As StringGetReturn mjgZhongLeiEnd GetSet(ByVal value As String)mjgZhongLei = valueEnd SetEnd PropertyPublic Property jgMingCheng As StringGetReturn mjgMingChengEnd GetSet(ByVal value As String)mjgMingCheng = valueEnd SetEnd PropertyPublic Property jgBianHao As StringGetReturn mjgBianHaoEnd GetSet(ByVal value As String)mjgBianHao = valueEnd SetEnd PropertyPublic Property jgJinJia As DecimalGetReturn mjgJinJiaEnd GetSet(ByVal value As Decimal)mjgJinJia = valueEnd SetEnd PropertyPublic Property jgShoujia As DecimalGetReturn mjgShoujiaEnd GetSet(ByVal value As Decimal)mjgShoujia = valueEnd SetEnd PropertyPublic Function GetAllJiaGeTable() As DataTableDim conn As SqlConnection = GetConnection()conn.Open()Dim strSql As String = "Select * from jgTable"Dim da As New SqlDataAdapter(strSql, conn)Dim dt As New DataTableda.Fill(dt)conn.Close()Return dtEnd FunctionPublic Function GetZhongLei() As DataTableDim conn As SqlConnection = GetConnection()conn.Open()Dim strSql As String = "Select 货品种类  from jhTable"Dim da As New SqlDataAdapter(strSql, conn)Dim dt As New DataTableda.Fill(dt)conn.Close()Return dtEnd FunctionPublic Function GetByZhongLei(ByVal zhl As String) As DataTableDim conn As SqlConnection = GetConnection()conn.Open()Dim strSql As String = "Select 货品名称,货品编号,进货单价 " _& "From jhTable " _& "Where 货品种类 like '%{0}%'"strSql = String.Format(strSql, zhl)Dim da As New SqlDataAdapter(strSql, conn)Dim dt As New DataTableda.Fill(dt)Return dtconn.Close()End FunctionPublic Sub Add()Dim conn As SqlConnection = GetConnection()Tryconn.Open()Dim sql As String = _"INSERT INTO jgTable" _& "(货品种类,货品名称,货品编号,进价,售价) " _& "VALUES " _& "(@货品种类,@货品名称,@货品编号,@进价,@售价)"'MsgBox(sql)'检查语法用的Dim cmd As SqlCommand = New SqlCommand(sql, conn)Dim p1 As SqlParameter = New SqlParameter("@货品种类", jgZhongLei)Dim p2 As SqlParameter = New SqlParameter("@货品名称", jgMingCheng)Dim p3 As SqlParameter = New SqlParameter("@货品编号", jgBianHao)Dim p4 As SqlParameter = New SqlParameter("@进价", jgJinJia)Dim p5 As SqlParameter = New SqlParameter("@售价", jgShoujia)cmd.Parameters.Add(p1)cmd.Parameters.Add(p2)cmd.Parameters.Add(p3)cmd.Parameters.Add(p4)cmd.Parameters.Add(p5)cmd.ExecuteNonQuery()Finallyconn.Close()End TryEnd Sub'更新Public Sub Update()Dim conn As SqlConnection = GetConnection()Tryconn.Open()Dim strsql As String = _"UPDATE jgTable " _& "SET 货品种类=@货品种类,货品编号=@货品编号,货品名称=@货品名称,进价=@进价,售价=@售价 " _& "WHERE 货品编号='" + jgBianHao + "'"'MsgBox(strsql)Dim cmd As SqlCommand = New SqlCommand(strsql, conn)Dim p1 As SqlParameter = New SqlParameter("@货品种类", jgZhongLei)Dim p2 As SqlParameter = New SqlParameter("@货品编号", jgBianHao)Dim p3 As SqlParameter = New SqlParameter("@货品名称", jgMingCheng)Dim p4 As SqlParameter = New SqlParameter("@进价", jgJinJia)Dim p5 As SqlParameter = New SqlParameter("@售价", jgShoujia)cmd.Parameters.Add(p1)cmd.Parameters.Add(p2)cmd.Parameters.Add(p3)cmd.Parameters.Add(p4)cmd.Parameters.Add(p5)cmd.ExecuteNonQuery()Catch ex As ExceptionFinallyconn.Close()End TryEnd Sub'删除Public Sub Delete(ByVal bianhao As String)Dim conn As SqlConnection = GetConnection()Tryconn.Open()Dim sql As String = String.Format("DELETE FROM jgTable WHERE 货品编号='{0}'", bianhao)Dim cmd As SqlCommand = New SqlCommand(sql, conn)cmd.ExecuteNonQuery()Catch ex As ExceptionFinallyconn.Close()End TryEnd SubEnd Class
End Namespace

客户端

Imports System.Data.SqlClient
Imports 超市管理系统.tableclass
Public Class FrmPriceDim flag As String = ""Dim newjiage As New jiageDim jgdt As DataTable'使用dataview定制从数据库返回的、存储在DataSet中的记录的视图Dim jgDataView As DataView'CurrencyManager用于控制绑定数据Dim bmdata As CurrencyManager'数据填充并更新显示过程Private Sub FillDataSetAndView()jgdt = New DataTablejgdt = newjiage.GetAllJiaGeTable'将表填入dataviewjgDataView = New DataView(jgdt)'将DataView变量绑定控件，能记录位置bmdata = Me.BindingContext(jgDataView)End Sub'获得数据库最新数据，并填充DataView对象Private Sub BindFields()cmbZhongLei.DataBindings.Clear()cmbMingCheng.DataBindings.Clear()cmbBianHao.DataBindings.Clear()txtJinJia.DataBindings.Clear()txtShouJia.DataBindings.Clear()'Add三个参数：控件属性，（DataSet,Datatable,DataView）,数据字段cmbZhongLei.DataBindings.Add("text", jgDataView, "货品种类")cmbMingCheng.DataBindings.Add("text", jgDataView, "货品名称")cmbBianHao.DataBindings.Add("text", jgDataView, "货品编号")txtJinJia.DataBindings.Add("text", jgDataView, "进价")txtShouJia.DataBindings.Add("text", jgDataView, "售价")End Sub'设置控件可用Private Sub SetControlEnable()cmbZhongLei.Enabled = TruecmbMingCheng.Enabled = TruecmbBianHao.Enabled = TruetxtJinJia.Enabled = TruetxtShouJia.Enabled = TrueEnd Sub'设置控件不可用Private Sub SetControlDisable()cmbZhongLei.Enabled = FalsecmbMingCheng.Enabled = FalsecmbBianHao.Enabled = FalsetxtJinJia.Enabled = FalsetxtShouJia.Enabled = FalseEnd SubPrivate Sub FrmPrice_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.LoadSetControlDisable()newjiage = New jiageDim zhldt As New DataTablezhldt = newjiage.GetZhongLeiFor Each row As DataRow In zhldt.RowscmbZhongLei.Items.Add(row.Item(0))NextFillDataSetAndView()BindFields()DataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCellsDataGridView1.DataSource = jgdtEnd SubPrivate Sub btnNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNew.ClickSetControlEnable()flag = "add"'bmdata.EndCurrentEdit()'bmdata.AddNew()End SubPrivate Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Clicknewjiage = New jiagenewjiage.jgZhongLei = cmbZhongLei.Textnewjiage.jgMingCheng = cmbMingCheng.Textnewjiage.jgBianHao = cmbBianHao.Textnewjiage.jgJinJia = txtJinJia.Textnewjiage.jgShoujia = txtShouJia.TextIf flag = "add" Thennewjiage.Add()ElseIf flag = "modify" Thennewjiage.Update()ElseSetControlDisable()Exit SubEnd Ifflag = ""FillDataSetAndView()BindFields()DataGridView1.DataSource = jgdtSetControlDisable()End SubPrivate Sub btnModify_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnModify.Clickflag = "modify"SetControlEnable()cmbBianHao.Focus()End SubPrivate Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.ClickIf MessageBox.Show("确定删除吗？", "提示信息", MessageBoxButtons.YesNo, MessageBoxIcon.Stop) = DialogResult.Yes Thennewjiage = New jiageIf bmdata.Count > 0 Thennewjiage.Delete(cmbBianHao.Text)jgdt.Rows(bmdata.Position).Delete()End IfEnd IfFillDataSetAndView()BindFields()DataGridView1.DataSource = jgdtEnd SubPrivate Sub btnExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExit.ClickMe.Close()End SubPrivate Sub DataGridView1_CellClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellClickDim index As Integer = e.RowIndexbmdata.Position = indexBindFields()cmbZhongLei.Text = DataGridView1.Rows(index).Cells(0).ValuecmbMingCheng.Text = DataGridView1.Rows(index).Cells(1).ValuecmbBianHao.Text = DataGridView1.Rows(index).Cells(2).ValuetxtJinJia.Text = DataGridView1.Rows(index).Cells(3).ValuetxtShouJia.Text = DataGridView1.Rows(index).Cells(4).ValueEnd SubPrivate Sub cmbZhongLei_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmbZhongLei.TextChangednewjiage = New jiageDim dt As New DataTabledt = newjiage.GetByZhongLei(cmbZhongLei.Text)'MsgBox(dt.Rows.Count)cmbMingCheng.Text = dt.Rows(0).Item(0)cmbBianHao.Text = dt.Rows(0).Item(1)txtJinJia.Text = dt.Rows(0).Item(2)End Sub
End Class