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 T:Microsoft.SqlServer.Management.Smo.DataType object classifies the type of data so that it can be handled correctly by Microsoft SQL Server. The T:Microsoft.SqlServer.Management.Smo.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 T:Microsoft.SqlServer.Management.Smo.DataType object property:

The DataType property for objects that accept data can be set in several ways.

The T:Microsoft.SqlServer.Management.Smo.DataType object has several properties that define the type of data. For example, the T:Microsoft.SqlServer.Management.Smo.SqlDataType property specifies the SQL Server data type. The constant values that represent SQL Server data types are listed in the T:Microsoft.SqlServer.Management.Smo.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.

T:Microsoft.SqlServer.Management.Smo.UserDefinedDataType and T:Microsoft.SqlServer.Management.Smo.UserDefinedType data types refer to objects that contain the definition of the type of data defined by the user. The T:Microsoft.SqlServer.Management.Smo.UserDefinedDataType is based on SQL Server data types from the T:Microsoft.SqlServer.Management.Smo.SqlDataType enumeration. The T:Microsoft.SqlServer.Management.Smo.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 T:Microsoft.SqlServer.Management.Smo.SqlDataType enumeration contains a list of all the SQL Server-supported data types.

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.

This code example shows how to use the constructor to create instances of data types that are based on different SQL Server data types.

System_CAPS_ICON_note.jpg Note


The T:Microsoft.SqlServer.Management.Smo.UserDefinedType, T:Microsoft.SqlServer.Management.Smo.UserDefinedDataType, and XML types all require a name value to identify the object.

[!CODE [SMO How to#SMO_VBDataTypes1](SMO How to#SMO_VBDataTypes1)]

This code example shows how to use the constructor to create instances of data types that are based on different SQL Server data types.

System_CAPS_ICON_note.jpg Note


The T:Microsoft.SqlServer.Management.Smo.UserDefinedType, T:Microsoft.SqlServer.Management.Smo.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); 
}

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 T:Microsoft.SqlServer.Management.Smo.UserDefinedType, T:Microsoft.SqlServer.Management.Smo.UserDefinedDataType, and XML types all require a name value to identify the object.

[!CODE [SMO How to#SMO_VBDataTypes2](SMO How to#SMO_VBDataTypes2)]

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 T:Microsoft.SqlServer.Management.Smo.UserDefinedType, T:Microsoft.SqlServer.Management.Smo.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; 
}

Show: