62,041
社区成员
发帖
与我相关
我的任务
分享
#region 导数据
private void ImportData()
{
t.Stop();
this.BeginInvoke(new ChangeBtn(BtnEnable), button1, false);
string RootPath = txtFolderPath.Text;
if (!Directory.Exists(RootPath))
{
//MessageBox.Show(this,"文件夹不存在!");
this.BeginInvoke(new ShowMsg(AlertMsg));
this.BeginInvoke(new ChangeBtn(BtnEnable), button2, true);
return;
}
string[] ExcelPath = Directory.GetFiles(RootPath, txtFileName.Text);
if (ExcelPath != null && ExcelPath.Length > 0)
{
for (int i = 0; i < ExcelPath.Length; i++)
{
string oconnString = @"Provider=Microsoft.Jet.OLEDB.4.0; Persist Security Info=False;Data Source=" + ExcelPath[i] + "; Extended Properties='Excel 8.0;IMEX=1'";
OleDbConnection oconn = new OleDbConnection(oconnString);
oconn.Open();
OleDbCommand ocmd = new OleDbCommand("select * from [Sheet1$]", oconn);
DataTable odt = new DataTable();
OleDbDataReader odr = ocmd.ExecuteReader();
odt.Load(odr);
SqlBulkCopyColumnMapping[] mapcol = new SqlBulkCopyColumnMapping[]
{
new SqlBulkCopyColumnMapping("Name", "AdminName"),
new SqlBulkCopyColumnMapping("Password", "AdminPwd"),
new SqlBulkCopyColumnMapping("Level", "AdminLevel")
};
ImporyBigData(odt, mapcol, "Manage");
ocmd.Dispose();
oconn.Close();
oconn.Dispose();
string oldFilePath = txtOldFileFolder.Text.Trim();
if (!Directory.Exists(oldFilePath))
{
Directory.CreateDirectory(oldFilePath);
}
File.Move(ExcelPath[i], oldFilePath + Path.GetFileName(ExcelPath[i]));
}
//MessageBox.Show("OK!");
}
t.Start();
this.BeginInvoke(new ChangeBtn(BtnEnable), button1, true);
}
#endregion
#region 导入大量数据方法
private void ImporyBigData(DataTable dt, SqlBulkCopyColumnMapping[] mapcol, string tabName)
{
SqlConnection SqlConn = null;
using (SqlConn = OpenConn())
{
using (SqlTransaction tran = SqlConn.BeginTransaction())
{
SqlBulkCopy bulkCopyOrders = new SqlBulkCopy(SqlConn, SqlBulkCopyOptions.Default, tran);
bulkCopyOrders.DestinationTableName = tabName;
for (int i = 0; i < mapcol.Length; i++)
{
bulkCopyOrders.ColumnMappings.Add(mapcol[i]);
}
bulkCopyOrders.BulkCopyTimeout = 1000;
try
{
bulkCopyOrders.WriteToServer(dt);
tran.Commit();
}
catch (Exception ex)
{
tran.Rollback();
throw ex;
}
finally
{
dt.Dispose();
bulkCopyOrders.Close();
if (SqlConn.State == ConnectionState.Open)
{
SqlConn.Close();
SqlConn.Dispose();
}
tran.Dispose();
}
}
}
}
#endregion
/// <summary>
/// 设置工作内存,降低内存占用
/// </summary>
/// <param name="maxWorkingSet">内存中允许的进程的最大工作集大小(以字节为单位)</param>
public static void SetWorkingSet(int maxWorkingSet)
{
System.Diagnostics.Process.GetCurrentProcess().MaxWorkingSet = (IntPtr)maxWorkingSet;
}
#endregion
string[] ExcelPath = Directory.GetFiles(RootPath, txtFileName.Text);
if (ExcelPath != null && ExcelPath.Length > 0)
{
for (int i = 0; i < ExcelPath.Length; i++)
{
System.Threading.Thread.Sleep(20);//休眠一下再继续干活儿