HOW TO TO CREATE SQL TABLES IN DATABASE JUST BASICS

STORED PROCEDURES INSERT: 

create proc usp_strd_Insert
(
@uid nvarchar(20),
 @uname nvarchar(20),
@Address nvarchar(20),
@Age nvarchar(20) )
 as begin
insert into strd values(@uid,@uname,@Address,@Age)
end

 DELETE: 
create proc usp_strd_Delete
 (
 @uid nvarchar(20) )
as begin
delete from strd where uid=@uid
end
select * from strd

 UPDATE:
 create proc usp_strd_Update
(
@uid nvarchar(20),
@uname nvarchar(20),
@Address nvarchar(20),
@Age nvarchar(20)
)
as begin
Update strd set uid=@uid,uname=@uname,Address=@Address,Age=@Age where uid=@uid;
end

Button Submit:- 

protected void Page_Load(object sender, EventArgs e) { }
 SqlConnection con = new SqlConnection("Data Source=XYZ-Pc;Initial Catalog=abc;User ID=sa;Password=123456");

 protected void btnSubmit_Click(object sender, EventArgs e)
 {
 con.Open();
 SqlCommand cmd = new SqlCommand("usp_strd_Insert",con);
 cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@uid",txtuid.Text);
 cmd.Parameters.AddWithValue("@uname", txtusername.Text); cmd.Parameters.AddWithValue("@Address",txtAddress.Text); cmd.Parameters.AddWithValue("@Age",txtAge.Text);
 cmd.ExecuteNonQuery();
 con.Close();
 Response.Write("Submitted Successfully...");
 }

 Button Clear:-

 protected void btnClear_Click(object sender, EventArgs e)
 {
 txtuid.Text = txtusername.
Text = txtAddress.
Text = txtAge.Text = null;
 }

Button Update:- 

 protected void btnUpdate_Click(object sender, EventArgs e)
 {
 con.Open();
 SqlCommand cmd = new SqlCommand("usp_strd_Update", con);
 cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@uid",txtuid.Text);
 cmd.Parameters.AddWithValue("@uname", txtusername.Text); cmd.Parameters.AddWithValue("@Address", txtAddress.Text); cmd.Parameters.AddWithValue("@Age", txtAge.Text);
 cmd.ExecuteNonQuery(); con.Close();
 Response.Write("Updated Successfully...");
 }

