首页 诗词 字典 板报 句子 名言 友答 励志 学校 网站地图
当前位置: 首页 > 教程频道 > .NET > VB Dotnet >

急求VB.net2005连接SqlServer2000代码及详细说明,该怎么解决

2012-03-18 
急求VB.net2005连接SqlServer2000代码及详细说明膜拜![解决办法]连接字符串www.connectionstrings.comADO.

急求VB.net2005连接SqlServer2000代码及详细说明
膜拜!

[解决办法]
连接字符串www.connectionstrings.com
ADO.NET操作http://msdn.microsoft.com/zh-cn/library/e80y5yhx(VS.80).aspx
[解决办法]
'我给你个很不错的vs2005操作sql server 2000/2005 类
' 添加引用 :项目(P) | 添加引用(R) | .NET | System.Data.SqlClient
Imports system.Data
Imports System.Data.SqlClient
Imports System.ComponentModel

Module sqlDBOperat

'The SQL Server .NET Data Provider是利用SqlConnection类来连接SQL Server7.0或更高版本的数据库,SqlConnection类位于名称空间System.Data.SqlClient下。
'性能方面也有很大的不同:The SQL Server .NET Data Provider的效率最高;The OLE DB .NET Data Provider的效率比较底;The ODBC .NET Data Provider的效率最慢。
Friend sqlDBConn As SqlConnection = New SqlConnection
Friend strConn As String = "data source=localhost;initial catalog=iWealthFount;user id=Architect;password=Architects_Magicwand;"
Friend Cmd As SqlCommand = Nothing
Friend strSQL As String = Nothing
Friend Reader As SqlDataReader = Nothing
Friend sqlDA As SqlDataAdapter = Nothing
Friend sqlDS As DataSet = Nothing

Public Function GetConnect() As SqlClient.SqlConnection

'连接数据库
'窗体加载时要调用本函数进行数据库连接,以后的操作中会多次在有这个连接的前提下进行。最后退出时再DisConnect()

Try
sqlDBConn = New SqlClient.SqlConnection(strConn)

If sqlDBConn.State = ConnectionState.Open Then
sqlDBConn.Close() '先关闭,以防不必要的冲突
sqlDBConn.Open() '重新打开数据库
End If

If sqlDBConn.State <> ConnectionState.Open Then
sqlDBConn.Open()
End If

Debug.Print("sqlConnecte..........OK")
Catch ex As Exception
MessageBox.Show(ex.ToString)
End Try

Return sqlDBConn

End Function
Public Function DisConnect() As Boolean
'关闭数据库
Try
'sqlDBConn = New SqlClient.SqlConnection(strConn)
sqlDBConn.Close()
Debug.Print("sqlDisConnecte.......OK")
Return True

Catch ex As Exception
MsgBox(ex.ToString)
Return False
End Try
End Function

Public Function sqlDataViewer(ByVal strSQL As String, ByVal strTableName As String, ByVal DataGridView As DataGridView) As String
'浏览数据表

Try
sqlDA = New SqlDataAdapter(strSQL, sqlDBConn)
sqlDS = New DataSet()
sqlDA.Fill(sqlDS, strTableName)
DataGridView.DataSource = sqlDS.Tables(strTableName).DefaultView

Debug.Print("数据集生成...........OK ( Rows:" & sqlDS.Tables(strTableName).Rows.Count & ")")

Catch sqlEx As SqlException
MsgBox(sqlEx.Message) 'sqlEx 语句提示可能会给黑客类人带来方便。有点危险。仅供自己正当使用。
Catch ex As Exception
MsgBox("您的非法操作导致数据访问错误!", MsgBoxStyle.Critical, "警告")
End Try

Return "连接过期"

End Function
Public Sub sqlInsDelUpdate(ByVal strSQL As String)
'执行插入、删除或更新
Try
Cmd = New SqlCommand(strSQL, sqlDBConn)
Cmd.ExecuteNonQuery()

Debug.Print("Ins|Del|Update...... OK")

Catch sqlEx As SqlException
MsgBox(sqlEx.Message) 'sqlEx 语句提示可能会给黑客类人带来方便。有点危险。仅供自己正当使用。
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Sub
Public Function GetInt(ByVal strSQL As String) As Integer

'返回一个浮点数值型查询结果

Cmd = New SqlCommand(strSQL, sqlDBConn)

Dim rowsAffected As Integer = 0



Try
rowsAffected = IIf(IsDBNull(Cmd.ExecuteScalar), 0, Cmd.ExecuteScalar)
Catch ex As Exception
Throw New NotSupportedException(ex.Message)
End Try

Return rowsAffected

End Function
Public Function GetSingle(ByVal strSQL As String) As Single

'返回一个浮点数值型查询结果

Cmd = New SqlCommand(strSQL, sqlDBConn)

Dim rowsAffected As Single = 0

Try
rowsAffected = IIf(IsDBNull(Cmd.ExecuteScalar), 0, Cmd.ExecuteScalar)
Catch ex As Exception
Throw New NotSupportedException(ex.Message)
End Try

Return rowsAffected

End Function
Public Function GetString(ByVal strSQL As String) As String

'返回一个字符串型查询结果

Cmd = New SqlCommand(strSQL, sqlDBConn)

Dim rowsAffected As String = ""

Try
rowsAffected = IIf(IsDBNull(Cmd.ExecuteScalar), 0, Cmd.ExecuteScalar)
Catch ex As Exception
Throw New NotSupportedException(ex.Message)
End Try

Return Trim(rowsAffected)

End Function
Public Function GetDateTime(ByVal strSQL As String) As DateTime

'返回一个日期时间型查询结果

Cmd = New SqlCommand(strSQL, sqlDBConn)

Dim rowsAffected As DateTime

Try
rowsAffected = IIf(IsDBNull(Cmd.ExecuteScalar), 0, Cmd.ExecuteScalar)
Catch ex As Exception
Throw New NotSupportedException(ex.Message)
End Try

Return rowsAffected

End Function
End Module

热点排行