将值插入mysql数据库无法正常工作 [英] Inserting values into a mysql database does not work as expected
问题描述
我尝试从C#中填充mySQL/MariaDB表.读取表并填充数据集即可.
I try to fill a mySQL/ MariaDB table from within C#. Reading the table and populating a dataset works.
表结构及其键等已给出且无法更改:
the table structure and its keys etc. are given and cannot be changed:
CREATE TABLE `fhem`.`history` (TIMESTAMP TIMESTAMP, DEVICE varchar(32), TYPE varchar(32), EVENT varchar(512), READING varchar(32), VALUE varchar(32), UNIT varchar(32));
CREATE TABLE `fhem`.`current` (TIMESTAMP TIMESTAMP, DEVICE varchar(32), TYPE varchar(32), EVENT varchar(512), READING varchar(32), VALUE varchar(32), UNIT varchar(32));
CREATE INDEX Search_Idx ON `fhem`.`history` (DEVICE, READING, TIMESTAMP);
这是我的代码,用于连接到数据库,创建sql命令和适配器,然后用数据库表中的数据填充数据集. 然后,我创建一个新的数据行,用值填充各列,并将其添加到DataSet的DataTable中.
This is my code to connect to the database, create the sql commands and adapters, and then fill the datasets with the data from the database tables. Then i create a new datarow, fill the columns with values and add this to the DataTable of th DataSet.
class Program
{
static void Main(string[] args)
{
MySqlConnectionStringBuilder connBuilder = new MySqlConnectionStringBuilder();
connBuilder.Server = "ds2";
connBuilder.Database = "fhem";
connBuilder.UserID = "fhemdbuser";
connBuilder.Port = 3307;
connBuilder.Password = "!2345Abcde";
//connBuilder.DefaultCommandTimeout = 120;
//connBuilder.UseDefaultCommandTimeoutForEF = true;
//connBuilder.AllowUserVariables = true;
MySqlConnection conn = new MySqlConnection(connBuilder.ConnectionString);
conn.Open();
MySqlCommand selectCmdCurrent = new MySqlCommand("select `TIMESTAMP`, `DEVICE`, `TYPE`, `EVENT`, `READING`, `VALUE`, `UNIT` from current;", conn);
MySqlCommand selectCmdCurrentTest = new MySqlCommand("select `TIMESTAMP`, `DEVICE`, `TYPE`, `EVENT`, `READING`, `VALUE`, `UNIT` from currentTest;", conn);
MySqlCommand updateCmd = new MySqlCommand("update `currentTest` set `TIMESTAMP`=@p1,`DEVICE`=@p2,`TYPE`=@p3,`EVENT`=@p4,`READING`=@p5,`VALUE`=@p6,`UNIT`=@p7", conn);
updateCmd.Parameters.Add("@p1", MySql.Data.MySqlClient.MySqlDbType.DateTime);
updateCmd.Parameters.Add("@p2", MySql.Data.MySqlClient.MySqlDbType.VarString, 128);
updateCmd.Parameters.Add("@p3", MySql.Data.MySqlClient.MySqlDbType.VarString, 128);
updateCmd.Parameters.Add("@p4", MySql.Data.MySqlClient.MySqlDbType.VarString, 512);
updateCmd.Parameters.Add("@p5", MySql.Data.MySqlClient.MySqlDbType.VarString, 128);
updateCmd.Parameters.Add("@p6", MySql.Data.MySqlClient.MySqlDbType.VarString, 128);
updateCmd.Parameters.Add("@p7", MySql.Data.MySqlClient.MySqlDbType.VarString, 128);
MySqlCommand insertCmd = new MySqlCommand("insert into `currentTest` values(@p1, @p2, @p3, @p4, @p5, @p6, @p7)", conn);
insertCmd.Parameters.Add("@p1", MySql.Data.MySqlClient.MySqlDbType.DateTime);
insertCmd.Parameters.Add("@p2", MySql.Data.MySqlClient.MySqlDbType.VarString, 128);
insertCmd.Parameters.Add("@p3", MySql.Data.MySqlClient.MySqlDbType.VarString, 128);
insertCmd.Parameters.Add("@p4", MySql.Data.MySqlClient.MySqlDbType.VarString, 512);
insertCmd.Parameters.Add("@p5", MySql.Data.MySqlClient.MySqlDbType.VarString, 128);
insertCmd.Parameters.Add("@p6", MySql.Data.MySqlClient.MySqlDbType.VarString, 128);
insertCmd.Parameters.Add("@p7", MySql.Data.MySqlClient.MySqlDbType.VarString, 128);
MySqlDataAdapter dataAdapterCurrent = new MySqlDataAdapter(selectCmdCurrent);
MySqlDataAdapter dataAdapterCurrentTest = new MySqlDataAdapter(selectCmdCurrentTest);
// MySqlCommandBuilder builder = new MySqlCommandBuilder(dataAdapterCurrent);
dataAdapterCurrentTest.InsertCommand = insertCmd;
dataAdapterCurrentTest.UpdateCommand = updateCmd;
DataSet ds = new DataSet();
MySqlCommand prepareCmd = new MySqlCommand("truncate currentTest;", conn);
prepareCmd.ExecuteNonQuery();
//prepareCmd = new MySqlCommand("insert into currentTest select * from current;", conn);
//prepareCmd.ExecuteNonQuery();
prepareCmd = new MySqlCommand("INSERT INTO `currentTest` VALUES(\"2020-06-06 12:45:23\",\"DEVICE\", \"TYPE\", \"EVENT\", \"READING\", \"VALUE\", \"UNIT\")", conn);
for (int i = 0; i < 10; i++)
prepareCmd.ExecuteNonQuery();
dataAdapterCurrentTest.Fill(ds, "currentTest");
dataAdapterCurrent.Fill(ds, "current");
int row = 0;
Stopwatch sw = new Stopwatch();
for (int i = 0; i < 10; i++)
{
DataRow newrow = ds.Tables["currentTest"].NewRow();
newrow["Timestamp"] = DateTime.Now;
newrow["Device"] = "Device";
newrow["TYPE"] = "Type";
newrow["EVENT"] = "Event";
newrow["READING"] = "Reading";
newrow["VALUE"] = "Value";
newrow["UNIT"] = "Unit";
ds.Tables["currentTest"].Rows.Add(newrow);
}
dataAdapterCurrentTest.Update(ds.Tables["currentTest"]);
ds.Tables["currentTest"].AcceptChanges();
}
}
手动插入命令将正确的值输入数据库,而通过
Whereas the manual insert command puts the correct values into the database, the programatic approach via
DataRow newrow = ds.Tables["currentTest"].NewRow();
ds.Tables["currentTest"].Rows.Add(newrow);
dataAdapterCurrentTest.Update(ds.Tables["currentTest"]);
ds.Tables["currentTest"].AcceptChanges();
仅添加具有有效日期的行,而其他列为NULL.
only adds rows with a valid date, but the other columns are NULL.
:
我对代码进行了一些更改,基本上,我修改了第二个for循环以像在第一个循环中那样插入新数据.然后,数据被正确地写入数据库.但是我怀疑,是否是调用.ExecuteNonQuery()
几千次以将我的日志文件放入数据库的一种有效方式...
:
I did some changes to my code, basically I modified the second for loop to insert new data like i did in the first loop. Then the data gets written correctly into the database. But I doubt, if it is a performant way to call .ExecuteNonQuery()
several thousand times to put my log file into the database...
:
120分钟后,具有.ExecuteNonQuery()
的数据库中的100 000个插入仍在运行.这很慢.
:
The 100 000 inserts into the database with .ExecuteNonQuery()
are still running after 120 Minutes. This is pretty slow.
推荐答案
使用MysqlDataAdapter,我们只需要设置selectCommand.它将由MySqlCommandBuilder
With MysqlDataAdapter we need to set the selectCommand only. It will generate the insertCommand automatically by MySqlCommandBuilder
您的代码几乎是正确的,仅需要进行2次更改.
1.在dataAdapterCurrentTest.Update(ds.Tables["currentTest"])
Your code is almost correct only 2 changes are required.
1. Add following line before dataAdapterCurrentTest.Update(ds.Tables["currentTest"])
new MySqlCommandBuilder(dataAdapterCurrentTest);
2.代码注释行
2. Comment folloing line of Code
dataAdapterCurrentTest.InsertCommand=insertCmd
所以最终的代码是
class Program
{
static void Main(string[] args)
{
MySqlConnectionStringBuilder connBuilder = new MySqlConnectionStringBuilder();
connBuilder.Server = "ds2";
connBuilder.Database = "fhem";
connBuilder.UserID = "fhemdbuser";
connBuilder.Port = 3307;
connBuilder.Password = "!2345Abcde";
//connBuilder.DefaultCommandTimeout = 120;
//connBuilder.UseDefaultCommandTimeoutForEF = true;
//connBuilder.AllowUserVariables = true;
MySqlConnection conn = new MySqlConnection(connBuilder.ConnectionString);
conn.Open();
MySqlCommand selectCmdCurrent = new MySqlCommand("select `TIMESTAMP`, `DEVICE`, `TYPE`, `EVENT`, `READING`, `VALUE`, `UNIT` from current;", conn);
MySqlCommand selectCmdCurrentTest = new MySqlCommand("select `TIMESTAMP`, `DEVICE`, `TYPE`, `EVENT`, `READING`, `VALUE`, `UNIT` from currentTest;", conn);
MySqlCommand updateCmd = new MySqlCommand("update `currentTest` set `TIMESTAMP`=@p1,`DEVICE`=@p2,`TYPE`=@p3,`EVENT`=@p4,`READING`=@p5,`VALUE`=@p6,`UNIT`=@p7", conn);
updateCmd.Parameters.Add("@p1", MySql.Data.MySqlClient.MySqlDbType.DateTime);
updateCmd.Parameters.Add("@p2", MySql.Data.MySqlClient.MySqlDbType.VarString, 128);
updateCmd.Parameters.Add("@p3", MySql.Data.MySqlClient.MySqlDbType.VarString, 128);
updateCmd.Parameters.Add("@p4", MySql.Data.MySqlClient.MySqlDbType.VarString, 512);
updateCmd.Parameters.Add("@p5", MySql.Data.MySqlClient.MySqlDbType.VarString, 128);
updateCmd.Parameters.Add("@p6", MySql.Data.MySqlClient.MySqlDbType.VarString, 128);
updateCmd.Parameters.Add("@p7", MySql.Data.MySqlClient.MySqlDbType.VarString, 128);
MySqlCommand insertCmd = new MySqlCommand("insert into `currentTest` values(@p1, @p2, @p3, @p4, @p5, @p6, @p7)", conn);
insertCmd.Parameters.Add("@p1", MySql.Data.MySqlClient.MySqlDbType.DateTime);
insertCmd.Parameters.Add("@p2", MySql.Data.MySqlClient.MySqlDbType.VarString, 128);
insertCmd.Parameters.Add("@p3", MySql.Data.MySqlClient.MySqlDbType.VarString, 128);
insertCmd.Parameters.Add("@p4", MySql.Data.MySqlClient.MySqlDbType.VarString, 512);
insertCmd.Parameters.Add("@p5", MySql.Data.MySqlClient.MySqlDbType.VarString, 128);
insertCmd.Parameters.Add("@p6", MySql.Data.MySqlClient.MySqlDbType.VarString, 128);
insertCmd.Parameters.Add("@p7", MySql.Data.MySqlClient.MySqlDbType.VarString, 128);
MySqlDataAdapter dataAdapterCurrent = new MySqlDataAdapter(selectCmdCurrent);
MySqlDataAdapter dataAdapterCurrentTest = new MySqlDataAdapter(selectCmdCurrentTest);
// MySqlCommandBuilder builder = new MySqlCommandBuilder(dataAdapterCurrent);
// dataAdapterCurrentTest.InsertCommand = insertCmd;
dataAdapterCurrentTest.UpdateCommand = updateCmd;
DataSet ds = new DataSet();
MySqlCommand prepareCmd = new MySqlCommand("truncate currentTest;", conn);
prepareCmd.ExecuteNonQuery();
//prepareCmd = new MySqlCommand("insert into currentTest select * from current;", conn);
//prepareCmd.ExecuteNonQuery();
prepareCmd = new MySqlCommand("INSERT INTO `currentTest` VALUES(\"2020-06-06 12:45:23\",\"DEVICE\", \"TYPE\", \"EVENT\", \"READING\", \"VALUE\", \"UNIT\")", conn);
for (int i = 0; i < 10; i++)
prepareCmd.ExecuteNonQuery();
dataAdapterCurrentTest.Fill(ds, "currentTest");
dataAdapterCurrent.Fill(ds, "current");
int row = 0;
Stopwatch sw = new Stopwatch();
for (int i = 0; i < 10; i++)
{
DataRow newrow = ds.Tables["currentTest"].NewRow();
newrow["Timestamp"] = DateTime.Now;
newrow["Device"] = "Device";
newrow["TYPE"] = "Type";
newrow["EVENT"] = "Event";
newrow["READING"] = "Reading";
newrow["VALUE"] = "Value";
newrow["UNIT"] = "Unit";
ds.Tables["currentTest"].Rows.Add(newrow);
}
new MySqlCommandBuilder(dataAdapterCurrentTest);
dataAdapterCurrentTest.Update(ds.Tables["currentTest"]);
ds.Tables["currentTest"].AcceptChanges();
}
}
这篇关于将值插入mysql数据库无法正常工作的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!