using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data .Odbc ;
using System.Data.OleDb ;
using System.Data .SqlClient ;
public partial class Default4 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Button1_Click(object sender, EventArgs e)
{
}
private void copy()
{
string nam = this.FileUpload1.PostedFile.ToString();
if (nam.Length != 0)
{
int i = nam.LastIndexOf("//");
string newnm = nam.Substring(i);
int j = newnm.LastIndexOf(".");
string filename = newnm.Substring(1, j - 1);
string vsFilePath = nam.Substring(0, i);
string wjlx = newnm.Substring(j, newnm.Length - j);
string constr = "";
string sqlstr = "";
string connectionString = "Data Source=(local); Initial Catalog=Text;User ID=sa;Password=sa; Persist Security Info=False";
if (wjlx.ToLower() == ".dbf")//dbf文件
{
constr = "Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=" + vsFilePath;
sqlstr = "select * from " + filename;
using (OdbcConnection sourceConnection = new OdbcConnection(constr))
{
sourceConnection.Open();
OdbcCommand ocomm = new OdbcCommand(sqlstr, sourceConnection);
OdbcDataReader reader = ocomm.ExecuteReader();
using (SqlConnection destinationConnection = new SqlConnection(connectionString))
{
destinationConnection.Open();
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connectionString))
{
//文件filename中的列与表DPM1中的列进行对应
//第一个参数为filename中的字段
//第二个参数为DPM1中的字段
bulkCopy.ColumnMappings.Add("odate", "odate1");
bulkCopy.ColumnMappings.Add("currencyid", "currencyid1");
bulkCopy.ColumnMappings.Add("branchid", "branchid1");
bulkCopy.ColumnMappings.Add("costid", "costid1");
//DPM1为目标表
bulkCopy.DestinationTableName = "DPM1";
try
{
// Write from the source to the destination.
bulkCopy.WriteToServer(reader);
}
catch (Exception ex)
{
Response.Write(ex.Message.ToString());
return;
}
finally
{
reader.Close();
}
}
}
}
}
else
{
if (wjlx.ToLower() == ".xls" || wjlx.ToLower() == ".txt")
{
if (wjlx.ToLower() == ".xls")//excel文件
{
constr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + vsFilePath + newnm + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";
sqlstr = "select * from [sheet1$]";
}
else//txt文件
{
constr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + vsFilePath + ";Extended Properties='text;HDR=Yes;FMT=Delimited;IMEX=1;'";
sqlstr = "select * from " + filename + "#txt";
}
using (OleDbConnection sourceConnection = new OleDbConnection(constr))
{
sourceConnection.Open();
OleDbCommand ocomm = new OleDbCommand(sqlstr, sourceConnection);
OleDbDataReader reader = ocomm.ExecuteReader();
using (SqlConnection destinationConnection = new SqlConnection(connectionString))
{
destinationConnection.Open();
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connectionString))
{
//文件filename中的列与表DPM1中的列进行对应
//第一个参数为filename中的字段
//第二个参数为DPM1中的字段
bulkCopy.ColumnMappings.Add("odate", "odate1");
bulkCopy.ColumnMappings.Add("currencyid", "currencyid1");
bulkCopy.ColumnMappings.Add("branchid", "branchid1");
bulkCopy.ColumnMappings.Add("costid", "costid1");
//DPM1为目标表
bulkCopy.DestinationTableName = "DPM1";
try
{
bulkCopy.WriteToServer(reader);
}
catch (Exception ex)
{
Response.Write(ex.Message.ToString());
return;
}
finally
{
reader.Close();
}
}
}
}
}
}
}
}
}