/// <summary>
/// Executes the select.
/// </summary>
/// <param name="connection">The connection.</param>
/// <param name="text">The text.</param>
/// <returns></returns>
public static Table executeSelect(SqlConnection connection, String text)
{
SqlCommand myCommand = new SqlCommand(text, connection);
Table table = new Table();
SqlDataReader myReader = null;
try
{
myReader = myCommand.ExecuteReader();
Boolean fieldsDone = false;
while (myReader.Read())
{
Row row = new Row();
for (int i = 0; i < myReader.FieldCount; i++)
{
if (fieldsDone == false)
{
TableField f = new TableField();
f.Name = myReader.GetName(i);
table.FieldCont.Fields.Add(f);
}
Value val = new Value();
val.Type = myReader.GetDataTypeName(i);
if (myReader.IsDBNull(i))
{
val.Data = "NULL";
}
else if (val.Type == "bit")
{
/*
* Byte [] buf = new Byte [2];
* myReader.GetBytes(i, 0, buf, 0, 1);
* val.Data = buf.ToString();
*/
val.Data = myReader.GetBoolean(i).ToString();
}
else if (val.Type == "tinyint")
{
val.Data = myReader.GetByte(i).ToString();
}
else if (val.Type == "smallint")
{
val.Data = myReader.GetInt16(i).ToString();
}
else if (val.Type == "int")
{
val.Data = myReader.GetInt32(i).ToString();
}
else if (val.Type == "bigint")
{
val.Data = myReader.GetInt64(i).ToString();
}
else if (val.Type == "float")
{
val.Data = myReader.GetDouble(i).ToString();
}
else if (val.Type == "decimal")
{
val.Data = myReader.GetDecimal(i).ToString();
}
else if (val.Type == "numeric")
{
val.Data = myReader.GetDouble(i).ToString();
}
else if (val.Type == "text" || val.Type == "char" || val.Type == "varchar")
{
val.Data = myReader.GetString(i);
}
else if (val.Type == "ntext" || val.Type == "nchar" || val.Type == "nvarchar")
{
val.Data = myReader.GetString(i);
}
else if (val.Type == "datetime")
{
val.Data = myReader.GetDateTime(i).ToString();
}
else
{
val.Data = String.Empty;
}
row.Values.Add(val);
}
table.Rows.Add(row);
fieldsDone = true;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
if (myReader != null)
{
myReader.Close();
}
}
return(table);
}
/// <summary>
/// Gets the table data.
/// </summary>
/// <param name="Target">The target.</param>
public void GetTableData(Table Target, Configuration config)
{
SqlConnection myConnection = Connection.connect();
if (myConnection == null)
{
return;
}
Target.FieldCont = new TableFieldContainer(Target);
Target.DataCont = new DataContainer(Target);
Target.ConstraintCont = new ConstraintContainer(Target);
Table TableList = ConnectionAdapter.executeSelect
(
myConnection,
String.Format
(
"SELECT name, object_id, parent_object_id FROM sys.objects WHERE type_desc='USER_TABLE' and name='{0}'",
Target.Name
)
);
String table_name = TableList.Rows[0].Values[0].Data;
String object_id = TableList.Rows[0].Values[1].Data;
String parent_object_id = TableList.Rows[0].Values[2].Data;
String query = String.Format
(
"SELECT sys.columns.name, sys.types.name, sys.columns.max_length, sys.columns.is_nullable, sys.columns.is_identity " +
"FROM sys.columns " +
"INNER JOIN sys.types " +
"ON sys.columns.system_type_id=sys.types.system_type_id " +
"AND sys.columns.user_type_id=sys.types.user_type_id " +
"WHERE sys.columns.object_id='{0}'",
object_id
);
Table ColumnList = ConnectionAdapter.executeSelect(myConnection, query);
foreach (Row col in ColumnList.Rows)
{
String type = col.Values[1].Data;
String num = col.Values[2].Data;
String nullable = col.Values[3].Data;
String identity = col.Values[4].Data;
if (IsTextType(type) == true && num != String.Empty && num != "NULL")
{
type += "(" + num + ")";
}
TableField f = new TableField(col.Values[0].Data, type, nullable.ToLower() == "true" ? true : false, identity.ToLower() == "true" ? true : false);
f.Parent = Target;
Target.FieldCont.Fields.Add(f);
}
//----------------------------------------------------------------
// Handle constraints
query = String.Format("SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME='{0}'", table_name);
Table ConstraintList = ConnectionAdapter.executeSelect(myConnection, query);
foreach (Row current_row in ConstraintList.Rows)
{
Constraint NewConstraint = new Constraint();
NewConstraint.Name = current_row.Values[0].Data;
NewConstraint.Type = Constraint.SQLConstraintTypeToConstraintType(current_row.Values[1].Data);
NewConstraint.Parent = Target;
if (NewConstraint.Type == Constraint.ConstraintType.Check)
{
query = String.Format("SELECT CHECK_CLAUSE FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS WHERE CONSTRAINT_NAME='{0}'", NewConstraint.Name);
Table ConstraintText = ConnectionAdapter.executeSelect(myConnection, query);
if (ConstraintText.Rows.Count > 0)
{
NewConstraint.Text = ConstraintText.Rows[0].Values[0].Data;
}
}
else if (NewConstraint.Type == Constraint.ConstraintType.ForeignKey)
{
query = String.Format
(
"SELECT " +
"CONSTRAINT_NAME = REF_CONST.CONSTRAINT_NAME, " +
"TABLE_NAME = FK.TABLE_NAME, " +
"COLUMN_NAME = FK_COLS.COLUMN_NAME, " +
"REFERENCED_TABLE_NAME = PK.TABLE_NAME, " +
"REFERENCED_COLUMN_NAME = PK_COLS.COLUMN_NAME " +
"FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS REF_CONST " +
"INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK " +
"ON REF_CONST.CONSTRAINT_CATALOG = FK.CONSTRAINT_CATALOG " +
"AND REF_CONST.CONSTRAINT_SCHEMA = FK.CONSTRAINT_SCHEMA " +
"AND REF_CONST.CONSTRAINT_NAME = FK.CONSTRAINT_NAME " +
"AND FK.CONSTRAINT_TYPE = 'FOREIGN KEY' " +
"INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON REF_CONST.UNIQUE_CONSTRAINT_CATALOG = PK.CONSTRAINT_CATALOG " +
"AND REF_CONST.UNIQUE_CONSTRAINT_SCHEMA = PK.CONSTRAINT_SCHEMA " +
"AND REF_CONST.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME " +
"AND PK.CONSTRAINT_TYPE = 'PRIMARY KEY' " +
"INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE FK_COLS ON REF_CONST.CONSTRAINT_NAME = FK_COLS.CONSTRAINT_NAME " +
"INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE PK_COLS ON PK.CONSTRAINT_NAME = PK_COLS.CONSTRAINT_NAME " +
"WHERE REF_CONST.CONSTRAINT_NAME = '{0}'",
NewConstraint.Name
);
Table ConstraintText = ConnectionAdapter.executeSelect(myConnection, query);
if (ConstraintText != null && ConstraintText.Rows.Count > 0)
{
NewConstraint.ReferencedTable = ConstraintText.Rows[0].Values[3].Data;
NewConstraint.ReferencedFieldNames.Add(ConstraintText.Rows[0].Values[4].Data);
}
}
query = String.Format
(
"SELECT COLUMN_NAME " +
"FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE " +
"WHERE TABLE_NAME='{0}' AND CONSTRAINT_NAME='{1}'",
table_name,
NewConstraint.Name
);
Table ConstraintRows = ConnectionAdapter.executeSelect(myConnection, query);
foreach (Row rw in ConstraintRows.Rows)
{
NewConstraint.FieldNames.Add(rw.Values[0].Data);
}
Target.ConstraintCont.Constraints.Add(NewConstraint);
}
// Handle data
if (config.AnalyzeData)
{
Target.DataCont = new DataContainer(Target);
}
myConnection.Close();
}
