Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > C# and C > C# 1.0 > C#
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
C# Programming questions specific to the Microsoft C# language. See also the forum Beginning Visual C# to discuss that specific Wrox book and code.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the C# section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old February 15th, 2006, 07:12 AM
Registered User
 
Join Date: Feb 2006
Location: , , .
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default Database Commands From C# Form. It Works But ...

Hello. I am working on a simple C# application to deal with an Access database. This is what I am using for my INSERT/DELETE/UPDATE/SELECT operations:

==============
OleDbConnection myConn = new OleDbConnection(myConnString);
OleDbDataAdapter da = new OleDbDataAdapter();
DataSet ds = new DataSet();

string mySQLString = "SQL for INSERT/DELETE/UPDATE/SELECT";

try
{
  da.SelectCommand = new OleDbCommand(mySQLString, myConn);
  da.Fill(ds);

  // Use ds.Tables[...] if SELECT operation

  ds.Clear();
  ds.Dispose();
  da.Dispose();
}

catch (System.Exception x)
{
  MessageBox.Show(x.ToString());
}
==============

It works, but is it right? Not many, if at all any, of the coding examples I've come across seem to go this way. Here's what's bothering me ...

(1) Is it a good idea to not open/close one's connections explicitly?

(2) Is it a good idea to have not utilized the ExecuteNonQuery() method?

(3) Is there anything that's redundant above? Perhaps the Clear() and Dispose() commands.

(4) Would I be paying a heavy price in resources/speed if I were to use Transactions?

(5) My updated values (if at all updated values, else old values) for the database are in a textfile. At present I am taking them line by line for INSERT? Is there a faster way? The textfile could have new rows as well, so I:

try
{INSERT}

catch (System.Exception)
{
  try
  {
    DELETE;
    INSERT;
  }

  catch (System.Exception x)
  {MessageBox.Show(x.ToString());}
}


(6) Is OleDb passe? Should I go SqlClient?


Again, it's a simple (4-Table) Access Database.

I agree, that's a lots of questions. All and any comments/answers will be helpful. Anything I am missing, anything that could be catastrophic, anything I should look-up. Thanks in advance!
Reply With Quote
  #2 (permalink)  
Old February 15th, 2006, 08:02 PM
Wrox Technical Editor
 
Join Date: Dec 2005
Location: , , Canada.
Posts: 271
Thanks: 0
Thanked 0 Times in 0 Posts
Default

(1) the Fill() method encapsulates the opening and closing of the database. Explicitly Opening and Closing of the connection is NOT needed.

(2) the ExecuteNonQuery() method should probably be avoided; it doesn't facilitate n-tiered application development, and is often a security concern (SQL injection). Through the DataSet and ADO.NET we have a new model for data access.

(3) the Dispose() methods are unneeded and should generally NOT be called explicitly as it invokes garbage collection, thus creating additional overhead. Instead you should set your DataSet and DataAdapter to null. Calling the .Clear() on a DataSet is a good idea as it frees up unneeded resources, however the Garbage Collection would have cleaned up those resources when the DataSet went out of scope.

(4) Transactions coupled with stored procedures are good.
Will you pay a heavy price in resources/speed? That depends on what are you using now? I can't give a quantitative answer.

(5) Not too clear on the question, but Stored Procedures are very quick.

(6) SqlClient over OleDB, OleDB over ODBC. SqlClient has been optimized for SQL Server and ASP.NET, OleDB adds another layer on DB communication, and ODBC adds even more layers.

----
You should read up on ADO.NET, specifically DataSets, and DataAdpaters..
Through DataAdapters you can offer an alternative too: [string mySQLString = "SQL for INSERT/DELETE/UPDATE/SELECT";]

You may want to consider buying an ADO.NET reference book..

Links of interest:
http://msdn.microsoft.com/library/de...sfilltopic.asp
http://msdn.microsoft.com/library/de...html/psent.asp

- A.Kahtava
Reply With Quote
  #3 (permalink)  
Old February 16th, 2006, 07:04 AM
Registered User
 
Join Date: Feb 2006
Location: , , .
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks Adam, for your response. It is very helpful.

About (5) in my original posting, let me explain it in more detail: When I acquire data for my application, it is in the form of a tab-delimited textfile (with no row of variable names; but this can be changed). The columns are in the same order as the columns in the Access Table. For example:

[ID Name Income Age Gender Raise Car...]
'1' 'AB' '30000' '35' 'M' '5' 'Saturn'
'2' 'CD' '45000' '30' 'M' '7' 'Jetta'


At the very outset, I INSERT each row and populate the Access Database and can query the data. Now, 2 days later, I get a new textfile as follows:

'1' 'AB' '32000' '35' 'M' '5' 'Civic'
'2' 'CD' '45000' '30' 'M' '7' 'Jetta'
'3' 'EF' '35000' '25' 'F' '4' 'Pontiac'

Some values, if at all any, have changed (e.g. in the first Row). Additionally, there's a new Row. If I INSERT again, it won't work for Rows 1 and 2. But INSERT will work for Row 3. So what I am doing is trying an INSERT first, and if it throws any Exception, then I DELETE the Row and (re) INSERT the ("new") Row. Any updated values in the texfile (I don't know which ones) are, therefore, taken care of. Here's how it goes:

(1) Try INSERT for "AB." It won't work. Delete "AB" and then INSERT "AB."
(2) Now try INSERT for "CD." It won't work. Delete "CD" and then INSERT "CD."
(3) Now try INSERT for "EF." It works.

I expect a multitude of such new updated textfiles over time. I am just trying to automate the transfer of data to the database, so that I can query it. And this has left me wondering: Must I go line-by-line through the textfile and each time? Is the INSERT-(DELETE-INSERT) approach the only way to go here? Can a textfile be linked to an Access Database, so that the Access Tables reflect the state of the textfile?

I was advised on a different forum to not go line-by-line from the textfile, but load the textfile into a dataset then (presumably) go row-by-row from the dataset. Is row-by-row the only way? Can a dataset be linked (bound?) to an Access Database, so that the Access Tables reflect the state of the dataset?

Again, a lot of questions. These are what I am searching on and reading up on over the next few days. Any tips/pointers/leads/links will be greatly appreciated. Thanks!

Reply With Quote
  #4 (permalink)  
Old February 24th, 2006, 01:07 PM
Authorized User
 
Join Date: Feb 2006
Location: Bluefields, , .
Posts: 47
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to MAKO
Default

Hi, box! I beleive going line by line and loading into a DataSet could help if you first have a Dataset with data from your access.mdb and then merge the new rows of your other dataset and persist changes against the DB. (Just an idea! not quite tried it yet!)

:-Q Marvin B-)
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
ABOUT AT COMMANDS bobwith5 Visual Basic 2005 Basics 1 October 31st, 2007 03:25 PM
sql commands dr BOOK: Beginning Ruby on Rails 1 April 27th, 2007 01:22 PM
Access database through oledb commands castor C# 1 April 26th, 2007 01:27 PM
Using 2 SQL commands attipa Classic ASP Databases 2 September 20th, 2003 06:23 PM



All times are GMT -4. The time now is 03:56 PM.


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.