scmdb.vb

本系统利用vb.net前台开发工具sql.server做为后台数据库进行开超市进销存管理系统系统

Imports System.Data
Imports System.Data.SqlClient

Public Class SCMDB
    '公用变量，保存ScmSys数据库连接信息
  Public Shared connStr As String = "server=localhost; database=ScmSys; uid=sa; pwd="

  '数据库连接
  Dim m_Connection As SqlConnection

  Private Sub OpenConnection()
    m_Connection = New SqlConnection(connStr)
    m_Connection.Open()
  End Sub

  Private Sub CloseConnection()
    m_Connection.Close()
  End Sub

  Public Function CreateDataTableByKey(ByVal FieldNames As String, ByVal TableName As String, _
        Optional ByVal ConditionStr As String = "", Optional ByVal TabName _
            As String = "myTab") As DataTable
    '此方法的作用是根据传入的字段、数据表等关键信息生成相应的数据表
    Dim myAdapter As SqlDataAdapter
    Dim myDataSet As New DataSet
    Dim SQLcmd As String
    If ConditionStr = "" Then
      SQLcmd = "select " & FieldNames & " from " & TableName
    Else
      SQLcmd = "select " & FieldNames & " from " & TableName & "  where  " & _
          ConditionStr
    End If
    Try
      OpenConnection()
      myAdapter = New SqlDataAdapter(SQLcmd, m_Connection)
      myAdapter.Fill(myDataSet, TabName)
      CloseConnection()
      '返回生成的数据表
      Return myDataSet.Tables(TabName)
    Catch ex As Exception
      '异常报告
      MsgBox("创建数据表发生异常！异常原因：" & ex.Message, _
              MsgBoxStyle.Exclamation)
    End Try
  End Function

  Public Function CreateDataTableBySQL(ByVal SQLCmd As String, Optional ByVal TabName As String = "myTab") As DataTable
    '此方法的作用是根据传入的SQL语句生成相应的数据表
    'Dim myConn As SqlConnection
    Dim myAdapter As SqlDataAdapter
    Dim myDataSet As New DataSet
    Try
      OpenConnection()
      myAdapter = New SqlDataAdapter(SQLCmd, m_Connection)
      myAdapter.Fill(myDataSet, TabName)
      CloseConnection()
      '返回生成的数据表
      Return myDataSet.Tables(TabName)
    Catch ex As Exception
      '异常报告
      MsgBox("创建数据表发生异常！异常原因：" & ex.Message, _
              MsgBoxStyle.Exclamation)
    End Try
  End Function

  Public Function UpdateDataBase(ByVal SQLcmd As String) As String
    '此方法的作用是根据传入的SQL语句更新相应的数据表
    Try
      'Dim myConn As SqlConnection
      Dim myCmd As SqlCommand
      OpenConnection()
      myCmd = New SqlCommand(SQLcmd, m_Connection)
      myCmd.ExecuteNonQuery()
      CloseConnection()
      '更新成功
      Return "Y"
    Catch ex As Exception
      '更新失败，返回失败原因
      Return ex.Message
    End Try
  End Function

End Class