Click here to Skip to main content
13,146,014 members (43,029 online)
Click here to Skip to main content
Articles » Languages » C# » Samples » Downloads

Stats

222.7K views
3.9K downloads
48 bookmarked
Posted 20 Aug 2009

C# and Table Value Parameters

, 20 Aug 2009
How to send bulk data using table value parameters from C#
TableValueParameters
TableValueDatabase
Properties
Database.sqlcmdvars
Database.sqldeployment
Database.sqlpermissions
Database.sqlsettings
Schema Objects
Stored Procedures
Tables
Types
sql
debug
Database.sqlcmdvars
Database.sqldeployment
Database.sqlsettings
Microsoft.SqlTypes.dbschema
TableValueDatabase.dbschema
TableValueDatabase.deploymanifest
release
TableValueDatabase.dbmdl
TableValueDatabase.dbproj
TableValueDatabase.dbproj.schemaview
TableValueDatabase.dbproj.user
TableValueParameters
bin
Debug
Database
SampleDB.mdf
SampleDB_log.ldf
TableValueParameters.exe
TableValueParameters.pdb
TableValueParameters.vshost.exe
TableValueParameters.vshost.exe.manifest
Release
Database
SampleDB.mdf
SampleDB_log.LDF
Properties
TableValueParameters.csproj.user
TableValueParameters.suo
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Configuration;
using System.Data.Common;
using System.Data;

namespace TableValueParameters
{
    public class Program
    {
        public static void Main(string[] args)
        {
            //To represent the table parameter in C# we need to either have a set of entities which are IEnumreable 
            //or a data reader or a Data table

            //in this example we create a data table with same name as the type we have in the DB
            DataTable dataTable = new DataTable("SampleDataType");
            //we create column names as per the type in DB
            dataTable.Columns.Add("SampleString", typeof(string));
            dataTable.Columns.Add("SampleInt", typeof(Int32));

            //and fill in some values
            dataTable.Rows.Add("99", 99);
            dataTable.Rows.Add("98", null);
            dataTable.Rows.Add("97", 99);

            SqlConnection sqlConnection = 
                new SqlConnection(
                    ConfigurationManager.ConnectionStrings["SampleDBConnectionString"].ConnectionString);
            SqlCommand command = sqlConnection.CreateCommand();
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.CommandText = "[dbo].[SampleProcedure]";
            
            SqlParameter parameter = new SqlParameter();
            //The parameter for the SP must be of SqlDbType.Structured
            parameter.ParameterName="@Sample";
            parameter.SqlDbType = System.Data.SqlDbType.Structured;
            parameter.Value = dataTable;

            command.Parameters.Add(parameter);

            sqlConnection.Open();
            int numberOfRowsUpdated = command.ExecuteNonQuery();
            Console.WriteLine("Number of rows updated = " + numberOfRowsUpdated);
            Console.ReadKey();
        }
    }

}

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

gaurav_verma_mca
Architect Imfinity
India India
Hi I have been working on enterprise applications for last six years.

You may also be interested in...

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.170915.1 | Last Updated 20 Aug 2009
Article Copyright 2009 by gaurav_verma_mca
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid