barcode with vb.net LINQ to SQL and stored procedures in C#

Printer Code 128 Code Set B in C# LINQ to SQL and stored procedures

Adoption of LINQ to SQL and EF will discourage the use of stored procedures to return rowsets. In addition, the code generators will use a subset of T-SQL query constructs, that is, only the constructs that the LINQ or ESQL language supports, rather than the full power of the T-SQL query language. Aficionados always think that a stored procedure represents a contract between consumer and provider. Although the database metadata indicates number and type of parameters, and comprises a contract, this is absolutely not true for rowsets returned by stored procedures. There is no database metadata that records anything about the returned rowsets, or even how many rowsets a stored procedure will return. In addition, errors that occur in the middle of a stored procedure might result in rowsets not being returned. Finally, there s always the possibility of returning multiple and/or different rowsets by using a stored procedure with conditional code. That s not much of a rowset contract at all. One way to ameliorate this problem in SQL Server is to use multi-statement tablevalued functions to return one rowset with known metadata. The main problem with this is performance; a multi-statement table-valued function (TVF) is the equivalent of filling a table variable in code and then returning it. Almost always there is extra disk I/O involved: the I/O of reading the base tables, plus the I/O of filling the table variable, plus the I/O of reading the table variable at the consumer. There are also performance considerations as SQL Server table variables have no statistics. Therefore if the table-valued function is used as a row source in a larger query, there is no way to estimate the number of rows returned by the TVF. SQL Server 2008 s strongly typed tablevalued parameters would be an analogous concept, but currently these are limited to being input-only in procedures. No strongly typed rowset result is currently supported. Now that we ve determined that there is no more of a rowset contract for stored procedures than for ad hoc SQL (the difference is in SQL encapsulation), what about T-SQL extensions that Entity SQL doesn t support There are database-specific extensions like SQL Server s PIVOT operator, or ANSI SQL standards, like ranking and windowing functions. LINQ aficionados are quick to talk about implementation through extension methods but the long and short of this is that these are a LINQ-ism, unrelated to LINQ to SQL. That is, the LINQ construct to SQL dialect statement mapping is fixed and embedded in the LINQ to SQL product. Using extensions to the SQL statement mapping can t change which T-SQL statement is produced. To control what is produced, you d need to implement equivalent concepts on the client side and leave the generated database code alone.

EF may have a better story with this because each provider/writer implements the ESQL to query mapping. Conceivably you could write a custom provider to encapsulate the supplied provider including the T-SQL specific extensions. The ESQL language itself does not have the capability of ODBC-like escape clauses; therefore, there d be no way to express this extended SQL-based functionality in ESQL. I d classify the subset of SQL and stored procedure rowset is an anonymous

type problems as issues that might be worked out in future releases of databases and frameworks. Until LINQ to SQL or EF provides escape clauses in the framework, the easiest way out is the ultimate escape clause, using the stored procedure that returns (anonymous) rowsets. And the more stored procedures are used (not insert, update, and delete procedures, which enhance the model, but rowset-returning procedures), the farther away from the model you get. This interferes with the usefulness of the model in general.