Save image to Database Sql Server Uwp c#

Question

Save image to Database Sql Server Uwp c#

cmd.Parameters.Add("@picture", SqlDbType.Image).Value = Photo;

Answer 1

Hi,

Welcome to Microsoft Q&A!

If you want to use SqlDbType.Image, you could convert the image to a byte array and save it to the database like the following.
Besides, for large file, it is recommended to save the image path to the database.

using System.Runtime.InteropServices.WindowsRuntime;  
using Windows.Storage;  
......  
IStorageFile file;  
IBuffer buffer = await FileIO.ReadBufferAsync(file);  
byte[] picbytes= buffer.ToArray();  
……  
cmd.Parameters.Add("@picture", SqlDbType.Image).Value = picbytes;  

Update:

You could query the image from the database and show it in Image control, which will verify whether you store it correctly.

  try{  
            string sql = "select picture from imagetable";  
            SqlConnection sc = new SqlConnection(connstring);  
            sc.Open();  
            SqlCommand scmd = new SqlCommand(sql, sc);  
            SqlDataReader red = scmd.ExecuteReader();  
            if (red.Read())  
            {  
                    BitmapImage bitmap = new BitmapImage();  
                    byte[] bytes = (byte[])red[0];  
                    var stream = new Windows.Storage.Streams.InMemoryRandomAccessStream();  
                    await stream.WriteAsync(bytes.AsBuffer());  
                    stream.Seek(0);  
                    await bitmap.SetSourceAsync(stream);  
                    myImage.Source = bitmap; //myImage is Image control  
            }  
            red.Close();   
                         }  
            catch (Exception ex)  
            {  
                MytextBlock.Text = ex.Message;  
            }  

Update2:

The error message prompts ”System.NullReferenceException”, so you could check if the file is null before you operate IBuffer buffer = await FileIO.ReadBufferAsync(file).

In addition, if you want to convert the SqlDataReader to List<Class>, you need to create a BitmapImage type property for the class. Then you could read the byte array from the database and convert it to a stream, and set it as the source of this property.

For example:

PetTable.cs:

public class PetTable  
{  
public BitmapImage Bitmap{get;set;}  
public string PetName{get;set;}  
public string PetColor{get;set;}  
}  

MainPage.cs:

public ObservableCollection<PetTable> pets;  
public MainPage()  
    {  
        this.InitializeComponent();     
        pets= newObservableCollection<PetTable>();  
      ......  
     while (reader.Read())  
                {  
                    PetTable pet= new PetTable ();  
BitmapImage map=new BitmapImage();  
                    byte[] bytes = (byte[])reader["Picture"]; // Picture is the column name  
var stream = new Windows.Storage.Streams.InMemoryRandomAccessStream();  
                    await stream.WriteAsync(bytes.AsBuffer());  
                    stream.Seek(0);  
                    await map.SetSourceAsync(stream);  
pet.Bitmap=map;  
                    pet.PetName = (string)reader["Name"];     
pet.PetColor = (string)reader["Color"];                 
                    pets.Add(pet);  
                }  

}

Finally, you could put an Image control on your XAML page and test it like the following, if the image shows in the control, which means your code works well.

MyImageControl.Source=pets[0].Bitmap;   

I have updated my Update2 according to your new question, please try the above code. If you follow it, you only need to specify the x:DataType for the Datatemplate. As follows:

<control:DataGrid AutoGenerateColumns="False" ItemsSource="{x:Bind pets, Mode=OneWay}">  
            <control:DataGrid.Columns>  
                    <control:DataGridTemplateColumn Header="Picture">  
                        <control:DataGridTemplateColumn.CellTemplate>  
                            <DataTemplate x:DataType="local:PetTable " >  
                                <Image x:Name="MyImage" Width="30" Height="30" Source="{x:Bind BitMap}"/>  
                            </DataTemplate>  
                        </control:DataGridTemplateColumn.CellTemplate>  
                   </control:DataGridTemplateColumn>                  
            </control:DataGrid.Columns>  
</control:DataGrid>  

Update3:

public ObservableCollection<PetTable> pets;  
   public MainPage()  
        {  
            this.InitializeComponent();    
            bind();  
        }  
  
       public async void bind()  
        {  
              pets= newObservableCollection<PetTable>();  
               ......  
             while (reader.Read())  
                {......}  
  
        }  

---

If the response is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

This is the code I Have to show Class.cs called PetConection..cs

