SqlBulkCopy che inserisce null invece di Double

asp.net c# excel sqlbulkcopy sql-server

Domanda

Sto cercando di importare i file excel sul mio sito web e poi salvare il suo contenuto nel server sql il mio problema con sqlbulkcopy quando riempie il database di alcune colonne riempite con valori errati che è NULL! mentre la colonna ha valori doppi non nulli!

ecco il codice:

            void ImporttoDatatable()
{

    try
    {
        if (FileUpload3.HasFile)
        {
            string FileName = FileUpload3.FileName;
            string path = Path.Combine(Server.MapPath("~/ImportDocument"), Guid.NewGuid().ToString() + Path.GetExtension(FileUpload3.PostedFile.FileName));


            FileUpload3.PostedFile.SaveAs(path);




            using (OleDbConnection OleDbcon = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + "; Extended Properties = \"Excel 8.0;HDR=Yes;IMEX=2\" "))
            {


                conn.Open();
                OleDbcon.Open();
                OleDbCommand command = new OleDbCommand("Select [IdOftable], [Time],[InstrumentLeftHand],[LeftSwitch],[LeftKnob],[ForceFeedbackLeftHand],[CumTimeLeftForceOverThreshold],[CumTimeLeftForceOver2xThreshold],[TranslationLeft_x],[TranslationLeft_y],[TranslationLeft_z],[quatLeft_x],[quatLeft_y],[quatLeft_z],[quatLeft_w],[InstrumentRightHand],[RightSwitch],[RightKnob],[ForceFeedbackRightHand],[CumTimeRightForceOverThreshold],[CumTimeRightForceOver2xThreshold],[TranslationRight_x],[TranslationRight_y],[TranslationRight_z],[quatRight_x],[quatRight_y],[quatRight_z],[quatRight_w],[BloodEmittedFrame],[BloodCurrentFrame],[TotalBloodEmitted],[TotalWhiteFibreCut],[TotalRedFibreCut],[Volume0_Brain],[Volume1_Tumor],[Volume2_Tumor] from [Sheet1$]", OleDbcon);

                //OleDbDataAdapter objAdapter1 = new OleDbDataAdapter(command);
                 DbDataReader dr = command.ExecuteReader();
                using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conn))
                {
                bulkCopy.DestinationTableName =  "MyExcel";
                    try
                    {
                        bulkCopy.BulkCopyTimeout = 400;
                        bulkCopy.WriteToServer(dr);
                        bulkCopy.BatchSize = 16000;
                    }
                    catch (Exception ex)
                    {
                        Response.Write(ex.ToString());
                    }
                    finally
                    {
                        dr.Close();
                    }
                    OleDbcon.Close();
                    bulkCopy.Close();

                }
            }
        } lblmessage.Text = "The File Succssesfully Imported ";
    }
    catch (Exception ex)
    {
        Response.Write(ex.ToString());
    }
}

e questa è la definizione della tabella sql:

        CREATE TABLE [dbo].[MyExcel] (
[IdOftable]                        NVARCHAR (50) NOT NULL,
[Time]                             FLOAT (53)    NULL,
[InstrumentLeftHand]               NVARCHAR (50) NULL,
[LeftSwitch]                       FLOAT (53)    NULL,
[LeftKnob]                         FLOAT (53)    NULL,
[ForceFeedbackLeftHand]            FLOAT (53)    NULL,
[CumTimeLeftForceOverThreshold]    FLOAT (53)    NULL,
[CumTimeLeftForceOver2xThreshold]  FLOAT (53)    NULL,
[TranslationLeft_x]                FLOAT (53)    NULL,
[TranslationLeft_y]                FLOAT (53)    NULL,
[TranslationLeft_z]                FLOAT (53)    NULL,
[quatLeft_x]                       FLOAT (53)    NULL,
[quatLeft_y]                       FLOAT (53)    NULL,
[quatLeft_z]                       FLOAT (53)    NULL,
[quatLeft_w]                       FLOAT (53)    NULL,
[InstrumentRightHand]              NVARCHAR (50) NULL,
[RightSwitch]                      FLOAT (53)    NULL,
[RightKnob]                        FLOAT (53)    NULL,
[ForceFeedbackRightHand]           FLOAT (53)    NULL,
[CumTimeRightForceOverThreshold]   FLOAT (53)    NULL,
[CumTimeRightForceOver2xThreshold] FLOAT (53)    NULL,
[TranslationRight_x]               FLOAT (53)    NULL,
[TranslationRight_y]               FLOAT (53)    NULL,
[TranslationRight_z]               FLOAT (53)    NULL,
[quatRight_x]                      FLOAT (53)    NULL,
[quatRight_y]                      FLOAT (53)    NULL,
[quatRight_z]                      FLOAT (53)    NULL,
[quatRight_w]                      FLOAT (53)    NULL,
[BloodEmittedFrame]                NVARCHAR (50) NULL,
[BloodCurrentFrame]                FLOAT (53)    NULL,
[TotalBloodEmitted]                FLOAT (53)    NULL,
[TotalWhiteFibreCut]               FLOAT (53)    NULL,
[TotalRedFibreCut]                 FLOAT (53)    NULL,
[Volume0_Brain]                    FLOAT (53)    NULL,
[Volume1_Tumor]                    FLOAT (53)    NULL,
[Volume2_Tumor ]                   FLOAT (53)    NULL,
PRIMARY KEY CLUSTERED ([IdOftable] ASC)
 );

