APP.config code:
<connectionStrings>
<add name="sqlconfig" connectionString="Data Source=AP134;Initial Catalog=Agilepoint;Integrated Security=SSPI"/>
</connectionStrings>
private void btnadd_Click(object sender, EventArgs e)
{
string gn = string.Empty;
string ck = string.Empty;
string connectionstring = ConfigurationManager.ConnectionStrings["sqlconfig"].ConnectionString;
SqlConnection cn = new SqlConnection(connectionstring);
SqlCommand cmd = new SqlCommand("empcrudoperations", cn);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter p1 = new SqlParameter("@Action", SqlDbType.VarChar,40);
p1.Value = "insert";
SqlParameter p = new SqlParameter("@eid", SqlDbType.Int);
SqlParameter p2 = new SqlParameter("@Firstname", SqlDbType.VarChar, 40);
SqlParameter p3 = new SqlParameter("@Lastname", SqlDbType.VarChar, 40);
var rdbtn = ggbbox.Controls.OfType<RadioButton>();
SqlParameter p4 = new SqlParameter("@Gender", SqlDbType.VarChar, 40);
foreach (RadioButton rb in rdbtn)
{
if (rdmale.Checked)
{
gn = rdmale.Text;
}
else
{
gn = rdfemale.Text;
}
}
SqlParameter p5 = new SqlParameter("@Contactno", SqlDbType.VarChar,40);
SqlParameter p6 = new SqlParameter("@Email", SqlDbType.VarChar, 40);
SqlParameter p7 = new SqlParameter("@Country", SqlDbType.VarChar, 40);
SqlParameter p8 = new SqlParameter("@State", SqlDbType.VarChar, 40);
SqlParameter p9 = new SqlParameter("@Address", SqlDbType.VarChar, 40);
SqlParameter p10 = new SqlParameter("@Role", SqlDbType.VarChar, 40);
var chkbox = gbrole.Controls.OfType<CheckBox>();
foreach (CheckBox ch in chkbox)
{
if (trdev.Checked)
{
ck = trdev.Text;
}
else if (seng.Checked)
{
ck = seng.Text;
}
else if (chr.Checked)
{
ck = chr.Text;
}
else
ck = cadmin.Text;
}
SqlParameter p11 = new SqlParameter("@Experiance", SqlDbType.Int);
SqlParameter p12 = new SqlParameter("@Salary", SqlDbType.Int);
p.Value = Convert.ToInt32(textBox1.Text);
p2.Value = tbname.Text;
p3.Value = tblname.Text;
p4.Value = gn;
p5.Value = tbcontact.Text;
p6.Value = tbemail.Text;
System.Text.RegularExpressions.Regex Emailval = new System.Text.RegularExpressions.Regex(@"^[a-zA-Z][\w\.-]*[a-zA-Z0-9]@[a-zA-Z0-9][\w\.-]*[a-zA-Z0-9]\.[a-zA-Z][a-zA-Z\.]*[a-zA-Z]$");
if (!Emailval.IsMatch(tbemail.Text))
{
MessageBox.Show("entered email id is Invaid");
}
p7.Value = cntry.Text;
p8.Value = cstate.Text;
p9.Value = tbaddress.Text;
p10.Value = ck;
p11.Value = tbexp.Text;
p12.Value = tbsalary.Text;
cmd.Parameters.Add(p1);
cmd.Parameters.Add(p);
cmd.Parameters.Add(p2);
cmd.Parameters.Add(p3);
cmd.Parameters.Add(p4);
cmd.Parameters.Add(p5);
cmd.Parameters.Add(p6);
cmd.Parameters.Add(p7);
cmd.Parameters.Add(p8);
cmd.Parameters.Add(p9);
cmd.Parameters.Add(p10);
cmd.Parameters.Add(p11);
cmd.Parameters.Add(p12);
cn.Open();
int count =cmd.ExecuteNonQuery();
tbname.Text = p2.Value.ToString();
tblname.Text = p3.Value.ToString();
tbcontact.Text = p5.Value.ToString();
tbemail.Text = p6.Value.ToString();
cntry.Text = p7.Value.ToString();
cstate.Text = p8.Value.ToString();
tbaddress.Text = p9.Value.ToString();
tbexp.Text = p10.Value.ToString();
if (count > 0)
{
MessageBox.Show("new employee added successfully");
}
cn.Close();
}
stored procedure:
create table Employee
(
Eid int primary key,
Firstname varchar(40),
Lastname varchar(40),
Gender varchar(40),
Contactno int,
Email varchar(40),
Country varchar(40),
State varchar(40),
Address varchar(40),
Role varchar(40),
Experience int,
Salary int
)
select * from Employee
set identity_insert Agilepoint.dbo.EmployeeDetails on
insert into Employee values(1,'jyothi','R','female',75234355,'jyothi.jyothi.joy@gmail.com','India','bangalore','#04 2ndfloor hoysalanagr','trainee dev.',0,8000)
insert into Employee values(2,'renu','R','female',76234355,'renu@gmail.com','USA','XXX','#04 2ndfloor hoysalanagr','software dev.',0,18000)
insert into Employee values(3,'arjun','R','male',2364355,'arjun@gmail.com','India','mangalore','#04 2ndfloor vijaynagar','sen. dev.',0,48000)
insert into Employee values(5,'amruth','R','male',9234355,'amruth@gmail.com','India','bangalore','#04 2ndfloor bommanahalli','admin',0,38000)
insert into Employee values(6,'rathna','R','female',85234355,'rathna.joy@gmail.com','Australia','XXX','#04 2ndfloor hoysalanagr','trainee dev.',0,28000)
select * from Employee
create proc empcrudoperations
(
@Action VARCHAR(10)='',
@eid int=0,
@Firstname varchar(40)='',
@Lastname varchar(40)='',
@gender varchar(40)='',
@Contactno int=0,
@Email varchar(40)='',
@Country varchar(40)='',
@state varchar(40)='',
@Address varchar(40)='',
@Role varchar(40)='',
@Experiance int=0,
@salary int=0
)
as
begin
SET NOCOUNT ON;
if @Action='select'
begin
select * from Employee
end
else if @Action='insert'
begin
insert into Employee(Eid,Firstname,Lastname,Gender,Contactno,Email,Country,State,Address,Role,Experience,Salary) values(@eid,@Firstname,@Lastname,@gender,@Contactno,@Email,@Country,@state,@Address,@Role,@Experiance,@salary)
end
else if @Action='update'
begin
--declare @eid int;
update Employee set Firstname=@Firstname,Lastname=@Lastname,gender=@gender,Contactno=@Contactno,Email=@Email,Country=@Country,State=@state,
Address=@Address,Role=@Role,Experience=@Experiance,Salary=@salary where eid=@eid
end
else if @Action='Delete'
begin
delete from Employee where eid=@eid;
end
else
begin
select * from Employee
end
end