/// <summary> /// /// </summary> /// <param name="columnName"></param> /// <param name="dbtype"></param> /// <param name="size"></param> /// <returns></returns> public DataColumn CreateField(string columnName, DbType dbtype, object size) { DataColumn field = new DataColumn(); field.ColumnName = columnName; field.DataType = DbTypesConverter.DbTypeToType(dbtype); if (size != null) { field.MaxLength = int.Parse(size.ToString()); } return(field); }
/// <summary> /// /// </summary> /// <param name="tableDef"></param> private void AddIndexesToTableDef(TableDefHelper tableDef) { DataTable dbIndexes = _connection.GetSchema("Indexes"); foreach (DataRow r in dbIndexes.Select("TABLE_NAME = '" + tableDef.TableName + "'")) { DataColumn newCol = new DataColumn(); newCol.ColumnName = r["COLUMN_NAME"].ToString(); newCol.AllowDBNull = r["NULLS"].ToString() == "1"; newCol.DataType = DbTypesConverter.ProviderTypeToType(r["TYPE"].ToString()); } }
/// <summary> /// /// </summary> /// <param name="tableDef"></param> private void AddColumnsToTableDef(TableDefHelper tableDef) { DataTable dbColumns = _connection.GetSchema("Columns"); foreach (DataRow r in dbColumns.Select("TABLE_NAME = '" + tableDef.TableName + "'")) { /*System.Diagnostics.Debug.WriteLine("-------------"); * foreach (DataColumn c in r.Table.Columns) * { * System.Diagnostics.Debug.WriteLine("\t" + c.ColumnName + " " + r[c.ColumnName]); * }*/ DataColumn field = new DataColumn(); field.ColumnName = r["COLUMN_NAME"].ToString(); field.DataType = DbTypesConverter.ProviderTypeToType(r["DATA_TYPE"].ToString()); if (r["DATA_TYPE"].ToString() == "130") // strings { field.MaxLength = int.Parse(r["CHARACTER_MAXIMUM_LENGTH"].ToString()); } tableDef.Columns.Add(field); } }
internal void Add(TableDefHelper item, bool newTableToDB) { TableDefHelper tb = this[item.TableName]; if (tb == null) { base.Add(item); if (newTableToDB) { DbCommand command = _connection.CreateCommand(); StringBuilder strFields = new StringBuilder(); foreach (DataColumn field in item.Columns) { strFields.Append(string.Format("{0} {1}", field.ColumnName, DbTypesConverter.TypeToProviderType(field.DataType))); if (field.MaxLength > 0) { strFields.Append(string.Format("({0})", field.MaxLength)); } strFields.Append(","); } if (strFields.Length > 0) { strFields.Remove(strFields.Length - 1, 1); command.CommandText = string.Format("CREATE TABLE {0} ({1})", item.TableName, strFields); command.ExecuteNonQuery(); if (item.Indexes.Count > 0) { foreach (IndexHelper idx in item.Indexes) { strFields.Length = 0; strFields.Capacity = 0; foreach (DataColumn column in idx.Fields) { strFields.Append(string.Format("{0},", column.ColumnName)); } if (strFields.Length > 0) { strFields.Remove(strFields.Length - 1, 1); DbCommand idxcmd = _connection.CreateCommand(); // idx.Foreign is readonly in DAO, but is automatically set when the Field has the ForeignTable property assigned. if (idx.Primary) { idxcmd.CommandText = string.Format("ALTER TABLE {0} ADD PRIMARY KEY ({1})", item.TableName, strFields); } else if (idx.Unique) { idxcmd.CommandText = string.Format("CREATE UNIQUE INDEX {0} ON {1} ({2})", idx.Name, item.TableName, strFields); } else { idxcmd.CommandText = string.Format("CREATE INDEX {0} ON {1} ({2})", idx.Name, item.TableName, strFields); } if (idx.IgnoreNulls && !idx.Primary) { idxcmd.CommandText += " WITH DISALLOW NULL"; } try { idxcmd.ExecuteNonQuery(); } catch (Exception ex) { System.Diagnostics.Debug.WriteLine(ex.ToString()); } } } } } } } }