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