Amazming....but curious...

[Guest]

Hi,

Enviornment : Visual Studio .NET 2005 and SQL Server 2005

I created one stored procedure, let's say GetData()...Basically, what it
does is that it just returns data from table. (Select [A], from [Test]
where [email protected] like this...)

I created a strongly typed data set with that table, and set the GetData
procedure for Fill Query in table adapter....

It works fine....
there are two questions come to my mind...

1. When I tested to insert some records through generated dataset, it
worked.(I don't have any store procedure for insertion) It makes sense, but
is this insertion done by store procedure or just SQL text?...I guess SQL
query text...It seems to me that insert query automatically was created based
on the selected data(from GetData proceudre)....Does it mean that if I use
strongly typed data set with stored proceudre, am I losing some benefit of
store procedure?.....or insertion store procedure just created on the fly???

2. Once schema has been generated, what if the underlying database changes
so that I need to update the schema to generate new class??....Only way I
found the easiest way is dropping the table again on the XSD designer after
removing the old one......That's not very desirable way..too much work...set
any parameters again....Is there any way to synchronize with the DB easy and
efficiently?

Thanks ,

...................................................TJ

 

[Cowboy \(Gregory A. Beamer\)]

You can create a code generator to create the XSD, but there is nothing
built-in, in Visual Studio, that will sync contents.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
http://gregorybeamer.spaces.live.com

*********************************************
Think outside the box!
*********************************************

 

[Cowboy \(Gregory A. Beamer\)]

One more thing:
If you are using stored procedures for all of your work, it should not
matter, as you are explicitly choosing the columns. As long as you do not
remove an item, you are in complete control. If you add to schema, you can
still use the old method until you are ready to update the procs and the
XSDs.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
http://gregorybeamer.spaces.live.com

*********************************************
Think outside the box!
*********************************************

 

[Guest]

Hi,

Thanks for your answer.
I know that using sp or sql text does not matter with strongly typed dataset.
It seems that SQL query(insert,update or delete...) is automatically created
based on the seleted data(through my stored procedure) in order to perform
other operations.

What I was curious is that .... I guess store procedure is different story.
in order to execute by sp, sp should exist in the databse. However, strong
typed dataset was be able to query insert or update without the sp in my
databse. (My database has only select query in the sp) I guess it queries by
generated insert or update sql text based on the selected data(through select
sp) ......if this is true, what if I want to exeute the insert or update
query by my store procedure instead of auto-generated sql query? Any way I
can do this?... Generally, sp is performance better than sql text I
believe.......Hope you understand what I was curious and asking...

Thanks,

One more thing:
If you are using stored procedures for all of your work, it should not
matter, as you are explicitly choosing the columns. As long as you do not
remove an item, you are in complete control. If you add to schema, you can
still use the old method until you are ready to update the procs and the
XSDs.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
http://gregorybeamer.spaces.live.com

*********************************************
Think outside the box!
*********************************************

Hi,

Enviornment : Visual Studio .NET 2005 and SQL Server 2005

I created one stored procedure, let's say GetData()...Basically, what it
does is that it just returns data from table. (Select [A], from [Test]
where [email protected] like this...)

I created a strongly typed data set with that table, and set the GetData
procedure for Fill Query in table adapter....

It works fine....
there are two questions come to my mind...

1. When I tested to insert some records through generated dataset, it
worked.(I don't have any store procedure for insertion) It makes sense,
but
is this insertion done by store procedure or just SQL text?...I guess SQL
query text...It seems to me that insert query automatically was created
based
on the selected data(from GetData proceudre)....Does it mean that if I use
strongly typed data set with stored proceudre, am I losing some benefit of
store procedure?.....or insertion store procedure just created on the
fly???

2. Once schema has been generated, what if the underlying database changes
so that I need to update the schema to generate new class??....Only way I
found the easiest way is dropping the table again on the XSD designer
after
removing the old one......That's not very desirable way..too much
work...set
any parameters again....Is there any way to synchronize with the DB easy
and
efficiently?

Thanks ,

..................................................TJ

 

[Cowboy \(Gregory A. Beamer\)]

Performance:
Stored Procedures can run faster than a lot of generated code. There are
exceptions, however. If you use parameterized queries, you will find that
SQL "on the fly" can actually be faster if you hit a condition that forces
procedure recompile or replan. That is not normal, but it does happen. Franz
Bouma (LLBLGen Pro) has written some good articles on this.

General:
The drag and drop bits in Visual Studio are great. I love table adapters,
etc., but there are certainly instances where you outgrow the drag and drop.
In these cases, it is better to take control.

Now, if you want an easy way to keep objects and database in sync, consider
a code generation tool. LLBLGen Pro (mentioned above) creates nice objects
and allows you to customize easily. NHibernate is also rather nice. Both are
aimed more at objects than DataSets. If you want a DataSet ORM, there are
tools like Persistable DataSets. The idea here is to regen the data bits
every time you have a schema change. Realize,however, that deletions from
schema can still cause problems, especially if you are binding that data.
Additions are not a big issue, overall.

I will state a word of caution here. I used .NET Tiers (an open source
template for CodeSmith) on a recent project. It was done because a) it was
quick and b) it was free (I already had CodeSmith). I argued against it, but
the finance guys have a way of forcing the issue. Regardless, it is what it
is. On the positive side, it works fine. On the negative, regenerating a
data layer is a pain in the butt. It is not impossible, but communication
becomes key unless you regen the layer every day. There is also no way to
set up .NET Tiers for Continuous Integration, so you cannot autogen when
there is a change.

