// dcs0 previous function relied upon OLEDb to get schema from SQL server
// coming up with the appropriate connection string proved problematic
// this is the Micro$oft recommended solution
/// <summary>
/// Gets Primary_Keys schema information about a SQL table
/// </summary>
/// <param name="tableName">Name of the table</param>
/// <returns>DataTable with schema information</returns>
public override DataSets.TableKeysSchema.Primary_KeysDataTable GetTableKeysSchema(string tableName)
{
DataSets.TableKeysSchema schema = new Epi.DataSets.TableKeysSchema();
SqlConnection conn = this.GetNativeConnection();
bool alreadyOpen = (conn.State != ConnectionState.Closed);
try
{
if (!alreadyOpen)
{
OpenConnection(conn);
}
string sql = "select KU.TABLE_CATALOG, KU.TABLE_SCHEMA, KU.TABLE_NAME, COLUMN_NAME, " +
"0 as COLUMN_PROPID, ORDINAL_POSITION as ORDINAL, KU.CONSTRAINT_NAME as PK_NAME " +
"from INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC " +
"inner join " +
"INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KU " +
"on TC.CONSTRAINT_TYPE = 'primary key' and " +
"TC.CONSTRAINT_NAME = KU.CONSTRAINT_NAME " +
"where KU.TABLE_NAME = '" + tableName +
"' order by KU.ORDINAL_POSITION;";
SqlDataAdapter da = new SqlDataAdapter(sql, conn);
da.Fill(schema.Primary_Keys);
da.Dispose();
}
catch (Exception ex)
{
throw new GeneralException("Unable to obtain primary keys schema.", ex);
}
finally
{
if (!alreadyOpen && conn.State != ConnectionState.Closed)
{
CloseConnection(conn);
}
}
return schema.Primary_Keys;
}
/// <summary>
/// Gets Primary_Keys schema information about an OLE table
/// </summary>
/// <param name="tableName">Name of the table</param>
/// <returns>DataTable with schema information</returns>
public override DataSets.TableKeysSchema.Primary_KeysDataTable GetTableKeysSchema(string tableName)
{
OleDbConnection conn = this.GetNativeConnection();
try
{
OpenConnection(conn);
DataTable tb = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys, new object[] { null, null, tableName });
DataSets.TableKeysSchema schema = new Epi.DataSets.TableKeysSchema();
schema.Merge(tb);
return schema.Primary_Keys;
}
finally
{
CloseConnection(conn);
}
}
