/// <summary>
/// 创建http Server线程
/// </summary>
public void RunHttpServerThread()
{
httpserverDB = new MySqlDbHelper(GlobalParameter.DB_ConnStr);
if (!httpserverDB.MyDbConnFlag)
{
Log.WriteError("打开数据库失败！" + GlobalParameter.DB_ConnStr);
GlobalParameter.CloseThisApp();
}
httpListenner = new HttpListener();
httpListenner.AuthenticationSchemes = AuthenticationSchemes.Anonymous;
//httpListenner.AuthenticationSchemes = AuthenticationSchemes.Basic;
httpListenner.Prefixes.Add("http://+:8080/");
try
{
httpListenner.Start();
Log.WriteDebug("Http Server 启动成功!");
httpListenner.BeginGetContext(new AsyncCallback(Context), httpListenner);
Log.WriteDebug("开始循环接收Http消息...");
}
catch (Exception ex)
{
Log.WriteCrash(ex);
GlobalParameter.CloseThisApp();
}
//snList = new List<SnIpPortNode>();
}
protected void Page_Load(object sender, EventArgs e)
{
PublishJobTable.GetPublishedJobTable_All();
this.DgvGdh.DataSource = MySqlDbHelper.ExecuteDataTable(
sqlSelect + " ORDER BY `Excel时间` DESC LIMIT 300");
this.DgvGdh.DataBind();
}
public static bool Add(DataTable dt)
{
if (dt == null || dt.Rows.Count == 0 || dt.Columns.Count == 0)
{
return(false);
}
//INSERT INTO [Job]()VALUES();
StringBuilder fields_sb = new StringBuilder();
StringBuilder values_sb = new StringBuilder();
StringBuilder value_sb = new StringBuilder();
//先确定字段
foreach (DataColumn col in dt.Columns)
{
fields_sb.Append(col.ColumnName + ",");
}
fields_sb.Remove(fields_sb.Length - 1, 1);
//确定值
foreach (DataRow row in dt.Rows)
{
value_sb.Clear();
foreach (DataColumn col in dt.Columns)
{
value_sb.Append("'" + row[col].ToString() + "',");
}
value_sb.Remove(value_sb.Length - 1, 1);
values_sb.AppendLine("(" + value_sb + "),");
}
return(MySqlDbHelper.ExecuteNonQuery(
string.Format("INSERT INTO `Job`\n({0})\nVALUES\n{1}"
, fields_sb.ToString(), values_sb.ToString().Trim().TrimEnd(','))) > 0);
}
private void btn_table_Click(object sender, EventArgs e)
{
string dbName = cb_dbs.SelectedValue.ToString();
string tableName = cb_tables.SelectedValue.ToString();
DataTable dt = MySqlDbHelper.GetTableColums(dbName, tableName);
StringBuilder result = new StringBuilder();
result.Append($"<el-table :data=\"{tableName}List\" stripe border fit> \r\n");
result.Append(" ".repeat(4) + $"<el-table-column type=\"index\" label=\"序号\"></el-table-column> \r\n");
for (int i = 0; i < dt.Rows.Count; i++)
{
//var ss = appSetting["DataTypeComtrast"].Children()["Mysql"];
string typeString = GetTypeString(dt.Rows[i]);
string label = string.IsNullOrWhiteSpace(dt.Rows[i]["注释"] + "") ? (dt.Rows[i]["注释"] + "").Split('|')[0] : dt.Rows[i]["列名"] + "";
result.Append(" ".repeat(4) + $"<el-table-column prop=\"{dt.Rows[i]["列名"]}\" label=\"{label}\" ></el-table-column>\r\n");
}
result.Append(" ".repeat(4) + $"<el-table-column label=\"操作\" align=\"center\" width=\"300\"> \r\n");
result.Append(" ".repeat(8) + $"<template> \r\n");
result.Append(" ".repeat(12) + $"<el-button size=\"mini\" type=\"primary\">查看</el-button> \r\n");
result.Append(" ".repeat(12) + $"<el-button size=\"mini\" type=\"success\">修改</el-button> \r\n");
result.Append(" ".repeat(12) + $"<el-button size=\"mini\" type=\"danger\" >删除</el-button> \r\n");
result.Append(" ".repeat(8) + $"</template> \r\n");
result.Append(" ".repeat(4) + $"</el-table-column> \r\n");
result.Append($"</el-table> \r\n");
tb_result.Text = result.ToString();
}
private void OutMysql_Click(object sender, EventArgs e)
{
loadXml_Click(sender, e);
parseLog_Click(sender, e);
DateTime begin = DateTime.Now;
if (m_AlldtTables.Count == 0)
{
MessageBox.Show("请先点击parseLog按钮!");
return;
}
foreach (var val in m_AlldtTables)
{
DataTable dt = val.Value;
if (dt != null)
{
//int count = BulkInsert(constr, dt);
//textBoxSql.AppendText(string.Format("{0}导入{1}条数据\n", dt.TableName, count));
MySqlDbHelper helper = new MySqlDbHelper();
helper.InsertByDataTable(ref dt);
textBoxSql.AppendText(string.Format("{0}导入数据库\n", dt.TableName));
}
}
TimeSpan span = DateTime.Now - begin;
textBoxSql.AppendText(string.Format("-----------------导入数据到mysql成功,用时：{0}｝\n", span.Seconds));
}
private string CreateModel(string dbName, string tableName)
{
DataTable dt = MySqlDbHelper.GetTableColums(dbName, tableName);
StringBuilder result = new StringBuilder();
result.Append($"using System; \r\n");
result.Append($"namespace {tb_namespace.Text.Trim()}.Model\r\n");
result.Append($"{{ \r\n");
result.Append(" ".repeat(4) + $"public class {tableName}\r\n");
result.Append(" ".repeat(4) + $"{{ \r\n");
for (int i = 0; i < dt.Rows.Count; i++)
{
//var ss = appSetting["DataTypeComtrast"].Children()["Mysql"];
string typeString = GetTypeString(dt.Rows[i]);
result.Append(" ".repeat(8) + "///<summary>\r\n");
result.Append(" ".repeat(8) + "///" + dt.Rows[i]["注释"] + "\r\n");
result.Append(" ".repeat(8) + "///</summary>\r\n");
result.Append(" ".repeat(8) + "public " + typeString + " " + dt.Rows[i]["列名"] + " { get; set; }\r\n");
result.Append(" ".repeat(8) + "\r\n");
}
result.Append(" ".repeat(4) + $"}} \r\n");
result.Append($"}} \r\n");
return(result.ToString());
}
public EventDetail Get(string eventId)
{
EventDetail entity = null;
string sqlText = @" SELECT `Id`,
`EventDate`,
`EventName`,
`EventId`
FROM `custom_event_details`
WHERE `EventId` = @EventId;";
MySqlParameter[] parameters =
{
new MySqlParameter("@EventId", MySqlDbType.VarChar, 10)
{
Value = eventId
}
};
MySqlDataReader sqlDataReader = MySqlDbHelper.ExecuteReader(sqlText, parameters);
if (sqlDataReader.Read())
{
entity = new EventDetail();
ReadRecordData(sqlDataReader, entity);
}
sqlDataReader.Close();
return(entity);
}
public PageData GetPageList(int pageSize, int curPage)
{
int recordsTotal = MySqlDbHelper.ExecuteScalar <int>("SELECT COUNT(*) FROM custom_event_details", null);
string sqlText = @" SELECT `Id`,
`EventDate`,
`EventName`,
`EventId`
FROM `custom_event_details` "
+ " ORDER BY Id DESC LIMIT " + (curPage - 1) * pageSize + "," + pageSize;
List <EventDetail> list = new List <EventDetail>();
MySqlDataReader sqlDataReader = MySqlDbHelper.ExecuteReader(sqlText, null);
PageData pageData = new PageData();
pageData.PageSize = pageSize;
pageData.CurPage = curPage;
pageData.RecordCount = Math.Max(1, recordsTotal);
if (pageData.RecordCount > 0)
{
pageData.PageCount = Convert.ToInt32(Math.Ceiling((double)pageData.RecordCount / (double)pageSize));
}
while (sqlDataReader.Read())
{
EventDetail entity = new EventDetail();
ReadRecordData(sqlDataReader, entity);
list.Add(entity);
}
sqlDataReader.Close();
pageData.PageList = list;
return(pageData);
}
private void btn_mockJson_Click(object sender, EventArgs e)
{
string dbName = cb_dbs.SelectedValue.ToString();
string tableName = cb_tables.SelectedValue.ToString();
DataTable dt = MySqlDbHelper.GetTableColums(dbName, tableName);
StringBuilder result = new StringBuilder();
for (int i = 0; i < dt.Rows.Count; i++)
{
//var ss = appSetting["DataTypeComtrast"].Children()["Mysql"];
string typeString = GetTypeString(dt.Rows[i]);
string mockValue = "";
switch (typeString)
{
case "string":
mockValue = "@string"; break;
case "int?":
mockValue = "@integer"; break;
case "DateTime?":
mockValue = "@date"; break;
default:
break;
}
result.Append($"{dt.Rows[i]["列名"]}: '{mockValue}', \r\n");
}
tb_result.Text = result.ToString();
}
/// <summary>
/// 批量导出
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btn_forfolder_Click(object sender, EventArgs e)
{
string dbName = cb_dbs.SelectedValue.ToString();
string folderPath = Application.StartupPath + "\\pl";
FileHelper.ClearFolder(folderPath);//清空文件夹
DataTable tables = MySqlDbHelper.GetTables(dbName);
FileHelper.CreateDirectory($"{folderPath}\\Model");
FileHelper.CreateDirectory($"{folderPath}\\DAL");
FileHelper.CreateDirectory($"{folderPath}\\BLL");
for (int i = 0; i < tables.Rows.Count; i++)
{
string tableName = tables.Rows[i]["TABLE_NAME"].ToString();
string filePath = $"{folderPath}\\Model\\{tableName}.cs";
string filePath_DAL = $"{folderPath}\\DAL\\{tableName}DAL.cs";
string filePath_BLL = $"{folderPath}\\BLL\\{tableName}BLL.cs";
//FileHelper.Create(filePath);
FileHelper.OpenWrite(filePath, CreateModel(dbName, tableName));
FileHelper.OpenWrite(filePath_DAL, CreateDAL(dbName, tableName));
FileHelper.OpenWrite(filePath_BLL, CreateBLL(dbName, tableName));
}
tb_result.Text += "批量导出成功";
}
/// <summary>
/// 生成模型
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btn_model_Click(object sender, EventArgs e)
{
string dbName = cb_dbs.SelectedValue.ToString();
string tableName = cb_tables.SelectedValue.ToString();
DataTable dt = MySqlDbHelper.GetTableColums(dbName, tableName);
StringBuilder result = new StringBuilder();
result.Append($"namespace {tb_namespace.Text.Trim()}\r\n");
result.Append($"{{ \r\n");
result.Append(" ".repeat(4) + $"public class {tableName}\r\n");
result.Append(" ".repeat(4) + $"{{ \r\n");
for (int i = 0; i < dt.Rows.Count; i++)
{
var ss = appSetting["DataTypeComtrast"].Children()["Mysql"];
string typeString = ss.Values(dt.Rows[i]["数据类型"] + "").FirstOrDefault().ToString();
result.Append(" ".repeat(8) + "///<summary>\r\n");
result.Append(" ".repeat(8) + "///" + dt.Rows[i]["注释"] + "\r\n");
result.Append(" ".repeat(8) + "///</summary>\r\n");
result.Append(" ".repeat(8) + "public " + typeString + " " + dt.Rows[i]["列名"] + " { get; set; }\r\n");
result.Append(" ".repeat(8) + "\r\n");
}
result.Append(" ".repeat(4) + $"}} \r\n");
result.Append($"}} \r\n");
tb_result.Text = result.ToString();
}
/// <summary>
/// 绑定数据表下拉框
/// </summary>
private void BindTables()
{
DataTable dt = MySqlDbHelper.GetTables(cb_dbs.SelectedValue.ToString());
cb_tables.DataSource = dt;
cb_tables.ValueMember = "TABLE_NAME";
cb_tables.DisplayMember = "TABLE_NAME";
}
/// <summary>
/// 绑定数据库下拉框
/// </summary>
private void BindDatabases()
{
DataTable dt = MySqlDbHelper.GetDataBases();
cb_dbs.DataSource = dt;
cb_dbs.ValueMember = "Database";
cb_dbs.DisplayMember = "Database";
}
private MySqlDbHelper myDB;// = new MySqlDbHelper();
public MyServlet(MySqlDbHelper db)
{
//this.myDB = new MySqlDbHelper(GlobalParameter.DB_ConnStr);
//if (!myDB.MyDbConnFlag)
//{
// Log.WriteError("打开数据库失败！" + GlobalParameter.DB_ConnStr);
// GlobalParameter.httpServerRun = false;
//}
this.myDB = db;
}
public CreatedAtCount GetLoveCount(Guid?shareId)
{
string sql1 = " SELECT COUNT(Id) num FROM huangguan_sharereply WHERE IsDel = 0 and ShareId = '{0}'".FormatStr(shareId);
string sql2 = " SELECT COUNT(` Love`) num FROM huangguan_sharereply WHERE ` Love` = 1 and ShareId = '{0}'".FormatStr(shareId);
DataTable dt1 = MySqlDbHelper.ExecuteQuery(connCommonsStr, sql1);
DataTable dt2 = MySqlDbHelper.ExecuteQuery(connCommonsStr, sql2);
CreatedAtCount cr = new CreatedAtCount();
cr.ReplyerCount = Convert.ToInt32(dt1.Rows[0]["num"]);
cr.loveCount = Convert.ToInt32(dt2.Rows[0]["num"]);
return(cr);
}
//找回密码
public ResultInfo FindPwd(string usr, string email)
{
ResultInfo result = new ResultInfo();
if (string.IsNullOrEmpty(usr))
{
result.IsSuccess = false;
result.Message = "用户名不能为空";
return(result);
}
string selsql = "select ID from huangguan_user where LoginName=N'{0}'".FormatStr(usr);
DataTable dt = MySqlDbHelper.ExecuteQuery(connCommonsStr, selsql);
if (dt.Rows.Count > 0)
{
string selemail = "select ID from huangguan_user where LoginName=N'{0}' and UserEmail='{1}'";
DataTable dtrow = MySqlDbHelper.ExecuteQuery(connCommonsStr, selemail);
if (dtrow.Rows.Count <= 0)
{
result.IsSuccess = false;
result.Message = "填写的邮箱与该用户注册邮箱不一致！";
return(result);
}
int n = new Random().Next(100000, 999999);
var md5 = EncryptHelper.GetEncryPwd(n.ToString());
string updatesql = @"update huangguan_user set LoginPwd='{0}' where LoginName='{1}'".FormatStr(md5, usr);
int countrow = MySqlDbHelper.ExecuteSql(connCommonsStr, updatesql);
if (countrow > 0)
{
EmailHelper ems = new EmailHelper();
ems.SendMail(email, "", null, "用户找回密码", "<div><p>您的初始密码是：" + n + "</p><p>登陆系统后，请自行修改密码！</p></div>",
null, null,
//"*****@*****.**", "*****@*****.**", "sino_ips",
"*****@*****.**", "*****@*****.**", "ttttt33333",
"smtp.163.com", 25, false);
result.Message = "密码已经发送到您注册邮箱，请查收邮件！";
result.IsSuccess = true;
return(result);
}
else
{
result.IsSuccess = false;
result.Message = "找回密码失败";
return(result);
}
}
else
{
result.IsSuccess = false;
result.Message = "用户名不存在";
return(result);
}
}
public int DeleteCrossing(string id)
{
int ret = -1;
string strSql = "delete from tb_crossing where ID = " + id;
MySqlDbHelper dbHelper = new MySqlDbHelper();
dbHelper.Connect();
ret = dbHelper.ExecuteNonQuery(strSql);
dbHelper.Close();
return(ret);
}
public int UpdateCrossing(string crossId, string crossName, string id)
{
int ret = -1;
string strSql = "update tb_crossing set CROSSING_ID='" + crossId + "',CROSSING_NAME = '" + crossName + "' where id = " + id;
MySqlDbHelper dbHelper = new MySqlDbHelper();
dbHelper.Connect();
ret = dbHelper.ExecuteNonQuery(strSql);
dbHelper.Close();
return(ret);
}
/// <summary>
/// 保存客户端也需要使用的配置到数据库
/// </summary>
/// <param name="myDB"></param>
static public void SaveAllClientConfig(MySqlDbHelper myDB)
{
GlobalParameter.SaveConfig2Db(myDB, "RootDirectory", GlobalParameter.XmlRootNode, "Server");
GlobalParameter.SaveConfig2Db(myDB, "UploadServerUrl", GlobalParameter.UploadServerUrl, "Server");
GlobalParameter.SaveConfig2Db(myDB, "UploadServerUser", GlobalParameter.UploadServerUser, "Server");
GlobalParameter.SaveConfig2Db(myDB, "UploadServerPasswd", GlobalParameter.UploadServerPasswd, "Server");
GlobalParameter.SaveConfig2Db(myDB, "ConnectionRequestUsername", GlobalParameter.ConnectionRequestUsername, "Server");
GlobalParameter.SaveConfig2Db(myDB, "ConnectionRequestPassWd", GlobalParameter.ConnectionRequestPassWd, "Server");
GlobalParameter.SaveConfig2Db(myDB, "Ntp1ServerPath", GlobalParameter.Ntp1ServerPath, "Server");
GlobalParameter.SaveConfig2Db(myDB, "Ntp2ServerPath", GlobalParameter.Ntp2ServerPath, "Server");
}
public UsrDto RegisterSub(UsrDto dddd)
{
if (string.IsNullOrEmpty(dddd.LoginName) || string.IsNullOrEmpty(dddd.LoginPwd))
{
return new UsrDto {
Error = "用户名和密码不能为空"
}
}
;
string selsql = @"select ID from huangguan_user where LoginName=N'{0}'"
.FormatStr(dddd.LoginName);
DataTable dtq = MySqlDbHelper.ExecuteQuery(connCommonsStr, selsql);
if (dtq.Rows.Count > 0)
{
return(new UsrDto {
Error = "用户名已经存在"
});
}
else
{
var md5 = EncryptHelper.GetEncryPwd(dddd.LoginPwd);
Guid id = Guid.NewGuid();
string insertsql = @"insert into huangguan_user(ID,LoginName,LoginPwd,NickName,UserPhone,RoleId,UserEmail,status,CreatedAt)
values('{0}',N'{1}','{2}',N'{3}','{4}','{5}','{6}','{7}','{8}')".FormatStr(id, dddd.LoginName, md5, dddd.LoginName, dddd.UserPhone, 2, dddd.UserEmail, 0, DateTime.Now);
int resultrow = MySqlDbHelper.ExecuteSql(connCommonsStr, insertsql);
if (resultrow > 0)
{
UsrDto dto = new UsrDto();
dto.ID = id;
dto.LoginName = dddd.LoginName;
dto.LoginPwd = md5.ToString();
dto.NickName = dddd.LoginName;
dto.UserPhone = dddd.UserPhone;
dto.HeadIcon = "";
dto.RoleId = "2";
dto.UserEmail = dddd.UserEmail;
dto.status = 0;
dto.CreatedAt = DateTime.Now;
dto.Token = IprAuthorizeAttribute.GetToken(dto.LoginName, 0);
return(dto);
}
else
{
return(new UsrDto {
Error = "注册失败，请重新填写！"
});
}
}
}
public string UpdateCategory(int CategoryID, string CategoryName)
{
string updatesql = string.Format(@"update huangguan_category set Name=N'{0}' where ID='{1}'", CategoryName, CategoryID);
int count = MySqlDbHelper.ExecuteSql(connCommonsStr, updatesql);
if (count > 0)
{
return("修改成功！");
}
else
{
return("修改失败，请重试！");
}
}
public int AddCrossing(string crossId, string crossName)
{
int ret = -1;
string strSql = "insert into tb_crossing(CROSSING_ID, CROSSING_NAME,CREATE_TIME) values('" +
crossId + "','" + crossName + "','" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "')";
MySqlDbHelper dbHelper = new MySqlDbHelper();
dbHelper.Connect();
ret = dbHelper.ExecuteNonQuery(strSql);
dbHelper.Close();
return(ret);
}
public ResultInfo SaveShare(ShareDto dto)
{
ResultInfo r = new ResultInfo();
if (dto.ID == Guid.Empty)
{
dto.CreatedAt = DateTime.Now;
dto.ID = Guid.NewGuid();
dto.IsDel = false;
dto.IsReaded = false;
if (string.IsNullOrEmpty(dto.Title) || dto.Sender == Guid.Empty)
{
r.IsSuccess = false;
r.Message = "参数空异常";
return(r);
}
if (!string.IsNullOrEmpty(dto.Description))
{
dto.Abstract = dto.Description.Substring(0, System.Math.Min(dto.Description.Length, 1000)).GetTxtFromHtml2().Replace(" ", "");
}
string inserttab = @"insert into huangguan_share(ID,Title,Description,Sender,Label,CreatedAt,IsDel,IsReaded,Abstract)
values('{0}',N'{1}',N'{2}','{3}',N'{4}','{5}',{6},{7},N'{8}')"
.FormatStr(dto.ID, dto.Title, dto.Description, dto.Sender, dto.Label, dto.CreatedAt, 0, 0, dto.Abstract);
int rowcount = MySqlDbHelper.ExecuteSql(connCommonsStr, inserttab);
r.IsSuccess = true;
r.Message = dto.ID.ToString();
return(r);
}
else
{
string selsql = @"select ID from huangguan_share where ID='{0}'".FormatStr(dto.ID);
DataTable dt = MySqlDbHelper.ExecuteQuery(connCommonsStr, selsql);
if (dt.Rows.Count <= 0)
{
r.IsSuccess = false;
r.Message = "分享主题信息为空异常";
return(r);
}
if (!string.IsNullOrEmpty(dto.Description))
{
dto.Abstract = dto.Description.Substring(0, System.Math.Min(dto.Description.Length, 1000)).GetTxtFromHtml2().Replace(" ", "");
}
string updatesql = @"update huangguan_share set Description=N'{0}',Label=N'{1}',Title=N'{2}',Abstract=N'{3}' where ID='{4}'".FormatStr(dto.Description, dto.Label, dto.Title, dto.Abstract, dto.ID);
int cccc = MySqlDbHelper.ExecuteSql(connCommonsStr, updatesql);
r.IsSuccess = true;
r.Message = dto.ID.ToString();
return(r);
}
}
//获取总注册用户 和 当月注册用户
public UserNum GetUserNum()
{
DateTime ddddddd = DateTime.Now;
var dfdf = ddddddd.Year + "-" + ddddddd.Month + "-" + "1";
var ggggg = DateTime.Parse(dfdf);
string sql1 = "SELECT COUNT(ID) num FROM huangguan_user ";
string sql2 = "SELECT COUNT(ID) num FROM huangguan_user WHERE CreatedAt >='{0}' ".FormatStr(ggggg);
DataTable dt1 = MySqlDbHelper.ExecuteQuery(connCommonsStr, sql1);
DataTable dt2 = MySqlDbHelper.ExecuteQuery(connCommonsStr, sql2);
UserNum cr = new UserNum();
cr.TotalNum = Convert.ToInt32(dt1.Rows[0]["num"]);
cr.MonthNum = Convert.ToInt32(dt2.Rows[0]["num"]);
return(cr);
}
public string UpdateUser(string loginName, UsrDto usrdata)
{
string sql = "select ID from huangguan_user where NickName =N'{0}'".FormatStr(usrdata.NickName);
DataTable dt = MySqlDbHelper.ExecuteQuery(connCommonsStr, sql);
if (dt.Rows.Count > 0)
{
return("用户昵称已经存在了，请换一个！");
}
string updatesql = @"update huangguan_user set NickName=N'{0}',UserPhone='{1}',UserEmail='{2}'
where LoginName=N'{3}'".FormatStr(usrdata.NickName, usrdata.UserPhone, usrdata.UserEmail, loginName);
int count = MySqlDbHelper.ExecuteSql(connCommonsStr, updatesql);
return(count > 0 ? "成功！" : "失败！");
}
public CreatedAtCount GetCreatedAtCount(Guid usrId)
{
DateTime ddddddd = DateTime.Now;
var dfdf = ddddddd.Year + "-" + ddddddd.Month + "-" + "1";
var ggggg = DateTime.Parse(dfdf);
string sql1 = "SELECT COUNT(*) num FROM huangguan_share where IsDel =0";
string sql2 = "SELECT COUNT(CreatedAt) num FROM huangguan_share WHERE CreatedAt >='{0}' and IsDel = 0".FormatStr(ggggg);
DataTable dt1 = MySqlDbHelper.ExecuteQuery(connCommonsStr, sql1);
DataTable dt2 = MySqlDbHelper.ExecuteQuery(connCommonsStr, sql2);
CreatedAtCount cr = new CreatedAtCount();
cr.count = Convert.ToInt32(dt1.Rows[0]["num"]);
cr.yesterdayCount = Convert.ToInt32(dt2.Rows[0]["num"]);
return(cr);
}
public int UpdateCrossingInfo(string crossing_id, string json, string base64Image)
{
MySqlDbHelper dbHelper = new MySqlDbHelper();
dbHelper.Connect();
string sqlStr = "update tb_crossing set CROSSING_CONFIG='" + json + "' where CROSSING_ID='" + crossing_id + "'";
if (!string.IsNullOrEmpty(base64Image))
{
sqlStr = "update tb_crossing set CROSSING_CONFIG='" + json + "' ,IMAGE_DATA='" + base64Image + "' where CROSSING_ID='" + crossing_id + "'";
}
int retVal = dbHelper.ExecuteNonQuery(sqlStr);
dbHelper.Close();
return(retVal);
}
private void InitDatabase(ComboBox cmb, DbConnectionDO c)
{
cmb.Items.Clear();
string connStr = "server=" + c.HOST + ";port=" + c.PORT + ";user="******";password="******"; database=information_schema;";
string sql = "show databases";
DataTable dt = new MySqlDbHelper(connStr).RunDataTableSql(sql, null, null, null);
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow row in dt.Rows)
{
cmb.Items.Add(row["Database"].ToString());
}
cmb.SelectedIndex = 0;
}
}
public int Insert(List <EventDetail> entityList)
{
if (entityList == null || entityList.Count == 0)
{
return(0);
}
int flag = 0;
StringBuilder insertSQLs = new StringBuilder();
List <MySqlParameter> paramList = new List <MySqlParameter>();
entityList.ForEach((entity) => {
insertSQLs.AppendFormat(
@"INSERT INTO `custom_event_details`
(`Id`,
`EventDate`,
`EventName`,
`EventId`)
SELECT
@Id_{0},
@EventDate_{0},
@EventName_{0},
@EventId_{0}
FROM DUAL
WHERE NOT EXISTS (SELECT * FROM custom_event_details WHERE EventId = @EventId_{0});" , flag);
paramList.AddRange(new List <MySqlParameter> {
new MySqlParameter("@Id_" + flag, MySqlDbType.Int32)
{
Value = entity.Id
},
new MySqlParameter("@EventDate_" + flag, MySqlDbType.DateTime)
{
Value = entity.EventDate
},
new MySqlParameter("@EventName_" + flag, MySqlDbType.VarChar, 45)
{
Value = entity.EventName
},
new MySqlParameter("@EventId_" + flag, MySqlDbType.VarChar, 10)
{
Value = entity.EventId
},
});
flag++;
});
return(MySqlDbHelper.ExecuteScalar <int>(insertSQLs.ToString(), paramList.ToArray()));
}
public static DbHelper GetDbHelper()
{
DbHelper result = null;
switch (DbFactory.DbType)
{
case "mysql":
result = new MySqlDbHelper(DbFactory.ConnectionString);
break;
case "oracle":
result = new OracleDbHelper(DbFactory.ConnectionString);
break;
}
return(result);
}
public static DbHelper GetDbHelper()
{
DbHelper result = null;
switch (DbFactory.DbType)
{
case "mysql":
result = new MySqlDbHelper(DbFactory.ConnectionString);
break;
case "oracle":
result = new OracleDbHelper(DbFactory.ConnectionString);
break;
}
return result;
}
public void TestXxMethod()
{
var helper = new MySqlDbHelper("mysqldb");
var query = new AAQueryModel();
query.PageIndex = 1;
query.PageSize = 5;
query.Group = "";
const string spName = "sp_common_pager";
const string tableName = @"car_info";
const string fields = " * ";
var orderField = string.IsNullOrWhiteSpace(query.Order) ? "createdtime desc" : query.Order;
//查询条件
var sqlWhere = "1=1";
var model = new PagingModel(spName, tableName, fields, orderField, sqlWhere, query.PageSize, query.PageIndex);
var list2 = helper.ExecutePaging<dynamic>(model, query.Echo);
Assert.IsNotNull(list2);
}
