public TableSizes GetTableSizes(DbConnection conn, LinkedDatabaseInfo linkedInfo) { var res = new TableSizes(); using (var cmd = conn.CreateCommand()) { cmd.CommandText = SqlServerLinkedServer.ReplaceLinkedServer(SqlServerDatabaseFactory.LoadEmbeddedResource("tablesizes.sql"), linkedInfo); using (var reader = cmd.ExecuteReader()) { while (reader.Read()) { string table = reader.SafeString("Table"); string schema = reader.SafeString("Schema"); var resItem = new TableSizesItem { RowCount = Int32.Parse(reader.SafeString("RowCount") ?? "0"), TotalSpaceKB = Int32.Parse(reader.SafeString("TotalSpaceKB") ?? "0"), UsedSpaceKB = Int32.Parse(reader.SafeString("UsedSpaceKB") ?? "0"), UnusedSpaceKB = Int32.Parse(reader.SafeString("UnusedSpaceKB") ?? "0"), }; res.Items[new NameWithSchema(schema, table)] = resItem; } } } return(res); }
public TableSizes GetTableSizes(DbConnection conn, LinkedDatabaseInfo linkedInfo) { var res = new TableSizes(); using (var cmd = conn.CreateCommand()) { cmd.CommandText = "show table status"; using (var reader = cmd.ExecuteReader()) { while (reader.Read()) { string table = reader.SafeString("Name"); string engine = reader.SafeString("Engine"); var resItem = new TableSizesItem { RowCount = reader.SafeString("Rows").SafeIntParse(), DataLengthKB = reader.SafeString("Data_length").SafeIntParse() / 1024, IndexLengthKB = reader.SafeString("Index_length").SafeIntParse() / 1024, }; resItem.TotalSpaceKB = resItem.IndexLengthKB + resItem.DataLengthKB; res.Items[new NameWithSchema(null, table)] = resItem; } } } return(res); }
public TableWriter(IShellContext context, IConnectionProvider connection, NameWithSchema name, TableInfo inputRowFormat, CopyTableTargetOptions options, TableInfo destinationTableOverride = null, LinkedDatabaseInfo linkedInfo = null, DataFormatSettings sourceDataFormat = null) { _connectionProvider = connection; _linkedInfo = linkedInfo; _name = name; _inputRowFormat = inputRowFormat; _queue = new CdlDataQueue(inputRowFormat); _context = context; _inserter = connection.Factory.CreateBulkInserter(); _inserter.SourceDataFormat = sourceDataFormat; _connection = _connectionProvider.Connect(); _inserter.Connection = _connection; _inserter.Factory = connection.Factory; _inserter.LinkedInfo = _linkedInfo; var db = context.GetDatabaseStructure(connection.ProviderString); _inserter.DestinationTable = destinationTableOverride ?? db.FindTableLike(_name.Schema, _name.Name); _inserter.CopyOptions = options; _inserter.MessageLogger = _context; _inserter.ServiceProvider = context.ServiceProvider; _thread = new Thread(Run); _thread.Start(); }
public void UpdateData(MultiTableUpdateScript script, LinkedDatabaseInfo linkedInfo) { if (script == null) { return; } string linkedInfoStr = linkedInfo != null?linkedInfo.ToString() : ""; int updrows = 0, updflds = 0; foreach (var upd in script.Updates) { Put("^update %s%f ^set ", linkedInfoStr, upd.Table); for (int i = 0; i < upd.Columns.Length; i++) { if (i > 0) { Put(", "); } Put("%i=%v", upd.Columns[i], upd.Values[i]); } Where(upd.Table, upd.CondCols, upd.CondValues); Put(";&n"); updrows++; updflds += upd.Values.Length; } }
public static string ReplaceLinkedServer(string sql, LinkedDatabaseInfo linkedInfo) { if (sql == null) { return(null); } string linkedServerSpec = ""; if (linkedInfo != null && linkedInfo.LinkedServerName != null) { linkedServerSpec = String.Format("[{0}].[{1}].", linkedInfo.LinkedServerName, linkedInfo.LinkedDatabaseName); } return(sql.Replace("[SERVER].", linkedServerSpec)); }
public override List <DatabaseOverviewInfo> GetDatabaseList(bool includeDetails, LinkedDatabaseInfo linkedInfo = null) { using (var cmd = Connection.CreateCommand()) { cmd.CommandText = "SELECT datname FROM pg_database WHERE datistemplate = false"; using (var reader = cmd.ExecuteReader()) { var res = new List <DatabaseOverviewInfo>(); while (reader.Read()) { var item = new DatabaseOverviewInfo(); item.Name = reader["datname"].SafeToString(); res.Add(item); } return(res); } } }
public void UpdateData(MultiTableUpdateScript script, LinkedDatabaseInfo linkedInfo) { }
public void UpdateData(TableInfo table, SingleTableDataScript script, LinkedDatabaseInfo linkedInfo) { }
public void DropTable(TableInfo obj, bool testIfExists, LinkedDatabaseInfo linkedInfo) { DropTable(obj, testIfExists); }
public void CreateTable(TableInfo obj, LinkedDatabaseInfo linkedInfo) { CreateTable(obj); }
public virtual List <DatabaseOverviewInfo> GetDatabaseList(bool includeDetails, LinkedDatabaseInfo linkedInfo = null) { return(new List <DatabaseOverviewInfo>()); }
public override List <DatabaseOverviewInfo> GetDatabaseList(bool includeDetails, LinkedDatabaseInfo linkedInfo = null) { if (includeDetails) { try { using (var cmd = Connection.CreateCommand()) { cmd.CommandText = SqlServerLinkedServer.ReplaceLinkedServer(SqlServerDatabaseFactory.LoadEmbeddedResource("databasesizes.sql"), linkedInfo); using (var reader = cmd.ExecuteReader()) { var res = new List <DatabaseOverviewInfo>(); while (reader.Read()) { var item = new DatabaseOverviewInfo(); item.Name = reader["DatabaseName"].SafeToString(); item.RowSizeKB = long.Parse(reader["RowSizeKB"].SafeToString()); item.LogSizeKB = long.Parse(reader["LogSizeKB"].SafeToString()); item.Collation = reader["Collation"].SafeToString(); item.RecoveryModel = reader["RecoveryModel"].SafeToString(); bool isSnapshot = reader["SnapshotIsolation"].SafeToString() == "1"; bool isReadCommitedSnapshot = reader["IsReadCommitedSnapshot"].SafeToString()?.ToLower() == "true"; if (isSnapshot) { if (isReadCommitedSnapshot) { item.Concurrency = "High"; } else { item.Concurrency = "Middle"; } } else { item.Concurrency = "Low"; } res.Add(item); } return(res); } } } catch (Exception err) { // use variant without details ServiceProvider.LogError <SqlServerInterface>(err, "Error fetching database details"); } } using (var cmd = Connection.CreateCommand()) { cmd.CommandText = SqlServerLinkedServer.ReplaceLinkedServer("SELECT name FROM [SERVER].sys.databases order by name", linkedInfo); using (var reader = cmd.ExecuteReader()) { var res = new List <DatabaseOverviewInfo>(); while (reader.Read()) { var item = new DatabaseOverviewInfo(); item.Name = reader["name"].SafeToString(); res.Add(item); } return(res); } } }
public void UpdateData(TableInfo table, SingleTableDataScript script, LinkedDatabaseInfo linkedInfo) { if (script == null) { return; } int delcnt = 0, inscnt = 0, updrows = 0, updflds = 0; string linkedInfoStr = linkedInfo != null?linkedInfo.ToString() : ""; foreach (var del in script.Deletes) { Put("^delete ^from %s%f", linkedInfoStr, table.FullName); Where(table.FullName, del.CondCols, del.CondValues); Put(";&n"); delcnt++; } foreach (var upd in script.Updates) { Put("^update %s%f ^set ", linkedInfoStr, table.FullName); for (int i = 0; i < upd.Columns.Length; i++) { if (i > 0) { Put(", "); } Put("%i=%v", upd.Columns[i], new ValueTypeHolder(upd.Values[i], table.ColumnByName(upd.Columns[i]).CommonType)); } Where(table.FullName, upd.CondCols, upd.CondValues); Put(";&n"); updrows++; updflds += upd.Values.Length; } ColumnInfo autoinc = null; if (table != null) { autoinc = table.FindAutoIncrementColumn(); } bool isIdentityInsert = false; foreach (var ins in script.Inserts) { if (autoinc != null) { if (Array.IndexOf(ins.Columns, autoinc.Name) >= 0) { if (!isIdentityInsert) { AllowIdentityInsert(table.FullName, true); } isIdentityInsert = true; } else { if (isIdentityInsert) { AllowIdentityInsert(table.FullName, false); } isIdentityInsert = false; } } var vals = new List <ValueTypeHolder>(); var insColumns = new List <string>(); for (int i = 0; i < ins.Columns.Length; i++) { var col = table.ColumnByName(ins.Columns[i]); if (col != null) { insColumns.Add(ins.Columns[i]); vals.Add(new ValueTypeHolder(ins.Values[i], col.CommonType)); } } if (insColumns.Count > 0) { Put("^insert ^into %s%f (%,i) ^values (%,v);&n", linkedInfoStr, table.FullName, insColumns, vals); } inscnt++; } if (isIdentityInsert) { AllowIdentityInsert(table.FullName, false); } }