none
ADO.NET code to insert xml into SQL database in order to use already existing Biztalk custom adapter in the solution. RRS feed

  • Question

  • Does anybody know how to write ado.net code to insert xml into SQL database in order to use already existing Biztalk custom adapter in the solution. i can not use use SQL or wcf -SQL adapters in this scenario
    Tuesday, November 10, 2015 6:41 PM

Answers

  • Why not to use SQL or WCF-SQL adapter for this ?

    Adapters are made for LOB communication .

    If you do not want to use any adapter than a plain C# code can be executed from from the expression shape to insert data into your SQL DB.

    Sample code can be like below 

    create procedure INsertData
    @xmlSource as xml
    as begin 
    insert into table1
    select NEWID(),@@xmlSource ,GETDATA()
    end
    
    Code
    
       public void InsertData(XmlDocument xmlSource, string strConnectionString)
      {
    
                 SqlConnection sqlConnection = new SqlConnection(strConnectionString);
                 string strStoredProcedure = "INsertData"; 
                SqlCommand sqlCommand = sqlConnection.CreateCommand();
                 sqlCommand.CommandText = strStoredProcedure;
                 sqlCommand.CommandType = CommandType.StoredProcedure;
                 sqlConnection.Open();
                  sqlParameter = new SqlParameter();  
                sqlParameter.ParameterName = "@xmlSourceData";
                 sqlParameter.SqlDbType = SqlDbType.Xml;
                 sqlParameter.Direction = ParameterDirection.Input;
                 sqlParameter.Value = new XmlNodeReader(xmlSource); 
                sqlCommand.Parameters.Add(sqlParameter); 
                sqlCommand.ExecuteNonQuery(); 
                sqlConnection.Close();
    
    }

    Thanks

    Abhishek


    If this answers your question please mark it accordingly. If this post is helpful, please vote as helpful by clicking the upward arrow mark next to my reply

    • Proposed as answer by Angie Xu Tuesday, November 24, 2015 2:08 AM
    • Marked as answer by Angie Xu Tuesday, November 24, 2015 2:08 AM
    Thursday, November 12, 2015 6:49 AM

All replies

  • Hi ANURUDDHA

    Based  on your description, your case more related to  Biztalk , I will move your case to  Biztalk forum for better support.

    Best regards,

    Kristin


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, November 11, 2015 1:39 AM
  • Why not to use SQL or WCF-SQL adapter for this ?

    Adapters are made for LOB communication .

    If you do not want to use any adapter than a plain C# code can be executed from from the expression shape to insert data into your SQL DB.

    Sample code can be like below 

    create procedure INsertData
    @xmlSource as xml
    as begin 
    insert into table1
    select NEWID(),@@xmlSource ,GETDATA()
    end
    
    Code
    
       public void InsertData(XmlDocument xmlSource, string strConnectionString)
      {
    
                 SqlConnection sqlConnection = new SqlConnection(strConnectionString);
                 string strStoredProcedure = "INsertData"; 
                SqlCommand sqlCommand = sqlConnection.CreateCommand();
                 sqlCommand.CommandText = strStoredProcedure;
                 sqlCommand.CommandType = CommandType.StoredProcedure;
                 sqlConnection.Open();
                  sqlParameter = new SqlParameter();  
                sqlParameter.ParameterName = "@xmlSourceData";
                 sqlParameter.SqlDbType = SqlDbType.Xml;
                 sqlParameter.Direction = ParameterDirection.Input;
                 sqlParameter.Value = new XmlNodeReader(xmlSource); 
                sqlCommand.Parameters.Add(sqlParameter); 
                sqlCommand.ExecuteNonQuery(); 
                sqlConnection.Close();
    
    }

    Thanks

    Abhishek


    If this answers your question please mark it accordingly. If this post is helpful, please vote as helpful by clicking the upward arrow mark next to my reply

    • Proposed as answer by Angie Xu Tuesday, November 24, 2015 2:08 AM
    • Marked as answer by Angie Xu Tuesday, November 24, 2015 2:08 AM
    Thursday, November 12, 2015 6:49 AM