• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 177
  • Last Modified:

making data available from the database... efficiently

Hi
I am learning how to use .Net and have a question regarding efficiency of the code.

As i understand it i can create a series of datasets (basically in memory tables) from the database.

What is the best way to code a page where i have a number of different values from different parts of my database? Some queries might need to include joins between tables but in other cases i could just run discreet queries on a single table.  Should i create a large number of different data sets and call each one with the relevant server control or should i create a single data set and manipulate the data within it.

Any info on this ... and tips, tricks, best practice... really to move me forward beyond just connecting to a DB and returning some rows from a query would be gratefully accepted

thanks
0
lz7cjc
Asked:
lz7cjc
  • 8
  • 6
1 Solution
 
RonaldBiemansCommented:
No, there is rarely the need for more than one dataset, a dataset can control as many datatables as you have memory.
you can add relationships to the tables in the dataset.
0
 
lz7cjcAuthor Commented:
I think i may have just had a mini breakthrough with that last comment!

so is it just at this part of the code that i can add different records to be used?

         DataAdapterVariable.Fill(DataSetVariable, "People")
         DataAdapterVariable.Fill(LabelDataSet, "calls")
         DataAdapterVariable.Fill(<any>, "<any>")

is that right?

do i need to be aware of any exceptions?

thanks
0
 
RonaldBiemansCommented:
if you do that you will have to change the select statement of dataadapter or use a combined dataadapter

like
dim da as new oledb.oledbdataadapter("select * from People;select * from calls",yourconnection)
and then just to

da.fill(yourdataset)

the dataset will now hold all the table (it will name them table,table1) if you want them to retain there original name you will have to use tablemappings like

Da.TableMappings.Add("table", "People")
Da.TableMappings.Add("table1", "calls")

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
RonaldBiemansCommented:
I would not use this method if you want to update the table, use different adapters to fill the dataset
0
 
lz7cjcAuthor Commented:
so much for the breakthrough... i don't want to use a combined data adapter... i just want to get the basic tools sorted in my head so i can build on the base elements of retrieving data from a DB and writing to the page...

this is what i have got ...

         'create the variable to hold the connection
         Dim ConnectToDatabase as SQLConnection
         'create the instance of the connection
         ConnectToDatabase = New SQLConnection
         ConnectToDatabase.ConnectionString = "UID=SA;PWD=hello; Data Source=(local); database=jobs;"

        'Create the commands that are sent to the Database
         Dim CommandVariable as SQLCommand
         CommandVariable = New SQLCommand
         CommandVariable.CommandText = "Select * from tbl_people"
         CommandVariable.Connection = ConnectToDatabase

          Dim LabelCommand as SqlCommand
          LabelCommand = New SqlCommand
          LabelCommand.CommandText = "SELECT comments FROM tbl_call WHERE (callID = 7)"
          LabelCommand.Connection = ConnectToDatabase

         'Make it happen
         Dim DataAdapterVariable as SQLDataAdapter
         DataAdapterVariable = New SQLDataAdapter

         'put the sql into the DataAdapter Object
         DataAdapterVariable.SelectCommand = CommandVariable
         DataAdapterVariable.Selectcommand.Connection.Open

         Dim LabelAdapter as SQLDataAdapter
         LabelAdapter = New SQLDataAdapter

         LabelAdapter.SelectCommand = LabelCommand
         LabelAdapter.Selectcommand.Connection.Open


         'Now need somewhere to store the info returned from the database
         Dim DataSetVariable as DataSet
         DataSetVariable = New DataSet

         Dim LabelDataSet as DataSet
         LabelDataSet = New DataSet


         'fill this container with a dataset
         DataAdapterVariable.Fill(DataSetVariable, "People")
         LabelAdapter.Fill(LabelDataSet, "calls")
        ' LabelAdapter.Fill(LabelDataSet, "Call")

         'now bind the data to a control ... in this case the datagrid PeopleGrid and get the information from the people dataset
         PeopleGrid.Datasource = DataSetVariable.Tables("

and this is the error:
 Exception Details: System.InvalidOperationException: The connection is already Open (state=Open).
Line 37:          LabelAdapter.Selectcommand.Connection.Open

thanks for your help (and patience!)

Nick
0
 
RonaldBiemansCommented:
'create the variable to hold the connection
         Dim ConnectToDatabase as SQLConnection
         'create the instance of the connection
         ConnectToDatabase = New SQLConnection
         ConnectToDatabase.ConnectionString = "UID=SA;PWD=hello; Data Source=(local); database=jobs;"

         'Make it happen
         Dim DataAdapterVariable as new SQLDataAdapter("Select * from tbl_people",connecttodatabase)
         Dim LabelAdapter as new SQLDataAdapter("SELECT comments FROM tbl_call WHERE (callID = 7)",connecttodatabase)

         Dim Ds as new DataSet

         'fill this container with a dataset
         connecttodatabase.open
         DataAdapterVariable.Fill(DataSetVariable, "People")
         LabelAdapter.Fill(LabelDataSet, "calls")
         connecttodatabase.close

         'now bind the data to a control ... in this case the datagrid PeopleGrid and get the information from the people dataset
         PeopleGrid.Datasource = Ds.tables(People")
0
 
RonaldBiemansCommented:
A lot less code :-)
0
 
RonaldBiemansCommented:
this can also be downsized

Dim ConnectToDatabase as new SQLConnection("UID=SA;PWD=hello; Data Source=(local); database=jobs;")
0
 
lz7cjcAuthor Commented:
it is a lot less code! you have completely removed the Command section - is this bit not necessary?

i am however still getting an error:
BC30451: Name 'DataSetVariable' is not declared.
Line 22: DataAdapterVariable.Fill(DataSetVariable, "People")
0
 
TorrwinCommented:
Well, you declared DS as your dataset, but did not change 'DataSetVariable' to 'DS' as well.
0
 
RonaldBiemansCommented:
Yes , sorry about that, no in this case you don't need the commandobject because you can do it all in the declaration of the dataadapter.

        Dim ConnectToDatabase as new SQLConnection("UID=SA;PWD=hello; Data Source=(local); database=jobs;")

         Dim DataAdapterVariable as new SQLDataAdapter("Select * from tbl_people",connecttodatabase)
         Dim LabelAdapter as new SQLDataAdapter("SELECT comments FROM tbl_call WHERE (callID = 7)",connecttodatabase)

         Dim Ds as new DataSet

         connecttodatabase.open
         DataAdapterVariable.Fill(DS, "People")
         LabelAdapter.Fill(DS, "calls")
         connecttodatabase.close

         PeopleGrid.Datasource = Ds.tables("People")
         CallsGrid.Datasource = Ds.tables("calls")
0
 
lz7cjcAuthor Commented:
yes...that now works... kinda...
the page now loads without errors... but there is no data!
and the people table certainly has rows in it...

any ideas?
thanks
0
 
lz7cjcAuthor Commented:
btw this is how i have written the server control
    <p>
        <asp:datagrid id="PeopleGrid" runat="server"></asp:datagrid>
    </p>
0
 
RonaldBiemansCommented:
sorry I forgot this is a web app

you have to add the databind method


PeopleGrid.Datasource = Ds.tables("People")
PeopleGrid.databind
0
 
lz7cjcAuthor Commented:
thank you so much
i shall no doubt have more questions for you but for now you have been magnificent

thanks
Nick
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 8
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now