custom Object collection to SqlDataReader

Question

I am trying to avoid using large objects in my current project, while I wish to upload collections of data to populate an SQL Server Table

I am planning on SqlBulkCopy(alternative could also be Sproc with table value parameter but that's not the scope of my current question)

as the method accepts either a DataTable or SqlDataReader

I was wondering if I could do something like :

public struct tblCarOb
{
    public String Model;
    public Int32 Year;
}

as i prefer structs over class objects it could be a class to.

List<tblCarOb> tcoLst = new List<tblCarOb>(){ new tblCarObj(){ Model = "A", Year= 2010 }};
using (sqlConnection ...)
{
    use Reader to read form tcoLst or tblCarOb[]

}

so I could avoid using the more complex DataTable

question is could it be done somehow ?

Update

public struct tblCarOb
{
    public String Model;
    public Int32 Year;
}

the idea is simple getting any object created as code above
without using EntityFrameWork
in my case I do not need to drop /create SQL Server Table
in my case The C# table object I create has a corresponding table in SQL Server
I prefer not to use reflection as I do with DataTable
** adding another class to implement it would be ok but not a whole DLL with 100K lines as the idea is to minimize the footprint.

the intention was to minimize overhead and performance hit.

thanks in advance

Yes it can be done. This is possibly a duplicate of stackoverflow.com/q/2258310/1048425 but not sure enough to close the question. Another example of a wrapper class that exposes IEnumarable as IDataReader can be found here code.msdn.microsoft.com/ObjectDataReader-8476dd72, — GarethD
– GarethD, Commented Nov 25, 2015 at 12:16

Aghilas Yakoub · Accepted Answer · 2015-11-25 12:26:11Z

1

I suggest you this code

        using (IDataReader reader = tcoLst.GetDataReader())
        using (SqlConnection conn = new SqlConnection(....))
        using (SqlBulkCopy bcp = new SqlBulkCopy(conn))
        {
            conn.Open();

            //-->>>>>>>define this value
            bcp.DestinationTableName = "YourTableName";

            string createTableSql = string.Empty;

            createTableSql += string.Format("IF EXISTS(SELECT * FROM sys.tables t WHERE t.name =  {0}) DROP TABLE {0};", bcp.DestinationTableName);
            createTableSql += string.Format("CREATE TABLE dbo.{0};",bcp.DestinationTableName);

            for (int column = 0; column < reader.FieldCount; column++)
            {
                if (column > 0)
                {
                    createTableSql += ",";
                }

                createTableSql += "[" + reader.GetName(column) + "]" + " VARCHAR(MAX) NULL";
            }

            createTableSql += ");";

            using (SqlCommand createTable = new SqlCommand(createTableSql, conn))
            {
                createTable.ExecuteNonQuery();
            }

            bcp.WriteToServer(reader);
        }

edited Nov 25, 2015 at 12:26

answered Nov 25, 2015 at 12:18

Aghilas Yakoub

29k5 gold badges49 silver badges51 bronze badges

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

5 Comments

Jbob Johan Over a year ago

GetDataReader() ? list Extension I did not hear about ?

Jbob Johan Over a year ago

I found something in google : csvreader.com/posts/GenericListDataReader.cs found on csvreader.com/posts/generic_list_datareader.php

Jbob Johan Over a year ago

but I think it's using EntityFramework Objects and not my custom standard class object or struct object

Jbob Johan Over a year ago

I couldn't Implement your current code on any plain object List<T> does not recognize GetDataReader() how come you get your code to work ?

Avia Afer Over a year ago

@AghilasYakoub can you please explain your answer

Collectives™ on Stack Overflow

custom Object collection to SqlDataReader

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related