usercontrol1.ctl

极易使用的SQL数据库操作控件 Ver 1.0 一个很容易使用的SQL服务器操作控件

VERSION 5.00
Begin VB.UserControl sqlSDBC 
   BackColor       =   &H00000000&
   ClientHeight    =   420
   ClientLeft      =   0
   ClientTop       =   0
   ClientWidth     =   420
   InvisibleAtRuntime=   -1  'True
   Palette         =   "UserControl1.ctx":0000
   Picture         =   "UserControl1.ctx":3556
   ScaleHeight     =   28
   ScaleMode       =   3  'Pixel
   ScaleWidth      =   28
   ToolboxBitmap   =   "UserControl1.ctx":3B98
   Begin VB.Image Image1 
      Height          =   915
      Left            =   -4680
      Picture         =   "UserControl1.ctx":3EAA
      Top             =   -2880
      Width           =   1740
   End
End
Attribute VB_Name = "sqlSDBC"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = True
'****************************************************************************
' ：） 人人为我，我为人人 ：）
'枕善居汉化收藏整理
'发布日期：06/06/28
'描    述：SQL数据库操作控件示例 Ver 1.0
'网    站：http://www.mndsoft.com/
'e-mail  ：[e-mail]   最新的邮箱,如果您有新的好的代码别忘记给枕善居哦
'OICQ    ：88382850
'****************************************************************************

Public Enum Way
UseLike = 1
UseEquel = 2
End Enum

Public Enum MoveWay
MoveNext = 1
MoveBack = 2
MoveFirst = 3
MoveLast = 4
End Enum

Dim SQLS As New SQLDMO.SQLServer
Dim SQLS3 As New SQLDMO.SQLServer
Dim RecordsetT As New ADODB.Recordset

Dim DatabaseT As New ADODB.Connection
Dim MStream As New ADODB.Stream

Dim ConnectionString As String
Dim ErrorNumber
Dim ErrorDescription
Dim User As String, Pass As String, SrvName As String, DBName As String, SQLSta As String

'连接到数据库
Public Function OpenConnection(Username As String, Password As String, ServerName As String, DatabaseNameIs As String)
On Error GoTo BestHandler

ConnectionString = "Server=" & ServerName & ";Provider=SQLOLEDB;UID=" & Username & ";PWD=" & Password & ";database=" & DatabaseNameIs & ";"

DatabaseT.Open ConnectionString

User = Username
Pass = Password
SrvName = ServerName
DBName = DatabaseNameIs   'DatabaseTName

BestHandler:
ErrorNumber = Err.Number
ErrorDescription = Err.Description

End Function

'查找记录
Public Function FindRecord(ColName As String, ByVal FindWay As Way, Text As String)
On Error GoTo BestHandler

Text = Replace(Text, "'", "''")

If FindWay = UseEquel Then
RecordsetT.Find "[" & ColName & "]" & " ='" & Text & "'", 0, adSearchForward, 1

Else

RecordsetT.Find "[" & ColName & "]" & " Like '" & Text & "%'", 0, adSearchForward, 1
End If
BestHandler:
ErrorNumber = Err.Number
ErrorDescription = Err.Description

End Function

'改变字段数据,类似update!
Public Function ChangeFieldData(FieldIndexOrName, NewData)
On Error GoTo BestHandler
RecordsetT.Update FieldIndexOrName, NewData
BestHandler:
ErrorNumber = Err.Number
ErrorDescription = Err.Description

End Function

'添加记录
Public Function AddNewRecord()
On Error GoTo BestHandler
RecordsetT.AddNew
BestHandler:
ErrorNumber = Err.Number
ErrorDescription = Err.Description

End Function

'取消操作，事务回滚
Public Function CancelOperation()
On Error GoTo BestHandler
RecordsetT.CancelUpdate
RecordsetT.Cancel
RecordsetT.CancelBatch adAffectCurrent
RecordsetT.Requery -1
BestHandler:
ErrorNumber = Err.Number
ErrorDescription = Err.Description
End Function

'删除记录
Public Function DeleteRecord()
On Error GoTo BestHandler
RecordsetT.Delete adAffectCurrent
BestHandler:
ErrorNumber = Err.Number
ErrorDescription = Err.Description

End Function

'移动记录指针，如上一条，下一条，首条，末条等
Public Function MoveRecord(ByVal Move As MoveWay)
On Error GoTo BestHandler
If Move = MoveFirst Then
RecordsetT.MoveFirst
ElseIf Move = MoveLast Then
RecordsetT.MoveLast
ElseIf Move = MoveNext Then
RecordsetT.MoveNext
ElseIf Move = MoveBack Then
RecordsetT.MovePrevious
End If
BestHandler:
ErrorNumber = Err.Number
ErrorDescription = Err.Description

