How to execute multiple queries in an application?

[dougancil]

I'm building an application that will be using several (7) SQL queries, I'm wondering whats the best way to approach this since I've never built an app with this many queries in it. These queries really won't change, just the start date and end date and I have query in my first form that finds out the next available date by checking a table. What I'm wondering is if it would be better to construct these queries in my application or if it would be better to use stored procedures on my sql server to do the same thing and not have the application carry the load of the queries. Any thoughts on this would be appreciated.

Thanks

Doug

[David Patz]

Not amazingly clued up on what difference it makes to store queries in SQL Server or in VB.Net - But I would recommend creating a DataAccess Class in VB - allowing you to execute queries as Select statements, or to Update,Delete or Insert Records. Here is an example of the class I recently used in ASP.Net (it follows the same syntax as VB.Net)

Public Class DataAccess
    'this class is used to interact with the database

    'the connection string used to access the database
    Private strPath As String = "Provider=Microsoft.Jet.OLEDB.4.0 ;" & _
    "Data Source=C:\Users\User\Documents\Visual Studio 2005\WebSites\" & _
    "SLB\App_Data\SLB.mdb" 'This path will obviously change according to your database location

    Public Function QueryDB(ByVal strSql As String) As DataTable
        'method receives a sql query string and returns a dataTable
        Dim odaSLB As New OleDb.OleDbDataAdapter(strSql, strPath)
        Dim dtTemp As New DataTable

        odaSLB.Fill(dtTemp)
        odaSLB.Dispose()

        Return dtTemp
    End Function

    Public Sub InsDelUpdtDB(ByVal strSql As String)
        'method establishes a connection with the database and
        'executes sql statements, such as INSERT, DELETE, UPDATE
        Dim conSLB As New OleDb.OleDbConnection(strPath)
        conSLB.Open()

        Dim comSLB As New OleDb.OleDbCommand(strSql, conSLB)

        comSLB.ExecuteNonQuery()
        comSLB.Dispose()
        conSLB.Close()
    End Sub
End Class

Using this class allows you to construct your own SQL statements and process them with less redundance - so should 'technically' be more efficient.

Hope this helps :)