예제 #1
0
		public void TestScriptWarnings() {
			var f = new Routine("dbo", "udf_GetDate2", null) {
				Text = ExampleFunc,
				RoutineType = Routine.RoutineKind.Function
			};
			Assert.IsTrue(f.Warnings().Any());
		}
예제 #2
0
        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());
        }
예제 #3
0
		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]", "");
		}
예제 #4
0
 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]", "");
 }
예제 #5
0
		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]", "");
		}
예제 #6
0
파일: Database.cs 프로젝트: hobs/schemazen
		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;
					}
				}
			}
		}
예제 #7
0
파일: Database.cs 프로젝트: hobs/schemazen
		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
			}
		}
예제 #8
0
 private static string MakeFileName(Routine r)
 {
     return MakeFileName(r.Schema, r.Name);
 }
예제 #9
0
        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;
                        }
                    }
                }
            }
        }