Re: User defined types command parameter types

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

You're comparing apples and oranges here. For one thing, talking to a
server database is simply not the same thing as talking to a web
service. A web service can be crafted to expose only certain
interfaces, it isn't wide open like a database. Also, databases
"speak" SQL, not wsdl and database applications are architected to
have a logical and physical separation between the tiers for both
performance and security reasons. Schema information is available,
it's just not cheap since it always requires rounttripping, which
kills performance and scalability and provides a possible attack
vector. And in SQLS 2005 you'll find it getting harder, not easier, to
obtain schema information at runtime. The reason for this is mainly
security -- attackers often attempt to discover schema as part of a
probe to uncover potential vulnerabilities. So I'm afraid events are
trending against you, and in the future it will be harder, not easier,
to obtain schema information from SQLS at runtime.

--Mary

On Sat, 27 Aug 2005 17:51:13 -0700, "Mike"
<Mike@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

>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: Backups have Shadow Copy Problems
    ... and restarted the server. ... suggested and changed the recovery model to simple on the one database called ... I understand the issue to be: the backup task failed ... You back up data from a volume that contains a Microsoft SQL Server ...
    (microsoft.public.windows.server.sbs)
  • Re: upsizing to sql 2005
    ... the word SERVER in it, ... You can access to the database by multiple means (Access, ... and how does it update the SQL database with the new records in Access? ... Query Name: Arcadia - ARC ...
    (microsoft.public.access.queries)
  • Re: Linked Tables in Access
    ... any use of SQL Passthru, Linked Tables or any other use of MDB / MDE ... server, or would I also need to convert *those* queries to passthrough ... I've been trying to understand why Access database files become corrupt. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Trouble Getting VS.Net 2003 WalkThroughs MSDE Connection
    ... Config Tool of SQL Server? ... > link to download the PUBs database. ... >>> Setup and they directed me to install MSDE and they attached a ...
    (microsoft.public.sqlserver.msde)
  • Re: Leveling by ID vs. "Standard"
    ... of this trade called Project Management. ... a database for the "Project Tables," ... to write reports on data from the server database. ... product supporting queries created with SQL. ...
    (microsoft.public.project)