When I create a project for a new compiled module using the DNN template, a data provider class exists in the project which calls the stored procedures in order to store and retrieve data from the database. The fact that I do not like using stored procedures but I prefer to have direct access to the database because it makes the development process simpler for me, I create a new class for accessing the data. Below there is an example of such a class and I would like to know if it is compatible with all versions of DNN.

    Public Class Categories
        Public Function InsertCategory(ByVal LanguageID As String, ByVal CategoryName As String, ByVal CategoryParent As Integer) As Integer
            Dim MyConnection As New System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("SiteSqlServer").ConnectionString)
            MyConnection.Open()

            Dim MyCommand As New System.Data.SqlClient.SqlCommand
            MyCommand.Connection = MyConnection
            MyCommand.CommandType = System.Data.CommandType.Text
            MyCommand.CommandText = "INSERT INTO Map_Categories (LanguageID, CategoryName, CategoryParent) VALUES (@LanguageID, @CategoryName, @CategoryParent)"
            MyCommand.Parameters.Add(New System.Data.SqlClient.SqlParameter("@LanguageID", LanguageID))
            MyCommand.Parameters.Add(New System.Data.SqlClient.SqlParameter("@CategoryName", CategoryName))
            MyCommand.Parameters.Add(New System.Data.SqlClient.SqlParameter("@CategoryParent", CategoryParent))

            Dim NumberOfRowsAffected As Integer = MyCommand.ExecuteNonQuery()
            MyConnection.Close()
            Return NumberOfRowsAffected
        End Function

        Public Function UpdateCategory(ByVal CategoryID As Integer, ByVal CategoryName As String) As Integer
            Dim MyConnection As New System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("SiteSqlServer").ConnectionString)
            MyConnection.Open()

            Dim MyCommand As New System.Data.SqlClient.SqlCommand
            MyCommand.Connection = MyConnection
            MyCommand.CommandType = System.Data.CommandType.Text
            MyCommand.CommandText = "UPDATE Map_Categories SET CategoryName = @CategoryName WHERE CategoryID = @CategoryID"
            MyCommand.Parameters.Add(New System.Data.SqlClient.SqlParameter("@CategoryID", CategoryID))
            MyCommand.Parameters.Add(New System.Data.SqlClient.SqlParameter("@CategoryName", CategoryName))

            Dim NumberOfRowsAffected As Integer = MyCommand.ExecuteNonQuery()
            MyConnection.Close()
            Return NumberOfRowsAffected
        End Function

        Public Function SelectCategories(ByVal LanguageID As String) As DataTable
            Dim MyConnection As New System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("SiteSqlServer").ConnectionString)
            MyConnection.Open()

            Dim MyCommand As New System.Data.SqlClient.SqlCommand
            MyCommand.Connection = MyConnection
            MyCommand.CommandType = System.Data.CommandType.Text
            MyCommand.CommandText = "SELECT CategoryID, CategoryName, CategoryParent FROM Map_Categories WHERE LanguageID = @LanguageID"
            MyCommand.Parameters.Add(New System.Data.SqlClient.SqlParameter("@LanguageID", LanguageID))

            Dim MyDataAdapter As New SqlClient.SqlDataAdapter(MyCommand)
            Dim MyDataSet As New DataSet()
            MyDataAdapter.Fill(MyDataSet)
            MyConnection.Close()

            Return MyDataSet.Tables(0)
        End Function

        Public Function DeleteCategory(ByVal CategoryID As Integer) As Integer
            Dim MyConnection As New System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("SiteSqlServer").ConnectionString)
            MyConnection.Open()

            Dim MyCommand As New System.Data.SqlClient.SqlCommand
            MyCommand.Connection = MyConnection
            MyCommand.CommandType = System.Data.CommandType.Text
            MyCommand.CommandText = "DELETE FROM Map_Categories WHERE CategoryID = @CategoryID"
            MyCommand.Parameters.Add(New System.Data.SqlClient.SqlParameter("@CategoryID", CategoryID))

            Dim NumberOfRowsAffected As Integer = MyCommand.ExecuteNonQuery()
            MyConnection.Close()
            Return NumberOfRowsAffected
        End Function
    End Class

I think that this class will help other developers as well but I would like to know if it will cause some kind of problem in a dnn installation with special settings.

The way that I do it is faster for me to develop a module and by changing the SQL command in my class I change the data that I will retrieve when I want to make a change. I would like to know why it is safer to use the SP. The only thing that I care about is that the code will executed successfully in all the dnn installations.

Sergey, thank you for your replies.

I know about the fact that the SP is precompiled and it is executed a bit faster than the standard SQL commands but for the projects that I develop it is not really a problem.

About the decompile thing that you said, if someone has access to the web server in order to get the dll and decompile it, he can also see the web.config file and get the SQL Server login information in order to connect to the database and get access to the data.

As I mentioned before the only thing that I care about is that it will make the job and it will not cause some kind of trouble during the execution of the module.