Exemplo n.º 1
0
        public void SchemaPrepare(BuildDbSchemaType buildtype = BuildDbSchemaType.Schema)
        {
            DataDbSchema dbs     = new DataDbSchema(sqlcn);
            bool         wasOpen = false;

            if (sqlcn.State == ConnectionState.Open)
            {
                wasOpen = true;
            }
            if (!wasOpen)
            {
                sqlcn.Open();
            }
            IEnumerable <DataRow> table    = sqlcn.GetSchema("Tables").Rows.Cast <DataRow>().AsEnumerable().AsQueryable();
            IEnumerable <DataRow> columns  = sqlcn.GetSchema("Columns").Rows.Cast <DataRow>().AsEnumerable().AsQueryable();
            IEnumerable <DataRow> index    = sqlcn.GetSchema("IndexColumns").Rows.Cast <DataRow>().AsEnumerable().AsQueryable();
            List <DbTable>        dbTables = table
                                             .Select(t =>
                                                     new DbTable()
            {
                TableName     = t["TABLE_NAME"].ToString(),
                DataDbColumns = new DbColumns()
                {
                    List = columns.Where(c => t["TABLE_NAME"].Equals(c["TABLE_NAME"]))
                           .Select(k => new DbColumn
                    {
                        ColumnName = k["COLUMN_NAME"].ToString(),
                        RubricType = SqlNetType.SqlTypeToNet(k["DATA_TYPE"].ToString()),
                        MaxLength  = (k["CHARACTER_MAXIMUM_LENGTH"] != DBNull.Value) ? Convert.ToInt32(k["CHARACTER_MAXIMUM_LENGTH"]) : -1,
                        isDBNull   = (k["IS_NULLABLE"].ToString() == "YES")? true: false,
                        DbOrdinal  = Convert.ToInt32(k["ORDINAL_POSITION"])
                    }).ToList()
                },
                DbPrimaryKey = index.Where(i =>
                                           t["TABLE_NAME"].Equals(i["table_name"]))
                               .Where(it => columns
                                      .Where(c => c["TABLE_NAME"].Equals(it["table_name"]) &&
                                             c["COLUMN_NAME"].Equals(it["column_name"])).Any())
                               .Select(k => new DbColumn()
                {
                    ColumnName = k["column_name"].ToString(),
                    isIdentity = (k["KeyType"].ToString() == "56") ? true : false,
                    isKey      = true,
                    DbOrdinal  = Convert.ToInt32(k["ordinal_position"]),
                    RubricType = SqlNetType.SqlTypeToNet(columns
                                                         .Where(c => c["TABLE_NAME"].Equals(k["table_name"]) &&
                                                                c["COLUMN_NAME"].Equals(k["column_name"]))
                                                         .First()["DATA_TYPE"].ToString())
                }).ToArray()
            }).ToList();

            dbs.DataDbTables.AddRange(dbTables.ToList());
            if (buildtype == BuildDbSchemaType.Schema)
            {
                DbHand.Schema = dbs;
            }
            else
            {
                DbHand.Temp = dbs;
            }
        }
        public bool DataBulk(SpaceCard[] cards, string buforTable, BulkPrepareType prepareType = BulkPrepareType.None, BulkDbType dbType = BulkDbType.TempDB)
        {
            try
            {
                IMultemic deck = null;
                if (cards.Any())
                {
                    deck = cards.ElementAt(0).Collection;
                    if (_cn.State == ConnectionState.Closed)
                    {
                        _cn.Open();
                    }
                    try
                    {
                        if (dbType == BulkDbType.TempDB)
                        {
                            _cn.ChangeDatabase("tempdb");
                        }
                        if (!DbHand.Temp.DataDbTables.Have(buforTable) || prepareType == BulkPrepareType.Drop)
                        {
                            string createTable = "";
                            if (prepareType == BulkPrepareType.Drop)
                            {
                                createTable += "Drop table if exists [" + buforTable + "] \n";
                            }
                            createTable += "Create Table [" + buforTable + "] ( ";
                            foreach (MemberRubric column in deck.Rubrics.AsValues())
                            {
                                string        sqlTypeString = "varchar(200)";
                                List <string> defineStr     = new List <string>()
                                {
                                    "varchar", "nvarchar", "ntext", "varbinary"
                                };
                                List <string> defineDec = new List <string>()
                                {
                                    "decimal", "numeric"
                                };
                                int colLenght = column.RubricSize;
                                sqlTypeString = SqlNetType.NetTypeToSql(column.RubricType);
                                string addSize = (colLenght > 0) ? (defineStr.Contains(sqlTypeString)) ? (string.Format(@"({0})", colLenght)) :
                                                 (defineDec.Contains(sqlTypeString)) ? (string.Format(@"({0}, {1})", colLenght - 6, 6)) : "" : "";
                                sqlTypeString += addSize;
                                createTable   += " [" + column.RubricName + "] " + sqlTypeString + ",";
                            }
                            createTable = createTable.TrimEnd(new char[] { ',' }) + " ) ";
                            SqlCommand createcmd = new SqlCommand(createTable, _cn);
                            createcmd.ExecuteNonQuery();
                        }
                    }
                    catch (SqlException ex)
                    {
                        throw new SqlInsertException(ex.ToString());
                    }
                    if (prepareType == BulkPrepareType.Trunc)
                    {
                        string     deleteData = "Truncate Table [" + buforTable + "]";
                        SqlCommand delcmd     = new SqlCommand(deleteData, _cn);
                        delcmd.ExecuteNonQuery();
                    }

                    try
                    {
                        DataReader  ndr      = new DataReader(cards);
                        SqlBulkCopy bulkcopy = new SqlBulkCopy(_cn);
                        bulkcopy.DestinationTableName = "[" + buforTable + "]";
                        bulkcopy.WriteToServer(ndr);
                    }
                    catch (SqlException ex)
                    {
                        throw new SqlInsertException(ex.ToString());
                    }
                    return(true);
                }
                else
                {
                    return(false);
                }
            }
            catch (SqlException ex)
            {
                throw new SqlInsertException(ex.ToString());
            }
        }