How to Get all Stored Procedure from C# [Resolved]

Posted by Jayakumars under ADO.NET on 9/6/2015 | Points: 10 | Views : 5329 | Status : [Member] [MVP] | Replies : 6
Hi

I have Mysql Data Base with 50 Stored Procedure .

1.How to Get all Stored Procedure without data using C# code only. No need design side only c# code .

2.How to get Last 5 days created Stored procedure stored to one text file

I need this Mysql Only without Data.

Mark as Answer if its helpful to you

Kumaraspcode2009@gmail.com



Responses

Posted by: Rajeshatkiit on: 1/6/2016 [Member] Starter | Points: 50

Up
0
Down

Resolved

You can use SMO for that. First of all, add references to these assemblies to your project:

Microsoft.SqlServer.ConnectionInfo
Microsoft.SqlServer.Smo
Microsoft.SqlServer.SmoEnum
They are located in the GAC (browse to C:\WINDOWS\assembly folder).

Use the following code as an example of scripting stored procedures:

using System;
using System.Collections.Generic;
using System.Data;
using Microsoft.SqlServer.Management.Smo;

class Program
{
static void Main(string[] args)
{
Server server = new Server(@".\SQLEXPRESS");
Database db = server.Databases["Northwind"];
List<SqlSmoObject> list = new List<SqlSmoObject>();
DataTable dataTable = db.EnumObjects(DatabaseObjectTypes.StoredProcedure);
foreach (DataRow row in dataTable.Rows)
{
string sSchema = (string)row["Schema"];
if (sSchema == "sys" || sSchema == "INFORMATION_SCHEMA")
continue;
StoredProcedure sp = (StoredProcedure)server.GetSmoObject(
new Urn((string)row["Urn"]));
if (!sp.IsSystemObject)
list.Add(sp);
}
Scripter scripter = new Scripter();
scripter.Server = server;
scripter.Options.IncludeHeaders = true;
scripter.Options.SchemaQualify = true;
scripter.Options.ToFileOnly = true;
scripter.Options.FileName = @"C:\StoredProcedures.sql";
scripter.Script(list.ToArray());
}
}

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sheonarayan on: 10/30/2015 [Administrator] HonoraryPlatinum | Points: 25

Up
2
Down
1. To get the stored procedure list in MySql
Execute below SQL query in ADO.NET the way you are executing other mySql table query.

SHOW PROCEDURE STATUS WHERE Db = 'mydb'

OR
select name from mysql.proc 


2. Once you have executed above query, you will get Created column also, filter it based on this to get SPs created in last 5 days.

Regards,
Sheo Narayan
http://www.dotnetfunda.com

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Rajnilari2015 on: 10/30/2015 [Member] [Microsoft_MVP] [MVP] Platinum | Points: 25

Up
0
Down
How to Get all Stored Procedure without data using C# code only

sérieusement ? C # est destinée à faire ?Ou il est ADO.NET?

Seriously ? C# is meant to do that ? Or it is ADO.NET?

--
Thanks & Regards,
RNA Team

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Rajnilari2015 on: 10/30/2015 [Member] [Microsoft_MVP] [MVP] Platinum | Points: 25

Up
0
Down
No need design side only c# code .

Soin d'expliquer ?

Care to explain ?

--
Thanks & Regards,
RNA Team

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Lucygrey on: 12/28/2015 [Member] Starter | Points: 25

Up
0
Down
One can get all stored Procedures Collection From SQL Server with the help of C#. If you need the storage in My SQL, then c# is a must I guess.

http://www.keenesystems.com/Services/SoftwareDevelopment.aspx

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Lucygrey on: 1/5/2016 [Member] Starter | Points: 25

Up
0
Down
Hey,Just read the output of SELECT NAME from SYS.PROCEDURES , then call EXEC sp_HelpText SPNAME for each stored procedure, you'll get a record set with one line of text per row.


http://www.keenesystems.com/Services/SoftwareDevelopment.aspx

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response