private void SaveIfNotExist()
{
string sql = string.Empty;
for (int i = 0; i < gvFormItems.Rows.Count; i++)
{
sql += SQLs.InsertFormItems(dbType,
gvFormItems.Rows[i].Cells[colApp.Index].Value,
gvFormItems.Rows[i].Cells[colForm.Index].Value,
gvFormItems.Rows[i].Cells[colItem.Index].Value,
gvFormItems.Rows[i].Cells[colCulture.Index].Value,
gvFormItems.Rows[i].Cells[colPropertyName.Index].Value,
gvFormItems.Rows[i].Cells[colValueType.Index].Value,
gvFormItems.Rows[i].Cells[colValue.Index].Value,
gvFormItems.Rows[i].Cells[colInUse.Index].Value);
}
using (IDbTransaction trans = GetTrans())
{
using (IDbCommand command = GetCommand(trans))
{
command.CommandText = sql;
command.ExecuteNonQuery();
}
trans.Commit();
}
sql = string.Empty;
for (int i = 0; i < gvMessages.Rows.Count; i++)
{
sql += SQLs.InsertMessages(dbType,
gvMessages.Rows[i].Cells[colMessageCulture.Index].Value,
gvMessages.Rows[i].Cells[colMessageName.Index].Value,
gvMessages.Rows[i].Cells[colMessageValue.Index].Value,
gvMessages.Rows[i].Cells[colCommentEnUs.Index].Value,
gvMessages.Rows[i].Cells[colMsgInUse.Index].Value);
}
using (IDbTransaction trans = GetTrans())
{
using (IDbCommand command = GetCommand(trans))
{
command.CommandText = sql;
command.ExecuteNonQuery();
}
trans.Commit();
}
}
private void OpenDatabase(string fileName)
{
try
{
CloseDBConnection();
connStr = "Data Source=" + fileName + ";Pooling=true;FailIfMissing=false";
connSQLite = new SQLiteConnection(connStr);
connSQLite.Open();
culture = new Culture(ref connSQLite);
currentFile = fileName;
captionStr = fileName;
dbType = DBLangEngine.DatabaseType.dtSQLite;
schema = string.Empty;
}
catch (Exception ex)
{
MessageBox.Show("Unable to connect to database ('" + ex.Message + "').", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
SQLs.SetSchema(DBLangEngine.DatabaseType.dtSQLite, schema);
Text = "DBLangVersion [" + captionStr + "]";
mnuSave.Enabled = true;
mnuAddFromCulture.Enabled = true;
mnuRemoveUnused.Enabled = true;
cmbCulture.Enabled = true;
mnuExportDatabase.Enabled = dbType == DBLangEngine.DatabaseType.dtSQLite;
mnuAddFomEN_US.Enabled = false;
mnuSelectCurrentCulture.Enabled = true;
mnuDumpData.Enabled = true;
cmbCulture.Items.AddRange(culture.Cultures.ToArray());
for (int i = 0; i < cmbCulture.Items.Count; i++)
{
if ((cmbCulture.Items[i] as Culture).LCID == 1033)
{
cmbCulture.SelectedIndex = i;
}
cmbCulture.AutoCompleteCustomSource.Add((cmbCulture.Items[i] as Culture).NativeName);
}
mnuSelectSomeCulture.Enabled = true;
ListCulturesMenu();
}
private void mnuRemoveUnused_Click(object sender, EventArgs e)
{
string culture = (cmbCulture.Items[cmbCulture.SelectedIndex] as Culture).CultureText;
string sql = SQLs.DeleteUnusedMessages(dbType, culture);
sql += SQLs.DeleteUnusedFormItems(dbType, culture);
using (IDbTransaction trans = GetTrans())
{
using (IDbCommand command = GetCommand(trans))
{
command.CommandText = sql;
command.ExecuteNonQuery();
}
trans.Commit();
}
LoadDB();
}
private void ListCulturesMenu()
{
mnuSelectSomeCulture.DropDownItems.Clear();
using (IDbCommand command = GetCommand())
{
command.CommandText = SQLs.SelectDistinctCulture(dbType);
using (IDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
ToolStripMenuItem item = new ToolStripMenuItem(dr.GetString(0))
{
Tag = CultureInfo.GetCultureInfo(dr.GetString(0)), Checked = (cmbCulture.SelectedItem as Culture).CultureText == dr.GetString(0)
};
item.Click += SelectSomeCultureClick;
mnuSelectSomeCulture.DropDownItems.Add(item);
}
}
}
}
private void DoMessageDump()
{
DBLangEngine.DatabaseType scriptDbType;
if (FormSelectDBType.Execute(out scriptDbType))
{
string scriptSchema = string.Empty;
if (scriptDbType == DBLangEngine.DatabaseType.dtMSSQL)
{
scriptSchema = "[dbo].";
}
else if (scriptDbType == DBLangEngine.DatabaseType.dtPostgreSQL)
{
scriptSchema = "public.";
}
List <string> sqlDump = new List <string>();
string addStrForm = "INSERT INTO FORMITEMS(APP_FORM, ITEM, CULTURE, PROPERTYNAME, VALUETYPE, VALUE, INUSE) " + Environment.NewLine +
"SELECT {0}, {1}, {2}, {3}, {4}, {5}, {6} " + Environment.NewLine +
"WHERE NOT EXISTS (SELECT 1 FROM {7}FORMITEMS WHERE APP_FORM = {0} AND ITEM = {1} AND CULTURE = {2} AND PROPERTYNAME = {3}); " + Environment.NewLine +
"UPDATE {7}FORMITEMS SET INUSE = {6}, VALUE = {5} WHERE APP_FORM = {0} AND ITEM = {1} AND PROPERTYNAME = {3} AND CULTURE = {2}; " + Environment.NewLine;
string addStrMessage = "INSERT INTO {5}MESSAGES(CULTURE, MESSAGENAME, VALUE, COMMENT_EN_US, INUSE) " +
"SELECT {0}, {1}, {2}, {3}, {4} " + Environment.NewLine +
"WHERE NOT EXISTS (SELECT 1 FROM {5}MESSAGES WHERE MESSAGENAME = {1} AND CULTURE = {0}); " + Environment.NewLine +
"UPDATE {5}MESSAGES SET VALUE = {2}, COMMENT_EN_US = {3}, INUSE = {4} WHERE MESSAGENAME = {1} AND CULTURE = {0}; " + Environment.NewLine;
if (scriptDbType == DBLangEngine.DatabaseType.dtMySQL)
{
addStrForm = "INSERT INTO FORMITEMS(APP_FORM, ITEM, CULTURE, PROPERTYNAME, VALUETYPE, VALUE, INUSE) " + Environment.NewLine +
"SELECT {0}, {1}, {2}, {3}, {4}, {5}, {6} FROM DUAL " + Environment.NewLine +
"WHERE NOT EXISTS (SELECT * FROM {7}FORMITEMS WHERE APP_FORM = {0} AND ITEM = {1} AND CULTURE = {2} AND PROPERTYNAME = {3}) LIMIT 1; " + Environment.NewLine +
"UPDATE {7}FORMITEMS SET INUSE = {6}, VALUE = {5} WHERE APP_FORM = {0} AND ITEM = {1} AND PROPERTYNAME = {3} AND CULTURE = {2}; " + Environment.NewLine;
addStrMessage = "INSERT INTO {5}MESSAGES(CULTURE, MESSAGENAME, VALUE, COMMENT_EN_US, INUSE) " +
"SELECT {0}, {1}, {2}, {3}, {4} FROM DUAL " + Environment.NewLine +
"WHERE NOT EXISTS (SELECT * FROM {5}MESSAGES WHERE MESSAGENAME = {1} AND CULTURE = {0}) LIMIT 1; " + Environment.NewLine +
"UPDATE {5}MESSAGES SET VALUE = {2}, COMMENT_EN_US = {3}, INUSE = {4} WHERE MESSAGENAME = {1} AND CULTURE = {0}; " + Environment.NewLine;
}
using (IDbCommand command = GetCommand())
{
command.CommandText = SQLs.SelectDumpFormItems(dbType);
using (IDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
sqlDump.Add(string.Format(addStrForm,
DBUtils.MkStr(dr.GetString(0)),
DBUtils.MkStr(dr.GetString(1)),
DBUtils.MkStr(dr.GetString(2)),
DBUtils.MkStr(dr.GetString(3)),
DBUtils.MkStr(dr.GetString(4)),
DBUtils.MkStr(dr.GetString(5)),
dr.GetInt32(6),
scriptSchema));
}
}
}
using (IDbCommand command = GetCommand())
{
command.CommandText = SQLs.SelectDumpMessages(dbType);
using (IDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
sqlDump.Add(string.Format(addStrMessage,
DBUtils.MkStr(dr.GetString(0)),
DBUtils.MkStr(dr.GetString(1)),
DBUtils.MkStr(dr.GetString(2)),
DBUtils.MkStr(dr.GetString(3)),
dr.GetInt32(4),
scriptSchema));
}
}
}
FormDataDump.Execute(sqlDump);
}
}
private void LoadDB(string culture = "")
{
gvFormItems.Rows.Clear();
gvMessages.Rows.Clear();
if (culture == string.Empty)
{
culture = (cmbCulture.Items[cmbCulture.SelectedIndex] as Culture).CultureText;
}
string culture2 = (cmbCulture.Items[cmbCulture.SelectedIndex] as Culture).CultureText;
using (IDbCommand command = GetCommand())
{
command.CommandText = SQLs.SelectUsedCultures(dbType, culture);
using (IDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
string split1, split2;
try
{
string[] split = dr.GetString(0).Split('.');
split1 = split[0];
split2 = split[1];
}
catch
{
split1 = dr.GetString(0);
split2 = string.Empty;
}
gvFormItems.Rows.Add(split1,
split2,
dr.GetString(1),
culture2,
dr.GetString(3),
dr.GetString(5),
dr.GetString(4),
dr.GetInt32(6) == 1);
}
}
}
using (IDbCommand command = GetCommand())
{
command.CommandText = SQLs.SelectUsedMessages(dbType, culture);
using (IDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
gvMessages.Rows.Add(dr.GetString(0),
dr.GetString(1),
dr.GetString(2),
culture2,
dr.GetInt32(4) == 1);
}
}
}
mnuAddFomEN_US.Enabled = !EnUSCultureLoaded();
mnuSelectCurrentCulture.Enabled = !CurrentCultureLoaded();
}
private void mnuLoadDB_Click(object sender, EventArgs e)
{
if (FormLoginDatabase.Execute(out dbType, out connStr, out captionStr, out schema))
{
CloseDBConnection();
try
{
if (dbType == DBLangEngine.DatabaseType.dtSQLite)
{
connSQLite = new SQLiteConnection(connStr);
}
else if (dbType == DBLangEngine.DatabaseType.dtMySQL)
{
connMySQL = new MySqlConnection(connStr);
}
else if (dbType == DBLangEngine.DatabaseType.dtPostgreSQL)
{
connPostgreSQL = new NpgsqlConnection(connStr);
}
else if (dbType == DBLangEngine.DatabaseType.dtMSSQL)
{
connMSSQL = new SqlConnection(connStr);
}
if (dbType == DBLangEngine.DatabaseType.dtSQLite)
{
connSQLite.Open();
culture = new Culture(ref connSQLite);
}
else if (dbType == DBLangEngine.DatabaseType.dtMySQL)
{
connMySQL.Open();
culture = new Culture(ref connMySQL);
}
else if (dbType == DBLangEngine.DatabaseType.dtPostgreSQL)
{
connPostgreSQL.Open();
culture = new Culture(ref connPostgreSQL);
}
else if (dbType == DBLangEngine.DatabaseType.dtMSSQL)
{
connMSSQL.Open();
culture = new Culture(ref connMSSQL);
}
}
catch (Exception ex)
{
MessageBox.Show("Unable to connect to database ('" + ex.Message + "').", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
SQLs.SetSchema(dbType, schema);
Text = "DBLangVersion [" + captionStr + "]";
mnuSave.Enabled = true;
mnuAddFromCulture.Enabled = true;
mnuRemoveUnused.Enabled = true;
cmbCulture.Enabled = true;
mnuAddFomEN_US.Enabled = false;
mnuSelectCurrentCulture.Enabled = true;
mnuDumpData.Enabled = true;
mnuExportDatabase.Enabled = dbType == DBLangEngine.DatabaseType.dtSQLite;
cmbCulture.Items.AddRange(culture.Cultures.ToArray());
for (int i = 0; i < cmbCulture.Items.Count; i++)
{
if ((cmbCulture.Items[i] as Culture).LCID == 1033)
{
cmbCulture.SelectedIndex = i;
}
cmbCulture.AutoCompleteCustomSource.Add((cmbCulture.Items[i] as Culture).NativeName);
}
mnuSelectSomeCulture.Enabled = true;
ListCulturesMenu();
}
}
