How can I get an ASP.NET (Framework 4.5) Web Forms project to recognize the SQLServer class?

Question

I have been unable to get a legacy VB.NET ASP site to run.

So, I tried creating a new one from scratch, adding folders and files one-by-one to it, from the legacy project. However, it does not recognize the SQLServer class and is now at the point where it generates 106 errors when building.

Thus, I have started over yet again. I created a new project > Templates > Visual Basic > Web > ASP.NET Web Aplication. and added the class that references SQL stuff first, to try to get at the root of the problem.

To do this, imitating the legacy project, I added an App_Code folder to the project, and then added commonClass.vb to it, and started copying over functions, one at a time. First, I added the Imports clauses, and then a couple of functions:

Imports Microsoft.VisualBasic
Imports System.Data.SqlClient
Imports System.Data
Imports DAL05.DataAccess
Imports System

Public Class commonClass

    Public Shared Function GetImages(ByVal MemberNo As String, ByVal ImageID As String, ByVal FileName As String) As DataView
        Dim ds As New DataSet()
        Dim SQLstring As String = "EXEC up_GettyImages '" & MemberNo & "','" & ImageID & "','" & FileName & "'"
        Dim Command As New SqlDataAdapter(SQLstring, System.Configuration.ConfigurationManager.AppSettings("OrderEnterConnection"))
        Command.Fill(ds, "xImages")
        Dim dv As DataView
        dv = ds.Tables("xImages").DefaultView()
        Return dv
    End Function

        Public Function GetPortalMenuItems() As DataSet
        Dim dset As New DataSet
        dset.ReadXml(System.Web.Hosting.HostingEnvironment.MapPath("~/App_Data/PortalMenuItems.xml"))
        Return dset
    End Function

End Class

Here appears the first problem: the last "s" in Imports DAL05.DataAccess has a red underscore, but clicking "Error Correction Options" says, "no correction suggestions"

Apparently it doesn't know what DAL05.DataAccess is; so I looked for a "References" folder in my project - there isn't one. The legacy project has a Bin folder with DAL05:

I had a problem with/question about adding this, too, here.

So is it that the legacy project targets .NET Framework 3.5, and the new one targets 4.5 that is causing the mismatch/confusion? Is DAL05.DataAccess the wrong thing to import? Has it been superseded by something else?

Under Project > Properties > References > Imported namespaces, I added "Microsoft.SqlServer", which then let me know that some of my Imports were unnecessary, so that I now only have:

Imports System.Data.SqlClient
Imports System.Data

...but when I added this method:

Public Function LogAction(ByVal SessionID As String, ByVal userId As String, ByVal action As String, ByVal comment As String) As DataTable
    Dim sqlDAL As New SQLServer(System.Configuration.ConfigurationManager.AppSettings("OrderEnterConnection"))
    Dim SQLstring As String = "Exec fredFlintstone '" & SessionID & "','" & userId & "','" & action & "','" & comment & "'"
    Dim dset As DataSet
    dset = sqlDAL.runSQLDataSet(SQLstring)
    Return dset.Tables(0)
End Function

...SQLServer is redded up, and the "helpful hint" there tells me:

Type 'SQLServer' is not defined.

...with the following options:

Change 'SQLServer' to 'SQLError'
Generate 'Class SQLServer'
Generate new type...

None of these options seem sensible to me.

If it's really not possible to use the SQLServer class, what should I use instead?

UPDATE

Even stranger to me (maybe because I am a stranger to VB) is that, although "SQLServer" is red in the editor and unrecognized, when I rebuild the project, it succeeds? How could that be?

If I F5 the project, it does fail:

Compilation Error

Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately. 

Compiler Error Message: BC30002: Type 'SQLServer' is not defined.

Source Error:    

Line 21: 
Line 22:     Public Function LogAction(ByVal SessionID As String, ByVal userId As String, ByVal action As String, ByVal comment As String) As DataTable
Line 23:         Dim sqlDAL As New SQLServer(System.Configuration.ConfigurationManager.AppSettings("OrderEnterConnection"))
Line 24:         Dim SQLstring As String = "Exec sp_LogAction_Net '" & SessionID & "','" & userId & "','" & action & "','" & comment & "'"
Line 25:         Dim dset As DataSet

