title: Not able to store SQL encrypted data [ SQL level encryption ] into SQL Database using command parameter in c#
body: <p>I am using SQL Encryption to encrypt my data. I get the Plain text from user, encrypt it using SQL Encryption and insert it into SQL DB into a varbinary column.
  However, the problem that I am facing is that my data is not getting stored completely. if I enter 5000, encrypt it and store to DB, upon decrypting it only returns 5 i.e. the first character. I am looking at this for 3 days now still not able to resolve it. 
  Below is the code to encrypt User input :`</p>
  
  <pre><code>    public static readonly String queryOpen = "OPEN SYMMETRIC KEY SymmetricKey1 DECRYPTION BY CERTIFICATE Certificate1; "; //unmodifiable because its readonly
      public static readonly String queryClose = ";CLOSE SYMMETRIC KEY SymmetricKey1;"; //unmodifiable because its readonly
  
  
   public static Byte[] EncryptInput(string input)
          {
  
              SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["test"].ConnectionString);
              con.Open();
  
              string query = queryOpen + "DECLARE @output varbinary(MAX);SET @output =  EncryptByKey(Key_GUID('SymmetricKey1'),@input)" + queryClose + "select @output";
              SqlCommand cmd = new SqlCommand(query, con);
              cmd.Parameters.AddWithValue("@input",input);
  
              Byte[] output = cmd.ExecuteScalar() as Byte[];
  
              return output;
  
  
          }
  </code></pre>
  
  <p>The above code takes input from user and encrypts it using SQL encryption and returns as Byte.
  Below is the code to insert the above returned data i.e output into SQL DB</p>
  
  <pre><code>public static readonly String queryOpen = "OPEN SYMMETRIC KEY SymmetricKey1 
  DECRYPTION BY CERTIFICATE Certificate1; "; //unmodifiable because its readonly
  
  public static readonly String queryClose = ";CLOSE SYMMETRIC KEY SymmetricKey1;"; //unmodifiable because its readonly
  
  string insertQuery = queryOpen + "insert into fee(feePaid) values (@feePaid)" + Globals.queryClose;
  
   SqlCommand cmd = new SqlCommand(insertQuery, con);
  cmd.Parameters.Add("feePaid", SqlDbType.VarBinary, int.MaxValue).Value = Globals.EncryptInput(TextBox1.Text); // The EncryptInput Method is called here
  
  cmd.ExecuteNonQuery();
  </code></pre>
  
  <p>Can some one please help and advise? Why am i getting only first character when decrypting e.g I get 5 if user input is 5000.</p>