I have a few questions for those who know so much about the web..

1. Is it better to declare.. 20 variables and do all your SQL queries immediately, or to do like 10 variables and open your connection, do one query, then reset your variables and runt he queries as you go, finally closing the connection? Sample below:

Dim conPubs As OdbcConnection
Dim conString As String
conString = System.Configuration.ConfigurationManager.AppSettings.Get("ConnectionString")
conPubs = New OdbcConnection( conString )
Dim dtrReader As OdbcDataReader
cmdSelect = New OdbcCommand( "SELECT * FROM VIPPackages ORDER BY PackageID", conPubs )
conPubs.Open()
dtrReader = cmdSelect.ExecuteReader()
if dtrReader.HasRows then
  rpVIP.DataSource = dtrReader
  rpVIP.DataBind()
  dtrReader.Close()
end if
cmdSelect = New OdbcConnection( "SELECT ID FROM VIPPackage", conPubs )
Dim intID1 As Integer = cmdSelect.ExecuteScalar()
cmdSelect = New OdbcConnection( "SELECT OOPS FROM VIPPackage", conPubs )
Dim intOops1 As Integer = cmdSelect.ExecuteScalar()
Dim strUserChose As String = Request.QueryString("chosen")
if strUserChose = 3 then
cmdSelect = New OdbcCommand( "SELECT * FROM People ORDER BY PersonID", conPubs )
dtrReader = cmdSelect.ExecuteReader()
if dtrReader.HasRows then
  rpPeople.DataSource = dtrReader
  rpVIP.DataBind()
  dtrReader.Close()
end if
end if
cmdSelect = New OdbcConnection( "SELECT ID FROM People", conPubs )
Dim intID2 As Integer = cmdSelect.ExecuteScalar()
cmdSelect = New OdbcConnection( "SELECT Oops FROM People", conPubs )
Dim intOops2 As Integer = cmdSelect.ExecuteScalar()
cmdSelect = New OdbcCommand( "SELECT * From Humans ORDER BY HumanID", conPubs )
dtrReader = cmdSelect.ExecuteReader()
if dtrReader.HasRows then
  rpHumans.DataSource = dtrReader
  rpVIP.DataBind()
  dtrReader.Close()
end if
cmdSelect = New OdbcConnection( "SELECT ID FROM Humans", conPubs )
Dim intID3 As Integer = cmdSelect.ExecuteScalar()
cmdSelect = New OdbcConnection( "SELECT Oops FROM Humans", conPubs )
Dim intOops3 As Integer = cmdSelect.ExecuteScalar()
conPubs.Close()

'''  OR  '''
Dim conPubs As OdbcConnection
Dim conString As String
conString = System.Configuration.ConfigurationManager.AppSettings.Get("ConnectionString")
conPubs = New OdbcConnection( conString )
Dim dtrReader As OdbcDataReader
Dim strUserChose As String = Request.QueryString("chosen")
Dim cmdSelect As New OdbcCommand( "SELECT * FROM VIPPackages ORDER BY PackageID", conPubs )
Dim cmdSelect3 As New OdbcCommand( "SELECT * FROM Humans ORDER BY HumanID", conPubs )
Dim cmdSelect4 As New OdbcCommand( "SELECT ID FROM VIPPackages", conPubs )
Dim cmdSelect5 As New OdbcCommand( "SELECT Oops FROM VIPPackages", conPubs )
Dim cmdSelect6 As New OdbcCommand( "SELECT ID FROM People", conPubs )
Dim cmdSelect7 As New OdbcCommand( "SELECT Oops FROM People", conPubs )
Dim cmdSelect8 As New OdbcCommand( "SELECT ID FROM Humans", conPubs )
Dim cmdSelect9 As New OdbcCommand( "SELECT Oops FROM Humans", conPubs )
conPubs.Open()
dtrReader = cmdSelect.ExecuteReader()
if dtrReader.HasRows then
  rpVIP.DataSource = dtrReader
  rpVIP.DataBind()
  dtrReader.Close()
end if
if strUser = 3 then
  Dim cmdSelect2 As New OdbcCommand( "SELECT * FROM People ORDER BY PersonID", conPubs )
  dtrReader = cmdSelect2.ExecuteReader()
  if dtrReader.HasRows then
    rpPeople.DataSource = dtrReader
    rpPeople.DataBind()
    dtrReader.Close()
  end if
end if
dtrReader = cmdSelect3.ExecuteReader()
if dtrReader.HasRows then
  rpHuman.DataSource = dtrReader
  rpHuman.DataBind()
  dtrReader.Close()
end if
intID1 = cmdSelect4.ExecuteScalar()
intOops1 = cmdSelect5.ExecuteScalar()
intID2 = cmdSelect6.ExecuteScalar()
intOops2 = cmdSelect7.ExecuteScalar()
intID3 = cmdSelect8.ExecuteScalar()
intOops3 cmdSelect9.ExecuteScalar()
conPubs.Close()

Obviously the above code is BS, but just wondering! Which is faster, declaring all variables, and opening and closing the connection as quick as possible, or overwriting variables and keeping the connection open longer..

2. Why do I see code from people, here and there, that is all broken down line by line instead of it being all one line? I'm not talking about when setting variables and including a ton of &'s in it.. I am talking about the below code:

<asp:DataList
    ID="dlList"
    repeatcolumns="3"
    forecolor="#FFFFFF"
    cellpadding="3">

'''  COMPARED TO  '''

<asp:DataList ID="dlList" repeatcolumns="3" forecolor="#FFFFFF" cellpadding="3">

What are the differences? Is one faster than the other? Please explain if you can! :)

Hi,

To answer your first question - from your code sample below the way you are doing data access is pretty bad practice. For one - opening a connection at all is an expensive operation, so doing it consecutively should be avoided, especially when you can get all that data in one query. If its not possible to get all the data you need in one table, a data set can contain multiple tables in which you can store multiple sql result sets.

Secondly for the multi line control declaration - that is only a person preference on how you'd like to style your code block. Sometimes its pretty much nessesary, for exampel here is a control I made that has a poop load of attributes. You dont wanna see all this stuff on one big long line:

<asp:GridView ID="gv_Files" runat="server" AutoGenerateColumns="false"   
      
        OnPageIndexChanging="gv_Files_PageIndexChanging"  
        OnSorting="gv_Files_Sorting"
        OnSorted="gv_Files_Sorted" 
         
        AllowPaging="true"
        AllowSorting="true"
        
        PageSize="25"
        PagerSettings-Mode="NextPreviousFirstLast"
        PagerSettings-NextPageText="Next Page" 
        PagerSettings-PreviousPageText="Previous Page"
        
        Width="580"
        BackColor="White" 
        BorderColor="#336666" 
        BorderStyle="Solid"
        BorderWidth="1px" 
        CellPadding="8" 
        
        AlternatingRowStyle-BackColor="#F1F1F1" 
        GridLines="Horizontal">

Hope I was understanding your SQL question properly! see ya.

jenni

Oh no of course that example up there was poor quality! Just wondering which would have been better. And secondly, thank you!

This article has been dead for over six months. Start a new discussion instead.