Converting records into comma separated values

MS-SQL Converting list of records into CSV

CREATE TABLE TestTable
(
	ID INT IDENTITY(1,1) PRIMARY KEY,
	[NAME] VARCHAR(50)
)
GO
INSERT INTO TestTable VALUES ('1')
INSERT INTO TestTable VALUES ('2')
INSERT INTO TestTable VALUES ('3')
INSERT INTO TestTable VALUES ('4')
INSERT INTO TestTable VALUES ('5')
INSERT INTO TestTable VALUES ('6')
INSERT INTO TestTable VALUES ('7')
GO
SELECT substring((SELECT ( ', ' + [Name] ) FROM TestTable FOR XML PATH( '' )), 3, 1000 )

Output:

1, 2, 3, 4, 5, 6, 7

Integrating jQuery UI Autocomplete in ASP.NET web application

Import jquery and jQueryUI and related CSS

bind autocomplete with asp.net textbox control

$(document).ready(function () {
            $("#<%=txtAutoComplete.ClientID %>").autocomplete({
                source: function (request, response) {
                    $.ajax({
                        url: "webservice/TestService.asmx/SearchData",
                        data: "{ 'q': '" + request.term + "', 'limit': '10' }",
                        dataType: "json",
                        type: "POST",
                        contentType: "application/json; charset=utf-8",
                        dataFilter: function (data) { return data; },
                        success: function (data) {
                            response($.map(data.d, function (item) {
                                return {
                                    label: item.Name,
                                    value: item.id + ""
                                }
                            }))
                        }
                    });
                }
            });
});

Declare web method inside web service

[System.Web.Services.WebMethodAttribute(), System.Web.Script.Services.ScriptMethodAttribute()]
        public List SearchData(string q, int limit)
        {
            return new List { new tdata { id = 0, name = "nitin" } };
        }

        public class tdata
        {
            public string name { get; set; }
            public int id { get; set; }
        }

SQL Function to remove numbers from a varchar

SQL Function to remove numbers from a varchar database field

CREATE FUNCTION dbo.RemoveSpecialChars (@s VARCHAR(MAX))
RETURNS VARCHAR(MAX)
WITH schemabinding
BEGIN
IF @s is null
RETURN null
DECLARE @s2 VARCHAR(MAX)
SET @s2 = ''
DECLARE @l int
SET @l = len(@s)
DECLARE @p int
SET @p = 1
WHILE @p <= @l BEGIN
DECLARE @c int
SET @c = ascii(substring(@s, @p, 1))
IF not @c between 48 and 57
SET @s2 = @s2 + char(@c)
SET @p = @p + 1
END
IF len(@s2) = 0
RETURN null
RETURN @s2
END

usage:

select dbo.RemoveSpecialChars('Test-123String')

output:

Test-String

Change ownership of sql database

SQL query to change ownership of sql database:

SQL Statement:
ALTER AUTHORIZATION ON DATABASE::{databasename} TO {username}

Make copy of an object & its properties

Deep copy of object:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.IO;
using System.Runtime.Serialization.Formatters.Binary;

///
/// Summary description for CopyObject
///
public class myObject
{
	public myObject()
	{
		//
		// TODO: Add constructor logic here
		//
	}

    public static object Copy(object objAnyTypeOfObject)
    {
        BinaryFormatter binFormater = new BinaryFormatter();
        MemoryStream memStream = new MemoryStream();
        binFormater.Serialize(memStream, objAnyTypeOfObject);
        //Reset the posotion to initial so that it can read the bytes
        memStream.Position = 0;
        return binFormater.Deserialize(memStream);
    }
}

Get Max Request length from web.config

From msdn:

The MaxRequestLength property specifies the limit for the buffering threshold of the input stream. For example, this limit can be used to prevent denial of service attacks that are caused by users who post large files to the server.

For retrieving maxrequest length from web.config use following function:

public int GetMaxLength()
{
        HttpRuntimeSection httpRuntime =
(HttpRuntimeSection)ConfigurationManager.GetSection("system.web/httpRuntime");
        return httpRuntime.MaxRequestLength;
}

For retrieving maxrequest length from web.config use following function:

public int GetMaxLength()
{
        HttpRuntimeSection httpRuntime =
(HttpRuntimeSection)ConfigurationManager.GetSection("system.web/httpRuntime");
        return httpRuntime.MaxRequestLength;
}

SQL script to backup and restore DB

