Manipulating A Database

[Toulinwoek]

OK, I'm hitting the wall harder than a rookie Indy driver!

I'm very new to programming in general, C# in particular, and I'm trying to teach myself. I'm using C# Express, which Microsoft claims is aimed squarely at people like me (novice). However, I can't seem to find the exact information I need on how to manipulate data in an external database from within the program I'm writing. The documentation seems to assume that I am more familiar with the concepts and the terminology than I am.

As basic as this might be for those of you with more experience, I can't grasp, from the documentation, the relationship between a dataset and a data adapter, for example. I don't know what code to type that will make the program use the database it's connected to (I did get it to connect to the database), for example to create/edit/store records. As a training project, I'm just trying to write a simple contact management program with 5 fields that stores data in an Access database.

I'd appreciate some insight, or at least some direction on where I can get a good grounding in this concept :confused:

[Paladine]

Well let me see if I can help a little.

Couple of things to keep in mind.
1. Express is still in Beta (Beta2) and is building on the existing technology of .NET Framework 1.1.
2. It is for the novice programmer, not the novice pc user or those entirely new to programming.

saying that, the people here at Daniweb are a great resource and I am sure will try their best to answer your questions. But I have a few to ask first, just to see what level of knowledge / understand you have.

Q1. Have you don any programming in C, C++, Java or C# before?
Q2. Have you ever used ADO, DAO, or ADO.NET? Do you know what they stand for and what the technology provides?

This will greatly affect where we get begin to provide some information.

A very simple view of the two items in question
DataAdapter = the Object holding the Connection to the DB and the Query you are executing.
DataSet = A snapshot of the Data in the DB requested through your Query, which obtained with the DataAdapter, that made the call to the DB provided in your connection string and executed your Query.

Here is some links to check out and bring you up to speed:
LINK #1
LINK #2

Hope this helps.

Happy Coding :cool:

OK, I'm hitting the wall harder than a rookie Indy driver!

I'm very new to programming in general, C# in particular, and I'm trying to teach myself. I'm using C# Express, which Microsoft claims is aimed squarely at people like me (novice). However, I can't seem to find the exact information I need on how to manipulate data in an external database from within the program I'm writing. The documentation seems to assume that I am more familiar with the concepts and the terminology than I am.

As basic as this might be for those of you with more experience, I can't grasp, from the documentation, the relationship between a dataset and a data adapter, for example. I don't know what code to type that will make the program use the database it's connected to (I did get it to connect to the database), for example to create/edit/store records. As a training project, I'm just trying to write a simple contact management program with 5 fields that stores data in an Access database.

I'd appreciate some insight, or at least some direction on where I can get a good grounding in this concept :confused:

[Toulinwoek]

The info you provided is very helpful, thanks. I guess "novice" assumes a bit more experience than "complete newbie"!

I've been in the computer field for 14 years, but the only programming I've ever done was playing around with QBasic about 10 years ago. I've done nothing with any of the higher-level languages.
I know what the ADO/DAO acronyms mean, but my understanding of the technology they refer to is basic and not entirely clear yet.

I've taken a quick look at the links you provided, and I can see that they are going to be of significant help, and I appreciate that more than if you had provided me with raw code; I really want to understand the concepts here, not just make this current project work. I've actually done pretty good so far with figuring out most things, but this one here was just beyond my grasp!

[Paladine]

OK, cool Toulinwoek, now we have a better basis to go from.

ADO.Net builds on the ADO in the sense of relational database functionality and as well as the principles of OOP. One key difference with ADO.Net is that it supports disconnected data processing. Which means you can continue to manipulate the data with out making further connections to the database or keeping an active open connectiong to the database.

Within ADO.Net Class structure there are classes which are Data Consumer Classes - those classes used for manipulating of the data, .Net Data Provider Classes - those classes which provide the interface with the database, and provided some data manipulating and retrieving features, and lastly other .Net Framework Classes - which are related to data handling, and are apart of the System.Data Namespace.

DataSet = an object that can contain data from a set of related tables. It contains those objects which represent components of those tables, such as; DataRelation (i.e. table relationships), DataTable (i.e. an object for each table), DataColumn, and DataRow.