Non ci sono eccezioni quando ho eseguito il codice ma quando ho controllato i dati all'interno del server ha valori nulli per alcuni valori che non supponiamo siano nulli = (

Io uso Visual Studio Express 2012 per il web. Cosa fare per correggerlo?

Grazie

Risposta accettata

Grazie a tutti per il vostro aiuto, finalmente funziona con me quando uso ciò che dice @GarethD

posterò il codice modificato nel caso qualcuno ne avesse bisogno:

 string name = (string)(Session["LoginUserName"]);
   if(FileUpload3.FileName == null ){

   lblmessage.Text = "Choose the file First then click import ";
   string display = "             Choose the file First then click import                   ";
   ClientScript.RegisterStartupScript(this.GetType(), "myalert", "alert('" + display + "');", true);
   }
   else 
   {


       string path = Path.Combine(Server.MapPath("~/ImportDocument"), Guid.NewGuid().ToString() + name + Path.GetExtension(FileUpload3.PostedFile.FileName));


   FileUpload3.PostedFile.SaveAs(path);
   OleDbConnection dbConnection = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + "; Extended Properties = \"Excel 8.0;HDR=Yes;IMEX=2\" ");
    dbConnection.Open();
    try
   {       

       // Get the name of the first worksheet:
       DataTable dbSchema = dbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
       if (dbSchema == null || dbSchema.Rows.Count < 1)
       {
           throw new Exception("Error: Could not determine the name of the first worksheet.");
       }
       string firstSheetName = dbSchema.Rows[0]["TABLE_NAME"].ToString();

       // Now we have the table name; proceed as before:
       OleDbCommand dbCommand = new OleDbCommand("Select [IdOftable], [Time],[InstrumentLeftHand],[LeftSwitch],[LeftKnob],[ForceFeedbackLeftHand],[CumTimeLeftForceOverThreshold],[CumTimeLeftForceOver2xThreshold],[TranslationLeft_x],[TranslationLeft_y],[TranslationLeft_z],[quatLeft_x],[quatLeft_y],[quatLeft_z],[quatLeft_w],[InstrumentRightHand],[RightSwitch],[RightKnob],[ForceFeedbackRightHand],[CumTimeRightForceOverThreshold],[CumTimeRightForceOver2xThreshold],[TranslationRight_x],[TranslationRight_y],[TranslationRight_z],[quatRight_x],[quatRight_y],[quatRight_z],[quatRight_w],[BloodEmittedFrame],[BloodCurrentFrame],[TotalBloodEmitted],[TotalWhiteFibreCut],[TotalRedFibreCut],[Volume0_Brain],[Volume1_Tumor],[Volume2_Tumor] from [" + firstSheetName + "]", dbConnection);
       OleDbDataReader dbReader = dbCommand.ExecuteReader();

       using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conn))
       {

           bulkCopy.DestinationTableName = "MyExcel";
           try
           {
               conn.Open();
               bulkCopy.BulkCopyTimeout = 400;
               bulkCopy.WriteToServer(dbReader);
               bulkCopy.BatchSize = 16000;
               conn.Close();

           }

           catch (Exception ex)
           {
               Response.Write(ex.ToString());
           }
           finally
           {
               bulkCopy.Close();
               dbReader.Close();
               //lblmessage.Text = " data successfully imported  ";
           //    Response.Write(@"<script language=""javascript"">alert('Details saved successfully')</script>");
               string display = "             Data Duccessfully Imported  Now Click On Calculate the result to calculate your results taking in your account The calculation will take a while , please wait   ";
               ClientScript.RegisterStartupScript(this.GetType(), "myalert", "alert('" + display + "');", true);
           }



       }
   }
   finally
   {
       dbConnection.Close();
   }
}


Autorizzato sotto: CC-BY-SA with attribution
Non affiliato con Stack Overflow
Autorizzato sotto: CC-BY-SA with attribution
Non affiliato con Stack Overflow