IEnumerable <ITreeNode> ITreeNode.GetChildren(bool refresh) { const string commandText = @"declare @provider nvarchar(128) select @provider = s.provider from sys.servers s (nolock) where s.name = @name create table #catalog ( CATALOG_NAME nvarchar(128), DESCRIPTION nvarchar(255) ) insert into #catalog execute sp_catalogs 'UKSECPRSD98\EUR' if @provider = 'SQLNCLI' begin select c.CATALOG_NAME from #catalog c where (@getSystemCatalogs = 0 and c.CATALOG_NAME not in('master','model','msdb','tempdb')) or (@getSystemCatalogs = 1 and c.CATALOG_NAME in('master','model','msdb','tempdb')) order by c.CATALOG_NAME end else begin select c.CATALOG_NAME from #catalog c order by c.CATALOG_NAME end drop table #catalog"; using (var connection = new SqlConnection(_linkedServer.LinkedServers.Server.ConnectionString)) { connection.Open(); var parameters = new SqlParameterCollectionBuilder(); parameters.Add("@name", _linkedServer.Name); parameters.Add("@getSystemCatalogs", false); var executor = connection.CreateCommandExecutor(); var executeReaderRequest = new ExecuteReaderRequest(commandText, parameters.ToReadOnlyCollection()); return(executor.ExecuteReader(executeReaderRequest, 128, dataRecord => new LinkedServerCatalogNode(_linkedServer, dataRecord.GetString(0)))); } }
IEnumerable <ITreeNode> ITreeNode.GetChildren(bool refresh) { var cb = new SqlCommandBuilder(); var commandText = string.Format(@"select i.name, i.index_id, i.type, i.is_unique from {0}.sys.schemas s (nolock) join {0}.sys.objects o (nolock) on s.schema_id = o.schema_id join {0}.sys.indexes i (nolock) on o.object_id = i.object_id where o.object_id = @object_id order by i.name", cb.QuoteIdentifier(_databaseNode.Name)); var parameters = new SqlParameterCollectionBuilder(); parameters.Add("object_id", _id); var request = new ExecuteReaderRequest(commandText, parameters.ToReadOnlyCollection()); var connectionString = _databaseNode.Databases.Server.ConnectionString; var executor = new SqlCommandExecutor(connectionString); return(executor.ExecuteReader(request, 128, dataRecord => { var name = dataRecord.GetStringOrDefault(0); var indexId = dataRecord.GetInt32(1); var type = dataRecord.GetByte(2); var isUnique = dataRecord.GetBoolean(3); return new IndexNode(_databaseNode, _id, indexId, name, type, isUnique); })); }
private static ReadOnlyCollection <object> ToParameters(GetCustomerInvoicesDbQuery query) { var parameters = new SqlParameterCollectionBuilder(); parameters.Add("customerId", query.CustomerId); parameters.AddDate("invoiceDate", query.InvoiceDate); return(parameters.ToReadOnlyCollection()); }
public static GetAppLockReturnCode GetAppLock(IDbConnection connection, IDbTransaction transaction, string resourceName, LockMode lockMode, LockOwner?lockOwner, TimeSpan?lockTimeout, string databasePrincipal) { const string commandText = "sp_getapplock"; var builder = new SqlParameterCollectionBuilder(); builder.Add("Resource", resourceName); builder.Add("LockMode", lockMode.ToString()); if (lockOwner != null) { builder.Add("LockOwner", lockOwner.ToString()); } if (lockTimeout != null) { builder.Add("LockTimeout", (int)lockTimeout.Value.TotalMilliseconds); } if (databasePrincipal != null) { builder.Add("DbPrincipal", databasePrincipal); } var returnCodeParameter = new SqlParameter { //ParameterName = "returnCode", //SqlDbType = SqlDbType.Int, Direction = ParameterDirection.ReturnValue }; builder.Add(returnCodeParameter); var parameters = builder.ToReadOnlyCollection(); var createCommandRequest = new CreateCommandRequest(commandText, parameters, CommandType.StoredProcedure, null, transaction); connection.CreateCommandExecutor().ExecuteNonQuery(createCommandRequest); var returnCode = (GetAppLockReturnCode)(int)returnCodeParameter.Value; return(returnCode); }