Example #1
0
 public static string getTableIDColumnName(string TABLE_NAME)
 {
     try
     {
         SettingsConfiguration1 SettingsConf = new SettingsConfiguration1();;
         SqlConnection          con          = new SqlConnection(string.Format("server ={0}; Initial Catalog = {1}; Connect Timeout = 10; User ID = {2}; Password ={3}", SettingsConf.local_server_name, SettingsConf.local_server_database_name, SettingsConf.local_server_user_name, SettingsConf.local_server_password));
         con.Open();
         SqlCommand cmd = new SqlCommand("SELECT TOP(1) ID_COLUMN FROM S_TABLES WHERE TABLE_NAME = '" + TABLE_NAME + "'", con);
         ;
         return(cmd.ExecuteScalar().ToString());
     }
     catch (Exception ex)
     {
         Log.set("SQL.getTableIDColumnName('" + TABLE_NAME + "'):" + ex.ToString());
     }
     return("");
 }
Example #2
0
        public static int getTableLastID(string TABLE_NAME)
        {
            try
            {
                SettingsConfiguration1 SettingsConf = new SettingsConfiguration1();;
                SqlConnection          con          = new SqlConnection(string.Format("server ={0}; Initial Catalog = {1}; Connect Timeout = 10; User ID = {2}; Password ={3}", SettingsConf.local_server_name, SettingsConf.local_server_database_name, SettingsConf.local_server_user_name, SettingsConf.local_server_password));
                con.Open();
                string IDCOLUMNNAME = getTableIDColumnName(TABLE_NAME);
                string sql          = "if exists(SELECT TOP(1) " + IDCOLUMNNAME + " FROM " + TABLE_NAME + " ORDER BY " + IDCOLUMNNAME + " DESC) " +
                                      "SELECT TOP(1) " + IDCOLUMNNAME + " FROM " + TABLE_NAME + " ORDER BY " + IDCOLUMNNAME + " DESC " +
                                      "ELSE SELECT 0";
                //Log.deneme(sql);
                SqlCommand cmd = new SqlCommand(sql, con);


                return((int)(cmd.ExecuteScalar()));
            }
            catch (Exception ex)
            {
                Log.set("SQL.getTableLastID('" + TABLE_NAME + "'):" + ex.ToString());
            }
            return(0);
        }
