Thursday, May 31, 2012

How to Encrypt and Decrypt web.config using aspnet_regiis


Encrypting and decrypting connection string and the web.config is one of the very important task we have to do when we deploy the application in the server. The main reason and the importance of this task is because once you deploy the application with clear text anyone who has permission to access the server can open your web.config and will be able to see the user id and password used to connect to your database.

There are many ways you can encrypt your web.config. Also there are many algorithms available to encrypt your connection string or web.config elements.

Here we will see a very simple method using aspnet_regiis to encrypt and decrypt connectionString element.

First we will have a look at the connectionStrings in web.config

Encrypt Connection String

Below is the command you will be executing to encrypt the connectionStrings.

C:\Windows\Microsoft.NET\Framework\v4.0.30319>aspnet_regiis -pef connectionStrings "C:\Encrypt"

Encrypt Connection String 1

In the above command,  connectionStrings is the element we are encrypting, you may change this if you have appSettings or any other section you wanted to encrypt. Also the path C:\Encrypt is the path where I kept the web.config file. It should be your application path where your web.config exists.

Now we will see after encryption how our connectionStrings looks like by opening our web.config.

Decrypt ConnectionString

You can see that our encrypt command added many keys and the connectionString is completely encrypted which you can not read at all.

Decrypt web.config

Ok, now we will move on to decrypting part of the same connectionStrings. Advantage of this approach is, you don’t need to write any specific code to decrypt the connection string. When you access the connection string form your code behind you will get the connection string in encrypted format.

Below is the command you will be executing to decrypt the ConnectionSteings in case you wanted to read the data in clear text format.

C:\Windows\Microsoft.NET\Framework\v4.0.30319>aspnet_regiis -pdf connectionStrings "C:\Encrypt"

Decrypt ConnectionString 1

Explanation of the above command is same as the one I have given it for encrypting. Now after executing above command you will see the clear text conectionStrings in your web.config.

I hope you are very clear about the idea and concept regarding encrypt and decrypt connectionString. If you still need some more explanation you can read it form MSDN

Sunday, May 27, 2012

How to find row size of the table in MS SQL


Some times you may required to know each row size in your table. Today I am going to present you a simple query which is helpful to find the row size of your table in MS SQL.

First we will see the design of the table,

CREATE TABLE [dbo].[Customer](
    [id] [int] NOT NULL,
    [name] [nchar](10) NULL,
    [Address] [nvarchar](50) NULL,

Now we will insert some values to the table

INSERT INTO [Customer]([id] ,[name] ,[Address]) VALUES (1 ,'John','Spain')
INSERT INTO [Customer]([id] ,[name] ,[Address]) VALUES (1 ,'James','Wales')
INSERT INTO [Customer]([id] ,[name] ,[Address]) VALUES (1 ,'Michael','Scotland')
INSERT INTO [Customer]([id] ,[name] ,[Address]) VALUES (1 ,'Mark','United Kingdom')

Now we will see the query to get the row size,

row_number() over (order by id desc) as [ROW],
ISNULL(datalength(Address),0) as RowSize
from Customer

Output of the above query will be

ROW    RowSize
1    52
2    40
3    34
4    34


Thursday, May 3, 2012

How to find application path in Console/VB.NET windows application

In ASP.NET web application we normally use Server.MapPath to get the application root folder path. But what we will write in VB.NET windows and console application???

Here is the two lines of code which will help you to get the application path in VB.NET windows and console application.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
namespace ConsoleApplication2
    class TestConsole 
        static void Main(string[] args)
            string strLocation = System.AppDomain.CurrentDomain.BaseDirectory;

Below is the screen print of the above code with output,

Application path in Console application

Wednesday, May 2, 2012

Open MS Word document in web browser


This is one of the common request you will get when you do any file operation in your project. This code can be used to open EXCEL, Power Point, PDF etc.

protected void btnWord_Click(object sender, EventArgs e)
    string strWordFile = @"c:/Test.docx";
    FileInfo objDoc = new FileInfo(strWordFile);
    Response.ContentType = "Application/msword";
    Response.AddHeader("content-disposition", "attachment;filename=" + objDoc.Name);
    Response.AddHeader("Content-Length", objDoc.Length.ToString());
    Response.ContentType = "application/octet-stream";

Tuesday, May 1, 2012

How to add different column values as Tooltip for DropDownList in ASP.NET


Sometimes we may have to show different field from the database as tool tip for the DropDownList in ASP.NET.

Below is a sample code to display the tooltip for DropDownList. Here I have used different field from the database to show as tooltip.

protected void btnLoad_Click(object sender, EventArgs e)
            string strCon = System.Configuration.ConfigurationManager.ConnectionStrings["sqlConnection"].ToString();
            SqlConnection conn = new SqlConnection(strCon);
            SqlDataAdapter da = null;
            DataSet dsCountry = new DataSet();
                string strCountrySQL = "select id, name, Address from dbo.Customer";
                da = new SqlDataAdapter(strCountrySQL, conn);
                DropDownList1.DataSource = dsCountry;
                DropDownList1.DataTextField = "name";
                DropDownList1.DataValueField = "id";
                int ItemCount = DropDownList1.Items.Count;
                for (int i = 0; i < ItemCount; i++)
                    DropDownList1.Items[i].Attributes.Add("Title", dsCountry.Tables[0].Rows[i]["Address"].ToString());
                DropDownList1.Items.Insert(0, new ListItem("--Select--", "--Select--"));
                DropDownList1.SelectedItem.Selected = false;
                DropDownList1.Items.FindByText("--Select--").Selected = true;
            catch (Exception ex)
                lblMsg.Text = "Error!! <br>+" + ex.Message.ToString();

Below is the screen print of the tooltip,