private void Dispose(MySqlConnection c, MySqlCommand com, MySqlDataReader dr)
{
try {
com?.Dispose();
dr?.Close();
dr?.Dispose();
c.Close();
listConnections.Remove(c);
c.Dispose();
} catch {
}
}
private void _Query(string queryString, QueryAction queryAction, ResultAction resultAction, Delegate postDelegate, bool query)
{
MySqlConnection connection = null;
MySqlCommand command = null;
MySqlDataReader reader = null;
try
{
connection = GetConnection();
command = new MySqlCommand(
queryString,
connection
);
queryAction?.Invoke(command);
if (query)
{
reader = command.ExecuteReader();
while (reader.Read())
{
resultAction?.Invoke(reader);
}
}
else
{
command.ExecuteNonQuery();
}
}
catch (Exception e)
{
Console.WriteLine(e);
}
finally
{
reader?.Dispose();
command?.Dispose();
connection?.Dispose();
try
{
postDelegate?.DynamicInvoke();
}
catch (Exception e)
{
Console.WriteLine(e);
}
}
}
public int cantidadPersonal()
{
conexion();
string query = "SELECT COUNT(*) AS totalPersonal FROM personal";
MySqlCommand cmd = new MySqlCommand(query, con);
MySqlDataReader cursor = cmd.ExecuteReader();
int totalPersonal = 0;
while (cursor.Read())
{
totalPersonal = Convert.ToInt32(cursor["totalPersonal"].ToString());
}
cmd.Dispose();
cursor.Dispose();
con.Close();
return(totalPersonal);
}
public List <Author> GetAuthors()
{
MySqlConnection conn = DB.Connection();
conn.Open();
MySqlCommand cmd = new MySqlCommand(@"SELECT author_id FROM authors_books WHERE book_id = @bookId;", conn);
cmd.Parameters.AddWithValue("@bookId", Id);
MySqlDataReader rdr = cmd.ExecuteReader() as MySqlDataReader;
List <int> authorIds = new List <int> {
};
while (rdr.Read())
{
int authorId = rdr.GetInt32(0);
authorIds.Add(authorId);
}
rdr.Dispose();
List <Author> authors = new List <Author> {
};
foreach (int authorId in authorIds)
{
MySqlCommand command = new MySqlCommand(@"SELECT * FROM authors WHERE id = @AuthorId;", conn);
command.Parameters.AddWithValue("@AuthorId", authorId);
MySqlDataReader commandRdr = command.ExecuteReader() as MySqlDataReader;
while (commandRdr.Read())
{
int thisAuthorId = commandRdr.GetInt32(0);
string authorName = commandRdr.GetString(1);
Author foundAuthor = new Author(authorName, thisAuthorId);
authors.Add(foundAuthor);
}
commandRdr.Dispose();
}
conn.Close();
if (conn != null)
{
conn.Dispose();
}
return(authors);
}
public static string AjaxMethod2(String sltCompany)
{
String str = "{\"olist\":[";
MySqlConnection _sqlConnect1; //Mysql连接对象
MySqlCommand _sqlCommand1; //Mysql命令对象
string _strConnectString1; //连接字符串
_strConnectString1 = System.Configuration.ConfigurationManager.ConnectionStrings["sqljiuye"].ToString(); //！！！数据库名需要修改
_sqlConnect1 = new MySqlConnection(_strConnectString1);
_sqlCommand1 = new MySqlCommand("", _sqlConnect1);
_sqlConnect1.Open();
_sqlCommand1.Parameters.AddWithValue("@sltCompany", sltCompany);
_sqlCommand1.CommandText = "SELECT year FROM operatingconditions WHERE company=@sltCompany";
if (_sqlCommand1.Connection == null)
{
_sqlCommand1.Connection = _sqlConnect1;
}
MySqlDataReader reader = _sqlCommand1.ExecuteReader();
try
{
while (reader.Read())
{
str = str + "\"" + reader.GetString(0) + "\",";
}
str = str.Substring(0, str.Length - 1) + "]}";
reader.Close();
reader.Dispose();
}
catch
{
_sqlConnect1.Close();
_sqlConnect1.Dispose();
return("{\"olist\":[\"error \"]}");;
}
_sqlConnect1.Close();
_sqlConnect1.Dispose();
return(str);
}
void InicjalizacjaDanych()
{
try
{
if (polaczenie.State == ConnectionState.Closed)
{
polaczenie.Open();
}
zapytanie = "select * from users";
komenda = new MySqlCommand(zapytanie, polaczenie);
czytnik = komenda.ExecuteReader();
listaPostaci.Clear();
lista_lisb.Items.Clear();
int licznik = 1;
if (czytnik.HasRows)
{
while (czytnik.Read())
{
listaPostaci.Add(new Postac(czytnik.GetInt32("id"), czytnik["name"].ToString(), czytnik.GetInt32("age")));
lista_lisb.Items.Add(string.Format("{0}. {1}", licznik++, czytnik["name"].ToString()));
}
}
}
catch (Exception ex)
{
string byk = string.Format("Problem podczas inicjalizacji danych:\n{0}", ex.Message);
MessageBox.Show(byk, "Błąd", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
polaczenie.Close();
if (czytnik != null)
{
czytnik.Dispose();
czytnik = null;
}
}
}
public static int idConfPredeterminada(int idExcluir = -1, string tipoDocumento = null)
{
int idConf = -1;
MySqlCommand cmd = null;
MySqlDataReader data = null;
Conexion con = null;
try
{
con = new Conexion();
cmd = con.EjecutarSQL("confPos_consultar");
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("?opcion", 3).Direction = ParameterDirection.Input;
cmd.Parameters.AddWithValue("?idConfPosi", idExcluir).Direction = ParameterDirection.Input;
cmd.Parameters.AddWithValue("?nombreMaquinai", General.getComputerName()).Direction = ParameterDirection.Input;
cmd.Parameters.AddWithValue("?tipoDocumentoi", tipoDocumento).Direction = ParameterDirection.Input;
data = cmd.ExecuteReader();
if (data.Read())
{
idConf = data.IsDBNull(0) ? -1 : data.GetInt32(0);
}
}
catch (Exception e) { throw e; }
finally
{
if (data != null)
{
data.Dispose();
}
if (cmd != null)
{
cmd.Dispose();
}
if (con != null)
{
con.cerrar();
}
}
return(idConf);
}
/// <summary>
/// 获取搜索字段列表
/// </summary>
/// <param name="strTableName"></param>
/// <returns></returns>
public static string GetSearchItemList(string strTableName)
{
string strReturnValue = "";
try
{
MySqlConnection cn = new MySqlConnection(CodeRobot.DBSqlHelper.DBMySQLHelper.ConnectionMySQL());
cn.Open();
string strSql = "SELECT COLUMN_NAME,DATA_TYPE,COLUMN_TYPE,COLUMN_KEY,COLUMN_COMMENT,EXTRA,COLUMN_DEFAULT,CHARACTER_SET_NAME FROM `information_schema`.`COLUMNS` WHERE TABLE_NAME='" + strTableName + "'";
MySqlCommand cmd = new MySqlCommand(strSql, cn);
MySqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
string strColumnName = dr["COLUMN_NAME"].ToString(); //字段列名
string strColumnKey = dr["COLUMN_KEY"].ToString(); //是否是主键
string strColumnComment = dr["COLUMN_COMMENT"].ToString(); //注释
if (strColumnComment.Contains("{搜索}"))
{
string strColumnComment2 = CommonHelper.GetColumnKeyComment(strColumnComment);
string strValue = " <div class=\"layui-inline\">\r\n";
strValue += " <label class=\"layui-form-label\">" + strColumnComment2 + "</label>\r\n";
strValue += " <div class=\"layui-input-inline\">\r\n";
strValue += " <input type=\"text\" id=\"" + strColumnName + "\" name=\"" + strColumnName + "\" placeholder=\"请输入" + strColumnComment2 + "\" autocomplete=\"off\" class=\"layui-input\">\r\n";
strValue += " </div>\r\n";
strValue += " </div>\r\n";
strReturnValue += strValue;
}
}
dr.Dispose();
cn.Close();
}
catch (Exception ex)
{
CodeRobot.Utility.LogHelper.Error(typeof(CodeHelper), ex, "获取搜索字段列表", "GetSearchItemList", false);
}
return(strReturnValue);
}
public List <Empresa> getAll()
{
List <Empresa> empresas = new List <Empresa>();
sql.Append("SELECT e.idEmpresa, e.nomeEmpresa, e.cnpjEmpresa, e.dataFundada, e.ddd, e.telefone, " +
"a.nomeAtividade, ex.descricao " +
"FROM empresa e " +
"INNER JOIN necessidade n ON n.idEmpresa = e.idEmpresa " +
"INNER JOIN atividade a ON a.idAtividade = n.idAtividade " +
"INNER JOIN extraempresa ex ON ex.idEmpresa = e.idEmpresa " +
"ORDER BY e.nomeEmpresa");
cmm.CommandText = sql.ToString();
MySqlDataReader dr = db.executarConsulta(cmm);
while (dr.Read())
{
empresas.Add(new Empresa
{
idEmpresa = (int)dr["idEmpresa"],
nomeEmpresa = (string)dr["nomeEmpresa"],
cnpjEmpresa = (string)dr["cnpjEmpresa"],
dataFundada = (string)dr["dataFundada"],
ddd = (string)dr["ddd"],
telefone = (string)dr["telefone"],
atividade = new Atividade
{
nomeAtividade = (string)dr["nomeAtividade"]
},
extraEmpresa = new ExtraEmpresa
{
descricao = (string)dr["descricao"]
}
});
}
dr.Close();
dr.Dispose();
sql.Clear();
return(empresas);
}
public List <Book> FilterBooks(string query, MySqlConnection ctx)
{
List <Book> result = null;
MySqlCommand command = new MySqlCommand(query, ctx);
MySqlDataReader rdr = command.ExecuteReader();
while (rdr.Read())
{
// initialize list only if the query returns any books
if (result == null)
{
result = new List <Book>();
}
Book book = new Book
{
Id = Convert.ToInt32(rdr[Book.ColumnNames.Id]),
WorkType = rdr[Book.ColumnNames.WorkType]?.ToString(),
BookAuthors = rdr[Book.ColumnNames.BookAuthors]?.ToString(),
ChapAuthors = rdr[Book.ColumnNames.ChapAuthors]?.ToString(),
Editors = rdr[Book.ColumnNames.Editors]?.ToString(),
BookTitle = rdr[Book.ColumnNames.BookTitle]?.ToString(),
ChapTitle = rdr[Book.ColumnNames.ChapTitle]?.ToString(),
FirstPage = rdr[Book.ColumnNames.FirstPage]?.ToString(),
LastPage = rdr[Book.ColumnNames.LastPage]?.ToString(),
Editorial = rdr[Book.ColumnNames.Editorial]?.ToString(),
City = rdr[Book.ColumnNames.City]?.ToString(),
Country = rdr[Book.ColumnNames.Country]?.ToString(),
Year = rdr[Book.ColumnNames.Year]?.ToString(),
Isbn = rdr[Book.ColumnNames.Isbn]?.ToString(),
Report = rdr[Book.ColumnNames.Report]?.ToString()
};
result.Add(book);
}
command.Dispose();
rdr.Dispose();
return(result);
}
// Чтение всех записей из таблицы
void ReadEntries()
{
if (savedProfileMail != null)
{
if (IsThisMailSavedOnServer(profileMail))
{
string query = string.Empty;
// Отлавливаем ошибки
try
{
query = "SELECT * FROM ProfileData WHERE profileMail=?savedProfileMail";
if (con.State.ToString() != "Open")
{
con.Open();
}
using (con)
{
using (cmd = new MySqlCommand(query, con))
{
MySqlParameter oParam = cmd.Parameters.Add("?savedProfileMail", MySqlDbType.VarChar);
oParam.Value = savedProfileMail;
rdr = cmd.ExecuteReader();
if (rdr.HasRows)
{
while (rdr.Read())
{
profileName = rdr["profileName"].ToString();
profileAddress = rdr["profileAddress"].ToString();
phoneNumber = rdr["profileNumber"].ToString();
//profileMail = rdr["profileMail"].ToString();
}
}
rdr.Dispose();
}
}
}
catch (IOException ex) { Debug.Log(ex.ToString()); }
finally { }
}
}
}
public IEnumerable <Eventos> getAllEventos()
{
MySqlCommand cmm = new MySqlCommand();
StringBuilder sql = new StringBuilder();
sql.Append("select * ");
sql.Append("FROM eventos e ");
sql.Append("inner join locais l ON e.idLocal = l.idLocal ");
//para nao dar muitas linhas fiz assim
cmm.CommandText = sql.ToString();
MySqlDataReader dr = conn.executarConsultas(cmm);
while (dr.Read())
{
Eventos eve = new Eventos
{
idEvento = (int)dr["idEvento"],
nomeEvento = (string)dr["nomeEvento"],
dataEvento = (DateTime)dr["dataEvento"],
enderecoEvento = (string)dr["enderecoEvento"],
localEvento = new Local
{
idLocal = (int)dr["idLocal"],
sigla = (string)dr["sigla"],
nomeEstado = (string)dr["nomeEstado"],
nomeCidade = (string)dr["nomeCidade"]
},
};
evento.Add(eve);
}
dr.Dispose();
return(evento);
}
public IEnumerable <Eventos> getAllEmpresa()
{
MySqlCommand cmm = new MySqlCommand();
StringBuilder sql = new StringBuilder();
sql.Append("select * ");
sql.Append("from eventos e ");
sql.Append("inner join eventos_empresas ee on ee.eventos_idEvento = e.idEvento ");
sql.Append("inner join empresas emp on emp.idEmpresa = ee.empresas_idEmpresa ");
sql.Append("order by idEvento desc ");
//para nao dar muitas linhas fiz assim
cmm.CommandText = sql.ToString();
MySqlDataReader dr = conn.executarConsultas(cmm);
while (dr.Read())
{
Eventos eve = new Eventos
{
idEvento = (int)dr["idEvento"],
nomeEvento = (string)dr["nomeEvento"],
dataEvento = (DateTime)dr["dataEvento"],
enderecoEvento = (string)dr["enderecoEvento"],
empresaEvento = new Empresas
{
idEmpresa = (int)dr["idEmpresa"],
nomeEmpresa = (string)dr["nomeEmpresa"],
enderecoEmpresa = (string)dr["enderecoEmpresa"]
},
};
evento.Add(eve);
}
dr.Dispose();
return(evento);
}
/// <summary>
/// elimina periodos creados por el tutor .... esta funcion solo pide el identificador del periodo ya
/// ya que como no pide el identificador del tutor porque para habilitar el eliminar se encesita tener privilegios
/// 1 o 2 que es tutor y super usuario (root)
/// </summary>
/// <param name="id_periodo">codigo periodo</param>
/// <returns>retorna true en caso que todo halla salido bien </returns>
public bool Eliminar_periodo(string id_periodo)
{
string sql = "delete from periodo_curso where id_periodo like '" + id_periodo + "'";
Conexion conn = new Conexion();
conn.IniciarConexion();
MySqlCommand cmd = new MySqlCommand(sql, conn.GetConexion);
MySqlDataReader lector = cmd.ExecuteReader();
try
{
if (lector.RecordsAffected >= 1)
{
cmd.Dispose();
lector.Dispose();
sql = "SELECT COUNT(id_periodo) FROM periodo_documentos WHERE id_periodo LIKE '" + id_periodo + "'";
MySqlDataAdapter adpt = new MySqlDataAdapter(sql, conn.GetConexion);
DataSet ds = new DataSet();
adpt.Fill(ds);
DataTable dt = ds.Tables[0];
if (dt.Rows.Count >= 1)
{
sql = "DELETE FROM periodo_documentos WHERE id_periodo LIKE '" + id_periodo + "'";
cmd = new MySqlCommand(sql, conn.GetConexion);
lector = cmd.ExecuteReader();
if (lector.RecordsAffected >= 1)
{
return(true);
}
}
return(true);
}
}
catch (Exception ex)
{
Log.Set_Log_Error(ex.Message, "TUTOR ELIMINAR PERIODO");
}
return(false);
}
public void PopulateCustomerTable(MySqlConnection conn)
{
string select = "SELECT " +
"cust.customerId," +
"cust.customerName," +
"addr.address," +
"addr.address2," +
"city.city," +
"addr.postalcode," +
"addr.phone," +
"ctry.country " +
"FROM customer cust INNER JOIN address addr ON addr.addressId = cust.addressId " +
"INNER JOIN city ON city.cityId = addr.cityId " +
"INNER JOIN country ctry ON ctry.countryId = city.countryId " +
"ORDER BY cust.customerId ASC";
MySqlCommand cmd = new MySqlCommand(select, conn);
MySqlDataReader reader = cmd.ExecuteReader();
if (reader != null)
{
while (reader.Read())
{
Customer customer = new Customer();
customer.CustomerId = Convert.ToInt32(reader[0]);
customer.CustomerName = reader[1].ToString();
customer.Address1 = reader[2].ToString();
customer.Address2 = reader[3].ToString();
customer.City = reader[4].ToString();
customer.PostalCode = reader[5].ToString();
customer.Phone = reader[6].ToString();
customer.Country = reader[7].ToString();
customer.AddCustomer(customer);
}
}
reader.Close();
reader.Dispose();
}
void UpdateData()
{
MySqlConnection cn = new MySqlConnection(conObject.connString);
if (cn.State != ConnectionState.Open)
{
cn.Open();
}
//Check if the new name already exists in the Database
string sqlCommand = "SELECT * FROM categories WHERE category='" + txtCategoryName.Text.ToString() + "'";
cmd = new MySqlCommand(sqlCommand, cn);
MySqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
XtraMessageBox.Show("Category *''" + txtCategoryName.Text + "''* already Exists in the Database", "EyeBInventory", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
txtCategoryName.Enabled = true;
btnSave.Enabled = true;
txtCategoryName.Enabled = true;
txtCategoryName.SelectAll();
btnSave.Enabled = true;
}
else
{
reader.Close();
reader.Dispose();
//Execute UPDATE on the 'categories' table
sqlCommand = "UPDATE categories SET category='" + txtCategoryName.Text.Trim().ToString() + "' WHERE category = '" + catname + "'";
cmd = new MySqlCommand(sqlCommand, cn);
cmd.ExecuteNonQuery();
//Execute UODATE on the 'stock' table
sqlCommand = "UPDATE stock SET category='" + txtCategoryName.Text.ToString().ToUpper() + "' WHERE category='" + catname + "'";
cmd = new MySqlCommand(sqlCommand, cn);
cmd.ExecuteNonQuery();
XtraMessageBox.Show("Updates Successfull!!", "EyeBInventory", MessageBoxButtons.OK, MessageBoxIcon.Information);
fill_list();
}
}
crlAutorisationVoyage IntfAutorisationVoyage.selectAutorisationVoyage(string numerosAV)
{
#region declaration
IntfDalAgent serviceAgent = new ImplDalAgent();
IntfDalVerification serviceVerification = new ImplDalVerification();
crlAutorisationVoyage AutorisationVoyage = null;
#endregion
#region implementation
if (numerosAV != "")
{
this.strCommande = "SELECT * FROM `autorisationvoyage` WHERE (`numerosAV`='" + numerosAV + "')";
this.serviceConnectBase.openConnection();
reader = this.serviceConnectBase.select(this.strCommande);
if (reader != null)
{
if (reader.HasRows)
{
if (reader.Read())
{
AutorisationVoyage = new crlAutorisationVoyage();
AutorisationVoyage.NumerosAV = reader["numerosAV"].ToString();
AutorisationVoyage.MatriculeAgent = reader["matriculeAgent"].ToString();
AutorisationVoyage.IdVerification = reader["idVerification"].ToString();
AutorisationVoyage.DatePrevueDepart = Convert.ToDateTime(reader["datePrevueDepart"].ToString());
}
}
reader.Dispose();
}
this.serviceConnectBase.closeConnection();
if (AutorisationVoyage != null)
{
AutorisationVoyage.Verification = serviceVerification.selectVerification(AutorisationVoyage.IdVerification);
AutorisationVoyage.Agent = serviceAgent.selectAgent(AutorisationVoyage.MatriculeAgent);
}
}
#endregion
return(AutorisationVoyage);
}
private void updateData()
{
try
{
listView1.Items.Clear();
db.sql = "select `nim`,`name` from `member`";
db.addCMD();
MySqlDataReader r = db.cmd.ExecuteReader();
while (r.Read())
{
if (r.GetString(0) == "001")
{
continue;
}
ListViewItem item = new ListViewItem(r.GetString(0));
item.SubItems.Add(r.GetString(1));
db.sql = $"select id from `income_transaction` where `id`='{thisMonth + thisYear + r.GetString(0)}'";
MySqlConnection cnn = new MySqlConnection("Server=localhost;Database=mydb;Uid=root;Pwd=vroxine;");
cnn.Open();
MySqlCommand cmd = new MySqlCommand(db.sql, cnn);
MySqlDataReader t = cmd.ExecuteReader();
if (!t.Read())
{
item.SubItems.Add("NONE");
}
else
{
item.SubItems.Add("DONE");
}
cnn.Close();
cmd.Dispose();
t.Dispose();
listView1.Items.Add(item);
}
r.Dispose();
db.disposeCmd();
}
catch (MySqlException err)
{
MessageBox.Show(err.Message, err.Number.ToString(), MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void ComboBox1_SelectedIndexChanged(object sender, EventArgs e)
{
try
{
Globals.conn.Open();
MySqlCommand mySqlCommand = new MySqlCommand();
mySqlCommand.Connection = Globals.conn;
mySqlCommand.CommandText = "select * from entree_stock where LIBART ='" + LIBART.Text + "'";
mySqlCommand.CommandType = CommandType.Text;
MySqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader();
mySqlCommand.CommandType = CommandType.Text;
if (mySqlDataReader.Read())
{
REFART.Text = mySqlDataReader["REFART"].ToString();
LIBART.Text = mySqlDataReader["LIBART"].ToString();
quantite.Text = mySqlDataReader["quantite"].ToString();
Fournisseur.Text = mySqlDataReader["fournisseur"].ToString();
Date_entree.Text = mySqlDataReader["date_entree"].ToString();
}
else
{
MessageBox.Show("Article non trouvé");
}
mySqlDataReader.Dispose();
mySqlCommand.Dispose();
Globals.conn.Close();
}
catch (Exception ex)
{
ProjectData.SetProjectError(ex);
Exception ex2 = ex;
MessageBox.Show(ex2.Message.ToString());
ProjectData.ClearProjectError();
}
finally
{
if (Globals.conn != null)
{
Globals.conn.Close();
}
}
}
public StringBuilder Reminder(MySqlConnection conn)
{
string reminder = "reminder_proc";
StringBuilder reminderString = new StringBuilder();
var formatString = "Your {0} appointment with {1} is in {2} minutes.";
MySqlCommand cmd = new MySqlCommand(reminder, conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@currentTime", DateTime.Now.ToUniversalTime().ToString("yyyy-MM-dd HH:mm:ss"));
cmd.Parameters.AddWithValue("@userName", User.displayName);
MySqlDataReader reader = cmd.ExecuteReader();
if (reader != null)
{
while (reader.Read())
{
var valueArray = new object[3] {
reader[0].ToString(), reader[1].ToString(), reader[2].ToString()
};
reminderString.AppendFormat(formatString, valueArray);
}
}
reader.Close();
reader.Dispose();
if (reminderString.ToString() == "")
{
reminderString.Append("You have no appointments in the next 15 minutes.");
return(reminderString);
}
else
{
return(reminderString);
}
}
public List <Experience> GetCollectionExperience()
{
try
{
db = new DbConnect();
List <Experience> experiences = new List <Experience>();
command = new MySqlCommand("GetExperience", db.GetConnection())
{
};
command.CommandType = System.Data.CommandType.StoredProcedure;
reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
experience = new Experience
{
ExperienceEntityID = reader.GetInt32("ExperienceEntityID"),
StartDate = reader.GetDateTime("StartDate"),
EndDate = reader.GetDateTime("EndDate"),
ExperienceTitle = reader.GetString("ExperienceTitle"),
InstitutionName = reader.GetString("InstitutionName"),
ExperienceDescription = reader.GetString("ExperienceDescription"),
ModifiedDate = reader.GetDateTime("ModifiedDate"),
};
experiences.Add(experience);
}
}
reader.Close();
reader.Dispose();
return(experiences);
}
catch (MySqlException mysqlEx)
{
throw mysqlEx;
}
}
/// <summary>
/// 获取新增数据参数
/// </summary>
/// <param name="strTableName">表名</param>
/// <returns></returns>
public static string GetInsertParas(string strTableName)
{
string strReturnValue = "";
try
{
int nNum = 0;
MySqlConnection cn = new MySqlConnection(CodeRobot.DBSqlHelper.DBMySQLHelper.ConnectionMySQL());
cn.Open();
string strSql = "SELECT COLUMN_NAME,COLUMN_KEY,COLUMN_COMMENT FROM `information_schema`.`COLUMNS` WHERE TABLE_NAME='" + strTableName + "'";
MySqlCommand cmd = new MySqlCommand(strSql, cn);
MySqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
string strColumnName = dr["COLUMN_NAME"].ToString(); //字段列名
string strColumnKey = dr["COLUMN_KEY"].ToString().Trim(); //是否是主键
string strColumnComment = dr["COLUMN_COMMENT"].ToString(); //注释
string strName = CommonHelper.GetTableNameUpper(strColumnName);
if (strColumnKey != "PRI")
{
if (strName == "CreatedAt")
{
strReturnValue += " model." + strName + " = DateTime.Now;\r\n";
}
else
{
strReturnValue += " model." + strName + " = para." + strName + ";\r\n";
}
nNum++;
}
}
dr.Dispose();
cn.Close();
}
catch (Exception ex)
{
CodeRobot.Utility.LogHelper.Error(typeof(CodeHelper), ex, "获取新增数据参数", "GetInsertParas", false);
}
return(strReturnValue);
}
public static Dictionary <object, object> GetGameAttributes(ulong gameId)
{
var attributes = new Dictionary <object, object>();
MySqlConnection conn = null;
MySqlDataReader reader = null;
try
{
conn = new MySqlConnection(connectionString);
conn.Open();
MySqlCommand cmd = new MySqlCommand();
cmd.CommandText = string.Format("SELECT * FROM games WHERE id='{0}'", gameId);
cmd.Connection = conn;
reader = cmd.ExecuteReader();
while (reader.Read())
{
attributes = JsonConvert.DeserializeObject <Dictionary <object, object> >(reader.GetString("attributes"));
}
}
catch (Exception ex)
{
Log.Error(ex.ToString());
}
finally
{
if (reader != null)
{
reader.Dispose();
}
if (conn != null)
{
conn.Dispose();
}
}
return(attributes);
}
public List <Specialty> GetSpecialties()
{
MySqlConnection conn = DB.Connection();
conn.Open();
MySqlCommand cmd = new MySqlCommand(@"SELECT specialty_id FROM stylists_specialties WHERE stylist_id = @StylistId;", conn);
cmd.Parameters.AddWithValue("@StylistId", _id);
MySqlDataReader rdr = cmd.ExecuteReader() as MySqlDataReader;
List <int> specialtyIds = new List <int> {
};
while (rdr.Read())
{
int specialtyId = rdr.GetInt32(0);
specialtyIds.Add(specialtyId);
}
rdr.Dispose();
List <Specialty> specialties = new List <Specialty> {
};
foreach (int specialtyId in specialtyIds)
{
MySqlCommand specialtyQuery = new MySqlCommand(@"SELECT * FROM specialties WHERE id = @SpecialtyId;", conn);
specialtyQuery.Parameters.AddWithValue("@SpecialtyId", specialtyId);
MySqlDataReader specialtyQueryRdr = specialtyQuery.ExecuteReader() as MySqlDataReader;
while (specialtyQueryRdr.Read())
{
int thisSpecialtyId = specialtyQueryRdr.GetInt32(0);
string specialtyName = specialtyQueryRdr.GetString(1);
Specialty foundSpecialty = new Specialty(specialtyName, thisSpecialtyId);
specialties.Add(foundSpecialty);
}
specialtyQueryRdr.Dispose();
}
conn.Close();
if (conn != null)
{
conn.Dispose();
}
return(specialties);
}
// Barcode Search Function
private void entrySearch_KeyDown(object sender, KeyEventArgs e)
{
if (e.Key == Key.Return)
{
conn.Close();
string search = entrySearch.Text;
string query = "SELECT prodItem, prodBrand, prodQty, prodSRP, prodRP, prodBought FROM datainventory WHERE prodNo= '" + search + "'";
conn.query(query); //CMD
conn.Open();
MySqlDataReader dr = conn.read();
if (dr.Read())
{
coItem.Text = dr.GetValue(0).ToString();
coBrand.Text = dr.GetValue(1).ToString();
coStocks.Text = dr.GetValue(2).ToString();
coSRP.Text = dr.GetValue(3).ToString();
coRP.Text = dr.GetValue(4).ToString();
coCurrent.Text = dr.GetValue(5).ToString();
stocksck.productQty = Convert.ToInt32(dr.GetValue(2));
int checkQty = Convert.ToInt32(stocksck.productQty);
if (checkQty >= 1)
{
// Closes the reader so the next query will work
dr.Close();
dr.Dispose(); // Dispose
Compute();
}
else
{
MessageBox.Show("Item Out of Stock!");
clearPartial();
return;
}
}
else
{
MessageBox.Show("Item not Found");
clearPartial();
}
}
}
public string Cart_delete(string id_koszyk)
{
if (this.OpenConnection() == true)
{
try
{
string id = "";
string query = "SET NAMES 'utf8' COLLATE 'utf8_unicode_ci'";
MySqlCommand cmd = new MySqlCommand(query, connection);
cmd.ExecuteNonQuery();
query = "SELECT p.id FROM koszyk k inner join produkt p on p.id = k.id_produkt where k.id IN (@id_koszyk);";
cmd = new MySqlCommand(query, connection);
cmd.Parameters.AddWithValue("@id_koszyk", id_koszyk);
MySqlDataReader dataReader = cmd.ExecuteReader();
while (dataReader.Read())
{
id = id + "," + dataReader.GetInt32("id").ToString();
}
dataReader.Dispose();
query = "UPDATE projekt.produkt p SET p.stan=p.stan+1 WHERE p.id IN (@id);";
cmd = new MySqlCommand(query, connection);
cmd.Parameters.AddWithValue("@id", id);
cmd.ExecuteNonQuery();
query = "DELETE FROM koszyk where id IN (@id_koszyk);";
cmd = new MySqlCommand(query, connection);
cmd.Parameters.AddWithValue("@id_koszyk", id_koszyk);
cmd.ExecuteNonQuery();
this.connection.Close();
return("Correct");
}
catch (Exception)
{
this.connection.Close();
return("Błąd zapytania.");
}
}
else
{
return("Błąd połączenia po stronie bazy danych.");
}
}
public static List <Asset> GetAssetList(int custId)
{
List <Asset> assetList = new List <Asset>();
MySqlConnection conn = new MySqlConnection(constr);
conn.Open();
try
{
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "select * from `goraven`.`asset` where `CustId` = " + custId.ToString() + " or `CustId`in (select `CustId` from `goraven`.`cust` where `parentCustId` in (select coalesce(`ParentCustId`,`CustId`) from `goraven`.`cust` where `CustId` =" + custId.ToString() + ")) or `CustId` in (select `ParentCustId` from `goraven`.`cust` where `CustId` =" + custId.ToString() + ");";
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Asset asset = new Asset();
asset.asset = reader.GetString("asset");
asset.custId = Convert.ToInt32(reader.GetString("CustId"));
if (!reader.IsDBNull(2))
{
asset.assetRef = reader.GetString("AssetRef");
}
if (!reader.IsDBNull(3))
{
asset.tag = reader.GetString("Tag");
}
asset.createDateTime = Convert.ToDateTime(reader.GetString("CreateDateTime"));
asset.status = Convert.ToInt32(reader.GetString("Status"));
assetList.Add(asset);
}
reader.Close();
reader.Dispose();
}
catch (Exception ex)
{
}
finally
{
conn.Close();
conn.Dispose();
}
return(assetList);
}
public void check_if_shutdown()
{
string local_ip = null;
try
{
conn = new MySql.Data.MySqlClient.MySqlConnection();
conn.ConnectionString = connectionString;
conn.Open();
MySqlCommand comm = conn.CreateCommand();
comm.CommandText = "SELECT * from ip_monitoring where ip = ?ip AND pc_number = ?pc_number AND shutdown = 1";
comm.Parameters.Add("?ip", this.ip);
comm.Parameters.Add("?pc_number", this.pc_number);
MySqlDataReader reader = comm.ExecuteReader();
while (reader.Read())
{
local_ip = this.ip;
}
reader.Dispose();
conn.Close();
if (local_ip != null)
{
MessageBox.Show("Your Computer has been shutdown by your Instructor");
conn.Open();
comm.CommandText = " UPDATE ip_monitoring set shutdown = 0 where ip = ?ip_address and pc_number = ?pc";
comm.Parameters.Add("?ip_address", local_ip);
comm.Parameters.Add("?pc", this.pc_number);
comm.ExecuteNonQuery();
conn.Close();
/*var psi = new ProcessStartInfo("shutdown", "/s /t 0");
* psi.CreateNoWindow = true;
* psi.UseShellExecute = false;
* Process.Start(psi);*/
}
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
Console.WriteLine(ex.ToString());
MessageBox.Show(ex.ToString());
}
}
public List <Voluntario> getAllDisponibilidade()
{
List <Voluntario> voluntarios = new List <Voluntario>();
sql.Append("SELECT v.idVoluntario, v.nomeVoluntario, t.dddTelefone, t.telefone, a.nomeAtividade, v.status " +
"FROM volunntario v " +
"INNER JOIN disponibilidade d ON d.idVoluntario = v.idVoluntario " +
"INNER JOIN atividade a ON a.idAtividade = d.idAtividade " +
"INNER JOIN telefone t ON t.idVoluntario = a.idAtividade " +
"WHERE d.statusDisponibilidade = 0 " +
"ORDER BY d.idDisponibilidade DESC");
cmm.CommandText = sql.ToString();
MySqlDataReader dr = db.executarConsulta(cmm);
while (dr.Read())
{
voluntarios.Add(new Voluntario
{
idVoluntario = (int)dr["idVoluntario"],
nomeVoluntario = (string)dr["nomeVoluntario"],
status = (string)dr["status"],
telefone =
new Telefone
{
dddTelefone = (string)dr["dddTelefone"],
telefone = (string)dr["telefone"]
},
atividade =
new Atividade
{
nomeAtividade = (string)dr["nomeAtividade"],
}
});
}
sql.Clear();
dr.Close();
dr.Dispose();
return(voluntarios);
}
public string getCustId()
{
string cusid = "";
try
{
conn.Open();
string query = "select cusid from customer_number";
MySqlCommand cmd = new MySqlCommand(query, conn);
MySqlDataReader mdr = cmd.ExecuteReader();
while (mdr.Read())
{
cusid = "SMCUS" + mdr["cusid"].ToString();
}
cmd.Dispose();
mdr.Dispose();
query = "update customer_number set cusid = cusid+1";
cmd = new MySqlCommand(query, conn);
cmd.ExecuteNonQuery();
cmd.Dispose();
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
return(cusid);
/*Random rnd = new Random();
* int day = rnd.Next(10, 30);
* int mnt = rnd.Next(31,60);
* int yer = rnd.Next(61, 99);
* return day.ToString()+mnt.ToString()+yer.ToString();*/
}
