private static string GetTypeWithLength(vw_SPGenenerator row)
{
if (row.CHARACTER_MAXIMUM_LENGTH != null && (row.DATA_TYPE != "text") && (row.DATA_TYPE != "ntext") && (row.DATA_TYPE != "image"))
{
return(string.Format("({0})", (row.CHARACTER_MAXIMUM_LENGTH == -1) ? "MAX" : row.CHARACTER_MAXIMUM_LENGTH.ToString()));
}
return("");
}
private Collection <vw_SPGenenerator> GetTabels_Rows(string databaseName, string csvTableNames)
{
mainSqlConnection = new SqlConnection(txtConnectionString.Text);
#region selectCommand
string whereClause = "";
string[] schemaDotTableNameArray = csvTableNames.Split(',');
for (int i = 0; i < schemaDotTableNameArray.Length; i++)
{
string[] temp = schemaDotTableNameArray[i].Split('.');
string schemaName = temp[0];
string tableName = temp[1];
if (i > 0)
{
whereClause += " Or ";
}
whereClause += string.Format(@"
([{0}].INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA = '{1}'
And [{0}].INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = '{2}'
)" , databaseName, schemaName, tableName);
}
string selectCommand = string.Format(
"{2}" +
"SELECT TOP (100) PERCENT {2}" +
" [{0}].INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA,{2}"+
" [{0}].INFORMATION_SCHEMA.COLUMNS.TABLE_NAME,{2}"+
" [{0}].INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME,{2}"+
" [{0}].INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION,{2}"+
" [{0}].INFORMATION_SCHEMA.COLUMNS.DATA_TYPE,{2}"+
" [{0}].INFORMATION_SCHEMA.COLUMNS.CHARACTER_MAXIMUM_LENGTH,{2}"+
" ({2}"+
" SELECT COUNT(*) AS Expr1{2}"+
" FROM [{0}].sys.all_columns {2}"+
" INNER JOIN [{0}].sys.tables ON [{0}].sys.all_columns.object_id = [{0}].sys.tables.object_id{2}"+
" INNER JOIN [{0}].sys.types ON [{0}].sys.all_columns.system_type_id = [{0}].sys.types.system_type_id{2}"+
" INNER JOIN {2}"+
" [{0}].sys.identity_columns ON [{0}].sys.tables.object_id = [{0}].sys.identity_columns.object_id{2}"+
" AND{2}"+
" [{0}].sys.all_columns.column_id = [{0}].sys.identity_columns.column_id{2}"+
" WHERE{2}"+
" ([{0}].sys.tables.name = [{0}].INFORMATION_SCHEMA.COLUMNS.TABLE_NAME){2}"+
" AND{2}"+
" ([{0}].sys.all_columns.name = [{0}].INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME){2}"+
" ) AS IsIdentity,{2}"+
" vw_PK_Table_Column.name AS Constraint_Name,{2}"+
" vw_PK_Table_Column.type AS Constraint_Type,{2}"+
" vw_PK_Table_Column.unique_index_id,{2}"+
" IndexTypeTable.IndexType{2}"+
"FROM{2}" +
" [{0}].INFORMATION_SCHEMA.COLUMNS {2}"+
" LEFT OUTER JOIN{2}"+
" ({2}"+
" SELECT{2}"+
" tables_1.name AS tblName,{2}"+
" all_columns_1.name AS ColName,{2}"+
" [{0}].sys.indexes.type AS IndexType{2}"+
" FROM{2}"+
" [{0}].sys.all_columns AS all_columns_1{2}"+
" INNER JOIN [{0}].sys.tables AS tables_1 ON all_columns_1.object_id = tables_1.object_id{2}"+
" INNER JOIN{2}"+
" [{0}].sys.index_columns AS index_columns_1 ON tables_1.object_id = index_columns_1.object_id {2}"+
" AND {2}"+
" all_columns_1.column_id = index_columns_1.column_id{2}"+
" INNER JOIN{2}"+
" [{0}].sys.indexes ON index_columns_1.object_id = [{0}].sys.indexes.object_id{2}"+
" AND{2}"+
" index_columns_1.index_id = [{0}].sys.indexes.index_id) AS IndexTypeTable ON [{0}].INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = IndexTypeTable.tblName {2}"+
" AND {2}"+
" [{0}].INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME = IndexTypeTable.ColName{2}"+
" LEFT OUTER JOIN{2}"+
" ({2}"+
" SELECT{2}"+
" [{0}].sys.key_constraints.name,{2}"+
" [{0}].INFORMATION_SCHEMA.KEY_COLUMN_USAGE.TABLE_NAME,{2}"+
" [{0}].INFORMATION_SCHEMA.KEY_COLUMN_USAGE.COLUMN_NAME,{2}"+
" [{0}].sys.key_constraints.object_id,{2}"+
" [{0}].sys.key_constraints.principal_id,{2}"+
" [{0}].sys.key_constraints.parent_object_id,{2}"+
" [{0}].sys.key_constraints.type,{2}"+
" [{0}].sys.key_constraints.unique_index_id{2}"+
" FROM{2}"+
" [{0}].INFORMATION_SCHEMA.KEY_COLUMN_USAGE{2}"+
" INNER JOIN [{0}].sys.key_constraints ON [{0}].INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_NAME = [{0}].sys.key_constraints.name{2}"+
" ) {2}"+
" AS vw_PK_Table_Column ON [{0}].INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = vw_PK_Table_Column.TABLE_NAME {2}"+
" AND {2}"+
" [{0}].INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME = vw_PK_Table_Column.COLUMN_NAME{2}"+
" WHERE{2}" +
" ({1}){2}"+
"ORDER BY {2}" +
" [{0}].INFORMATION_SCHEMA.COLUMNS.TABLE_NAME,{2}"+
" [{0}].INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION{2}",
databaseName, whereClause, Environment.NewLine);
#endregion
SqlDataAdapter da = new SqlDataAdapter(selectCommand, mainSqlConnection);
DataSet dsColumnsOfTable = new DataSet();
da.Fill(dsColumnsOfTable);
Collection <vw_SPGenenerator> records = new Collection <vw_SPGenenerator>();
//vw_SPGenenerator record;
foreach (DataRow row in dsColumnsOfTable.Tables[0].Rows)
{
vw_SPGenenerator record = new vw_SPGenenerator();
record.CHARACTER_MAXIMUM_LENGTH = (row["CHARACTER_MAXIMUM_LENGTH"] == DBNull.Value) ? null : (int?)row["CHARACTER_MAXIMUM_LENGTH"];
record.COLUMN_NAME = (string)row["COLUMN_NAME"];
if (row["Constraint_Name"] != System.DBNull.Value)
{
record.Constraint_Name = (string)row["Constraint_Name"];
}
if (row["Constraint_Type"] != System.DBNull.Value)
{
record.Constraint_Type = (string)row["Constraint_Type"];
}
record.DATA_TYPE = (string)row["DATA_TYPE"];
record.IsIdentity = (int?)row["IsIdentity"];
//record.IsIndex = (int?)row["IsIndex"];
if (row["IndexType"] != DBNull.Value)
{
record.IndexType = (byte?)row["IndexType"];
}
record.ORDINAL_POSITION = (int?)row["ORDINAL_POSITION"];
record.TABLE_NAME = (string)row["TABLE_NAME"];
record.TABLE_SCHEMA = (string)row["TABLE_SCHEMA"];
if (row["unique_index_id"] != System.DBNull.Value)
{
record.unique_index_id = (int?)row["unique_index_id"];
}
//(string)dr["COLUMN_NAME"],
records.Add(record);
}
return(records);
}
