Мой Kbyte.Ru
Рассылка Kbyte.Ru
Группы на Kbyte.Ru
Партнеры Kbyte.Ru
Реклама
Сделано руками
Сделано руками
> Исходные коды - Mulish Mehdi -

Visual Basic .NET - Базы данных

Все примеры / Базы данных

Класс доступа к данным SQL Server

Автор: Mulish Mehdi | добавлено: 05.03.2010, 19:40 | просмотров: 2563 (2+) | комментариев: 0 | рейтинг: *x8
Довольно неплохой класс (но не лучший ;) ..) упрощающий доступ к базам данных SQL Server.

Код

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.IO

Public NotInheritable Class SqlDataProvider

#Region " Declarations "

 Shared SqlConn As SqlConnection = Nothing
 Shared SqlComm As SqlCommand = Nothing
 Shared DataAdapter As SqlDataAdapter = Nothing
 Shared DataReader As SqlDataReader = Nothing
 Shared StrReader As StreamReader = Nothing
 Shared _LastError As Exception

#End Region

#Region " Enumerations "

 Public Enum ExecuteMode
  Insert = 0
  Update = 1
 End Enum

#End Region

#Region " Properties "

 Public Shared ReadOnly Property GetLastError() As Exception
  Get
   Return _LastError
  End Get
 End Property

#End Region

#Region " Private Methods "

 Private Shared Function AddParameters(ByVal CommandObject As SqlCommand, ByVal CommandParameters() As SqlParameter) As String
  Dim ReturnValue As String = String.Empty
  Dim i As Integer
  If Not CommandParameters Is Nothing Then
   For i = 0 To CommandParameters.Length - 1
    CommandObject.Parameters.Add(CommandParameters(i))
    If CommandParameters(i).Direction = ParameterDirection.Output Then
     ReturnValue = CommandParameters(i).ParameterName
    End If
   Next
  End If
   Return ReturnValue
 End Function

 Private Shared Sub AddParameters(ByVal ConnectionObject As SqlConnection, ByVal CommandObject As SqlCommand, ByVal ParameterValues() As Object)
  ConnectionObject.Open()
  SqlCommandBuilder.DeriveParameters(CommandObject)
  ConnectionObject.Close()
  For i As Integer = 1 To CommandObject.Parameters.Count - 1
   CommandObject.Parameters(i).Value = ParameterValues(i - 1)
  Next
 End Sub

#End Region

#Region " ExecuteQuery Method "

 Public Shared Function ExecuteQuery(ByVal ConnectionString As String, ByVal CommandText As String, ByVal CommandType As CommandType, _
          ByVal CommandParameters() As SqlParameter) As Object
  Dim ReturnValue As Object = Nothing
  Try
   SqlConn = New SqlConnection(ConnectionString)
   SqlComm = New SqlCommand(CommandText, SqlConn)
   SqlComm.CommandType = CommandType
   Dim OutputParameter As String = SqlDataProvider.AddParameters(SqlComm, CommandParameters)
   If Not (SqlConn.State = ConnectionState.Open) Then SqlConn.Open()
   ReturnValue = SqlComm.ExecuteNonQuery()
   If Not (OutputParameter = String.Empty) Then ReturnValue = SqlComm.Parameters(OutputParameter).Value
  Catch ex As Exception
   _LastError = ex
  Finally
   If Not (SqlConn.State = ConnectionState.Closed) Then SqlConn.Close()
  End Try
  Return ReturnValue
 End Function

 Public Shared Function ExecuteQuery(ByVal ConnectionString As String, ByVal CommandText As String, ByVal ParamArray ParameterValues() As Object) As Object
  Dim Return_Value As Object = Nothing
  Try
   SqlConn = New SqlConnection(ConnectionString)
   SqlComm = New SqlCommand(CommandText, SqlConn)
   SqlComm.CommandType = CommandType.StoredProcedure
   AddParameters(SqlConn, SqlComm, ParameterValues)
   SqlConn.Open()
   SqlComm.ExecuteNonQuery()
   Return_Value = SqlComm.Parameters(0).Value
  Catch ex As Exception
   _LastError = ex
  Finally
   SqlConn.Close()
  End Try
  Return Return_Value
 End Function

#End Region

