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("");
}
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);
}
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);
}
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);
}
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);
}
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);
}