数据存储问题 [英] Data storing problem

问题描述

我编写了一些代码，其中大文本被分成一个句子&存储在表格和如果句子中已经存在该句子，那么它就不会被存储。这是我的代码：

I have written code in which large text is splitted into a sentence & stored in a table & if the sentence already exists in the table, then it's not stored. Here is my code:

int index = Convert.ToInt32(e.CommandArgument.ToString());

                GridViewRow row = GridView1.Rows[index];

                var text = GridView1.Rows[index].Cells[0].Text;

                String[] sentences = Regex.Split(text, @"(?<=[.!?])\s+(?=\p{Lt})");

                System.Data.DataTable dt1 = new System.Data.DataTable();

                dt1.Columns.Add("SentenceText1");

                foreach (string value in sentences)
                {
                    dt1.Rows.Add(value);
                }
                SqlConnection con = new SqlConnection(conn);
                con.Open();
                string sql = "";
                string apply = "";
                for (int i = 0; i < dt1.Rows.Count; i++)
                {
                    sql = sql + "INSERT INTO Sentences(SentenceText) SELECT '" + dt1.Rows[i]["SentenceText1"].ToString() + "' FROM Sentences WHERE NOT EXISTS(SELECT SentenceText FROM Sentences WHERE SentenceText='" + dt1.Rows[i]["SentenceText1"].ToString() + "')";
                    apply = apply + "insert into ContentSentences(ContentID)select ContentID from Contents"
                    SqlCommand cmd = new SqlCommand(sql, con,);
                    cmd.ExecuteNonQuery();
                }

它有效，但它存储了 表中的值。那么如何解决这个问题？

It works but it stores the " " value in the table. So how can I resolve this problem?

推荐答案

首先你应该使用参数化查询 - 参见 http://blog.codinghorror.com/give-me-parameterized-sql-or-give-me-death/ [< a href =http://blog.codinghorror.com/give-me-parameterized-sql-or-give-me-death/target =_ blanktitle =New Window> ^ ]



2 - 这甚至都没有编译，所以我怀疑你在这里复制代码时犯了错误（参见 apply = apply ...



3 - 您试图从数据库而不是列名中选择句子文本：我认为

Firstly you should be using a parameterized query - see http://blog.codinghorror.com/give-me-parameterized-sql-or-give-me-death/[^]

2 - this doesn't even compile, so I suspect you've made an error as you copied the code here (see apply = apply...

3 - You are attempting to select the sentence text from the database instead of the column name: I think

...SELECT '" + dt1.Rows[i]["SentenceText1"].ToString() + "' FROM Sentences...

应该

...SELECT SentenceText FROM Sentences...

4 - SQL看起来没有 - 它似乎是将东西插回到已经存在的表中。你需要检查你的方式。



5。最后，当您循环遍历结果时，您可以避免使用类似

4 - The SQL doesn't look at all right - it appears to be inserting stuff back into the table that is already there. You need to review the way you are doing that.

5. Finally, as you are looping through the results anyway you can avoid blanks with something like

foreach (var value in sentences)
{
    if(!string.IsNullOrWhiteSpace(value))
        dt1.Rows.Add(value);
}

或使用linq

foreach (var value in sentences.Where(value => !string.IsNullOrWhiteSpace(value)))
{
    dt1.Rows.Add(value);
}

在使用sql（使用参数化查询!!）后，我注意到你将插入值的次数与表中已有的行数一样多。我修改了sql只是添加了一行像这样的

[edit - Points 3 and 4 were just wrong - sorry]
[edit 2]
After playing around with the sql (using parameterized query!!) I noticed that you will insert the value for as many times as you already have rows on the table. I amended the sql to just add a single row like this

INSERT INTO Sentences(SentenceText) SELECT TOP 1 @sentenceText
FROM Sentences WHERE NOT EXISTS(SELECT SentenceText FROM Sentences WHERE SentenceText=@sentenceText)

这篇关于数据存储问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！