Comparing Table Entries Using C# and MS Access

[VonHagNDaz]

Hey Guys, Saying I'm a rookie at databases would be giving me too much credit. That said, I'm working on a project where a manager can create a job for his employees. Each employee is stored in a table tblUsers and each job is stored in a table tblJobs. Now I know I have a few problems with the way my DB is set up. tblUsers has a primary key of UserName and tblJobs has a primary key of Job Descriptions. Each entry in tblJobs has a linked key, UserName from tblUsers is linked to Project lead from tblJobs. I have two ArrayLists, jobs and copyOfJobs. When a manager signs in, copyOfJobs is passed to what I call ManagerForm. In ManagerForm, the manager can make alterations to copyOfJobs, and once he exits, I want to compare jobs with copyOfJobs. The contents of jobs is pulled from an Access Database, then copied to copyOfJobs. Once ManagerForm is closed, I want to compare jobs to copyOfJobs. Durring the comparison I want to check if things such as project name, project lead, duedate for a particular assignment has changed. If changed, I want it removed from the database and reinserted with the new values. I'm completely stuck! Any advice/comments would be greatly appreciated. Here is some code to prove I've actually messed around with this before coming to you guys.

    public void GetUsers(ArrayList users)
    {
        object\[\] userData = new object\[4\];

        OleDbCommand dbCommand = new OleDbCommand("SELECT \* FROM tblUsers", this.\_dbConnection);

        try
        {
            OleDbDataReader dbReader = dbCommand.ExecuteReader();

            while (dbReader.Read())
            {
                User currentUser = new User();
                dbReader.GetValues(userData);

                //get user name
                currentUser.Name = userData\[0\].ToString();
                //get password
                currentUser.Password = userData\[1\].ToString();
                //get rights
                string temp = userData\[2\].ToString();
                int rights = Convert.ToInt16(temp);
                currentUser.Rights = (User.EN\_RIGHTS)rights;
                //get jobs
                //still thinking about this one


                users.Add(currentUser);
            }
            dbReader.Close();
        }
        catch (Exception e)
        {
            String temp = String.Format("DBManager::GetUsers: {0}", e.Message);

[Johan Hakkesteegt]

Okay, so that's just a lot of text, and the question seems to be drowning in it. That's why nobody is answering I guess. So I will answer what I am guessing is the core question here: The basic idea is to get the list with sql, compare stuff and change values with C# and finally update these values with sql. So first of all you'll want to use an UPDATE query instead of removing and reinserting each row.

//So this indeed gets you the list.
OleDbCommand dbCommand = new OleDbCommand("SELECT * FROM tblUsers", this._dbConnection);

//Your reader then loops through the rows and you compare and check stuff

//Then you apply some logic, and determine what the values should be

//And then somewhere at the end of your reader loop you actually update your database like so:
OleDbCommand dbUpdateCommand = new OleDbCommand("UPDATE tblUsers " _
"SET FieldOne = @ParameterOne " _
", FieldTwo = @ParameterTwo " _
"WHERE KeyField = @ParameterThree ", this._dbConnection);
dbUpdateCommand.Parameters.Add("@ParameterOne", _
SqlDbType.NVarChar).Value = SomeVariableHoldingTheValueInQuestion;
dbUpdateCommand.ExecuteNonQuery;

N.B. You may have to use a second connection for the update (I don't remember for sure)... N.B. I used the syntax for MS SQL. You may have to adapt it to MS Access syntax, as it often uses its own slightly different version. You can check this by building a simple update query with the MS Access Query wizard / query builder. A coding tip: try to avoid using index numbers for database fields in your code: currentUser.Name = userData["UserName"].ToString(); is better than currentUser.Name = userData[0].ToString(); because otherwise when you make a change in your database table or in the query, that will change the order of the fields, and then your code will run amok. Or you will be forced to edit your code all the time, and figure out which field is which index number over and over.

My advice is free, and you may get what you paid for.