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