1,216
社区成员
发帖
与我相关
我的任务
分享
Option Explicit Off
Imports System.Data.SqlClient
#Region "数据库基本操作类库"
Public Class PublicClass
#Region "数据库连接类"
Public sqlconn As New SqlClient.SqlConnection
Public Sub open_conn()
'Dim sqlconnstr As String
Try
sqlconn.ConnectionString = ("server=127.0.0.1;integrated security=False;Initial Catalog=YNCSys;User ID=sa;Pwd=adminsa226200")
Catch ex As Exception
Throw ex
End Try
End Sub
#End Region
#Region "select 语句,需要输入T-SQL语句"
Public Function dataSelect(ByVal strsql As String, ByVal strTableName As String) As DataSet
Dim mydataset As New DataSet
Dim mySqlAdapter As SqlClient.SqlDataAdapter
Try
sqlconn.Open()
mySqlAdapter = New SqlClient.SqlDataAdapter(strsql, sqlconn)
mySqlAdapter.Fill(mydataset, strTableName)
sqlconn.Close()
mySqlAdapter = Nothing
Catch ex As Exception
If sqlconn.State = ConnectionState.Open Then
sqlconn.Close()
End If
Throw ex
End Try
Return mydataset
End Function
#End Region
#Region "数据插入操作,参数为insert语句和表名"
Public Sub dataInsert(ByVal strSql As String, ByVal strTableName As String)
Dim mysqlcomm As SqlClient.SqlCommand
Try
sqlconn.Open()
mysqlcomm = New SqlClient.SqlCommand(strSql, sqlconn)
mysqlcomm.ExecuteNonQuery()
sqlconn.Close()
mysqlcomm = Nothing
Catch ex As Exception
If sqlconn.State = ConnectionState.Open Then
sqlconn.Close()
End If
Throw ex
End Try
End Sub
#End Region
#Region "修改数据模块,参数为updata和表名"
Public Sub dataUpdate(ByVal strSql As String, ByVal strTableName As String)
Dim mysqlcomm As New SqlClient.SqlCommand
Try
sqlconn.Open()
mysqlcomm = New SqlClient.SqlCommand(strSql, sqlconn)
mysqlcomm.ExecuteNonQuery()
sqlconn.Close()
mysqlcomm = Nothing
Catch ex As Exception
If sqlconn.State = ConnectionState.Open Then
sqlconn.Close()
End If
Throw ex
End Try
End Sub
#End Region
#Region "删除数据操作,参数为delete和要删除的表名"
Public Sub dataDelete(ByVal strSql As String, ByVal strTableName As String)
Dim mysqlcomm As New SqlClient.SqlCommand
Try
sqlconn.Open()
mysqlcomm = New SqlClient.SqlCommand(strSql, sqlconn)
mysqlcomm.ExecuteNonQuery()
sqlconn.Close()
mysqlcomm = Nothing
Catch ex As Exception
If sqlconn.State = ConnectionState.Open Then
sqlconn.Close()
End If
Throw ex
End Try
End Sub
#End Region
#Region "控件绑定方法,参数为SQL存储过程和DataGridView控件"
Public Sub BDDataToDataGridView(ByVal PRO As String, ByVal DGV As DataGridView)
sqlconn.Open()
Dim sqlcmd As New System.Data.SqlClient.SqlCommand
sqlcmd.Connection = sqlconn
Dim da As New System.Data.SqlClient.SqlDataAdapter()
da.SelectCommand = sqlcmd
da.SelectCommand.CommandType = CommandType.StoredProcedure
da.SelectCommand.CommandText = "select * from '" + PRO.ToString().Trim() + "'" '通过SQL的存储过程绑定数据
Dim ds As New DataSet()
da.SelectCommand.Connection = sqlconn
da.Fill(ds)
DGV.DataSource = ds.Tables(0)
DGV.Refresh()
End Sub
#Region "控件绑定方法,参数为SQL存储过程和ComboBox控件"
Public Sub BDToComboBox(ByVal PRO As String, ByVal ComboBox As ComboBox)
sqlconn.Open()
Dim sqlcmd As New System.Data.SqlClient.SqlCommand
sqlcmd.Connection = sqlconn
Dim da As New System.Data.SqlClient.SqlDataAdapter()
da.SelectCommand = sqlcmd
da.SelectCommand.CommandText = "select * from '" + ComboBox.SelectedText.Trim + "'" '通过SQL的存储过程绑定数据
Dim ds As New DataSet()
da.SelectCommand.Connection = sqlconn
da.Fill(ds)
ComboBox.DataSource = ds.Tables(0)
ComboBox.Refresh()
End Sub
#End Region
#End Region
End Class
#End Region