يه كلاس براي ارتباط با بانك اطلاعاتي در ASP.Net

a_mollaei · Aug 27, 2004

با سلام.

من اين كلاس رو براي ارتباط با DataBase نوشتم. فكر كردم به درد بچه هاي Net. كار مي خوره.

لطفا مشكلاتشو هم برام بگيد و اگه پيشنهادي براي تكميلش داريد هم از ما دريغ نكنيد.

كد كلاس:

کد:

Imports System
Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient

Namespace Database
    Class dbConst
        Public Const _ConnectionString As String = "Initial Catalog=DatabaseName;Data Source=localhost;USER ID=sa; Password="
    End Class
    Class DbDataSet

        Public Sub MakeDataSet(ByVal TblName, ByVal SQLStr, ByVal dsetname)
            Dim constant As dbConst
            constant = New dbConst()
            Dim myConnectionString = constant._ConnectionString
            Dim objConnection As New SqlConnection(myConnectionString)
            Dim objCommand As New SqlCommand(SQLStr, objConnection)
            Dim adapter As New SqlDataAdapter(objCommand)
            objConnection.Open()
            adapter.Fill(dsetname, TblName)
            objConnection.Close()
        End Sub

        Public Sub addtable(ByVal TableName, ByVal SQLString, ByVal Dset)
            Dim myConnectionString = "Initial Catalog=majamee;Data Source=localhost;USER ID=sa; Password=aaa"
            Dim objConnection As New SqlConnection(myConnectionString)
            Dim objCommand As New SqlCommand(SQLString, objConnection)
            Dim adapter As New SqlDataAdapter(objCommand)
            objConnection.Open()
            adapter.Fill(Dset, TableName)
            objConnection.Close()
        End Sub
    End Class
    Class dbExecute
        Private FieldsArray
        Private ValuesArray
        Private TableName
        Private _Condition
        Private _SQLString

        Public Property Fields()
            Get
                Return FieldsArray
            End Get
            Set(ByVal Value)
                Dim tmpVal As String = Value
                If StrComp(Right(tmpVal, 1), ",", 1) = 0 Then
                    tmpVal = Mid(tmpVal, 1, Len(tmpVal) - 1)
                End If
                FieldsArray = tmpVal
            End Set
        End Property

        Public Property Values()
            Get
                Return ValuesArray
            End Get
            Set(ByVal Value)
                Dim tmpVal As String = Value
                If StrComp(Right(tmpVal, 1), ",", 1) = 0 Then
                    tmpVal = Mid(tmpVal, 1, Len(tmpVal) - 1)
                End If
                If StrComp(Right(tmpVal, 2), ",'", 1) = 0 Then
                    tmpVal = Mid(tmpVal, 1, Len(tmpVal) - 2)
                End If
                ValuesArray = tmpVal
            End Set
        End Property

        Public Property Table()
            Get
                Return TableName
            End Get
            Set(ByVal Value)
                TableName = Value
            End Set
        End Property

        Public Property Condition()
            Get
                Return _Condition
            End Get
            Set(ByVal Value)
                _Condition = Value
            End Set
        End Property

        Public ReadOnly Property SQLString()
            Get
                Return _SQLString
            End Get
        End Property


        Public Sub Insert()
            Dim insertCommand As New SqlCommand()
            Dim insertConnection As New SqlConnection()
            Dim tmpConst As New dbConst()
            _SQLString = "INSERT INTO " &amp; TableName &amp; "(" &amp; FieldsArray &amp; ") VALUES (" &amp; ValuesArray &amp; ")"
            insertConnection.ConnectionString = tmpConst._ConnectionString
            insertConnection.Open()
            insertCommand.CommandText = _SQLString
            insertCommand.Connection = insertConnection
            insertCommand.ExecuteNonQuery()
        End Sub

        Public Sub Insert(ByVal strTable As String, ByVal strFields As String, ByVal strValues As String)
            Dim insertCommand As New SqlCommand()
            Dim insertConnection As New SqlConnection()
            Dim tmpConst As New dbConst()
            Table = strTable
            Fields = strFields
            Values = strValues
            _SQLString = "INSERT INTO " &amp; TableName &amp; "(" &amp; FieldsArray &amp; ") VALUES (" &amp; ValuesArray &amp; ")"
            insertConnection.ConnectionString = tmpConst._ConnectionString
            insertConnection.Open()
            insertCommand.CommandText = _SQLString
            insertCommand.Connection = insertConnection
            insertCommand.ExecuteNonQuery()
        End Sub

        Public Sub Update()
            Dim updateCommand As New SqlCommand()
            Dim updateConnection As New SqlConnection()
            Dim tmpConst As New dbConst()
            Dim valarr
            Dim fieldarr
            Dim i As Int16
            valarr = Split(Values, ",", -1, 1)
            fieldarr = Split(Fields, ",", -1, 1)
            _SQLString = "UPDATE " &amp; Table &amp; " SET "
            For i = 0 To UBound(valarr)
                _SQLString = _SQLString &amp; fieldarr(i) &amp; "=" &amp; valarr(i) &amp; ","
            Next
            _SQLString = Mid(_SQLString, 1, Len(_SQLString) - 1)
            If StrComp(Condition, "", 1) &lt;> 0 Then
                _SQLString = _SQLString &amp; " WHERE " &amp; Condition
            End If
            updateConnection.ConnectionString = tmpConst._ConnectionString
            updateConnection.Open()
            updateCommand.CommandText = _SQLString
            updateCommand.Connection = updateConnection
            updateCommand.ExecuteNonQuery()
        End Sub
        Public Sub Update(ByVal strTable As String, ByVal strFields As String, ByVal strValues As String, ByVal strCondition As String)
            Dim updateCommand As New SqlCommand()
            Dim updateConnection As New SqlConnection()
            Dim tmpConst As New dbConst()
            Dim valarr
            Dim fieldarr
            Dim i As Int16
            Table = strTable
            Fields = strFields
            Values = strValues
            Condition = strCondition
            valarr = Split(Values, ",", -1, 1)
            fieldarr = Split(Fields, ",", -1, 1)
            _SQLString = "UPDATE " &amp; Table &amp; " SET "
            For i = 0 To UBound(valarr)
                _SQLString = _SQLString &amp; fieldarr(i) &amp; "=" &amp; valarr(i) &amp; ","
            Next
            _SQLString = Mid(_SQLString, 1, Len(_SQLString) - 1)
            If StrComp(Condition, "", 1) &lt;> 0 Then
                _SQLString = _SQLString &amp; " WHERE " &amp; Condition
            End If
            updateConnection.ConnectionString = tmpConst._ConnectionString
            updateConnection.Open()
            updateCommand.CommandText = _SQLString
            updateCommand.Connection = updateConnection
            updateCommand.ExecuteNonQuery()
        End Sub

        Public Sub Delete()
            Dim deleteCommand As New SqlCommand()
            Dim deleteConnection As New SqlConnection()
            Dim tmpConst As New dbConst()

            If StrComp(Condition, "", 1) = 0 Then
                _SQLString = "DELETE FROM " &amp; Table
            Else
                _SQLString = "DELETE FROM " &amp; Table &amp; " WHERE " &amp; Condition
            End If
            deleteConnection.ConnectionString = tmpConst._ConnectionString
            deleteConnection.Open()
            deleteCommand.CommandText = _SQLString
            deleteCommand.Connection = deleteConnection
            deleteCommand.ExecuteNonQuery()
        End Sub
        Public Sub Delete(ByVal strTable As String, ByVal strCondition As String)
            Dim deleteCommand As New SqlCommand()
            Dim deleteConnection As New SqlConnection()
            Dim tmpConst As New dbConst()
            Table = strTable
            Condition = strCondition
            If StrComp(Condition, "", 1) = 0 Then
                _SQLString = "DELETE FROM " &amp; Table
            Else
                _SQLString = "DELETE FROM " &amp; Table &amp; " WHERE " &amp; Condition
            End If
            deleteConnection.ConnectionString = tmpConst._ConnectionString
            deleteConnection.Open()
            deleteCommand.CommandText = _SQLString
            deleteCommand.Connection = deleteConnection
            deleteCommand.ExecuteNonQuery()
        End Sub
    End Class