public static async Task<ObservableCollection<PetTable>> GetPets()
{
const string GetPetTableQuery = "SELECT Picture, Type, DuoDate, PetName, PetRace, PetColor, PetChip, PetGender, PetSterile FROM PetTable";

            var pets = new ObservableCollection<PetTable>();
            try
            {
                using (SqlConnection conn = new SqlConnection(ConnectionString))
                {
                    await conn.OpenAsync();

                    if (conn.State == System.Data.ConnectionState.Open)
                    {
                        using (SqlCommand cmd = new SqlCommand(GetPetTableQuery, conn))
                        {
                            using (SqlDataReader reader = cmd.ExecuteReader())
                            {
                                if (reader.HasRows)
                                {

                                    BitmapImage bitmap = new BitmapImage();
                                    byte[] bytes = (byte[])reader[0];
                                    var stream = new Windows.Storage.Streams.InMemoryRandomAccessStream();
                                    await stream.WriteAsync(bytes.AsBuffer());
                                    stream.Seek(0);
                                    await bitmap.SetSourceAsync(stream);

                                    while (reader.Read())
                                    {
                                        var pet = new PetTable
                                        {



                                            Type = reader.GetString(0),
                                            DuoDate = reader.GetDateTime(1),
                                            PetName = reader.GetString(2),
                                            PetRace = reader.GetString(3),
                                            PetColor = reader.GetString(4),
                                            PetChip = reader.GetString(5),
                                            PetGender = reader.GetString(6),
                                            PetSterile = reader.GetString(7),
                                        };

                                        pets.Add(pet);
                                    }
                                }
                            }

                        }
                    }
                }

                return pets;
            }
            catch (Exception ex)
            {
                var Msg = new MessageDialog("Exeception:" + ex.Message);
                Msg.Commands.Add(new UICommand("Close"));

            }

            return null;
        }

Answer 3

enter code herein the mainpage example has await appears therefore this code part cannot go InitializaComponet.

when I enter the data the image according to PetTable.Cs is Bitmap although I have this to enter the data. doub.t is to enter the image in database

using (SqlConnection conn = new SqlConnection(connectionString))
                {
                    conn.Open();

                    if (conn.State == System.Data.ConnectionState.Open)
                    {
                        using (SqlCommand cmd = conn.CreateCommand())
                        {

                            cmd.CommandText = SqlString;
                            cmd.Parameters.Clear();

                            MemoryStream memStream = new MemoryStream();
                            byte[] picbytes = memStream.GetBuffer();



                            cmd.Parameters.Add("@picture", SqlDbType.Image).Value = picbytes;
                            cmd.Parameters.Add("@pettype", SqlDbType.VarChar, 40).Value = txtMascota.Text;
                            cmd.Parameters.Add("@duoDate", SqlDbType.Date).Value = DateNac.SelectedDate.Value.Date.ToLocalTime();
                            cmd.Parameters.Add("@petName", SqlDbType.VarChar, 40).Value = txtName.Text;
                            cmd.Parameters.Add("@petRace", SqlDbType.VarChar, 40).Value = txtRace.Text;
                            cmd.Parameters.Add("@petColor", SqlDbType.VarChar, 40).Value = txtColor.Text;
                            cmd.Parameters.Add("@petChip", SqlDbType.VarChar, 10).Value = txtChip.Text;
                            cmd.Parameters.Add("@petGender", SqlDbType.VarChar, 40).Value = (bool)radioMale.IsChecked
                                ? radioMale.Content : radioFemale.Content;
                            cmd.Parameters.Add("@petSterile", SqlDbType.VarChar, 40).Value = (bool)rbesteri.IsChecked
                                ? rbesteri.Content : rbnoesteri.Content;




                            cmd.ExecuteNonQuery();
                            conn.Close();

                            MessageDialog Md = new MessageDialog("The record has been inserted");
                            await Md.ShowAsync();

Answer 4

does not display data in the dataGrid
public async void bind()
{

             pets = new ObservableCollection<PetTable>();
            try
            {
                const string GetPetTableQuery = "SELECT  PetType, DuoDate, PetName, PetRace, PetColor, PetChip, PetGender, PetSterile    FROM  PetTable";

                using (SqlConnection conn = new SqlConnection(connectionString))
                {
                    await  conn.OpenAsync();

                    if (conn.State == System.Data.ConnectionState.Open)
                    {
                        using (SqlCommand cmd = new SqlCommand(GetPetTableQuery, conn))
                        {
                            using (SqlDataReader reader = cmd.ExecuteReader())
                            {
                                if (reader.HasRows)
                                {
                                    while (reader.Read())
                                    {
                                        PetTable pet = new PetTable();

                                        BitmapImage map = new BitmapImage();
                                        byte[] bytes = (byte[])reader["Picture"];
                                        var stream = new InMemoryRandomAccessStream();
                                        await stream.WriteAsync(bytes.AsBuffer());
                                        stream.Seek(0);
                                        await map.SetSourceAsync(stream);
                                        pet.Picture = map;




                                        pet.PetType = (string)reader["Type"];
                                        pet.DuoDate = (DateTime)reader["Date"];
                                        pet.PetName = (string)reader["Name"];
                                        pet.PetRace = (string)reader["Race"];
                                        pet.PetColor = (string)reader["Color"];
                                        pet.PetChip = (string)reader["Chip"];
                                        pet.PetGender = (string)reader["Gender"];
                                        pet.PetSterile = (string)reader["Sterile"];

                                        pets.Add(pet);



                                    }

                                }


                            }

                        }
                    }


                }


            }
            catch (Exception ex)
            {
                var Msg = new MessageDialog("Exeception:" + ex.Message);
                Msg.Commands.Add(new UICommand("Close"));

            }


        }

Answer 5

No problem for OneDrive to try it, can be same project