CODEWITHSUNDEEP
c#.netsql-serverwinforms
Rufus Kumar
Rufus Kumar

Reputation: 15

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

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?

Upvotes: 0

Views: 6871

Answers (1)

SelvaS
SelvaS

Reputation: 2125

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

  3. 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.

Upvotes: 4

Related Questions