End Namespace

خوب در كد بالا ما چند كلاس در يك NameSpace داريم. اين كلاسها كار خواندن و نوشتن اطلاعات رو در DataBase انجام مي دن.

براي استفاده از كلاسها بايد اين NameSpace رو در فايلتون Import كنيد.
فرض كنيد اسم پروژه ما Test هست.
براي اضافه كردن NameSpace مينويسيم:

کد:

Imports Test.Database

كلاس DbConst كلاسيه كه ConnectionString رو براي اتصال به ديتابيس بر مي گردونه.
شما بايد گزينه DataBaseName رو به نام جدول خودتون تغيير بديد.

براي خوندن اطلاعات از ديتابيس از كلاس DbDataSet استفاده مي كنيم.
متد هاي MakeDataset و AddTable براي ساختن يه ديتاست جديد و افزودن يه جدول به ديتاست جاري به كار مي روند.
مثال:
فرض كنيد دوتا Table به نامهاي tbl1 و tbl2 داريم. و مي خوايم محتويات اونا رو نشون بديم.

کد:

:.
Dim DsetMaker As New MakeDataSet()
Dim tmpDset As New Dataset()
Dim DRowtbl1() As DataRow
Dim DRowtbl2() As DataRow
Dim i As Integer
Dim tmpSQL As String = "SELECT * FROM tbl1"
DsetMaker.MakeDataSet("tblOne",tmpSQL,tmpDset)
tmpSQL = "SELECT * FROM tbl2"
DsetMaker.AddTable("tblTow",tmpSQL,tmpDSet)
DRowtbl1 = tmpDSet.Tables("One").Select
DRowtbl2 = tmpDSet.Tables("Tow").Select
For i=0 To Ubound(DRowtbl1)
Response.write DRowtbl1(i)("Fld1")
Next i
:.

براي ديدن محتواي جدول دوم هم يه حلقه ديگه تشكيل مي ديم.

براي Insert,Update,Delete از كلاس dbExecute استفاده مي كنيم.

توجه داشته باشيد كه متد هاي Update و Insert متدهاي OverLoad شده هستند و شما از هر كدام از دو صورت اونها مي تونيد استفاده كنيد. يعني مثلا براي Insert توي ديتابيس اول خاصيتهاي Fields,Values,Table رو مقدار دهي كنيد و بعد از متد َUpdate استفاده كنيد.

مثال:

کد:

 Dim Executer As New dbExecute()
Executer.Table = "tbl1"
Executer.Fields = "Fld1,Fld2"
Executer.Values = "'hello','Word'"
Executer.Insert()

يا مي تونيد از متد Insert با پارامتر استفاده كنيد.

کد:

Executer.Insert("tbl1", "fld1,fld2","'Hello','Word'")

استفاده از متد Update هم دقيقا مثل متد Insert است. فقط اگه شرطي داشتيد مي تونيد بهش اعمال كنيد.

براي متد Delete هم مي تونيد يه شرط بذاريد يا بدون شرط اجراش كنيد كه در اين صورت كل Table پاك مي شه.

لطفا نقايسشو برام بگيد تا كاملش كنم.

اميد وارم به دردتون بخوره.