ADO Databases
|
|
ADO Databases |
|
Table Creation |
|
Introduction |
When studying data sets, we defined a database as one or more lists. A list in a database is called a table. The idea is that a table is an arrangement of the categories of information stored in a list and a table makes it easy to locate and manage the records of a list. To better explore lists, you should know how a table organizes its value.
A table is made of one or more categories divided as columns. Consider the following example of a list of teachers of a high school:
| Last Name | First Name | Main Subject | Alternate Subject |
| Pastore | Albert | Math | Physics |
| Andong | Gertrude | Chemistry | Static |
| Missiano | Helena | Physical Ed | |
| Jones | Celestine | Comp Sciences | Math |
Notice that the first names are grouped in a common category, so are the last names and so on. This makes it easy to locate a category and possibly a value.
|
Table Creation |
To create a table, you start an expression with CREATE TABLE followed by the name of the table:
CREATE TABLE Name;
The CREATE and TABLE keywords must be used to create a table. The Name factor specifies the name of the new table.
|
The Name of a Table |
After the CREATE TABLE expression, you must enter a name for the table. The name of a table can be very flexible. This flexibility can be overwhelming and confusing. To avoid these, there are suggestions and conventions we will apply when naming our tables:
Here is an example:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ADODB;
namespace Exercise10
{
public class Program
{
static int Main(string[] args)
{
string strStatement = "CREATE TABLE Persons...";
return 0;
}
}
}
After formulating the expression that creates the table, you can pass it to the Execute() method of a Connection variable. This would be done as follows:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ADODB;
namespace Exercise10
{
public class Program
{
static int Main(string[] args)
{
ConnectionClass conDatabase = new ConnectionClass();
try
{
object objAffected;
string strStatement = "CREATE TABLE Persons...";
string strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source='C:\\Programs\\Exercise1.mdb';";
conDatabase.Open(strConnection, "", "", 0);
conDatabase.Execute(strStatement, out objAffected, 0);
}
finally
{
conDatabase.Close();
}
return 0;
}
}
}
Besides the CREATE TABLE expression followed by a name, there are other issues related to creating a table. We will review more details in future lessons.
|
Table Maintenance |
|
The Tables Collection |
The tables of an ADO database are stored in a collection represented in the Connection class by the Tables property. To locate this collection, you can access the Tables property of the Catalog class of the ADOX namespace.
To remove a table from a database, create a DROP TABLE expression followed by the name of the table. The formula to use is:
DROP TABLE TableName;
Replace the TableName factor of our formula with the name of the table you want to delete. Here is an example:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ADODB;
namespace Exercise10
{
public class Program
{
static int Main(string[] args)
{
ConnectionClass conDatabase = new ConnectionClass();
try
{
object objAffected;
string strStatement = "DROP TABLE Persons";
string strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source='C:\\Programs\\Exercise1.mdb';";
conDatabase.Open(strConnection, "", "", 0);
conDatabase.Execute(strStatement, out objAffected, 0);
}
finally
{
conDatabase.Close();
}
return 0;
}
}
}
|
Columns Fundamentals |
|
Introduction |
We saw that a table was used to organize the values of a list by using categories of information. Here is an example:
| Last Name | First Name | Main Class | Years of Experience |
| Pastore | Albert | Math | 4 |
| Andong | Gertrude | Chemistry | 8 |
| Missiano | Helena | Physical Ed | 5 |
| Jones | Celestine | Comp Sciences | 10 |
With this type of arrangement, each column holds a particular category of information. A table must have at least one column. This means that, to create a table using the CREATE TABLE TableName formula, you must specify a (at least one) column.
|
Column Creation |
To create the columns of a table, on the right side of the name, type an opening and a closing parentheses. In the parentheses of the CREATE TABLE TableName() expression, the formula of creating a column is:
ColumnName DataType Options
Notice that there is only space that separates the sections of the formula. This formula is for creating one column. If you want the table to have more than one column, follow this formula as many times as possible but separate the sections with colons. This would be done as follows:
CREATE TABLE TableName(Column1 DataType1 Options1, Column2 DataType2 Options2, Column_n DataType_n Options_n)
In the next sections, we will review the factors of this formula. To create a table in ADO, you can pass the whole statement to the Execute() method of the Connection class.
|
Characteristics of a Column |
|
Introduction |
Like a table of a database, a column must have a name. As mentioned for a table, the name of a column is very flexible. Because of this, we will adopt the same types of naming conventions we reviewed for tables:
When creating the table, set the name of the column in the ColumnName placeholder of our formula. Here is an example:
CREATE TABLE Students(FullName, DataType Options)
Notice that the name of the column is not included in quotes.
|
The Types of Columns |
To exercise a good level of control over the values that can be entered or stored in a database, you can configure each column to allow some types of value and/or to exclude some other types. This is done by specifying an appropriate type of data for the column. To specify the data type of a column, pass the name of the data type as the second factor of the column.
Text-Based Columns: If the fields under a column would be used to hold any type of value, including regular text, such a column is treated as string-based. There are various data types you can apply to such a column. You can specify the data type as Char, Text, String or Varchar. Here are examples of three columns created with these types:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ADODB;
namespace Exercise10
{
public class Program
{
static int Main(string[] args)
{
ConnectionClass conDatabase = new ConnectionClass();
try
{
object objAffected;
string strStatement = "CREATE TABLE Customers(firstName char, " +
"MiddleName String, " +
"lastName varchar);";
string strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source='C:\\Programs\\Exercise1.mdb';";
conDatabase.Open(strConnection, "", "", 0);
conDatabase.Execute(strStatement, out objAffected, 0);
}
finally
{
conDatabase.Close();
}
return 0;
}
}
}
Each one of the char, string, or varchar data types would produce the same effect. A column with the string, the char, or the varchar data type allows any type of value made of any character up to 255 symbols. If you want the column to hold longer text, specify its data type as Memo, NOTE, or LONGTEXT. Such a column can hold any type of text, any combination of characters, and symbols, up to 64000 characters. Here are examples:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ADODB;
namespace Exercise10
{
public class Program
{
static int Main(string[] args)
{
ConnectionClass conDatabase = new ConnectionClass();
try
{
object objAffected;
string strStatement = "CREATE TABLE Employees(FullName String, " +
"JobDescription Text, " +
"Comments Memo, " +
"ShortTimeGoals Note, " +
"LongTimeGoals LongText);";
string strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source='C:\\Programs\\Exercise1.mdb';";
conDatabase.Open(strConnection, "", "", 0);
conDatabase.Execute(strStatement, out objAffected, 0);
}
finally
{
conDatabase.Close();
}
return 0;
}
}
}
Boolean Columns: If you want to create a column to hold only values as being true or being false, specify its data type as YESNO, BIT, or LOGICAL. Here are examples:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ADODB;
namespace Exercise10
{
public class Program
{
static int Main(string[] args)
{
ConnectionClass conDatabase = new ConnectionClass();
try
{
object objAffected;
string strStatement = "CREATE TABLE Contractors(" +
"FullName String, " +
"IsFullTime BIT, " +
"CanTeachVariousSubjects LOGICAL, " +
"IsMarried YESNO);";
string strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source='C:\\Programs\\Exercise1.mdb';";
conDatabase.Open(strConnection, "", "", 0);
conDatabase.Execute(strStatement, out objAffected, 0);
}
finally
{
conDatabase.Close();
}
return 0;
}
}
}
Byte and Integer1: If you want a column to hold natural numbers, you can specify its data type as Byte or Integer1. This is suited for a column that will hold small numeric values not to exceed 255. Here are examples:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ADODB;
namespace Exercise10
{
public class Program
{
static int Main(string[] args)
{
ConnectionClass conDatabase = new ConnectionClass();
try
{
object objAffected;
string strStatement = "CREATE TABLE EmploymentStatus(" +
"StatusID Byte, " +
"Category Integer1);";
string strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source='C:\\Programs\\Exercise1.mdb';";
conDatabase.Open(strConnection, "", "", 0);
conDatabase.Execute(strStatement, out objAffected, 0);
}
finally
{
conDatabase.Close();
}
return 0;
}
}
}
Short and Integer2: If you want the column to hold larger numbers that can exceed 255, specify its data type as SHORT or INTEGER2.
Long: If the column will hold small to very large numbers, specify its data type as INT, INTEGER, INTEGER4 or Long.
Floating-Point Value With Single Precision: If you want to create a column that will hold regular decimal values without regards to precision on its value, specify its data type as Single.
Floating-Point Value With Double Precision: If the values of a column will require a good level of precision, specify its data type as either Double, or Numeric. Here is an example:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ADODB;
namespace Exercise10
{
public class Program
{
static int Main(string[] args)
{
ConnectionClass conDatabase = new ConnectionClass();
try
{
object objAffected;
string strStatement = "CREATE TABLE Students(" +
"FullName varchar, " +
"Height Single, " +
"Weight Numeric, " +
"GPA Double);";
string strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source='C:\\Programs\\Exercise1.mdb';";
conDatabase.Open(strConnection, "", "", 0);
conDatabase.Execute(strStatement, out objAffected, 0);
}
finally
{
conDatabase.Close();
}
return 0;
}
}
}
Money and Currency Columns: If you want the values of a column to hold monetary values, specify its data type as either Money or Currency. Here is an example:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ADODB;
namespace Exercise10
{
public class Program
{
static int Main(string[] args)
{
ConnectionClass conDatabase = new ConnectionClass();
try
{
object objAffected;
string strStatement = "CREATE TABLE Customers(" +
"FullName Text, " +
"WeeklyHours Double, " +
"HourlySalary Money, " +
"WeeklySalary Currency);";
string strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source='C:\\Programs\\Exercise1.mdb';";
conDatabase.Open(strConnection, "", "", 0);
conDatabase.Execute(strStatement, out objAffected, 0);
Console.WriteLine("A table named Customers has been added to the database");
}
finally
{
conDatabase.Close();
}
return 0;
}
}
}
Both Money and Currency have the same effect.
Date and Time: If you are creating a column whose values would consist of date, time, or both date and time, specify its data type as DATE or DATETIME. Here are examples:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ADODB;
namespace Exercise10
{
public class Program
{
static int Main(string[] args)
{
ConnectionClass conDatabase = new ConnectionClass();
try
{
object objAffected;
string strStatement = "CREATE TABLE Teachers(" +
"FullName Text, " +
"DateHired Date, " +
"DateLastReviewed DateTime);";
string strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source='C:\\Programs\\Exercise1.mdb';";
conDatabase.Open(strConnection, "", "", 0);
conDatabase.Execute(strStatement, out objAffected, 0);
}
finally
{
conDatabase.Close();
}
return 0;
}
}
}
Both data types have the same effect in Microsoft Access.
Binary: The binary data type can let a column accept any type of data but it is equipped to interpret the value. For example, it can be used to receive hexadecimal numbers. To specify this when creating a column, set its data type to either BINARY or VARBINARY.
Image: If you are creating a column that will hold external documents, such as pictures, formatted (from Microsoft Word for example), or spreadsheet, etc, specify its data type to one of the following: IMAGE, OLEOBJECT, LONGBINARY, or GENERAL.
|
Column Maintenance |
|
Introduction |
Column maintenance consists of adding a new column or deleting an existing column. Because the columns belong to a table, their maintenance is related to it. To perform this maintenance, you start with the ALTER TABLE expression followed by the name of the table.
|
Adding a New Column |
After a table with one or more columns has been created, you can add a new column to it. To add a new column, after the ALTER TABLE statement and the name of the table, include an ADD COLUMN expression using the following formula:
ALTER TABLE TableName ADD COLUMN ColumnName DataType
The ColumnName factor must be a valid name for the new column and you must follow the rules for naming columns. The data type must be one of those we reviewed. Here is an example that adds a new string-based column named CellPhone to a table named Employees:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ADODB;
namespace Exercise10
{
public class Program
{
static int Main(string[] args)
{
ConnectionClass conDatabase = new ConnectionClass();
try
{
object objAffected;
string strStatement = "ALTER TABLE Employees ADD COLUMN CellPhone string;";
string strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source='C:\\Programs\\Exercise1.mdb';";
conDatabase.Open(strConnection, "", "", 0);
conDatabase.Execute(strStatement, out objAffected, 0);
Console.WriteLine("A new column named CellPhone has been added " +
"to the Employees table.");
}
finally
{
conDatabase.Close();
}
return 0;
}
}
}
It is important to understand that the ADD COLUMN expression creates a new column at the end of the existing column(s). It cannot be used to insert a column in a table.
|
Deleting a Column |
To delete a column, start with the ALTER TABLE expression followed by the name of the table. After the ALTER TABLE TableName expression, follow it with a DROP COLUMN expression using this formula:
ALTER TABLE TableName DROP COLUMN ColumnName;
Replace the ColumnName factor of our formula with the name of the undesired column. Here is an example:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ADODB;
namespace Exercise10
{
public class Program
{
static int Main(string[] args)
{
ConnectionClass conDatabase = new ConnectionClass();
try
{
object objAffected;
string strStatement = "ALTER TABLE Employees DROP COLUMN Comments;";
string strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source='C:\\Programs\\Exercise1.mdb';";
conDatabase.Open(strConnection, "", "", 0);
conDatabase.Execute(strStatement, out objAffected, 0);
}
finally
{
conDatabase.Close();
}
return 0;
}
}
}
|
Data Entry Fundamentals |
|
Introduction |
So far, we have learned how to create a database and how to create a table. Here are the database and the table we will use:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ADOX;
using ADODB;
namespace Exercise10
{
public class Program
{
static int Main(string[] args)
{
string strSQL;
object obj = new object();
Catalog catPeople = new Catalog();
Connection conPeople = new Connection();
try
{
catPeople.Create("Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source='C:\\Programs\\Exercise.accdb';");
Console.WriteLine("A new Microsoft JET database named " +
"People.mdb has been created");
conPeople.Open("Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source='C:\\Programs\\Exercise.accdb'",
"", "", 0);
strSQL = "CREATE TABLE Persons(firstName Text(20), " +
"lastName Text(20), " +
"Gender Text(20));";
conPeople.Execute(strSQL, out obj, 0);
Console.WriteLine("A table named Persons has been " +
"created in the Exercise.accdb database");
}
finally
{
conPeople.Close();
}
return 0;
}
}
}
This creates a Microsoft JET database named People, followed by a table named Persons equipped with three columns.
After creating a table and its column(s), you can populate the database with data. Data entry consists of filling a table with the necessary values. A series of values that corresponds to same levels of columns is called a row or a record.
|
New Record Creation |
Before performing data entry on a table, you must know how the table is structured, the sequence of its columns, the type of data that each column is made of. To enter data in a table, you start with the INSERT combined with the VALUES keywords. The statement uses the following syntax:
INSERT TableName VALUES(Column1, Column2, Column_n)
Alternatively, or to be more precise, you can specify that you are entering data in the table using the INTO keyword between the INSERT keyword and the TableName factor. This is done with the following syntax:
INSERT INTO TableName VALUES(Column1, Column2, Column_n)
The TableName factor must be a valid name of an existing table in the currently selected database. If the name is wrong, the SQL interpreter would simply consider that the table you are referring to doesn't exist. Consequently, you would receive an error.
The VALUES keyword indicates that you are ready to list the values of the columns. The values of the columns must be included in parentheses. Specify the value of each column in the parentheses that follow the VALUES keyword:
Boolean Values: If the column is Boolean-based, you must specify its value as 0 or 1.
Numeric Values: If the column is a numeric type and if the number is an integer, you should provide a valid natural number without the decimal separator. If the column is for a decimal number, you can type the value with its character separator (the period for US English).
Character and String Values: If the data type of a column is a string type, you should include its value between double-quotes. For example, a shelf number can be specified as 'HHR-604' and a middle initial can be given as 'D'.
Date and Time Values: If the column was created for a date or a time data type, you should/must use an appropriate formula with the year represented by 2 or 4 digits. You should also include the date in single-quotes. If you want to specify the year with 2 digits, use the formula:
'yy-mm-dd'
Or
'yy/mm/dd'
You can use the dash symbol "-" or the forward slash "/" as the date separator. An alternative to representing a year is with 4 digits. In this case, you would use the formulas:
'yyyy-mm-dd'
Or
'yyyy/mm/dd'
The year with 4 digits is more precise as it properly expresses a complete year.
A month from January to September can be represented as 1, 2, 3, 4, 5, 6, 7, 8, or 9. Day numbers follow the same logic.
|
Adjacent Data entry |
The most common technique of performing data entry requires that you know the sequence of columns of the table in which you want to enter data. With this subsequent list in mind, enter the value of each field in its correct position.
During data entry on adjacent fields, if you don't have a value for a numeric field, you should type 0 as its value. For a string field whose data you don't have and cannot provide, type two single-quotes to specify an empty field. Here is an example:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ADODB;
namespace Exercise10
{
public class Program
{
static int Main(string[] args)
{
object obj = new object();
Connection conPeople = new Connection();
try
{
conPeople.Open("Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source='C:\\Programs\\Exercise.accdb'", "", "", 0);
conPeople.Execute("INSERT INTO Persons(firstName, lastName, Gender) " +
"VALUES('James', 'Carlton', 'Male');", out obj, 0);
Console.WriteLine("A new record has been created in the Persons table");
}
finally
{
conPeople.Close();
}
return 0;
}
}
}
|
Random Data Entry |
The adjacent data entry requires that you know the position of each column. The SQL provides an alternative that allows you to perform data entry using the name of a column instead of its position. This allows you to provide the values of fields in any order of your choice.
To perform data entry at random, you must provide a list of the columns of the table in the order of your choice. You can either use all columns or provide a list of the same columns but in your own order. Here is an example:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ADODB;
public class Program
{
static int Main()
{
string strSQL;
object obj = new object();
ADOX.Catalog catPeople = new ADOX.Catalog();
Connection conPeople = new Connection();
try
{
conPeople.Open("Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source='C:\\Programs\\Exercise.accdb'", "", "", 0);
conPeople.Execute("INSERT INTO Persons(lastName, Gender, firstName) " +
"VALUES('Germain', 'Male', 'Ndongo');", out obj, 0);
Console.WriteLine("A new record has been created in the Persons table");
}
finally
{
conPeople.Close();
}
return 0;
}
}
You don't have to provide data for all columns, just those you want, in the order you want. To do this, enter the names of the desired columns on the right side of the name of the table, in parentheses. The syntax used would be:
INSERT TableName(ColumnName1, Columnname2, ColumnName_n) VALUES(ValueFormColumnName1, ValueFormColumnName2, ValueFormColumnName_n);
|
Data Entry Assistance |
|
The Nullity of a Field |
When performing data entry, you can expect the user to skip any column whose value is not available and move to the next. In some cases, you may require that the value of a column be specified before the user can move on. If you are creating a column and if you want to let the user add or not add a value for the column, type the NULL keyword on the right side of the data type. If you want to require a value for the column, type NOT NULL. Here are examples:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ADODB;
public class Program
{
static int Main()
{
ADOX.Catalog catADOX = new ADOX.Catalog();
catADOX.Create("Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source='C:\\Programs\\VideoCollection.accdb';");
Console.WriteLine("A new Microsoft JET database named VideoCollection.accdb " +
"has been created");
Connection conVideos = new Connection();
object obj = new object();
conVideos.Open("Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source='C:\\Programs\\VideoCollection.accdb'",
"", "", 0);
conVideos.Execute("CREATE TABLE Videos(" +
"VideoTitle STRING NOT NULL, " +
"Director STRING NULL, " +
"YearReleased SHORT, " +
"Rating BYTE NULL);",
out obj, 0);
Console.WriteLine("A new table named Videos has been created");
conVideos.Close();
return 0;
}
}
In this case, when performing data entry, the user must always provide a value for the VideoTitle column in order to create a record. If you omit to specify the nullity of a field, it is assumed NULL; that's the case for the YearReleased column of the above Videos table.
When we study relationships, we will see that, on a table, each record should be uniquely identified. This should be the case even if many records seem to have the same values for each column. We saw already that you can require that the user provide a value for each record of a certain column. In some cases, the user may not have the right value for a column but at the time, the record would need to be created, even if it is temporary. To solve this type of problem and many others, you can create a column that provides its own value. On the other hand, to create a special column that can be used to uniquely identify each record, you can apply an integer data type to it but ask the database engine to automatically provide a numeric value for the column.
If you are creating the column, you can specify its data type as either COUNTER or AUTOINCREMENT. Only one column of a table can have one of these data types. Here is an example:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ADODB;
public static class Program
{
static int Main()
{
Connection conVideos = new Connection();
object obj = new object();
conVideos.Open("Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source='C:\\Programs\\VideoCollection.accdb'",
"", "", 0);
conVideos.Execute("CREATE TABLE Videos(" +
"ShelfNumber COUNTER, " +
"VideoTitle STRING NOT NULL, " +
"Director STRING NULL, " +
"YearReleased SHORT, " +
"Rating BYTE NULL);",
out obj, 0);
Console.WriteLine("A new table named Videos has been created");
conVideos.Close();
return 0;
}
}
By default, when you apply the COUNTER or the AUTOINCREMENT data type, when the user creates the first record, the field int the auto-incrementing column receives a number of 1. If the user creates a second record, the auto-incrementing value receives a number of 2, and so on. If you want, you can make the first record receive a number other than 1. You can also make it increment to a value other than 1. To apply this feature, the COUNTER and the AUTOINCREMENT types use a seed as their parentheses:
COUNTER(x,y)
or
AUTOINCREMENT(x,y)
The x value represents the starting value of the records. The y value specifies how much would be added to a value to get the next.
|
Fields Sizes |
When reviewing the data types available for columns, we saw that some of them could use a string-based data type, namely TEXT, CHAR, or VARCHAR. By default, if you create a table and you set a column's data type to TEXT, CHAR, or VARCHAR, it is made to hold 255 characters. If you want, you can control the maximum number of characters that would be allowed in a column during data entry.
To specify the number of characters of the string-based column, add the parentheses to the TEXT, the CHAR, or the VARCHAR data types, and in the parentheses, enter the desired number. Here are examples:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ADODB;
public static class Program
{
static int Main()
{
Connection conVideos = new Connection();
object obj = new object();
conVideos.Open("Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source='C:\\Programs\\VideoCollection.accdb'",
"", "", 0);
conVideos.Execute("CREATE TABLE Videos(" +
"ShelfNumber COUNTER, " +
"VideoTitle STRING(120) NOT NULL, " +
"Director VARCHAR(80) NULL, " +
"YearReleased SHORT, " +
"Rating TEXT(20) NULL);",
out obj, 0);
Console.WriteLine("A new table named Videos has been created");
conVideos.Close();
return 0;
}
}
|
Default Values |
A default value allows a column to use a value that is supposed to be common to most cells of a particular column. The default value can be set as a constant value or it can use a function that would adapt to the time the value is needed.
To specify a default value, after the name and the data type of a column, type DEFAULT and assign it the desired value, based on the data type. Here is an example:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ADODB;
public static class Program
{
static int Main()
{
Connection conVideos = new Connection();
object obj = new object();
conVideos.Open("Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source='C:\\Programs\\VideoCollection.accdb'",
"", "", 0);
conVideos.Execute("CREATE TABLE Videos(" +
"ShelfNumber COUNTER, " +
"VideoTitle STRING(120) NOT NULL, " +
"Director VARCHAR(80) NULL, " +
"YearReleased SHORT, " +
"Rating TEXT(20) NULL Default='PG-13')",
out obj, 0);
Console.WriteLine("A new table named Videos has been created");
conVideos.Close();
return 0;
}
}
|
|
||
| Previous | Copyright © 2008-2016, FunctionX, Inc. | Home |
|
|
||