Save Byte[] into a SQL Server Database from C#

Save byte[] into a SQL Server database from C#

You should be able to write something like this:

string queryStmt = "INSERT INTO dbo.YourTable(Content) VALUES(@Content)";

using(SqlConnection _con = new SqlConnection(--your-connection-string-here--))
using(SqlCommand _cmd = new SqlCommand(queryStmt, _con))
{
SqlParameter param = _cmd.Parameters.Add("@Content", SqlDbType.VarBinary);
param.Value = YourByteArrayVariableHere;

_con.Open();
_cmd.ExecuteNonQuery();
_con.Close();
}

Using Linq-to-SQL, you'd write something like this:

using(YourDataContextHere ctx = new YourDataContextHere())
{
SomeClassOfYours item = new SomeClassOfYours();

item.ByteContent = (your byte content here);

ctx.SomeClassOfYourses.InsertOnSubmit(item);
ctx.SubmitChanges();
}

That will insert your byte[] into a column Content of type VARBINARY in your SQL Server table as a byte stream, which you can read back 1:1 again later on.

c# put a byte[] into an database and retrive later

Use BINARY or VARBINARY to store binary data.

string query = "INSERT INTO dbo.MyTable(Content) VALUES(@Content)";

using(SqlConnection connection = new SqlConnection(/*yout connection string here*/))
using(SqlCommand command = new SqlCommand(query, connection))
{
connection.Open();
SqlParameter param = command.Parameters.Add("@Content", SqlDbType.VarBinary);
param.Value = YourByteArrayVariableHere;

command.ExecuteNonQuery();
}

You could retrieve it by using a SqlDataReader to get data and than cast the result as byte array. This code gets only the first row. If you want to get more use while (d.Read()) instead of the if (d.Read()).

string query = "Select Content from dbo.MyTable";
using(SqlConnection connection = new SqlConnection(/*your connection string here*/))
using(SqlCommand command = new SqlCommand(query, connection))
{
connection.Open();
using (SqlDataReader d = command.ExecuteReader())
{
if (d.Read())
{
byte[] byteArray = (byte[])d["Content"];
}
}
}

Insert byte array into SQL Server from C# and how to retrieve it

You send your values as literals in your SQL query. This is a bad idea, but first the problem with your query:

A varbinary literal in SQL Server is not a string (enclosed in quotes), but a very big hex number, looking something like this example: SET @binary = 0x1145A5B9C98.

By the way, I find it strange that you enclose your ID and your Length in quotes as well. I assume they are integers, so they should be specified without quotes: SET @i = 2. It may still work in your case, because the string is going to be converted to integer by SQL Server on the fly. It's just confusing and less efficient.

Now, please never do SQL requests by concatenating literals like that. Use SQL parameters instead. Something like that:

cmd.CommandText = "INSERT INTO Files (FileId, Data, Length) VALUES (@id, @data, @length)";
cmd.Parameters.AddWithValue("id", 3);
cmd.Parameters.AddWithValue("data", someByteArray);
cmd.Parameters.AddWithValue("length", someByteArray.Length);

If you want to make even simpler, look into some helper. I recommend Dapper.

Lastly, I note that you are storing both a varbinary and its length. That's not required, you can always get the length of a varbinary stored in SQL Server like this: SELECT LEN(Data) FROM Files

Save Byte[] in odbc with c#

Fixing the SQL Injection vulnerability in your code will also solve the problem with inserting the image data.

You should avoid storing connection and command objects in fields. Instead, they should be created as local variables, and wrapped in using statements to ensure that their resources are always cleaned up.

If you can't move the DbConnection field to a local variable yet, then you should call its Close method in a finally block.

You shouldn't call ExecuteReader on a command that isn't going to return any records - INSERT, UPDATE or DELETE commands. Instead, call ExecuteNonQuery, which returns the number of rows affected.

You also don't need to call ExecuteReader to test whether a record exists. Just select the ID of the first matching record, and use ExecuteScalar to return that value.

public void RegistroHuella(ComboBox ComboBx, Label LabelMs, Byte[] bytes)
{
try
{
int hola = ComboBx.SelectedIndex;

ConexionHuella();

using (var command = DbConnection.CreateCommand())
{
command.CommandText = "SELECT TOP 1 ID FROM HUELLAS WHERE ID = ?";
command.Parameters.AddWithValue("@hola", hola);

if (command.ExecuteScalar() != null)
{
LabelMs.Text = "El estudiante ya existe en la base de datos";
return;
}
}

using (var command = DbConnection.CreateCommand())
{
command.CommandText = "INSERT INTO HUELLAS VALUES (?, ?)";
command.Parameters.AddWithValue("@hola", hola);
command.Parameters.AddWithValue("@bytes", bytes);

int recordsAffected = command.ExecuteNonQuery();
if (recordsAffected > 0)
{
LabelMs.Text = "El estudiante ha sido registrado correctamente.";
}
else
{
LabelMs.Text = "Hubo un problema al momento de registrar a este usuario.";
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
//LabelMs.Text = ex.Message;
}
finally
{
DbConnection.Close();
}
}

NB: The OdbcCommand doesn't seem to work with named parameters. You have to use ? as the parameter placeholder, and ensure that the parameters are added to the collection in the same order as they appear in the query.

saving bytearray to VarBinary column in SQL Server inserts only one byte

If you're using a stored procedure, and you've defined your parameter as just varbinary - you'll get a default length of 1 byte as per MSDN documentation:

When n is not specified in a data definition or variable declaration statement, the default length is 1. When n is not specified with the CAST function, the default length is 30.

So if you have a stored procedure with

@MyData VARBINARY

then you have just one single byte - you need to change that to something like

@MyData VARBINARY(200) 

or something else that's suitable for you

Insert and retrieve ByteArray from SQL Server in C#

row["Test"] contains byte[], so row["Test"].ToString() returns default ToString of byte[] type, which is "System.Byte[]". After which you convert System.Byte[] and get that result

foreach (DataRow row in dt.Rows)
{
byte[] byteArray = (byte[])row["test"];
}

Image byte array is not saving properly to database

The default length of varbinary is 1 if the length isn't specified. See MSDN:

The default length is 1 when n isn't specified in a data definition or variable declaration statement

CREATE PROCEDURE [dbo].[SetProfilePicture]
@Image VARBINARY,

needs to be given a length, as such:

CREATE PROCEDURE [dbo].[SetProfilePicture]
@Image VARBINARY(max),


Related Topics



Leave a reply



Submit