كود للتعامل مع قواعد البيانات بكل سهولة

نبذة:

هذا الـNamespace يستفاد منه في الإرتباط بقواعد البيانات وعمل جميع العمليات عليها وهو ينقسم الى 2Classes الإول هو OleDBCls وهذا يمنكن الإرتباط مع غالبية قواعد البيانات والآخر هو SqlCleintCls ويستفاد منه في الإرتباط بقاعدة بيانات SQL Server وقد تمت برمجنه بالكامل بلغة Visual Basic .NET

كيفية الإستخدام:

-إنسخ الكود وضعه في ملف جديد في مشروعك.

-أكتب مايلي:

      
  'Visual Basic .NET
'================ 
Import db
Class T1
Sub Test()
Dim dbObj as new SqlClientCls()
dbObj.Connection="Put Connection String Here"
dbObj.strSQL= "insert into Customers(Name) values ('Khalid')"
dbObj.SQLDO(dbObj.strSQL,db.ExecuteNonQuery)
End Sub
End Class

=============================================================
//Visual C#.NET
//============ 
using db;
class T1
{
public void Test()
{
SqlClientCls dbObj = new SqlClientCls();
dbObj.Connection="Put Connection String Here";
dbObj.strSQL= "insert into Customers(Name) values ('Khalid')";
dbObj.SQLDO(dbObj.strSQL,db.ExecuteNonQuery);
} 
}

Code:

'########################################  
'########Contact me:####### #############
'######Email:w3_webdeveloper@yahoo.com###
'######Please Dont Remove this Header####
'######################################## 
Namespace db

Public Class OLEDBCLS
Public ds As DataSet
Public dt As DataTable
Public strSQL As String
Private SQLstr As String
Public temp As String
Private _ConnectionString As String
Public dr As OleDb.OleDbDataReader
Private DBcommand As OleDb.OleDbCommand
Private conStr, Str As String
Private DBconnection As OleDb.OleDbConnection
Private DBReader As OleDb.OleDbDataReader
Private DBdataSet As DataSet
Private DBadapter As OleDb.OleDbDataAdapter
Private DBdataView As DataView
Public Property ConnectionString() As String
Get
Return _ConnectionString
End Get
Set(ByVal Value As String)
_ConnectionString = Value
End Set
End Property
Sub New() 

End Sub
Sub New(ByVal ConnectString As String)
ConnectionString = ConnectString
End Sub

Function DataSet_Select(ByVal SQL As String, Optional ByVal table As String = "") As DataSet
Dim connection As New OleDb.OleDbConnection(ConnectionString)
If connection.State <> ConnectionState.Open Then
connection.Open()
End If
Dim strSQL As String
Dim c As Integer
strSQL = SQL
Dim da As New OleDb.OleDbDataAdapter(strSQL, connection)
DBdataSet = New DataSet
If table <> "" Then
da.Fill(DBdataSet, table)
Else
da.Fill(DBdataSet)
End If
connection.Close()
Return DBdataSet
End Function
Enum sqlType
ExecuteNonQuery = 0
ExecuteScalar = 1
End Enum

Public Function SQL_DO(ByVal SQL As String, ByVal type As sqlType, Optional ByVal _CommandType As CommandType = CommandType.Text) As String 'Implements IDbClass.SQL_DO
Dim result As String
DBconnection = New OleDb.OleDbConnection(ConnectionString)
DBcommand = New OleDb.OleDbCommand(SQL, DBconnection)
DBcommand.CommandType = _CommandType

If DBconnection.State = ConnectionState.Closed Then
DBconnection.Open()
End If
Select Case (type)
Case 0 'ExecuteNonQuery
DBcommand.ExecuteNonQuery()
DBconnection.Close()
Return "non"
Case 1 'ExecuteScalar
If DBcommand.ExecuteScalar() Is DBNull.Value Then
DBconnection.Close()
Return ""
Else
result = DBcommand.ExecuteScalar()
DBconnection.Close()
Return result
End If
End Select
End Function

Public Function Reader(ByVal SQL As String, Optional ByVal _CommandType As CommandType = CommandType.Text) As OleDb.OleDbDataReader
DBconnection = New OleDb.OleDbConnection(ConnectionString)
DBcommand = New OleDb.OleDbCommand(SQL, DBconnection)
DBcommand.CommandType = _CommandType
If DBconnection.State = ConnectionState.Closed Then
DBconnection.Open()
End If
DBReader = DBcommand.ExecuteReader()
Return DBReader
End Function
End Class

Public Class SQLCLIENTCLS
Public ds As DataSet
Public dt As DataTable
Public strSQL As String
Private SQLstr As String
Public temp As String
Private _ConnectionString As String
Public dr As SqlClient.SqlDataReader
Private DBcommand As SqlClient.SqlCommand
Private conStr, Str As String
Private DBconnection As SqlClient.SqlConnection
Private DBReader As SqlClient.SqlDataReader
Private DBdataSet As DataSet
Private DBadapter As SqlClient.SqlDataAdapter
Private DBdataView As DataView
Private Param As SqlClient.SqlParameter
Public Property ConnectionString() As String
Get
Return _ConnectionString
End Get
Set(ByVal Value As String)
_ConnectionString = Value
End Set
End Property
Sub New(ByVal strConnection As String)
If strConnection <> "" Then
ConnectionString = strConnection
Else
Throw New Exception("Empty Connection String is not Vailied")
End If
End Sub
Sub New()

