Posted by: Cirilo Meggiolaro | 01/3/2009

Tip of the day #81 – Create stored procedures with Microsoft.SqlServer.Management.Smo namespace

Let’s check today how to create stored procedures in a Sql Server database using the Microsoft.SqlServer.Management.Smo namespace.

If you haven’t check the latest tips, it is a good time to check them, especially Tip #75 that explains the main classes from the namespace and Tip #80 that explains the hierarchical view for database objects.

How to…

The tasks you need to perform to create a stored procedure are:

  • Create an instance of the Server class;

Server svr = new Server(@”MYPC\SQLEXPRESS”);

  • Retrieve the database object instance passing the server object we created on the previous step and the database name;

Database db = new Database(svr, “SmoDb”);

  • Create an instance of the stored procedure class passing the database object and the stored procedure name;

StoredProcedure sp = new StoredProcedure(db, “GetCustomerName”);

  • Define the procedure properties as necessary;

sp.TextMode = false;
sp.AnsiNullsStatus = false;
sp.QuotedIdentifierStatus = false;

  • Create stored procedure parameters if necessary using the StoredProcedureParameter class and add them to the Parameters collection under the stored procedure object;

StoredProcedureParameter parmInput = new StoredProcedureParameter(sp, “@customer_id”, DataType.Int);

sp.Parameters.Add(parmInput);

  • Defines the stored procedure body using the TextBody property;

sp.TextBody = @”SELECT @retVal = (SELECT customer_name
FROM customer with(nolock)
WHERE customer_id = @customer_id
)”;

  • Invoke the Create method from the stored procedure object.

sp.Create();

The code must be similar to the following:

/// Creates a server class instance.
Server svr = new Server(@”MYPC\SQLEXPRESS”);

try
{
    if (svr != null)
    {
        /// Retrieves the database from the server class instance.
        Database db = svr.Databases[“SmoDb”];

        if (db != null)
        {
            /// Creates a new stored procedure class instance passing the
            /// database object and the procedure name as parameter.

            StoredProcedure sp = new StoredProcedure(db, “GetCustomerName”);

            /// Defines TextMode to false to allow the operations.
            sp.TextMode = false;

            /// Sets the stored procedure properties.
            sp.AnsiNullsStatus = false;
            sp.QuotedIdentifierStatus = false;

            /// Creates a stored procedure parameter.
            StoredProcedureParameter parmInput = new StoredProcedureParameter(sp, “@customer_id”, DataType.Int);
            StoredProcedureParameter parmOutput = new StoredProcedureParameter(sp, “@retVal”, DataType.VarChar(200));
            parmOutput.IsOutputParameter = true;

            /// Adds the parameter to the stored procedure parameter collection.
            sp.Parameters.Add(parmInput);
            sp.Parameters.Add(parmOutput);

            /// Defines the procedure body.
            sp.TextBody = @”SELECT @retVal = (SELECT customer_name
                                                                     FROM customer with(nolock)
                                                                   WHERE customer_id = @customer_id)”;

            /// Creates the stored procedure in the database.
            sp.Create();
        }
    }
}
catch (Exception ex)
{
    Console.WriteLine(ex.Message);
}

Alter stored procedure

If you need to alter a stored procedure the process is quite similar. The difference is that instead of create a new StoredProcedure class instance, you are going to retrieve the procedure from the StoredProcedures collection available under the database object, set the properties as necessary and invoke the Alter method from the stored procedure object.

The code must be similar to the following:

/// Creates a server class instance.
Server svr = new Server(@”MYPC\SQLEXPRESS”);

try
{
    if (svr != null)
    {
        /// Retrieves the database from the server class instance.
        Database db = svr.Databases[“SmoDb”];

        if (db != null)
        {
            /// Retrieves the stored procedure from the StoredProcedures
            /// collection available under the database object.

            StoredProcedure sp = db.StoredProcedures[“GetCustomerName”, “dbo”];

            if (sp != null)
            {
                /// Defines TextMode to false to allow the operations.
                sp.TextMode = false;

                /// Sets the stored procedure properties.
                sp.QuotedIdentifierStatus = true;

                /// Updates the stored procedure in the database.
                sp.Alter();
            }
        }
    }
}
catch (Exception ex)
{
    Console.WriteLine(ex.Message);
}

TextMode property

The Boolean property called TextMode specifies whether a text header can be set or not. If you want to set stored procedures properties like Ansi_Nulls, Quoted_Identifier and others you’ll have to set the TextMode to false before invoke the Create or Alter methods from the stored procedure class.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

%d bloggers like this: