Re: User defined types command parameter types

When I call web services, I use the WSDL to define the parameters I pass. I
don't have to instantiate a command object and write code to tell it the name
and type of each parameter. It is technical feasible to generate proxy stubs
for sql calls at design time just as you do for web services. It just hasn't
been implemented yet. The closest we get is the command builder class. It
gets the parameters for you. If you cache that command it is relatively
effecient. It is my opinion that the whole process should and could be more
seemless and intutive.

"Mary Chipman [MSFT]" wrote:

> The reason you have to do it in two places is because you have two
> different code bases running in two separate processes. ADO.NET is
> simply the pipeline passing information from your client application
> written in C# to the database where your stored procedures are written
> in T-SQL. Think of it as messaging, where your app sends a message to
> the server telling it what it wants done. If the instructions in your
> message aren't accurate, then the server isn't going to be able to
> provide the results you expect.
>
> SQLS 2005 hosting the CLR isn't going to make any difference in this
> regard. T-SQL is still used under the covers for data access, and if
> all your application does is basic data access, then you probably
> aren't going to need or want SQL CLR. The reason for the CLR isn't to
> make coding clients easier, it's to be able to have complex
> computational code executing on the server in a compiled language. For
> example, an operation doing calculations that would take 3 pages of
> T-SQL code can be done in a few lines of code by taking advantage of
> the BCL and be far more performant, executing in proc rather than as
> interpreted (T-SQL). So even if you do end up taking advantage of the
> CLR inside of SQLS, your client application will still be running
> *outside* of the server. Waiting for future versions isn't going to
> change the basic fact that your client code is executing in a
> completely separate process which only communicates with the server
> through the conduit of ADO.NET. So the need to specify parameters on
> both sides of the pipe isn't going away any time soon.
>
> --Mary
>
> On Fri, 26 Aug 2005 11:43:28 -0700, "Mike"
> <Mike@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
>
> >Hi Mary,
> >
> >That's a good point and you are right this is design time issue not a run
> >time issue. However, having the type defined in one place makes changes to
> >the code much easier to make. The round trip issue you referenced could be
> >avoided if Visual Studio was more tightly integrated with SQL server. Then it
> >would only update the type information at compile time. In general, I would
> >like to see better integration of stored procedures and application code. I
> >would like to be able to call a stored procedure the same way I call any C#
> >function. I do not like having to writing all the plumbing code that is
> >currently required to call stored procedures. Why should have to define
> >stored procedure parameters in two places? I don't do that with methods I
> >write in C#.
> >
> >I know that the new version of SQL Server takes a step in that direction by
> >building the CLR into SQL Server. I hope following version will continue that
> >direction.
> >
> >Thank you for your efforts to help. I think I will just have to be patient
> >and wait for newer versions of SQL Server and .Net.
> >
> >Thanks,
> >Mike
>
.

Relevant Pages

  • Re: Extended Stored Procedures developed in .NET?
    ... No, not in managed code. ... Also, below is with permission from David Browne, explaining how you can have SQL Server execute CLR ...
    (microsoft.public.sqlserver.programming)
  • Re: DB design, facilitates Double entries of internal transactions
    ... The single server is also ... transactions, even if just a pair of ID's/amounts, is still double entry. ... but I would tend to recommend SQL Server ... Stored Procedures and transaction control on the server. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Temp files in Stored Procedures
    ... you do need to use SELECT INTO, try to schedule it when your SQL Server is ... server to its knees. ... >> The ideal method is to avoid using temp tables within stored procedures. ...
    (microsoft.public.sqlserver.programming)
  • Re: User defined types command parameter types
    ... the server telling it what it wants done. ... SQLS 2005 hosting the CLR isn't going to make any difference in this ... >avoided if Visual Studio was more tightly integrated with SQL server. ... >like to see better integration of stored procedures and application code. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Upsizing .mdb for split DB
    ... MDB file. ... Plan the migration for the data from Access to SQL Server carefully. ... You must decide where the stored procedures go and the logic ...
    (microsoft.public.access.adp.sqlserver)
Loading