Note:
Goto Project Menu
->Add Reference -> select 'Microsoft.VisualBasic' from .NET tab; include namespace ‘using Microsoft.VisualBasic’(To get input box)
---------
Add OpenFileDialog control to the form, which appears below the form
Add PictureBox Control and change properties as following:
->BorderStyle=Fixed3D; SizeMode=StrechImage
----------------------------------------------------
Code:
using System;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Drawing.Imaging;
using System.IO;
using Microsoft.VisualBasic;
namespace prash_studentinfo
{
public partial class student_info : Form
{
public student_info()
{
InitializeComponent();
}
SqlConnection con;
SqlCommand cmd;
SqlDataAdapter adapter;
DataSet ds; int rptr = 0;
MemoryStream ms;
byte[] photo_aray;
private void student_info_Load(object sender, EventArgs e)
{
con = new SqlConnection("user id=sa;password=123;database=prash");
loaddata();
showdata();
}
void loaddata()
{
cmd = new SqlCommand("get_student", con);
cmd.CommandType = CommandType.StoredProcedure; adapter = new SqlDataAdapter(cmd);
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
ds = new DataSet(); adapter.Fill(ds, "student");
}
void showdata()
{
if (ds.Tables[0].Rows.Count > 0)
{
textBox1.Text = ds.Tables[0].Rows[rptr][0].ToString();
textBox2.Text = ds.Tables[0].Rows[rptr][1].ToString();
textBox3.Text = ds.Tables[0].Rows[rptr][2].ToString();
textBox4.Text = ds.Tables[0].Rows[rptr][3].ToString();
pictureBox1.Image = null;
if (ds.Tables[0].Rows[rptr][4]!=System.DBNull.Value)
{
photo_aray = (byte[])ds.Tables[0].Rows[rptr][4];
MemoryStream ms = new MemoryStream(photo_aray);
pictureBox1.Image = Image.FromStream(ms);
}
}
else
MessageBox.Show("No Records");
}
private void browse_Click(object sender, EventArgs e)
{
openFileDialog1.Filter = "jpeg|*.jpg|bmp|*.bmp|all files|*.*";
DialogResult res = openFileDialog1.ShowDialog();
if (res == DialogResult.OK)
{
pictureBox1.Image = Image.FromFile(openFileDialog1.FileName);
}
}
private void clear_Click(object sender, EventArgs e)
{
textBox1.Text = textBox2.Text = textBox3.Text = textBox4.Text = "";
pictureBox1.Image = null;
}
private void insert_Click(object sender, EventArgs e)
{
cmd = new SqlCommand("insert_student", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@sno", textBox1.Text);
cmd.Parameters.AddWithValue("@sname", textBox2.Text);
cmd.Parameters.AddWithValue("@course", textBox3.Text);
cmd.Parameters.AddWithValue("@fee", textBox4.Text);
conv_photo();
con.Open();
int n = cmd.ExecuteNonQuery();
con.Close();
if (n > 0)
{
MessageBox.Show("record inserted");
loaddata();
}
else
MessageBox.Show("insertion failed");
}
void conv_photo()
{
//converting photo to binary data
if (pictureBox1.Image != null)
{
//using FileStream: (will not work in updating record without changing photo)
//FileStream fs = new FileStream(openFileDialog1.FileName, FileMode.Open, FileAccess.Read);
//byte[] photo_aray = new byte[fs.Length];
//fs.Read(photo_aray, 0, photo_aray.Length);
//using MemoryStream: (works for updating record without changing photo)
ms = new MemoryStream();
pictureBox1.Image.Save(ms, ImageFormat.Jpeg);
byte[] photo_aray = new byte[ms.Length];
ms.Position = 0;
ms.Read(photo_aray, 0, photo_aray.Length);
cmd.Parameters.AddWithValue("@photo", photo_aray);
}
}
private void search_Click(object sender, EventArgs e)
{
try
{
int n = Convert.ToInt32(Interaction.InputBox("Enter sno:", "Search", "20", 100, 100));
DataRow drow;
drow = ds.Tables[0].Rows.Find(n);
if (drow != null)
{
textBox1.Text = drow[0].ToString();
textBox2.Text = drow[1].ToString();
textBox3.Text = drow[2].ToString();
textBox4.Text = drow[3].ToString();
pictureBox1.Image = null;
if (drow[4] != System.DBNull.Value)
{
photo_aray = (byte[])drow[4];
MemoryStream ms = new MemoryStream(photo_aray);
pictureBox1.Image = Image.FromStream(ms);
}
}
else
MessageBox.Show("Record Not Found");
}
catch
{
MessageBox.Show("Invalid Input");
}
}
private void update_Click(object sender, EventArgs e)
{
cmd = new SqlCommand("update_student", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@sno", textBox1.Text);
cmd.Parameters.AddWithValue("@sname", textBox2.Text);
cmd.Parameters.AddWithValue("@course", textBox3.Text);
cmd.Parameters.AddWithValue("@fee", textBox4.Text);
conv_photo();
con.Open();
int n = cmd.ExecuteNonQuery();
con.Close();
if (n > 0)
{
MessageBox.Show("Record Updated");
loaddata();
}
else
MessageBox.Show("Updation Failed");
}
private void delete_Click(object sender, EventArgs e)
{
cmd = new SqlCommand("delete_student", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@sno", textBox1.Text);
con.Open();
int n = cmd.ExecuteNonQuery();
con.Close();
if (n > 0)
{
MessageBox.Show("Record Deleted");
loaddata();
rptr = 0;
showdata();
}
else
MessageBox.Show("Deletion Failed");
}
private void first_Click(object sender, EventArgs e)
{
rptr = 0; showdata();
MessageBox.Show("First record");
}
private void previous_Click(object sender, EventArgs e)
{
if (rptr > 0)
{
rptr--; showdata();
}
else
MessageBox.Show("First record");
}
private void next_Click(object sender, EventArgs e)
{
if (rptr < ds.Tables[0].Rows.Count - 1)
{
rptr++; showdata();
}
else
MessageBox.Show("Last record");
}
private void last_Click(object sender, EventArgs e)
{
rptr = ds.Tables[0].Rows.Count - 1;
showdata(); MessageBox.Show("Last record");
}
private void exit_Click(object sender, EventArgs e)
{
this.Close();
}
}
}
---------------------------------
Query for creating table:
create table student(sno int primary key,sname varchar(50),course varchar(50),fee money,photo image)
---------------------------------
Stored procedures:
create procedure get_student
as
select sno,sname, course, fee, photo from student
---------------
create procedure insert_student
(@sno int, @sname varchar(50), @course varchar(50), @fee smallmoney,@photo image=null)
as
insert into student(sno, sname,course, fee, photo) values (@sno, @sname, @course, @fee, @photo)
---------------
create procedure update_student
(@sno int, @sname varchar(50),@course varchar(50), @fee smallmoney,@photo image=null)
as
update student set sname=@sname, course=@course, fee=@fee, photo=@photo where sno=@sno
---------------
create procedure delete_student
(@sno int=null)
as
if not(@sno=null)
delete from student where sno=@sno
