public override string BuildInsertStatementWithParameters(SchemaMapper.SchemaMapping.SchemaMapper schmapper, DataTable dt) { string strQuery = "INSERT INTO \"" + schmapper.SchemaName + "\".\"" + schmapper.TableName + "\" ("; string strValues = ""; foreach (DataColumn dc in dt.Columns) { strQuery = strQuery + "\"" + dc.ColumnName + "\","; strValues += "@" + dc.ColumnName + ","; } strQuery = strQuery.TrimEnd(',') + ") VALUES (" + strValues + ")"; return(strQuery); }
public override string BuildInsertStatement(SchemaMapper.SchemaMapping.SchemaMapper schmapper, DataTable dt, int startindex, int rowscount) { string strQuery = "INSERT INTO \"" + schmapper.SchemaName + "\".\"" + schmapper.TableName + "\" ("; foreach (DataColumn dc in dt.Columns) { strQuery = strQuery + "\"" + dc.ColumnName + "\","; } strQuery = strQuery.TrimEnd(',') + ") VALUES "; int i = startindex; int lastrowindex = startindex + rowscount; for (i = startindex; i <= lastrowindex; i++) { strQuery = strQuery + "("; foreach (var Col in schmapper.Columns) { switch (Col.DataType) { case SchemaMapper_Column.ColumnDataType.Date: strQuery += "'" + ((DateTime)dt.Rows[i][Col.Name]).ToString("yyyy-MM-dd HH:mm:ss") + "',"; break; case SchemaMapper_Column.ColumnDataType.Text: case SchemaMapper_Column.ColumnDataType.Memo: strQuery += "'" + dt.Rows[i][Col.Name].ToString() + "',"; break; case SchemaMapper_Column.ColumnDataType.Number: strQuery += dt.Rows[i][Col.Name].ToString() + ","; break; } } strQuery = strQuery.TrimEnd(',') + "),"; } strQuery = strQuery.TrimEnd(','); return(strQuery); }
public void InsertUsingOracleBulk(SchemaMapper.SchemaMapping.SchemaMapper schmapper, DataTable dt) { try { using (var bulkCopy = new OracleBulkCopy(ConnectionString, OracleBulkCopyOptions.Default)) { foreach (DataColumn col in dt.Columns) { bulkCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName); } bulkCopy.BulkCopyTimeout = 600; bulkCopy.DestinationTableName = "[" + schmapper.SchemaName + "].[" + schmapper.TableName + "]"; bulkCopy.WriteToServer(dt); } } catch (Exception ex) { throw ex; } }
public override void InsertIntoDb(SchemaMapper.SchemaMapping.SchemaMapper schmapper, DataTable dt, int rowsperbatch = 10000) { try { using (OracleConnection sqlcon = new OracleConnection(ConnectionString)) { if (sqlcon.State != ConnectionState.Open) { sqlcon.Open(); } int totalcount = dt.Rows.Count; int currentindex = 0; while (currentindex < totalcount) { string strQuery = ""; if ((currentindex + rowsperbatch) >= totalcount) { rowsperbatch = totalcount - currentindex - 1; } strQuery = BuildInsertStatement(schmapper, dt, currentindex, rowsperbatch); using (OracleCommand sqlcmd = new OracleCommand(strQuery, sqlcon)) { sqlcmd.ExecuteNonQuery(); currentindex = currentindex + rowsperbatch; } } } } catch (Exception ex) { throw ex; } }
public override void InsertIntoDbWithParameters(SchemaMapper.SchemaMapping.SchemaMapper schmapper, DataTable dt) { try { using (OracleConnection sqlcon = new OracleConnection(ConnectionString)) { if (sqlcon.State != ConnectionState.Open) { sqlcon.Open(); } string strQuery = BuildInsertStatementWithParameters(schmapper, dt); using (OracleTransaction trans = sqlcon.BeginTransaction()) { using (OracleCommand sqlcmd = new OracleCommand(strQuery, sqlcon)) { sqlcmd.CommandType = CommandType.Text; foreach (var Col in schmapper.Columns) { switch (Col.DataType) { case SchemaMapper_Column.ColumnDataType.Date: sqlcmd.Parameters.Add("@" + Col.Name, OracleDbType.Date); break; case SchemaMapper_Column.ColumnDataType.Text: sqlcmd.Parameters.Add("@" + Col.Name, OracleDbType.Varchar2); break; case SchemaMapper_Column.ColumnDataType.Memo: sqlcmd.Parameters.Add("@" + Col.Name, OracleDbType.Varchar2, 4000); break; case SchemaMapper_Column.ColumnDataType.Number: sqlcmd.Parameters.Add("@" + Col.Name, OracleDbType.Int64); break; } } foreach (DataRow drrow in dt.Rows) { foreach (var Col in schmapper.Columns) { sqlcmd.Parameters["@" + Col.Name].Value = drrow[Col.Name]; } sqlcmd.ExecuteNonQuery(); } trans.Commit(); } } } } catch (Exception ex) { throw ex; } }