Data
Access in .NET: Tips and Tricks
Host: Deepak Gulati – Applications Developer, Microsoft
India
July
5, 2002
KunalS_MS: Good Afternoon and thank you all for joining into today's
MSDN Expert Chat Session
KunalS_MS: Today's topic is Data Access in .NET: Tips and Tricks
KunalS_MS: And our expert host of the day is Deepak Gulati - Applications
Developer, Microsoft India
KunalS_MS: Deepak thank you very much for taking time out of your
hectic schedule..
KunalS_MS: and helping us with your expert tips & tricks on data access
in .NET
Deepak_[MS]: Hi All!
KunalS_MS: In today's expert chat, we will slightly change the usual
format
KunalS_MS: Before you share your queries with Deepak
KunalS_MS: Deepak would first like to share few tips & tricks with
you...
KunalS_MS: most from his experience of developer applications in Microsoft
KunalS_MS: post that you are most welcome to flood him with your queries...
KunalS_MS: So now - over to you Deepak!
Deepak_[MS]: Thanks Kunal
Deepak_[MS]: I have all kinds of tips - so some of them
might seem obvious to some of you, but I am sure the beginers would
appreciate them
Deepak_[MS]: Lets get started.. the first thing I wanted
to talk about was SqlCommand
Deepak_[MS]: Depending on what kind of result you are
expecting from result of a query, you should use the optimal method
of SqlCommand
Deepak_[MS]: e.g. if its a stored proc doing updates
in the back-end and you really don't expect any results from execution
of that query, use ExecuteNonQuery()
Deepak_[MS]: also a lot of times there are queries that
return just one record - e.g. a query like - select sum(price) from
sales is expected to return just one value
Deepak_[MS]: in that case just call - the ExecuteScalar()
method and assign the return value to the appropriate data type
Deepak_[MS]: there is no nead to create a DataReader
or DataSet in case of ExecuteScalar
Deepak_[MS]: Another quick tip I wanted to share was
realted to parameterized stored procedure..
Deepak_[MS]: a lot of time you want to pass null to the
stored procedure..
Deepak_[MS]: but nither the empty string "" nor Nothing
works
Deepak_[MS]: you can use DBNull.value to set a SqlParameter
to null
Deepak_[MS]: which is the same null as is the Sql database
Deepak_[MS]: Another tip that I think worth sharing is
about access data from multiple sql queries without roundtripping
Deepak_[MS]: lets say you have 3 sql statements or store
procedures, now you want to execute all the 3 queries in one go without
roundtripping to sql server mutiple times..
Deepak_[MS]: in that case, you can separate the sql queries
with a semi colon..
Deepak_[MS]: e.g. String SqlStatement = "select * from
authors; select * from titles";
Deepak_[MS]: now you can use this query with DataAdpater..
Deepak_[MS]: Filling the DataSet will automatically give
you multiple tables with result of each query in a different table!
Deepak_[MS]: you can use dataset.Tables collection to
get result of each query
Deepak_[MS]: so dataset.Tables[0] will give you result
of first query (select * from authors) and so on
Deepak_[MS]: in case you want to do the same thing with
stored procedures, things need to be done slightly differently
Deepak_[MS]: lets say we have 2 stored procs - spGetAllAuthors
and spGetAllTitles which return authors and titles respectively
Deepak_[MS]: in this case, my Sql statement would become..
Deepak_[MS]: One last tip - you'll find this useful if
you are developing an application where performance is crucial
Deepak_[MS]: When accessing the values from DataReader,
use numeric indexes for columns instead of using column names
Deepak_[MS]: lets say reader is your DataReader which
has columns you are trying to access..
Deepak_[MS]: now its more efficient to do reader.GetString(0)
than reader["column_name"]
Deepak_[MS]: although the approach does slightly hinder
readability of your code, but when you are trying to milk your application
for last bit of performance, readability is usually not a prime concern
Deepak_[MS]: Thats all I had in my bag for today!
Deepak_[MS]: Feel free to ask me for clarifications/code/details
on these tips and any other questions that you might have..
Moderator: (michael) Could u please tell me more about connection
pooling using all the 3 data providers. I mean connection pooling
in OLEDB.NET and ODBC.NET.
Deepak_[MS]: Hi Michael, the pooling mechanism for OLEDB.NET
should be same as classic OLEDB, unfortunately I've not worked with
ODBC.NET yet.. I would try and get more details and post them on the
forums
Deepak_[MS]: refer to this link in the online documentation..
Deepak_[MS]: since ODBC.NET was a separate download,
I am sure the docs there would have something...
Moderator: (Jagan) What is a disconnected resultset?
Deepak_[MS]: Hi Jagan, a disconnected results set is
data returned as a result of your query, but after you've got the
data the connection to the backend server is no longer open
Deepak_[MS]: in OLEDB you had disconnected recordsets..
in case of ADO.NET we have DataSet
Deepak_[MS]: one of the fundamental reasons for having
lot of emphasis on disconnected recordsets was to provide a rich environment
to the clients for data manipulation which is independent of the underlying
data source
Moderator: (Jagan) What if add or update to that resultset and how
does it effect my data in the server?
Deepak_[MS]: Good question Jagan.
Deepak_[MS]: the DataAdapter / SqlCommand act as a link
between your disconnected DataSet and the data source underneath
Deepak_[MS]: you call the Fill method of DataAdapter
to get the results of your query, conversely, the DataAdapter has
Update method which does the revers
Deepak_[MS]: i.e. takes the changes made to your dataset
and gets it into the backend
Deepak_[MS]: also you can always call Update/Insert/Delete
Sql Queries directly on the underlying data source...
Moderator: (ravikanth) What are the best ways to get identity value
of newly inserted row in ADO.NET?
Deepak_[MS]: Hi Ravikanth
Deepak_[MS]: there is a small trick you can use - it
sort of combines the tips that I presented today
Deepak_[MS]: lets say you have an insert command, batch
it with a select command and get the @@IDENTITY sql variable
Deepak_[MS]: so String strSql = "insert command; select
@@IDENTITY"
Deepak_[MS]: calling execute scalar should get you the
identity ... this is something I've just thought of.. so give it a
shot and let me know if it works!
Moderator: (Prasad) Explain about Table Mapping
Deepak_[MS]: Hi Prasad
Deepak_[MS]: The .NET SDK documentation is quite comprehensive
on TableMapping... here is the link you can use to look up the docs
Moderator: (ravikanth) What is the guarantee that we will get newly
inserted row identity if multiple insertions taking place?
Deepak_[MS]: If you are using SQL 2K, there is a solution
(one more reason to use only microsoft technologies)
Deepak_[MS]: oops.. sorry.. wrong link
Deepak_[MS]: SQL 2k has something called SCOPE_IDENTITY
Deepak_[MS]: Returns the last IDENTITY value inserted
into an IDENTITY column in the same scope. A scope is a module --
a stored procedure, trigger, function, or batch. Thus, two statements
are in the same scope if they are in the same stored procedure, function,
or b
Deepak_[MS]: or batch
Deepak_[MS]: so instead of @@IDENTITY use SCOPE_IDENTITY()
to get the identity generated as a result of the insert that you performed
Deepak_[MS]: if you have SQL 2000 books online installed
you can refer to SCOPE_IDENTITY at -mkMSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\tsqlref.chm::/ts_sa-ses_6n8p.htm
Deepak_[MS]: sorry it is supposed to be ms: @ (without
space)
Moderator: (ravikanth) what if I don't use any stored procedures/triggers/functions,
then any solution?
Deepak_[MS]: when you give an insert command followed
by a semi colon and then followed by the select SCOPE_IDENTITY(),
your statements are treated as part of the same batch
Deepak_[MS]: so it would work even if your insert command
is just a normal INSERT sql statement
Moderator: (sreejumon) How can we connect exchange server using ADO.NET?
Deepak_[MS]: Hi Sreejumon, you can use the OLE DB provider
for exchange (exoledb)
Moderator: (michael) But after changing the values inside data row
, now i want to update backend with the original value or say proposed
value.then how it can be done?
Deepak_[MS]: Ok - you first need to get all the changes
done to the datatable..
Deepak_[MS]: once you are sure that the changes need
to go to the backend, use Update method of DataAdapter to get all
of them to backend
Moderator: (Govardhan) Deepak you mentioned about being connected
while looping through the Reader. So is it permissable to call Connection.Close
after doing a Command.ExecuteQuery and still be able to read through
the SqlDataReader that the ExecuteQuery returns?
Deepak_[MS]: nope - you should be looking at DataSet
if you want to close the connection first..
Deepak_[MS]: however - there is a small trick that you
can use - which automatically closes the underlying connection when
you close the Reader
Deepak_[MS]: this is especially useful when you are returning
a reader from a function and are not sure if the callee will close
the connectionc cleanly
Deepak_[MS]: when calling the ExecuteReader of SqlCommand,
pass it CommandBehavior.CloseConnection
Deepak_[MS]: i.e. instead of doing cnn.ExecuteReader();
do cnn.ExecuteReader(CommandBehavior.CloseConnection)
Deepak_[MS]: now whenever your Reader will close, the
underlying connection will be closed automatically!
KunalS_MS: Hello All, I am sorry but we have approached the end of
today's session
KunalS_MS: time has run out
KunalS_MS: however, if we have not been able to answer any questions/queries
KunalS_MS: please do post them in the forums and Deepak shall surely
have a look at them
KunalS_MS: Please do share your satisfaction feedback and suggestions
- http://www.microsoft.com/india/communitysatsurvey
KunalS_MS: Your suggestions are indeed vital for us
KunalS_MS: You can post your queries at the MSDN Online Developer
Forums - http://www.microsoft.com/india/onlineforums
