Sorry about that. I misunderstood. I may end up overwhelming you with this
response (apologies in advance)
Overall, the box that MS has painted is a bit small. It works well with
certain items, but fails on others. When you start working with
relationships, I find it easier to build a custom DAL rather than relying on
MS's stuff. Please note that I am not bagging on MS's implementation, just
realizing it does not fit all sizes.
There are a couple of directions I find easier to use, in the long run. One
is to use a tool like LLBLGen Pro (
http://www.llblgen.com/) or setting up
the schema as a full dataset and only using the parts you desire (Persistent
DataSets method -
http://www.lastcomponent.com/). You can do the same if you
desire to save money on the toolset. I have found using attributes to figure
out which order to load in is very useful.If your database is not finished,
you are best to code generate the order attribute (custom attribute that
dictates which order the items are loaded into the database when new rows
are created) from the dependencies in the database. This works great as long
as you NEVER have circular references in the database (yes, it happens some
times, esp. when one makes functions that touch tables and uses these
functions in triggers - if you are saying WTF, so was I ;->).
If you go with custom business objects, it is often useful to use GUIDs on
the objects and allow the identity column to update on the object(s) (both
parent and child) when the rows are saved (parent first, child second). In
fact, GUIDs, while a pain when dinking at a database, are very useful when
setting up databases that have to be consumed in the manner described in
this thread. The database does not care if you create the GUID or it does.
No matter which way you head, you are likely going to have to explicitly
state all of your commands rather than allowing .NET to auto-generate them
for you. The CommandBuilder (the bits that create INSERT, UPDATE and DELETE
when a SELECT is stated but the rest are not) is okay at generating
commands, but falls apart in too many instances. If you take control of
this, you will be better off.
Oh, one more suggestion. If you do a search you can find CodeSmith, along
with a plethora of free templates. Most will work with the free version of
CodeSmith, if you can still find a copy out there. The pay version is not
too expensive for a business, of course, but may be costly if just learning,
a student or a one man shop. There are templates for generating the standard
CRUD (Create, Read, Update, Delete aka INSERT, SELECT, UPDATE and DELETE).
Thes estored procs can then be used with DataAdapters, et al, and save you
some heartache. You may want to edit the INSERT template on tables with an
IDENTITY to INSERT without the primary key and let it autogen.
I have probably typed your head off now. Hope at least some of it makes
sense.
MORE INLINE
--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
*************************************************
Think outside the box!
*************************************************
John said:
Greg - I think you are misunderstanding my question. TCATEGORYPARENTID is
not my primary key, it's a foreign key looping back on the same table, so
essentially it is a foreign key. The primary key is TCATEGORYID.
Basically my question is: can I do what I want to do with *one unique*
SqlDataSource?
I will have to test this. Not extremely fond of SqlDataSource myself, as it
tries to do too much. Great for proof of concept, but not much use, IMO,
after that. Sorry guys.
I have tried using a DataAdapter and it also doesn't work. It seems that a
parameter value of NULL is purely and simply not supported. Is this
correct?
As long as it is identity and you custom write the INSERT, the DataAdapter
works. If you use the auto-gened version, it will require all fields from
the SELECT and blow up on the NULL. There are ways around this, but it is
better to code your own INSERT (or preferably, your own INSERT stored
procedure) and call it instead of the auto-gened version.
using (SqlConnection conn = new
SqlConnection(SqlDataSource1.ConnectionString))
{
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT * FROM TCATEGORY WHERE
TCATEGORYPARENTID = @TCATEGORYPARENTID");
cmd.Connection = conn;
SqlParameter p = new SqlParameter("@TCATEGORYPARENTID",
SqlDbType.UniqueIdentifier);
p.Value = DBNull.Value;
cmd.Parameters.Add(p);
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
int c = ds.Tables[0].Rows.Count; // result = 0 here
}
Thanks!
Cowboy (Gregory A. Beamer) said:
Explicitly declare the insert commands (the only time you will have a
null on a primary key) and avoid using the automagically generated (and
completely uncontrollable) command created when you ONLY specify a SELECT
command (ie, the CommandBuilder builds commands for you).
If you want this illustrated, write out the insert command on the
DataAdapter and see that it requires all fields by default. Since you
have a null, the insert will fail. Change this insert to not require the
primary key (identity) and you will be fine.
Good luck!
--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
*************************************************
Think outside the box!
*************************************************
John said:
Hi,
I just cannot manage to perform a SELECT query with NULL parameter...
My TCATEGORY table does have one row where TCATEGORYPARENTID is null
(real DB null value).
TCATEGORYID and TCATEGORYPARENTID are uniqueidentifier columns.
My questions:
- is Type="Object", below, necessary?
- what shall I specify for DefaultValue in my function? I tried
everything. How come the DefaultValue must be a string? Why can't I
specify DBNull.Value?
- How do I make this work... do I have to maintain two different
SqlDataSources, one with TPARENTCATEGORYID IS NULL in the select
statement, the other one with a parameter? Please let me know if this is
what I have got to do.
Thanks for your help.
-J
Code follows:
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:MyBaseConnectionString %>"
SelectCommand="SELECT [TCATEGORYID], [[TCATEGORYNAME] FROM [TCATEGORY]
WHERE ([TCATEGORYPARENTID] = @TCATEGORYPARENTID) ORDER BY
[TCATEGORYPARENTID], [TCATEGORYNAME]">
<SelectParameters>
<asp
arameter Name="TCATEGORYPARENTID" Type="Object" />
</SelectParameters>
</asp:SqlDataSource>
private void QueryCategory()
{
SqlDataSource1.SelectParameters["TCATEGORYPARENTID"].DefaultValue =
System.Data.SqlTypes.SqlGuid.Null.ToString();
DataView dv =
(DataView)SqlDataSource1.Select(DataSourceSelectArguments.Empty);
if (dv != null) // I always end up with either dv null or
dv.Table.Rows.Count = 0 :-(
{
foreach (DataRow row in dv.Table.Rows)
{
[...]
}
}
}
