将数据从Excel导入ASP.NET C#中的Sql Server 2008(导入日期时间数据)

问题描述

Hey i am trying to import data from excel to sql server , it works fine till i am not passing date but now i want to pass the date to sql server it provides error as datatype not matches.
Anyone has logic or please suggest me what can i do to ..

我的代码是

my code is

string path = string.Concat(Server.MapPath("~/TempFiles/"), FileUpload1.FileName);
               //Save File as Temp then you can delete it if you want
               FileUpload1.SaveAs(path);


               string excelConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 8.0", path);

               // Create Connection to Excel Workbook
               using (OleDbConnection connection =
                            new OleDbConnection(excelConnectionString))
               {
                   OleDbCommand command = new OleDbCommand
                           ("Select * FROM [Sheet1$]", connection);

                   connection.Open();

                   // Create DbDataReader to Data Worksheet
                   using (DbDataReader dr = command.ExecuteReader())
                   {

                       // SQL Server Connection String
                       string sqlConnectionString = @conn;

                       // Bulk Copy to SQL Server
                       using (SqlBulkCopy bulkCopy =
                                  new SqlBulkCopy(sqlConnectionString))
                       {
                           bulkCopy.DestinationTableName ="Table1";
                           bulkCopy.WriteToServer(dr);
                           Label1.Text = "The Client data has been exported successfully from Excel to SQL";
                       }
                   }
               }

直到我没有传递日期之前，这段代码才能正常工作...

this code perfectly works till i am not passing date...

推荐答案

直到我没有输入日期，此代码才能正常工作...

this code perfectly works till i am not passing date...

string sqlConnectionString = @conn;
command.CommandType = CommandType.Text;
                OleDbDataAdapter objAdapter1 = new OleDbDataAdapter(command);
                DataTable dt = new DataTable();
                DataSet objDataset1 = new DataSet();

                objAdapter1.Fill(dt);

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    if (dt.Rows[0][5].ToString() != "")
                    {
                        DateTime dt1 = cf.texttodb(dt.Rows[0][5].ToString());
                        dt.Rows[i][5] = dt1;
                    }}
 using (SqlBulkCopy bulkCopy =
                               new SqlBulkCopy(sqlConnectionString))
                {
                    bulkCopy.DestinationTableName = "Tablename";
                    bulkCopy.WriteToServer(dt);
                    Label1.Text = "The Client data has been exported successfully from Excel to SQL";
                }

在此我创建了一个函数txtdob，将我的字符串转换为日期时间格式.谢谢，我尝试了将其标记为答案的功能

in this i had created a function txtdob which converts my string to datetime format Thank you i tried it workes if u feel so mark it as answer

这篇关于将数据从Excel导入ASP.NET C#中的Sql Server 2008(导入日期时间数据)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！