如何在Excel中使用批量插入在Excel中的Db表中插入空值 [英] How Do I Insert Null Values In Db Table From Excel With Bulk Insert In C#

问题描述

你好，



如果excel表有空列，那我怎么能在数据库中为excel中的空列插入空值。

Hello,

if excel sheet having empty column, then how can i insert null value in database for the empty column in excel.

推荐答案

在表格中创建列时，添加coulumnName数据类型= NULL



例如：



empname varchar（200）= NULL

while creating columns in table , you add coulumnName Datatype = NULL

for ex:

empname varchar(200)=NULL

请发布您尝试过的代码..

Please post the code what you have tried..

试试这样，

try like that,

#region"BULK UPLOAD DATA TO DB TABLE                  "
      private static void BulkUploadDataToDBTable(DataTable dtData, int FileID)
      {
          string strSource = "DumpDataFromSourceFiles";
          try
          {
              if (dtData != null && dtData.Rows.Count > 0)
              {
                  myLogFile.LogMessage((int)AppLog.LogLevel.DEBUG, strSource, "Started bulkuploading the records in datatable to PhoneRSVP table");
                  string sqlConnectionString = Constants.DBConn;
                  SqlConnection sqlConnection = new SqlConnection(sqlConnectionString);
                  sqlConnection.Open();

                  //Adding the PhoneRSVPFileID column to the datatable
                  DataColumn dcFileID = new DataColumn();
                  dcFileID.DataType = typeof(int);
                  dcFileID.DefaultValue = FileID;
                  dcFileID.ColumnName = "PhoneRSVPFileID";
                  dtData.Columns.Add(dcFileID);

                  //Adding the CreatedOn column to the datatable
                  DataColumn dcCreatedOn = new DataColumn();
                  dcCreatedOn.DataType = typeof(DateTime);
                  dcCreatedOn.DefaultValue = DateTime.Now;
                  dcCreatedOn.ColumnName = "CreatedOn";
                  dtData.Columns.Add(dcCreatedOn);


                  //to clear existing data from table
                  SqlCommand cmd = new SqlCommand("Delete from PhoneRSVP", sqlConnection);
                  cmd.ExecuteNonQuery();

                  using (SqlBulkCopy bcp = new SqlBulkCopy(sqlConnectionString, SqlBulkCopyOptions.FireTriggers))
                  {
                      bcp.ColumnMappings.Add("CUST_SKEY", "CUST_SKEY");
                      bcp.ColumnMappings.Add("APPNUM_SKEY", "APPNUM_SKEY");
                      bcp.ColumnMappings.Add("INVITEE_FIRST_NAME", "INVITEE_FIRST_NAME");
                      bcp.ColumnMappings.Add("INVITEE_LAST_NAME", "INVITEE_LAST_NAME");
                      bcp.ColumnMappings.Add("STORE_NUMBER", "STORE_NUMBER");
                      bcp.ColumnMappings.Add("EVENT_DATE", "EVENT_DATE");
                      bcp.ColumnMappings.Add("EVENT_TIME", "EVENT_TIME");
                      bcp.ColumnMappings.Add("GUESTS", "GUESTS");
                      bcp.ColumnMappings.Add("RSVP_DATETIME_RESPONSE", "RSVP_DATETIME_RESPONSE");
                      bcp.ColumnMappings.Add("VRU_CSR", "VRU_CSR");
                      bcp.ColumnMappings.Add("EVENT_CODE", "EVENT_CODE");
                      bcp.ColumnMappings.Add("PhoneRSVPFileID", "PhoneRSVPFileID");
                      bcp.ColumnMappings.Add("CreatedOn", "CreatedOn");


                      bcp.DestinationTableName = "PhoneRSVP";

                      bcp.BulkCopyTimeout = 0;
                      bcp.BatchSize = 1000;

                      bcp.WriteToServer(dtData);
                  }
                  myLogFile.LogMessage((int)AppLog.LogLevel.DEBUG, strSource, "Records in datatable bulkuploaded into PhoneRSVP table successfully.");
                  sqlConnection.Close();
              }
          }
          catch (Exception ex)
          {
              //MoveToArchive(filePath, false);
              myLogFile.LogMessage((int)AppLog.LogLevel.ERROR, strSource, ex.Message);
              errfunName = strSource;
              throw ex;
          }
      }
      #endregion

这篇关于如何在Excel中使用批量插入在Excel中的Db表中插入空值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！