Output Stored Procedure Results To a Text File 1

[SaturnSeven]

From a aspx page, how can I call a sql stored procedure with a input parameter and have it's results outputted to a text file (specific file and location)
I have managed to populate a Gridview with the stored procedure results, so I happy the SR is OK.
It's just the output to a text file I struggling with.

Any help / guidence will be greatly appreciated.
Many thanks in advance

 

[jmeckley]

there are lots of options. the 1st thing to answer is what the format of the data will be, CSV, XML, XLS, formatted message.

if all you need is a simple csv then a function like this would work

public DataTable FetchDataFor(int input)
{
   //use input to load datatable from stored proc
   return new DataTable();
}

public TextWriter OutputDataTable(DataTable table)
{
   TextWriter writer = new StringWriter();

   string[] headerColumns = new string[table.Columns.Count];
   foreach(DataColumn column in table.Columns)
   {
      headerColumns[column.Ordinal] = column.ColumnName;
   }
   writer.Write(@""");
   writer.Write(string.Join(@"","", headerColumns);
   writer.WriteLine(@""");

   foreach(DataRow row in table.Rows)
   {
      string[] rowToAppend = new string[table.Columns.Count]; 
      foreach(DataColumn column in table.Columns)
      {
         rowToAppend[column.Ordinal] = row[column].ToString();
      }

      writer.Write(@""");
      writer.Write(string.Join(@"","", rowToAppend);
      writer.WriteLine(@""");
   }
   return writer;
}

public void WriteToFile(string filePath, TextWriter writer)
{
   File.Create(filePath, writer.ToString());
}

to export

WriteToFile(@"c:\", OutputDataTable(FetchDataFor(1)));

i wrote this from memory, so i'm sure there will be some bugs.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[SaturnSeven]

Thanks for function, "TextWriter" is throwing error:
The type or namespace name 'TextWriter' could not be found (are you missing a using directive or an assembly reference?)
Any ideas
Many thanks

 

[jmeckley]

you need to include the proper namespace at the begining of the file.
using System.IO;

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[SaturnSeven]

Done that, now I get error on:
File.Create(filePath, writer.ToString()); (File & writer)
Error message:
The best overloaded method match for 'System.IO.File.Create(string, int)'
Argument '2': cannot convert from 'string' to 'int'

 

[jmeckley]

So File.Create is not the correct function to use. Search the System.IO namespace for the appropiate object which creates a file and allows you to write to it. there are many options to do so. it would be something that will take a filepath and either Stream/TextWriter or string as the input of the file.

to get the text of the TextWriter use writer.ToString();

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[jbenson001]

Look at the help files, they are valuable. We are not here to write the code for you. Jason has given you a good start. So just try poking through the code, debugging and using the help files/google etc. If you really get stuck, then post back. As Jason said, he did it from memory and won't be perfect.

 

[SaturnSeven]

I only use this forum when I am really stuck, I've already been through help files/google etc , but to no avail.

I know you are "not here to write code for me" I never asked for any code, I did only ask for help / guidence. I would much rather be pointed to a help file or similar example, or told that you need to use function X, then Y, then Z.

I should have stated that I was working with a vb aspx page and not c#, so for me, debugging this code was 'more' difficult being a different language.

So if anyone can help / offer guidence? please advise

 

[ca8msm]

You can do this in the stored procedure itself. Read up on the "xp_cmdshell" method and ask for help in the SQL Server forum if you get stuck.


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[jbenson001]

There are plenty of conversion tools online that can help convert Jason's code for you.