将Excel数据上传到SQL Server错误

user2345661 发表于 Dev

用户2345661

这是我用来尝试将数据上传到SQL Server数据库的代码。

它已经运行了几次，但是现在在“浏览”文本框中显示未选择文件。

我尝试调试并检查

 strFilepPath = DirectoryPath + FileUpload1.FileName;

实际上具有所选文件的路径。

在调试代码不会运行过去 sqlBulk.WriteToServer(ds.Tables[0]);

protected void btnImport_Click(object sender, EventArgs e)
{
    string strFilepPath;

    // Create a Connection String
    string CS = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

    // SqlConnection is in System.Data.SqlClient namespace
    using (SqlConnection con = new SqlConnection(CS))
    {
        // SqlCommand to Drop and Re-Create Table
        string DropString = "USE Sit302GroupProject IF OBJECT_ID('students', 'U') IS NOT NULL DROP TABLE students;";

        string CreateString = "USE Sit302GroupProject " +
             "CREATE TABLE   students " +
            "(student_id   int Not Null, " +
             "first_name   nvarchar (255) Not Null, " +
             "surname   nvarchar (255) Not Null, " +
             "email   nvarchar (255) Not Null, " +
             "campus   nvarchar (255) Not Null, " +
             "enrollment_status   nvarchar (255) Not Null, " +
             "project_type   nvarchar (255) Not Null, " +
             "group_id   nvarchar (50) Null) ";

        try
        {
            SqlCommand DropCommand = new SqlCommand(DropString, con);
            SqlCommand CreateCommand = new SqlCommand(CreateString, con);

            con.Open();

            CreateCommand.ExecuteNonQuery();
            DropCommand.ExecuteNonQuery();

            con.Close();
        }
        catch (Exception ex1)
        {
            ScriptManager.RegisterStartupScript(Page, GetType(), "script1",
            "alert('error occured: " + ex1.Message.ToString() + "');", true);
        }
    }

    DataSet ds = new DataSet();
    string strConnection = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

    if (FileUpload1.HasFile)
    {
        try
        {
            FileInfo fi = new FileInfo(FileUpload1.PostedFile.FileName);
            string ext = fi.Extension;

            if (ext == ".xls" || ext == ".xlsx")
            {
                string filename = Path.GetFullPath(FileUpload1.PostedFile.FileName);
                string DirectoryPath = Server.MapPath("~/UploadExcelFile//");
                strFilepPath = DirectoryPath + FileUpload1.FileName;

                Directory.CreateDirectory(DirectoryPath);

                FileUpload1.SaveAs(strFilepPath);

                string strConn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strFilepPath + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\"";

                OleDbConnection conn = new OleDbConnection(strConn);
                conn.Open();

                OleDbCommand cmd = new OleDbCommand("SELECT * FROM [Sheet1$]", conn);

                OleDbDataAdapter da = new OleDbDataAdapter(cmd);
                da.Fill(ds);

                SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection, SqlBulkCopyOptions.KeepIdentity);
                sqlBulk.DestinationTableName = "students";
                sqlBulk.WriteToServer(ds.Tables[0]);

                conn.Close();
                sqlBulk.Close();

                Array.ForEach(Directory.GetFiles(@DirectoryPath), File.Delete);

                ScriptManager.RegisterStartupScript(Page, GetType(), "script1",
                "alert('Excel file successfully imported into DB');", true);

                return;
            }
            else
            {
                ScriptManager.RegisterStartupScript(Page, GetType(), "script1",
                "alert('Please upload excel file only');", true);
                return;
            }
        }
        catch (Exception ex)
        {
            DeleteExcelFile(FileUpload1.FileName);
            ScriptManager.RegisterStartupScript(Page, GetType(), "script1",
              "alert('error occured: " + ex.Message.ToString() + "');", true);
            return;
        }
    }
    else
    {
        ScriptManager.RegisterStartupScript(Page, GetType(), "script1",
        "alert('Please upload excel file');", true);
        return;
    }
}

protected void DeleteExcelFile(string Name)
{
    if (Directory.Exists(Request.PhysicalApplicationPath +
    "UploadExcelFile\\"))
    {
        string[] logList = Directory.GetFiles(Request.PhysicalApplicationPath
        + "UploadExcelFile\\", "*.xls");
        foreach (string log in logList)
        {
            FileInfo logInfo = new FileInfo(log);
            string logInfoName = logInfo.Name.Substring(0,
            logInfo.Name.LastIndexOf('.'));
            if (logInfoName.Length >= Name.Length)
            {
                if (Name.Equals(logInfoName.Substring(0, Name.Length)))
                {
                    logInfo.Delete();
                }
            }
        }
    }
}

marc_s

首先创建表似乎很奇怪，在下一行中，您再次将其删除...

SqlCommand DropCommand = new SqlCommand(DropString, con);
SqlCommand CreateCommand = new SqlCommand(CreateString, con);

con.Open();

CreateCommand.ExecuteNonQuery();  // creates "dbo.students"
DropCommand.ExecuteNonQuery();    // drops that same "dbo.students" right away ....

con.Close();

因此，最后-您没有一个可批量插入数据的表...

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。