473,577 Members | 3,302 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

How To Use A Database In Your Program Part II

Curtis Rutland
3,256 Recognized Expert Specialist
How To Use A Database In Your Program Part II

This article is intended to extend Frinny’s excellent article: How to Use a Database in Your Program. Frinny’s article defines the basic concepts of using databases very well and is prerequisite reading for this article.

Frinny’s article explains how to use a SQL Server in your program, but there are other databases as well. Some of them provide .NET connectors, but for those that don’t, or if you don't want to use them, there are two protocols that you can use to connect to them: ODBC and Ole DB. Note that the objects explained in the following section are interchangeable with their counterparts; they have the same methods and behave the same way.

ODBC
Namespace: System.Data.Odb c
ODBC stands for “Open Database Connectivity.” This protocol is the most generic. ODBC relies on DSNs: “Data Source Names.” These must be set up on the machine which the application will be run on: for ASP.NET, the server; for Windows apps, each client computer. DSNs can be set through the registry, but this is outside the scope of this article. The ODBC drivers are typically installed when the database or its management tools are installed. For example, the IBMDA400 (for connecting to an AS400/Series I) driver is installed when you install IBM’s Client Access. The drivers are required to add the DSN.
Useful objects: OdbcConnection, OdbcCommand, OdbcDataAdapter, OdbcCommandBuil der

Ole DB
Namespace: System.Data.Ole Db
Ole DB stands for “Object Linking and Embedding, Database.” This protocol tends to be less generic than ODBC, because the connection string specifies the provider to use. This provider must be installed on the system that the application runs on, but no DSNs or external connections are required. One of the “pros” to using Ole DB is that it is easier to dynamically define a connection. For example, you can connect to an Excel spreadsheet using either ODBC or Ole DB, but ODBC requires a pre-defined DSN, or you must edit the registry to add one. You can simply change the “Data Source” property of the connection string if you use Ole DB.
Useful objects: OleDbConnection, OleDbCommand, OleDbDataAdapte r, OleDbCommandBui lder

Note: Please note that from here on out I will use the objects from the System.Data.Ole Db namespace for examples. Remember that they are fully interchangeable with their counterparts. For example, a SqlConnection behaves the same as an OleDbConnection and an OdbcConnection, so you can swap them out when you use different protocols.

DataAdapters
Namespace: System.Data (for DataSet and DataTable)
Frinny explained how to use a OleDbCommand to make a OleDbDataReader . Another method, slightly more automated, is to use an OleDbDataAdapte r to fill a DataTable. OleDbDataAdapte rs contain the Fill(dataSetIns tance, “tableName”) method, which will use your SELECT statement to fill a DataTable in a DataSet. This DataTable can be directly bound to controls such as DataGrids and ListBoxes. You can also use OleDbCommandBui lders to create INSERT, UPDATE, and DELETE commands based on your SELECT command. When the data in the DataTable has been modified and you want to commit the changes, you can use the OleDbDataAdapte r’s Update() method to save the changes back to the database.

Expand|Select|Wrap|Line Numbers
  1. //C#
  2. //set up the connection string
  3. string ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\dev\db.mdb;User Id=admin;Password=;";
  4. //set up the select statement
  5. string SelectCommand = "SELECT * FROM test";
  6. //set up the connection object using the conn string
  7. OleDbConnection Connection = new OleDbConnection(ConnectionString);
  8. //set up the data adapter using the select statement and the connection object
  9. OleDbDataAdapter Adapter = new OleDbDataAdapter(SelectCommand, Connection);
  10. //a new empty dataset
  11. DataSet ds = new DataSet();
  12. //fill the dataset with a new datatable of all the results
  13. Adapter.Fill(ds, "test");//string is the DataTable name, can be anything
  14. //now, let "Table" point to the datatable with our results
  15. DataTable Table = ds.Tables["test"];
  16. //and you can use the table as needed
  17. dataGridView1.DataSource = Table;
  18.  
