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;
}
/// <summary>
/// 修改数据
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
public int Update(Policy entity)
{
string sql = "UPDATE tb_policy SET agentType=@agentType,subject=@subject,content=@content,sender=@sender,attachment=@attachment,attachmentName=@attachmentName,creatTime=@creatTime,";
sql = sql + " type=@type,validateStartTime=@validateStartTime,validateEndTime=@validateEndTime,isValidate=@isValidate,isDelete=@isDelete,deleteTime=@deleteTime,toAll=@toAll where sequence=@sequence ";
//string sql = "UPDATE cimuser SET userNickName=@userNickName WHERE userid=@userid";
using (MySqlConnection mycn = new MySqlConnection(mysqlConnection))
{
mycn.Open();
MySqlCommand command = new MySqlCommand(sql, mycn);
command.Parameters.AddWithValue("@agentType", entity.agentType);
command.Parameters.AddWithValue("@sequence", entity.sequence);
command.Parameters.AddWithValue("@subject", entity.subject);
command.Parameters.AddWithValue("@content", entity.content);
command.Parameters.AddWithValue("@sender", entity.sender);
command.Parameters.AddWithValue("@attachment", entity.attachment);
command.Parameters.AddWithValue("@attachmentName", entity.attachmentName);
command.Parameters.AddWithValue("@creatTime", entity.creatTime);
command.Parameters.AddWithValue("@type", entity.type);
command.Parameters.AddWithValue("@validateStartTime", entity.validateStartTime);
command.Parameters.AddWithValue("@validateEndTime", entity.validateEndTime);
command.Parameters.AddWithValue("@isValidate", entity.isValidate);
command.Parameters.AddWithValue("@isDelete", entity.isDelete);
command.Parameters.AddWithValue("@deleteTime", entity.deleteTime);
command.Parameters.AddWithValue("@toAll", entity.toAll);
int i = command.ExecuteNonQuery();
mycn.Close();
mycn.Dispose();
return i;
}
}
protected void Button_click(object sender, EventArgs e)
{
MySqlConnection bazaPovezava = new MySqlConnection(bazaConnString);
try
{
bazaPovezava.Open();
string SQLcommand = "INSERT INTO User(username, firstname, lastname, password, email, city, country) VALUES(?un, ?fn, ?ln, ?pw, ?em, ?ci, ?co);";
MySqlCommand bazaUkaz = new MySqlCommand(SQLcommand, bazaPovezava);
bazaUkaz.Parameters.Add(new MySqlParameter("?un", username.Text));
bazaUkaz.Parameters.Add(new MySqlParameter("?fn", firstname.Text));
bazaUkaz.Parameters.Add(new MySqlParameter("?ln", surname.Text));
bazaUkaz.Parameters.Add(new MySqlParameter("?pw", pass.Text));
bazaUkaz.Parameters.Add(new MySqlParameter("?em", email.Text));
bazaUkaz.Parameters.Add(new MySqlParameter("?ci", city.Text));
bazaUkaz.Parameters.Add(new MySqlParameter("?co", country.Text));
bazaUkaz.ExecuteNonQuery();
}
catch(Exception ex)
{
Console.WriteLine(ex);
}
finally
{
bazaPovezava.Close();
}
}
/* =====================================================================================
* ================== G E T V A L U E S B Y O T H E R V A L U E S ==================
* ===================================================================================== */
static string[] getCharakByNameAndKodas(string name, string kodas)
{
string[] result = { "", "" };
MySqlConnection con = new MySqlConnection(connectionStringAlt);
con.Open();
//query
string query = "SELECT pr_z.pr_pavad as 'name',g_v.prid, g_v.g_kodas as 'kodas' FROM pg_zodynas INNER JOIN pg_seima on pg_seima.pg_id_v=pg_zodynas.pg_id INNER JOIN goods_v g_v on g_v.pgs_id = pg_seima.pgs_id INNER JOIN pr_zodynas AS pr_z ON g_v.pr_id=pr_z.pr_id AND g_v.del_date IS NULL WHERE pg_zodynas.pavaddgs = '" + name + "'";
MySqlCommand cmd = new MySqlCommand(query, con);
//executing query
MySqlDataReader data = cmd.ExecuteReader();
while (data.Read())
{
if (data["kodas"].ToString() == kodas)
result[0] = data["name"].ToString();
{
result[1] = data["prid"].ToString();
}
}
data.Close();
con.Close();
return result;
}
public static void Main()
{
string connectionString = ConfigurationManager.ConnectionStrings["library"].ConnectionString;
MySqlConnection dbConnection = new MySqlConnection(connectionString);
// MySqlConnection dbConnection = new MySqlConnection("Server=localhost; Port=3306;Database=library; Uid = root; Pwd = root; pooling = true");
dbConnection.Open();
using (dbConnection)
{
int newBook = AddNewBookToDBTable(dbConnection, "King Lion", "James Clavel", DateTime.Parse("2015.10.10"), 1234567890123);
int newBook1 = AddNewBookToDBTable(dbConnection, "Untouchables", "Unknown", DateTime.Parse("2015.10.10"), 1234567890123);
int newBook2 = AddNewBookToDBTable(dbConnection, "C# intro", "Svetlin Nakov", DateTime.Parse("2015.10.10"), 1234567890123);
Console.WriteLine("Inserted new product with Id: {0}", newBook);
Console.WriteLine(new string('-', 30));
ListAllBooksFromDBTable(dbConnection);
Console.WriteLine(new string('-', 30));
Console.Write("Please enter text to search a book:");
string input = Console.ReadLine();
Console.WriteLine(new string('-', 30));
Console.WriteLine("Products that contain: {0}", input);
SearchAllBooksThatContainString(dbConnection, input);
}
}
/// <summary>
/// Initializes a new instance of the <see cref="ManagerGeneralItemStagesGui"/> class.
/// </summary>
/// <param name="itemid">The itemid.</param>
public ManagerGeneralItemStagesGui(string itemid)
{
//Login.close = 1;
InitializeComponent();
this.WindowStartupLocation = WindowStartupLocation.CenterScreen;
this.itemID = itemid;
try
{
MySqlConnection MySqlConn = new MySqlConnection(Login.Connectionstring);
MySqlConn.Open();
string Query1 = "select itemName from item where itemid='" + itemID + "'";
MySqlCommand MSQLcrcommand1 = new MySqlCommand(Query1, MySqlConn);
MSQLcrcommand1.ExecuteNonQuery();
MySqlDataAdapter mysqlDAdp = new MySqlDataAdapter(MSQLcrcommand1);
MySqlDataReader dr = MSQLcrcommand1.ExecuteReader();
while (dr.Read())
{
if (!dr.IsDBNull(0))
{
itemName = dr.GetString(0);
}
}
MySqlConn.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
type_comboBox.Items.Add("רישום");
type_comboBox.Items.Add("בעבודה");
type_comboBox.Items.Add("תיקון");
type_comboBox.Items.Add("פסול");
type_comboBox.Items.Add("גמר ייצור");
type_comboBox.Items.Add("הסתיים");
type_comboBox.SelectedIndex = 0;
itemidlabel.Content = itemID;
itemnamelabel.Content = itemName;
try
{
MySqlConnection MySqlConn = new MySqlConnection(Login.Connectionstring);
MySqlConn.Open();
string Query1 = ("SELECT itemStageOrder as `מספר שלב`,stageName as `שם שלב` ,stage_discription as `תאור השלב` FROM item WHERE itemid='" + itemID + "' and itemStatus='רישום' ");
MySqlCommand MSQLcrcommand1 = new MySqlCommand(Query1, MySqlConn);
MSQLcrcommand1.ExecuteNonQuery();
MySqlDataAdapter mysqlDAdp = new MySqlDataAdapter(MSQLcrcommand1);
dt.Clear();
mysqlDAdp.Fill(dt);
dataGrid1.ItemsSource = dt.DefaultView;
mysqlDAdp.Update(dt);
MySqlConn.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
//m mayucula en main obligatoriamente.
public static void Main(string[] args)
{
MySqlConnection mysqlconection = new MySqlConnection (
"Database=dbprueba;Data Source=localhost;User id=root; Password=sistemas");
mysqlconection.Open ();
MySqlCommand mysqlcommand = mysqlconection.CreateCommand ();
mysqlcommand.CommandText = "select * from articulo";
// "select a.categoria as articulocategoria, c.nombre as categorianombre, count(*)" +
// "from articulo a " +
// "left join categoria c on a.categoria= c.id " +
// "group by articulocategoria, categorianombre";
MySqlDataReader mysqldatareader = mysqlcommand.ExecuteReader ();
//---------------------------------------------------------------
updateDatabase (mysqlconection);
showColumnNames (mysqldatareader);
show (mysqldatareader);
//---------------------------------------------------------------
mysqldatareader.Close ();
mysqlconection.Close ();
}
public MySqlTransformationProvider(Dialect dialect, string connectionString)
: base(dialect, connectionString)
{
_connection = new MySqlConnection(_connectionString);
_connection.ConnectionString = _connectionString;
_connection.Open();
}
public MySqlConnection GetDBConnection()
{
try
{
MySqlConnection db;
if (_databaseQueue.Count > 0)
{
db = _databaseQueue.Dequeue();
System.Threading.ThreadPool.QueueUserWorkItem(ProcessDatabaseQueue);
}
else
{
db = new MySqlConnection(Config.GetConnectionString());
db.Open();
}
return db;
}
catch (MySqlException e)
{
MySqlConnection db = new MySqlConnection();
Logger.WriteLog(e.Message, Logger.LogType.Error);
db.Dispose();
return db;
}
}
public static void mysqlVer(MySqlConnection mySqlConnection)
{
MySqlCommand mySqlCommand = mySqlConnection.CreateCommand ();
mySqlCommand.CommandText = "SELECT * FROM categoria";
MySqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader ();
Console.WriteLine ("FieldCount = {0}", mySqlDataReader.FieldCount);
for (int index = 0; index < mySqlDataReader.FieldCount; index++)
{
Console.WriteLine ("Column {0} = {1}", index, mySqlDataReader.GetName (index));
}
while (mySqlDataReader.Read())
{
object id = mySqlDataReader ["id"];
object nombre = mySqlDataReader ["nombre"];
Console.Write ("\n{0}, {1}", id, nombre);
}
Console.WriteLine ("\n\nPress any key to continue...");
Console.Read ();
mySqlDataReader.Close ();
}
protected void Dodaj(object sender, EventArgs e)
{
string DruzynaID = txtDruzyna.Text;
string Imie = txtImie.Text;
string Nazwisko = txtNazwisko.Text;
string Data = txtData.Text;
string Pozycja = txtPozycja.Text;
string Waga = txtWaga.Text;
string Wzrost = txtWzrost.Text;
string Numer = txtNumer.Text;
string sDate = String.Format("{0:yyyy-mm-dd}", Data);
MySqlConnection polaczenie = new MySqlConnection(url);
MySqlCommand cmd = new MySqlCommand("INSERT INTO pilkarze (ID_Druzyny, Imie, Nazwisko, Data_urodz, Pozycja, Waga, Wzrost, Nr_Kosz) VALUES (@IdDruzyny, @imie, @nazwisko, @data, @pozycja, @waga, @wzrost, @numer)", polaczenie);
MySqlDataAdapter sda = new MySqlDataAdapter();
cmd.Parameters.AddWithValue("@IdDruzyny", DruzynaID);
cmd.Parameters.AddWithValue("@imie", Imie);
cmd.Parameters.AddWithValue("@nazwisko", Nazwisko);
cmd.Parameters.AddWithValue("@data", sDate);
cmd.Parameters.AddWithValue("@pozycja", Pozycja);
cmd.Parameters.AddWithValue("@waga", Waga);
cmd.Parameters.AddWithValue("@wzrost", Wzrost);
cmd.Parameters.AddWithValue("@numer", Numer);
polaczenie.Open();
cmd.ExecuteNonQuery();
polaczenie.Close();
this.WypelnijGridView();
}
private static MySqlDataAdapter AdaptadorABM(MySqlConnection SqlConnection1)
{
MySqlCommand SqlInsertCommand1;
MySqlCommand SqlUpdateCommand1;
MySqlCommand SqlDeleteCommand1;
MySqlDataAdapter SqlDataAdapter1 = new MySqlDataAdapter();
SqlInsertCommand1 = new MySqlCommand("Generos_Insertar", SqlConnection1);
SqlUpdateCommand1 = new MySqlCommand("Generos_Actualizar", SqlConnection1);
SqlDeleteCommand1 = new MySqlCommand("Generos_Borrar", SqlConnection1);
SqlDataAdapter1.DeleteCommand = SqlDeleteCommand1;
SqlDataAdapter1.InsertCommand = SqlInsertCommand1;
SqlDataAdapter1.UpdateCommand = SqlUpdateCommand1;
// IMPLEMENTACIÓN DE LA ORDEN UPDATE
SqlUpdateCommand1.Parameters.Add("p_id", MySqlDbType.Int32, 3, "IdGeneroGEN");
SqlUpdateCommand1.Parameters.Add("p_descripcion", MySqlDbType.VarChar, 50, "DescripcionGEN");
SqlUpdateCommand1.Parameters.Add("p_activoWeb", MySqlDbType.Int32, 1, "ActivoWebGEN");
SqlUpdateCommand1.CommandType = CommandType.StoredProcedure;
// IMPLEMENTACIÓN DE LA ORDEN INSERT
SqlInsertCommand1.Parameters.Add("p_id", MySqlDbType.Int32, 3, "IdGeneroGEN");
SqlInsertCommand1.Parameters.Add("p_descripcion", MySqlDbType.VarChar, 50, "DescripcionGEN");
SqlInsertCommand1.Parameters.Add("p_activoWeb", MySqlDbType.Int32, 1, "ActivoWebGEN");
SqlInsertCommand1.CommandType = CommandType.StoredProcedure;
// IMPLEMENTACIÓN DE LA ORDEN DELETE
SqlDeleteCommand1.Parameters.Add("p_id", MySqlDbType.Int32, 3, "IdGeneroGEN");
SqlDeleteCommand1.CommandType = CommandType.StoredProcedure;
return SqlDataAdapter1;
}
private void WypelnijGridView()
{
txtDruzyna.Text = "";
txtImie.Text = "";
txtNazwisko.Text = "";
txtData.Text = "";
txtPozycja.Text = "";
txtWaga.Text = "";
txtWzrost.Text = "";
txtNumer.Text = "";
string constr = ConfigurationManager.ConnectionStrings["pol"].ConnectionString;
using (MySqlConnection con = new MySqlConnection(constr))
{
using (MySqlCommand cmd = new MySqlCommand("SELECT * FROM pilkarze"))
{
using (MySqlDataAdapter sda = new MySqlDataAdapter())
{
cmd.Connection = con;
sda.SelectCommand = cmd;
using (DataTable dt = new DataTable())
{
sda.Fill(dt);
GridView1.DataSource = dt;
GridView1.DataBind();
}
}
}
}
}
public static string FetchPublicContent(string password)
{
using (MySqlConnection con = new MySqlConnection(Database.ConnectionString))
{
con.Open();
using (MySqlCommand command = con.CreateCommand())
{
command.Parameters.AddWithValue("@docID", requestID);
command.Parameters.AddWithValue("@password", password);
command.CommandText =
"SELECT Revisions.Content from Revisions join Documents on Revisions.docID=Documents.docID where Revisions.docID=@docID and Revisions.revisionID=(Select Max(revisionID) from Revisions where docID=@docID) AND publicPassword = @password" ;
MySqlDataReader reader = command.ExecuteReader();
if(reader.HasRows)
{
reader.Read();
return LiveDocs.livedocs.Editor.ParseMarkup((String)reader[0]);
}
else
{
throw new Exception("Password not correct or document does not exist");
}
}
}
return null;
}
public bool actionCreate(Task task)
{
var conn = new MySqlConnection(TaskDAO.StringConnection);
try
{
conn.Open();
var sql = "INSERT INTO tasks (title, description, created_at, user_id) VALUES (@title, @description, @created_at, @user_id)";
var cmd = new MySqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@title", task.Title);
cmd.Parameters.AddWithValue("@description", task.Description);
cmd.Parameters.AddWithValue("@created_at", DateTime.Now);
cmd.Parameters.AddWithValue("@user_id", task.User.Id);
cmd.ExecuteNonQuery();
return true;
} catch(Exception ex)
{
Console.WriteLine(ex.Message);
return false;
}
finally
{
conn.Close();
}
}
//test connection for server
public static bool TestConnection()
{
try
{
//set database connection
using (MySqlConnection con = new MySqlConnection(Big.Config.GetConnectionString()))
{
//open connection
con.Open();
return true;
}
}
catch (MySqlException ex)
{
//revert settings
Properties.Settings.Default.HOST = String.Empty;
Properties.Settings.Default.DATABASE = String.Empty;
Properties.Settings.Default.USERNAME = String.Empty;
Properties.Settings.Default.PASSWORD = String.Empty;
Properties.Settings.Default.DSN = String.Empty;
//save settings
Properties.Settings.Default.Save();
//error configuration
MessageBox.Show("Error: Cannot connect to server" + ex.Message.ToString(), "System Message", MessageBoxButtons.OK, MessageBoxIcon.Error);
return false;
}
}
private void btn_load_Click(object sender, EventArgs e)
{
MySqlConnection connection;
var sql = String.Format("SELECT * FROM creature_ai_scripts WHERE creature_id = {0}", UInt32.Parse(tb_entry.Text));
try
{
connection = new MySqlConnection("server=127.0.0.1;uid=root;pwd=;database=world2;");
connection.Open();
MySqlCommand cmd = new MySqlCommand(sql, connection);
var data = cmd.ExecuteReader();
if (!data.HasRows)
return;
while(data.Read())
{
tc_content.TabPages.Add("AI");
var newTab = tc_content.TabPages[tc_content.TabCount - 1];
var aiTab = new AiTab();
aiTab.Dock = DockStyle.Fill;
aiTab.FromDatabase(data);
newTab.Controls.Add(aiTab);
}
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
}
}
public List<Categoria> ObterCategoriasCadastradas(string idioma)
{
List<Categoria> lista = new List<Categoria>();
MySqlConnection conn = new MySqlConnection(connectionString);
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = conn;
cmd.CommandText = "SELECT id, nome, urlImagem FROM tb_categorias order by nome";
conn.Open();
MySqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
while (dr.Read())
{
lista.Add(new Categoria
{
id = (int)dr["id"],
nome = Tradutor.Traduzir(dr["nome"].ToString(), idioma),
urlImagem = dr["urlImagem"].ToString()
});
}
}
conn.Close();
return lista;
}
// the logging system regerence
//static LoggingSystem.Log loggingSystem = new LoggingSystem.Log();
#endregion Variables
#region Construction
static ServerAccessMySQL()
{
LoggingSystem.LoggingSystem.LogMessage = "Salut";
mySQLConnection = getMySQLConnection(null);
//if (MyResultsTrace) SQLView.LogResult(new string[] { name });
}
private static MySqlDataAdapter AdaptadorABM(MySqlConnection SqlConnection1)
{
MySqlCommand SqlInsertCommand1;
MySqlCommand SqlUpdateCommand1;
MySqlCommand SqlDeleteCommand1;
MySqlDataAdapter SqlDataAdapter1 = new MySqlDataAdapter();
SqlInsertCommand1 = new MySqlCommand("AlicuotasIva_Insertar", SqlConnection1);
SqlUpdateCommand1 = new MySqlCommand("AlicuotasIva_Actualizar", SqlConnection1);
SqlDeleteCommand1 = new MySqlCommand("AlicuotasIva_Borrar", SqlConnection1);
SqlDataAdapter1.DeleteCommand = SqlDeleteCommand1;
SqlDataAdapter1.InsertCommand = SqlInsertCommand1;
SqlDataAdapter1.UpdateCommand = SqlUpdateCommand1;
// IMPLEMENTACIÓN DE LA ORDEN INSERT
SqlInsertCommand1.Parameters.Add("p_id", MySqlDbType.Int16, 2, "IdAlicuotaALI");
SqlInsertCommand1.Parameters.Add("p_porcentaje", MySqlDbType.Decimal, 12, "PorcentajeALI");
SqlInsertCommand1.CommandType = CommandType.StoredProcedure;
SqlUpdateCommand1.Parameters.Add("p_id", MySqlDbType.Int16, 2, "IdAlicuotaALI");
SqlUpdateCommand1.Parameters.Add("p_porcentaje", MySqlDbType.Decimal, 12, "PorcentajeALI");
SqlUpdateCommand1.CommandType = CommandType.StoredProcedure;
// IMPLEMENTACIÓN DE LA ORDEN DELETE
SqlDeleteCommand1.Parameters.Add("p_id", MySqlDbType.Int32, 2, "IdAlicuotaALI");
SqlDeleteCommand1.CommandType = CommandType.StoredProcedure;
return SqlDataAdapter1;
}
public static bool IsFullPublic()
{
bool isPublic = false;
bool isFullyPublic = false;
using (MySqlConnection con = new MySqlConnection(Database.ConnectionString))
{
con.Open();
using (MySqlCommand command = con.CreateCommand())
{
command.Parameters.AddWithValue("@docID", requestID);
command.CommandText =
"SELECT public,publicPassword FROM documents WHERE docID = @docID";
MySqlDataReader r = command.ExecuteReader();
while(r.Read())
{
isPublic = Convert.ToBoolean(r["public"]);
if(isPublic && (r["publicPassword"].ToString() == "" || r["publicPassword"].ToString() == "public"))
{
isFullyPublic = true;
}
}
}
}
return isFullyPublic;
}
/// <summary>
/// Constructor which takes the connection string name
/// </summary>
/// <param name="connectionStringName"></param>
public MySQLDatabase(string connectionStringName)
{
var configuration = new Configuration()
.AddJsonFile("config.json");
string connectionString = configuration[connectionStringName];
_connection = new MySqlConnection(connectionString);
}
public void Initialise(string connectionString)
{
m_connectionString = connectionString;
try
{
m_log.Info("[REGION DB]: MySql - connecting: " + Util.GetDisplayConnectionString(m_connectionString));
}
catch (Exception e)
{
m_log.Debug("Exception: password not found in connection string\n" + e.ToString());
}
GetWaitTimeout();
using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
{
dbcon.Open();
Assembly assem = GetType().Assembly;
Migration m = new Migration(dbcon, assem, "EstateStore");
m.Update();
Type t = typeof(EstateSettings);
m_Fields = t.GetFields(BindingFlags.NonPublic |
BindingFlags.Instance |
BindingFlags.DeclaredOnly);
foreach (FieldInfo f in m_Fields)
{
if (f.Name.Substring(0, 2) == "m_")
m_FieldMap[f.Name.Substring(2)] = f;
}
}
}
private void ConnectDatabase()
{
string connStr = "server=" + caspar_database_server_hostname + ";database=" + caspar_database_server_database + ";uid=" +
caspar_database_server_username + ";password="******";";
connection = new MySqlConnection(connStr);
connection.Open();
}
/// <summary>
/// Wykonuje iSelecta na bazie, zwraca MySqlDataReader z wynikami
/// </summary>
/// <param name="iSelect">string z SELECTEM</param>
/// <param name="iGetReaderData">Funkcja(MySqlDataReader), ogarniająca dane</param>
public static void WykonajSelecta(string iSelect, Action<MySqlDataReader> iGetReaderData)
{
//MySqlDataReader readerToReturn;
string MyConnectionString = "Server=localhost;Database=mydb1;Uid=root;";
MySqlConnection con = new MySqlConnection(MyConnectionString);
con.Open();
try
{
MySqlCommand cmd = con.CreateCommand();
cmd.CommandText = iSelect;
MySqlDataReader reader = cmd.ExecuteReader();
//Delegata, który ogarnie dane
iGetReaderData(reader);
}
catch (Exception)
{
throw;
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
//no i chuj, żeby zamknąć połączenie trzeba tutaj wjebać delegata
//return readerToReturn;
}
private void button1_Click(object sender, EventArgs e)
{
try
{
button5.Enabled = true;
selectedItem = listBox1.SelectedItem.ToString();
querry = "SELECT * FROM " + listBox1.SelectedItem.ToString();
connection = new MySqlConnection(connectionString);
dataAdapter = new MySqlDataAdapter(querry, connectionString);
dt = new DataTable();
dataAdapter.Fill(dt);
if (dataGridView1.DataSource == null)
{
dataGridView1.Rows.Clear();
dataGridView1.Columns.Clear();
}
dataGridView1.DataSource = dt;
}
catch (NullReferenceException ex)
{
MessageBox.Show("Please select one of the databases in the list.", "Error!", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
catch (Exception ex)
{
MessageBox.Show("An error occured!", "Error!", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
// CONNECTION POOLING IS A MUST!!!
// TODO: Rewrite needed for config.xml, only providing username, password and database. Create connection string via stringbuilders
/// <summary>
/// </summary>
/// <returns>
/// </returns>
/// <exception cref="Exception">
/// </exception>
public static IDbConnection GetConnection()
{
IDbConnection conn = null;
if (Sqltype == "MySql")
{
conn = new MySqlConnection(ConnectionString_MySQL);
}
if (Sqltype == "MsSql")
{
conn = new SqlConnection(ConnectionString_MSSQL);
}
if (Sqltype == "PostgreSQL" )
{
conn = new NpgsqlConnection(ConnectionString_PostGreSQL);
}
if (conn == null)
{
throw new Exception("ConnectionString error");
}
conn.Open();
return conn;
}
public const string mysqlConnection = DBConstant.mysqlConnection;//"User Id=root;Host=115.29.229.134;Database=chinaunion;password=c513324665;charset=utf8";
/// <summary>
/// 添加数据
/// </summary>
/// <returns></returns>
public int Add(Policy entity)
{
string sql = "INSERT INTO tb_policy (agentType,subject,content,sender,attachment,attachmentName,creatTime,type, validateStartTime,validateEndTime, isValidate, isDelete, deleteTime,toAll) VALUE (@agentType,@subject,@content,@sender,@attachment,@attachmentName,@creatTime,@type, @validateStartTime,@validateEndTime, @isValidate, @isDelete, @deleteTime,@toAll)";
using (MySqlConnection mycn = new MySqlConnection(mysqlConnection))
{
mycn.Open();
MySqlCommand command = new MySqlCommand(sql, mycn);
command.Parameters.AddWithValue("@agentType", entity.agentType);
command.Parameters.AddWithValue("@subject", entity.subject);
command.Parameters.AddWithValue("@content", entity.content);
command.Parameters.AddWithValue("@sender", entity.sender);
command.Parameters.AddWithValue("@attachment", entity.attachment);
command.Parameters.AddWithValue("@attachmentName", entity.attachmentName);
command.Parameters.AddWithValue("@creatTime", entity.creatTime);
command.Parameters.AddWithValue("@type", entity.type);
command.Parameters.AddWithValue("@validateStartTime", entity.validateStartTime);
command.Parameters.AddWithValue("@validateEndTime", entity.validateEndTime);
command.Parameters.AddWithValue("@isValidate", entity.isValidate);
command.Parameters.AddWithValue("@isDelete", entity.isDelete);
command.Parameters.AddWithValue("@deleteTime", entity.deleteTime);
command.Parameters.AddWithValue("@toAll", entity.toAll);
int i = command.ExecuteNonQuery();
mycn.Close();
mycn.Dispose();
return i;
}
}
internal void CreateStaff(Staff staff)
{
using (MySqlConnection conn = new MySqlConnection(PredatorConstants.CONNECTION_STRING))
{
if (MySqlConnectionManager.OpenConnection(conn))
{
string commandText =
@"INSERT INTO staff (fName, lName, username, password, email, accessLevel, creationDate)
VALUES(@FNAME, @LNAME, @USERNAME, @PASSWORD, @EMAIL, @ACCESSLEVEL, @CREATIONDATE)";
MySqlCommand command = new MySqlCommand(commandText, conn);
command.Parameters.Add("@FNAME", MySqlDbType.VarChar); ;
command.Parameters["@FNAME"].Value = staff.fName;
command.Parameters.Add("@LNAME", MySqlDbType.VarChar);
command.Parameters["@LNAME"].Value = staff.lName;
command.Parameters.Add("@USERNAME", MySqlDbType.VarChar);
command.Parameters["@USERNAME"].Value = staff.username;
command.Parameters.Add("@PASSWORD", MySqlDbType.VarChar);
command.Parameters["@PASSWORD"].Value = staff.password;
command.Parameters.Add("@EMAIL", MySqlDbType.VarChar);
command.Parameters["@EMAIL"].Value = staff.email;
command.Parameters.Add("@ACCESSLEVEL", MySqlDbType.Int32);
command.Parameters["@ACCESSLEVEL"].Value = staff.accessLevel;
command.Parameters.Add("@CREATIONDATE", MySqlDbType.DateTime);
command.Parameters["@CREATIONDATE"].Value = staff.creationDate;
command.ExecuteNonQuery();
MySqlConnectionManager.CloseConnection(conn);
}
}
}
protected void insertNewRateRow(int idM, string email)
{
try
{
String connString = System.Configuration.ConfigurationManager.ConnectionStrings["WebbAppConnString"].ToString();
conn = new MySql.Data.MySqlClient.MySqlConnection(connString);
conn.Open();
queryStr = "";
queryStr = "insert into comments(movies_id, user_email,betyg, comment, isSetBetyg) values('" + idM + "','" + email + "' ,'" + 0 + "','NONE',false)";
cmd = new MySql.Data.MySqlClient.MySqlCommand(queryStr, conn);
cmd.ExecuteReader();
conn.Close();
}
catch {
System.Diagnostics.Debug.WriteLine("You already bought this movie!!");
}
}
protected void updateComments(string s, int betyg, string email, int id)
{
try
{
String connString = System.Configuration.ConfigurationManager.ConnectionStrings["WebbAppConnString"].ToString();
conn = new MySql.Data.MySqlClient.MySqlConnection(connString);
conn.Open();
queryStr = "";
queryStr = "update comments set comment = '" + s + "', betyg= " + betyg + ", isSetBetyg=" + true + " where movies_id =" + id + " and user_email='" + email + "' and isSetBetyg=false";
cmd = new MySql.Data.MySqlClient.MySqlCommand(queryStr, conn);
cmd.ExecuteReader();
conn.Close();
TextBox1.Text = "";
DropDownListRate.SelectedValue = "None";
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
System.Diagnostics.Debug.Write("error: " + ex);
errorLabel.Text = " It is not possible to set a new rate for this movie!!";
}
}
public static int execS(String sql)
{
if (MainClass.usedb)
{
int lines = 0;
MySql.Data.MySqlClient.MySqlCommand cmd = null;
MySql.Data.MySqlClient.MySqlConnection conn = null;
try
{
conn = createConnection();
cmd = new MySql.Data.MySqlClient.MySqlCommand(sql, conn);
cmd.CommandTimeout = (60 * 1000) * 3;
lines = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine("execS" + ex.Message + ex.StackTrace);
return(-1);
}
finally
{
if (cmd != null)
{
cmd.Dispose();
cmd = null;
}
if (conn != null)
{
conn.Close();
conn.Dispose();
conn = null;
}
}
return(lines);
}
else
{
return(0);
}
}
static MySql.Data.MySqlClient.MySqlConnection createConnection()
{
if (MainClass.usedb)
{
try
{
MySql.Data.MySqlClient.MySqlConnection conn = null;
conn = new MySql.Data.MySqlClient.MySqlConnection(strConn);
int count = 0;
int maxCount = 5;
while (conn.State != ConnectionState.Open)
{
try
{
conn.Open();
}
catch
{
System.Threading.Thread.Sleep(300);
}
count++;
if (count >= maxCount)
{
break;
}
}
return(conn);
}
catch (Exception ex)
{
Console.WriteLine("createConnection" + ex.Message + ex.StackTrace);
throw ex;
}
}
else
{
return(null);
}
}
public void AppVeyorTest_MySql()
{
var connection = new MySql.Data.MySqlClient.MySqlConnection("Server=127.0.0.1;Port=3308;Database=test;User Id=root;");
connection.Open();
var command = connection.CreateCommand();
command.CommandText = "SELECT 1";
int result = 0;
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
result = reader.GetInt32(0);
}
}
Assert.Equal(1, result);
}
public static void GeneraVersion(MySql.Data.MySqlClient.MySqlConnection conexionCD40, string idSistema, string path)
{
// Procedimientos pr = new Procedimientos();
GetVersion v;
v.IdSistema = idSistema;
v.Version = Procedimientos.VersionSectorizacion(conexionCD40, idSistema);
try
{
System.Xml.Serialization.XmlSerializer xmlWriter = new System.Xml.Serialization.XmlSerializer(typeof(GetVersion));
System.IO.StreamWriter xmlFile = new System.IO.StreamWriter(path);
xmlWriter.Serialize(xmlFile, v);
xmlFile.Close();
}
catch (System.IO.IOException e)
{
System.Diagnostics.Debug.Assert(false, e.Message);
}
}
public void fetchUsers()
{
String conStr = System.Configuration.ConfigurationManager.ConnectionStrings["constr"].ToString();
conn = new MySql.Data.MySqlClient.MySqlConnection(conStr);
conn.Open();
queryStr = "SELECT * FROM simple_asp_form.user";
cmd = new MySql.Data.MySqlClient.MySqlCommand(queryStr, conn);
reader = cmd.ExecuteReader();
String name;
String mobile;
String email;
while (reader.HasRows && reader.Read())
{
name = reader.GetString(reader.GetOrdinal("first_name"));
mobile = reader.GetString(reader.GetOrdinal("mobile"));
email = reader.GetString(reader.GetOrdinal("email"));
TableRow row = new TableRow();
TableCell cell1 = new TableCell();
cell1.Text = name;
TableCell cell2 = new TableCell();
cell2.Text = mobile;
TableCell cell3 = new TableCell();
cell3.Text = email;
row.Cells.Add(cell1);
row.Cells.Add(cell2);
row.Cells.Add(cell3);
myTable.Rows.Add(row);
}
reader.Close();
conn.Close();
}
protected void AddNewExerciseToDatabase()
{
try
{
String connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["WebAppConnString"].ToString();
conn = new MySql.Data.MySqlClient.MySqlConnection(connectionString);
conn.Open();
queryString = "INSERT INTO webapppersonalfit.exercises (name, sets, reps, progname, day) "
+ "VALUES(?name, ?sets, ?reps, ?progname, ?day)";
cmd = new MySql.Data.MySqlClient.MySqlCommand(queryString, conn);
cmd.Parameters.AddWithValue("?name", ExNameTextBox.Text);
cmd.Parameters.AddWithValue("?sets", SetTextBox.Text);
cmd.Parameters.AddWithValue("?reps", RepTextBox.Text);
cmd.Parameters.AddWithValue("?progname", programname);
cmd.Parameters.AddWithValue("?day", DayTextBox.Text);
cmd.ExecuteReader();
conn.Close();
}
catch (Exception e)
{
}
}
protected void btnLogin_Click(object sender, EventArgs e)
{
conn = new MySql.Data.MySqlClient.MySqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["ConnString"].ConnectionString);
string query = "select * from Users where username='******' and password='******'";
MySql.Data.MySqlClient.MySqlDataAdapter da = new MySql.Data.MySqlClient.MySqlDataAdapter(query, conn);
DataTable dt = new DataTable();
da.Fill(dt);
if (dt.Rows.Count > 0)
{
// Session["ID"] = Convert.ToInt32(dt.Rows[0]["Id"]);
Session["name"] = dt.Rows[0]["Fname"].ToString() + " " + dt.Rows[0]["lName"].ToString();
Response.Redirect("index.aspx");
}
else
{
lblShow.Text = "Invalid Username and Password";
txtUsername.Text = "";
txtPassword.Text = "";
txtUsername.Focus();
}
}
public static string RegresaCadena_1_ResultadoMysql(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();
string result = "";
foreach (DataRow rw in content.Rows)
{
result = rw[0].ToString();
break;
}
return(result);
}
protected void Page_Load(object sender, EventArgs e)
{
title = "";
allTitles = new List <Movies>();
actualCustomer = new Customers();
actualCustomer = (Customers)Session["myCustomer"];
System.Diagnostics.Debug.WriteLine("Rateeeeeeeeeeee=" + actualCustomer.Email);
if (!IsPostBack)
{
String connString = System.Configuration.ConfigurationManager.ConnectionStrings["WebbAppConnString"].ToString();
conn = new MySql.Data.MySqlClient.MySqlConnection(connString);
conn.Open();
em = actualCustomer.Email;
queryStr = "";
queryStr = "SELECT distinct id, title from movies,bookings,bookings_has_movies,comments where movies.id = bookings_has_movies.movies_id and bookings_has_movies.Bookings_id = bookings.idBookings and bookings.user_email = '" + em + "' and bookings_has_movies.movies_id=comments.movies_id and bookings.user_email=comments.user_email and comments.isSetBetyg=false";
cmd = new MySql.Data.MySqlClient.MySqlCommand(queryStr, conn);
reader = cmd.ExecuteReader();
while (reader.Read())
{
id = reader.GetString(reader.GetOrdinal("id"));
title = reader.GetString(reader.GetOrdinal("title"));
allTitles.Add(new Movies(id, title));
System.Diagnostics.Debug.Write("Sizeeeeeeeeeeeee:" + allTitles.Count + "id: " + id + " title: " + title);
}
conn.Close();
ddlMovies.DataValueField = "id";
ddlMovies.DataTextField = "title";
ddlMovies.DataSource = allTitles;
ddlMovies.DataBind();
}
}
protected void ok(object sender, EventArgs e)
{
UserInfo temp = (UserInfo)Session["currentUser"];
BillPayEntry bpstatus = new BillPayEntry();
if (temp.MyBillPayments == null)
{
}
else
{
// foreach (BillPayEntry i in temp.MyBillPayments)
//{
// i.Status = 1;
//}
MySql.Data.MySqlClient.MySqlConnection co;
string connection = System.Configuration.ConfigurationManager.ConnectionStrings["MySqlConnectionString"].ToString();
co = new MySql.Data.MySqlClient.MySqlConnection(connection);
co.Open();
try
{
string query = $"UPDATE `c432017fa01tirunagarus`.`tirunagarus_WADfl17_RapidBillPay` SET `status` = 1 WHERE `emailAddress` = '{temp.EmailAddress}'; ";
MySql.Data.MySqlClient.MySqlCommand command = new MySql.Data.MySqlClient.MySqlCommand(query, co);
int rows = command.ExecuteNonQuery();
co.Close();
}
catch (Exception ex)
{
Console.WriteLine("session abandon: " + ex.Message);
}
}
Session.Abandon(); //abandons the current session user. no need of argument
// Session.Remove("currentUser");
Response.Redirect("~/default.aspx");
}
protected void Page_Load(object sender, EventArgs e)
{
id = (string)Session["myID"];
System.Diagnostics.Debug.Write("aici" + id + "aici");
//
connString = System.Configuration.ConfigurationManager.ConnectionStrings["WebbAppConnString"].ToString();
conn = new MySql.Data.MySqlClient.MySqlConnection(connString);
conn.Open();
queryStr = "";
queryStr = "SELECT * from movies where id= '" + id + "'";
cmd = new MySql.Data.MySqlClient.MySqlCommand(queryStr, conn);
reader = cmd.ExecuteReader();
while (reader.Read())
{
title = reader.GetString(reader.GetOrdinal("title"));
artists = reader.GetString(reader.GetOrdinal("artists"));
category = reader.GetString(reader.GetOrdinal("category"));
// customerRate = reader.GetString(reader.GetOrdinal("customerRate"));
//coments = reader.GetString(reader.GetOrdinal("coments"));
price = reader.GetString(reader.GetOrdinal("price"));
quantity = reader.GetString(reader.GetOrdinal("quantity"));
imdbLink = reader.GetString(reader.GetOrdinal("imdbLink"));
picture = reader.GetString(reader.GetOrdinal("picture"));
ddlCategory.Text = category;
}
conn.Close();
if (!IsPostBack)
{
textBoxTitle.Text = title;
textBoxDistribution.Text = artists;
textBoxPicture.Text = picture;
textBoxPrice.Text = price;
textBoxQuantity.Text = quantity;
textBoxIMBD.Text = imdbLink;
}
}
protected void RecoveryButton(object sender, EventArgs e) //got code from login.aspx
{
string username = email_text_box.Text;
string pass = "";
MySql.Data.MySqlClient.MySqlConnection co;
string connection = System.Configuration.ConfigurationManager.ConnectionStrings["MySqlConnectionString"].ToString();
try
{
using (co = new MySql.Data.MySqlClient.MySqlConnection(connection))
{
co.Open();
MySql.Data.MySqlClient.MySqlCommand command = new MySql.Data.MySqlClient.MySqlCommand("select emailAddress,password from tirunagarus_WADfl17_UserInfo where emailAddress='" + username + "'", co);
MySql.Data.MySqlClient.MySqlDataReader sqlReader = command.ExecuteReader();
if (sqlReader.HasRows == true)
{
while (sqlReader.Read())
{
// string email = ((string)sqlReader["emailAddress"]);
// if (email == username)
// {
pass = ((string)sqlReader["Password"]);
sendMail(sender, e, pass);
//}
}
}
else
{
ClientScript.RegisterStartupScript(this.GetType(), "myalert", "alert('Sorry we cannot find a matching email. Please Sign Up');", true);
}
}
}
catch { }
}
protected void SubmitEventHandler(object sender, EventArgs e)
{
String connString = System.Configuration.ConfigurationManager.ConnectionStrings["WebAppConnString"].ToString();
conn = new MySql.Data.MySqlClient.MySqlConnection(connString);
conn.Open();
queryString = "";
queryString = "INSERT INTO webapppersonalfit.trainer (userID, name, short_intro, long_intro, url_pic)"
+ "VALUES(?userID, ?name, ?short_intro, ?long_intro, ?url_pic)";
cmd = new MySql.Data.MySqlClient.MySqlCommand(queryString, conn);
cmd.Parameters.AddWithValue("?userID", (String)Session["UserID"]);
cmd.Parameters.AddWithValue("?name", fullnametextbox.Value);
cmd.Parameters.AddWithValue("?short_intro", shortintro.Value);
cmd.Parameters.AddWithValue("?long_intro", longintro.Value);
cmd.Parameters.AddWithValue("?url_pic", urlpic.Value);
cmd.ExecuteReader();
conn.Close();
Response.Redirect("TrainerCatalog.aspx");
}
protected void Page_Load(object sender, EventArgs e)
{
String sConnString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnString"].ToString();
conn = new MySql.Data.MySqlClient.MySqlConnection(sConnString);
if (Request.QueryString["msg"] == "update")
{
lblShow.Text = "Information Update Successfully";
}
if (!IsPostBack)
{
RequiredFieldValidator2.Visible = false;
Label1.Visible = txtConfirm.Visible = false;
txtUsername.Enabled = txtPassword.Enabled = btnChange.Enabled = false;
int user_id = Convert.ToInt16(Session["ID"]);
string query = "select * from users where id=" + user_id + "";
MySql.Data.MySqlClient.MySqlDataReader dr = null;
cmd = new MySql.Data.MySqlClient.MySqlCommand(query, conn);
cmd.CommandType = CommandType.Text;
conn.Open();
dr = cmd.ExecuteReader();
// conn.Close();
while (dr.Read())
{
txtUsername.Text = dr.GetString(1);
txtPassword.Text = dr.GetString(2);
txtFirst.Text = dr.GetString(3);
txtMiddle.Text = dr.GetString(4);
txtLast.Text = dr.GetString(5);
txtAddress.Text = dr.GetString(6);
txtEmail.Text = dr.GetString(7);
txtContact.Text = dr.GetString(8);
}
conn.Close();
}
}
protected void SetTrainerClientDatabase(String progname, String trainerId, String clientId)
{
MySql.Data.MySqlClient.MySqlConnection conn3;
MySql.Data.MySqlClient.MySqlCommand cmd3;
try
{
String connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["WebAppConnString"].ToString();
conn3 = new MySql.Data.MySqlClient.MySqlConnection(connectionString);
conn3.Open();
queryString = "INSERT INTO webapppersonalfit.trainerclient (progname, trainerid, clientid) "
+ "VALUES(?progname, ?trainerid, ?clientid)";
cmd3 = new MySql.Data.MySqlClient.MySqlCommand(queryString, conn3);
cmd3.Parameters.AddWithValue("?progname", progname);
cmd3.Parameters.AddWithValue("?trainerid", trainerId);
cmd3.Parameters.AddWithValue("?clientid", clientId);
cmd3.ExecuteReader();
conn3.Close();
}
catch (Exception ex)
{
}
}
protected void GetProgramInfo()
{
MySql.Data.MySqlClient.MySqlConnection conn2;
MySql.Data.MySqlClient.MySqlCommand cmd2;
MySql.Data.MySqlClient.MySqlDataReader reader;
String connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["WebAppConnString"].ToString();
conn2 = new MySql.Data.MySqlClient.MySqlConnection(connectionString);
conn2.Open();
String query = "SELECT * FROM webapppersonalfit.program WHERE prog_name='" + (String)Session["prog_name"] + "';";
cmd2 = new MySql.Data.MySqlClient.MySqlCommand(query, conn2);
reader = cmd2.ExecuteReader();
if (reader.HasRows && reader.Read())
{
amount = reader.GetString(reader.GetOrdinal("price"));
trainerid = reader.GetString(reader.GetOrdinal("trainer_id"));
}
reader.Close();
conn2.Close();
}
protected void scheduleTransaction(object sender, EventArgs e)
{
BillPayEntry bp = new BillPayEntry();
bp.PaymentTransactionDate = date_text_box.Text;
bp.RecipientBusinessName = bussiness_name.Text;
bp.RecipientBusinessAddress = address_bussiness.Text;
bp.AmountPaid = payment_amount.Text;
bp.PaymentDetails = amount_description.Text;
bp.Status = 0;
UserInfo temp = (UserInfo)Session["currentUser"];
bp.Emailaddress = temp.EmailAddress;
if (temp.MyBillPayments == null)
{
List <BillPayEntry> newpay = new List <BillPayEntry>();
newpay.Add(bp);
temp.MyBillPayments = newpay;
}
else
{
temp.MyBillPayments.Add(bp);
}
string stat = "null";
if (bp.Status == 0)
{
stat = "In Progress";
}
TableRow row = new TableRow
{
Cells =
{
new TableCell {
Text = bp.PaymentTransactionDate
},
new TableCell {
Text = bp.RecipientBusinessName
},
new TableCell {
Text = bp.RecipientBusinessAddress
},
new TableCell {
Text = bp.AmountPaid
},
new TableCell {
Text = stat
}
}
};
transaction_details.Rows.AddAt(2, row);
string msgTo = bp.Emailaddress;
string msgSubject = "New Transaction Scheduling Notification";
string msgBody = "Dear User " + msgTo + ",<br /><br />" +
"You have scheduled a transaction in Rapid Bill Pay on" + bp.PaymentTransactionDate + " <br/> <br />" +
"You can check the status of the transaction by visiting <a href='http://dcm.uhcl.edu/c432017fa01tirunagarus/transactionDetails.aspx'> Compose a trasaction</a> " +
"<br /><br />" +
"Thank you again for using <a href='http://dcm.uhcl.edu/c432017fa01tirunagarus/'>Rapid Bill Pay</a> " +
"<br /><br />" +
"With Best Wishes, <br />" +
"Sumanjali Tirunagaru";
MailMessage mailObj = new MailMessage();
mailObj.Body = msgBody;
mailObj.From = new MailAddress("*****@*****.**", "Admin Team");
mailObj.To.Add(new MailAddress(msgTo));
mailObj.Subject = msgSubject;
mailObj.IsBodyHtml = true;
SmtpClient smtpClient = new System.Net.Mail.SmtpClient("smtp.gmail.com", 587);
smtpClient.UseDefaultCredentials = false;
smtpClient.Credentials = new System.Net.NetworkCredential("*****@*****.**", "!hahahaha");
smtpClient.EnableSsl = true;
try
{
smtpClient.Send(mailObj);
}
catch (Exception ex)
{
}
//db updating
MySql.Data.MySqlClient.MySqlConnection co;
string connection = System.Configuration.ConfigurationManager.ConnectionStrings["MySqlConnectionString"].ToString();
co = new MySql.Data.MySqlClient.MySqlConnection(connection);
co.Open();
string query = "INSERT INTO `c432017fa01tirunagarus`.`tirunagarus_WADfl17_RapidBillPay`(`emailAddress`, `paymentTransactionDate`, `recipientBusinessName`, `recipientBusinessAddress`, `amountPaid`, `paymentDetails`, `status`) VALUES ('" + bp.Emailaddress + "','" + bp.PaymentTransactionDate + "','" + bp.RecipientBusinessName + "','" + bp.RecipientBusinessAddress + "','" + bp.AmountPaid + "','" + bp.PaymentDetails + "'," + bp.Status + ") ";
MySql.Data.MySqlClient.MySqlCommand command = new MySql.Data.MySqlClient.MySqlCommand(query, co);
command.ExecuteNonQuery();
co.Close();
Response.Redirect("~/transactionDetails.aspx");
}
public static DataSet ExecuteDataset(MySqlConnection connection, string commandText)
{
return(MySqlHelper.ExecuteDataset(connection, commandText, null));
}
public static object ExecuteScalar(MySqlConnection connection, string commandText)
{
return(MySqlHelper.ExecuteScalar(connection, commandText, null));
}
protected void LoadTable()
{
try
{
connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["WebAppConnString"].ToString();
conn = new MySql.Data.MySqlClient.MySqlConnection(connectionString);
conn.Open();
String trainerid = (String)Session["UserID"];
String query = "SELECT * FROM webapppersonalfit.userregistration AS UR webapppersonalfit.program AS P, webapppersonalfit.trainerclient AS TC " +
"WHERE TC.trainerid=" + "'" + trainerid + "'" +
"AND P.prog_name=TC.progname " +
"AND UR.userID=TC.clientid;";
cmd = new MySql.Data.MySqlClient.MySqlCommand(query, conn);
reader = cmd.ExecuteReader();
String content = "";
while (true)
{
TableRow r = new TableRow();
r.CssClass = "row100 body";
TableCell c = new TableCell();
c.CssClass = "cell100 column1";
if (reader.HasRows && reader.Read())
{
content = reader.GetString(reader.GetOrdinal("progname"));
c.Controls.Add(new LiteralControl(content));
r.Cells.Add(c);
c = new TableCell();
c.CssClass = "cell100 column2";
content = reader.GetString(reader.GetOrdinal("focus"));
c.Controls.Add(new LiteralControl(content));
r.Cells.Add(c);
c = new TableCell();
c.CssClass = "cell100 column3";
content = reader.GetString(reader.GetOrdinal("duration"));
c.Controls.Add(new LiteralControl(content));
r.Cells.Add(c);
c = new TableCell();
c.CssClass = "cell100 column4";
content = reader.GetString(reader.GetOrdinal("firstname")) + " " + reader.GetString(reader.GetOrdinal("lastname"));
c.Controls.Add(new LiteralControl(content));
r.Cells.Add(c);
//c = new TableCell();
//c.CssClass = "cell100 column5";
//content = reader.GetString(reader.GetOrdinal("userID"));
//Button btn = new Button();
//btn.ID = content;
//btnList.Add(btn);
//c.Controls.Add(btn);
//r.Cells.Add(c);
}
else
{
break;
}
Table1.Rows.Add(r);
}
reader.Close();
conn.Close();
}
catch (Exception e)
{
//passwordTextBox.Value = e.ToString();
}
}
internal MySqlTransaction(MySqlConnection c, IsolationLevel il)
{
conn = c;
level = il;
open = true;
}
private void addSpecialty(String trainerID)
{
String connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["WebAppConnString"].ToString();
MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(connectionString);
conn.Open();
String query = "SELECT * FROM webapppersonalfit.trainerspecialty as TS WHERE TS.trainerID=" + trainerID + ";";
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(query, conn);
MySql.Data.MySqlClient.MySqlDataReader reader = cmd.ExecuteReader();
String primary = "badge-primary";
String secondary = "badge-secondary";
String success = "badge-success";
String danger = "badge-danger";
String warning = "badge-warning";
String info = "badge-info";
String light = "badge-light";
String dark = "badge-dark";
uint i = 1;
while (reader.HasRows && reader.Read())
{
Label span = new Label();
span.CssClass = "badge badge-pill trainer-span ";
span.Text = reader.GetString(reader.GetOrdinal("specialty"));
if (i % 8 == 0)
{
span.CssClass += dark;
}
else if (i % 7 == 0)
{
span.CssClass += light;
}
else if (i % 6 == 0)
{
span.CssClass += info;
}
else if (i % 5 == 0)
{
span.CssClass += warning;
}
else if (i % 4 == 0)
{
span.CssClass += danger;
}
else if (i % 3 == 0)
{
span.CssClass += success;
}
else if (i % 2 == 0)
{
span.CssClass += secondary;
}
else
{
span.CssClass += primary;
}
i++;
MyPlaceholder.Controls.Add(span);
}
}
/// <include file='docs/mysqlcommand.xml' path='docs/ctor3/*'/>
public MySqlCommand(string cmdText, MySqlConnection connection)
: this(cmdText)
{
Connection = connection;
}
protected void Page_Load(object sender, EventArgs e)
{
string connString = ConfigurationManager.ConnectionStrings["DatabaseConnection"].ConnectionString;
conn = new MySql.Data.MySqlClient.MySqlConnection(connString);
}
public override void Configure(MySqlConnection conn)
{
base.Configure(conn);
stream.MaxPacketSize = (ulong)maxPacketSize;
stream.Encoding = Encoding;
}
internal MySqlTransaction(MySqlConnection connection, IsolationLevel isolationLevel)
{
Connection = connection;
IsolationLevel = isolationLevel;
}
protected void Page_Load(object sender, EventArgs e)
{
if (Session["currentUser"] == null)
{
// Response.Redirect("default.aspx");
}
else
{
UserInfo populate = (UserInfo)Session["currentuser"];
string emailid = populate.EmailAddress;
MySql.Data.MySqlClient.MySqlConnection co;
string connection = System.Configuration.ConfigurationManager.ConnectionStrings["MySqlConnectionString"].ToString();
try
{
using (co = new MySql.Data.MySqlClient.MySqlConnection(connection))
{
co.Open();
MySql.Data.MySqlClient.MySqlCommand command = new MySql.Data.MySqlClient.MySqlCommand
("select * from tirunagarus_WADfl17_RapidBillPay where EmailAddress='" + emailid + "'", co);
MySql.Data.MySqlClient.MySqlDataReader sqlReader = command.ExecuteReader();
if (sqlReader.HasRows == true)
{
while (sqlReader.Read())
{
// StudentInfor astudInfor = new StudentInfor();
BillPayEntry bill = new BillPayEntry();
bill.Emailaddress = ((string)sqlReader["emailAddress"]);
bill.PaymentTransactionDate = ((string)sqlReader["paymentTransactionDate"]);
bill.RecipientBusinessName = ((string)sqlReader["recipientBusinessName"]);
bill.RecipientBusinessAddress = ((string)sqlReader["recipientBusinessAddress"]);
bill.AmountPaid = ((string)sqlReader["amountPaid"]);
bill.PaymentDetails = ((string)sqlReader["paymentDetails"]);
bill.Status = ((int)sqlReader["status"]);
string stat = "In Progress";
if (bill.Status == 1)
{
stat = "Completed";
}
TableRow row = new TableRow
{
Cells =
{
new TableCell {
Text = bill.PaymentTransactionDate
},
new TableCell {
Text = bill.RecipientBusinessName
},
new TableCell {
Text = bill.RecipientBusinessAddress
},
new TableCell {
Text = bill.AmountPaid
},
new TableCell {
Text = stat
},
}
};
// studentTable.Rows.Add(row);
transaction_details.Rows.AddAt(2, row);
} //while sql reader()
} //HasRows
else
{
TableRow tRow = new TableRow();
TableCell tCell = new TableCell();
tCell.Text = "No records found";
tRow.Cells.Add(tCell);
transaction_details.Rows.AddAt(2, tRow);
}
} //sql connection
} //try
catch { }
} // else main
}
protected void validateCreate(object sender, EventArgs e)
{
if (privacy_checkbox.Checked)
{
aUser.StateOrProvince = state_text_box.SelectedValue;
aUser.StreetAddress = cust_mail_address.Text;
aUser.ZipCode = zipcode.Text;
aUser.FirstName = first_name.Text;
aUser.LastName = last_name.Text;
aUser.Homephone = homephone.Text;
aUser.CellPhone = cellphone.Text;
aUser.EmailAddress = email_signup.Text;
aUser.Password = password_signup.Text;
aUser.SecurityQuestion = security_questions.SelectedValue;
aUser.SecurityQuestionAnswer = security_answers.Text;
((List <UserInfo>)Application["AllUsersList"]).Add(aUser);
string msgTo = email_signup.Text;
string msgSubject = "New Signing Up Notification";
string msgBody = "Dear User " + msgTo + ",<br /><br />" +
"Thank you for signing up with us. <br/> <br />" +
"You can now access your Rapid Bill Pay account at <a href='http://dcm.uhcl.edu/c432017fa01tirunagarus/'>Rapid Bill Pay </a> " +
"<br /><br />" +
"Thank you again for your Signing Up. If you have any questions, please contact us at" +
"<a href='http://dcm.uhcl.edu/c432017fa01tirunagarus/contactus.aspx'>Contact Us </a> " +
"<br /><br />" +
"With Best Wishes, <br />" +
"Sumanjali Tirunagaru";
MailMessage mailObj = new MailMessage();
mailObj.Body = msgBody;
mailObj.From = new MailAddress("*****@*****.**", "Admin Team");
mailObj.To.Add(new MailAddress(msgTo));
mailObj.Subject = msgSubject;
mailObj.IsBodyHtml = true;
SmtpClient smtpClient = new System.Net.Mail.SmtpClient("smtp.gmail.com", 587);
smtpClient.UseDefaultCredentials = false;
smtpClient.Credentials = new System.Net.NetworkCredential("*****@*****.**", "!hahahaha");
smtpClient.EnableSsl = true;
try
{
smtpClient.Send(mailObj);
}
catch (Exception ex)
{
}
//db updating
MySql.Data.MySqlClient.MySqlConnection co;
string connection = System.Configuration.ConfigurationManager.ConnectionStrings["MySqlConnectionString"].ToString();
co = new MySql.Data.MySqlClient.MySqlConnection(connection);
co.Open();
string query = "INSERT INTO `c432017fa01tirunagarus`.`tirunagarus_WADfl17_UserInfo`(`emailAddress`,`lastName`,`firstName`,`homePhone`,`password`,`cellPhone`,`securityQuestion`,`securityQuestionAnswer`,`StreetAddress`,`ZipCode`) VALUES ('" + aUser.EmailAddress + "','" + aUser.FirstName + "','" + aUser.LastName + "','" + aUser.Homephone + "','" + aUser.Password + "','" + aUser.CellPhone + "','" + aUser.SecurityQuestion + "','" + aUser.SecurityQuestionAnswer + "','" + aUser.StreetAddress + "','" + aUser.ZipCode + "') ";
MySql.Data.MySqlClient.MySqlCommand command = new MySql.Data.MySqlClient.MySqlCommand(query, co);
int rows = command.ExecuteNonQuery();
co.Close();
string strconfirm = "<script>if(window.confirm('Thanks your for signing up. You can now login using the Log in option. An email has also been sent to email address you provided during sign up')){window.location.href='default.aspx'}</script>";
ClientScript.RegisterStartupScript(this.GetType(), "Confirm", strconfirm, false);
}
else
{
ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "alertMessage", "alert('Please Accept Terms And Conditions')", true);
}
}
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();
}
}
}