SQL Server CE not supporting ExecuteSQLCommand with EF?

Question

我一直在使用带有实体框架的SQL Server Express LocalDb与VS2015开发一个C# WPF项目。我为数据库构建了一个自定义播种器，它从 Excel 文件中读取测试数据，只需将 Excel 数据合并到命令字符串中，然后使用context.Database.ExecuteSQLCommand插入。

现在，我正在考虑使用 SQL Server Compact Edition 4.0 启动该项目，但我发现此命令不再有效。我是否必须使用SqlCeConnection和SqlCeCommand再次写我的上传者，或者我错过了什么？

此外，从某个地方我了解到，使用 EF，您可以切换 SQL 提供程序，并且代码不需要其他更改。我将来会有更多的惊喜吗？

上传程序命令示例：

string cmd = "INSERT INTO Venues(Name, City, Telephone) Values ('X','Y','Z')"
context.Database.ExecuteSqlCommand(cmd);

错误：

解析查询时出错。[ 令牌行号 = 2，令牌行偏移量 = 1，标记错误 = 插入 ]

这不仅仅是一个测试问题，因为我也想将此上传器包含在生产版本中，以便快速插入主数据(例如员工列表(。

编辑：上传者代码。如果可以在不诉诸原始SQL的情况下完成此操作，那也将是一个很好的解决方案。

这将循环遍历 Excel 工作表(以实体命名(和列(第一行具有属性名称(以及行 2->n(数据(。这基本上可以处理 Excel 限制内任何数量的数据的上传。关键是代码不知道实体(也可以参数化 DataContext(。代码可能不是最佳的，因为我只是一个初学者，但对我有用，除了不适用于 SQL CE。编辑以适应CE不是一个大问题，但我想问一下可能更好的方法。

public static class ExcelUploader
{
static ArrayList data;
static List<string> tableNames;
public static string Upload(string filePath)
{
string result = "";
data = new ArrayList();
tableNames = new List<string>();
ArrayList upLoadData = ReadFile(filePath);
List<string> dataList = ArrayListToStringList(upLoadData);
using (var db = new DataContext())
{
using (var trans = db.Database.BeginTransaction())
{
try
{
foreach (var cmd in dataList)
{
Console.WriteLine(cmd);
db.Database.ExecuteSqlCommand(cmd);
}
db.SaveChanges();
trans.Commit();
}
catch (Exception e)
{
trans.Rollback();
result = e.Message;
MessageBox.Show(result);
}
}
}
return result;
}

private static ArrayList ReadFile(string fileName)
{
List<string> commands = new List<string>();
var xlApp = new Microsoft.Office.Interop.Excel.Application();
var wb = xlApp.Workbooks.Open(fileName, ReadOnly: true);
xlApp.Visible = false;
foreach (Worksheet ws in wb.Worksheets)
{
var r = ws.UsedRange;
var array = r.Value;
data.Add(array);
tableNames.Add(ws.Name);
}
wb.Close(SaveChanges: false);
xlApp.Quit();
return data;
}
private static List<string> ArrayListToStringList(ArrayList arrList)
{
List<string> result = new List<string>();
for(int tableAmount = 0;tableAmount<data.Count;tableAmount++)
{
result.Add(ArrayToSqlCommand(arrList[tableAmount] as Array, tableNames[tableAmount]));
}
return result;
}
private static string ArrayToSqlCommand(Array arr, string tableName)
{
int propertyRow = 1;
int firstDataRow = 2;
string command = "";
// loop rows                
for (int rowIndex = firstDataRow; rowIndex <= arr.GetUpperBound(0); rowIndex++)
{
command += "INSERT INTO " + tableName + "(";
//add column names
for (int colIndex = 1; colIndex <= arr.GetUpperBound(1); colIndex++)
{
//get property name
command += arr.GetValue(propertyRow, colIndex);
//add comma if not last column, otherwise close bracket
if (colIndex == arr.GetUpperBound(1))
{
command += ") Values (";
}
else
{
command += ", ";
}
}
//add values
for (int colIndex = 1; colIndex <= arr.GetUpperBound(1); colIndex++)
{
//get property value
command += "'" + arr.GetValue(rowIndex, colIndex) + "'";
//add comma if not last column, otherwise close bracket
if (colIndex == arr.GetUpperBound(1))
{
command += ");";
}
else
{
command += ", ";
}
}
command += "n";
}
return command;
}
}

Answer 1

我提供的有两种使用原始SQL查询的方法。

初始数据

1( Excel表格

+========+==================== +

|姓名 |城市 |电话 | |===========================| |亚当 |地址1 |111-11-11 | |-------|-------|-----------| |彼得 |地址2 |222-22-22 | +-------+-------+-----------+

2( SQL Server CE 表

CREATE TABLE Venues
(
Id        int identity primary key,
[Name]    nvarchar(100) null,
City      nvarchar(100) null,
Telephone nvarchar(100) null
);

3(从Excel获取数据

在这里，我们有兴趣从Excel工作表中获取数组。一旦我们得到它，我们就可以安全地关闭Excel。该代码假定文件"员工.xlsx"位于可执行文件旁边。

private object[,] GetExcelData()
{
xlApp = new Excel.Application { Visible = false };
var xlBook =
xlApp.Workbooks.Open(System.IO.Path.Combine(
Environment.CurrentDirectory,
"Employees.xlsx"));
var xlSheet = xlBook.Sheets[1] as Excel.Worksheet;
// For process termination
var xlHwnd = new IntPtr(xlApp.Hwnd);
var xlProc = Process.GetProcesses()
.Where(p => p.MainWindowHandle == xlHwnd)
.First();
// Get Excel data: it's 2-D array with lower bounds as 1.
object[,] arr = xlSheet.Range["A1"].CurrentRegion.Value;
// Shutdown Excel
xlBook.Close();
xlApp.Quit();
xlProc.Kill();
GC.Collect();
GC.WaitForFullGCComplete();
return arr;
}

现在，您可以使用其中一种方法来生成查询。

选项 1.使用 ExecuteSqlCommand

使用ExecuteSqlCommand时，建议使用参数化查询以避免错误。可以传递显式创建的SqlCeParameter，也可以只传递值。

private void UseExecuteSqlCommand()
{
object[,] arr = GetExcelData();
using (var db = new EmpContext())
{
db.Database.Initialize(true);
int count = 0;
string sql = "INSERT INTO Venues (Name, City, Telephone) " +
"VALUES (@name, @city, @phone);";
// Start from 2-nd row since we need to skip header
for (int r = 2; r <= arr.GetUpperBound(0); ++r)
{
db.Database.ExecuteSqlCommand(
sql,
new SqlCeParameter("@name", (string)arr[r, 1]),
new SqlCeParameter("@city", (string)arr[r, 2]),
new SqlCeParameter("@phone", (string)arr[r, 3])
);
++count;
}
conn.Close();
MessageBox.Show($"{count} records were saved.");
}
}

选项 2.使用数据库连接

如果您希望代码更通用，则可以创建接受DbConnection的方法。这将允许通过SqlConnection或SqlCeConnection。但是代码变得更加冗长，因为我们无法使用构造函数，因为这些类是抽象的。

private void UseDbConnection()
{
object[,] arr = GetExcelData();
using (var db = new EmpContext())
{
db.Database.Initialize(true);
int count = 0;
string sql = "INSERT INTO Venues (Name, City, Telephone) " +
"VALUES (@name, @city, @phone);";
DbParameter param = null;
DbConnection conn = db.Database.Connection;
conn.Open();
DbCommand command = conn.CreateCommand();
command.CommandText = sql;
command.CommandType = CommandType.Text;
// Create parameters
// Name
param = command.CreateParameter();
param.ParameterName = "@name";
command.Parameters.Add(param);
// City
param = command.CreateParameter();
param.ParameterName = "@city";
command.Parameters.Add(param);
// Telephone
param = command.CreateParameter();
param.ParameterName = "@phone";
command.Parameters.Add(param);
// Start from 2-nd row since we need to skip header
for (int r = 2; r <= arr.GetUpperBound(0); ++r)
{
command.Parameters["@name"].Value = (string)arr[r, 1];
command.Parameters["@city"].Value = (string)arr[r, 2];
command.Parameters["@phone"].Value = (string)arr[r, 3];
command.ExecuteNonQuery();
++count;
}
conn.Close();
MessageBox.Show($"{count} records were saved.");
}
}

您还可以对参数使用序号位置，这样就无需创建参数名称并使代码更短：

private void UseDbConnection()
{
object[,] arr = GetExcelData();
using (var db = new EmpContext())
{
db.Database.Initialize(true);
int count = 0;
// Take a note - use '?' as parameters
string sql = "INSERT INTO Venues (Name, City, Telephone) " +
"VALUES (?, ?, ?);";
DbConnection conn = db.Database.Connection;
conn.Open();
DbCommand command = conn.CreateCommand();
command.CommandText = sql;
command.CommandType = CommandType.Text;
// Create parameters
command.Parameters.Add(command.CreateParameter());
command.Parameters.Add(command.CreateParameter());
command.Parameters.Add(command.CreateParameter());
for (int r = 2; r <= arr.GetUpperBound(0); ++r)
{
// Access parameters by position
command.Parameters[0].Value = (string)arr[r, 1];
command.Parameters[1].Value = (string)arr[r, 2];
command.Parameters[2].Value = (string)arr[r, 3];
command.ExecuteNonQuery();
++count;
}
conn.Close();
MessageBox.Show($"{count} records were saved.");
}
}

附言我没有检查底层连接是否已打开，但这样做是个好主意。

Answer 2

基于JohnyL的出色输入，我能够修改我的代码，使其与SQL Server Express和SQL Server CE一起使用。我将把我的新代码作为答案，因为我必须进一步参数化它，因为我也无法在代码中写入属性名称。但这是一个简单的步骤，一旦我从JohnyL那里得到了这个想法。不确定，数据库写入操作是否应该包装在 DbTransaction 中，但这目前有效。

public static class ExcelUploader
{
static ArrayList data;
static List<string> tableNames;
static List<DbCommand> cmdList = new List<DbCommand>();
static DbConnection conn;
public static void Upload(string filePath)
{
data = new ArrayList();
tableNames = new List<string>();
//get Excel data to array list
ArrayList upLoadData = ReadFile(filePath);
using (var db = new DataContext())
{
conn = db.Database.Connection;
//transform arraylist into a list of DbCommands
ArrayListToCommandList(upLoadData);
conn.Open();
try
{
foreach (var cmd in cmdList)
{
//Console.WriteLine(cmd.CommandText);
cmd.ExecuteNonQuery();
}
}
catch (Exception e)
{
var result = e.Message;
MessageBox.Show(result);
}
}
}
//opens Excel file and reads worksheets to arraylist
private static ArrayList ReadFile(string fileName)
{
List<string> commands = new List<string>();
var xlApp = new Microsoft.Office.Interop.Excel.Application();
var wb = xlApp.Workbooks.Open(fileName, ReadOnly: true);
xlApp.Visible = false;
foreach (Worksheet ws in wb.Worksheets)
{
var r = ws.UsedRange;
var array = r.Value;
data.Add(array);
tableNames.Add(ws.Name);
}
wb.Close(SaveChanges: false);
xlApp.Quit();
return data;
}
//transforms arraylist to a list of DbCommands
private static void ArrayListToCommandList(ArrayList arrList)
{
List<DbCommand> result = new List<DbCommand>();
for (int tableAmount = 0; tableAmount < data.Count; tableAmount++)
{
ArrayToSqlCommands(arrList[tableAmount] as Array, tableNames[tableAmount]);
}
}
private static void ArrayToSqlCommands(Array arr, string tableName)
{
//Excel row which holds property names
int propertyRow = 1;
//First Excel row with values
int firstDataRow = 2;
string sql = "";
DbCommand cmd = conn.CreateCommand();
sql += "INSERT INTO " + tableName + "(";
//add column names to command text
for (int colIndex = 1; colIndex <= arr.GetUpperBound(1); colIndex++)
{
//get property name
sql += arr.GetValue(propertyRow, colIndex);
//add comma if not last column, otherwise close bracket
if (colIndex == arr.GetUpperBound(1))
{
sql += ") Values (";
}
else
{
sql += ", ";
}
}
//add value parameter names to command text
for (int colIndex = 1; colIndex <= arr.GetUpperBound(1); colIndex++)
{
//get property name
sql += "@" + arr.GetValue(propertyRow, colIndex);
//add comma if not last column, otherwise close bracket
if (colIndex == arr.GetUpperBound(1))
{
sql += ");";
}
else
{
sql += ", ";
}
}
//add data elements as command parameter values
for (int rowIndex = firstDataRow; rowIndex <= arr.GetUpperBound(0); rowIndex++)
{
//initialize command
cmd = conn.CreateCommand();
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
for (int colIndex = 1; colIndex <= arr.GetUpperBound(1); colIndex++)
{
//set parameter values
DbParameter param = null;
param = cmd.CreateParameter();
param.ParameterName = "@" + (string)arr.GetValue(propertyRow, colIndex);
cmd.Parameters.Add(param);
cmd.Parameters[param.ParameterName].Value = arr.GetValue(rowIndex, colIndex);
}
//add command to command list
cmdList.Add(cmd);
}
}
}