这个查询我做错了什么？

I can't seem to find why this function doesn't insert records into the database. :( I get no error messages or whatsoever, just nothing in the database.

我似乎无法找到为什么这个函数不会将记录插入数据库。 :(我得到没有错误消息或任何东西,只是在数据库中没有。

EDIT: this is how my query looks now .. still nothing ..

编辑:这是我的查询现在看起来..仍然没有..

connection.Open();
XmlNodeList nodeItem = rssDoc.SelectNodes("/edno23/posts/post");

foreach (XmlNode xn in nodeItem)
{
    cmd.Parameters.Clear();
    msgText = xn["message"].InnerText;
    C = xn["user_from"].InnerText;
    avatar = xn["user_from_avatar"].InnerText;
    string   endhash =  GetMd5Sum(msgText.ToString());
    cmd.Parameters.Add("@endhash",endhash);
    cmd.CommandText  = "Select * FROM posts Where hash=@endhash";
    SqlCeDataReader reader = cmd.ExecuteReader();

    while (reader.Read())
    {
        string msgs = reader["hash"].ToString();

        if (msgs != endhash || msgs == null)
        {
            sql = "INSERT INTO posts([user],msg,avatar,[date],hash) VALUES(@username,@messige,@userpic,@thedate,@hash)";
            cmd.CommandText = sql;
            cmd.Parameters.Add("@username", C);
            cmd.Parameters.Add("@messige", msgText.ToString());
            cmd.Parameters.Add("@userpic", avatar.ToString());
            cmd.Parameters.Add("@thedate", dt);
            cmd.Parameters.Add("@hash", endhash);
            cmd.ExecuteNonQuery();// executes query
            adapter.Update(data);// saves the changes
        }
    }

    reader.Close();
}

connection.Close();

7 个解决方案

#1

Does nodeItem actually have any items in it? If not, the contents of the foreach loop aren't being executed.

nodeItem实际上是否包含任何项目?如果不是,则不执行foreach循环的内容。

What's the adapter and data being used for? The queries and updates seem be done via other commands and readers.

什么是适配器和数据用于什么?查询和更新似乎是通过其他命令和读者完成的。

What does 'hash' actually contain? If it's a hash, why are you hashing the content of the hash inside the while loop? If not, why is it being compared against a hash in the query SELECT * FROM posts WHERE hash = @endhash?

'hash'实际包含什么?如果是哈希,为什么要在while循环中散列哈希的内容?如果没有,为什么要将它与查询中的哈希进行比较SELECT * FROM posts WHERE hash = @endhash?

Won't closing the connection before the end of the while loop invalidate the reader used to control the loop?

在while循环结束之前不会关闭连接会使用于控制循环的阅读器无效吗?

#2

Lots of things going on here...

这里发生了很多事......

You are using the command 'cmd' to loop over records with a datareader, and then using the same 'cmd' command inside the while statement to execute an insert statement. You declared another command 'cmdAdd' before but don't seem to use it anywhere; is that what you intended to use for the insert statement?

您正在使用命令'cmd'使用datareader循环遍历记录,然后在while语句中使用相同的'cmd'命令来执行insert语句。您之前声明了另一个命令'cmdAdd'但似乎没有在任何地方使用它;你打算用什么插入语句?

You also close your data connection inside the while loop that iterates over your datareader. You are only going to read one record and then close the connection to your database that way; if your conditional for inserting is not met, you're not going to write anything to the database.

您还可以在遍历datareader的while循环内关闭数据连接。您只会读取一条记录,然后以这种方式关闭与数据库的连接;如果不符合插入条件,则不会向数据库写入任何内容。

EDIT:

You really should open and close the connection to the database outside the foreach on the xmlnodes. If you have 10 nodes to loop over, the db connection is going to be opened and closed 10 times (well, connection pooling will probably prevent that, but still...)

你真的应该在xmlnodes上的foreach之外打开和关闭数据库的连接。如果你有10个节点要循环,那么db连接将被打开和关闭10次(好吧,连接池可能会阻止它,但仍然......)

You are also loading the entire 'posts' table into a dataset for seemingly no reason. You're not changing any of the values in the dataset yet you are calling an update on it repeatedly (at "save teh shanges"). If the 'posts' table is even remotely large, this is going to suck a lot of memory for no reason (on a handheld device, no less).

您还将整个“帖子”表加载到数据集中,看似没有理由。您没有更改数据集中的任何值,而是重复调用它的更新(在“save teh shanges”中)。如果'posts'表甚至是远程大的,这将无缘无故地占用大量内存(在手持设备上,不能少)。

#3

Is anything returned from "Select * FROM posts Where hash=@endhash"?

从“Select * FROM posts Where hash = @ endhash”返回的是什么?

If not, nothing inside the while loop matters....

如果没有,while循环内部没有任何内容......

#4

Why are you closing the Database Connection inside the while loop?
The code you posted should throw an exception when you try to call cmd.ExecuteNonQuery() with an unopen DB connection object.

为什么要在while循环中关闭数据库连接?当您尝试使用未打开的数据库连接对象调用cmd.ExecuteNonQuery()时,您发布的代码应该抛出异常。

SqlCeCommand.ExecuteNonQuery() method returns the number of rows affected.
Why don't you check whether it is returning 1 or not in the debugger as shown below?

SqlCeCommand.ExecuteNonQuery()方法返回受影响的行数。你为什么不检查它是否在调试器中返回1,如下所示?

int rowsAffectedCount = cmd.ExecuteNonQuery();

Hope it helps :-)

希望能帮助到你 :-)

