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