// get all rows command
public string getSqlCommandWithDataTable(excelDataView xdv, string sqlCommandPattern, bool useSelectedRows, string uploadMode)
{
DataTable dt = xdv.IndependentDataLayout;
string dbTableName = dt.TableName;
// command data
List<string> sqlLines = new List<string>();
// get repalced columns
Dictionary<string, string> filedNameExchange = ApplicationConfiguration.Instance.GetTableDictValueByPath("mappingConfiguration.TableField", "ColumnSource", "ColumnDB");
// get replaced tablename
Dictionary<string, string> tableNameExchange = ApplicationConfiguration.Instance.GetTableDictValueByPath("mappingConfiguration.TableName", "dataGridViewTextBoxColumn1", "dataGridViewTextBoxColumn2");
// get indexes
Dictionary<string, string> tableIndexesAdd = ApplicationConfiguration.Instance.GetTableDictValueByPath("mappingConfiguration.Indexes", "ColumnTableOwner", "ColumnIdxName");
// if table page has to be chnaged --- replace it
if (tableNameExchange.ContainsKey(dbTableName) && tableNameExchange[dbTableName] != string.Empty)
dbTableName = tableNameExchange[dbTableName];
if (dbTableName == string.Empty)
return string.Empty;
if (ApplicationConfiguration.Instance.GetValueByPath<bool>("formatConfiguration.alwaysTruncate"))
sqlLines.Add(string.Format("TRUNCATE {0};", dbTableName));
// loop by columns (fields)
List<string> columns = new List<string>();
string columnName = string.Empty;
// get new column names
for (int j = 0; j < dt.Columns.Count; j++)
{
columnName = dt.Columns[j].ColumnName;
if (filedNameExchange.ContainsKey(dt.Columns[j].ColumnName))
columnName = filedNameExchange[dt.Columns[j].ColumnName];
if (columns.Contains(columnName))
continue;
if (columnName != "ST")
columns.Add(columnName);
}
// add or detect primary data column
bool indexWasAdded = false;
bool indexIsManual = false;
string dataRowIndexName = ApplicationConfiguration.Instance.GetValueByPath<string>("mappingConfiguration.defaultIndexFiledName");
// use id as row index
if (ApplicationConfiguration.Instance.GetValueByPath<bool>("formatConfiguration.useRecordIdAsRowIndex"))
columns.Add(ApplicationConfiguration.Instance.GetValueByPath<string>("mappingConfiguration.defaultIndexFiledName"));
else
// try to add custom index
if (ApplicationConfiguration.Instance.GetValueByPath<bool>("mappingConfiguration.useCustomIndexes") && tableIndexesAdd.ContainsKey(dt.TableName))
{
if (columns.Contains(tableIndexesAdd[dt.TableName]))
indexIsManual = true;
else
{
columns.Add(tableIndexesAdd[dt.TableName]);
indexWasAdded = true;
}
dataRowIndexName = tableIndexesAdd[dt.TableName];
}
// column formatting
// string.Format(Properties.Settings.Default.dataFormatColumnQuote, tableIndexesAdd[dt.TableName])
columns = Array.ConvertAll<string, string>(columns.ToArray(), ci => string.Format(ApplicationConfiguration.Instance.GetValueByPath<string>("formatConfiguration.columnFormat"), ci)).ToList<string>();
// sql params
string sqlCommand = string.Empty;
// loop by rows (values)
List<object> dataValues = new List<object>();
int jv = 0;
Dictionary<int, DataRow> drwColl = new Dictionary<int, DataRow>();
// collect only selected rows
if (useSelectedRows)
{
DataRowView drv = null;
foreach (DataGridViewRow dgvRow in xdv.getViewSelected())
{
drv = (DataRowView)dgvRow.DataBoundItem;
drwColl.Add(dgvRow.Index, drv.Row);
}
}//dt.Rows.Cast<int, DataRow>().Select(xr=>xr x=>x)
else
{
int dxidx = 0;
drwColl = dt.Rows.Cast<DataRow>().Select(x => x).ToDictionary<DataRow, int>(x => dxidx++);
}
// getting linked values for this list
Hashtable valueLinker = new Hashtable();
if (ApplicationConfiguration.Instance.GetValueByPath<Hashtable>("mappingConfiguration.TableValue") != null)
valueLinker = ApplicationConfiguration.Instance.GetTableHashValueByPath("mappingConfiguration.TableValue");
DataRow record = null;
int indexNo = 0;
foreach (KeyValuePair<int, DataRow> rec in drwColl)
{
// use record index as sql command index
if (!indexIsManual)
if (ApplicationConfiguration.Instance.GetValueByPath<bool>("formatConfiguration.useRecordIdAsRowIndex"))
indexNo++;
else
indexNo = rec.Key + 1;
dataValues.Clear();
// record
record = rec.Value;
// check if all items are not empty
if (this.IsRowEmpty(record.ItemArray))
continue;
// value link
this.getLinkedValue(xdv.LayoutCollection, valueLinker, ref record);
// escape each value
dataValues.AddRange(this.getValuesWithDefault(rec.Value));
// removing last state column
if (record.Table.Columns.Contains("ST"))
dataValues.Remove(dataValues.Last());
// use id as row index
if (!indexIsManual && ApplicationConfiguration.Instance.GetValueByPath<bool>("formatConfiguration.useRecordIdAsRowIndex") && !indexWasAdded)
dataValues.Insert(0, indexNo);
// data value formatting
dataValues = Array.ConvertAll<object, object>(dataValues.ToArray(), k => string.Format(ApplicationConfiguration.Instance.GetValueByPath<string>("formatConfiguration.valueFormat"), k)).ToList<object>();
// manual index value
object dataIndex = indexNo;
if (!ApplicationConfiguration.Instance.GetValueByPath<bool>("formatConfiguration.useRecordIdAsRowIndex") && indexIsManual)
for (int i = 0; i < columns.Count; i++)
if (columns[i].Contains(dataRowIndexName))
{
dataIndex = getNumer(dataValues[i].ToString());
}
// this data is new
List<string> _tmpCol;// = columns.ToList();
List<object> _tmpDat;// = dataValues.ToList();
getCustomTableFields(dt.TableName, out _tmpCol, out _tmpDat, columns, dataValues);
// creating sql command
switch (uploadMode)
{
case corelib.UPLOAD_MODE_DELETE:
{
// 0 - db name
// 1 - idx
sqlLines.Add(string.Format(sqlCommandPattern, dbTableName, dataIndex, dataRowIndexName));
break;
}
case corelib.UPLOAD_MODE_INSERT:
{
// 0 - db name
// 1 - columns
// 2 - values
// 3 - idx
// this data is new
//List<string> _tmpCol;// = columns.ToList();
//List<object> _tmpDat;// = dataValues.ToList();
/*
_tmpCol.Add("ADATECREATE");
_tmpCol.Add("ADATEUPDATE");
_tmpDat.Add("'" + DateTime.Now.ToString() + "'");
_tmpDat.Add("'" + DateTime.Now.ToString() + "'");
*/
sqlLines.Add(string.Format(sqlCommandPattern, dbTableName, string.Join(", ", _tmpCol.ToArray()), string.Join<object>(", ", _tmpDat), dataIndex));
break;
}
case corelib.UPLOAD_MODE_UPDATE:
{
// 0 - db name
// 1 - update fields
// 2 - idx
int index = 0;
List<string> upItems = new List<string>();
//List<string> tmpCol = columns.ToList();
_tmpCol.Remove('`' + dataRowIndexName + '`');
//List<object> tmpDv = dataValues.ToList();
_tmpDat.RemoveAt(0);
// this data exists
//tmpCol.Add("ADATEUPDATE");
//tmpDv.Add("'" + DateTime.Now.ToString() + "'");
upItems = _tmpCol.ConvertAll<string>(
col => (col.Contains("ADATECREATE") ? "-" : string.Format("{0} = {1}", col, _tmpDat[index++]))
);
upItems.RemoveAll(delegate(string currentItem) {
return currentItem == "-";
});
// use data value by id as row index
/*
if (!Properties.Settings.Default.dataFormat_idAsRowIndex && indexWasAdded)
for (int i = 0; i < upItems.Count; i++)
if (upItems[i].Contains(dataRowIndexName))
{
dataIndex = getNumer(upItems[i]);
}
*/
// indexNo
if (upItems.Count == 1)
sqlLines.Add(string.Format(sqlCommandPattern, dbTableName, upItems[0], dataIndex, dataRowIndexName));
else
sqlLines.Add(string.Format(sqlCommandPattern, dbTableName, string.Join(", ", upItems.ToArray()), dataIndex, dataRowIndexName));
break;
}
}
}
sqlLines.Insert(0, "SET NAMES cp1251;");
return string.Join(Environment.NewLine, sqlLines.ToArray());
}
public string getSqlCommandWithDataTable(excelDataView xdv, string sqlCommandPattern, bool useSelectedRows)
{
string uploadMode = string.Empty;
if (ApplicationConfiguration.Instance.GetValueByPath<string>("commandsConfiguration.update") == sqlCommandPattern)
uploadMode = corelib.UPLOAD_MODE_UPDATE;
if (ApplicationConfiguration.Instance.GetValueByPath<string>("commandsConfiguration.insert") == sqlCommandPattern)
uploadMode = corelib.UPLOAD_MODE_INSERT;
if (ApplicationConfiguration.Instance.GetValueByPath<string>("commandsConfiguration.delete") == sqlCommandPattern)
uploadMode = corelib.UPLOAD_MODE_DELETE;
return getSqlCommandWithDataTable(xdv, sqlCommandPattern, useSelectedRows, uploadMode);
}
public string getSqlCommandWithDataTable(excelDataView xdv)
{
string sqlCommandPattern = string.Empty;
if (ApplicationConfiguration.Instance.GetValueByPath<string>("formatConfiguration.uploadMode") == corelib.UPLOAD_MODE_INSERT)
sqlCommandPattern = "INSERT INTO {0} ({1}) VALUES ({2});";
if (ApplicationConfiguration.Instance.GetValueByPath<string>("formatConfiguration.uploadMode") == corelib.UPLOAD_MODE_UPDATE)
sqlCommandPattern = "UPDATE {0} SET {1} WHERE {0}.{3} = {2};";
if (ApplicationConfiguration.Instance.GetValueByPath<string>("formatConfiguration.uploadMode") == corelib.UPLOAD_MODE_DELETE)
sqlCommandPattern = "DELETE FROM {0} WHERE {0}.{3} = {1};";
return getSqlCommandWithDataTable(xdv, sqlCommandPattern);
}
public string getSqlCommandWithDataTable(excelDataView xdv, string sqlCommandPattern)
{
return getSqlCommandWithDataTable(xdv, sqlCommandPattern, false);
}
public object Clone()
{
excelDataView _clone = new excelDataView(this.dataLayout, this.LayoutCollection);
_clone.MenuHandler = this.MenuHandler;
return _clone;
}