Insert Image into SQL Server database using Parameter.Dbtype (stored procedure)

Question

I'm trying to insert IMAGE into SQL Server database (via a stored procedure), for this I'm using a class that has parameter details and in code behind on button click event I tried to map the values.

param = comm.CreateParameter();
param.ParameterName = "Imagedata";
param.Value = Imagedata;
param.DbType = DbType.String;
comm.Parameters.Add(param);

I tried using Binary instead of String, I got an error stating that unable to convert String to Byte[]. Datatype I used in SQL was Varbinary(MAX).

bool a = false;
String imagefilepath = @fileName;
FileStream imagefile = new FileStream(imagefilepath, FileMode.Open, FileAccess.Read);
Imagedata = new Byte[imagefile.Length];
imagefile.Read(Imagedata, 0, Imagedata.Length);
imagefile.Flush();
imagefile.Close();
a = Users.InsertUser(this.txt_userid.Text.Trim().ToUpper(),
                     this.txt_mobnum.Text.Trim().ToUpper(), 
                     this.txt_name.Text,
                     this.role_cmbox.Text.Trim().ToUpper(), 
                     this.box_branch.Text.Trim().ToUpper(), 
                     this.txt_designation.Text.Trim().ToUpper(), 
                     this.txt_repassword.Text.Trim().ToUpper(), 
                     this.Imagedata.Length.ToString());

Stored procedure

[dbo].[InsertUser](@UserID varchar(15),@Password varchar(20),@UserName varchar(20),  
@Role varchar(15),@Branch varchar(15),@Designation varchar(15),@Mobilenumber varchar(15),@Imagedata varbinary(MAX))    
as  
INSERT INTO[LBank].dbo.[Login]
           ([UserID]
           ,[Password]  
           ,[UserName]  
           ,[Role]
           ,[Branch]
           ,[Designation]
           ,[Mobilenumber]
           ,[Imagedata]
           )   
VALUES(@UserID,@Password,@UserName,@Role,@Branch,@Designation,@Mobilenumber,@Imagedata);

What should be the DbType and how to solve and insert image successfully?

Am I wrong or the last parameter of the call to Users.InsertUser is the length of the ImageData array instead of a reference to the array? — Steve
– Steve, Commented Mar 8, 2015 at 22:30
What is Users.InsertUser? In particular, what is the last parameter to that method? — John Saunders
– John Saunders, Commented Mar 9, 2015 at 5:36
You need to use SqlDbType.Image in param.DbType instead of String. String and Binary Data (image data) is different. — Krishnandu Sarkar
– Krishnandu Sarkar, Commented Mar 9, 2015 at 6:34
@Steve Please let me know in detail.. i'm taking in the byte length, if its Reference to the array what should be then. — Rufus Kumar
– Rufus Kumar, Commented Mar 9, 2015 at 6:58

Community · Accepted Answer · 2017-05-23 12:23:52Z

4

You have to use SqlDbType instead of DbType.
And for File/Image to Byte Array you can use File.ReadAllBytes() instead of FileStream (refer this)

Pass Byte Array instead of Byte Array's Length in your InsertUser Methods's last parameter.

    bool a = false;
    String imagefilepath = @fileName;
    ImageData = File.ReadAllBytes(imagefilepath);

    a = Users.InsertUser(this.txt_userid.Text.Trim().ToUpper(),
                   this.txt_mobnum.Text.Trim().ToUpper(),
                   this.txt_name.Text,
                   this.role_cmbox.Text.Trim().ToUpper(),
                   this.box_branch.Text.Trim().ToUpper(),
                   this.txt_designation.Text.Trim().ToUpper(),
                   this.txt_repassword.Text.Trim().ToUpper(),
                   this.Imagedata); //Here you need to pass the byte array not length


    var param = comm.CreateParameter();
    param.ParameterName = "Imagedata";
    param.Value = Imagedata;
    param.SqlDbType = SqlDbType.Image;
    comm.Parameters.Add(param);

Hope this helps.

edited May 23, 2017 at 12:23

CommunityBot

11 silver badge

answered Mar 9, 2015 at 7:35

SelvaS

2,1251 gold badge24 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Rufus Kumar Over a year ago

File.ReadAllBytes .. thanks for helping me out worked for me Unable to Vote but really use full :( coz of reputation

Collectives™ on Stack Overflow

Insert Image into SQL Server database using Parameter.Dbtype (stored procedure)

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related