how to encrypt the data to insert into sql database and also decrypt the data from the database

Question

DataSet ds = new DataSet();

string sql = "select Count,UserType,UserName,Password from Users where UserName = '" + txtUserName.Text + "'";
SqlDataReader dr = conn.SqlReaderQuery(sql);
try
{
if (!dr.Read() || dr["Password"].ToString() != txtPwd.Text)
{
MessageBox.Show("Enter Correct Username/Password");
txtPwd.Text = "";
txtUserName.Text = "";
txtUserName.Focus();
}
else
{
int Count = Convert.ToInt16(dr["Count"].ToString());

string usertype = "";
usertype = dr["UserName"].ToString();
if (usertype == txtUserName.Text)
{
if (Count < 10)
{
Count++;

string ad = "";
ad = dr["UserType"].ToString();
if (ad == "Administrator")
{

frmMain fm = new frmMain();
fm.Show();
this.Hide();
string Update = "update Users set Count='" + Count + "'where UserName='" + txtUserName.Text + "'and Password='" + txtPwd.Text + "'";
string HUpdate = "update Users set Count='" + Count + "'where UserType='HumanResource'";
dbConnection.ExecuteSQL(Update);
dbConnection.ExecuteSQL(HUpdate);
MessageBox.Show(Count.ToString());
}
else if (ad == "HumanResource")
{

frmHRMain hrm = new frmHRMain();
hrm.Show();
this.Hide();
string Update = "update Users set Count='" + Count + "'where UserName='" + txtUserName.Text + "'and Password='" + txtPwd.Text + "'";
string HrUpdate = "update Users set Count='" + Count + "'where UserType='Administrator'";
dbConnection.ExecuteSQL(Update);
dbConnection.ExecuteSQL(HrUpdate);
MessageBox.Show(Count.ToString());
}

}

else if (Count >= 10)
{
// MessageBox.Show("Your Demo Version is Expired Please contact to Adminstrator");
MessageBox.Show("Your Login Expired Please contact to Adminstrator", Count.ToString());
// MessageBox.Show("Your Login Expired Please contact to Adminstrator", "Invalid Login", Count.ToString(), MessageBoxButtons.YesNo, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1, MessageBoxOptions.LeftAlign, "http://csofttechnologies.in/Contact.html", "Please Buy a registre");
}
}
}
}

catch (Exception ex)
{
MessageBox.Show(ex.Message);
frmDbPath db = new frmDbPath();
db.ShowDialog();
}
finally
{
//dr.Close();
}
dr.Close();

// my question is how to encrypt the data is insert into sql database and also decrypt the data from the database

Atul Kumar · Answer

Creating master key USE Encryptdb GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MyPassword' GO Create Certificate USE Encryptdb GO CREATE CERTIFICATE EncryptCert WITH SUBJECT = ' MySubject ' GO Create Symmetric Key USE Encryptdb GO CREATE SYMMETRIC KEY MyKey WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY CERTIFICATE EncryptCert GO Finally Encrypt Data USE Encryptdb GO OPEN SYMMETRIC KEY MyKey DECRYPTION BY CERTIFICATE EncryptCert UPDATE My Table SET MyCol = ENCRYPTBYKEY ( KEY_GUID ( 'MyKey' ) SecondCol ) GO

naga jyothi · Answer

Thank u very much give one example of sql queries

Atul Kumar · Answer

You can use built in functions of database. There are some steps you have to go through if you want proper encryption of data. 1. First create master key of database with secure password. 2. Create certificate with that master key. 3. Create new sym or asym key with generate certificate and secure password. 4. finally encrypt data with proper algorithm of your choice with the help of generated key.