Count String In SQL Server

T-SQL is the language for find data in database. It can insert, search, update and delete all data. We also can join table or find complexly data(data in other table). we also use T-SQL to make application or function to run in database. Today I want to know some knowledge about count string in SQL Server.

Ex: We have string as below

@str='9-9-99-99-999'

We want to count symbol (-) in this string
Please write this statement:

Declare @countStr varchar(100)
Set @countStr=Len(Replace(@str,'9',''))
Print @countStr

It will show result =4

I will have other tips about SQL Server to share. If you have any problem please comment. I will try to help and find the solution for you.

How To Put Symbol Into Table In C#(ASP.Net)

Insert symbol into table in database isn't easy. There are many developer know about it. When I meet this problem I search a long time to find the way for completed this task. Finally I found the solution that very easy.

The solution just add some property in page design as code below:

<@ Page Language="C#" AutoEventWireup="true" CodeBehind="CustomerExtraField.aspx.cs" Inherits="System" ValidateRequest="false" %>

When you add this properties you will can insert symbol in table.

Create Dynamic AJAX Control - Calendar Extender

If you want to create Ajax Control Toolkit- Calendar Extender please follow
code below:

// Create a textbox to hold the date
TextBox dateValue = new TextBox();
dateValue.ID = "dateValue";

// Create the calendar extender
AjaxControlToolkit.CalendarExtender ajaxCalendar =
  new AjaxControlToolkit.CalendarExtender();
ajaxCalendar.ID = "ajaxCalendar";
ajaxCalendar.Format = "MM/dd/yyyy";
ajaxCalendar.TargetControlID = dateValue.ID;

placeHolder1.Controls.Add(dateValue);
placeHolder1.Controls.Add(ajaxCalendar);

In the ASPX, I have just a simple PlaceHolder where I append the dynamically created controls:

<asp:placeholder id="placeHolder1" runat="server">
asp:placeholder>

Please see picture below:

How to Visible Field of GridView in C#

While I develop application using ASP.Net with C#, I want to share small knowledge to visible unnecessary fields in GridView. Sometime you don't want to show some data in interface, but you still can use theirs values. It just high on the form. Please see code below to visible field in gridview:

protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
        e.Row.Cells[1].Visible = false;
}

- e.Row.Cells[1].Visible is the field index that you want to visible.

In page design of GridView:

OnRowDataBound="GridView1_RowDataBound"

When you run code, it will not see field index No1. and you can also add other field in gridview.

How to Convert Text To Number In SQL Server

In Sql Server we can convert text to number by use two properties.
If table column is VARCHAR and has all the numeric values in it, it can be retrieved as Integer using CAST or CONVERT function.

1. CAST

Please see example below:

SELECT CAST(YourVarcharCol AS INT) FROM Table

it mean you can convert text to number by use CAST then select field in table that you want to
select and assign data type.

2. CONVERT

SELECT CONVERT(INT, YourVarcharCol) FROM Table

Two properties have different in used.
so if you want which properties you msut see Syntax clearly.

How to Create Class For Encrypt and Decrypt Password In C#

If you want to Encrypt and Decrypt password when insert into table
you can use code below:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Security.Cryptography;
using System.Configuration;

namespace EnCryptDecrypt
{
class CryptorEngine
{
public static string Encrypt(string toEncrypt, bool useHashing)
{
byte[] keyArray;
byte[] toEncryptArray = UTF8Encoding.UTF8.GetBytes(toEncrypt);

System.Configuration.AppSettingsReader settingsReader = new AppSettingsReader();
string key = (string)settingsReader.GetValue("SecurityKey", typeof(String));

if (useHashing)
{
MD5CryptoServiceProvider hashmd5 = new MD5CryptoServiceProvider();
keyArray = hashmd5.ComputeHash(UTF8Encoding.UTF8.GetBytes(key));
hashmd5.Clear();
}
else
keyArray = UTF8Encoding.UTF8.GetBytes(key);

TripleDESCryptoServiceProvider tdes = new TripleDESCryptoServiceProvider();
tdes.Key = keyArray;
tdes.Mode = CipherMode.ECB;
tdes.Padding = PaddingMode.PKCS7;

ICryptoTransform cTransform = tdes.CreateEncryptor();
byte[] resultArray = cTransform.TransformFinalBlock(toEncryptArray, 0, toEncryptArray.Length);
tdes.Clear();
return Convert.ToBase64String(resultArray, 0, resultArray.Length);
}

public static string Decrypt(string cipherString, bool useHashing)
{
byte[] keyArray;
//byte[] toEncryptArray = Convert.FromBase64String(cipherString); Original coding

byte[] toEncryptArray = Convert.FromBase64String(cipherString);

System.Configuration.AppSettingsReader settingsReader = new AppSettingsReader();
string key = (string)settingsReader.GetValue("SecurityKey", typeof(String));

if (useHashing)
{
MD5CryptoServiceProvider hashmd5 = new MD5CryptoServiceProvider();
keyArray = hashmd5.ComputeHash(UTF8Encoding.UTF8.GetBytes(key));
hashmd5.Clear();
}
else
keyArray = UTF8Encoding.UTF8.GetBytes(key);

TripleDESCryptoServiceProvider tdes = new TripleDESCryptoServiceProvider();
tdes.Key = keyArray;
tdes.Mode = CipherMode.ECB;
tdes.Padding = PaddingMode.PKCS7;

ICryptoTransform cTransform = tdes.CreateDecryptor();
byte[] resultArray = cTransform.TransformFinalBlock(toEncryptArray, 0, toEncryptArray.Length);

tdes.Clear();
return UTF8Encoding.UTF8.GetString(resultArray);
}
}
}

How to call in c#?

string textpassword = txtPassword.Text.Trim();
string cipherText = EnCryptDecrypt.CryptorEngine.Encrypt(textpassword, true);
value of password is cipherText

If it have error please copy code below into Web.config in the