By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,718 Members | 1,220 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,718 IT Pros & Developers. It's quick & easy.

How to execute multiple queries in an application?

100+
P: 347
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
Nov 17 '10 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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)

Expand|Select|Wrap|Line Numbers
  1. Public Class DataAccess
  2.     'this class is used to interact with the database
  3.  
  4.     'the connection string used to access the database
  5.     Private strPath As String = "Provider=Microsoft.Jet.OLEDB.4.0 ;" & _
  6.     "Data Source=C:\Users\User\Documents\Visual Studio 2005\WebSites\" & _
  7.     "SLB\App_Data\SLB.mdb" 'This path will obviously change according to your database location
  8.  
  9.     Public Function QueryDB(ByVal strSql As String) As DataTable
  10.         'method receives a sql query string and returns a dataTable
  11.         Dim odaSLB As New OleDb.OleDbDataAdapter(strSql, strPath)
  12.         Dim dtTemp As New DataTable
  13.  
  14.         odaSLB.Fill(dtTemp)
  15.         odaSLB.Dispose()
  16.  
  17.         Return dtTemp
  18.     End Function
  19.  
  20.     Public Sub InsDelUpdtDB(ByVal strSql As String)
  21.         'method establishes a connection with the database and
  22.         'executes sql statements, such as INSERT, DELETE, UPDATE
  23.         Dim conSLB As New OleDb.OleDbConnection(strPath)
  24.         conSLB.Open()
  25.  
  26.         Dim comSLB As New OleDb.OleDbCommand(strSql, conSLB)
  27.  
  28.         comSLB.ExecuteNonQuery()
  29.         comSLB.Dispose()
  30.         conSLB.Close()
  31.     End Sub
  32. 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 :)
Nov 19 '10 #2

Post your reply

Sign in to post your reply or Sign up for a free account.