/// <summary> /// Метод для сохранения стиля /// </summary> public void SaveStyle(object parameter = null) { using (SqlWork sqlWork = new SqlWork(_connect)) { sqlWork.BeginTransaction(); sqlWork.sql = String.Format(@"SELECT EXISTS (SELECT true FROM sys_scheme.style_object_info WHERE id_table={0} AND id_obj={1})", _table.Id, _pkAttribute.Value); bool exists = sqlWork.ExecuteScalar <Boolean>(); if (!exists) { sqlWork.sql = String.Format("INSERT INTO sys_scheme.style_object_info (id_table, id_obj) VALUES ({0}, {1})", _table.Id, _pkAttribute.Value); sqlWork.ExecuteNonQuery(); } sqlWork.sql = String.Format(@"UPDATE sys_scheme.style_object_info SET fontname = '{0}', fontcolor = {1}, fontframecolor = {2}, fontsize = {3}, brushbgcolor = {4}, brushfgcolor = {5}, brushstyle = {6}, brushhatch = {7}, pencolor = {8}, pentype = {9}, penwidth = {10}, symbol = {11} WHERE id_table = {12} AND id_obj = {13}", Style.FontName, Style.FontColor, Style.FontFrameColor, Style.FontSize, Style.BrushBgColor, Style.BrushFgColor, Style.BrushStyle, Style.BrushHatch, Style.PenColor, Style.PenType, Style.PenWidth, Style.Symbol, _table.Id, _pkAttribute.Value); sqlWork.ExecuteNonQuery(); sqlWork.EndTransaction(); } }
public void SetGroupsOrder(List <itemObjOrdered> newGroupsOrder) { try { SqlWork sqlCmd = new SqlWork(); sqlCmd.BeginTransaction(); for (int i = 0; i < newGroupsOrder.Count; i++) { itemObjOrdered itemObjOrd = newGroupsOrder[i]; int objId = itemObjOrd.Id_o; int orderNum = i + 1; sqlCmd.sql = String.Format("UPDATE {0}.{1} SET order_num={2} WHERE id={3}", Program.scheme, "table_groups", orderNum, objId); sqlCmd.ExecuteNonQuery(); } sqlCmd.EndTransaction(); } catch (Exception ex) { Rekod.Classes.workLogFile.writeLogFile(Rekod.Properties.Resources.GF_OrderGroup + " \n" + ex.Message, false, true); } }
/// <summary> /// Сохранение /// </summary> public void Save(object parameter = null) { if (CanSave()) { cti.ThreadProgress.ShowWait(); try { if (IsRegistered) { using (SqlWork sqlWork = new SqlWork(true)) { sqlWork.sql = String.Format(@"UPDATE sys_scheme.table_info SET name_map = @name_map, photo = {1}, lablefiled = '{2}', pk_fileld = '{3}', geom_field = '{4}' WHERE id = {5};", Text, HasFiles, (LabelField != null) ? LabelField.Name : "", (PkField != null) ? PkField.Name : "", (GeomField != null) ? GeomField.Name : "", Id); sqlWork.ExecuteNonQuery(new List <Params>() { new Params("@name_map", Text, NpgsqlDbType.Varchar) }); } } else { using (SqlWork sqlWork = new SqlWork(true)) { sqlWork.BeginTransaction(); sqlWork.sql = "SELECT nextval('sys_scheme.table_info_id_seq')::integer as val"; int idTable = sqlWork.ExecuteScalar <Int32>(); sqlWork.CloseReader(); sqlWork.sql = String.Format(@"INSERT INTO sys_scheme.table_info (id, name_db, name_map, geom_type, type, map_style, scheme_name, photo, pk_fileld, lablefiled, geom_field) VALUES ({0}, '{1}',@name_map, {3}, {4}, {5}, '{6}', {7}, '{8}', '{9}', '{10}')", idTable, Name, Text, Convert.ToInt32(GeomType), Convert.ToInt32(TableType), IsMapStyle, ParentScheme.SchemeName, HasFiles, PkField.Name, (LabelField != null) ? LabelField.Name : "", (GeomField != null) ? GeomField.Name : ""); sqlWork.ExecuteNonQuery(new List <Params>() { new Params("@name_map", Text, NpgsqlDbType.Varchar) }); if (IsMapStyle && (TableType == ERTableType.Interval || TableType == ERTableType.Catalog)) { bool exists_style = false; sqlWork.sql = String.Format("SELECT * FROM {0}.{1} LIMIT 1", ParentScheme.SchemeName, Name); sqlWork.ExecuteReader(); if (sqlWork.CanRead()) { for (int i = 0; sqlWork.GetFiealdCount() > i; i++) { if (sqlWork.GetFieldName(i) == "exists_style") { exists_style = true; break; } } } sqlWork.CloseReader(); if (!exists_style) { MessageBoxResult dr = MessageBox.Show( Rekod.Properties.Resources.PgReg_NeedAdditionalFields, Rekod.Properties.Resources.PgReg_StyleCreation, MessageBoxButton.YesNo); if (dr == MessageBoxResult.Yes) { sqlWork.sql = String.Format("ALTER TABLE {0}.{1} " + "ADD exists_style boolean DEFAULT true," + "ADD fontname character varying DEFAULT 'Map Symbols'," + "ADD fontcolor integer DEFAULT 16711680," + "ADD fontframecolor integer DEFAULT 16711680," + "ADD fontsize integer DEFAULT 12," + "ADD symbol integer DEFAULT 35," + "ADD pencolor integer DEFAULT 16711680," + "ADD pentype integer DEFAULT 2," + "ADD penwidth integer DEFAULT 1," + "ADD brushbgcolor bigint DEFAULT 16711680," + "ADD brushfgcolor integer DEFAULT 16711680," + "ADD brushstyle integer DEFAULT 0," + "ADD brushhatch integer DEFAULT 1;", ParentScheme.SchemeName, Name); sqlWork.ExecuteNonQuery(); } } } // Регистрация полей foreach (var f in Fields) { if (f.ToBeRegistered) { if (f.DataType == 0) { f.DataType = ERDataType.Text; } sqlWork.CloseReader(); sqlWork.sql = String.Format( @"INSERT INTO sys_scheme.table_field_info(id_table, name_db, name_map, type_field, visible, name_lable, is_reference, is_interval) VALUES ({0}, '{1}', '{2}', {3}, {4}, @name_lable, {6}, {7});", idTable, f.Name, f.Name, Convert.ToInt32(f.DataType), true, f.Description, false, false); sqlWork.ExecuteNonQuery(new List <Params>() { new Params("@name_lable", f.Description, NpgsqlDbType.Varchar) }); } } sqlWork.CloseReader(); if (HasFiles) { bool exist = false; sqlWork.sql = String.Format("SELECT EXISTS(SELECT * FROM pg_tables WHERE schemaname='{0}' AND tablename='photo_{1}');", ParentScheme.SchemeName, Name); exist = sqlWork.ExecuteScalar <Boolean>(); if (exist) { bool est_v_tabl = false; sqlWork.sql = String.Format("SELECT EXISTS(SELECT id FROM sys_scheme.table_photo_info WHERE id_table = {0});", idTable); est_v_tabl = sqlWork.ExecuteScalar <Boolean>(); if (!est_v_tabl) { sqlWork.sql = String.Format("INSERT INTO sys_scheme.table_photo_info(id_table, photo_table, photo_field, photo_file, id_field_tble)" + " VALUES ({0}, 'photo_{1}', 'id_obj', 'file', '{2}');", idTable, Name, PkField.Name); sqlWork.ExecuteNonQuery(); } } else { sqlWork.sql = String.Format("SELECT sys_scheme.set_photo_table({0}, true)", idTable); sqlWork.ExecuteNonQuery(); } } else { sqlWork.sql = String.Format("DELETE FROM sys_scheme.table_photo_info WHERE id_table = {0};", Id); sqlWork.ExecuteNonQuery(); } sqlWork.sql = String.Format("SELECT sys_scheme.set_table_rights_for_admins({0});", idTable.ToString()); sqlWork.ExecuteNonQuery(); sqlWork.EndTransaction(); IsRegistered = true; foreach (var f in Fields) { if (f.ToBeRegistered) { f.IsRegistered = true; } } Id = idTable; } } if (IsSscActive) { if (SscGroup != null) { Interfaces.tablesInfo tableInfo = new Interfaces.tablesInfo() { idTable = Id, nameDB = Name, nameMap = Text, pkField = PkField.Name, geomFieldName = (GeomField == null) ? null : GeomField.Name, lableFieldName = (LabelField == null) ? null : LabelField.Name, TypeGeom = (Interfaces.TypeGeometry)GeomType }; tableInfo.ListField = new List <Interfaces.fieldInfo>(); foreach (var f in Fields.Where(p => p.IsRegistered)) { tableInfo.ListField.Add(new Interfaces.fieldInfo() { nameDB = Name, nameMap = Text }); } MapAdmin.RegisterTable(tableInfo, SscGroup); } } cti.ThreadProgress.Close(); MessageBox.Show(Rekod.Properties.Resources.PgReg_ChangesSaved); } catch (Exception ex) { cti.ThreadProgress.Close(); MessageBox.Show(ex.Message); } } }
private void load(List <FieldMatch> fields) { if (fields.Count == 0) { return; } var parameters = new List <Interfaces.Params>(); var insertSql = "insert into {0}.{1} ("; var selectSql = "select "; string valuesStr = ") values ("; DataTable datas = new DataTable(); foreach (var f in fields) { insertSql += "\"" + f.Dest.Name + "\","; valuesStr += ":" + f.Dest.Name + ","; selectSql += f.Src + ","; var p = new Interfaces.Params() { paramName = f.Dest.Name }; ImporterHelper.SetParamType(f.Dest, p); parameters.Add(p); datas.Columns.Add(f.Src); } selectSql = selectSql.TrimEnd(','); insertSql = insertSql.TrimEnd(','); valuesStr = valuesStr.TrimEnd(','); insertSql += valuesStr + ")"; selectSql += string.Format(" FROM [{0}#dbf]", Name); //инициируем считывание var reader = (new OleDbCommand(selectSql, Connection)).ExecuteReader(); using (SqlWork sw = new SqlWork(_pgConnect, true)) { sw.BeginTransaction(); sw.sql = string.Format(insertSql, _pgTable.SchemeName, _pgTable.Name); for (int i = 0, report = 0; reader.Read(); report++) { //добавляем в таблицу List <object> colData = new List <object>(); for (int j = 0; j < datas.Columns.Count; j++) { colData.Add(reader[j]); } datas.Rows.Add(colData.ToArray()); i++; if (i >= _rowsInSect) { //кладем в базу for (int j = 0; j < datas.Rows.Count; j++) { for (int k = 0; k < datas.Columns.Count; k++) { ImporterHelper.SetParamValue(parameters[k], datas.Rows[j][k]); } sw.ExecuteNonQuery(parameters.ToArray()); } i = 0; datas.Clear(); } ReportProgress(0, report); } reader.Close(); sw.EndTransaction(); } }
private void load(List <FieldMatch> fields) { if (fields.Count == 0 && _loadGeom == false) { return; } // размер блока для загрузки в БД int rowsInSect = Math.Min(100, Math.Max(1, RowsCount / 10)); // список параметров для загрузки в БД List <Interfaces.Params>[] paramList = new List <Interfaces.Params> [rowsInSect]; // названия колонок таблицы List <String> columns = new List <string>(); string wktTo = GetSRText(_pgTable.Srid); string wktFrom = GetSRText(_srid); //инициируем считывание _shpWork.StartRead(); List <string> errors = new List <string>(); using (var sw = new SqlWork(_connect, true)) { sw.BeginTransaction(); sw.sql = FormQuerySQL(fields, ref paramList, ref columns); for (int i = 0, report = 0; _shpWork.Read() && report < RowsCount; report++) { //добавляем в таблицу for (int j = 0; j < columns.Count; j++) { try { if (_loadGeom == true && j == columns.Count - 1) { ImporterHelper.SetParamValue( paramList[i][j], _shpWork.TransformedCurrentGeometry(wktTo, wktFrom)); } else { ImporterHelper.SetParamValue( paramList[i][j], _shpWork[columns[j]]); } } catch (Exception e) { if (!errors.Contains(e.Message)) { errors.Add(e.Message); } } } if (++i >= rowsInSect || report == RowsCount - 1) { //кладем в базу for (int k = 0; k < i; k++) { try { sw.ExecuteNonQuery(paramList[k].ToArray()); } catch (Exception e) { if (!errors.Contains(e.Message)) { errors.Add(e.Message); } } } i = 0; ReportProgress(0, report); } } _shpWork.EndRead(); sw.EndTransaction(); if (errors.Count > 0) { throw new Exception(String.Join(Environment.NewLine, errors)); } } }
private void load(List <FieldMatch> fields, string rngString) { if (fields.Count == 0) { return; } var parameters = new List <Interfaces.Params>(); var insertSql = "insert into {0}.{1} ("; string valuesStr = ") values ("; foreach (var f in fields) { insertSql += "\"" + f.Dest.Name + "\","; valuesStr += ":" + f.Dest.Name + ","; var p = new Interfaces.Params() { paramName = f.Dest.Name }; ImporterHelper.SetParamType(f.Dest, p); parameters.Add(p); } insertSql = insertSql.TrimEnd(','); valuesStr = valuesStr.TrimEnd(','); insertSql += valuesStr + ")"; //читаем excel String sheetString = rngString.Split('$')[0]; String rangeString = rngString.Split('$')[1]; var excelAppl = new Excel.Application() { Visible = false }; var workbook = excelAppl.Workbooks.Open(_inputFile.FullName); Excel.Worksheet workSheet = (Excel.Worksheet)(workbook.Sheets.Item[sheetString]); Excel.Range workRange = workSheet.get_Range(rangeString); // Где на странице Excel находятся данные int lastRow = workRange.Row + workRange.Rows.Count - 1; int firstRow = workRange.Row; int lastColumn = workRange.Column + workRange.Columns.Count - 1; int firstColumn = workRange.Column; // Получаются данные из excel в виде массива объектов var excValues = (object[, ])(workRange.Value); workbook.Close(); // Сколько строк и сколько столбцов _rowsCount = excValues.GetLength(0) - 1; //первая строка заголовки var colsCount = excValues.GetLength(1); excelAppl.Quit(); DataTable datas = new DataTable(); for (int j = 1; j <= colsCount; j++) { datas.Columns.Add(excValues[1, j].ToString()); } using (SqlWork sw = new SqlWork(_pgConnect, true)) { sw.BeginTransaction(); sw.sql = string.Format(insertSql, _pgTable.SchemeName, _pgTable.Name); for (int i = 2, report = 0; i <= _rowsCount + 1; i++) { //добавляем в таблицу datas.Rows.Add(getRow(excValues, i, colsCount)); report++; if (report >= _rowsInSect) { //убираем ненужные колонки List <object> cols2delete = new List <object>(); for (int j = 0; j < datas.Columns.Count; j++) { if (fields.Find(w => w.Src == datas.Columns[j].Caption) == null) { cols2delete.Add(datas.Columns[j]); } } foreach (var c in cols2delete) { datas.Columns.Remove((DataColumn)c); } //кладем в базу for (int j = 0; j < datas.Rows.Count; j++) { for (int k = 0; k < datas.Columns.Count; k++) { ImporterHelper.SetParamValue(parameters[k], datas.Rows[j][k]); } sw.ExecuteNonQuery(parameters.ToArray()); } report = 0; datas = new DataTable(); for (int j = 1; j <= colsCount; j++) { datas.Columns.Add(excValues[1, j].ToString()); } } ReportProgress(0, i - 2); } sw.EndTransaction(); } }
/// <summary> /// Создание новой схемы /// </summary> public void CreateNewScheme(object parameter = null) { if (CanCreateNewScheme(parameter)) { try { String newSchemeName = parameter.ToString().Trim().ToLower(); if (newSchemeName.Length > 0) { if (newSchemeName[0] >= 'a' && newSchemeName[0] <= 'z') { bool valid = true; for (int i = 1; i < newSchemeName.Length; i++) { if ((newSchemeName[i] >= 'a' && newSchemeName[i] <= 'z') || (newSchemeName[i] >= '0' && newSchemeName[i] <= '9') || newSchemeName[i] == '_') { } else { valid = false; } } if (!valid) { MessageBox.Show("В названии схемы могут присутствовать только латинские символы, цифры и знак подчеркивания"); } else { using (SqlWork sqlWork = new SqlWork(true)) { sqlWork.BeginTransaction(); sqlWork.sql = String.Format(@"CREATE SCHEMA {0} " + "AUTHORIZATION postgres; " + "GRANT ALL ON SCHEMA public TO postgres; " + "GRANT ALL ON SCHEMA public TO public; " + "COMMENT ON SCHEMA public IS 'scheme created by the system';", newSchemeName); sqlWork.ExecuteNonQuery(); sqlWork.sql = String.Format("INSERT INTO sys_scheme.table_schems(\"name\") VALUES ('{0}');", newSchemeName); sqlWork.ExecuteNonQuery(); sqlWork.EndTransaction(); } ReloadSchems(); MessageBox.Show("Новая схема создана", "Создание новое схемы", MessageBoxButton.OK, MessageBoxImage.Information); } } else { MessageBox.Show("Первый символ в названии схемы должен быть латинской буквой"); } } else { MessageBox.Show("Вы должны ввести хотя бы один символ в название схемы"); } } catch (Exception ex) { MessageBox.Show(ex.Message); } } }