private void bttnSaveTable_Click(object sender, EventArgs e)
{
if (txtTables.Text == "")
{
}
else
{
myDB = dbe.OpenDatabase(svflDialog.FileName);
myTB = myDB.CreateTableDef(txtTables.Text.ToString());
myFL = myTB.CreateField("Ref" + txtTables.Text, DAO.DataTypeEnum.dbLong);
myFL.Attributes = (int)DAO.FieldAttributeEnum.dbAutoIncrField;
myTB.Fields.Append(myFL);
Index myInd = myTB.CreateIndex("primaryKey");
myFL = myInd.CreateField("Ref" + txtTables.Text);
((IndexFields)(myInd.Fields)).Append(myFL);
myInd.Primary = true;
myTB.Indexes.Append(myInd);
myDB.TableDefs.Append(myTB);
MessageBox.Show(myTB.Name + " was created with success.", "Important Message", MessageBoxButtons.OK, MessageBoxIcon.Information);
this.Height = 280;
txtTables.Clear();
txtTables.Enabled = false;
bttnSaveTable.Enabled = false;
txtfields.Focus();
txtfields.Clear();
}
}
private void metroButtonCreateTable_Click(object sender, EventArgs e)
{
myDB = dbe.OpenDatabase(saveFileDialog1.FileName);
if (metroTextBoxTableName.Text == "")
{
MessageBox.Show("You must give a name to your Table", "Important Message", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
myTB = myDB.CreateTableDef(metroTextBoxTableName.Text);
myFL = myTB.CreateField("Ref" + metroTextBoxTableName.Text, DAO.DataTypeEnum.dbLong);
myFL.Attributes = (int)DAO.FieldAttributeEnum.dbAutoIncrField;
myTB.Fields.Append(myFL);
myInd = myTB.CreateIndex("primaryKey");
myFL = myInd.CreateField("Ref" + metroTextBoxTableName.Text);
((IndexFields)(myInd.Fields)).Append(myFL);
myInd.Primary = true;
myTB.Indexes.Append(myInd);
int n = metroGrid1.Rows.Add();
metroGrid1.Rows[n].Cells[0].Value = "Ref" + metroTextBoxTableName.Text;
metroGrid1.Rows[n].Cells[1].Value = "AutoNumber";
metroButtonCreateTable.Enabled = false;
metroButtonRelation.Enabled = false;
}
}
private void CreateField(MetroTextBox metroTextBoxFieldName, ComboBox metroComboBoxDataType)
{
string typedata = metroComboBoxDataType.Text;
switch (typedata)
{
case "Short Text":
{
myFL = myTB.CreateField(metroTextBoxFieldName.Text, DAO.DataTypeEnum.dbText);
}
break;
case "Long Text":
{
myFL = myTB.CreateField(metroTextBoxFieldName.Text, DAO.DataTypeEnum.dbText);
}
break;
case "Number":
{
myFL = myTB.CreateField(metroTextBoxFieldName.Text, DAO.DataTypeEnum.dbInteger);
}
break;
case "Large Number":
{
myFL = myTB.CreateField(metroTextBoxFieldName.Text, DAO.DataTypeEnum.dbLong);
}
break;
case "Date/Time":
{
myFL = myTB.CreateField(metroTextBoxFieldName.Text, DAO.DataTypeEnum.dbDate);
}
break;
case "Currency":
{
myFL = myTB.CreateField(metroTextBoxFieldName.Text, DAO.DataTypeEnum.dbCurrency);
}
break;
case "AutoNumber":
{
myFL = myTB.CreateField(metroTextBoxFieldName.Text, DAO.DataTypeEnum.dbInteger);
myFL.Attributes = Convert.ToInt32(DAO.FieldAttributeEnum.dbAutoIncrField);
}
break;
case "Yes/No":
{
myFL = myTB.CreateField(metroTextBoxFieldName.Text, DAO.DataTypeEnum.dbBoolean);
}
break;
}
}
private TableDef CreateFields()
{
Field MyFL;
if (CHKPrimaryKey.Checked && !CHKAutoIncrement.Checked)
{
MyFL = MyTb.CreateField(txtFieldsName.Text, CBOType.SelectedItem, txtLength.Text);
MyTb.Fields.Append(MyFL);
Index MyInd = MyTb.CreateIndex("PK" + txtFieldsName.Text);
MyFL = MyInd.CreateField(txtFieldsName.Text);
((IndexFields)(MyInd.Fields)).Append(MyFL);
MyInd.Primary = true;
MyTb.Indexes.Append(MyInd);
MessageBox.Show("The field & the index have been created !", "Successful Creation", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else if (CHKPrimaryKey.Checked && CHKAutoIncrement.Checked)
{
MyFL = MyTb.CreateField(txtFieldsName.Text, FieldAttributeEnum.dbAutoIncrField);
MyTb.Fields.Append(MyFL);
Index MyInd = MyTb.CreateIndex("PK" + txtFieldsName.Text);
MyFL = MyInd.CreateField(txtFieldsName.Text);
((IndexFields)(MyInd.Fields)).Append(MyFL);
MyInd.Primary = true;
MyTb.Indexes.Append(MyInd);
MessageBox.Show("The field & the index have been created !", "Successful Creation", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else if (CHKAutoIncrement.Checked && !CHKPrimaryKey.Checked)
{
MyFL = MyTb.CreateField(txtFieldsName.Text, FieldAttributeEnum.dbAutoIncrField);
MyTb.Fields.Append(MyFL);
MessageBox.Show("The field has been created !", "Successful Creation", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
MyFL = MyTb.CreateField(txtFieldsName.Text, CBOType.SelectedItem, txtLength.Text);
MyTb.Fields.Append(MyFL);
MessageBox.Show("The field has been created !", "Successful Creation", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
return MyTb;
}
private void CreateField(TextBox txtfields, ComboBox cmbDataIndexmboBox1)
{
string typedata = cmbDataIndexmboBox1.Text;
switch (typedata)
{
case "Short Text":
{
myFL = myTB.CreateField(txtfields.Text, DAO.DataTypeEnum.dbText);
}
break;
case "Number":
{
myFL = myTB.CreateField(txtfields.Text, DAO.DataTypeEnum.dbInteger);
}
break;
case "Date/Time":
{
myFL = myTB.CreateField(txtfields.Text, DAO.DataTypeEnum.dbDate);
}
break;
case "Currency":
{
myFL = myTB.CreateField(txtfields.Text, DAO.DataTypeEnum.dbCurrency);
}
break;
case "Yes/No":
{
myFL = myTB.CreateField(txtfields.Text, DAO.DataTypeEnum.dbBoolean);
}
break;
}
}
private void CreateDoubleFields(TableDef tableDef)
{
try
{
List <string> dblFields = new List <string>();
foreach (Field field in tableDef.Fields)
{
if (field.Name.Contains("Dbl_"))
{
dblFields.Add(field.Name); //existing double fields
}
}
foreach (string paramId in doubleFields.Keys)
{
string fieldName = "Dbl_" + paramId;
if (!dblFields.Contains(fieldName))
{
Field field = tableDef.CreateField(fieldName, DataTypeEnum.dbDouble);
tableDef.Fields.Append(field);
}
}
string keyField = mainGridView.Columns[0].Name;
foreach (DataGridViewRow row in mainGridView.Rows)
{
string keyValue = row.Cells[keyField].Value.ToString();
foreach (string paramId in doubleFields.Keys)
{
string paramName = doubleFields[paramId];
string paramValue = row.Cells[paramName].Value.ToString();
if (null != row.Cells[paramName].Tag)
{
paramValue = row.Cells[paramName].Tag.ToString();
}
string fieldName = "Dbl_" + paramId;
string queryStr = "UPDATE [" + tableName + "] SET [" + fieldName + "] = '" + paramValue + "' WHERE [" + keyField + "] = '" + keyValue + "'";
try { daoDB.Execute(queryStr, RecordsetOptionEnum.dbFailOnError); }
catch { continue; }
}
}
}
catch (Exception ex)
{
MessageBox.Show("Failed to create double fields.\n" + ex.Message, "Expression Builder Error:", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
private bool ValidateExpression(TableDef tableDef, string expression)
{
try
{
Field2 field2 = tableDef.CreateField("temp", DataTypeEnum.dbText) as Field2;
field2.Expression = expression;
field2.AllowZeroLength = true;
tableDef.Fields.Append(field2);
}
catch (Exception ex)
{
MessageBox.Show("The expression you entered contains invalid syntax.\n" + ex.Message, "Expression Builder Error:", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return(false);
}
tableDef.Fields.Delete("temp");
return(true);
}
private void btn_createTable_Click(object sender, EventArgs e)
{
string tableName = txt_tableName.Text.Trim();
if (String.IsNullOrEmpty(tableName))
{
MessageBox.Show("Please provide a name for the new table.", this.MdiParent.Text, MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
txt_tableName.Focus();
return;
}
try
{
// Creating the table in memory. It is not in the database yet.
myTb = clsGlobal.myDB.CreateTableDef(tableName);
// Table must have at least one field before being appended to the database.
// Creating a simple autoincrement field named ID.
Field fld = myTb.CreateField("ID", DAO.DataTypeEnum.dbLong);
fld.Attributes = (int)DAO.FieldAttributeEnum.dbAutoIncrField;
myTb.Fields.Append(fld);
// Appending table myTb to database myDB.
clsGlobal.myDB.TableDefs.Append(myTb);
}
catch (Exception exc)
{
string message = "The following error occurred while creating the new table:\n\n";
message += exc.Message;
MessageBox.Show(message, this.MdiParent.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
MessageBox.Show("The table was created successfully!\nYou may now add more fields to it.", this.MdiParent.Text, MessageBoxButtons.OK, MessageBoxIcon.Information);
// Adding the ID field characteristics to the list of fields (grd_fields).
grd_fields.Rows.Add("ID", "Long");
// Enabling grp_fields so the user can add more fields to the table, and disabling grp_table.
grp_table.Enabled = false;
grp_fields.Enabled = true;
}
private void CreateField(TableDef tblName, String strFieldName, Boolean booAllowZeroLength, DataTypeEnum fieldType,
int lngAttributes, int intMaxLength, Object defaultValue)
{
Field tmpNewField = tblName.CreateField(strFieldName, fieldType, intMaxLength);
if (fieldType == DataTypeEnum.dbText || fieldType == DataTypeEnum.dbMemo)
{
tmpNewField.AllowZeroLength = booAllowZeroLength;
}
tmpNewField.Attributes = lngAttributes;
if (defaultValue != null)
{
tmpNewField.DefaultValue = defaultValue;
}
tblName.Fields.Append(tmpNewField);
}
private void DeleteCalculatedField(ListViewItem listViewItem)
{
try
{
int paramId = 0;
if (int.TryParse(listViewItem.Name, out paramId))
{
selTable.Fields.Delete(listViewItem.Name);
Field field = selTable.CreateField(listViewItem.Name, DataTypeEnum.dbText);
field.AllowZeroLength = true;
selTable.Fields.Append(field);
fieldsToRecover.Add(listViewItem.Text); //paramName
}
else
{
selTable.Fields.Delete(listViewItem.Name);
selDataTable.Columns.Remove(listViewItem.Text);
}
}
catch (Exception ex)
{
MessageBox.Show("Failed to delete calculated fields from database.\n" + ex.Message, "DeleteCalculated Error:", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
private void btn_createField_Click(object sender, EventArgs e)
{
// **** Field Name ****
string fieldName = txt_fieldName.Text.Trim();
if (String.IsNullOrEmpty(fieldName))
{
MessageBox.Show("Please provide a name for the new field.", this.MdiParent.Text, MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
txt_fieldName.Focus();
return;
}
// **** Field Type ****
if (cbo_fieldType.SelectedIndex == -1)
{
MessageBox.Show("Please select the field type.", this.MdiParent.Text, MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
cbo_fieldType.Focus();
return;
}
DAO.DataTypeEnum fieldType = 0;
switch (cbo_fieldType.Text)
{
case "Short Text":
fieldType = DAO.DataTypeEnum.dbText;
break;
case "Long":
fieldType = DAO.DataTypeEnum.dbLong;
break;
case "Integer":
fieldType = DAO.DataTypeEnum.dbInteger;
break;
default:
break;
}
// **** Field Size ****
string strFieldSize = txt_fieldSize.Text.Trim();
int fieldSize = 0;
bool bParseFieldSize = int.TryParse(strFieldSize, out fieldSize);
// Checking the provided field size only if the type chosen is dbText.
if (fieldType == DAO.DataTypeEnum.dbText)
{
if (!(fieldSize > 0))
{
MessageBox.Show("Please enter a valid non-zero field size.", this.MdiParent.Text, MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
txt_fieldSize.Focus();
return;
}
}
// Creating the field and adding it to the table.
try
{
Field fld = myTb.CreateField(txt_fieldName.Text, fieldType, fieldSize);
myTb.Fields.Append(fld); // appending the field to table myTb.
MessageBox.Show("Field was created successfully!", this.MdiParent.Text, MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception exc)
{
string message = "The following error occurred while creating the field:\n\n";
message += exc.Message;
MessageBox.Show(message, this.MdiParent.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
// Adding the field characteristics to the list of fields (grd_fields).
grd_fields.Rows.Add(txt_fieldName.Text, cbo_fieldType.Text, txt_fieldSize.Text);
// Clearing the fields.
txt_fieldName.Clear();
txt_fieldSize.Clear();
cbo_fieldType.SelectedIndex = -1;
}
private void tleAddField_Click(object sender, EventArgs e)
{
//Check if name is already in use
foreach (Field afield in myTB.Fields)
{
if (afield.Name == txtField.Text)
{
MetroMessageBox.Show(this, "Field not created, name already in use!", "Create Field", MessageBoxButtons.OK, MessageBoxIcon.Error);
txtField.Clear();
txtField.Focus();
return;
}
}
//Defining dataype selected
DataTypeEnum dtype = DataTypeEnum.dbInteger;//Integer by default
if (cboDataType.SelectedItem.ToString() == "AutoNumber")
{
dtype = DataTypeEnum.dbLong;
}
else
{
foreach (DataTypeEnum item in Enum.GetValues(typeof(DataTypeEnum)))
{
if (cboDataType.SelectedItem.ToString() == item.ToString().Substring(2))
{
dtype = item;
}
}
}
//Create an item to be shown in the listview
ListViewItem field = new ListViewItem();
field.SubItems.Add(txtField.Text);
field.SubItems.Add(cboDataType.Text);
//If selected datatype is Text, takes the size
if (!string.IsNullOrEmpty(txtSize.Text))
{
field.SubItems.Add(txtSize.Text);
myFL = myTB.CreateField(txtField.Text, dtype, Convert.ToInt32(txtSize.Text));
}
//if not, does not consider size
else
{
myFL = myTB.CreateField(txtField.Text, dtype);
if (cboDataType.Text == "AutoNumber")
{
myFL.Attributes = (int)DAO.FieldAttributeEnum.dbAutoIncrField;
}
}
//Add item created to the listview
lvwTable.Items.Add(field);
//Append field to the table
myTB.Fields.Append(myFL);
clearAftCreate();
tleSaveTbl.Enabled = true;
}
private void btnCreateTables_Click(object sender, EventArgs e)
{
TableDef mytdef = null;
Field myfield = null, indexfield;
Index myInd = null;
int j = 0;
List <Field> flist = new List <Field>();
try
{
// Delete all the tables from DB to avoid Table's conflicts
//DeleteAllTables(mydb);
foreach (TabPage tbtemp in tabcontFields.TabPages)
{
// check if Table exist in DB
if (tableExist(mydb, tbtemp.Text) == false)
{
////////////////////////////
// Table and Fields Creation
////////////////////////////
mytdef = mydb.CreateTableDef(tbtemp.Text);
for (int i = 0; i < dgridList[j].RowCount - 1; i++)
{
myfield = mytdef.CreateField(dgridList[j].Rows[i].Cells["colField"].Value, (DataTypeEnum)Enum.Parse(typeof(DataTypeEnum), dgridList[j].Rows[i].Cells["colDataType"].Value.ToString()));
if (i == 0)
{
// Add autoincrement attribute to first field "ID"
myfield.Attributes = (Int32)DAO.FieldAttributeEnum.dbAutoIncrField;
}
// Add Field to TableDef
mytdef.Fields.Append(myfield);
flist.Add(myfield);
}
/////////////////////////////////////
////Create an Index and Primary Key
/////////////////////////////////////
myInd = mytdef.CreateIndex(dgridList[j].Rows[0].Cells["colField"].Value.ToString());
myInd.Primary = true;
//////////////////////////////////
// Add field ID to index as ass Primary Key by default
//Creation of indexed fields
indexfield = myInd.CreateField(dgridList[j].Rows[0].Cells["colField"].Value);
//Add field in index
((IndexFields)(myInd.Fields)).Append(indexfield);
// Creation of associated PK and indexes defined by the user
for (int i = 1; i < dgridList[j].RowCount - 1; i++)
{
// Verify if PK check Box is checked to create an index and Primary Key
if ((string)(dgridList[j].Rows[i].Cells["Key"]).Value == "true")
{
//Creation of indexed fields
indexfield = myInd.CreateField(dgridList[j].Rows[i].Cells["colField"].Value);
//Add field in index
((IndexFields)(myInd.Fields)).Append(indexfield);
}
}
//Add index in the table
mytdef.Indexes.Append(myInd);
//Add table into DB
mydb.TableDefs.Append(mytdef);
// Add the Field's description to each field adding a new property to each cell
Property prt = null;
int rindex;
rindex = 0;
foreach (Field ftemp in flist)
{
if (dgridList[j].Rows[rindex].Cells["colDescription"].Value != null)
{
//Cretion of new porperty called Description in each Cell
prt = ftemp.CreateProperty("Description", DataTypeEnum.dbText, dgridList[j].Rows[rindex].Cells["colDescription"].Value);
ftemp.Properties.Append(prt);
}
rindex++;
}
flist.Clear();
j++;
}
else
{
j++;
}
}
MessageBox.Show("Tables have been Created in DB " + mydb.Name, "Tables Created", MessageBoxButtons.OK, MessageBoxIcon.Information);
btnGoRelations.Show();
}
catch (NullReferenceException)
{
MessageBox.Show("All fields must have a Data type selected in the table " + mytdef.Name, "Required Fields", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
catch (System.Runtime.InteropServices.COMException daoErr)
{
if (daoErr.ErrorCode == FieldExistErrorCode) // Field exist Error Code
{
MessageBox.Show("There are repeated fields in table " + mytdef.Name + "\nField's name must be unique", "Repeated Field", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
if (daoErr.ErrorCode == FieldUnamedErrorCode) // Field unamed Error Code
{
MessageBox.Show("All Fields must have a name in table " + mytdef.Name, "Unnamed Field", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else // Other Errors related to Interpot Services
{
MessageBox.Show(daoErr.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
catch (Exception ex) // General Exceptions
{
MessageBox.Show(ex.ToString(), "Table Creation Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void createField(DataGridView dgv, int indexRow) //function to create a new field
{
string name;
name = dgv.Rows[indexRow].Cells["clmName"].Value.ToString();
int size;
Field field = new Field();
//creates the field according to the type chosen by the user
if (dgv.Rows[indexRow].Cells["clmType"].Value.ToString() == "Long" && autoIncr < 2)
{
size = Convert.ToInt16(dgv.Rows[indexRow].Cells["clmSize"].Value.ToString());
field = table.CreateField(name, DAO.DataTypeEnum.dbLong, size);
if (dgv.Rows[indexRow].Cells["clmAuto"].Value != null && dgv.Rows[indexRow].Cells["clmAuto"].Value.ToString() == "True")
{
field.Attributes = (int)DAO.FieldAttributeEnum.dbAutoIncrField;
}
}
else if (dgv.Rows[indexRow].Cells["clmType"].Value.ToString() == "Double")
{
size = Convert.ToInt16(dgv.Rows[indexRow].Cells["clmSize"].Value.ToString());
field = table.CreateField(name, DAO.DataTypeEnum.dbDouble, size);
}
else if (dgv.Rows[indexRow].Cells["clmType"].Value.ToString() == "Text")
{
size = Convert.ToInt16(dgv.Rows[indexRow].Cells["clmSize"].Value.ToString());
field = table.CreateField(name, DAO.DataTypeEnum.dbText, size);
}
else if (dgv.Rows[indexRow].Cells["clmType"].Value.ToString() == "Currency")
{
size = Convert.ToInt16(dgv.Rows[indexRow].Cells["clmSize"].Value.ToString());
field = table.CreateField(name, DAO.DataTypeEnum.dbCurrency, size);
}
else if (dgv.Rows[indexRow].Cells["clmType"].Value.ToString() == "Boolean")
{
size = Convert.ToInt16(dgv.Rows[indexRow].Cells["clmSize"].Value.ToString());
field = table.CreateField(name, DAO.DataTypeEnum.dbBoolean, size);
}
else
{
field = table.CreateField(name, DAO.DataTypeEnum.dbDate);
}
table.Fields.Append(field); //appends the field to the table
if (dgv.Rows[indexRow].Cells["clmFieldProperty"].Value != null &&
dgv.Rows[indexRow].Cells["clmFieldProperty"].Value.ToString() == "Primary")
//check if field is primary, then append the property
{
pricount++;
Index index = table.CreateIndex("pk_" + name);
field = index.CreateField(name);
index.Primary = true;
index.Required = true;
index.IgnoreNulls = false;
((IndexFields)index.Fields).Append(field);
table.Indexes.Append(index);
}
else if (dgv.Rows[indexRow].Cells["clmFieldProperty"].Value != null &&
dgv.Rows[indexRow].Cells["clmFieldProperty"].Value.ToString() == "Unique")
//check if field is unique, then append the property
{
unqcount++;
Index index = table.CreateIndex("unq_" + name);
field = index.CreateField(name);
index.Required = true;
index.Unique = true;
index.IgnoreNulls = false;
((IndexFields)index.Fields).Append(field);
table.Indexes.Append(index);
}
else if (dgv.Rows[indexRow].Cells["clmFieldProperty"].Value != null &&
dgv.Rows[indexRow].Cells["clmFieldProperty"].Value.ToString() == "Index")
//check if field is index, then append the property
{
Index index = table.CreateIndex("idx_" + name);
field = index.CreateField(name);
index.Required = true;
index.Unique = true;
index.IgnoreNulls = false;
((IndexFields)index.Fields).Append(field);
table.Indexes.Append(index);
}
}
private bool createTblField(TableField newFld) //procedure of creating a field and adding it to the table
{
DataTypeEnum fldTypeEnum;
Field myFld;
string fldName = newFld.FieldName;
string fldType = newFld.FieldType;
int fldLenNum = newFld.FieldLength;
bool isPkeyFld = newFld.IsPkey;
bool isAutoIncr = newFld.IsAutoIncr;
//create fields:
switch (fldType)
{
case "dbInt":
fldTypeEnum = DataTypeEnum.dbInteger;
break;
case "dbLong":
fldTypeEnum = DataTypeEnum.dbLong;
break;
case "dbText":
fldTypeEnum = DataTypeEnum.dbText;
break;
case "dbBoolean":
fldTypeEnum = DataTypeEnum.dbBoolean;
break;
case "dbDate":
fldTypeEnum = DataTypeEnum.dbDate;
break;
default:
MessageBox.Show("Unknown error during type defenition! \nDefault type will be TEXT!", "Error");
fldTypeEnum = DataTypeEnum.dbText;
break;
}
//creating a field:
if (fldTypeEnum == DataTypeEnum.dbText)
{
//text field - length set to 50 by default
//fldLenNum = (fldLengthStr != "") ? Convert.ToInt32(fldLengthStr) : 50;
myFld = myTb.CreateField(fldName, DAO.DataTypeEnum.dbText, fldLenNum);
}
else
{
//numeric data field - default length =1 but it is not used anyway
//fldLenNum = (fldLengthStr != "") ? Convert.ToInt32(fldLengthStr) : 1;
myFld = myTb.CreateField(fldName, fldTypeEnum);
if (isAutoIncr)
{
//Auto Increment numeric field if selected checkbox
if ((fldTypeEnum == DataTypeEnum.dbInteger) || (fldTypeEnum == DataTypeEnum.dbLong))
{
myFld.Attributes = (int)DAO.FieldAttributeEnum.dbAutoIncrField;
}
}
}
//lets try to append a field to the table:
try
{
myTb.Fields.Append(myFld);
if (isPkeyFld)
{
//create the indexes
Index myInd = myTb.CreateIndex("PrimaryKey");
myFld = myInd.CreateField(fldName);
((IndexFields)(myInd.Fields)).Append(myFld);
myInd.Primary = true;
myTb.Indexes.Append(myInd);
}
return(true);
}
catch (Exception ex)
{
MessageBox.Show("Can not create field: " + newFld.FieldName + "\n" + ex.Message,
"Error while creating new field", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(false);
}
}
private void createField(DataGridView dgv, int indexRow)
{
string name;
name = dgv.Rows[indexRow].Cells["ColumnTableName"].Value.ToString();
int size;
Field field = new Field();
if (dgv.Rows[indexRow].Cells["ColumnType"].Value.ToString() == "LONG")
{
size = Convert.ToInt16(dgv.Rows[indexRow].Cells["ColumnSize"].Value.ToString());
field = table.CreateField(name, DAO.DataTypeEnum.dbLong, size);
if (dgv.Rows[indexRow].Cells["ColumnAutoIncr"].Value != null && dgv.Rows[indexRow].Cells["ColumnAutoIncr"].Value.ToString() == "T")
{
field.Attributes = (int)DAO.FieldAttributeEnum.dbAutoIncrField;
}
}
else if (dgv.Rows[indexRow].Cells["ColumnType"].Value.ToString() == "DOUBLE")
{
size = Convert.ToInt16(dgv.Rows[indexRow].Cells["ColumnSize"].Value.ToString());
field = table.CreateField(name, DAO.DataTypeEnum.dbDouble, size);
}
else if (dgv.Rows[indexRow].Cells["ColumnType"].Value.ToString() == "TEXT")
{
size = Convert.ToInt16(dgv.Rows[indexRow].Cells["ColumnSize"].Value.ToString());
field = table.CreateField(name, DAO.DataTypeEnum.dbText, size);
}
else
{
field = table.CreateField(name, DAO.DataTypeEnum.dbDate);
}
table.Fields.Append(field);
if (dgv.Rows[indexRow].Cells["ColumnIndex"].Value != null && dgv.Rows[indexRow].Cells["ColumnIndex"].Value.ToString() == "PRIMARY")
{
Index index = table.CreateIndex("Primary key" + name);
field = index.CreateField(name);
index.Primary = true;
index.Required = true;
index.IgnoreNulls = false;
((IndexFields)index.Fields).Append(field);
table.Indexes.Append(index);
}
else if (dgv.Rows[indexRow].Cells["ColumnIndex"].Value != null && dgv.Rows[indexRow].Cells["ColumnIndex"].Value.ToString() == "UNIQUE")
{
Index index = table.CreateIndex("Unique");
field = index.CreateField(name);
index.Required = true;
index.Unique = true;
index.IgnoreNulls = false;
((IndexFields)index.Fields).Append(field);
table.Indexes.Append(index);
}
else if (dgv.Rows[indexRow].Cells["ColumnIndex"].Value != null && dgv.Rows[indexRow].Cells["ColumnIndex"].Value.ToString() == "INDEX")
{
Index index = table.CreateIndex("Index" + name);
field = index.CreateField(name);
index.Required = true;
index.Unique = true;
index.IgnoreNulls = false;
((IndexFields)index.Fields).Append(field);
table.Indexes.Append(index);
}
}
private void btnAdd_Click(object sender, EventArgs e)
{
if (cboType.Text == "")
{
MessageBox.Show("You must choose field type!", "Field ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (txtFieldName.Text == "")
{
MessageBox.Show("You must enter field name!", "Field ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
switch (cboType.SelectedIndex)
{
case 0: //AutoNumber
myFL = myTB.CreateField(txtFieldName.Text, DAO.DataTypeEnum.dbLong);
myFL.Attributes = (int)DAO.FieldAttributeEnum.dbAutoIncrField;
myTB.Fields.Append(myFL);
ListViewField(lvFieldList, txtFieldName.Text, cboType.Text);
ClearAll(txtFieldName, cboType);
MessageBox.Show("Field added successfully!", "Add Field", MessageBoxButtons.OK, MessageBoxIcon.Information);
ButtonControl(false, false, true, false, true);
break;
case 1: //Text
myFL = myTB.CreateField(txtFieldName.Text, DAO.DataTypeEnum.dbText, 100);
myTB.Fields.Append(myFL);
ListViewField(lvFieldList, txtFieldName.Text, cboType.Text);
ClearAll(txtFieldName, cboType);
MessageBox.Show("Field added successfully!", "Add Field", MessageBoxButtons.OK, MessageBoxIcon.Information);
ButtonControl(false, false, true, false, true);
break;
case 2: //Number
myFL = myTB.CreateField(txtFieldName.Text, DAO.DataTypeEnum.dbLong);
myTB.Fields.Append(myFL);
ListViewField(lvFieldList, txtFieldName.Text, cboType.Text);
ClearAll(txtFieldName, cboType);
MessageBox.Show("Field added successfully!", "Add Field", MessageBoxButtons.OK, MessageBoxIcon.Information);
ButtonControl(false, false, true, false, true);
break;
case 3: //Currency
myFL = myTB.CreateField(txtFieldName.Text, DAO.DataTypeEnum.dbCurrency);
myTB.Fields.Append(myFL);
ListViewField(lvFieldList, txtFieldName.Text, cboType.Text);
ClearAll(txtFieldName, cboType);
MessageBox.Show("Field added successfully!", "Add Field", MessageBoxButtons.OK, MessageBoxIcon.Information);
ButtonControl(false, false, true, false, true);
break;
case 4: //DateTime
myFL = myTB.CreateField(txtFieldName.Text, DAO.DataTypeEnum.dbDate);
myTB.Fields.Append(myFL);
ListViewField(lvFieldList, txtFieldName.Text, cboType.Text);
ClearAll(txtFieldName, cboType);
MessageBox.Show("Field added successfully!", "Add Field", MessageBoxButtons.OK, MessageBoxIcon.Information);
ButtonControl(false, false, true, false, true);
break;
}
}
private void CreateField(bool exist, TableDef tableDef, string strExpression)
{
try
{
if (exist)
{
string paramId = fieldDictionary[selField].ParamId;
tableDef.Fields.Delete(paramId);
if (calType == CalculationType.String)
{
Field2 field2 = tableDef.CreateField(paramId, DataTypeEnum.dbText) as Field2;
field2.Expression = strExpression;
field2.AllowZeroLength = true;
tableDef.Fields.Append(field2);
}
else if (calType == CalculationType.Math)
{
Field2 field2 = tableDef.CreateField(paramId, DataTypeEnum.dbDouble) as Field2;
field2.Expression = strExpression;
tableDef.Fields.Append(field2);
if (setDecimal)
{
Property property = field2.CreateProperty("DecimalPlaces", DataTypeEnum.dbByte, decimalPlace);
field2.Properties.Append(property);
property = field2.CreateProperty("Format", DataTypeEnum.dbText, "Fixed");
field2.Properties.Append(property);
field2.Properties.Refresh();
}
}
}
else// not exist in the database
{
if (calType == CalculationType.String)
{
Field2 field2 = tableDef.CreateField(selField, DataTypeEnum.dbText) as Field2;
field2.Expression = strExpression;
field2.AllowZeroLength = true;
tableDef.Fields.Append(field2);
}
else if (calType == CalculationType.Math)
{
Field2 field2 = tableDef.CreateField(selField, DataTypeEnum.dbDouble) as Field2;
if (roundUp)
{
string oldExpression = strExpression;
strExpression = string.Format("IIf({0}>Int({0}),Int({0})+1,Int({0}))", oldExpression);
}
else if (roundDown)
{
strExpression = "Int(" + strExpression + ")";
}
field2.Expression = strExpression;
tableDef.Fields.Append(field2);
if (setDecimal)
{
Property property = field2.CreateProperty("DecimalPlaces", DataTypeEnum.dbByte, decimalPlace);
field2.Properties.Append(property);
property = field2.CreateProperty("Format", DataTypeEnum.dbText, "Fixed");
field2.Properties.Append(property);
field2.Properties.Refresh();
}
}
DataColumn column = new DataColumn();
column.DataType = System.Type.GetType(strDataType);
column.ColumnName = selField;
selDataTable.Columns.Add(column);
}
}
catch (Exception ex)
{
MessageBox.Show("Failed to create a calculated field.\n" + ex.Message, "Expression Builder Error:", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
private void CreateField(TableDef tblName, String strFieldName, Boolean booAllowZeroLength, DataTypeEnum fieldType,
int lngAttributes, int intMaxLength, Object defaultValue)
{
Field tmpNewField = tblName.CreateField(strFieldName, fieldType, intMaxLength);
if (fieldType == DataTypeEnum.dbText || fieldType == DataTypeEnum.dbMemo)
tmpNewField.AllowZeroLength = booAllowZeroLength;
tmpNewField.Attributes = lngAttributes;
if (defaultValue != null)
tmpNewField.DefaultValue = defaultValue;
tblName.Fields.Append(tmpNewField);
}
private void save_new_field()
{
ListViewItem to_add;
string datatype = "";
string index = "";
nf = null;
MessageBox.Show(fieldtype + " is the datatype");
switch (fieldtype)
{
case 0:
myFL = myTB.CreateField(Field_Name, DataTypeEnum.dbText, 100);
datatype = "Text";
myTB.Fields.Append(myFL);
break;
case 1:
myFL = myTB.CreateField(Field_Name, DataTypeEnum.dbLong);
datatype = "Number";
myTB.Fields.Append(myFL);
break;
case 2:
myFL = myTB.CreateField(Field_Name, DataTypeEnum.dbDate);
datatype = "DateTime";
myTB.Fields.Append(myFL);
break;
case 3:
myFL = myTB.CreateField(Field_Name, DataTypeEnum.dbCurrency);
datatype = "Currency";
myTB.Fields.Append(myFL);
break;
case 4:
myFL = myTB.CreateField(Field_Name, DataTypeEnum.dbBoolean);
datatype = "YesNo";
myTB.Fields.Append(myFL);
break;
case 5:
myFL = myTB.CreateField(Field_Name, DataTypeEnum.dbLong);
datatype = "AutoNumber";
myTB.Fields.Append(myFL);
myFL.Attributes = (int)FieldAttributeEnum.dbAutoIncrField;
break;
}
if (!primary)
{
if (MessageBox.Show("Is this primary key?", "Index", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
{
Index myInd = myTB.CreateIndex("PrimaryKey");
myFL = myInd.CreateField(Field_Name);
((IndexFields)myInd.Fields).Append(myFL);
myInd.Primary = true;
myTB.Indexes.Append(myInd);
index = "Primary";
primary = true;
}
}
to_add = new ListViewItem(index);
to_add.SubItems.Add(Field_Name);
to_add.SubItems.Add(datatype);
lstview_current_table.Items.Add(to_add);
}
private void btnCreate_Click(object sender, EventArgs e) //triggers the creation of a new field
{
bool error = false;
foreach (TableDef oneTable in clsDataStorage.db.TableDefs) //loops through the tabledefinitions
{
if (oneTable.Attributes == 0)
{
foreach (Field abcd in oneTable.Fields)
{
if (abcd.Name == txtFieldName.Text) //check if table already contains a field with the same name
{
MetroMessageBox.Show(this, "This table already contains a field with the name " + abcd.Name + ".\nField names have to be unique!", "Error!", MessageBoxButtons.OK, MessageBoxIcon.Error);
error = true;
}
}
if (cmbFieldProperty.Text == "Primary" || cmbFieldProperty.Text == "Unique") //check if table contains primary or unique elements
{
foreach (Index inx in oneTable.Indexes)
{
if (inx.Primary == true && !error)
{
MetroMessageBox.Show(this, "This table already contains a field with the property INDEX.\nA table can only contain one index.", "Error!", MessageBoxButtons.OK, MessageBoxIcon.Error);
error = true;
break;
}
if (inx.Unique == true && !error)
{
MetroMessageBox.Show(this, "This table already contains a field with the property UNIQUE.\nA table can only contain one UNIQUE.", "Error!", MessageBoxButtons.OK, MessageBoxIcon.Error);
error = true;
break;
}
}
}
}
}
if (!error) //if no primary or unique, then create a new field
{
table = clsDataStorage.db.TableDefs[cmbListTable.Text];
string name;
name = txtFieldName.Text;
int size;
Field field = new Field();
//creates the field according to the type chosen by the user
if (cmbFieldType.Text == "Long")
{
size = Convert.ToInt32(txtFieldSize.Text);
field = table.CreateField(name, DAO.DataTypeEnum.dbLong, size);
if (chkAuto.Checked)
{
field.Attributes = (int)DAO.FieldAttributeEnum.dbAutoIncrField;
}
}
else if (cmbFieldType.Text == "Double")
{
size = Convert.ToInt32(txtFieldSize.Text);
field = table.CreateField(name, DAO.DataTypeEnum.dbDouble, size);
}
else if (cmbFieldType.Text == "Text")
{
size = Convert.ToInt32(txtFieldSize.Text);
field = table.CreateField(name, DAO.DataTypeEnum.dbText, size);
}
else if (cmbFieldType.Text == "Currency")
{
size = Convert.ToInt32(txtFieldSize.Text);
field = table.CreateField(name, DAO.DataTypeEnum.dbCurrency, size);
}
else if (cmbFieldType.Text == "Boolean")
{
size = Convert.ToInt32(txtFieldSize.Text);
field = table.CreateField(name, DAO.DataTypeEnum.dbBoolean, size);
}
else
{
field = table.CreateField(name, DAO.DataTypeEnum.dbDate);
}
table.Fields.Append(field); //appends the field to the table
if (cmbFieldProperty.Text == "Primary") //check if field is primary, then append the property
{
Index index = table.CreateIndex("pk_" + name);
field = index.CreateField(name);
index.Primary = true;
index.Required = true;
index.IgnoreNulls = false;
((IndexFields)index.Fields).Append(field);
table.Indexes.Append(index);
}
else if (cmbFieldProperty.Text == "Unique") //check if field is unique, then append the property
{
Index index = table.CreateIndex("unq_" + name);
field = index.CreateField(name);
index.Required = true;
index.Unique = true;
index.IgnoreNulls = false;
((IndexFields)index.Fields).Append(field);
table.Indexes.Append(index);
}
else if (cmbFieldProperty.Text == "Index") //check if field is index, then append the property
{
Index index = table.CreateIndex("idx_" + name);
field = index.CreateField(name);
index.Required = true;
index.Unique = true;
index.IgnoreNulls = false;
((IndexFields)index.Fields).Append(field);
table.Indexes.Append(index);
}
MetroMessageBox.Show(this, "The new field was included successfully.", "New field", MessageBoxButtons.OK, MessageBoxIcon.Information);
this.Close(); //after the field being added, closes the form
}
}