Pranav's Blog

Getting Request.ServerVariables from asp.net

Here is the method of getting all server variables in asp.net from Request.ServerVariables

Response.Write(“<table border=’1′>”);
for (int i = 0; i < Request.ServerVariables.Count; i++)
{
string key = Request.ServerVariables.Keys[i];
Response.Write(
string.Format(“<tr><td style=’border:1px solid;’>{0}</td><td>{1}</td>

“);

This will list all the server variables in the page in table format…

Enjoy… 🙂

August 12, 2011 at 4:28 pm Leave a comment

How to connect sqlserver from command prompt using sqlcmd / bulk insert

Hi all,

I need to insert large amount of records into sql server. The script is ready in file. The insert query is ready but the record is around 15 Lacs.

SQL server gets time out every time I run the file from “SQL Server Management Studio”.

Then I thought to do it from command prompt (whether anything can be done using cmd) as in my previous article I have used to replace text file from command prompt.

Then I came to know that you can do everything in sql server using command prompt. The main thing is you need to go to below path in command prompt. C:\Program Files\Microsoft SQL Server\100\Tools\Binn.
You just need to use below commands to connect to sql server using cmd.

sqlcmd -S lpc:ComputerName\<instancename> -U username -P password

Remember that, -S, -U -P are all case sensitive. Now you write any query but to fire the query you need to type go and then enter.

e.g. once you have typed above command correct, you’ll see
1> in your command prompt. You can write below thing.

1> select * from tbl1 then enter and then type Go then enter… it’ll show you result.

But, I need to run the script file so to run the file use below command.

sqlcmd -S lpc:ComputerName\<instancename> -U username -P password -i <MyScript.sql>

This will run this script file and give you output in command window. Suppose you need to get output in different file then you can use below command.

sqlcmd -S lpc:ComputerName\<instancename> -U username -P password -i <MyScript.sql> -o <MyOutput.txt>

If you want to know all the things of sqlcmd then use below command. It’ll give you list of all the tags.

sqlcmd -?

Again I came into another problem. I need to insert 15 Lac record and the script will not allow to run 1000 record per query. Now I am hope less with the command prompt as well 😦

Then I came to know about bulk insert. I have another file with .dat extension and that file contains records like below

1;xyz;test
2;abc;temp

Create one table in sql server Table1.

Create table Table1(id bigint, name varchar(50), column1 varchar(100))

Run below query for bulk insert

BULK INSERT Table1
FROM ‘C:\filename.dat’
WITH
(
FIELDTERMINATOR =’;’,
ROWTERMINATOR = ‘\n’
)

And it worked very well. By this way it has inserted all 15 Lac records in just 30 sec into database.

Happy Programming 🙂

August 3, 2011 at 5:28 pm 2 comments

How to find and replace text in large file

Hey folks,

Back after a looooong break.

This time I would like to replace text in a file. Hmm…

You might wonder that it’s so simple, Open file press ctrl + F and replace from there. But here the file is very big in size. It’s 300 MB File.

I’ve tried different editor to replace it but I’m failed and hopeless. I thought I need to replace it one by one.

Then one of my friend told me to try command line argument through cmd and I have surfed for that and I found below article.
http://blogs.technet.com/b/heyscriptingguy/archive/2005/02/08/how-can-i-find-and-replace-text-in-a-text-file.aspx

Here you need to create one VB Script (.vbs) file and put these text inside it. let’s say the name of the file is replace.vbs.

Const ForReading = 1
Const ForWriting = 2

strFileName = Wscript.Arguments(0)
strOldText = Wscript.Arguments(1)
strNewText = Wscript.Arguments(2)

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile(strFileName, ForReading)

strText = objFile.ReadAll
objFile.Close
strNewText = Replace(strText, strOldText, strNewText)

Set objFile = objFSO.OpenTextFile(strFileName, ForWriting)
objFile.WriteLine strNewText
objFile.Close

After creating file you need to fire below command from command prompt.
cscript replace.vbs “C:\Scripts\Text.txt” “test ” “temp ”

Remember, you need to fire this command from the same drive/directory where your replace.vbs is.
But In my case this trick doesn’t work because I need to replace double quote (“) into single quote(‘) and I have tried different solution but it didn’t work.
Finally I thought to change the replace.vbs file. and I have do below change in file.

Const ForReading = 1
Const ForWriting = 2

strFileName = Wscript.Arguments(0)
'strOldText = Wscript.Arguments(1)
'strNewText = Wscript.Arguments(2)

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile(strFileName, ForReading)

strText = objFile.ReadAll
objFile.Close
strNewText = Replace(strText, """", "'")

Set objFile = objFSO.OpenTextFile(strFileName, ForWriting)
objFile.WriteLine strNewText
objFile.Close

and then I’ve run below command.

cscript replace.vbs "C:\Scripts\Text.txt"

its done the trick in just 3 sec. Hurray 🙂 …

August 3, 2011 at 4:36 pm 2 comments

User defined function to strip html in sql server 2005/2008

I need to strip html at many places in my project, I can do that by JavaScript function but I want some more common mechanism to strip html codes while getting the data from database. So I thought of using striping html @ database level and I searched on the net and here is the function for striping html.

CREATE FUNCTION [dbo].[udf_StripHTML] (@HTMLText VARCHAR(MAX)) RETURNS VARCHAR(MAX) AS BEGIN DECLARE @Start INT DECLARE @End INT DECLARE @Length INT SET @Start = CHARINDEX('<',@HTMLText) SET @End = CHARINDEX('>',@HTMLText,CHARINDEX('<',@HTMLText)) SET @Length = (@End - @Start) + 1 WHILE @Start > 0 AND @End > 0 AND @Length > 0 BEGIN SET @HTMLText = STUFF(@HTMLText,@Start,@Length,'') SET @Start = CHARINDEX('<',@HTMLText) SET @End = CHARINDEX('>',@HTMLText,CHARINDEX('<',@HTMLText)) SET @Length = (@End - @Start) + 1 END RETURN LTRIM(RTRIM(@HTMLText)) END GO

Above function takes string as a parameter which contains html data and it will return string without html part.

Example:
Select dbo.udf_StripHTML('<br> This is <hr> <html> Pranav''s Blog </html>') and it will return below string-> This is Pranav's Blog

Happy Programming 🙂

June 9, 2010 at 10:44 am Leave a comment

How to expire(remove) page caching in ASP.Net

Hey Folks,

Today I have faced one problem in my live website. It was showing old contents though I have changed the content in the web page. Then as I press F5 or Ctrl+F5 it will show me the right content. I have removed my cache from the browser but not succeed.

Then I have written below code in page level in Page_Load event, and then my page worked live never before. Here is the code:

VB:
‘Remove Page Caching…
Response.Expires = -1
Response.ExpiresAbsolute = Now()
Response.CacheControl = “no-cache”

C#:
//Remove Page Caching…
Response.Expires = -1;
Response.ExpiresAbsolute = DateTime.Now;
Response.CacheControl = “no-cache”;

Hurreeyyy !!!

June 7, 2010 at 4:25 pm 1 comment

COALESCE with DISTINCT in SQLServer

If you want comma separated values from columns it is so simple. Use COALESCE function provided by SQL Server.

DECLARE @csv varchar(max)
select @csv = COALESCE(@csv + ',','') + cast(id AS VARCHAR) from tablename
print @csv

COALESCE with DISTINCT Values

If you want, DISTINCT values in COALESCE function use inner table like below.

DECLARE @csv varchar(max)
select @csv = COALESCE(@csv + ',','') + cast(id AS VARCHAR) from
(SELECT DISTINCT id FROM TableName)
print @csv

Happy Programming 😉

June 3, 2010 at 11:58 am 8 comments

Decode html through javascript

I have come across a problem where I want to use xml file into javascript and that time I need to decode html code so that it works smooth e.g. “&” should rendered as “&” and “‘” should rendered as single quote (‘). Here is the function for what I need

Happy now 🙂

June 3, 2010 at 11:16 am 1 comment

Convert String Array to Long Array

Hey folks,

As I told you I am back within no time.

Yesterday, I have faced a problem where I want to convert string array into long array. I need to search a lot in internet for solving this and after a lot finding I came to this simple solution.

string[] strArray = { “xyz”, “abc”, “def” };
Long[] lArray = Array.ConvertAll<string, long>(strArray, Convert.ToInt64);

it’s so simple, cheers 🙂

June 3, 2010 at 11:08 am 1 comment

The selected class cannot be deleted because it is used as the return type for one or more DataContext methods.

I am back after a looooooooong time.

Today I have faced one problem in dbml file.

As soon as I tried to delete the table from dbml file it gave me below message:
The selected class cannot be deleted because it is used as the return type for one or more DataContext methods.

Any guesses for the error ? 🙂

No…

No problem, here is the solution.
The reason of this error is, the table which I was trying to delete is used as a return type of one of the stored procedure in the dbml file.
So, to solve this problem, first delete all the SP in which this table is used as a return type and then delete the table. Now again drag & drop all the Table and all the stored procedure.

Don’t forget to change return type of SP to the table as this might give you error.

Cheers……… 🙂

June 1, 2010 at 7:22 pm 1 comment

New feature of Visual Studio 2008

Hi Friends, This is my first post in my blog.

I am using Visual Studio 2008 for a month and I was just searching for any new features in it. I have just checked a great feature in Visual Studio. Debug in source files. Yes, my friend, what you have heard is true. Debug in source code.

You just need to set few settings and you can debug how your grid is bind or how DataAdapter.Fill() method works or anything you want to debug. Click Here for the exact steps on how to enable source-code debugging.
For debugging source code, one must have VS-2008 Professional Edition

June 4, 2008 at 10:42 am 2 comments

Pranav’s Blog