sqlBUlkCopy用法4

来源：互联网发布：js rows 编辑：程序博客网时间：2024/05/18 06:20

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();
                                }
                            }

                        }
                    }
                }

}

}
}
}