private void JoinButtonLogic(string JoinType)
{
// fill the column textboxes with column info
saveJoinType = JoinType;
LeftSideJoinTab = lbJoined.CheckedItems[0].ToString();
RightSideJoinTab = lbUnjoined.CheckedItems[0].ToString();
queryTabInfo LeftTabInfo = getQTinfoFor(LeftSideJoinTab);
List <string> LeftTabCols = LeftTabInfo.showColumns;
lblJoinedTab.Text = LeftSideJoinTab;
lblUnjoinedTab.Text = RightSideJoinTab;
clbLeftCols.Items.Clear();
foreach (string OneCol in LeftTabCols)
{
clbLeftCols.Items.Add(OneCol);
}
TabThatNeedsCols = lbUnjoined.CheckedItems[0];
myCC = GetTheColumns();
clbRightCols.Items.Clear();
foreach (object OneCol in myCC.lbColumnLister.Items)
{
clbRightCols.Items.Add(OneCol);
}
// auto check columns with same names
this.gbColsJoin.Visible = true;
this.Refresh();
Application.DoEvents();
}
private void BuildSQLstring()
{
builtSQLquery = "";
bsqSelectClause = "SELECT ";
bsqFromClause = "\r\nFROM ";
bsqExtrasClause = "\r\nWHERE (<add your criteria here>)\r\n-- ORDER BY <optionally add sort fields>";
if (getQO.btnAlias.Checked)
{
BuildTableAliases();
}
if (getQO.btnDistinct.Checked)
{
bsqSelectClause += "DISTINCT ";
}
if (getQO.btnTop20.Checked)
{
bsqSelectClause += "TOP(20) ";
}
foreach (queryTabInfo oneTabInfo in queryParms)
{
foreach (string OneField in oneTabInfo.showColumns)
{
bsqSelectClause += GetFieldName(oneTabInfo.TableName, oneTabInfo.Alias, OneField) + ", ";
}
if (oneTabInfo.JoinType != null)
{
bsqFromClause += oneTabInfo.JoinType.ToUpper() + " ";
}
bsqFromClause += oneTabInfo.TableName + " ";
if (oneTabInfo.Alias != "")
{
bsqFromClause += "AS " + oneTabInfo.Alias + " ";
}
if (getQO.btnNolock.Checked)
{
bsqFromClause += "WITH(NOLOCK) ";
}
if (oneTabInfo.JoinType != null)
{
bsqFromClause += "ON ";
for (int i = 0; i < oneTabInfo.MyJoinColumns.Count; i++)
{
bsqFromClause += GetFieldName(oneTabInfo.TableName, oneTabInfo.Alias, oneTabInfo.MyJoinColumns[i]);
bsqFromClause += " = ";
queryTabInfo ToTabInfo = getQTinfoFor(oneTabInfo.JoiningTo);
string toJoinAlias = ToTabInfo.Alias;
bsqFromClause += GetFieldName(oneTabInfo.JoiningTo, toJoinAlias, oneTabInfo.ToJoinColumns[i]) + " AND ";
}
bsqFromClause = bsqFromClause.Substring(0, bsqFromClause.Length - 4);
}
}
bsqSelectClause = bsqSelectClause.Substring(0, bsqSelectClause.Length - 2);
builtSQLquery = bsqSelectClause + bsqFromClause + bsqExtrasClause;
}
private void btnDoneJoinCs_Click(object sender, EventArgs e)
{
this.gbColsJoin.Visible = false;
if (!GetColsToShowRight(myCC))
{
return;
}
Application.DoEvents();
// store info to internal struct list
var myqTinfo = new queryTabInfo();
myqTinfo.TableName = TabThatNeedsCols.ToString();
myqTinfo.showColumns = GetReturnedColumns();
myqTinfo.JoinType = saveJoinType;
myqTinfo.JoiningTo = lbJoined.CheckedItems[0].ToString();
myqTinfo.MyJoinColumns = new List <string>();
myqTinfo.ToJoinColumns = new List <string>();
foreach (object OneCol in clbLeftCols.CheckedItems)
{
myqTinfo.MyJoinColumns.Add(OneCol.ToString());
}
foreach (object OneCol in clbRightCols.CheckedItems)
{
myqTinfo.ToJoinColumns.Add(OneCol.ToString());
}
queryParms.Add(myqTinfo);
// move table over from unjoined to joined list and clear checkboxes
lbUnjoined.Items.Remove(lbUnjoined.CheckedItems[0]);
lbJoined.Items.Add(TabThatNeedsCols.ToString());
lbJoined.ClearSelected();
lbUnjoined.ClearSelected();
while (lbJoined.CheckedIndices.Count > 0)
{
lbJoined.SetItemChecked(lbJoined.CheckedIndices[0], false);
}
AdjustJoinButtons();
this.Show();
}
private void btnEdit_Click(object sender, EventArgs e)
{
TabThatNeedsCols = lbJoined.CheckedItems[0];
queryTabInfo myTabInfo = getQTinfoFor(TabThatNeedsCols.ToString());
List <string> alreadyChecked = myTabInfo.showColumns;
myCC = GetTheColumns(alreadyChecked);
Application.DoEvents();
myCC.ShowDialog();
if (GetReturnedColumns().Count == 0)
{
// what happens if they deselect all the columns?
}
myTabInfo.showColumns = GetReturnedColumns();
queryParms[qpIndex] = myTabInfo;
myCC.Close();
}
public void JFinitialize()
{
frmTabDispParent.Hide();
this.Top = frmTabDispParent.PlaceForms.topForm;
this.Left = frmTabDispParent.PlaceForms.LeftForm;
Application.DoEvents();
bool firstObj = true;
foreach (object OneTab in passedJoinedTabs)
{
if (firstObj)
{
lbJoined.Items.Add(OneTab);
TabThatNeedsCols = OneTab;
firstObj = false;
}
else
{
lbUnjoined.Items.Add(OneTab);
}
}
myCC = GetTheColumns();
Application.DoEvents();
myCC.ShowDialog();
if (GetReturnedColumns().Count == 0)
{
// what happens if they didn't select any columns the first time?
}
var myqTinfo = new queryTabInfo();
myqTinfo.TableName = TabThatNeedsCols.ToString();
myqTinfo.showColumns = GetReturnedColumns();
queryParms.Add(myqTinfo);
myCC.Close();
}
private void BuildTableAliases()
{
InputName myLogicClass = new InputName();
for (int i = 0; i < queryParms.Count; i++)
{
queryTabInfo oneTabInfo = queryParms[i];
string thisAlias = myLogicClass.SuggestAlias(oneTabInfo.TableName);
if (thisAlias.Length == 1)
{
thisAlias = thisAlias.ToUpper();
}
else
{
thisAlias = thisAlias.ToLower();
}
oneTabInfo.Alias = thisAlias;
queryParms[i] = oneTabInfo;
}
}
private bool GetColsToShowRight(ColumnChooser myCC)
{
this.Hide();
Application.DoEvents();
myCC.ShowDialog();
if (GetReturnedColumns().Count == 0)
{
UndoTheJoin();
myCC.Close();
return(false);
}
var myqTinfo = new queryTabInfo();
myqTinfo.TableName = TabThatNeedsCols.ToString();
myqTinfo.showColumns = GetReturnedColumns();
myCC.Close();
return(true);
}