如何向 SQL Server Compact 添加记录 [英] How to add record to SQL Server Compact
问题描述
我看过很多视频和答案，现在在网上我有一个有效的例子.
I have looked at many videos and answers, on the web I now have a working example.
我希望将文件拖放到面板上，并具有我提供的名称以及记录到数据库中的文件路径.一些示例显示了 SQL 部分的三行，我有一行.
I wish to drag and drop files onto a panel and have the name I provide plus a path to the file recorded into the database. Some examples show three lines for the SQL part I have one.
SqlCeCommand cmd = new SqlCeCommand("INSERT INTO NStacks(NStacksName, NStacksItem)VALUES((textBox1.Text) + (File))", con);
其他人有不同的路线.
当程序在没有SQL查询的情况下正常执行时，该行
When the program is executed normally without the SQL query then the line
MessageBox.Show("Attempted write DB " + textBox1.Text +" "+ File);
将显示应输入数据库的给定信息.
will display the given information that should be entered into the Database.
我现在面临着数据库错误消息，我不知道究竟出了什么问题.
I am now faced with database error messages which I don’t know what exactly is wrong.
解析查询时出错.[令牌行数=1，令牌行偏移=72，令牌错误=文件]".
"There was an error parsing the query. [Token line number =1,Token line offset =72,Token in error = File]".
我的代码概述如下.
namespace NStacks1
{
public partial class Form1 : Form
{
SqlCeConnection con = new SqlCeConnection("Data Source=C:\\Users\\userename\\Documents\\Visual Studio 2010\\Projects\\NStacks1\\NStacks1\\NStacks.sdf");
SqlCeCommand cmd;
public Form1()
{
InitializeComponent();
}
private void panel1_DragDrop(object sender, DragEventArgs e)
{
if (textBox1.Text.Equals(""))
{
MessageBox.Show("Blank name please enter a name and try again.");
}
else
{
string[] Files = (string[])e.Data.GetData(DataFormats.FileDrop, false);
foreach (string File in Files)
{
try
{
con.Open();
SqlCeCommand cmd = new SqlCeCommand("INSERT INTO NStacks(NStacksName, NStacksItem)VALUES((textBox1.Text) + (File))", con);
cmd.ExecuteNonQuery();
MessageBox.Show("Attempted write DB " + textBox1.Text +" "+ File);
con.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
con.Close();
}
}
}
}
private void panel1_DragEnter(object sender, DragEventArgs e)
{
e.Effect = DragDropEffects.All;
}
}
}
推荐答案
INSERT INTO NStacks(NStacksName, NStacksItem)VALUES((textBox1.Text) + (File))
INSERT INTO NStacks(NStacksName, NStacksItem)VALUES((textBox1.Text) + (File))
您告诉数据库 INSERT INTO 表 NStacks VALUES "(textBox1.Text) + (File)" 仅在 NStacksName 列上，因为您缺少逗号 , 来分隔值.您不是插入字符串，而是插入不存在的关键字，从而导致 SQL 解析错误.这是 SQL 的解释方式(注意红色摆动线):
You're telling the DB to INSERT INTO the table NStacks the VALUES "(textBox1.Text) + (File)" on the column NStacksName ONLY, as you're missing a comma , to separate the values. You aren't inserting a string, you're inserting non-existent keywords, creating the SQL parse error. This is how the SQL is interpreted (note the red wiggle line):
SQL 中的字符串必须用单引号 ' 括起来，如 ... VALUES ('value1', 'value2')，如下所示:
Strings in SQL must be enclosed with single-quotes ' like ... VALUES ('value1', 'value2'), looking like this:
此外，您需要使用参数清理您的 SQL！(答案是在 PHP 中)，使用 C# 它看起来像:
Also, you need to sanitize your SQL with parameters! (answer is in PHP), with C# it will look like:
SqlCeCommand command = new SqlCeCommand("INSERT INTO NStacks(NStacksName, NStacksItem)VALUES(@NStacksName, @NStacksItem)", con);
command.Parameters.Add(new SqlCeParameter("NStacksName", textBox1.Text));
command.Parameters.Add(new SqlCeParameter("NStacksItem", File));
command.ExecuteNonQuery();
或者使用数据库连接类:
Or with a DB connection class:
using System;
using System.Data;
using System.Data.SqlServerCe;
namespace DataBase
{
public class DBConnection
{
private SqlCeConnection sqlConnection;
public DBConnection(string connectionString)
{
sqlConnection = new SqlCeConnection(connectionString);
}
private bool CloseConnection(SqlConnection sqlConnection)
{
try
{
sqlConnection.Close();
return true;
}
catch (SqlException e)
{
//Handle exception
return false;
}
}
private bool OpenConnection(SqlConnection sqlConnection)
{
try
{
sqlConnection.Open();
return true;
}
catch (SqlCeException e)
{
//Handle exception
return false;
}
}
public DataTable NonQuery(string sqlString, params SqlCeParameter[] sqlParameters)
{
DataTable table = new DataTable();
table.Columns.Add(new DataColumn("Affected Rows", typeof(int)));
if (this.OpenConnection(this.sqlConnection))
{
try
{
SqlCeCommand sqlCommand = new SqlCeCommand(sqlString, this.sqlConnection);
sqlCommand.Parameters.AddRange(sqlParameters);
table.Rows.Add(sqlCommand.ExecuteNonQuery());
}
catch (SqlCeException e)
{
table.Rows.Add(0);
//Handle exception
}
finally
{
this.CloseConnection(this.sqlConnection);
}
}
return table;
}
public DataTable Query(string sqlString, params SqlCeParameter[] sqlParameters)
{
DataTable table = new DataTable();
if (this.OpenConnection(this.sqlConnection))
{
try
{
SqlCeCommand sqlCommand = new SqlCeCommand(sqlString, this.sqlConnection);
sqlCommand.Parameters.AddRange(sqlParameters);
SqlCeDataAdapter sqlDataAdapter = new SqlCeDataAdapter(sqlCommand);
sqlDataAdapter.Fill(table);
}
catch (SqlCeException e)
{
//Handle exception
}
finally
{
this.CloseConnection(this.sqlConnection);
}
}
return table;
}
}
}
并称之为:
// Outside foreach loop (better make it a class field and initialize this
// inside the class constructor)
DBConnection db = new DBConnection(connectionString);
// Inside foreach loop
DataTable result = db.NonQuery("INSERT INTO NStacks(NStacksName, NStacksItem)VALUES(@NStacksName, @NStacksItem)",
new SqlCeParameter("NStacksName", textBox1.Text),
new SqlCeParameter("NStacksItem", File));
这篇关于如何向 SQL Server Compact 添加记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
