Better or worse mouse trap?

[Paul]

Hi, I'm building a database project and have just started getting away
from my newbie habit of storing, say, User names in a User field, as
text. I've now replaced the Name field with a NameID field and map this
field as a Foreign Key to a User table where it is a PK. I'm trying to
build the app based upon a real relational model. This will save a lot
of space in the Name field and other fields as the ID integers replace
the text strings.

The problem I have with it is the seeming waste of processing power
during binding, and how often that happens. The main table binds to a
DataGrid, and I can't now just bind each ItemTemplate cell to the
corresponding text in the main table. Now I have to call a code-behind
function during binding with the ID as an argument and let the function
figure out the mapping between the ID and Name, and then send the Name
back to the ASP.NET for presentation.

Then this has to happen again in EditMode when several DropDownLists get
into the act, and again, I call a code-behind function to figure out
that text belongs in the SelectedItem.


This is not large data; it's small data, maybe a couple of thousand rows
with 12-15 fields each.

In a way this all seems more wasteful of processor resources than what
I'm saving in unused disk storage.

Is this a better mouse trap or a worse one? I guess I'm doing it the
"right" way, with regard to using a relational model. I'm just not sure
it's worth it. Thanks for you opinion. ~Paul

 

[Darrin J Olson]

I think you'll find that doing it the new way you have described, using a pk
reference, will make things work smoother in programming and processing as
you go along. Not only with relational database modeling, but also when you
are creating objects that relate to one another.

-Darrin

 

[Nikerz Inc]

Hi Paul,

Would you have some code to share, I have a feeling that your data adapter
is pulling too much information at once, there is a few ways to shorten your
selection with a query of a sort and acutally using parameters to pass to
your query to make it more efficient. As for binding the textboxes I might
have some information for you as well incase you even want to do some
updates and such.

I'll keep an eye on this thread, just reply and I'd like to help out where I
can.

Sincerly,

Nikerz Inc

 

