Pass object to proc sql server

Asked

Viewed 218 times

1

Is there any way I can make a 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 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 me going like this: employee id, functionary.name and etc Pass the whole function object? It is more elegant and etc

c# sql-server api
  • 5

    Not.

  • 1

    There is a way: you can serialize the object in XML and pass as argument to an SP. See article: https://www.itworld.com/article/2960645/development/tsql-how-to-use-xml-parameters-in-stored-procedures.html

  • It is also possible through the use of table-Valued Parameter (TVP). Suggested reading: "Using Table-Valued Parameters in SQL Server" -> http://sommarskog.se/arrays-in-sql-2008.html

  • 1

    Note that, an object like you say, is something that exists in a language, for the bank does not make much sense, unless it is a standard format, like xml or json, but this does not make it "more elegant". The Procedure was created to receive parameters and thus works very well. Passing objects in programming languages has other concepts involved, objects can be passed by reference, they can have behaviors, etc., things that don’t make any sense to a precedent. Just pointing out that, although it is possible, with this reduced number of parameters does not make much sense

1 answer

0

You can serialize the object and pass it to SP as XML, then transport 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 XML complete 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 just the XML for the SP:

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

Finally to process the information in the AS and pass it to a temporary:

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 tuning).

Browser other questions tagged c# sql-server api

You are not signed in. Login or sign up in order to post.