SQL Server: Converting Column type from INT to BIT

The following query creates a temporary column (INT) copies the data to it and recreates the original column as BIT and copies the data back.

Please note that column order will not be retained. If you need the same column order, go to Design view of the table and drag the column to the location you want and save.

Change the value in variable @name and @tableName. The query takes care of the rest.

DECLARE @name NVARCHAR(255), @tempName NVARCHAR(255), 
        @tableName NVARCHAR(255), @sql NVARCHAR(max);

SET @name = 'ColumnName';
SET @tempName = @name +'1';
SET @tableName = 'TableName';

--SELECT @name, @tempName, @tableName

-- create new temp column
SET @SQL = 'ALTER TABLE [dbo].[' + @tableName + '] ADD ' + @tempName + ' INT';
EXEC sp_executesql @sql


--copy data in temp column
SET @SQL = 'UPDATE [dbo].[' + @tableName + '] SET ' + @tempName + ' = ' + @name;
EXEC sp_executesql @sql

--drop column which you want to modify
SET @SQL = 'ALTER TABLE [dbo].[' + @tableName + '] DROP COLUMN ' + @name;
EXEC sp_executesql @sql

--create again that column with bit type
SET @SQL = 'ALTER TABLE [dbo].[' + @tableName + '] ADD ' + @name +' BIT';
EXEC sp_executesql @sql

--copy data back
SET @SQL = 'UPDATE [dbo].[' + @tableName + '] SET ' + @name + ' = ' + @tempName;
EXEC sp_executesql @sql

--drop temp column
SET @SQL = 'ALTER TABLE [dbo].[' + @tableName + '] DROP COLUMN ' + @tempName;
EXEC sp_executesql @sql

Encrypt in SQL Server and Decrypt in C#

Recently, I had a requirement to fetch the encrypted string passed from SQL Server using function EncryptByPassPhrase (more details about this function here). This encrypted string was passed from a totally different setup to my ASP.Net web application.

My ASP.Net web Application required the decoded text. To do this I had to create a stored procedure which accepted the pass phrase and encrypted string as input and returned the decrypted string. The encrypted string type was VARBINARY. The stored procedure was:

CREATE PROCEDURE [dbo].sp_DecryptText 
    (@PassPhrase NVARCHAR(128), 
    @EncryptedText VARBINARY(MAX),
    @DecryptedText NVARCHAR(MAX) OUTPUT)
AS
BEGIN
    SET @DecryptedText = DECRYPTBYPASSPHRASE(@PassPhrase, @EncryptedText);
    RETURN
END

So I wrote a C# code to connect to stored procedure passing all the values as:

command.Parameters.AddWithValue("@PassPhrase", PassPhrase);
command.Parameters.Add("@EncryptedText", SqlDbType.VarBinary, 8000).Value = Encoding.Unicode.GetBytes(encryptedString);
command.Parameters.Add("@DecryptedText", SqlDbType.NVarChar, -1).Direction = ParameterDirection.Output;

But this did not work as the encryptedString was already in hexadecimal format and converting it to bytes resulted in a totally different string.

To get the stored procedure working, I converted the encryptedString to hexadecimal before passing it to the stored procedure by writing a separate function ParseHexString as:

static byte[] ParseHexString(string value)
{
    if (string.IsNullOrEmpty(value)) return null;
    if (1 == (1 & value.Length)) throw new ArgumentException("Invalid length for a hex string.", "value");

    int startIndex = 0;
    int length = value.Length;
    char[] input = value.ToCharArray();
    if ('0' == input[0] && 'x' == input[1])
    {
        if (2 == length) return null;
        startIndex = 2;
        length -= 2;
    }

    Func<char, byte> charToWord = c =>
    {
        if ('0' <= c && c <= '9') return (byte)(c - '0');
        if ('A' <= c && c <= 'F') return (byte)(10 + c - 'A');
        if ('a' <= c && c <= 'f') return (byte)(10 + c - 'a');
        throw new ArgumentException("Invalid character for a hex string.", "value");
    };

    byte[] result = new byte[length >> 1];
    for (int index = 0, i = startIndex; index < result.Length; index++, i += 2)
    {
        byte w1 = charToWord(input[i]);
        byte w2 = charToWord(input[i + 1]);
        result[index] = (byte)((w1 << 4) + w2);
    }

    return result;
}

So my call to stored procedure changed to:

command.Parameters.AddWithValue("@PassPhrase", PassPhrase);
command.Parameters.Add("@EncryptedText", SqlDbType.VarBinary, 8000).Value = ParseHexString(encryptedString);
command.Parameters.Add("@DecryptedText", SqlDbType.NVarChar, -1).Direction = ParameterDirection.Output;

And the output was correct and in the right format!!