private Boolean CheckConnection(out String channelName)
{
channelName = "";
try
{
SqlConnection dConn = GlobalFunctions.GetDBConnection();
String qry = "Select * from Channels";
GlobalFunctions.AppendEqualityQueryClause(ref qry, "User_Server");
GlobalFunctions.AppendEqualityQueryClause(ref qry, "Workbook_Name");
SqlCommand cmd = new SqlCommand(qry, dConn);
GlobalFunctions.AddSQLParameter(ref cmd, SqlDbType.VarChar, "@User_Server", System.Environment.MachineName);
GlobalFunctions.AddSQLParameter(ref cmd, SqlDbType.VarChar, "@Workbook_Name", GlobalFunctions.ActiveWBName);
//GlobalFunctions.AddSQLParameter(ref cmd, SqlDbType.VarChar, "@Workbook_Path", ActiveWB.Path);
DataTable ShareDT = GlobalFunctions.GetShareDBData(cmd, "Channels");
foreach (DataRow dr in ShareDT.Rows)
{
channelName = dr["Channel"].ToString();
channelID = dr["Channel_ID"].ToString();
Vars.UserID = "WORKBOOK_NAME:" + dr["Workbook_Name"].ToString() + ";USER_SERVER=" + dr["User_Server"].ToString() + ";ID:" + dr["ID"].ToString();
return(true);
}
return(false);
}
catch (Exception ex)
{
GlobalFunctions.ErrorLog(ex);
return(false);
}
}
private void CommitUpdates(CellUpdate oneCell)
{
SqlCommand cmd = GetSelectCommand();
GlobalFunctions.AddSQLParameter(ref cmd, SqlDbType.Variant, "@Channel_ID", channelID);
GlobalFunctions.AddSQLParameter(ref cmd, SqlDbType.Variant, "@Excel_Row", oneCell.Row);
GlobalFunctions.AddSQLParameter(ref cmd, SqlDbType.Variant, "@Excel_Column", oneCell.Col);
GlobalFunctions.AddSQLParameter(ref cmd, SqlDbType.Variant, "@Change_Type", oneCell.Type);
GlobalFunctions.AddSQLParameter(ref cmd, SqlDbType.Variant, "@Worksheet", oneCell.Worksheet);
using (SqlDataAdapter ShareDataAdapter = new SqlDataAdapter(cmd))
{
DataSet ShareDS = new DataSet();
ShareDataAdapter.Fill(ShareDS);
DataTable ShareDT = ShareDS.Tables[0];
DataColumn[] keyColumns = new DataColumn[4];
keyColumns[0] = ShareDT.Columns["Channel_ID"];
keyColumns[1] = ShareDT.Columns["Worksheet"];
keyColumns[2] = ShareDT.Columns["Excel_Row"];
keyColumns[3] = ShareDT.Columns["Excel_Column"];
ShareDT.PrimaryKey = keyColumns;
SqlCommandBuilder cb = new SqlCommandBuilder(ShareDataAdapter);
DataRow dr = ShareDT.Rows.Find(new object[] { channelID, oneCell.Worksheet, oneCell.Row, oneCell.Col });
if (dr == null)
{
//if (oneCell.val == "")
//{
// // ShareDataAdapter.DeleteCommand = cb.GetDeleteCommand();
// ShareDataAdapter.InsertCommand = GetInsertCommand(cb);
//}
//else
//{
//
//}
ShareDataAdapter.InsertCommand = GetInsertCommand(cb);
Insert(ShareDT, oneCell);
ShareDataAdapter.Update(ShareDT);
GlobalFunctions.InfoLog("Insert", oneCell);
}
else
{
String changeID = dr["ID"].ToString();
Update(changeID, oneCell);
GlobalFunctions.InfoLog("Update", oneCell);
}
}
SpeedUpRefresh();
}
private List <CellUpdate> GetDBCells()
{
try
{
SqlCommand cmd = GetOthersChangesSelect();
GlobalFunctions.AddSQLParameter(ref cmd, SqlDbType.DateTime2, "@Change_Time", Vars.LatestUpdateTime.ToShortDateString() + " " + Vars.LatestUpdateTime.TimeOfDay.ToString());
GlobalFunctions.AddSQLParameter(ref cmd, SqlDbType.VarChar, "@channel_id", channelID);
GlobalFunctions.AddSQLParameter(ref cmd, SqlDbType.VarChar, "@Change_Author", Vars.SessionID);
DataTable ShareDT = GlobalFunctions.GetShareDBData(cmd, "Changes");
return(ConvertToList(ShareDT));
}
catch (Exception ex)
{
GlobalFunctions.ErrorLog(ex);
throw;
}
}
private void RemoveConnection()
{
if (Globals.ThisAddIn.channelID == "")
{
return;
}
String qry = "Select * from channels";
SqlConnection dConn = GlobalFunctions.GetDBConnection();
SqlCommand cmd = new SqlCommand(qry, dConn);
String ActiveWBName = GlobalFunctions.ActiveWBName;
GlobalFunctions.AddSQLParameter(ref cmd, SqlDbType.Variant, "@Channel_ID", Globals.ThisAddIn.channelID);
GlobalFunctions.AddSQLParameter(ref cmd, SqlDbType.Variant, "@Workbook_Name", ActiveWBName);
GlobalFunctions.AddSQLParameter(ref cmd, SqlDbType.Variant, "@User_Server", System.Environment.MachineName);
using (SqlDataAdapter ShareDataAdapter = new SqlDataAdapter(cmd))
{
DataSet ShareDS = new DataSet();
ShareDataAdapter.Fill(ShareDS, "Channels");
DataTable ShareDT = ShareDS.Tables["Channels"];
DataColumn[] keyColumns = new DataColumn[3];
keyColumns[0] = ShareDT.Columns["Channel_ID"];
keyColumns[1] = ShareDT.Columns["Workbook_Name"];
keyColumns[2] = ShareDT.Columns["User_Server"];
ShareDT.PrimaryKey = keyColumns;
SqlCommandBuilder cb = new SqlCommandBuilder(ShareDataAdapter);
DataRow dr = ShareDT.Rows.Find(new object[] { Globals.ThisAddIn.channelID, ActiveWBName, System.Environment.MachineName });
if (dr != null)
{
ShareDataAdapter.DeleteCommand = cb.GetDeleteCommand();
dr.Delete();
ShareDataAdapter.Update(ShareDT);
}
}
}