/// <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;
}
}
/// <summary>
/// 一个公用interface执行所有非 select语句
/// </summary>
/// <param name="non_query_type"></param>
/// <returns>返回影响数据库几行</returns>
public int GeneralNonSelectQuery(MySqlCommand cmd)
{
int iReturn = 0;
string connStr = sql.GetSQL(sql.SQL.S_CONNECTION_STR);
MySqlConnection conn = new MySqlConnection(connStr);
try
{
conn.Open();
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
iReturn = cmd.ExecuteNonQuery();
cmd.Dispose();
conn.Close();
conn.Dispose();
}
catch (Exception ex)
{
cmd.Dispose();
conn.Close();
conn.Dispose();
}
return iReturn;
}
/// <summary>
/// 关闭连接
/// </summary>
public static void CloseConnection()
{
if (ConnectionPool_mysql.con.State == System.Data.ConnectionState.Open)
{
if (m_isTransaction == true)
{
trans.Dispose();
m_isTransaction = false;
}
con.Close();
con.Dispose();
}
}
public string ConnectionTest()
{
s = ConfigurationManager.AppSettings["MySql"].ToString();
MySqlConnection mysqlcon = new MySqlConnection(s);
MySqlCommand mysqlcom = new MySqlCommand("", mysqlcon);
MySqlDataAdapter mysqldataadp = new MySqlDataAdapter(mysqlcom);
DataSet ds=new DataSet();
string message = "";
try
{
//mysqlcon.Open();
if (mysqlcon.State != ConnectionState.Open)
{
mysqlcon.Open();
}
mysqldataadp.SelectCommand.CommandText="select * from ow_articletype";
message = "打开数据库成功！";
mysqldataadp.Fill(ds);
if (ds.Tables.Count > 0)
{
message = ds.Tables[0].Rows[0][1].ToString();
}
else
{
message = "可惜查不到数据";
}
}
catch (Exception ex)
{
if(mysqlcon.State==ConnectionState.Open)
{
mysqlcon.Close();
mysqlcon.Dispose();
}
message = "数据库访问失败！ 信息："+ex.Message;
}
finally
{
if (mysqlcon.State == ConnectionState.Open)
{
mysqlcon.Close();
}
mysqlcon.Dispose();
mysqlcom.Dispose();
mysqldataadp.Dispose();
}
return message;
}
public void log(string msg, string typ)
{
Console.WriteLine("EAkzg_wyrzyg_task log: " + msg + ", typ: " + typ);
using (MySql.Data.MySqlClient.MySqlConnection connDebug = new MySql.Data.MySqlClient.MySqlConnection())
{
using (MySqlCommand cmd = new MySqlCommand())
{
string insert = "";
try
{
connDebug.ConnectionString = myConnectionString;
connDebug.Open();
AddCommasIfRequired(msg);
AddCommasIfRequired(typ);
insert = "INSERT INTO eakzg_schema.eakzg_wyrzyg_debug(data,msg,typ) VALUES ( " +
@" now(), '" + msg + "','" + typ + "')";
cmd.Connection = connDebug;
cmd.CommandText = insert;
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine("Blad log msg: " + ex.Message + " msq: " + msg + " typ:" + typ);
}
finally
{
connDebug.Close();
connDebug.Dispose();
cmd.Dispose();
}
}
}
}
protected void ContactsDropDownList_SelectedIndexChanged(object sender, EventArgs e)
{
ProjectsDropDownList.Items.Clear();
ProjectsDropDownList.Items.Add(new ListItem("--Select Project--", ""));
ProjectsDropDownList.AppendDataBoundItems = true;
DBConnection = new MySqlConnection(objUKFilmLocation.DBConnect);
DBCommand = DBConnection.CreateCommand();
DBConnection.Open();
DBCommand.CommandText = "select ProjectID, WorkingTitle from ProjectDetails where ContactID = '" + ProjectContactsDropDownList.SelectedItem.Value + "' order by WorkingTitle Asc;";
ProjectsDropDownList.DataSource = DBCommand.ExecuteReader();
ProjectsDropDownList.DataTextField = "WorkingTitle";
ProjectsDropDownList.DataValueField = "ProjectID";
ProjectsDropDownList.DataBind();
if (ProjectsDropDownList.Items.Count > 1)
{
ProjectsDropDownList.Enabled = true;
}
ProjectsDropDownList.Visible = true;
DBConnection.Close();
DBConnection.Dispose();
}
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(ExamQuestion entity)
{
string sql = "INSERT INTO tb_exam_question (exam_sequence,questionType,answer,question,option1,option2,option3,option4,option5,option6,option7,option8)";
sql = sql + " VALUE (@exam_sequence,@questionType,@answer,@question,@option1,@option2,@option3,@option4,@option5,@option6,@option7,@option8)";
using (MySqlConnection mycn = new MySqlConnection(mysqlConnection))
{
mycn.Open();
MySqlCommand command = new MySqlCommand(sql, mycn);
command.Parameters.AddWithValue("@exam_sequence", entity.exam_sequence);
command.Parameters.AddWithValue("@questionType", entity.questionType);
command.Parameters.AddWithValue("@answer", entity.answer);
command.Parameters.AddWithValue("@question", entity.question);
command.Parameters.AddWithValue("@option1", entity.option1);
command.Parameters.AddWithValue("@option2", entity.option2);
command.Parameters.AddWithValue("@option3", entity.option3);
command.Parameters.AddWithValue("@option4", entity.option4);
command.Parameters.AddWithValue("@option5", entity.option5);
command.Parameters.AddWithValue("@option6", entity.option6);
command.Parameters.AddWithValue("@option7", entity.option7);
command.Parameters.AddWithValue("@option8", entity.option8);
int i = command.ExecuteNonQuery();
mycn.Close();
mycn.Dispose();
return i;
}
}
public int DB_Connect()
{
try
{
if (conn != null)
{
if (conn.State == ConnectionState.Open)
{
return(0);
}
}
conn = new MySql.Data.MySqlClient.MySqlConnection();
conn.ConnectionString = myConnectionString;
conn.Open();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
switch (ex.Number)
{
case 0:
// MessageBox.Show("Cannot connect to server. Contact administrator");
break;
case 1045:
// MessageBox.Show("Invalid username/password, please try again");
break;
}
conn.Dispose();
}
return(0);
}
/// <summary>
/// 修改数据
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
public int Update(AgentComplain entity)
{
string sql = "UPDATE tb_complain SET agentNo=@agentNo,userName=@userName,processCode=@processCode,joinTime=@joinTime,joinMenu=@joinMenu,content=@content,processBranchCode=@processBranchCode,processBranchName=@processBranchName,";
sql = sql + " replyTime=@replyTime,comment=@comment,createTime=@createTime 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("@agentNo", entity.agentNo);
command.Parameters.AddWithValue("@userName", entity.userName);
command.Parameters.AddWithValue("@sequence", entity.sequence);
command.Parameters.AddWithValue("@processCode", entity.processCode);
command.Parameters.AddWithValue("@joinTime", entity.joinTime);
command.Parameters.AddWithValue("@joinMenu", entity.joinMenu);
command.Parameters.AddWithValue("@content", entity.content);
command.Parameters.AddWithValue("@processBranchCode", entity.processBranchCode);
command.Parameters.AddWithValue("@processBranchName", entity.processBranchName);
command.Parameters.AddWithValue("@replyTime", entity.replyTime);
command.Parameters.AddWithValue("@comment", entity.comment);
command.Parameters.AddWithValue("@createTime", entity.createTime);
int i = command.ExecuteNonQuery();
mycn.Close();
mycn.Dispose();
return i;
}
}
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 ActionResult conn()
{
MySqlConnection con = new MySqlConnection("server=us-cdbr-iron-east-02.cleardb.net;database=ad_55b0f9c367ab0d1;uid=ba331630319965;pwd=603b8557");
MySqlDataReader dr;
try
{
con.Open();
//MySqlCommand cmd = new MySqlCommand("create table Service_Application(service_id int, firstname varchar(50),middlename varchar(50),lastname varchar(50) )", con);
MySqlCommand cmd = new MySqlCommand("select * from Service_Application", con);
//cmd.ExecuteNonQuery();
dr = cmd.ExecuteReader();
string result = string.Empty;
while (dr.Read())
{
for (int i = 0; i < dr.FieldCount; i++)
result+= dr.GetValue(i).ToString() + ", ";
}
TempData["name"] = result;
return View("~/Views/Shared/View.cshtml");
}
finally
{
con.Dispose();
}
}
public DataTable Consultar(string strNumDoc)
{
DataTable dtDatosCliente = new DataTable();
MySqlConnection oSC = new MySqlConnection();
try
{
cls_Conexion oConexion = new cls_Conexion();
oSC = oConexion.conexion();
oSC.Open();
MySqlDataAdapter daCliente = new MySqlDataAdapter("SP_Consultar_Persona", oSC);
daCliente.SelectCommand.CommandType = CommandType.StoredProcedure;
daCliente.SelectCommand.Parameters.AddWithValue("inNro_Doc", strNumDoc);
daCliente.Fill(dtDatosCliente);
return dtDatosCliente;
}
catch (Exception e)
{
string strError = e.Message;
throw new Exception(strError);
throw;
}
finally
{
oSC.Close();
oSC.Dispose();
}
}
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(InvoicePayment entity)
{
string sql = "INSERT INTO tb_invoice_payment (agentNo,agentName,month,receivedTime,processTime,content,invoiceFee,invoiceType,invoiceNo,payStatus) VALUE (@agentNo,@agentName,@month,@receivedTime,@processTime,@content,@invoiceFee,@invoiceType,@invoiceNo,@payStatus)";
using (MySqlConnection mycn = new MySqlConnection(mysqlConnection))
{
mycn.Open();
MySqlCommand command = new MySqlCommand(sql, mycn);
command.Parameters.AddWithValue("@agentNo", entity.agentNo);
command.Parameters.AddWithValue("@agentName", entity.agentName);
command.Parameters.AddWithValue("@month", entity.month);
command.Parameters.AddWithValue("@receivedTime", entity.receivedTime);
command.Parameters.AddWithValue("@processTime", entity.processTime);
command.Parameters.AddWithValue("@content", entity.content);
command.Parameters.AddWithValue("@invoiceFee", entity.invoiceFee);
command.Parameters.AddWithValue("@invoiceType", entity.invoiceType);
command.Parameters.AddWithValue("@invoiceNo", entity.invoiceNo);
command.Parameters.AddWithValue("@payStatus", entity.payStatus);
int i = command.ExecuteNonQuery();
mycn.Close();
mycn.Dispose();
return i;
}
}
/// <summary>
/// 修改数据
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
public int Update(InvoicePayment entity)
{
string sql = "UPDATE tb_invoice_payment SET agentNo=@agentNo,agentName=@agentName,month=@month,";
sql = sql + "receivedTime=@receivedTime,processTime=@processTime,content=@content,invoiceFee=@invoiceFee";
sql = sql + "invoiceType=@invoiceType,invoiceNo=@invoiceNo,payStatus=@payStatus";
sql = sql + " where agentNo=@agentNo and month=@month and invoiceNo=@invoiceNo";
//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("@agentNo", entity.agentNo);
command.Parameters.AddWithValue("@agentName", entity.agentName);
command.Parameters.AddWithValue("@month", entity.month);
command.Parameters.AddWithValue("@receivedTime", entity.receivedTime);
command.Parameters.AddWithValue("@processTime", entity.processTime);
command.Parameters.AddWithValue("@content", entity.content);
command.Parameters.AddWithValue("@invoiceFee", entity.invoiceFee);
command.Parameters.AddWithValue("@invoiceType", entity.invoiceType);
command.Parameters.AddWithValue("@invoiceNo", entity.invoiceNo);
command.Parameters.AddWithValue("@payStatus", entity.payStatus);
int i = command.ExecuteNonQuery();
mycn.Close();
mycn.Dispose();
return i;
}
}
public bool _Adicionar(Lojas _loja, string stringConnect)
{
try
{
MySqlConnection conexao = new MySqlConnection(stringConnect);
var sql = new MySqlCommand("INSERT INTO `Loja` (`L_Loja` , `L_Responsavel` , `L_Telefone` , `L_Email` ) VALUES (@L_Loja, @L_Responsavel, @L_Telefone, @L_Email);", conexao);
sql.Parameters.AddWithValue("@L_Loja", _loja.l_loja);
sql.Parameters.AddWithValue("@L_Responsavel", _loja.l_responsavel);
sql.Parameters.AddWithValue("@L_Telefone", _loja.l_telefone);
sql.Parameters.AddWithValue("@L_Email", _loja.l_email);
conexao.Open();
sql.ExecuteScalar();
if (conexao.State == ConnectionState.Open)
{
conexao.Close();
conexao.Dispose();
}
sql.Dispose();
return true;
}
catch (Exception)
{
return false;
}
}
public static Session GetSession(string SessionToken)
{
MySqlConnection dbcon = new MySqlConnection(connectionString);
MySqlCommand command = new MySqlCommand();
dbcon.Open();
command.CommandText = "SELECT * FROM sessions WHERE SessionToken=@SessionToken";
command.Parameters.AddWithValue("@SessionToken", Program.sessionToken);
command.Connection = dbcon;
command.ExecuteNonQuery();
Session session = new Session();
MySqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
session.DateCreated = (int)reader["DateCreated"];
session.LastHeartbeat = (int)reader["LastHeartbeat"];
}
dbcon.Close();
dbcon.Dispose();
command.Dispose();
return session;
}
public static void SqlLogin()
{
MySqlConnection conn = null;
conn = new MySqlConnection("Host = localhost;Database = test;Username = root;Password = kaokao");
string sql = "select * from user where username='******' and password='******' and hwid='" + NeededValue.Hwid + "'";
conn.Open();
MySqlCommand comm = new MySqlCommand(sql, conn);
MySqlDataReader reader = comm.ExecuteReader();
try
{
if (reader.Read())
{
NeededValue.Loginflag = 1;
}
else
{
NeededValue.Loginflag = 0;
}
}
catch (Exception error)
{
MessageBox.Show(text: error.Message);
}
finally
{
conn.Close();
conn.Dispose();
reader .Dispose();
}
}
public static BaseResult TestConnection(string connectionString)
{
var result = new BaseResult();
try
{
var connection = new MySql.Data.MySqlClient.MySqlConnection(connectionString);
connection.Open();
connection.Close();
connection.Dispose();
}
catch (MySqlException e)
{
if (e.InnerException.Message.ToUpperInvariant().Contains("UNKNOWN DATABASE"))
{
SchemaExists = false;
}
result.success = false;
result.message = e.Message;
return(result);
}
result.success = true;
return(result);
}
public List<Capa_Objetos.cls_Ciudad> listar_Ciudad()
{
MySqlConnection oSC = new MySqlConnection();
var ListaCiudad = new List<Capa_Objetos.cls_Ciudad>();
try
{
cls_Conexion oConexion = new cls_Conexion();
oSC = oConexion.conexion();
oSC.Open();
MySqlCommand oSCmd = new MySqlCommand();
oSCmd.Connection = oSC;
oSCmd.CommandType = CommandType.Text;
oSCmd.CommandText = "SELECT * FROM tbl_Ciudad";
var dr = oSCmd.ExecuteReader();
while (dr.Read())
{
ListaCiudad.Add(new Capa_Objetos.cls_Ciudad(dr.GetInt32(0), dr.GetString(1)));
}
}
catch (Exception e)
{
string strError = e.Message;
throw new Exception(strError);
throw new Exception("Error al Recuperar Datos Verifique");
}
finally
{
oSC.Close();
oSC.Dispose();
}
return ListaCiudad;
}
private bool disconnectFromDatabase()
{
conn.Close();
conn.Dispose();
conn = null;
return(true);
}
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(AgentWechatAccount entity)
{
string sql = "INSERT INTO agent_wechat_account (agentNo,agentName,branchNo,branchName,regionName,contactId,contactName,contactEmail,contactTel,contactWechat,feeRight,policyRight,performanceRight,studyRight,complainRight,monitorRight,errorRight,contactRight,type)";
sql = sql + " VALUE (@agentNo,@agentName,@branchNo,@branchName,@regionName,@contactId,@contactName,@contactEmail,@contactTel,@contactWechat,@feeRight,@policyRight,@performanceRight,@studyRight,@complainRight,@monitorRight,@errorRight,@contactRight,@type)";
using (MySqlConnection mycn = new MySqlConnection(mysqlConnection))
{
mycn.Open();
MySqlCommand command = new MySqlCommand(sql, mycn);
command.Parameters.AddWithValue("@agentNo", entity.agentNo);
command.Parameters.AddWithValue("@agentName", entity.agentName);
command.Parameters.AddWithValue("@branchNo", entity.branchNo);
command.Parameters.AddWithValue("@branchName", entity.branchName);
command.Parameters.AddWithValue("@regionName", entity.regionName);
command.Parameters.AddWithValue("@contactId", entity.contactId);
command.Parameters.AddWithValue("@contactName", entity.contactName);
command.Parameters.AddWithValue("@contactEmail", entity.contactEmail);
command.Parameters.AddWithValue("@contactTel", entity.contactTel);
command.Parameters.AddWithValue("@contactWechat", entity.contactWechat);
command.Parameters.AddWithValue("@feeRight", entity.feeRight);
command.Parameters.AddWithValue("@policyRight", entity.policyRight);
command.Parameters.AddWithValue("@performanceRight", entity.performanceRight);
command.Parameters.AddWithValue("@studyRight", entity.studyRight);
command.Parameters.AddWithValue("@complainRight", entity.complainRight);
command.Parameters.AddWithValue("@monitorRight", entity.monitorRight);
command.Parameters.AddWithValue("@errorRight", entity.errorRight);
command.Parameters.AddWithValue("@contactRight", entity.contactRight);
command.Parameters.AddWithValue("@type", entity.type);
int i = command.ExecuteNonQuery();
mycn.Close();
mycn.Dispose();
return i;
}
}
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(AgentContact entity)
{
string sql = "INSERT INTO agent_Contact (agentNo,agentName,branchNo,branchName,area,zone,contactTel,contactName,contactEmail) VALUE (@agentNo,@agentName,@branchNo,@branchName,@area,@zone,@contactTel,@contactName,@contactEmail)";
using (MySqlConnection mycn = new MySqlConnection(mysqlConnection))
{
mycn.Open();
MySqlCommand command = new MySqlCommand(sql, mycn);
command.Parameters.AddWithValue("@agentNo", entity.agentNo);
command.Parameters.AddWithValue("@agentName", entity.agentName);
command.Parameters.AddWithValue("@branchNo", entity.branchNo);
command.Parameters.AddWithValue("@branchName", entity.branchName);
command.Parameters.AddWithValue("@area", entity.area);
command.Parameters.AddWithValue("@zone", entity.zone);
command.Parameters.AddWithValue("@contactTel", entity.contactTel);
command.Parameters.AddWithValue("@contactName", entity.contactName);
command.Parameters.AddWithValue("@contactEmail", entity.contactEmail);
int i = command.ExecuteNonQuery();
mycn.Close();
mycn.Dispose();
return i;
}
}
public bool sendSetDataMariaDB(string sql)
{
using (MySqlConnection connection = new MySqlConnection(getConnectionString()))
{
using (MySqlCommand command = new MySqlCommand(sql))
{
command.Connection = connection;
connection.Open();
try
{
if (command.ExecuteNonQuery() > 0)
{
return true;
}
}
catch (System.Exception e)
{
writeConnectionLogs("La sentencia sql:" + sql + "<------->" + " presenta el siguiente error: " + e.Message);
}
}
connection.Close();
connection.Dispose();
}
return false;
}
public static object database_conn(string sqlstring)
{
conn = new MySqlConnection();
conn.ConnectionString = "server=" + Properties.Settings.Default.host + "; port=" + Properties.Settings.Default.port + "; user id=" + Properties.Settings.Default.username + "; password="******"; database=" + Properties.Settings.Default.database;
try
{
conn.Open();
MySqlCommand sql = new MySqlCommand(sqlstring, conn);
DataSet ds = new DataSet();
MySqlDataAdapter DataAdapter = new MySqlDataAdapter();
DataAdapter.SelectCommand = sql;
DataAdapter.Fill(ds, "table1");
return ds;
}
catch (MySqlException myerror)
{
MessageBox.Show("Error Connecting to Database: " + myerror.Message, "Database Read Error", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1);
return "";
}
finally
{
conn.Close();
conn.Dispose();
}
}
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;
}
}
public static ResponseStruct DeleteAllImages()
{
ResponseStruct Result;
try
{
MySqlConnection DBConnection = new MySqlConnection(OverallInformations.TradingSystemDBConnectionString);
DBConnection.Open();
string SQLQuery = "TRUNCATE TABLE images_from_players";
MySqlCommand DBCommand = new MySqlCommand(SQLQuery, DBConnection);
DBCommand.ExecuteNonQuery();
DBCommand.Dispose();
DBConnection.Dispose();
string[] ImageFileNames = Directory.GetFiles(OverallInformations.ImagePath, "*.*", SearchOption.AllDirectories);
foreach (string anImageFileName in ImageFileNames)
{
File.Delete(anImageFileName);
}
Result = new ResponseStruct() { Status = ResponseStatusType.Done, Broadcast = new List<string>() { "Đã xóa." } };
}
catch (Exception ex)
{
LauncherServer.OutputMessage(String.Format("[Server]: DeleteAllImages() | Error: {0}", ex.Message), LauncherServer.MessageType.Error);
Result = new ResponseStruct() { Status = ResponseStatusType.Fail, Error = new List<string>() { ex.Message }, Broadcast = new List<string>() { "Kiểm tra hệ thống để xác định nguyên nhân." } };
}
return Result;
}
static void Main(string[] args)
{
var config = new ConfigurationBuilder()
.SetBasePath(Directory.GetCurrentDirectory())
.AddJsonFile("appconfig.json")
.Build();
string connectionString = $"Persist Security Info=False;database={config["database"]};" +
$"server={config["server"]};" +
$"user id={config["user"]};" +
$"Password={config["password"]}";
MySqlConnection connection = null;
try {
connection = new MySql.Data.MySqlClient.MySqlConnection(connectionString);
connection.Open();
foreach (var runExample in ExampleList.GetList())
{
runExample(connection);
}
} catch (Exception ex) {
connection?.Dispose();
Console.WriteLine(ex.Message);
}
}
public static void CloseCon(MySqlConnection con)
{
con.Close();
con.Dispose();
GC.Collect();
}
/// <summary>
/// 修改数据
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
public int Update(AgentInvoice entity)
{
string sql = "UPDATE agent_invoice SET agentNo=@agentNo,agentName=@agentName,invoiceMonth=@invoiceMonth,";
sql = sql + "invoiceNo=@invoiceNo,invoiceDate=@invoiceDate,invoiceContent=@invoiceContent,invoiceType=@invoiceType,";
sql = sql + "invoiceFee=@invoiceFee ,comment=@comment where agentNo=@agentNo and invoiceMonth=@invoiceMonth and invoiceNo=@invoiceNo";
//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("@agentNo", entity.agentNo);
command.Parameters.AddWithValue("@agentName", entity.agentName);
command.Parameters.AddWithValue("@invoiceMonth", entity.invoiceMonth);
command.Parameters.AddWithValue("@invoiceNo", entity.invoiceNo);
command.Parameters.AddWithValue("@invoiceDate", entity.invoiceDate);
command.Parameters.AddWithValue("@invoiceContent", entity.invoiceContent);
command.Parameters.AddWithValue("@invoiceType", entity.invoiceType);
command.Parameters.AddWithValue("@invoiceFee", entity.invoiceFee);
command.Parameters.AddWithValue("@comment", entity.comment);
int i = command.ExecuteNonQuery();
mycn.Close();
mycn.Dispose();
return i;
}
}
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(Exam entity)
{
string sql = "INSERT INTO tb_exam (subject,sender,creatTime,type,validateStartTime,validateEndTime,isValidate,toAll,agentType,duration)";
sql = sql + " VALUE (@subject,@sender,@creatTime,@type,@validateStartTime,@validateEndTime,@isValidate,@toAll,@agentType,@duration)";
using (MySqlConnection mycn = new MySqlConnection(mysqlConnection))
{
mycn.Open();
MySqlCommand command = new MySqlCommand(sql, mycn);
command.Parameters.AddWithValue("@subject", entity.subject);
command.Parameters.AddWithValue("@sender", entity.sender);
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("@toAll", entity.toAll);
command.Parameters.AddWithValue("@agentType", entity.agentType);
command.Parameters.AddWithValue("@duration", entity.duration);
int i = command.ExecuteNonQuery();
mycn.Close();
mycn.Dispose();
return i;
}
}
public DataTable lista_Documentos()
{
DataTable dtDocumentos = new DataTable();
MySqlConnection oSC = new MySqlConnection();
try
{
cls_Conexion oConexion = new cls_Conexion();
oSC = oConexion.conexion();
oSC.Open();
MySqlDataAdapter daDoc = new MySqlDataAdapter("SELECT * FROM tbl_TipoDocumento", oSC);
daDoc.Fill(dtDocumentos);
return dtDocumentos;
}
catch(Exception e)
{
string strError = e.Message;
throw new Exception(strError);
throw new Exception("Error al Recuperar Datos Verifique");
}
finally
{
oSC.Close();
oSC.Dispose();
}
return null;
}
public void EA_Disconnect()
{
EA_Close();
conn.Close();
conn.Dispose();
GC.Collect();
GC.WaitForPendingFinalizers();
}
public void Close()
{
if (conexion.State == ConnectionState.Open)
{
conexion.Close();
conexion.Dispose();
}
}
public string Save(REceiptTypes.ReceiptDataType _SaveData,out string RCVTmp)
{
RCVTmp = GetNewTMPPayID(_SaveData.CompanyID, _SaveData.AccPeriod);
MySql.Data.MySqlClient.MySqlTransaction Mytrans;
MySqlConnection CurCon = new MySqlConnection();
CurCon = Mycommon.AccountConnection;
if (CurCon.State == ConnectionState.Closed)
CurCon.Open();
string respond = "";
Mytrans = Mycommon.AccountConnection.BeginTransaction();
MySqlCommand oSqlCommand = new MySqlCommand();
if (!ExistReceipt(RCVTmp, CurCon))
{
respond = SaveReceipt(_SaveData, RCVTmp, CurCon);
if (respond != "True")
{
Mytrans.Rollback();
Mycommon.AccountConnection.Close();
CurCon.Dispose();
return respond;
}
else
{
respond = SaveDetails(_SaveData.ReceiptList, RCVTmp, CurCon);
if (respond != "True")
{
Mytrans.Rollback();
Mycommon.AccountConnection.Close();
CurCon.Dispose();
return respond;
}
else
{
Mytrans.Commit();
Mycommon.AccountConnection.Close();
CurCon.Dispose();
return "True";
}
}
}
else
return "Number Already Exist, Use Update Button";
}
public static void CloseConnection(MySqlConnection connection)
{
try
{
connection.Close();
connection.Dispose();
}
catch { }
}
//关闭数据库链接
public static void Close_Conn(MySqlConnection Conn)
{
if (Conn != null)
{
Conn.Close();
Conn.Dispose();
}
GC.Collect();
}
public void Dispose()
{
if (transaction != null)
{
transaction.Dispose();
}
connection.Dispose();
}
public static void CloseConnection(MySqlConnection conn)
{
if(conn != null)
{
if(conn.State == ConnectionState.Open)
conn.Close();
conn.Dispose();
}
}
public void Dispose()
{
if (_conn.State != ConnectionState.Closed)
{
_conn.Close();
}
_conn.Dispose();
//Reader = null;
_conn = null;
}
//new code from crackstation
private void registerUserWithSlowHash()
{
try
{
string connString = System.Configuration.ConfigurationManager.ConnectionStrings["MywebConnection"].ToString();
con = new MySql.Data.MySqlClient.MySqlConnection(connString);
con.Open();
querystr = "";
querystr = "INSERT INTO mydatabase.registertable(Username,DOB,Mobile,slowHashSalt)" +
"VALUES(?username,?datebirth,?mob,?slowhashsalt)";
cmd = new MySqlCommand(querystr, con);
cmd.Parameters.AddWithValue("?username", Username.Text);
cmd.Parameters.AddWithValue("?datebirth", dob.Text);
cmd.Parameters.AddWithValue("?mob", mob.Text);
string saltHashReturned = PasswordStorage.CreateHash(passwd.Text);
int commaIndex = saltHashReturned.IndexOf(":");
string extractedString = saltHashReturned.Substring(0, commaIndex);
commaIndex = saltHashReturned.IndexOf(":");
extractedString = saltHashReturned.Substring(commaIndex + 1);
commaIndex = extractedString.IndexOf(":");
string salt = extractedString.Substring(0, commaIndex);
commaIndex = extractedString.IndexOf(":");
extractedString = extractedString.Substring(commaIndex + 1);
string hash = extractedString;
//from the first : to the second : is the salt
//from the second : to the end is the hash
cmd.Parameters.AddWithValue("?slowhashsalt", saltHashReturned);
cmd.ExecuteReader();
con.Close();
//ClientScript.RegisterStartupScript(Page.GetType(), "validation", "<script language='javascript'>alert('You have been Succesfully Registered! Click O.K to navigate to Homepage.');window.location.replace('Default.aspx');</script>");
cmd.Dispose();
clearfields();
ClientScript.RegisterStartupScript(Page.GetType(), "validation", "<script language='javascript' >alertMX('Registered Succesfully! Click OK');</script>");
//ClientScript.RegisterStartupScript(Page.GetType(), "validation", "<script language='javascript' >myalert('Test', 'This is a test modal dialog');</script>");
}
catch (MySqlException reg)
{
Console.WriteLine("{0}+MySql Exceptions", reg);
}
finally
{
if (!(con == null))
{
con.Dispose();
}
}
}
static void Main(string[] args)
{
Console.OutputEncoding = Encoding.UTF8;
String classno = "I1A";
if (args.Length > 0)
{
classno = args[0];
}
String grade = classno.Substring(0, 2);
String[] grade_name = { "幼兒", "幼低", "幼高" };
String[] class_name = { "信", "望", "愛", "善", "樂" };
String classname = "";
switch (grade)
{
case "I1": classname = grade_name[0]; break;
case "I2": classname = grade_name[1]; break;
case "I3": classname = grade_name[2]; break;
default:
break;
}
switch (classno.Substring(2, 1))
{
case "A": classname += class_name[0]; break;
case "B": classname += class_name[1]; break;
case "C": classname += class_name[2]; break;
case "D": classname += class_name[3]; break;
case "E": classname += class_name[4]; break;
default:
classname = classno;
break;
}
MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection("");
conn.Open();
DataTable dt = GetStudMarkTableDataTable(conn, classno);
Console.Write(Newtonsoft.Json.JsonConvert.SerializeObject(dt));
conn.Close();
conn.Dispose();
Console.OutputEncoding = Encoding.Default;
}
private MySqlConnection closeConnection()
{
try
{
currentConnection.Close();
currentConnection.Dispose();
}
catch (Exception ex)
{
myLog.Error(ex);
}
return(currentConnection);
}
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);
}
}
} // end CargarParametros
/*
* En el procedimiento Comando, se buscará primero si ya existe el comando en dicha Hashtable para retornarla
* (convertida en el tipo correcto). Caso contrario, se procederá a la creación del mismo,
* y su agregado en el repositorio. Dado que cabe la posibilidad de que ya estemos dentro de una transacción,
* es necesario abrir una segunda conexión a la base de datos, para obtener la definición de los parámetros
* del procedimiento Almacenado (caso contrario da error, por intentar leer sin tener asignado el
* objeto Transaction correspondiente). Además, el comando, obtenido por cualquiera de los mecanismos
* debe recibir la conexión y la transacción correspondientes (si no hay Transacción, la variable es null,
* y ese es el valor que se le pasa al objeto Command)
*/
protected override System.Data.IDbCommand Comando(string procedimientoAlmacenado)
{
MySql.Data.MySqlClient.MySqlCommand com;
if (ColComandos.Contains(procedimientoAlmacenado))
{
com = (MySql.Data.MySqlClient.MySqlCommand)ColComandos[procedimientoAlmacenado];
}
else
{
var con2 = new MySql.Data.MySqlClient.MySqlConnection(CadenaConexion);
con2.Open();
com = new MySql.Data.MySqlClient.MySqlCommand(procedimientoAlmacenado, con2)
{
CommandType = System.Data.CommandType.StoredProcedure
};
MySql.Data.MySqlClient.MySqlCommandBuilder.DeriveParameters(com);
con2.Close();
con2.Dispose();
ColComandos.Add(procedimientoAlmacenado, com);
}//end else
com.Connection = (MySql.Data.MySqlClient.MySqlConnection)Conexion;
com.Transaction = (MySql.Data.MySqlClient.MySqlTransaction)MTransaccion;
return(com);
}// end Comando
protected virtual void Dispose(bool disposing)
{
if (disposed)
{
return;
}
if (disposing)
{
if (data != null)
{
data.Dispose();
}
if (ds != null)
{
ds.Dispose();
}
if (da != null)
{
da.Dispose();
}
if (cb != null)
{
cb.Dispose();
}
if (com != null)
{
com.Dispose();
}
if (comm != null)
{
comm.Dispose();
}
}
disposed = true;
}
public void Close()
{
conn.Close();
conn.Dispose();
}
static void Main(string[] args)
{
Console.OutputEncoding = Encoding.UTF8;
Console.Write("{");
String classno = "I1A";
if (args.Length > 0)
{
classno = args[0];
}
String grade = classno.Substring(0, 2);
String[] grade_name = { "幼兒", "幼低", "幼高" };
String[] class_name = { "信", "望", "愛", "善", "樂" };
String classname = "";
switch (grade)
{
case "I1": classname = grade_name[0]; break;
case "I2": classname = grade_name[1]; break;
case "I3": classname = grade_name[2]; break;
default:
break;
}
switch (classno.Substring(2, 1))
{
case "A": classname += class_name[0]; break;
case "B": classname += class_name[1]; break;
case "C": classname += class_name[2]; break;
case "D": classname += class_name[3]; break;
case "E": classname += class_name[4]; break;
default:
classname = classno;
break;
}
MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection("");
conn.Open();
String sql = String.Format(@"SELECT ka_id,assess_date_stamp,assess_week_stamp,assess_days,topic,subtopic from kassesstopic where current_flag=1 and classno='{0}';", grade);
String ka_id = "0";
using (MySqlDataReader dr = new MySqlCommand(sql, conn).ExecuteReader())
{
while (dr.Read())
{
ka_id = dr.GetString(0);
Console.Write("\"assdate\":\"{0}\",\"assweek\":\"{1}\",", dr[1], dr[2]);
Console.Write("\"assdays\":\"{0}\",\"asstopic\":\"{1}\",", dr[3], dr[4]);
Console.Write("\"asssubtopic\":\"{0}\",", dr[5]);
}
dr.Close();
dr.Dispose();
}
Console.Write("\"stud\":[");
{
String semester = ""; String term = "";
{
using (MySqlDataReader settingdr = new MySqlCommand(@"SELECT term ,semester from ksetting", conn).ExecuteReader())
{
while (settingdr.Read())
{
term = settingdr.GetString(0);
semester = settingdr.GetString(1);
}
settingdr.Close();
settingdr.Dispose();
}
}
int c = 0;
String sql0 = String.Format(
@"select curr_class,curr_seat,a.c_name,height,weight
from studinfo a left join Kstudbodychecking b on a.stud_ref=b.stud_ref
where curr_class='{0}' and b.classno='{0}' and b.term='{1}' and semester={2} order by curr_class,curr_seat;
", classno, term, semester);
using (MySqlDataReader dr = new MySqlCommand(sql0, conn).ExecuteReader())
{
while (dr.Read())
{
c++;
if (c > 1)
{
Console.Write(",");
}
Console.Write("{{\"c_name\":\"{0}\",", dr[2]);
Console.Write("\"classno\":\"{0}\",", classname);
Console.Write("\"seat\":\"{0}\",", dr[1]);
Console.Write("\"height\":\"{0}\",", dr[3]);
Console.Write("\"weight\":\"{0}\"}}", dr[4]);
}
dr.Close();
dr.Dispose();
}
Console.Write("],");
}
Console.Write("\"topic\":[");
{
String sql0 = String.Format("select subject,item from kassesstopic_subjectitem where ka_id={0} order by s_no,si_no;", ka_id);
using (MySqlDataReader dr = new MySqlCommand(sql0, conn).ExecuteReader())
{
int c = 0;
string t = "";
while (dr.Read())
{
c++;
if (c > 1)
{
Console.Write(",");
}
if (c > 1 && !t.Equals(dr.GetString(0)))
{
Console.Write("\"<tr><td class=c01>{0}\",", " ");
}
if (t.Equals(dr.GetString(0)))
{
Console.Write("\"<tr><td class=c01>{0}", "");
}
else
{
Console.Write("\"<tr><td class=c01>{0}:", dr[0]);
t = dr.GetString(0);
}
if (dr.IsDBNull(1))
{
Console.Write("<td class=c02>{0}\"", "");
}
else
{
int ci = dr[1].ToString().IndexOf('.');
String cic = "";
if (ci < 4 && ci > 0)
{
cic = "class=c0" + ci;
}
Console.Write("<td class=c02><div {1}>{0}</div>\"", dr[1].ToString().Replace('"'.ToString(), """).Replace("\t", "").Replace("\r", "").Replace("\n", ""), cic);
}
}
dr.Close();
dr.Dispose();
}
Console.Write("]");
}
conn.Close();
conn.Dispose();
Console.Write("}");
Console.OutputEncoding = Encoding.Default;
}
//Sluit de database connectie
private void Close()
{
conn.Close();
conn.Dispose();
}
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();
}
}
}
