需要做一个项目,里边涉及对mysql表的简单操作,下边是代码
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading;
using System.Threading.Tasks;
using TPI;
namespace KeyWordStatistic
{
public class ChinessKeyWordStatistic
{
string connectMysql = "server = 192.168.*****"
+ "; database = Test"
+ "; user id = ****"
+ "; password = *****"
+ ";pooling=true;CharSet=utf8;port=3306;SslMode = none;";
string mysqltableName = "ChinesekeyWordsCnts";
public void NewThread()
{
CreatedMysqlTable();
var Dic = new Dictionary<string, int>();
//添加Dic数据
var sql = "INSERT IGNORE INTO t_ck VALUE(?k,?cnt)";
var j = 0;
foreach (var kv in Dic)
{
j += MySqlHelper.ExecuteNonQuery(connectMysql, sql, new MySqlParameter("k", kv.Key), new MySqlParameter("cnt", kv.Value));
Console.CursorLeft = 0;
Console.Write($"添加条数:{j.ToString("D8")}");
}
Console.WriteLine();
Console.ReadLine();
}
private bool CreatedMysqlTable()
{
#region 创建mysql数据库表
using (var Conn = new MySqlConnection(connectMysql))
{
Conn.Open();
string createStatement = "CREATE TABLE "+ mysqltableName + " (`keyWords` VarChar(255) not null primary key , `cnts` int )ENGINE=MyISAM DEFAULT CHARSET=utf8";
using (MySqlCommand cmd = new MySqlCommand(createStatement, Conn))
{
try
{
cmd.ExecuteNonQuery();
return true;
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
return false;
}
}
#endregion
}
}
private void DealValue(string bValue)
{
if (string.IsNullOrEmpty(bValue))
return;
bValue.Split(';').ToList().ForEach(item =>
{
if (!string.IsNullOrEmpty(item))
{
if (item != "篇长")
UpdateMysql(item);
}
});
}
private void UpdateMysql(string key)
{
int count = 0;
using (var Conn = new MySqlConnection(connectMysql))
{
Conn.Open();
key = key.Replace("'", "").Replace("/", "").Replace("\"", "");
string searchSql = "select * from " + mysqltableName + " where keyWords='" + key + "'";
bool isExist = false;
try
{
using (var Reader = MySqlHelper.ExecuteReader(connectMysql, searchSql))
{
try
{
while (Reader.Read())
{
isExist = true;
count = Convert.ToInt32(Reader.GetValue(1).ToString());
count++;
string updateSql = "update " + mysqltableName + " set cnts=" + count + " where keyWords = '" + key + "'";
using (MySqlCommand cmd = new MySqlCommand(updateSql, Conn))
{
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
}
if (!isExist)
{
string sqlstr = "insert into " + mysqltableName + " values('" + key + "',1)";
using (MySqlCommand cmd = new MySqlCommand(sqlstr, Conn))
{
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
}
}
catch (Exception e)
{
return;
}
}
}
private string RemoveInvalidateChar(string str)
{
StringBuilder info = new StringBuilder();
foreach (char c in str)
{
int s = (int)c;
if (((s >= 0) && (s <= 8)) || ((s >= 11) && (s <= 12)) || ((s >= 14) && (s <= 32)))
{
info.Append(" ");
}
else
info.Append(c);
}
return info.ToString();
}
}
}