none
how can we replace SqlXmlCommand RRS feed

  • Question

  • Hi Experts,

    We have a problem like we should not use Microsoft.Data.SqlXml namespace which does the following function

    SqlXmlCommand command = new SqlXmlCommand("Provider=SQLOLEDB;" + ConfigurationSettings.AppSettings["DBConnect"]);
    command.CommandText = "Stored_Procedure_name'" + document.OuterXml + "'";
     this.function1(new StreamReader(command.ExecuteStream()).ReadToEnd());

    I have to achieve the same functionality without any difference without using Microsoft.Data.SqlXml. Please post your suggetions

    Thanks in advance!!!

    Tuesday, July 17, 2012 12:09 PM

Answers

  • Hi Deepu,

    Welcome to the MSDN Forum.

    How about SqlCommand: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.aspx ?

    Here is the code snippet:

    public void queryProc(string Sql)
    {
    	try {
    		//for sql server
    		SqlConnection conn = new SqlConnection();
    		conn.ConnectionString = ConnectionString;
    
    		//for SQL 
    		SqlCommand command1 = new SqlCommand(Sql, conn);
    		command1.CommandType = CommandType.StoredProcedure;
    
    		SqlParameter p1 = new SqlParameter("@Param1", "abc'efd");
    		SqlParameter p2 = new SqlParameter("@Param2", "efghij\\@#$%^&");
    		SqlParameter p3 = new SqlParameter();
    		p3.ParameterName = "@Param3";
    		p3.SqlDbType = SqlDbType.VarChar;
    		p3.Direction = ParameterDirection.Output;
    		p3.Size = 60;
    		SqlParameter p4 = new SqlParameter();
    		p4.ParameterName = "@Param4";
    		p4.SqlDbType = SqlDbType.VarChar;
    		p4.Direction = ParameterDirection.Output;
    		p4.Size = 60;
    
    		command1.Parameters.AddRange({
    			p1,
    			p2,
    			p3,
    			p4
    		});
    		command1.Connection.Open();
    		SqlDataReader r = command1.ExecuteReader();
    		r.Close();
    		SqlDataReader r2 = command1.ExecuteReader();
    		//command1.Connection.Close()
    		r2.Close();
    		Console.WriteLine(p3.SqlValue);
    		Console.WriteLine(p4.SqlValue);
    	} catch (Exception ex) {
    		Interaction.MsgBox(ex.Message);
    	}
    }

    I hope this will be helpful.

    Best regards,


    Mike Feng
    MSDN Community Support | Feedback to us
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, July 18, 2012 7:07 AM
    Moderator

All replies

  • Hi Deepu,

    Welcome to the MSDN Forum.

    How about SqlCommand: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.aspx ?

    Here is the code snippet:

    public void queryProc(string Sql)
    {
    	try {
    		//for sql server
    		SqlConnection conn = new SqlConnection();
    		conn.ConnectionString = ConnectionString;
    
    		//for SQL 
    		SqlCommand command1 = new SqlCommand(Sql, conn);
    		command1.CommandType = CommandType.StoredProcedure;
    
    		SqlParameter p1 = new SqlParameter("@Param1", "abc'efd");
    		SqlParameter p2 = new SqlParameter("@Param2", "efghij\\@#$%^&");
    		SqlParameter p3 = new SqlParameter();
    		p3.ParameterName = "@Param3";
    		p3.SqlDbType = SqlDbType.VarChar;
    		p3.Direction = ParameterDirection.Output;
    		p3.Size = 60;
    		SqlParameter p4 = new SqlParameter();
    		p4.ParameterName = "@Param4";
    		p4.SqlDbType = SqlDbType.VarChar;
    		p4.Direction = ParameterDirection.Output;
    		p4.Size = 60;
    
    		command1.Parameters.AddRange({
    			p1,
    			p2,
    			p3,
    			p4
    		});
    		command1.Connection.Open();
    		SqlDataReader r = command1.ExecuteReader();
    		r.Close();
    		SqlDataReader r2 = command1.ExecuteReader();
    		//command1.Connection.Close()
    		r2.Close();
    		Console.WriteLine(p3.SqlValue);
    		Console.WriteLine(p4.SqlValue);
    	} catch (Exception ex) {
    		Interaction.MsgBox(ex.Message);
    	}
    }

    I hope this will be helpful.

    Best regards,


    Mike Feng
    MSDN Community Support | Feedback to us
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, July 18, 2012 7:07 AM
    Moderator
  • Thanks 

    I have stored procedure which returns data in the xml format so I used command.ExecuteXmlReader but is gives me error like invalid return values

    exmaple Stored procedure


    EXEC sp_xml_preparedocument @hDoc OUTPUT, @XMLData

    SELECT TOP 100 PERCENT '<Messages><UserMessage>sign-in information was sent</UserMessage></Messages>' AS XMLDATA
    SELECT TOP 100 PERCENT '<Return>' AS XMLDATA
    SELECT TOP 100 PERCENT '<Success>True</Success>' AS XMLDATA
    SELECT TOP 100 PERCENT '<Users>' AS XMLDATA

    SELECT TOP 100 PERCENT ID AS UserID, UserType, FirstName, LastName, Email, Username, Password
    FROM dbo.v_Users [User]
    WHERE (Active = 1) AND (Username = @Parameter) OR (Email = @Parameter)
    ORDER BY UserType, LastName, FirstName, ID, Username
    FOR XML AUTO

    SELECT TOP 100 PERCENT '</Users>' AS XMLDATA

    SELECT TOP 100 PERCENT '</Return>' AS XMLDATA

    SELECT TOP 100 PERCENT '</ForgotPasswordResponse>' AS XMLDATA

    Thanks in advance!!!

    Regards,

    Deepu

    Wednesday, July 18, 2012 9:16 AM
  • Hi Deepu,

    Where is the start tag of "</ForgotPasswordResponse>"?

    Best regards,


    Mike Feng
    MSDN Community Support | Feedback to us
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, July 19, 2012 2:08 AM
    Moderator