[dba-VB] C# - Direct data manipulation

Hi John --

I must say I have never done that "dynamic" t-SQL via C# - I guess it should
be doable: using SMO or even SqlCommand's .ExecuteNonQuery(?) it should be
possible to create SPs and UDFs, and then execute them as usual...

BTW, UDFs, which return tables are very often a good substitute for all
kinds of temp tables and dynamic SQL...

I wonder what for all that "dynamism" there? 
Do you have constantly changing database structure?
Do you have constantly changing customer requests, which do not allow you to
create a set of stable parameterized SPs and UDFs, and for small(?) changing
part of queries use LINQ for SQL or ADO.NET EF?

Thank you.

--
Shamil

-----Original Message-----
From: dba-vb-bounces at databaseadvisors.com
[mailto:dba-vb-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Wednesday, December 02, 2009 1:54 AM
To: VBA
Subject: [dba-VB] C# - Direct data manipulation

As previously mentioned, over the last couple of years I have built up a set
of stored procedures 
which perform TSQL.  I then run sets of these stored procedures to perform
processes.  The TSQL is 
always dynamic.

As an example a stored procedure might build a table, with boilerplate field
names and data types, 
but a passed in database and table name.  Inside of the stored procedure I
build up the correct SQL 
to create the table in the database specified, with the name specified.
Another stored procedure 
might create in index, with boilerplate field names, but a passed in
database and table name.  Again 
the stored procedure builds up the TSQL statement to create the index in the
correct db and table.

I already have these stored procedures debugged and working and have been
using them in sets to 
perform "big picture processes" for quite some time, but the whole "execute
a stored procedure" 
using a command object, parameter objects in a parameter array, interpreting
the parameters etc just 
seems so... 1950s.  I feel like I am flipping switches on the front panel of
a computer in a 50s 
sci-fi movie as a tape reel spins madly in the background.

Now that I am moving to C# I am wondering if this is required any more.  Can
C# execute TSQL 
directly somehow?  Can I assemble this boiler plate plus database / table
kind of thing into a TSQL 
statement directly in C# and then somehow tell SQL Server to execute that
TSQL?  I understand the 
"SQL optimization" thing but it seems like (not sure here) that because the
TSQL is dynamically 
constructed in the stored procedure, the whole "optimization" thing goes
right out the window.  If I 
can execute TSQL directly from C#, what can I get back?  Rows affected?
Error codes?  And how?

As it stands I am happily flipping switches, tape reels whirring, exporting
data for address 
validation.  This is good, I am happy, but ...

-- 
John W. Colby
www.ColbyConsulting.com


 

__________ Information from ESET NOD32 Antivirus, version of virus signature
database 4653 (20091201) __________

The message was checked by ESET NOD32 Antivirus.

http://www.esetnod32.ru