Esempio n. 1
0
 public int InsertColumn(string TableName, Column aCollumn)
 {
     if (this.aListTable.Where(p => p.TableName == TableName).ToList().Count() > 0)
     {
         this.aListTable.Where(p => p.TableName == TableName).ToList()[0].aListColumn.Insert(0, aCollumn);
         return 1;
     }
     else
     {
         return 0;
     }
 }
Esempio n. 2
0
        private Table GetTable(TableInfo aTableInfo)
        {
            if (this.aConn.State == ConnectionState.Open)
            {
                if (aTableInfo.IsView == false)
                {
                    string sql = "SELECT sys.schemas.name as [Schema] , sys.Tables.name as [Table] , sys.Tables.object_id, sys.all_columns.name as [Column], sys.types.name as [DataType] , sys.indexes.is_primary_key as is_primary_key , sys.all_columns.is_identity , sys.all_columns.column_id ,";
                    sql = sql + "   ISNULL(sys.foreign_key_columns.parent_column_id ,0) as is_foreignkey ,  sys.foreign_keys.name AS Constraints , OBJECT_NAME (sys.foreign_keys.referenced_object_id) AS ReferenceTableName,";
                    sql = sql + "	COL_NAME(sys.foreign_key_columns.referenced_object_id, sys.foreign_key_columns.referenced_column_id) AS ReferenceColumnName,";
                    sql = sql + "	sys.all_columns.is_filestream , sys.all_columns.is_nullable   FROM  ";
                    sql = sql + "     sys.Tables inner join sys.all_columns on sys.Tables.object_id = sys.all_columns.object_id  ";
                    sql = sql + "	             inner join sys.schemas on sys.Tables.schema_id = sys.schemas.schema_id";
                    sql = sql + "				left join  sys.types on sys.all_columns.system_type_id =  sys.types.user_type_id";
                    sql = sql + "				left join  sys.foreign_key_columns on (sys.all_columns.object_id = sys.foreign_key_columns.parent_object_id ) and (sys.all_columns.column_id = sys.foreign_key_columns.parent_column_id)";
                    sql = sql + "				left join sys.foreign_keys  on  sys.foreign_keys.parent_object_id = sys.tables.object_id";
                    sql = sql + "				left join  sys.indexes on sys.Tables.object_id = sys.indexes.OBJECT_ID and sys.indexes.index_id = sys.all_columns.column_id";
                    sql = sql + " 			where sys.tables.name =" + "'" + GetTableOnly(aTableInfo.TableName) + "'";

                    SqlCommand Cmd = new SqlCommand(sql, aConn);
                    SqlDataAdapter Adapter = new SqlDataAdapter(Cmd);
                    DataTable aTable = new DataTable();

                    Table aTemptTable = new Table();
                    Column aTemptColumn = new Column();

                    Adapter.Fill(aTable);

                    aTemptTable.TableName = aTable.Rows[0][1].ToString(); ;
                    aTemptTable.Schema = aTable.Rows[0][0].ToString();
                    for (int i = 0; i < aTable.Rows.Count; i++)
                    {
                        aTemptColumn = new Column();
                        aTemptColumn.ColumnName = aTable.Rows[i]["Column"].ToString();

                        aTemptColumn.DataTypeSQL = aTable.Rows[i]["DataType"].ToString();

                        if (aTable.Rows[i]["DataType"].ToString() == "bigint") { aTemptColumn.DataTypeNET = "Int64"; }
                        else if (aTable.Rows[i]["DataType"].ToString() == "binary") { aTemptColumn.DataTypeNET = "Byte[]"; }
                        else if (aTable.Rows[i]["DataType"].ToString() == "bit") { aTemptColumn.DataTypeNET = "Boolean"; }
                        else if (aTable.Rows[i]["DataType"].ToString() == "char") { aTemptColumn.DataTypeNET = "String"; }

                        else if (aTable.Rows[i]["DataType"].ToString() == "date") { aTemptColumn.DataTypeNET = "DateTime"; }
                        else if (aTable.Rows[i]["DataType"].ToString() == "datetime") { aTemptColumn.DataTypeNET = "DateTime"; }
                        else if (aTable.Rows[i]["DataType"].ToString() == "datetime2") { aTemptColumn.DataTypeNET = "DateTime"; }
                        else if (aTable.Rows[i]["DataType"].ToString() == "datetimeoffset") { aTemptColumn.DataTypeNET = "DateTimeOffset"; }
                        else if (aTable.Rows[i]["DataType"].ToString() == "decimal") { aTemptColumn.DataTypeNET = "Decimal"; }
                        else if (aTable.Rows[i]["DataType"].ToString() == "FILESTREAM attribute (varbinary(max))") { aTemptColumn.DataTypeNET = "Byte[]"; }
                        else if (aTable.Rows[i]["DataType"].ToString() == "float") { aTemptColumn.DataTypeNET = "Double"; }
                        else if (aTable.Rows[i]["DataType"].ToString() == "image") { aTemptColumn.DataTypeNET = "Byte[]"; }
                        else if (aTable.Rows[i]["DataType"].ToString() == "int") { aTemptColumn.DataTypeNET = "int"; }
                        else if (aTable.Rows[i]["DataType"].ToString() == "money") { aTemptColumn.DataTypeNET = "Decimal"; }
                        else if (aTable.Rows[i]["DataType"].ToString() == "nchar") { aTemptColumn.DataTypeNET = "String"; }
                        else if (aTable.Rows[i]["DataType"].ToString() == "ntext") { aTemptColumn.DataTypeNET = "String"; }
                        else if (aTable.Rows[i]["DataType"].ToString() == "numeric") { aTemptColumn.DataTypeNET = "Decimal"; }
                        else if (aTable.Rows[i]["DataType"].ToString() == "nvarchar") { aTemptColumn.DataTypeNET = "String"; }

                        else if (aTable.Rows[i]["DataType"].ToString() == "real") { aTemptColumn.DataTypeNET = "Single"; }
                        else if (aTable.Rows[i]["DataType"].ToString() == "rowversion") { aTemptColumn.DataTypeNET = "Byte[]"; }
                        else if (aTable.Rows[i]["DataType"].ToString() == "smalldatetime") { aTemptColumn.DataTypeNET = "DateTime"; }
                        else if (aTable.Rows[i]["DataType"].ToString() == "smallint") { aTemptColumn.DataTypeNET = "Int16"; }
                        else if (aTable.Rows[i]["DataType"].ToString() == "smallmoney") { aTemptColumn.DataTypeNET = "Decimal"; }
                        else if (aTable.Rows[i]["DataType"].ToString() == "sql_variant") { aTemptColumn.DataTypeNET = "Object"; }
                        else if (aTable.Rows[i]["DataType"].ToString() == "text") { aTemptColumn.DataTypeNET = "String"; }
                        else if (aTable.Rows[i]["DataType"].ToString() == "time") { aTemptColumn.DataTypeNET = "TimeSpan"; }
                        else if (aTable.Rows[i]["DataType"].ToString() == "timestamp") { aTemptColumn.DataTypeNET = "Byte[]"; }
                        else if (aTable.Rows[i]["DataType"].ToString() == "tinyint") { aTemptColumn.DataTypeNET = "Byte"; }
                        else if (aTable.Rows[i]["DataType"].ToString() == "uniqueidentifier") { aTemptColumn.DataTypeNET = "Guid"; }
                        else if (aTable.Rows[i]["DataType"].ToString() == "varbinary") { aTemptColumn.DataTypeNET = "Byte[]"; }
                        else if (aTable.Rows[i]["DataType"].ToString() == "varchar") { aTemptColumn.DataTypeNET = "String"; }
                        else if (aTable.Rows[i]["DataType"].ToString() == "xml") { aTemptColumn.DataTypeNET = "Xml"; }

                        else
                        {
                            aTemptColumn.DataTypeNET = aTable.Rows[i]["DataType"].ToString();
                        }

                        //aTemptColumn.DefaultValue = aTable.Rows[i][4].ToString();
                        aTemptColumn.AllowNull = Convert.ToBoolean(aTable.Rows[i][12].ToString());

                        if (bool.Parse(aTable.Rows[i]["is_identity"].ToString()) == true)
                        {
                            aTemptColumn.AutoColumn = true;

                        }
                        if (aTable.Rows[i]["is_primary_key"].ToString().ToUpper() == "true".ToUpper())
                        {
                            aTemptColumn.ColumnType = 1;

                        }
                        //else if (int.Parse(aTable.Rows[i]["is_foreignkey"].ToString()) > 0 )
                        else if (int.Parse(aTable.Rows[i]["is_foreignkey"].ToString()) > 0)
                        {
                            aTemptColumn.ColumnType = 2;
                            aTemptColumn.RefTable = aTable.Rows[i]["ReferenceTableName"].ToString();
                            aTemptColumn.RefColumn = aTable.Rows[i]["ReferenceColumnName"].ToString();

                        }
                        else
                        {
                            aTemptColumn.ColumnType = 3;
                        }

                        aTemptTable.aListColumn.Insert(aTemptTable.aListColumn.Count, aTemptColumn);

                    }
                    return aTemptTable;
                }

            }
            return null;
        }
Esempio n. 3
0
 public int InsertColumn(string TableName , Column aCollumn);