public List <ColumnaBE> Select_SQL_Columna(ConexionBE objConexionBE, TablaBE objTablaBE)
{
List <ColumnaBE> lstColumnaBE = new List <ColumnaBE>();
SqlCommand command = new SqlCommand();
ColumnaBE objColumnaBE = new ColumnaBE();
SqlDataReader dr = null;
try
{
SqlConnection SqlCon = new SqlConnection(ConnectionString.GetSQLServer(objConexionBE));
SqlCon.Open();
command.Connection = SqlCon;
command.CommandType = CommandType.Text;
command.CommandText = "SELECT distinct c.COLUMN_NAME, " +
"c.IS_NULLABLE, " +
"c.DATA_TYPE, " +
"case when (select COUNT(k.COLUMN_NAME) from INFORMATION_SCHEMA.KEY_COLUMN_USAGE k left join INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc on k.CONSTRAINT_NAME = tc.CONSTRAINT_NAME AND k.TABLE_NAME = tc.TABLE_NAME where tc.CONSTRAINT_TYPE = 'PRIMARY KEY' and k.TABLE_NAME = c.TABLE_NAME and k.COLUMN_NAME = c.COLUMN_NAME) > 0 then 'YES' else 'NO' end 'IS_PK', " +
"case when columnproperty(object_id(c.table_name), c.column_name,'IsIdentity') = 1 then 'YES' else 'NO' end 'IS_IDENTITY', " +
"c.CHARACTER_MAXIMUM_LENGTH, " +
"c.NUMERIC_PRECISION, " +
"c.NUMERIC_SCALE " +
"FROM INFORMATION_SCHEMA.COLUMNS c " +
"where c.TABLE_NAME = '" + objTablaBE.Nombre + "' and c.TABLE_SCHEMA = '" + objTablaBE.Esquema + "'";
dr = command.ExecuteReader();
while (dr.Read())
{
objColumnaBE = new ColumnaBE();
objColumnaBE.Nombre = dr["COLUMN_NAME"].ToString();
objColumnaBE.Acepta_Nulos = dr["IS_NULLABLE"].ToString().Equals("NO") ? false : true;
objColumnaBE.Tipo_Dato = dr["DATA_TYPE"].ToString();
objColumnaBE.Es_PK = dr["IS_PK"].ToString().Equals("NO") ? false : true;
objColumnaBE.Es_Identity = dr["IS_IDENTITY"].ToString().Equals("NO") ? false : true;
objColumnaBE.Tamano_Maximo = dr["CHARACTER_MAXIMUM_LENGTH"] != DBNull.Value ? Convert.ToInt32(dr["CHARACTER_MAXIMUM_LENGTH"]) : -1;
objColumnaBE.Precision_Numerica = dr["NUMERIC_PRECISION"] != DBNull.Value ? Convert.ToInt32(dr["NUMERIC_PRECISION"]) : -1;
objColumnaBE.Precision_Numerica_Base = dr["NUMERIC_SCALE"] != DBNull.Value ? Convert.ToInt32(dr["NUMERIC_SCALE"]) : -1;
lstColumnaBE.Add(objColumnaBE);
}
return(lstColumnaBE);
}
catch (Exception)
{
throw;
}
finally
{
command.Dispose();
dr.Close();
dr.Dispose();
}
}
public static String MySQLParameter(ColumnaBE objColumnaBE)
{
switch (objColumnaBE.Tipo_Dato.ToLower())
{
case "char":
case "time":
case "varbinary":
case "varchar":
return(objColumnaBE.Tipo_Dato + "(" + (objColumnaBE.Tamano_Maximo == -1 ? "MAX" : objColumnaBE.Tamano_Maximo.ToString()) + ")");
case "decimal":
return(objColumnaBE.Tipo_Dato + "(" + objColumnaBE.Precision_Numerica + ", " + objColumnaBE.Precision_Numerica_Base + ")");
default:
return(objColumnaBE.Tipo_Dato);
}
}
public static String SQLParameter(ColumnaBE objColumnaBE)
{
switch (objColumnaBE.Tipo_Dato.ToLower())
{
//case "bigint":
//case "bit":
//case "date":
//case "datetime":
//case "float":
//case "geography":
//case "geometry":
//case "hierarchyid":
//case "image":
//case "int":
//case "money":
//case "ntext":
//case "real":
//case "smalldatetime":
//case "smallint":
//case "smallmoney":
//case "sql_variant":
//case "text":
//case "money":
// break;
case "binary":
case "char":
case "datetime2":
case "datetimeoffset":
case "nchar":
case "nvarchar":
case "time":
case "varbinary":
case "varchar":
return(objColumnaBE.Tipo_Dato + "(" + (objColumnaBE.Tamano_Maximo == -1 ? "MAX" : objColumnaBE.Tamano_Maximo.ToString()) + ")");
case "decimal":
case "numeric":
return(objColumnaBE.Tipo_Dato + "(" + objColumnaBE.Precision_Numerica + ", " + objColumnaBE.Precision_Numerica_Base + ")");
default:
return(objColumnaBE.Tipo_Dato);
}
}
