Problem
I had a situation where I had to add a record in the table, and at the same time I needed to add record in another table but I needed an Id of the first table to enter in the second second table. What must I do to achieve that.
Impact
Program will not do what it is suppose to do.
Solution
Solution to this problem is to use transaction object. Think of it this way, if we go to bank and transfer the money from one account to another, first bank has to take money out of the account and then transfer it in the other account, what happens if first transaction succeeds but the second one does't. C# uses transaction object to solve this issue. When we use transaction object both of these transactions must succeed before we enter the record in the database, if one fails all other transactions will rollback. Here is the code that I wrote where I am entering record in the data base and retrieving the id of the record just been entered and then committing to the action.
public int Save(Customer customer)
{
int roweffected = 0;
try
{
//first open the connection to the database
_connection.Open();
//begin the transaction.
using(SqlTransaction transaction = _connection.BeginTransaction())
{
//create a command object to enter a record in the database.
using (SqlCommand command = new SqlCommand())
{
command.CommandText = "Insert into Customer (FirstName,LastName,DateOfBirth,HouseNumber," +
"Street,City,Country,PostCode,Phone,Email) Values(@firstname,@lastname," +
"@dateofbirth,@housenumber,@street,@city,@country," +
"@postcode,@phone,@email)";
command.Connection = _connection;
command.Transaction = transaction;
command.Parameters.AddWithValue("@firstname", customer.FirstName);
command.Parameters.AddWithValue("@lastname", customer.LastName);
command.Parameters.AddWithValue("@dateofbirth", customer.DateOfBirth);
command.Parameters.AddWithValue("@housenumber", customer.HouseNumber);
command.Parameters.AddWithValue("@street", customer.StreetName);
command.Parameters.AddWithValue("@city", customer.City);
command.Parameters.AddWithValue("@country", customer.Country);
command.Parameters.AddWithValue("@postcode", customer.PostCode);
command.Parameters.AddWithValue("@phone", customer.Phone);
command.Parameters.AddWithValue("@email", customer.Email);
roweffected = command.ExecuteNonQuery();
}
//Create another command object to retrieve the record id you just entered.
using (SqlCommand command = new SqlCommand())
{
//this query returns the id of the last record entered in the customer table.
command.CommandText = "Select @@Identity from Customer";
command.Connection = _connection;
command.Transaction = transaction;
var value = command.ExecuteScalar();
}
transaction.Commit();
}
return roweffected;
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
Monday, March 28, 2011
Saturday, March 26, 2011
How To Save Image To SqlServer
Problem: I am currently working on my college project where I was required to save images to the database and I had no idea how to do that.
Impact:
Unable to save images to the database.
Solution:
After doing some research I found some material on this topic, so I wrote a small program for this post to share my knowledge with you guys. First create a windows form application, add four button, load, browse, save and clear, Add two text boxes, A picture box and from Dialog Tool Box select openFileDialog. Here is the screen shot of my form.
First we need to up load the image to the picture box using open file dialog. When user click on browse open file dialog will pop up and the select the image. Following code shows how to get image in picture box.
private void BrowseButton_Click(object sender, EventArgs e)
{
openFileDialog1.ShowDialog();
//_file is a string declared as a private field at class level.
_file = openFileDialog1.FileName;
pictureBox1.Image = Image.FromFile(_file);
FileBrowserTextBox.Text = _file;
}
Now this is the hard part. Once the image is loaded in the picture box and you are sure that this is the image you want to save, then click on Save button. But before we save the image, we need to convert the image into byte of array, and then pass it to the database. I used separate layer (Project) for database and created a class called ImageRepository. Image Repository does two things save and load, before I show you how to do that we need to convert the image into byte[]. Following code shows how to convert image into byte[] and then pass it to the database.
private void SaveButton_Click(object sender, EventArgs e)
{
try
{
//use ReadFile method to get byteArray
byte[] data = ReadFile();
ImageRepository repository = new ImageRepository();
int result = repository.SaveImage(data);
if(result==1)
{
MessageBox.Show(@"Image Saved Successfully");
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
private byte[] ReadFile()
{
byte[] data;
//Use FileInfo object to get file size.
FileInfo fileInfo = new FileInfo(_file);
long fileLength = fileInfo.Length;
//Open FileStream to read file
FileStream fileStream = new FileStream(_file,FileMode.Open,FileAccess.Read,FileShare.Read);
//Use BinaryReader to read file stream into byte array.
BinaryReader binaryReader = new BinaryReader(fileStream);
//When you use BinaryReader, you need to supply number of bytes
//Close the file stream
fileStream.Close();
return data;
}
Now we will use Repository to save the image.
public int SaveImage(byte[] imagedata)
{
try
{
byte[] imageData = imagedata;
//get the connectin string.
string connectionstring = @"Data Source=FARAZ-PC\FARAZ_K1;Initial Catalog=RentAMovieDB;Integrated Security=True";
SqlConnection connection = new SqlConnection(connectionstring);
connection.Open();
SqlCommand command = new SqlCommand();
//Insert image to the database. I am updating my record since I had a table with a column name Image. you could use Insert command instead of update.
command.CommandText = "UPDATE Movie SET Image = @image where Name = 'Your Highness'";
command.Connection = connection;
//Adding the parameter to the command object.
command.Parameters.AddWithValue("@image", imageData);
//if the result is 1 that means that image was saved.
int result = command.ExecuteNonQuery();
connection.Close();
return result;
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
Now we need to load the image from the database. Since I have my Image stored in Movie table, so I will use Movie name to search for the image I am looking for. Getting the image from the database is other way round, before we were converting the image into byte[] array and then saving it, now we are getting the image from that database as byte[] array and we need to convert it back into image and load it to picture box. First I am providing the name of the movie to look for the image. Here is what happens when user clicks on LoadImage button.
private void LoadButton_Click(object sender, EventArgs e)
{
try
{
ImageRepository repository = new ImageRepository();
//get the byte[] data from database using repository class.
byte[] image = repository.LoadImage(LoadTextBox.Text);
Image newImage;
using(MemoryStream memoryStream = new MemoryStream(image,0,image.Length))
{
memoryStream.Write(image,0,image.Length);
//Set image variable value using memory stream.
newImage = Image.FromStream(memoryStream, true);
}
//Set picturebox image.
pictureBox1.Image = newImage;
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
//get image using the repository class loadImage method.
public byte[] LoadImage(string movieName)
{
byte[] image = null;
string connectionstring = @"Data Source=FARAZ-PC\FARAZ_K1;Initial Catalog=RentAMovieDB;Integrated Security=True";
SqlConnection connection = new SqlConnection(connectionstring);
connection.Open();
SqlCommand command = new SqlCommand();
command.CommandText = "Select image from Movie where Name = '" + movieName+"'";
command.Connection = connection;
SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
DataSet dataset = new DataSet();
dataAdapter.Fill(dataset);
foreach (DataRow row in dataset.Tables[0].Rows)
{
image = (byte[])row["Image"];
}
return image;
}
Here is the form after loading the image from the database.
Impact:
Unable to save images to the database.
Solution:
After doing some research I found some material on this topic, so I wrote a small program for this post to share my knowledge with you guys. First create a windows form application, add four button, load, browse, save and clear, Add two text boxes, A picture box and from Dialog Tool Box select openFileDialog. Here is the screen shot of my form.
First we need to up load the image to the picture box using open file dialog. When user click on browse open file dialog will pop up and the select the image. Following code shows how to get image in picture box.
private void BrowseButton_Click(object sender, EventArgs e)
{
openFileDialog1.ShowDialog();
//_file is a string declared as a private field at class level.
_file = openFileDialog1.FileName;
pictureBox1.Image = Image.FromFile(_file);
FileBrowserTextBox.Text = _file;
}
Now this is the hard part. Once the image is loaded in the picture box and you are sure that this is the image you want to save, then click on Save button. But before we save the image, we need to convert the image into byte of array, and then pass it to the database. I used separate layer (Project) for database and created a class called ImageRepository. Image Repository does two things save and load, before I show you how to do that we need to convert the image into byte[]. Following code shows how to convert image into byte[] and then pass it to the database.
private void SaveButton_Click(object sender, EventArgs e)
{
try
{
//use ReadFile method to get byteArray
byte[] data = ReadFile();
ImageRepository repository = new ImageRepository();
int result = repository.SaveImage(data);
if(result==1)
{
MessageBox.Show(@"Image Saved Successfully");
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
private byte[] ReadFile()
{
byte[] data;
//Use FileInfo object to get file size.
FileInfo fileInfo = new FileInfo(_file);
long fileLength = fileInfo.Length;
//Open FileStream to read file
FileStream fileStream = new FileStream(_file,FileMode.Open,FileAccess.Read,FileShare.Read);
//Use BinaryReader to read file stream into byte array.
BinaryReader binaryReader = new BinaryReader(fileStream);
//When you use BinaryReader, you need to supply number of bytes
//to read from file.
//In this case we want to read entire file.
//So supplying total number of bytes.
data = binaryReader.ReadBytes((int)fileLength);//Close the file stream
fileStream.Close();
return data;
}
Now we will use Repository to save the image.
public int SaveImage(byte[] imagedata)
{
try
{
byte[] imageData = imagedata;
//get the connectin string.
string connectionstring = @"Data Source=FARAZ-PC\FARAZ_K1;Initial Catalog=RentAMovieDB;Integrated Security=True";
SqlConnection connection = new SqlConnection(connectionstring);
//Open the connection to the database.
connection.Open();
SqlCommand command = new SqlCommand();
//Insert image to the database. I am updating my record since I had a table with a column name Image. you could use Insert command instead of update.
command.CommandText = "UPDATE Movie SET Image = @image where Name = 'Your Highness'";
command.Connection = connection;
//Adding the parameter to the command object.
command.Parameters.AddWithValue("@image", imageData);
//if the result is 1 that means that image was saved.
int result = command.ExecuteNonQuery();
connection.Close();
return result;
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
Now we need to load the image from the database. Since I have my Image stored in Movie table, so I will use Movie name to search for the image I am looking for. Getting the image from the database is other way round, before we were converting the image into byte[] array and then saving it, now we are getting the image from that database as byte[] array and we need to convert it back into image and load it to picture box. First I am providing the name of the movie to look for the image. Here is what happens when user clicks on LoadImage button.
private void LoadButton_Click(object sender, EventArgs e)
{
try
{
ImageRepository repository = new ImageRepository();
//get the byte[] data from database using repository class.
byte[] image = repository.LoadImage(LoadTextBox.Text);
Image newImage;
//Read image data into a memory stream
using(MemoryStream memoryStream = new MemoryStream(image,0,image.Length))
{
memoryStream.Write(image,0,image.Length);
//Set image variable value using memory stream.
newImage = Image.FromStream(memoryStream, true);
}
//Set picturebox image.
pictureBox1.Image = newImage;
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
//get image using the repository class loadImage method.
public byte[] LoadImage(string movieName)
{
byte[] image = null;
string connectionstring = @"Data Source=FARAZ-PC\FARAZ_K1;Initial Catalog=RentAMovieDB;Integrated Security=True";
SqlConnection connection = new SqlConnection(connectionstring);
connection.Open();
SqlCommand command = new SqlCommand();
command.CommandText = "Select image from Movie where Name = '" + movieName+"'";
command.Connection = connection;
SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
DataSet dataset = new DataSet();
dataAdapter.Fill(dataset);
foreach (DataRow row in dataset.Tables[0].Rows)
{
image = (byte[])row["Image"];
}
return image;
}
Here is the form after loading the image from the database.
Tuesday, March 15, 2011
Data Validation
Problem:
Data validation is perhaps the most used feature of any application, what should I do to validate my data.
Impact:
Program might not work properly because data is not be valid.
Solution:
We could create a class that we could use to validate data. I create a class named Validator. This class contains static methods, each method takes a textbox as an argument verifies specific data type, for example string is empty or null, or if the value is integer or not or greater than or less than etc.
public static class Validator
{
private static ToolTip _tooltip = new ToolTip();
public static bool IsInteger(TextBox textBox)
{
int value;
if(int.TryParse(textBox.Text,out value))
{
textBox.BackColor = Color.White;
_tooltip.SetToolTip(textBox, "");
return true;
}
else
{
textBox.BackColor = Color.Maroon;
_tooltip.SetToolTip(textBox, "Please enter a number.");
textBox.Focus();
textBox.Clear();
}
return false;
}
public static bool IsValidPhoneNumber(TextBox textBox)
{
string strRegex = @"^[1-9]\d{2}-[1-9]\d{2}-\d{4}$";
Regex regex = new Regex(strRegex);
if(regex.IsMatch(textBox.Text))
{
textBox.BackColor = Color.White;
_tooltip.SetToolTip(textBox, "");
return true;
}
textBox.BackColor = Color.Maroon;
_tooltip.SetToolTip(textBox, "Please enter a valid email address.");
textBox.Focus();
textBox.Clear();
return false;
}
public static bool IsValidEmailAddress(TextBox textBox)
{
string strRegex = @"^([a-zA-Z0-9_\-\.]+)@((\[[0-9]{1,3}" +
@"\.[0-9]{1,3}\.[0-9]{1,3}\.)|(([a-zA-Z0-9\-]+\" +
@".)+))([a-zA-Z]{2,4}|[0-9]{1,3})(\]?)$";
Regex regex = new Regex(strRegex);
if(regex.IsMatch(textBox.Text))
{
textBox.BackColor = Color.White;
_tooltip.SetToolTip(textBox, "");
return true;
}
else
{
textBox.BackColor = Color.Maroon;
_tooltip.SetToolTip(textBox, "Please enter a valid email address.");
textBox.Focus();
textBox.Clear();
}
return false;
}
public static bool IsDouble(TextBox textBox)
{
double value;
if(double.TryParse(textBox.Text,out value))
{
textBox.BackColor = Color.White;
_tooltip.SetToolTip(textBox, "");
return true;
}
textBox.BackColor = Color.Maroon;
_tooltip.SetToolTip(textBox, "Please enter a number.");
textBox.Focus();
textBox.Clear();
return false;
}
internal static bool IsPresent(TextBox textBox)
{
if(!string.IsNullOrEmpty(textBox.Text))
{
textBox.BackColor = Color.White;
_tooltip.SetToolTip(textBox,"");
return true;
}
textBox.BackColor = Color.Maroon;
textBox.Focus();
_tooltip.SetToolTip(textBox,"This field is required.");
textBox.Clear();
return false;
}
}
now we could use this class as a dll library in any application and use it to validate data.
for example if we have a age text box that suppose to contain integer value, to check if text box contains we use the validator like this.
if(Validator.IsInteger(AgeTextBox))
{
//perform some action.
}
else
{
MessageBox.Show("Invalid integer value.");
}
Data validation is perhaps the most used feature of any application, what should I do to validate my data.
Impact:
Program might not work properly because data is not be valid.
Solution:
We could create a class that we could use to validate data. I create a class named Validator. This class contains static methods, each method takes a textbox as an argument verifies specific data type, for example string is empty or null, or if the value is integer or not or greater than or less than etc.
public static class Validator
{
private static ToolTip _tooltip = new ToolTip();
public static bool IsInteger(TextBox textBox)
{
int value;
if(int.TryParse(textBox.Text,out value))
{
textBox.BackColor = Color.White;
_tooltip.SetToolTip(textBox, "");
return true;
}
else
{
textBox.BackColor = Color.Maroon;
_tooltip.SetToolTip(textBox, "Please enter a number.");
textBox.Focus();
textBox.Clear();
}
return false;
}
public static bool IsValidPhoneNumber(TextBox textBox)
{
string strRegex = @"^[1-9]\d{2}-[1-9]\d{2}-\d{4}$";
Regex regex = new Regex(strRegex);
if(regex.IsMatch(textBox.Text))
{
textBox.BackColor = Color.White;
_tooltip.SetToolTip(textBox, "");
return true;
}
textBox.BackColor = Color.Maroon;
_tooltip.SetToolTip(textBox, "Please enter a valid email address.");
textBox.Focus();
textBox.Clear();
return false;
}
public static bool IsValidEmailAddress(TextBox textBox)
{
string strRegex = @"^([a-zA-Z0-9_\-\.]+)@((\[[0-9]{1,3}" +
@"\.[0-9]{1,3}\.[0-9]{1,3}\.)|(([a-zA-Z0-9\-]+\" +
@".)+))([a-zA-Z]{2,4}|[0-9]{1,3})(\]?)$";
Regex regex = new Regex(strRegex);
if(regex.IsMatch(textBox.Text))
{
textBox.BackColor = Color.White;
_tooltip.SetToolTip(textBox, "");
return true;
}
else
{
textBox.BackColor = Color.Maroon;
_tooltip.SetToolTip(textBox, "Please enter a valid email address.");
textBox.Focus();
textBox.Clear();
}
return false;
}
public static bool IsDouble(TextBox textBox)
{
double value;
if(double.TryParse(textBox.Text,out value))
{
textBox.BackColor = Color.White;
_tooltip.SetToolTip(textBox, "");
return true;
}
textBox.BackColor = Color.Maroon;
_tooltip.SetToolTip(textBox, "Please enter a number.");
textBox.Focus();
textBox.Clear();
return false;
}
internal static bool IsPresent(TextBox textBox)
{
if(!string.IsNullOrEmpty(textBox.Text))
{
textBox.BackColor = Color.White;
_tooltip.SetToolTip(textBox,"");
return true;
}
textBox.BackColor = Color.Maroon;
textBox.Focus();
_tooltip.SetToolTip(textBox,"This field is required.");
textBox.Clear();
return false;
}
}
now we could use this class as a dll library in any application and use it to validate data.
for example if we have a age text box that suppose to contain integer value, to check if text box contains we use the validator like this.
if(Validator.IsInteger(AgeTextBox))
{
//perform some action.
}
else
{
MessageBox.Show("Invalid integer value.");
}
Monday, March 14, 2011
Generics
Problem:
I wrote a program that store the student and course information in Dictionary. Both Course and Student uses the same methods to store or retrieve record, so there is a duplication of code here, how can I make this code more efficient?
Impact:
Code duplication
Solution:
In situation like this where we have we two object doing the same thing, generic comes to rescue. Generics allow us to define type-safe data structures, without committing to actual data types. Generics allow us to reuse the same code algorithms without writing type specific duplicate code.Generics allow us to define type-safe classes without compromising type safety, performance, or productivity.
Here is the application that I was working on.
In this application I have Main form that let you Add new student record, search student, Add course and search course. Both student forms and course forms uses the same ServiceFactory class to add the record and display record.
Main Form
I wrote a program that store the student and course information in Dictionary. Both Course and Student uses the same methods to store or retrieve record, so there is a duplication of code here, how can I make this code more efficient?
Impact:
Code duplication
Solution:
In situation like this where we have we two object doing the same thing, generic comes to rescue. Generics allow us to define type-safe data structures, without committing to actual data types. Generics allow us to reuse the same code algorithms without writing type specific duplicate code.Generics allow us to define type-safe classes without compromising type safety, performance, or productivity.
Here is the application that I was working on.
In this application I have Main form that let you Add new student record, search student, Add course and search course. Both student forms and course forms uses the same ServiceFactory class to add the record and display record.
Main Form
when user click on Add New Student button it display another form that is used to collect student information. This form contains one button "Save" and when user click on save button it class the StudentService class. Here is the detail of the application. First Main form.
namespace RegistrationSystem.App
{
public partial class frmMain : Form
{
public frmMain()
{
InitializeComponent();
}
private void btnAddRecord_Click(object sender, EventArgs e)
{
frmStudent frm = new frmStudent();
frm.ShowDialog();
}
private void btnViewRecord_Click(object sender, EventArgs e)
{
frmDisplayStudent frm = new frmDisplayStudent();
frm.ShowDialog();
}
private void AddCourseButton_Click(object sender, EventArgs e)
{
frmCourse frmCourse = new frmCourse();
frmCourse.ShowDialog();
}
private void GetCourseButton_Click(object sender, EventArgs e)
{
frmDisplayCourse frmDisplayCourse = new frmDisplayCourse();
frmDisplayCourse.ShowDialog();
}
}
}
now when user click on btnAddRecord, it displays student form.
user enter student record and click on Save button.
private void btnSave_Click(object sender, EventArgs e)
{
try
{
Student student = new Student();
student.FirstName = txtFirstName.Text;
student.LastName = txtLastName.Text;
student.StudentId = txtStudentId.Text;
StudentService.SaveStudent(student);
MessageBox.Show("Record Saved Successfully");
SetFormForNewRecord();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void SetFormForNewRecord()
{
txtFirstName.Text = "";
txtLastName.Text = "";
txtStudentId.Text = "";
}
and here is the StudentService class.
public static class StudentService
{
public static void SaveStudent(Student student)
{
ServiceFactory<Student>.AddNewRecord(student.StudentId,student);
}
public static Student GetStudentById(string studentId)
{
return ServiceFactory<Student>.GetRecord(studentId);
}
}
StudentService uses ServiceFactory to save and get record.
T is for type object.
public class ServiceFactory<T>
{
private static Dictionary<string, T> _data;
static ServiceFactory()
{
_data = new Dictionary<string, T>();
}
public static void AddNewRecord(string Id,T record)
{
if(_data.ContainsKey(Id))
{
throw new Exception("Record already exist");
}
_data.Add(Id,record);
}
public static T GetRecord(string Id)
{
if (! _data.ContainsKey(Id))
{
throw new Exception("Record not found");
}
return _data[Id];
}
}
Subscribe to:
Posts (Atom)