从SQL Server检索数据时创建DataGridView自定义列 [英] Create DataGridView Custom Column while retrieving Data from SQL Server

问题描述

我是WPF新手并开发股票管理系统。为此，我正在使用WPF和SQL Server数据库。我想在数据库中存储和检索产品图像。为此，我正在使用数据网格视图。我以byte []
数组的形式存储图像，但字段类型是数据库中的Image。

// [Product_Image]鸟    IMAGE NULL，

记录已成功插入但在数组中 格式。在使用数据网格选择数据时，它会在图像列中显示冗长的数组。我有各种方法将其转换回图像格式。 

但主要问题是，如何在显示DataGrid中的数据之前操作Datagridview中的图像列。

以下是我的一些代码段;

======= FROM AddInventoryPage.xaml.cs =======

//点击上传图片按钮时，图片存储在'fs'中，而fs在类范围内声明为

// FileStream fs;

        private void productPicture_Click（object sender，RoutedEventArgs e）

        {

            dbContext = new MyInventoryLINQtoSQLClassDataContext（connectionString）;

            InventoryTable inventoryTableObject = new InventoryTable（）;



            OpenFileDialog op = new OpenFileDialog（）;

            op.Title =" Select a Picture"; $
            op.Filter =" JPEG Files（* .jpg）| * .jpg | PNG Files（* .png）| * .png | BMP Files（* .bmp）| * .bmp | All file" + 

                "s（*。*）| *。*" ;;
$


            if（op.ShowDialog（）== true）

            {

                uploadImageControl.Source = new BitmapImage（new Uri（op.FileName））;

                fs = new FileStream（op.FileName，FileMode.Open，FileAccess.Read）;

            }¥b $ b        }

//点击保存记录按钮时，执行以下函数

private void addInventoryButton_Click（object sender，RoutedEventArgs e）

        {

            dbContext = new MyInventoryLINQtoSQLClassDataContext（connectionString）;

            InventoryTable inventoryTableObject = new InventoryTable（）;

            byte [] data = new byte [fs.Length];

            fs.Read（data，0，System.Convert.ToInt32（fs.Length））;

            fs.Close（）;



            inventoryTableObject.Product_Image = data;    //将图像存储在数据库中

            inventoryTableObject.Product_Title = productTitle.Text;

            inventoryTableObject.Product_Model_SKU = productModel.Text;

            inventoryTableObject.Vendor_Name = vendorName.Text;

            inventoryTableObject.Company_Name = companyName.Text;

            inventoryTableObject.Category = productCategory.Text;

            inventoryTableObject.Purchase_Price = double.Parse（purchasePrice.Text）;

            inventoryTableObject.Retail_Price = double.Parse（retailPrice.Text）;

            inventoryTableObject.Retail_Price_Limit = double.Parse（retailPriceLimit.Text）;

            inventoryTableObject.Size = size.Text;

            inventoryTableObject.Color = color.Text;

            inventoryTableObject.Quantity = int.Parse（quantity.Text）;

            inventoryTableObject.Reorder_Quantity = int.Parse（reorderQuantity.Text）;

            inventoryTableObject.Product_Location = productLocation.Text;

            inventoryTableObject.Metadata = metadata.Text;
$


            dbContext.InventoryTables.InsertOnSubmit（inventoryTableObject）;

            dbContext.SubmitChanges（）;

            dbMsgTextBlock.Text =" One Record已添加到数据库中！" ;;

        }

======= MyInventoryPage.xaml.cs =======

使用System.Windows.Controls;

使用System.Data.SqlClient;

使用System.Data;

使用System.Windows.Forms;



名称空间IMS_Waqar

{

$
  &NBSP; public partial class< g class =" gr_ gr_30 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace"数据-GR-ID =" 30英寸; id =" 30"> Myinventory< / g>< g class =" gr_ gr_31 gr-alert
gr_gramm gr_inline_cards gr_run_anim Style replaceWithoutSep"数据-GR-ID =" 31 QUOT; id =" 31">：Page< / g>

  &NBSP; {

  &NBSP; &NBSP;   

  &NBSP; &NBSP; &NBSP; public MyInventory（）

  &NBSP; &NBSP; &NBSP; {

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; InitializeComponent（）;

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; MyInventoryLINQtoSQLClassDataContext dbContext = new MyInventoryLINQtoSQLClassDataContext（）;

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; MyInventoryDatagrid.ItemsSource = dbContext.InventoryTables;

  &NBSP; &NBSP; &NBSP; }


  &NBSP; &NBSP; &NBSP; private void bindDatagrid（）

  &NBSP; &NBSP; &NBSP; {

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; SqlConnection con = new SqlConnection（）;

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; con.ConnectionString =（@"Data Source =（LocalDB）\ MSSQLLocalDB; AttachDbFilename = D：\IMS \IMS_Waqar \IMS_Waqar \ims_db.mdf; Integrated Security = True"）;

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; con.Open（）;

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; SqlCommand cmd = new SqlCommand（）;

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; cmd.CommandText =" select * from [InventoryTable]";

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; cmd.Connection = con;

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; SqlDataAdapter da = new SqlDataAdapter（cmd）;

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; DataTable dt = new DataTable（&"InventoryTable"）;

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; da.Fill（dt）;

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; MyInventoryDatagrid.ItemsSource = dt.DefaultView;

  &NBSP; &NBSP; &NBSP; &NBSP;   

  &NBSP; &NBSP; &NBSP; }¥b $ b  &NBSP; }
}

