Many of the samples in this product brief use the emp and dept tables. You can create the tables using an ISQL script, or by using the data provider.
The following script can be run in ISQL. See the Microsoft SQL Server documentation for details.
CREATE TABLE emp (
empno INT PRIMARY KEY,
ename VARCHAR(10),
job VARCHAR(9),
mgr INT NULL,
hiredate DATETIME,
sal NUMERIC(7,2),
comm NUMERIC(7,2) NULL,
dept INT)
begin
insert into emp values
(1,
'JOHNSON'
,
'ADMIN'
,6,
'12-17-1990'
,18000,NULL,4)
insert into emp values
(2,
'HARDING'
,
'MANAGER'
,9,
'02-02-1998'
,52000,300,3)
insert into emp values
(3,
'TAFT'
,
'SALES I'
,2,
'01-02-1996'
,25000,500,3)
insert into emp values
(4,
'HOOVER'
,
'SALES I'
,2,
'04-02-1990'
,27000,NULL,3)
insert into emp values
(5,
'LINCOLN'
,
'TECH'
,6,
'06-23-1994'
,22500,1400,4)
insert into emp values
(6,
'GARFIELD'
,
'MANAGER'
,9,
'05-01-1993'
,54000,NULL,4)
insert into emp values
(7,
'POLK'
,
'TECH'
,6,
'09-22-1997'
,25000,NULL,4)
insert into emp values
(8,
'GRANT'
,
'ENGINEER'
,10,
'03-30-1997'
,32000,NULL,2)
insert into emp values
(9,
'JACKSON'
,
'CEO'
,NULL,
'01-01-1990'
,75000,NULL,4)
insert into emp values
(10,
'FILLMORE'
,
'MANAGER'
,9,
'08-09-1994'
,56000,NULL,2)
insert into emp values
(11,
'ADAMS'
,
'ENGINEER'
,10,
'03-15-1996'
,34000,NULL,2)
insert into emp values
(12,
'WASHINGTON'
,
'ADMIN'
,6,
'04-16-1998'
,18000,NULL,4)
insert into emp values
(13,
'MONROE'
,
'ENGINEER'
,10,
'12-03-2000'
,30000,NULL,2)
insert into emp values
(14,
'ROOSEVELT'
,
'CPA'
,9,
'10-12-1995'
,35000,NULL,1)
end
CREATE TABLE dept (
deptno INT NOT NULL,
dname VARCHAR(14),
loc VARCHAR(13))
begin
insert into dept values (1,
'ACCOUNTING'
,
'ST LOUIS'
)
insert into dept values (2,
'RESEARCH'
,
'NEW YORK'
)
insert into dept values (3,
'SALES'
,
'ATLANTA'
)
insert into dept values (4,
'OPERATIONS'
,
'SEATTLE'
)
end
SQLServerConnection Conn;
Conn =
new
SQLServerConnection("host=nc-star;port=1433;
User ID=test01;Password=test01; Database Name=Test");
try
{
Conn.Open();
}
catch
(SQLServerException ex)
{
// Connection failed
Console.WriteLine(ex.Message);
return
;
}
string
[] DropTableSQL = {
"drop table emp"
,
"drop table dept"
};
for
(
int
x=0; x<=1; x++)
{
try
{
// Drop the tables, don't care if they don't exist
SQLServerCommand DBCmd =
new
SQLServerCommand(DropTableSQL[x], Conn);
DBCmd.ExecuteNonQuery();
}
catch
(SQLServerException ex)
{
}
// Create the tables
string
CreateEmpTableSQL = "CREATE TABLE emp
(empno INT PRIMARY KEY NOT NULL,"
+
"ename VARCHAR(10) NOT NULL,"
+
"job VARCHAR(9) NOT NULL,"
+
"mgr INT,"
+
"hiredate DATETIME NOT NULL,"
+
"sal NUMERIC(7,2) NOT NULL,"
+
"comm NUMERIC(7,2),"
+
"dept INT NOT NULL)"
;
string
CreateDeptTableSQL =
"CREATE TABLE dept ("
+
"deptno INT NOT NULL,"
+
"dname VARCHAR(14),"
+
"loc VARCHAR(13))"
;
try
{
SQLServerCommand DBCmd =
new
SQLServerCommand(CreateEmpTableSQL, Conn);
DBCmd.ExecuteNonQuery();
DBCmd.CommandText = CreateDeptTableSQL;
DBCmd.ExecuteNonQuery();
}
catch
(Exception ex)
{
//Create tables failed
Console.WriteLine (ex.Message);
return
;
}
// Now insert the records
string
[] InsertEmpRecordsSQL = {
"insert into emp values
(1,
'JOHNSON'
,
'ADMIN'
,6,
'12-17-1990'
,18000,NULL,4)",
"insert into emp values
(2,
'HARDING'
,
'MANAGER'
,9,
'02-02-1998'
,52000,300,3)",
"insert into emp values
(3,
'TAFT'
,
'SALES I'
,2,
'01-02-1996'
,25000,500,3)",
"insert into emp values
(4,
'HOOVER'
,
'SALES I'
,2,
'04-02-1990'
,27000,NULL,3)",
"insert into emp values
(5,
'LINCOLN'
,
'TECH'
,6,
'06-23-1994'
,22500,1400,4)",
"insert into emp values
(6,
'GARFIELD'
,
'MANAGER'
,9,
'05-01-1993'
,54000,NULL,4)",
"insert into emp values
(7,
'POLK'
,
'TECH'
,6,
'09-22-1997'
,25000,NULL,4)",
"insert into emp values
(8,
'GRANT'
,
'ENGINEER'
,10,
'03-30-1997'
,32000,NULL,2)",
"insert into emp values
(9,
'JACKSON'
,
'CEO'
,NULL,
'01-01-1990'
,75000,NULL,4)",
"insert into emp values
(10,
'FILLMORE'
,
'MANAGER'
,9,
'08-09-1994'
,56000, NULL,2)",
"insert into emp values
(11,
'ADAMS'
,
'ENGINEER'
,10,
'03-15-1996'
,34000, NULL,2)",
"insert into emp values
(12,
'WASHINGTON'
,
'ADMIN'
,6,
'04-16-1998'
,18000,NULL,4)",
"insert into emp values
(13,
'MONROE'
,
'ENGINEER'
,10,
'12-03-2000'
,30000,NULL,2)",
"insert into emp values
(14,
'ROOSEVELT'
,
'CPA'
,9,
'10-12-1995'
,35000,NULL,1)"};
string
[] InsertDeptRecordsSQL = {
"insert into dept values (1,'ACCOUNTING','ST LOUIS')"
,
"insert into dept values (2,'RESEARCH','NEW YORK')"
,
"insert into dept values (3,'SALES','ATLANTA')"
,
"insert into dept values (4, 'OPERATIONS','SEATTLE')"
};
// Insert dept table records first
for
(
int
x = 0; x<InsertDeptRecordsSQL.Length; x++)
{
try
{
SQLServerCommand DBCmd =
new
SQLServerCommand(InsertDeptRecordsSQL[x], Conn);
DBCmd.ExecuteNonQuery();
}
catch
(Exception ex)
{
Console.WriteLine (ex.Message);
return
;
}
}
// Now the emp table records
for
(
int
x = 0; x<InsertEmpRecordsSQL.Length; x++)
{
try
{
SQLServerCommand DBCmd =
new
SQLServerCommand(InsertEmpRecordsSQL[x], Conn);
DBCmd.ExecuteNonQuery();
}
catch
(Exception ex)
{
Console.WriteLine (ex.Message);
return
;
}
}
Console.WriteLine (
"Tables created Successfully!"
);
// Close the connection
Conn.Close();