c# insert multiple blobs into same table

Question

Can you do an insert statement with multiple file blob reads in the same command?

In the code below inputfile contains the following:

string[] inputfile = {"C:\\test_blob\\blob1.pdf","C:\\test_blob\\blob2.jpg"};

I'm uncertain if cmd.Parameters can be done prior to the cmd.CommandText or if I can do more than one File.ReadAllBytes() as a cmd.Parameter.

public static void insert_blob_file(string dbname, string uid, string pwd, string[] inputfile)
{
    using (var conn = new OdbcConnection("DSN=" + dbname + ";UID=" + uid + ";pwd=" + pwd))
    {
        conn.Open();
        using (var cmd = conn.CreateCommand())
        {
            for (int i = 0; i < inputfile.Count();i++)
            {
                var inputStream = new FileStream[i];
                using (inputStream[i] = File.OpenRead(inputfile[i]))
                {
                    cmd.Parameters.AddWithValue("blob" + i.ToString(), File.ReadAllBytes(inputfile[i]));
                }
                cmd.CommandText = "INSERT INTO MyTable (Id, MyBlobColumn,String1,MyBlobColum1,String2,String3) VALUES (1,@blob0,SomeString,@blob1,SomeString,SomeString)";
            }
                cmd.ExecuteNonQuery();
        }
        conn.Close();
    }
}

Christopher · Accepted Answer · 2018-04-27 11:39:40Z

2

First, wich of the 2/3 Strategies for storing blobs do you even use? Here is a nice listing of the 2 common and SQL Servers special approach to try to combine them: https://www.simple-talk.com/sql/learn-sql-server/an-introduction-to-sql-server-filestream/

Secondly building queries via string conaction is just going to expose you to SQL Injections. You really should be using the SQL Parameter Syntax instead. Aside from being safer, they might even be faster as the SQL servers does not need to imply types. You can explicitly tell it the types and proper mapping.

Thirdly, I asume you are calling a function like insert_blob_file in some form of multitasking. SQL Operations are network operations and those can take really long times, one way or the other.

As for the actuall problem: When inserting or updating large amounts of data, batching is rather important. You want to do enough at once to avoid overhead. But not so much, you end up locking up the table and thus possibly the whole DB for a very long time. Especially if the network connection to the client is not the fastest. I always advice to do bulk inserts on the DBMS side just to avoid this, but it seems unlikely you can do that here.

With blobs every insert should be a seperate job. Do not even try to do bulk blob inserts.

answered Apr 27, 2018 at 11:39

Christopher

9,8442 gold badges19 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user3308131 Over a year ago

Thanks for your advice, but if you have a table with more than one blob how can you insert the remaining blobs when the row already exists?

Christopher Over a year ago

@user3308131: If you have a table with multiple blob columns? You just use SQL Paramters and Update. But overall that is something you propably want to avoid. Resolving that 1:N relationship properly is something you should be doing with that case. Bulk data and long rows is bad for both network transfers and overall SQL Performance. If you need a bunch of blobs for each element, resolve that 1:N or even N:M relationship properly.

user3308131 Over a year ago

I really have no choice in this matter sense what I am doing is creating a new database from several oracle databases into one SQL server database. I'm with you in that BLOBs are bad and you should just store links to data instead of storing them in the database itself, however, as you probably already know, some things are not up for debate.

Christopher Over a year ago

@user3308131 Blobbs themself are not bad ro good. They are a nessesity. How you handle them, that can be good or bad. If you are making an new SQL Server DB, you might as well go the filestream route. At least ask if you can use it. And if you make a new DB, properly solving a inproperly resolved 1:N relationship (multiple columns of in one table rather then a seperate table). If you need the old structure for legacy code? That is what views are for. If anything can manipulate multiple blob columns in one insert or update, it has to be SQLParameters. They get around query lenght limitations.

Collectives™ on Stack Overflow

c# insert multiple blobs into same table

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related