Insert dataset records in database

Question

I need to insert the dataset records(C#) into the MS Accessdatabase table. I need to do the bulk insertion of records.

How can I do this in C#

Answer 1

For this sort of task, consider using the data adapter abstraction. With an Microsoft Access database, you can use the OleDbDataAdapter implementation as shown in the example below:

// Prerequisite: The data to be inserted is available in a DataTable/DataSet.
var data = new DataTable();
data.Columns.Add("CompanyName", typeof(string));
data.Columns.Add("Phone", typeof(string));
data.Rows.Add("Foo", "12345678");
data.Rows.Add("Bar", "87654321");

// Now, open a database connection using the Microsoft.Jet.OLEDB provider.
// The "using" statement ensures that the connection is closed no matter what.
using (var connection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data source=Northwind.mdb"))
{
    connection.Open();

    // Create an OleDbDataAdapter and provide it with an INSERT command.
    var adapter = new OleDbDataAdapter();
    adapter.InsertCommand = new OleDbCommand("INSERT INTO Shippers (CompanyName, Phone) VALUES (@CompanyName , @Phone)", connection);
    adapter.InsertCommand.Parameters.Add("CompanyName", OleDbType.VarChar, 40, "CompanyName");
    adapter.InsertCommand.Parameters.Add("Phone", OleDbType.VarChar, 24, "Phone");

    // Hit the big red button!
    adapter.Update(data);
}

You can do the same against other brands of database engines as well by replacing OleDbCommand, OleDbDataAdapter and OleDbConnection with the appropriate implementations for your database engine. For Microsoft SQL Server, look for classes prefixed with Sql, eg. SqlCommand.

Answer 2

   public void insert_dataset(DataSet ds,string ret_table, string table, string fileds, ArrayList arr_data)
    {
        ArrayList arr_rec=new ArrayList();

        string[] str_fields = fileds.Split(',');

        for (int i=0;i<ds.Tables[ret_table].Rows.Count;i++)
        {
            for (int j = 0; j < str_fields.Length; j++)
            {
                arr_rec.Add(ds.Tables[ret_table].Rows[i].ItemArray[j]);
            }

            insert_table(table, fileds, arr_rec);

            arr_rec.Clear();
        }


    }
    public void insert_table(string table,string fileds,ArrayList arr_data)
    {
        string str_command, str_params;

        string[] str_fields = fileds.Split(',');

        for (int i = 0; i < str_fields.Length; i++)
        {
            str_fields[i] = "@" + str_fields[i].Trim();
        }

        str_params = string.Join(",", str_fields);

        str_command = "INSERT INTO " + table + "(" + fileds + ") values(" + str_params + ")";

        con = new OleDbConnection();
        //for sql
        //con=new SqlConnection();

        con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + frm_main.cur_directory + "\\db_temp1.mdb;Persist Security Info=True";
        //for sql
        //con.ConnectionString="server=(local);trusted_connection=yes;database=telephon;";

        cmd = con.CreateCommand();
        //for sql
        //cmd=new SqlCommand();
        cmd.Connection = con;

        con.Open();

        cmd.CommandText =str_command;

       // cmd.Parameters.AddWithValue("@ACagname", "2");
       for (int i = 0; i < arr_data.Count; i++)
        {
            cmd.Parameters.AddWithValue(str_fields[i],arr_data[i]);
        }

        cmd.ExecuteNonQuery();

        con.Close();

    }