Stored Procs and ADO.NET

[RichDef]

Hello Everyone!

 

I'm new to .net and I have been looking into ADO.net and I understand that there has been a general paradigm shift with ADO.net in which the client works disconnected from the datasource rather than actually maintainig a connection and manipulating the datasource directly. From what I understand, most of the time in ADO.net manipulation of data is done on client side to the dataset which is then used to update the actual datasource.

 

One thing I have been wondering about is the utilization of stored procedures in SQL Server. Sometimes SP's do some major manipulations on the actual database - how does this jive with the whole disconnected aspects of ADO.net?

 

I was just wondering about this because a stored proc operates directly on the database while other aspects of a .net application may manipulate data in a disconnected way.

 

Thanks

 

-Rich

[Nerseus]

It's still up to you to decide how much data-tweaking can/should happen client side versus server side. For instance, I have a solution that manages data in three areas: client side, web-server "side", and in SQL Server.

 

The idea behind disconnected data is meant to limit the number of active connections to a database, not where/how you manage your data. The one main difference is in locking. In a disconnected scenario, you don't hold any hard locks on the data. If you need to lock anything, you'll have to do it through code or flags on the database (such as using a timestamp column or a bit flag or username).

 

You can definitely use stored procedures in ADO.NET to do your SELECTs, UPDATEs, INSERTs, etc. and even have very complex queries. For instance, my app passes XML (from a DataSet) to SQL Server which can treat the XML like a table to pull out the data for INSERTs, UPDATEs, etc. You must manually grab the identity (@@IDENTITY) yourself if you do this, whereas using a DataAdapters Update method will automatically update "identity" columns in the DataSet if used properly. That's a bit more than you probably wanted :)

 

If you have specific questions about where to do specific things, you might get a clearer answer (such as if you need to know how to pass data to a proc to do your major manipulations - I mentioned XML above, but that may not work for you).

 

-Ner

[RichDef]

Ner,

 

Thanks for clarifying this some for me. I really don't have a question about a doing a specific thing. I'm just trying to understand ADO.net generally and how it differs from ADO, which seems a little more straight forward to me.

 

You manrioned this: "You can definitely use stored procedures in ADO.NET to do your SELECTs, UPDATEs, INSERTs, etc. and even have very complex queries."

 

This is what I was wondering about - If I'm using Stored Procs like this couldn't I avoid creating a dataset and data adapter entirely? If I did how would utilizing ADO.net like this differ from ADO? Maybe it wouldn't??

 

-Rich

[Nerseus]

A DataSet is a handy-dandy way to store the data client-side for doing all your work before sending it back up. It allows for multiple tables, linked if you want (like a real relational DB - sort of), plus a LOT more. It's also serializable, meaning it can be represented in 100% text for easy sending over the internet (to return from a webmethod).

 

If you want, you could use a DataReader to get the data locally but you usually want to store it somewhere besides the textboxes the user is editing. A grid, for instance, needs to be bound somehow - usually a DataSet though you could fill an ArrayList from a DataReader.

 

You may want to check out MSDN online (free) to see the main differences between ADO and ADO.NET. There are a LOT of differences to be aware of since using .NET is a LOT different than using VB6. It's more than just syntax changes - it's really a different way to structure your code and logic and ADO.NET is a part of the "big picture".

 

-Nerseus