Source File: C:\Projects\MemberOrderEntryNew\MemberOrderEntry3\MemberOrderEntry3\App_Code\commonClass.vb    Line: 23

...but that it (re)builds fine is confusing to me.

UPDATE 2

I also tried System.Data.SQLClient, but to no avail - it has the same problem as "SQLServer":

Answer 1

Here are two options for replacing the LogAction method:

Public Function LogActionOption1(SessionID As String, userId As String, action As String, comment As String) As DataTable
    Dim dt As New DataTable

    Using sqlConn As New SqlConnection(System.Configuration.ConfigurationManager.AppSettings("OrderEnterConnection"))
        Using sqlCmd As New SqlCommand("sp_LogAction_Net", sqlConn)
            sqlCmd.CommandType = CommandType.StoredProcedure
            'TODO: REQUIRED - set the parameter names, types and sizes to comply with the declarations in the stored procedure.
            ' Note: sessionid is expected to be a 24-character string: http://stackoverflow.com/a/3518523/1115360
            sqlCmd.Parameters.Add(New SqlParameter With {.ParameterName = "@SessionId", .SqlDbType = SqlDbType.Char, .Size = 24, .Value = SessionID})
            sqlCmd.Parameters.Add(New SqlParameter With {.ParameterName = "@userId", .SqlDbType = SqlDbType.NVarChar, .Size = 50, .Value = userId})
            sqlCmd.Parameters.Add(New SqlParameter With {.ParameterName = "@action", .SqlDbType = SqlDbType.NVarChar, .Size = 16, .Value = action})
            sqlCmd.Parameters.Add(New SqlParameter With {.ParameterName = "@comment", .SqlDbType = SqlDbType.NVarChar, .Size = 500, .Value = comment})
            Dim da As New SqlDataAdapter(sqlCmd)
            da.Fill(dt)
        End Using
    End Using

    Return dt

End Function

However, it could be that the returned datatable is not actually used, or not used in a way that justifies using a datatable.

If it is only required to confirm that the data was added to the database:

Public Function LogActionOption2(SessionID As String, userId As String, action As String, comment As String) As Boolean
    Dim success As Boolean = False
    Dim dt As New DataTable

    Using sqlConn As New SqlConnection(System.Configuration.ConfigurationManager.AppSettings("OrderEnterConnection"))
        Using sqlCmd As New SqlCommand("sp_LogAction_Net", sqlConn)
            sqlCmd.CommandType = CommandType.StoredProcedure
            'TODO: REQUIRED - set the parameter names, types and sizes to comply with the declarations in the stored procedure.
            ' Note: sessionid is expected to be a 24-character string: http://stackoverflow.com/a/3518523/1115360
            sqlCmd.Parameters.Add(New SqlParameter With {.ParameterName = "@SessionId", .SqlDbType = SqlDbType.Char, .Size = 24, .Value = SessionID})
            sqlCmd.Parameters.Add(New SqlParameter With {.ParameterName = "@userId", .SqlDbType = SqlDbType.NVarChar, .Size = 50, .Value = userId})
            sqlCmd.Parameters.Add(New SqlParameter With {.ParameterName = "@action", .SqlDbType = SqlDbType.NVarChar, .Size = 16, .Value = action})
            sqlCmd.Parameters.Add(New SqlParameter With {.ParameterName = "@comment", .SqlDbType = SqlDbType.NVarChar, .Size = 500, .Value = comment})

            sqlConn.Open()
            success = (sqlCmd.ExecuteNonQuery > 0)
            sqlConn.Close()

        End Using
    End Using

    Return success

End Function

And if even that minimal feedback is not used, you can easily change that into a Sub.

P.S. If you end up modifying the stored procedures, you should avoid prefixing them with "sp_": Is the sp_ prefix still a no-no?

Answer 2

Based on the information you've provided thus far, it's not clear why you need the DAL05 dll at all. If you can connect to SQL Server with the standard System.Data.SqlClient objects, use those and ditch Dal05.

If you absolutely have to know what Dal05 is doing and how it works, grab Telerik's JustDecompile (it's free) and (assuming it's a .NET dll) you will be able to view the source to find out what it is doing and how its classes work.

Answer 3

I got the MembesOrderEntry project to build and run, displaying the login page in the browser.

The next step is updating the Telerik DLLs. Which ones shall I use? The ones in ?