Массовая вставка данных с использованием C # DataTable и функции SQL Server OpenXML

Я получил требование, что «Чтение данных из файла Excel, а затем после проверки данных

нажмите все записи в таблице базы данных». Другое дело, когда при вставке данных в базу данных происходит сбой во время вставки записи, я должен откатить всю вставленную запись.

Чтобы выполнить задачу, я сделал следующим образом

OpenXML.

Я создал процедуру, которая использует функцию OpenXML сервера sql, которая позволяет вставлять несколько записей одновременно. OpenXML требует XML-строку записи для вставки данных в базу данных.

ALTER PROCEDURE [dbo].[Ins_Employee]    
(    @XmlString text    )    
AS    
BEGIN    
 SET NOCOUNT ON    
 BEGIN TRANSACTION    
 Begin Try    

  DECLARE @XMLDocPointer INT    
  EXEC sp_xml_preparedocument @XMLDocPointer OUTPUT, @XmlString    

   INSERT INTO Employee
   (Name, Email, PhoneNo)    
   SELECT Name,Email,PhoneNo   
   FROM OPENXML(@XMLDocPointer,'/ROOT/DATA',2)    
   WITH  (Name VARCHAR(50),-- '@Name',     
         Email VARCHAR(50),-- '@Email',     
         PhoneNo VARCHAR(50) --'@PhoneNo')     

   EXEC sp_xml_removedocument @XMLDocPointer    
   COMMIT TRANSACTION    
   Return 0;     
 End Try    
 Begin Catch    
   ROLLBACK TRANSACTION    
 End Catch    
END 

Как вы видите выше, процедура OpenXML использует xmlDocument в качестве входных данных, которые создаются системой, определяют процедуру sp_xml_preparedocument, которая принимает xmlString в качестве входных данных и возвращает XmlDocument.

После того, как OpenXML выполнит задачу вставки sp_xml_removedocument, системная процедура должна удалить этот элемент.

Все записи вставляются в один раз с помощью функции OpenXML, так как я использовал транзакцию, если вставка одной записи не удалась, все вставленные записи получают откат.

Следующая строка кода, используемого для выполнения кода, т.е. хранимой процедуры.

Как вы видите, я передаю Element centric xml в процедуру.

Exec Ins_Employee
 '
  
    pranay
    [email protected]
    99007007
  
 '

Примечание.

Если вы передаете XML-строку как атрибут, ориентированный на нее, как в процедуре, вам нужно определить переменную, чтобы оператор выбора в процедуре был

SELECT Name,Email,PhoneNo   
   FROM OPENXML(@XMLDocPointer,'/ROOT/DATA',2)    
   WITH  (Name VARCHAR(50) '@Name',     
         Email VARCHAR(50) '@Email',     
         PhoneNo VARCHAR(50) '@PhoneNo')

Exec Ins_Employee
  '     
       
  '

Теперь, после завершения работы с базой данных, кодовая часть приложения выглядит следующим образом.

Загруженный файл Excel, который содержит данные сотрудника

Уровень представления

Следующая функция в уровне представления считывает данные из файла excel, который загружается на сервер.

private void ReadAndInsertExcelData()
{
     int i;
     bool blValid = true;
     OleDbCommand ocmd;
     OleDbDataAdapter oda;
     DataTable dtDetails;
     DataSet dsDetails;

     OleDbConnection oconn = new OleDbConnection     
          (@"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + 
               Server.MapPath("~/Upload/MonthlyActual.xls") + ";Extended 
               Properties='Excel 8.0;HDR=YES;IMEX=1'");
     try
     {
          ocmd = new OleDbCommand("select * from [Sheet1$]", oconn);
          oda = new OleDbDataAdapter(ocmd);
          dsDetails = new DataSet();
          oda.Fill(dsDetails, "DATA");
          dtDetails = dsDetails.Tables[0];
          dsDetails.DataSetName = "ROOT";
          i = 0;

          DataRow[] drLst = dtDetails.Select("(Name is null) or (Email is 
                              null) or (PhoneNo is null)");
          if (drLst.Count() > 0)
               blValid = false;
          if (blValid)
          {
               XMLController xMLController = new XMLController();
               xMLController.Ins(BaseLineType, dtDetails);
          }
     }
     catch 
     {
          lblMsg.Text = ex.Message;
          lblMsg.ForeColor = System.Drawing.Color.Red;
     }
     finally
     {
          lblMsg.Text = "Data Inserted Sucessfully";
          oda = null;
          dtDetails = null;
          dsDetails = null;
     }
}

public int Ins(DataTable pImportTable)
{
     int IsSuccess = -100;
     try
     {
          StringBuilder sbXMLString = new StringBuilder();
          System.IO.StringWriter sw = new System.IO.StringWriter
                                                  (sbXMLString);
          pImportTable.WriteXml(sw);

          DALXML dALManualCost = new DALXML();
          dALManualCost.Ins(sbXMLString.ToString());
          IsSuccess = dALManualCost.IsSuccess;
     }
     catch
     {
          throw;
     }
     return IsSuccess;
}

sbXMLString.Append("");
          for (int i = 0; i < pImportTable.Rows.Count; i++)
          {
            sbXMLString.Append("<DATA ");
            sbXMLString.Append("Name='" + 
                         pImportTable.Rows[i][0].ToString().Trim() + "' ");
            sbXMLString.Append("Email='" + pImportTable.Rows
                         [i][1].ToString().Trim() + "' ");
            sbXMLString.Append("PhoneNo='" + 
                         pImportTable.Rows[i][2].ToString().Trim() + "' ");
            
            sbXMLString.Append(" />");
 
          }
          sbXMLString.Append("");

public void Ins(string pXMLString)
{
     try
     {
          Database db = CommonHelper.GetDataBaseInstance();
          DbCommand cmdXML = db.GetStoredProcCommand
                         (SP_INSERT_STAGINGMANUALCOSTMONTHLY);

          db.AddInParameter(cmdXML, "XmlString", DbType.String, 
                                                       pXMLString);
          db.AddParameter(cmdXML, "ret", DbType.Int32,                     
          ParameterDirection.ReturnValue, "", DataRowVersion.Current, 
                                                            IsSuccess);

          db.ExecuteNonQuery(cmdXML);
          IsSuccess = Convert.ToInt32(db.GetParameterValue(cmdXML, "ret"));
     }
     catch
     {
          IsSuccess = -100;
          throw;
     }
}