C＃ExecuteNonQuery不更新数据库 [英] C# ExecuteNonQuery not updating database
问题描述
大家好，
我有以下代码，而且它没有更新数据库
Hi guys,
I have this following code, and it's not updating the database
const string str = @"Data Source=localhost;Initial Catalog=Items;Integrated Security=True";
static void Main(string[] args)
{
const string connectionString = str;
DataTable DataTableAllWithoutID = new DataTable();
#if !test
string queryString = "select * from table_items_shelves;";
SqlDataAdapter adapterAllWithoutID = new SqlDataAdapter(queryString, connectionString);
adapterAllWithoutID.Fill(DataTableAllWithoutID);
adapterAllWithoutID.Dispose();
SqlConnection connection = new SqlConnection(connectionString);
connection.Open();
string insertString = "Update table_items_shelves Set Item_ID = @Item_ID where Item_Name = '@key';";
SqlCommand insertCommand = new SqlCommand(insertString, connection);
insertCommand.Parameters.Add("@Item_ID", SqlDbType.Int);
insertCommand.Parameters.Add("@key", SqlDbType.NVarChar);
#else
DataTableAllWithoutID.Columns.Add("Item_Name", typeof(string));
DataTableAllWithoutID.Columns.Add("Item_ID", typeof(object));
foreach (List<object> row in input)
{
DataRow newRow = DataTableAllWithoutID.Rows.Add();
newRow.ItemArray = row.ToArray();
}
#endif
//this code will get empty items
List<DataRow> nullOrEmpty = DataTableAllWithoutID.AsEnumerable()
.Where(x => x.Field<object>("Item_ID") == null)
.ToList();
//this creates a dictionary of valid items
Dictionary<int, List<DataRow>> dict = DataTableAllWithoutID.AsEnumerable()
.Where(x => x.Field<object>("Item_ID") != null)
.GroupBy(x => x.Field<object>("Item_ID"), x => x)
.ToDictionary(x => Convert.ToInt32(x.Key), x => (List<DataRow>)x.ToList());
//create IEnumerator for the null items
IEnumerator<DataRow> emptyRows = nullOrEmpty.GetEnumerator();
Boolean noMoreEmptyRows = false;
if (emptyRows != null)
{
foreach (int key in dict.Keys)
{
Console.WriteLine(key.ToString());
//get count of items
int count = dict[key].Count;
int itemID = (int)key;
for (int index = count; count < 8; count++)
{
if (emptyRows.MoveNext())
{
//get an item from the null list
emptyRows.Current["Item_ID"] = itemID;
insertCommand.Parameters["@Item_ID"].Value = itemID;
insertCommand.Parameters["@key"].Value = emptyRows.Current["Item_Name"];
insertCommand.ExecuteNonQuery();
Console.WriteLine("current item ID " + itemID);
Console.WriteLine("current count " + count);
//Console.ReadKey();
}//end if
else
{
noMoreEmptyRows = true;
break;
}//end else
}//end for
if (noMoreEmptyRows)
break;
}//end foreach
if (!noMoreEmptyRows)
{
//increment key to one greater than max value
int maxKey = dict.Keys.Max() + 1;
int count = 0;
while (emptyRows.MoveNext())
{
//get an item from the null list
emptyRows.Current["Item_ID"] = maxKey.ToString();
insertCommand.Parameters["@Item_ID"].Value = maxKey.ToString();
insertCommand.Parameters["@key"].Value = emptyRows.Current["Item_ID"];
insertCommand.ExecuteNonQuery();
count++;
if (count == 8)
{
maxKey++;
count = 0;
}
}
}
}//end if
#if test
foreach (DataRow row in DataTableAllWithoutID.Rows)
{
Console.WriteLine("Item_Name : {0} Item ID : {1}",
row["Item_Name"], row["Item_ID"]);
}
#endif
FIllGroupID(str);
Console.ReadKey();
}
推荐答案
你可以试试
You could try
DataTableAllWithoutID.Columns.Add("Item_ID", typeof(int?));
而不是对象，
和
instead of object,
and
insertCommand.Parameters["@Item_ID"].Value = maxKey
（消灭.ToString（））因为你的数据库系统需要一个整数值。
- 你还必须在 Main的开头加一个断点（ ）方法，并以调试模式运行项目（Visual Studio中的F5）。然后，您将能够逐行执行代码（F11）并检查变量的值。你期望他们回归什么？你真正得到了什么？这两个问题的答案之间的差异总是会导致潜在的问题。
- 为什么要定义全局const字符串 str ，并将其分配给 Main（）方法开头的另一个const字符串？全球应该足够了:)
-
(wipe out the .ToString()) since your DB system expects an integer value here.
[edit]
- You also have to put a breakpoint in the beginning of your Main() method, and run your project in debug mode (F5 in Visual Studio). You will then be able to execute your code line by line (F11) and check the values of your variables. What do you expect them to return? What do you get for real? The difference between answers of both this questions always leads to a potential problem.
- Why do you define a global const string str, and assign it to another const string in the beginning of your Main() method? The global one should suffice :)
- What is input in
foreach (List<object> row in input)？
- 你的桌子的 [Item_ID] 列是否可以为空？
[/ edit]
?
- Is the [Item_ID] column of your table a nullable one?
[/edit]
以下是问题所在。
Here is what the problem was.
where Item_Name = '@key';
this is a match for the literal '@key' - not the parameter @key. and i needed:
where Item_Name = @key;
这篇关于C＃ExecuteNonQuery不更新数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
