C# – insert the values using Mysqldataadapter.update( )

cMySQL

string InsertQuery = ""; 
                     MySqlTransaction transaction;
               MySqlConnection con1 = new MySqlConnection();
                     MySqlDataAdapter ODA;
                     string server = loggerConnectionString.servername;
con1.ConnectionString ="server="localhost";uid=root;pwd=;database=globasys;";

                     con1.Open();
                     transaction = con1.BeginTransaction();

                     ODA = new MySqlDataAdapter();

                     InsertQuery = "Insert into tr_config_unit_params " + Environment.NewLine;
                     InsertQuery += "(unit_param_id,unit_id, unit_param_desc, unit_param_opc_progid, unit_param_host, unit_param_link, unit_param_data_type, unit_param_type) " + Environment.NewLine;
                     InsertQuery += " VALUES(@unit_param_id,@unit_id,@unit_param_desc, @unit_param_opc_progid, @unit_param_host, @unit_param_link, @unit_param_data_type, @unit_param_type)";;
                     ODA.InsertCommand =new   MySqlCommand(InsertQuery, con1);
                     ODA.InsertCommand.Parameters.Add("@unit_param_id", MySqlDbType.Int32);
                     ODA.InsertCommand.Parameters.Add("@unit_id"", MySqlDbType.Int32);
                     ODA.InsertCommand.Parameters.Add("@unit_param_desc", MySqlDbType.VarChar);
                     ODA.InsertCommand.Parameters.Add("@unit_param_opc_progid", MySqlDbType.VarChar);
                     ODA.InsertCommand.Parameters.Add("@unit_param_host", MySqlDbType.VarChar);
                     ODA.InsertCommand.Parameters.Add("@unit_param_link", MySqlDbType.VarChar);
                     ODA.InsertCommand.Parameters.Add("@unit_param_data_type", MySqlDbType.Int32);
                     ODA.InsertCommand.Parameters.Add("@unit_param_type", MySqlDbType.Int32);

                     ODA.InsertCommand.Transaction = transaction;

                     DataSet ds = new DataSet();
                     ds = dt;

                  int y=   ODA.Update(dt,"tr_config_unit_params");


                     transaction.Commit();
                  con1.Close();

i have insert the 150000 rows using Mysqldataadapter.update() but the query execute successfully but there is no row insert into the database table in MYSQL

thanks in advance…..

Best Answer

Update function of MySqlDataAdapter is overloaded. Calls the respective INSERT, UPDATE, or DELETE statements for each inserted, updated, or deleted row in the specified DataSet.

So, you can use following code:

string conn = "server="localhost";uid=root;pwd=;database=globasys;";

using (MySqlConnection con1 = new MySqlConnection(conn))
{
con1.Open();

MySqlTransaction transaction = con1.BeginTransaction();

string InsertQuery = "Insert into tr_config_unit_params " + Environment.NewLine;
InsertQuery += "(unit_param_id,unit_id, unit_param_desc, unit_param_opc_progid, unit_param_host, unit_param_link, unit_param_data_type, unit_param_type) " + Environment.NewLine;
InsertQuery += " VALUES(@unit_param_id,@unit_id,@unit_param_desc, @unit_param_opc_progid, @unit_param_host, @unit_param_link, @unit_param_data_type, @unit_param_type)";;

MySqlCommand command = new MySqlCommand(InsertQuery, con1);
command.Transaction = transaction;

try
{
    //dt is a DataTable
    foreach(DataRow dataRow in dt)
    {
        command.Parameters.Clear()
        command.Parameters.AddWithValue("@unit_param_id", dataRow["unit_param_id"]);
        command.Parameters.AddWithValue("@unit_id", dataRow["unit_id"]);
        command.Parameters.AddWithValue("@unit_param_desc", dataRow["unit_param_desc"]);
        command.Parameters.AddWithValue("@unit_param_opc_progid", dataRow["unit_param_opc_progid"]);
        command.Parameters.AddWithValue("@unit_param_host", dataRow["unit_param_host"]);
        command.Parameters.AddWithValue("@unit_param_link", dataRow["unit_param_link"]);
        command.Parameters.AddWithValue("@unit_param_data_type", dataRow["unit_param_data_type"]);
        command.Parameters.AddWithValue("@unit_param_type", dataRow["unit_param_type"]);

        command.ExecuteNonQuery();
    }

    transaction.Commit();
}
catch(MySqlException mySqlEx)
{
    transaction.Rollback();

    throw mySqlEx;
}
}

This is the style that Microsoft tends to use in their examples.

It appears that the guidance in this area may have changed, as StyleCop now enforces the use of the C# specific aliases.

C# – Should ‘using’ directives be inside or outside the namespace

There is actually a (subtle) difference between the two. Imagine you have the following code in File1.cs:

// File1.cs
using System;
namespace Outer.Inner
{
    class Foo
    {
        static void Bar()
        {
            double d = Math.PI;
        }
    }
}

Now imagine that someone adds another file (File2.cs) to the project that looks like this:

// File2.cs
namespace Outer
{
    class Math
    {
    }
}

The compiler searches Outer before looking at those using directives outside the namespace, so it finds Outer.Math instead of System.Math. Unfortunately (or perhaps fortunately?), Outer.Math has no PI member, so File1 is now broken.

This changes if you put the using inside your namespace declaration, as follows:

// File1b.cs
namespace Outer.Inner
{
    using System;
    class Foo
    {
        static void Bar()
        {
            double d = Math.PI;
        }
    }
}

Now the compiler searches System before searching Outer, finds System.Math, and all is well.

Some would argue that Math might be a bad name for a user-defined class, since there's already one in System; the point here is just that there is a difference, and it affects the maintainability of your code.

It's also interesting to note what happens if Foo is in namespace Outer, rather than Outer.Inner. In that case, adding Outer.Math in File2 breaks File1 regardless of where the using goes. This implies that the compiler searches the innermost enclosing namespace before it looks at any using directive.

Best Answer

Related Solutions

C# – the difference between String and string in C#

This is the style that Microsoft tends to use in their examples.

C# – Should ‘using’ directives be inside or outside the namespace

Related Topic