Home > Articles > Data > SQL Server

📄 Contents

  1. The Upload Script
  2. The Code-Behind (.cs) Script
  3. Your Turn
  • Print
  • + Share This
Like this article? We recommend

The Code-Behind (.cs) Script

Now that we have the interface our users will see, let's take a look at the code-behind script that does all the work (see Listing 2).

Listing 2 Code-behind script for the user script in Listing 1

1 using System;
2 using System.Configuration;
3 using System.Data;
4 using System.Data.SqlClient;
5 using System.Web;
6 using System.IO;
7 using System.Web.SessionState;
8 using System.Web.UI;
9 using System.Web.UI.WebControls;
10 using System.Web.UI.HtmlControls;

11 namespace DBImages
12 {
13 public class UploadImage : System.Web.UI.Page
14 {
15 protected System.Web.UI.WebControls.Button UploadBtn;
16 protected System.Web.UI.WebControls.RequiredFieldValidator RequiredFieldValidator1;
17 protected System.Web.UI.HtmlControls.HtmlInputText txtImgName;
18 protected System.Web.UI.HtmlControls.HtmlInputFile UploadFile;

19 public UploadImage() { }

20 private void Page_Load(object sender, System.EventArgs e){ }

21 public void UploadBtn_Click(object sender, System.EventArgs e)
22 {
23 if (Page.IsValid) //save the image
24 {
25 Stream imgStream = UploadFile.PostedFile.InputStream;
26 int imgLen = UploadFile.PostedFile.ContentLength;
27 string imgContentType = UploadFile.PostedFile.ContentType;
28 string imgName = txtImgName.Value;
29 byte[] imgBinaryData = new byte[imgLen];
30 int n = imgStream.Read(imgBinaryData,0,imgLen);

31 String idis = Request.QueryString["id"];
32 int busid = System.Convert.ToInt32(idis);

33 int RowsAffected = SaveToDB( imgName, imgBinaryData,imgContentType, busid);
34 if ( RowsAffected>0 )
35 {
36 Response.Write("<BR>The Image was saved");
37 }
38 else
39 {
40 Response.Write("<BR>An error occurred uploading the image");
41 }

42 }
43 }

44 private int SaveToDB(string imgName, byte[] imgbin, string imgcontenttype, int busid)
45 {
46 //use the web.config to store the connection string
47 SqlConnection connection = new SqlConnection(ConfigurationSettings.AppSettings["DSN"]);
48 SqlCommand command = new SqlCommand( "INSERT INTO Image (img_name,img_data,img_contenttype,business_id) VALUES ( @img_name, @img_data,@img_contenttype,@img_busid)", connection );

49 SqlParameter param0 = new SqlParameter( "@img_name", SqlDbType.VarChar,50 );
50 param0.Value = imgName;
51 command.Parameters.Add( param0 );

52 SqlParameter param1 = new SqlParameter( "@img_data", SqlDbType.Image );
53 param1.Value = imgbin;
54 command.Parameters.Add( param1 );

55 SqlParameter param2 = new SqlParameter( "@img_contenttype", SqlDbType.VarChar,50 );
56 param2.Value = imgcontenttype;
57 command.Parameters.Add( param2 );

58 SqlParameter param3 = new SqlParameter( "@img_busid", SqlDbType.Int,4 );
59 param3.Value = busid;
60 command.Parameters.Add( param3 );

61 connection.Open();
62 int numRowsAffected = command.ExecuteNonQuery();
63 connection.Close();

64 return numRowsAffected;
65 }
66 }
67 }

Lines 1–10 load our namespaces from the .NET library. Line 2 loads the System.Configuration namespace, needed because we record our database-connection string (line 47) in our Web.Config file. A class in this namespace calls on this file for these settings when needed after the application has been compiled. You could replace this line with a typical connection string if you want. Line 6 loads the System.IO namespace, important for handling streams of binary data or image data. It contains classes that allow you to treat these data streams as arrays and manipulate them as such if necessary. The other namespaces are fairly obvious; they give us more control over the web and HTML controls we're using.

Line 11 starts the DBImages namespace that our first script inherits (see Listing 1, line 1). Think of a namespace as a collection of classes and classes as a collection of functions when looking at this script (or any C# code-behind script, for that matter). The first class, UploadImage, begins on line 13 with the class constructor starting on line 19. Think of the constructor as the starting function that's executed when the class is called. For example, when we inherit the namespace in the first script in Listing 1 (line 1), we call on the first class to begin execution of the code-behind script called UploadImage. The constructor is the first code to be executed inside this class. Often, the constructor is named after the calls of which it is part.

Line 20 automatically loads the UploadBtn_Click function when the first script page in Listing 1 is accessed by the user. It isn't executed until the button is clicked. When the user clicks the button to upload the image using that button control, the function is called, and lines 23–41 are executed. Lines 25–30 read in the image binary data as a stream and store it into a byte array (line 29) called imgBinaryData. This array is the actual contents of the image in binary form. If you open the image in a text editor, the binary data you see is the same data that this array stores into the database. If you have businesses using this script, lines 31–32 pull the business ID from the URL string and store it in a variable called busid. You don't need this value, but it's handy if you want to keep track of who is loading what.

Line 33 calls the function SaveToDB (lines 44–65) that saves all of the data we collect into the database. We pass in all our values through the function's parameters and insert them into the database using the SQL INSERT statement with SQL parameters (lines 48–60). We could have just used the INSERT statement with the function parameters as the variables contained in the Value part of the statement, but using SQL parameters (variables preceded by the @ symbol) provides more control over how those variables are passed to the database.

  • + Share This
  • 🔖 Save To Your Account