private string OracleDataTypeToMSSQL(USER_TAB_COLUMNS oracleType)
{
if (sysdatatypemappingsList == null || sysdatatypemappingsList.Count == 0)
{
return string.Empty;
}
if (oracleType.CHARACTER_SET_NAME == "CHAR_CS")
{
return "varchar";
}
if (oracleType.CHARACTER_SET_NAME == "NCHAR_CS")
{
return "nvarchar";
}
if (oracleType.DATA_TYPE == "NUMBER")
{
if (string.IsNullOrEmpty( oracleType.DATA_PRECISION))
{
return "bigint";
}
if (int.Parse(oracleType.DATA_PRECISION) <= 1 && (string.IsNullOrEmpty(oracleType.DATA_SCALE) || int.Parse(oracleType.DATA_SCALE) == 0))
{
return "bit";
}
if (int.Parse(oracleType.DATA_PRECISION) <= 3 && (string.IsNullOrEmpty(oracleType.DATA_SCALE) || int.Parse(oracleType.DATA_SCALE) == 0))
{
return "bit";
}
if (int.Parse(oracleType.DATA_PRECISION) <= 5 && (string.IsNullOrEmpty(oracleType.DATA_SCALE) || int.Parse(oracleType.DATA_SCALE) == 0))
{
return "smallint";
}
if ( int.Parse(oracleType.DATA_PRECISION) <= 10 && (string.IsNullOrEmpty(oracleType.DATA_SCALE) || int.Parse(oracleType.DATA_SCALE) ==0 ))
{
return "int";
}
return "int";
}
if (oracleType.DATA_TYPE == "DATE")
{
return "datetime";
}
if (oracleType.DATA_TYPE == "TIMESTAMP")
{
string sqltype = string.Empty;
switch (oracleType.DATA_LENGTH)
{
case "3":
sqltype = "datetime";
break;
case "7":
sqltype = "datetime2";
break;
default:
break;
}
return sqltype;
}
//sysdatatypemappings datatypemap = sysdatatypemappingsList.Find(p => (p.destination_type == oracleType.DATA_TYPE ));
return string.Empty;
}
private string GetColumnSql(USER_TAB_COLUMNS dr)
{
StringBuilder sql = new StringBuilder();
sql.AppendFormat("\t[{0}] {1}{2} {3} {4}, --{5}\r\n",
dr.COLUMN_NAME,
OracleDataTypeToMSSQL(dr),
(HasSize(OracleDataTypeToMSSQL( dr))) ? "(" + dr.DATA_LENGTH + ")" : (HasPrecisionAndScale(dr.DATA_TYPE)) ? "(" + dr.DATA_PRECISION + "," + dr.DATA_SCALE + ")" : "",
"", //(dr["IsIdentity"].ToString() == "true") ? "IDENTITY" : "",
( dr.NULLABLE == "Y") ? "NULL" : "NOT NULL",
dr.COMMENTS
);
return sql.ToString();
}