/// <summary>
/// Handles the press of the test connection string button.
/// </summary>
/// <param name="sender">The sender.</param>
/// <param name="e">The event arguments.</param>
private void TestConnectionString(object sender, EventArgs e)
{
this.btnTest.Enabled = false;
IngresConnection conn = new IngresConnection();
if (this.tbConnectionString.Text.Trim() == string.Empty)
{
MessageBox.Show("A connection string must be entered.");
this.btnTest.Enabled = true;
return;
}
try
{
conn.ConnectionString = this.tbConnectionString.Text;
}
catch (Exception)
{
this.tbConnectionResult.Text = "Invalid connection string.";
this.btnTest.Enabled = true;
return;
}
try
{
conn.Open();
this.tbConnectionResult.Text = "Successfully opened a connection!";
MessageBox.Show("A connection to the database was successfully opened!\n\nCopy the generated connection string for use in the web config file.", "Successful Connection");
}
catch (Exception ex)
{
this.tbConnectionResult.Text = ex.Message;
}
finally
{
if (conn.State != ConnectionState.Closed)
{
conn.Close();
}
conn = null;
this.btnTest.Enabled = true;
}
}
public override IList<string> GetOwners()
{
var owners = new List<string>();
var conn = new IngresConnection(_connectionString);
conn.Open();
try
{
using (conn)
{
var tableCommand = conn.CreateCommand();
tableCommand.CommandText = "SELECT DISTINCT table_owner FROM iitables WHERE table_owner <> '$ingres'";
var sqlDataReader = tableCommand.ExecuteReader(CommandBehavior.CloseConnection);
while (sqlDataReader.Read())
{
var ownerName = sqlDataReader.GetString(0).TrimEnd();
owners.Add(ownerName);
}
}
}
finally
{
conn.Close();
}
return owners;
}
protected override IList<Column> GetTablesDetails(Table table, string owner)
{
table.OwnerSchema = owner;
var columns = new List<Column>();
var conn = new IngresConnection(_connectionString);
conn.Open();
try
{
using (conn)
{
using (var tableDetailsCommand = conn.CreateCommand())
{
tableDetailsCommand.CommandText = string.Format("SELECT" +
" column_name," +
" column_datatype," +
" column_nulls," +
" column_length," +
" column_scale " +
"FROM iicolumns " +
"WHERE table_owner = '{0}' " +
"AND table_name = '{1}' " +
"ORDER BY column_sequence",
owner, table.Name);
using (var sqlDataReader = tableDetailsCommand.ExecuteReader(CommandBehavior.Default))
{
while (sqlDataReader.Read())
{
string columnName = sqlDataReader.GetString(0).TrimEnd();
string dataType = sqlDataReader.GetString(1).TrimEnd();
bool isNullable = sqlDataReader.GetString(2).Equals("Y", StringComparison.CurrentCultureIgnoreCase);
int characterMaxLenth = sqlDataReader.GetInt32(3);
int numericPrecision = sqlDataReader.GetInt32(3);
int numericScale = sqlDataReader.GetInt32(4);
var m = new DataTypeMapper();
bool isPrimaryKey = IsPrimaryKey(owner, table.Name, columnName);
columns.Add(new Column(table)
{
Name = columnName,
DataType = dataType,
IsNullable = isNullable,
IsPrimaryKey = isPrimaryKey,
IsForeignKey = IsForeignKey(owner, table.Name, columnName),
MappedDataType = m.MapFromDBType(DatabaseServerType.Ingres, dataType, characterMaxLenth, numericPrecision, numericScale, isPrimaryKey),
DataLength = characterMaxLenth,
ConstraintName = GetConstraintName(owner, table.Name, columnName),
DataPrecision = numericPrecision,
DataScale = numericScale
});
table.Columns = columns;
}
table.PrimaryKey = DeterminePrimaryKeys(table);
table.ForeignKeys = DetermineForeignKeyReferences(table);
table.HasManyRelationships = DetermineHasManyRelationships(table);
}
}
}
}
finally
{
conn.Close();
}
return columns;
}
public override List<Table> GetTables(string owner)
{
var tables = new List<Table>();
var conn = new IngresConnection(_connectionString);
conn.Open();
try
{
using (conn)
{
var tableCommand = conn.CreateCommand();
tableCommand.CommandText = String.Format("SELECT table_name " +
"FROM iitables " +
"WHERE table_owner = '{0}' " +
"AND table_type in ('T', 'V') " +
"AND table_name NOT LIKE 'ii%'",
owner);
var sqlDataReader = tableCommand.ExecuteReader(CommandBehavior.CloseConnection);
while (sqlDataReader.Read())
{
var tableName = sqlDataReader.GetString(0).TrimEnd();
tables.Add(new Table
{
Name = tableName,
OwnerSchema = owner,
DatabaseName = this.DatabaseName
});
}
}
tables.Sort((x, y) => String.CompareOrdinal(x.Name, y.Name));
}
finally
{
conn.Close();
}
return tables;
}
private string GetForeignKeyReferenceTableName(string owner, string tableName, string columnName)
{
var conn = new IngresConnection(_connectionString);
conn.Open();
try
{
using (conn)
{
using (var tableCommand = conn.CreateCommand())
{
tableCommand.CommandText = String.Format("SELECT p.table_name " +
"FROM iiref_constraints rc " +
"INNER JOIN iikeys p " +
"ON p.schema_name = rc.unique_schema_name " +
"AND p.constraint_name = rc.unique_constraint_name " +
"INNER JOIN iiconstraints c " +
"ON c.schema_name = rc.ref_schema_name " +
"AND c.constraint_name = rc.ref_constraint_name " +
"INNER JOIN iikeys f " +
"ON f.schema_name = rc.ref_schema_name " +
"AND f.constraint_name = rc.ref_constraint_name " +
"AND p.key_position = f.key_position " +
"WHERE f.schema_name = '{0}' " +
"AND f.table_name = '{1}' " +
"AND f.column_name = '{2}'",
owner,
tableName,
columnName);
return tableCommand.ExecuteScalar().ToString();
}
}
}
finally
{
conn.Close();
}
}
private string GetConstraintName(string owner, string tableName, string columnName)
{
var conn = new IngresConnection(_connectionString);
conn.Open();
try
{
using (conn)
{
using (var tableDetailsCommand = conn.CreateCommand())
{
tableDetailsCommand.CommandText = string.Format("SELECT k.constraint_name " +
"FROM iikeys k " +
"INNER JOIN iiconstraints c " +
"ON k.constraint_name = c.constraint_name " +
"WHERE c.constraint_type = 'R' " +
"AND k.schema_name = '{0}' " +
"AND k.table_name = '{1}' " +
"AND k.column_name = '{2}'",
owner, tableName, columnName);
var result = tableDetailsCommand.ExecuteScalar();
return result == null ? String.Empty : result.ToString();
}
}
}
finally
{
conn.Close();
}
}
private bool IsForeignKey(string owner, string tableName, string columnName)
{
var conn = new IngresConnection(_connectionString);
conn.Open();
try
{
using (conn)
{
using (var tableDetailsCommand = conn.CreateCommand())
{
tableDetailsCommand.CommandText = string.Format("SELECT COUNT(0) " +
"FROM iikeys k " +
"INNER JOIN iiconstraints c " +
"ON k.constraint_name = c.constraint_name " +
"WHERE c.constraint_type = 'R' " +
"AND k.schema_name = '{0}' " +
"AND k.table_name = '{1}' " +
"AND k.column_name = '{2}'",
owner, tableName, columnName);
var obj = tableDetailsCommand.ExecuteScalar();
int result;
if (obj != null &&
Int32.TryParse(obj.ToString(), out result))
return result > 0;
}
}
}
finally
{
conn.Close();
}
return false;
}
/// <summary>
/// Gets the Id for the current application.
/// </summary>
/// <param name="conn">The Ingres connection to use.</param>
/// <param name="tran">The Ingres transaction to use.</param>
/// <returns>The Id for the current application.</returns>
private string GetApplicationId(IngresConnection conn, IngresTransaction tran)
{
string id = null;
string sql = @"
SELECT
ApplicationId
FROM
aspnet_Applications
WHERE LoweredApplicationName = ?
";
// Create the new command and enrol in the current transaction
IngresCommand cmd = new IngresCommand(sql, this.conn);
cmd.Transaction = this.tran;
cmd.Parameters.Add("LoweredApplicationName", DbType.String).Value = this.config.ApplicationName.ToLower();
conn.Open();
IngresDataReader reader = cmd.ExecuteReader();
if (reader != null)
{
if (reader.HasRows)
{
reader.Read();
// Retrieve the Id
id = DBUtil.ColValAsString(reader, "ApplicationId");
reader.Close();
}
else
{
// Close the reader.
reader.Close();
// We don't have an application so create one.
this.CreateApplication(this.config.ApplicationName, out id);
}
}
// Mark the application Id as current so that we don't have to fetch it from the database
// again unless it changes.
this.config.IsApplicationIdCurrent = true;
// Close the connection
conn.Close();
return id;
}