public static void ImportToSQL(string tableName)
{
OpenFileDialog ofd = new OpenFileDialog();
if (ofd.ShowDialog() == DialogResult.OK)
{
DataSet ds = new DataSet();
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + ofd.FileName + ";" +
"Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
string strExcel = "";
OleDbDataAdapter myCommand = null;
strExcel = string.Format("select * from [{0}$]", "Sheet1"); //Bicycle为excel中工作薄
myCommand = new OleDbDataAdapter(strExcel, strConn);
myCommand.Fill(ds, "Sheet1");
DataTable dt = ds.Tables["Sheet1"];
DbEntry.Context.UsingConnection(delegate()
{
IDbBulkCopy c = DbEntry.Context.GetDbBulkCopy();
c.BatchSize = 10;
c.DestinationTableName = tableName;
c.NotifyAfter = 10;
//c.SqlRowsCopied += new SqlRowsCopiedEventHandler(
// delegate(object sender, SqlRowsCopiedEventArgs e)
// {
// Console.WriteLine("{0} rows copied.", e.RowsCopied);
// });
c.WriteToServer(dt);
});
}
}
public void TestGetTheRightCopier()
{
IDbBulkCopy c = DbEntry.Provider.GetDbBulkCopy(false);
Assert.IsNotNull(c);
Assert.IsTrue(c is CommonBulkCopy);
}
public void TestCommonBulkCopy()
{
var sql = new SqlStatement("SELECT [Id],[Name] FROM [Books] ORDER BY [Id]");
var rcs = new List <long>();
DbEntry.Provider.ExecuteDataReader(sql, delegate(IDataReader dr)
{
DbEntry.NewConnection(delegate
{
IDbBulkCopy c = _sqlite.GetDbBulkCopy(false);
c.BatchSize = 2;
c.DestinationTableName = "test";
c.NotifyAfter = 3;
c.SqlRowsCopied += ((sender, e) => rcs.Add(e.RowsCopied));
c.WriteToServer(dr);
});
});
Assert.AreEqual(1, rcs.Count);
Assert.AreEqual(3, rcs[0]);
Assert.AreEqual(5, StaticRecorder.Messages.Count);
Assert.AreEqual("INSERT INTO [test] ([Id],[Name]) VALUES (@Id_0,@Name_1);\n<Text><30>(@Id_0=1:Int64,@Name_1=Diablo:String)", StaticRecorder.Messages[0]);
Assert.AreEqual("INSERT INTO [test] ([Id],[Name]) VALUES (@Id_0,@Name_1);\n<Text><30>(@Id_0=2:Int64,@Name_1=Beijing:String)", StaticRecorder.Messages[1]);
Assert.AreEqual("INSERT INTO [test] ([Id],[Name]) VALUES (@Id_0,@Name_1);\n<Text><30>(@Id_0=3:Int64,@Name_1=Shanghai:String)", StaticRecorder.Messages[2]);
Assert.AreEqual("INSERT INTO [test] ([Id],[Name]) VALUES (@Id_0,@Name_1);\n<Text><30>(@Id_0=4:Int64,@Name_1=Pal95:String)", StaticRecorder.Messages[3]);
Assert.AreEqual("INSERT INTO [test] ([Id],[Name]) VALUES (@Id_0,@Name_1);\n<Text><30>(@Id_0=5:Int64,@Name_1=Wow:String)", StaticRecorder.Messages[4]);
}
public void TestCommonBulkCopyAbort()
{
var sql = new SqlStatement("SELECT [Id],[Name] FROM [Books] ORDER BY [Id]");
DbEntry.Provider.ExecuteDataReader(sql, delegate(IDataReader dr)
{
DbEntry.NewConnection(delegate
{
IDbBulkCopy c = _sqlite.GetDbBulkCopy(false);
c.BatchSize = 2;
c.DestinationTableName = "test";
c.NotifyAfter = 3;
c.SqlRowsCopied += delegate(object sender, SqlRowsCopiedEventArgs e)
{
e.Abort = true;
};
c.WriteToServer(dr);
});
});
Assert.AreEqual(3, StaticRecorder.Messages.Count);
Assert.AreEqual("INSERT INTO [test] ([Id],[Name]) VALUES (@Id_0,@Name_1);\n<Text><30>(@Id_0=1:Int64,@Name_1=Diablo:String)", StaticRecorder.Messages[0]);
Assert.AreEqual("INSERT INTO [test] ([Id],[Name]) VALUES (@Id_0,@Name_1);\n<Text><30>(@Id_0=2:Int64,@Name_1=Beijing:String)", StaticRecorder.Messages[1]);
Assert.AreEqual("INSERT INTO [test] ([Id],[Name]) VALUES (@Id_0,@Name_1);\n<Text><30>(@Id_0=3:Int64,@Name_1=Shanghai:String)", StaticRecorder.Messages[2]);
}
public void TestGetTheRightCopier2()
{
var dc = new DataProvider("SqlServerMock");
DbEntry.NewTransaction(delegate
{
IDbBulkCopy c = dc.GetDbBulkCopy(false); // exception
Assert.IsNotNull(c);
Assert.IsTrue(c is SqlServerBulkCopy);
});
}
public void TestGetTheRightCopier3()
{
DbContext dc = new DbContext("SqlServer");
dc.NewTransaction(delegate()
{
IDbBulkCopy c = dc.GetDbBulkCopy();
Assert.IsNotNull(c);
Assert.IsTrue(c is SqlServerBulkCopy);
});
}
protected void BulkCopy(string sql, bool identityInsert)
{
Src.ExecuteDataReader(new SqlStatement(sql), dr =>
{
DbEntry.NewConnection(delegate
{
IDbBulkCopy c = Dest.GetDbBulkCopy(identityInsert);
c.BatchSize = _batchSize;
c.DestinationTableName = DestinationTableName;
c.NotifyAfter = _notifyAfter;
c.SqlRowsCopied += (sender, e) => Console.WriteLine("{0}: {1}", DestinationTableName, e.RowsCopied);
c.WriteToServer(dr);
});
});
Console.WriteLine("{0} ({1}) copied!", DestinationTableName, _times++);
}
public void TestBulkCopyWithNullValue()
{
var sql = new SqlStatement("SELECT [Id],[Name],[MyInt],[MyBool] FROM [NullTest] ORDER BY [Id]");
DbEntry.Provider.ExecuteDataReader(sql, delegate(IDataReader dr)
{
DbEntry.NewConnection(delegate
{
IDbBulkCopy c = _sqlite.GetDbBulkCopy(false);
c.BatchSize = 2;
c.DestinationTableName = "test";
c.NotifyAfter = 3;
c.WriteToServer(dr);
});
});
Assert.AreEqual(4, StaticRecorder.Messages.Count);
// TODO: why null value is single type ?
Assert.AreEqual("INSERT INTO [test] ([Id],[Name],[MyInt],[MyBool]) VALUES (@Id_0,@Name_1,@MyInt_2,@MyBool_3);\n<Text><30>(@Id_0=1:Int64,@Name_1=tom:String,@MyInt_2=<NULL>:Single,@MyBool_3=True:Boolean)", StaticRecorder.Messages[0]);
Assert.AreEqual("INSERT INTO [test] ([Id],[Name],[MyInt],[MyBool]) VALUES (@Id_0,@Name_1,@MyInt_2,@MyBool_3);\n<Text><30>(@Id_0=2:Int64,@Name_1=<NULL>:Single,@MyInt_2=1:Int32,@MyBool_3=False:Boolean)", StaticRecorder.Messages[1]);
Assert.AreEqual("INSERT INTO [test] ([Id],[Name],[MyInt],[MyBool]) VALUES (@Id_0,@Name_1,@MyInt_2,@MyBool_3);\n<Text><30>(@Id_0=3:Int64,@Name_1=<NULL>:Single,@MyInt_2=<NULL>:Single,@MyBool_3=<NULL>:Single)", StaticRecorder.Messages[2]);
Assert.AreEqual("INSERT INTO [test] ([Id],[Name],[MyInt],[MyBool]) VALUES (@Id_0,@Name_1,@MyInt_2,@MyBool_3);\n<Text><30>(@Id_0=4:Int64,@Name_1=tom:String,@MyInt_2=1:Int32,@MyBool_3=True:Boolean)", StaticRecorder.Messages[3]);
}
public void TestSqlServeerBulkCopy()
{
DbContext dc = new DbContext("SqlServer");
SqlStatement sql = new SqlStatement("select [Id],[Name] from [Books] order by [Id]");
List <long> rcs = new List <long>();
DbEntry.Context.ExecuteDataReader(sql, delegate(IDataReader dr)
{
dc.NewConnection(delegate()
{
IDbBulkCopy c = dc.GetDbBulkCopy();
c.BatchSize = 2;
c.DestinationTableName = "test";
c.NotifyAfter = 3;
c.SqlRowsCopied += new SqlRowsCopiedEventHandler(delegate(object sender, SqlRowsCopiedEventArgs e)
{
rcs.Add(e.RowsCopied);
});
c.WriteToServer(dr);
});
});
Assert.AreEqual(1, rcs.Count);
Assert.AreEqual(3, rcs[0]);
}
