Please Help. Questions about SQL in web form

[Aaron]

Questions are at the end.

I am developing an ASP.NET web form and I have some questions about how to
properly use the SQLDataAdapter and Dataset objects.

When the application is started, the user is presented with a search form.
The txtSearchDate.text and DropDownList1.selecteditem.text are used as
search criteria. The user enters a date in txtSearchDate and selects an
agent from a dropdown. Then they press the retrieve button which makes a
connection to SQL, opens the table, fills a dataset, and a datagrid is bound
to this dataset.

Here is the code that is used for the sql portion:

Function GetPersonRecords()
Dim SqlCon As New SqlClient.SqlConnection()
Dim DA As New SqlClient.SqlDataAdapter()
Dim SqlCmd As New SqlClient.SqlCommand()
Dim DS As New DataSet()
Dim SQLSelectStatement As String
Dim ColumnNames As String
Dim FromClause As String
Dim WhereClause As String
Dim OrderClause As String
SqlCon = WebBizDev.Utilities.SQLConnect("Boca2", "USER", "PASSWORD",
"pursuittest")
ColumnNames = WebBizDev.Utilities.SQLColumnNames("Activity.Date AS
Activity_Date, Activity.FollowUp AS Activity_FollowUp, Activity.Notes AS
Activity_Notes, Activity.Notes_Confidential AS Activity_Notes_Confidential,
Client.Name_Last AS Client_Name_Last, Client.Name_First AS
Client_Name_First, Client.Name_Full AS Client_Name_Full, Client.Title_Long
AS Client_Title_Long, Company1.Company AS Client_Company, Client.Person_ID
AS Client_Person_ID, Activity_ID")
FromClause = WebBizDev.Utilities.SQLFromClause("Person Applicant
LEFT OUTER JOIN Company ON Applicant.Company_ID = Company.Company_ID RIGHT
OUTER JOIN Person Client RIGHT OUTER JOIN Job RIGHT OUTER JOIN Activity LEFT
OUTER JOIN Company Company1 ON Activity.Client_Company_ID =
Company1.Company_ID ON Job.Job_ID = Activity.Job_ID ON Client.Person_ID =
Activity.Client_ID ON Applicant.Person_ID = Activity.Applicant_ID")
WhereClause =
WebBizDev.Utilities.SQLWhereClause("(Activity.Activity_CD = 2) and
(Activity.Date >='" & txtSearchDate.Text() & "') and (Activity.agent like '"
& DropDownList1.SelectedItem.Text & "')")
OrderClause = WebBizDev.Utilities.SQLOrderClause("Activity.FollowUp
DESC")
SqlCmd.CommandText = "Select " & ColumnNames & FromClause &
WhereClause & OrderClause
SqlCmd.Connection = SqlCon
DA.SelectCommand = SqlCmd
DA.Fill(DS, "First")
Return DS
End Function






You can see that I am creating a SQLDataAdapter and filling the dataset in
this function, then returning the dataset to the calling sub and binding the
datagrid to the returned DS. This uses the Button1_Click handler.

The calling subs are here:





Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Dim ds As DataSet
ds = GetPersonRecords()
ClearTextBoxes() 'Clear Data from textboxes
BindDataGrid(DataGrid1, ds, "Client_person_ID", "First") 'Query for
data
End Sub

Sub DataGrid_Select(ByVal sender As Object, ByVal e As
DataGridCommandEventArgs)
Dim ds As DataSet
Session("CurrentID") = e.Item.Cells(8).Text.ToString
Session("CurrentActivityID") = e.Item.Cells(9).Text.ToString
ds = GetCurrentActivity(Session("CurrentActivityID"))
BindDataGrid(DataGrid2, ds, "ActID", "Second")
End Sub

Sub BindDataGrid(ByVal DataGridName As System.Web.UI.WebControls.DataGrid,
ByVal FuncToCall As DataSet, ByVal DGKeyField As String, ByVal DGDataMember
As String)
Dim ds As DataSet
ds = FuncToCall
DataGridName.DataSource = ds 'Define which dataset to be used by
datagrid1
DataGridName.DataKeyField = DGKeyField ' Define a primary key for
the Dataset
DataGridName.DataMember = DGDataMember
DataGridName.DataBind() 'Bind the datagrid to it's datasource
End Sub




Then the user can select a record in the datagrid by clicking the select
button in a button column. This then makes another connection to SQL, opens
the table, fills a dataset, and a datagrid is bound to this dataset. Code:





Function GetCurrentActivity(ByVal CurrentActivityID As String)
Dim SqlCon As New SqlClient.SqlConnection()
Dim DA As New SqlClient.SqlDataAdapter()
Dim SqlCmd As New SqlClient.SqlCommand()
Dim DS As New DataSet()
Dim SQLSelectStatement As String
Dim ColumnNames As String
Dim FromClause As String
Dim WhereClause As String
Dim OrderClause As String
Dim second As New DataTable()
SqlCon = WebBizDev.Utilities.SQLConnect("Boca2", "USER", "PASSWORD",
"pursuittest")
ColumnNames = WebBizDev.Utilities.SQLColumnNames("BDCallsMade as
'Calls', BDEmailsSent as 'Emails', activity_ID as ActID, company.company as
Company, date as 'Activity Date', followup as 'Follow Up', notes as Notes,
notes_confidential as Confidential")
FromClause = WebBizDev.Utilities.SQLFromClause("activity Activity
INNER JOIN Company ON Activity.Client_Company_ID = Company.Company_ID")
WhereClause = WebBizDev.Utilities.SQLWhereClause("(activity_cd = 2
and client_id =" & Session("CurrentID") & ")")
OrderClause = WebBizDev.Utilities.SQLOrderClause("date Desc")
SqlCmd.CommandText = "Select " & ColumnNames & FromClause &
WhereClause & OrderClause
SqlCmd.Connection = SqlCon
DA.SelectCommand = SqlCmd
DA.Fill(DS, "Second")
Return DS
End Function



Once again, I am creating a SQLDataAdapter and filling the dataset in this
function, then returning the dataset to the calling sub and binding the
datagrid to the returned DS. This uses the datagrid_select sub.

My questions:
Should there be only one data adapter?
Should there be only one dataset with multiple datatables?
Can I reuse these objects and if so, how? This is probably the most
critical question. Example code would be very helpful.
For example, I fiill the dataset in GetPersonRecords and create a datatable
called "First". How can I use that dataset again and add the additional
datatable, "Second", which is used in GetCurrentActivity?


I am not having any problems displaying or selecting records.
The whole reason for these questions is I need to update these records and I
would like to use the DataAdapter.UpdateCommand instead of ExecuteNonQuery.
The record selected in the first datagrid (a person) has corresponding
activity detail which is displayed in the second datagrid. I need to be
able to edit the data that is in both datatables, and I think the way to do
this is with a datarelation between the two datatables in the dataset.
I am also perfoming a join in my sql select clauses. Perhaps it is better
to create datatables and relationships within my dataset than to use sql
joins. Is this a correct assumption?

I just need some help understanding the correct implementations of the
SQLDataAdapter, DatSet, DataTable, and DataRelation.

I know there is a lot here and I appreciate your time.

Thanks for your time,
Aaron

 

[Scott Allen]

The typical rule of thumb in server side development is to create just
the objects you need when you need them and consume as few resources
on the server as you can.

My understanding of your scenario is the user selects some criteria,
clicks a button and sees a single DataGrid of PersonRecords. The user
clicks another button and sees a single DataGrid of a person's
activity. If you wanted a single DataSet with multiple DataTable
objects to cover both scenarios, you would always be keeping the
DataSet around or populating the DataSet with information the
application won't display during the current request. If this is the
case I'd lean towards your current solution as it is easier to
implement.

 

[William Ryan eMVP]

1) That really depends but you can probbaly do what you want with a lot
less code and querying. First, you can definitely do this with only one
dataset. Since you are in ASP.NET< you'll probably need to store it in
Session state or cache it so you can reference it

Get rid of all of that join syntax unless you are REALLY sure you need it.
Instead, pull the data over seperately and use a datarelation
http://www.knowdotnet.com/articles/datarelation.html. Now, use a where
clause on the child table so you don't pull over every single record and
ones that aren't in the parent. You don't need anything in the child table
that isn't in the parent, so use the Where clause to pull this off. Also,
Make sure you don't have any child records that aren't in the parent or you
won't be able to apply the relation. One dataset with related tables will
serve your needs.

Once you have the data in the tables. you can use datatable select,
DataTable.Compute, DataView.Rowfilter
http://www.knowdotnet.com/articles/dataviews1.html
http://www.knowdotnet.com/articles/expressions.html
http://www.knowdotnet.com/articles/adopartiii.html
to filter and sort data. If you already have the data in a table, AVOID
making another trip to the db to get it in a different form or to get a
filtered form of it at All costs. It's wasteful enough on the desktop and
it's exacerbated when you deal with restricted bandwidth that's commonplace
on the web

I think this answers one and Two.

You can store the dataset in Session state. When you first get the dataset
back, you can store it in Session state like this:

Session("SomeVariable") = myDataSet

Then, to turn session variable back into a dataset, use
CType(Session("SomeVariable"), DataSet)

With CTYpe, you can reference the variable just like its a dataset (it is)
and the tables collection is easy to access Dim s as String =
CType(Session("SomeVariable"), DataSet).Tables[0].Rows.Count.ToString

You can add a table to it to:

SecondDataAdapter.Fill(CType(Session("SomeVariable"), Dataset),
"TableNameYouWantToFillOrRead")

You can get carried away with Session state, but it's pretty safe to use
unless your datasetss are ridiculously large and even then, it depends on
the server. If you find it's too muich (and I've used some pretty big
datasets in my day [10,000 + rows total] w/ 80 plus users b/c people often
used the app incorrectly or tried grabbing legacy data for 6 months from the
web]) and haven't had any issues. that's not to say it's ok to be reckless,
but I'm saying you have a good deal of flexibility here.

I'd definitely take a step back and read up a little on ADO.NET b/c you
don't want to build something that 'works' ok in test, only to find it
perform really bad in production and then have to redo it (or, if a mananger
or consultant proposes a solution, add 30 indexes to your table which is
the manger fix for every performance problem ;-))

Let me know if you have any problems or questions, I'll be glad to help.

Cheers,

Bill

 

[Aaron]

Thanks for the advice and insight, Bill. I am currently reading David
Sceppa's ADO.NET core reference, and there is a ton of great information to
be found. In my reading I have found there are usually several different
ways to accomplish a given task. I wasn't sure if the approach I am using
is the best way to do what I need done, and obviously, it's not.

I will certainly check the links you have provided and I appreciate your
willingness to help.

I'm gonna keep reading this book, as well, and hopefully I'll get all of
the pieces put together.

Thanks,
Aaron

 

[William Ryan eMVP]

Let me know if I can be of any help. David's book will defintely help you.
He has a section (can't remember the chapter) where he goes into the issue
of Joins vs. DataRelations, it's definitely worth reading (as is the rest of
the book)