public int InserirPedido(int numCliente, List<Item> pedido)
{
int NumPedido = 0;
int idPedido = 0;
MySqlConnection conn = new MySqlConnection(connectionString);
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = conn;
conn.Open();
cmd.CommandText = "Select Max(numero) + 1 from tb_Pedidos";
NumPedido = int.Parse(cmd.ExecuteScalar().ToString());
cmd.CommandText = "Insert into tb_Pedidos (numero, id_cliente, data) Values(" + NumPedido + "," + numCliente + ", sysdate()); select Max(id) from tb_Pedidos;";
idPedido = int.Parse(cmd.ExecuteScalar().ToString());
foreach (Item item in pedido)
{
cmd.CommandText = "insert into tb_items (nome, descricao, preco, quantidade, id_pedido, urlImagem) Values ('" + item.descricao + "', Null,"+ item.preco.ToString().Replace(",",".") + "," + item.quantidade + "," + idPedido + ", Null);";
cmd.ExecuteNonQuery();
}
conn.Close();
return NumPedido;
}
public void CallingStoredProcWithOnlyExecPrivs()
{
if (Version < new Version(5, 0)) return;
execSQL("CREATE PROCEDURE spTest() BEGIN SELECT 1; END");
execSQL("CREATE PROCEDURE spTest2() BEGIN SELECT 1; END");
suExecSQL(String.Format("GRANT USAGE ON `{0}`.* TO 'abc'@'%' IDENTIFIED BY 'abc'", database0));
try
{
suExecSQL(String.Format("GRANT SELECT ON `{0}`.* TO 'abc'@'%'", database0));
suExecSQL(String.Format("GRANT EXECUTE ON PROCEDURE `{0}`.spTest TO abc", database0));
string connStr = GetConnectionString("abc", "abc", true) + "; check parameters=false";
using (MySqlConnection c = new MySqlConnection(connStr))
{
c.Open();
MySqlCommand cmd = new MySqlCommand("spTest", c);
cmd.CommandType = CommandType.StoredProcedure;
object o = null;
Assert.DoesNotThrow(delegate { o = cmd.ExecuteScalar(); });
Assert.AreEqual(1, o);
cmd.CommandText = "spTest2";
Assert.Throws(typeof(MySqlException), delegate { cmd.ExecuteScalar(); });
}
}
finally
{
suExecSQL("DROP USER abc");
}
}
public void UpdateTest()
{
execSQL("CREATE TABLE Test (id int NOT NULL, name VARCHAR(100))");
execSQL("INSERT INTO Test (id,name) VALUES(10, 'Test')");
execSQL("INSERT INTO Test (id,name) VALUES(11, 'Test2')");
// do the update
MySqlCommand cmd = new MySqlCommand("UPDATE Test SET name='Test3' WHERE id=10 OR id=11", conn);
MySqlConnection c = cmd.Connection;
Assert.AreEqual(conn, c);
int cnt = cmd.ExecuteNonQuery();
Assert.AreEqual(2, cnt);
// make sure we get the right value back out
cmd.CommandText = "SELECT name FROM Test WHERE id=10";
string name = (string)cmd.ExecuteScalar();
Assert.AreEqual("Test3", name);
cmd.CommandText = "SELECT name FROM Test WHERE id=11";
name = (string)cmd.ExecuteScalar();
Assert.AreEqual("Test3", name);
// now do the update with parameters
cmd.CommandText = "UPDATE Test SET name=?name WHERE id=?id";
cmd.Parameters.Add( new MySqlParameter("?id", 11));
cmd.Parameters.Add( new MySqlParameter("?name", "Test5"));
cnt = cmd.ExecuteNonQuery();
Assert.AreEqual(1, cnt, "Update with Parameters Count");
// make sure we get the right value back out
cmd.Parameters.Clear();
cmd.CommandText = "SELECT name FROM Test WHERE id=11";
name = (string)cmd.ExecuteScalar();
Assert.AreEqual("Test5", name);
}
public void InsertTest()
{
execSQL("CREATE TABLE Test (id int NOT NULL, name VARCHAR(100))");
// do the insert
MySqlCommand cmd = new MySqlCommand("INSERT INTO Test (id,name) VALUES(10,'Test')", conn);
int cnt = cmd.ExecuteNonQuery();
Assert.AreEqual( 1, cnt, "Insert Count" );
// make sure we get the right value back out
cmd.CommandText = "SELECT name FROM Test WHERE id=10";
string name = (string)cmd.ExecuteScalar();
Assert.AreEqual( "Test", name, "Insert result" );
// now do the insert with parameters
cmd.CommandText = "INSERT INTO Test (id,name) VALUES(?id, ?name)";
cmd.Parameters.Add( new MySqlParameter("?id", 11));
cmd.Parameters.Add( new MySqlParameter("?name", "Test2"));
cnt = cmd.ExecuteNonQuery();
Assert.AreEqual( 1, cnt, "Insert with Parameters Count" );
// make sure we get the right value back out
cmd.Parameters.Clear();
cmd.CommandText = "SELECT name FROM Test WHERE id=11";
name = (string)cmd.ExecuteScalar();
Assert.AreEqual( "Test2", name, "Insert with parameters result" );
}
public void InsertTest()
{
try
{
// do the insert
MySqlCommand cmd = new MySqlCommand("INSERT INTO Test (id,name) VALUES(10,'Test')", conn);
int cnt = cmd.ExecuteNonQuery();
Assert.AreEqual( 1, cnt, "Insert Count" );
// make sure we get the right value back out
cmd.CommandText = "SELECT name FROM Test WHERE id=10";
string name = (string)cmd.ExecuteScalar();
Assert.AreEqual( "Test", name, "Insert result" );
// now do the insert with parameters
cmd.CommandText = "INSERT INTO Test (id,name) VALUES(?id, ?name)";
cmd.Parameters.Add( new MySqlParameter("?id", 11));
cmd.Parameters.Add( new MySqlParameter("?name", "Test2"));
cnt = cmd.ExecuteNonQuery();
Assert.AreEqual( 1, cnt, "Insert with Parameters Count" );
// make sure we get the right value back out
cmd.Parameters.Clear();
cmd.CommandText = "SELECT name FROM Test WHERE id=11";
name = (string)cmd.ExecuteScalar();
Assert.AreEqual( "Test2", name, "Insert with parameters result" );
}
catch (MySqlException ex)
{
Assert.Fail( ex.Message );
}
}
public string ExecuteQuery(string Query)
{
connection.Open();
MySqlCommand command = new MySqlCommand(Query);
command.Connection = this.connection;
String res = "";
if (command.ExecuteScalar() != null) res = command.ExecuteScalar().ToString();
connection.Close();
if (res == "") res = "-1";
return res;
}
public async Task <string> GetAccountAuth(string login, string pass)
{
try
{
new I18N.West.CP1250();
string Connect = "Database=u0354899_diplom;Data Source=31.31.196.162;User Id=u0354899_vlad;Password=vlad19957;charset=utf8";
MySql.Data.MySqlClient.MySqlConnection myConnection = new MySql.Data.MySqlClient.MySqlConnection(Connect);
MySql.Data.MySqlClient.MySqlCommand myCommand = new MySql.Data.MySqlClient.MySqlCommand();
myConnection.Open();
myCommand.Connection = myConnection;
myCommand.CommandText = string.Format("SELECT login FROM Student WHERE login='******' AND password='******' ", login, pass); //запрос: если есть такой логин в таблице
myCommand.Prepare(); //подготавливает строку
myCommand.ExecuteNonQuery(); //выполняет запрос
string LoginGlobal = (string)myCommand.ExecuteScalar(); //результат запроса
if (LoginGlobal == login)
{
myCommand.CommandText = string.Format("SELECT id FROM Student WHERE login='******'", login); //запрос: если есть такой логин в таблице
myCommand.Prepare(); //подготавливает строку
myCommand.ExecuteNonQuery(); //выполняет запрос
Student.idStudent = (int)myCommand.ExecuteScalar(); //результат запроса
myConnection.Close();
MainPage.AuthStudent = true;
return(await Task <string> .FromResult("Вы зашли как студент"));
}
else
{
myCommand.CommandText = string.Format("SELECT login FROM teacher WHERE login='******' AND password='******' ", login, pass); //запрос: если есть такой логин в таблице
myCommand.Prepare(); //подготавливает строку
myCommand.ExecuteNonQuery(); //выполняет запрос
LoginGlobal = (string)myCommand.ExecuteScalar(); //результат запроса
if (LoginGlobal == login)
{
myConnection.Close();
MainPage.AuthTeacher = true;
return(await Task <string> .FromResult("Вы зашли как преподаватель"));
}
else
{
myConnection.Close();
return(await Task <string> .FromResult("Логин или пароль не совпадают"));
}
}
}
catch (Exception ex)
{
return(await Task <string> .FromResult(ex.Message));
}
}
public static string AddUser(Class.User user)
{
StartConnection();
try
{
using (MySqlCommand = new MySqlCommand("insert into user (user.ID,user.email,user.name,user.password) values('',@email,@name,@password)", MySqlConnection))
{
MySqlCommand.Parameters.AddWithValue("@email", user.Email.ToLower());
MySqlCommand.Parameters.AddWithValue("@name", user.Name);
MySqlCommand.Parameters.AddWithValue("@password", user.Password);
MySqlConnection.Open();
var result = MySqlCommand.ExecuteScalar();
if (MySqlConnection.State != System.Data.ConnectionState.Closed)
{
MySqlConnection.Close();
}
if (result != null)
{
return("User has been added to database");
}
else
{
return("No database result Methods.AddUser()");
}
}
}
catch (Exception ex)
{
if (MySqlConnection.State != System.Data.ConnectionState.Closed)
{
MySqlConnection.Close();
}
return("Something went wrong in Methods.AddUser()");
}
}
//Saves a Desease
public bool Save(User user, Desease desease, int bsnNumber)
{
using (MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand("insert into deseases (ID,BSNNumber,Description,DeterminerID,Date,DeclaredHealthy,DeclaredHealthyDate) values(null,@bsnnumber,@description,@determiner,@date,false,@dateextra)", con))
{
try
{
con.Open();
cmd.Parameters.AddWithValue("@description", desease.Description);
cmd.Parameters.AddWithValue("@bsnnumber", bsnNumber);
cmd.Parameters.AddWithValue("@determiner", desease.determiner);
cmd.Parameters.AddWithValue("@date", desease.date);
cmd.Parameters.AddWithValue("@dateextra", DateTime.Now);
var result = cmd.ExecuteScalar();
if (result != null)
{
}
}
catch (Exception ex)
{
if (con.State != System.Data.ConnectionState.Closed)
{
con.Close();
}
throw new Exception(ex.Message);
}
if (con.State != System.Data.ConnectionState.Closed)
{
con.Close();
}
}
return(true);
}
private void btnMySQLConnect_Click(object sender, EventArgs e)
{
using (MySqlConnection conn = new MySqlConnection(connectionString))
{
try
{
if (conn.State == ConnectionState.Closed)
{
conn.Open();
string query = "SELECT VERSION()";
MySqlCommand cmd = new MySqlCommand(query, conn);
string version = Convert.ToString(cmd.ExecuteScalar());
//Console.WriteLine("MySQL version : {0}", version);
MessageBox.Show("Connection Established!\n" + "MySQL Version: " + version, "Message",
MessageBoxButtons.OK, MessageBoxIcon.Information);
//label1.Text = "Connection Established!\n" + "MySQL Version: " + version;
}
else
{
MessageBox.Show("Connection Already Open.", "Message",
MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
catch (Exception ex)
{
MessageBox.Show("Connection Error!\n" + ex.Message, "Message",
MessageBoxButtons.OK, MessageBoxIcon.Error);
//label1.Text = "Connection Error!\n" + ex.Message;
}
}
}
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;
}
void IDB.Insert(object obj)
{
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand();
cmd.Connection = conn;
cmd.Transaction = tran;
cmd.CommandTimeout = 8000;
//
string sql = "";
string fields = "";
string values = "";
foreach (System.Reflection.PropertyInfo p in obj.GetType().GetProperties())
{
if (fields == "")
{
fields += p.Name;
values += "@" + p.Name;
}
else
{
fields += "," + p.Name;
values += "," + "@" + p.Name;
}
}
sql = "insert into " + ReflectionHelper.GetDataTableNameByModel(obj) + "(" + fields + ")values(" + values + ")";
cmd.CommandText = sql;
cmd.Parameters.AddRange(ReflectionHelper.ModelToSqlParameters(obj));
//
cmd.ExecuteScalar();
}
protected void showgrpdetails(object sender, EventArgs e)
{
string clsid = (String)Session["clsid"];
//string tclsid = "cls_" + clsid;
string tgcls = "grp_" + clsid;
oy.Text = tgcls;
infogrp.Visible = true;
grpsizeinfo.Visible = true;
te.Visible = true;
grpnumber.Visible = true;
selctgrp.Visible = true;
String connstring = System.Configuration.ConfigurationManager.ConnectionStrings["WebAppConString"].ToString();
connection = new MySql.Data.MySqlClient.MySqlConnection(connstring);
connection.Open();
querystr = "select count(*) from " + tgcls;
cmd = new MySql.Data.MySqlClient.MySqlCommand(querystr, connection);
long num = (long)cmd.ExecuteScalar();
connection.Close();
double numm = Convert.ToDouble(num);
double noofgrps = Math.Floor(numm / 5);
infogrp.Text = "The limit of the group is 6";
grpsizeinfo.Text = "There are groups from 1 to" + noofgrps;
}
private static void AddBook(string bookName, DateTime datePublish, long isbn, string author)
{
using (var mySqlConnection = new MySqlConnection(ConnectionString))
{
mySqlConnection.Open();
using (mySqlConnection)
{
var bookStr = "INSERT INTO books " + "(titleBook, publishDate, ISBN) VALUES " + "(@title, @date, @isbn)";
var addBook = new MySqlCommand(bookStr, mySqlConnection);
addBook.Parameters.AddWithValue("@title", bookName);
addBook.Parameters.AddWithValue("@date", datePublish);
addBook.Parameters.AddWithValue("@isbn", isbn);
addBook.ExecuteNonQuery();
var cmdSelectIdentity = new MySqlCommand("SELECT @@Identity", mySqlConnection);
var insertedRecordId = (ulong)cmdSelectIdentity.ExecuteScalar();
var authorStr = "INSERT INTO authors " + "(Books_idBooks, AuthorName) VALUES " + "(@bookId, @name)";
var addAuthor = new MySqlCommand(authorStr, mySqlConnection);
addAuthor.Parameters.AddWithValue("@bookId", (int)insertedRecordId);
addAuthor.Parameters.AddWithValue("@name", author);
addAuthor.ExecuteNonQuery();
}
}
}
public static MediaEvent Info(int id)
{
//p_info_playlist_details
MediaEvent m = null;
try
{
using (MySqlConnection conn = new MySqlConnection(App.setting.connectString))
{
conn.Open();
string query = "`p_info_playlist_details`";
using (MySqlCommand cmd = new MySqlCommand(query, conn))
{
cmd.Parameters.Add(new MySqlParameter("@_id", MySqlDbType.Int32) { Direction = System.Data.ParameterDirection.Input, Value = id });
cmd.CommandType = System.Data.CommandType.StoredProcedure;
var tmp = cmd.ExecuteScalar();
if (Convert.ToInt32(tmp) > 0)
{
using (MySqlDataReader reader = cmd.ExecuteReader())
{
m = reader.toMediaEvent();
}
}
};
conn.Close();
};
}
catch (Exception)
{
}
return m;
}
private void get_1k_resule(String Connection)
{
MySql.Data.MySqlClient.MySqlConnection conn;
MySql.Data.MySqlClient.MySqlCommand cmd;
conn = new MySql.Data.MySqlClient.MySqlConnection();
cmd = new MySql.Data.MySqlClient.MySqlCommand();
conn.ConnectionString = Connection;
try
{
conn.Open();
cmd.Connection = conn;
cmd.CommandText = "SELECT * FROM `laneheartbeat`;";
cmd.Prepare();
MySqlDataAdapter sda = new MySqlDataAdapter("SELECT * FROM `laneheartbeat` LIMIT 500", conn);
Log_Textblock.Text = cmd.ExecuteScalar().ToString();
DataSet ds = new DataSet();
ds.Clear();
DataTable dt = new DataTable();
sda.Fill(ds, "dt");
Result_DataGrid.DataContext = ds;
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
MessageBox.Show("Error " + ex.Number + " has occurred: " + ex.Message,
"Error");
}
}
private void btn_save_Click(object sender, EventArgs e)
{
connection = new MySqlConnection(ConnectionString);
if (txt_fname.Text == "" || txt_lname.Text == "" || mcmb_status.Text == "")
{
string myStringVariable1 = string.Empty;
MetroMessageBox.Show(this, "Field is Empty");
return;
}
connection.Open();
string str = "Select count(*) from employee_info where em_fname = '" + txt_fname.Text + "' and em_lname = '" + txt_lname.Text + "'";
MySqlCommand cmd = new MySqlCommand(str, connection);
int userExist = Convert.ToInt32(cmd.ExecuteScalar());
if (userExist > 0)
{
MetroMessageBox.Show(this, "Employee already exist");
btn_cancel.PerformClick();
return;
}
connection.Close();
Insert();
}
public static long ExecuteScalarLong(MySqlCommand cmd, string sql)
{
long l = 0;
cmd.CommandText = sql;
long.TryParse(cmd.ExecuteScalar() + "", out l);
return l;
}
public int retrieveAlbumId()
{
DBConnect connection = new DBConnect();
string query = "SELECT albumId FROM Album WHERE albumName LIKE @albumName;";
try
{
MySqlCommand cmd = new MySqlCommand(query, connection.OpenConnection());
cmd.CommandText = query;
cmd.Prepare();
cmd.Parameters.AddWithValue("@albumName", this.albumName);
albumId = int.Parse(cmd.ExecuteScalar() + "");
cmd.ExecuteNonQuery();
connection.CloseConnection();
return albumId;
}
catch (Exception ex)
{
return -1;
}
}
public void UpdateAllRows()
{
MySqlCommand cmd = new MySqlCommand("SELECT COUNT(*) FROM toys", conn);
object count = cmd.ExecuteScalar();
using (testEntities context = new testEntities())
{
foreach (Toy t in context.Toys)
t.Name = "Top";
context.SaveChanges();
}
cmd.CommandText = "SELECT COUNT(*) FROM Toys WHERE name='Top'";
object newCount = cmd.ExecuteScalar();
Assert.AreEqual(count, newCount);
}
public void ProcedureFromCache()
{
return;
if (Version < new Version(5, 0)) return;
execSQL("DROP PROCEDURE IF EXISTS spTest");
execSQL("CREATE PROCEDURE spTest(id int) BEGIN END");
PerformanceCounter hardQuery = new PerformanceCounter(
".NET Data Provider for MySQL", "HardProcedureQueries", true);
PerformanceCounter softQuery = new PerformanceCounter(
".NET Data Provider for MySQL", "SoftProcedureQueries", true);
long hardCount = hardQuery.RawValue;
long softCount = softQuery.RawValue;
MySqlCommand cmd = new MySqlCommand("spTest", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("?id", 1);
cmd.ExecuteScalar();
Assert.AreEqual(hardCount + 1, hardQuery.RawValue);
Assert.AreEqual(softCount, softQuery.RawValue);
hardCount = hardQuery.RawValue;
MySqlCommand cmd2 = new MySqlCommand("spTest", conn);
cmd2.CommandType = CommandType.StoredProcedure;
cmd2.Parameters.AddWithValue("?id", 1);
cmd2.ExecuteScalar();
Assert.AreEqual(hardCount, hardQuery.RawValue);
Assert.AreEqual(softCount + 1, softQuery.RawValue);
}
object IDB.ExecuteScalar(string sql, System.Data.IDbDataParameter[] pars, CommandType cmdType)
{
MySql.Data.MySqlClient.MySqlConnection conn = this.GetNewConn();
try
{
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand();
cmd.Connection = conn;
cmd.CommandTimeout = 8000;
cmd.CommandType = cmdType;
cmd.CommandText = sql;
if (pars != null)
{
cmd.Parameters.AddRange(pars);
}
//
conn.Open();
return(cmd.ExecuteScalar());
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
}
}
/// <summary>
/// Saves a medication
/// </summary>
/// <param name="user"></param>
public bool Save(User user, Medication medication, int BSNNumber)
{
using (MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand("insert into medications (medications.ID,medications.Description,medications.HandedOut,medications.HandedOutDate,medications.ExpirationDate,medications.BSNNumber,medications.HandedOutByID,medications.Determiner) values(null,@description,0,@dateextra,@dateextra1,@bsnnumber,0,@determiner)", con))
{
try
{
con.Open();
cmd.Parameters.AddWithValue("@description", medication.Description);
cmd.Parameters.AddWithValue("@bsnnumber", BSNNumber);
cmd.Parameters.AddWithValue("@determiner", medication.Determiner);
cmd.Parameters.AddWithValue("@dateextra", DateTime.Now);
cmd.Parameters.AddWithValue("@dateextra1", DateTime.Now);
var result = cmd.ExecuteScalar();
if (result != null)
{
}
}
catch (Exception ex)
{
if (con.State != System.Data.ConnectionState.Closed)
{
con.Close();
}
throw new Exception(ex.Message);
}
if (con.State != System.Data.ConnectionState.Closed)
{
con.Close();
}
}
return(true);
}
protected void selectthegrp(object sender, EventArgs e)
{
string clsid = (String)Session["clsid"];
string tgrpid = "grp_" + clsid;
Int32 mygrp = Convert.ToInt32(grpnumber.Text);
int stuid = (Int32)Session["stuid"];
String connstring = System.Configuration.ConfigurationManager.ConnectionStrings["WebAppConString"].ToString();
connectiong = new MySql.Data.MySqlClient.MySqlConnection(connstring);
connectiong.Open();
querystr1 = "select count(*) from " + tgrpid + " where groupid=" + mygrp + "";
cmd = new MySql.Data.MySqlClient.MySqlCommand(querystr1, connectiong);
long check = (long)cmd.ExecuteScalar();
Int32 checks = Convert.ToInt32(check);
if (checks < 6)
{
querystr = "update " + tgrpid + " set groupid='" + mygrp + "' where uhclid=" + stuid + "";
cmd = new MySql.Data.MySqlClient.MySqlCommand(querystr, connectiong);
reader = cmd.ExecuteReader();
reader.Close();
Int32 num = Convert.ToInt32(grpnumber.Text);
show.Text = "Your group number is " + num;
}
else
{
show.Text = "The maximum limit of group is only 6..Please select other group!!";
}
}
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 bool Save(User user, File file, int userid)
{
using (MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand("insert into files (files.ID,files.fileName,files.filePath,files.userID,files.fileDescription,files.Date ) values(null,@filename,@filepath,@userid,@filedesc,@date)", con))
{
try
{
con.Open();
cmd.Parameters.AddWithValue("@filename", file.FileName);
cmd.Parameters.AddWithValue("@filepath", Uri.EscapeUriString(file.FilePath));
cmd.Parameters.AddWithValue("@userid", userid);
cmd.Parameters.AddWithValue("@filedesc", file.Description);
cmd.Parameters.AddWithValue("@date", DateTime.Now);
var result = cmd.ExecuteScalar();
if (result != null)
{
}
}
catch (Exception ex)
{
if (con.State != System.Data.ConnectionState.Closed)
{
con.Close();
}
throw new Exception(ex.Message);
}
if (con.State != System.Data.ConnectionState.Closed)
{
con.Close();
}
}
return(true);
}
private void button5_Click(object sender, EventArgs e)
{
try
{
string Connect = "Database=vlad_m;Data Source=192.168.27.79;User Id=vlad_m;charset=cp1251;Password=vlad19957";
MySql.Data.MySqlClient.MySqlConnection myConnection = new MySql.Data.MySqlClient.MySqlConnection(Connect);
MySql.Data.MySqlClient.MySqlCommand myCommand = new MySql.Data.MySqlClient.MySqlCommand();
myConnection.Open();
myCommand.Connection = myConnection;
myCommand.CommandText = string.Format("TRUNCATE TABLE export_double"); //запрос: если есть такой логин в таблице
myCommand.Prepare(); //подготавливает строку
myCommand.ExecuteNonQuery(); //выполняет запрос
// добавить проверку на уникальность
myCommand.CommandText = string.Format("INSERT INTO export_double select id_dog, count(*) from import_double group by id_dog having count(*) > 1"); //запрос: если есть такой логин в таблице
myCommand.Prepare(); //подготавливает строку
myCommand.ExecuteNonQuery(); //выполняет запрос
myCommand.CommandText = string.Format("select count(id_dog) from export_double"); //запрос: если есть такой логин в таблице
myCommand.Prepare(); //подготавливает строку
myCommand.ExecuteNonQuery(); //выполняет запрос
int count = Convert.ToInt32(myCommand.ExecuteScalar());
myConnection.Close();
MessageBox.Show("Все export_double! " + count);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public bool AutenticarUsuario(Usuario u)
{
try
{
MySqlConnection conn = new ConexaoBancoMySQL().getConnection();
conn = new MySqlConnection(connectionString);
String validarUsuario = "SELECT * FROM cadastro_usuario WHERE nome_usuario = @nome AND senha_usuario = MD5(@senha) AND ativo = 's';";
conn.Open();
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(validarUsuario, conn);
cmd.Parameters.Add(new MySql.Data.MySqlClient.MySqlParameter("nome", u.Nome));
cmd.Parameters.Add(new MySql.Data.MySqlClient.MySqlParameter("senha", u.Senha));
cmd.Parameters.Add(new MySql.Data.MySqlClient.MySqlParameter("ativo", u.Ativo));
int retorno = Convert.ToInt32(cmd.ExecuteScalar());
cmd.Prepare();
cmd.ExecuteNonQuery();
conn.Close();
if (retorno > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (Exception ex)
{
MessageBox.Show("Dados incorretos, informe novamente! " + ex.ToString());
return(false);
}
}
public void saveStatus()
{
//p_save_status_media
try
{
using (MySqlConnection conn = new MySqlConnection(App.setting.connectString))
{
conn.Open();
string query = "`p_save_status_media`";
using (MySqlCommand cmd = new MySqlCommand(query, conn))
{
cmd.Parameters.Add(new MySqlParameter("@_media_id", MySqlDbType.Int32) { Direction = System.Data.ParameterDirection.Input, Value = this.ID });
cmd.Parameters.Add(new MySqlParameter("@_status", MySqlDbType.Int16) { Direction = System.Data.ParameterDirection.Input, Value = this.Status });
//_status
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.ExecuteScalar();
};
conn.Close();
};
}
catch (Exception)
{
}
}
public UInt64 InsertAccident(String CommandText)
{
UInt64 temp = 0;
using (MySqlConnection ManagerConn = new MySqlConnection(ConnectionString))
{
MySqlCommand command = new MySqlCommand();
command.CommandText = CommandText + "SELECT @@IDENTITY;";
Console.WriteLine(command.CommandText);
LogManager.Write(command.CommandText, true);
command.Connection = ManagerConn;
try
{
command.Connection.Open();
temp = (UInt64)command.ExecuteScalar();
}
catch (MySqlException ex)
{
Console.WriteLine("Ошибка работы с БД(InsertAccident): \r\n{0}", ex.ToString());
LogManager.Write("Ошибка работы с БД(InsertAccident): \r\n" + ex.Message, true);
}
finally
{
command.Connection.Close();
ManagerConn.Close();
}
return temp;
}
}
public void loadData()
{
try
{
enableButtons();
lviewAccount.Items.Clear();
con.ConnectionString = conString;
con.Open();
String query = "SELECT cashierid, cashieruser, isadmin, cashierfname, cashiermname, cashierlname, datejoined FROM cashieraccount";
MySqlCommand cmd = new MySqlCommand(query, con);
cmd.ExecuteScalar();
MySqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
ListViewItem item = new ListViewItem(rdr["cashierid"].ToString());
item.SubItems.Add(rdr["cashieruser"].ToString());
item.SubItems.Add(rdr["isadmin"].ToString());
item.SubItems.Add(rdr["cashierfname"].ToString());
item.SubItems.Add(rdr["cashiermname"].ToString());
item.SubItems.Add(rdr["cashierlname"].ToString());
item.SubItems.Add(rdr["datejoined"].ToString());
lviewAccount.Items.Add(item);
}
con.Close();
}
catch (Exception)
{
MessageBox.Show("Your Database was NOT Successfully Establish", "SalesMate", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void button1_Click(object sender, EventArgs e)
{
using (MySqlConnection con = new MySqlConnection(connectionString))
{
try
{
con.Open();
string query = "SELECT VERSION()";
MySqlCommand cmd = new MySqlCommand(query, con);
string version = Convert.ToString(cmd.ExecuteScalar());
//Console.WriteLine("MySQL version : {0}", version);
label1.Text = "Connection Established!\n" + "MySQL Version: " + version;
}
catch (Exception ex)
{
label1.Text = "Connection Error!\n" + ex.Message;
}
//I am using 'using', so wouldn't need the finally block, for the using function
//will close the connection automatically.
//finally
//{
// con.close();
//}
}
}
public static Boolean Autenticar(string login, string pass)
{
//consulta a la base de datos (administrador)
string sql = @"SELECT COUNT(*)
FROM Persona
WHERE Login = @login AND password = @pass";
//cadena conexion
//using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["default"].ToString()))
using (MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["MySql"].ToString()))
{
conn.Open();//abrimos conexion
//SqlCommand cmd = new SqlCommand(sql, conn); //ejecutamos la instruccion
MySqlCommand cmd = new MySqlCommand(sql, conn); //ejecutamos la instruccion
cmd.Parameters.AddWithValue("@login", login); //enviamos los parametros
cmd.Parameters.AddWithValue("@pass", pass);
int count = Convert.ToInt32(cmd.ExecuteScalar()); //devuelve la fila afectada
if (count > 0)
return true;
return false;
}
}
public int Count(string table)
{
//This counts the numbers of entries in a table and returns it as an integear
//Example: SELECT Count(*) FROM names
//Code: int myInt = MySQLClient.Count("names");
string query = "SELECT Count(*) FROM " + table + "";
int Count = -1;
if (this.Open() == true)
{
try
{
MySqlCommand cmd = new MySqlCommand(query, conn);
Count = int.Parse(cmd.ExecuteScalar() + "");
this.Close();
}
catch { this.Close(); }
return Count;
}
else
{
return Count;
}
}
public bool Exists(string SQLString)
{
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
using (MySqlCommand cmd = new MySqlCommand(SQLString, connection))
{
try
{
connection.Open();
object obj = cmd.ExecuteScalar();
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
return false;
}
else
{
return true;
}
}
catch (MySql.Data.MySqlClient.MySqlException e)
{
connection.Close();
throw new Exception(e.Message);
}
finally
{
cmd.Dispose();
connection.Close();
}
}
}
}
// returns new identity seed if returnSeed==True
public static object InsertRecord(string sql, bool returnSeed)
{
if (TestCon())
{
using (var connection = Database.Create())
{
using (var cmd = new MySqlCommand(sql, connection))
{
try
{
connection.Open();
if (returnSeed)
{
return cmd.ExecuteScalar();
}
else
{
cmd.ExecuteNonQuery();
return "";
}
// return (returnSeed) ? cmd.ExecuteScalar() : cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
return Functions.FormatReturn(-1, ex.Message);
}
}
}
}
else
{
return Functions.FormatReturn(-1, "Unable to connect to database.");
}
}
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 int CreateUser(User u)
{
int ret = 0;
using (connect = new MySqlConnection(_connectionString))
{
connect.Open();
using (MySqlTransaction transaction = connect.BeginTransaction())
{
try
{
string query = "NewUserAccount";
var cmd = new MySqlCommand(query, connect) { CommandType = CommandType.StoredProcedure };
cmd.Parameters.AddWithValue("PUsername", u.username);
cmd.Parameters.AddWithValue("PPWD", u.password);
cmd.Parameters.AddWithValue("EmployeeID", u.EmployeeID);
ret = int.Parse(cmd.ExecuteScalar().ToString());
transaction.Commit();
connect.Close();
}
catch (InvalidOperationException ioException)
{
transaction.Rollback();
connect.Close();
}
}
}
return ret;
}
public static int GetNumberOfRowsThatContainAValue(int teamNumber)
{
int numberOfRows = 0;
try
{
string mySqlConnectionString = MakeMySqlConnectionString();
var conn = new MySqlConnection {ConnectionString = mySqlConnectionString};
string mySQLCommantText = String.Format("SELECT COUNT(*) FROM {0} WHERE TeamNumber={1}", Program.selectedEventName, teamNumber);
using (var cmd = new MySqlCommand(mySQLCommantText, conn))
{
conn.Open();
numberOfRows = int.Parse(cmd.ExecuteScalar().ToString());
conn.Close();
cmd.Dispose();
return numberOfRows;
}
}
catch (MySqlException ex)
{
Console.WriteLine("Error Code: " + ex.ErrorCode);
Console.WriteLine(ex.Message);
}
return numberOfRows;
}
public static int GetNumberOfRowsInATable()
{
int numberOfRows = 0;
try
{
string mySqlConnectionString = MakeMySqlConnectionString();
var conn = new MySqlConnection {ConnectionString = mySqlConnectionString};
using (var cmd = new MySqlCommand("SELECT COUNT(*) FROM " + Program.selectedEventName, conn))
{
conn.Open();
numberOfRows = int.Parse(cmd.ExecuteScalar().ToString());
conn.Close();
cmd.Dispose();
return numberOfRows;
}
}
catch (MySqlException ex)
{
Console.WriteLine("Error Code: " + ex.ErrorCode);
Console.WriteLine(ex.Message);
ConsoleWindow.WriteLine("Error Code: " + ex.ErrorCode);
ConsoleWindow.WriteLine(ex.Message);
}
return numberOfRows;
}
public int count(string pTags, int pTagCount, DateTime pFrom, DateTime pTo)
{
try
{
int _count = 0;
MySqlCommand _adapter = new MySqlCommand("call spCountImages(\"" + pTags + "\"," + pTagCount + ",'" + string.Format("{0:yyyy-MM-dd HH:mm:ss}", pFrom) + "','" + string.Format("{0:yyyy-MM-dd HH:mm:ss}", pTo) + "')", GlobalVariables.goMySqlConnection);
try
{
try
{
_count = int.Parse(_adapter.ExecuteScalar().ToString());
}
catch
{
_count = 0;
}
return _count;
}
catch (Exception ex)
{
throw ex;
}
finally
{
_adapter.Dispose();
}
}
catch (Exception ex)
{
throw ex;
}
}
public async Task <bool> CheckTestForStudent()
{
bool check = false;
int test = 0;
try
{
new I18N.West.CP1250();
string Connect = "Database=u0354899_diplom;Data Source=31.31.196.162;User Id=u0354899_vlad;Password=vlad19957;charset=utf8";
MySql.Data.MySqlClient.MySqlConnection myConnection = new MySql.Data.MySqlClient.MySqlConnection(Connect);
MySql.Data.MySqlClient.MySqlCommand myCommand = new MySql.Data.MySqlClient.MySqlCommand();
myConnection.Open();
myCommand.Connection = myConnection;
myCommand.CommandText = string.Format("SELECT count(*) From test_history WHERE idstudent = '{0}' AND idtest = '{1}'", Student.idStudent, Student.idTest); //запрос: если есть такой логин в таблице
myCommand.Prepare(); //подготавливает строку
myCommand.ExecuteNonQuery(); //выполняет запрос
test = Convert.ToInt32(myCommand.ExecuteScalar()); //результат запроса
if (test == 0)
{
check = true;
}
else
{
check = false;
}
myConnection.Close();
}
catch (Exception e)
{
Console.Write(e.Message);
}
return(await Task <bool> .FromResult(check));
}
public int InsertIntoDatabase(String sql)
{
try
{
//string connStr = "server=172.16.58.71;port=3306;user=root;database=crawler;password=a;";
string connStr = "server=208.11.220.249;port=3306;user=suman123456789;database=tgmc11cfb;password=internet;";
MySqlCommand cmd;
MySqlConnection conn = new MySqlConnection(connStr);
conn.Open();
cmd = new MySqlCommand(sql + ";SELECT LAST_INSERT_ID()", conn);
int id = Convert.ToInt32(cmd.ExecuteScalar());
//em1.Status("No Error : Data" + id);
return (id);
}
catch (Exception ex)
{
Console.WriteLine(ex);
//em1.Status(ex.ToString());
}
return 0;
}
public override Object CreateSqlInsert(string _tableName, bool autoincr)
{
System.Text.StringBuilder _sb = new System.Text.StringBuilder();
_sb.Append("INSERT INTO ");
_sb.Append("`" + _tableName + "`");
_sb.Append(" (");
_sb.Append(_sb_insertCol);
_sb.Append(") VALUES(");
_sb.Append(_sb_insertVal);
_sb.Append(");");
if (autoincr)
{
_sb.AppendLine("SELECT LAST_INSERT_ID();");
}
this._command.CommandText = _sb.ToString();
Settings.Log.LogSQL(_command.CommandText, _command.Parameters);
Connect();
var ret = _command.ExecuteScalar();
Disconnect();
return(ret);
}
void IDB.Update(object obj, string key_fields)
{
MySql.Data.MySqlClient.MySqlConnection conn = this.GetNewConn();
try
{
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand();
cmd.Connection = conn;
cmd.CommandTimeout = 8000;
//
string sql = "";
string fields = "";
foreach (System.Reflection.PropertyInfo p in obj.GetType().GetProperties())
{
if (fields == "")
{
fields += p.Name + "=" + "@" + p.Name;
}
else
{
fields += "," + p.Name + "=" + "@" + p.Name;
}
}
string filter = "";
if (key_fields.Contains(",") == false)
{
filter = key_fields + "=" + "@" + key_fields;
}
else
{
foreach (string field in key_fields.Split(','))
{
if (filter == "")
{
filter += field + "=" + "@" + field;
}
else
{
filter += " and " + field + "=" + "@" + field;
}
}
}
sql = "update " + ReflectionHelper.GetDataTableNameByModel(obj) + " set " + fields + " where " + filter;
cmd.CommandText = sql;
cmd.Parameters.AddRange(ReflectionHelper.ModelToSqlParameters(obj));
//
conn.Open();
cmd.ExecuteScalar();
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
}
}
private void button1_Click(object sender, EventArgs e)
{
String error = "Error: One or more required fields missing"; // catch null or empty string arguments
String username = textBox1.Text;
String password = textBox2.Text;
if (username == "")
{
MessageBox.Show("Please enter a valid username!", error);
}
else if (password == "")
{
MessageBox.Show("Please enter a valid password!", error);
}
else
{
IsLogin(username, password);
if (IsLogin(username, password))
{
conn.Open();
string typequery = $"SELECT account_type FROM login WHERE username='******' AND password='******';";
MySql.Data.MySqlClient.MySqlCommand myCommand = new MySql.Data.MySqlClient.MySqlCommand(typequery, conn);
myCommand.ExecuteNonQuery();
string type = Convert.ToString(myCommand.ExecuteScalar());
conn.Close();
MessageBox.Show($"Succesfully logged in as {username}! You are a {type}.");
if (type == "Member")
{
this.Hide(); // close login window
var mem = new Member();
mem.Closed += (s, args) => this.Close();
mem.Show(); // open member window
}
else if (type == "Employee")
{
this.Hide(); // close login window
var emp = new Employee();
emp.Closed += (s, args) => this.Close();
emp.Show(); // open employee window
}
else if (type == "Manager")
{
this.Hide(); // close login window
var manage = new Manager();
manage.Closed += (s, args) => this.Close();
manage.Show(); // open main window
}
}
else
{
MessageBox.Show($"Couldn't login as {username}. Please ensure username and password are correct before trying again!");
}
}
}
public void fetchCustomerOrderID()
{
string sql = "SELECT LAST_INSERT_ID()";
if (Database.isConnected())
{
var cmd = new MySql.Data.MySqlClient.MySqlCommand(sql, Database.connection);
id = int.Parse(cmd.ExecuteScalar().ToString());
}
}
/// <summary>
/// 执行命令，返回第一行第一列的值
/// </summary>
/// <param name="connectionString">数据库连接字符串</param>
/// <param name="cmdType">命令类型（存储过程或SQL语句）</param>
/// <param name="cmdText">SQL语句或存储过程名</param>
/// <param name="cmdParms">MySql.Data.MySqlClient.MySqlCommand参数数组</param>
/// <returns>返回Object对象</returns>
public static object ExecuteScalar(MySqlConnection conn, CommandType cmdType, string cmdText, params MySql.Data.MySqlClient.MySqlParameter[] cmdParms)
{
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand();
cmd.CommandTimeout = 600;
PrepareCommand(conn, null, cmd, cmdType, cmdText, cmdParms);
object val = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return(val);
}
int IsRegisterEmailAddExists(string emailID)
{
oCon.Open();
string fetchQuery = "SELECT * FROM tbluserlist WHERE fldUserEmail = " + "'" + emailID + "'";
oMySQLData.MySqlCommand cmd = new oMySQLData.MySqlCommand(fetchQuery, oCon);
int result = 0;
result = Convert.ToInt32(cmd.ExecuteScalar());
oCon.Close();
return(result);
}
public object ExecuteScalerObj(List <MySql.Data.MySqlClient.MySqlParameter> sqlParams, string sqlQuery, CommandType cmdType)
{
MySql.Data.MySqlClient.MySqlConnection con = new MySql.Data.MySqlClient.MySqlConnection();
object result = 0;
try
{
using (con = new MySql.Data.MySqlClient.MySqlConnection(_sqlCon))
{
con.Open();
MySql.Data.MySqlClient.MySqlCommand sqlCmd = new MySql.Data.MySqlClient.MySqlCommand(sqlQuery, con);
sqlCmd.CommandType = cmdType;
if (sqlParams != null)
{
foreach (MySql.Data.MySqlClient.MySqlParameter sqlPrm in sqlParams)
{
if (sqlPrm.Value == null)
{
sqlPrm.Value = DBNull.Value;
}
}
sqlCmd.Parameters.AddRange(sqlParams.ToArray());
}
result = sqlCmd.ExecuteScalar();
con.Close();
}
}
catch (MySql.Data.MySqlClient.MySqlException sqlEx)
{
if (con != null)
{
con.Close();
}
throw sqlEx;
}
catch (Exception ex)
{
if (con != null)
{
con.Close();
}
throw ex;
}
return(result);
}
//nathan's point stuff
private void updatePointWell(String vote, String accountID)
{
string myaccountID = (String)Session["accountID"];
int votecount = 0;
conn.Open();
//CHECK THIS
queryStr = "SELECT votecount FROM account where accountId = '" + myaccountID + "'";
using (cmd = new MySqlCommand(queryStr, conn))
{
votecount = Convert.ToInt32(cmd.ExecuteScalar());
}
conn.Close();
//INCREASE THE VOTECOUNT
conn.Open();
queryStr = "";
queryStr = "UPDATE account SET votecount = votecount+1 WHERE accountId = " + myaccountID;
cmd = new MySql.Data.MySqlClient.MySqlCommand(queryStr, conn);
cmd.ExecuteReader();
conn.Close();
point_algo pa = new point_algo();
float pointworth = pa.point_worth(votecount);
/*fixed for SQL injection */
//ADD POINT WORTH INTO THE POINT WELL (well drys up if it isnt used, the decay... pretty good right? :D
if (vote == "+1")
{
//CHECK THIS (upvote)
conn.Open();
MySqlCommand cmddd = conn.CreateCommand();
queryStr = "";
queryStr = "UPDATE account SET pointwell = pointwell +'" + pointworth + "' WHERE accountId = " + accountID;
cmd = new MySql.Data.MySqlClient.MySqlCommand(queryStr, conn);
cmd.ExecuteReader();
conn.Close();
}
else
{
//DOWNVOTE
conn.Open();
MySqlCommand cmddd = conn.CreateCommand();
queryStr = "";
queryStr = "UPDATE account SET pointwell = pointwell -'" + pointworth + "' WHERE accountId = " + accountID;
cmd = new MySql.Data.MySqlClient.MySqlCommand(queryStr, conn);
cmd.ExecuteReader();
conn.Close();
}
}
object IDB.ExecuteScalar(string sql, System.Data.IDbDataParameter[] pars)
{
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand();
cmd.Connection = conn;
cmd.Transaction = tran;
cmd.CommandTimeout = 8000;
cmd.CommandText = sql;
if (pars != null)
{
cmd.Parameters.AddRange(pars);
}
//
return(cmd.ExecuteScalar());
}
public static bool Ejecutar2_Mysql(string Consulta, ref string Resultado)
{
bool Ejecutar2_Mysql = false;
WSQLAux_My = "";
Conecta_My = Conexion_My("", "");
WSQLAux_My = Consulta;
try
{
CmdAux_My = new MySql.Data.MySqlClient.MySqlCommand();
CmdAux_My.Connection = Conecta_My;
CmdAux_My.CommandTimeout = 0;
CmdAux_My.CommandType = CommandType.Text;
CmdAux_My.CommandText = WSQLAux_My;
Conecta_My.Open();
try
{
Resultado = CmdAux_My.ExecuteScalar().ToString();
}
catch (Exception ex)
{
Resultado = "XXXXX";
}
if (Resultado == "XXXXX")
{
Resultado = "";
Ejecutar2_Mysql = false;
}
else
{
Ejecutar2_Mysql = true;
}
Conecta_My.Close();
CmdAux_My.Dispose();
}
catch (Exception ex)
{
if (Conecta_My.State == ConnectionState.Open)
{
Conecta_My.Close();
}
MessageBox.Show("Error en la Cosulta: " + WSQLAux_My + " / " + ex.Message, "SISTEMA V 2.0", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
return(Ejecutar2_Mysql);
}
public Int64 Valor(string strsql)
{
try
{
cnSQL.Open();
MySql.Data.MySqlClient.MySqlCommand cmsSQL = new MySql.Data.MySqlClient.MySqlCommand(strsql, cnSQL);
return(Convert.ToInt64(cmsSQL.ExecuteScalar()));
}
catch (Exception ex)
{
throw ex;
}
finally
{
cnSQL.Close();
}
}
public static bool CheckForObjectExistence(string Identifier, bool Primary = true, string Table = "Accounts")
{
lock (Connection) {
try {
using (MySQL.MySqlCommand MySqlCommand = new MySQL.MySqlCommand("SELECT COUNT(*) FROM " + Table + " WHERE " + (Primary ? "ID" : "Name") + " = '" + MySQL.MySqlHelper.EscapeString(Identifier) + "';", Connection)) {
return(int.Parse(MySqlCommand.ExecuteScalar().ToString()) == 0 ? false : true);
}
}
catch (System.Exception Exception) {
if (Program.Debug)
{
Utils.Log.Error("Database", Exception.Message);
}
return(false);
}
}
}
public static long GetJustID()
{
long identity = -1;
// 打开
OpenConnecion();
MySql.Data.MySqlClient.MySqlCommand cmdID;
//string sID="";
cmdID = new MySql.Data.MySqlClient.MySqlCommand("select @@identity", ConnectionPool_mysql.con);
if (m_isTransaction)
{
cmdID.Transaction = ConnectionPool_mysql.trans;
}
identity = Convert.ToInt64(cmdID.ExecuteScalar());
return(identity);
}
/// <summary>
/// Function for Check the Sql Path
/// </summary>
/// <param name="ServerName"></param>
/// <param name="UserId"></param>
/// <param name="Password"></param>
/// <returns></returns>
public bool CheckSqlPath(string ServerName, string UserId, string Password)
{
bool isTrue = false;
ServerClass ClassS = new ServerClass();
try
{
MySql.Data.MySqlClient.MySqlConnection connection = new MySql.Data.MySqlClient.MySqlConnection(string.Format("server={0};user id={1}; password={2};", ServerName, UserId, Password));
if (connection.State == System.Data.ConnectionState.Closed)
{
connection.Open();
}
MySql.Data.MySqlClient.MySqlCommand command = new MySql.Data.MySqlClient.MySqlCommand("SELECT @@basedir;", connection);
object obj = command.ExecuteScalar();
try
{
ClassS.UpdateAppConfig("MySqlUrl", (obj != null) ? obj.ToString() : null);
}
catch { }
MySql.Data.MySqlClient.MySqlCommand command1 = new MySql.Data.MySqlClient.MySqlCommand("select @@hostname;", connection);
object obj1 = command1.ExecuteScalar();
try
{
ClassS.UpdateAppConfig("MySqlPathInstalledOn", (obj1 != null) ? obj1.ToString() : null);
}
catch { }
isTrue = true;
if (connection.State == System.Data.ConnectionState.Open)
{
connection.Close();
}
}
catch (Exception)
{
isTrue = false;
}
return(isTrue);
}
public ActionResult Login(LoginModel ObjModel)
{
string mycon;
mycon = "server=localhost;port=3306;database=esignapp;user=root;password=sql123";
con = new MySql.Data.MySqlClient.MySqlConnection();
con.ConnectionString = mycon;
con.Open();
string ExistUser = "******" + ObjModel.UserName + "' and password='******'";
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(ExistUser, con);
string strResult = string.Empty;
strResult = (string)cmd.ExecuteScalar();
if (strResult == null)
{
con.Close();
ModelState.AddModelError("Password", "Invalid Password");
return(View());
}
else
{
string lstdata = "select ID,username, password from tbl_user where username='******'";
cmd = new MySql.Data.MySqlClient.MySqlCommand(lstdata, con);
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Session["UserID"] = reader["ID"].ToString();
}
con.Close();
Session["UserName"] = ObjModel.UserName;
return(RedirectToAction("Dashboard", "Home"));
}
}
/*!
* \brief This function assigns the connection string that is associated with the user.
* \details This function queries the database and using the role associated with each user determines the connection string that has their appropiate premissions.
* \param <b>void</b>
*/
private string getConStr()
{
int role = 0;
string conStr = ConfigurationManager.ConnectionStrings[localUser.CONSTR].ConnectionString;
StringBuilder cmdSB = new StringBuilder("SELECT Role FROM Users WHERE UserName='******' AND Password='******';");
using (MySqlConnection connection = new MySqlConnection(conStr))
{
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(cmdSB.ToString(), connection);
try
{
connection.Open();
role = int.Parse(cmd.ExecuteScalar().ToString());
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
connection.Close();
}
}
switch (role)
{
case 0: //Admin
return("AdminAcc");
case 1: //Buyer
return("BuyerAcc");
case 2: //Planner
return("PlannerAcc");
default:
return("loginAcc");
}
}
protected void generatearanomnumber()
{
Random rd = new Random();
string clsid = (String)Session["clsid"];
string tlclsid = "logindetails_" + clsid;
String connstring = System.Configuration.ConfigurationManager.ConnectionStrings["WebAppConString"].ToString();
connection = new MySql.Data.MySqlClient.MySqlConnection(connstring);
connection.Open();
querystr = "select count(*) from " + tlclsid;
cmd = new MySql.Data.MySqlClient.MySqlCommand(querystr, connection);
long num = (long)cmd.ExecuteScalar();
connection.Close();
Int32 numm = Convert.ToInt32(num);
int randomnum = Convert.ToInt32(rd.Next(1, numm));
// check.Text =Convert.ToString( randomnum );
checktherandomnumberandassign(randomnum);
}
