public async Task <string> GetStoreProcsSchema(CDatasource source, string storeProcName = "") { _logger.Information("Retrieving store procs schema for connection string {0}", source.ConnectionString.SecureString()); using (var connection = new NpgsqlConnection(source.ConnectionString)) { var resourceName = string.IsNullOrEmpty(storeProcName) ? "store-procs" : "store-procs-name"; using (var stream = new StreamReader(Assembly.GetAssembly(typeof(PostgreSqlProvider)) .GetManifestResourceStream("RelatedRows.Providers.PostgreSql.Properties." + resourceName + ".sql"))) { var query = await stream.ReadToEndAsync(); using (var rdr = await connection.ExecuteReaderAsync(query.Replace("@storeProcName", storeProcName), commandTimeout: 120)) { var data = string.Empty; if (rdr.Read()) { data = rdr.GetValue(0).ToString(); } rdr.Close(); return(data); } } } }
public async Task <ITabularSource> GetData(CDatasource source, CTable table, string query) { _logger.Information("Running query {@query} on server {@server}.{@databaseName} for table {@name}", query, source.serverName, source.databaseName, table.name); using (var connection = new NpgsqlConnection(source.ConnectionString)) { var rdr = await connection.ExecuteReaderAsync(query); var data = new TabularSource(table); data.LoadFromReader(rdr); rdr.Close(); return(data); } }
public async Task <DataTable> GetStoreProcedureData(CDatasource source, string name, params IDbDataParameter[] pars) { _logger.Information("Running store proc {@name} on server {@server}.{@databaseName} with params {@pars}", name, source.serverName, source.databaseName, pars.Select(p => $", {p.ParameterName}={p.Value}")); using (var connection = new NpgsqlConnection(source.ConnectionString)) { using (var cmd = connection.CreateCommand()) { cmd.Connection.Open(); cmd.CommandText = name; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddRange(pars); var reader = await cmd.ExecuteReaderAsync(); var table = new DataTable(name); table.Load(reader); reader.Close(); return(table); } } }
public async Task <DataTable> GetQueryData(CDatasource source, string query) { _logger.Information("Running query {@query} on server {@server}.{@databaseName}", query, source.serverName, source.databaseName); using (var connection = new NpgsqlConnection(source.ConnectionString)) { using (var cmd = connection.CreateCommand()) { cmd.Connection.Open(); cmd.CommandText = query; cmd.CommandType = CommandType.Text; var reader = await cmd.ExecuteReaderAsync(); var fromIdx = query.ToUpper().IndexOf("FROM") + 5; var portion = query.Substring(fromIdx).Split(' '); var table = new DataTable(portion.FirstOrDefault().Trim()); table.Load(reader); reader.Close(); return(table); } } }
public async Task <DataTable> GetData(CDatasource source, string name, string query) { _logger.Information("Running query {@query} on server {@server}.{@databaseName} for table {@name}", query, source.serverName, source.databaseName, name); using (var connection = new NpgsqlConnection(source.ConnectionString)) { var rdr = await connection.ExecuteReaderAsync(query); var data = new DataTable(name); data.Load(rdr); rdr.Close(); if (data.Columns.IndexOf("CountOver$") >= 0) { if (data.Rows.Count > 0) { data.Namespace = data.Rows[0]["CountOver$"].ToString(); } data.Columns.Remove("CountOver$"); } return(data); } }