If you have custom needs, you can create your own code generator.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
http://gregorybeamer.spaces.live.com

*********************************************
Think outside the box!
*********************************************

Hi,

Thanks for your answer.
I know that using sp or sql text does not matter with strongly typed
dataset.
It seems that SQL query(insert,update or delete...) is automatically
created
based on the seleted data(through my stored procedure) in order to perform
other operations.

What I was curious is that .... I guess store procedure is different
story.
in order to execute by sp, sp should exist in the databse. However, strong
typed dataset was be able to query insert or update without the sp in my
databse. (My database has only select query in the sp) I guess it queries
by
generated insert or update sql text based on the selected data(through
select
sp) ......if this is true, what if I want to exeute the insert or update
query by my store procedure instead of auto-generated sql query? Any way I
can do this?... Generally, sp is performance better than sql text I
believe.......Hope you understand what I was curious and asking...

Thanks,

One more thing:
If you are using stored procedures for all of your work, it should not
matter, as you are explicitly choosing the columns. As long as you do not
remove an item, you are in complete control. If you add to schema, you
can
still use the old method until you are ready to update the procs and the
XSDs.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
http://gregorybeamer.spaces.live.com

*********************************************
Think outside the box!
*********************************************

Hi,

Enviornment : Visual Studio .NET 2005 and SQL Server 2005

I created one stored procedure, let's say GetData()...Basically, what
it
does is that it just returns data from table. (Select [A], from
[Test]
where [email protected] like this...)

I created a strongly typed data set with that table, and set the
GetData
procedure for Fill Query in table adapter....

It works fine....
there are two questions come to my mind...

1. When I tested to insert some records through generated dataset, it
worked.(I don't have any store procedure for insertion) It makes sense,
but
is this insertion done by store procedure or just SQL text?...I guess
SQL
query text...It seems to me that insert query automatically was created
based
on the selected data(from GetData proceudre)....Does it mean that if I
use
strongly typed data set with stored proceudre, am I losing some benefit
of
store procedure?.....or insertion store procedure just created on the
fly???

2. Once schema has been generated, what if the underlying database
changes
so that I need to update the schema to generate new class??....Only way
I
found the easiest way is dropping the table again on the XSD designer
after
removing the old one......That's not very desirable way..too much
work...set
any parameters again....Is there any way to synchronize with the DB
easy
and
efficiently?

Thanks ,

..................................................TJ