本文整理汇总了C#中DataConnection.joinQuery方法的典型用法代码示例。如果您正苦于以下问题：C# DataConnection.joinQuery方法的具体用法？C# DataConnection.joinQuery怎么用？C# DataConnection.joinQuery使用的例子？那么, 这里精选的方法代码示例或许可以为您提供帮助。您也可以进一步了解该方法所在类DataConnection的用法示例。
在下文中一共展示了DataConnection.joinQuery方法的4个代码示例，这些例子默认根据受欢迎程度排序。您可以为喜欢或者感觉有用的代码点赞，您的评价将有助于系统推荐出更棒的C#代码示例。
示例1: loadvisits
public void loadvisits(int studentID, DateTime minDate, DateTime maxDate)
{
min = minDate;
max = maxDate;
ID = studentID;
//clears the list box to enter new information
listBoxEditVisit.Items.Clear();
//TAB THIS
listBoxEditVisit.Items.Add("DATE".PadRight(15) + "\t" + "FIRST NAME".PadRight(30) + "\t" + "LAST NAME".PadRight(30) + "\t" + "ID".PadRight(12)+ "\t" + "TIME IN".PadRight(10)+"\t" + "TIME OUT".PadRight(10) + "\t" + "METHOD".PadRight(20) + "\t" + "TUTOR'S FIRST NAME".PadRight(30) + "TUTOR'S LAST NAME".PadRight(30)+ "\t" + "SUBJECT" + "\t" + "CATALOG" + "\t" + "SECTION");
//creates new dataconnection
DataConnection conn = new DataConnection();
SqlDataReader rd;
conn.Open();
//gets visits request
//just added STUDENT.FIRSTNAME, STUDENT.LASTNAME, STUDENT TABLE
rd = conn.joinQuery("SELECT VISIT.CLARION_ID, VISIT.DATE, VISIT.TIME_IN, VISIT.TIME_OUT, STUDENT.FIRSTNAME, STUDENT.LASTNAME, VISIT.METHOD, TUTOR.TUTOR_ID, SUBJECT, CATALOG, S_TUTOR.FIRSTNAME AS TUTORFIRSTNAME, S_TUTOR.LASTNAME AS TUTORLASTNAME, SECTION FROM VISIT INNER JOIN student on visit.clarion_id = student.clarion_id LEFT JOIN TUTOR ON VISIT.TUTOR_ID = TUTOR.TUTOR_ID LEFT JOIN STUDENT S_TUTOR ON TUTOR.CLARION_ID = S_TUTOR.CLARION_ID WHERE visit.DATE<='" + maxDate + "' AND visit.DATE>='" + minDate + (studentID == 0 ? "'":"' AND VISIT.CLARION_ID = '" + studentID + "'") + " ORDER BY DATE, TIME_IN");
if (rd.HasRows)
{
while (rd.Read())
{
DateTime thedate = DateTime.Parse(rd["DATE"].ToString());
string TIMEOUT = rd["TIME_OUT"].ToString().PadRight(10);
if (TIMEOUT.Replace(" ","").Length !=8)
TIMEOUT = " ".PadRight(18);
listBoxEditVisit.Items.Add(thedate.ToString("d").PadRight(15) + "\t" + rd["FIRSTNAME"].ToString().PadRight(30) + "\t" + rd["LASTNAME"].ToString().PadRight(30) + "\t" + (int.Parse(rd["CLARION_ID"].ToString())).ToString("D8").PadRight(12) + "\t" + rd["TIME_IN"].ToString().PadRight(10) + "\t" + TIMEOUT.PadRight(10) + "\t" + rd["METHOD"].ToString().PadRight(20) + "\t" + rd["TUTORFIRSTNAME"].ToString().PadRight(30) + " " + rd["TUTORLASTNAME"].ToString().PadRight(30) + "\t" + rd["SUBJECT"].ToString().PadRight(5) + "\t" + ((rd["CATALOG"]).ToString()).PadRight(5) + "\t" + ((rd["SECTION"]).ToString()).PadRight(4));
}
}
rd.Close();
//closes connection
conn.Close();
if (studentID == 0)
{
conn.Open();
//gets visits request
//just added STUDENT.FIRSTNAME, STUDENT.LASTNAME, STUDENT TABLE
// rd = conn.joinQuery("SELECT VISIT.CLARION_ID, VISIT.DATE, VISIT.TIME_IN, VISIT.TIME_OUT, STUDENT.FIRSTNAME, STUDENT.LASTNAME, VISIT.METHOD, TUTOR.TUTOR_ID, SUBJECT, CATALOG, S_TUTOR.FIRSTNAME AS TUTORFIRSTNAME, S_TUTOR.LASTNAME AS TUTORLASTNAME, SECTION FROM VISIT INNER JOIN student on visit.clarion_id = student.clarion_id LEFT JOIN TUTOR ON VISIT.TUTOR_ID = TUTOR.TUTOR_ID LEFT JOIN STUDENT S_TUTOR ON TUTOR.CLARION_ID = S_TUTOR.CLARION_ID WHERE visit.DATE<='" + maxDate + "' AND visit.DATE>='" + minDate + (studentID == 0 ? "'" : "' AND VISIT.CLARION_ID = '" + studentID + "'") + " ORDER BY DATE, TIME_IN");
rd = conn.joinQuery("select tutor_hour.tutor_id, tutor_hour.date, tutor_hour.time_out ,tutor_hour.time_difference, tutor_hour.time_in, student.lastname, student.firstname from tutor_hour inner join tutor on tutor_hour.tutor_id = tutor.tutor_id inner join student on tutor.clarion_id = student.clarion_id where tutor_hour.DATE<='" + maxDate + "' AND tutor_hour.DATE>='" + minDate+"' ");
if (rd.HasRows)
{
while (rd.Read())
{
DateTime thedate = DateTime.Parse(rd["DATE"].ToString());
string TIMEOUT = rd["TIME_OUT"].ToString().PadRight(10);
if (TIMEOUT.Replace(" ", "").Length != 8)
TIMEOUT = " ".PadRight(18);
listBoxEditVisit.Items.Add(thedate.ToString("d").PadRight(15) + "\t" + rd["FIRSTNAME"].ToString().PadRight(30) + "\t" + rd["LASTNAME"].ToString().PadRight(30) + "\t" + ("TUT" + int.Parse(rd["tutor_ID"].ToString()).ToString("D4").PadRight(10)) + "\t" + rd["TIME_IN"].ToString().PadRight(10) + "\t" + rd["TIME_OUT"].ToString().PadRight(10) + "\t" + "TUTOR");
}
}
rd.Close();
//closes connection
conn.Close();
}
}示例2: loadlist
public void loadlist()
{
listBoxEnableTutors.Items.Clear();
listBoxDisableTutors.Items.Clear();
listBoxLoggedIn.Items.Clear();
DataConnection conn = new DataConnection();
SqlDataReader rd;
conn.Open();
try
{
rd = conn.GetReader("STUDENT.FIRSTNAME, STUDENT.LASTNAME, STUDENT.clarion_id, tutor.status", "TUTOR INNER JOIN STUDENT ON TUTOR.CLARION_ID=STUDENT.CLARION_ID");
if (rd.HasRows)
{
while (rd.Read())
{
if (rd[3].ToString() == "active")
listBoxEnableTutors.Items.Add(rd[0].ToString() + " " + rd[1].ToString() + " " + rd[2]);
else
listBoxDisableTutors.Items.Add(rd[0].ToString() + " " + rd[1].ToString() + " " + rd[2]);
}
}
listBoxLoggedIn.Items.Add("DATE\t\tTIME IN\t\tID\t\t" + "LAST NAME".PadRight(30) + "\tFIRST NAME");
}
catch
{
MessageBox.Show("Cannot load tutors", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
conn.Close();
conn.Open();
try
{
rd = conn.GetReader("*", "VISIT", "student", "visit.clarion_id=student.clarion_id and time_out is null", 1);
if (rd.HasRows)
{
while (rd.Read())
{
DateTime jdate = DateTime.Parse(rd[1].ToString());
listBoxLoggedIn.Items.Add(jdate.ToString("MM/dd/yyyy") + "\t" + rd[2] + "\t" + int.Parse(rd[0].ToString()).ToString("D8").PadRight(10) + "\t" + rd[13].ToString().PadRight(30) + "\t" + rd[14].ToString().PadRight(30));
}
}
}
catch
{
MessageBox.Show("Cannot load currently logged in visits", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
conn.Close();
conn.Open();
try
{
rd = conn.joinQuery("select tutor_hour.tutor_id, tutor_hour.date ,tutor_hour.time_difference, tutor_hour.time_in, student.lastname, student.firstname from tutor_hour inner join tutor on tutor_hour.tutor_id = tutor.tutor_id inner join student on tutor.clarion_id = student.clarion_id where time_difference is null");
//rd = conn.GetReader("*", "tutor_hour", "student", "tutor_hour.clarion_id=student.clarion_id and time_out is null", 1);
if (rd.HasRows)
{
while (rd.Read())
{
DateTime jdate = DateTime.Parse(rd[1].ToString());
listBoxLoggedIn.Items.Add(jdate.ToString("MM/dd/yyyy") + "\t" + rd[3] + "\t" + ("TUT" + int.Parse(rd[0].ToString()).ToString("D5")).PadRight(10) + "\t" + rd[4].ToString().PadRight(30) + "\t" + rd[5].ToString().PadRight(30));
}
}
}
catch
{
MessageBox.Show("Cannot load currently logged in visits", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
conn.Close();
}示例3: displayBtn_Click
//Created by Sean: button1_Click inside the Reporting Tab
private void displayBtn_Click(object sender, EventArgs e)
{
//TODO: This should add a placeholder to the listbox that represents
// the data that will be placed into the excel file
string column, table, condition = "", row = "", first="", last="";
DataConnection conn = new DataConnection();
SqlDataReader rd;
int year = 0, term=0, count = 0;
bool y = int.TryParse(txtYear.Text, out year);
term = DataConnection.getTerm(year, comboTerm.SelectedItem.ToString());
conn.Open();
rd = conn.GetReader("*", "VISIT", "WHERE TERM = '" + term.ToString() + "'");
if (!(rd.HasRows))
{
conn.Close();
return;
}
conn.Close();
conn.Open();
/*
term += (int.Parse(year.ToString())/1000)*1000;
term += (int.Parse(year.ToString()) % 100) * 10;
if (comboTerm.SelectedItem.ToString() == "Winter")
term += 9;
else if (comboTerm.SelectedItem.ToString() == "Spring")
term += 1;
else if (comboTerm.SelectedItem.ToString() == "Summer")
term += 5;
else
term += 8;
*/
switch (comboCountCategory.SelectedItem.ToString())
{
case "Method":
column = "method, COUNT(DISTINCT CLARION_ID), term";
table = "VISIT";
//MessageBox.Show(comboFilter.SelectedItem.ToString());
condition = " where term = '" + term + "' GROUP BY METHOD, term";
listBoxReport.Items.Add( "Method".PadRight(30) + "\t" + "Number of Students");
if (comboFilter.SelectedItem.ToString() == "All")
{
// MessageBox.Show("wind");
rd = conn.GetReader(column, table, condition);
while (rd.Read())
{
// for (int i = 0; i < 2; i++)
{
row += rd[0].ToString().PadRight(30) + "\t" +rd[1];
// MessageBox.Show((80 - rd[0].ToString().Length).ToString());
}
//MessageBox.Show("1".PadLeft(80-(rd[0].ToString().Length*1)));
// MessageBox.Show(string.Format("{0,-50} {1,60}", rd[0].ToString(), rd[1].ToString()));h
listBoxReport.Items.Add(row);
//listBoxReport.Items.Add(new object[] { rd[0], rd[1] });
row = "";
//MessageBox.Show(row);
}
}
else
{
rd = conn.GetReader("method, COUNT(DISTINCT CLARION_ID), term", "visit", " where term = '" + term.ToString() + "' and method = '" + comboFilter.SelectedItem.ToString() + "' GROUP BY METHOD, term ");
if (rd.HasRows)
{
while (rd.Read())
{
for (int i = 0; i < 2; i++)
row += rd[i].ToString().PadRight(30) + "\t";
listBoxReport.Items.Add(row);
row = "";
}
}
else
listBoxReport.Items.Add(comboFilter.SelectedItem.ToString().PadRight(30) + "\t0");
}
listBoxReport.Items.Add("");
/*
//condition = " method " + " = "+ " '"+"other"+"' ";
filterColumn = "METHOD";
* */
break;
case "Student":
int newid = -1;
int nontradcount = 0;
int studentcount = 0;
count = 0;
TimeSpan newtime = new TimeSpan();
if (comboFilter.SelectedItem.ToString() == "All" || comboFilter.SelectedItem.ToString() == "Total Hours")
{
listBoxReport.Items.Add("Student Name".PadRight(20) + "\t" + "".PadRight(20) + "\t" + "Total Hours");
// rd = conn.GetReader(column, table, condition);
rd = conn.joinQuery("select visit.clarion_id, visit.time_difference, visit.term, student.lastname, student.firstname, student.age from visit inner join student on visit.clarion_id = student.clarion_id where time_difference is not null and visit.term = '" + term.ToString() + "'");
while (rd.Read())
//.........这里部分代码省略.........
示例4: btnAddTutor_Click
//Adds Tutor to the list of tutors via Student ID and adds their information to the Tutors table
private void btnAddTutor_Click(object sender, EventArgs e)
{
/////////////// edit table so only clarion id, status and cnet_username are used
//bool valid = false;
//Gets the student id
string studentID = txtTutorStudentID.Text;
/*cn.Open();
cmd.CommandText = "select * from student where CLARION_ID=" + studentID;
rd = cmd.ExecuteReader();
*/
try
{
bool notInDB = frmMain.studentIDExists(int.Parse(studentID));
DataConnection conn = new DataConnection();
conn.Open();
SqlDataReader rd = conn.joinQuery("select clarion_id from tutor where clarion_id = " + studentID);
if (rd.HasRows)
{
notInDB = false;
}
conn.Close();
if (notInDB)
{
conn.Open();
conn.Query("insert into tutor(clarion_id,status) values ('" + studentID + "', '" + "active" + "')");
conn.Close();
}
else
MessageBox.Show("Tutor is already in database.", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
catch
{
MessageBox.Show("Error while searching for student ID. Please check to see if it is valid.", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
loadlist();
}