/// <summary>
/// Add a new column to the table
/// </summary>
/// <param name="tableName"> The name of the table </param>
/// <param name="column"> The information about the column </param>
public void AddColumn(string tableName, DBColumn column)
{
if (conn == null)
{
throw new Exception("Open a database first!");
}
// Gonna cause problems when a column is not null & doesn't have a default value or is unique
using (SQLiteCommand cmd = new SQLiteCommand("ALTER TABLE " + tableName + " ADD COLUMN " + column.SQLString(), conn))
cmd.ExecuteNonQuery();
}
/// <summary>
/// Get information about columns in a table
/// </summary>
public DBColumn[] GetColumns(string tableName)
{
if (conn == null)
{
throw new Exception("Open a database first!");
}
DBColumn[] columns = null;
int columnsCount = 0;
using (DataTable schema = conn.GetSchema("COLUMNS"))
{
DataRow[] rows = schema.Select("TABLE_NAME = '" + tableName + "'");
if (rows.Length > 0)
{
columnsCount = rows.Length;
columns = new DBColumn[columnsCount];
int pkIndx = schema.Columns["PRIMARY_KEY"].Ordinal,
aiIndx = schema.Columns["AUTOINCREMENT"].Ordinal,
uIndx = schema.Columns["UNIQUE"].Ordinal,
inIndx = schema.Columns["IS_NULLABLE"].Ordinal,
i = 0;
bool autoIncrement = false, unique = false, primaryKey = false, is_nullable = false;
object dataType;
string dataTypeStr;
for (; i < columnsCount; ++i)
{
columns[i] = new DBColumn()
{
name = rows[i]["COLUMN_NAME"].ToString()
};
#region TypeAssigning
dataType = rows[i]["DATA_TYPE"];
columns[i].columnTypeStr = dataType.ToString();
if (dataType == null)
{
columns[i].columnType = DBColumn.BasicTypes.TEXT;
}
else
{
dataTypeStr = dataType.ToString().ToLower();
if (dataTypeStr.Contains("int"))
{
// INTEGER -> sint, integer, tinyint, smallint, mediumint, bigint, unsigned big int, int2, int8, etc.
columns[i].columnType = DBColumn.BasicTypes.INTEGER;
}
else if (dataTypeStr.Contains("blob"))
{
// BLOB -> blob
columns[i].columnType = DBColumn.BasicTypes.BLOB;
}
else if (dataTypeStr == "real" || dataTypeStr.Contains("double") || dataTypeStr.Contains("float"))
{
// REAL -> real, double, double precision, float
columns[i].columnType = DBColumn.BasicTypes.REAL;
}
else if (dataTypeStr == "numeric" || dataTypeStr == "decimal" || dataTypeStr.Contains("bool") || dataTypeStr.Contains("date"))
{
// NUMERIC -> numeric, decimal, boolean, date, datetime
columns[i].columnType = DBColumn.BasicTypes.NUMERIC;
}
else
{
// TEXT -> character, varchar, varying character, nchar, native character, nvarchar, text, clob, etc.
columns[i].columnType = DBColumn.BasicTypes.TEXT;
}
}
#endregion
#region PropertiesAssignment
if (bool.TryParse(rows[i].ItemArray[aiIndx].ToString(), out autoIncrement))
{
columns[i].autoIncrement = autoIncrement;
}
else
{
columns[i].autoIncrement = false;
}
if (bool.TryParse(rows[i].ItemArray[uIndx].ToString(), out unique))
{
columns[i].unique = unique;
}
else
{
columns[i].unique = false;
}
if (bool.TryParse(rows[i].ItemArray[pkIndx].ToString(), out primaryKey))
{
columns[i].primaryKey = primaryKey;
}
else
{
columns[i].primaryKey = false;
}
if (bool.TryParse(rows[i].ItemArray[inIndx].ToString(), out is_nullable))
{
columns[i].notNull = !is_nullable;
}
else
{
columns[i].notNull = false;
}
#endregion
}
}
}
return(columns);
}
