C# DBConnector.TransBegin方法代码示例

        /// <summary>
        /// imports the data from the list
        /// (only newer data will be imported)
        /// </summary>
        /// <param name="fileName"></param>
        internal void ImportCommodities(List<EDCommodities> Commodities)
        {
            String              sqlString;
            Int32 Counter = 0;

            DBConnector lDBCon = new DBConnector(Program.DBCon.ConfigData, true);					

            try
            {
                // gettin' some freaky performance
                lDBCon.Execute("set global innodb_flush_log_at_trx_commit=2");

                lDBCon.TransBegin();
                
                sendProgressEvent(new ProgressEventArgs() { Info="import commodities", CurrentValue=0, TotalValue=Commodities.Count });

                // insert or update all commodities
                foreach (EDCommodities Commodity in Commodities)
                {
                    sqlString = "insert into tbCategory(id, category, loccategory) values ("  +
                                Commodity.Category.Id.ToString() + "," +
                                SQL.DBConnector.SQLAString(Commodity.Category.Name.ToString()) + "," +
                                SQL.DBConnector.SQLAString(Commodity.Category.Name.ToString()) +  
                                ") ON DUPLICATE KEY UPDATE " +
                                "category         = " + SQL.DBConnector.SQLAString(Commodity.Category.Name.ToString()) + "," +
                                "loccategory      = " + SQL.DBConnector.SQLAString(Commodity.Category.Name.ToString());

                    lDBCon.Execute(sqlString);

                    sqlString = "insert into tbCommodity(id, commodity, loccommodity, category_id, average_price) values ("  +
                                Commodity.Id.ToString() + "," + 
                                SQL.DBConnector.SQLAString(Commodity.Name.ToString()) + "," + 
                                SQL.DBConnector.SQLAString(Commodity.Name.ToString()) + "," + 
                                Commodity.CategoryId.ToString() + "," + 
                                DBConvert.ToString(Commodity.AveragePrice) + 
                                ") ON DUPLICATE KEY UPDATE " +
                                "commodity         = " + SQL.DBConnector.SQLAString(Commodity.Name.ToString()) + "," + 
                                "loccommodity      = " + SQL.DBConnector.SQLAString(Commodity.Name.ToString()) + "," + 
                                "category_id       = " + Commodity.CategoryId.ToString() + "," + 
                                "average_price     = " + DBConvert.ToString(Commodity.AveragePrice);

                    lDBCon.Execute(sqlString);

                    Counter++;

                    
                    
                    if(sendProgressEvent(new ProgressEventArgs() { Info="import commodities", CurrentValue=Counter, TotalValue=Commodities.Count }))
                        break;
                }

                lDBCon.TransCommit();

                // reset freaky performance
                lDBCon.Execute("set global innodb_flush_log_at_trx_commit=1");

                lDBCon.Dispose();
            }
            catch (Exception ex)
            {
                if(lDBCon.TransActive())
                    lDBCon.TransRollback();

                try
                {
                    // reset freaky performance
                    lDBCon.Execute("set global innodb_flush_log_at_trx_commit=1");
                    lDBCon.Dispose();
                }
                catch (Exception) { }

                throw new Exception("Error while importing system data", ex);
            }
        }

        /// <summary>
        /// imports the old list of visited stations to the database
        /// </summary>
        /// <param name="StationObject"></param>
        /// <param name="EconomyRow"></param>
        public void ImportVisitedStations(string Filename)
        {
            String sqlString;
            Int32 Counter = 0;

            DBConnector lDBCon = new DBConnector(Program.DBCon.ConfigData, true);

            try
            {
                List<StationVisit> History  = JsonConvert.DeserializeObject<List<StationVisit>>(File.ReadAllText(Filename));

                // gettin' some freaky performance
                lDBCon.Execute("set global innodb_flush_log_at_trx_commit=2");

                lDBCon.TransBegin("ImportVisitedStations");

                sendProgressEvent(new ProgressEventArgs() { Info="import visited stations", CurrentValue=Counter,  TotalValue=History.Count });

                foreach(StationVisit VisitEvent in History)
                {
                    String System  = StructureHelper.CombinedNameToSystemName(VisitEvent.Station);                    
                    String Station = StructureHelper.CombinedNameToStationName(VisitEvent.Station);

                    //Debug.Print(System + "," + Location);

                    try
                    {
                        sqlString = String.Format("insert ignore into tbVisitedSystems(system_id, time)" +
                                                  " SELECT d.* FROM (SELECT" +
                                                  " (select id from tbSystems where systemname = {0}) as system_id," +
                                                  " {1} as time) as d", 
                                                  DBConnector.SQLAString(DBConnector.SQLEscape(System)), 
                                                  DBConnector.SQLDateTime(VisitEvent.Visited));

                        lDBCon.Execute(sqlString);
                    }
                    catch (Exception)
                    {
                        //Debug.Print("Error while importing system in history :" + System);
                    };

                    try
                    {
                        sqlString = String.Format("insert ignore into tbVisitedStations(station_id, time)" +
                                                  " SELECT d.* FROM (SELECT" +
                                                  " (select id from tbStations" + 
                                                  "        where stationname = {0}" + 
                                                  "          and system_id   = (select id from tbSystems where systemname = {1})) as station_id," +
                                                  " {2} as time) as d", 
                                                  DBConnector.SQLAString(DBConnector.SQLEscape(Station)), 
                                                  DBConnector.SQLAString(DBConnector.SQLEscape(System)),
                                                  DBConnector.SQLDateTime(VisitEvent.Visited));

                        lDBCon.Execute(sqlString);
                    }
                    catch (Exception)
                    {
                        //Debug.Print("Error while importing station in history :" + Location);
                    };
                    
                    Counter++;
                    sendProgressEvent(new ProgressEventArgs() { Info="import visited stations", CurrentValue=Counter,  TotalValue=History.Count });
                }

                lDBCon.TransCommit();

                lDBCon.Execute("set global innodb_flush_log_at_trx_commit=1");

                lDBCon.Dispose();
            }
            catch (Exception ex)
            {
                if(lDBCon.TransActive())
                    lDBCon.TransRollback();

                lDBCon.Execute("set global innodb_flush_log_at_trx_commit=1");

                lDBCon.Dispose();

                throw new Exception("Error while importing the history of visited stations", ex);
            }
        }

        /// <summary>
        /// imports the data from the file into the database
        /// (only newer data will be imported)
        /// </summary>
        /// <param name="fileName"></param>
        public void ImportStations(String Filename, Boolean addPrices)
        {
            DBConnector               lDBCon = null;
            String sqlString;
            List<EDStation> Stations;
            dsEliteDB.tbstations_orgRow[] FoundRows_org;
            dsEliteDB.tbstationsRow[] FoundRows;
            DateTime Timestamp_new, Timestamp_old;
            Int32 ImportCounter = 0;
            dsEliteDB Data;
            Int32 Counter = 0;
            UInt32 currentComodityClassificationID=0;
            Int32 updated = 0;
            Int32 added = 0;


            Data = new dsEliteDB();

            try
            {
                lDBCon = new DBConnector(Program.DBCon.ConfigData, true);

                // gettin' some freaky performance
                lDBCon.Execute("set global innodb_flush_log_at_trx_commit=2");

                Stations = JsonConvert.DeserializeObject<List<EDStation>>(File.ReadAllText(Filename));

                sendProgressEvent(new ProgressEventArgs() { Info="import systems", NewLine = true });

                lDBCon.TransBegin();

                sqlString = "select * from tbStations lock in share mode";
                lDBCon.TableRead(sqlString, Data.tbstations);
                sqlString = "select * from tbStations_org lock in share mode";
                lDBCon.TableRead(sqlString, Data.tbstations_org);
                sqlString = "select * from tbStationEconomy lock in share mode";
                lDBCon.TableRead(sqlString, Data.tbstationeconomy);
                sqlString = "select * from tbsource";
                lDBCon.Execute(sqlString, Data.tbsource);
                sqlString = "select * from tbCommodityClassification lock in share mode";
                lDBCon.TableRead(sqlString, Data.tbcommodityclassification);
                sqlString = "select * from tbcommodity_has_attribute lock in share mode";
                lDBCon.TableRead(sqlString, Data.tbcommodity_has_attribute);
                sqlString = "select * from tbattribute lock in share mode";
                lDBCon.TableRead(sqlString, Data.tbattribute);

                currentComodityClassificationID = getFreeIndex("tbCommodityClassification");

                lDBCon.Execute(sqlString, Data.tbsource);

                foreach (EDStation Station in Stations)
                {

                    FoundRows = (dsEliteDB.tbstationsRow[])Data.tbstations.Select("id=" + Station.Id.ToString());

                    if (FoundRows.Count() > 0)
                    {
                        // Location is existing

                        if ((bool)(FoundRows[0]["is_changed"]))
                        {
                            // data is changed by user - hold it ...

                            // ...and check table "tbStations_org" for the original data
                            FoundRows_org = (dsEliteDB.tbstations_orgRow[])Data.tbstations_org.Select("id=" + Station.Id.ToString());

                            if ((FoundRows_org != null) && (FoundRows_org.Count() > 0))
                            {
                                // Location is in "tbStations_org" existing - keep the newer version 
                                Timestamp_old = (DateTime)(FoundRows_org[0]["updated_at"]);
                                Timestamp_new = DateTimeOffset.FromUnixTimeSeconds(Station.UpdatedAt).DateTime;

                                if (Timestamp_new > Timestamp_old)
                                {
                                    // data from file is newer
                                    CopyEDStationToDataRow(Station, (DataRow)FoundRows_org[0], false, null, true);

                                    CopyEDStationEconomiesToDataRows(Station, Data.tbstationeconomy);
                                    CopyEDStationCommodityToDataRow(Station, Data, ref currentComodityClassificationID);

                                    ImportCounter += 1;

                                }
                            }

                        }
                        else
                        {
                            // Location is existing - keep the newer version 
                            Timestamp_old = (DateTime)(FoundRows[0]["updated_at"]);
                            Timestamp_new = DateTimeOffset.FromUnixTimeSeconds(Station.UpdatedAt).DateTime;

                            if (Timestamp_new > Timestamp_old)
                            {
                                // data from file is newer
//.........这里部分代码省略.........

        /// <summary>
        /// imports the "own" station data into the database
        /// </summary>
        /// <param name="fileName"></param>
        public void ImportStations_Own(List<EDStation> Stations, Dictionary<Int32, Int32> changedSystemIDs, Boolean OnlyAddUnknown = false, Boolean setVisitedFlag = false)
        {
            DBConnector               lDBCon = null;
            String sqlString;
            dsEliteDB.tbstationsRow[] FoundRows;
            dsEliteDB.tbsystemsRow[] FoundSysRows;
            DateTime Timestamp_new, Timestamp_old;
            Int32 ImportCounter = 0;
            Int32 currentSelfCreatedIndex = -1;
            dsEliteDB Data = new dsEliteDB();
            Int32 Counter = 0;

            try
            {
                lDBCon = new DBConnector(Program.DBCon.ConfigData, true);

                // gettin' some freaky performance
                lDBCon.Execute("set global innodb_flush_log_at_trx_commit=2");

                lDBCon.TransBegin();

                sqlString = "select * from tbStations lock in share mode";
                lDBCon.TableRead(sqlString, Data.tbstations);
                sqlString = "select * from tbStations_org lock in share mode";
                lDBCon.TableRead(sqlString, Data.tbstations_org);
                sqlString = "select * from tbStationEconomy lock in share mode";
                lDBCon.TableRead(sqlString, Data.tbstationeconomy);

                // get the smallest ID for self added stations
                sqlString = "select min(id) As min_id from tbStations";
                lDBCon.Execute(sqlString, "minID", Data);

                if (Convert.IsDBNull(Data.Tables["minID"].Rows[0]["min_id"]))
                    currentSelfCreatedIndex = -1;
                else
                {
                    currentSelfCreatedIndex = ((Int32)Data.Tables["minID"].Rows[0]["min_id"]) - 1;
                    if (currentSelfCreatedIndex >= 0)
                        currentSelfCreatedIndex = -1;
                }

                sendProgressEvent(new ProgressEventArgs() { Info="import self-added stations", CurrentValue=Counter,  TotalValue=Stations.Count });

                foreach (EDStation Station in Stations)
                {
                    Int32 SystemID;

                    if(Station.Name == "Glass City")
                        Debug.Print("stop");

                    // is the system id changed ? --> get the new system id, otherwise the original
                    if (changedSystemIDs.TryGetValue(Station.SystemId, out SystemID))
                        Station.SystemId = SystemID;

                    // if there are missing system ids, try to get them
                    if ((Station.SystemId == 0) && (!String.IsNullOrEmpty(Station.SystemName)))
                    {
                        FoundSysRows = (dsEliteDB.tbsystemsRow[])Data.tbsystems.Select("systemname=" + DBConnector.SQLAString(DBConnector.DTEscape(Station.SystemName)));

                        if((FoundSysRows != null) && (FoundSysRows.Count() > 0))
                        {
                            // got it - set the id
                            Station.SystemId = FoundSysRows[0].id;
                        }
                    }

                    if (!String.IsNullOrEmpty(Station.Name.Trim()) && (Station.SystemId != 0))
                    {
                        // self-created stations don't have the correct id so they must be identified by name    
                        FoundRows = (dsEliteDB.tbstationsRow[])Data.tbstations.Select("stationname=" + DBConnector.SQLAString(DBConnector.DTEscape(Station.Name)) + " and " +
                                                                     "system_id =  " + Station.SystemId);

                        if ((FoundRows != null) && (FoundRows.Count() > 0))
                        {
                            // Location is existing, get the same Id
                            Station.Id = (Int32)FoundRows[0]["id"];

                            if (!OnlyAddUnknown)
                            {

                                if ((bool)(FoundRows[0]["is_changed"]))
                                {
                                    // existing data data is also changed by user - keep the newer version 
                                    Timestamp_old = (DateTime)(FoundRows[0]["updated_at"]);
                                    Timestamp_new = DateTimeOffset.FromUnixTimeSeconds(Station.UpdatedAt).DateTime;

                                    if (Timestamp_new > Timestamp_old)
                                    {
                                        // data from file is newer
                                        CopyEDStationToDataRow(Station, (DataRow)FoundRows[0], true, null, true);

                                        CopyEDStationEconomiesToDataRows(Station, Data.tbstationeconomy);
                                        // commodities are not considered because there was no possibility for input in the old RN

                                        ImportCounter += 1;
                                    }
//.........这里部分代码省略.........

        /// <summary>
        /// imports the data from the list of systems
        /// </summary>
        /// <param name="fileName"></param>
        public Dictionary<Int32, Int32> ImportSystems_Own(ref List<EDSystem> Systems, Boolean OnlyAddUnknown = false, Boolean setVisitedFlag = false)
        {
            DBConnector               lDBCon = null;
            String sqlString;
            dsEliteDB.tbsystemsRow[] FoundRows;
            DateTime Timestamp_new, Timestamp_old;
            Int32 ImportCounter = 0;
            Dictionary<Int32, Int32> changedSystemIDs = new Dictionary<Int32, Int32>();
            Int32 currentSelfCreatedIndex = -1;
            dsEliteDB Data;
            Int32 Counter = 0;

            

            try
            {
                lDBCon = new DBConnector(Program.DBCon.ConfigData, true);

                Data = new dsEliteDB();

                // gettin' some freaky performance
                lDBCon.Execute("set global innodb_flush_log_at_trx_commit=2");

                lDBCon.TransBegin();

                sqlString = "select * from tbSystems lock in share mode";
                lDBCon.TableRead(sqlString, Data.tbsystems);

                sqlString = "select * from tbSystems_org lock in share mode";
                lDBCon.TableRead(sqlString, Data.tbsystems_org);

                currentSelfCreatedIndex = getNextOwnSystemIndex();

                sendProgressEvent(new ProgressEventArgs() { Info="import self-added systems", CurrentValue=Counter, TotalValue=Systems.Count });

                foreach (EDSystem System in Systems)
                {
                    if (!String.IsNullOrEmpty(System.Name.ToString().Trim()))
                    {
                        // self-created systems don't have the correct id so it must be identified by name    
                        FoundRows = (dsEliteDB.tbsystemsRow[])Data.tbsystems.Select("systemname=" + DBConnector.SQLAString(DBConnector.DTEscape(System.Name.ToString())));

                        if ((FoundRows != null) && (FoundRows.Count() > 0))
                        {
                            if (!OnlyAddUnknown)
                            {
                                // system is existing
                                // memorize the changed system ids for importing user changed stations in the (recommend) second step
                                changedSystemIDs.Add(System.Id, (Int32)FoundRows[0]["id"]);
                                System.Id = (Int32)FoundRows[0]["id"];

                                if ((bool)(FoundRows[0]["is_changed"]))
                                {
                                    // old data is changed by user and the new data is also a user changed data
                                    // keep the newer version in the main table 
                                    Timestamp_old = (DateTime)(FoundRows[0]["updated_at"]);
                                    Timestamp_new = DateTimeOffset.FromUnixTimeSeconds(System.UpdatedAt).DateTime;

                                    if (Timestamp_new > Timestamp_old)
                                    {
                                        // data from file is newer -> take it but hold the old id
                                        CopyEDSystemToDataRow(System, (DataRow)FoundRows[0], true, null, true);

                                        ImportCounter += 1;
                                    }
                                }
                                else
                                {
                                    // new data is user changed data, old data is original data
                                    // copy the original data ("tbSystems") to the saving data table ("tbSystems_org")
                                    // and get the correct system ID
                                    Data.tbsystems_org.LoadDataRow(FoundRows[0].ItemArray, false);
                                    CopyEDSystemToDataRow(System, (DataRow)FoundRows[0], true, null, true);

                                    ImportCounter += 1;
                                }
                            }
                            else
                            {
                                System.Id = (Int32)FoundRows[0]["id"];
                            }
                        }
                        else
                        {
                            // add a new system
                            // memorize the changed system ids for importing user changed stations in the (recommend) second step
                            if (!OnlyAddUnknown)
                                changedSystemIDs.Add(System.Id, currentSelfCreatedIndex);

                            System.Id = currentSelfCreatedIndex;

                            dsEliteDB.tbsystemsRow newRow = (dsEliteDB.tbsystemsRow)Data.tbsystems.NewRow();
                            CopyEDSystemToDataRow(System, (DataRow)newRow, true, null, true);

                            newRow.visited      = setVisitedFlag;
                            newRow.updated_at   = DateTime.UtcNow;
//.........这里部分代码省略.........