Create Function [dbo].[SplitString](@String varchar(8000), @Delimiter char(1))
returns @temptable TABLE (items varchar(8000),id int)
as
begin
declare @idx int
declare @idy int
declare @slice varchar(8000)
set @idy=1
select @idx = 1
if len(@String)<1 or @String is null return
while @idx!= 0
begin
set @idx = charindex(@Delimiter,@String)
if @idx!=0
set @slice = left(@String,@idx - 1)
else
set @slice = @String
if(len(@slice)>0)
insert into @temptable(id,Items) values(@idy, @slice)
set @idy=@idy+1
set @String = right(@String,len(@String) - @idx)
if len(@String) = 0 break
end
return
end
Programming is fun and interesting. As you all know, sometime we programmers stucks in the middle way of coding without getting a solution. Blogs like this will help us to find a path to solve our problems. The contents of this blog are the solutions that I found by myself and from other sources. Sharing such solutions is also interesting. Thank you!
Wednesday, March 18, 2015
Split string using delimiter in sql server
Way to find row details of all tables in a DB - SQL
SELECT
t.NAME AS TableName,
i.name as indexName,
p.[Rows],
sum(a.total_pages) as TotalPages,
sum(a.used_pages) as UsedPages,
sum(a.data_pages) as DataPages,
(sum(a.total_pages) * 8) / 1024 as TotalSpaceMB,
(sum(a.used_pages) * 8) / 1024 as UsedSpaceMB,
(sum(a.data_pages) * 8) / 1024 as DataSpaceMB
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
WHERE
t.NAME NOT LIKE 'dt%' AND
i.OBJECT_ID > 255 AND
i.index_id <= 1
GROUP BY
t.NAME, i.object_id, i.index_id, i.name, p.[Rows]
ORDER BY
p.[Rows] desc
Monday, March 16, 2015
Simple two-way encryption for C#
Simple AES
using System;
using System.Data;
using System.Security.Cryptography;
using System.IO;
public class SimpleAES
{
// Change these keys
private byte[] Key = { 123, 217, 19, 11, 24, 26, 85, 45, 114, 184, 27, 162, 37, 112, 222, 209, 241, 24, 175, 144, 173, 53, 196, 29, 24, 26, 17, 218, 131, 236, 53, 209 };
private byte[] Vector = { 146, 64, 191, 111, 23, 3, 113, 119, 231, 121, 2521, 112, 79, 32, 114, 156 };
private ICryptoTransform EncryptorTransform, DecryptorTransform;
private System.Text.UTF8Encoding UTFEncoder;
public SimpleAES()
{
//This is our encryption method
RijndaelManaged rm = new RijndaelManaged();
//Create an encryptor and a decryptor using our encryption method, key, and vector.
EncryptorTransform = rm.CreateEncryptor(this.Key, this.Vector);
DecryptorTransform = rm.CreateDecryptor(this.Key, this.Vector);
//Used to translate bytes to text and vice versa
UTFEncoder = new System.Text.UTF8Encoding();
}
/// -------------- Two Utility Methods (not used but may be useful) -----------
/// Generates an encryption key.
static public byte[] GenerateEncryptionKey()
{
//Generate a Key.
RijndaelManaged rm = new RijndaelManaged();
rm.GenerateKey();
return rm.Key;
}
/// Generates a unique encryption vector
static public byte[] GenerateEncryptionVector()
{
//Generate a Vector
RijndaelManaged rm = new RijndaelManaged();
rm.GenerateIV();
return rm.IV;
}
/// ----------- The commonly used methods ------------------------------
/// Encrypt some text and return a string suitable for passing in a URL.
public string EncryptToString(string TextValue)
{
return ByteArrToString(Encrypt(TextValue));
}
/// Encrypt some text and return an encrypted byte array.
public byte[] Encrypt(string TextValue)
{
//Translates our text value into a byte array.
Byte[] bytes = UTFEncoder.GetBytes(TextValue);
//Used to stream the data in and out of the CryptoStream.
MemoryStream memoryStream = new MemoryStream();
/*
* We will have to write the unencrypted bytes to the stream,
* then read the encrypted result back from the stream.
*/
#region Write the decrypted value to the encryption stream
CryptoStream cs = new CryptoStream(memoryStream, EncryptorTransform, CryptoStreamMode.Write);
cs.Write(bytes, 0, bytes.Length);
cs.FlushFinalBlock();
#endregion
#region Read encrypted value back out of the stream
memoryStream.Position = 0;
byte[] encrypted = new byte[memoryStream.Length];
memoryStream.Read(encrypted, 0, encrypted.Length);
#endregion
//Clean up.
cs.Close();
memoryStream.Close();
return encrypted;
}
/// The other side: Decryption methods
public string DecryptString(string EncryptedString)
{
return Decrypt(StrToByteArray(EncryptedString));
}
/// Decryption when working with byte arrays.
public string Decrypt(byte[] EncryptedValue)
{
#region Write the encrypted value to the decryption stream
MemoryStream encryptedStream = new MemoryStream();
CryptoStream decryptStream = new CryptoStream(encryptedStream, DecryptorTransform, CryptoStreamMode.Write);
decryptStream.Write(EncryptedValue, 0, EncryptedValue.Length);
decryptStream.FlushFinalBlock();
#endregion
#region Read the decrypted value from the stream.
encryptedStream.Position = 0;
Byte[] decryptedBytes = new Byte[encryptedStream.Length];
encryptedStream.Read(decryptedBytes, 0, decryptedBytes.Length);
encryptedStream.Close();
#endregion
return UTFEncoder.GetString(decryptedBytes);
}
/// Convert a string to a byte array. NOTE: Normally we'd create a Byte Array from a string using an ASCII encoding (like so).
// System.Text.ASCIIEncoding encoding = new System.Text.ASCIIEncoding();
// return encoding.GetBytes(str);
// However, this results in character values that cannot be passed in a URL. So, instead, I just
// lay out all of the byte values in a long string of numbers (three per - must pad numbers less than 100).
public byte[] StrToByteArray(string str)
{
if (str.Length == 0)
throw new Exception("Invalid string value in StrToByteArray");
byte val;
byte[] byteArr = new byte[str.Length / 3];
int i = 0;
int j = 0;
do
{
val = byte.Parse(str.Substring(i, 3));
byteArr[j++] = val;
i += 3;
}
while (i < str.Length);
return byteArr;
}
// Same comment as above. Normally the conversion would use an ASCII encoding in the other direction:
// System.Text.ASCIIEncoding enc = new System.Text.ASCIIEncoding();
// return enc.GetString(byteArr);
public string ByteArrToString(byte[] byteArr)
{
byte val;
string tempStr = "";
for (int i = 0; i <= byteArr.GetUpperBound(0); i++)
{
val = byteArr[i];
if (val < (byte)10)
tempStr += "00" + val.ToString();
else if (val < (byte)100)
tempStr += "0" + val.ToString();
else
tempStr += val.ToString();
}
return tempStr;
}
}
AES Encryption / Decryption for JAVA
package org.ferris.aes.crypto;
import java.io.UnsupportedEncodingException;
import java.security.Key;
import java.security.spec.KeySpec;
import javax.crypto.Cipher;
import javax.crypto.SecretKey;
import javax.crypto.SecretKeyFactory;
import javax.crypto.spec.IvParameterSpec;
import javax.crypto.spec.PBEKeySpec;
import javax.crypto.spec.SecretKeySpec;
import org.apache.commons.codec.binary.Base64;
/**
*
*
*/
public class AesBase64Wrapper {
private static String IV = "IV_VALUE_16_BYTE";
private static String PASSWORD = "PASSWORD_VALUE";
private static String SALT = "SALT_VALUE";
public String encryptAndEncode(String raw) {
try {
Cipher c = getCipher(Cipher.ENCRYPT_MODE);
byte[] encryptedVal = c.doFinal(getBytes(raw));
String s = getString(Base64.encodeBase64(encryptedVal));
return s;
} catch (Throwable t) {
throw new RuntimeException(t);
}
}
public String decodeAndDecrypt(String encrypted) throws Exception {
byte[] decodedValue = Base64.decodeBase64(getBytes(encrypted));
Cipher c = getCipher(Cipher.DECRYPT_MODE);
byte[] decValue = c.doFinal(decodedValue);
return new String(decValue);
}
private String getString(byte[] bytes) throws UnsupportedEncodingException {
return new String(bytes, "UTF-8");
}
private byte[] getBytes(String str) throws UnsupportedEncodingException {
return str.getBytes("UTF-8");
}
private Cipher getCipher(int mode) throws Exception {
Cipher c = Cipher.getInstance("AES/CBC/PKCS5Padding");
byte[] iv = getBytes(IV);
c.init(mode, generateKey(), new IvParameterSpec(iv));
return c;
}
private Key generateKey() throws Exception {
SecretKeyFactory factory = SecretKeyFactory.getInstance("PBKDF2WithHmacSHA1");
char[] password = PASSWORD.toCharArray();
byte[] salt = getBytes(SALT);
KeySpec spec = new PBEKeySpec(password, salt, 65536, 128);
SecretKey tmp = factory.generateSecret(spec);
byte[] encoded = tmp.getEncoded();
return new SecretKeySpec(encoded, "AES");
}
}
AES Encryption / Decryption for .NET(C#)
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Security.Cryptography;
namespace EncryptDecryptTest
{
class Program
{
class AesBase64Wrapper
{
private static string IV = "IV_VALUE_16_BYTE";
private static string PASSWORD = "PASSWORD_VALUE";
private static string SALT = "SALT_VALUE";
public static string EncryptAndEncode(string raw)
{
using (var csp = new AesCryptoServiceProvider())
{
ICryptoTransform e = GetCryptoTransform(csp, true);
byte[] inputBuffer = Encoding.UTF8.GetBytes(raw);
byte[] output = e.TransformFinalBlock(inputBuffer, 0, inputBuffer.Length);
string encrypted = Convert.ToBase64String(output);
return encrypted;
}
}
public static string DecodeAndDecrypt(string encrypted)
{
using (var csp = new AesCryptoServiceProvider())
{
var d = GetCryptoTransform(csp, false);
byte[] output = Convert.FromBase64String(encrypted);
byte[] decryptedOutput = d.TransformFinalBlock(output, 0, output.Length);
string decypted = Encoding.UTF8.GetString(decryptedOutput);
return decypted;
}
}
private static ICryptoTransform GetCryptoTransform(AesCryptoServiceProvider csp, bool encrypting)
{
csp.Mode = CipherMode.CBC;
csp.Padding = PaddingMode.PKCS7;
var spec = new Rfc2898DeriveBytes(Encoding.UTF8.GetBytes(PASSWORD), Encoding.UTF8.GetBytes(SALT), 65536);
byte[] key = spec.GetBytes(16);
csp.IV = Encoding.UTF8.GetBytes(IV);
csp.Key = key;
if (encrypting)
{
return csp.CreateEncryptor();
}
return csp.CreateDecryptor();
}
}
static void Main(string[] args)
{
string encryptMe;
string encrypted;
string decrypted;
encryptMe = "please encrypt me";
Console.WriteLine("encryptMe = " + encryptMe);
encrypted = AesBase64Wrapper.EncryptAndEncode(encryptMe);
Console.WriteLine("encypted: " + encrypted);
decrypted = AesBase64Wrapper.DecodeAndDecrypt(encrypted);
Console.WriteLine("decrypted: " + decrypted);
Console.WriteLine("press any key to exit....");
Console.ReadKey();
}
}
}
Subscribe to:
Posts (Atom)