1

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

Jbob Johan
  • 221
  • 1
  • 7
  • Yes it can be done. This is possibly a duplicate of http://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 https://code.msdn.microsoft.com/ObjectDataReader-8476dd72, – GarethD Nov 25 '15 at 12:16

1 Answers1

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);
        }
Aghilas Yakoub
  • 28,516
  • 5
  • 46
  • 51