With in those .Net Provider Classes I mentioned, are several classes, two of particular interest are: DataReader Class and DataAdpater Class. The DataReader Class provides a quick, forward & read-only access to data. While the DataAdapter provides the main link between the provider classes and the consumer class (those the manipulate the data).

DataAdpater Class : SQL, OleDB, Oracle, ODBC, or any other type
it has four Command objects which are 4 basic SQL type commands;

• SELECT<
• INSERT<
• UPDATE<
• DELETE <

Hence the tie into consumer class from provider class.

Example use:

using System;
using System.Data;
using System.Data.SqlClient;


namespace SQLRelation
{
	class DataRelation
	{
		[STAThread]
		static void Main(string[] args)
		{
			SqlConnection thisConnection = new SqlConnection("Network Library=DBMSSOCN;Data Source=192.168.0.100,1433;database=Northwind;User id=Me;Password=Me;");
			thisConnection.Open();

			DataSet thisDataSet = new DataSet();
		    // setup DataAdapter objects for each table and fill the adapter
		    SqlDataAdapter custAdapter = new SqlDataAdapter("SELECT * FROM Customers", thisConnection);
			
			custAdapter.Fill(thisDataSet, "Customers");

		    foreach (DataRow custRow in thisDataSet.Tables["Customers"].Rows)
			{
		 	Console.WriteLine("Customer ID: " + custRow["CustomerID"] + " Name: " + custRow["CompanyName"]);
			}
				
			thisConnection.Close();
		}
	}
}

I hope this explaination and example provide you some more insight.... if you still have questions feel free to ask.

Happy Coding
:cool:

[Toulinwoek]

Very good. I'm going to visit those links and if I have any questions, I'll post them later.

Thanks

[Paladine]

All correct, but closing the connection does just that...close the connection. It does nothing with DML (Data Manipluation Language) i.e. Update, Insert, etc.

In the process you mentioned it would be through the DataAdapter that those changes are passed to the DB .

Close connection simply disconnects you from the DB.

Hope that clears things up?

[Toulinwoek]

Thanks, I'm getting it now, and the links were helpful. I do have one other question:

From both programming and deployment (distribution) standpoints, which database would be better, Access or SQL? Seems to me that SQL is kind of like a language in itself and that might steepen my learning curve. Any suggestions?

[Paladine]

Well unfortunately it is not a simple better or worse. It depends on what you are doing / requiring.

From a distribution standpoint, and relatively small scale, the Access DB is far easier. SQL would require great setup costs (time and money).

Saying that, don't kid yourself. The general SQL language is the same in both applications (Access & SQL). True the datatypes are different, but work in the same manner. To use DDL (data definition language) and DML (data manipulation language) is essentially the same. Stored procedure use, and more sophisticated DB use (i.e. Triggers) is more in tune with SQL use.

As for speed, it is task related, a large SELECT with Inner Joins or Outer joins in SQL would be far quicker than Access. Access is labeled as not a true DB, but it depends on what you define a DB as. Hierarchy: Oracle = SQL, Access, the Excel or Lotus, then a flat file.

Hope this helps.

p.s. Please, no one start flamming me about Oracle = SQL, it is relatively speaking. :-)

Thanks, I'm getting it now, and the links were helpful. I do have one other question:

From both programming and deployment (distribution) standpoints, which database would be better, Access or SQL? Seems to me that SQL is kind of like a language in itself and that might steepen my learning curve. Any suggestions?

:D :D

[Toulinwoek]

OK. I've decided to do a sample project using an Access database. You were right, there isn't much difference between using Access or SQL from a task point of view. I'm working on a "learning" project, which is just a database app to catalog DVD's (no online capabilities at this point).

I have created the database, and it consists of 5 tables. 4 of those will be used to allow the user to populate several combo boxes (unless there's an easier way), then there's one main table. I have set up the connection to the database and created the data adapter and dataset. Here's what I'm now wondering:

1. Will I need to set up separate connections, adapters and datasets for each table?
2. If I have a datagrid to view records, how would I enable selecting a record to populate the coresponding fields on the main form?
3. Since some combo boxes get list values from separate tables in the database, where should the table relationships be set up, in the database, in the program, or both?

Finally, I've been studying code samples of adding records and it's hard to decipher some of it. I was looking at a book called "Programming in the key of C#" and wonder if you know anything about that book or would recommend it. If not, is there a good book you would recommend for beginners?

Thanks again!