public void DataAdapterBatch() { // Open a new connection to the network server running on localhost string host = "localhost"; int port = s_clientPort; string connStr = string.Format("server={0}:{1}", host, port); using (GFXDClientConnection conn = new GFXDClientConnection(connStr)) { conn.Open(); // create a table // using the base DbCommand class rather than GemFireXD specific class DbCommand cmd = conn.CreateCommand(); cmd.CommandText = "create table t1 (id int primary key," + " addr varchar(20))"; cmd.ExecuteNonQuery(); try { // populate DataTable from the underlying table cmd = conn.CreateCommand(); cmd.CommandText = "select * from t1"; DbDataAdapter adapter = new GFXDDataAdapter((GFXDCommand)cmd); adapter.SelectCommand = cmd; // associate a command builder with the DataAdapter new GFXDCommandBuilder((GFXDDataAdapter)adapter); // fill a DataTable using the above select SQL command // though there is no data to be populated yet, the schema will still be // set correctly in the DataTable even with no data which is required // before trying to make any updates DataTable table = new DataTable(); adapter.Fill(table); // set batch size for best performance adapter.UpdateBatchSize = s_numInserts; // now perform the inserts in the DataTable for (int i = 0; i < s_numInserts; i++) { DataRow newRow = table.NewRow(); newRow["ID"] = i; newRow["ADDR"] = "addr" + i; table.Rows.Add(newRow); } // apply the inserts to the underlying GemFireXD table adapter.Update(table); // check the inserts VerifyInserts(conn, s_numInserts); } finally { // drop the table cmd = new GFXDCommand("drop table t1", conn); cmd.ExecuteNonQuery(); conn.Close(); } } }
public void InsertBulkData(string tableName) { try { using (var conn = new GFXDClientConnection(ConnectionString)) { string[] words = tableName.Split('.'); GFXDCommand cmd = conn.CreateCommand(); cmd.CommandType = CommandType.Text; cmd.CommandText = "Select * from " + tableName + " order by " + words[1] + "ID"; conn.Open(); GFXDDataAdapter adapter = cmd.CreateDataAdapter(); var table = new DataTable(tableName); adapter.Fill(table); int cnt = table.Rows.Count; var idx = (int)table.Rows[cnt - 1].ItemArray[0]; var builder = new GFXDCommandBuilder(adapter); adapter.InsertCommand = builder.GetInsertCommand(); // Create new product row for (int ctx = 0; ctx < 10000; ctx++) { DataRow row = table.NewRow(); row[0] = ++idx; for (int i = 1; i < (table.Rows[cnt - 1].ItemArray.Count()); i++) { row[i] = table.Rows[cnt - 1].ItemArray[i]; } table.Rows.Add(row); } // Update the underlying table adapter.Update(table); } } catch (GFXDException ex) { string err = ex.Message; } catch (Exception ex) { string err = ex.Message; } }
public void UpdateBulkData(string tableName) { try { using (var conn = new GFXDClientConnection(ConnectionString)) { conn.Open(); //GFXDCommand cmd = conn.CreateCommand(); //cmd.CommandType = CommandType.Text; //cmd.CommandText = "Delete from Sales.SalesReason where SalesReasonID between 17000 and 19485"; //var da = new GFXDDataAdapter(cmd); //var dt = new DataTable(); //da.Fill(dt); GFXDCommand cmd = conn.CreateCommand(); DataTable dt = new DataTable(); var adapter = new GFXDDataAdapter(); cmd.CommandText = "SELECT * FROM Sales.SalesReason"; adapter.SelectCommand = cmd; DbCommandBuilder builder = new GFXDCommandBuilder(adapter); adapter.Fill(dt); for (int i = 500; i < 1000; i++) { dt.Rows[i].Delete(); } adapter.Update(dt); } } catch (GFXDException ex) { string err = ex.Message; } catch (Exception ex) { string err = ex.Message; } }