public void TestScriptWarnings() {
var f = new Routine("dbo", "udf_GetDate2", null) {
Text = ExampleFunc,
RoutineType = Routine.RoutineKind.Function
};
Assert.IsTrue(f.Warnings().Any());
}
public void TestScriptWarnings()
{
const string baseText = @"--example of routine that has been renamed since creation
CREATE PROCEDURE {0}
@id int
AS
select * from Address where id = @id
";
var getAddress = new Routine("dbo", "GetAddress", null);
getAddress.RoutineType = Routine.RoutineKind.Procedure;
getAddress.Text = string.Format(baseText, "[dbo].[NamedDifferently]");
Assert.IsTrue(getAddress.Warnings().Any());
getAddress.Text = string.Format(baseText, "dbo.NamedDifferently");
Assert.IsTrue(getAddress.Warnings().Any());
getAddress.Text = string.Format(baseText, "dbo.[GetAddress]");
Assert.IsFalse(getAddress.Warnings().Any());
getAddress.Text = string.Format(baseText, "dbo.GetAddress");
Assert.IsFalse(getAddress.Warnings().Any());
getAddress.Text = string.Format(baseText, "GetAddress");
Assert.IsFalse(getAddress.Warnings().Any());
}
public void TestScript() {
var f = new Routine("dbo", "udf_GetDate", null) {
RoutineType = Routine.RoutineKind.Function,
Text = ExampleFunc
};
Console.WriteLine(f.ScriptCreate());
TestHelper.ExecBatchSql(f.ScriptCreate() + "\nGO", "");
TestHelper.ExecSql("drop function [dbo].[udf_GetDate]", "");
}
public void TestScript()
{
var f = new Routine("dbo", "udf_GetDate");
f.Text = @"
CREATE FUNCTION [dbo].[udf_GetDate]()
RETURNS DATETIME AS
BEGIN
RETURN GETDATE()
END
";
Console.WriteLine(f.ScriptCreate(null));
TestHelper.ExecBatchSql(f.ScriptCreate(null) + "\nGO", "");
TestHelper.ExecSql("drop function [dbo].[udf_GetDate]", "");
}
public void TestScript() {
var t = new Table("dbo", "Address");
t.Columns.Add(new Column("id", "int", false, null));
t.Columns.Add(new Column("street", "varchar", 50, false, null));
t.Columns.Add(new Column("city", "varchar", 50, false, null));
t.Columns.Add(new Column("state", "char", 2, false, null));
t.Columns.Add(new Column("zip", "char", 5, false, null));
t.Constraints.Add(new Constraint("PK_Address", "PRIMARY KEY", "id"));
var getAddress = new Routine("dbo", "GetAddress", null);
getAddress.Text = @"
CREATE PROCEDURE [dbo].[GetAddress]
@id int
AS
select * from Address where id = @id
";
TestHelper.ExecSql(t.ScriptCreate(), "");
TestHelper.ExecBatchSql(getAddress.ScriptCreate() + "\nGO", "");
TestHelper.ExecSql("drop table [dbo].[Address]", "");
TestHelper.ExecSql("drop procedure [dbo].[GetAddress]", "");
}
private void LoadRoutines(SqlCommand cm) {
//get routines
cm.CommandText = @"
select
s.name as schemaName,
o.name as routineName,
o.type_desc,
m.definition,
m.uses_ansi_nulls,
m.uses_quoted_identifier,
isnull(s2.name, s3.name) as tableSchema,
isnull(t.name, v.name) as tableName,
tr.is_disabled as trigger_disabled
from sys.sql_modules m
inner join sys.objects o on m.object_id = o.object_id
inner join sys.schemas s on s.schema_id = o.schema_id
left join sys.triggers tr on m.object_id = tr.object_id
left join sys.tables t on tr.parent_id = t.object_id
left join sys.views v on tr.parent_id = v.object_id
left join sys.schemas s2 on s2.schema_id = t.schema_id
left join sys.schemas s3 on s3.schema_id = v.schema_id
where objectproperty(o.object_id, 'IsMSShipped') = 0
";
using (var dr = cm.ExecuteReader()) {
while (dr.Read()) {
var r = new Routine((string) dr["schemaName"], (string) dr["routineName"], this);
r.Text = dr["definition"] is DBNull ? string.Empty : (string) dr["definition"];
r.AnsiNull = (bool) dr["uses_ansi_nulls"];
r.QuotedId = (bool) dr["uses_quoted_identifier"];
Routines.Add(r);
switch ((string) dr["type_desc"]) {
case "SQL_STORED_PROCEDURE":
r.RoutineType = Routine.RoutineKind.Procedure;
break;
case "SQL_TRIGGER":
r.RoutineType = Routine.RoutineKind.Trigger;
r.RelatedTableName = (string) dr["tableName"];
r.RelatedTableSchema = (string) dr["tableSchema"];
r.Disabled = (bool) dr["trigger_disabled"];
break;
case "SQL_SCALAR_FUNCTION":
case "SQL_INLINE_TABLE_VALUED_FUNCTION":
r.RoutineType = Routine.RoutineKind.Function;
break;
case "VIEW":
r.RoutineType = Routine.RoutineKind.View;
break;
}
}
}
}
private void LoadXmlSchemas(SqlCommand cm) {
try {
// get xml schemas
cm.CommandText = @"
select s.name as DBSchemaName, x.name as XMLSchemaCollectionName, xml_schema_namespace(s.name, x.name) as definition
from sys.xml_schema_collections x
inner join sys.schemas s on s.schema_id = x.schema_id
where s.name != 'sys'";
using (var dr = cm.ExecuteReader()) {
while (dr.Read()) {
var r = new Routine((string) dr["DBSchemaName"], (string) dr["XMLSchemaCollectionName"], this) {
Text =
string.Format("CREATE XML SCHEMA COLLECTION {0}.{1} AS N'{2}'", dr["DBSchemaName"],
dr["XMLSchemaCollectionName"], dr["definition"]),
RoutineType = Routine.RoutineKind.XmlSchemaCollection
};
Routines.Add(r);
}
}
} catch (SqlException) {
// SQL server version doesn't support XML schemas, nothing to do here
}
}
private static string MakeFileName(Routine r)
{
return MakeFileName(r.Schema, r.Name);
}
private void LoadRoutines(SqlCommand cm)
{
//get routines
cm.CommandText = @"
select
s.name as schemaName,
o.name as routineName,
o.type_desc,
m.definition,
m.uses_ansi_nulls,
m.uses_quoted_identifier,
t.name as tableName
from sys.sql_modules m
inner join sys.objects o on m.object_id = o.object_id
inner join sys.schemas s on s.schema_id = o.schema_id
left join sys.triggers tr on m.object_id = tr.object_id
left join sys.tables t on tr.parent_id = t.object_id
where objectproperty(o.object_id, 'IsMSShipped') = 0
";
using (SqlDataReader dr = cm.ExecuteReader())
{
while (dr.Read())
{
// TODO: consider refactoring - it is bad practice to write to the console from a class library...
if (dr["definition"] is DBNull)
{
Console.ForegroundColor = ConsoleColor.Magenta;
Console.WriteLine("Warning: Unable to get definition for {0} {1}.{2}", (string)dr["type_desc"], (string)dr["schemaName"], (string)dr["routineName"]);
Console.ForegroundColor = ConsoleColor.White;
}
else
{
var r = new Routine((string)dr["schemaName"], (string)dr["routineName"]);
r.Text = (string)dr["definition"];
r.AnsiNull = (bool)dr["uses_ansi_nulls"];
r.QuotedId = (bool)dr["uses_quoted_identifier"];
Routines.Add(r);
switch ((string)dr["type_desc"])
{
case "SQL_STORED_PROCEDURE":
r.RoutineType = Routine.RoutineKind.Procedure;
break;
case "SQL_TRIGGER":
r.RoutineType = Routine.RoutineKind.Trigger;
break;
case "SQL_SCALAR_FUNCTION":
case "SQL_INLINE_TABLE_VALUED_FUNCTION":
r.RoutineType = Routine.RoutineKind.Function;
break;
case "VIEW":
r.RoutineType = Routine.RoutineKind.View;
break;
}
}
}
}
}