private static void RunBulkCopy(SchemaType schemaType, bool keepNulls, bool keepKey, IDataReader reader)
{
System.Diagnostics.Stopwatch sw = new System.Diagnostics.Stopwatch();
sw.Start();
SqlCeBulkCopyOptions options = new SqlCeBulkCopyOptions();
if (keepNulls)
{
options = options |= SqlCeBulkCopyOptions.KeepNulls;
}
if (keepKey)
{
options = options |= SqlCeBulkCopyOptions.KeepIdentity;
}
using (SqlCeBulkCopy bc = new SqlCeBulkCopy(connectionString, options))
{
bc.DestinationTableName = "tblDoctor";
if (schemaType == SchemaType.DataReaderTestMappedCollectionKeepOriginal)
{
bc.ColumnMappings.Add(new SqlCeBulkCopyColumnMapping("Active", "Active"));
bc.ColumnMappings.Add(new SqlCeBulkCopyColumnMapping("MiddleName", "MiddleName"));
bc.ColumnMappings.Add(new SqlCeBulkCopyColumnMapping("FirstName", "FirstName"));
bc.ColumnMappings.Add(new SqlCeBulkCopyColumnMapping(2, "DoctorId"));
bc.ColumnMappings.Add(new SqlCeBulkCopyColumnMapping(3, 3)); //LastName
bc.ColumnMappings.Add(new SqlCeBulkCopyColumnMapping(4, 4));
bc.ColumnMappings.Add(new SqlCeBulkCopyColumnMapping("SpecialityId_FK", "SpecialityId_FK"));
bc.ColumnMappings.Add(new SqlCeBulkCopyColumnMapping("LastUpdated", "LastUpdated"));
}
bc.WriteToServer(reader);
}
sw.Stop();
System.Diagnostics.Debug.WriteLine(string.Format(CultureInfo.InvariantCulture, "{0} rows copied in {1} ms, Constrained: {2}, Keep Nulls: {3}", "??", sw.ElapsedMilliseconds, schemaType, keepNulls));
}
private static void RunBulkCopy(SchemaType schemaType, bool keepNulls, List <Shippers> testList)
{
SqlCeBulkCopyOptions options = new SqlCeBulkCopyOptions();
switch (schemaType)
{
case SchemaType.FullNoIdentity:
break;
case SchemaType.NoConstraints:
break;
case SchemaType.FullConstraints:
options = SqlCeBulkCopyOptions.KeepIdentity;
break;
default:
break;
}
if (keepNulls)
{
options = options |= SqlCeBulkCopyOptions.KeepNulls;
}
System.Diagnostics.Stopwatch sw = new System.Diagnostics.Stopwatch();
sw.Start();
using (SqlCeBulkCopy bc = new SqlCeBulkCopy(connectionString, options))
{
bc.DestinationTableName = "Shippers";
bc.WriteToServer(testList);
}
sw.Stop();
System.Diagnostics.Debug.WriteLine(string.Format(CultureInfo.InvariantCulture, "{0} rows copied in {1} ms, Constrained: {2}, Keep Nulls: {3}", testList.Count, sw.ElapsedMilliseconds, schemaType, keepNulls));
}
public void Test_DisableConstraints_Enabled_And_Invalid_Data()
{
var connString = GetChinookConnectionString();
using (var bc = new SqlCeBulkCopy(connString, SqlCeBulkCopyOptions.DisableConstraints))
{
bc.DestinationTableName = "InvoiceLine";
var dt = GetTestInvoiceLineTable(connString);
dt.Rows[0][1] = Int32.MaxValue - 10000;
bc.WriteToServer(dt);
}
}
public void Test_DisableConstraints_Disabled_Timing()
{
var connString = GetChinookConnectionString();
using (var bc = new SqlCeBulkCopy(connString))
{
bc.DestinationTableName = "InvoiceLine";
var sw = new Stopwatch();
sw.Start();
bc.WriteToServer(GetTestInvoiceLineTable(connString));
sw.Stop();
Debug.WriteLine("With constraints: " + sw.ElapsedMilliseconds);
}
}
public void Test_DisableConstraints_Enabled_Timing()
{
var connString = GetChinookConnectionString();
using (var bc = new SqlCeBulkCopy(connString, SqlCeBulkCopyOptions.DisableConstraints))
{
bc.DestinationTableName = "InvoiceLine";
var sw = new Stopwatch();
sw.Start();
bc.WriteToServer(GetTestInvoiceLineTable(connString));
sw.Stop();
Debug.WriteLine("Without constraints: " + sw.ElapsedMilliseconds);
var fkRepo = new ForeignKeyRepository(connString, "InvoiceLine");
var savedConstraints = fkRepo.GetConstraints();
Assert.IsTrue(savedConstraints.Count == 2);
}
}
public void importDataFromExcel()
{
string sSQLTable = "table1";
//this works
string myExcelDataQuery = "SELECT Date, PID, SlopeDWN, SlopeUP, Number, Equip, BestHFR, FocusPos FROM [Sheet1$]";
try
{
if (MainWindow.ImportPath == null)
{
MainWindow m = new MainWindow();
// DialogResult result = openFileDialog1.ShowDialog();
// ImportPath = openFileDialog1.FileName.ToString();
// ImportPath = Filename;
m.TB34 = Filename;
// textBox34.Text = ImportPath.ToString();
}
string sSqlConnectionString = conString;
string sExcelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + MainWindow.ImportPath + @";Extended Properties=""Excel 8.0;HDR=YES;IMEX=1""";
// string sExcelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\Users\kevin\Documents\scopefocusData.xls;Extended Properties=""Excel 8.0;HDR=YES;IMEX=1""";
string sClearSQL = "DELETE FROM " + sSQLTable;
SqlCeConnection SqlConn = new SqlCeConnection(conString);
SqlCeCommand SqlCmd = new SqlCeCommand(sClearSQL, SqlConn);
SqlConn.Open();
SqlCmd.ExecuteNonQuery();
SqlConn.Close();
OleDbConnection OleDbConn = new OleDbConnection(sExcelConnectionString);
OleDbCommand OleDbCmd = new OleDbCommand(myExcelDataQuery, OleDbConn);
OleDbConn.Open();
OleDbDataReader dr = OleDbCmd.ExecuteReader();
using (SqlCeBulkCopy bc = new SqlCeBulkCopy(conString))
{
bc.DestinationTableName = "table1";
bc.WriteToServer(dr);
}
OleDbConn.Close();
MessageBox.Show("Data Import Successful", "scopefocus");
}
catch
{
MessageBox.Show("Import Failed", "scopefocus");
}
}
private static void RunBulkCopy(SchemaType schemaType, bool keepNulls, DataTable testTable)
{
SqlCeBulkCopyOptions options = new SqlCeBulkCopyOptions();
switch (schemaType)
{
case SchemaType.FullNoIdentity:
break;
case SchemaType.NoConstraints:
break;
case SchemaType.FullConstraints:
options = SqlCeBulkCopyOptions.KeepIdentity;
break;
case SchemaType.FullConstraintsDuplicateRows:
options = SqlCeBulkCopyOptions.KeepIdentity;
break;
}
if (keepNulls)
{
options = options |= SqlCeBulkCopyOptions.KeepNulls;
}
if (schemaType == SchemaType.FullConstraintsDuplicateRows)
{
options = options |= SqlCeBulkCopyOptions.IgnoreDuplicateErrors;
}
var sw = new Stopwatch();
sw.Start();
using (SqlCeBulkCopy bc = new SqlCeBulkCopy(connectionString, options))
{
bc.NotifyAfter = 1000;
bc.RowsCopied += new EventHandler <SqlCeRowsCopiedEventArgs>(bc_RowsCopied);
bc.DestinationTableName = "Shippers";
bc.WriteToServer(testTable);
}
sw.Stop();
System.Diagnostics.Debug.WriteLine(string.Format(CultureInfo.InvariantCulture, "{0} rows copied in {1} ms, Constrained: {2}, Keep Nulls: {3}", testTable.Rows.Count, sw.ElapsedMilliseconds, schemaType, keepNulls));
}
public void TestBinary16()
{
var row = new Dictionary <string, object> {
{ "id", generateId() },
{ "name", "test2" },
{ "type", 1 },
{ "email", "*****@*****.**" },
{ "mobile", "5368798797" },
{ "phone", "9879654321" },
{ "city", "London2" },
{ "town", "Town2" },
{ "address", "test street" }
};
var testData = new DataTable();
foreach (var h in row.Keys)
{
testData.Columns.Add(h);
}
testData.Rows.Add(row.Values.ToArray());
using (var bc = new SqlCeBulkCopy(String.Format("Data Source = {0}; Flush Interval = 3", "testdb.sdf")))
{
bc.DestinationTableName = "customer";
/*bc.ColumnMappings.Add("id", "id");
* bc.ColumnMappings.Add("name", "name");
* bc.ColumnMappings.Add("type", "type");
* bc.ColumnMappings.Add("email", "email");
* bc.ColumnMappings.Add("mobile", "mobile");
* bc.ColumnMappings.Add("phone", "phone");
* bc.ColumnMappings.Add("city", "city");
* bc.ColumnMappings.Add("address", "address");*/
bc.WriteToServer(testData);
}
}
public void importDataFromExcel()
{
string sSQLTable = "table1";
//this works
string myExcelDataQuery = "SELECT Date, PID, SlopeDWN, SlopeUP, Number, Equip, BestHFR, FocusPos FROM [Sheet1$]";
try
{
if (MainWindow.ImportPath == null)
{
MainWindow m = new MainWindow();
// DialogResult result = openFileDialog1.ShowDialog();
// ImportPath = openFileDialog1.FileName.ToString();
// ImportPath = Filename;
m.TB34 = Filename;
// textBox34.Text = ImportPath.ToString();
}
string sSqlConnectionString = conString;
string sExcelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +MainWindow.ImportPath + @";Extended Properties=""Excel 8.0;HDR=YES;IMEX=1""";
// string sExcelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\Users\kevin\Documents\scopefocusData.xls;Extended Properties=""Excel 8.0;HDR=YES;IMEX=1""";
string sClearSQL = "DELETE FROM " + sSQLTable;
SqlCeConnection SqlConn = new SqlCeConnection(conString);
SqlCeCommand SqlCmd = new SqlCeCommand(sClearSQL, SqlConn);
SqlConn.Open();
SqlCmd.ExecuteNonQuery();
SqlConn.Close();
OleDbConnection OleDbConn = new OleDbConnection(sExcelConnectionString);
OleDbCommand OleDbCmd = new OleDbCommand(myExcelDataQuery, OleDbConn);
OleDbConn.Open();
OleDbDataReader dr = OleDbCmd.ExecuteReader();
using (SqlCeBulkCopy bc = new SqlCeBulkCopy(conString))
{
bc.DestinationTableName = "table1";
bc.WriteToServer(dr);
}
OleDbConn.Close();
MessageBox.Show("Data Import Successful", "scopefocus");
}
catch
{
MessageBox.Show("Import Failed", "scopefocus");
}
}
////************ 7-25-14 try this......
//public static string SQLGetType(object type, int columnSize, int numericPrecision, int numericScale)
//{
// switch (type.ToString())
// {
// case "System.Byte[]":
// return "VARBINARY(MAX)";
// case "System.Boolean":
// return "BIT";
// case "System.DateTime":
// return "DATETIME";
// case "System.DateTimeOffset":
// return "DATETIMEOFFSET";
// case "System.Decimal":
// if (numericPrecision != -1 && numericScale != -1)
// return "DECIMAL(" + numericPrecision + "," + numericScale + ")";
// else
// return "DECIMAL";
// case "System.Double":
// return "FLOAT";
// case "System.Single":
// return "REAL";
// case "System.Int64":
// return "BIGINT";
// case "System.Int32":
// return "INT";
// case "System.Int16":
// return "SMALLINT";
// case "System.String":
// return "NVARCHAR(" + ((columnSize == -1 || columnSize > 8000) ? "MAX" : columnSize.ToString()) + ")";
// case "System.Byte":
// return "TINYINT";
// case "System.Guid":
// return "UNIQUEIDENTIFIER";
// default:
// throw new Exception(type.ToString() + " not implemented.");
// }
//}
//// Overload based on row from schema table
//public static string SQLGetType(DataRow schemaRow)
//{
// int numericPrecision;
// int numericScale;
// if (!int.TryParse(schemaRow["NumericPrecision"].ToString(), out numericPrecision))
// {
// numericPrecision = -1;
// }
// if (!int.TryParse(schemaRow["NumericScale"].ToString(), out numericScale))
// {
// numericScale = -1;
// }
// return SQLGetType(schemaRow["DataType"],
// int.Parse(schemaRow["ColumnSize"].ToString()),
// numericPrecision,
// numericScale);
//}
//// Overload based on DataColumn from DataTable type
//public static string SQLGetType(DataColumn column)
//{
// return SQLGetType(column.DataType, column.MaxLength, -1, -1);
//}
//public static string GetCreateFromDataTableSQL(string tableName, DataTable table)
//{
// string sql = "CREATE TABLE [" + tableName + "] (\n";
// // columns
// foreach (DataColumn column in table.Columns)
// {
// sql += "[" + column.ColumnName + "] " + SQLGetType(column) + ",\n";
// }
// sql = sql.TrimEnd(new char[] { ',', '\n' }) + "\n";
// // primary keys
// if (table.PrimaryKey.Length > 0)
// {
// sql += "CONSTRAINT [PK_" + tableName + "] PRIMARY KEY CLUSTERED (";
// foreach (DataColumn column in table.PrimaryKey)
// {
// sql += "[" + column.ColumnName + "],";
// }
// sql = sql.TrimEnd(new char[] { ',' }) + "))\n";
// }
// return sql;
//}
//public static bool TableExists(this SqlCeConnection connection, string tableName)
//{
// if (tableName == null) throw new ArgumentNullException("tableName");
// if (string.IsNullOrWhiteSpace(tableName)) throw new ArgumentException("Invalid table name");
// if (connection == null) throw new ArgumentNullException("connection");
// if (connection.State != ConnectionState.Open)
// {
// throw new InvalidOperationException("TableExists requires an open and available Connection. The connection's current state is " + connection.State);
// }
// using (SqlCeCommand command = connection.CreateCommand())
// {
// command.CommandType = CommandType.Text;
// command.CommandText = "SELECT 1 FROM Information_Schema.Tables WHERE TABLE_NAME = @tableName";
// command.Parameters.AddWithValue("tableName", tableName);
// object result = command.ExecuteScalar();
// return result != null;
// }
//}
// *** end 7-25-14 try
public void importDataFromExcel()
{
string sSQLTable = "table1";
//this works
string myExcelDataQuery = "SELECT Date, PID, SlopeDWN, SlopeUP, Number, Equip, BestHFR, FocusPos FROM [Sheet1$]";
try
{
if (_importPath == null)
{
DialogResult result = openFileDialog1.ShowDialog();
_importPath = openFileDialog1.FileName.ToString();
// textBox34.Clear();
textBox34.Text = _importPath.ToString();
if (_importPath == "")
return;
}
string sSqlConnectionString = conString;
string sExcelConnectionString = "provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + _importPath + "';Extended Properties=Excel 8.0;"; //this works for VS 2013 and .xls. for .xlsx may need if .xlsx then line below
// see http://www.codeproject.com/Questions/227945/Import-Excel-data-in-textbox-using-oledb-conn-in-C
string sClearSQL = "DELETE FROM " + sSQLTable;
// string SQLcreate = "CREATE TABLE table1 (Date datetime, PID int, SlopeDWN float(8,15), SlopeUP float(8,15), Number int, Equip nvarchar(100), BestHFR int, FocusPos int)";
// string sqlExists = "SELECT 1 FROM Information_Schema.Tables WHERE TABLE_NAME = @table1";
SqlCeConnection SqlConn = new SqlCeConnection(conString);
SqlCeCommand SqlCmd = new SqlCeCommand(sClearSQL, SqlConn);
SqlConn.Open();
SqlCmd.ExecuteNonQuery();
SqlConn.Close();
if (Path.GetExtension(_importPath.Substring(1)) == ".txt")
{
string filepath = _importPath;
StreamReader sr = new StreamReader(filepath);
string line = sr.ReadLine();
string[] value = line.Split(',');
DataTable dt = new DataTable();
DataRow row;
foreach (string dc in value)
{
dt.Columns.Add(new DataColumn(dc));
}
using (SqlCeConnection con = new SqlCeConnection(conString))
{
con.Open();
//SqlCeCommand SqlCmd3 = new SqlCeCommand(sqlExists, con);
//if (SqlCmd3.ExecuteNonQuery() == 0)
//{
while (!sr.EndOfStream)
{
value = sr.ReadLine().Split(',');
if (value.Length == dt.Columns.Count)
{
row = dt.NewRow();
row.ItemArray = value;
dt.Rows.Add(row);
DateTime d = Convert.ToDateTime(row.ItemArray[0]);
int p = Convert.ToInt16(row.ItemArray[1]);
// int num2 = _apexHFR;
// int num4 = _posminHFR;
float down = Convert.ToSingle(row.ItemArray[2]);//********1-23-15 these looked backwards this line was up and next WAS down
float up = Convert.ToSingle(row.ItemArray[3]);
string test = row.ItemArray[5].ToString();
int hfrtest = Convert.ToInt16(row.ItemArray[6]);
int testintpos = Convert.ToInt32(row.ItemArray[7]);
using (SqlCeCommand com = new SqlCeCommand("INSERT INTO table1 (Date, PID, SlopeDWN, SlopeUP, Number, Equip, BestHFR, FocusPos) VALUES (@Date, @PID, @SlopeDWN, @SlopeUP, @Number, @equip, @BestHFR, @FocusPos)", con))
{
com.Parameters.AddWithValue("@Date", d);
com.Parameters.AddWithValue("@PID", p);
com.Parameters.AddWithValue("@SlopeDWN", down);
com.Parameters.AddWithValue("@SlopeUP", up);
com.Parameters.AddWithValue("@Number", rows + 1);
com.Parameters.AddWithValue("@equip", test);
com.Parameters.AddWithValue("@BestHFR", hfrtest);
com.Parameters.AddWithValue("@FocusPos", testintpos);
com.ExecuteNonQuery();
rows++;
}
}
// }
}
//else
// MessageBox.Show("sql datatable does not exist, try reinstalling", "scopefocus");
con.Close();
}
}
else
{
SqlConn.Open();
SqlCmd.ExecuteNonQuery();
SqlConn.Close();
OleDbConnection OleDbConn = new OleDbConnection(sExcelConnectionString);
OleDbCommand OleDbCmd = new OleDbCommand(myExcelDataQuery, OleDbConn);
OleDbConn.Open();
OleDbDataReader dr = OleDbCmd.ExecuteReader();
using (SqlCeBulkCopy bc = new SqlCeBulkCopy(conString))
{
bc.DestinationTableName = "table1";
bc.WriteToServer(dr);
}
OleDbConn.Close();
MessageBox.Show("Data Import Successful", "scopefocus");
}
}
catch
{
MessageBox.Show("Import Failed", "scopefocus");
return;
}
}