How to send SqlParameter to WCF where SQLParameter contains Bytes[]?

Question

I am trying to send SqlPrameter to wcf service. The SqlParameter param which I am trying to send includes string,integer and Bytes (I am storing file in Database in varbinary). The

The following is the C# code I am using in my application:

Public Function InsertadanceClaimAttachMents(ByVal params As SqlParameter()) As Integer Implements IService.InsertadanceClaimAttachMents
       Dim i As Int16
       Try
           sql = ""
           sql = "Proc_InsertAdvanceClamAtt"

           i = Db.ExecProcedure(Declarations.ConnectionString, sql, params)
           Return i
       Catch ex As Exception
           Db.WriteErrorLog(ex, "Wcf_DBAccess.vb", "InsertadanceClaimAttachMents")
           Return 0
       End Try
   End Function

The following is the VB function I am using in my WCF service

  public bool insertAdvanceAttachments(Byte[] bytes, string filename, string ClaimNo, string mkrid)
       {
           Int32 I;
           SqlConnection con = new SqlConnection(conn);
           SqlCommand cmd = new SqlCommand();
           SqlDataAdapter da = new SqlDataAdapter();
           cmd.Connection = con;
           cmd.CommandType = CommandType.StoredProcedure;
           cmd.CommandText = "Proc_InsertAdvanceClamAtt";


           cmd.Parameters.AddWithValue("@img", bytes);
           cmd.Parameters.AddWithValue("@imgname", filename);
           cmd.Parameters.AddWithValue("@ClaimNo", ClaimNo);
           cmd.Parameters.AddWithValue("@mkrid", mkrid);

           try
           {
               con.Open();
               cmd.ExecuteNonQuery();
               return true;
           }
           catch
           {
               return false;
           }
           //finally { con.Close(); }


           //try
           //{
           //    // string param, paramval;
           //    //param = "@img|@imgname|@ClaimNo|@mkrid";
           //    //paramval = ASCIIEncoding.ASCII.GetString(bytes).ToString() + "|" + filename + "|" + ClaimNo + "|" + mkrid;


           //    //SqlParameter[] param = { new SqlParameter("@img", bytes ), new SqlParameter("@imgname", filename), new SqlParameter("@ClaimNo", ClaimNo), new SqlParameter("@mkrid", mkrid) };


           //    SqlParameter[] param = { new SqlParameter("@img", SqlDbType.VarBinary, bytes.Length, ParameterDirection.Input, false, 0, 0, "Data", DataRowVersion.Current, (SqlBinary)bytes), new SqlParameter("@imgname", filename), new SqlParameter("@ClaimNo", ClaimNo), new SqlParameter("@mkrid", mkrid) };

           //    I = baz_obj.InsertadanceClaimAttachMents(param);
           //    return true;
           //}
           //catch (Exception ex)
           //{
           //    return false;
           //}
           return true;
       }

The problem is that I am not even able to get to the WCF service get following error:

There was an error while trying to serialize parameter
http://tempuri.org/:params.
The InnerException message was 'Type 'System.Data.SqlTypes.SqlBinary' with data contract name 'base64Binary:http://www.w3.org/2001/XMLSchema' is not expected. Consider using a DataContractResolver or add any types not known statically to the list of known types - for example, by using the KnownTypeAttribute attribute or by adding them to the list of known types passed to DataContractSerializer.'. Please see InnerException for more details

UPDATE

SqlParameter[] param = { new SqlParameter("@img", SqlDbType.VarBinary), new SqlParameter("@imgname", SqlDbType.VarChar), new SqlParameter("@ClaimNo", SqlDbType.VarChar), new SqlParameter("@mkrid", SqlDbType.VarChar) };
           param[0].Value = bytes;
           param[1].Value = filename;
           param[2].Value = ClaimNo;
           param[3].Value = mkrid;

Are you trying to send a SqlParameter by WCF? That's a terrible idea. This is going to be a gigantic security issue and I'm pretty sure a SQLParameter is not serialisable. Just send your data to your service and let that create the SqlParameters — Liam
– Liam, Commented Feb 5, 2014 at 10:42

Community · Accepted Answer · 2017-05-23 12:28:39Z

1

Dim data As byte()
Dim parameter As New SqlParameter("@Param1", SqlDbType.VarBinary);
parameter.Value = data

you can use SqlDbType.Image instead of SqlDbType.VarBinary

Check out this question also not the same question but you can get your answer from here.

edited May 23, 2017 at 12:28

CommunityBot

11 silver badge

answered Feb 5, 2014 at 10:44

Shell

6,86911 gold badges43 silver badges75 bronze badges

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

1 Comment

Liam Over a year ago

this does not address the WCF issue. Even if the OP added this code, his methodology still will not work.

Murali Murugesan · Accepted Answer · 2014-02-05 10:49:38Z

0

Set your parameter type to VarBinary

SQLParameter param1=new SqlParameter("@img",SqlDbType.VarBinary);
param1.Value=bytes
param.Add(param1);
param.AddRange(etc);

Check Data type mappings

edited Feb 5, 2014 at 10:49

answered Feb 5, 2014 at 10:39

Murali Murugesan

22.6k18 gold badges76 silver badges123 bronze badges

Collectives™ on Stack Overflow

How to send SqlParameter to WCF where SQLParameter contains Bytes[]?

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related