Button Delete:-

 protected void btnDelete_Click(object sender, EventArgs e)
 {
 con.Open();
 SqlCommand cmd = new SqlCommand("usp_strd_Delete",con);
 cmd.CommandType = CommandType.StoredProcedure;
 cmd.Parameters.AddWithValue("@uid", txtuid.Text);
 cmd.ExecuteNonQuery();
 con.Close();
 Response.Write("Deleted Successfully...");



 PRISONER MANAGEMENT

 Button Update:-

 private void btnUpdate_Click(object sender, EventArgs e)
 {
 con.Open();
 SqlCommand cmd = new SqlCommand
("update Prisoner_Insert set PrisonerID='" + txtprisonerid.Text + "' ,Crime='" + txtcrime.Text + "',Age='" + txtage.Text + "',Address='" + txtaddress.Text + "',Gender='" + cbgender.Text + "' where PrisonerName='" + txtprisonername.Text + "'", con)
 con.Close();
}

Login Form:

 private void btnLogin_Click(object sender, EventArgs e)
 {
 con.Open();
("select username,password from login where username='" + txtUserName.Text + "' and password='" + txtPassword.Text + "'", con);
 SqlDataReader dr = cmd.ExecuteReader();
 if (dr.Read() == true)
 {
 MessageBox.Show("Login Succefully..");

 Form f = new Jailer_Information();
 f.Show(); 
 this.Hide(); 
 }
 else
 {
 MessageBox.Show("fail..");
 }
Note:- 
IN WEB FORMS Show The Message Alert - Response.Write("Submitted Sucessfully");
AND ONE FORM TO OTHER FORM WRITE-Response.Redirect("Formname.aspx");

 Button Submit:- 

 private void btnSubmit_Click(object sender, EventArgs e)
 {
 con.Open();
 SqlCommand cmd = new SqlCommand
("insert into Jailerdetails values('"+txtJailerName.Text+"','"+txtUserName.Text+"','"+txtPasword.Text+"','"+txtGenderBox.Text+"','"+txtPhoneNumber.Text+"','"+txtAge.Text+"','"+txtAddress.Text+"') ", con);
 SqlDataReader dr = cmd.ExecuteReader();
 MessageBox.Show("submited successfully...");
 con.Close();
 }
Note:- IN WEB FORMS Show The Message Alert  Response.Write("Submitted Sucessfully");

 Button Clear:
 private void btnClear_Click(object sender, EventArgs e)
 {
 txtJailerName.Text = txtUserName.Text = txtPasword.Text = txtGenderBox.Text = txtPhoneNumber.Text = txtAge.Text = txtAddress.Text = null;
 }

Button Submit:

 private void btnSubmit_Click(object sender, EventArgs e)
 {
 con.Open();
 SqlCommand cmd = new SqlCommand
("insert into prisonerdetails values('" + txtPrisonerID.Text + "','" + txtPrisonerNAme.Text + "','" + txtCrime.Text + "','" + comboBox1.Text + "','" + txtAge.Text + "','" + txtAddress.Text + "')", con); SqlDataReader dr = cmd.ExecuteReader();
 MessageBox.Show("submited successfully...");
 con.Close();
}

 Button ViewDetails:-
 private void btnViewJailer_Click(object sender, EventArgs e)
{
 con.Open();
 SqlCommand cmd = new SqlCommand
("select * from Jailerdetails where Jailername='"+combobox.Text+"'", con);
 SqlDataReader dr = cmd.ExecuteReader();
 DataTable dt = new DataTable();
 dt.Load(dr);
 dataGridView1.DataSource = dt;
 con.Close();
 }

 Button Viewall Details:- 

 private void btnViewAllJailers_Click(object sender, EventArgs e)
 {
 con.Open();
 SqlCommand cmd = new SqlCommand
("select * from Jailerdetails",con);
 SqlDataReader dr = cmd.ExecuteReader();
 DataTable dt = new DataTable();
 dt.Load(dr);
 dataGridView1.DataSource = dt;
 con.Close();
}

 HOSPTIAL SYSTEM SQL QUIRES:-

 DOCTOR:- 

 CREATE TABLE Doctors
(
 Eid varchar(50) NOT NULL,
 UserName  varchar(50) NOT NULL,
 Password varchar(50) NOT NULL,
 Designation varchar(50) NOT NULL,
 EName varchar(50) NOT NULL,
 Qualification varchar(50) NOT NULL,
 Experience int NOT NULL,
 Address varchar(50) NOT NULL,
 PhoNo int NOT NULL,
 Email varchar(50) NOT NULL,
 Dept varchar(50) NOT NULL
)

 EMPLOYEE:- 

 CREATE TABLE Employee
(
 [Empid] [nvarchar](100) NULL,
 [UserName] [nvarchar](100) NULL,
 [Password] [nvarchar](100) NULL,
 [Ename] [nvarchar](100) NULL,
 [Designation] [nvarchar](100) NULL,
 [Dept] [nvarchar](100) NULL,
 [Qualification] [nvarchar](100) NULL,
 [Experience] [nvarchar](100) NULL,
 [Address] [nvarchar](100) NULL,
 [Phone] [nvarchar](100) NULL,
 [Email] [nvarchar](100) NULL
 )

 LAB EMP:- 

 CREATE TABLE LabEmp
(
 [Eid] [varchar](50) NOT NULL,
 [UserName] [varchar](50) NOT NULL,
 [Password] [varchar](50) NOT NULL,
 [Ename] [varchar](50) NOT NULL,
 [Designation] [varchar](50) NOT NULL,
 [Qualifi] [varchar](50) NOT NULL,
 [Exp] [int] NOT NULL,
 [Address] [varchar](50) NOT NULL,
 [PhoNo] [int] NOT NULL,
 [Email] [varchar](50) NOT NULL,
 [Dept] [varchar](50) NOT NULL
 )

 LABREPORT:- 

CREATE TABLE LabReport
(
 [PatientID] [nvarchar](50) NULL,
 [DocName] [nvarchar](50) NULL,
 [Test] [varchar](50) NULL,
 [Amount] [nvarchar](50) NULL,
 [Report] [nvarchar](50) NULL
)

 Patient:- 

CREATE TABLE Patient
(
 [PatientId] [varchar](50) NOT NULL,
 [patientname] [varchar](50) NOT NULL,
 [age] [int] NULL,
 Gender] [varchar](50) NOT NULL,
 [purposeofvisit] [nvarchar](50) NOT NULL,
 [amount] [bigint] NULL,
 [address1] [nvarchar](50) NOT NULL,
 [contactno] [bigint] NULL
)

 PatientDetails:-

 CREATE TABLE PatientDetails
(
 [PatientID] [varchar](50) NOT NULL,
 [Problem] [varchar](50) NOT NULL,
 [Test] [varchar](50) NOT NULL,
 [TestReports] [varchar](50) NOT NULL,
 [Diagosys] [varchar](50) NOT NULL,
 [Prescription] [varchar](50) NOT NULL
 )

 PatientReg:- 
CREATE TABLE PatientReg
(
 [Date] [datetime] NULL,
 [PatientName] [varchar](50) NULL,
 [PatientId] [varchar](50) NULL,
 [DOB] [datetime] NULL,
 [Sex] [varchar](50) NULL,
 [Address] [varchar](50) NULL,
 [POV] [varchar](50) NULL,
 [PhoNo] [numeric](18, 0) NULL,
 [EmailId] [varchar](50) NULL,
 [Amount] [numeric](6, 2) NULL
 )

Reception:-

 CREATE TABLE receiption
(
 [EmpId] [varchar](50) NOT NULL,
 [Uname] [varchar](50) NOT NULL,
 [Pwd] [varchar](50) NOT NULL,
 [Ename] [varchar](50) NOT NULL,
 [Desig] [varchar](50) NOT NULL,
 [Qualification] [varchar](50) NOT NULL,
 [Exp] [int] NOT NULL,
 [Address] [varchar](50) NOT NULL,
 [Phono] [int] NOT NULL, 
[EmailID] [varchar](50) NOT NULL,
 [Dept] [varchar](50) NOT NULL
 )

 STORED PROCEDURES:-

 DOCTORS:-
 create procedure SP_Doctors
(
 @Eid varchar(50), @UserName varchar(50), @Password varchar(50), @Designation varchar(50), @EName varchar(50), @Qualification varchar(50), @Experience int, @Address varchar(50), @PhoNo int, @Email varchar(50), @Dept varchar(50)
 )
 as begin
 insert into Doctors values(@Eid,@UserName,@Password,@Designation,@EName,@Qualification,@Experience,@Address,@PhoNo,@Email,@Dept)
 end

 Employee:- 
 create procedure [dbo]SP_Employee]
 (
 @Empid varchar(50), @UserName varchar(50), @Password varchar(50), @Ename varchar(50), @Designation varchar(50), @Dept varchar(50), @Qualification varchar(50), @Experience int, @Address varchar(50) , @Phone varchar(50), @Email nvarchar(50)
 )
 as begin
 insert into Employee values(@Empid,@UserName,@Password,@Ename,@Designation,@Dept,@Qualification,@Experience,@Address,@Phone,@Email)
end