I've been developing a C# WPF project with VS2015 using SQL Server Express LocalDb with Entity Framework. I have built a custom seeder for the database, that reads test data from an Excel file, that simply combines the Excel data into a command string, and this is inserted using context.Database.ExecuteSQLCommand.
Now, I was thinking of launching the project with SQL Server Compact Edition 4.0, but I find this command is not working anymore. Do I have to write my uploader again using SqlCeConnection and SqlCeCommand or am I missing something?
Also, from somewhere I have understood that with EF you can switch the SQL provider and the code would not need other changes. Am I in for more surprises down the road?
Example of the uploader command:
string cmd = "INSERT INTO Venues(Name, City, Telephone) Values ('X','Y','Z')"
context.Database.ExecuteSqlCommand(cmd);
The error:
There was an error parsing the query. [ Token line number = 2,Token line offset = 1,Token in error = INSERT ]
This is not just a testing issue, as I would want to include this uploader in the production version, too, for quick inserting of master data (e.g. employee list).
EDIT: Uploader code. If this can be done without resorting to raw SQL, that would be a good solution, too.
This loops through Excel sheets (named after entities) and columns (first row has property name) and rows 2->n (data). This handles the upload of basically any amount of data within Excel limitations. The point is that the code has no knowledge of the entities (might have been possible to parameterize DataContext too). Code might not be optimal, as I'm just a beginner, but has worked for me, except not with SQL CE. Editing to suit CE is not a big issue, but I wanted to ask for possibly better ways.
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;
}
}
There are two ways to use raw SQL queries I'd offer.
+=======+=======+===========+ | Name | City | Telephone | |===========================| | Adam | Addr1 | 111-11-11 | |-------|-------|-----------| | Peter | Addr2 | 222-22-22 | +-------+-------+-----------+
CREATE TABLE Venues
(
Id int identity primary key,
[Name] nvarchar(100) null,
City nvarchar(100) null,
Telephone nvarchar(100) null
);
Here we're interested in getting array from Excel sheet. As soon as we get it, we can safely close Excel. The code assumes file "Employees.xlsx" to be next to executable file.
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;
}
Now you can use one of the ways to generate query.
When using ExecuteSqlCommand, it's advisable to use parameterized queries to avoid errors. You can pass explicitly created SqlCeParameter or just pass a value.
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.");
}
}
If you want your code to be more generic, you can create method which would accept DbConnection. This will allow to pass either SqlConnection or SqlCeConnection. But the code becomes more verbose because we can't use constructors since these classes are abstract.
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.");
}
}
You can also use ordinal positions for parameters which eliminates creating parameters names and makes code much shorter:
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.");
}
}
P.S. I didn't check whether the underlying connection is opened, but it's a good idea to do so.