Hi Alan,
I guess you would like to create the table dynamically? This feature currently only supported in the
SQLite Adapter
. The
SQLServer Adapter
and all other Database Adpater does not support this feature by default.
If you want to create the table dynamically you have to use the power of the
.NET Script Heater
and the little script below.
These script checks first whether the table exists. In case not the table are created based on the defined fied definition of the current WRITE adapter.
Attached you´ll find a little example.
Note: You can set the table name in the Designer below in the Adapter proberties.
.NET C# Script to create SQL tables dynamicallybool bFirst = true;
public object DoWork()
{
object o = null;
if (bFirst)
{
// only for the first row
bFirst = false;
// get the WRITE Adapter
DatabaseAdapter adapter = (DatabaseAdapter)AdapterWrite;
string sql = String.Format("select count(*) from {0}", adapter.SQL);
// check whether the table exists
try
{
o = adapter.Execute(sql, true);
// Table already exists, no more to do.
return null;
}
catch(Exception)
{
// nothing :-)
}
// create sql create statement
sql = String.Format("create table {0} (", adapter.SQL);
int len = 0;
int count = 0;
foreach(Field f in adapter.Fields)
{
DatabaseAdapterField field = (DatabaseAdapterField)f;
if (count > 0)
sql += ", ";
sql += "[" + field.Name + "] ";
switch(field.DataType)
{
case DataType.String:
len = field.Length;
if (len == 0)
len = 100; // default length
//SQL data type for strings
sql += "varchar(" + len.ToString() + ")";
break;
case DataType.Date:
case DataType.Time:
case DataType.DateTime:
//SQL data type for datetime
sql += "datetime";
break;
case DataType.Boolean:
//SQL data type for Bool: for MYSQL use boolean!
sql += "bit";
break;
case DataType.Integer:
//SQL data type for Int
sql += "Integer";
break;
case DataType.Double:
case DataType.Decimal:
case DataType.Currency:
//SQL data type for Double and Currency, also posible numeric(18,2) ...
sql += "Float";
break;
}
if (field.AutoIncrement)
sql += " identity(1,1)";
if (field.PrimaryKey)
sql += " not null";
count++;
}
sql += " );";
// for debug write the SQL statement to file
// File.WriteAllText("C:\\Temp\\sql.txt", sql);
// create table
adapter.Execute(sql);
}
return null;
}
Best wishes
Robert Stark
Did this answer your question? We would be grateful if you provide a brief comment as feedback. It may also help others who may have encountered a similar problem.