Click here to Skip to main content
15,899,313 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi, I have two stored procedures inside a text file which are:
-usp_StoredProcedureOne
-usp_StoredProcedureTwo

After I have execute them in C# and they are saved in SQL Server 2008, I want to retrieve their names and store into a text file for logging purposes. I have below codes which are used to execute stored procedure and commit them using C#. How can I retrieve the name only? Below are my codes:

C#
using (SqlConnection conn = new SqlConnection(sqlConnectionString))
{
    conn.Open();
    Server server = new Server(new ServerConnection(conn));
    SqlTransaction transaction;
    transaction = conn.BeginTransaction();

    try
    {
        server.ConnectionContext.ExecuteNonQuery(content);
        transaction.Commit();

        //I wanna retrieve the name after commit.
        using (SqlCommand cmd = new SqlCommand("SELECT OBJECT_NAME(@@PROCID)", conn))
        {
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                using (StreamWriter sw = new StreamWriter(@"C:\Users\Desktop\" + DateTime.Now.ToString("yyyyMMdd") + "_Extra.txt", true))
                {
                    sw.WriteLine(reader);
                    sw.Close();
                }
            }
        }

        return true;
    }
    catch(Exception ex){}
}


What I have tried:

I have tried using SELECT OBJECT_NAME(@@PROCID) but it did not return any value for me.
Posted
Updated 21-Feb-16 20:44pm
v2
Comments
dan!sh 22-Feb-16 2:31am    
Not sure what you are trying to achieve here. Don't you already have the stored procedure name in text file?
Jamie888 22-Feb-16 2:37am    
Yes sir, in the text file I have two complete stored procedure. After execution, I want to retrieve only their stored procedure name(e.g. usp_StoredProcedureOne) and save it into a new text file.
Maciej Los 22-Feb-16 2:45am    
A "content" variable should store the name of procedure...
Jamie888 23-Feb-16 21:01pm    
Yes sir. Thank you for your suggestion. I have tried it by using Regex. The final output now is "CREATE PROCEDURE "procedure name"".

1 solution

You do not really need to query the database to get the procedure name. You can get the name from content variable (which holds create procedure script). Just get the word that follows Create Procedure.

If you will always have only these 2 procedures, why not simply create the files with static names? You already know what the names should be. IMHO there is nothing wrong with hardcoding file name if you know there will be only these two files.

If you really want to query database to get the stored procedure names, take a look at [databaseName].Information_Schema.Routines view. You can get the procedure name from there using a simple select.
 
Share this answer
 
v2
Comments
Jamie888 22-Feb-16 3:27am    
Sir, i have tried your suggestion and used Regex to achieve my purpose. But unfortunately, I cant get the result. The content of text file created will be always "System.Text.RegularExpressions.MatchCollection" instead of stored procedure name.
Below are my codes:
var matches = Regex.Matches(content, @"PROCEDURE\S*(?:\s\S+)?");
using (StreamWriter sw = new StreamWriter(@"C:\Users\Desktop\" + DateTime.Now.ToString("yyyyMMdd") + "_Extra.txt", true))
{
//sw.WriteLine(Environment.NewLine);
sw.WriteLine(matches);
sw.Close();
}
Jamie888 22-Feb-16 3:40am    
Sir, I have tried to modify my codes for Regex part and it works fine now. Below are my codes:

string pattern = @"CREATE PROCEDURE\S*(?:\s\S+)?";
Regex rgx = new Regex(pattern);

foreach (Match match in rgx.Matches(content))
{
using (StreamWriter sw = new StreamWriter(@"C:\Users\Desktop\" + DateTime.Now.ToString("yyyyMMdd") + "_Extra.txt", true))
{
//sw.WriteLine(Environment.NewLine);
sw.WriteLine(match.Value);
sw.Close();
}
}
dan!sh 22-Feb-16 4:04am    
Are you sure? Your Regex does not look right.
Jamie888 22-Feb-16 4:11am    
Sir, I have modified my comment above. Its ok to have CREATE PROCEDURE syntax in front of my stored procedure name.
dan!sh 22-Feb-16 4:20am    
You can also see the response to this question.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900