The complete code is below
using System;
using System.Data;
using System.Windows.Forms;
using System.Configuration;
using System.Data.SqlClient;
using DatabaseDemo2.SystemConstants;
using System.Drawing;
using System.IO;
using DatabaseDemo2.Properties;
using System.Collections;
namespace DatabaseDemo2
{
public partial class StudentInfoForm : Form
{
public StudentInfoForm()
{
InitializeComponent();
}
// StudentId
private int studentId = 0;
public int StudentId
{
get { return studentId; }
set { studentId = value; }
}
// IsUpdate
private bool isUpdate = false;
public bool IsUpdate
{
get { return isUpdate; }
set { isUpdate = value; }
}
// Original Row Version
public byte[] OriginalRowVersion { get; set; }
public enum Gender
{
NoSelection = 0,
Male = 1,
Female = 2
}
private void CloseButton_Click(object sender, EventArgs e)
{
this.Close();
}
private void SaveButton_Click(object sender, EventArgs e)
{
if (this.IsUpdate)
{
// If Data is already Updated then show the user message and reload the fresh data for update.
if (IfDataNotUpdated(OriginalRowVersion, GetCurrentRowVersion()))
{
UpdateStudentDetails();
MessageBox.Show("Student Info is updated.", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
MessageBox.Show("The record is updated by another user. Record will be reloaded now. Please update the record again.",
"Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
// Reload the data
LoadAndMapDataToControlIfUpdate();
}
}
else
{
// Execute Insert code
InsertStudentDetails(out this.studentId);
this.IsUpdate = true;
MessageBox.Show("Student Info is added to the system.", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
private byte[] GetCurrentRowVersion()
{
byte[] currentRowVersion = new byte[8];
string connString = ConfigurationManager.ConnectionStrings["dbx"].ConnectionString;
using (SqlConnection conn = new SqlConnection(connString))
{
using (SqlCommand cmd = new SqlCommand("usp_GetCurrentRowVersion", conn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@StudentId", this.StudentId);
conn.Open();
currentRowVersion = (byte[])cmd.ExecuteScalar();
}
}
return currentRowVersion;
}
private bool IfDataNotUpdated(byte[] originalRowVersion, byte[] currentRowVersion)
{
return StructuralComparisons.StructuralEqualityComparer.Equals(originalRowVersion, currentRowVersion);
}
private void UpdateStudentDetails()
{
string connString = ConfigurationManager.ConnectionStrings["dbx"].ConnectionString;
using (SqlConnection conn = new SqlConnection(connString))
{
using (SqlCommand cmd = new SqlCommand("usp_StudentUpdateDetails", conn))
{
cmd.CommandType = CommandType.StoredProcedure;
// Parameter
cmd.Parameters.AddWithValue("@StudentId", this.StudentId);
cmd.Parameters.AddWithValue("@Name", NameTextBox.Text);
cmd.Parameters.AddWithValue("Email", EmailTextBox.Text);
cmd.Parameters.AddWithValue("@IsInterestInCSharp", IsCSharpCheckBox.Checked);
cmd.Parameters.AddWithValue("@IsInterestedInVB", IsVBCheckBox.Checked);
cmd.Parameters.AddWithValue("@IsInterestedInJava", IsJavaCheckBox.Checked);
cmd.Parameters.AddWithValue("@GenderId", GetGender());
cmd.Parameters.AddWithValue("@DateOfBirth", (DOBDateTimePicker.Text.Trim() == string.Empty) ? (DateTime?)null : DOBDateTimePicker.Value.Date);
cmd.Parameters.AddWithValue("@StartTime", (StartTimeDateTimePicker.Text.Trim() == string.Empty) ? (TimeSpan?)null : StartTimeDateTimePicker.Value.TimeOfDay);
cmd.Parameters.AddWithValue("@EndTime", (EndTimeDateTimePicker.Text.Trim() == string.Empty) ? (TimeSpan?)null : EndTimeDateTimePicker.Value.TimeOfDay);
cmd.Parameters.AddWithValue("@FundTypeId", (FundTypeComboBox.SelectedIndex == -1) ? 0 : FundTypeComboBox.SelectedValue);
cmd.Parameters.AddWithValue("@FeesPaymentId", (FeesPaymentComboBox.SelectedIndex == -1) ? 0 : FeesPaymentComboBox.SelectedValue);
cmd.Parameters.AddWithValue("@Comments", CommentsTextBox.Text);
cmd.Parameters.AddWithValue("@Address", AddressTextBox.Text);
cmd.Parameters.AddWithValue("@LocalityId", (LocalityComboBox.SelectedIndex == -1) ? 0 : LocalityComboBox.SelectedValue);
cmd.Parameters.AddWithValue("@CityId", (CityComboBox.SelectedIndex == -1) ? 0 : CityComboBox.SelectedValue);
cmd.Parameters.AddWithValue("@PostCode", PostCodeTextBox.Text);
cmd.Parameters.AddWithValue("@Photo", SavePhoto());
// Open Connection
conn.Open();
// ExecuteReader (Select statement)
// ExecuteScalar (Select statement)
// ExecuteNoQuery (Insert, Update or Delete)
cmd.ExecuteNonQuery();
}
}
}
private void InsertStudentDetails(out int studentId)
{
string connString = ConfigurationManager.ConnectionStrings["dbx"].ConnectionString;
using (SqlConnection conn = new SqlConnection(connString))
{
using (SqlCommand cmd = new SqlCommand("usp_StudentInsertDetails", conn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@StudentId", SqlDbType.Int).Direction = ParameterDirection.Output;
// Parameter
cmd.Parameters.AddWithValue("@Name", NameTextBox.Text);
cmd.Parameters.AddWithValue("Email", EmailTextBox.Text);
cmd.Parameters.AddWithValue("@IsInterestInCSharp", IsCSharpCheckBox.Checked);
cmd.Parameters.AddWithValue("@IsInterestedInVB", IsVBCheckBox.Checked);
cmd.Parameters.AddWithValue("@IsInterestedInJava", IsJavaCheckBox.Checked);
cmd.Parameters.AddWithValue("@GenderId", GetGender());
cmd.Parameters.AddWithValue("@DateOfBirth", (DOBDateTimePicker.Text.Trim() == string.Empty) ? (DateTime?)null : DOBDateTimePicker.Value.Date);
cmd.Parameters.AddWithValue("@StartTime", (StartTimeDateTimePicker.Text.Trim() == string.Empty) ? (TimeSpan?)null : StartTimeDateTimePicker.Value.TimeOfDay);
cmd.Parameters.AddWithValue("@EndTime", (EndTimeDateTimePicker.Text.Trim() == string.Empty) ? (TimeSpan?)null : EndTimeDateTimePicker.Value.TimeOfDay);
cmd.Parameters.AddWithValue("@FundTypeId", (FundTypeComboBox.SelectedIndex == -1) ? 0 : FundTypeComboBox.SelectedValue);
cmd.Parameters.AddWithValue("@FeesPaymentId", (FeesPaymentComboBox.SelectedIndex == -1) ? 0 : FeesPaymentComboBox.SelectedValue);
cmd.Parameters.AddWithValue("@Comments", CommentsTextBox.Text);
cmd.Parameters.AddWithValue("@Address", AddressTextBox.Text);
cmd.Parameters.AddWithValue("@LocalityId", (LocalityComboBox.SelectedIndex == -1) ? 0 : LocalityComboBox.SelectedValue);
cmd.Parameters.AddWithValue("@CityId", (CityComboBox.SelectedIndex == -1) ? 0 : CityComboBox.SelectedValue);
cmd.Parameters.AddWithValue("@PostCode", PostCodeTextBox.Text);
cmd.Parameters.AddWithValue("@Photo", SavePhoto());
// Open Connection
conn.Open();
// ExecuteReader (Select statement)
// ExecuteScalar (Select statement)
// ExecuteNoQuery (Insert, Update or Delete)
cmd.ExecuteNonQuery();
studentId = Convert.ToInt16(cmd.Parameters["@StudentId"].Value);
}
}
}
private byte[] SavePhoto()
{
MemoryStream ms = new MemoryStream();
StudentImagePictureBox.Image.Save(ms, StudentImagePictureBox.Image.RawFormat);
return ms.GetBuffer();
}
//private TimeSpan? GetEndTime()
//{
// if (EndTimeDateTimePicker.Text.Trim() == string.Empty)
// {
// return null;
// }
// else
// {
// return EndTimeDateTimePicker.Value.TimeOfDay;
// }
//}
//private TimeSpan? GetStartTime()
//{
// if (StartTimeDateTimePicker.Text.Trim() == string.Empty)
// {
// return null;
// }
// else
// {
// return StartTimeDateTimePicker.Value.TimeOfDay;
// }
//}
//private DateTime? GetDateOfBirth()
//{
// if (DOBDateTimePicker.Text.Trim() == string.Empty)
// {
// return (DateTime?)null;
// }
// else
// {
// return DOBDateTimePicker.Value.Date;
// }
//}
private int GetGender()
{
if (MaleRadioButton.Checked)
{
return (int)Gender.Male;
}
if (FemaleRadioButton.Checked)
{
return (int)Gender.Female;
}
return (int)Gender.NoSelection;
}
private void DOBDateTimePicker_ValueChanged(object sender, EventArgs e)
{
if (DOBDateTimePicker.Value == DOBDateTimePicker.MinDate)
{
DOBDateTimePicker.CustomFormat = " ";
}
else
{
DOBDateTimePicker.CustomFormat = "dd/MM/yyyy";
}
}
private void DOBDateTimePicker_KeyDown(object sender, KeyEventArgs e)
{
if ((e.KeyCode == Keys.Back) || (e.KeyCode == Keys.Delete))
{
DOBDateTimePicker.CustomFormat = " ";
}
}
private void Time_ValueChanged(object sender, EventArgs e)
{
DateTimePicker dtp = (DateTimePicker)sender;
if (dtp.Value == dtp.MinDate)
{
GetCustomTimeFormat(sender, " ");
}
else
{
GetCustomTimeFormat(sender, "HH:mm");
}
}
private void GetCustomTimeFormat(object sender, string format)
{
DateTimePicker dtp = (DateTimePicker)sender;
dtp.CustomFormat = format;
}
private void Time_KeyDown(object sender, KeyEventArgs e)
{
if ((e.KeyCode == Keys.Back) || (e.KeyCode == Keys.Delete))
{
GetCustomTimeFormat(sender, " ");
}
}
private void Time_MouseDown(object sender, MouseEventArgs e)
{
GetCustomTimeFormat(sender, "HH:mm");
}
private void StudentInfoForm_Load(object sender, EventArgs e)
{
LoadDataIntoComboBoxes();
LoadAndMapDataToControlIfUpdate();
}
private void LoadAndMapDataToControlIfUpdate()
{
if (this.IsUpdate)
{
DataTable dtStudentInfo = GetStudentInfoById(this.StudentId);
DataRow row = dtStudentInfo.Rows[0];
NameTextBox.Text = row["Name"].ToString();
EmailTextBox.Text = row["Email"].ToString();
IsCSharpCheckBox.Checked = (row["IsInterestedInCSharp"] is DBNull) ? false : Convert.ToBoolean(row["IsInterestedInCSharp"]);
IsVBCheckBox.Checked = (row["IsInterestedInVB"] is DBNull) ? false : Convert.ToBoolean(row["IsInterestedInVB"]);
IsJavaCheckBox.Checked = (row["IsInterestedInJava"] is DBNull) ? false : Convert.ToBoolean(row["IsInterestedInJava"]);
MaleRadioButton.Checked = (row["GenderId"] is DBNull) ? false : (Convert.ToInt16(row["GenderId"]) == 1) ? true : false;
FemaleRadioButton.Checked = (row["GenderId"] is DBNull) ? false : (Convert.ToInt16(row["GenderId"]) == 2) ? true : false;
DOBDateTimePicker.Value = (row["DateOfBirth"] is DBNull) ? DOBDateTimePicker.MinDate : Convert.ToDateTime(row["DateOfBirth"]).Date;
StartTimeDateTimePicker.Value = (row["StartTime"] is DBNull) ? StartTimeDateTimePicker.MinDate : Convert.ToDateTime(row["StartTime"]);
EndTimeDateTimePicker.Value = (row["EndTime"] is DBNull) ? StartTimeDateTimePicker.MinDate : Convert.ToDateTime(row["EndTime"]);
FundTypeComboBox.SelectedValue = row["FundTypeId"];
FeesPaymentComboBox.SelectedValue = row["FeesPaymentId"];
AddressTextBox.Text = row["Address"].ToString();
CityComboBox.SelectedValue = row["CityId"];
LocalityComboBox.SelectedValue = row["LocalityId"];
PostCodeTextBox.Text = row["PostCode"].ToString();
CommentsTextBox.Text = row["Comments"].ToString();
StudentImagePictureBox.Image = (row["Photo"] is DBNull) ? Resources.No_image_available : GetPhoto((byte[])row["Photo"]);
this.OriginalRowVersion = (byte[])row["OriginalRowVersion"];
}
}
private Image GetPhoto(byte[] photo)
{
MemoryStream ms = new MemoryStream(photo);
return Image.FromStream(ms);
}
private DataTable GetStudentInfoById(int studentId)
{
DataTable dtStudentById = new DataTable();
string connString = ConfigurationManager.ConnectionStrings["dbx"].ConnectionString;
using (SqlConnection conn = new SqlConnection(connString))
{
using (SqlCommand cmd = new SqlCommand("usp_StudentGetStudentInfoById", conn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@StudentId", studentId);
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
dtStudentById.Load(reader);
}
}
return dtStudentById;
}
private void LoadDataIntoComboBoxes()
{
FundTypeComboBox.DataSource = GetListData((int)ListDataTypes.FundType);
FundTypeComboBox.DisplayMember = "Description";
FundTypeComboBox.ValueMember = "ListDataId";
FundTypeComboBox.SelectedIndex = -1;
FeesPaymentComboBox.DataSource = GetListData((int)ListDataTypes.FeesPayment);
FeesPaymentComboBox.DisplayMember = "Description";
FeesPaymentComboBox.ValueMember = "ListDataId";
FeesPaymentComboBox.SelectedIndex = -1;
CityComboBox.SelectedValueChanged -= new EventHandler(CityComboBox_SelectedValueChanged);
CityComboBox.DataSource = GetAllCities();
CityComboBox.DisplayMember = "Description";
CityComboBox.ValueMember = "CityId";
CityComboBox.SelectedIndex = -1;
CityComboBox.SelectedValueChanged += new EventHandler(CityComboBox_SelectedValueChanged);
}
private DataTable GetAllCities()
{
DataTable dtCities = new DataTable();
string connString = ConfigurationManager.ConnectionStrings["dbx"].ConnectionString;
using (SqlConnection conn = new SqlConnection(connString))
{
using (SqlCommand cmd = new SqlCommand("usp_GetAllCities", conn))
{
cmd.CommandType = CommandType.StoredProcedure;
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
dtCities.Load(reader);
}
}
return dtCities;
}
// Objects
// DataSet
// DataTable
// Array
// Collection
// Generics
private DataTable GetListData(int listDataTypeId)
{
DataTable dtListData = new DataTable();
string connString = ConfigurationManager.ConnectionStrings["dbx"].ConnectionString;
using (SqlConnection conn = new SqlConnection(connString))
{
using (SqlCommand cmd = new SqlCommand("usp_GetAllListData", conn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ListDataTypeId", listDataTypeId);
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
dtListData.Load(reader);
}
}
return dtListData;
}
private void CityComboBox_SelectedValueChanged(object sender, EventArgs e)
{
LocalityComboBox.DataSource = GetAllLocalitiesByCityId((int)CityComboBox.SelectedValue);
LocalityComboBox.DisplayMember = "Description";
LocalityComboBox.ValueMember = "LocalityId";
}
private DataTable GetAllLocalitiesByCityId(int cityId)
{
DataTable dtLocalities = new DataTable();
string connString = ConfigurationManager.ConnectionStrings["dbx"].ConnectionString;
using (SqlConnection conn = new SqlConnection(connString))
{
using (SqlCommand cmd = new SqlCommand("usp_GetAllLocalitiesByCityId", conn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@CityId", cityId);
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
dtLocalities.Load(reader);
}
}
return dtLocalities;
}
private void StudentImagePictureBox_Click(object sender, EventArgs e)
{
OpenFileDialog ofd = new OpenFileDialog();
ofd.Title = "Select the Photo";
//ofd.Filter = "PNG File (*.png)|*.png|JPG File (*.jpg)|*.jpg|BMP File (*.bmp)|*.bmp|Gif File (*.gif)|*.gif";
ofd.Filter = "Image File (*.png;*.jpg;*.bmp;*.gif)|*.png;*.jpg;*.bmp;*.gif";
if (ofd.ShowDialog() == DialogResult.OK)
{
StudentImagePictureBox.Image = new Bitmap(ofd.FileName);
}
}
}
}
Stored Procedure: usp_GetAllListData
CREATE PROCEDURE [dbo].[usp_GetAllListData]
(
@ListDataTypeId INT
)
AS
BEGIN
SELECT [ListDataId]
,[Description]
FROM [dbo].[ListData]
WHERE [ListDataTypeId] = @ListDataTypeId
END
Stored Procedure: usp_GetAllCities
CREATE PROCEDURE [dbo].[usp_GetAllCities]
AS
BEGIN
SELECT [CityId]
,[Description]
FROM [dbo].[Cities]
END
Stored Procedure: usp_StudentGetStudentInfoById
CREATE PROCEDURE [dbo].[usp_StudentGetStudentInfoById]
(
@StudentId INT
)
AS
BEGIN
SELECT [StudentId]
,[Name]
,[Email]
,[IsInterestedInCSharp]
,[IsInterestedInVB]
,[IsInterestedInJava]
,[GenderId]
,[DateOfBirth]
,[StartTime]
,[EndTime]
,[FundTypeId]
,[FeesPaymentId]
,[Comments]
,[Address]
,[LocalityId]
,[CityId]
,[PostCode]
,[Photo]
,[OriginalRowVersion]
FROM [dbo].[Students]
WHERE [StudentId] = @StudentId
END
Stored Procedure: usp_GetAllLocalitiesByCityId
CREATE PROCEDURE [dbo].[usp_GetAllLocalitiesByCityId]
(
@CityId INT
)
AS
BEGIN
SELECT [LocalityId]
,[Description]
FROM [dbo].[Localities]
WHERE [CityId] = @CityId
END
GO
Stored Procedure: usp_StudentInsertDetails
CREATE PROCEDURE [dbo].[usp_StudentInsertDetails]
(
@StudentId INT OUTPUT
,@Name NVARCHAR(50)
,@Email NVARCHAR(50)
,@IsInterestInCSharp BIT
,@IsInterestedInVB BIT
,@IsInterestedInJava BIT
,@GenderId INT
,@DateOfBirth DATETIME = null
,@StartTime DATETIME = null
,@EndTime DATETIME = null
,@FundTypeId INT
,@FeesPaymentId INT
,@Comments NVARCHAR(MAX)
,@Address NVARCHAR(1500)
,@LocalityId INT
,@CityId INT
,@PostCode NVARCHAR(50)
,@Photo IMAGE
)
AS
BEGIN
INSERT INTO [dbo].[Students]
([Name]
,[Email]
,[IsInterestedInCSharp]
,[IsInterestedInVB]
,[IsInterestedInJava]
,[GenderId]
,[DateOfBirth]
,[StartTime]
,[EndTime]
,[FundTypeId]
,[FeesPaymentId]
,[Comments]
,[Address]
,[LocalityId]
,[CityId]
,[PostCode]
,[Photo])
VALUES
(@Name
,@Email
,@IsInterestInCSharp
,@IsInterestedInVB
,@IsInterestedInJava
,@GenderId
,@DateOfBirth
,@StartTime
,@EndTime
,@FundTypeId
,@FeesPaymentId
,@Comments
,@Address
,@LocalityId
,@CityId
,@PostCode
,@Photo)
SELECT @StudentId = @@IDENTITY
END
GO
Stored Procedure: usp_StudentUpdateDetails
CREATE PROCEDURE [dbo].[usp_StudentUpdateDetails]
(
@StudentId INT
,@Name NVARCHAR(50)
,@Email NVARCHAR(50)
,@IsInterestInCSharp BIT
,@IsInterestedInVB BIT
,@IsInterestedInJava BIT
,@GenderId INT
,@DateOfBirth DATETIME = null
,@StartTime DATETIME = null
,@EndTime DATETIME = null
,@FundTypeId INT
,@FeesPaymentId INT
,@Comments NVARCHAR(MAX)
,@Address NVARCHAR(1500)
,@LocalityId INT
,@CityId INT
,@PostCode NVARCHAR(50)
,@Photo IMAGE
)
AS
BEGIN
UPDATE [dbo].[Students]
SET [Name] = @Name
,[Email] = @Email
,[IsInterestedInCSharp] = @IsInterestInCSharp
,[IsInterestedInVB] = @IsInterestedInVB
,[IsInterestedInJava] = @IsInterestedInJava
,[GenderId] = @GenderId
,[DateOfBirth] = @DateOfBirth
,[StartTime] = @StartTime
,[EndTime] = @EndTime
,[FundTypeId] = @FundTypeId
,[FeesPaymentId] = @FeesPaymentId
,[Comments] = @Comments
,[Address] = @Address
,[LocalityId] = @LocalityId
,[CityId] = @CityId
,[PostCode] = @PostCode
,[Photo] = @Photo
WHERE [StudentId] = @StudentId
END
GO
Stored Procedure: usp_GetCurrentRowVersion
CREATE PROCEDURE [dbo].[usp_GetCurrentRowVersion]
(
@StudentId INT
)
AS
BEGIN
SELECT [OriginalRowVersion]
FROM [dbo].[Students]
WHERE [StudentId] = @StudentId
END
GO
