//----------------------------------------------------------------------------------------------------
public static string BuildSql__PrimaryKey_Select(TableStructure tableStructure)
{
AjaxService.ASPdatabaseService.GetSetVal();
tableStructure.Validate(Enums.ValidationTypes.Select);
string sql = String.Format(@"
SELECT
[ConstraintName] = kcu.CONSTRAINT_NAME,
[TableCatalog] = kcu.TABLE_CATALOG,
[Schema] = kcu.TABLE_SCHEMA,
[TableName] = kcu.TABLE_NAME,
[ColumnName] = kcu.COLUMN_NAME,
[OrdinalPosition] = kcu.ORDINAL_POSITION,
[ConstraintType] = tc.CONSTRAINT_TYPE
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE as kcu
Inner Join INFORMATION_SCHEMA.TABLE_CONSTRAINTS as tc ON kcu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
WHERE tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
and kcu.TABLE_SCHEMA = '{0}' and kcu.Table_Name = '{1}'
Order By kcu.ORDINAL_POSITION;
",
tableStructure.Schema,
tableStructure.TableName);
return(sql);
}
//----------------------------------------------------------------------------------------------------
public static string BuildSql__Table_Drop(TableStructure tableStructure)
{
AjaxService.ASPdatabaseService.GetSetVal();
tableStructure.Validate(Enums.ValidationTypes.Drop);
string sql = String.Format(@" DROP TABLE [{0}].[{1}]; ", tableStructure.Schema, tableStructure.TableName);
return(sql);
}
//----------------------------------------------------------------------------------------------------
public static string BuildSql__Column_SelectAll(TableStructure tableStructure)
{
AjaxService.ASPdatabaseService.GetSetVal();
tableStructure.Validate(Enums.ValidationTypes.Select);
string sql = String.Format(@"
SELECT
col.TABLE_SCHEMA as [Schema],
col.TABLE_NAME as [TableName],
col.ORDINAL_POSITION as [OrdinalPosition],
col.COLUMN_NAME as [ColumnName],
col.DATA_TYPE as [DataType],
col.CHARACTER_MAXIMUM_LENGTH as [MaxLength],
col.NUMERIC_PRECISION as [Precision],
col.NUMERIC_SCALE as [Scale],
col.IS_NULLABLE as [AllowNulls],
col.COLUMN_DEFAULT as [DefaultValue],
CASE WHEN CCU.COLUMN_NAME IS NULL THEN 0 ELSE 1 END AS [IsPrimaryKey],
COLUMNPROPERTY(object_id('[' + col.Table_Schema + '].[' + col.Table_Name + ']'), COL.COLUMN_NAME, 'IsIdentity') AS [IsIdentity],
IDENT_SEED('[' + col.Table_Schema + '].[' + col.Table_Name + ']') AS Identity_Seed,
IDENT_INCR('[' + col.Table_Schema + '].[' + col.Table_Name + ']') AS Identity_Increment,
IDENT_CURRENT('[' + col.Table_Schema + '].[' + col.Table_Name + ']') AS Identity_CurrentIdentity
FROM
INFORMATION_SCHEMA.COLUMNS as col
left JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS as tc
ON col.TABLE_SCHEMA = tc.TABLE_SCHEMA AND col.TABLE_NAME = tc.TABLE_NAME AND tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
left JOIN
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE as ccu
ON col.TABLE_SCHEMA = tc.TABLE_SCHEMA AND col.COLUMN_NAME = ccu.COLUMN_NAME AND tc.CONSTRAINT_NAME = ccu.CONSTRAINT_NAME
where col.[TABLE_SCHEMA] = '{0}' and col.[TABLE_NAME] = '{1}'
order by
col.TABLE_SCHEMA, col.TABLE_NAME, col.ORDINAL_POSITION
",
tableStructure.Schema,
tableStructure.TableName);
return(sql);
}
//----------------------------------------------------------------------------------------------------
public static string BuildSql__Index_Select(TableStructure tableStructure)
{
AjaxService.ASPdatabaseService.GetSetVal();
tableStructure.Validate(Enums.ValidationTypes.Select);
string sql = String.Format(@"
SELECT
[Schema] = SC.name,
[TableName] = T.name,
[IndexName] = IND.name,
[IsUnique] = IND.is_unique,
[ColumnId] = IC.index_column_id,
[ColumnName] = COL.name,
[IsDescending] = IC.is_descending_key
FROM
sys.indexes IND
INNER JOIN
sys.index_columns IC
on IND.object_id = IC.object_id and IND.index_id = IC.index_id
INNER JOIN
sys.columns COL
on IC.object_id = COL.object_id and IC.column_id = COL.column_id
INNER JOIN
sys.tables T
on IND.object_id = T.object_id
INNER JOIN
sys.schemas SC
on T.schema_id = SC.schema_id
WHERE
IND.is_primary_key = 0
and SC.name = '{0}'
and T.name = '{1}'
ORDER BY
SC.name, T.name, IND.name, IC.index_column_id;
",
tableStructure.Schema, tableStructure.TableName);
return(sql);
}
//----------------------------------------------------------------------------------------------------
public static string BuildSql__ForeignKey_Select(TableStructure tableStructure)
{
AjaxService.ASPdatabaseService.GetSetVal();
tableStructure.Validate(Enums.ValidationTypes.Select);
string sql = String.Format(@"
(select
[RelationshipSide] = 'FK',
[ConstraintName] = RC.CONSTRAINT_NAME,
[OrdinalPosition] = CU.ORDINAL_POSITION,
[FK_Schema] = FK.TABLE_SCHEMA,
[FK_Table] = FK.TABLE_NAME,
[FK_Column] = CU.COLUMN_NAME,
[PK_Schema] = PK.TABLE_SCHEMA,
[PK_Table] = PK.TABLE_NAME,
[PK_Column] = PT.COLUMN_NAME,
[UpdateRule] = RC.UPDATE_RULE,
[DeleteRule] = RC.DELETE_RULE
from
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
on RC.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
on RC.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
on RC.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN (SELECT t1.TABLE_SCHEMA, t1.TABLE_NAME, t2.COLUMN_NAME, t2.ORDINAL_POSITION
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS t1
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE t2
on t1.CONSTRAINT_NAME = t2.CONSTRAINT_NAME
WHERE t1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT
on
PT.TABLE_SCHEMA = PK.TABLE_SCHEMA and
PT.TABLE_NAME = PK.TABLE_NAME and
PT.ORDINAL_POSITION = CU.ORDINAL_POSITION
where
FK.TABLE_SCHEMA = '{0}' and FK.TABLE_NAME = '{1}')
UNION
(select
[RelationshipSide] = 'PK',
[ConstraintName] = RC.CONSTRAINT_NAME,
[OrdinalPosition] = CU.ORDINAL_POSITION,
[FK_Schema] = FK.TABLE_SCHEMA,
[FK_Table] = FK.TABLE_NAME,
[FK_Column] = CU.COLUMN_NAME,
[PK_Schema] = PK.TABLE_SCHEMA,
[PK_Table] = PK.TABLE_NAME,
[PK_Column] = PT.COLUMN_NAME,
[UpdateRule] = RC.UPDATE_RULE,
[DeleteRule] = RC.DELETE_RULE
from
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
on RC.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
on RC.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
on RC.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN (SELECT t1.TABLE_SCHEMA, t1.TABLE_NAME, t2.COLUMN_NAME, t2.ORDINAL_POSITION
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS t1
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE t2
on t1.CONSTRAINT_NAME = t2.CONSTRAINT_NAME
WHERE t1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT
on
PT.TABLE_SCHEMA = PK.TABLE_SCHEMA and
PT.TABLE_NAME = PK.TABLE_NAME and
PT.ORDINAL_POSITION = CU.ORDINAL_POSITION
where
PK.TABLE_SCHEMA = '{0}' and PK.TABLE_NAME = '{1}')
Order By
RelationshipSide, ConstraintName, OrdinalPosition;
",
tableStructure.Schema,
tableStructure.TableName);
return(sql);
}
//----------------------------------------------------------------------------------------------------
//public static string BuildSql__Table_Select(TableStructure tableStructure)
//{
// tableStructure.Validate(Enums.ValidationTypes.Select);
// string sql = "";
// return sql;
//}
//----------------------------------------------------------------------------------------------------
public static string BuildSql__Table_Create(TableStructure tableStructure)
{
AjaxService.ASPdatabaseService.GetSetVal();
tableStructure.Validate(Enums.ValidationTypes.Create);
string sql = "";
tableStructure.Columns = (from c in tableStructure.Columns
orderby c.OrdinalPosition
select c).ToArray();
string sql_Columns = "";
foreach (var column in tableStructure.Columns)
{
//------------------------------
if (sql_Columns != "")
{
sql_Columns += ", \n ";
}
//------------------------------ Null / Not Null
string strNull = " NOT NULL";
if (column.AllowNulls)
{
strNull = " NULL";
}
//------------------------------ Default
string strDefault = "";
if (column.DefaultValue != null)
{
if (column.IsNumericType_GetAndSet() && !column.DefaultValue.EndsWith(")"))
{
column.DefaultValue = "(" + column.DefaultValue + ")";
}
string dValue = column.DefaultValue;
if (dValue.StartsWith("'") && !dValue.StartsWith("''"))
{
dValue = dValue.Substring(1, dValue.Length - 1);
}
if (dValue.EndsWith("'") && !dValue.EndsWith("''"))
{
dValue = dValue.Substring(0, dValue.Length - 1);
}
dValue = dValue.Replace("'", "''");
if (dValue.EndsWith(")"))
{
strDefault = " DEFAULT" + dValue + "";
}
else
{
strDefault = " DEFAULT(N'" + dValue + "')";
}
}
//------------------------------ Identity
string strIdentity = "";
if (column.IsIdentity && column.Identity != null)
{
strNull = " NOT NULL";
strDefault = "";
strIdentity = String.Format(" IDENTITY({0},{1})", column.Identity.Seed, column.Identity.Increment);
}
//------------------------------
sql_Columns += String.Format(" [{0}] {1}{2}{3}{4}", column.ColumnName, column.DataType, strIdentity, strNull, strDefault);
}
sql = String.Format(@"
CREATE TABLE [{0}].[{1}]
( {2} );
",
tableStructure.Schema, tableStructure.TableName,
sql_Columns);
return(sql);
}