logo
down
shadow

MySql BulkCopy/Insert from DataReader


MySql BulkCopy/Insert from DataReader

By : user3860592
Date : January 02 2021, 06:48 AM
should help you out Aside from using "LOAD DATA IN FILE" MySQL has a non-SQL Standard 'bulk insert' mechanism where you can specify multiple 'values' to be inserted: http://dev.mysql.com/doc/refman/5.0/en/insert.html
code :


Share : facebook icon twitter icon
BulkCopy Vs BCP Insert

BulkCopy Vs BCP Insert


By : Ben Black
Date : March 29 2020, 07:55 AM
To fix this issue You can use the SqlBulkCopy class instead. I've been using it many times, it's very efficient and easy to use.
Take a look at this post: http://social.msdn.microsoft.com/Forums/eu/adodotnetdataproviders/thread/dcc7a701-9acf-4c2f-a5d7-1fcb4dbb05f2
.NET BulkCopy Only Firing Trigger On One Insert

.NET BulkCopy Only Firing Trigger On One Insert


By : mnolan87110
Date : March 29 2020, 07:55 AM
Does that help I was already assigning a bulk group id to all the items. I accomplished this with the following:
code :
1. If item has bulk group id
2. If item is being inserted, not updated
3. Do a select into from a selection of items based on the bulk group id
    IF(@BulkGroupInsertId IS NULL OR EXISTS (SELECT * FROM DELETED))
    BEGIN
        -- Do Single Insert
    END
    ELSE
    BEGIN

    -- Bulk Insert

        INSERT INTO TeamSubscription (DivisionTeamId, PhoneNumber, DateCreated)
        SELECT tc.TeamId, p.MobilePhone, GETDATE()
        FROM
            -- Commented Out
        WHERE 
            -- Commented Out
        GROUP BY
            -- Commented Out

    END 
bulkcopy batch size affecting the insert

bulkcopy batch size affecting the insert


By : user3114940
Date : March 29 2020, 07:55 AM
will help you Take a look at this whitepaper that tests batch sizes extensively:
SQLBulkCopyPerformance
how to use sql Bulkcopy to insert datagridview rows in table

how to use sql Bulkcopy to insert datagridview rows in table


By : David M
Date : March 29 2020, 07:55 AM
hope this fix your issue First, some general observations that may help you better understand writing code in C#:
code :
// this line is declaring a new variable named 'dt' 
// and assigning it an instance of a new DataTable
DataTable dt = new DataTable();
// this line is immediately overwriting the new DataTable 
// assigned to 'dt' with the DataTable returned from 'GetDataTableFromDGV'
dt = GetDataTableFromDGV(DGV);
DataTable dt = GetDataTableFromDGV(DBV);
foreach (DataGridViewColumn column in dgv.Columns)
{
    if (column.Visible)
    {
        dt.Columns.Add();
    }
} 
copy.ColumnMappings.Add(0, 1);
// etc...
// assumes the columns in the DataGridView are named as follows:
var columns = new[] {"Id", "Invoice_Id", "Software_Id", "Price", "Quantity", "Sum" }
var dt = new DataTable();
foreach (DataGridViewColumn column in dgv.Columns)
{
    // note: this is case-sensitive
    if (columns.Contains(column.Name))
    {
        dt.Columns.Add();
    }
}
copy.ColumnMappings.Add("Invoice_Id", "DestinationCol1");
copy.ColumnMappings.Add("Software_Id", "DestinationCol2");
// etc...
private DataTable GetDataTableFromDGV(DataGridView dgv)
{
    // use your actual columns names instead of Col1, Col2, etc...
    var columns = new[] {"Invoice_Id", "Software_Id", "Price", "Quantity", "Sum" }
    var dt = new DataTable();
    foreach (DataGridViewColumn column in dgv.Columns)
    {
        if (columns.Contains(column.Name))
        {
            dt.Columns.Add();
        }
    }

    foreach (DataGridViewRow row in dgv.Rows)
    {
        DataRow newRow = dt.NewRow();
        foreach (string columnName in columns)
        {
            newRow[columnName] = row.Cells[columnName].Value
        }

        dt.Rows.Add(newRow);
    }
    return dt;
}
private void InsertDTtoDB(string ConnectionString, string TableName, DataGridView DGV)
{
    DataTable dt = GetDataTableFromDGV(DGV);
    using (SqlConnection cn = new SqlConnection(ConnectionString))
    {
        cn.Open();
        using (SqlBulkCopy copy = new SqlBulkCopy(cn))
        {   
            // update the "DestinationCol[x]" values to the destination column names
            copy.ColumnMappings.Add("Invoice_Id", "DestinationCol1");
            copy.ColumnMappings.Add("Software_Id", "DestinationCol2");
            copy.ColumnMappings.Add("Price", "DestinationCol3");
            copy.ColumnMappings.Add("Quantity", "DestinationCol4");
            copy.ColumnMappings.Add("Sum", "DestinationCol5");
            copy.DestinationTableName = TableName;
            copy.WriteToServer(dt);
        }
    }
}
private DataTable GetDataTableFromDGV(DataGridView dgv)
{
    // assumes the columns in the DataGridView are named as follows:
    var columns = new[] {"Invoice_Id", "Software_Id", "Price", "Quantity", "Sum" }
    var dt = new DataTable();
    foreach (DataGridViewColumn column in dgv.Columns)
    {
        if (columns.Contains(column.Name))
        {
            dt.Columns.Add();
        }
    }

    foreach (DataGridViewRow row in dgv.Rows)
    {
        // check if the row has a null "Invoice_Id" and exclude
        if(row.Cells["Invoice_Id"] == null
        {
            continue;
        }

        DataRow newRow = dt.NewRow();
        foreach (string columnName in columns)
        {
            newRow[columnName] = row.Cells[columnName].Value
        }

        dt.Rows.Add(newRow);
    }
    return dt;
}
How can I Do OLEDB BulkCopy to insert bulkrecords?

How can I Do OLEDB BulkCopy to insert bulkrecords?


By : samir
Date : March 29 2020, 07:55 AM
wish help you to fix your issue SqlBulkCopy uses a specific database feature, that has no generic OLEDB counterpart. Individual providers may have something similar, but you would have to use to provider-specific variant (or find a library that already wraps this, if one).
In short: as far as I know, you can't.
shadow
Privacy Policy - Terms - Contact Us © 35dp-dentalpractice.co.uk