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

Advanced Video Search in You tube

Searching videos on YouTube is simple but if you know a few tricks, it becomes even more fun and accurate. The YouTube team has considerably simplified their video search function making it easier for people to perform advanced searches on YouTube without learning any complicated syntax. Let’s look at some real-world examples:

#1. Find a specific YouTube channel

royal wedding, channelYouTube will have numerous videos related to the royal wedding but if you are only looking for their official YouTube channel, add the word channel to your query separated by a comma.

#2. Limit your search to recently uploaded videos

oprah winfrey, this weekThis will only show recent videos related to Oprah that have been uploaded to YouTube in the past week. You may also use “today” or “this month” to limit your search to videos that were uploaded in the past month.

#3. Find only official videos, no fan material please

never say never, partnerIf you are looking for the ‘official’ Justin Bieber track and don’t want to see any parodies or fan material in the search results, add the word ‘partner’ to your search query.

#4. Find movies on YouTube

jackie chan, movieYes, you can find full-length movies on YouTube (including Bollywood titles). The search operator for finding movies is, you got it right, movie.

#5. Find high-quality videos on YouTube

the king's speech trailer, hdAdd the keyword hd to your search query and YouTube will only show High-definition video clips that are either 720p (1280x720) or 1080p (1920x1080 pixels).

#6. Find 3D video clips on YouTube

avatar, 3dGot a pair of 3D glasses? Add the keyword 3D to your search query and discover 3D videos. You may also enjoy some 3D content on YouTube without the glasses – see details.

#7. Find video playlists on YouTube

bryan adams, playlistPlaylists are a great option if you want to find and watch (or listen) multiple related videos in one go – like all the Old Spice commercials or your favorite Savage Garden tracks. You may also copy YouTube playlists to your own account.

#8. Find lengthy videos on YouTube

tom and jerry cartoons, longAdd the keyword long to your search query and YouTube will only return videos that are at least 20 minutes in length.

#9. Perform exact-match searches on YouTube

allintitle:"google goes gaga"You can borrow the popular allintitle search operator from Google on YouTube to perform exact title matches. Might be useful when the original video is not available in your country due to geographic restrictions.

#10. Mix and Match

ted talks, hd, this month, longThe best part about YouTube search is that you combine multiple search operators with commas and apply them all to your query (much like the Boolean AND operator). The above example will help use find all TED talks that have recently been uploaded to YouTube.

check internet connectivity using c#

using System ;
using System.Runtime ;
using System.Runtime.InteropServices ;
public class InternetClass
{
//Creating the extern function for checking internet connection.
[DllImport("wininet.dll")]
private extern static bool InternetGetConnectedState(out int Description, int ReservedValue) ;
//Creating a function that uses the API function to check internet connection.
public static bool IsConnectedToInternet( )
{
int Desc ;
return InternetGetConnectedState(out Desc, 0) ;
}
}