Click here to Skip to main content
Click here to Skip to main content
Articles » Languages » C# » Samples » Downloads
 
Add your own
alternative version

C# and Table Value Parameters

, 20 Aug 2009 CPOL
How to send bulk data using table value parameters from C#
TableValueParameters.zip
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
Settings.settings
TableValueParameters.csproj.user
TableValueParameters.suo
/*
Deployment script for TableValueDatabase
*/

GO
SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON;

SET NUMERIC_ROUNDABORT OFF;


GO
:setvar DatabaseName "TableValueDatabase"
:setvar DefaultDataPath ""

GO
USE [master]

GO
:on error exit
GO
IF (DB_ID(N'$(DatabaseName)') IS NOT NULL
    AND DATABASEPROPERTYEX(N'$(DatabaseName)','Status') <> N'ONLINE')
BEGIN
    RAISERROR(N'The state of the target database, %s, is not set to ONLINE. To deploy to this database, its state must be set to ONLINE.', 16, 127,N'$(DatabaseName)') WITH NOWAIT
    RETURN
END

GO
IF (DB_ID(N'$(DatabaseName)') IS NOT NULL) 
BEGIN
    ALTER DATABASE [$(DatabaseName)]
    SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE [$(DatabaseName)];
END

GO
PRINT N'Creating $(DatabaseName)...'
GO
CREATE DATABASE [$(DatabaseName)]
    ON 
    PRIMARY(NAME = [TableValueDatabase], FILENAME = N'$(DefaultDataPath)TableValueDatabase.mdf')
    LOG ON (NAME = [TableValueDatabase_log], FILENAME = N'$(DefaultDataPath)TableValueDatabase_log.ldf')
GO
EXECUTE sp_dbcmptlevel [$(DatabaseName)], 100;


GO
IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        ALTER DATABASE [$(DatabaseName)]
            SET ANSI_NULLS ON,
                ANSI_PADDING ON,
                ANSI_WARNINGS ON,
                ARITHABORT ON,
                CONCAT_NULL_YIELDS_NULL ON,
                NUMERIC_ROUNDABORT OFF,
                QUOTED_IDENTIFIER ON,
                ANSI_NULL_DEFAULT ON,
                CURSOR_DEFAULT LOCAL,
                RECOVERY FULL,
                CURSOR_CLOSE_ON_COMMIT OFF,
                AUTO_CREATE_STATISTICS ON,
                AUTO_SHRINK OFF,
                AUTO_UPDATE_STATISTICS ON,
                RECURSIVE_TRIGGERS OFF 
            WITH ROLLBACK IMMEDIATE;
        ALTER DATABASE [$(DatabaseName)]
            SET AUTO_CLOSE OFF 
            WITH ROLLBACK IMMEDIATE;
    END


GO
IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        ALTER DATABASE [$(DatabaseName)]
            SET ALLOW_SNAPSHOT_ISOLATION OFF;
    END


GO
IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        ALTER DATABASE [$(DatabaseName)]
            SET READ_COMMITTED_SNAPSHOT OFF;
    END


GO
IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        ALTER DATABASE [$(DatabaseName)]
            SET AUTO_UPDATE_STATISTICS_ASYNC OFF,
                PAGE_VERIFY NONE,
                DATE_CORRELATION_OPTIMIZATION OFF,
                DISABLE_BROKER,
                PARAMETERIZATION SIMPLE,
                SUPPLEMENTAL_LOGGING OFF 
            WITH ROLLBACK IMMEDIATE;
    END


GO
IF IS_SRVROLEMEMBER(N'sysadmin') = 1
    BEGIN
        IF EXISTS (SELECT 1
                   FROM   [master].[dbo].[sysdatabases]
                   WHERE  [name] = N'$(DatabaseName)')
            BEGIN
                EXECUTE sp_executesql N'ALTER DATABASE [$(DatabaseName)]
    SET TRUSTWORTHY OFF,
        DB_CHAINING OFF 
    WITH ROLLBACK IMMEDIATE';
            END
    END
ELSE
    BEGIN
        PRINT N'The database settings for DB_CHAINING or TRUSTWORTHY cannot be modified. You must be a SysAdmin to apply these settings.';
    END


GO
IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        ALTER DATABASE [$(DatabaseName)]
            SET HONOR_BROKER_PRIORITY OFF 
            WITH ROLLBACK IMMEDIATE;
    END


GO
USE [$(DatabaseName)]

GO
IF fulltextserviceproperty(N'IsFulltextInstalled') = 1
    EXECUTE sp_fulltext_database 'enable';


GO

GO

GO
PRINT N'Creating dbo.SampleTable...';


GO
CREATE TABLE [dbo].[SampleTable] (
    [Id]           INT           IDENTITY (1, 1) NOT NULL,
    [SampleString] NVARCHAR (64) NOT NULL,
    [SampleInt]    INT           NULL
) ON [PRIMARY];


GO
PRINT N'Creating dbo.SampleDataType...';


GO
CREATE TYPE [dbo].[SampleDataType] AS  TABLE (
    [SampleString] NVARCHAR (64) NOT NULL,
    [SampleInt]    INT           NULL);


GO
PRINT N'Creating dbo.SampleProcedure...';


GO
CREATE PROCEDURE [dbo].[SampleProcedure]
@Sample [dbo].[SampleDataType] READONLY
AS
Begin
	Insert	Into	SampleTable(SampleString,SampleInt)
		Select SampleString, SampleInt	From	@Sample
End


GO

GO
-- Refactoring step to update target server with deployed transaction logs
CREATE TABLE  __RefactorLog (OperationKey UNIQUEIDENTIFIER NOT NULL PRIMARY KEY)
GO
sp_addextendedproperty N'microsoft_database_tools_support', N'refactoring log', N'schema', N'dbo', N'table', N'__RefactorLog'
GO

GO

GO
IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        DECLARE @VarDecimalSupported AS BIT;
        SELECT @VarDecimalSupported = 0;
        IF ((ServerProperty(N'EngineEdition') = 3)
            AND (((@@microsoftversion / power(2, 24) = 9)
                  AND (@@microsoftversion & 0xffff >= 3024))
                 OR ((@@microsoftversion / power(2, 24) = 10)
                     AND (@@microsoftversion & 0xffff >= 1600))))
            SELECT @VarDecimalSupported = 1;
        IF (@VarDecimalSupported > 0)
            BEGIN
                EXECUTE sp_db_vardecimal_storage_format N'$(DatabaseName)', 'ON';
            END
    END


GO
ALTER DATABASE [$(DatabaseName)]
    SET MULTI_USER 
    WITH ROLLBACK IMMEDIATE;


GO

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.

| Advertise | Privacy | Mobile
Web03 | 2.8.141015.1 | Last Updated 20 Aug 2009
Article Copyright 2009 by gaurav_verma_mca
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid