Top 5 SQL System stored procedures every developer should know

There are a number of documented and undocumented system stored procedures in MS SQL Server that are helpful for every web developer working with databases. From the developer’s perspective, here’s a list of 5 System stored procedures that are my favorite.



1. sp_help



Purpose



sp_help gives information about database objects. If you wanted to quickly know the structure of a table but are too lazy to look for the schema diagram or to dig for the table you are interested in within the Object explorer, sp_help is here to help



Syntax



It can be used without parameters to get the information of objects in the database.



sp_help



It can be used with a parameter to get the information of a particular object



sp_help



Example



sp_help 'dbo.tblCompany'






2. sp_helptext



Purpose



sp_help gives definition information of objects such as system stored procedures, user defined stored procedures, user defined functions, triggers etc.



Syntax



sp_helptext



Example



sp_helptext 'dbo.sp_tblCompany'




3. sp_MSforeachtable



Purpose



This is a very useful stored procedure for executing a command for ALL the tables in the database. Say you wanted to get the number of rows in all the tables in your database, you could write



Example



EXEC sp_MSforeachtable 'SELECT ''?'', COUNT(*) FROM ?'



The literal ? is used as a token to replace the table name.



4. sp_depends



Purpose



Ever wanted to make a change to a table but were not sure what other objects are dependent on this table? There could be views or stored procedures that could break due to this change. In situations like this, sp_depends come to the rescue.



Syntax

sp_depends



Example



sp_depends 'dbo.tblCompany'




5. sp_spaceused



Purpose



This is a simple stored procedure that gives information on the size of the database or the database objects



Syntax



If it is used without parameters, it would return the database information



sp_spaceused



If it is used with a parameter, it would return the information on the object



sp_spaceused



Example



sp_spaceused 'dbo.tblCompany'

How to Read and Write on Registry using C#

In some cases it is necessary to work with the Registry. The .NET Framework provides some classes for this. This example shows you, how to worky easily with the registry.



Namespace : Microsoft.Win32



Write Information to the Registry



RegistryKey rk = Registry.CurrentUser;
RegistryKey rkSoftware = rk.OpenSubKey("Software", true);
if (rkSoftware != null)
{
RegistryKey rkCompany = rkSoftware.OpenSubKey("Company Name",true);
if (rkCompany == null)
{
rkCompany = rkSoftware.CreateSubKey("\"Company Name");
}
RegistryKey rkInstaller = rkCompany.OpenSubKey("MySoftware", true);
if (rkInstaller == null)
rkInstaller = rkCompany.CreateSubKey("MySoftware");

rkInstaller.SetValue("value1", "test1");
rkInstaller.SetValue("value2", "test2");

rkInstaller.Close();
rkCompany.Close();
rkSoftware.Close();
}
rk.Close();



Read Information from the registry



RegistryKey rk = Registry.CurrentUser.OpenSubKey(@"SoftwareCompany NameMySoftware");
if (rk != null)
{
string value1 = (string)rk.GetValue("value1");
string value2 = (string)rk.GetValue("value2");

rk.Close();
}

Showing Multiline string in a DataGridView Cell

Recently I need to show multi-line string in a datagridview cell. To achieve that we need to write code in CellFormatting event of DataGridview. Following is the code

private void dataGridView1_CellFormatting(object sender, DataGridViewCellFormattingEventArgs e)
{
if (e.Value.Equals("Error"))
{
e.CellStyle.BackColor = Color.Red;
e.CellStyle.ForeColor = Color.White;

}
else
{
if ((!e.Value.Equals("OK")) && e.ColumnIndex==2)
{
e.CellStyle.BackColor = Color.Green;
e.CellStyle.ForeColor = Color.White;
e.CellStyle.WrapMode = DataGridViewTriState.True;
dataGridView1.Columns[2].AutoSizeMode = DataGridViewAutoSizeColumnMode.DisplayedCells;
dataGridView1.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCells;
}
}
}

Dynamic Create DataTable from Generic List using Reflection

I have few methods that returns different Generic Lists.But main thing here I am using Reflection to convert Generic List into Datatable.

//Due To Html tag problem I am using single quote with generic Type T otherwise you dont need to use single quote.

public static DataTable ListToDataTable(List list)
{
DataTable dt = new DataTable();

foreach (PropertyInfo info in typeof(T).GetProperties())
{
Type pt = info.PropertyType;
if (pt.IsGenericType && pt.GetGenericTypeDefinition() == typeof(Nullable))
{
pt = Nullable.GetUnderlyingType(pt);
dt.Columns.Add(info.Name, pt);
}
else
dt.Columns.Add(new DataColumn(info.Name, info.PropertyType));
}
foreach (T t in list)
{
DataRow row = dt.NewRow();
foreach (PropertyInfo info in typeof(T).GetProperties())
{
row[info.Name] = info.GetValue(t, null);
}
dt.Rows.Add(row);
}
return dt;
}

Encrypting and Decrypting web.config Information

The most sensitive information stored in web.config file can be the connection string. You do not want to disclose the information related to your database to all the users where the application is deployed. Every time it is not possible to have a private machine for your sites, you may need to deploy the site in shared host environment. In this situation to maintain the security to encrypt and decrypt the web.config file.

We can encrypt the configuration sections by using two built-in providers: DPAPI (Windows Data Protection API) Provider or the RSA provider. The RSA provider (default) uses an RSA key which holds public and private keys, where as the DPAPI provider uses built-in machine-specific key. Let us explore the steps required to encrypt the sections using RSA.

There is two method for encryption and decryption of web.config file.One through asp.net command line and second through programmatically.
Method # 1:

Encryption

ASP.NET 2.0 provides in built functionality to encrypt few sections of web.config file. The task can be completed using Aspnet_regiis.exe. Below is the web.config file and section.



In this method for encrypting and decryption of web.config does not involve any code, instead is based on the command line tool aspnet_regiis.This command line tool can be found within the %windows%\Microsoft.NET\Framework\versionNumber folder, or can be run directly from the Visual Studio command prompt.

aspnet_regiis.exe -pef “connectionStrings” C:\Projects\DemoApplication



-pef indicates that the application is built as File System website. The second argument is the name of configuration section needs to be encrypted. Third argument is the physical path where the web.config file is located.If you are using IIS base web site the command will be,

aspnet_regiis.exe -pe “connectionStrings” -app “/DemoApplication”

.-pe indicates that the application is built as IIS based site. The second argument is the name of configuration section needs to be encrypted. Third argument “-app” indicates virtual directory and last argument is the name of virtual directory where application is deployed. If everything goes well you will receive a message “Encrypting configuration section…Succeeded!”Open your web.config file and you can see that connection string is encrypted and its look like this.

AQAAANCMnd8BFdERjHoAwE/Cl+sBAAAAed...GicAlQ==



Decryption:

Now to decrypt the configuration section in web.config file use following command,For File System Application,

aspnet_regiis.exe -pdf “connectionStrings” C:\Projects\DemoApplication

For IIS based Application

aspnet_regiis.exe -pd “connectionStrings” -app “/DemoApplication”

If you want to encrypt any nested section in web.config file like element within you need to write full section name as shown below,

aspnet_regiis.exe -pef “system.web/Pages” C:\Projects\DemoApplication

Method # 2:

Step 1: Open Visual Studio > File > WebSite > Select the language (C# or Visual Basic) and location to create a new ASP.NET website.Step 2: Now add a web.config file to the project. Right click the project > Add New Item > Web Configuration FileOpen the web.config and add the following sample entries in the file between the tag as shown below:



...



Step 3: Now add two buttons to the page, called btnEncrypt and btnDecrypt. We will use these buttons to encrypt and decrypt the sections of the web.config file. Add the following code in the button click event of the two buttons:

C#

string provider = "RSAProtectedConfigurationProvider";string section = "connectionStrings";

protected void btnEncrypt_Click(object sender, EventArgs e){try{ Configuration confg = WebConfigurationManager.OpenWebConfiguration(Request.ApplicationPath); ConfigurationSection confStrSect = confg.GetSection(section); if (confStrSect != null) { confStrSect.SectionInformation.ProtectSection(provider); confg.Save(); } // the encrypted section is automatically decrypted!! Response.Write("Configuration Section " + "" + WebConfigurationManager.ConnectionStrings["MyConnString"].ConnectionString + "" + " is automatically decrypted");}catch (Exception ex){

} }

protected void btnDecrypt_Click(object sender, EventArgs e){try{ Configuration confg = WebConfigurationManager.OpenWebConfiguration(Request.ApplicationPath); ConfigurationSection confStrSect = confg.GetSection(section); if (confStrSect != null && confStrSect.SectionInformation.IsProtected) { confStrSect.SectionInformation.UnprotectSection(); confg.Save(); }

}catch (Exception ex){

}}

VB.NET

Private provider As String = "RSAProtectedConfigurationProvider"Private section As String = "connectionStrings"

Protected Sub btnEncrypt_Click(ByVal sender As Object, ByVal e As EventArgs)Try Dim confg As Configuration = WebConfigurationManager.OpenWebConfiguration(Request.ApplicationPath) Dim confStrSect As ConfigurationSection = confg.GetSection(section) If Not confStrSect Is Nothing Then confStrSect.SectionInformation.ProtectSection(provider) confg.Save() End If ' the encrypted section is automatically decrypted!! Response.Write("Configuration Section " & "" & WebConfigurationManager.ConnectionStrings("MyConnString").ConnectionString & "" & " is automatically decrypted")Catch ex As Exception

End TryEnd Sub

Protected Sub btnDecrypt_Click(ByVal sender As Object, ByVal e As EventArgs)Try Dim confg As Configuration = WebConfigurationManager.OpenWebConfiguration(Request.ApplicationPath) Dim confStrSect As ConfigurationSection = confg.GetSection(section) If Not confStrSect Is Nothing AndAlso confStrSect.SectionInformation.IsProtected Then confStrSect.SectionInformation.UnprotectSection() confg.Save() End If

Catch ex As Exception

End TryEnd Sub

In the code above, we open the web.config file as a System.Configuration.Configuration object using the specified virtual path. We then call the GetSection() to retrieve the specified ConfigurationSection object, in our case connectionStrings. The ConfigurationSection.SectionInformation property gets us the SectionInformation object, and then we finally call the ProtectSection() method on the SectionInformation object to mark the section for protection.Similarly while decrypting the section, we call the UnprotectSection() method of the SectionInformation object.Step 4: Now run the application and click on the Encrypt button. Now close the application > Open the web.config file. You will observe that the has been encrypted in the following manner:

Rsa Key ZehN7B+VXBdJTe1X3NFz9Uz3NqxvjSMmbytLeHGNlZa4JkkpRkXzphm5sedHeMTk5KZCHxoYrJ4ssJ0OcZnzLxNUrAB9Ie3y8xJVWJ2s0RQdmaGk5bSJADE1xKJBuOtDIOi/Ron7qJDWXwllC3vvmNwgabmJ9RU+RN35TOQpznc= q2amqNwjeyEbMxF5pZ3XqfboNUJKSml773mPkISGi6uWCWCDPs0ICClmH1eQYcsI9FlxFvEfyRyRRugqOU2xe+gd3aRZEZ5irpGFB45Fn6M+te7kgOeTK1gjGEsbeaNjBNwgpcXMh9RiA9xVOvWlLAyJ3u8DsDQ+4JmM/zTUtxer/8DlUI7+u8D+9V4b5tWxShp4BToMFdTcefhMb19pGdn+jocGetWBJirO5CJsLXI=

Note: If you are running this application from the file system, when you close the application, Visual Studio will display a dialog with the message of “The file has been modified outside the editor. Do you want to reload it?” Click yes and then view the web.config.Step 5: Run the application again and now click on the Decrypt button. You will observe that the section is no longer encrypted and can be read as plain text.Note: Take a note that ‘section groups’ like , etc. cannot be encrypted through programmatically .You can encrypt all the sections of web.config file except following using the method I displayed in this article,



To encrypt these section you needed to use Aspnet_setreg.exe tool