Database update

[mce]

I have the following codes to update some data in the sql database. The dataset seems to be updated, but when i close the application and check the database, non of the value there is updated. What had i missed out here?

Random m_random;
        SqlDataAdapter m_sqlAdapter;
        DataSet m_Dataset;
        SqlConnection m_sqlConn;
        SqlCommandBuilder m_sqlCommandBuilder;

         string strConn = Properties.Settings.Default.testConnectionString.ToString();
            m_sqlConn = new SqlConnection(strConn);
      
            m_sqlAdapter = new SqlDataAdapter("SELECT * FROM Table1",m_sqlConn);
            m_sqlCommandBuilder = new SqlCommandBuilder(m_sqlAdapter);

         
            m_Dataset = new DataSet();  
         
            //m_sqlAdapter.SelectCommand = new SqlCommand("select * from Table1",m_strConn);
            m_sqlAdapter.Fill(m_Dataset,"Table1");


   foreach (DataRow dr in m_Dataset.Tables["Table1"].Rows)
            {
                int nRandom = 0;
  
                dr["Analog1"] = m_random.Next(100);
                
                m_sqlAdapter.Update(m_Dataset, "Table1");
                m_Dataset.AcceptChanges();
                 
            }

[foamy]

hmm, I've never seen this approach to an update method before... I'd do it like this:

using (SqlConnection connection = new SqlConnection("ConnectionString"))
            {
                String QueryString = "UPDATE Table SET Column = Value WHERE Column = "Something"";
                using (SqlCommand cmd = new SqlCommand(QueryString, connection))
                {
                    try
                    {
                        cmd.Connection.Open();
                        cmd.ExecuteNonQuery();
                    }
                    catch(Exception x)
                    {
                    }
                }
            }

To get the random generator into things, simply run a loop that includes this code... so you'll end up with:

SELECT statement -> saves the table data into a collection
loop -> updates each row in the table using the random number generator

[nelo]

I agree with Foamy although I had to use a similar approach once. I think the problem with the data set approach is that Update method on the data adapter is being called before AcceptChanges on the data set.

   foreach (DataRow dr in m_Dataset.Tables["Table1"].Rows)
   {
                int nRandom = 0;
  
                dr["Analog1"] = m_random.Next(100);                 
   }
   m_Dataset.AcceptChanges();
   m_sqlAdapter.Update(m_Dataset, "Table1");

Just for your information I don't think you need to call AcceptChanges on each change. Lastly I always go for explicit transactions if I can (i.e. BeginTransaction and Commit on success Rollback on failure using the SqlTransaction in this case) regardless of whether I'm using DataAdapter/DataSet or pure Command objects.

[mce]

I need to update/write to the database a few hundreads records very frequently and continuously from a thread. Since i have a few hundreds records to update, i just want to open connection to the database once, and on a separate thread, i will update the records in batch, so i don need to write to the database for every record. I can update a hundread records, and write to the database once after i have modified all the records. How can i do this? I tried using begin edit as follow, but i doesn't write to the database, regardless i do accept changes or not. However, if i don use BeginEdit and AcceptChanges, it write to the Database.

By the way, could you show me some codes on how to use explicit transactions as you mentioned?

 foreach (DataRow dr in m_Dataset.Tables["Table1"].Rows)
   {
                int nRandom = 0;
                dr.BeginEdit();
  
                dr["Analog1"] = m_random.Next(100);                 
   }
   m_Dataset.AcceptChanges();
   m_sqlAdapter.Update(m_Dataset, "Table1");

[Arjay]

I need to update/write to the database a few hundreads records very frequently and continuously from a thread. Since i have a few hundreds records to update, i just want to open connection to the database once, and on a separate thread,

If you open your db connection in one thread and attempt to use the connection in another thread, you will run into problems. Just open the connection in the thread where you need to use it, update the record(s) and close the connection.

Holding a connection open in the hopes of improving performance rarely works out (because modern data providers are smart enough to cache connections).

[mce]

I am now trying the sqltransaction and i just couldn't get my code to work, i.e write to the database.

Could you correct me where i am wrong?

This is what i have inside my update thread.

string updateSQL = "UPDATE MYTABLE SET "
     + "Analog=@myAnalog WHERE ID=@myID;";

  sqlTransaction transaction;
            try
            {
// Start a local transaction.
                if (_sqlConn.State == ConnectionState.Closed)
                    _sqlConn.Open();

_sqlCommand= new SqlCommand(updateSQL, _sqlConn);

for (//loop 100 records){

                
                transaction = _sqlConn.BeginTransaction("SampleTransaction");

             
                _sqlCommand.Transaction = transaction;

 _sqlCommand.Parameters.AddWithValue("@myAnalog", SomeNewValue);
                    _sqlCommand.Parameters.AddWithValue("@myID", (int)RecordIndex);


                    _sqlCommand.ExecuteNonQuery();

}

     transaction.Commit();

catch(Exception ex)
{

}

[Arjay]

Move the BeginTransaction(...) statement outside the for loop.

[Arjay]

Again, DO NOT keep the database connection open.

WRONG

if (_sqlConn.State == ConnectionState.Closed)
                    _sqlConn.Open();

[mce]

I still have problem on the database update, it looks like the sql update statement is wrong somewhere. The database is updated if i used a hardcoded statement as follow.

 _sqlCommand.CommandText = "UPDATE MYTABLE SET "
     + "Analog=16 WHERE ID=1234";

But what i want is a constant changing Analog value where i need to update dynamically like in my previous post.
But my database just doen't get updated if i do the following.

string updateSQL = "UPDATE MYTABLE SET "
     + "Analog=@myAnalog WHERE ID=@myID;";

 _sqlCommand.Parameters.AddWithValue("@myAnalog", SomeNewValue);
                   
 _sqlCommand.Parameters.AddWithValue("@myID", (int)RecordIndex);