//TODO: Should add SQL Server specific attribute here (last_value)
public static DbIdentity check(Column column)
{
DbIdentity returnValue = null;
Table table = column.table;
string platform = table.database.platform;
OdbcCommand command;
OdbcDataReader reader;
switch (table.database.dialect)
{
case Ddl.Dialect.db2:
command = new OdbcCommand(
"select cid.*, generated" +
" from syscat.columns col, syscat.colidentattributes cid" +
" where col.tabschema = '" +
table.schema +
"' and col.tabname = '" +
table.name +
"' and col.colname = '" +
column.name +
"'" +
" and cid.tabschema = col.tabschema" +
" and cid.tabname = col.tabname" +
" and cid.colname = col.colname",
table.database.connection);
reader = command.ExecuteReader();
if (reader.Read())
{
returnValue = new DbIdentity(
(decimal)reader["START"],
(decimal)reader["INCREMENT"],
(string)reader["GENERATED"]
);
}
reader.Close();
break;
case Ddl.Dialect.sqlServer:
//TODO: Complete
//TODO: Check for version (pre-2005) can't get identity metrics
command = new OdbcCommand(
"select scm.name schemaName, " +
"tbl.name tableName, " +
"idc.name columnName, " +
"seed_value seedValue, " +
"increment_value incrementValue " +
"from sys.schemas scm, " +
"sys.tables tbl, " +
"sys.identity_columns idc " +
"where tbl.schema_id = scm.schema_id " +
"and idc.object_id = tbl.object_id " +
"and scm.name = '" + table.schema + "' " +
"and tbl.name = '" + table.name + "' " +
"and idc.name = '" + column.name + "' ",
table.database.connection);
reader = command.ExecuteReader();
if (reader.Read())
{
if( column.type == OdbcType.BigInt )
returnValue = new DbIdentity(
(decimal)(long)reader["seedValue"],
(decimal)(long)reader["incrementValue"],
""
);
// MS SQL 2008 R2
else if (column.type == OdbcType.SmallInt)
returnValue = new DbIdentity(
(decimal)(Int16)reader["seedValue"],
(decimal)(Int16)reader["incrementValue"],
""
);
// MS SQL 2008 R2
else if (column.type == OdbcType.TinyInt)
returnValue = new DbIdentity(
(decimal)(byte)reader["seedValue"],
(decimal)(byte)reader["incrementValue"],
""
);
else
returnValue = new DbIdentity(
(decimal)(int)reader["seedValue"],
(decimal)(int)reader["incrementValue"],
""
);
}
reader.Close();
break;
}
return returnValue;
}
public Column(
Table table,
string name,
//OdbcType type,
string typeName,
int columnSize,
short decimalDigits,
bool nullable,
string defaultValue
)
{
this.table = table;
this.name = name;
this.typeName = typeName;
if (this.typeName == "CHAR () FOR BIT DATA") // DB2
{
this.typeName = "CHAR";
this.specialTypeExtender = "FOR BIT DATA";
}
if (this.typeName == "VARCHAR () FOR BIT DATA") // DB2
{
this.typeName = "VARCHAR";
this.specialTypeExtender = "FOR BIT DATA";
}
if (this.typeName == "INTEGER") // DB2
this.typeName = "INT";
else if (this.typeName == "CLOB") // DB2
this.typeName = "TEXT";
else if (this.typeName == "BLOB") // DB2
this.typeName = "BINARY";
else if (this.typeName == "TIMESTAMP" && table.database.dialect == Ddl.Dialect.db2)
this.typeName = "DATETIME";
else if (this.typeName == "int identity") // MS SQL 2005
this.typeName = "int";
else if (this.typeName == "bigint identity") // MS SQL 2005
this.typeName = "bigint";
else if (this.typeName == "float") // MS SQL 2005
this.typeName = "double";
//else if (this.typeName == "money") // MS SQL 2005
// this.typeName = "decimal";
else if (this.typeName == "smallint identity") // MS SQL 2008 R2
this.typeName = "smallint";
else if (this.typeName == "tinyint identity") // MS SQL 2008 R2
this.typeName = "tinyint";
else if (this.typeName == "datetime2") // MS SQL 2008 R2
{
this.typedateTime2 = true;
this.typeName = "datetime";
}
else if (this.typeName == "datetimeoffset") // MS SQL 2008 R2
{
this.typedateTimeoffset = true;
this.typeName = "datetime";
}
else if (this.typeName == "geography") // MS SQL 2008 R2
{
this.typegeography = true;
this.typeName = "Text";
}
else if (this.typeName == "geometry") // MS SQL 2008 R2
{
this.typegeometry = true;
this.typeName = "Text";
}
else if (this.typeName == "hierarchyid") // MS SQL 2008 R2
{
this.typehierarchyid = true;
this.typeName = "Text";
}
else if (this.typeName == "smallmoney") // MS SQL 2008 R2
{
this.typesmallmoney = true;
this.typeName = "Double";
}
else if (this.typeName == "money") // MS SQL 2008 R2
{
this.typemoney = true;
this.typeName = "Double";
}
else if (this.typeName == "sql_variant") // MS SQL 2008 R2
{
this.typesql_variant = true;
this.typeName = "Text";
}
else if (this.typeName == "xml") // MS SQL 2008 R2
{
this.typexml = true;
this.typeName = "Text";
}
this.type = (OdbcType)Enum.Parse(typeof(OdbcType), this.typeName, true);
this.columnSize = columnSize;
this.decimalDigits = decimalDigits;
this.nullable = nullable;
this.defaultValue = defaultValue;
parameter = new OdbcParameter("@" + this.name, this.type);
if(this.type == OdbcType.Timestamp)
parameter.DbType = DbType.DateTime;
identity = DbIdentity.check(this);
}
