How to retrieve a stored procedure using c#?

Is it possible to retrieve a stored procedure using c#?

What i need to do is i have to search for a particular stored procedure name in a database and if that stored procedure is available means i have to retrieve that stored procedure and write into a text file..

I dont know开发者_Go百科 how to retrieve stored procedure.

Any Suggestion?

EDIT:i have retrieved the stored procedure names using SELECT NAME from SYS.PROCEDURES and i dont know how to retrieve those stored procedures

you can just write a procedure

select Column1,Column2 from sys.procedures where name='yourProcName'

http://databases.aspfaq.com/database/how-do-i-find-a-stored-procedure-containing-text.html

Then just create the SqlConnection & SqlCommand as normal and execute the SQL to find out the details of the proc:

SELECT ROUTINE_NAME, ROUTINE_DEFINITION 
    FROM INFORMATION_SCHEMA.ROUTINES 
    WHERE ROUTINE_NAME = @YourDesiredStoredProc;
    AND ROUTINE_TYPE='PROCEDURE'

C#:

// details is a custom class you would use to hold the details of the proc :)
// Boring connection stuff
if(reader.Read())
{
    this.details = new StoredProcedure(reader.GetString(0), reader.GetString(1);
}
else
{
    throw new ArgumentException("Procedure does not exist");
}

// Close boring connection stuff.

this.details.Write("your_file_name");

If SQLServer: http://msdn.microsoft.com/en-us/library/ms345443(v=sql.90).aspx I guess you can execute that sql statement and write the returning value to your textfile.

Execute your query against sys.syscomments. According to MDSN page it contains entries for each view, rule, default, trigger, CHECK constraint, DEFAULT constraint, and stored procedure within the database. The text column contains the original SQL definition statements.