/* Compile from the command line with: csc /target:library helpdb.cs You may need to add C:\Windows\Microsoft.NET\Framework\v2.0.50727 to your path. */ using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; public partial class StoredProcedures { [Microsoft.SqlServer.Server.SqlProcedure] public static void helpdb(String dbname) { // Connect through the context connection. using (SqlConnection conn = new SqlConnection("context connection=true")) { conn.Open(); // Setup the command to run sp_helpdb. SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "sp_helpdb"; cmd.Parameters.Add("@dbname", SqlDbType.NVarChar, 128); cmd.Parameters["@dbname"].Value = @dbname; // And run it. SqlDataAdapter da = new SqlDataAdapter(cmd); DataSet dataset = new DataSet(); da.Fill(dataset); // Now set up the command to insert the data into the temp table. cmd.CommandType = CommandType.Text; cmd.Parameters.Clear(); cmd.CommandText = "INSERT #helpdb(dbname, logicalname, fileid, filename, " + " filegroup, size, maxsize, growth, usage) " + " VALUES(@dbname, @logicalname, @fileid, @filename, " + " @filegroup, @size, @maxsize, @growth, @usage) "; // Define all parameters. cmd.Parameters.Add("@dbname", SqlDbType.NVarChar, 128); cmd.Parameters.Add("@logicalname", SqlDbType.NVarChar, 128); cmd.Parameters.Add("@fileid", SqlDbType.SmallInt); cmd.Parameters.Add("@filename", SqlDbType.NVarChar, 260); cmd.Parameters.Add("@filegroup", SqlDbType.NVarChar, 128); cmd.Parameters.Add("@size", SqlDbType.NVarChar, 18); cmd.Parameters.Add("@maxsize", SqlDbType.NVarChar, 18); cmd.Parameters.Add("@growth", SqlDbType.NVarChar, 18); cmd.Parameters.Add("@usage", SqlDbType.VarChar, 18); // This parameter has the same value throughout. cmd.Parameters["@dbname"].Value = dbname; // Iterate over the datatable and insert each row into the // temptable. foreach (DataRow row in dataset.Tables[1].Rows) { cmd.Parameters["@logicalname"].Value = row["name"]; cmd.Parameters["@fileid"].Value = row["fileid"]; cmd.Parameters["@filename"].Value = row["filename"]; cmd.Parameters["@filegroup"].Value = row["filegroup"]; cmd.Parameters["@size"].Value = row["size"]; cmd.Parameters["@maxsize"].Value = row["maxsize"]; cmd.Parameters["@growth"].Value = row["growth"]; cmd.Parameters["@usage"].Value = row["usage"]; cmd.ExecuteNonQuery(); } // Clean up. dataset.Dispose(); da.Dispose(); cmd.Dispose(); } } };