This C# ADO.NET application allows you to manage your pictures/images using a SQL Server database.
The application uses a ListBox control, PictureBox control, Menu control, two Button controls, and a SQL Server database back-end to upload images from your hard drive and download images for display. You can display an image from the database by double-clicking the filename in the ListBox. This solution will also allow you to delete an image from the database as well as save an image to an external location on your hard drive.
Before you can use this application, you must first run the MyPictures SQL script to create the MyPictures database, Images table, and the Stored Procedures needed for the application to interface with the database . The SQL script must be executed against a SQL Server or SQLExpress database. The SQL script is included in the Source Code Zip file, which can be downloaded by selecting the link.
Finally, the solution includes a DBInterface.cs class. This class manages the connections and operations against the SQL Server database using ADO.NET.
Form1.cs
using System;
using System.IO;
using System.Drawing;
using System.Drawing.Drawing2D;
using System.Drawing.Imaging;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;
using ImagesInSQLServer;
namespace UploadImagesToDB
{
///
/// Summary description for Form2.
///
public class Form2 : System.Windows.Forms.Form
{
// Store the information retrieved from the database into a dataset
//
private DataSet ds = new DataSet("Pictures");
private System.Windows.Forms.ListBox UploadedFiles;
private System.Windows.Forms.Button UploadFile;
private System.Windows.Forms.PictureBox Picture;
private System.Data.SqlClient.SqlConnection sqlConnection1;
private System.Windows.Forms.Button btnDelete;
private System.Windows.Forms.MainMenu mainMenu1;
private System.Windows.Forms.MenuItem menuItem1;
private System.Windows.Forms.MenuItem menuItem2;
private System.Windows.Forms.MenuItem menuItem3;
private System.Windows.Forms.MenuItem menuItem4;
private System.Windows.Forms.MenuItem menuItem5;
string strImgName;
///
/// Required designer variable.
///
private System.ComponentModel.Container components = null;
public Form2()
{
//
// Required for Windows Form Designer support
//
InitializeComponent();
//
// TODO: Add any constructor code after InitializeComponent call
//
}
///
/// Clean up any resources being used.
///
protected override void Dispose( bool disposing )
{
if( disposing )
{
if(components != null)
{
components.Dispose();
}
}
base.Dispose( disposing );
}
#region Windows Form Designer generated code
///
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
///
private void InitializeComponent()
{
this.UploadedFiles = new System.Windows.Forms.ListBox();
this.UploadFile = new System.Windows.Forms.Button();
this.Picture = new System.Windows.Forms.PictureBox();
this.sqlConnection1 = new System.Data.SqlClient.SqlConnection();
this.btnDelete = new System.Windows.Forms.Button();
this.mainMenu1 = new System.Windows.Forms.MainMenu();
this.menuItem1 = new System.Windows.Forms.MenuItem();
this.menuItem2 = new System.Windows.Forms.MenuItem();
this.menuItem3 = new System.Windows.Forms.MenuItem();
this.menuItem4 = new System.Windows.Forms.MenuItem();
this.menuItem5 = new System.Windows.Forms.MenuItem();
this.SuspendLayout();
//
// UploadedFiles
//
this.UploadedFiles.Anchor = System.Windows.Forms.AnchorStyles.None;
this.UploadedFiles.Location = new System.Drawing.Point(8, 8);
this.UploadedFiles.Name = "UploadedFiles";
this.UploadedFiles.Size = new System.Drawing.Size(184, 446);
this.UploadedFiles.Sorted = true;
this.UploadedFiles.TabIndex = 0;
this.UploadedFiles.DoubleClick += new System.EventHandler(this.UploadedFiles_DoubleClick_1);
//
// UploadFile
//
this.UploadFile.Anchor = ((System.Windows.Forms.AnchorStyles)((System.Windows.Forms.AnchorStyles.Bottom | System.Windows.Forms.AnchorStyles.Right)));
this.UploadFile.Font = new System.Drawing.Font("Tahoma", 8.25F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((System.Byte)(0)));
this.UploadFile.Location = new System.Drawing.Point(8, 464);
this.UploadFile.Name = "UploadFile";
this.UploadFile.Size = new System.Drawing.Size(88, 23);
this.UploadFile.TabIndex = 4;
this.UploadFile.Text = "Upload Image";
this.UploadFile.Click += new System.EventHandler(this.UploadFile_Click);
//
// Picture
//
this.Picture.Anchor = System.Windows.Forms.AnchorStyles.None;
this.Picture.BackColor = System.Drawing.SystemColors.Window;
this.Picture.BorderStyle = System.Windows.Forms.BorderStyle.FixedSingle;
this.Picture.Location = new System.Drawing.Point(200, 8);
this.Picture.Name = "Picture";
this.Picture.Size = new System.Drawing.Size(400, 480);
this.Picture.SizeMode = System.Windows.Forms.PictureBoxSizeMode.CenterImage;
this.Picture.TabIndex = 6;
this.Picture.TabStop = false;
//
// btnDelete
//
this.btnDelete.Location = new System.Drawing.Point(104, 464);
this.btnDelete.Name = "btnDelete";
this.btnDelete.Size = new System.Drawing.Size(88, 23);
this.btnDelete.TabIndex = 7;
this.btnDelete.Text = "Delete Image";
this.btnDelete.Click += new System.EventHandler(this.btnDelete_Click);
//
// mainMenu1
//
this.mainMenu1.MenuItems.AddRange(new System.Windows.Forms.MenuItem[] {
this.menuItem1});
//
// menuItem1
//
this.menuItem1.Index = 0;
this.menuItem1.MenuItems.AddRange(new System.Windows.Forms.MenuItem[] {
this.menuItem2,
this.menuItem3,
this.menuItem4,
this.menuItem5});
this.menuItem1.Text = "File";
//
// menuItem2
//
this.menuItem2.Index = 0;
this.menuItem2.Text = "Upload Image";
this.menuItem2.Click += new System.EventHandler(this.menuItem2_Click);
//
// menuItem3
//
this.menuItem3.Index = 1;
this.menuItem3.Text = "Save As";
this.menuItem3.Click += new System.EventHandler(this.menuItem3_Click);
//
// menuItem4
//
this.menuItem4.Index = 2;
this.menuItem4.Text = "Delete Image";
this.menuItem4.Click += new System.EventHandler(this.menuItem4_Click);
//
// menuItem5
//
this.menuItem5.Index = 3;
this.menuItem5.Text = "Exit";
this.menuItem5.Click += new System.EventHandler(this.menuItem5_Click);
//
// Form2
//
this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
this.ClientSize = new System.Drawing.Size(608, 494);
this.Controls.Add(this.btnDelete);
this.Controls.Add(this.Picture);
this.Controls.Add(this.UploadFile);
this.Controls.Add(this.UploadedFiles);
this.Menu = this.mainMenu1;
this.Name = "Form2";
this.Text = "Upload/Download Images to MS SQL Server";
this.Load += new System.EventHandler(this.Form2_Load_1);
this.ResumeLayout(false);
}
#endregion
///
/// The main entry point for the application.
///
[STAThread]
static void Main()
{
Application.Run(new Form2());
}
//
// If available, load a list of image files from the SQL Server database
//
private void Form2_Load_1(object sender, System.EventArgs e)
{
LoadFiles();
}
private void Exit_Click(object sender, System.EventArgs e)
{
this.Close();
}
//
// Upload file to SQL Server database
//
private void UploadFile_Click(object sender, System.EventArgs e)
{
try
{
//
// Get file to be upload
//
OpenFileDialog dlgFile = new OpenFileDialog();
dlgFile.Filter="JPEG Images (*.jpg,*.jpeg)|*.jpg;*.jpeg|Gif Images (*.gif)|*.gif|Bitmaps (*.bmp)|*.bmp";
dlgFile.FilterIndex=1;
dlgFile.ShowDialog();
if (dlgFile.FileName=="" || !File.Exists(dlgFile.FileName))
{
//
// If the requested file is not ok...
//
return;
}
DBInterface up = new DBInterface();
long id =up.UploadImage(dlgFile.FileName);
string msg=null;
if (id >0)
{
msg = "Upload successful";
LoadFiles();
}
else
{
msg = "An error has occured";
}
MessageBox.Show(msg);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
//
// Retrieve a list of uploaded files from the SQL server database
//
private void LoadFiles()
{
try
{
DBInterface up = new DBInterface();
up.GetUploadedFiles(ref ds,"Pictures");
UploadedFiles.DataSource = ds.Tables["Pictures"];
UploadedFiles.DisplayMember = "Name";
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
//
// Download the selected file and display the image in the PictureBox.
//
private void UploadedFiles_DoubleClick_1(object sender, System.EventArgs e)
{
try
{
DataRowView drv = (DataRowView) UploadedFiles.SelectedItem;
string selectedText = drv.Row["Name"].ToString();
long id=-1;
//
// the id is stored in text. The structure is: id - FileName.
//
id = long.Parse(selectedText.Substring(0,selectedText.IndexOf(" - ",0)).Trim());
string filename=null;
DBInterface up = new DBInterface();
byte[] result = up.DownloadImage(id,ref filename);
up = null;
//
// You cannot assign a byte array directly to an image.
// Use MemoryStream, an object that creates a file in memory
// and then pass this to create the image object.
//
MemoryStream ms= new MemoryStream(result,0,result.Length);
Image im = Image.FromStream(ms);
// If image greater than the dimensions of the PictureBox,
// resize to fit.
if (im.Height > 480 || im.Width > 400)
Picture.Image = FixedSize(im, 400,480);
else
Picture.Image = im;
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
}
//
// Scale to a fixed size...in this case to the size of the PictureBox
//
// Since images will have varying orientations, it will be necessary fit either
// to the width or height, then pad the opposite dimension with filler.
//
static Image FixedSize(Image imgPhoto, int Width, int Height)
{
int sourceWidth = imgPhoto.Width;
int sourceHeight = imgPhoto.Height;
int sourceX = 0;
int sourceY = 0;
int destX = 0;
int destY = 0;
float nPercent = 0;
float nPercentW = 0;
float nPercentH = 0;
nPercentW = ((float)Width/(float)sourceWidth);
nPercentH = ((float)Height/(float)sourceHeight);
if(nPercentH < nPercentW)
{
nPercent = nPercentH;
destX = System.Convert.ToInt16((Width -
(sourceWidth * nPercent))/2);
}
else
{
nPercent = nPercentW;
destY = System.Convert.ToInt16((Height -
(sourceHeight * nPercent))/2);
}
int destWidth = (int)(sourceWidth * nPercent);
int destHeight = (int)(sourceHeight * nPercent);
Bitmap bmPhoto = new Bitmap(Width, Height,
PixelFormat.Format24bppRgb);
bmPhoto.SetResolution(imgPhoto.HorizontalResolution,
imgPhoto.VerticalResolution);
Graphics grPhoto = Graphics.FromImage(bmPhoto);
grPhoto.Clear(Color.White);
grPhoto.InterpolationMode =
InterpolationMode.HighQualityBicubic;
grPhoto.DrawImage(imgPhoto,
new Rectangle(destX,destY,destWidth,destHeight),
new Rectangle(sourceX,sourceY,sourceWidth,sourceHeight),
GraphicsUnit.Pixel);
grPhoto.Dispose();
return bmPhoto;
}
//
// Delete image from database
//
private void btnDelete_Click(object sender, System.EventArgs e)
{
DialogResult dr = MessageBox.Show("This feature deletes the highlighted filename in the listbox." +
"nDo you want to continue?",
"Delete File",
MessageBoxButtons.YesNo,
MessageBoxIcon.Question,
MessageBoxDefaultButton.Button1);
if(Convert.ToString(dr) == "No") return;
DataRowView drv = (DataRowView) UploadedFiles.SelectedItem;
string selectedText = drv.Row["Name"].ToString();
long id=-1;
//
// the id is stored in text. The structure is: id - FileName.
//
id = long.Parse(selectedText.Substring(0,selectedText.IndexOf(" - ",0)).Trim());
try
{
DBInterface up = new DBInterface();
if (up.deleteImageFromDatabase(id))
{
LoadFiles();
MessageBox.Show("Image was successfully deleted from database");
}
else
{
MessageBox.Show("Image was NOT deleted from database");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
//
// Perform the "Save As" function on the image in the display window
//
private void menuItem3_Click(object sender, System.EventArgs e)
{
if (Picture.Image ==null)
{
MessageBox.Show("Please load an image into the viewer before selecting "Save As"!");
return;
}
DialogResult dr = MessageBox.Show("This feature saves the image currently loaded in the viewer." +
"nDo you want to continue?",
"Save As",
MessageBoxButtons.YesNo,
MessageBoxIcon.Question,
MessageBoxDefaultButton.Button1);
if(Convert.ToString(dr) == "No") return;
try
{ // save the image in the required format
SaveFileDialog dlgSave = new SaveFileDialog();
dlgSave.Filter="JPEG Images (*.jpg,*.jpeg)|*.jpg;*.jpeg|Gif Images (*.gif)|*.gif|Bitmaps (*.bmp)|*.bmp";
if (dlgSave.ShowDialog()==DialogResult.OK)
{
strImgName=dlgSave.FileName;
if (strImgName.EndsWith("jpg"))
Picture.Image.Save(strImgName,ImageFormat.Jpeg);
if (strImgName.EndsWith("gif"))
Picture.Image.Save(strImgName,ImageFormat.Gif);
if (strImgName.EndsWith("bmp"))
Picture.Image.Save(strImgName,ImageFormat.Bmp);
}
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void menuItem5_Click(object sender, System.EventArgs e)
{
Exit_Click(this, e);
}
private void menuItem2_Click(object sender, System.EventArgs e)
{
UploadFile_Click(this, e);
}
private void menuItem4_Click(object sender, System.EventArgs e)
{
btnDelete_Click(this, e);
}
}
}
DBInterface.cs
using System;
using System.IO;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using System.Windows.Forms;
/*
* Created By: cSharp4Newbies.com
* License: Free
* Description: Implements methods to insert, retrieve, and delete images from SQL Server
* Link to us: You can support us by linking to our main web page - www.csharp4newbies.com
*
*/
namespace ImagesInSQLServer
{
//
// This class manages the connections and operations against a SQL Server database
//
public class DBInterface
{
//
// Retrieves a list of files from the SQL Server database
//
public void GetUploadedFiles(ref DataSet ds, string table)
{
//
// The variables required for connecting to the server.
//
SqlConnection conn =null;
SqlCommand cmd = null;
SqlDataAdapter da = null;
// ----------------------------------------------
try
{
//
// If the table already exists, clear its content; else adds a new table.
//
if (ds.Tables.Contains(table))
ds.Tables[table].Clear();
else
ds.Tables.Add(table);
// ----------------------------------------------
//
// Creates a connection to the database and initilizes the command
//
conn = new SqlConnection(ConnectionString());
cmd = new SqlCommand("GetAllImages",conn);
cmd.CommandType = CommandType.StoredProcedure;
// ----------------------------------------------
//
// Initializes the DataAdapter used for retrieving the data
//
da = new SqlDataAdapter(cmd);
// ----------------------------------------------
//
// Opens the connection and populates the dataset
//
conn.Open();
da.Fill(ds,table);
conn.Close();
// ----------------------------------------------
}
catch(Exception)
{
throw;
}
}
//
// Upload image to the database server.
//
public long UploadImage(string FileName)
{
if (!File.Exists(FileName))
{
return -1;
}
FileStream fs=null;
try
{
#region Reading file
fs = new FileStream(FileName,FileMode.Open);
//
// Finding out the size of the file to be uploaded
//
FileInfo fi = new FileInfo(FileName);
long temp = fi.Length;
int lung = Convert.ToInt32(temp);
// ------------------------------------------
//
// Reading the content of the file into an array of bytes.
//
byte[] picture=new byte[lung];
fs.Read(picture,0,lung);
fs.Close();
// ------------------------------------------
#endregion
long result = uploadImageToDatabase(picture,fi.Name);
return result;
}
catch(Exception)
{
throw;
}
}
//
// Wrapper for downloading a file from the database.
//
public byte[] DownloadImage(long kFileName, ref string fileName)
{
byte[] result = downloadImageFromDatabase(kFileName, ref fileName);
return result;
}
//
// Returns the connection string for connecting to the database
//
public static string ConnectionString()
{
//
// Update to include your SQL Server database information
//
return "Connect Timeout=600;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=MyPictures;Packet Size=4096;Data Source=" + System.Environment.MachineName.Trim();
}
//
// Upload a file to the SQL Server database
//
/// A byte array that contains the information to be uploaded. /// The file name asociated with that byte array. /// The unique ID of the file on the server OR -1 if an error occurs.
private long uploadImageToDatabase(byte[] picture, string fileName)
{
//
// Defining the variables required for accessing the database server.
//
SqlConnection conn = null;
SqlCommand cmd =null;
SqlParameter kFileName =null;
SqlParameter FileName =null;
SqlParameter pic =null;
// By default, we assume we have an error.
long result=-1;
try
{
//
// Connecting to database.
//
conn = new SqlConnection(ConnectionString());
cmd = new SqlCommand("InsertImage",conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
// ----------------------------------------------
//
// Initializing parameters and assigning the values to be sent to the server
//
kFileName = new SqlParameter("@kFileName",System.Data.SqlDbType.BigInt,8);
kFileName.Direction = ParameterDirection.Output;
// This parameter does not have a size because we do not know what the size is going to be.
pic = new SqlParameter("@picture",SqlDbType.Image);
pic.Value = picture;
FileName = new SqlParameter("@FileName",SqlDbType.VarChar,250);
FileName.Value = fileName;
// ----------------------------------------------
//
// Adding the parameters to the database. Remember that the order in which the parameters
// are added is VERY important!
//
cmd.Parameters.Add(pic);
cmd.Parameters.Add(FileName);
cmd.Parameters.Add(kFileName);
// ----------------------------------------------
//
// Opening the connection and executing the command.
//
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
// ----------------------------------------------
//
// The result is the unique identifier created on the database.
//
result = (long)kFileName.Value;
// ----------------------------------------------
//
// Disposing of the objects so we don't occupy memory.
//
conn.Dispose();
cmd.Dispose();
// ----------------------------------------------
}
catch(Exception)
{
result = -1;
throw;
}
return result;
}
//
// Download a file from a database according to the unique id in that database.
//
// A byte array containing the information is returned
private byte[] downloadImageFromDatabase(long kFile, ref string FileName)
{
SqlConnection conn =null;
SqlCommand cmd = null;
SqlParameter kFileName = null;
SqlParameter fileName = null;
SqlDataReader dr=null;
byte[] result=null;
try
{
//
// Connecting to database.
//
conn = new SqlConnection(ConnectionString());
cmd = new SqlCommand("GetImage",conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
// ----------------------------------------------
//
// Initializing parameters and assigning the values to be sent to the server
//
kFileName= new SqlParameter("@kFileName",System.Data.SqlDbType.BigInt,8);
kFileName.Value = kFile;
fileName = new SqlParameter("@FileName",SqlDbType.VarChar,250);
fileName.Direction = ParameterDirection.Output;
// ----------------------------------------------
//
// Adding the parameters to the database. Remember that the order in which the parameters
// are added is VERY important!
//
cmd.Parameters.Add(kFileName);
cmd.Parameters.Add(fileName);
// ----------------------------------------------
//
// Opening the connection and executing the command.
// The idea behind using a dataReader is that, on the SQL Server, we cannot assign to a
// variable the value of an image field. So, we use a querry to select the record we want
// and we use a datareader to read that query.
// Because we are returnig information based on a primary key, we are always returning
// only one row of data.
//
conn.Open();
dr = cmd.ExecuteReader();
dr.Read();
//
// We are casting the value returned by the datareader to the byte[] data type.
//
result = (byte[])dr.GetValue(0);
//
// We are also returning the filename associated with the byte array.
//
FileName = (string)dr.GetValue(1);
//
// Closing the datareader and the connection
//
dr.Close();
conn.Close();
// ------------------------------------------
//
// Disposing of the objects so we don't occupy memory.
//
conn.Dispose();
cmd.Dispose();
// ------------------------------------------
}
catch(Exception)
{
result = null;
throw;
}
return result;
}
//
// Deletes an image from the datbase based on the unique id.
//
public bool deleteImageFromDatabase(long kFile)
{
SqlConnection conn =null;
SqlCommand cmd = null;
bool tmp;
try
{
//
// Connecting to database.
//
conn = new SqlConnection(ConnectionString());
cmd = new SqlCommand("DeleteImage",conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
// ----------------------------------------------
//
// Initializing parameters and assigning the values to be sent to the server
//
SqlParameter kFileName = new SqlParameter();
kFileName.ParameterName = "@kFileName";
kFileName.SqlDbType = SqlDbType.BigInt;
kFileName.Direction = ParameterDirection.Input;
kFileName.Value = kFile;
SqlParameter ReturnValue = new SqlParameter();
ReturnValue.ParameterName = "@return_value";
ReturnValue.SqlDbType = SqlDbType.Int;
ReturnValue.Direction = ParameterDirection.ReturnValue;
SqlParameter NbrRows = new SqlParameter();
NbrRows.ParameterName = "@NbrRows";
NbrRows.SqlDbType = SqlDbType.Int;
NbrRows.Direction = ParameterDirection.Output;
// ----------------------------------------------
//
// Adding the parameters to the database. Remember that the order in which the parameters
// are added is VERY important!
//
cmd.Parameters.Add(ReturnValue);
cmd.Parameters.Add(kFileName);
cmd.Parameters.Add(NbrRows);
conn.Open();
cmd.ExecuteNonQuery();
int affectedRows = (int) cmd.Parameters["@NbrRows"].Value;
// Check to determine if the file was really deleted
if (affectedRows == 0)
tmp = false;
else
tmp = true;
return tmp;
}
catch(Exception)
{
throw;
}
finally
{
if (conn != null)
{
conn.Close();
conn.Dispose();
}
if (cmd !=null)
cmd.Dispose();
}
}
}
}
Взято с http://csharp4newbies.com/
Исходник: UploadImagesToDB.zip
0.00 (0%) 0 votes
