Solved

Saving changes in datagridview to sql database

Posted on 2010-11-27
10
1,664 Views
Last Modified: 2012-05-10
Building on an earlier question, once the record had been removed from the datagridview, the changes are not saved to the database.
//Delete button click event
void dgvAllMovies_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            if (e.ColumnIndex == 0)
            {  
             // remove the row in the datasource which will automatically remove row from the grid                
                    DataGridViewRow grigRow = dgvAllMovies.Rows[e.RowIndex];
                    DataRowView drv = grigRow.DataBoundItem as DataRowView;
                    DataTable dt = (DataTable)dgvAllMovies.DataSource;
                    //Delete confermation
                    if (MessageBox.Show("Delete "+ drv.Row[1].ToString()+"?","Confirm!", MessageBoxButtons.YesNo) == DialogResult.Yes)
                    {
                        dt.Rows.Remove(drv.Row);
                    }
                }
                else
                {
                    return;
                }
            }

 //Save button
private void btnSave_Click(object sender, EventArgs e)
        {
            {
                DataTable dt = (DataTable)dgvAllMovies.DataSource;
                 DAL data = new DAL();
                data.SaveData(dt, "select * from tblMovies");
            }

//DAL method
public void SaveData(DataTable dataTable, string sourceQuery)
 {
     using (SqlConnection conn = new SqlConnection(ConnectionString))
     {
         conn.Open();

         SqlDataAdapter adapter = new SqlDataAdapter();

         adapter.SelectCommand = new SqlCommand(sourceQuery, conn);

         SqlCommandBuilder commandBuilder = new SqlCommandBuilder(adapter);

         adapter.Update(dataTable);
     }
 }

Open in new window

0
Comment
Question by:98fatboyrider
  • 5
  • 4
10 Comments
 
LVL 20

Expert Comment

by:BuggyCoder
ID: 34222856
kindly go through this it will help:-

http://support.microsoft.com/kb/308507

0
 
LVL 10

Expert Comment

by:P1ST0LPETE
ID: 34223093
1. Your btnSave_Click() method is only passing a SELECT statement to your SaveData() method.  This doesn't delete anything from the Database.
2. Your SaveData() method is also only built to run a SELECT command and fill a table.

Based on how you have it setup, I assume you want to not make any changes until the user hits the *save* button.  In this case you need to compare the original data  with the current data in the DataGridView.  Something like this:


private void btnSave_Click(object sender, EventArgs e)
{
    DAL data = new DAL();
    DataTable currentData = (DataTable)dgvAllMovies.DataSource;
    DataTable orginalData = new DataTable();
    data.SaveData(originalData, "SELECT * FROM [tblMovies]");  //I'm sure you have another way to load data, but this would work.

    foreach(DataRow originalRow in orignalData.Rows)
    {
        bool rowFound = false;
        
        foreach(DataRow currentRow in currentData.Rows)
        {
            if(currentRow == originalRow)
            {
                rowFound = true;
                break;
            }
        }

        if(rowFound == false)
        {
            string deleteSQL = "DELETE FROM [tlbMovies] WHERE [ID] = " originalRow["ID"].ToString();
            SqlConnection conn = new SqlConnection(ConnectionString);
            SqlCommand cmd = new SqlCommand(deleteSQL, conn);
            cmd.Connection.Open();
            cmd.ExecuteNonQuery();
        }
    }
}

Open in new window

0
 

Author Comment

by:98fatboyrider
ID: 34223327
I'd like to commit the change after the confirmation message box, I don't need a separate save button. There seems to be an issue with the delete statement at "originalRow. Will the code below work?
void dgvAllMovies_CellContentClick(object sender, DataGridViewCellEventArgs e)
        { 
            if (e.ColumnIndex == 0)
            {  
               // remove the row in the datasource which will automatically remove row from the grid                
               DataGridViewRow grigRow = dgvAllMovies.Rows[e.RowIndex];
               DataRowView drv = grigRow.DataBoundItem as DataRowView;
               DataTable dt = (DataTable)dgvAllMovies.DataSource;
                //Delete confermation
                 if (MessageBox.Show("Delete "+ drv.Row[1].ToString()+"?","Confirm!", MessageBoxButtons.YesNo) == DialogResult.Yes)
                  {
                   dt.Rows.Remove(drv.Row);
                   DAL data = new DAL();
                   DataTable currentData = (DataTable)dgvAllMovies.DataSource;
                   DataTable orginalData = new DataTable();
                   data.SaveData(originalData, "SELECT * FROM [tblMovies]");
                 
                     foreach(DataRow originalRow in orignalData.Rows)
                        {
                            bool rowFound = false;
        
                             foreach(DataRow currentRow in currentData.Rows)
                            {
                                if(currentRow == originalRow)
                                  {
                                    rowFound = true;
                                     break;
                                   }
                            }

                                if(rowFound == false)
                                {
                                    string deleteSQL = "DELETE FROM [tblMovies] WHERE [ID] = "originalRow[ID].ToString();
                                    SqlConnection conn = new SqlConnection(ConnectionString);
                                    SqlCommand cmd = new SqlCommand(deleteSQL, conn);
                                    cmd.Connection.Open();
                                    cmd.ExecuteNonQuery();
                                }
                            }
                         }
                       }
 
                  else
                   {
                     return;
                   }
            }

Open in new window

0
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 10

Accepted Solution

by:
P1ST0LPETE earned 500 total points
ID: 34225767
Ok, if you want to commit the change in the database right after the confirmation box, then things become a lot easier.

You should have an index or "ID" column in your tlbMovies right?  So you should be able to do this:
 
void dgvAllMovies_CellContentClick(object sender, DataGridViewCellEventArgs e)
{ 
     if (e.ColumnIndex == 0)
     {    
         DataGridViewRow gridRow = dgvAllMovies.Rows[e.RowIndex]
         DataRowView drv = gridRow.DataBoundItem as DataRowView;
         
         //Delete confermation
         if (MessageBox.Show("Delete "+ drv.Row[1].ToString()+"?","Confirm!", MessageBoxButtons.YesNo) == DialogResult.Yes)
         {
             //Replace "ID" with column name of the index column in tlbMovies:
             string id = gridRow.Cells["ID"].Value.ToString();

             string deleteSQL = "DELETE FROM [tblMovies] WHERE [ID] = id;
             SqlConnection conn = new SqlConnection(ConnectionString);
             SqlCommand cmd = new SqlCommand(deleteSQL, conn);
             try
             {
                 cmd.Connection.Open();
                 cmd.ExecuteNonQuery();
             }
             catch(Exception ex)
             {
                 //Uncomment this code when testing/developing: 
                 //MessageBox.Show("Error: " + ex.ToString());
             }
             finally
             {
                 cmd.Connection.Close();
                 cmd.Dispose();
                 conn.Dispose();
             }
         }

         //Once delete has finished, reload the DataSource from the database, and rebind the DataSource to the DataGridView.

         //Again, you probably have a better method to load data (like how you load the DataGridView when the app first runs)
         //But this would work as well:
         DataTable dbData = new DataTable();
         data.SaveData(dbData, "SELECT * FROM [tblMovies]");
     }
}

Open in new window

However, I'm assuming that DAL stands for Data Access Layer?  In your DAL class you probably want to have a generic method named "ExecuteNonQuery" that receives an sql string as a parameter.  Then you do all the Database work in your database class.  If you make all the classes in your database (DAL) class generic, then you can copy/paste and reuse this class in multiple projects in the future.  Something like this:
 
public void ExecuteNonQuery(string SQL)
{
    try
    {
        conn = new SqlConnection(conn);
        cmd = new SqlCommand(SQL, conn);
        cmd.Connection.Open();
        cmd.ExecuteNonQuery();
    }
    catch (Exception ex)
    {
        //Handle error.....
    }
    finally
    {
        cmd.Connection.Close();
        cmd.Dispose();
        conn.Dispose();
    }
}

Open in new window

And a generic way to fill a DataTable like this:
 
public DataTable DataTable(string SQL)
{
    DataTable dt = new DataTable();
    try
    {
        conn = new SqlConnection(conn);
        da = new SqlDataAdapter(SQL, conn);
        da.Fill(dt);
    }
    catch (Exception ex)
    {
        //Handle error.....
    }
    finally
    {
        da.Dispose();
        conn.Dispose();
    }
    return dt;
}

Open in new window

0
 

Author Comment

by:98fatboyrider
ID: 34259378
Sorry for the delayed response. I'm reworking my data access layer class to include the generic method and datatable you recommended. I will post my updated code this weekend. Thank you.
0
 
LVL 10

Expert Comment

by:P1ST0LPETE
ID: 34294572
I have a Database class that I just copy/paste and use in many of my projects.
Looks like this:
 
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web;

namespace YourProject.Classes
{
    public static class Database
    {
        private static SqlConnection connnection = null;
        private static SqlCommand command = null;
        private static SqlDataAdapter dataAdapter = null;
        private static String connString = ConfigurationManager.ConnectionStrings["YourConnectionStringFromWeb.Config"].ToString();
        private static String Error = string.Empty;

        public static void ExecuteNonQuery(string SQL)
        {
            try
            {
                connnection = new SqlConnection(connString);
                command = new SqlCommand(SQL, connnection);
                command.Connection.Open();
                command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                string error = "SQL string: " + SQL + "\r\n\t" + ex.ToString();
                SystemErrors.RecordError(error, "Database.ExecuteNonQuery()");
            }
            finally
            {
                command.Connection.Close();
                command.Dispose();
                connnection.Dispose();
            }
        }

        public static String ExecuteScalar(string SQL)
        {
            string result = string.Empty;

            try
            {
                connnection = new SqlConnection(connString);
                command = new SqlCommand(SQL, connnection);
                command.Connection.Open();
                result = command.ExecuteScalar().ToString();
            }
            catch (Exception ex)
            {
                string error = "SQL string: " + SQL + "\r\n\t" + ex.ToString();
                SystemErrors.RecordError(error, "Database.ExecuteScalar()");
            }
            finally
            {
                command.Connection.Close();
                command.Dispose();
                connnection.Dispose();
            }

            return result;
        }

        public static SqlDataReader LoadSqlReader(string SQL)
        {
            SqlDataReader reader = null;
            try
            {
                connnection = new SqlConnection(connString);
                command = new SqlCommand(SQL, connnection);
                command.Connection.Open();
                reader = command.ExecuteReader();
            }
            catch (Exception ex)
            {
                string error = "SQL string: " + SQL + "\r\n\t" + ex.ToString();
                SystemErrors.RecordError(error, "Database.LoadSqlReader()");

                command.Connection.Close();
                command.Dispose();
                connnection.Close();
            }
            return reader;
        }

        public static DataTable GetDataTable(string SQL)
        {
            DataTable table = new DataTable();
            try
            {
                connnection = new SqlConnection(connString);
                dataAdapter = new SqlDataAdapter(SQL, connnection);
                dataAdapter.Fill(table);
            }
            catch (Exception ex)
            {
                string error = "SQL string: " + SQL + "\r\n\t" + ex.ToString();
                SystemErrors.RecordError(error, "Database.FillDataTable()");
            }
            finally
            {
                dataAdapter.Dispose();
                connnection.Dispose();
            }
            return table;
        }

        public static class StoredProcedure
        {
            public static void ExecuteNonQuery(String StoredProcedureName, SqlParameter[] Parameters, Connections dbconnection)
            {
                try
                {
                    connnection = new SqlConnection(connString);
                    command = new SqlCommand(StoredProcedureName, connnection);
                    command.CommandType = CommandType.StoredProcedure;

                    foreach (SqlParameter parameter in Parameters)
                        command.Parameters.Add(parameter);

                    command.Connection.Open();
                    command.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    string error = "Stored Procedure Name: '" + StoredProcedureName + "'\r\n\t" + ex.ToString();
                    SystemErrors.RecordError(error, "Database.StoredProcedure.ExecuteNonQuery()");
                }
                finally
                {
                    command.Connection.Close();
                    command.Dispose();
                    connnection.Dispose();
                }
            }

            public static String ExecuteScalar(String StoredProcedureName, SqlParameter[] Parameters, Connections dbconnection)
            {
                string result = string.Empty;
                try
                {
                    connnection = new SqlConnection(connString);
                    command = new SqlCommand(StoredProcedureName, connnection);
                    command.CommandType = CommandType.StoredProcedure;

                    foreach (SqlParameter parameter in Parameters)
                        command.Parameters.Add(parameter);

                    command.Connection.Open();
                    result = command.ExecuteScalar().ToString();
                }
                catch (Exception ex)
                {
                    string error = "Stored Procedure Name: '" + StoredProcedureName + "'\r\n\t" + ex.ToString();
                    SystemErrors.RecordError(error, "Database.StoredProcedure.ExecuteScalar()");
                }
                finally
                {
                    command.Connection.Close();
                    command.Dispose();
                    connnection.Dispose();
                }
                return result;
            }

            public static DataTable GetDataTable(String StoredProcedureName, SqlParameter[] Parameters, Connections dbconnection)
            {
                DataTable table = new DataTable();
                try
                {
                    connnection = new SqlConnection(connString);
                    command = new SqlCommand(StoredProcedureName, connnection);
                    command.CommandType = CommandType.StoredProcedure;

                    foreach (SqlParameter parameter in Parameters)
                        command.Parameters.Add(parameter);

                    dataAdapter = new SqlDataAdapter(command);
                    dataAdapter.Fill(table);
                }
                catch (Exception ex)
                {
                    string error = "Stored Procedure Name: '" + StoredProcedureName + "'\r\n\t" + ex.ToString();
                    SystemErrors.RecordError(error, "Database.StoredProcedure.FillDataTable()");
                }
                finally
                {
                    dataAdapter.Dispose();
                    command.Dispose();
                    connnection.Dispose();
                }
                return table;
            }
        }
    }
}

Open in new window


So with it being static, you can do something easy like this:

DataTable table = Database.GetDataTable("SELECT * FROM [TableName]");

Also, as you may have guessed, "SystemErrors" is another static class I have created for storing my application errors.
0
 
LVL 10

Expert Comment

by:P1ST0LPETE
ID: 34294603
oops. forgot to delete the "Connections dbconnection" from the stored proc methods.  Forgot to strip that out before I posted.  Just remove it from the method and it should work just fine.
0
 

Author Comment

by:98fatboyrider
ID: 34297942
I think this is close but I'm getting a "Newline in constraint" error on the DELETE statement below.
string deleteSQL = "DELETE FROM [tblMovies] WHERE [MovieID] = id;

Open in new window

0
 
LVL 10

Assisted Solution

by:P1ST0LPETE
P1ST0LPETE earned 500 total points
ID: 34301399
Change it to this:

string deleteSQL = "DELETE FROM [tblMovies] WHERE [MovieID] = " + id;
0
 

Author Closing Comment

by:98fatboyrider
ID: 34325106
I still need to workout an issue with the connection string but, this was a great help. Thank you.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question