Ejemplo n.º 1
0
        // 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());
        }
Ejemplo n.º 2
0
        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);
        }
Ejemplo n.º 3
0
        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);
        }
Ejemplo n.º 4
0
 public string getSqlCommandWithDataTable(excelDataView xdv, string sqlCommandPattern)
 {
     return getSqlCommandWithDataTable(xdv, sqlCommandPattern, false);
 }
Ejemplo n.º 5
0
 public object Clone()
 {
     excelDataView _clone = new excelDataView(this.dataLayout, this.LayoutCollection);
     _clone.MenuHandler = this.MenuHandler;
     return _clone;
 }