Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Output Stored Procedure Results To a Text File 1

Status
Not open for further replies.

SaturnSeven

Programmer
Aug 4, 2005
40
GB
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
 
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
Code:
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
Code:
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.
 
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
 
you need to include the proper namespace at the begining of the file.
using System.IO;

Jason Meckley
Programmer
Specialty Bakers, Inc.
 
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'
 
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.
 
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.
 
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
 
There are plenty of conversion tools online that can help convert Jason's code for you.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top