End Sub
Sub New(ByVal workstationid As String, ByVal database As String, Optional ByVal userid As String = "sa", Optional ByVal password As String = "")
ConnectionString = "workstation id=" + workstationid + ";packet size=4096;user id=" + userid + ";password=" + password + ";initial catalog=" + database + ";persist security info=False"
End Sub
Function Dataset_Select(ByVal SQL As String, Optional ByVal table As String = "", Optional ByVal _CommandType As CommandType = CommandType.Text) As DataSet
Dim connection As New SqlClient.SqlConnection(ConnectionString)
If connection.State <> ConnectionState.Open Then
connection.Open()
End If
Dim strSQL As String
Dim c As Integer
strSQL = SQL
DBcommand = New SqlClient.SqlCommand(SQL)
DBcommand.CommandType = _CommandType
DBcommand.Connection = connection
Dim da As New SqlClient.SqlDataAdapter(DBcommand)
DBdataSet = New DataSet
If table <> "" Then
da.Fill(DBdataSet, table)
Else
da.Fill(DBdataSet)
End If
connection.Close()
Return DBdataSet
End Function
Enum sqlType
ExecuteNonQuery = 0
ExecuteScalar = 1
ExecuteReader = 2
End Enum

Public Function SQL_DO(ByVal SQL As String, ByVal type As sqlType, Optional ByVal _CommandType As CommandType = CommandType.Text) As String 'Implements IDbClass.SQL_DO
Dim result As String
DBconnection = New SqlClient.SqlConnection(ConnectionString)
DBcommand = New SqlClient.SqlCommand(SQL, DBconnection)
DBcommand.CommandType = _CommandType

If DBconnection.State = ConnectionState.Closed Then
DBconnection.Open()
End If
Select Case (type)
Case 0 'ExecuteNonQuery
DBcommand.ExecuteNonQuery()
DBconnection.Close()
Return "non"
Case 1 'ExecuteScalar
If DBcommand.ExecuteScalar() Is DBNull.Value Then
DBconnection.Close()
Return ""
Else
result = DBcommand.ExecuteScalar()
DBconnection.Close()
Return result
End If
End Select
End Function
Sub StoredProceder_1st(ByVal StoredProcederName As String)
DBcommand = New SqlClient.SqlCommand(StoredProcederName)
DBcommand.CommandType = CommandType.StoredProcedure
End Sub
Sub AddParameter_3rd(ByVal ParameterName As String, ByVal value As Object, ByVal dbfType As SqlDbType, Optional ByVal Size As Integer = 0)
Select Case Size
Case 0
Param = New SqlClient.SqlParameter(ParameterName, dbfType)
Case Else
Param = New SqlClient.SqlParameter(ParameterName, dbfType, Size)
End Select
Param.Value = value
DBcommand.Parameters.Add(Param)
End Sub
Function ExecuteStoredProceder_4th(ByVal type As sqlType) As Object
DBconnection = New SqlClient.SqlConnection(ConnectionString)
DBcommand.Connection = DBconnection
If DBconnection.State = ConnectionState.Closed Then
DBconnection.Open()
End If
Select Case type
Case sqlType.ExecuteNonQuery
DBcommand.ExecuteNonQuery()
DBcommand.Parameters.Clear()
Case sqlType.ExecuteScalar
If DBcommand.ExecuteScalar() Is DBNull.Value Then
DBconnection.Close()
DBcommand.Parameters.Clear()
Return ""
Else
Dim result As String = DBcommand.ExecuteScalar()
If DBconnection.State = ConnectionState.Open Then
DBconnection.Close()
End If
DBcommand.Parameters.Clear()
Return CType(result, String)
End If
Case sqlType.ExecuteReader
dr = DBcommand.ExecuteReader()
Return dr
End Select
End Function
Function ExecuteStoredProcederDS_4th() As DataSet
DBconnection = New SqlClient.SqlConnection(ConnectionString)
DBdataSet = New DataSet
DBcommand.Connection = DBconnection
If DBconnection.State = ConnectionState.Closed Then
DBconnection.Open()
End If
DBadapter = New SqlClient.SqlDataAdapter(DBcommand)
DBadapter.Fill(DBdataSet)
If DBconnection.State = ConnectionState.Open Then
DBconnection.Close()
End If
Return DBdataSet
End Function

'this sub is required befor adding parameters to the command
Sub ClearCommandParameters_2nd()
DBcommand.Parameters.Clear()
End Sub

Public Function Reader(ByVal SQL As String, Optional ByVal _CommandType As CommandType = CommandType.Text) As SqlClient.SqlDataReader
Str = ConnectionString
DBconnection = New SqlClient.SqlConnection(Str)
DBcommand = New SqlClient.SqlCommand(SQL, DBconnection)
DBcommand.CommandType = _CommandType
If DBconnection.State = ConnectionState.Closed Then
DBconnection.Open()
End If
DBReader = DBcommand.ExecuteReader()
Return DBReader
End Function
 End Class
End Namespace