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 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])"));
}