将图像插入 SQL Server 的代码 [英] Code to Insert Image into SQL Server

问题描述

我想从我的 Windows 窗体应用程序将图像插入 SQL Server 数据库.

这个问题看起来像是在问我想知道什么，但它已关闭:

public static void InsertImage(int inventoryID, int businessID, FileInfo file, string sqlConnection){var list = new List();使用 (var stream = file.Open(FileMode.Open)){var data = new byte[stream.Length];stream.Read(data, 0, data.Length);list.AddRange(data);}var bmp = System.Drawing.Image.FromFile(file.FullName, true);使用 (var conn = new SqlConnection(sqlConnection)){conn.Open();var imageId = -1;var sqlSelect = "SELECT [ImageId] FROM [dbo].[ImageTable] WHERE [InventoryId]=@InventoryId;";使用 (var cmd = new SqlCommand(sqlSelect, conn)){cmd.Parameters.Add("@InventoryId", System.Data.SqlDbType.Int).Value = inventoryID;使用 (var r = cmd.ExecuteReader()){如果(r.Read()){var o = r["ImageId"];if ((o != null) && (o != DBNull.Value)){imageId = (int)o;}}}}如果 (imageId == -1){var sqlCmd = "插入 [dbo].[ImageTable] " +"([InventoryId], [ImageFileName], [ImageSize], [ImageWidth], [ImageHeight], [ImageBytes]) " +价值观" +"(@InventoryId, @ImageFileName, @ImageSize, @ImageWidth, @ImageHeight, @ImageBytes); ";使用 (var cmd = new SqlCommand(sqlCmd, conn)){cmd.Parameters.Add("@InventoryId", System.Data.SqlDbType.Int).Value = inventoryID;cmd.Parameters.Add("@ImageFileName", System.Data.SqlDbType.VarChar, 255).Value = file.Name;cmd.Parameters.Add("@ImageSize", System.Data.SqlDbType.Int).Value = list.Count;cmd.Parameters.Add("@ImageWidth", System.Data.SqlDbType.SmallInt).Value = bmp.Width;cmd.Parameters.Add("@ImageHeight", System.Data.SqlDbType.SmallInt).Value = bmp.Height;cmd.Parameters.Add("@ImageBytes", System.Data.SqlDbType.VarBinary, -1).Value = list.ToArray();cmd.ExecuteNonQuery();}}}}

为了运行/测试代码，我创建了这个辅助方法:

public static string[] GetImages(string fullFolderPath, string searchPattern){var list = new List();如果 (Directory.Exists(fullFolderPath)){如果 (String.IsNullOrEmpty(searchPattern)){searchPattern = "*.jpg";}var dir = new DirectoryInfo(fullFolderPath);var files = dir.GetFiles(searchPattern);for (int i = 0; i 

现在，从我的控制台应用程序运行它是一个简单的单一调用:

static void Main(string[] args){var list = GetImages(@"C:\inetpub\wwwroot\Ads", "*.jpg");}

I want to insert an image into the SQL Server database from my Windows Forms application.

This question looks like it was trying to ask what I wanted to find out, but it was closed:

Insert image into SQL Server

解决方案

Here is the code I used to do that.

Modify this code as needed for the table that you are going to use by viewing the design of your database in Microsoft Management Studio:

public static void InsertImage(int inventoryID, int businessID, FileInfo file, string sqlConnection)
{
    var list = new List<byte>();
    using (var stream = file.Open(FileMode.Open))
    {
        var data = new byte[stream.Length];
        stream.Read(data, 0, data.Length);
        list.AddRange(data);
    }
    var bmp = System.Drawing.Image.FromFile(file.FullName, true);
    using (var conn = new SqlConnection(sqlConnection))
    {
        conn.Open();
        var imageId = -1;
        var sqlSelect = "SELECT [ImageId] FROM [dbo].[ImageTable] WHERE [InventoryId]=@InventoryId;";
        using (var cmd = new SqlCommand(sqlSelect, conn))
        {
            cmd.Parameters.Add("@InventoryId", System.Data.SqlDbType.Int).Value = inventoryID;
            using (var r = cmd.ExecuteReader())
            {
                if (r.Read())
                {
                    var o = r["ImageId"];
                    if ((o != null) && (o != DBNull.Value))
                    {
                        imageId = (int)o;
                    }
                }
            }
        }
        if (imageId == -1)
        {
            var sqlCmd = "INSERT INTO [dbo].[ImageTable] " +
                "([InventoryId], [ImageFileName], [ImageSize], [ImageWidth], [ImageHeight], [ImageBytes]) " +
                "VALUES " +
                "(@InventoryId,  @ImageFileName,  @ImageSize,  @ImageWidth,  @ImageHeight,  @ImageBytes); ";
            using (var cmd = new SqlCommand(sqlCmd, conn))
            {
                cmd.Parameters.Add("@InventoryId", System.Data.SqlDbType.Int).Value = inventoryID;
                cmd.Parameters.Add("@ImageFileName", System.Data.SqlDbType.VarChar, 255).Value = file.Name;
                cmd.Parameters.Add("@ImageSize", System.Data.SqlDbType.Int).Value = list.Count;
                cmd.Parameters.Add("@ImageWidth", System.Data.SqlDbType.SmallInt).Value = bmp.Width;
                cmd.Parameters.Add("@ImageHeight", System.Data.SqlDbType.SmallInt).Value = bmp.Height;
                cmd.Parameters.Add("@ImageBytes", System.Data.SqlDbType.VarBinary, -1).Value = list.ToArray();
                cmd.ExecuteNonQuery();
            }
        }
    }
}

To run/test the code, I created this helper method:

public static string[] GetImages(string fullFolderPath, string searchPattern)
{
    var list = new List<String>();
    if (Directory.Exists(fullFolderPath))
    {
        if (String.IsNullOrEmpty(searchPattern))
        {
            searchPattern = "*.jpg";
        }
        var dir = new DirectoryInfo(fullFolderPath);
        var files = dir.GetFiles(searchPattern);
        for (int i = 0; i < files.Length; i++)
        {
            InsertImage(i + 1, 1, files[i], _sqlConnection);
            list.Add(files[i].FullName);
        }
    }
    return list.ToArray();
}

Now, running it from my Console Application is a simple, single call:

static void Main(string[] args)
{
    var list = GetImages(@"C:\inetpub\wwwroot\Ads", "*.jpg");
}

这篇关于将图像插入 SQL Server 的代码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！