VistaDB 6
VistaDB
/
Developer's Guide
/
CLR Stored Procedures and Functions
/ Technical Details of CLR Procs and Functions
Collapse All
Expand All
In This Topic
VistaDB
Getting Started
Using VistaDB with Entity Framework
Getting Started
Creating A Database
Registering the Provider
Connection Strings
Using Connection Strings in Code
Deploying Your Application
Upgrading To Entity Framework 6
Using VistaDB with ADO.NET
Getting Started
Creating A Database
Common Operations (Shared ADO.NET code)
Common Operations (VistaDB Only)
Connection Strings
Using Connection Strings in Code
Deploying Your Application
Using VistaDB with Typed Datasets
Getting Started
Creating A Database
Deploying Your Application
Sample Projects included with VistaDB
Running NUnit Tests
DBA Sample Tool
Help Search FTS Sample
Ticket System Sample
Timesheet Website Sample
Migrating From Microsoft SQL Server
Creating A Database
ADO.NET Direct Code
NuGet Packages for VistaDB
Upgrading to VistaDB 6
Database Maintenance
What's New - Version 6.6
What's New - Version 6.5
What's New - Version 6.4
What's New - Version 6.3
What's New - Version 6.2
What's New - Version 6.1
What's New - Version 6.0
What's New - Version 5.8
What's New - Version 5.7
What's New - Version 5.6
What's New - Version 5.5
What's New - Version 5.2
What's New - Version 5.2.2
What's New - Version 5.1
What's New - Version 5.1.1
What's New - Version 5.1.2
What's New - Version 5.1.3
What's New - Version 5.1.4
What's New - Version 5.0
What's New - Version 5.0.2
What's New - Version 5.0.3
What's New - Version 5.0.4
What's New - Version 5.0.5
What's New - Version 5.0.6
What's New - Version 5.0.7
Why VistaDB?
Importance of Managed and Typesafe Code
Types of Applications and Platforms Supported
What about LINQ?
Scalable to SQL Server or SQL Express
Encryption
How does VistaDB work?
Components for the .NET Framework
Serverless Database Engine
VistaDB Zero Config
Data Engine and SQL Query Processor
Zero config deployment
Scalable to SQL Server or SQL Express
Direct Data Access Objects - DDA
Physical Database File Storage
Data Types and Mapping from SQL Server to VistaDB
Database limits based upon pagesize
Database rowsize limits
Pagesize performance implications
VDC6 Files
Tools User's Guide
Data Builder
Menu Commands
File
XML Import and Export
Database
Table
View
CLR Procs
Add Assembly
SQL Script
Generate Schema Deployment Script
Export Schema as Scripts
Tools
Options
Window
Help
Toolbar Commands
Main Content Pane
Context Sensitive Screens
Data File Information
SQL Query Pane
SQL Query Results
Keyboard Shortcuts
VSQL6 Files and SQL file management
Syntax Editor Advanced Options
LINQ Query Pane
LINQ Entity Model Tab
LINQ Script CRUD Examples
Tables Pane
Per Table Options
Views
Alter View
Relationships
Programmability
Assemblies
Stored Procedures
Add a new Stored Procedure Dialog
Modify an existing stored procedure
Functions
Create or alter User Defined Functions
Data Builder Redistribution
Data Migration Wizard
SQL CE Conversion
Access Conversion
Command Line
Visual Studio
Known Visual Studio Issues
Developer's Guide
ADO.NET Overview
Getting Started
Connection Strings
Using Connection Strings in Code
Common Operations using Any ADO.NET Provider
Common Operations using VistaDB Exclusively
ADO.NET Factory Objects in your app.config or web.config
Connected ADO.NET Objects
Disconnected ADO.NET Objects
SQL Reference
Optimizing SQL Performance in VistaDB
Operators
LEFT JOIN
DEFAULT column entries
ORDER BY
GROUP BY
Functions
String Functions
ASCII
LOWER
UNICODE
UPPER
CHAR
NCHAR
CHARINDEX
LEN
LTRIM
RTRIM
REVERSE
SPACE
LEFT
RIGHT
REPLACE
REPLICATE
STR
STUFF
SUBSTRING
PATINDEX
Mathematical Functions
ABS
ACOS
ASIN
ATAN
ATN2
CEILING
COS
COT
DEGREES
EXP
FLOOR
FRAC
INT
LOG
LOG10
MAXOF
MINOF
PI
POWER
RADIANS
RAND
ROUND
SIGN
SIN
SQRT
SQUARE
TAN
Date and Time Functions
DATE *deprecated*
DATEADD
DATEDIFF
DATENAME
DATEPART
DAY
GETDATE
GETUTCDATE
MONTH
YEAR
Remove TIME from a DATETIME
System Functions
CAST
ISNULL
NULLIF
CONVERT
CASE
CONTAINS
LASTIDENTITY
SCOPE_IDENTITY
LASTTIMESTAMP
COALESCE
NEWID
SP_RENAME
Aggregate Functions
AVG
COUNT
COUNT_BIG
MAX
MIN
SUM
STDEV
Special Functions
GETVIEWS
GETVIEWCOLUMNS
SP_UDF()
SP_STORED_PROCEDURES()
PRINT
Subscribing to PRINT output in your application
Control of Flow Statements
IF / ELSE
BEGIN / END
SET @VAR
DECLARE
SET OPTIMIZATION
CHECK VIEW
WHILE / BREAK / CONTINUE
TRY / CATCH
RAISERROR
System Variables
Temp Tables
Table Variable Example
Temp Table Example
Database Schema
VistaDBConnection GetSchema Examples
VistaDBDataReader GetSchemaTable example
CASE in a WHERE clause
Optimizing conditionals in where clauses
Reserved Keywords
Future Reserved Keywords
ODBC Reserved Keywords
Stored Procedures and User-Defined Functions
TSQL Proc Creation
Executing stored procedures
TSQL Proc Removal
Error handling in TSQL Procs
Tips for SQL Server migration
SQL User Defined Function Example
ALTER a FUNCTION Example
Differences from SQL Server
Unsupported SQL Server Features and Syntax
CLR Stored Procedures and Functions
Registering CLR Methods as SQL Functions
Loading CLR Assemblies into VistaDB
Calling CLR Procedures and Functions Overview
Calling CLR Code using ADO.NET Objects
Calling CLR Functions using SQL Script
Technical Details of CLR Procs and Functions
Database Connections in a CLR Procedure
CLR Methods and Attributes
CLR Aggregate Functions
Example CLR Function and CLR Procedure
Debugging CLR Procs and Functions
Logging
Using Loupe
Deploying VistaDB with your Application
Additional Steps for Entity Framework
Database Maintenance
App runs locally and fails on remote machine
Sample Projects included with VistaDB
Running NUnit Tests
DBA Sample Tool
Help Search FTS Sample
Ticket System Sample
Timesheet Website Sample
How To Perform Common Tasks
Determine your Database Engine Version
Build and Visualize Foreign Key References
Adding a Foreign Key
Foreign Key Integrity Actions
Foreign Key Integrity Operation Types
Foreign Key Indexes
SQL Server Foreign Key Differences
Pack a Database
Create a New Database in Code
SQL Create Database
DDA CreateDatabase
Backup and Restore a Database
Running VistaDB Unit Tests
NUnit overview
Running NUnit tests
Debugging with NUnit
Use the VistaDBCommandBuilder
Get the Database Schema using SQL
Handle Transactions in SQL
Number of open transactions
Work with Isolated Storage
Working with a database in isolated storage
Calculating free space in isolated storage
Update Datasets to use VistaDB 6
Use Connection Pooling
Clearing Connection Pools
Handle SQL and VistaDB Exceptions
Serialization of Exceptions
Load exception Data extended information
Check for a Bad Encryption Key (Password) at Load Time
Use VistaDB Providers for ASP.NET Websites
Sample web.config for membership providers
Forcing app database connections closed
Exporting Data out of VistaDB
Migrate from VistaDB to SQL Server
API Reference
VistaDB.6 Assembly
VistaDB.6.Entity.6 Assembly
VistaDB.6.Loupe Assembly
VistaDB.6.Web Assembly
Licensing
Runtime License
EULA - End User License Agreement
Company & Support
Getting Support
Technical Details of CLR Procs and Functions
In This Topic