public int fjöldi_rada()
{
MySqlConnection connection;
string connectionString = "Server=10.0.105.33;Database=Leikur;Uid=first;Pwd=first;";
connection = new MySqlConnection(connectionString);
connection.Open();
string query = @"SELECT * FROM spilari";
MySqlCommand cmd = new MySqlCommand(query, connection);
cmd.ExecuteNonQuery();
MySqlDataReader queryCommandReader = cmd.ExecuteReader();
DataTable dataTable = new DataTable();
dataTable.Load(queryCommandReader);
MySqlDataAdapter adapter = new MySqlDataAdapter();
DataSet ds = new DataSet();
adapter.SelectCommand = cmd;
adapter.Fill(ds, "SQL Temp Table");
adapter.Dispose();
cmd.Dispose();
return ds.Tables[0].Rows.Count;
}
protected override void Dispose(bool disposing)
{
if (disposing)
{
_connection?.Dispose();
_adapter?.Dispose();
}
}
public DataTable NotificacaoNova(int idDestinatario, out bool novaMensagem)
{
try
{
DataTable dt = new DataTable();
string dataHora = DateTime.Now.Year + "/" + DateTime.Now.Month + "/" + DateTime.Now.Day + " " +
DateTime.Now.ToLongTimeString();
string sql = " SELECT * FROM NOTIFICACOES " +
" WHERE ID_DES = " + idDestinatario +
" AND VISUALIZACAO = 0 " +
" AND DATA_CADASTRO = " + "'" + dataHora + "'";
MySqlCommand cmd = new MySqlCommand(sql, bd.Conecta());
MySqlDataAdapter da = new MySqlDataAdapter();
da.SelectCommand = cmd;
da.Fill(dt);
da.Dispose();
if (dt.Rows.Count == 1)
{
novaMensagem = true;
}
else
{
novaMensagem = false;
}
bd.Desconecta(cmd.Connection);
return(dt);
}
catch (Exception ex)
{
MessageBox.Show("Erro ao consultar notificações : \n" + ex.Message);
novaMensagem = false;
return(null);
}
}
private void PopulateJudges2()
{
lstScores.Items.Clear();
string judgeid = db.GetID("SELECT * FROM tbljudge WHERE JudgeFName = '" + cmbJudge.Text + "'", "JudgeID").ToString();
MySqlDataAdapter adapter = new MySqlDataAdapter("SELECT * FROM tbljudge WHERE JudgeID = " + judgeid + "", db.OpenConnection());
DataTable table = new DataTable();
table.Clear();
adapter.Fill(table);
db.CloseConnection();
foreach (DataRow row in table.Rows)
{
byJudge.Add(new Judge(row["JudgeID"].ToString()));
}
adapter.Dispose();
table.Dispose();
}
public DataTable SelectQuery(string query)
{
DataTable dt = new DataTable();
try
{
adptr = new MySqlDataAdapter(query, OpenConnection());
adptr.Fill(dt);
adptr.Dispose();
CloseConnection();
return(dt);
}
catch
{
return(null);
}
}
public bool AddGambleStoneInningInfo(GambleStoneRoundInfo round, GambleStoneInningInfo inning, CustomerMySqlTransaction myTrans)
{
MySqlCommand mycmd = null;
try
{
string tableName = GambleStoneInningInfoTableNamePrefix + round.TableName;
mycmd = myTrans.CreateCommand();
string sqlSelectTableText = "show tables where Tables_in_" + MyDBHelper.DataBaseServerName + " = @tableName ";
mycmd.CommandText = sqlSelectTableText;
mycmd.Parameters.AddWithValue("@tableName", tableName);
DataTable table = new DataTable();
MySqlDataAdapter adapter = new MySqlDataAdapter(mycmd);
adapter.Fill(table);
adapter.Dispose();
if (table.Rows.Count == 0)
{
//表不存在，则创建
mycmd.CommandText = string.Format(this.sqlCreateGambleStoneInningInfoTable, round.TableName);
mycmd.ExecuteNonQuery();
}
string sqlText = "insert into " + tableName + " (`id`,`InningIndex`,`RoundID`,`CountDownSeconds`,`State`,`BetRedStone`,`BetGreenStone`,`BetBlueStone`,`BetPurpleStone`,`WinnedColor`,`WinnedTimes`,`WinnedOutStone`) " +
" values (@ID,@InningIndex,@RoundID,@CountDownSeconds,@State,@BetRedStone,@BetGreenStone,@BetBlueStone,@BetPurpleStone,@WinnedColor,@WinnedTimes,@WinnedOutStone ) ";
mycmd.CommandText = sqlText;
mycmd.Parameters.AddWithValue("@ID", inning.ID);
mycmd.Parameters.AddWithValue("@InningIndex", inning.InningIndex);
mycmd.Parameters.AddWithValue("@RoundID", inning.RoundID);
mycmd.Parameters.AddWithValue("@CountDownSeconds", inning.CountDownSeconds);
mycmd.Parameters.AddWithValue("@State", (byte)inning.State);
mycmd.Parameters.AddWithValue("@BetRedStone", inning.BetRedStone);
mycmd.Parameters.AddWithValue("@BetGreenStone", inning.BetGreenStone);
mycmd.Parameters.AddWithValue("@BetBlueStone", inning.BetBlueStone);
mycmd.Parameters.AddWithValue("@BetPurpleStone", inning.BetPurpleStone);
mycmd.Parameters.AddWithValue("@WinnedColor", inning.WinnedColor);
mycmd.Parameters.AddWithValue("@WinnedTimes", inning.WinnedTimes);
mycmd.Parameters.AddWithValue("@WinnedOutStone", inning.WinnedOutStone);
mycmd.ExecuteNonQuery();
return(true);
}
finally
{
mycmd.Dispose();
}
}
public void pic()
{
try {
DatabaseOperation.Open();
cd = DatabaseOperation.for_pic(tname);
da = new MySqlDataAdapter(cd);
DataTable dt = new DataTable();
da.Fill(dt);
byte[] img = (byte[])dt.Rows[0][0];
MemoryStream ms = new MemoryStream(img);
pictureBox1.Image = Image.FromStream(ms);
da.Dispose();
DatabaseOperation.Close();
}catch (MySqlException e) {
MessageBox.Show("Please Register your Thumbmark to your Respective Bank.");
}
}
private void namesDataGrid_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
int index = e.RowIndex;
try
{
DataGridViewRow selectedIndex = namesDataGrid.Rows[index];
patientName = selectedIndex.Cells[0].Value.ToString();
}
catch
{
}
MySqlConnection con = new MySqlConnection();
con.ConnectionString = login.DBconnection;
string name = " select * from patients where Fullname = '" + patientName + "'";
MySqlCommand com = new MySqlCommand(name, con);
MySqlDataAdapter ad;
try
{
con.Open();
ad = new MySqlDataAdapter(com);
//taking email to the table for searchimg its corresponding messages in sentmail table
DataTable table = new DataTable();
ad.Fill(table);
if (table.Rows.Count > 0)
{
fullNameLable.Text = table.Rows[0][3].ToString();
fileNumberLable.Text = table.Rows[0][1].ToString();
ad.Dispose();
}
else
{
}
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
}
con.Close();
}
/// <summary>
/// internal method to Close an instanced Connection
/// </summary>
/// <param name="FreeConnection"></param>
/// <returns></returns>
public MysqlError Close(bool FreeConnection)
{
mLastError = null;
if (mConnection == null)
{
mState = MysqlState.Error;
return(MysqlError.CloseBeforeInit);
}
try {
mConnection.Close();
if (mConnection.State != System.Data.ConnectionState.Closed)
{
mState = MysqlState.Error;
return(MysqlError.FailedToClose);
}
} catch (MySqlException e) {
mState = MysqlState.Error;
mLastError = e;
return(MysqlError.CanNotDisconnectFromDB);
} catch (Exception e) {
mState = MysqlState.Error;
mLastError = e;
return(MysqlError.UnknownClose);
} finally {
// free Memory
if (mCommand != null)
{
mCommand.Dispose();
}
if (mAdapter != null)
{
mAdapter.Dispose();
}
if (FreeConnection == true && mConnection != null)
{
mConnection.Dispose();
}
}
mState = MysqlState.Closed;
return(MysqlError.None);
}
public DataTable EncaminhamentoNovo(string nomeProfissional, out bool novoEncaminhamento)
{
try
{
DataTable dt = new DataTable();
string dataHora = DateTime.Now.Year + "/" + DateTime.Now.Month + "/" + DateTime.Now.Day + " " +
DateTime.Now.ToLongTimeString();
string sql = " SELECT * FROM AGENDAMENTO " +
" WHERE NOME_PROFISSIONAL = " + "'" + nomeProfissional + "'" +
" AND DATA_CADASTRO = " + "'" + dataHora + "'";
MySqlCommand cmd = new MySqlCommand(sql, bd.Conecta());
MySqlDataAdapter da = new MySqlDataAdapter();
da.SelectCommand = cmd;
da.Fill(dt);
da.Dispose();
if (dt.Rows.Count == 1)
{
novoEncaminhamento = true;
}
else
{
novoEncaminhamento = false;
}
bd.Desconecta(cmd.Connection);
return(dt);
}
catch (Exception ex)
{
MessageBox.Show("Erro ao consultar a notificação do encaminhamento : \n" + ex.Message);
novoEncaminhamento = false;
return(null);
}
}
public int AddPersonal(Personal person)
{
int result = 0;
MySqlConnection connection = null;
MySqlCommand mysqlCmd = null;
MySqlDataAdapter mysqlAdapter = null;
String sql;
sql = "INSERT INTO personal (dni, nombre, apellidos, mail) VALUES ('" + person.MyNif.ToString() + "', '" + person.MyNombre.ToString() + "'"
+ ", '" + person.MyApellidos.ToString() + "', '" + person.MyMail.ToString() + "')";
try
{
connection = dataSource.getConnection();
connection.Open();
mysqlCmd = new MySqlCommand(sql, connection);
mysqlAdapter = new MySqlDataAdapter(mysqlCmd);
mysqlCmd.ExecuteNonQuery();
result = 1;
}
catch (Exception e)
{
Console.Write(e);
result = 0;
}
finally
{
if (mysqlCmd != null)
{
mysqlCmd.Dispose();
}
if (mysqlAdapter != null)
{
mysqlAdapter.Dispose();
}
if (connection != null)
{
connection.Close();
}
}
return(result);
}
public int updatePersona(Personal person)
{
int result = 0;
MySqlConnection connection = null;
MySqlCommand mysqlCmd = null;
MySqlDataAdapter mysqlAdapter = null;
String sql;
sql = "UPDATE personal SET dni='" + person.MyNif.ToString() + "',nombre='" + person.MyNombre.ToString() + "',apellidos='" + person.MyApellidos.ToString() + "',"
+ "mail='" + person.MyMail.ToString() + "' WHERE dni ='" + person.MyNif.ToString() + "'";
try
{
connection = dataSource.getConnection();
connection.Open();
mysqlCmd = new MySqlCommand(sql, connection);
mysqlAdapter = new MySqlDataAdapter(mysqlCmd);
mysqlCmd.ExecuteNonQuery();
result = 1;
}
catch (Exception e)
{
Console.Write(e);
result = 0;
}
finally
{
if (mysqlCmd != null)
{
mysqlCmd.Dispose();
}
if (mysqlAdapter != null)
{
mysqlAdapter.Dispose();
}
if (connection != null)
{
connection.Close();
}
}
return(result);
}
//public IncomeMoneyAccount GetIncomeMoneyAccountConfig()
//{
// IncomeMoneyAccount config = null;
// MySqlConnection myconn = null;
// try
// {
// myconn = MyDBHelper.Instance.CreateConnection();
// myconn.Open();
// DataTable table = new DataTable();
// string cmdText = "SELECT * FROM incomemoneyaccount";
// MySqlCommand mycmd = new MySqlCommand(cmdText, myconn);
// MySqlDataAdapter adapter = new MySqlDataAdapter(mycmd);
// adapter.Fill(table);
// if (table.Rows.Count != 0)
// {
// config = new IncomeMoneyAccount();
// config.IncomeMoneyAlipay = Convert.ToString(table.Rows[0]["IncomeMoneyAlipay"]);
// config.IncomeMoneyAlipayRealName = Convert.ToString(table.Rows[0]["IncomeMoneyAlipayRealName"]);
// config.Alipay2DCode = (byte[])table.Rows[0]["Alipay2DCode"];
// }
// table.Clear();
// table.Dispose();
// adapter.Dispose();
// mycmd.Dispose();
// return config;
// }
// catch (Exception exc)
// {
// throw exc;
// }
// finally
// {
// MyDBHelper.Instance.DisposeConnection(myconn);
// }
//}
//public bool SaveIncomeMoneyAccountConfig(IncomeMoneyAccount config)
//{
// MySqlConnection myconn = null;
// try
// {
// myconn = MyDBHelper.Instance.CreateConnection();
// myconn.Open();
// string cmdText = "delete from incomemoneyaccount; " +
// "insert into incomemoneyaccount (IncomeMoneyAlipay, IncomeMoneyAlipayRealName, Alipay2DCode) values " +
// " (@IncomeMoneyAlipay, @IncomeMoneyAlipayRealName, @Alipay2DCode)";
// MySqlCommand mycmd = new MySqlCommand(cmdText, myconn);
// mycmd.Parameters.AddWithValue("@IncomeMoneyAlipay", config.IncomeMoneyAlipay);
// mycmd.Parameters.AddWithValue("@IncomeMoneyAlipayRealName", config.IncomeMoneyAlipayRealName);
// mycmd.Parameters.AddWithValue("@Alipay2DCode", config.Alipay2DCode);
// mycmd.ExecuteNonQuery();
// mycmd.Dispose();
// return true;
// }
// catch (Exception exc)
// {
// throw exc;
// }
// finally
// {
// MyDBHelper.Instance.DisposeConnection(myconn);
// }
//}
#endregion
#region RegisterUserConfig
public RegisterUserConfig GetRegisterUserConfig()
{
RegisterUserConfig config = null;
MySqlConnection myconn = null;
try
{
myconn = MyDBHelper.Instance.CreateConnection();
myconn.Open();
DataTable table = new DataTable();
string cmdText = "SELECT * FROM registeruserconfig";
MySqlCommand mycmd = new MySqlCommand(cmdText, myconn);
MySqlDataAdapter adapter = new MySqlDataAdapter(mycmd);
adapter.Fill(table);
if (table.Rows.Count != 0)
{
config = new RegisterUserConfig();
config.UserCountCreateByOneIP = Convert.ToInt32(table.Rows[0]["UserCountCreateByOneIP"]);
config.GiveToNewUserExp = Convert.ToDecimal(table.Rows[0]["GiveToNewUserExp"]);
config.GiveToNewUserGoldCoin = Convert.ToDecimal(table.Rows[0]["GiveToNewUserGoldCoin"]);
config.GiveToNewUserMines = Convert.ToDecimal(table.Rows[0]["GiveToNewUserMines"]);
config.GiveToNewUserMiners = Convert.ToInt32(table.Rows[0]["GiveToNewUserMiners"]);
config.GiveToNewUserStones = Convert.ToDecimal(table.Rows[0]["GiveToNewUserStones"]);
config.FirstAlipayRechargeGoldCoinAwardMultiple = Convert.ToSingle(table.Rows[0]["FirstAlipayRechargeGoldCoinAwardMultiple"]);
}
table.Clear();
table.Dispose();
adapter.Dispose();
mycmd.Dispose();
return(config);
}
catch (Exception exc)
{
throw exc;
}
finally
{
MyDBHelper.Instance.DisposeConnection(myconn);
}
}
private void textBox13_TextChanged(object sender, EventArgs e)
{
MySqlConnection conn = connection_config.GetDBConnection();
MySqlCommand command = conn.CreateCommand();
MySqlDataAdapter da = new MySqlDataAdapter();
DataTable dt = new DataTable();
string query;
string today = DateTime.Now.ToShortDateString();
try
{
conn.Open();
dt.Clear();
query = "SELECT id,pesel,name,surname,gender,birth_city,birthday,father_name,mother_name,phone,email,post_code,city,address FROM patients WHERE pesel LIKE '" + textBox13.Text + "%'";
command.Connection = conn;
command.CommandText = query;
da = new MySqlDataAdapter();
da.SelectCommand = command;
dt = new DataTable();
da.Fill(dt);
dataGridView1.DataSource = dt;
dataGridView1.AllowUserToAddRows = false;
this.dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
dataGridView1.Columns[0].Visible = false;
dataGridView1.Columns[4].Visible = false;
dataGridView1.Columns[5].Visible = false;
dataGridView1.Columns[6].Visible = false;
dataGridView1.Columns[7].Visible = false;
dataGridView1.Columns[8].Visible = false;
dataGridView1.Columns[9].Visible = false;
dataGridView1.Columns[10].Visible = false;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
da.Dispose();
conn.Close();
}
}
private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
string speed = "";
double c = 0;
int d = e.RowIndex;
try
{
NameL.Text = "Имя выбранного пункта: " + dataGridView1[1, d].Value.ToString();
string connection = "Database=marathonskills;" + "Data Source=127.0.0.1;" + "User Id=root;" + "Password=root;";
MySqlConnection connect = new MySqlConnection(connection);
MySqlCommand command = new MySqlCommand("SELECT `top speed` FROM `speed` WHERE name = '" + dataGridView1[1, d].Value.ToString() + "';", connect);
connect.Open();
MySqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
speed = reader.GetString(0);
}
reader.Close();
connect.Close();
string str = speed;
str = str.Substring(0, str.Length - 4);
c = double.Parse(str);
double ccc = 42 / c;
DiscrL.Text = "Максимальная скорость " + dataGridView1[1, d].Value.ToString() + " - " + speed + ". Это займет " + ccc + " ч. чтобы завершить 42km марафон.";
try
{
MySqlCommand cmd = new MySqlCommand("SELECT image FROM `speed` WHERE name = '" + dataGridView1[1, d].Value.ToString() + "';", connect);
MySqlDataAdapter da = new MySqlDataAdapter(cmd);
DataTable table = new DataTable();
da.Fill(table);
byte[] img = (byte[])table.Rows[0][0];
MemoryStream ms = new MemoryStream(img);
pictureBox1.Image = Image.FromStream(ms);
da.Dispose();
}
catch { }
}
catch
{
MessageBox.Show("Данной ячейки не существует!");
}
}
public IList <T> Select <T>(string TableName, string[] Fields, string Where) where T : new()
{
DataTable dt = new DataTable();
StringBuilder sb_Sql = new StringBuilder();
StringBuilder sb_field = new StringBuilder();
for (int i = 0; i < Fields.Length; i++)
{
sb_field.Append(Fields[i]);
sb_field.Append(",");
}
sb_field.Remove(sb_field.Length - 1, 1);
sb_Sql.Append("select ");
sb_Sql.Append(sb_field.ToString());
sb_Sql.Append(" from ");
sb_Sql.Append(TableName);
sb_Sql.Append(" ");
sb_Sql.Append(Where);
try
{
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
using (MySqlDataAdapter da = new MySqlDataAdapter(sb_Sql.ToString(), connection))
{
da.Fill(dt);
da.Dispose();
}
}
}
catch (Exception ex)
{
SystemError.SystemLog(ex.Message);
dt = null;
}
IList <T> List = null;
if (dt != null)
{
List = ModelConvertHelper <T> .ConvertToModel(dt);
}
return(List);
}
public DataSet ExecuteQuery(string procedureName, List <MySqlParameter> parameters = null)
{
MySqlCommand command = null;
MySqlDataAdapter adap = null;
DataSet ds;
try
{
command = _conn.CreateCommand();
command.Transaction = _transaction;
command.CommandText = procedureName;
command.CommandType = CommandType.StoredProcedure;
if (parameters != null)
{
foreach (MySqlParameter p in parameters)
{
command.Parameters.Add(p);
}
}
adap = new MySqlDataAdapter(command);
ds = new DataSet();
adap.Fill(ds);
return(ds);
}
catch (MySqlException e)
{
throw new Exception("There is something wrong with the database: " + e.Message);
}
catch (Exception e)
{
throw new Exception("General exception: " + e.Message);
}
finally
{
command.Dispose();
adap.Dispose();
command = null;
adap = null;
}
}
private void dataGridView2_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
string len = "";
int d = e.RowIndex;
double c = 0;
try
{
NameL.Text = "Имя выбранного пункта: " + dataGridView2[1, d].Value.ToString();
string connection = "Database=marathonskills;" + "Data Source=127.0.0.1;" + "User Id=root;" + "Password=root;";
MySqlConnection connect = new MySqlConnection(connection);
MySqlCommand command = new MySqlCommand("SELECT length FROM `distance` WHERE name = '" + dataGridView2[1, d].Value.ToString() + "';", connect);
connect.Open();
MySqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
len = reader.GetString(0);
}
reader.Close();
connect.Close();
string str = len;
str = str.Substring(0, str.Length - 1);
c = double.Parse(str);
double ccc = 42000 / c;
DiscrL.Text = "Длина " + dataGridView2[1, d].Value.ToString() + " - " + len + ". Это займет " + ccc + " из них, чтобы покрыть расстояние в 42 км марафона.";
try
{
MySqlCommand cmd = new MySqlCommand("SELECT image FROM `distance` WHERE name = '" + dataGridView2[1, d].Value.ToString() + "';", connect);
MySqlDataAdapter da = new MySqlDataAdapter(cmd);
DataTable table = new DataTable();
da.Fill(table);
byte[] img = (byte[])table.Rows[0][0];
MemoryStream ms = new MemoryStream(img);
pictureBox1.Image = Image.FromStream(ms);
da.Dispose();
}
catch { }
}
catch
{
MessageBox.Show("Данной ячейки не существует!");
}
}
public DataTable DLookupT(string query)
{
DataTable data = null;
bool connectedP = isConnectedDo;
try
{
if (dbType == DatabaseType.MSSQL)
{
SqlDataAdapter a = new SqlDataAdapter(query, MSSQLConn);
data = new DataTable();
a.Fill(data);
a.Dispose();
}
else if (dbType == DatabaseType.MYSQL)
{
MySqlDataAdapter a = new MySqlDataAdapter(query, MySQLConn);
data = new DataTable();
a.Fill(data);
a.Dispose();
}
else if (dbType == DatabaseType.MSACCESS2003 | dbType == DatabaseType.MSACCESS2007)
{
OleDbDataAdapter a = new OleDbDataAdapter(query, MSDBSQLConn);
data = new DataTable();
a.Fill(data);
a.Dispose();
}
}
catch (Exception eee)
{
OnError(eee);
data = new DataTable();
data.Columns.AddRange(new DataColumn[] { new DataColumn("Error"), new DataColumn("InnerError") });
data.Rows.Add(eee.Message, eee.ToString());
}
finally
{
if (!connectedP)
{
closeConnection();
}
}
return(data);
}
public void listpatient()
{
MySqlConnection conn = connection_config.GetDBConnection();
MySqlCommand command = conn.CreateCommand();
MySqlDataAdapter da = new MySqlDataAdapter();
DataTable dt = new DataTable();
string query;
string today = DateTime.Now.ToShortDateString();
try
{
conn.Open();
dt.Clear();
query = "SELECT id,pesel AS 'PESEL',name AS 'Imię',surname AS 'Nazwisko',gender AS 'Płeć',birth_city AS 'Miejsce urodzenia',birthday AS 'Data urodzenia',father_name AS 'Imię ojca',mother_name AS 'Imię matki',phone AS 'Numer telefonu',email AS 'E-mail',post_code AS 'Kod pocztowy',city AS 'Miasto',address AS 'Adres' FROM patients";
command.Connection = conn;
command.CommandText = query;
da = new MySqlDataAdapter();
da.SelectCommand = command;
dt = new DataTable();
da.Fill(dt);
dataGridView1.DataSource = dt;
dataGridView1.AllowUserToAddRows = false;
this.dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
dataGridView1.Columns[0].Visible = false;
dataGridView1.Columns[4].Visible = false;
dataGridView1.Columns[5].Visible = false;
dataGridView1.Columns[6].Visible = false;
dataGridView1.Columns[7].Visible = false;
dataGridView1.Columns[8].Visible = false;
dataGridView1.Columns[9].Visible = false;
dataGridView1.Columns[10].Visible = false;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
da.Dispose();
conn.Close();
}
}
//Actualizar un registro de la tabla monitor
//Parametro = objeto monitor
//return 1 si la insercion es correcta o 0 si es error
public int updateMonitor(Monitor monitor)
{
int result = 0;
MySqlConnection connection = null;
MySqlCommand mysqlCmd = null;
MySqlDataAdapter mysqlAdapter = null;
String fechaConver = monitor.MyfechaNaci.ToString("yyyy/MM/dd");
String sql;
sql = "UPDATE monitor SET dni='" + monitor.Mydni.ToString() + "',fecha_nac='" + fechaConver + "'"
+ " WHERE dni ='" + monitor.Mydni.ToString() + "'";
try
{
connection = dataSource.getConnection();
connection.Open();
mysqlCmd = new MySqlCommand(sql, connection);
mysqlAdapter = new MySqlDataAdapter(mysqlCmd);
mysqlCmd.ExecuteNonQuery();
result = 1;
}
catch (Exception e)
{
Console.Write(e);
result = 0;
}
finally
{
if (mysqlCmd != null)
{
mysqlCmd.Dispose();
}
if (mysqlAdapter != null)
{
mysqlAdapter.Dispose();
}
if (connection != null)
{
connection.Close();
}
}
return(result);
}
private void Form1_Load(object sender, EventArgs e)
{
// TODO: 이 코드는 데이터를 'linktoDataSet.unit' 테이블에 로드합니다. 필요한 경우 이 코드를 이동하거나 제거할 수 있습니다.
this.unitTableAdapter.Fill(this.linktoDataSet.unit);
DataSet ds = new DataSet();
//string strConn = "Server=localhost;Database=linkto;Uid=root;Pwd=apstinc;";
using (MySqlConnection conn = new MySqlConnection(strConn))
{
conn.Open();
string sql = "SELECT Distinct ItemID FROM unit limit 500";
MySqlCommand cmd = new MySqlCommand(sql, conn);
MySqlDataAdapter adpt = new MySqlDataAdapter(sql, conn);
MySqlDataReader rdr = cmd.ExecuteReader();
conn.Close();
adpt.Fill(ds);
adpt.Dispose();
/* while (rdr.Read())
* {
* // Console.WriteLine("{0}", rdr["ItemID"]);
* }*/
rdr.Close();
}
Console.WriteLine("Before Merge: {0}", ds.Tables[0].Rows.Count);
System.Threading.Thread.Sleep(1000);
//데이터베이스 목록 드롭다운에 넣기
List <string> tableList = new List <string>();
foreach (DataRow r in ds.Tables[0].Rows)
{
string list = r["ItemID"].ToString();
tableList.Add(list);
}
//System.Threading.Thread.Sleep(10000);
//Console.WriteLine("{0}", ds.Tables[0].Rows[0]);
Console.WriteLine("{0}", ds.Tables[0]);
string[] data = tableList.ToArray();
comboBox1.Items.AddRange(data);
comboBox1.SelectedIndex = 0;
}
private void allowanceCombo_SelectedIndexChanged(object sender, EventArgs e)
{
MySqlConnection con = new MySqlConnection();
con.ConnectionString = Home.DBconnection;
string getAllowanceId = "select * from allowance where allowanceName = '" + allowanceCombo.Text + "'";
MySqlCommand com = new MySqlCommand(getAllowanceId, con);
MySqlDataAdapter da;
DataTable table = new DataTable();
try
{
con.Open();
da = new MySqlDataAdapter(com);
da.Fill(table);
da.Dispose();
if (table.Rows.Count > 0)
{
string getAllowanceID = "select * from employeeallowance where empCode = '" + empCode + "' and allowanceID = '" + table.Rows[0][0].ToString() + "'";
MySqlCommand com1 = new MySqlCommand(getAllowanceID, con);
DataTable tab = new DataTable();
da = new MySqlDataAdapter(com1);
da.Fill(tab);
da.Dispose();
if (tab.Rows.Count > 0)
{
AllowID = tab.Rows[0][0].ToString();
allowanceAmountTxt.Text = tab.Rows[0][3].ToString();
allowanceamount = tab.Rows[0][3].ToString();
}
}
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
}
con.Close();
}
//Actualiza un registro de la tabla administrador
//Parametro = objeto Administrador
//Devuelve 1 en caso de insercion correcta o 0 en caso de error
public int updateAdmin(Administrador admin)
{
int result = 0;
MySqlConnection connection = null;
MySqlCommand mysqlCmd = null;
MySqlDataAdapter mysqlAdapter = null;
String sql;
sql = "UPDATE administrador SET dni='" + admin.Mydni.ToString() + "',num_SS='" + admin.Mynum_SS.ToString() + "',titulacion='" + admin.Mytitulacion.ToString() + "'"
+ " WHERE dni ='" + admin.Mydni.ToString() + "'";
try
{
connection = dataSource.getConnection();
connection.Open();
mysqlCmd = new MySqlCommand(sql, connection);
mysqlAdapter = new MySqlDataAdapter(mysqlCmd);
mysqlCmd.ExecuteNonQuery();
result = 1;
}
catch (Exception e)
{
Console.Write(e);
result = 0;
}
finally
{
if (mysqlCmd != null)
{
mysqlCmd.Dispose();
}
if (mysqlAdapter != null)
{
mysqlAdapter.Dispose();
}
if (connection != null)
{
connection.Close();
}
}
return(result);
}
public DataTable ListProducNombre(string[] str)
{
string proc = "BuscarNombre";
DataTable tabla;
MySqlDataAdapter p = new MySqlDataAdapter(proc, ConexionDB.AbrirConexion());
DataTable dt = new DataTable();
p.SelectCommand.CommandType = CommandType.StoredProcedure;
p.SelectCommand.Parameters.Add("@cadena", MySqlDbType.String).Value = str[0];
p.Fill(dt);
tabla = dt;
p.Dispose();
ConexionDB.CerrarConexion();
return(tabla);
}
public DataSet GetDataset(String sql)
{
con.Close();
con.Open();
MySqlDataAdapter Adapter = new MySqlDataAdapter();
DataSet ds = new DataSet();
MySqlCommand cmd;
cmd = SqlCmnd();
cmd.CommandText = sql;
cmd.CommandTimeout = 12000;
Adapter.SelectCommand = cmd;
Adapter.Fill(ds);
Adapter.Dispose();
cmd.Dispose();
con.Close();
return(ds);
}
public DataTable SelectTable(string query)
{
DataTable dt = new DataTable();
try
{
myAdapter = new MySqlDataAdapter(query, OpenConnection());
myAdapter.Fill(dt);
myAdapter.Dispose();
CloseConnection();
return(dt);
}
catch (Exception e)
{
MessageBox.Show(e.Message);
return(null);
}
}
public void LoadList()
{
var asof = (DateTime)AsOfDate.SelectedDate;
//var date = (DateTime)Date.SelectedDate;
var asofStr = $"STR_TO_DATE('{asof.ToString("dd/MM/yyyy")}', '%d/%m/%Y')";
// var dateStr = $"STR_TO_DATE('{date.ToString("dd/MM/yyyy")}', '%d/%m/%Y')";
MySqlCommand cmd = null;
var query = $"Select rate,ccy,date,as_of_date,version,term from interest_rate where Name='{Name.Text}' and ccy = '{Ccy.Text}' and as_of_date={asofStr} ";
cmd = new MySqlCommand(query, _db);
var dataAdapter = new MySqlDataAdapter(cmd);
dataAdapter.Fill(_dt);
dataAdapter.Dispose();
cmd.Dispose();
_data.ItemsSource = _dt.DefaultView;
}
void Reload(ComboBox cbText)
{
using (MySqlConnection mysqlCon = new MySqlConnection(connectionString))
{
mysqlCon.Open();
MySqlCommand command = new MySqlCommand("SELECT * FROM purchaseordertable WHERE CONCAT(`poNum`) like '%" + cbText.Text + "%'", mysqlCon);
MySqlDataAdapter adapter = new MySqlDataAdapter(command);
System.Data.DataTable table = new System.Data.DataTable();
adapter.Fill(table);
poGrid.DataSource = table;
poGrid.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;
hideColumns();
labelPONum.Text = cbText.Text;
adapter.Dispose();
mysqlCon.Close();
}
}
static public DataTable ResponseQuery(string query, DbParameter[] parameters = null)
{
var con = Con();
con.Open();
DataSet ds = new DataSet();
MySqlCommand command = new MySqlCommand(query, con);
MySqlDataAdapter da = new MySqlDataAdapter(command);
if (parameters != null)
{
command.Parameters.AddRange(parameters);
}
da.Fill(ds);
da.Dispose();
con.Close();
return(ds.Tables[0]);
}
public static DataTable dt(string connStr, string sql)
{
DataTable dt = new DataTable();
using (MySqlConnection conn = new MySqlConnection(connStr))
{
conn.Open();
MySqlCommand cmd = new MySqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = sql;
cmd.Connection = conn;
MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
adapter.Fill(dt);
adapter.Dispose();
conn.Close();
}
return(dt);
}
public string select_from()
{
MySqlConnection connection;
string connectionString = "Server=10.0.105.33;Database=Leikur;Uid=first;Pwd=first;";
connection = new MySqlConnection(connectionString);
connection.Open();
string query = @"SELECT * FROM spilari";
MySqlCommand cmd = new MySqlCommand(query, connection);
cmd.ExecuteNonQuery();
MySqlDataReader queryCommandReader = cmd.ExecuteReader();
DataTable dataTable = new DataTable();
dataTable.Load(queryCommandReader);
MySqlDataAdapter adapter = new MySqlDataAdapter();
DataSet ds = new DataSet();
adapter.SelectCommand = cmd;
adapter.Fill(ds, "SQL Temp Table");
adapter.Dispose();
cmd.Dispose();
String rowText = string.Empty;
int topRows = ds.Tables[0].Rows.Count;
for (int i = 0; i < topRows; i++)
{
foreach (DataColumn column in dataTable.Columns)
{
rowText += dataTable.Rows[i][column.ColumnName] + "|";
}
rowText = rowText + "-";
}
connection.Close();
return rowText;
}