请帮助我解决此问题。 

或If您有更好的选择来存储和检索数据库中的图像，请分享您的体验。问候！

$

解决方案

>>但主要问题是，如何在DataGrid中显示数据之前操作Datagridview中的image列。

Hi Waqar，

根据您的描述，您的意思是 你想知道如何在DataGrid中加载图像？数据库中有byte []，你想在DataGrid中显示图像。

你可以使用的IValueConverter接口。

 < Window.Resources> 
< local：BinaryImageConverter x：Key =" BinaryToBitMapConverter" /> 
< /Window.Resources> 
< Grid> 
< Grid.Resources> 
< DataTemplate x：Key =" DataTemplate"> 
< Image Source =" {Binding Path = ImageData，Converter = {StaticResource BinaryToBitMapConverter}}" /> 
< / DataTemplate> 
< /Grid.Resources> 
 
< DataGrid Name =" DG1"的ItemsSource = QUOT; {结合}"的AutoGenerateColumns = QUOT假QUOT; > 
< DataGrid.Columns> 
< DataGridTextColumn Header =" First Name" Binding =" {Binding Path = FistName}" /> 
< DataGridTemplateColumn Header =" Picture" CellTemplate =" {StaticResource DateTemplate}" /> 
< /DataGrid.Columns> 
< / DataGrid> 
 
< / Grid> 

 public class BinaryImageConverter：IValueConverter 
 {
 public object Convert（object value，Type targetType，object parameter，System.Globalization.CultureInfo culture）
 {
 if（value！= null） && value是byte []）
 {
 byte [] ByteArray = value as byte []; 
 BitmapImage bmp = new BitmapImage（）; 
 bmp.BeginInit（）; 
 bmp.StreamSource = new MemoryStream（ByteArray）; 
 bmp.EndInit（）; 
返回bmp; 
} 
返回null; 
} 
公共对象ConvertBack（对象值，类型targetType，对象参数，System.Globalization.CultureInfo文化）
 {
 throw new Exception（"方法或操作不是。实施"）; 
} 
} 

最好的问候，

Cherry

I'm new to WPF and developing a Stock Management System. For this purpose, I'm using WPF and SQL Server Database. I want to store and retrieve product images in the database. For this purpose, I'm using data grid view. I'm storing images in form of byte[] array but the field type is Image in the database.

//[Product_Image]      IMAGE NULL,

The record is inserted successfully but in an array  format. While selecting data using data grid, it shows lengthy arrays in the image column. I got various ways to convert it back to Image format. 
But the main question is, how to manipulate image column in Datagridview before showing the data in DataGrid.
Here are some of my code snippets;

======= FROM AddInventoryPage.xaml.cs =======

//While clicking on the Upload Image button, image stores in 'fs' while fs is declared at class scope as

//FileStream fs;

        private void productPicture_Click(object sender, RoutedEventArgs e)
        {
            dbContext = new MyInventoryLINQtoSQLClassDataContext(connectionString);
            InventoryTable inventoryTableObject = new InventoryTable();

            OpenFileDialog op = new OpenFileDialog();
            op.Title = "Select a Picture";
            op.Filter = "JPEG Files (*.jpg)|*.jpg|PNG Files (*.png)|*.png|BMP Files (*.bmp)|*.bmp|All file" + 
                "s (*.*)|*.*";

            if (op.ShowDialog() == true)
            {
                uploadImageControl.Source = new BitmapImage(new Uri(op.FileName));
                fs = new FileStream(op.FileName, FileMode.Open, FileAccess.Read);
            }
        }

//while clicking on the Save record button, the following function executes

