public static DataTable Select_HasNoParameter(string Query_Select)
{
//Tạo kết nối tới file Access
CreateConnect();
//Tạo đối tượng OledDBCommand
OleDbCommand MyCommand = new OleDbCommand();
//Gán tham số cho đối tượng OledDBCommand để thực hiện truy vấn dữ liệu từ CSDL
MyCommand.CommandType = CommandType.StoredProcedure;
MyCommand.CommandText = Query_Select;
MyCommand.Connection = MyConnect;
//Tạo DataReader
OleDbDataReader MyDataReader;
//Cho DataReader nhận dữ liệu trả về của câu lệnh SQL vừa truy vấn
MyDataReader = MyCommand.ExecuteReader();
//Load DataReader vào DataTable
DataTable MyTable = new DataTable();
MyTable.Load(MyDataReader);
//Dùng xong thì hủy các đối tượng
//Hủy đối tượng OleDBCommand
MyCommand.Dispose();
MyCommand = null;
//Hủy đối tượng DataReader (phải close trước khi hủy)
MyDataReader.Close();
MyDataReader.Dispose();
MyDataReader = null;
//Hủy kết nối với CSDL
DestroyConnect();
return(MyTable);
}
protected void Page_Load(object sender, EventArgs e)
{
string RoomID = Request.Url.Query.ToString().Substring(1);
Label2.Text = RoomID;
System.Data.SqlClient.SqlConnection MyConnection;
System.Data.SqlClient.SqlCommand MyCommand;
System.Data.SqlClient.SqlDataReader MyDataReader;
MyConnection = new System.Data.SqlClient.SqlConnection("Data Source=co-web-3.lboro.ac.uk;Initial Catalog=team17;User ID=team17;Password=g6g88fcv");
MyConnection.Open();
MyCommand = new System.Data.SqlClient.SqlCommand();
MyCommand.Connection = MyConnection;
MyCommand.CommandText = "SELECT Capacity FROM Room WHERE RoomID = '" + RoomID + "' ";
MyDataReader = MyCommand.ExecuteReader();
string nocap = "";
while (MyDataReader.Read())
{
nocap = MyDataReader.GetSqlValue(0).ToString();
}
MyDataReader.Close();
MyDataReader = null;
Label3.Text = nocap;
NoStudentsValidation.MaximumValue = nocap;
NoStudentsValidation.ErrorMessage = "Number of Students must be between 1 and " + nocap + ".";
}
public int MySql_Num_Rows(string CommandText)
{
int n = 0;
try
{
con.Open();
MySqlCommand myCommand = new MySqlCommand(CommandText, con);
MySqlDataReader MyDataReader;
MyDataReader = myCommand.ExecuteReader();
//SELECT COUNT(*) FROM table
while (MyDataReader.Read())
{
n++;
}
MyDataReader.Close();
}
catch (MySqlException)
{
}
finally
{
con.Close();
}
return(n);
}
protected void DropDownListSelectCandidate_SelectedIndexChanged(object sender, EventArgs e)
{
buttonActive(false, true, false, true, false, true);
SqlConnection sqlConn = new SqlConnection(myConnStr);
SqlCommand cmd = new SqlCommand("select * from CandidateTable where CandidateId='" + DropDownListSelectCandidate.SelectedValue + "'", sqlConn);
SqlDataReader MyDataReader;
try
{
sqlConn.Open();
MyDataReader = cmd.ExecuteReader();
MyDataReader.Read();
TextBoxCandidateId.Text = MyDataReader["CandidateId"].ToString();
TextBoxCandidateFirstName.Text = MyDataReader["CandidateFName"].ToString();
TextBoxCandidateLastName.Text = MyDataReader["CandidateLName"].ToString();
DropDownListDepartment.Text = MyDataReader["CandidateDepartment"].ToString();
TextBoxCandidateCode.Text = MyDataReader["CandidateCode"].ToString();
//Response.Write("<script language='javascript'> alert('Database connection openned');</script>");
}
catch (Exception er)
{
Response.Write("<script language='javascript'> alert('Database connection failed');</script>");
//Response.Write("<script language='javascript'> alert('Customer not found!');</script>");
}
finally
{
sqlConn.Close();
// Response.Write("<script language='javascript'> alert('Database connection closed');</script>");
}
}
protected void DropDownListSelectStaff_SelectedIndexChanged(object sender, EventArgs e)
{
buttonActive(false, true, false, true, false, true);
SqlConnection sqlConn = new SqlConnection(myConnStr);
SqlCommand cmd = new SqlCommand("select * from StaffTable where StaffId='" + DropDownListSelectStaff.SelectedValue + "'", sqlConn);
SqlDataReader MyDataReader;
try
{
sqlConn.Open();
MyDataReader = cmd.ExecuteReader();
MyDataReader.Read();
TextBoxStaffId.Text = MyDataReader["StaffId"].ToString();
TextBoxStaffFirstName.Text = MyDataReader["StaffFName"].ToString();
TextBoxStaffLastName.Text = MyDataReader["StaffLName"].ToString();
TextBoxStaffUsername.Text = MyDataReader["StaffUsername"].ToString();
TextBoxStaffPassword.Text = MyDataReader["StaffPassword"].ToString();
TextBoxStaffEmail.Text = MyDataReader["StaffEmail"].ToString();
//Response.Write("<script language='javascript'> alert('Database connection openned');</script>");
}
catch (Exception er)
{
Response.Write("<script language='javascript'> alert('Database connection failed');</script>");
//Response.Write("<script language='javascript'> alert('Customer not found!');</script>");
}
finally
{
sqlConn.Close();
// Response.Write("<script language='javascript'> alert('Database connection closed');</script>");
}
}
protected void DropDownListSelectHelp_SelectedIndexChanged(object sender, EventArgs e)
{
buttonActive(false, true, false, true, false);
SqlConnection sqlConn = new SqlConnection(myConnStr);
SqlCommand cmd = new SqlCommand("select * from HelpTable where HelpId='" + DropDownListSelectHelp.SelectedValue + "'", sqlConn);
SqlDataReader MyDataReader;
try
{
sqlConn.Open();
MyDataReader = cmd.ExecuteReader();
MyDataReader.Read();
TextBoxHelpId.Text = MyDataReader["HelpId"].ToString();
TextBoxHelpQuestion.Text = MyDataReader["HelpQuestion"].ToString();
TextBoxHelpAnswer.Text = MyDataReader["HelpAnswer"].ToString();
//Response.Write("<script language='javascript'> alert('Database connection openned');</script>");
}
catch (Exception er)
{
Response.Write("<script language='javascript'> alert('Database connection failed');</script>");
//Response.Write("<script language='javascript'> alert('Customer not found!');</script>");
}
finally
{
sqlConn.Close();
// Response.Write("<script language='javascript'> alert('Database connection closed');</script>");
}
}
private void button1_Click(object sender, EventArgs e)
{
if (MessageBox.Show("Сохранить изменения?", "ЖД Вокзал", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
{
//начинаем запрос
try
{
MySqlConnection conn = new MySqlConnection(Form1.connStr);
// устанавливаем соединение с БД
conn.Open();
string add = "UPDATE Poezd SET " +
"Kolichestvo_vagonov = '" + textBox2.Text + "', " +
"Kolichestvo_mest = '" + textBox3.Text + "' WHERE " +
"ID_Poezda = '" + transport.idedit_poezd + "'";
MySqlCommand adda = new MySqlCommand(add, conn);
// MySqlCommand insrt = new MySqlCommand(insert, conn);
MySqlDataReader MyDataReader;
MyDataReader = adda.ExecuteReader();
while (MyDataReader.Read())
{
}
MyDataReader.Close();
conn.Close();
MessageBox.Show("Поезд отредактирован!", "ЖД Вокзал", MessageBoxButtons.OK, MessageBoxIcon.Information);
Close();
}
catch
{
MessageBox.Show("Ошибка редактирования!", "ЖД Вокзал", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
protected void ButtonEditStaff_Click(object sender, EventArgs e)
{
buttonActive(true, false, false, false, true);
SqlConnection sqlConn = new SqlConnection(myConnStr);
SqlCommand cmd = new SqlCommand("select * from DepartmentTable where DepartmentId='" + DropDownListSelectDepartment.SelectedValue + "'", sqlConn);
SqlDataReader MyDataReader;
try
{
sqlConn.Open();
MyDataReader = cmd.ExecuteReader();
MyDataReader.Read();
TextBoxDepartmentId.Text = MyDataReader["DepartmentId"].ToString();
TextBoxDepartmentName.Text = MyDataReader["DepartmentName"].ToString();
//Response.Write("<script language='javascript'> alert('Database connection openned');</script>");
}
catch (Exception er)
{
Response.Write("<script language='javascript'> alert('Database connection failed');</script>");
//Response.Write("<script language='javascript'> alert('Customer not found!');</script>");
}
finally
{
sqlConn.Close();
// Response.Write("<script language='javascript'> alert('Database connection closed');</script>");
}
}
public IEnumerable <string> GetAllValues()
{
List <string> valuesList = new List <string>();
string query = "SELECT * FROM user";
//TODO: Test if mysql connection is open
MySqlCommand myCommand = new MySqlCommand(query, connection);
MySqlDataReader MyDataReader;
try
{
MyDataReader = myCommand.ExecuteReader();
while (MyDataReader.Read())
{
//General idea: MyDataReader at [0] string int etc...
valuesList.Add(MyDataReader.GetString(0));
valuesList.Add(MyDataReader.GetString(1));
}
MyDataReader.Close();
}
catch (Exception ex)
{
Console.WriteLine("Exception reading from db : " + ex);
}
return(valuesList);
}
public void Load()
{
Thread.CurrentThread.CurrentCulture = (CultureInfo)Thread.CurrentThread.CurrentCulture.Clone(); Thread.CurrentThread.CurrentCulture.DateTimeFormat.ShortDatePattern = "ddd, MMM dd, yyyy";
select_dPicker.SelectedDate = DateTime.Now;
note_tBox.Text = note;
time_TPicker.Value = Convert.ToDateTime(hour.Replace(".", ":"));
if (Connection.MyConnection.State == ConnectionState.Broken)
{
Connection.MyConnection.Open();
}
if (Connection.MyConnection.State == ConnectionState.Open)
{
using (OdbcCommand MyCommand = new OdbcCommand("SELECT `tms_projects`.`project_name`,`tms_reminders`.`project` ,`tms_category`.`category_name` ,`tms_reminders`.`category` FROM `tms_reminders` INNER JOIN `tms_projects` ON `tms_projects`.`project_id`=`tms_reminders`.`project` INNER JOIN `tms_category` ON `tms_category`.`category_id`=`tms_reminders`.`category` WHERE `tms_reminders`.`rid`='" + id + "'", Connection.MyConnection))
{
OdbcDataReader MyDataReader;
MyDataReader = MyCommand.ExecuteReader();
MyDataReader.Read();
proname_wTBox.Text = MyDataReader.GetString(0);
proid = MyDataReader.GetInt32(1);
catename_wTBox.Text = MyDataReader.GetString(2);
catid = MyDataReader.GetInt32(3);
MyDataReader.Close();
}
}
}
private void edit_poezd_Load(object sender, EventArgs e)
{
label1.Text = "ID: " + transport.idedit_poezd;
//ДЕЛАЕМ ЗАПРОС К БД
MySqlConnection conn = new MySqlConnection(Form1.connStr);
// устанавливаем соединение с БД
conn.Open();
// запрос
string auth = "SELECT * FROM Poezd WHERE ID_Poezda = '" + transport.idedit_poezd + "'";
try
{
MySqlCommand commandauth = new MySqlCommand(auth, conn);
// string name = commandauth.ExecuteScalar().ToString();
MySqlDataReader MyDataReader;
MyDataReader = commandauth.ExecuteReader();
while (MyDataReader.Read())
{
textBox2.Text = MyDataReader.GetString(1);
textBox3.Text = MyDataReader.GetString(2);
}
MyDataReader.Close();
// закрываем соединение с БД
conn.Close();
}
catch
{
}
}
private void Window_Loaded(object sender, RoutedEventArgs e)
{
if (Connection.MyConnection.State == ConnectionState.Broken)
{
Connection.MyConnection.Open();
}
if (Connection.MyConnection.State == ConnectionState.Open)
{
using (OdbcCommand MyCommand = new OdbcCommand("SELECT duedate,priority,reminder,minute,note FROM tms_reminders WHERE rid='" + TaskReminderCount.rid + "'", Connection.MyConnection))
{
OdbcDataReader MyDataReader;
MyDataReader = MyCommand.ExecuteReader();
MyDataReader.Read();
duedateLab.Content = MyDataReader.GetDateTime(0).ToString("yyyy-MM-dd hh:mm tt ");
priolab.Content = MyDataReader.GetString(1);
title_Lab.Content = MyDataReader.GetString(2);
noofhourlab.Content = TimeDisplay(MyDataReader.GetInt32(3));
string tempcomment = MyDataReader.GetString(4);
if (tempcomment.Length == 0)
{
commentlab.Text = "<no descrption>";
}
else
{
commentlab.Text = MyDataReader.GetString(4);
}
MyDataReader.Close();
}
}
}
private void fillList()
{
SqlConnection sqlConn = new SqlConnection(myConnStr);
SqlCommand cmd = new SqlCommand("select * from DateRangeTable", sqlConn);
SqlDataReader MyDataReader;
DropDownListDateRange.Items.Clear();
try
{
sqlConn.Open();
MyDataReader = cmd.ExecuteReader();
while (MyDataReader.Read())
{
ListItem newitem = new ListItem();
newitem.Text = "Start Date: " + MyDataReader["DateRangeStart"] + ". End Date:" + MyDataReader["DateRangeEnd"];
newitem.Value = MyDataReader["DateRangeId"].ToString();
DropDownListDateRange.Items.Add(newitem);
}
}
catch (Exception er)
{
Response.Write("<script language='javascript'> alert('Database connection failed');</script>");
}
finally
{
//sqlConn.Close();
}
}
protected void ButtonEdit_Click(object sender, EventArgs e)
{
buttonActive(true, false, true);
SqlConnection sqlConn = new SqlConnection(myConnStr);
SqlCommand cmd = new SqlCommand("select * from DateRangeTable", sqlConn);
SqlDataReader MyDataReader;
try
{
sqlConn.Open();
MyDataReader = cmd.ExecuteReader();
MyDataReader.Read();
TextBoxDateRangeId.Text = MyDataReader["DateRangeId"].ToString();
TextBoxDateRangeStart.Text = MyDataReader["DateRangeStart"].ToString();
TextBoxDateRangeEnd.Text = MyDataReader["DateRangeEnd"].ToString();
//Response.Write("<script language='javascript'> alert('Database connection openned');</script>");
}
catch (Exception er)
{
Response.Write("<script language='javascript'> alert('Database connection failed');</script>");
//Response.Write("<script language='javascript'> alert('Customer not found!');</script>");
}
finally
{
//sqlConn.Close();
//Response.Write("<script language='javascript'> alert('Database connection closed');</script>");
}
}
// Retourne le total par station
public static Dictionary <int, double> getMoyenne()
{
Dictionary <int, double> result = new Dictionary <int, double>();
if (!connection)
{
setConnection();
if (!connection)
{
return(result);
}
}
//Desc de la table donnees
OdbcCommand MyCommand = new OdbcCommand("select station, avg(available + free) from donnees where valid='1' group by station;", MyConnection);
OdbcDataReader MyDataReader;
MyDataReader = MyCommand.ExecuteReader();
Console.WriteLine("Executed : " + MyDataReader.RecordsAffected);
while (MyDataReader.Read())
{
result[MyDataReader.GetInt32(0)] = MyDataReader.GetDouble(1);
}
//Close all resources
MyDataReader.Close();
return(result);
}
private void fillList()
{
SqlConnection sqlConn = new SqlConnection(myConnStr);
SqlCommand cmd = new SqlCommand("select * from StaffTable", sqlConn);
SqlDataReader MyDataReader;
DropDownListSelectStaff.Items.Clear();
try
{
sqlConn.Open();
MyDataReader = cmd.ExecuteReader();
while (MyDataReader.Read())
{
ListItem newitem = new ListItem();
newitem.Text = MyDataReader["StaffId"] + ", " + MyDataReader["StaffFName"] + ", " + MyDataReader["StaffLName"] + ", " + MyDataReader["StaffUsername"] + ", " + MyDataReader["StaffPassword"] + ", " + MyDataReader["StaffEmail"];
newitem.Value = MyDataReader["StaffId"].ToString();
DropDownListSelectStaff.Items.Add(newitem);
}
}
catch (Exception er)
{
Response.Write("<script language='javascript'> alert('Database connection failed');</script>");
}
finally
{
sqlConn.Close();
}
}
/// <summary>
/// Возвращает список с данными из таблицы
/// </summary>
/// <param name="quary">Запрос</param>
/// <param name="numberReadRow">Номера тех столбцов, с которых будут браться данные </param>
/// <returns></returns>
public static List <List <dynamic> > MySqlQuaryOut(string quary, params int[] numberReadRow)
{
List <List <dynamic> > arrData = new List <List <dynamic> >();
OpenConnect(ref mySQLConn);
MySqlCommand myCommand = new MySqlCommand(quary, mySQLConn);
MySqlDataReader MyDataReader;
MyDataReader = myCommand.ExecuteReader();
for (int i = 0; i < numberReadRow.Length; i++)
{
arrData.Add(new List <dynamic>());
}
while (MyDataReader.Read())
{
for (int i = 0; i < numberReadRow.Length; i++)
{
if (!MyDataReader.IsDBNull(numberReadRow[i]))
{
arrData[i].Add(MyDataReader.GetString(numberReadRow[i]));
}
else
{
arrData[i].Add("null");
}
}
}
mySQLConn.Close();
return(arrData);
}
private List <string> getHeaders()
{
string connectionStr = "server=localhost;user=root;database=university_lab;CharSet=utf8;password=root;";
string query = $@"select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='forms'";
List <string> resultList = new List <string>();
MySqlConnection conn = new MySqlConnection(connectionStr);
MySqlCommand myCommand = new MySqlCommand(query, conn);
conn.Open();
MySqlDataReader MyDataReader;
MyDataReader = myCommand.ExecuteReader();
while (MyDataReader.Read())
{
resultList.Add(MyDataReader.GetString(0)); //Получаем строку
}
MyDataReader.Close();
conn.Close();
return(resultList);
}
// Renvoie le details des stations
public static ArrayList getStationsDetails()
{
ArrayList result = new ArrayList();
if (!connection)
{
setConnection();
if (!connection)
{
return(result);
}
}
//Desc de la table donnees
OdbcCommand MyCommand = new OdbcCommand("desc stations;", MyConnection);
OdbcDataReader MyDataReader;
MyDataReader = MyCommand.ExecuteReader();
ArrayList header = new ArrayList();
while (MyDataReader.Read())
{
if (string.Compare(MyConnection.Driver, "myodbc3.dll") == 0)
{
header.Add(MyDataReader.GetString(0)); //Supported only by MyODBC 3.5
}
}
//Fetch
MyCommand.CommandText = "select * from stations;";
MyDataReader.Close();
MyDataReader = MyCommand.ExecuteReader();
Console.WriteLine("Executed : " + MyDataReader.RecordsAffected);
while (MyDataReader.Read())
{
Dictionary <string, string> temp = new Dictionary <string, string>();
if (string.Compare(MyConnection.Driver, "myodbc3.dll") == 0)
{
for (int i = 0; i < header.Count; i++)
{
if (MyDataReader.GetString(i) != "")
{
temp.Add(header[i] as string, MyDataReader.GetString(i));
}
}
}
result.Add(temp);
}
//Close all resources
MyDataReader.Close();
//MyConnection.Close();
return(result);
}
protected void titles1()
{
System.Data.SqlClient.SqlConnection MyConnection;
System.Data.SqlClient.SqlCommand MyCommand;
System.Data.SqlClient.SqlDataReader MyDataReader;
MyConnection = new System.Data.SqlClient.SqlConnection("Data Source=co-web-3.lboro.ac.uk;Initial Catalog=team17;User ID=team17;Password=g6g88fcv");
MyConnection.Open();
MyCommand = new System.Data.SqlClient.SqlCommand();
MyCommand.Connection = MyConnection;
MyCommand.CommandText = "SELECT RequestID FROM Request WHERE (Status = 'Pending') AND (UserID = '" + Session["login_name"] + "') AND (RoundID IN (SELECT RoundID From Round WHERE (SemesterID = '" + DropDownList1.SelectedValue + "')))";
MyDataReader = MyCommand.ExecuteReader();
while (MyDataReader.Read())
{
if (MyDataReader.GetSqlValue(0).ToString() == "Null")
{
Label2.Visible = false;
}
else
{
Label2.Visible = true;
}
}
MyDataReader.Close();
MyDataReader = null;
MyCommand.CommandText = "SELECT RequestID FROM Request WHERE (Status = 'Allocated') AND (UserID = '" + Session["login_name"] + "') AND (RoundID IN (SELECT RoundID From Round WHERE (SemesterID = '" + DropDownList1.SelectedValue + "')))";
MyDataReader = MyCommand.ExecuteReader();
while (MyDataReader.Read())
{
if (MyDataReader.GetSqlValue(0).ToString() == "Null")
{
Label1.Visible = false;
}
else
{
Label1.Visible = true;
}
}
MyDataReader.Close();
MyDataReader = null;
MyCommand.CommandText = "SELECT RequestID FROM Request WHERE (Status = 'Denied') AND (UserID = '" + Session["login_name"] + "') AND (RoundID IN (SELECT RoundID From Round WHERE (SemesterID = '" + DropDownList1.SelectedValue + "')))";
MyDataReader = MyCommand.ExecuteReader();
while (MyDataReader.Read())
{
if (MyDataReader.GetSqlValue(0).ToString() == "Null")
{
Label3.Visible = false;
}
else
{
Label3.Visible = true;
}
}
MyDataReader.Close();
MyDataReader = null;
}
// Initialize the connection to the DB
private static void setConnection()
{
try
{
//Connect to MySQL using MyODBC
MyConnection = new OdbcConnection(MyConString);
MyConnection.Open();
connection = true;
tailles = new Dictionary <int, int>();
OdbcCommand MyCommand = new OdbcCommand("select id,max(total) from stations group by id;", MyConnection);
//OdbcCommand MyCommand = new OdbcCommand( "select station, max(free+available) from donnees where free !=\"\" group by station;", MyConnection );
OdbcDataReader MyDataReader;
MyDataReader = MyCommand.ExecuteReader();
Console.WriteLine("Executed : " + MyDataReader.RecordsAffected);
while (MyDataReader.Read())
{
int valeur = 0;
int station = MyDataReader.GetInt32(0);
//int hour = MyDataReader.GetInt32(1);
try
{
valeur = MyDataReader.GetInt32(1);
}
catch (Exception e)
{
Console.WriteLine("Pas de base : " + e.Message);
continue;
}
tailles.Add(station, valeur);
}
//Close all resources
MyDataReader.Close();
}
catch (OdbcException MyOdbcException)//Catch any ODBC exception ..
{
connection = false;
for (int i = 0; i < MyOdbcException.Errors.Count; i++)
{
MessageBox.Show("Aucune connection à la base de données n'a pu être établie");
Console.Write("ERROR #" + i + "\n" +
"Message: " + MyOdbcException.Errors[i].Message + "\n" +
"Native: " + MyOdbcException.Errors[i].NativeError.ToString() + "\n" +
"Source: " + MyOdbcException.Errors[i].Source + "\n" +
"SQL: " + MyOdbcException.Errors[i].SQLState + "\n");
}
}
}
static void Main(string[] args)
{
MySqlConnection myConnection = new MySqlConnection("Database = vlad_m; Data Source = 192.168.27.79; User Id = vlad_m; charset=cp1251;default command timeout = 999; Password=vlad19957");
MySqlCommand myCommand = new MySqlCommand();
MySqlDataReader MyDataReader;
OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=dBASE 5.0;Data Source=D:\\Alex\\RNCB\\exp_db_gis");
myCommand.Connection = myConnection;
myConnection.Open();
conn.Open();
string s = DateTime.Now.ToString("yyyyMMdd" + "_9103006160.DBF");
OleDbCommand dbf = new OleDbCommand(string.Format("CREATE TABLE [" + s + "] (ELS VARCHAR(20),PAYERIDENT VARCHAR(11), FIO VARCHAR(50), LS VARCHAR(11), STREET VARCHAR(25), BUILDING VARCHAR(10), FLAT VARCHAR(10), SUM1 NUMERIC(10,2), SERVICECOD VARCHAR(2))"), conn);
dbf.Prepare();
dbf.ExecuteNonQuery();
// myCommand.CommandText = string.Format("UPDATE rncb,id_ls set rncb.jky = id_ls.ls_jky where rncb.ls = id_ls.id");
//myCommand.Prepare();//подготавливает строку
// myCommand.ExecuteNonQuery();//выполняет запрос
myCommand.CommandText = string.Format("select " +
"id_ls.ls_jky, rncb.PAYERIDENT, rncb.FIO, rncb.LS, rncb.STREET, rncb.BUILDING, rncb.FLAT, rncb.SUM1, rncb.SERVICECOD " +
"from rncb " +
"left OUTER JOIN id_ls on rncb.LS = id_ls.id " +
"order by 4; ");
myCommand.Prepare();//подготавливает строку
MyDataReader = myCommand.ExecuteReader();
while (MyDataReader.Read())
{
dbf.CommandText = string.Format("INSERT INTO [" + s + "] (ELS,PAYERIDENT,FIO,LS,STREET,BUILDING,FLAT,SUM1,SERVICECOD) VALUES ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}')",
MySqlHelper.EscapeString(MyDataReader.GetValue(0).ToString()),
MySqlHelper.EscapeString(MyDataReader.GetValue(1).ToString()),
MySqlHelper.EscapeString(MyDataReader.GetValue(2).ToString()),
MySqlHelper.EscapeString(MyDataReader.GetValue(3).ToString()),
MySqlHelper.EscapeString(MyDataReader.GetValue(4).ToString()),
MySqlHelper.EscapeString(MyDataReader.GetValue(5).ToString()),
MySqlHelper.EscapeString(MyDataReader.GetValue(6).ToString()),
MySqlHelper.EscapeString(MyDataReader.GetValue(7).ToString()),
MySqlHelper.EscapeString(MyDataReader.GetValue(8).ToString()));
dbf.Prepare();
dbf.ExecuteNonQuery();
}
MyDataReader.Close();
MyDataReader.Close();
conn.Close();
}
// Retourne les vecteurs pour les jours ouvres
public static void getRemplissageByHourOuvresHisto(DateTime start, DateTime end)
{
Dictionary <int, Dictionary <int, KeyValuePair <double, double> > > result = new Dictionary <int, Dictionary <int, KeyValuePair <double, double> > >();
if (!connection)
{
setConnection();
}
//Desc de la table donnees
string s = " and date >= " + convertToTimestamp(start) + " and date <= " + convertToTimestamp(end) + " ";
s += "and day < 6 ";
OdbcCommand MyCommand = new OdbcCommand("select station as Station, hour as Hour, cast(avg(available) * 100 as unsigned) as Valeur, cast(std(available) * 100 as unsigned) as Variance from donnees where valid='1' and free!=\"\" " + s + "group by station, hour;", MyConnection);
OdbcDataReader MyDataReader;
MyDataReader = MyCommand.ExecuteReader();
Console.WriteLine("Executed : " + MyDataReader.RecordsAffected);
while (MyDataReader.Read())
{
int valeur = 0;
int station = MyDataReader.GetInt32(0);
int hour = MyDataReader.GetInt32(1);
int variance = 0;
try {
valeur = MyDataReader.GetInt32(2);
}
catch (Exception e) {
Console.WriteLine("Error : " + e.Message);
continue;
}
try {
variance = MyDataReader.GetInt32(3);
}
catch (Exception e) {
Console.WriteLine("Error : " + e.Message);
continue;
}
if (!result.ContainsKey(station))
{
result[station] = new Dictionary <int, KeyValuePair <double, double> >();
}
result[station][hour] = new KeyValuePair <double, double>((valeur / 100.0) / (double)tailles[station], (variance / 100.0) / (double)tailles[station]);
}
//Close all resources
MyDataReader.Close();
statsTabHeureOuvre = result;
}
private void Window_Loaded(object sender, RoutedEventArgs e)
{
outt_TPicker.Value = Convert.ToDateTime(Login.etimehh + ":" + Login.etimemm);
using (OdbcCommand MyCommand = new OdbcCommand("select date_year,date_month,date_day from tms_logsheet where logsheet_id='" + lgid + "'", Connection.MyConnection))
{
OdbcDataReader MyDataReader;
MyDataReader = MyCommand.ExecuteReader();
MyDataReader.Read();
load_lab.Content = "You haven't sign-out on " + (MyDataReader.GetInt32(2).ToString()) + "/" + (MyDataReader.GetInt32(1).ToString()) + "/" + (MyDataReader.GetInt32(0).ToString()) + ".";
MyDataReader.Close();
}
}
// Retourne les vecteurs par jour
public static Dictionary <int, Dictionary <int, double> > getRemplissageByDay(DateTime start, DateTime end)
{
Dictionary <int, Dictionary <int, double> > result = new Dictionary <int, Dictionary <int, double> >();
if (!connection)
{
setConnection();
if (!connection)
{
return(result);
}
}
//Desc de la table donnees
string s = " and date >= " + convertToTimestamp(start) + " and date <= " + convertToTimestamp(end) + " ";
OdbcCommand MyCommand = new OdbcCommand("select station as Station, day as Day, cast(avg(available) * 100 as unsigned) as Valeur from donnees where valid='1' and free!=\"\" " + s + "group by station, day;", MyConnection);
OdbcDataReader MyDataReader;
MyDataReader = MyCommand.ExecuteReader();
Console.WriteLine("Executed : " + MyDataReader.RecordsAffected);
while (MyDataReader.Read())
{
int valeur = 0;
int station = MyDataReader.GetInt32(0);
int hour = MyDataReader.GetInt32(1);
try
{
valeur = MyDataReader.GetInt32(2);
}
catch (Exception e)
{
Console.WriteLine("Error : " + e.Message);
continue;
}
if (!result.ContainsKey(station))
{
result[station] = new Dictionary <int, double>();
}
result[station][hour] = valeur / 100.0;
result[station][hour] = result[station][hour] / (double)tailles[station];
}
//Close all resources
MyDataReader.Close();
return(result);
}
/// <summary>
/// 执行查询语句，返回MySqlDataReader ( 注意：调用该方法后，一定要对MySqlDataReader进行Close )
/// 因为外部要读取数据，不能立即回收连接，所以以MyDataReader封装一下关闭回收连接的操作
/// </summary>
/// <param name="strSQL">查询语句</param>
/// <returns>MySqlDataReader</returns>
public static MyDataReader ExecuteReader(string dbKey, string strSQL)
{
MyDbConnection2 conn = null;
conn = PopDBConnection(dbKey);
if (null != conn)
{
MySqlDataReader mySqlDataReader = conn.ExecuteReader(strSQL);
MyDataReader myDataReader = new MyDataReader(conn, mySqlDataReader);
return(myDataReader);
}
return(null);
}
protected void Button1_Click(object sender, EventArgs e)
{
System.Data.SqlClient.SqlConnection MyConnection;
System.Data.SqlClient.SqlCommand MyCommand;
System.Data.SqlClient.SqlDataReader MyDataReader;
MyConnection = new System.Data.SqlClient.SqlConnection("Data Source=co-web-3.lboro.ac.uk;Initial Catalog=team17;User ID=team17;Password=g6g88fcv");
MyConnection.Open();
MyCommand = new System.Data.SqlClient.SqlCommand();
MyCommand.Connection = MyConnection;
MyCommand.CommandText = "INSERT INTO Module (Name, ModuleID, DepartmentID, Part) VALUES ('" + ModuleName.Text + "','" + DepCode.Text + ModulePart.Text.Substring(0, 1) + ModuleCode.Text + "','" + DepCode.Text + "','" + ModulePart.Text + "')";
MyDataReader = MyCommand.ExecuteReader();
MyDataReader.Close();
MyDataReader = null;
Response.Redirect("~/Secure/modadd.aspx");
}
public void Zapros(string CommandText)
{
MySqlCommand myCommand = new MySqlCommand();
MySqlDataReader MyDataReader;
MySqlConnection myConnection = new MySqlConnection(CONNECT);
listSpravka.ItemsSource = null;
myCommand = new MySqlCommand(CommandText, myConnection);
myConnection.Open();
MyDataReader = myCommand.ExecuteReader();
while (MyDataReader.Read())
{
norma.Add(new Norma(MyDataReader.GetInt32(0), MyDataReader.GetInt32(1), MyDataReader.GetString(2)));
}
MyDataReader.Close();
}
public static MyDataReader ExecuteReader(string dbKey, string strSQL)
{
MyDbConnection2 conn = DbHelperMySQL3.PopDBConnection(dbKey);
MyDataReader result;
if (null != conn)
{
MySqlDataReader mySqlDataReader = conn.ExecuteReader(strSQL, new MySqlParameter[0]);
MyDataReader myDataReader = new MyDataReader(conn, mySqlDataReader);
result = myDataReader;
}
else
{
result = null;
}
return(result);
}
// Effectue les requetes
private static Dictionary <int, Dictionary <int, KeyValuePair <double, double> > > sendRequest(String query, int select)
{
Dictionary <int, Dictionary <int, KeyValuePair <double, double> > > result = new Dictionary <int, Dictionary <int, KeyValuePair <double, double> > >();
if (!connection)
{
setConnection();
if (!connection)
{
return(result);
}
}
//Console.WriteLine("\n !!! success, connected successfully !!!\n");
////Display connection information
//Console.WriteLine("Connection Information:");
//Console.WriteLine("\tConnection String:" + MyConnection.ConnectionString);
//Console.WriteLine("\tConnection Timeout:" + MyConnection.ConnectionTimeout);
//Console.WriteLine("\tDatabase:" + MyConnection.Database);
//Console.WriteLine("\tDataSource:" + MyConnection.DataSource);
//Console.WriteLine("\tDriver:" + MyConnection.Driver);
//Console.WriteLine("\tServerVersion:" + MyConnection.ServerVersion);
//Desc de la table donnees
OdbcCommand MyCommand = new OdbcCommand(query, MyConnection);
OdbcDataReader MyDataReader;
MyDataReader = MyCommand.ExecuteReader();
Console.WriteLine("Executed : " + MyDataReader.RecordsAffected);
while (MyDataReader.Read())
{
KeyValuePair <double, double> temp1 = new KeyValuePair <double, double>(MyDataReader.GetDouble(1), MyDataReader.GetDouble(2));
Dictionary <int, KeyValuePair <double, double> > temp = new Dictionary <int, KeyValuePair <double, double> >();
temp[select] = temp1;
result[MyDataReader.GetInt32(0)] = temp;
//result.Add(MyDataReader.GetInt32(0), temp);
}
//Close all resources
MyDataReader.Close();
//MyConnection.Close();
return(result);
}
