Skip to content

InsertData

Jump to bottom
Darren Comeau edited this page Oct 11, 2018 · 4 revisions

InsertData(String, String, String[], Object[])

With the database you have setup, inserts data into the given object. Will insert into views where the database engine allows.

Overloads

public void InsertData(string schemaName, string objectName, string[] columns, Object[] data);
public void InsertData(string schemaName, string objectName, string[] columns, Object[] data, ColumnDefaults defaults);

InsertData(string schemaName, string objectName, string[] columns, Object[] data)

Parameters

schemaName String

The database schema which hosts the object.

objectName String

The name of the database table or view where the data should be inserted.

columns String[]

A string array of the columns which data is to be inserted into. The order here must match the order for the data.

data Object[] An array of objects where each object is a row in the table and is an array of the column data. The order of the data must match the order of the columns.

InsertData(string schemaName, string objectName, string[] columns, Object[] data, ColumnDefaults defaults)

Parameters

schemaName String

The database schema which hosts the object.

objectName String

The name of the database table or view where the data should be inserted.

columns String[]

A string array of the columns which data is to be inserted into. The order here must match the order for the data.

data Object[] An array of objects where each object is a row in the table and is an array of the column data. The order of the data must match the order of the columns.

defaults ColumnDefaults An object representing a collection of columns and the defaults to be applied to those columns. Any columns in the data section will take precedence over the default values here.

Examples

Basic insert of data into a table using all available columns.

// Setup the Database Tester object using the MS SQL implementation
var connectionstring = @"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=tempdb;Integrated Security=SSPI;";
VulcanAnalytics.DBTester.DatabaseTester tester = new VulcanAnalytics.DBTester.MsSqlDatabaseTester(connectionstring);

// Create a table to insert data into
tester.ExecuteStatementWithoutResult("drop table if exists [dbo].[TestPerson];");
tester.ExecuteStatementWithoutResult("create table [dbo].[TestPerson]([TestId] int, [Name] varchar(255), [ModifiedDate] datetime);");

// Parameters for InsertData method
var schemaName = "dbo";
var tableName = "TestPerson";
var columns = new string[] { "TestId", "Name", "ModifiedDate" };
var data = new object[]
    {
        new Object[]{1,"Joe","2018-09-28T12:49:13.576"},
        new Object[]{2,"John","2018-10-01T18:31:29.256"}
    };

// Call the Insertdata method to insert the rows
tester.InsertData(schemaName, tableName, columns, data);

Insert of data into a table using 3/4 available columns with a default value specified for the other column.

// Setup the Database Tester object using the MS SQL implementation
var connectionstring = @"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=tempdb;Integrated Security=SSPI;";
VulcanAnalytics.DBTester.DatabaseTester tester = new VulcanAnalytics.DBTester.MsSqlDatabaseTester(connectionstring);

// Create a table to insert data into
tester.ExecuteStatementWithoutResult("drop table if exists [dbo].[TestPerson];");
tester.ExecuteStatementWithoutResult("create table [dbo].[TestPerson]([TestId] int, [Name] varchar(255), [ModifiedDate] datetime, [ModifiedBy] sysname);");

// Set the defaults for the insert
var defaults = new ColumnDefaults();
defaults.AddDefault(new KeyValuePair<string, object>("ModifiedBy", "DarrenComeau"));

// Parameters for InsertData method
var schemaName = "dbo";
var tableName = "TestPerson";
var columns = new string[] { "TestId", "Name", "ModifiedDate" };
var data = new object[]
    {
        new Object[]{1,"Joe","2018-09-28T12:49:13.576"},
        new Object[]{2,"John","2018-10-01T18:31:29.256"}
    };

// Call the Insertdata method to insert the rows
tester.InsertData(schemaName, tableName, columns, data, defaults);

Insert of data into a table with a null column.

// Setup the Database Tester object using the MS SQL implementation
var connectionstring = @"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=tempdb;Integrated Security=SSPI;";
VulcanAnalytics.DBTester.DatabaseTester tester = new VulcanAnalytics.DBTester.MsSqlDatabaseTester(connectionstring);

// Create a table to insert data into
tester.ExecuteStatementWithoutResult("drop table if exists [dbo].[TestPerson];");
tester.ExecuteStatementWithoutResult("create table [dbo].[TestPerson]([TestId] int, [Name] varchar(255), [ModifiedDate] datetime, [ModifiedBy] sysname);");

// Set the defaults for the insert
var defaults = new ColumnDefaults();
defaults.AddDefault(new KeyValuePair<string, object>("ModifiedBy", "DarrenComeau"));

// Parameters for InsertData method
var schemaName = "dbo";
var tableName = "TestPerson";
var columns = new string[] { "TestId", "Name", "ModifiedDate" };
var data = new object[]
    {
        new Object[]{1,"Joe","2018-09-28T12:49:13.576"},
        new Object[]{2,null,"2018-10-01T18:31:29.256"}
    };

// Call the Insertdata method to insert the rows
tester.InsertData(schemaName, tableName, columns, data, defaults);

Remarks

Further variations of this method are intended to accept text formats such as Xml, Json, and Csv. Please get in touch if you require this, have a contribution or another format.

Clone this wiki locally