Find all Primary key in Table/Database

SELECT t.table_schema AS PrimarySchemaName ,
t.TABLE_NAME AS PrimaryKeyTable,
tc.CONSTRAINT_NAME AS PrimaryKey,
COALESCE(tc2.constraint_schema,'N/A') AS ForeignSchemaName,
COALESCE(rc1.CONSTRAINT_NAME,'N/A') AS ForeignKey ,
COALESCE(tc2.TABLE_NAME,'N/A') AS ForeignKeyTable
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
INNER JOIN INFORMATION_SCHEMA.TABLES t ON tc.TABLE_NAME = t.TABLE_NAME
LEFT JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc1 ON tc.CONSTRAINT_NAME =rc1.UNIQUE_CONSTRAINT_NAME
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc2 ON tc2.CONSTRAINT_NAME =rc1.CONSTRAINT_NAME
WHERE TC.CONSTRAINT_TYPE ='PRIMARY KEY'
ORDER BY tc.TABLE_NAME,tc.CONSTRAINT_NAME,rc1.CONSTRAINT_NAME

It will Return all the Primary Key of every Table with their table name

Input only Number in TextBox in C#

IsDigit


IsHighSurrogate

IsLetter

IsLetterOrDigit

IsLower

IsLowSurrogate

IsNumber

IsPunctuation

IsSeparator

IsSurrogate

IsSurrogatePair

IsSymbol

IsUpper

IsWhiteSpace



private void textBox1_KeyPress(object sender, KeyPressEventArgs e)

{

if (Char.IsDigit(e.KeyChar) )

{ MessageBox.Show("Bad Input");

e.Handled = true; ;

}}

// It will Input Only Digit in TextBox
            if ((e.KeyChar < '0') || (e.KeyChar > '9'))
            {
                e.Handled = true;
            }
          
            // It will Input Only Alphabet and Digit in TextBox nothing else
            if (!Char.IsLetterOrDigit(e.KeyChar))
            {
                MessageBox.Show("Bad Input");
                e.Handled = true; ;
            }

Encrypt Decrypt Password in C#

insert into Emp(name,age) values(EncryptByPassPhrase('20','aarti'),50)


select id, DECRYPTBYPASSPHRASE('20',name) "Name" from Emp

Procedure in SQL Server

Create PROCEDURE ins


@name varchar(50),

@age float

as

insert into Emp(name,age) values(@name,@age)



Execute Procedure



ins 'Raj',22

Insert Update Delete Through Linq in C#

take a Window Form Application


add a Class LINQ to SQL Classes named DataClasses1.dbml

now Double Click on DataClasses1.dbml it Design View will be open

now on it Give Database Conection and drag Table on Designer



Now make a object of DataClasses1DataContext Class and access all Clases



using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Windows.Forms;

using System.Data.Linq;



namespace LinqImplementation

{

public partial class Form1 : Form

{

DataClasses1DataContext dc1 = null;

string ss = null;

public Form1()

{

InitializeComponent();

ss = "Data Source=(local);Initial Catalog=EMP;Integrated Security=SSPI;";

dc1 = new DataClasses1DataContext(ss);

}



private void btnSelect_Click(object sender, EventArgs e)

{



var q = from a in dc1.GetTable()

where a.ClientName.Contains("aj")

a.Salary == 20000

select a;



dataGridView1.DataSource = q;



}



private void btnSave_Click(object sender, EventArgs e)

{

try

{

Table XPCClient = dc1.GetTable();

Client cl = new Client();

cl.Oid = Guid.NewGuid();

cl.ClientName = txtclientname.Text;

cl.Salary = int.Parse(txtsalary.Text);

cl.SEX = cmbsex.SelectedIndex;

XPCClient.InsertOnSubmit(cl);

XPCClient.Context.SubmitChanges();

MessageBox.Show("Record Inserted Successfully");

}

catch (Exception ex)

{

MessageBox.Show(ex.Message+"Record Insertion Failed");

}

}



private void btnDelete_Click(object sender, EventArgs e)

{

Table XpCClient = dc1.GetTable();

var obj = (from w in XpCClient

where w.Salary == 12000

select w).FirstOrDefault();



var obj1 = XpCClient.Where(a => a.Salary == 20000).FirstOrDefault();

XpCClient.DeleteOnSubmit(obj);

XpCClient.Context.SubmitChanges();

}



private void btnUpdate_Click(object sender, EventArgs e)

{

Table XPCClient = dc1.GetTable();

var obj = from a in XPCClient

where a.Salary == Convert.ToInt32(txtsalary.Text)

select a;

Client obj1 = (from ab in XPCClient

where ab.Salary == Convert.ToInt32(txtsalary.Text)

select ab).FirstOrDefault();



if (obj == null)

MessageBox.Show("Record Not Found");

else

{

foreach (Client c in obj)

{

c.Salary = 55000;

dc1.SubmitChanges();

}

}

}

}

}



Note : Client is a Table in EMP Database

Insert Update Delete Through Linq in C#

take a Window Form Application


add a Class LINQ to SQL Classes named DataClasses1.dbml

now Double Click on DataClasses1.dbml it Design View will be open

now on it Give Database Conection and drag Table on Designer



Now make a object of DataClasses1DataContext Class and access all Clases



using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Windows.Forms;

using System.Data.Linq;



namespace LinqImplementation

{

public partial class Form1 : Form

{

DataClasses1DataContext dc1 = null;

string ss = null;

public Form1()

{

InitializeComponent();

ss = "Data Source=(local);Initial Catalog=EMP;Integrated Security=SSPI;";

dc1 = new DataClasses1DataContext(ss);

}



private void btnSelect_Click(object sender, EventArgs e)

{



var q = from a in dc1.GetTable()

where a.ClientName.Contains("aj")

a.Salary == 20000

select a;



dataGridView1.DataSource = q;



}



private void btnSave_Click(object sender, EventArgs e)

{

try

{

Table XPCClient = dc1.GetTable();

Client cl = new Client();

cl.Oid = Guid.NewGuid();

cl.ClientName = txtclientname.Text;

cl.Salary = int.Parse(txtsalary.Text);

cl.SEX = cmbsex.SelectedIndex;

XPCClient.InsertOnSubmit(cl);

XPCClient.Context.SubmitChanges();

MessageBox.Show("Record Inserted Successfully");

}

catch (Exception ex)

{

MessageBox.Show(ex.Message+"Record Insertion Failed");

}

}



private void btnDelete_Click(object sender, EventArgs e)

{

Table XpCClient = dc1.GetTable();

var obj = (from w in XpCClient

where w.Salary == 12000

select w).FirstOrDefault();



var obj1 = XpCClient.Where(a => a.Salary == 20000).FirstOrDefault();

XpCClient.DeleteOnSubmit(obj);

XpCClient.Context.SubmitChanges();

}



private void btnUpdate_Click(object sender, EventArgs e)

{

Table XPCClient = dc1.GetTable();

var obj = from a in XPCClient

where a.Salary == Convert.ToInt32(txtsalary.Text)

select a;

Client obj1 = (from ab in XPCClient

where ab.Salary == Convert.ToInt32(txtsalary.Text)

select ab).FirstOrDefault();



if (obj == null)

MessageBox.Show("Record Not Found");

else

{

foreach (Client c in obj)

{

c.Salary = 55000;

dc1.SubmitChanges();

}

}

}

}

}



Note : Client is a Table in EMP Database