private void describeMenu_Click(object sender, EventArgs e)
{
ssh.Connect();
DescribeOutput descOutput = ssh.Describe(listBox1.SelectedItem.ToString());
DataTable dtDescribe = new DataTable();
dtDescribe.Columns.Add("Name");
dtDescribe.Columns.Add("Type");
dtDescribe.Columns.Add("Modifiers");
dtDescribe.Columns.Add("Primary Key");
foreach (DBColumn column in descOutput.Columns)
{
DataRow r = dtDescribe.NewRow();
r["Name"] = column.ColumnName;
r["Type"] = column.Type;
r["Modifiers"] = column.Modifiers;
r["Primary Key"] = column.IsPrimaryKey ? "X" : "";
dtDescribe.Rows.Add(r);
}
describeTable.DataSource = dtDescribe;
ssh.Disconnect();
}
private bool ExistColumn(DescribeOutput destinationTabDescription, DBColumn dbColumn)
{
foreach (DBColumn destColumn in destinationTabDescription.Columns)
{
if (destColumn.ColumnName.Equals(dbColumn.ColumnName, StringComparison.InvariantCultureIgnoreCase))
{
return(true);
}
}
return(false);
}
private List <DBColumn> GetMissingColumn(string table,
SshDbWrapper sourceConnection,
SshDbWrapper destinationConnection)
{
DescribeOutput sourceTabDescription = sourceConnection.Describe(table);
DescribeOutput destinationTabDescription = destinationConnection.Describe(table);
List <DBColumn> missingColumns = new List <DBColumn>();
foreach (DBColumn col in sourceTabDescription.Columns)
{
if (!ExistColumn(destinationTabDescription, col))
{
missingColumns.Add(col);
}
}
return(missingColumns);
}
public DescribeOutput Describe(string tableName)
{
var response = sshClient.RunCommand(PsqlInlineCommand(String.Format("\\d {0}", tableName)));
DescribeOutput output = new DescribeOutput();
List <DBColumn> describeColumns = new List <DBColumn>();
output.TableName = tableName;
int rowCounter = 0;
string primaryKey = "";
foreach (String line in response.Result.Split('\n'))
{
output.RawText += line + System.Environment.NewLine;
if (rowCounter > 1)
{
string[] spltArg = line.Split('|');
if (spltArg.Length > 1)
{
DBColumn newColumn = new DBColumn();
newColumn.ColumnName = spltArg[0].Trim();
newColumn.Type = spltArg[1].Trim();
if (spltArg.Length > 2)
{
newColumn.Modifiers = spltArg[2].Trim();
}
describeColumns.Add(newColumn);
}
else
{
if (line.Trim().ToUpper().Contains("PRIMARY KEY,"))
{
string[] keyval = line.Split('(');
primaryKey = keyval[1].TrimEnd(')');
}
}
}
rowCounter++;
}
output.Columns = describeColumns;
output.SetPrimaryKey(primaryKey);
return(output);
}
public SelectOutput SelectFromTable(string table,
String whereCondition,
String order,
String limit
)
{
String columnList = "";
DescribeOutput describe = Describe(table);
DataTable outputTable = new DataTable();
SelectOutput selectOutput = new SelectOutput();
foreach (DBColumn column in describe.Columns)
{
columnList += column.ColumnName + ", ";
outputTable.Columns.Add(column.ColumnName);
}
columnList = columnList.Trim().TrimEnd(',');
if (!string.IsNullOrEmpty(whereCondition))
{
whereCondition = "WHERE " + whereCondition;
}
if (!string.IsNullOrEmpty(order))
{
order = "ORDER BY " + order;
}
if (!string.IsNullOrEmpty(limit))
{
limit = "LIMIT " + limit;
}
String writeCommand = String.Format("echo \"SELECT {0} FROM {1} {2} {3} {4};\" > {5}", columnList, table, whereCondition, order, limit, SqlFile);
var response = sshClient.RunCommand(writeCommand);
response = sshClient.RunCommand(PsqlScriptExec());
int counter = 0;
foreach (String line in response.Result.Split('\n'))
{
if (counter > 1)
{
string[] listOfData = line.Split('|');
if (listOfData.Length > 1)
{
DataRow r = outputTable.NewRow();
int i = 0;
foreach (DataColumn column in outputTable.Columns)
{
r[column.ColumnName] = listOfData[i].Trim();
i++;
}
outputTable.Rows.Add(r);
}
}
counter++;
}
selectOutput.Table = outputTable;
selectOutput.PrimaryKey = describe.PrimaryKey;
return(selectOutput);
}
