public void TestSqlServerInsertExcludeIdentity()
{
//arrange
var table = new DatabaseTable();
table.Name = "Categories";
table.Columns.Add(new DatabaseColumn {
Name = "Id", DbDataType = "INTEGER", IsAutoNumber = true
});
table.Columns.Add(new DatabaseColumn {
Name = "Name", DbDataType = "VARCHAR"
});
var dt = new DataTable {
Locale = CultureInfo.InvariantCulture
};
dt.Columns.Add("Id", typeof(int));
dt.Columns.Add("Name", typeof(string));
dt.Rows.Add(1, "Hello");
var insertWriter = new InsertWriter(table, dt);
insertWriter.IncludeIdentity = false;
//act
string txt = insertWriter.Write(SqlType.SqlServer);
//we don't care about formatting
txt = RemoveLineBreaks(txt);
//assert
Assert.AreEqual("INSERT INTO [Categories] ( [Name]) VALUES (N'Hello');", txt);
}
public void TestOracleInsert()
{
//arrange
var table = new DatabaseTable();
table.Name = "Categories";
table.Columns.Add(new DatabaseColumn {
Name = "Id", DbDataType = "INTEGER"
});
table.Columns.Add(new DatabaseColumn {
Name = "Name", DbDataType = "VARCHAR"
});
var dt = new DataTable {
Locale = CultureInfo.InvariantCulture
};
dt.Columns.Add("Id", typeof(int));
dt.Columns.Add("Name", typeof(string));
dt.Rows.Add(1, "Hello");
var insertWriter = new InsertWriter(table, dt);
insertWriter.IncludeIdentity = true;
//act
string txt = insertWriter.Write(SqlType.Oracle);
//we don't care about formatting
txt = RemoveLineBreaks(txt);
//assert
Assert.AreEqual("INSERT INTO \"Categories\" ( \"Id\", \"Name\") VALUES (1 ,'Hello');", txt);
}
public void TestSqlServerInsert()
{
//arrange
var table = new DatabaseTable();
table.Name = "Categories";
table.Columns.Add(new DatabaseColumn {
Name = "Id", DbDataType = "INTEGER"
});
table.Columns.Add(new DatabaseColumn {
Name = "Name", DbDataType = "VARCHAR"
});
var dt = new DataTable {
Locale = CultureInfo.InvariantCulture
};
dt.Columns.Add("Id", typeof(int));
dt.Columns.Add("Name", typeof(string));
dt.Rows.Add(1, "Hello");
var insertWriter = new InsertWriter(table, dt);
insertWriter.IncludeIdentity = true;
//act
string txt = insertWriter.Write(SqlType.SqlServer);
//we don't care about formatting
txt = txt.Replace(Environment.NewLine, string.Empty);
//assert
Assert.AreEqual("INSERT INTO [Categories] ( [Id], [Name]) VALUES (1 ,N'Hello');", txt);
}
public bool RunData(DirectoryInfo directory, SqlType dialect, DatabaseTable table)
{
if (table == null)
{
Message = "未选择表";
return(false);
}
var path = Path.Combine(directory.FullName, table.Name + "_data.sql");
try
{
var rdr = new Reader(table, table.DatabaseSchema.ConnectionString, table.DatabaseSchema.Provider);
var dt = rdr.Read();
var insertWriter = new InsertWriter(table, dt);
if (dialect == SqlType.SqlServer || dialect == SqlType.SqlServerCe ||
dialect == SqlType.SQLite ||
dialect == SqlType.Db2 || dialect == SqlType.MySql)
{
insertWriter.IncludeIdentity = true;
}
string txt = insertWriter.Write(dialect);
File.WriteAllText(path, txt);
Message = path;
return(true);
}
catch (Exception exception)
{
Message = exception.Message;
}
return(false);
}
public void Insert_Guid_Entity_Test()
{
var assignment = new Dictionary <string, object> {
{ "id", Guid.NewGuid() }, { "name", "bob" }, { "created", DateTime.MaxValue }, { "companyname", "Dunder Miflin" }, { "optout", true }
};
var insert = new Insert {
HasIdentityKey = false, Type = Insert.SetType.Values, Values = assignment, Into = new Table {
Name = TableName1
}
};
var statement = InsertWriter <GuidEntity> .CreateStatement(insert, GuidMap);
statement.Result.ShouldEqual(Statement.ResultType.None);
statement.Parameters.Count.ShouldEqual(5);
statement.Parameters.First().Value.ShouldNotEqual(Guid.Empty);
statement.Parameters.Skip(1).First().Value.ShouldEqual("bob");
statement.Parameters.Skip(2).First().Value.ShouldEqual(DateTime.MaxValue);
statement.Parameters.Skip(3).First().Value.ShouldEqual("Dunder Miflin");
statement.Parameters.Skip(4).First().Value.ShouldEqual(true);
statement.Text.ShouldEqual(
string.Format("INSERT INTO [{0}] ([id], [name], [created], [companyname], [optout]) VALUES (@{1}, @{2}, @{3}, @{4}, @{5})", TableName1,
statement.Parameters.First().Key,
statement.Parameters.Skip(1).First().Key,
statement.Parameters.Skip(2).First().Key,
statement.Parameters.Skip(3).First().Key,
statement.Parameters.Skip(4).First().Key));
}
public void Insert_Into_Test()
{
var properties = new List <string> {
"Name", "Created", "CompanyName", "OptOut"
};
var select = new Select {
Projection = properties.Select(x => new SelectProjection {
Projection = Projection.Create.Field(x, x == "CompanyName" || x == "OptOut")
}).ToList(),
From = { Type = Data.DataType.Table,
Table = new Table {
Name = TableName2
} }
};
var insert = new Insert {
Type = Insert.SetType.Query, Into = new Table {
Name = TableName1
}, Query = select
};
var statement = InsertWriter <IdentityEntity> .CreateStatement(insert, IdentityMap);
statement.Result.ShouldEqual(Statement.ResultType.None);
statement.Parameters.Count.ShouldEqual(0);
statement.Text.ShouldEqual(string.Format("INSERT INTO [{0}] ([name], [created], [companyname], [optout]) SELECT [name], [created], [companyname], [optout] FROM [{1}] {2}",
TableName1, TableName2, select.From.Alias));
}
public void Insert_Identity_Entity_Test()
{
var assignment = new Dictionary <string, object> {
{ "name", "bob" }, { "created", DateTime.MaxValue }, { "companyname", "Dunder Miflin" }, { "optout", true }
};
var insert = new Insert {
HasIdentityKey = true, Type = Insert.SetType.Values, Values = assignment, Into = new Table {
Name = TableName1
}
};
var statement = InsertWriter <IdentityEntity> .CreateStatement(insert, IdentityMap);
statement.Result.ShouldEqual(Statement.ResultType.Scalar);
statement.Parameters.Count.ShouldEqual(4);
statement.Parameters.First().Value.ShouldEqual("bob");
statement.Parameters.Skip(1).First().Value.ShouldEqual(DateTime.MaxValue);
statement.Parameters.Skip(2).First().Value.ShouldEqual("Dunder Miflin");
statement.Parameters.Skip(3).First().Value.ShouldEqual(true);
statement.Text.ShouldEqual(
string.Format("INSERT INTO [{0}] ([name], [created], [companyname], [optout]) VALUES (@{1}, @{2}, @{3}, @{4}); SELECT CAST(SCOPE_IDENTITY() AS int)", TableName1,
statement.Parameters.First().Key,
statement.Parameters.Skip(1).First().Key,
statement.Parameters.Skip(2).First().Key,
statement.Parameters.Skip(3).First().Key));
}
private IQueryable <TEntity> CopyInto(Insert insert)
{
insert.Query.Projection = GetSharedColumns(insert.Query, insert.Into);
var statement = InsertWriter <TEntity> .CreateStatement(insert, _map);
Command.Create(statement, _profiler).ExecuteNonQuery(_connectionManager);
return(new Table <TEntity>(_connectionManager, insert.Into.Name, _map, _profiler, _noLock));
}
public void SimpleInsertWorks() {
var insertWriter = new InsertWriter(new SqlServerDialect(), MakeConfig());
var post = new Post { PostId = 1, Title = "Boo", Rating = 11 };
var result = insertWriter.GenerateSql(post);
Debug.Write(result.Sql);
Assert.Equal(
"insert into [Posts] ([AuthorId], [BlogId], [Content], [DoNotMap], [Rating], [Title]) values (@p_1, @p_2, @p_3, @p_4, @p_5, @p_6)",
result.Sql);
}
public void SimpleInsertWorks()
{
var insertWriter = new InsertWriter(new SqlServerDialect(), MakeConfig());
var post = new Post {
PostId = 1, Title = "Boo", Rating = 11
};
var result = insertWriter.GenerateSql(post);
Debug.Write(result.Sql);
Assert.Equal(
"insert into [Posts] ([AuthorId], [BlogId], [Content], [DoNotMap], [Rating], [Title]) output inserted.[PostId] values (@p_1, @p_2, @p_3, @p_4, @p_5, @p_6)",
result.Sql);
}
public void InsertingVersionedEntities()
{
var insertWriter = new InsertWriter(new SqlServerDialect(), new VersionedConfig());
var entity = new VersionedEntity {
Name = "The Baz Man"
};
var result = insertWriter.GenerateSql(entity);
Assert.DoesNotContain(nameof(VersionedEntity.SysEndTime), result.Sql);
Assert.DoesNotContain(nameof(VersionedEntity.SysStartTime), result.Sql);
Assert.DoesNotContain(nameof(VersionedEntity.SessionUser), result.Sql);
Assert.DoesNotContain(nameof(VersionedEntity.CreatedBy), result.Sql);
}
public void TestInsertIntegration()
{
//arrange
var dbReader = TestHelper.GetNorthwindReader();
var table = dbReader.Table("Orders");
var rdr = new Reader(table, ConnectionString, Providername);
var dt = rdr.Read();
var insertWriter = new InsertWriter(table, dt);
insertWriter.IncludeIdentity = true;
//act
string txt = insertWriter.Write(SqlType.SqlServer);
//assert
Console.WriteLine(txt);
//check this manually
}
public bool RunData(DirectoryInfo directory, SqlType dialect, DatabaseTable table)
{
if (table == null)
{
Message = "No table";
return(false);
}
var path = Path.Combine(directory.FullName, table.Name + "_data.sql");
try
{
var rdr = new Reader(table, table.DatabaseSchema.ConnectionString, table.DatabaseSchema.Provider);
var dt = rdr.Read();
var insertWriter = new InsertWriter(table, dt);
//we have special processing for sqlserver
if (dialect == SqlType.SqlServer || dialect == SqlType.SqlServerCe
//assume db2 uses "generated by default" not "generated always".
|| dialect == SqlType.SQLite
//SQLite is fine with identity inserts and MySQL autoincrement is effectively DEFAULT=MAX(col) anyway
|| dialect == SqlType.Db2 || dialect == SqlType.MySql)
{
insertWriter.IncludeIdentity = true;
}
//act
string txt = insertWriter.Write(dialect);
File.WriteAllText(path, txt);
Message = @"Wrote " + path;
return(true);
}
catch (Exception exception)
{
Message =
@"An error occurred while creating the script.\n" + exception.Message;
}
return(false);
}
public void TestSqlServerInsertIncludeIdentity()
{
//arrange
var table = new DatabaseTable();
table.Name = "Categories";
table.Columns.Add(new DatabaseColumn {
Name = "Id", DbDataType = "INTEGER", IsAutoNumber = true
});
table.Columns.Add(new DatabaseColumn {
Name = "Name", DbDataType = "VARCHAR"
});
var dt = new DataTable {
Locale = CultureInfo.InvariantCulture
};
dt.Columns.Add("Id", typeof(int));
dt.Columns.Add("Name", typeof(string));
dt.Rows.Add(1, "Hello");
var insertWriter = new InsertWriter(table, dt);
insertWriter.IncludeIdentity = true;
//act
string txt = insertWriter.Write(SqlType.SqlServer);
//we don't care about formatting
txt = RemoveLineBreaks(txt);
//assert
Assert.IsTrue(txt.Contains("INSERT INTO [Categories] ( [Id], [Name]) VALUES (1 ,N'Hello');"));
Assert.IsTrue(txt.StartsWith("SET IDENTITY_INSERT [Categories] ON", StringComparison.OrdinalIgnoreCase));
Assert.IsTrue(txt.Contains("SET IDENTITY_INSERT [Categories] OFF"));
Assert.IsTrue(txt.Contains("DBCC CHECKIDENT ([Categories])"));
}
public void Insert(TEntity entity)
{
var adapter = new EntityAdapter <TEntity>(entity, _map);
var hasIdentityKey = _map.Key.KeyType == PrimaryKeyType.Integer &&
_map.Key.KeyGeneration == PrimaryKeyGeneration.Server;
var keyColumnName = _map.Key.GetColumnName();
if (_map.Key.KeyType == PrimaryKeyType.Guid &&
_map.Key.KeyGeneration == PrimaryKeyGeneration.Client)
{
adapter.Key = GuidComb.Create();
}
var values = adapter.GetValues().Where(x => !hasIdentityKey ||
(x.Key != keyColumnName)).ToDictionary(x => x.Key, x => x.Value);
var insert = new Insert {
HasIdentityKey = hasIdentityKey,
Type = Model.Insert.SetType.Values,
Into = new Table {
Name = _table
},
Values = values
};
var command = Command.Create(InsertWriter <TEntity>
.CreateStatement(insert, _map), _profiler);
if (command.Statement.Result == Statement.ResultType.None)
{
command.ExecuteNonQuery(_connectionManager);
}
else
{
adapter.Key = command.ExecuteScalar(_connectionManager);
}
}