Expand|Select|Wrap|Line Numbers
  1. 'VB.NET
  2. 'set up the connection string
  3. Dim ConnectionString As String
  4. ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\dev\db.mdb;User Id=admin;Password=;"
  5. 'set up the select command
  6. Dim SelectCommand As String
  7. SelectCommand = "SELECT * FROM test"
  8. 'set up the connection using the conn string
  9. Dim Connection As New OleDbConnection(ConnectionString)
  10. 'set up the adapter using select command and connection object
  11. Dim Adapter As New OleDbDataAdapter(SelectCommand, Connection)
  12. 'a new blank data set
  13. Dim ds As New DataSet()
  14. 'fill the dataset with a datatable of our results
  15. Adapter.Fill(ds, "test") 'test is the datatable name, can be anything
  16. '//now, let "Table" point to the datatable with our results
  17. Dim Table As DataTable
  18. Table = ds.Tables("test")]
  19. 'use the data as you see fit
  20. DataGridView1.DataSource = Table
  21.  
Now, Table is pointing to a DataTable that contains all the data retrieved from your query. You can also modify this data, and easily reflect your changes back to the database.

Expand|Select|Wrap|Line Numbers
  1. //C#
  2. //set up the command builder using the data adapter
  3. OleDbCommandBuilder Builder = new OleDbCommandBuilder(Adapter);
  4. //use the builder to create update, insert, and delete commands
  5. Adapter.UpdateCommand = Builder.GetUpdateCommand();
  6. Adapter.InsertCommand = Builder.GetInsertCommand();
  7. Adapter.DeleteCommand = Builder.GetDeleteCommand();
  8.  
  9. //manipulate the data in the table
  10. Table.Rows[0]["FirstName"] = "Jimmy";
  11.  
  12. //commit changes of table "test" in the dataset back to the database 
  13. Adapter.Update(ds, "test");
  14.  
Expand|Select|Wrap|Line Numbers
  1. 'VB.NET
  2. 'set up the command builder using the data adapter
  3. Dim Builder As New OleDbCommandBuilder(Adapter)
  4. 'use the builder to generate update, insert, and delete commands
  5. Adapter.UpdateCommand = Builder.GetUpdateCommand()
  6. Adapter.InsertCommand = Builder.GetInsertCommand()
  7. Adapter.DeleteCommand = Builder.GetDeleteCommand()
  8.  
  9. 'manipulate the data in the table
  10. Table.Rows(0)("FirstName") = "Jimmy"
  11.  
  12. 'commit changes of table "test" in the dataset back to the database 
  13. Adapter.Update(ds, "test")
  14.  
Note: Your data will be loaded into memory, so don’t use this method if you have very large datasets.

ExecuteScalar
Namespace: System.Data.<pr otocol> (OleDb,Odbc,Sql Client)
Frinny showed you how to execute a DataReader and a “NonQuery” (UPDATE/INSERT/DELETE statement). There is one more useful method to show: ExecuteScalar . ExecuteScalar() returns the first column of the first row of the query as an Object. This is useful when you want to select one value out of your database. This is especially useful for scalar functions like count, max, and sum, but isn’t required to be a scalar.

Expand|Select|Wrap|Line Numbers
  1. //C#
  2. //set up select scalar command
  3. string ScalarQuery = "SELECT COUNT(*) FROM test";
  4. //set up command object using query and previous connection object
  5. OleDbCommand Command = new OleDbCommand(ScalarQuery, Connection);
  6. //a variable to hold our results:
  7. int count = -1;
  8. //attempt the query
  9. try
  10. {
  11.     //open the connection
  12.     Command.Connection.Open();
  13.     //execute the query and assign the result to "count"
  14.     count = Convert.ToInt32(Command.ExecuteScalar());
  15.     //close the connection
  16.     Command.Connection.Close();
  17. }
  18. //if it didn't work...
  19. catch (OleDbException exc)
  20. {
  21.     MessageBox.Show(String.Format("OleDb Error: {0}", exc.Message));
  22. }
  23. finally
  24. {
  25.     //always double check to make sure your connection is closed
  26.     if (Command.Connection.State != ConnectionState.Closed)
  27.         Command.Connection.Close();
  28. }
  29. //show the results
  30. MessageBox.Show(String.Format("Scalar Result: {0}", count));
  31.  
