need sp_helpdb return values

[betterc]

I've learned how to connect, attach and detach my msde tables and databases. Now I need to be able to make a call to sp_helpdb and get the return values from a C# applicaiton. Can someone give me some help please? Thanks, cb

[leppie]

Look at this week's articles :) top secret xacc-ide 0.0.1

[betterc]

Where?

[betterc]

I think his article is good for creating wrappers for stored procedures that have been created for a given db. But my question is specifically about sp_helpdb. How would you construct c# code to execute this sp and get it's returned value(s)???? cb

[Heath Stewart]

The same as you do for any stored procedure: create a SqlCommand and fill it's Parameters collection property with parameters using the same names as the parameters defined by the stored procedure, plus one parameter with a direction of ParameterDirection.ReturnValue:

SqlCommand cmd = connection.CreateCommand();
cmd.CommandText = "sp_helpdb";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@dbname", SqlDbType.NVarChar, 128).Value = "master";
SqlParameter retVal = new SqlParameter("@RETURN_VALUE", SqlDbType.NVarChar,
128, ParameterDirection.ReturnValue, false, 0, 0, null,
DataRowVersion.Current, null);
cmd.Parameters.Add(retVal);
try
{
connection.Open();
cmd.ExecuteNonQuery();
}
finally
{
connection.Close();
}
Console.WriteLine(retVal.Value);

Microsoft MVP, Visual C# My Articles

[betterc]

I entered the code as you suggested and it works returning a '0'. My questions is how do I 'get' the values such as the name/path of the db and log files? Thank you, c

[Heath Stewart]

Sorry, I was thinking of a different stored proc. Handle the SqlConnection.InfoMessage event. This gets you the text that is printed using the PRINT statement in SQL (among other things). This is what sp_helpdb uses to print the information about whatever database name you passed as a parameter.

Microsoft MVP, Visual C# My Articles