#Region " ExecuteScript Method "

 Public Shared Sub ExecuteScript(ByVal ConnectionString As String, ByVal ScriptFile As String)
  Dim SqlScript As String = String.Empty
  Try
   StrReader = File.OpenText(ScriptFile)
   SqlScript = StrReader.ReadToEnd()
   SqlConn = New SqlConnection(ConnectionString)
   SqlComm = New SqlCommand(SqlScript.Replace("Go", "").Replace("go", "").Replace("gO", "").Replace("GO", ""), SqlConn)
   If Not (SqlConn.State = ConnectionState.Open) Then SqlConn.Open()
   SqlComm.ExecuteNonQuery()
  Catch ex As Exception
   _LastError = ex
  Finally
   StrReader.Close()
   If Not (SqlConn.State = ConnectionState.Closed) Then SqlConn.Close()
  End Try
 End Sub

#End Region

#Region " ExecuteReader Method "

 Public Shared Function ExecuteReader(ByVal ConnectionString As String, ByVal CommandText As String, ByVal CommandType As CommandType, _
           ByVal CommandParameters() As SqlParameter, ByVal ParamArray Columns() As String) As Object()
  Dim ReturnValue(0 To Columns.Length - 1) As Object
  Try
   SqlConn = New SqlConnection(ConnectionString)
   SqlComm = New SqlCommand(CommandText, SqlConn)
   SqlComm.CommandType = CommandType
   SqlDataProvider.AddParameters(SqlComm, CommandParameters)
   If Not SqlConn.State = ConnectionState.Open Then SqlConn.Open()
   DataReader = SqlComm.ExecuteReader()
   Dim index As Integer
   While DataReader.Read()
    ReturnValue(index) = DataReader(Columns(index))
    index += 1
   End While
  Catch ex As Exception
   _LastError = ex
  Finally
   DataReader.Close()
   If Not SqlConn.State = ConnectionState.Closed Then SqlConn.Close()
  End Try
  Return ReturnValue
 End Function

#End Region

#Region " ExecuteDataSource Method "

 Public Shared Function ExecuteDataSource(ByVal ConnectionString As String, ByVal ExecuteMode As ExecuteMode, ByVal CommandText As String, _
            ByVal CommandType As CommandType, ByVal CommandParameters() As SqlParameter, ByVal TableName As String, _
            ByVal Dataset As DataSet) As Integer
  Dim ReturnValue As Integer
  Try
   SqlConn = New SqlConnection(ConnectionString)
   SqlComm = New SqlCommand(CommandText, SqlConn)
   SqlComm.CommandType = CommandType
   SqlDataProvider.AddParameters(SqlComm, CommandParameters)
   DataAdapter = New SqlDataAdapter()
   Select Case ExecuteMode
    Case SqlDataProvider.ExecuteMode.Insert
     DataAdapter.InsertCommand = SqlComm
    Case SqlDataProvider.ExecuteMode.Update
     DataAdapter.UpdateCommand = SqlComm
   End Select
   ReturnValue = DataAdapter.Update(Dataset, TableName)
  Catch ex As Exception
   _LastError = ex
  End Try
  Return ReturnValue
 End Function

#End Region

#Region " FillDataSet Method "

 Public Shared Function FillDataSet(ByVal ConnectionString As String, ByVal CommandText As String, ByVal CommandType As CommandType, _
          ByVal CommandParameters() As SqlParameter) As DataSet
  Dim ReturnDataSet As New DataSet
  Try
   SqlConn = New SqlConnection(ConnectionString)
   SqlComm = New SqlCommand(CommandText, SqlConn)
   SqlComm.CommandType = CommandType
   SqlDataProvider.AddParameters(SqlComm, CommandParameters)
   DataAdapter = New SqlDataAdapter(SqlComm)
   DataAdapter.Fill(ReturnDataSet)
  Catch ex As Exception
   _LastError = ex
  End Try
  Return ReturnDataSet
 End Function

#End Region

End Class
Об авторе

Mulish Mehdi

Нет информации об авторе...
Mulish Mehdi
Последние комментарии (всего: 0)

Добавлять комментарии могут только зарегистрированные пользователи сайта.
Если у Вас уже есть учетная запись на Kbyte.Ru, пройдите процедуру авторизации OpenID.
Если Вы еще не зарегистрированы на Kbyte.Ru - зарегистрируйтесь.


Нет комментариев...

Авторизация
 
OpenID
Зарегистрируйся и получи 10% скидку на добавление своего сайта в каталоги! Подробнее »
Поиск по сайту
Реклама
Счетчики