Expand|Select|Wrap|Line Numbers
  1. 'VB.NET
  2. 'set up select scalar command
  3. Dim ScalarQuery As String
  4. ScalarQuery = "SELECT COUNT(*) FROM test"
  5. 'set up command object using query and previous connection object
  6. Dim Command As New OleDbCommand(ScalarQuery, Connection)
  7. 'a variable to hold our results:
  8. Dim count As Integer
  9. count = -1
  10. 'attempt the query
  11. Try
  12.     'open the connection
  13.     Command.Connection.Open()
  14.     'execute the query and store the result in count
  15.     count = Convert.ToInt32(Command.ExecuteScalar())
  16.     'close the connection
  17.     Command.Connection.Close()
  18. 'if it fails...
  19. Catch ex As OleDbException
  20.     MessageBox.Show(String.Format("OleDb Error: {0}", ex.Message))
  21. Finally
  22.     'always make sure your connection is closed
  23.     If Command.Connection.State <> ConnectionState.Closed Then
  24.         Command.Connection.Close()
  25.     End If
  26. End Try
  27. 'display results:
  28. MessageBox.Show(String.Format("Scalar Result: {0}", count))
  29.  
Jul 2 '08 #1
1 27072
Curtis Rutland
3,256 Recognized Expert Specialist
Updated to include comments in the code.
Jul 8 '08 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

10
2529
by: NotGiven | last post by:
I have never used a config file - my config will be a database record. I know how to get the record for that user. Is it simply using the elements of the recordset array to set page by page seettings? Any links I can read more about it? Thanks.
5
3004
by: Mike | last post by:
Does python support named semaphores for Linux? I saw that ActivePython for Win32 does. Can I get a list of currently running processes? I already posted in the GTK forum about looking for the window title. Thx
51
5059
by: John Baker | last post by:
Hi: Some time ago I developed a program in Access, and separated the database and the program itself (using the normal access tools).We have the db on our server and the programin the desktop system. I have modified the program a number of times, and now find that I need to change the DB slightlt. This appears to require that I REMERGE the...
2
1668
by: Patrick Glenn | last post by:
I am looking for an online zip code program / database, that will aloo me at a minimum to be able to type in a zip code, a distance (5mile, 10miles, 50miles, etc) and have the process return back all zipcodes within the specified distance of the supplied zipcode. Any help or assistance will be appreciated. Patrick
0
7103
by: south622 | last post by:
I'm taking a beginning Java course and I'm stuck in week eight of a nine week course. If anyone could help me I would greatly appreciate it. This assignment was due yesterday and each day I go past the due date 10% of the grade is taken off. I think I'm coming down with the flu and my brain is just not processing this assignment. Here is the...
1
2240
by: vijay | last post by:
HI All, I am using C#.net 2005 and my problem is, i want to automatically run sql script to Restore database as a part of .net Setup and Deployment. I have done setup for my project by using setup wizard but not able to add sql script to resore database while i deploy my setup to client machine i should run script...
22
60195
Frinavale
by: Frinavale | last post by:
How To Use A Database In Your Program Many .NET solutions are database driven and so many of us often wonder how to access the database. To help you understand the answer to this question I've provided the following as a quick example of how to retrieve data from a database. In order to connect to a SQL Server Database using .NET you will need...
0
7776
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8121
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8286
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7869
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
8143
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6517
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
0
5340
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3779
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
1
2287
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.