Working with Data Types
Data comes in many types and sizes, such as a string that has a defined length, a number that has specific accuracy, or a user-defined data type that is another object that has its own set of rules. The DataType object classifies the type of data so that it can be handled correctly by Microsoft SQL Server. The DataType object is associated with objects that accept data. The following SQL Server Management Objects (SMO) objects accept data that must be defined by a DataType object property:
The DataType property for objects that accept data can be set in several ways.
Use the default constructor and specify DataType object properties explicitly
Use an overloaded constructor and specify the DataType properties as parameters.
Specify the DataType inline in the object constructor.
Use one of the static members of the DataType class, for example Int. This will in fact return an instance of a DataType object.
The DataType object has several properties that define the type of data. For example, the SqlDataType property specifies the SQL Server data type. The constant values that represent SQL Server data types are listed in the SqlDataType enumeration. This refers to data types such as varchar, nchar, currency, integer, float, and datetime.
When the data type is established, specific properties must be set for the data. For example, if it is an nchar type, the length of the string data must be set in the Length property. The same applies for numeric values, where you would have to specify precision and scale.
UserDefinedDataType and UserDefinedType data types refer to objects that contain the definition of the type of data defined by the user. The UserDefinedDataType is based on SQL Server data types from the SqlDataType enumeration. The UserDefinedType is based on Microsoft .NET data types. Typically, these would represent data of a specific type that is frequently reused by the database because of business rules defined by the organization. For example, a data type that stores an amount of money and a currency denominator would be helpful in a company that deals in multiple currencies.
The SqlDataType enumeration contains a list of all the SQL Server-supported data types.
Examples
To use any code example that is provided, you will have to choose the programming environment, the programming template, and the programming language in which to create your application. For more information, see "How to: Create a Visual Basic SMO Project in Visual Studio .NET" or "How to: Create a Visual C# SMO Project in Visual Studio .NET" in SQL Server Books Online.
Constructing a DataType Object with the Specification in the Constructor in Visual Basic
This code example shows how to use the constructor to create instances of data types that are based on different SQL Server data types.
Note
The UserDefinedType, UserDefinedDataType, and XML types all require a name value to identify the object.
'Declare a DataType object variable and define the data type in the constructor.
Dim dt As DataType
'For the decimal data type the following two arguements specify precision, and scale.
dt = New DataType(SqlDataType.Decimal, 10, 2)
Constructing a DataType Object with the Specification in the Constructor in Visual C#
This code example shows how to use the constructor to create instances of data types that are based on different SQL Server data types.
Note
The UserDefinedType, UserDefinedDataType, and XML types all require a name value to identify the object.
{
//Declare a DataType object variable and define the data type in the constructor.
DataType dt;
//For the decimal data type the following two arguements specify precision, and scale.
dt = new DataType(SqlDataType.Decimal, 10, 2);
}
Constructing a DataType Object by Using the Default Constructor in Visual Basic
This code example shows how to use the default constructor to create instances of data types that are based on different SQL Server data types. The properties are then used to specify the data type.
Note The UserDefinedType, UserDefinedDataType, and XML types all require a name value to identify the object.
'Declare and create a DataType object variable.
Dim dt As DataType
dt = New DataType
'Define the data type by setting the SqlDataType property.
dt.SqlDataType = SqlDataType.VarChar
'The VarChar data type requires a value for the MaximumLength property.
dt.MaximumLength = 100
Constructing a DataType Object by Using the Default Constructor in Visual C#
This code example shows how to use the default constructor to create instances of data types that are based on different SQL Server data types. The properties are then used to specify the data type.
Note The UserDefinedType, UserDefinedDataType, and XML types all require a name value to identify the object.
{
//Declare and create a DataType object variable.
DataType dt;
dt = new DataType();
//Define the data type by setting the SqlDataType property.
dt.SqlDataType = SqlDataType.VarChar;
//The VarChar data type requires a value for the MaximumLength property.
dt.MaximumLength = 100;
}