public void SetColumnString(string tableName, string columnName, int length = 255,
IndexModes indexMode = IndexModes.WithoutChange, string defaultValue = null, bool required = false, bool emptyStrings = true)
{
if (!ColumnExists(tableName, columnName))
{
Execute("ALTER TABLE " + tableName + " ADD COLUMN [" + columnName + "] TEXT(" + length + ")");
Dispose();
}
ExecuteAdo("ALTER TABLE " + tableName + " ALTER COLUMN [" + columnName + "] TEXT(" + length + ") WITH COMPRESSION");
Dispose();
SetColumnIndex(tableName, columnName, indexMode);
ConnectDao();
var field = Database.TableDefs[tableName].Fields[columnName];
field.Required = required;
field.AllowZeroLength = emptyStrings;
field.DefaultValue = defaultValue ?? "";
}
public void SetColumnDateTime(string tableName, string columnName, IndexModes indexMode = IndexModes.WithoutChange,
DateTime?defaultValue = null, bool required = false)
{
if (!ColumnExists(tableName, columnName))
{
Execute("ALTER TABLE " + tableName + " ADD COLUMN [" + columnName + "] DATETIME");
}
else
{
ExecuteAdo("ALTER TABLE " + tableName + " ALTER COLUMN [" + columnName + "] DATETIME");
}
Dispose();
SetColumnIndex(tableName, columnName, indexMode);
ConnectDao();
var field = Database.TableDefs[tableName].Fields[columnName];
field.Required = required;
field.DefaultValue = defaultValue.HasValue ? defaultValue.ToString() : "";
}
public void SetColumnLong(string tableName, string columnName, IndexModes indexMode = IndexModes.WithoutChange,
long?defaultValue = null, bool required = false)
{
if (!ColumnExists(tableName, columnName))
{
Execute("ALTER TABLE " + tableName + " ADD COLUMN [" + columnName + "] LONG");
Dispose();
}
else
{
ExecuteAdo("ALTER TABLE " + tableName + " ALTER COLUMN [" + columnName + "] LONG");
}
SetColumnIndex(tableName, columnName, indexMode);
ConnectDao();
Database.TableDefs[tableName].Fields[columnName].Required = required;
string defValS = defaultValue.HasValue ? defaultValue.ToString() : "";
Database.TableDefs[tableName].Fields[columnName].DefaultValue = defValS;
}
//Добавление логического поля (если существует, просто навешивание комбобоксы на ячейки)
public void SetColumnBool(string tableName, string columnName, IndexModes indexMode = IndexModes.WithoutChange,
bool?defaultValue = null)
{
if (!ColumnExists(tableName, columnName))
{
Execute("ALTER TABLE " + tableName + " ADD COLUMN [" + columnName + "] YESNO");
Dispose();
}
else
{
ExecuteAdo("ALTER TABLE " + tableName + " ALTER COLUMN [" + columnName + "] YESNO");
Dispose();
}
ConnectDao();
Database.TableDefs[tableName].Fields[columnName].Properties.Append(
Database.TableDefs[tableName].Fields[columnName].CreateProperty("DisplayControl", 3, 106, false));
SetColumnIndex(tableName, columnName, indexMode);
ExecuteAdo("ALTER TABLE " + tableName + " ALTER COLUMN [" + columnName + "] SET DEFAULT " + defaultValue);
}
//Добавление индекса
public void SetColumnIndex(string tableName, string columnName, IndexModes indexMode = IndexModes.WithoutChange,
string oldIndexName = null)
{
//важно, что в случае indexMode = EmptyIndex columnName на самом деле - название индекса, а не поля,
//т.к. они могут и не совпадать
switch (indexMode)
{
case IndexModes.CommonIndex:
if (oldIndexName != null)
{
SetColumnIndex(tableName, oldIndexName, IndexModes.EmptyIndex);
}
if (!IndexExists(tableName, columnName))
{
ExecuteAdo("CREATE INDEX [" + columnName + "] ON " + tableName + " ([" + columnName + "])");
}
break;
case IndexModes.UniqueIndex:
if (oldIndexName != null)
{
SetColumnIndex(tableName, oldIndexName, IndexModes.EmptyIndex);
}
if (!IndexExists(tableName, columnName))
{
ExecuteAdo("CREATE UNIQUE INDEX [" + columnName + "] ON " + tableName + " ([" + columnName + "])");
}
break;
case IndexModes.EmptyIndex:
if (IndexExists(tableName, columnName))
{
ExecuteAdo("DROP INDEX [" + columnName + "] ON " + tableName);
}
break;
}
}
public Database(string connectionString, IndexModes mode)
: this(connectionString)
{
_im = mode;
}
//Добавление индекса по двум полям
public void SetColumnIndex(string tableName, string columnName, string columnName2, bool primary, IndexModes indexMode = IndexModes.WithoutChange, string oldIndexName = null)
{
switch (indexMode)
{
case IndexModes.CommonIndex:
if (oldIndexName != null)
{
SetColumnIndex(tableName, oldIndexName, IndexModes.EmptyIndex);
}
if (!IndexExists(tableName, columnName))
{
ExecuteAdo("CREATE INDEX [" + columnName + "] ON " + tableName + " ([" + columnName + "], [" + columnName2 + "])" + (primary ? " WITH PRIMARY" : ""));
}
break;
case IndexModes.UniqueIndex:
if (oldIndexName != null)
{
SetColumnIndex(tableName, oldIndexName, IndexModes.EmptyIndex);
}
if (!IndexExists(tableName, columnName))
{
ExecuteAdo("CREATE UNIQUE INDEX [" + (primary ? "PrimaryKey" : columnName) + "] ON " + tableName + " ([" + columnName + "], [" + columnName2 + "])" + (primary ? " WITH PRIMARY" : ""));
}
break;
}
}