For issue solving purpose frequently I take backups of live SQL database to our development server,
at start I was using the wizard in the SQL management studio to take database backups,
I wanted to do all those things with single click,
I came accross this gr8 blog post,
I modded the script to automatically backup source database(@sourcedatabaseName) and restore it into destination database(@databaseName):

-----------------------------------------------------------------
-- *****    Restore Database Script - Mod by Nitin Sawant   *****
--
-- Modify only three variables:
-- 1. @sourcedatabaseName
-- 2. @databaseName
-- 3. @restoreDirectory
--
-- initial version: http://blog.tech-cats.com/2007/10/sql-server-script-to-restore-database.html
-----------------------------------------------------------------
use master
go

declare @backupFileName varchar(100), @restoreDirectory varchar(100),
 @databaseDataFilename varchar(100), @databaseLogFilename varchar(100),
 @databaseDataFile varchar(100), @databaseLogFile varchar(100),
 @databaseName varchar(100), @execSql nvarchar(1000), @sourcedatabaseName nvarchar(1000)
-----------------------------------------------------------------
-- Set the name of the database to restore
set @sourcedatabaseName = 'LIVE_DB'--Source database name
set @databaseName = 'TESTING_DB'--Destination database name
set @restoreDirectory = 'D:\' -- such as 'c:\temp\', the directory where the .bak of source DB will be created
-----------------------------------------------------------------

--Take a Fresh DB backup
DECLARE @Dt DATETIME
SET @Dt = GETDATE()
DECLARE @BkAddress NVARCHAR(1000)
--the database name and today's date
set @backupFileName = @restoreDirectory + @sourcedatabaseName + '_'+DATENAME ( day , @Dt )+''+LEFT(DATENAME ( month , @Dt ),3)+''+DATENAME ( year , @Dt )+''+REPLACE(RIGHT(CONVERT(VARCHAR, GETDATE(), 100),7),':','.')+'-'+DATENAME ( s , @Dt )+'.bak'
PRINT 'Creating backup of the database at:' + @BkAddress
EXEC('Backup Database '+@sourcedatabaseName+' To Disk ='''+ @backupFileName+'''')

-- Get the data file and its path
select @databaseDataFile = rtrim([Name]),
 @databaseDataFilename = rtrim([Filename])
from master.dbo.sysaltfiles as files
 inner join
 master.dbo.sysfilegroups as groups
 on
 files.groupID = groups.groupID
where DBID = (
  select dbid
  from master.dbo.sysdatabases
  where [Name] = @databaseName
 )

-- Get the log file and its path
select @databaseLogFile = rtrim([Name]),
 @databaseLogFilename = rtrim([Filename])
from master.dbo.sysaltfiles as files
where DBID = (
  select dbid
  from master.dbo.sysdatabases
  where [Name] = @databaseName
 )
 and
 groupID = 0

print 'Killing active connections to the "' + @databaseName + '" database'

-- Create the sql to kill the active database connections
set @execSql = ''
select @execSql = @execSql + 'kill ' + convert(char(10), spid) + ' '
from master.dbo.sysprocesses
where db_name(dbid) = @databaseName
 and
 DBID <> 0
 and
 spid <> @@spid
exec (@execSql)

print 'Restoring "' + @databaseName + '" database from "' + @backupFileName + '" with '
print '  data file "' + @databaseDataFile + '" located at "' + @databaseDataFilename + '"'
print '  log file "' + @databaseLogFile + '" located at "' + @databaseLogFilename + '"'

set @execSql = '
restore database [' + @databaseName + ']
from disk = ''' + @backupFileName + '''
with
  file = 1,
  move ''' + @databaseDataFile + ''' to ' + '''' + @databaseDataFilename + ''',
  move ''' + @databaseLogFile + ''' to ' + '''' + @databaseLogFilename + ''',
  norewind,
  nounload,
  replace'

exec sp_executesql @execSql

exec('use ' + @databaseName)
go

-- If needed, restore the database user associated with the database
/*
exec sp_revokedbaccess 'myDBUser'
go

exec sp_grantdbaccess 'myDBUser', 'myDBUser'
go

exec sp_addrolemember 'db_owner', 'myDBUser'
go

use master
go
*/

Please note that this script works only if the two databases are hosted on same server.

You can download the .sql file of the script: RestoreDatabase.sql

Init

This is Initial post....

public class Hello
{
	public void SayHello()
	{
		Console.WriteLine("Hello World");
	}
}
void Main()
{
	Hello obj = new Hello();
	obj.SayHello();
}