public async Task InsertAsync()
{
var batchInsertNum = 1000000;
var batchTable = new DbTable("t_test");
batchTable.AddColumn(new DbColumn
{
AutoIncrement = true,
DataType = typeof(long),
Name = "id"
});
batchTable.AddColumn("name");
batchTable.AddColumn("no");
for (var i = 0; i < batchInsertNum; i++)
{
var row = batchTable.AddRow();
row["name"] = "SmartSql-" + Guid.NewGuid().ToString("N");
row["no"] = i;
}
using (BatchInsert batchInsert = new BatchInsert(_sqlMapper))
{
batchInsert.Table = batchTable;
await batchInsert.InsertAsync();
}
}
public async Task InsertAsync()
{
var batchInsertNum = 1000000;
var batchTable = new DbTable("t_test");
batchTable.AddColumn("Name");
batchTable.AddColumn("No");
for (var i = 0; i < batchInsertNum; i++)
{
var row = batchTable.AddRow();
row["Name"] = "SmartSql-" + Guid.NewGuid().ToString("N");
row["No"] = i;
}
using (BatchInsert batchInsert = new BatchInsert(_sqlMapper))
{
batchInsert.Table = batchTable;
batchInsert.AddColumnMapping(new ColumnMapping
{
Column = "Name",
Mapping = "name"
});
batchInsert.AddColumnMapping(new ColumnMapping
{
Column = "No",
Mapping = "no"
});
await batchInsert.InsertAsync();
}
}
internal void CreatePluginDatabase()
{
DbTable table = this.Database.CreateTable("modules");
table.AddColumn("name", DbType.String);
table.AddColumn("location", DbType.String);
table.AddColumn("enabled", DbType.Boolean);
table.AddColumn("hash", DbType.String);
}
private static void InitDbTableColumns(IDataReader dataReader, DbTable dbTable)
{
for (int i = 0; i < dataReader.FieldCount; i++)
{
string colName = dataReader.GetName(i);
Type colType = dataReader.GetFieldType(i);
dbTable.AddColumn(colName, colType);
}
}
public void Insert()
{
var batchInsertNum = 1000000;
var batchTable = new DbTable("t_test");
batchTable.AddColumn(new DbColumn
{
AutoIncrement = true,
DataType = typeof(long),
Name = "id"
});
batchTable.AddColumn("name", typeof(string));
batchTable.AddColumn("no", typeof(int));
for (var i = 0; i < batchInsertNum; i++)
{
var row = batchTable.AddRow();
row["name"] = "SmartSql-" + Guid.NewGuid().ToString("N");
row["no"] = i;
}
using (BatchInsert batchInsert = new BatchInsert(_sqlMapper))
{
batchInsert.Table = batchTable;
batchInsert.AddColumnMapping(new ColumnMapping
{
Column = "id",
Mapping = "Id"
});
batchInsert.AddColumnMapping(new ColumnMapping
{
Column = "name",
Mapping = "Name"
});
batchInsert.AddColumnMapping(new ColumnMapping
{
Column = "no",
Mapping = "No"
});
batchInsert.Insert();
}
}
// Columns: TABLE_NAME COLUMN_NAME ORDINAL_POSITION CONSTRAINT_NAME
// Empty: TABLE_CATALOG TABLE_SCHEMA CONSTRAINT_CATALOG CONSTRAINT_SCHEMA
public override DbTable GetTableConstraintColumns()
{
//We manually construct result table by merging information from 2 sources - for PKs and FKs, separately
var dtCols = new DbTable();
dtCols.AddColumn("TABLE_NAME", typeof(string));
dtCols.AddColumn("COLUMN_NAME", typeof(string));
dtCols.AddColumn("CONSTRAINT_NAME", typeof(string));
dtCols.AddColumn("ORDINAL_POSITION", typeof(int));
dtCols.AddColumn("TABLE_CATALOG", typeof(string));
dtCols.AddColumn("TABLE_SCHEMA", typeof(string));
dtCols.AddColumn("CONSTRAINT_SCHEMA", typeof(string));
dtCols.AddColumn("CONSTRAINT_CATALOG", typeof(string));
//Add Primary key columns
var dtIndexCols = GetIndexColumns();
foreach (DbRow pkRow in dtIndexCols.Rows)
{
var ixName = pkRow.GetAsString("INDEX_NAME");
if (IsPrimaryKeyIndex(ixName))
{
AddKeyColumnRow(dtCols, ixName, pkRow.GetAsString("TABLE_NAME"), pkRow.GetAsString("COLUMN_NAME"), pkRow.GetAsInt("COLUMN_ORDINAL_POSITION"));
}
}
//Add Foreign key columns
foreach (var tbl in this.Model.Tables)
{
var sql = string.Format("pragma foreign_key_list([{0}])", tbl.TableName);
// returns id, seq, table, from, to, on_update, on_delete, match
var dt = ExecuteSelect(sql);
foreach (DbRow fkRow in dt.Rows)
{
var fromColName = fkRow.GetAsString("from");
var fromCol = tbl.Columns.FindByName(fromColName);
var id = fkRow.GetAsInt("id");
var keyNamePrefix = "FK_" + tbl.TableName + "_" + id + "_";
var key = tbl.Keys.FirstOrDefault(k => k.Name.StartsWith(keyNamePrefix));
if (key != null)
{
AddKeyColumnRow(dtCols, key.Name, tbl.TableName, fkRow.GetAsString("from"), fkRow.GetAsInt("seq"));
}
}
}
return(dtCols);
}
// 将 SchemaTable 的数据转为 DbTable 的字段声明，创建 DbTable, 填充 _columns 并返回
public static DbTable NewDbTable(DataTable t)
{
if (t == null)
{
return(null);
}
// 还可能关注 ColumnOrdinal, ColumnSize. ColumnOrdinal 是以 1 为起始的字段位置
var dt = new DbTable();
var column_ColumnName = t.Columns["ColumnName"];
var column_DataType = t.Columns["DataType"];
var column_AllowDBNull = t.Columns["AllowDBNull"];
var column_ColumnSize = t.Columns["ColumnSize"];
var column_ColumnOrdinal = t.Columns["ColumnOrdinal"];
foreach (DataRow row in t.Rows)
{
var cn = (string)row[column_ColumnName];
var ca = (bool)row[column_AllowDBNull];
var ct = (Type)row[column_DataType];
var idx = (int)row[column_ColumnOrdinal] - 1;
if (ct.Name == "UInt64" && (int)row[column_ColumnSize] == 1)
{
dt.AddColumn(new DbColumn {
name = cn, dataType = DbDataTypes.Boolean, nullable = ca, columnIndex = idx
});
}
else
{
dt.AddColumn(new DbColumn {
name = cn, dataType = GetDbType(ct), nullable = ca, columnIndex = idx
});
}
}
return(dt);
}
private DbTable ToDbTable(System.Data.DataTable table)
{
var tbl = new DbTable();
foreach (DataColumn col in table.Columns)
{
tbl.AddColumn(col.ColumnName, col.DataType);
}
foreach (DataRow drow in table.Rows)
{
var row = tbl.AddRow();
foreach (var c in tbl.Columns)
{
row[c.Index] = drow[c.Index];
}
}
return(tbl);
}
