Pass object to a sql server proc

Question

Pass object to a sql server proc

Navigation

#1 by (0 votes)

1

How can I make an SP that receives an object? This is my SP

ALTER PROCEDURE [dbo].[sp_alt_funcionarios]  
    -- Add the parameters for the stored procedure here  
(  
     @id int,
     @nome varchar(60)  
    ,@dataNascimento DateTime  
    ,@cpf bigint 
    ,@cidade  int  
)  
AS  
BEGIN  
    ---- SET NOCOUNT ON added to prevent extra result sets from  
    SET NOCOUNT ON;  

        -- Insert statements for procedure here  
        update funcionarios set
            nome = @nome
            ,dataNascimento = @dataNascimento
            ,cpf = @cpf
            ,cidade = @cidade
         where 
            id = @id

END

and this is my service that picks up uses this SP

public class PutFuncionario
    {
        BancoContext banco = new BancoContext();

        public HttpResponseMessage updateFuncionario(Funcionario funcionario)
        {
            banco.Database.ExecuteSqlCommand("exec sp_alt_cidade @id, " +
                                             "@nome," +
                                             "@dataNascimento, " +
                                             "@cpf, " +
                                             "@cidade", 
                                             new SqlParameter("@id", funcionario.id), 
                                             new SqlParameter("@nome", funcionario.nome),
                                             new SqlParameter("@dataNascimento", funcionario.dataNascimento),
                                             new SqlParameter("@cpf", funcionario.cpf),
                                             new SqlParameter("@cidade", funcionario.cidade));

            return new HttpResponseMessage(HttpStatusCode.OK);
        }

Instead of going like this: official.id, official name and etc Pass the entire object officer? It is more elegant and etc.

api sql-server c#

asked by anonymous 09.08.2018 / 22:04

1 answer

How to format html tags with javascipt Problems with values passed to static variables with class on server

score 0 · Answer 1

You can serialize the object and pass it to SP as XML, then transpose the information to a temporary one and work with it:

You need to slightly change the class Funcionario :

[Serializable]
[XmlType("funcionario")]
public class Funcionario
{
    [XmlElement("id")]
    public int id { get; set; }
    [XmlElement("nome")]
    public string nome { get; set; }
    [XmlElement("datanascimento")]
    public DateTime dataNascimento { get; set; }
    [XmlElement("cpf")]
    public int cpf { get; set; }
    [XmlElement("cidade")]
    public int cidade { get; set; }
}

Then it is necessary to serialize the object and return a string with the complete XML and structured:

private string SerializeXml()
{
    XmlSerializer xmlSerializer = new XmlSerializer(typeof(Funcionario));
    Funcionario funcionario = new Funcionario()
    {
        nome = "Joel",
        dataNascimento = new DateTime(1980, 10, 15),
        cidade = 1,
        cpf = 123456778,
        id = 1
    };
    string xml = string.Empty;

    using (StringWriter sw = new StringWriter())
    {
        using (XmlWriter xmlwriter = XmlWriter.Create(sw))
        {
            xmlSerializer.Serialize(xmlwriter, funcionario);
            xml = sww.ToString();
        }
    }

    xml = $"<Funcionarios>{xml}</Funcionarios>";

    return xml;
}

Pass only XML to SP:

banco.Database.ExecuteSqlCommand("exec sp_alt_cidade @xml", new SqlParameter("@xml", xml));

Finally treat the information in SP and pass it to a temporary one:

DECLARE @intXML INT

EXEC sp_xml_preparedocument @intXML OUTPUT, @xml

INSERT INTO #TmpFuncionario
SELECT [id], [nome], [datanascimento], [cpf], [cidade] 
FROM OPENXML (@intXML, 'Funcionarios/Funcionario') 
    WITH([id] INT, [nome] VARCHAR(60), [datanascimento] DATETIME, [cpf] BIGINT, [cidade] INT)

I have not tested the code, but it will be something like this (you may need some refinements).