private static void UpdateDataTableBySQL(DataBase dataBase, DataGridView dataView)
{
try
{
DataTable dataTable = (DataTable)dataView.Tag;
if (null == dataTable) return;
string table = dataTable.TableName;
string columns = "(";
for (int i = 0; i < dataTable.Columns.Count; i++)
{
columns += dataTable.Columns[i].ColumnName;
if (i < dataTable.Columns.Count - 1) columns += ",";
}
columns += ")";
string command = "BEGIN TRANSACTION;";
command += "DELETE FROM "+table+";";
foreach (DataGridViewRow row in dataView.Rows)
{
string rowCommand = "INSERT INTO " + table + columns + "VALUES(";
bool validRow = true;
for (int i = 0; i < row.Cells.Count; i++)
{
if (null == row.Cells[i].Value)
{
validRow = false;
break;
}
rowCommand += "'" + row.Cells[i].Value.ToString() + "'";
if (i < row.Cells.Count - 1) rowCommand += ",";
}
rowCommand += ");";
if (validRow) command += rowCommand;
}
command += "COMMIT;";
Command(dataBase.name, command);
}
catch (Exception ex)
{
Utilities.OnError(Utilities.GetCurrentMethod(), ex);
}
}
private static void UpdateDataTable(DataBase dataBase, DataGridView dataView)
{
DataTable dataTable = (DataTable)dataView.Tag;
if (null == dataTable) return;
try
{
switch (dataBase.dbType)
{
case DataBase.DBType.SQLite:
{
dataBase.commandSQLite = new SQLiteCommandBuilder(dataBase.adapterSQLite).GetUpdateCommand();
dataBase.adapterSQLite.Update(dataTable);
}
break;
case DataBase.DBType.MySql:
{
dataBase.commandMySql = new MySqlCommandBuilder(dataBase.adapterMySql).GetUpdateCommand();
dataBase.adapterMySql.Update(dataTable);
}
break;
case DataBase.DBType.SqlServer:
{
dataBase.commandSqlServer = new SqlCommandBuilder(dataBase.adapterSqlServer).GetUpdateCommand();
dataBase.adapterSqlServer.Update(dataTable);
}
break;
case DataBase.DBType.OleDb:
{
dataBase.commandOleDb = new OleDbCommandBuilder(dataBase.adapterOleDb).GetUpdateCommand();
dataBase.adapterOleDb.Update(dataTable);
}
break;
case DataBase.DBType.Odbc:
{
dataBase.commandOdbc = new OdbcCommandBuilder(dataBase.adapterOdbc).GetUpdateCommand();
dataBase.adapterOdbc.Update(dataTable);
}
break;
}
}
catch
{
UpdateDataTableBySQL(dataBase, dataView);
}
}
/// <summary>
/// Connect to a SqlServer database.
/// This must be called before any SQL methods.
/// The SqlServer service must be running and database with credentials already created, if in doubt use SQLite.
/// </summary>
/// <param name="server">The SqlServer server (e.g. "(local)\SQLEXPRESS").</param>
/// <param name="database">The SqlServer database name.</param>
/// <returns>A label to identify the database.</returns>
public static Primitive ConnectSqlServer(Primitive server, Primitive database)
{
try
{
ExtractDll();
DataBase dataBase = GetDataBase(database, false);
if (null == dataBase)
{
dataBase = new DataBase(database, NextID());
dataBase.ConnectSqlServer(server);
}
dataBases.Add(dataBase);
return dataBase.name;
}
catch (Exception ex)
{
Utilities.OnError(Utilities.GetCurrentMethod(), ex);
}
return "";
}
private static DataTable GetDataTable(DataBase dataBase, string query, DataTable dataTable)
{
if (null == dataTable) dataTable = new DataTable();
dataTable.Clear();
switch (dataBase.dbType)
{
case DataBase.DBType.SQLite:
{
dataBase.commandSQLite.CommandText = query;
dataBase.adapterSQLite = new SQLiteDataAdapter(dataBase.commandSQLite);
dataBase.adapterSQLite.Fill(dataTable);
}
break;
case DataBase.DBType.MySql:
{
dataBase.commandMySql.CommandText = query;
dataBase.adapterMySql = new MySqlDataAdapter(dataBase.commandMySql);
dataBase.adapterMySql.Fill(dataTable);
}
break;
case DataBase.DBType.SqlServer:
{
dataBase.commandSqlServer.CommandText = query;
dataBase.adapterSqlServer = new SqlDataAdapter(dataBase.commandSqlServer);
dataBase.adapterSqlServer.Fill(dataTable);
}
break;
case DataBase.DBType.OleDb:
{
dataBase.commandOleDb.CommandText = query;
dataBase.adapterOleDb = new OleDbDataAdapter(dataBase.commandOleDb);
dataBase.adapterOleDb.Fill(dataTable);
}
break;
case DataBase.DBType.Odbc:
{
dataBase.commandOdbc.CommandText = query;
dataBase.adapterOdbc = new OdbcDataAdapter(dataBase.commandOdbc);
dataBase.adapterOdbc.Fill(dataTable);
}
break;
}
return dataTable;
}
/// <summary>
/// Connect to an Odbc driver connected database.
/// This must be called before any SQL methods.
/// The Odbc service must be running and database with credentials already created, if in doubt use SQLite.
/// </summary>
/// <param name="driver">The Odbc driver (e.g. "{MySQL ODBC 3.51 Driver}").</param>
/// <param name="server">The Odbc server (e.g. "localhost").</param>
/// <param name="port">The Odbc port number.</param>
/// <param name="user">The Odbc user name.</param>
/// <param name="password">The Odbc user password.</param>
/// <param name="option">The Odbc option number to control the Odbc connection (e.g. 0 or 3).</param>
/// <param name="database">The Odbc database name.</param>
/// <returns>A label to identify the database.</returns>
public static Primitive ConnectOdbc(Primitive driver, Primitive server, Primitive port, Primitive user, Primitive password, Primitive option, Primitive database)
{
try
{
ExtractDll();
DataBase dataBase = GetDataBase(database, false);
if (null == dataBase)
{
dataBase = new DataBase(database, NextID());
dataBase.ConnectOdbc(driver, server, port, user, password, option);
}
dataBases.Add(dataBase);
return dataBase.name;
}
catch (Exception ex)
{
Utilities.OnError(Utilities.GetCurrentMethod(), ex);
}
return "";
}