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