Example #3
0
        public static bool updateSTLINE()
        {
            try
            {
                SettingsConfiguration1 SettingsConf = new SettingsConfiguration1();;
                string donem             = convertToDonem(SettingsConf.tiger_donem);
                string firmNum           = convertToFirm(SettingsConf.tiger_firma_num);
                string TABLE_NAME        = "LG_" + firmNum + "_" + donem + "_STLINE";
                int    lastIDDestinition = getTableLastID(TABLE_NAME);
                string T2        = "LG_" + firmNum + "_" + donem + "_STFICHE";
                string sourceCon = string.Format("server ={0}; Initial Catalog = {1}; User ID = {2}; Password ={3}", SettingsConf.main_server_name, getTableSourceDatabaseName(TABLE_NAME), SettingsConf.main_server_user_name, SettingsConf.main_server_password);

                string destinationCon = string.Format("server ={0}; Initial Catalog = {1}; User ID = {2}; Password ={3}", SettingsConf.local_server_name, SettingsConf.local_server_database_name, SettingsConf.local_server_user_name, SettingsConf.local_server_password);

                string   tarih          = getTableLastUpdatedTime(TABLE_NAME);
                DateTime lastUpdateTime = Convert.ToDateTime(tarih == "" ? DateTime.Now.ToString() : tarih);

                string lastUpdateTimeColumnName = getTableLastUpdatedTimeColumnName(TABLE_NAME);

                using (SqlConnection sourceConnection = new SqlConnection(sourceCon))
                {
                    if (sourceConnection.State == ConnectionState.Closed)
                    {
                        sourceConnection.Open();
                    }
                    SqlCommand commandSourceData = new SqlCommand("SELECT T1.* FROM " + TABLE_NAME + " AS T1 " +
                                                                  "LEFT JOIN " + T2 + " AS T2 ON T2.LOGICALREF = T1.STFICHEREF WHERE T2.CAPIBLOCK_MODIFIEDDATE > '" + lastUpdateTime.ToString("yyyy-MM-dd HH:mm:ss") + "' AND T1.LOGICALREF <= " + lastIDDestinition + ";", sourceConnection);

                    SqlDataReader reader = commandSourceData.ExecuteReader();
                    using (SqlConnection destinationConnection = new SqlConnection(destinationCon))
                    {
                        if (destinationConnection.State == ConnectionState.Closed)
                        {
                            destinationConnection.Open();
                        }
                        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection))
                        {
                            bulkCopy.DestinationTableName = TABLE_NAME;
                            bulkCopy.BulkCopyTimeout      = 0;
                            try
                            {
                                using (SqlCommand command = destinationConnection.CreateCommand())
                                {
                                    command.CommandText = "SELECT name FROM sys.columns WHERE object_id = OBJECT_ID('" + TABLE_NAME + "')";
                                    using (var readerColumnName = command.ExecuteReader())
                                    {
                                        string COL = "";
                                        while (readerColumnName.Read())
                                        {
                                            COL = readerColumnName.GetString(0);
                                            if (COL != "SYNC_ID")
                                            {
                                                bulkCopy.ColumnMappings.Add(COL, COL);
                                            }
                                        }
                                        readerColumnName.Close();
                                    }
                                }
                                bulkCopy.WriteToServer(reader);
                                string sqlDuble = "DELETE ASD FROM (SELECT *, ROW_NUMBER() OVER(PARTITION BY LOGICALREF  ORDER BY SYNC_ID DESC) RANK FROM " + TABLE_NAME + ") ASD WHERE RANK > 1; " +
                                                  "UPDATE S_TABLES SET LAST_UPDATE_TIME = GETDATE() WHERE TABLE_NAME = '" + TABLE_NAME + "';" +
                                                  "INSERT INTO S_SUCCESS(TABLE_NAME,FUNCTION_,TIME_) VALUES ('" + TABLE_NAME + "', 'UPDATED', GETDATE());";
                                commandSourceData = new SqlCommand(sqlDuble, destinationConnection);
                                commandSourceData.ExecuteScalar();

                                return(true);
                            }
                            catch (Exception ex)
                            {
                                Log.set("SQL.updateSTLINE('" + TABLE_NAME + "'):" + ex.ToString());
                                return(false);
                            }
                            finally
                            {
                                reader.Close();
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Log.set("SQL.updateSTLINE():" + ex.ToString());
                return(false);
            }

            return(false);
        }
Example #4
0
        public static bool updatedData(string TABLE_NAME, int saat = 5)
        {
            try
            {
                SettingsConfiguration1 SettingsConf = new SettingsConfiguration1();;
                string destinationCon = string.Format("server ={0}; Initial Catalog = {1}; User ID = {2}; Password ={3}", SettingsConf.local_server_name, SettingsConf.local_server_database_name, SettingsConf.local_server_user_name, SettingsConf.local_server_password);
                // OZEL TABLO UPDATE METHODLARI
                if (checkOzelColumns(TABLE_NAME))
                {
                    if (TABLE_NAME.ToUpper().IndexOf("STLINE") != -1)
                    {
                        return(updateSTLINE());
                    }
                }
                // OZEL TABLO DAL
                else
                {
                    string   tarih          = getTableLastUpdatedTime(TABLE_NAME);
                    DateTime lastUpdateTime = Convert.ToDateTime(tarih == "" ? DateTime.Now.ToString() : tarih).AddHours(saat * -1);

                    string lastUpdateTimeColumnName = getTableLastUpdatedTimeColumnName(TABLE_NAME);
                    // last update time column yok
                    if (lastUpdateTimeColumnName == "")
                    {
                        using (SqlConnection destinationConnection = new SqlConnection(destinationCon))
                        {
                            try
                            {
                                destinationConnection.Open();
                            }
                            catch (Exception ex)
                            {
                                Log.set("updatedData(" + TABLE_NAME + ") can't connected destinationConnection:" + ex.ToString());
                                return(false);
                            }

                            if (insertedData(TABLE_NAME))
                            {
                                SqlCommand commandSourceData = new SqlCommand("UPDATE S_TABLES SET LAST_UPDATE_TIME = GETDATE() WHERE TABLE_NAME = '" + TABLE_NAME + "'; " +
                                                                              "INSERT INTO S_SUCCESS(TABLE_NAME,FUNCTION_,TIME_) VALUES ('" + TABLE_NAME + "', 'UPDATED', GETDATE());", destinationConnection);
                                commandSourceData.ExecuteScalar();
                                return(true);
                            }
                            else
                            {
                                return(false);
                            }
                        }
                    }
                    // last update time column bar
                    else
                    {
                        try
                        {
                            string idcolumn  = getTableIDColumnName(TABLE_NAME);
                            int    lastId    = getTableLastID(TABLE_NAME);
                            string sourceCon = string.Format("server ={0}; Initial Catalog = {1}; User ID = {2}; Password ={3}", SettingsConf.main_server_name, getTableSourceDatabaseName(TABLE_NAME), SettingsConf.main_server_user_name, SettingsConf.main_server_password);

                            destinationCon = string.Format("server ={0}; Initial Catalog = {1}; User ID = {2}; Password ={3}", SettingsConf.local_server_name, SettingsConf.local_server_database_name, SettingsConf.local_server_user_name, SettingsConf.local_server_password);

                            using (SqlConnection sourceConnection = new SqlConnection(sourceCon))
                            {
                                if (sourceConnection.State == ConnectionState.Closed)
                                {
                                    try
                                    {
                                        sourceConnection.Open();
                                    }
                                    catch (Exception ex)
                                    {
                                        Log.set("updatedData(" + TABLE_NAME + ") can't connected sourceConnection:" + ex.ToString());
                                        return(false);
                                    }
                                }

                                SqlCommand commandSourceData = new SqlCommand("SELECT * FROM " + TABLE_NAME + " WHERE " + lastUpdateTimeColumnName + " > '" + lastUpdateTime.ToString("yyyy-MM-dd HH:mm:ss") + "' AND " + idcolumn + " <= " + lastId + " ;", sourceConnection);

                                SqlDataReader reader = commandSourceData.ExecuteReader();
                                using (SqlConnection destinationConnection = new SqlConnection(destinationCon))
                                {
                                    if (destinationConnection.State == ConnectionState.Closed)
                                    {
                                        try
                                        {
                                            destinationConnection.Open();
                                        }
                                        catch (Exception ex)
                                        {
                                            Log.set("updatedData(" + TABLE_NAME + ") can't connected destinationConnection:" + ex.ToString());
                                            return(false);
                                        }
                                    }
                                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection))
                                    {
                                        bulkCopy.DestinationTableName = TABLE_NAME;
                                        bulkCopy.BulkCopyTimeout      = 0;
                                        try
                                        {
                                            using (SqlCommand command = destinationConnection.CreateCommand())
                                            {
                                                command.CommandText = "SELECT name FROM sys.columns WHERE object_id = OBJECT_ID('" + TABLE_NAME + "')";
                                                using (var readerColumnName = command.ExecuteReader())
                                                {
                                                    string COL = "";
                                                    while (readerColumnName.Read())
                                                    {
                                                        COL = readerColumnName.GetString(0);
                                                        if (COL != "SYNC_ID")
                                                        {
                                                            bulkCopy.ColumnMappings.Add(COL, COL);
                                                        }
                                                    }
                                                    readerColumnName.Close();
                                                }
                                            }
                                            bulkCopy.WriteToServer(reader);
                                            string sqlDube = "DELETE ASD FROM (SELECT *, ROW_NUMBER() OVER(PARTITION BY " + getTableIDColumnName(TABLE_NAME) + "  ORDER BY " + lastUpdateTimeColumnName + " DESC) RANK FROM " + TABLE_NAME + ") ASD WHERE RANK > 1;" +
                                                             "UPDATE S_TABLES SET LAST_UPDATE_TIME = GETDATE() WHERE TABLE_NAME = '" + TABLE_NAME + "';" +
                                                             "INSERT INTO S_SUCCESS(TABLE_NAME,FUNCTION_,TIME_) VALUES ('" + TABLE_NAME + "', 'UPDATED', GETDATE());";

                                            commandSourceData = new SqlCommand(sqlDube, destinationConnection);
                                            commandSourceData.ExecuteScalar();
                                            return(true);
                                        }
                                        catch (Exception ex)
                                        {
                                            Log.set("SQL.updatedData('" + TABLE_NAME + "'):" + ex.ToString());
                                            return(false);
                                        }
                                        finally
                                        {
                                            reader.Close();
                                        }
                                    }
                                }
                            }
                        }
                        catch (Exception ex)
                        {
                            Log.set("SQL.updatedData('" + TABLE_NAME + "'):" + ex.ToString());
                            return(false);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Log.set("SQL.updatedData('" + TABLE_NAME + "'):" + ex.ToString());
            }
            return(false);
        }
Example #5
0
        public static bool deleteData(string TABLE_NAME)
        {
            try
            {
                int    sourceCount       = 0;
                int    destinationCount  = 0;
                int    lastIDDestinition = getTableLastID(TABLE_NAME);
                string IDColumnName      = getTableIDColumnName(TABLE_NAME);

                SettingsConfiguration1 SettingsConf = new SettingsConfiguration1();;

                string sourceCon = string.Format("server ={0}; Initial Catalog = {1}; User ID = {2}; Password ={3}", SettingsConf.main_server_name, getTableSourceDatabaseName(TABLE_NAME), SettingsConf.main_server_user_name, SettingsConf.main_server_password);

                string destinationCon = string.Format("server ={0}; Initial Catalog = {1}; User ID = {2}; Password ={3}", SettingsConf.local_server_name, SettingsConf.local_server_database_name, SettingsConf.local_server_user_name, SettingsConf.local_server_password);

                SqlConnection destinationConection = new SqlConnection(destinationCon);
                SqlConnection sourceConection      = new SqlConnection(sourceCon);

                if (destinationConection.State == ConnectionState.Closed)
                {
                    try
                    {
                        destinationConection.Open();
                    }
                    catch (Exception)
                    {
                        return(false);
                    }
                }
                SqlCommand cmd = new SqlCommand("SELECT TOP(1) MIN_LEVEL FROM S_TABLES WHERE TABLE_NAME = '" + TABLE_NAME + "'", destinationConection);
                ;

                int min_level = (int)(cmd.ExecuteScalar());
                if (sourceConection.State == ConnectionState.Closed)
                {
                    try
                    {
                        sourceConection.Open();
                    }
                    catch (Exception)
                    {
                        return(false);
                    }
                }

                updatedData(TABLE_NAME);

                check(0, lastIDDestinition);

                void check(int startID, int endID)
                {
                    // Log.deneme("check edilyar(start:" + startID.ToString()+ ", end:"+ endID.ToString()+")");
                    SqlCommand destinationCountComment = new SqlCommand("SELECT COUNT(*) FROM " + TABLE_NAME + " WHERE " + IDColumnName + " >= " + startID.ToString() + " AND " + IDColumnName + " <= " + endID.ToString(), destinationConection);

                    SqlCommand sourceCountComment = new SqlCommand("SELECT COUNT(*) FROM " + TABLE_NAME + " WHERE " + IDColumnName + " >= " + startID.ToString() + " AND " + IDColumnName + " <= " + endID.ToString(), sourceConection);

                    destinationCount = (int)(destinationCountComment.ExecuteScalar());
                    sourceCount      = (int)(sourceCountComment.ExecuteScalar());
                    //MessageBox.Show("check(start:" + startID.ToString()+",end:"+ endID.ToString()+") = destinictionCount:"+ destinationCount.ToString()+ ", sourceCount:" + sourceCount.ToString());

                    ;
                    if (destinationCount != sourceCount)
                    {
                        if (Math.Abs(endID - startID) <= min_level)
                        {
                            updateFinded(startID, endID);
                            check(0, lastIDDestinition);
                        }
                        else
                        {
                            check((int)((endID - startID) / 2 + startID), endID);
                            check(startID, (int)((endID - startID) / 2 + startID));
                        }
                    }
                }

                void updateFinded(int startID, int endID)
                {
                    //MessageBox.Show("UPDATEfinded(" + startID.ToString() + "," + endID.ToString() + ")");
                    using (SqlConnection sourceConnection = new SqlConnection(sourceCon))
                    {
                        if (sourceConnection.State == ConnectionState.Closed)
                        {
                            sourceConnection.Open();
                        }

                        SqlCommand commandSourceData = new SqlCommand("SELECT * FROM " + TABLE_NAME + " WHERE " + IDColumnName + " >= " + startID.ToString() + " AND " + IDColumnName + " <= " + endID.ToString(), sourceConnection);

                        SqlDataReader reader = commandSourceData.ExecuteReader();
                        using (SqlConnection destinationConnection = new SqlConnection(destinationCon))
                        {
                            if (destinationConnection.State == ConnectionState.Closed)
                            {
                                destinationConnection.Open();
                            }
                            using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection))
                            {
                                bulkCopy.DestinationTableName = TABLE_NAME;
                                bulkCopy.BulkCopyTimeout      = 0;

                                using (SqlCommand command = destinationConnection.CreateCommand())
                                {
                                    // GELJEKKI MAGLUMATLARY ONUNDEN BAR BOLSA POZMALY;
                                    commandSourceData = new SqlCommand("DELETE FROM " + TABLE_NAME + " WHERE " + IDColumnName + " >= " + startID.ToString() + " AND " + IDColumnName + " <= " + endID.ToString(), destinationConnection);
                                    commandSourceData.ExecuteScalar();

                                    command.CommandText = "SELECT name FROM sys.columns WHERE object_id = OBJECT_ID('" + TABLE_NAME + "')";
                                    using (var readerColumnName = command.ExecuteReader())
                                    {
                                        string COL = "";
                                        while (readerColumnName.Read())
                                        {
                                            COL = readerColumnName.GetString(0);
                                            if (COL != "SYNC_ID")
                                            {
                                                bulkCopy.ColumnMappings.Add(COL, COL);
                                            }
                                        }
                                        ;
                                        readerColumnName.Close();
                                    }
                                }
                                bulkCopy.WriteToServer(reader);
                            }
                        }
                    }
                }
                try
                {
                    SqlConnection destinationConnection = new SqlConnection(destinationCon);
                    if (destinationConnection.State == ConnectionState.Closed)
                    {
                        destinationConnection.Open();
                    }

                    SqlCommand commandSourceData = new SqlCommand("UPDATE S_TABLES SET LAST_DELETE_TIME = GETDATE() WHERE TABLE_NAME = '" + TABLE_NAME + "'; " +
                                                                  "INSERT INTO S_SUCCESS(TABLE_NAME,FUNCTION_,TIME_) VALUES ('" + TABLE_NAME + "', 'DELETE', GETDATE());", destinationConnection);
                    commandSourceData.ExecuteScalar();
                }
                catch (Exception ex)
                {
                    Log.set("SQL.deleteData('" + TABLE_NAME + "'):" + ex.ToString());
                    return(false);
                }
            }
            catch (Exception ex)
            {
                Log.set("deleteData(" + TABLE_NAME + "):" + ex.ToString());
                return(false);
            }
            return(true);
        }
Example #6
0
        public static bool insertedData(string TABLE_NAME)
        {
            try
            {
                SettingsConfiguration1 SettingsConf = new SettingsConfiguration1();;

                int    lastID    = getTableLastID(TABLE_NAME);
                string ID_COLUMN = getTableIDColumnName(TABLE_NAME);


                string sourceCon = string.Format("server ={0}; Initial Catalog = {1}; User ID = {2}; Password ={3}", SettingsConf.main_server_name, getTableSourceDatabaseName(TABLE_NAME), SettingsConf.main_server_user_name, SettingsConf.main_server_password);

                string destinationCon = string.Format("server ={0}; Initial Catalog = {1}; User ID = {2}; Password ={3}", SettingsConf.local_server_name, SettingsConf.local_server_database_name, SettingsConf.local_server_user_name, SettingsConf.local_server_password);

                using (SqlConnection sourceConnection = new SqlConnection(sourceCon))
                {
                    if (sourceConnection.State == ConnectionState.Closed)
                    {
                        try
                        {
                            sourceConnection.Open();
                        }
                        catch (Exception ex)
                        {
                            Log.set("insertedData(" + TABLE_NAME + ") can't connected sourceConnection:" + ex.ToString());
                            return(false);
                        }
                    }
                    string sql = "";
                    if (ID_COLUMN != "")
                    {
                        sql = "SELECT * FROM " + TABLE_NAME + " WHERE " + ID_COLUMN + " > " + lastID + ";";
                    }
                    else
                    {
                        sql = "SELECT * FROM " + TABLE_NAME + ";";
                        if (TABLE_NAME.IndexOf("GNTOTST") != -1)
                        {
                            sql = "SELECT [STOCKREF], [INVENNO], [ONHAND] FROM " + TABLE_NAME + " WHERE ONHAND <> 0;";
                        }
                        if (TABLE_NAME.IndexOf("MARKET") != -1)
                        {
                            sql = "SELECT * FROM " + TABLE_NAME + " WHERE SUBSGOODCODE <> '';";
                        }
                    }
                    SqlCommand    commandSourceData = new SqlCommand(sql, sourceConnection);
                    SqlDataReader reader            = commandSourceData.ExecuteReader();
                    using (SqlConnection destinationConnection = new SqlConnection(destinationCon))
                    {
                        if (destinationConnection.State == ConnectionState.Closed)
                        {
                            try
                            {
                                destinationConnection.Open();
                            }
                            catch (Exception ex)
                            {
                                Log.set("insertedData(" + TABLE_NAME + ") can't connected destinationConnection:" + ex.ToString());
                                return(false);
                            }
                        }


                        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection))
                        {
                            bulkCopy.DestinationTableName = TABLE_NAME;
                            bulkCopy.BulkCopyTimeout      = 0;
                            try
                            {
                                using (SqlCommand command = destinationConnection.CreateCommand())
                                {
                                    command.CommandText = "SELECT name FROM sys.columns WHERE object_id = OBJECT_ID('" + TABLE_NAME + "')";
                                    using (var readerColumnName = command.ExecuteReader())
                                    {
                                        string COL = "";
                                        while (readerColumnName.Read())
                                        {
                                            COL = readerColumnName.GetString(0);
                                            if (COL != "SYNC_ID")
                                            {
                                                bulkCopy.ColumnMappings.Add(COL, COL);
                                            }
                                        }
                                        readerColumnName.Close();
                                    }
                                }
                                if (ID_COLUMN == "")
                                {
                                    SqlCommand truntable = new SqlCommand("TRUNCATE TABLE " + TABLE_NAME, destinationConnection);
                                    truntable.ExecuteScalar();
                                }
                                bulkCopy.WriteToServer(reader);

                                commandSourceData = new SqlCommand("UPDATE S_TABLES SET LAST_INSERT_TIME = GETDATE() WHERE TABLE_NAME = '" + TABLE_NAME + "'; INSERT INTO S_SUCCESS(TABLE_NAME,FUNCTION_,TIME_) VALUES ('" + TABLE_NAME + "', 'INSERT', GETDATE());", destinationConnection);
                                commandSourceData.ExecuteScalar();
                                return(true);
                            }
                            catch (Exception ex)
                            {
                                Log.set("SQL.insertedData('" + TABLE_NAME + "'):" + ex.ToString());
                                return(false);
                            }
                            finally
                            {
                                reader.Close();
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Log.set("SQL.insertedData('" + TABLE_NAME + "'):" + ex.ToString());
                return(false);
            }
            return(false);
        }