Get procedure OUTPUT and save in a txt

Question

Get procedure OUTPUT and save in a txt

Navigation

#1 by (1 votes)

3

You can help me the result of a output save it in a .txt using C # and SQL

SQLServer

create table Dedito
(
ruc nvarchar(11),
nom varchar(max),
asd varchar(max),
ddd varchar(max)
)
go
insert into Dedito values('20532803749','danilo','Computadora','FLD_25')
insert into Dedito values('20543433459','Lucero','mochila','FLD_30')
insert into Dedito values('24555523449','Keymi','zapato','FLD_2')
insert into Dedito values('29999663196','CAmila','cuaderno','FLD_15')
insert into Dedito values('20947646732','Diana','lapicero','FLD_77')
select  * from Dedito

create procedure prueba
@aa varchar(max) output
as
begin

select (ruc) +'|'+(ddd) from Dedito
end

DECLARE @aa VARCHAR(max)
EXEC [dbo].[prueba] @aa output
print @aa

--Resultado
--20947646732|FLD_77

C #

    SqlConnection connection = new SqlConnection("Data Source=192.168.125.55;Initial Catalog=pp1;user id=sa;password=***********;");
        connection.Open();
        SqlCommand command = new SqlCommand("prueba", connection);
        command.CommandType = CommandType.StoredProcedure;
        SqlDataAdapter adapter = new SqlDataAdapter(command);
        DataTable table = new DataTable();
        adapter.Fill(table);
        StreamWriter writer = new StreamWriter("D:/DD/path.txt", true);
        foreach (DataRow row in table.Rows)
        {
            writer.WriteLine(row["aa"].ToString());
        }
writer.Close();
        connection.Close();

Greetings

sql c#

asked by Dannylo Ramos 25.09.2018 в 22:34

source

1 answer

Create column from another column in which each row indicates the column to use to obtain that cell Problem with navigation using MVVM in Xamarin

score 1 · Accepted Answer

The Stored Procedure works for you that way ?, because I see that it will return multiple results and you have only one output variable type VARCHAR . I do not know what you really want to do, but the answer to your question is this:

SqlConnection connection = new SqlConnection("CONNECTION_STRING");
SqlCommand command = new SqlCommand("prueba", connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(new SqlParameter("@aa", SqlDbType.VarChar, -1));
command.Parameters["@aa"].Direction = ParameterDirection.Output;
connection.Open();
System.IO.File.WriteAllText(
    @"C:\path.txt", 
    command.Parameters["@aa"].Value.ToString()
    );
connection.Close();

What I would recommend is just take the SELECT and run it from the application using the logic you want to use in Stored Procedure .

The problem is that you are not assigning the value to the variable that you are going to return within SP :

create procedure prueba
    @aa varchar(max) output
as
begin
    select @aa = (ruc) +'|'+(ddd) from Dedito
end

Now, according to what I see, what you want to do is get the value of all the records and concatenate them in the way you indicate, for which I recommend that you do so:

create procedure prueba
as
begin
    select (ruc) +'|'+(ddd) AS 'aa' from Dedito
end

And in C # place this:

SqlConnection connection = new SqlConnection(Con);
connection.Open();
SqlCommand command = new SqlCommand("prueba", connection);
command.CommandType = CommandType.StoredProcedure;
SqlDataAdapter adapter = new SqlDataAdapter(command);
DataTable table = new DataTable();
adapter.Fill(table);
System.IO.StreamWriter writer = new System.IO.StreamWriter(@"C:\path.txt", true);
foreach (DataRow row in table.Rows)
{
    writer.WriteLine(row["aa"].ToString());
}
writer.Close();
connection.Close();