Friday 29 September 2017

Sql Script for part -5 (sql server)

USE [master]
GO
/****** Object: Database [schoolm] Script Date: 9/28/2017 7:55:55 PM ******/
CREATE DATABASE [schoolm]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'schoolm', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\schoolm.mdf' , SIZE = 3136KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'schoolm_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\schoolm_log.ldf' , SIZE = 832KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
ALTER DATABASE [schoolm] SET COMPATIBILITY_LEVEL = 110
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [schoolm].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [schoolm] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [schoolm] SET ANSI_NULLS OFF
GO
ALTER DATABASE [schoolm] SET ANSI_PADDING OFF
GO
ALTER DATABASE [schoolm] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [schoolm] SET ARITHABORT OFF
GO
ALTER DATABASE [schoolm] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [schoolm] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [schoolm] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [schoolm] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [schoolm] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [schoolm] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [schoolm] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [schoolm] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [schoolm] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [schoolm] SET ENABLE_BROKER
GO
ALTER DATABASE [schoolm] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [schoolm] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [schoolm] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [schoolm] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [schoolm] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [schoolm] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [schoolm] SET HONOR_BROKER_PRIORITY OFF
GO
ALTER DATABASE [schoolm] SET RECOVERY FULL
GO
ALTER DATABASE [schoolm] SET MULTI_USER
GO
ALTER DATABASE [schoolm] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [schoolm] SET DB_CHAINING OFF
GO
ALTER DATABASE [schoolm] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF )
GO
ALTER DATABASE [schoolm] SET TARGET_RECOVERY_TIME = 0 SECONDS
GO
USE [schoolm]
GO
/****** Object: Table [dbo].[administator] Script Date: 9/28/2017 7:55:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[administator](
[ad_id] [int] IDENTITY(1,1) NOT NULL,
[ad_name] [nvarchar](20) NOT NULL,
[ad_password] [nvarchar](20) NOT NULL,
PRIMARY KEY CLUSTERED
(
[ad_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object: Table [dbo].[classes] Script Date: 9/28/2017 7:55:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[classes](
[class_id] [int] IDENTITY(1,1) NOT NULL,
[classname] [nvarchar](20) NOT NULL,
[fees] [float] NULL,
PRIMARY KEY CLUSTERED
(
[class_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object: Table [dbo].[fees] Script Date: 9/28/2017 7:55:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[fees](
[fee_id] [int] IDENTITY(1,1) NOT NULL,
[fee_amount] [float] NOT NULL,
[fee_fk_st_id] [int] NULL,
[SA_FK_ID] [int] NULL,
[dayx] [nvarchar](2) NULL,
[monthx] [nvarchar](2) NULL,
PRIMARY KEY CLUSTERED
(
[fee_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object: Table [dbo].[student] Script Date: 9/28/2017 7:55:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[student](
[std_id] [int] IDENTITY(1,1) NOT NULL,
[std_name] [nvarchar](20) NOT NULL,
[std_fname] [nvarchar](20) NOT NULL,
[std_gender] [nvarchar](6) NOT NULL,
[std_address] [nvarchar](100) NOT NULL,
[std_admissiondate] [nvarchar](20) NOT NULL,
[std_ad_fk_id] [int] NULL,
PRIMARY KEY CLUSTERED
(
[std_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object: Table [dbo].[student_img] Script Date: 9/28/2017 7:55:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[student_img](
[img_id] [int] IDENTITY(1,1) NOT NULL,
[img_path] [nvarchar](max) NOT NULL,
[img_fk] [int] NULL,
PRIMARY KEY CLUSTERED
(
[img_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
/****** Object: Table [dbo].[STUDENT_STATUS] Script Date: 9/28/2017 7:55:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[STUDENT_STATUS](
[SA_ID] [int] IDENTITY(1,1) NOT NULL,
[SA_ST_ID] [int] NULL,
[SA_Class_id] [int] NULL,
[sa_year] [nvarchar](5) NULL,
PRIMARY KEY CLUSTERED
(
[SA_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET IDENTITY_INSERT [dbo].[administator] ON

INSERT [dbo].[administator] ([ad_id], [ad_name], [ad_password]) VALUES (1, N'salmnan', N'password123')
INSERT [dbo].[administator] ([ad_id], [ad_name], [ad_password]) VALUES (2, N'raza', N'password113')
SET IDENTITY_INSERT [dbo].[administator] OFF
SET IDENTITY_INSERT [dbo].[classes] ON

INSERT [dbo].[classes] ([class_id], [classname], [fees]) VALUES (1, N'Montessori', 5000)
INSERT [dbo].[classes] ([class_id], [classname], [fees]) VALUES (2, N'I', 4000)
INSERT [dbo].[classes] ([class_id], [classname], [fees]) VALUES (3, N'II', 4000)
INSERT [dbo].[classes] ([class_id], [classname], [fees]) VALUES (4, N'III', 4000)
INSERT [dbo].[classes] ([class_id], [classname], [fees]) VALUES (5, N'IV', 4500)
INSERT [dbo].[classes] ([class_id], [classname], [fees]) VALUES (6, N'V', 4500)
INSERT [dbo].[classes] ([class_id], [classname], [fees]) VALUES (7, N'VI', 5000)
INSERT [dbo].[classes] ([class_id], [classname], [fees]) VALUES (8, N'VII', 5000)
INSERT [dbo].[classes] ([class_id], [classname], [fees]) VALUES (9, N'VIII', 5500)
INSERT [dbo].[classes] ([class_id], [classname], [fees]) VALUES (10, N'IX', 6000)
INSERT [dbo].[classes] ([class_id], [classname], [fees]) VALUES (11, N'X', 6000)
SET IDENTITY_INSERT [dbo].[classes] OFF
SET IDENTITY_INSERT [dbo].[fees] ON

INSERT [dbo].[fees] ([fee_id], [fee_amount], [fee_fk_st_id], [SA_FK_ID], [dayx], [monthx]) VALUES (1007, 4000, 2, 1, N'28', N'9')
INSERT [dbo].[fees] ([fee_id], [fee_amount], [fee_fk_st_id], [SA_FK_ID], [dayx], [monthx]) VALUES (1008, 4000, 3, 4, N'28', N'9')
INSERT [dbo].[fees] ([fee_id], [fee_amount], [fee_fk_st_id], [SA_FK_ID], [dayx], [monthx]) VALUES (1009, 5000, 2005, 5, N'28', N'9')
SET IDENTITY_INSERT [dbo].[fees] OFF
SET IDENTITY_INSERT [dbo].[student] ON

INSERT [dbo].[student] ([std_id], [std_name], [std_fname], [std_gender], [std_address], [std_admissiondate], [std_ad_fk_id]) VALUES (2, N'ahsan', N'ahmed', N'male', N't-3t2 north khi', N'8/26/2017', 1)
INSERT [dbo].[student] ([std_id], [std_name], [std_fname], [std_gender], [std_address], [std_admissiondate], [std_ad_fk_id]) VALUES (3, N'sunny', N'ahmed', N'male', N'e-3q2 north khi', N'8/26/2017', 2)
INSERT [dbo].[student] ([std_id], [std_name], [std_fname], [std_gender], [std_address], [std_admissiondate], [std_ad_fk_id]) VALUES (2004, N'jen', N'peter', N'male', N'i-32 street 5', N'9/12/2017', 2)
INSERT [dbo].[student] ([std_id], [std_name], [std_fname], [std_gender], [std_address], [std_admissiondate], [std_ad_fk_id]) VALUES (2005, N'sami', N'ali', N'male', N'y-434', N'9/12/2017', 2)
INSERT [dbo].[student] ([std_id], [std_name], [std_fname], [std_gender], [std_address], [std_admissiondate], [std_ad_fk_id]) VALUES (3004, N'ali ahmed', N'ahmed', N'male', N'y-3232', N'9/14/2017', 2)
INSERT [dbo].[student] ([std_id], [std_name], [std_fname], [std_gender], [std_address], [std_admissiondate], [std_ad_fk_id]) VALUES (3006, N'tony', N'james', N'male', N'u-454', N'9/19/2017', 2)
INSERT [dbo].[student] ([std_id], [std_name], [std_fname], [std_gender], [std_address], [std_admissiondate], [std_ad_fk_id]) VALUES (3007, N'sami', N'ahmed', N'male', N'y-565', N'9/21/2017', 2)
INSERT [dbo].[student] ([std_id], [std_name], [std_fname], [std_gender], [std_address], [std_admissiondate], [std_ad_fk_id]) VALUES (3008, N'raza ahmed', N'masood', N'male', N'y-6546', N'9/21/2017', 2)
INSERT [dbo].[student] ([std_id], [std_name], [std_fname], [std_gender], [std_address], [std_admissiondate], [std_ad_fk_id]) VALUES (3009, N'sana', N'ali', N'female', N'ds-6654', N'9/21/2017', 2)
INSERT [dbo].[student] ([std_id], [std_name], [std_fname], [std_gender], [std_address], [std_admissiondate], [std_ad_fk_id]) VALUES (3010, N'sana', N'ali', N'female', N'ds-6654', N'9/21/2017', 2)
INSERT [dbo].[student] ([std_id], [std_name], [std_fname], [std_gender], [std_address], [std_admissiondate], [std_ad_fk_id]) VALUES (3011, N'moosa', N'ali', N'male', N't-545', N'9/21/2017', 2)
INSERT [dbo].[student] ([std_id], [std_name], [std_fname], [std_gender], [std_address], [std_admissiondate], [std_ad_fk_id]) VALUES (3012, N'test', N'test2', N'male', N'i-4324', N'9/21/2017', 1)
INSERT [dbo].[student] ([std_id], [std_name], [std_fname], [std_gender], [std_address], [std_admissiondate], [std_ad_fk_id]) VALUES (4007, N'ali', N'ahmed raza', N'male', N'r-654', N'9/23/2017', 2)
SET IDENTITY_INSERT [dbo].[student] OFF
SET IDENTITY_INSERT [dbo].[student_img] ON

INSERT [dbo].[student_img] ([img_id], [img_path], [img_fk]) VALUES (6, N'C:\Users\salman\Documents\Visual Studio 2013\Projects\WindowsFormsApplication4\WindowsFormsApplication4\Resources\2005.jpg', 2005)
SET IDENTITY_INSERT [dbo].[student_img] OFF
SET IDENTITY_INSERT [dbo].[STUDENT_STATUS] ON

INSERT [dbo].[STUDENT_STATUS] ([SA_ID], [SA_ST_ID], [SA_Class_id], [sa_year]) VALUES (1, 2, 2, N'2017')
INSERT [dbo].[STUDENT_STATUS] ([SA_ID], [SA_ST_ID], [SA_Class_id], [sa_year]) VALUES (2, 2004, 2, N'2017')
INSERT [dbo].[STUDENT_STATUS] ([SA_ID], [SA_ST_ID], [SA_Class_id], [sa_year]) VALUES (4, 3, 4, N'2017')
INSERT [dbo].[STUDENT_STATUS] ([SA_ID], [SA_ST_ID], [SA_Class_id], [sa_year]) VALUES (5, 2005, 1, N'2017')
INSERT [dbo].[STUDENT_STATUS] ([SA_ID], [SA_ST_ID], [SA_Class_id], [sa_year]) VALUES (6, 3004, 6, N'2017')
INSERT [dbo].[STUDENT_STATUS] ([SA_ID], [SA_ST_ID], [SA_Class_id], [sa_year]) VALUES (7, 3006, 6, N'2017')
INSERT [dbo].[STUDENT_STATUS] ([SA_ID], [SA_ST_ID], [SA_Class_id], [sa_year]) VALUES (8, 2, 1, N'2016')
INSERT [dbo].[STUDENT_STATUS] ([SA_ID], [SA_ST_ID], [SA_Class_id], [sa_year]) VALUES (9, 3, 3, N'2016')
INSERT [dbo].[STUDENT_STATUS] ([SA_ID], [SA_ST_ID], [SA_Class_id], [sa_year]) VALUES (10, 3009, 7, N'2017')
INSERT [dbo].[STUDENT_STATUS] ([SA_ID], [SA_ST_ID], [SA_Class_id], [sa_year]) VALUES (11, 3011, 11, N'2017')
INSERT [dbo].[STUDENT_STATUS] ([SA_ID], [SA_ST_ID], [SA_Class_id], [sa_year]) VALUES (12, 3012, 9, N'2017')
INSERT [dbo].[STUDENT_STATUS] ([SA_ID], [SA_ST_ID], [SA_Class_id], [sa_year]) VALUES (1010, 4007, 4, N'2017')
SET IDENTITY_INSERT [dbo].[STUDENT_STATUS] OFF
SET ANSI_PADDING ON

GO
/****** Object: Index [UQ__administ__84D9FB7AC548E39F] Script Date: 9/28/2017 7:55:55 PM ******/
ALTER TABLE [dbo].[administator] ADD UNIQUE NONCLUSTERED
(
[ad_name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE [dbo].[fees] WITH CHECK ADD FOREIGN KEY([fee_fk_st_id])
REFERENCES [dbo].[student] ([std_id])
GO
ALTER TABLE [dbo].[fees] WITH CHECK ADD FOREIGN KEY([SA_FK_ID])
REFERENCES [dbo].[STUDENT_STATUS] ([SA_ID])
GO
ALTER TABLE [dbo].[student] WITH CHECK ADD FOREIGN KEY([std_ad_fk_id])
REFERENCES [dbo].[administator] ([ad_id])
GO
ALTER TABLE [dbo].[student_img] WITH CHECK ADD FOREIGN KEY([img_fk])
REFERENCES [dbo].[student] ([std_id])
GO
ALTER TABLE [dbo].[STUDENT_STATUS] WITH CHECK ADD FOREIGN KEY([SA_Class_id])
REFERENCES [dbo].[classes] ([class_id])
GO
ALTER TABLE [dbo].[STUDENT_STATUS] WITH CHECK ADD FOREIGN KEY([SA_ST_ID])
REFERENCES [dbo].[student] ([std_id])
GO
/****** Object: StoredProcedure [dbo].[delete_student] Script Date: 9/28/2017 7:55:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[delete_student]
(
@std_id int
)
as
begin

delete from student_img where img_fk=@std_id
delete from student where std_id=@std_id

end

GO
/****** Object: StoredProcedure [dbo].[insert_student] Script Date: 9/28/2017 7:55:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[insert_student]
(
@std_name nvarchar(20),
@std_fname nvarchar(20),
@std_gender nvarchar(6),
@std_address nvarchar(100),
@std_admissiondate nvarchar(20),
@std_ad_fk_id int

)
as

begin

insert into student(std_name,std_fname,std_gender,std_address,std_admissiondate,std_ad_fk_id)
values(@std_name,@std_fname,@std_gender,@std_address,@std_admissiondate,@std_ad_fk_id)

end

GO
/****** Object: StoredProcedure [dbo].[insert_student_img] Script Date: 9/28/2017 7:55:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[insert_student_img]
(
@img_path nvarchar(max),
@img_fk int
)
as

begin

insert into student_img(img_path,img_fk)
values(@img_path,@img_fk)

end

GO
/****** Object: StoredProcedure [dbo].[insert_STUDENT_STATUS] Script Date: 9/28/2017 7:55:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[insert_STUDENT_STATUS]
(
@SA_ST_ID INT ,
@SA_Class_id INT ,
@sa_year nvarchar(5)

)
as

begin

insert into STUDENT_STATUS(SA_ST_ID,SA_Class_id,sa_year)
VALUES(@SA_ST_ID,@SA_Class_id,@sa_year)

end

GO
/****** Object: StoredProcedure [dbo].[insert_tbladmin] Script Date: 9/28/2017 7:55:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[insert_tbladmin]
(
@ad_name nvarchar(20) ,
@ad_password nvarchar(20)

)
as

begin
insert into administator(ad_name,ad_password)
values(@ad_name,@ad_password)

end

GO
/****** Object: StoredProcedure [dbo].[insert_tblfees] Script Date: 9/28/2017 7:55:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[insert_tblfees]
(
@fee_amount float ,
@dayx nvarchar(2),
@monthx nvarchar(2),
@fee_fk_st_id int,
@SA_FK_ID INT

)
as

begin

insert into fees(fee_amount,dayx,monthx,fee_fk_st_id,SA_FK_ID)
values(@fee_amount,@dayx,@monthx,@fee_fk_st_id,@SA_FK_ID)

end

GO
/****** Object: StoredProcedure [dbo].[insert_tblstudent] Script Date: 9/28/2017 7:55:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[insert_tblstudent]
(
@std_name nvarchar(20) ,
@std_fname nvarchar(20),
@std_gender nvarchar(6),
@std_address nvarchar(100) ,
@std_admissiondate nvarchar(20),
@std_ad_fk_id int
)
as
begin

insert into student(std_name,std_fname,std_gender,std_address,std_admissiondate,std_ad_fk_id)
values(@std_name,@std_fname,@std_gender,@std_address,@std_admissiondate,@std_ad_fk_id)
end

GO
/****** Object: StoredProcedure [dbo].[UPDATE_STUDENT] Script Date: 9/28/2017 7:55:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[UPDATE_STUDENT]
(
@std_id int,
@std_name nvarchar(20),
@std_fname nvarchar(20),
@std_gender nvarchar(6),
@std_address nvarchar(100)
)
as

begin

update student

set std_name=@std_name,
std_fname=@std_fname,
std_gender=@std_gender,
std_address=@std_address

where std_id=@std_id

end

GO
USE [master]
GO
ALTER DATABASE [schoolm] SET READ_WRITE
GO

Thursday 28 September 2017

Dbms Application # 15 (how to find fees Defaulter in recrods/Table Alteration/Removing anamolies)

All important Query

select * from sys.tables
select * from fees

delete from fees where fee_id=2

select * from STUDENT_STATUS

select s.std_id,sa.SA_ID,f.fee_id,s.std_name,sa.sa_year,f.dayx+'/'+f.monthx as PaidDate,f.fee_amount from student s inner join STUDENT_STATUS sa on s.std_id=sa.SA_ST_ID inner join fees f on sa.SA_ID=f.SA_FK_ID where sa.sa_year='2017'

select count(fee_id) from fees where fee_fk_st_id=2 and monthx='9'

delete from STUDENT_STATUS where SA_ID=3

alter table fees
drop column fee_date

modified Storeprocedure

USE [schoolm]

/****** Object: StoredProcedure [dbo].[insert_tblfees] Script Date: 9/28/2017 12:03:43 PM ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

ALTER proc [dbo].[insert_tblfees]

(

@fee_amount float ,

@dayx nvarchar(2),

@monthx nvarchar(2),

@fee_fk_st_id int,

@SA_FK_ID INT

)

begin

insert into fees(fee_amount,dayx,monthx,fee_fk_st_id,SA_FK_ID)

values(@fee_amount,@dayx,@monthx,@fee_fk_st_id,@SA_FK_ID)

end

Modified Fees insertion method

public void insert_FEES(student_status sa,string id)

{

SqlConnection conn = new SqlConnection(connstring);

try

{

SqlCommand cmd = new SqlCommand("insert_tblfees", conn);

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add("@fee_amount", SqlDbType.Float).Value = sa.class_fees;

cmd.Parameters.Add("@dayx", SqlDbType.NVarChar).Value = System.DateTime.Now.Day.ToString();

cmd.Parameters.Add("@monthx", SqlDbType.NVarChar).Value = System.DateTime.Now.Month.ToString();

cmd.Parameters.Add("@fee_fk_st_id", SqlDbType.Int).Value =id ;

cmd.Parameters.Add("@SA_FK_ID", SqlDbType.Int).Value = sa.status_student_id;

conn.Open();

cmd.ExecuteNonQuery();

MessageBox.Show("DATA record has been inserted successfully.....");

}

catch (Exception)

{

MessageBox.Show("data is not inserted !!!");

}

finally

{

conn.Close();

}

} //method end...........

Fees Submission events code:

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Configuration;

using System.Data;

using System.Data.SqlClient;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Windows.Forms;

namespace WindowsFormsApplication4

{

public partial class feessubmission : Form

{

viewclass vc = new viewclass();

string q;

private string connstring = ConfigurationManager.ConnectionStrings["TESTDB"].ConnectionString;

public feessubmission()

{

InitializeComponent();

}

private void button1_Click(object sender, EventArgs e)

{

SqlConnection connection = new SqlConnection(connstring);

string sql = "select s.std_id,s.std_name,sa.SA_ID,SA_Class_id,sa.sa_year,c.classname,c.fees from student s inner join STUDENT_STATUS sa on sa.SA_ST_ID=s.std_id inner join classes c on c.class_id=sa.SA_Class_id where sa.sa_year='"+System.DateTime.Now.Year+"' and s.std_id="+textBox1.Text;

try

{

label2.Text = " ";

label3.Text = " ";

label4.Text = " ";

label5.Text = " ";

connection.Open();

SqlCommand cmd = new SqlCommand(sql, connection);

SqlDataReader reader = cmd.ExecuteReader();

while (reader.Read())

{

label2.Text =reader.GetValue(1).ToString();

label3.Text = reader.GetValue(5).ToString();

label4.Text =reader.GetValue(6).ToString();

label5.Text = reader.GetValue(2).ToString();

}

connection.Close();

}

catch (Exception ex)

{

MessageBox.Show("No RECORDS WERE FOUND!");

}

returnclass rc = new returnclass();

string pathquerry = rc.scalarReturn("select img_path from student_img where img_fk=" + textBox1.Text);

if (pathquerry == " ")

{

pictureBox1.Image = Image.FromFile(@"C:\Users\salman\documents\visual studio 2013\Projects\WindowsFormsApplication4\WindowsFormsApplication4\Resources\profile.jpg");

}

else

{

pictureBox1.Image = Image.FromFile(pathquerry);

}

pictureBox1.SizeMode = PictureBoxSizeMode.StretchImage;

string s= rc.scalarReturn("select count(fee_id) from fees where fee_fk_st_id="+textBox1.Text+" and monthx='"+System.DateTime.Now.Month.ToString()+"'");

if (s.Equals("0"))

{

button2.Enabled = true;

label10.Text = "Unpaid!";

label10.ForeColor = System.Drawing.Color.Red;

}

else

{

label10.Text = "paid!";

button2.Enabled = false;

label10.ForeColor = System.Drawing.Color.Green;

}

private void button2_Click(object sender, EventArgs e)

{

student_status sa = new student_status();

sa.status_student_id =Convert.ToInt32( label5.Text);

sa.class_fees = label4.Text;

sa.class_name = label3.Text;

insert i = new insert();

i.insert_FEES(sa, textBox1.Text);

q = "select s.std_id,sa.SA_ID,f.fee_id,s.std_name,sa.sa_year,f.dayx+'/'+f.monthx as PaidDate,f.fee_amount from student s inner join STUDENT_STATUS sa on s.std_id=sa.SA_ST_ID inner join fees f on sa.SA_ID=f.SA_FK_ID where sa.sa_year='" + System.DateTime.Now.Year.ToString() + "'";

dataGridView1.DataSource = vc.showrecord(q);

}

private void feessubmission_Load(object sender, EventArgs e)

{

dataGridView1.DataSource = vc.showrecord(q);

}

Tuesday 26 September 2017

How to use Video Control in html-5

<!doctype html>
<html>
<head>
<meta charset="utf-8">
<title>Video and audion controls</title>
<style>
body
{
margin:0 auto;
padding:0px;
background-color:#F00;

}
#wrapper
{
width:80%;
margin:0 auto;
height:900px;
background-color:#FFF;
border:1px solid black;

}
header
{
width:100%;
height:100px;
background-color:#CCC;
margin-bottom:100px;

}

</style>

</head>

<body>

<div id="wrapper">
<header>

<DIV style="WIDTH:300PX;HEIGHT:100%;">
<img style="width:200px;height:100%" src="img/you.png"/>
</DIV>

</header>

<div style="WIDTH:100%;HEIGHT:500px; margin:0 auto; border:1px solid black ">

<video controls style="width:100%;height:100%">

<source src="video/carvideo.mp4" type="video/mp4" >
</video>

</div>

<div style="WIDTH:100%;HEIGHT:auto; margin:0 auto; border:1px solid black ">
<video controls style="width:25%;height:200px">

<source src="video/carvideo.mp4" type="video/mp4">
</video>

<video controls style="width:24%;height:200px">

<source src="video/carvideo.mp4" type="video/mp4">
</video>

<video controls style="width:24%;height:200px">

<source src="video/carvideo.mp4" type="video/mp4">
</video>
<video controls style="width:24%;height:200px">

<source src="video/carvideo.mp4" type="video/mp4">
</video>

</div>

</div>

</body>
</html>

Quiz Application# 1 (Databse Design)

create table admin_athu
(
ad_id int primary key identity ,
ad_user nvarchar(20) not null,
ad_password nvarchar(20) not null

)

create table student_record
(
std_id int primary key identity ,
std_name nvarchar(20) not null,
std_batchcode nvarchar(20) not null,
std_password nvarchar(20) not null,
std_ad_id int foreign key references admin_athu(ad_id )
)

create table exams
(
ex_id int primary key identity ,
exam_name nvarchar(20) not null
)

create table questions
(
q_id int primary key identity,
q_title nvarchar(max) not null,
q_opa nvarchar(200) not null,
q_opb nvarchar(200) not null,
q_opc nvarchar(200) not null,
q_opd nvarchar(200) not null,
q_opcorrect nvarchar(200) not null,
q_addeddate nvarchar(20) not null,
q_fk_ad int foreign key references admin_athu(ad_id ),
q_fk_ex int foreign key references exams(ex_id)
)

Sunday 24 September 2017

How to create Attractive Forms in html-5 using some Css code

Copy and paste the code in your html file:

<!doctype html>
<html>
<head>
<meta charset="utf-8">
<title>Forms </title>

<style>
body
{ margin:0 auto;
padding:0px;
background-color:#99C;
background-image:url(img/1.jpg);
background-size:cover;
}

form
{
margin:0 auto;
width:350px;
height:600px;
background-color:#F6F;
margin-top:100px;
border-radius:50px 20px 50px 20px;
color:#93F;
border:8px inset #90F;
font-style:italic;
font-weight:bold;
opacity:0.6;

background-image:url(img/2.jpg);
}
TD{
padding:20PX;}

</style>

</head>

<body>

<form>
<br>
<br>

<table>

<tr>
<td> User Name: </td>

<td>
<input type="text" size="15" required="required" maxlength="10"/>

</td>
</tr>

<tr>
<td> Password: </td>
<td> <input type="password" size="15" required="required"/> </td>
</tr>

<tr>
<td> Gender: </td>
<td>
<input type="radio"value="male" name="gender"/>Male <input type="radio" value="Female" name="gender"/>Female
</td>

</tr>

<tr>

<td>Education </td>
<td>
<input type="checkbox" name="EDU" value="SSC"/>SSC
<input type="checkbox" name="EDU" value="HSC"/>HSC
<input type="checkbox" name="EDU" value="GRADUATION"/>GRAD
</td>

</tr>

<TR>

<TD> City: </TD>
<TD>
<select>
<option value="khi">Karachi</option>
<option value="lhr">Lahore</option>
<option value="isb">Islamabad</option>
<option value="MUL">Multan </option>

</select>

</TD>

</TR>

<tr>

<td>Upload Resume: </td>
<td> <input type="file"/> </td>

</tr>

<tr>

<td> </td>
<td><button> Submit!</button> </td>

</tr>

</table>

</form>

</body>
</html>

How to export From sql records to data Grid view

Database to Data Grid view:

string connectionString = null; //connection string

string sql = null; //query from db

connectionString = "Data Source=LAB6-168;Initial Catalog=schoolm;User ID=sa;Password=aptech";

SqlConnection cnn = new SqlConnection(connectionString); //con object

cnn.Open(); //coneection open form db

sql = "SELECT * FROM student"; //query

SqlDataAdapter dscmd = new SqlDataAdapter(sql, cnn); //reads all records from db

DataSet ds = new DataSet();

dscmd.Fill(ds);

dataGridView1.DataSource = ds.Tables[0];

How to export database table in excel file (From SQL TO EXCEL)

Button event Code:

SqlConnection cnn;

string connectionString = null;

string sql = null;

string data = null;

int i = 0;

int j = 0;

Microsoft.Office.Interop.Excel.Application xlApp;

Microsoft.Office.Interop.Excel.Workbook xlWorkBook;

Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;

object misValue = System.Reflection.Missing.Value;

xlApp = new Microsoft.Office.Interop.Excel.Application();

xlWorkBook = xlApp.Workbooks.Add(misValue);

xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

connectionString = "Data Source=LAB6-168;Initial Catalog=schoolm;User ID=sa;Password=aptech";

cnn = new SqlConnection(connectionString);

cnn.Open();

sql = "SELECT * FROM student";

SqlDataAdapter dscmd = new SqlDataAdapter(sql, cnn);

DataSet ds = new DataSet();

dscmd.Fill(ds);

for (i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)

{

for (j = 0; j <= ds.Tables[0].Columns.Count - 1; j++)

{

data = ds.Tables[0].Rows[i].ItemArray[j].ToString();

xlWorkSheet.Cells[i + 1, j + 1] = data;

}

xlWorkBook.SaveAs(@"C:\Users\salman\Desktop\csharp-Excel6.xls", Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);

xlWorkBook.Close(true, misValue, misValue);

xlApp.Quit();

releaseObject(xlWorkSheet);

releaseObject(xlWorkBook);

releaseObject(xlApp);

MessageBox.Show("Excel file created , you can find the file c:\\csharp.net-informations.xls");

Release Method outside button event :

private void releaseObject(object obj)

{

try

{

System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);

obj = null;

}

catch (Exception ex)

{

obj = null;

MessageBox.Show("Exception Occured while releasing object " + ex.ToString());

}

finally

{

GC.Collect();

}

Saturday 23 September 2017

database application in urdu/hindi part 13 (FETCHING RECORD FROM MULTIPLE TABLES,Fees Submission Form)

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace WindowsFormsApplication4
{
public partial class feessubmission : Form
{
private string connstring = ConfigurationManager.ConnectionStrings["TESTDB"].ConnectionString;
public feessubmission()
{
InitializeComponent();
}

private void button1_Click(object sender, EventArgs e)
{
SqlConnection connection = new SqlConnection(connstring);
string sql = "select s.std_id,s.std_name,sa.SA_ID,SA_Class_id,sa.sa_year,c.classname,c.fees from student s inner join STUDENT_STATUS sa on sa.SA_ST_ID=s.std_id inner join classes c on c.class_id=sa.SA_Class_id where sa.sa_year='"+System.DateTime.Now.Year+"' and s.std_id="+textBox1.Text;

try
{
label2.Text = " ";
label3.Text = " ";
label4.Text = " ";
label5.Text = " ";

connection.Open();
SqlCommand cmd = new SqlCommand(sql, connection);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
label2.Text ="Name: "+reader.GetValue(1).ToString();
label3.Text ="Class: "+ reader.GetValue(5).ToString();
label4.Text ="fees: "+ reader.GetValue(6).ToString();
label5.Text = "Status id: "+reader.GetValue(2).ToString();

}
connection.Close();
}
catch (Exception ex)
{
MessageBox.Show("No RECORDS WERE FOUND!");
}

returnclass rc = new returnclass();
string pathquerry = rc.scalarReturn("select img_path from student_img where img_fk=" + textBox1.Text);
if (pathquerry == " ")
{
pictureBox1.Image = Image.FromFile(@"C:\Users\salman\documents\visual studio 2013\Projects\WindowsFormsApplication4\WindowsFormsApplication4\Resources\profile.jpg");
}
else
{
pictureBox1.Image = Image.FromFile(pathquerry);
}

pictureBox1.SizeMode = PictureBoxSizeMode.StretchImage;

}

private void button2_Click(object sender, EventArgs e)
{
student_status sa = new student_status();
sa.status_student_id =Convert.ToInt32( label5.Text);
sa.class_fees = label4.Text;
sa.class_name = label3.Text;

insert i = new insert();
i.insert_FEES(sa, textBox1.Text);

}
}
}

Friday 22 September 2017

Html-css single Page complete Layout

HTML CODE:

<!doctype html>

<html>

<head>

</head>

<body>

<nav>

<ul>

<li> <a href="#"> Marketing</a> </li>

<li> <a href="#"> Service</a> </li>

<li> <a href="#"> Contact</a> </li>

</ul>

</nav>

</div>

</header>

</div>

</div>

</div>

</div>

</body>

</html>

CSS CODE:

@charset "utf-8";

/* CSS Document */

body

{ margin:0 auto;

padding:0px;

background-color:#03F;

}

#wrapper

{

width:100%;

height:1000px;

background-color:#939;

}

header

{

width:100%;

height:100px;

background-color:#09C;

}

#content

{

width:100%;

height:800px;

background-color:#ccc;

}

footer

{

width:100%;

height:100px;

background-color:#F96;

}

.chead

{

width:50%;

height:100px;

background-color:#CCC;

float:left;

}

nav

{

width:100%;

height:100px;

}

nav ul

{ margin:0;

list-style-type:none;

}

nav ul li

{

float:left;

margin-top:30px;

}

nav ul li a

{

text-decoration:none;

padding:20px;

color:#fff; font-size:14px;

}

.box

{

width:21.25%;

height:200px;

background-color:#9C3;

float:left;

margin-left:20px;

border-radius:10px 10px 10px 10px;

}

How to apply image icon on top of web page and make image gallery without Css

<!doctype html>

<html>

<head>

<title>Images in html</title>

</head>

<body>

</body>

</html>

Thursday 21 September 2017

How to Make html-tables attractive by using 3 lines of Css code

Tables in html

<!doctype html>

<html>

<head>

<title>TABLES</title>

<style>

TABLE

{ width:100%;

text-align:center;

background-color:#963;

}

{

padding:10PX;

}

TD:HOVER

{

background-color:WHITE;

COLOR:#963;

}

</style>

</head>

<body>

<TR>

<TD> CLASS</TD>

<TD>TIMING </TD>

</TR>

<TR>

<TD> Salman</TD>

</TR>

<TR>

</TR>

<TR>

<TD> Arsalan</TD>

</TR>

<TR>

<TD> Muzammil</TD>

</TR>

</table>

</body>

</html>

How to make layout of facebook page part 1 header

HTML CODE:

<!doctype html>

<html>

<head>

<title>Facebook-Login or SignUp</title>

</head>

<body>

</div>

<p>Email or Phone

<br/>

</p>

</div>

<p>Password

<br/>

</p>

</div>

<button style="background-color:rgb(59,89,152);color:white;margin-top:35px;"> Login</button>

</div>

</div>

</header>

</div>

</body>

</html>

CSS CODE:

@charset "utf-8";

/* CSS Document */

body

{ margin:0 auto;

padding:0px;

}

#wrapper

{

width:100%;

height:auto;

background-color:#999;

}

header

{

width:100%;

height:100px;

background-color:rgb(59,89,152);

}

#maincontent

{

width:100%;

height:800px;

background-color:#fff;

}

#footer

{

width:100%;

height:200px;

background-color:#903;

}

.cheader

{

width:50%;

height:100px;

float:left;

}

.cheader2

{

width:25%;

height:100px;

float:left;

color:white;

}

Wednesday 20 September 2017

How To Divide a page into 10 Pieces/sections using Html and css

Html code

</div>

</div>

css code:

#content

{

width:100%;

height:600px;

background-color:#099;

}

#aside

{

width:25%;

height:200px;

background-color:#F00;

float:left;

}

#section

{

width:75%;

height:200px;

background-color:#03C;

float:left;

}

.info

{

width:10%;

height:200px;

background-color:#03C;

float:left;

}

DATABASE APPLICATION # 2.1 (Return a single value form database )

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Configuration;

namespace WindowsFormsApplication4
{
class returnclass
{
private string connstring = ConfigurationManager.ConnectionStrings["TESTDB"].ConnectionString;

public string scalarReturn(string q)
{string s=" ";
SqlConnection conn = new SqlConnection(connstring);
conn.Open();

try
{
SqlCommand cmd = new SqlCommand(q, conn);
s = cmd.ExecuteScalar().ToString();

}
catch (Exception)
{
s = " ";

}

return s;

}

}
}

Database application # 5 VIEWS AND SEARCH FILTER

public partial class viewform : Form
{
viewclass vc = new viewclass();
string q;
public viewform()
{
InitializeComponent();
}

private void button1_Click(object sender, EventArgs e)
{
q = "select s.std_id as 'id',s.std_name as 'Name',s.std_fname AS 'Father name',s.std_gender as 'Gender',s.std_address as 'Address',s.std_admissiondate as 'Admission Date' ,a.ad_name as 'Admin Name' from student s inner join administator a on a.ad_id=s.std_ad_fk_id where s.std_id=" + textBox2.Text;
dataGridView1.DataSource = vc.showrecord(q);

returnclass rc = new returnclass();
string pathquerry= rc.scalarReturn("select img_path from student_img where img_fk="+textBox2.Text);
pictureBox1.Image = Image.FromFile(pathquerry);
pictureBox1.SizeMode = PictureBoxSizeMode.StretchImage;

}

private void viewform_Load(object sender, EventArgs e)
{
q = "select s.std_id as 'id',s.std_name as 'Name',s.std_fname AS 'Father name',s.std_gender as 'Gender',s.std_address as 'Address',s.std_admissiondate as 'Admission Date' ,a.ad_name as 'Admin Name' from student s inner join administator a on a.ad_id=s.std_ad_fk_id";
dataGridView1.DataSource = vc.showrecord(q);

}

private void textBox1_KeyUp(object sender, KeyEventArgs e)
{
q = "select s.std_id as 'id',s.std_name as 'Name',s.std_fname AS 'Father name',s.std_gender as 'Gender',s.std_address as 'Address',s.std_admissiondate as 'Admission Date' ,a.ad_name as 'Admin Name' from student s inner join administator a on a.ad_id=s.std_ad_fk_id where s.std_name like '" + textBox1.Text + "%'";
dataGridView1.DataSource = vc.showrecord(q);

}

}
}

Database Application # 3 (Store Procedure of Data Insertion )

Store Procedure of Data Insertion in student Table

Create proc [dbo].[insert_student]
(
@std_name nvarchar(20),
@std_fname nvarchar(20),
@std_gender nvarchar(6),
@std_address nvarchar(100),
@std_admissiondate nvarchar(20),
@std_ad_fk_id int

)
as

begin

insert into student(std_name,std_fname,std_gender,std_address,std_admissiondate,std_ad_fk_id)
values(@std_name,@std_fname,@std_gender,@std_address,@std_admissiondate,@std_ad_fk_id)

end

Anchor Tag /Hyper Link in HTML-5

Anchor Tag /Hyper Link

Guide:
1-Make a folder on desktop named as "Anchor Tag"
2-In "Anchor Tag" folder make 4 folders named as img, js, pages and css
3-Open your code Editor , copy the below html and paste it , and save the file named as index.html in "Anchor Tag" folder
4-Now Create another html page , name it as "aboutus.html" & save it in pages folder.

<!doctype html>
<html>
<head>
<meta charset="utf-8">
<title>Anchor tag</title>
</head>

<body>

<h1> Index Page</h1>

<UL>

<LI>
<a target="_blank" href="pages/ABOUTUS.html"> About Us page ( using blank attribute )</a>
</Li>

<LI>
<a target="_self" href="pages/ABOUTUS.html"> About Us page ( using self attribute )</a>
</Li>

<LI>
<a target="_top" href="pages/ABOUTUS.html"> About Us page ( using top attribute )</a>
</Li>

</UL>

</body>
</html>

WEB PAGE LAYOUT USING HTML & CSS (css-code)

Css Code:

@charset "utf-8";
/* CSS Document */
body
{ margin:0 auto;
padding:0px;
background-color:#9CF;
}
#wrapper
{
margin:0 auto;
width:80%;
height:1000px;
background-color:#906;

}
header
{
width:100%;
height:200px;
background-color:#0F0;

}
nav
{
width:100%;
height:50px;
background-color:#FC9;

}
nav ul
{ margin:0;
list-style-type:none;
}
nav ul li
{
float:left;
margin-top:10px;

}
nav ul li a
{ text-decoration:none;
font-size:26px;
padding:10px;
color:#FFF;
}
a:hover
{
color:#00C;
background-color:#FFF;

}
#content
{
width:100%;
height:600px;
background-color:#099;
}
#foot
{
width:100%;
height:200px;
background-color:#333;

}

WEB PAGE LAYOUT USING HTML & CSS (html code)

HTML CODE:

copy and paste the code in code editor

<!doctype html>
<html>
<head>
<meta charset="utf-8">
<title>layout</title>

<link href="css/style.css" type="text/css" rel="stylesheet">
</head>

<body>
<div id="wrapper">
<header>
<img src="img/logo.png" style="width:200px;height:150px;margin-left:10px;margin-top:20px;"/>
</header>
<nav>
<ul>
<li><a href="index.html">Home</a> </li>
<li><a href="pages/admission.html">Admission</a> </li>
<li><a href="pages/aboutus.html">About Us</a> </li>
<li><a href="pages/contactus.html">Contact Us</a> </li>

</ul>
</nav>
<div id="content">

</div>

<div id="foot">

</div>
</div>
</body>
</html>

Monday 18 September 2017

Database Application # 4 (insert form)

Student Class Properties:

class student

{

public int s_id { get; set; }

public string sname { get; set; }

public string sfname { get; set; }

public string sgender { get; set; }

public string saddress { get; set; }

public string sdate { get; set; }

public string sfk { get; set; }

}

Method of Data Insertion

public string insert_srecord(student s)
{
string msg = " ";
SqlConnection conn = new SqlConnection(connstring);

try
{
SqlCommand cmd = new SqlCommand("insert_student", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@std_name", SqlDbType.NVarChar, 20).Value = s.sname;
cmd.Parameters.Add("@std_fname", SqlDbType.NVarChar, 20).Value = s.sfname;
cmd.Parameters.Add("@std_gender", SqlDbType.NVarChar, 20).Value = s.sgender;
cmd.Parameters.Add("@std_address", SqlDbType.NVarChar, 100).Value = s.saddress;
cmd.Parameters.Add("@std_admissiondate", SqlDbType.NVarChar, 20).Value = s.sdate;
cmd.Parameters.Add("@std_ad_fk_id", SqlDbType.NVarChar, 20).Value = s.sfk;

conn.Open();
cmd.ExecuteNonQuery();

msg = "data successfully inserted!";

}
catch (Exception)
{

msg = "data is not successfully inserted!";

}

finally
{
conn.Close();
}

return msg;

} //method end......................

Database Application # 2 (login C#)

LOGIN FORM CODE C#

string user = textBox1.Text;
string password = textBox2.Text;
string userdb,passworddb;

returnclass rc = new returnclass();
userdb = rc.scalarReturn("select count(ad_id) from administator where ad_name='" + user + "'");

if (userdb.Equals("0"))
{
MessageBox.Show("Invalid user name!");
}
else
{
passworddb = rc.scalarReturn("select ad_password from administator where ad_name='" + user + "'");

if (passworddb.Equals(password))
{
fk_ad= rc.scalarReturn("select ad_id from administator where ad_name='" + user + "'");

Form2 f=new Form2();
f.Show();

}
else
{
MessageBox.Show("Invalid Password!");
}

}

Sunday 17 September 2017

Notepad In C#

How to change color:

ColorDialog dlg = new ColorDialog();

if (dlg.ShowDialog() == DialogResult.OK)
{

richTextBox1.SelectionColor = dlg.Color;
}

How to change font:

FontDialog dlg = new FontDialog();

if (dlg.ShowDialog() == DialogResult.OK)
{
richTextBox1.SelectionFont = new Font(dlg.Font.Name, dlg.Font.Size, FontStyle.Bold);
}

How to open new file:

OpenFileDialog dlg = new OpenFileDialog();

if (dlg.ShowDialog() == DialogResult.OK)
{
string fileName;
fileName = dlg.FileName;
if (File.Exists(fileName)==true)
{
StreamReader objreader=new StreamReader(fileName);
richTextBox1.Text = objreader.ReadToEnd();
objreader.Close();

}

}

How to Save a new file:

SaveFileDialog saveFileDialog1 = new SaveFileDialog();

saveFileDialog1.Filter = "txt files (*.txt)|*.txt|All files (*.*)|*.*";
saveFileDialog1.FilterIndex = 2;
saveFileDialog1.RestoreDirectory = true;

if (saveFileDialog1.ShowDialog() == DialogResult.OK)
{

string path = saveFileDialog1.FileName;
byte[] plainDataArray = ASCIIEncoding.ASCII.GetBytes(richTextBox1.Text);
using (var fileStream = new FileStream(path, FileMode.OpenOrCreate, FileAccess.Write))
{
fileStream.Write(plainDataArray, 0, plainDataArray.GetLength(0));

}
}

Saturday 16 September 2017

Databse Application # 1 (Complete Database Script)

Copy and paste the below script in sql server management studio

Steps:
1- open sql server management studion
2-click new query
3-copy the below script and paste on sql server management studio.

you ll get this database in your sql server management studio

USE [master]
GO
/****** Object: Database [schoolm] Script Date: 9/16/2017 12:57:48 PM ******/
CREATE DATABASE [schoolm]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'schoolm', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\schoolm.mdf' , SIZE = 3136KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'schoolm_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\schoolm_log.ldf' , SIZE = 784KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
ALTER DATABASE [schoolm] SET COMPATIBILITY_LEVEL = 110
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [schoolm].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [schoolm] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [schoolm] SET ANSI_NULLS OFF
GO
ALTER DATABASE [schoolm] SET ANSI_PADDING OFF
GO
ALTER DATABASE [schoolm] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [schoolm] SET ARITHABORT OFF
GO
ALTER DATABASE [schoolm] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [schoolm] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [schoolm] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [schoolm] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [schoolm] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [schoolm] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [schoolm] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [schoolm] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [schoolm] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [schoolm] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [schoolm] SET ENABLE_BROKER
GO
ALTER DATABASE [schoolm] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [schoolm] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [schoolm] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [schoolm] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [schoolm] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [schoolm] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [schoolm] SET HONOR_BROKER_PRIORITY OFF
GO
ALTER DATABASE [schoolm] SET RECOVERY FULL
GO
ALTER DATABASE [schoolm] SET MULTI_USER
GO
ALTER DATABASE [schoolm] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [schoolm] SET DB_CHAINING OFF
GO
ALTER DATABASE [schoolm] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF )
GO
ALTER DATABASE [schoolm] SET TARGET_RECOVERY_TIME = 0 SECONDS
GO
USE [schoolm]
GO
/****** Object: StoredProcedure [dbo].[delete_student] Script Date: 9/16/2017 12:57:48 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[delete_student]
(
@std_id int
)
as
begin

delete from student_img where img_fk=@std_id
delete from student where std_id=@std_id

end

GO
/****** Object: StoredProcedure [dbo].[insert_student] Script Date: 9/16/2017 12:57:48 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[insert_student]
(
@std_name nvarchar(20),
@std_fname nvarchar(20),
@std_gender nvarchar(6),
@std_address nvarchar(100),
@std_admissiondate nvarchar(20),
@std_ad_fk_id int

)
as

begin

insert into student(std_name,std_fname,std_gender,std_address,std_admissiondate,std_ad_fk_id)
values(@std_name,@std_fname,@std_gender,@std_address,@std_admissiondate,@std_ad_fk_id)

end

GO
/****** Object: StoredProcedure [dbo].[insert_student_img] Script Date: 9/16/2017 12:57:48 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[insert_student_img]
(
@img_path nvarchar(max),
@img_fk int
)
as

begin

insert into student_img(img_path,img_fk)
values(@img_path,@img_fk)

end

GO
/****** Object: StoredProcedure [dbo].[insert_tbladmin] Script Date: 9/16/2017 12:57:48 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[insert_tbladmin]
(
@ad_name nvarchar(20) ,
@ad_password nvarchar(20)

)
as

begin
insert into administator(ad_name,ad_password)
values(@ad_name,@ad_password)

end

GO
/****** Object: StoredProcedure [dbo].[insert_tblfees] Script Date: 9/16/2017 12:57:48 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[insert_tblfees]
(
@fee_amount float ,
@fee_date nvarchar(20),
@fee_fk_st_id int

)
as

begin

insert into fees(fee_amount,fee_date,fee_fk_st_id)
values(@fee_amount,@fee_date,@fee_fk_st_id)

end

GO
/****** Object: StoredProcedure [dbo].[insert_tblstudent] Script Date: 9/16/2017 12:57:48 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[insert_tblstudent]
(
@std_name nvarchar(20) ,
@std_fname nvarchar(20),
@std_gender nvarchar(6),
@std_address nvarchar(100) ,
@std_admissiondate nvarchar(20),
@std_ad_fk_id int
)
as
begin

insert into student(std_name,std_fname,std_gender,std_address,std_admissiondate,std_ad_fk_id)
values(@std_name,@std_fname,@std_gender,@std_address,@std_admissiondate,@std_ad_fk_id)
end

GO
/****** Object: StoredProcedure [dbo].[UPDATE_STUDENT] Script Date: 9/16/2017 12:57:48 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[UPDATE_STUDENT]
(
@std_id int,
@std_name nvarchar(20),
@std_fname nvarchar(20),
@std_gender nvarchar(6),
@std_address nvarchar(100)
)
as

begin

update student

set std_name=@std_name,
std_fname=@std_fname,
std_gender=@std_gender,
std_address=@std_address

where std_id=@std_id

end

GO
/****** Object: Table [dbo].[administator] Script Date: 9/16/2017 12:57:48 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[administator](
[ad_id] [int] IDENTITY(1,1) NOT NULL,
[ad_name] [nvarchar](20) NOT NULL,
[ad_password] [nvarchar](20) NOT NULL,
PRIMARY KEY CLUSTERED
(
[ad_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object: Table [dbo].[fees] Script Date: 9/16/2017 12:57:48 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[fees](
[fee_id] [int] IDENTITY(1,1) NOT NULL,
[fee_amount] [float] NOT NULL,
[fee_date] [nvarchar](20) NOT NULL,
[fee_fk_st_id] [int] NULL,
PRIMARY KEY CLUSTERED
(
[fee_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object: Table [dbo].[student] Script Date: 9/16/2017 12:57:48 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[student](
[std_id] [int] IDENTITY(1,1) NOT NULL,
[std_name] [nvarchar](20) NOT NULL,
[std_fname] [nvarchar](20) NOT NULL,
[std_gender] [nvarchar](6) NOT NULL,
[std_address] [nvarchar](100) NOT NULL,
[std_admissiondate] [nvarchar](20) NOT NULL,
[std_ad_fk_id] [int] NULL,
PRIMARY KEY CLUSTERED
(
[std_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object: Table [dbo].[student_img] Script Date: 9/16/2017 12:57:48 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[student_img](
[img_id] [int] IDENTITY(1,1) NOT NULL,
[img_path] [nvarchar](max) NOT NULL,
[img_fk] [int] NULL,
PRIMARY KEY CLUSTERED
(
[img_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET IDENTITY_INSERT [dbo].[administator] ON

INSERT [dbo].[administator] ([ad_id], [ad_name], [ad_password]) VALUES (1, N'salmnan', N'password123')
INSERT [dbo].[administator] ([ad_id], [ad_name], [ad_password]) VALUES (2, N'raza', N'password113')
SET IDENTITY_INSERT [dbo].[administator] OFF
SET IDENTITY_INSERT [dbo].[fees] ON

INSERT [dbo].[fees] ([fee_id], [fee_amount], [fee_date], [fee_fk_st_id]) VALUES (1, 6000, N'26/8/2017', 3)
INSERT [dbo].[fees] ([fee_id], [fee_amount], [fee_date], [fee_fk_st_id]) VALUES (2, 6000, N'26/8/2017', 2)
SET IDENTITY_INSERT [dbo].[fees] OFF
SET IDENTITY_INSERT [dbo].[student] ON

INSERT [dbo].[student] ([std_id], [std_name], [std_fname], [std_gender], [std_address], [std_admissiondate], [std_ad_fk_id]) VALUES (2, N'ahsan', N'ahmed', N'male', N't-3t2 north khi', N'8/26/2017', 1)
INSERT [dbo].[student] ([std_id], [std_name], [std_fname], [std_gender], [std_address], [std_admissiondate], [std_ad_fk_id]) VALUES (3, N'sunny', N'ahmed', N'male', N'e-3q2 north khi', N'8/26/2017', 2)
INSERT [dbo].[student] ([std_id], [std_name], [std_fname], [std_gender], [std_address], [std_admissiondate], [std_ad_fk_id]) VALUES (2004, N'jen', N'peter', N'male', N'i-32 street 5', N'9/12/2017', 2)
INSERT [dbo].[student] ([std_id], [std_name], [std_fname], [std_gender], [std_address], [std_admissiondate], [std_ad_fk_id]) VALUES (2005, N'sami', N'ali', N'male', N'y-434', N'9/12/2017', 2)
INSERT [dbo].[student] ([std_id], [std_name], [std_fname], [std_gender], [std_address], [std_admissiondate], [std_ad_fk_id]) VALUES (3004, N'ali ahmed', N'ahmed', N'male', N'y-3232', N'9/14/2017', 2)
SET IDENTITY_INSERT [dbo].[student] OFF
SET IDENTITY_INSERT [dbo].[student_img] ON

INSERT [dbo].[student_img] ([img_id], [img_path], [img_fk]) VALUES (6, N'C:\Users\salman\Documents\Visual Studio 2013\Projects\WindowsFormsApplication4\WindowsFormsApplication4\Resources\2005.jpg', 2005)
SET IDENTITY_INSERT [dbo].[student_img] OFF
SET ANSI_PADDING ON

GO
/****** Object: Index [UQ__administ__84D9FB7ADFDCEA85] Script Date: 9/16/2017 12:57:48 PM ******/
ALTER TABLE [dbo].[administator] ADD UNIQUE NONCLUSTERED
(
[ad_name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE [dbo].[fees] WITH CHECK ADD FOREIGN KEY([fee_fk_st_id])
REFERENCES [dbo].[student] ([std_id])
GO
ALTER TABLE [dbo].[student] WITH CHECK ADD FOREIGN KEY([std_ad_fk_id])
REFERENCES [dbo].[administator] ([ad_id])
GO
ALTER TABLE [dbo].[student_img] WITH CHECK ADD FOREIGN KEY([img_fk])
REFERENCES [dbo].[student] ([std_id])
GO
USE [master]
GO
ALTER DATABASE [schoolm] SET READ_WRITE
GO