#5

You've got some issues with not implementing "using" blocks. I've added some to your inner code below. The blocks for the connection and select command are more wishful thinking on my part. I hope you're doing the same with the data adapter.

你有一些问题没有实现“使用”块。我在下面的内部代码中添加了一些内容。连接和选择命令的块对我来说是更加一厢情愿的想法。我希望你对数据适配器做同样的事情。

using (var connection = new SqlCeConnection(connectionString))
{
    connection.Open();
    var nodeItem = rssDoc.SelectNodes("/edno23/posts/post");

    foreach (XmlNode xn in nodeItem)
    {
        using (
            var selectCommand =
                new SqlCeCommand(
                    "Select * FROM posts Where hash=@endhash",
                    connection))
        {
            var msgText = xn["message"].InnerText;
            var c = xn["user_from"].InnerText;
            var avatar = xn["user_from_avatar"].InnerText;
            var endhash = GetMd5Sum(msgText);
            selectCommand.Parameters.Add("@endhash", endhash);
            selectCommand.CommandText =
                "Select * FROM posts Where hash=@endhash";
            using (var reader = selectCommand.ExecuteReader())
            {
                while (reader.Read())
                {
                    var msgs = reader["hash"].ToString();

                    if (msgs == endhash && msgs != null)
                    {
                        continue;
                    }

                    const string COMMAND_TEXT =
                        "INSERT INTO posts([user],msg,avatar,[date],hash) VALUES(@username,@messige,@userpic,@thedate,@hash)";
                    using (
                        var insertCommand =
                            new SqlCeCommand(
                                COMMAND_TEXT, connection))
                    {
                        insertCommand.Parameters.Add("@username", c);
                        insertCommand.Parameters.Add(
                            "@messige", msgText);
                        insertCommand.Parameters.Add(
                            "@userpic", avatar);
                        insertCommand.Parameters.Add("@thedate", dt);
                        insertCommand.Parameters.Add(
                            "@hash", endhash);
                        insertCommand.ExecuteNonQuery();
                            // executes query
                    }
                    adapter.Update(data); // saves teh changes
                }

                reader.Close();
            }
        }
    }

    connection.Close();
}

Of course with the additional nesting, parts should be broken out as separate methods.

当然,通过额外的嵌套,零件应该作为单独的方法分解。

#6

I suspect your problem is that you're trying to reuse the same SqlCeCommand instances.

我怀疑你的问题是你正在尝试重用相同的SqlCeCommand实例。

Try making a new SqlCeCommand within the while loop. Also, you can use the using statement to close your data objects.

尝试在while循环中创建一个新的SqlCeCommand。此外,您可以使用using语句来关闭数据对象。

---

Why are you calling adapter.Update(data) since you're not changing the DataSet at all? I suspect you want to call adapter.Fill(data). The Update method will save any changes in the DataSet to the database.

你为什么要调用adapter.Update(data),因为你根本没有改变DataSet?我怀疑你想调用adapter.Fill(数据)。 Update方法将DataSet中的任何更改保存到数据库。

#7

How to debug programs: http://www.drpaulcarter.com/cs/debug.php

如何调试程序:http://www.drpaulcarter.com/cs/debug.php

Seriously, can you post some more information about where it's working? Does it work if you use SQL Server Express instead of SQL CE? If so, can you break out SQL Profiler and take a look at the SQL commands being executed?

说真的,你能发布一些关于它工作地点的更多信息吗?如果您使用SQL Server Express而不是SQL CE,它是否有效?如果是这样,您可以打破SQL事件探查器并查看正在执行的SQL命令吗?