C# IDbConnection.ExecuteScalarSql方法代码示例

		public override bool CanModify(SchemaInstaller.InstallContext context, IDbConnection connection)
		{
			// we can drop a function as long as there are no schemes using it
			return connection.ExecuteScalarSql<int>(
				"SELECT COUNT(*) FROM sys.partition_functions p JOIN sys.partition_schemes s ON (p.function_id = s.function_id) WHERE p.name = @Name", 
				new Dictionary<string, object>() { { "Name", Name.Object } }) == 0;
		}

		public override bool Exists(IDbConnection connection)
		{
			return 0 < connection.ExecuteScalarSql<int>(String.Format(@"
					SELECT COUNT (*)
						FROM sys.database_principals WHERE name = '{0}' AND type = 'R'",
				Name.Object));
		}

		public override bool CanModify(SchemaInstaller.InstallContext context, IDbConnection connection)
		{
			// we can drop a udt unless it is used in a table
			return connection.ExecuteScalarSql<int>(
				"SELECT COUNT(*) FROM sys.types t JOIN sys.columns c ON (t.user_type_id = c.user_type_id) WHERE t.Name = @Name",
				new Dictionary<string, object>() { { "Name", Name.Object } }) == 0;
		}

		public override bool Exists(IDbConnection connection)
		{
			return 0 < connection.ExecuteScalarSql<int>(String.Format(@"
					SELECT COUNT (*)
						FROM sys.conversation_priorities
						WHERE name = '{0}'",
				Name.Object));
		}

		public override bool Exists(IDbConnection connection)
		{
			return 0 < connection.ExecuteScalarSql<int>(String.Format(@"
					SELECT COUNT (*)
						FROM sys.service_queues
						WHERE object_id = OBJECT_ID('{0}')",
				Name.SchemaQualifiedObject));
		}

		public override bool Exists(IDbConnection connection)
		{
			return 0 < connection.ExecuteScalarSql<int>(String.Format(@"
					SELECT COUNT (*)
						FROM sys.service_message_types
						WHERE name = '{0}'",
				Name.Object));
		}

		public override bool CanDrop(SchemaInstaller.InstallContext context, IDbConnection connection)
		{
			return 0 == connection.ExecuteScalarSql<int>(String.Format(@"
				SELECT COUNT(*)
				FROM sys.service_queues q
				JOIN sys.service_queue_usages u ON (q.object_id = u.service_queue_id)
				WHERE q.object_id = OBJECT_ID('{0}')",
				Name.SchemaQualifiedObject));
		}

		public override bool CanDrop(SchemaInstaller.InstallContext context, IDbConnection connection)
		{
			return 0 == connection.ExecuteScalarSql<int>(String.Format(@"
				SELECT COUNT(*)
				FROM sys.service_contract_message_usages c
				JOIN sys.service_message_types m ON (c.message_type_id = m.message_type_id)
				WHERE m.name = '{0}'",
				Name.Object));
		}

		public override bool Exists(IDbConnection connection)
		{
			return 0 < connection.ExecuteScalarSql<int>(String.Format(@"
					SELECT COUNT (*)
						FROM sys.objects o
						JOIN sys.schemas s ON (o.schema_id = s.schema_id)
						WHERE s.name = '{0}' AND o.name = '{1}'",
				Name.Schema,
				Name.Object));
		}

		public override bool CanModify(SchemaInstaller.InstallContext context, IDbConnection connection)
		{
			// we can drop a scheme as long as there are no tables using it
			return connection.ExecuteScalarSql<int>(@"SELECT COUNT(*) FROM sys.partition_schemes s 
				WHERE s.name = @Name AND (
					s.data_space_id IN (SELECT data_space_id FROM sys.indexes) OR 
					s.data_space_id IN (SELECT lob_data_space_id FROM sys.tables) OR
					s.data_space_id IN (SELECT filestream_data_space_id FROM sys.tables))
				", new Dictionary<string, object>() { { "Name", Name.Object } }) == 0;
		}

		public override bool Exists(IDbConnection connection)
		{
			return 0 < connection.ExecuteScalarSql<int>(String.Format(@"SELECT COUNT(*)
						FROM sys.indexes i
						JOIN sys.objects t ON (i.object_id = t.object_id)
						JOIN sys.schemas s ON (t.schema_id = s.schema_id)
						WHERE s.Name = '{0}' AND t.name = '{1}' AND i.name = '{2}'",
				Name.Schema,
				Name.Table,
				Name.Object));
		}

		public override bool Exists(IDbConnection connection)
		{
			return 0 < connection.ExecuteScalarSql<int>(String.Format(@"SELECT COUNT(*)
						FROM sys.default_constraints d
						JOIN sys.schemas s ON (d.schema_id = s.schema_id)
						JOIN sys.objects o ON (d.parent_object_id = o.object_id)
						JOIN sys.columns c ON (c.object_id = o.object_id AND c.column_id = d.parent_column_id)
						WHERE s.Name = '{0}' AND o.name = '{1}' AND c.name = '{2}'",
				Name.Schema,
				Name.Table,
				Name.Object));
		}

		public override bool CanDrop(SchemaInstaller.InstallContext context, IDbConnection connection)
		{
			// azure can't drop the clustered index, so we have to warn if we are attempting to modify that
			if (context.IsAzure)
			{
				if (Sql.IndexOf("NONCLUSTERED", StringComparison.OrdinalIgnoreCase) == -1 && Sql.IndexOf("CLUSTERED", StringComparison.OrdinalIgnoreCase) != -1)
					return false;
			}

			return 0 == connection.ExecuteScalarSql<int>(String.Format(@"SELECT COUNT(*)
						FROM sys.xml_indexes i
						WHERE i.object_id = OBJECT_ID('{0}')",
				Name.SchemaQualifiedTable));
		}

		public override bool CanDrop(SchemaInstaller.InstallContext context, IDbConnection connection)
		{
			return 0 == connection.ExecuteScalarSql<int>(String.Format("SELECT COUNT(*) FROM sys.objects o WHERE o.schema_id = SCHEMA_ID('{0}')", Name.Object));
		}