/// <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); }