private void addInventoryButton_Click(object sender, RoutedEventArgs e)
        {
            dbContext = new MyInventoryLINQtoSQLClassDataContext(connectionString);
            InventoryTable inventoryTableObject = new InventoryTable();

            byte[] data = new byte[fs.Length];
            fs.Read(data, 0, System.Convert.ToInt32(fs.Length));
            fs.Close();

            inventoryTableObject.Product_Image = data;    //storing image in database
            inventoryTableObject.Product_Title = productTitle.Text;
            inventoryTableObject.Product_Model_SKU = productModel.Text;
            inventoryTableObject.Vendor_Name = vendorName.Text;
            inventoryTableObject.Company_Name = companyName.Text;
            inventoryTableObject.Category = productCategory.Text;
            inventoryTableObject.Purchase_Price = double.Parse(purchasePrice.Text);
            inventoryTableObject.Retail_Price = double.Parse(retailPrice.Text);
            inventoryTableObject.Retail_Price_Limit = double.Parse(retailPriceLimit.Text);
            inventoryTableObject.Size = size.Text;
            inventoryTableObject.Color = color.Text ;
            inventoryTableObject.Quantity = int.Parse(quantity.Text);
            inventoryTableObject.Reorder_Quantity = int.Parse(reorderQuantity.Text);
            inventoryTableObject.Product_Location = productLocation.Text;
            inventoryTableObject.Metadata = metadata.Text;

            dbContext.InventoryTables.InsertOnSubmit(inventoryTableObject);
            dbContext.SubmitChanges();
            dbMsgTextBlock.Text = "One Record has been added to the database!";
        }

======= MyInventoryPage.xaml.cs =======

using System.Windows.Controls;
using System.Data.SqlClient;
using System.Data;
using System.Windows.Forms;

namespace IMS_Waqar
{

    public partial class <g class="gr_ gr_30 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" data-gr-id="30" id="30">Myinventory</g><g class="gr_ gr_31 gr-alert gr_gramm gr_inline_cards gr_run_anim Style replaceWithoutSep" data-gr-id="31" id="31">:Page</g>
    {
        
        public MyInventory()
        {
            InitializeComponent();
            MyInventoryLINQtoSQLClassDataContext dbContext = new MyInventoryLINQtoSQLClassDataContext();
            MyInventoryDatagrid.ItemsSource = dbContext.InventoryTables;
        }

        private void bindDatagrid()
        {
            SqlConnection con = new SqlConnection();
            con.ConnectionString = (@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=D:\IMS\IMS_Waqar\IMS_Waqar\ims_db.mdf;Integrated Security=True");
            con.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "select * from [InventoryTable]";
            cmd.Connection = con;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable("InventoryTable");
            da.Fill(dt);
            MyInventoryDatagrid.ItemsSource = dt.DefaultView;
            
        }
    }
}

Kindly help me through this. 
Or If You have a better option to store and retrieve the images in the database, Kindly share Your experience. Regards!

解决方案

>>But the main question is, how to manipulate image column in Datagridview before showing the data in DataGrid.

Hi Waqar,

According to your description, you mean  that you want to know how to load image in DataGrid? There are byte[] in database, you want to show image in DataGrid.

The IValueConverter interface you can use.

<Window.Resources>
        <local:BinaryImageConverter x:Key="BinaryToBitMapConverter" />
    </Window.Resources>
    <Grid>
        <Grid.Resources>
            <DataTemplate x:Key="DataTemplate">
                <Image Source="{Binding Path=ImageData, Converter={StaticResource BinaryToBitMapConverter}}"/>
            </DataTemplate>
        </Grid.Resources>

        <DataGrid Name="DG1" ItemsSource="{Binding}" AutoGenerateColumns="False" >
            <DataGrid.Columns>
                <DataGridTextColumn Header="First Name"  Binding="{Binding Path=FistName}"/>
                <DataGridTemplateColumn Header="Picture" CellTemplate="{StaticResource DateTemplate}"/>
            </DataGrid.Columns>
        </DataGrid>

    </Grid>

 public class BinaryImageConverter : IValueConverter
    {
        public object Convert(object value, Type targetType, object parameter, System.Globalization.CultureInfo culture)
        {
            if (value != null && value is byte[])
            {
                byte[] ByteArray = value as byte[];
                BitmapImage bmp = new BitmapImage();
                bmp.BeginInit();
                bmp.StreamSource = new MemoryStream(ByteArray);
                bmp.EndInit();
                return bmp;
            }
            return null;
        }
        public object ConvertBack(object value, Type targetType, object parameter, System.Globalization.CultureInfo culture)
        {
            throw new Exception("The method or operation is not implemented.");
        }
    }

Best Regards,

Cherry

这篇关于从SQL Server检索数据时创建DataGridView自定义列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！