Checking the MySQL Database

[Dmennite]

Hello all,

Not sure if this is the proper forum for this but first:

I am using Visual Studio 2008 and MySQL Server Version 5.1.42 and Client Version 5.1.11 programming language C#

issue:
save button from windows form for record editing using databindings and textboxes...

is there a way to check if the value in the textboxes match the field in the database so it does not overwrite the field if it has not been changed.

I had thought about creating a stored procedure that throws the fields into strings which then could be checked if each equals corresponding textbox.text...
if so do nothing otherwise update that field...

I see alot of redundant coding to accomplish this this way... was wondering if someone had an idea that would require less coding within my program...

for example:

private void btnSavDealer_Click(object sender, EventArgs e)
        {
            //Are you sure box
            DialogResult dlgResult = MessageBox.Show("Are you sure you wish to save changes to this Dealer?", "Continue?", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
            if (dlgResult == DialogResult.No)
            {
                // No, Do nothing and set focus back to dealer name
                txtDealName.Focus();
                txtDealName.SelectAll();
                return;
            }
            else if (dlgResult == DialogResult.Yes)
            {

                //Create Database connection
                MySqlConnection conn = new MySqlConnection("server=localhost;user id=root;Password=******;persist security info=True;database=central1;");

                //get Dealer Name from Database
                try
                {
                    conn.Open();
                    MySqlCommand myCommand = new MySqlCommand(spname, conn);
                    myCommand.CommandType = CommandType.StoredProcedure;
                    myCommand.Parameters.AddWithValue("@dealerID", lblDealNum2.Text);
                    myCommand.Parameters["@dealerID"].Direction = ParameterDirection.Input;
                    myCommand.Parameters.Add(new MySqlParameter("@dealername", MySqlDbType.VarChar));
                    myCommand.Parameters["@dealername"].Direction = ParameterDirection.Output;
                    myCommand.ExecuteNonQuery();
                    dn = (string)myCommand.Parameters["@dealername"].Value;
                    conn.Close();
                }
                catch (MySqlException MyException)
                {
                    MessageBox.Show("Stored procedure error: MySQL code: " + MyException.Number
                                      + "  " + MyException.Message);
                    conn.Close();
                }

                // MessageBox.Show(dn); //Test to make sure retrieving correct name

                //Dealer Name
                if (txtDealName.Text == "")
                {
                    MessageBox.Show("You must enter a name for this Dealer.");
                    txtDealName.Focus();
                    txtDealName.SelectAll();
                    return;
                }
                else if (txtDealName.Text == dn)
                {
                    //Do nothing
                }
                else
                {
                    //MessageBox.Show(txtDealName.Text); //More testing
                    string query = "UPDATE dealer SET DealName = '" + txtDealName.Text + "' WHERE DealNum = '" + lblDealNum2.Text + "'";
                    MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(query);

                    try
                    {
                        conn.Open();
                        cmd.Connection = conn;
                        cmd.ExecuteNonQuery();
                        conn.Close();
                    }

                    catch (MySqlException)
                    {
                        System.Diagnostics.Debugger.Break();
                    }
                }
                this.Close();
            }
        }

This one works but uses a single query stored procedure that returns only dealer name...
I am trying for an easier way as I have 10 to 15 fields to check

[sknake]

>> is there a way to check if the value in the textboxes match the field in the database so it does not overwrite the field if it has not been changed.

It is very redundant so Microsoft included code generators for DALs (data access layers). You can create a typed dataset in your project (File -- New -- Dataset) that will generate the code for updates, inserts, deletes, etc. You should either start using datasets or write a code generator for authoring the sprocs and c# code to interface with your database.

[Dmennite]

Scott,

Yeah I have the dataset there, however being somewhat of a newb when it comes to database management within code...

I just don't want the code to update the database if the value has not been changed (less writing... less chance of corruption).

I figured out how to do use the dataset with a datagridview on another form but not when i am not using a dgv...

private void btnDelDeal_Click(object sender, EventArgs e)
        {
            DialogResult dlgResult = MessageBox.Show("Are you sure you wish to DELETE this Dealer?", "Continue?", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
            if (dlgResult == DialogResult.No)
            {
                // No, Do nothing
            }
            else if (dlgResult == DialogResult.Yes)
            {
                foreach (DataGridViewRow row in dealerDataGridView.SelectedRows)
                {
                    dealerDataGridView.Rows.Remove(row);
                }
                try
                {
                    this.Validate();
                    this.dealerBindingSource.EndEdit();
                    this.dealerTableAdapter.Update(this.central1DataSet.dealer);
                    MessageBox.Show("Update successful");
                }
                catch (System.Exception)
                {
                    MessageBox.Show("Update failed");
                }
            }
        }

if there are any tutorials that you know of that discuss datasets in a detail view setting please let me know.

Dennis Parker
IT Manager
AlarmSoft

[sknake]

You can drop a grid on the form to wire up the datasource property to create the datasets in the designer then delete the grid. I do that or mess with the control databinding property so it will create a bindingsource as well.

As far as not writing a field if it didn't change -- there is very little risk of that causing an issue. Journaled filesystems and transaction logs will prevent data corruption or help recover from it so I wouldn't worry about that too much. I don't think the slight, if any, performance benefit justifies pushing only changed fields.

[Dmennite]

Thanks again Scott as always I look for your informative replies when posting here.

Dennis Parker