OK, the application I'm writing has 50+ tables and some of the tables have
200+ fields (government standard, not my choice -- its about as denormalized
as is possible).  We will be distributing this database and our application
on hundreds of machines and I'm worried that writing stored procedures for
every insert, update, and delete for each of these tables and maintaining
them over the course of years is going to be extremely difficult (the
standards change all the time, an extra field here, modify this here,etc).
I'm thinking of taking the hit in performance and writing 1 stored procedure
(for insert, update, or delete) that will dynamically build the
corresponding statement and only worry about changing the tables/fields.
Does anybody know of a better way?  Performance is important, but its not
essential to squeeze the database for everything its worth.  The current
iteration of our program (which isn't written in .NET) is horribly
inefficient and the sp that I made to dynamically build the datagrids to
display the data is a couple of orders of magnitude better in performance
than it.

Thanks,
Kevin

I think dynamic sql will increase the complexity of problems you'll have
to deal with down the road.

The only situation I know of where dynamic sql is generally preferable
is when you need to let people do parameter based searches.


-----Original Message-----
From: Kevin Ayers [mailto:kevin@f...] 
Sent: Tuesday, February 25, 2003 4:56 PM
To: ADO.NET
Subject: [ado_dotnet] dynamic vs static sql


OK, the application I'm writing has 50+ tables and some of the tables
have
200+ fields (government standard, not my choice -- its about as 
200+ denormalized
as is possible).  We will be distributing this database and our
application on hundreds of machines and I'm worried that writing stored
procedures for every insert, update, and delete for each of these tables
and maintaining them over the course of years is going to be extremely
difficult (the standards change all the time, an extra field here,
modify this here,etc). I'm thinking of taking the hit in performance and
writing 1 stored procedure (for insert, update, or delete) that will
dynamically build the corresponding statement and only worry about
changing the tables/fields. Does anybody know of a better way?
Performance is important, but its not essential to squeeze the database
for everything its worth.  The current iteration of our program (which
isn't written in .NET) is horribly inefficient and the sp that I made to
dynamically build the datagrids to display the data is a couple of
orders of magnitude better in performance than it.

Thanks,
Kevin


===
Fast Track ADO.NET with C# is a concise introduction to the concepts,
techniques, and libraries that you will need in order to start using
ADO.NET in your applications. The book covers DataSets and Typed
DataSets, accessing data using DataReaders and DataAdaptors, the close
relationship between ADO.NET and XML, how and where to use ADO.NET in
your enterprise applications, and how to use Web Services and ADO.NET to
easily pass data between applications.
http://www.wrox.com/books/1861007604.htm

I'd agree normally, and in fact I have since convinced my manager to have
stored procs for most of the stuff we need.  I think we're going to just
have VB.NET generate the stored procs and then modify them to our need.
It'll be more work, but I think the entire usability and flexibility of it
won out (plus I'm the only person who knows how to generate all of the
dynamic sql anyway and I have to write the rest of the application as well).

Kevin
----- Original Message -----
From: "Alex Smotritsky" <alex.smotritsky@v...>
To: "ADO.NET" <ado_dotnet@p...>
Sent: Tuesday, February 25, 2003 10:38 PM
Subject: [ado_dotnet] RE: dynamic vs static sql


> I think dynamic sql will increase the complexity of problems you'll have
> to deal with down the road.
>
> The only situation I know of where dynamic sql is generally preferable
> is when you need to let people do parameter based searches.
>
>
> -----Original Message-----
> From: Kevin Ayers [mailto:kevin@f...]
> Sent: Tuesday, February 25, 2003 4:56 PM
> To: ADO.NET
> Subject: [ado_dotnet] dynamic vs static sql
>
>
> OK, the application I'm writing has 50+ tables and some of the tables
> have
> 200+ fields (government standard, not my choice -- its about as
> 200+ denormalized
> as is possible).  We will be distributing this database and our
> application on hundreds of machines and I'm worried that writing stored
> procedures for every insert, update, and delete for each of these tables
> and maintaining them over the course of years is going to be extremely
> difficult (the standards change all the time, an extra field here,
> modify this here,etc). I'm thinking of taking the hit in performance and
> writing 1 stored procedure (for insert, update, or delete) that will
> dynamically build the corresponding statement and only worry about
> changing the tables/fields. Does anybody know of a better way?
> Performance is important, but its not essential to squeeze the database
> for everything its worth.  The current iteration of our program (which
> isn't written in .NET) is horribly inefficient and the sp that I made to
> dynamically build the datagrids to display the data is a couple of
> orders of magnitude better in performance than it.
>
> Thanks,
> Kevin
>
>
> ===
> Fast Track ADO.NET with C# is a concise introduction to the concepts,
> techniques, and libraries that you will need in order to start using
> ADO.NET in your applications. The book covers DataSets and Typed
> DataSets, accessing data using DataReaders and DataAdaptors, the close
> relationship between ADO.NET and XML, how and where to use ADO.NET in
> your enterprise applications, and how to use Web Services and ADO.NET to
> easily pass data between applications.
> http://www.wrox.com/books/1861007604.htm
>
>
> ===
> Fast Track ADO.NET with C# is a concise introduction to the concepts,
techniques, and libraries that you will need in order to start using ADO.NET
in your applications. The book covers DataSets and Typed DataSets, accessing
data using DataReaders and DataAdaptors, the close relationship between
ADO.NET and XML, how and where to use ADO.NET in your enterprise
applications, and how to use Web Services and ADO.NET to easily pass data
between applications.
> http://www.wrox.com/books/1861007604.htm

It's pretty easy to write system-wide stored procedures to generate
standard SELECT, INSERT etc statements for any table. All my stored
procedures start out from generated and template code - it's well worth
the effort.  
Take a look at some of the sample code on www.swynk.com. I

brian

-----Original Message-----
From: Kevin Ayers [mailto:kevin@f...] 
Sent: Wed, 26 Feb 2003 20:17
To: ADO.NET
Subject: [ado_dotnet] RE: dynamic vs static sql


I'd agree normally, and in fact I have since convinced my manager to
have stored procs for most of the stuff we need.  I think we're going to
just have VB.NET generate the stored procs and then modify them to our
need. It'll be more work, but I think the entire usability and
flexibility of it won out (plus I'm the only person who knows how to
generate all of the dynamic sql anyway and I have to write the rest of
the application as well).

Kevin
----- Original Message -----
From: "Alex Smotritsky" <alex.smotritsky@v...>
To: "ADO.NET" <ado_dotnet@p...>
Sent: Tuesday, February 25, 2003 10:38 PM
Subject: [ado_dotnet] RE: dynamic vs static sql


> I think dynamic sql will increase the complexity of problems you'll 
> have to deal with down the road.
>
> The only situation I know of where dynamic sql is generally preferable

> is when you need to let people do parameter based searches.
>
>
> -----Original Message-----
> From: Kevin Ayers [mailto:kevin@f...]
> Sent: Tuesday, February 25, 2003 4:56 PM
> To: ADO.NET
> Subject: [ado_dotnet] dynamic vs static sql
>
>
> OK, the application I'm writing has 50+ tables and some of the tables 
> have
> 200+ fields (government standard, not my choice -- its about as 
> 200+ denormalized
> as is possible).  We will be distributing this database and our 
> application on hundreds of machines and I'm worried that writing 
> stored procedures for every insert, update, and delete for each of 
> these tables and maintaining them over the course of years is going to

> be extremely difficult (the standards change all the time, an extra 
> field here, modify this here,etc). I'm thinking of taking the hit in 
> performance and writing 1 stored procedure (for insert, update, or 
> delete) that will dynamically build the corresponding statement and 
> only worry about changing the tables/fields. Does anybody know of a 
> better way? Performance is important, but its not essential to squeeze

> the database for everything its worth.  The current iteration of our 
> program (which isn't written in .NET) is horribly inefficient and the 
> sp that I made to dynamically build the datagrids to display the data 
> is a couple of orders of magnitude better in performance than it.
>
> Thanks,
> Kevin
>
>
> ===
> Fast Track ADO.NET with C# is a concise introduction to the concepts, 
> techniques, and libraries that you will need in order to start using 
> ADO.NET in your applications. The book covers DataSets and Typed 
> DataSets, accessing data using DataReaders and DataAdaptors, the close

> relationship between ADO.NET and XML, how and where to use ADO.NET in 
> your enterprise applications, and how to use Web Services and ADO.NET 
> to easily pass data between applications. 
> http://www.wrox.com/books/1861007604.htm
> ---
> Change your mail options at http://p2p.wrox.com/manager.asp or to 
> unsubscribe send a blank email to 
>
>
> ===
> Fast Track ADO.NET with C# is a concise introduction to the concepts,
techniques, and libraries that you will need in order to start using
ADO.NET in your applications. The book covers DataSets and Typed
DataSets, accessing data using DataReaders and DataAdaptors, the close
relationship between ADO.NET and XML, how and where to use ADO.NET in
your enterprise applications, and how to use Web Services and ADO.NET to
easily pass data between applications.
> http://www.wrox.com/books/1861007604.htm
> ---
> Change your mail options at http://p2p.wrox.com/manager.asp or to 
> unsubscribe send a blank email to


===
Fast Track ADO.NET with C# is a concise introduction to the concepts,
techniques, and libraries that you will need in order to start using
ADO.NET in your applications. The book covers DataSets and Typed
DataSets, accessing data using DataReaders and DataAdaptors, the close
relationship between ADO.NET and XML, how and where to use ADO.NET in
your enterprise applications, and how to use Web Services and ADO.NET to
easily pass data between applications.
http://www.wrox.com/books/1861007604.htm

---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.449 / Virus Database: 251 - Release Date: 27/01/2003
 

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.449 / Virus Database: 251 - Release Date: 27/01/2003
 

yeah, I'm going that way.  I'm just afraid of a maintenance issue - we'll be
installing this stuff on hundreds if not thousands of computers and
maintaining the dbase is going to be a nightmare already.

Kevin

----- Original Message -----
From: "Brian Smith" <bsmith@l...>
To: "ADO.NET" <ado_dotnet@p...>
Sent: Wednesday, February 26, 2003 3:39 PM
Subject: [ado_dotnet] RE: dynamic vs static sql


> It's pretty easy to write system-wide stored procedures to generate
> standard SELECT, INSERT etc statements for any table. All my stored
> procedures start out from generated and template code - it's well worth
> the effort.
> Take a look at some of the sample code on www.swynk.com. I
>
> brian
>
> -----Original Message-----
> From: Kevin Ayers [mailto:kevin@f...]
> Sent: Wed, 26 Feb 2003 20:17
> To: ADO.NET
> Subject: [ado_dotnet] RE: dynamic vs static sql
>
>
> I'd agree normally, and in fact I have since convinced my manager to
> have stored procs for most of the stuff we need.  I think we're going to
> just have VB.NET generate the stored procs and then modify them to our
> need. It'll be more work, but I think the entire usability and
> flexibility of it won out (plus I'm the only person who knows how to
> generate all of the dynamic sql anyway and I have to write the rest of
> the application as well).
>
> Kevin
> ----- Original Message -----
> From: "Alex Smotritsky" <alex.smotritsky@v...>
> To: "ADO.NET" <ado_dotnet@p...>
> Sent: Tuesday, February 25, 2003 10:38 PM
> Subject: [ado_dotnet] RE: dynamic vs static sql
>
>
> > I think dynamic sql will increase the complexity of problems you'll
> > have to deal with down the road.
> >
> > The only situation I know of where dynamic sql is generally preferable
>
> > is when you need to let people do parameter based searches.
> >
> >
> > -----Original Message-----
> > From: Kevin Ayers [mailto:kevin@f...]
> > Sent: Tuesday, February 25, 2003 4:56 PM
> > To: ADO.NET
> > Subject: [ado_dotnet] dynamic vs static sql
> >
> >
> > OK, the application I'm writing has 50+ tables and some of the tables
> > have
> > 200+ fields (government standard, not my choice -- its about as
> > 200+ denormalized
> > as is possible).  We will be distributing this database and our
> > application on hundreds of machines and I'm worried that writing
> > stored procedures for every insert, update, and delete for each of
> > these tables and maintaining them over the course of years is going to
>
> > be extremely difficult (the standards change all the time, an extra
> > field here, modify this here,etc). I'm thinking of taking the hit in
> > performance and writing 1 stored procedure (for insert, update, or
> > delete) that will dynamically build the corresponding statement and
> > only worry about changing the tables/fields. Does anybody know of a
> > better way? Performance is important, but its not essential to squeeze
>
> > the database for everything its worth.  The current iteration of our
> > program (which isn't written in .NET) is horribly inefficient and the
> > sp that I made to dynamically build the datagrids to display the data
> > is a couple of orders of magnitude better in performance than it.
> >
> > Thanks,
> > Kevin
> >
> >
> > ===
> > Fast Track ADO.NET with C# is a concise introduction to the concepts,
> > techniques, and libraries that you will need in order to start using
> > ADO.NET in your applications. The book covers DataSets and Typed
> > DataSets, accessing data using DataReaders and DataAdaptors, the close
>
> > relationship between ADO.NET and XML, how and where to use ADO.NET in
> > your enterprise applications, and how to use Web Services and ADO.NET
> > to easily pass data between applications.
> > http://www.wrox.com/books/1861007604.htm
> > ---
> > Change your mail options at http://p2p.wrox.com/manager.asp or to
> > unsubscribe send a blank email to 
> >
> >
> > ===
> > Fast Track ADO.NET with C# is a concise introduction to the concepts,
> techniques, and libraries that you will need in order to start using
> ADO.NET in your applications. The book covers DataSets and Typed
> DataSets, accessing data using DataReaders and DataAdaptors, the close
> relationship between ADO.NET and XML, how and where to use ADO.NET in
> your enterprise applications, and how to use Web Services and ADO.NET to
> easily pass data between applications.
> > http://www.wrox.com/books/1861007604.htm
> > ---
> > Change your mail options at http://p2p.wrox.com/manager.asp or to
> > unsubscribe send a blank email to
>
>
> ===
> Fast Track ADO.NET with C# is a concise introduction to the concepts,
> techniques, and libraries that you will need in order to start using
> ADO.NET in your applications. The book covers DataSets and Typed
> DataSets, accessing data using DataReaders and DataAdaptors, the close
> relationship between ADO.NET and XML, how and where to use ADO.NET in
> your enterprise applications, and how to use Web Services and ADO.NET to
> easily pass data between applications.
> http://www.wrox.com/books/1861007604.htm
>
> ---
> Incoming mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.449 / Virus Database: 251 - Release Date: 27/01/2003
>
>
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.449 / Virus Database: 251 - Release Date: 27/01/2003
>
>
>
> ===
> Fast Track ADO.NET with C# is a concise introduction to the concepts,
techniques, and libraries that you will need in order to start using ADO.NET
in your applications. The book covers DataSets and Typed DataSets, accessing
data using DataReaders and DataAdaptors, the close relationship between
ADO.NET and XML, how and where to use ADO.NET in your enterprise
applications, and how to use Web Services and ADO.NET to easily pass data
between applications.
> http://www.wrox.com/books/1861007604.htm

Your only solution is to separate your DAL from your existing code as
much as possible. You might build your own or use one that is off the
shelf.

Some suggestions:

LLBgen
nTierGen
Microsoft Application Blocks



-----Original Message-----
From: Kevin Ayers [mailto:kevin@f...] 
Sent: Tuesday, February 25, 2003 4:56 PM
To: ADO.NET
Subject: [ado_dotnet] dynamic vs static sql

OK, the application I'm writing has 50+ tables and some of the tables
have
200+ fields (government standard, not my choice -- its about as
denormalized
as is possible).  We will be distributing this database and our
application
on hundreds of machines and I'm worried that writing stored procedures
for
every insert, update, and delete for each of these tables and
maintaining
them over the course of years is going to be extremely difficult (the
standards change all the time, an extra field here, modify this
here,etc).
I'm thinking of taking the hit in performance and writing 1 stored
procedure
(for insert, update, or delete) that will dynamically build the
corresponding statement and only worry about changing the tables/fields.
Does anybody know of a better way?  Performance is important, but its
not
essential to squeeze the database for everything its worth.  The current
iteration of our program (which isn't written in .NET) is horribly
inefficient and the sp that I made to dynamically build the datagrids to
display the data is a couple of orders of magnitude better in
performance
than it.

Thanks,
Kevin


===
Fast Track ADO.NET with C# is a concise introduction to the concepts,
techniques, and libraries that you will need in order to start using
ADO.NET in your applications. The book covers DataSets and Typed
DataSets, accessing data using DataReaders and DataAdaptors, the close
relationship between ADO.NET and XML, how and where to use ADO.NET in
your enterprise applications, and how to use Web Services and ADO.NET to
easily pass data between applications.
http://www.wrox.com/books/1861007604.htm

Actually some dynamic SQL will help you if you are making a lot of
similar queries on different tables, not just parameter based searches.

You're greatest challenge will be designing the DAL so it can be
scalable and updatable easily. 


-----Original Message-----
From: Alex Smotritsky [mailto:alex.smotritsky@v...] 
Sent: Tuesday, February 25, 2003 10:39 PM
To: ADO.NET
Subject: [ado_dotnet] RE: dynamic vs static sql

I think dynamic sql will increase the complexity of problems you'll have
to deal with down the road.

The only situation I know of where dynamic sql is generally preferable
is when you need to let people do parameter based searches.


-----Original Message-----
From: Kevin Ayers [mailto:kevin@f...] 
Sent: Tuesday, February 25, 2003 4:56 PM
To: ADO.NET
Subject: [ado_dotnet] dynamic vs static sql


OK, the application I'm writing has 50+ tables and some of the tables
have
200+ fields (government standard, not my choice -- its about as 
200+ denormalized
as is possible).  We will be distributing this database and our
application on hundreds of machines and I'm worried that writing stored
procedures for every insert, update, and delete for each of these tables
and maintaining them over the course of years is going to be extremely
difficult (the standards change all the time, an extra field here,
modify this here,etc). I'm thinking of taking the hit in performance and
writing 1 stored procedure (for insert, update, or delete) that will
dynamically build the corresponding statement and only worry about
changing the tables/fields. Does anybody know of a better way?
Performance is important, but its not essential to squeeze the database
for everything its worth.  The current iteration of our program (which
isn't written in .NET) is horribly inefficient and the sp that I made to
dynamically build the datagrids to display the data is a couple of
orders of magnitude better in performance than it.

Thanks,
Kevin


===
Fast Track ADO.NET with C# is a concise introduction to the concepts,
techniques, and libraries that you will need in order to start using
ADO.NET in your applications. The book covers DataSets and Typed
DataSets, accessing data using DataReaders and DataAdaptors, the close
relationship between ADO.NET and XML, how and where to use ADO.NET in
your enterprise applications, and how to use Web Services and ADO.NET to
easily pass data between applications.
http://www.wrox.com/books/1861007604.htm


===
Fast Track ADO.NET with C# is a concise introduction to the concepts,
techniques, and libraries that you will need in order to start using
ADO.NET in your applications. The book covers DataSets and Typed
DataSets, accessing data using DataReaders and DataAdaptors, the close
relationship between ADO.NET and XML, how and where to use ADO.NET in
your enterprise applications, and how to use Web Services and ADO.NET to
easily pass data between applications.
http://www.wrox.com/books/1861007604.htm

We are doing a mixed approach.  For the most part we wil be using static sql
sp's for individual record's updates, deletes, and full record selection
(getting all the records, not just a few for display).  We will be using a
dynamic sql sp for most of our browse table style controls.  We're doing
this because there are many, many different browse tables and they must be
searchable and very modifiable and dynamic sql is the only viable way of
doing this w/o tons of sp's and a support nightmare.

Kevin

----- Original Message -----
From: "Rahul Singh" <rahul.singh@a...>
To: "ADO.NET" <ado_dotnet@p...>
Sent: Friday, February 28, 2003 7:21 AM
Subject: [ado_dotnet] RE: dynamic vs static sql


> Actually some dynamic SQL will help you if you are making a lot of
> similar queries on different tables, not just parameter based searches.
>
> You're greatest challenge will be designing the DAL so it can be
> scalable and updatable easily.
>
>
> -----Original Message-----
> From: Alex Smotritsky [mailto:alex.smotritsky@v...]
> Sent: Tuesday, February 25, 2003 10:39 PM
> To: ADO.NET
> Subject: [ado_dotnet] RE: dynamic vs static sql
>
> I think dynamic sql will increase the complexity of problems you'll have
> to deal with down the road.
>
> The only situation I know of where dynamic sql is generally preferable
> is when you need to let people do parameter based searches.
>
>
> -----Original Message-----
> From: Kevin Ayers [mailto:kevin@f...]
> Sent: Tuesday, February 25, 2003 4:56 PM
> To: ADO.NET
> Subject: [ado_dotnet] dynamic vs static sql
>
>
> OK, the application I'm writing has 50+ tables and some of the tables
> have
> 200+ fields (government standard, not my choice -- its about as
> 200+ denormalized
> as is possible).  We will be distributing this database and our
> application on hundreds of machines and I'm worried that writing stored
> procedures for every insert, update, and delete for each of these tables
> and maintaining them over the course of years is going to be extremely
> difficult (the standards change all the time, an extra field here,
> modify this here,etc). I'm thinking of taking the hit in performance and
> writing 1 stored procedure (for insert, update, or delete) that will
> dynamically build the corresponding statement and only worry about
> changing the tables/fields. Does anybody know of a better way?
> Performance is important, but its not essential to squeeze the database
> for everything its worth.  The current iteration of our program (which
> isn't written in .NET) is horribly inefficient and the sp that I made to
> dynamically build the datagrids to display the data is a couple of
> orders of magnitude better in performance than it.
>
> Thanks,
> Kevin
>
>
> ===
> Fast Track ADO.NET with C# is a concise introduction to the concepts,
> techniques, and libraries that you will need in order to start using
> ADO.NET in your applications. The book covers DataSets and Typed
> DataSets, accessing data using DataReaders and DataAdaptors, the close
> relationship between ADO.NET and XML, how and where to use ADO.NET in
> your enterprise applications, and how to use Web Services and ADO.NET to
> easily pass data between applications.
> http://www.wrox.com/books/1861007604.htm
>
>
> ===
> Fast Track ADO.NET with C# is a concise introduction to the concepts,
> techniques, and libraries that you will need in order to start using
> ADO.NET in your applications. The book covers DataSets and Typed
> DataSets, accessing data using DataReaders and DataAdaptors, the close
> relationship between ADO.NET and XML, how and where to use ADO.NET in
> your enterprise applications, and how to use Web Services and ADO.NET to
> easily pass data between applications.
> http://www.wrox.com/books/1861007604.htm
>
>
>
> ===
> Fast Track ADO.NET with C# is a concise introduction to the concepts,
techniques, and libraries that you will need in order to start using ADO.NET
in your applications. The book covers DataSets and Typed DataSets, accessing
data using DataReaders and DataAdaptors, the close relationship between
ADO.NET and XML, how and where to use ADO.NET in your enterprise
applications, and how to use Web Services and ADO.NET to easily pass data
between applications.
> http://www.wrox.com/books/1861007604.htm

I hope you're protecting yourself from SQL injection attacks...:-)

brian

-----Original Message-----
From: Kevin Ayers [mailto:kevin@f...] 
Sent: Fri, 28 Feb 2003 13:57
To: ADO.NET
Subject: [ado_dotnet] RE: dynamic vs static sql


We are doing a mixed approach.  For the most part we wil be using static
sql sp's for individual record's updates, deletes, and full record
selection (getting all the records, not just a few for display).  We
will be using a dynamic sql sp for most of our browse table style
controls.  We're doing this because there are many, many different
browse tables and they must be searchable and very modifiable and
dynamic sql is the only viable way of doing this w/o tons of sp's and a
support nightmare.

Kevin

----- Original Message -----
From: "Rahul Singh" <rahul.singh@a...>
To: "ADO.NET" <ado_dotnet@p...>
Sent: Friday, February 28, 2003 7:21 AM
Subject: [ado_dotnet] RE: dynamic vs static sql


> Actually some dynamic SQL will help you if you are making a lot of 
> similar queries on different tables, not just parameter based 
> searches.
>
> You're greatest challenge will be designing the DAL so it can be 
> scalable and updatable easily.
>
>
> -----Original Message-----
> From: Alex Smotritsky [mailto:alex.smotritsky@v...]
> Sent: Tuesday, February 25, 2003 10:39 PM
> To: ADO.NET
> Subject: [ado_dotnet] RE: dynamic vs static sql
>
> I think dynamic sql will increase the complexity of problems you'll 
> have to deal with down the road.
>
> The only situation I know of where dynamic sql is generally preferable

> is when you need to let people do parameter based searches.
>
>
> -----Original Message-----
> From: Kevin Ayers [mailto:kevin@f...]
> Sent: Tuesday, February 25, 2003 4:56 PM
> To: ADO.NET
> Subject: [ado_dotnet] dynamic vs static sql
>
>
> OK, the application I'm writing has 50+ tables and some of the tables 
> have
> 200+ fields (government standard, not my choice -- its about as 
> 200+ denormalized
> as is possible).  We will be distributing this database and our 
> application on hundreds of machines and I'm worried that writing 
> stored procedures for every insert, update, and delete for each of 
> these tables and maintaining them over the course of years is going to

> be extremely difficult (the standards change all the time, an extra 
> field here, modify this here,etc). I'm thinking of taking the hit in 
> performance and writing 1 stored procedure (for insert, update, or 
> delete) that will dynamically build the corresponding statement and 
> only worry about changing the tables/fields. Does anybody know of a 
> better way? Performance is important, but its not essential to squeeze

> the database for everything its worth.  The current iteration of our 
> program (which isn't written in .NET) is horribly inefficient and the 
> sp that I made to dynamically build the datagrids to display the data 
> is a couple of orders of magnitude better in performance than it.
>
> Thanks,
> Kevin
>
>
> ===
> Fast Track ADO.NET with C# is a concise introduction to the concepts, 
> techniques, and libraries that you will need in order to start using 
> ADO.NET in your applications. The book covers DataSets and Typed 
> DataSets, accessing data using DataReaders and DataAdaptors, the close

> relationship between ADO.NET and XML, how and where to use ADO.NET in 
> your enterprise applications, and how to use Web Services and ADO.NET 
> to easily pass data between applications. 
> http://www.wrox.com/books/1861007604.htm
> ---
> Change your mail options at http://p2p.wrox.com/manager.asp or to 
> unsubscribe send a blank email to 
>
>
> ===
> Fast Track ADO.NET with C# is a concise introduction to the concepts, 
> techniques, and libraries that you will need in order to start using 
> ADO.NET in your applications. The book covers DataSets and Typed 
> DataSets, accessing data using DataReaders and DataAdaptors, the close

> relationship between ADO.NET and XML, how and where to use ADO.NET in 
> your enterprise applications, and how to use Web Services and ADO.NET 
> to easily pass data between applications. 
> http://www.wrox.com/books/1861007604.htm
> ---
> Change your mail options at http://p2p.wrox.com/manager.asp or to 
> unsubscribe send a blank email to 
>
>
>
> ===
> Fast Track ADO.NET with C# is a concise introduction to the concepts,
techniques, and libraries that you will need in order to start using
ADO.NET in your applications. The book covers DataSets and Typed
DataSets, accessing data using DataReaders and DataAdaptors, the close
relationship between ADO.NET and XML, how and where to use ADO.NET in
your enterprise applications, and how to use Web Services and ADO.NET to
easily pass data between applications.
> http://www.wrox.com/books/1861007604.htm
> ---
> Change your mail options at http://p2p.wrox.com/manager.asp or to 
> unsubscribe send a blank email to


===
Fast Track ADO.NET with C# is a concise introduction to the concepts,
techniques, and libraries that you will need in order to start using
ADO.NET in your applications. The book covers DataSets and Typed
DataSets, accessing data using DataReaders and DataAdaptors, the close
relationship between ADO.NET and XML, how and where to use ADO.NET in
your enterprise applications, and how to use Web Services and ADO.NET to
easily pass data between applications.
http://www.wrox.com/books/1861007604.htm

---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.449 / Virus Database: 251 - Release Date: 27/01/2003
 

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.449 / Virus Database: 251 - Release Date: 27/01/2003
 

If you're referring to placing sql commands in the search criteria, then yes
those are already covered pretty well.

Kevin

----- Original Message -----
From: "Brian Smith" <bsmith@l...>
To: "ADO.NET" <ado_dotnet@p...>
Sent: Friday, February 28, 2003 10:56 AM
Subject: [ado_dotnet] RE: dynamic vs static sql


> I hope you're protecting yourself from SQL injection attacks...:-)
>
> brian
>
> -----Original Message-----
> From: Kevin Ayers [mailto:kevin@f...]
> Sent: Fri, 28 Feb 2003 13:57
> To: ADO.NET
> Subject: [ado_dotnet] RE: dynamic vs static sql
>
>
> We are doing a mixed approach.  For the most part we wil be using static
> sql sp's for individual record's updates, deletes, and full record
> selection (getting all the records, not just a few for display).  We
> will be using a dynamic sql sp for most of our browse table style
> controls.  We're doing this because there are many, many different
> browse tables and they must be searchable and very modifiable and
> dynamic sql is the only viable way of doing this w/o tons of sp's and a
> support nightmare.
>
> Kevin
>
> ----- Original Message -----
> From: "Rahul Singh" <rahul.singh@a...>
> To: "ADO.NET" <ado_dotnet@p...>
> Sent: Friday, February 28, 2003 7:21 AM
> Subject: [ado_dotnet] RE: dynamic vs static sql
>
>
> > Actually some dynamic SQL will help you if you are making a lot of
> > similar queries on different tables, not just parameter based
> > searches.
> >
> > You're greatest challenge will be designing the DAL so it can be
> > scalable and updatable easily.
> >
> >
> > -----Original Message-----
> > From: Alex Smotritsky [mailto:alex.smotritsky@v...]
> > Sent: Tuesday, February 25, 2003 10:39 PM
> > To: ADO.NET
> > Subject: [ado_dotnet] RE: dynamic vs static sql
> >
> > I think dynamic sql will increase the complexity of problems you'll
> > have to deal with down the road.
> >
> > The only situation I know of where dynamic sql is generally preferable
>
> > is when you need to let people do parameter based searches.
> >
> >
> > -----Original Message-----
> > From: Kevin Ayers [mailto:kevin@f...]
> > Sent: Tuesday, February 25, 2003 4:56 PM
> > To: ADO.NET
> > Subject: [ado_dotnet] dynamic vs static sql
> >
> >
> > OK, the application I'm writing has 50+ tables and some of the tables
> > have
> > 200+ fields (government standard, not my choice -- its about as
> > 200+ denormalized
> > as is possible).  We will be distributing this database and our
> > application on hundreds of machines and I'm worried that writing
> > stored procedures for every insert, update, and delete for each of
> > these tables and maintaining them over the course of years is going to
>
> > be extremely difficult (the standards change all the time, an extra
> > field here, modify this here,etc). I'm thinking of taking the hit in
> > performance and writing 1 stored procedure (for insert, update, or
> > delete) that will dynamically build the corresponding statement and
> > only worry about changing the tables/fields. Does anybody know of a
> > better way? Performance is important, but its not essential to squeeze
>
> > the database for everything its worth.  The current iteration of our
> > program (which isn't written in .NET) is horribly inefficient and the
> > sp that I made to dynamically build the datagrids to display the data
> > is a couple of orders of magnitude better in performance than it.
> >
> > Thanks,
> > Kevin
> >
> >
> > ===
> > Fast Track ADO.NET with C# is a concise introduction to the concepts,
> > techniques, and libraries that you will need in order to start using
> > ADO.NET in your applications. The book covers DataSets and Typed
> > DataSets, accessing data using DataReaders and DataAdaptors, the close
>
> > relationship between ADO.NET and XML, how and where to use ADO.NET in
> > your enterprise applications, and how to use Web Services and ADO.NET
> > to easily pass data between applications.
> > http://www.wrox.com/books/1861007604.htm
> > ---
> > Change your mail options at http://p2p.wrox.com/manager.asp or to
> > unsubscribe send a blank email to 
> >
> >
> > ===
> > Fast Track ADO.NET with C# is a concise introduction to the concepts,
> > techniques, and libraries that you will need in order to start using
> > ADO.NET in your applications. The book covers DataSets and Typed
> > DataSets, accessing data using DataReaders and DataAdaptors, the close
>
> > relationship between ADO.NET and XML, how and where to use ADO.NET in
> > your enterprise applications, and how to use Web Services and ADO.NET
> > to easily pass data between applications.
> > http://www.wrox.com/books/1861007604.htm
> > ---
> > Change your mail options at http://p2p.wrox.com/manager.asp or to
> > unsubscribe send a blank email to 
> >
> >
> >
> > ===
> > Fast Track ADO.NET with C# is a concise introduction to the concepts,
> techniques, and libraries that you will need in order to start using
> ADO.NET in your applications. The book covers DataSets and Typed
> DataSets, accessing data using DataReaders and DataAdaptors, the close
> relationship between ADO.NET and XML, how and where to use ADO.NET in
> your enterprise applications, and how to use Web Services and ADO.NET to
> easily pass data between applications.
> > http://www.wrox.com/books/1861007604.htm
> > ---
> > Change your mail options at http://p2p.wrox.com/manager.asp or to
> > unsubscribe send a blank email to
>
>
> ===
> Fast Track ADO.NET with C# is a concise introduction to the concepts,
> techniques, and libraries that you will need in order to start using
> ADO.NET in your applications. The book covers DataSets and Typed
> DataSets, accessing data using DataReaders and DataAdaptors, the close
> relationship between ADO.NET and XML, how and where to use ADO.NET in
> your enterprise applications, and how to use Web Services and ADO.NET to
> easily pass data between applications.
> http://www.wrox.com/books/1861007604.htm
>
> ---
> Incoming mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.449 / Virus Database: 251 - Release Date: 27/01/2003
>
>
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.449 / Virus Database: 251 - Release Date: 27/01/2003
>
>
>
> ===
> Fast Track ADO.NET with C# is a concise introduction to the concepts,
techniques, and libraries that you will need in order to start using ADO.NET
in your applications. The book covers DataSets and Typed DataSets, accessing
data using DataReaders and DataAdaptors, the close relationship between
ADO.NET and XML, how and where to use ADO.NET in your enterprise
applications, and how to use Web Services and ADO.NET to easily pass data
between applications.
> http://www.wrox.com/books/1861007604.htm