Bulk copy of SQL data from table on disk to an in-memory table

Question

I want to periodically copy large records from a SQL server into a in-memory table. I need to put these fields I read into my own objects, not into another SQL table or flat file storage. Is a SQLBulkCopy the way forward to do this?

You mean that you just wanna read some values from db and store them in a c# object ? You won't put them back in Sql Server ? — Raphaël Althaus, Sep 05 '13 at 05:08
nope. One problem is that the database is on another domain, so I have to invoke a stored procedure to trigger the data being returned to me — friartuck, Sep 05 '13 at 05:30
Well, then why SqlBulkCopy ? It's usefull to insert massive amount of data in an Sql Server db. The source for SqlBulkCopy may be a c# DataTable, for example, but the target will always be a Sql Server table. — Raphaël Althaus, Sep 05 '13 at 05:33

score 0 · Answer 1 · answered Sep 05 '13 at 05:57

Bulk copy is useful when you want to provide massive insertion into you tables, you can also play with consistency validation etc.. This is best SQLServer approach as it gave as ~10x performance.
Your requirement sounds like you need just one select, SELECT field1, field2, etc. FROM ....

Nothing more.

score 0 · Answer 2 · answered Sep 05 '13 at 19:59

0

Is a SQLBulkCopy the way forward to do this

No: SQLBulkCopy does not copy data to Memory but to a SQL Server table. You need a SqlDataReader

answered Sep 05 '13 at 19:59

Pleun

8,856
2
30
50

score 0 · Answer 3 · answered Sep 05 '13 at 20:07

create a class..

public class MemoryObject
{
    public Int64 Id {get; set;}  
    public string OtherProperty {get; set;}     
}

then, use a SQLDataReader...

public List<MemoryObject> GetMemoryObjects()
{
    using (SqlConnection connection = new SqlConnection("..."))
    {
        string sql = "query";

        SqlCommand command = new SqlCommand(sql.ToString(), connection);
        command.CommandTimeout = 3600;

        connection.Open();
        var memoryObjects = new List<MemoryObject>();

        using (var rdr = command.ExecuteReader())
            {
                    while (rdr.Read())
                    {
                        var memoryObject = new MemoryObject {
                            Id = rdr.GetInt64(0),
                            OtherProperty = rdr.GetString(1)
                        };
                        memoryObjects.Add(memoryObject);
                    }                   
            }
    }

    return memoryObjects;
}

Bulk copy of SQL data from table on disk to an in-memory table

3 Answers3