[Michael Mayer [C# MVP]]

In case you don't have a chance to post code here, here's a url that might
help you.

http://www.w3schools.com/sql/sql_join.asp

Basically, you should let the database join the id to the name. You probably
should not be writing code in the code-behind to do it.

The simplest code is something like this: (for the above example of
employees and orders).

SELECT Employees.Name as Name, Orders.Product as Product
FROM Employees, Orders
WHERE Employees.Employee_ID=Orders.Employee_ID

I would use something simple like that at first, then read up on the JOIN
keyword.

The datatable / dataset returned from the database will then have a column
Name and a column Product that you can just bind to as easily as when name
was stored in the same table as orders. Moving user names to a dedicated
users table should have very little affect on your web code (other than
changing the sql statement or stored procedure used to get the information).


- Mike Mayer, C# MVP

 

[Paul]

Hi, Nike, and thanks for offering to check out my code. I hope there is
a tweak or two that might make it more efficient. Starting with binding
in ASP.NET, a TemplateColumn with a DDL (DropDownList) whose purpose is
to store the string for the Creator user who created the record in the
main table.

<asp:TemplateColumn SortExpression="CreatorID" HeaderText="Creator">
<ItemTemplate>
<%# GetCreatorNames(DataBinder.Eval(Container.DataItem, "CreatorID")) %>
</ItemTemplate>

(Comment: Note that I call GetCreatorNames() in the code-behind. The
ItemTemplate has read in an ID Integer and the function will return its
corresponding string. The function is next.)


Dim dtAllUsers As DataTable = CType(Session("dtAllUsers"), DataTable)
Dim x As Integer
Dim Counter As Integer = dtAllUsers.Rows.Count
Dim NameStr As String

For x = 0 To Counter - 1

If dtAllUsers.Rows(x)(0) = CreatorID Then

NameStr = dtAllUsers.Rows(x)(1)
Return NameStr
Exit For

End If
Next

(Comment: You see that I have a table with all users in it and that I go
through it row by row looking for a match for the CreatorID. When I
find it I return the corresponding string. Then the ASP.NET increments
to the next row and does the same thing. What I'm afraid of is that by
searching the table for every row in the main table I am wasting a lot
of time. I experimented with returning other objects, like a collection
of strings that have the user strings in correct order for the main
table. Of course, that doesn't work because I am in the middle of
binding one cell, not a whole column at once. Bah, humbug. I need to
return a single string. So I'm hoping there is a way to build a
collection or array and return only one at a time. The problem is with
this reentrant nature of repeatedly calling the function, all the
objects get recreated fresh each time and I am working with a collection
from the first item again.

I must be missing something here, some way to make the process static.
Then I could avoid searching the AllUsers table each time I needed to
bind a silly single cell.

I won't post the EditItemTemplate code because I'm happy with it. That
involves a DDL that has a DataSource which I make a table of names.
That solves the problem of all the Returns for each name.

Thanks for getting this far, Nike. I'll be checking back later but my
ISP has to bounce our account again, and maybe even recreate our account
so I may seem to disappear for a few days! I'll be bahck. ~Paul

 

[Paul]

Mike,
Will your suggestion work with a DataGrid column? The DG is bound to a
MS SQL table, and I didn't think you could specify another DataSource
for one column only. This is a TemplateColumn too. I posted the code
I'm using in another record in this thread. It appears to me that when
binding through ASP.NET with a DG TemplateColumn that I have use the
DataSource that the DG uses for all columns. No?

I have used SQL to return the mapped names to the IDs and to stick them
in a separate table. The problem is that I can't find a way to bind one
column to a column in that table. I don't want to say "it can't be
done" but it appears that way from my limited understanding.

The code I posted should be in the record before this one. I'd
appreciate your comments. ~Paul

 

[Michael Mayer [C# MVP]]

Mike,
Will your suggestion work with a DataGrid column?

Sure it will. But you'll probably want to change your binding

The DG is bound to a
MS SQL table, and I didn't think you could specify another DataSource
for one column only

You don't want to bind directly to a table in MS SQL server, insteady, you
need to bind to either a stored procedure, or use your own SQL in a data
adapter. Microsoft advocates stored procedures, but you don't have to use
them

Follow this walk-through:
http://msdn.microsoft.com/library/d...layingDataInGridInWebFormsPage.asp?frame=true

During the data adapter wizard, either create a new Stored Procedure or
generate a sql statement something like this (see also my previous message
in this thread about SQL JOIN, that might be closer to what you want)

SELECT
dtAllUsers.Name as Name ** (or whatever the Name column is)
** list columns here that you want from "othertable"
** or use othertable.*
FROM **othertable**, dtAllUsers
WHERE **othertable**.UserId = dtAllUsers.UserId

Follow the rest of the steps to create and bind a dataset and then the
datagrid. In your datagrid templates, you should be able to bind to "Name"
just as easily as any other column of "othertable"

Hope that makes sense and achieves what you want (unless I've missed your
requiremnets altogether). Let me know if you have any more questions.

 

[Paul]

Mike, forgot to mention that I had never worked with the "AS" property
in a SQL query, so I'm now figuring out why your sample query used it.
Very slick, it apparently adds the column to the table and I can bind to
it in the ASP.NET. I haven't done it yet but wanted you to know that I
wasn't ignoring that sample you gave me. ~Paul

 

[Paul]

Mike, so sorry I misspoke when I said that I bind directly to a MS SQL
table. Not true. In code-behind I bind to a dataset that is read
through a DataAdaptor. My confusion is that I also bind in ASP.NET for
the main/startup table, the one I'm having trouble with. To me, that
looked like it was binding directly to the SQL table but it must be
using the code-behind DataAdaptor and SQL query to do that (murky on how
and what sequence it works that way.) So, my problem is strictly to do
with the SQL query that the DataAdaptor uses, I guess, after reading
what you had to say. Quickly, here is the binding statement from the
ASP.NET, followed by the code-behind where I map the CreatorID in the
main table to its corresponding string from the AllUsers table:

***********************
<asp:TemplateColumn SortExpression="CreatorID" HeaderText="Creator">
<ItemTemplate>

<%# GetCreatorNames(DataBinder.Eval(Container.DataItem, "CreatorID")) %>
</ItemTemplate>
*************************
Function GetCreatorNames(ByVal CreatorID As Integer)
For x = 0 To Counter - 1

If dtAllUsers.Rows(x)(0) = CreatorID Then

NameStr = dtAllUsers.Rows(x)(1)
Return NameStr
Exit For

End If
Next
***************************

You can see the kludge effect! The problem is that the ASP.NET
ItemTemplate is binding to a table that has the CreatorID in it, where I
need the CreatorName string. Do this make sense?

Now, if my SQL query builds the dataset with a main table that has a
CreatorName or Name column, can I then just refer to it instead of
CreatorID in the DataBinder.Eval statement in my above ItemTemplate? I
think that will work so that I can get rid of this code-behind kludge.
Yes, no?

Thanks for your patience. ~Paul

 

[Michael Mayer [C# MVP]]

Now, if my SQL query builds the dataset with a main table that has a
CreatorName or Name column, can I then just refer to it instead of
CreatorID in the DataBinder.Eval statement in my above ItemTemplate? I
think that will work so that I can get rid of this code-behind kludge.
Yes, no?

Yes - should be. As your previous post mentioned, AS is cool in that it
creates an alias of a column - and that column might be in another table. So
you can do:

Users.Username AS Username

and then just bind to Username in your templates

 

[Paul]

Mike, I'm up and running fine now. Wow. Creating the "AS" alias column
and mapping it to the table with the names and IDs did the trick.
Thanks so much. It feels totally excellent to see this working. This
is tons better than that code-behind kludge.

Thanks again, Mike!

~Paul