End Function

'打开记录集，先要打开连接
Public Function OpenRecordset(SQLStatment As String)
On Error GoTo BestHandler

RecordsetT.Open SQLStatment, DatabaseT, adOpenKeyset, adLockOptimistic

SQLSta = SQLStatment

BestHandler:
ErrorNumber = Err.Number
ErrorDescription = Err.Description

End Function

'刷新，一般时删除或者添加记录后
Public Function Refresh()
On Error GoTo BestHandler
RecordsetT.Requery -1
BestHandler:
ErrorNumber = Err.Number
ErrorDescription = Err.Description

End Function

'保存文件到数据库
Public Function SaveFileToDB(FilePath As String, FieldIndexOrName)
On Error GoTo BestHandler
       
       MStream.Type = adTypeBinary
              
       MStream.Open
       
       MStream.LoadFromFile (FilePath)
       
       RecordsetT.Fields(FieldIndexOrName).Value = MStream.Read
       
       RecordsetT.Update
       
     MStream.Close
     
       Refresh
       
BestHandler:
ErrorNumber = Err.Number
ErrorDescription = Err.Description

End Function

'保存数据库到文件
Public Sub SaveDBToFile(FieldIndexOrName, FilePath As String)
On Error GoTo BestHandler

MStream.Type = adTypeBinary

MStream.Open

MStream.Write RecordsetT.Fields(FieldIndexOrName).Value

MStream.SaveToFile FilePath, adSaveCreateOverWrite

MStream.Close

BestHandler:
ErrorNumber = Err.Number
ErrorDescription = Err.Description

End Sub

'加载图片到对象
Public Sub LoadDBPicToObject(FieldIndexOrName, ObjectName As Object)
On Error GoTo BestHandler

'检查是否打开
If MStream.State = adStateOpen Then MStream.Close
MStream.Type = adTypeBinary

MStream.Open

MStream.Write RecordsetT.Fields(FieldIndexOrName).Value

MStream.SaveToFile Environ$("TEMP") & "\temp", adSaveCreateOverWrite

'加载临时文件
ObjectName.Picture = LoadPicture(Environ$("TEMP") & "\temp")

MStream.Close
BestHandler:
ErrorNumber = Err.Number
ErrorDescription = Err.Description

End Sub

Private Sub UserControl_Resize()
On Error GoTo BestHandler
Height = 420
Width = 420
BestHandler:
ErrorNumber = Err.Number
ErrorDescription = Err.Description

End Sub

'关闭记录集
Public Function CloseRS()
On Error Resume Next

If RecordsetT.State = adStateOpen Then

RecordsetT.Close

End If

If DatabaseT.State = adStateOpen Then

DatabaseT.Close

End If


ErrorNumber = Err.Number
ErrorDescription = Err.Description

End Function

'获取服务器状态
Public Property Get SQLServerStatus(SQLServerName) As Variant
On Error GoTo BestHandler
SQLS3.Name = SQLServerName
If SQLS3.Status = SQLDMOSvc_Running Then
SQLServerStatus = "Running"
ElseIf SQLS3.Status = SQLDMOSvc_Paused Then
SQLServerStatus = "Paused"
ElseIf SQLS3.Status = SQLDMOSvc_Stopped Then
SQLServerStatus = "Stopped"
ElseIf SQLS3.Status = SQLDMOSvc_Unknown Then
SQLServerStatus = "Unknown"
ElseIf SQLS3.Status = SQLDMOSvc_Continuing Then
SQLServerStatus = "Continuing"
ElseIf SQLS3.Status = SQLDMOSvc_Pausing Then
SQLServerStatus = "Pausing"
ElseIf SQLS3.Status = SQLDMOSvc_Starting Then
SQLServerStatus = "Starting"
ElseIf SQLS3.Status = SQLDMOSvc_Stopping Then
SQLServerStatus = "Stopping"
End If
BestHandler:
ErrorNumber = Err.Number
ErrorDescription = Err.Description

End Property

'检查记录游标位置
Public Property Get IfBOForEOF() As Boolean
On Error GoTo BestHandler
If RecordsetT.BOF = True Or RecordsetT.EOF = True Then
IfBOForEOF = True
End If
BestHandler:
ErrorNumber = Err.Number
ErrorDescription = Err.Description

End Property

'获取字段数据
Public Property Get GetFieldData(FieldIndexOrName) As Variant
On Error GoTo BestHandler
GetFieldData = RecordsetT.Fields(FieldIndexOrName)
BestHandler:
ErrorNumber = Err.Number