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 SqlCommand GetUpdateCommand() { if (UpdateCommand == null) { String UpdateQry = "update Changes set Cell_Value = @cell_value, Change_Author = @change_author, Change_Time = @change_time where ID = @ID"; SqlConnection dConn = GlobalFunctions.GetDBConnection(); UpdateCommand = new SqlCommand(UpdateQry, dConn); } UpdateCommand.Parameters.Clear(); return(UpdateCommand); }
public DateTime getDbTime() { var connection = GlobalFunctions.GetDBConnection(); connection.Open(); SqlCommand Comm = new SqlCommand("SELECT SYSDATETIME();", connection); string dr = Comm.ExecuteScalar().ToString(); DateTime dbtime = Convert.ToDateTime(dr); connection.Close(); return(dbtime.AddHours(-5)); }
private SqlCommand GetOthersChangesSelect() { if (OthersSelectCommand == null) { String qry = "Select * from Changes"; GlobalFunctions.AppendOperatorClause(ref qry, "Change_Time", ">="); GlobalFunctions.AppendOperatorClause(ref qry, "Change_Author", "<>"); GlobalFunctions.AppendEqualityQueryClause(ref qry, "channel_id"); qry += " order by change_time ASC"; SqlConnection dConn = GlobalFunctions.GetDBConnection(); OthersSelectCommand = new SqlCommand(qry, dConn); } OthersSelectCommand.Parameters.Clear(); return(OthersSelectCommand); }
/* CHANGE TABLE COMMANDS */ private SqlCommand GetSelectCommand() { if (SelectCommand == null || true) { SqlConnection dConn = GlobalFunctions.GetDBConnection(); String qry = "Select * from Changes"; GlobalFunctions.AppendEqualityQueryClause(ref qry, "Channel_ID"); GlobalFunctions.AppendEqualityQueryClause(ref qry, "Excel_Row"); GlobalFunctions.AppendEqualityQueryClause(ref qry, "Excel_Column"); GlobalFunctions.AppendEqualityQueryClause(ref qry, "Change_Type"); GlobalFunctions.AppendEqualityQueryClause(ref qry, "Worksheet"); SelectCommand = new SqlCommand(qry, dConn); } SelectCommand.Parameters.Clear(); return(SelectCommand); }
private String getChannelID(String ChannelName) { try { SqlConnection dConn = GlobalFunctions.GetDBConnection(); String qry = "Select * from Channels Order By Channel_ID asc"; SqlCommand cmd = new SqlCommand(qry, dConn); DataTable ShareDT = GlobalFunctions.GetShareDBData(cmd, "Channels"); Int32 MaxChan = 0; if (ShareDT.Rows.Count > 0) { foreach (DataRow dr in ShareDT.Rows) { String ChannelID = dr["Channel_ID"].ToString(); String rChannelName = dr["Channel"].ToString(); MaxChan = Math.Max(1, Int32.Parse(ChannelID)); if (rChannelName == ChannelName) { return(ChannelID); } } return((MaxChan + 1).ToString()); } else { return(""); } } 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); } } }
private Boolean CreateConnection() { if (editBoxChannel.Text == "") { return(false); } String ChannelID = getChannelID(editBoxChannel.Text); if (ChannelID == "") { ChannelID = "1"; } String qry = "Select * from channels"; SqlConnection dConn = GlobalFunctions.GetDBConnection(); SqlCommand cmd = new SqlCommand(qry, dConn); using (SqlDataAdapter ShareDataAdapter = new SqlDataAdapter(cmd)) { DataSet ShareDS = new DataSet(); ShareDataAdapter.Fill(ShareDS, "Channels"); DataTable ShareDT = ShareDS.Tables["Channels"]; SqlCommandBuilder cb = new SqlCommandBuilder(ShareDataAdapter); ShareDataAdapter.InsertCommand = cb.GetInsertCommand(); DataRow dr = ShareDT.NewRow(); dr["Channel"] = editBoxChannel.Text; dr["Channel_ID"] = ChannelID; dr["Workbook_Name"] = GlobalFunctions.ActiveWBName; dr["User_Server"] = System.Environment.MachineName; //dr["Workbook_Path"] = ActiveWB.Path; ShareDT.Rows.Add(dr); ShareDataAdapter.Update(ShareDT); } return(true); }