public void RowCreationTest()
{
Utilities.DataTypes.Table Table = new Utilities.DataTypes.Table("Column1", "Column2", "Column3");
Table.AddRow(1, "A", 9.2f)
.AddRow(2, "B", 8.2f)
.AddRow(3, "C", 7.2f);
Assert.Equal(3, Table.Rows.Count);
Assert.Equal(1, Table[0][0]);
Assert.Equal("A", Table[0][1]);
Assert.Equal(9.2f, Table[0][2]);
Assert.Equal(2, Table[1][0]);
Assert.Equal("B", Table[1][1]);
Assert.Equal(8.2f, Table[1][2]);
Assert.Equal(3, Table[2][0]);
Assert.Equal("C", Table[2][1]);
Assert.Equal(7.2f, Table[2][2]);
Assert.Equal("Column1", Table[0].ColumnNames[0]);
Assert.Equal("Column2", Table[0].ColumnNames[1]);
Assert.Equal("Column3", Table[0].ColumnNames[2]);
Assert.Equal(1, Table[0]["Column1"]);
Assert.Equal("A", Table[0]["Column2"]);
Assert.Equal(9.2f, Table[0]["Column3"]);
Assert.Equal(2, Table[1]["Column1"]);
Assert.Equal("B", Table[1]["Column2"]);
Assert.Equal(8.2f, Table[1]["Column3"]);
Assert.Equal(3, Table[2]["Column1"]);
Assert.Equal("C", Table[2]["Column2"]);
Assert.Equal(7.2f, Table[2]["Column3"]);
}
public void CreationTest()
{
Utilities.DataTypes.Table Table = new Utilities.DataTypes.Table("Column1", "Column2", "Column3");
Assert.Equal(3, Table.ColumnNames.Length);
Assert.Equal("Column1", Table.ColumnNames[0]);
Assert.Equal("Column2", Table.ColumnNames[1]);
Assert.Equal("Column3", Table.ColumnNames[2]);
}
private static IEnumerable<string> GetTriggerCommand(Table Table)
{
Contract.Requires<ArgumentNullException>(Table != null, "Table");
Contract.Requires<ArgumentNullException>(Table.Triggers != null, "Table.Triggers");
var ReturnValue = new List<string>();
foreach (Trigger Trigger in Table.Triggers)
{
string Definition = Regex.Replace(Trigger.Definition, "-- (.*)", "");
ReturnValue.Add(Definition.Replace("\n", " ").Replace("\r", " "));
}
return ReturnValue;
}
private static IEnumerable<string> GetTableCommand(Table Table)
{
Contract.Requires<ArgumentNullException>(Table != null, "Table");
Contract.Requires<ArgumentNullException>(Table.Columns != null, "Table.Columns");
var ReturnValue = new List<string>();
var Builder = new StringBuilder();
Builder.Append("EXEC dbo.sp_executesql @statement = N'CREATE TABLE ").Append(Table.Name).Append("(");
string Splitter = "";
foreach (IColumn Column in Table.Columns)
{
Builder.Append(Splitter).Append(Column.Name).Append(" ").Append(Column.DataType.To(SqlDbType.Int).ToString());
if (Column.DataType == SqlDbType.VarChar.To(DbType.Int32)
|| Column.DataType == SqlDbType.NVarChar.To(DbType.Int32)
|| Column.DataType == SqlDbType.Binary.To(DbType.Int32))
{
Builder.Append((Column.Length < 0 || Column.Length >= 4000) ?
"(MAX)" :
"(" + Column.Length.ToString(CultureInfo.InvariantCulture) + ")");
}
else if (Column.DataType == SqlDbType.Decimal.To(DbType.Int32))
{
int Precision = (Column.Length * 2).Clamp(38, 18);
Builder.Append("(").Append(Precision).Append(",").Append(Column.Length.Clamp(38, 0)).Append(")");
}
if (!Column.Nullable)
{
Builder.Append(" NOT NULL");
}
if (Column.Unique)
{
Builder.Append(" UNIQUE");
}
if (Column.PrimaryKey)
{
Builder.Append(" PRIMARY KEY");
}
if (!string.IsNullOrEmpty(Column.Default))
{
Builder.Append(" DEFAULT ").Append(Column.Default.Replace("(", "").Replace(")", "").Replace("'", "''"));
}
if (Column.AutoIncrement)
{
Builder.Append(" IDENTITY");
}
Splitter = ",";
}
Builder.Append(")'");
ReturnValue.Add(Builder.ToString());
int Counter = 0;
foreach (IColumn Column in Table.Columns)
{
if (Column.Index && Column.Unique)
{
ReturnValue.Add(string.Format(CultureInfo.CurrentCulture,
"EXEC dbo.sp_executesql @statement = N'CREATE UNIQUE INDEX Index_{0}{1} ON {2}({3})'",
Column.Name,
Counter.ToString(CultureInfo.InvariantCulture),
Column.ParentTable.Name,
Column.Name));
}
else if (Column.Index)
{
ReturnValue.Add(string.Format(CultureInfo.CurrentCulture,
"EXEC dbo.sp_executesql @statement = N'CREATE INDEX Index_{0}{1} ON {2}({3})'",
Column.Name,
Counter.ToString(CultureInfo.InvariantCulture),
Column.ParentTable.Name,
Column.Name));
}
++Counter;
}
return ReturnValue;
}
private static IEnumerable<string> GetForeignKeyCommand(Table Table)
{
Contract.Requires<ArgumentNullException>(Table != null, "Table");
Contract.Requires<ArgumentNullException>(Table.Columns != null, "Table.Columns");
var ReturnValue = new List<string>();
foreach (IColumn Column in Table.Columns)
{
if (Column.ForeignKey.Count > 0)
{
foreach (IColumn ForeignKey in Column.ForeignKey)
{
string Command = string.Format(CultureInfo.CurrentCulture,
"EXEC dbo.sp_executesql @statement = N'ALTER TABLE {0} ADD FOREIGN KEY ({1}) REFERENCES {2}({3})",
Column.ParentTable.Name,
Column.Name,
ForeignKey.ParentTable.Name,
ForeignKey.Name);
if (Column.OnDeleteCascade)
Command += " ON DELETE CASCADE";
if (Column.OnUpdateCascade)
Command += " ON UPDATE CASCADE";
if (Column.OnDeleteSetNull)
Command += " ON DELETE SET NULL";
Command += "'";
ReturnValue.Add(Command);
}
}
}
return ReturnValue;
}
private static IEnumerable<string> GetAlterTriggerCommand(Table Table, ITable CurrentTable)
{
Contract.Requires<ArgumentNullException>(Table != null, "Table");
Contract.Requires<ArgumentNullException>(Table.Triggers != null, "Table.Triggers");
var ReturnValue = new List<string>();
foreach (Trigger Trigger in Table.Triggers)
{
foreach (Trigger Trigger2 in CurrentTable.Triggers)
{
string Definition1 = Trigger.Definition;
string Definition2 = Trigger2.Definition.Replace("Command0", "");
if (Trigger.Name == Trigger2.Name && string.Equals(Definition1, Definition2, StringComparison.InvariantCultureIgnoreCase))
{
ReturnValue.Add(string.Format(CultureInfo.CurrentCulture,
"EXEC dbo.sp_executesql @statement = N'DROP TRIGGER {0}'",
Trigger.Name));
string Definition = Regex.Replace(Trigger.Definition, "-- (.*)", "");
ReturnValue.Add(Definition.Replace("\n", " ").Replace("\r", " "));
break;
}
}
}
return ReturnValue;
}
private static IEnumerable<string> GetAlterTableCommand(Table Table, ITable CurrentTable)
{
Contract.Requires<ArgumentNullException>(Table != null, "Table");
Contract.Requires<ArgumentNullException>(Table.Columns != null, "Table.Columns");
var ReturnValue = new List<string>();
foreach (IColumn Column in Table.Columns)
{
IColumn CurrentColumn = CurrentTable[Column.Name];
string Command = "";
if (CurrentColumn == null)
{
Command = string.Format(CultureInfo.CurrentCulture,
"EXEC dbo.sp_executesql @statement = N'ALTER TABLE {0} ADD {1} {2}",
Table.Name,
Column.Name,
Column.DataType.To(SqlDbType.Int).ToString());
if (Column.DataType == SqlDbType.VarChar.To(DbType.Int32)
|| Column.DataType == SqlDbType.NVarChar.To(DbType.Int32)
|| Column.DataType == SqlDbType.Binary.To(DbType.Int32))
{
Command += (Column.Length < 0 || Column.Length >= 4000) ?
"(MAX)" :
"(" + Column.Length.ToString(CultureInfo.InvariantCulture) + ")";
}
else if (Column.DataType == SqlDbType.Decimal.To(DbType.Int32))
{
int Precision = (Column.Length * 2).Clamp(38, 18);
Command += "(" + Precision.ToString(CultureInfo.InvariantCulture) + "," + Column.Length.Clamp(38, 0).ToString(CultureInfo.InvariantCulture) + ")";
}
Command += "'";
ReturnValue.Add(Command);
foreach (IColumn ForeignKey in Column.ForeignKey)
{
Command = string.Format(CultureInfo.CurrentCulture,
"EXEC dbo.sp_executesql @statement = N'ALTER TABLE {0} ADD FOREIGN KEY ({1}) REFERENCES {2}({3}){4}{5}{6}'",
Table.Name,
Column.Name,
ForeignKey.ParentTable.Name,
ForeignKey.Name,
Column.OnDeleteCascade ? " ON DELETE CASCADE" : "",
Column.OnUpdateCascade ? " ON UPDATE CASCADE" : "",
Column.OnDeleteSetNull ? " ON DELETE SET NULL" : "");
ReturnValue.Add(Command);
}
}
else if (CurrentColumn.DataType != Column.DataType
|| (CurrentColumn.DataType == Column.DataType
&& CurrentColumn.DataType == SqlDbType.NVarChar.To(DbType.Int32)
&& CurrentColumn.Length != Column.Length
&& CurrentColumn.Length.Between(0, 4000)
&& Column.Length.Between(0, 4000)))
{
Command = string.Format(CultureInfo.CurrentCulture,
"EXEC dbo.sp_executesql @statement = N'ALTER TABLE {0} ALTER COLUMN {1} {2}",
Table.Name,
Column.Name,
Column.DataType.To(SqlDbType.Int).ToString());
if (Column.DataType == SqlDbType.VarChar.To(DbType.Int32)
|| Column.DataType == SqlDbType.NVarChar.To(DbType.Int32)
|| Column.DataType == SqlDbType.Binary.To(DbType.Int32))
{
Command += (Column.Length < 0 || Column.Length >= 4000) ?
"(MAX)" :
"(" + Column.Length.ToString(CultureInfo.InvariantCulture) + ")";
}
else if (Column.DataType == SqlDbType.Decimal.To(DbType.Int32))
{
int Precision = (Column.Length * 2).Clamp(38, 18);
Command += "(" + Precision.ToString(CultureInfo.InvariantCulture) + "," + Column.Length.Clamp(38, 0).ToString(CultureInfo.InvariantCulture) + ")";
}
Command += "'";
ReturnValue.Add(Command);
}
}
return ReturnValue;
}
