public void EditSpecification(Specification s)
{
using (connect = new MySqlConnection(_connectionString))
{
connect.Open();
using (MySqlTransaction transaction = connect.BeginTransaction())
{
try
{
string query = "EditPackageSpecification";
var cmd = new MySqlCommand(query, connect) { CommandType = CommandType.StoredProcedure };
cmd.Parameters.AddWithValue("SpecificationID", s.ID);
cmd.Parameters.AddWithValue("PWeight", s.Weight);
cmd.Parameters.AddWithValue("DeimensionHeight", s.Height);
cmd.Parameters.AddWithValue("DimensionWidth", s.Width);
cmd.Parameters.AddWithValue("DimensionLength", s.Length);
cmd.ExecuteNonQuery();
connect.Close();
}
catch (InvalidOperationException ioException)
{
connect.Close();
}
}
}
}
private void timer1_Tick(object sender, EventArgs e)
{
MySqlConnection cn = new MySqlConnection("server=localhost;user id=root; database=proyecto2; password="******"SELECT user_id FROM vs_users", cn);
MySqlDataAdapter da = new MySqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
if (dt.Rows.Count > 0)
{
login entrada = new login();
entrada.Show();
this.Hide();
cn.Close();
}
else
{
registro entrada = new registro();
entrada.Show();
this.Hide();
cn.Close();
}
}
}
protected void Button2_Click(object sender, EventArgs e)
{
try
{
conn = new MySql.Data.MySqlClient.MySqlConnection();
conn.ConnectionString = myConnectionString;
conn.Open();
MySqlCommand cmd3 = new MySqlCommand("INSERT INTO pokoje (id_pokoj, Nr_pokoj, Ilosc_lozek, Ilosc_osob, Typ_pokoj, Cena_pokoj) VALUES (@id_pokoj,@nrpokoj,@ilosclozek,@iloscosob,@typpokoj,@cenapokoj)", conn);
cmd3.CommandType = CommandType.Text;
cmd3.Parameters.AddWithValue("@nrpokoj", TextBox_up_Nr0.Text);
cmd3.Parameters.AddWithValue("@ilosclozek", TextBox_up_IL0.Text);
cmd3.Parameters.AddWithValue("@iloscosob", TextBox_up_IO0.Text);
cmd3.Parameters.AddWithValue("@typpokoj", DropDownList_up_Typ0.Text);
cmd3.Parameters.AddWithValue("@cenapokoj", TextBox_up_Cena0.Text);
cmd3.Parameters.AddWithValue("@id_pokoj", TextBox_up_Id0.Text);
cmd3.ExecuteNonQuery();
conn.Close();
//INSERT INTO pozycja_rezerwacji(Rezerwacja_id_rezerwacja, Pokoje_id_pokoj) VALUES (8, 7);
MySqlCommand cmd4 = new MySqlCommand("INSERT INTO pozycja_rezerwacji(Rezerwacja_id_rezerwacja, Pokoje_id_pokoj) VALUES (8, @id_pokoj)", conn);
cmd4.CommandType = CommandType.Text;
conn.Open();
cmd4.Parameters.AddWithValue("@id_pokoj", TextBox_up_Id0.Text);
cmd4.ExecuteNonQuery();
conn.Close();
}
catch (Exception ex)
{
Blad.Visible = true;
}
}
public static List<BusStops> GetAllBusStops()
{
string ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["TrackABus"].ConnectionString;
using (var connection = new MySqlConnection(ConnectionString))
{
using (var cmd = connection.CreateCommand())
{
try
{
var stops = new List<BusStops>();
connection.Open();
cmd.CommandText = "SELECT StopName FROM BusStop;";
var read = cmd.ExecuteReader();
while (read.Read())
{
stops.Add(new BusStops(){busStopNames = read.GetString(0)});
}
read.Close();
connection.Close();
return stops;
}
catch (Exception e)
{
Debug.WriteLine(e.Message);
connection.Close();
return null;
}
}
}
}
public void alterUser(TextBox textBoxGammelPassord, TextBox textBoxEpost, TextBox textBoxNyPassord, TextBox textBoxAdresse, TextBox textBoxTelefon, TextBox textBoxID, String bondeID)
{
String dbconnect = myconnectionstring;
MySqlConnection dbconn = new MySqlConnection(dbconnect);
if (textBoxGammelPassord.Text == gammeltpassordLocal)
{
MySqlCommand cmd = dbconn.CreateCommand();
cmd.CommandText = "UPDATE login SET epost='" + textBoxEpost.Text + "', passord= '" + textBoxNyPassord.Text + "'WHERE bondeID= '" + bondeID + "'";
dbconn.Open();
cmd.ExecuteNonQuery();
dbconn.Close();
MySqlCommand cmd2 = dbconn.CreateCommand();
cmd2.CommandText = "UPDATE Kontakt SET adresse= '" + textBoxAdresse.Text + "', telefonnr= '" + textBoxTelefon.Text + "' WHERE bondeID= '" + bondeID + "'";
dbconn.Open();
cmd2.ExecuteNonQuery();
dbconn.Close();
MessageBox.Show(textBoxTelefon.Text);
getinfobruker(textBoxGammelPassord, textBoxEpost, textBoxNyPassord, textBoxAdresse, textBoxTelefon ,textBoxID, bondeID);
}
else
{
MessageBox.Show("Feil passord");
}
}
//type 2 allows users to have custom setting, type 1 is default for group
public static string getDays(string passedUser)
{
string return_data = "";
MySqlConnection connection = new MySqlConnection(MyConString);
try
{
MySqlCommand command = connection.CreateCommand();
MySqlDataReader Reader;
command.CommandText = "SELECT DATEDIFF(NOW(),(SELECT `submitted` FROM timedata WHERE (((timedata.user)='" + passedUser + "')) ORDER BY timedata.submitted DESC LIMIT 0,1)) AS diff;";
connection.Open();
Reader = command.ExecuteReader();
while (Reader.Read())
{
return_data = Reader["diff"].ToString();
}
connection.Close();
return return_data;
}
catch (Exception e)
{
try
{
connection.Close();
}
catch { }
return_data = "Error";
return return_data;
}
}
protected bool sprAdmin(int id)
{
string connStr = ConfigurationManager.ConnectionStrings["MySQLConnStr"].ConnectionString;
MySqlConnection conn = new MySqlConnection(connStr);
try
{
conn.Open();
string sql = "SELECT typ FROM users WHERE id=@Id;";
MySqlCommand zapytanie = new MySqlCommand(sql, conn);
zapytanie.Parameters.Add(new MySqlParameter("@Id", Session["id"].ToString()));
object wynik = zapytanie.ExecuteScalar();
if (wynik != null && wynik.ToString() == "A")
{
conn.Close();
return true;
}
conn.Close();
}
catch (MySqlException ex)
{
//Blad.InnerHtml = ex.ToString();
}
return false;
}
public int CreateContact(Contact c)
{
int ret = 0;
using (connect = new MySqlConnection(_connectionString))
{
connect.Open();
using (MySqlTransaction transaction = connect.BeginTransaction())
{
try
{
string query = "NewContact";
var cmd = new MySqlCommand(query, connect) { CommandType = CommandType.StoredProcedure };
cmd.Parameters.AddWithValue("PForename", c.Forename);
cmd.Parameters.AddWithValue("PSurname", c.Surname);
cmd.Parameters.AddWithValue("JobTitle", c.Position);
cmd.Parameters.AddWithValue("TelNumber", c.PhoneNumber);
ret = int.Parse(cmd.ExecuteScalar().ToString());
transaction.Commit();
connect.Close();
}
catch (InvalidOperationException ioException)
{
transaction.Rollback();
connect.Close();
}
}
}
return ret;
}
/// <summary>
/// 执行SQL语句，返回影响的记录数
/// </summary>
/// <param name="SQLString">SQL语句</param>
/// <returns>影响的记录数</returns>
public int ExecuteSql(string SQLString)
{
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
using (MySqlCommand cmd = new MySqlCommand(SQLString, connection))
{
try
{
connection.Open();
int rows = cmd.ExecuteNonQuery();
return rows;
}
catch (MySql.Data.MySqlClient.MySqlException e)
{
connection.Close();
throw new Exception(e.Message);
}
finally
{
cmd.Dispose();
connection.Close();
}
}
}
}
public void DeleteSpecification(int ID)
{
using (connect = new MySqlConnection(_connectionString))
{
connect.Open();
using (MySqlTransaction transaction = connect.BeginTransaction())
{
try
{
string query = "DeletePackageSpecification";
var cmd = new MySqlCommand(query, connect) { CommandType = CommandType.StoredProcedure };
cmd.Parameters.AddWithValue("SpecificationID", ID);
cmd.ExecuteNonQuery();
connect.Close();
}
catch (InvalidOperationException ioException)
{
connect.Close();
}
}
}
}
internal void InertNews(News news)
{
string sql = string.Format("insert into topic set date='{0}',url='{1}',place='{2}',level='{3}',reason='{4}',code='{5}',area_id='{6}'", news.Date, news.Detail_Url, news.Place, news.Level, news.Reason,news.Only,news.Area_Id);
string error = null;
MySqlConnection conn = null;
try
{
conn = new MySqlConnection(connStr);
conn.Open();
MySqlDataAdapter adapter = new MySqlDataAdapter(sql, conn);
// 创建DataSet，用于存储数据.
DataSet testDataSet = new DataSet();
// 执行查询，并将数据导入DataSet.
adapter.Fill(testDataSet, "result_data");
conn.Close();
Console.WriteLine(news.Place+"--"+news.Reason);
}
catch (Exception e)
{
conn.Close();
error = e.Message;
Console.WriteLine("insert Topic error------>{0}", error);
}
}
/// <summary>
/// 一个公用interface执行所有非 select语句
/// </summary>
/// <param name="non_query_type"></param>
/// <returns>返回影响数据库几行</returns>
public int GeneralNonSelectQuery(MySqlCommand cmd)
{
int iReturn = 0;
string connStr = sql.GetSQL(sql.SQL.S_CONNECTION_STR);
MySqlConnection conn = new MySqlConnection(connStr);
try
{
conn.Open();
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
iReturn = cmd.ExecuteNonQuery();
cmd.Dispose();
conn.Close();
conn.Dispose();
}
catch (Exception ex)
{
cmd.Dispose();
conn.Close();
conn.Dispose();
}
return iReturn;
}
private bool checkLogin(string username, string pass)
{
try
{
string mcon = "datasource=mysql.cc.puv.fi;port=3306;username=e1200641;password=bXk9fEzVExXW";
string Query = "select * from e1200641_netExam.login where username="******" and passwd=" + pass;
MySqlConnection MyConn = new MySqlConnection(mcon);
MySqlCommand MyCommand = new MySqlCommand(Query, MyConn);
MySqlDataReader MyReader;
MyConn.Open();
MyReader = MyCommand.ExecuteReader();
if (MyReader.Read())
{
MyConn.Close();
return true;
}
else
{
MyConn.Close();
return true;
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
return true;
}
}
public int CreateAccountType(Account_Type a)
{
int ret = 0;
using (connect = new MySqlConnection(_connectionString))
{
connect.Open();
using (MySqlTransaction transaction = connect.BeginTransaction())
{
try
{
string query = "NewAccountType";
var cmd = new MySqlCommand(query, connect) { CommandType = CommandType.StoredProcedure };
cmd.Parameters.AddWithValue("AccountName", a.Name);
cmd.Parameters.AddWithValue("PBenifit", a.Benefit);
cmd.Parameters.AddWithValue("PCost", a.Cost);
ret = int.Parse(cmd.ExecuteScalar().ToString());
transaction.Commit();
connect.Close();
}
catch (InvalidOperationException ioException)
{
transaction.Rollback();
connect.Close();
}
}
}
return ret;
}
public int CreatePackage(Package p)
{
int ret = 0;
using (connect = new MySqlConnection(_connectionString))
{
connect.Open();
using (MySqlTransaction transaction = connect.BeginTransaction())
{
try
{
string query = "NewPackage";
var cmd = new MySqlCommand(query, connect) { CommandType = CommandType.StoredProcedure };
cmd.Parameters.AddWithValue("SpecificationID", p.SpecificationID);
cmd.Parameters.AddWithValue("GoodsID", p.GoodsID);
ret = int.Parse(cmd.ExecuteScalar().ToString());
transaction.Commit();
connect.Close();
}
catch (InvalidOperationException ioException)
{
transaction.Rollback();
connect.Close();
}
}
}
return ret;
}
public static int addBussesToDB(List<string> bussesToAdd)
{
using (var connection = new MySqlConnection(DBConnection.getConnectionString()))
{
using (var cmd = connection.CreateCommand())
{
try
{
foreach (var bus in bussesToAdd)
{
cmd.CommandText = string.Format("INSERT INTO Bus (ID, IsDescending, fk_BusRoute) " +
"VALUES({0}, null, null)", bus);
connection.Open();
cmd.ExecuteNonQuery();
connection.Close();
}
return 0;
}
catch (Exception e)
{
if (connection.State == System.Data.ConnectionState.Open)
connection.Close();
Debug.WriteLine(e.Message);
if (e.Message == "Unable to connect to any of the specified MySQL hosts.")
return -3;
else
return -1;
}
}
}
}
public bool UserExists(string username)
{
GetDBConnectionReference.GetDBConnectionClient gdbcc = new GetDBConnectionReference.GetDBConnectionClient();
string conString = gdbcc.GetDBConnectionString();
MySqlConnection db = new MySqlConnection(conString);
db.Open();
MySqlCommand query = new MySqlCommand();
query.Connection = db;
query.CommandText = "SELECT * FROM user WHERE username = @username";
query.Prepare();
query.Parameters.AddWithValue("@username", username);
MySqlDataReader queryResults = query.ExecuteReader();
if(queryResults.Read())
{
queryResults.Close();
queryResults = null;
db.Close();
return true;
}
else
{
queryResults.Close();
queryResults = null;
db.Close();
return false;
}
}
public int CreateBank(Bank b)
{
int ret = 0;
using (connect = new MySqlConnection(_connectionString))
{
connect.Open();
using (MySqlTransaction transaction = connect.BeginTransaction())
{
try
{
string query = "NewBank";
var cmd = new MySqlCommand(query, connect) { CommandType = CommandType.StoredProcedure };
cmd.Parameters.AddWithValue("SortCode", b.SortCode);
cmd.Parameters.AddWithValue("AccountNumber", b.ID);
ret = int.Parse(cmd.ExecuteScalar().ToString());
transaction.Commit();
connect.Close();
}
catch (InvalidOperationException ioException)
{
transaction.Rollback();
connect.Close();
}
}
}
return ret;
}
public int CreateTransaction(Transaction t)
{
int ret = 0;
using (connect = new MySqlConnection(_connectionString))
{
connect.Open();
using (MySqlTransaction transaction = connect.BeginTransaction())
{
try
{
string query = "NewTransaction";
var cmd = new MySqlCommand(query, connect) { CommandType = CommandType.StoredProcedure };
cmd.Parameters.AddWithValue("DateOfOrder", t.DateOfOrder);
cmd.Parameters.AddWithValue("PurchaceID", t.OrderID);
cmd.Parameters.AddWithValue("CustomerID", t.CustomerID);
cmd.Parameters.AddWithValue("BankingID", t.BankID);
ret = int.Parse(cmd.ExecuteScalar().ToString());
transaction.Commit();
connect.Close();
}
catch (InvalidOperationException ioException)
{
transaction.Rollback();
connect.Close();
}
}
}
return ret;
}
public void ChangePassword(User u, String password)
{
using (connect = new MySqlConnection(_connectionString))
{
connect.Open();
using (MySqlTransaction transaction = connect.BeginTransaction())
{
try
{
string query = "ChangePassword";
var cmd = new MySqlCommand(query, connect) { CommandType = CommandType.StoredProcedure };
cmd.Parameters.AddWithValue("pUID", u.username);
cmd.Parameters.AddWithValue("pPwd", password);
cmd.ExecuteNonQuery();
transaction.Commit();
connect.Close();
}
catch (InvalidOperationException ioException)
{
transaction.Rollback();
connect.Close();
}
}
}
}
public static JsonResult GetAllRouteNames()
{
using (var connection = new MySqlConnection(DBConnection.getConnectionString()))
{
using (var cmd = connection.CreateCommand())
{
try
{
cmd.CommandText = "SELECT RouteNumber FROM BusRoute WHERE SubRoute = 0";
connection.Open();
var read = cmd.ExecuteReader();
List<string> RouteNumber = new List<string>();
while (read.Read())
{
RouteNumber.Add(read.GetString(0));
}
connection.Close();
read.Close();
return JConverter.ConvertToJson(RouteNumber);
}
catch (Exception e)
{
connection.Close();
Debug.WriteLine(e.Message);
return null;
}
}
}
}
public static void addgenres(string genre)
{
bool HasRows;
string MyConString = "SERVER=192.168.5.106;" +
"DATABASE=jukebox;" +
"UID=jukebox;" +
"PASSWORD=;";
MySqlConnection connection = new MySqlConnection(MyConString);
connection.Open();
MySqlCommand command = connection.CreateCommand();
MySqlDataReader Reader;
command.CommandText = "SELECT uid FROM genres WHERE genre='" + MySqlEscape(genre.Trim()) + "'";
Reader = command.ExecuteReader();
HasRows = Reader.HasRows;
Reader.Close();
connection.Close();
if (!HasRows)
{
connection.Open();
string sql = "INSERT INTO genres (genre) VALUES ('" + MySqlEscape(genre.Trim()) + "')";
command = new MySqlCommand(sql, connection);
command.ExecuteNonQuery();
connection.Close();
}
}
public MySqlUtility(string csMySqlDB)
{
try
{
this.csConnectionString = csMySqlDB;
oMySqlConnection = new MySqlConnection(csMySqlDB);
//Make sure we can connect
oMySqlConnection.Open();
oMySqlConnection.Close();
}
catch (Exception ex)
{
throw new Exception("Error throw when trying to generate MySQL connection using the supplied connection string. See Inner Exception for details", ex);
}
finally
{
if (oMySqlConnection != null)
{
if (oMySqlConnection.State == System.Data.ConnectionState.Open)
{
oMySqlConnection.Close();
}
}
}
}
internal object SignUp(string id, string password)
{
if (SignName(id) == false)
{
return "the name had been signed";
}
else
{
string sql = string.Format("insert into login set student_id='{0}',password='******'", id, password);
string error = null;
MySqlConnection conn = new MySqlConnection(connStr_local);
try
{
conn = new MySqlConnection(connStr_local);
conn.Open();
MySqlDataAdapter adapter = new MySqlDataAdapter(sql, conn);
// 创建DataSet，用于存储数据.
DataSet testDataSet = new DataSet();
// 执行查询，并将数据导入DataSet.
adapter.Fill(testDataSet, "result_data");
conn.Close();
}
catch (Exception e)
{
conn.Close();
error = e.Message;
Console.WriteLine("insert station error------>{0}", error);
return error;
}
return "1";
}
}
static void Main()
{
string cs = @"server=localhost;port=9306;userid=root;password=;database=testdb";
MySqlConnection conn = null;
try
{
conn = new MySqlConnection(cs);
conn.Open();
MySqlCommand cmd = new MySqlCommand("SELECT * FROM rt", conn);
MySqlDataReader r;
r = cmd.ExecuteReader();
while(r.Read())
{
Console.WriteLine(
r.GetString(0) + " | " +
r.GetString(1) + " | " +
r.GetString(2) + " | " +
r.GetString(3));
}
r.Close();
conn.Close();
} catch (MySqlException ex)
{
Console.WriteLine("Error: {0}", ex.ToString());
} finally
{
if (conn != null)
conn.Close();
}
}
public static bool domysql(string username ,string password)
{
string constr = "server=localhost;User Id=root;password=justice;Database=test";
MySqlConnection mycon = new MySqlConnection(constr);
mycon.Open();
string sqlcommd = "select * from user where username = '******' and password = '******'";
//MySqlCommand mycmd = new MySqlCommand(sqlcommd, mycon);
Console.WriteLine(">>>>>>>>"+sqlcommd);
MySqlDataAdapter ad = new MySqlDataAdapter(sqlcommd, mycon);
DataTable dt = new DataTable();
ad.Fill(dt);//这里也可以把数据查询结果填充到DataSet中
if (dt.Rows.Count > 0)
{
Console.WriteLine("登陆成功！");
mycon.Close();
return true;
}
else {
Console.WriteLine("账号密码错误！");
mycon.Close();
return false;
}
}
public int CreateNewDepartment(Department department)
{
int ret = 0;
using (connect = new MySqlConnection(_connectionString))
{
connect.Open();
using (MySqlTransaction transaction = connect.BeginTransaction())
{
try
{
string query = "NewDepartment";
var cmd = new MySqlCommand(query, connect) { CommandType = CommandType.StoredProcedure };
cmd.Parameters.AddWithValue("DepartmentTitle", department.Title);
cmd.Parameters.AddWithValue("AddressID", department.Address);
cmd.Parameters.AddWithValue("DepartmentHead", department.Head);
ret = int.Parse(cmd.ExecuteScalar().ToString());
transaction.Commit();
connect.Close();
}
catch (InvalidOperationException ioException)
{
transaction.Rollback();
connect.Close();
}
}
}
return ret;
}
public void AddColumn(string tableName, string columnName, string columnDescription)
{
using (Connection = new MySqlConnection(GetConnectionString()))
{
var q1 = string.Format(CheckColumnQuery, tableName, columnName);
using (CheckColumnCommand = new MySqlCommand(q1, Connection))
{
Connection.Open();
var res = int.Parse(Convert.ToString(CheckColumnCommand.ExecuteScalar()));
Connection.Close();
if (res > 0)
{
return;
}
}
var q3 = string.Format(Query, tableName, columnName, columnDescription);
using (Command = new MySqlCommand(q3, Connection))
{
Command.CommandTimeout = 12 * 3600;
Connection.Open();
Command.ExecuteNonQuery();
Connection.Close();
}
var q2 = string.Format(IndexQuery, "Index" + columnName, tableName, columnName);
using (IndexCommand = new MySqlCommand(q2, Connection))
{
Connection.Open();
IndexCommand.ExecuteNonQuery();
Connection.Close();
}
}
}
public static JsonResult GetAllBusses()
{
using (var connection = new MySqlConnection(DBConnection.getConnectionString()))
{
using (var cmd = connection.CreateCommand())
{
try
{
cmd.CommandText = "SELECT ID FROM Bus ORDER BY ID";
List<int> Busses = new List<int>();
connection.Open();
var read = cmd.ExecuteReader();
while (read.Read())
{
Busses.Add(read.GetInt32(0));
}
read.Close();
connection.Close();
return JConverter.ConvertToJson(Busses);
}
catch (Exception e)
{
connection.Close();
Debug.WriteLine(e.Message);
return null;
}
}
}
}
private void registerUser()
{
try {
firstName = textBoxFName.Text;
lastName = textBoxLName.Text;
pass = textBoxPass.Text;
email = textBoxEmail.Text;
street = textBoxStreet.Text;
phone = textBoxPhone.Text;
System.Diagnostics.Debug.WriteLine("SomeText2 " + pass);
String connString = System.Configuration.ConfigurationManager.ConnectionStrings["WebbAppConnString"].ToString();
conn = new MySql.Data.MySqlClient.MySqlConnection(connString);
conn.Open();
queryStr = "";
queryStr = "INSERT INTO customers (email,firstName,lastName,password,address,phone) values('" + email + "','" + firstName + "','" + lastName + "','" + pass + "','" + street + "','" + phone + "')";
cmd = new MySql.Data.MySqlClient.MySqlCommand(queryStr, conn);
cmd.ExecuteReader();
conn.Close();
}catch (Exception e) {
}
System.Diagnostics.Debug.WriteLine("regester user with phone: " + phone);
sms s = new sms();
s.Sendsms("+", "You have been registered att Movie Store");
clearBoxes();
}
protected void submitButtonEventHandler(object sender, EventArgs e)
{
try
{
String connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["WebAppConnString"].ToString();
conn = new MySql.Data.MySqlClient.MySqlConnection(connectionString);
conn.Open();
queryString = "INSERT INTO webapppersonalfit.transaction (progname, userid, amount, timestamp) "
+ "VALUES(?progname, ?userid, ?amount, ?timestamp)";
String date = DateTime.UtcNow.ToString();
cmd = new MySql.Data.MySqlClient.MySqlCommand(queryString, conn);
cmd.Parameters.AddWithValue("?progname", progname);
cmd.Parameters.AddWithValue("?userid", userid);
cmd.Parameters.AddWithValue("?amount", amount);
cmd.Parameters.AddWithValue("?timestamp", date);
cmd.ExecuteReader();
conn.Close();
SetTrainerClientDatabase(progname, trainerid, userid);
Response.Redirect("TransactionConfirmationPage.aspx");
}
catch (Exception ex)
{
}
}
protected void Page_Load(object sender, EventArgs e)
{
string A1 = "", A2 = "", A3 = "";
if (Request.Params["A1"] != null)
{
A1 = Request.Params["A1"];
Response.Write("A1 = " + A1 + "\n");
}
if (Request.Params["A2"] != null)
{
A2 = Request.Params["A2"];
Response.Write("A2 = " + A2 + "\n");
}
if (Request.Params["A3"] != null)
{
A3 = Request.Params["A3"];
Response.Write("A3 = " + A3 + "\n");
}
MySql.Data.MySqlClient.MySqlConnection con = new MySql.Data.MySqlClient.MySqlConnection("Server=42.121.126.238;Database=nnkkdatabase;Uid=root;Pwd=60battle23;CharSet=gb2312");
con.Open();
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand("insert into stats (`time`, A1, A2, A3, IP) values (now(), @A1, @A2, @A3, @IP)", con);
cmd.Parameters.AddWithValue("@A1", A1);
cmd.Parameters.AddWithValue("@A2", A2);
cmd.Parameters.AddWithValue("@A3", A3);
cmd.Parameters.AddWithValue("@IP", Request.UserHostAddress);
cmd.ExecuteNonQuery();
con.Close();
}
public static void EjecutaQueryMySql(string sql)
{
MySql.Data.MySqlClient.MySqlConnection cnn = new MySql.Data.MySqlClient.MySqlConnection(ConfigurationManager.ConnectionStrings["cnnMysql"].ToString());
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(sql, cnn);
cmd.CommandType = CommandType.Text;
cnn.Open();
cmd.ExecuteNonQuery();
cnn.Close();
}
private void LoginWithPasswordHashFunction()
{
String saltHash = null;
try
{
connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["WebAppConnString"].ToString();
conn = new MySql.Data.MySqlClient.MySqlConnection(connectionString);
conn.Open();
String query = "SELECT userID, slowHashSalt, firstname, middlename, lastname, isPT FROM webAppPersonalFit.userregistration WHERE username=?uname";
cmd = new MySql.Data.MySqlClient.MySqlCommand(query, conn);
cmd.Parameters.AddWithValue("?uname", usernameTextBox.Value);
reader = cmd.ExecuteReader();
if (reader.HasRows && reader.Read())
{
String saltHashes = reader.GetString(reader.GetOrdinal("slowHashSalt"));
//Console.WriteLine(saltHashes);
saltHash = saltHashes;
userID = "" + reader.GetInt16(reader.GetOrdinal("userID"));
isPT = Convert.ToInt32(reader.GetByte(reader.GetOrdinal("isPT")));
}
else
{
ValidationTextBoxLabel.Text = "Invalid Username or Password.";
}
if (saltHash != null)
{
bool validUser = PasswordStorage.VerifyPassword(passwordTextBox.Value, saltHash);
if (validUser == true)
{
Session[userID] = userID;
Response.BufferOutput = true;
Server.Transfer("TrainerCatalog.aspx", true);
}
else
{
ValidationTextBoxLabel.Text = "Invalid Username or Password.";
}
}
ValidationTextBoxLabel.Text = "Invalid Username or Password.";
reader.Close();
conn.Close();
}
catch (Exception e)
{
Console.WriteLine(e);
}
}
private void addElement()
{
String connString = System.Configuration.ConfigurationManager.ConnectionStrings["webAppConn"].ToString();
conn = new MySql.Data.MySqlClient.MySqlConnection(connString);
conn.Open();
queryString = "INSERT INTO magazyn.capacitors (Name, Capacity, Passive_Id)" +
"VALUES('NICHICON20','20','2')";
cmd = new MySql.Data.MySqlClient.MySqlCommand(queryString, conn);
cmd.ExecuteReader();
conn.Close();
}
protected void login_click(object sender, EventArgs e)
{
string staffID = LoginStaffID_textbox.Text;
string password = LoginPassword_textbox.Text;
MySql.Data.MySqlClient.MySqlCommand loginCommand1 = new MySql.Data.MySqlClient.MySqlCommand("SELECT * FROM users WHERE StaffID = " + staffID + " AND password = "******";", conn);
MySql.Data.MySqlClient.MySqlDataReader loginReader = null;
conn.Open();
loginReader = loginCommand1.ExecuteReader();
if (loginReader.HasRows)
{
//login was successful
Response.Redirect("Staff.aspx?name=" + loginReader.GetString(0) + "&position=" + loginReader.GetString(1));
}
else
{
//login was unsuccessful
}
conn.Close();
}
public static DataTable RegresaTablaMySql(string sql)
{
MySql.Data.MySqlClient.MySqlConnection cnn = new MySql.Data.MySqlClient.MySqlConnection(ConfigurationManager.ConnectionStrings["cnnMysql"].ToString());
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(sql, cnn);
cmd.CommandType = CommandType.Text;
MySql.Data.MySqlClient.MySqlDataAdapter adpt = new MySql.Data.MySqlClient.MySqlDataAdapter(cmd);
System.Data.DataTable content = new System.Data.DataTable();
cnn.Open();
adpt.Fill(content);
cnn.Close();
return(content);
}
private void GetObtainMarks()
{
this.pr_rpt_gen_btn.Visible = true;
string year = this.pr_gen_year.Value;
string sem = this.pr_gen_sem.Value;
string dept = this.pr_rpt_gen_stddept.Value;
MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(connection);
string queryStr;
MySql.Data.MySqlClient.MySqlCommand cmd;
conn.Open();
try
{
queryStr = "SELECT * FROM student.marks WHERE Year = '" + year + "' AND Semester = '" + sem + "' AND Department = '" + dept + "';";
cmd = new MySql.Data.MySqlClient.MySqlCommand(queryStr, conn);
MySqlDataReader reader = cmd.ExecuteReader();
if (!reader.Read())
{
//this.duty_msg.Visible = true;
reader.Close();
}
else
{
reader.Close();
da = new MySqlDataAdapter();
da.SelectCommand = cmd;
dt = new DataTable();
da.Fill(dt);
view_std_marks.DataSource = dt;
view_std_marks.DataBind();
}
}
catch (Exception)
{
}
finally
{
if (conn.State == System.Data.ConnectionState.Open)
{
conn.Close();
}
}
}
protected void Login1_Authenticate(object sender, AuthenticateEventArgs e)
{
string connectionString = WebConfigurationManager.ConnectionStrings["MySqlConnectionString"].ConnectionString;
MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(connectionString);
//SqlConnection conn = new SqlConnection(connectionString);
string md5password = FormsAuthentication.HashPasswordForStoringInConfigFile(Login1.Password, "MD5");
string sqlstr = "select * from Operator_Organize where LoginName='" + Login1.UserName + "' and Password='******'";
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(sqlstr, conn);
//SqlCommand cmd = new SqlCommand(sqlstr, conn);
try
{
conn.Open();
}
catch (System.Exception ex)
{
Response.Write(ex.Message);
}
finally
{
MySqlDataReader reader = cmd.ExecuteReader();
//SqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
e.Authenticated = true;
Session["OperatorID"] = reader["OperatorID"];
Session["OperatorName"] = reader["OperatorName"];
Session["LoginName"] = reader["LoginName"];
Session["Password"] = reader["Password"];
Session["OrganizeID"] = reader["OrganizeID"];
Session["OrgCode"] = reader["OrgCode"];
Session["OrgName"] = reader["OrgName"];
Session["parentID"] = reader["parentID"];
Session["TreeLevel"] = reader["TreeLevel"];
}
else
{
e.Authenticated = false;
}
reader.Close();
conn.Close();
}
}
public JsonResult VeriCek1()
{
MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection("server = localhost; Port= 3306; userid = root; database = denemedb; charset = utf8; convertzerodatetime = true");
conn.Open();
MySqlCommand cmd = new MySqlCommand("select *from duyurular where duyurular.ID = 1", conn);
MySqlDataReader reader = cmd.ExecuteReader();
reader.Read();
Duyuru duyuru = new Duyuru();
duyuru.ID = reader.GetInt32(0);
duyuru.Baslik = reader.GetString(0);
reader.Close();
cmd.Cancel();
conn.Close();
return(Json(duyuru));
}
public void add_date_firstDay(string date, int line, string first, string sec, string thi, string four, string fiv, string six, string sev, string eig, string nin, string ten, string ele,string twe)
{
DateTime dt = Convert.ToDateTime(date);
//string connect = "datasource = 127.0.0.1; port = 3306;Connection Timeout=30; Min Pool Size=20; Max Pool Size=200; username = root; password = ;";
MySqlConnection conn = new MySqlConnection(connect);
MySqlCommand sda = new MySqlCommand(@"insert into shedulling.tablelayout1 values
('" + dt + "','" + line + "','" + first + "','" + sec + "','" + thi + "','" + four + "','" + fiv + "','" + six + "','" + sev + "','" + eig + "','" + nin + "','" + ten + "', '" + ele + "','"+twe+ "')", conn);
MySqlDataReader reader;
try
{
conn.Open();
reader = sda.ExecuteReader();
while (reader.Read())
{
}
reader.Close();
conn.Close();
conn.Dispose();
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
finally
{
if (conn != null && conn.State == ConnectionState.Open)
{
conn.Close();
}
}
}
