Ok after lots of searches on google, I finally found few good solutions to add images to the MS SQL server and also retrieve it back in the Application. Basically there are three ways :
- Storing Base64 string [nvarchar(max)] value in the table.
- Passing the Base64 string to a webservice of ASP.NET or PHP to convert into ByteArray and store in the database.
- Storing the path of the image in the database (using a webservice).
Method 1 – Storing Base64 string [nvarchar(max)] value in the table.
You can use Eclipse ADT or Android Studio to make this application. I’m using Eclipse ADT. To know how to make Android app work with MS SQL server you can refer my previous post here
Make a new Android Application and add the jtds-1.2.7.jar library to it.
Open your activity_main.xml file and Edit it as following :
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android" xmlns:tools="http://schemas.android.com/tools" android:layout_width="match_parent" android:layout_height="match_parent" android:background="#e74c3c" android:orientation="vertical" android:weightSum="12" > <TextView android:id="@+id/lblclick" android:layout_width="wrap_content" android:layout_height="0dp" android:layout_gravity="center" android:layout_marginTop="7dp" android:layout_weight="1" android:padding="2dp" android:text="ADROID IMAGEVIEW UPLOAD" android:textColor="#fff" android:textSize="19dp" android:typeface="sans" /> <EditText android:id="@+id/edtname" android:layout_width="256dp" android:layout_height="0dp" android:layout_gravity="center" android:layout_weight="1" android:background="#fff" android:gravity="center" android:hint="ENTER IMAGE NAME" android:padding="5dp" android:textColor="#e74c3c" android:typeface="sans" /> <ProgressBar android:id="@+id/progressBar1" android:layout_width="wrap_content" android:layout_height="20dp" android:layout_gravity="center" android:layout_marginLeft="5dp" android:layout_weight="1" android:gravity="center" /> <LinearLayout android:layout_width="match_parent" android:layout_height="0dp" android:layout_weight="1" android:orientation="horizontal" android:weightSum="2" > <Button android:id="@+id/btnchooseimage" android:layout_width="0dp" android:layout_height="wrap_content" android:layout_weight="1" android:layout_margin="2dp" android:background="#2c3e50" android:gravity="center" android:textStyle="bold" android:text="CHOOSE IMAGE" android:textColor="#e74c3c" android:typeface="sans" /> <Button android:id="@+id/btnupload" android:layout_width="0dp" android:layout_height="wrap_content" android:layout_weight="1" android:textStyle="bold" android:layout_margin="2dp" android:background="#34495e" android:gravity="center" android:text="UPLOAD" android:textColor="#e74c3c" android:typeface="sans" /> </LinearLayout> <ImageView android:id="@+id/imageview" android:layout_width="match_parent" android:layout_height="0dp" android:layout_margin="5dp" android:layout_weight="6.5" /> <TextView android:id="@+id/txtmsg" android:layout_width="wrap_content" android:layout_height="0dp" android:layout_gravity="center" android:layout_margin="5dp" android:layout_weight="1.5" android:padding="10dp" android:text="v xcvcxvcvcvxcvxcvxcvxcvxcvxc v xcvcxvcvcvxcvxcvxcvxcvxcvxc v xcvcxvcvcvxcvxcvxcvxcvxcvxc" android:textColor="#fff" android:textSize="13sp" /> </LinearLayout>
Open the MainActivity.java file and edit it as following (Please change the package name according to your app package name) :
package com.android.asp;
import java.io.ByteArrayOutputStream;
import java.io.FileNotFoundException;
import java.io.IOError;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import android.annotation.SuppressLint;
import android.content.Intent;
import android.graphics.Bitmap;
import android.graphics.BitmapFactory;
import android.net.Uri;
import android.os.Bundle;
import android.os.Environment;
import android.os.StrictMode;
import android.support.v7.app.ActionBarActivity;
import android.util.AndroidRuntimeException;
import android.util.Base64;
import android.util.Log;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.ImageView;
import android.widget.ProgressBar;
import android.widget.TextView;
import android.widget.Toast;
public class MainActivity extends ActionBarActivity {
public static final int requestcode = 1;
ImageView img;
Button btnupload, btnchooseimage;
EditText edtname;
byte[] byteArray;
String encodedImage;
TextView txtmsg;
ProgressBar pg;
ResultSet rs;
Connection con;
String un;
String password;
String db;
String ip;
@SuppressLint("NewApi")
private Connection ConnectionHelper(String user, String password,
String database, String server) {
StrictMode.ThreadPolicy policy = new StrictMode.ThreadPolicy.Builder()
.permitAll().build();
StrictMode.setThreadPolicy(policy);
Connection connection = null;
String ConnectionURL = null;
try {
Class.forName("net.sourceforge.jtds.jdbc.Driver");
ConnectionURL = "jdbc:jtds:sqlserver://" + server + ";"
+ "databaseName=" + database + ";user=" + user
+ ";password=" + password + ";";
connection = DriverManager.getConnection(ConnectionURL);
} catch (SQLException se) {
Log.e("ERRO", se.getMessage());
} catch (ClassNotFoundException e) {
Log.e("ERRO", e.getMessage());
} catch (Exception e) {
Log.e("ERRO", e.getMessage());
}
return connection;
}
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
img = (ImageView) findViewById(R.id.imageview);
btnupload = (Button) findViewById(R.id.btnupload);
btnchooseimage = (Button) findViewById(R.id.btnchooseimage);
edtname = (EditText) findViewById(R.id.edtname);
txtmsg = (TextView) findViewById(R.id.txtmsg);
pg = (ProgressBar) findViewById(R.id.progressBar1);
pg.setVisibility(View.GONE);
un = "sa";
password = "123";
db = "MyDB";
ip = "192.168.0.100";
con = ConnectionHelper(un, password, db, ip);
btnchooseimage.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
ChooseImage();
}
});
btnupload.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
UploadtoDB();
}
});
}
public void UploadtoDB() {
String msg = "unknown";
try {
con = ConnectionHelper(un, password, db, ip);
String commands = "Insert into ImgTbl2 (ImgName,Img) values ('"
+ edtname.getText().toString() + "','" + encodedImage
+ "')";
// encodedImage which is the Base64 String
PreparedStatement preStmt = con.prepareStatement(commands);
preStmt.executeUpdate();
msg = "Inserted Successfully";
} catch (SQLException ex) {
msg = ex.getMessage().toString();
Log.d("hitesh", msg);
} catch (IOError ex) {
// TODO: handle exception
msg = ex.getMessage().toString();
Log.d("hitesh", msg);
} catch (AndroidRuntimeException ex) {
msg = ex.getMessage().toString();
Log.d("hitesh", msg);
} catch (NullPointerException ex) {
msg = ex.getMessage().toString();
Log.d("hitesh", msg);
}
catch (Exception ex) {
msg = ex.getMessage().toString();
Log.d("hitesh", msg);
}
txtmsg.setText(msg);
}
public void ChooseImage() {
if (Environment.getExternalStorageState().equals(
Environment.MEDIA_MOUNTED)
&& !Environment.getExternalStorageState().equals(
Environment.MEDIA_CHECKING)) {
Intent intent = new Intent(Intent.ACTION_PICK);
intent.setType("image/*");
startActivityForResult(intent, 1);
} else {
Toast.makeText(MainActivity.this,
"No activity found to perform this task",
Toast.LENGTH_SHORT).show();
}
}
@Override
protected void onActivityResult(int requestCode, int resultCode, Intent data) {
super.onActivityResult(requestCode, resultCode, data);
if (resultCode == RESULT_OK) {
Bitmap originBitmap = null;
Uri selectedImage = data.getData();
Toast.makeText(MainActivity.this, selectedImage.toString(),
Toast.LENGTH_LONG).show();
txtmsg.setText(selectedImage.toString());
InputStream imageStream;
try {
imageStream = getContentResolver().openInputStream(
selectedImage);
originBitmap = BitmapFactory.decodeStream(imageStream);
} catch (FileNotFoundException e) {
txtmsg.setText(e.getMessage().toString());
}
if (originBitmap != null) {
this.img.setImageBitmap(originBitmap);
ByteArrayOutputStream stream = new ByteArrayOutputStream();
originBitmap.compress(Bitmap.CompressFormat.PNG, 100, stream);
byteArray = stream.toByteArray();
encodedImage = Base64.encodeToString(byteArray, Base64.DEFAULT);
Toast.makeText(MainActivity.this, "Conversion Done",
Toast.LENGTH_SHORT).show();
}
} else {
txtmsg.setText("There's an error if this code doesn't work, thats all I know");
}
}
}
In the onActivityResult method the Image choosen is converted into Base64 value string and the same is being stored in the Database.
if (resultCode == RESULT_OK) {
Bitmap originBitmap = null;
Uri selectedImage = data.getData();
Toast.makeText(MainActivity.this, selectedImage.toString(),
Toast.LENGTH_LONG).show();
txtmsg.setText(selectedImage.toString());
InputStream imageStream;
try {
imageStream = getContentResolver().openInputStream(
selectedImage);
originBitmap = BitmapFactory.decodeStream(imageStream);
} catch (FileNotFoundException e) {
txtmsg.setText(e.getMessage().toString());
}
if (originBitmap != null) {
this.img.setImageBitmap(originBitmap);
ByteArrayOutputStream stream = new ByteArrayOutputStream();
originBitmap.compress(Bitmap.CompressFormat.PNG, 100, stream);
byteArray = stream.toByteArray();
encodedImage = Base64.encodeToString(byteArray, Base64.DEFAULT);
Toast.makeText(MainActivity.this, "Conversion Done",
Toast.LENGTH_SHORT).show();
}
} else {
txtmsg.setText("There's an error if this code doesn't work, thats all I know");
}
In the method UploadtoDB() which is called on the button click event of btnupload button,
“encodedImage” which is the Base64 String is passed in the Insert statement and later it can be retrieved back.
public void UploadtoDB() {
String msg = "unknown";
try {
con = ConnectionHelper(un, password, db, ip);
String commands = "Insert into ImgTbl2 (ImgName,Img) values ('"
+ edtname.getText().toString() + "','" + encodedImage
+ "')";
// encodedImage which is the Base64 String
PreparedStatement preStmt = con.prepareStatement(commands);
preStmt.executeUpdate();
msg = "Inserted Successfully";
} catch (SQLException ex) {
msg = ex.getMessage().toString();
Log.d("hitesh", msg);
} catch (IOError ex) {
// TODO: handle exception
msg = ex.getMessage().toString();
Log.d("hitesh", msg);
} catch (AndroidRuntimeException ex) {
msg = ex.getMessage().toString();
Log.d("hitesh", msg);
} catch (NullPointerException ex) {
msg = ex.getMessage().toString();
Log.d("hitesh", msg);
}
catch (Exception ex) {
msg = ex.getMessage().toString();
Log.d("hitesh", msg);
}
txtmsg.setText(msg);
}
Script for Creating the database :
USE [MyDB] GO /****** Object: Table [dbo].[ImgTbl2] Script Date: 08/12/2015 23:20:16 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[ImgTbl2]( [ID] [int] IDENTITY(1,1) NOT NULL, [ImgName] [nvarchar](50) NULL, [Img] [nvarchar](max) NULL ) ON [PRIMARY]
To retrieve the stored image you will have to decode the BASE64 string to bitmap or bytearray :
byte[] decodeString = Base64.decode("BASE64 STRING OVER HERE", Base64.DEFAULT);
Bitmap decodebitmap = BitmapFactory.decodeByteArray(
decodeString, 0, decodeString.length);
img.setImageBitmap(decodebitmap);
You can pass the ID to retrieve the String value from Database and Decode it back.
See Android Retrieving Images stored in Base64 value from MS SQL Server
For C#, use following code :
try
{
con = new SqlConnection(h);
con.Open();
cmd = new SqlCommand("select * from ImgTbl2 where ID=@id", con);
cmd.Parameters.AddWithValue("@id", txtid.Text.ToString());
SqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
byte[] imageBytes = Convert.FromBase64String(reader["Img"].ToString());
String name = reader["ImgName"].ToString();
lblimagename.Text = name;
MemoryStream ms1 = new MemoryStream(imageBytes);
Image img = Image.FromStream(ms1);
pictureBox1.BackgroundImage = img;
}
con.Close();
}
catch (Exception ex)
{
if (con.State == ConnectionState.Open)
con.Close();
}
Full C# code
Please note that I’m using :
- TextBox with ID – txtid
- Button with ID – btnfetch
- PictureBox with ID – pictureBox1
- Label with ID – lblimagename
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Data.Sql;
using System.Configuration;
using System.IO;
using System.Drawing.Imaging;
namespace Image_to_Array_Android
{
public partial class Base64 : Form
{
SqlConnection con;
String h = "Data Source=h-pc;Initial Catalog=MyDB;Persist Security Info=True;User ID=sa;Password=123";
SqlCommand cmd;
public Base64()
{
InitializeComponent();
}
private void btnfetch_Click(object sender, EventArgs e)
{
try
{
con = new SqlConnection(h);
con.Open();
cmd = new SqlCommand("select * from ImgTbl2 where ID=@id", con);
cmd.Parameters.AddWithValue("@id", txtid.Text.ToString());
SqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
byte[] imageBytes = Convert.FromBase64String(reader["Img"].ToString());
String name = reader["ImgName"].ToString();
lblimagename.Text = name;
MemoryStream ms1 = new MemoryStream(imageBytes);
Image img = Image.FromStream(ms1);
pictureBox1.BackgroundImage = img;
}
con.Close();
}
catch (Exception ex)
{
if (con.State == ConnectionState.Open)
con.Close();
}
}
}
}
I will be posting remaining methods in couple of days.