public void Add(Insert insertCommand) { if (this.InsertCommands.Count > 0) { if (this.Tables != insertCommand.Tables) throw new ArgumentException("qGen: BulkInsert requiere que todas las inserciones sean en la misma tabla y con los mismos campos"); } else { this.Tables = insertCommand.Tables; this.Fields = insertCommand.Fields; this.DataBase = insertCommand.DataBase; this.WhereClause = insertCommand.WhereClause; this.m_Mode = insertCommand.m_Mode; } this.InsertCommands.Add(insertCommand); }
public void Add(Insert insertCommand) { if (this.InsertCommands.Count > 0) { if (this.Tables != insertCommand.Tables) { throw new ArgumentException("qGen: BulkInsert requiere que todas las inserciones sean en la misma tabla y con los mismos campos"); } } else { this.Tables = insertCommand.Tables; this.Fields = insertCommand.Fields; this.DataBase = insertCommand.DataBase; this.WhereClause = insertCommand.WhereClause; this.m_Mode = insertCommand.m_Mode; } this.InsertCommands.Add(insertCommand); }
public void Add(Insert insertCommand) { if (this.InsertCommands.Count > 0) { if (this.Tables.Count != insertCommand.Tables.Count) { throw new ArgumentException("qGen: BulkInsert requires all Insert to be on the same tables"); } if (this.Tables[0].PrefixAndName != insertCommand.Tables[0].PrefixAndName) { // TODO: check all tables throw new ArgumentException("qGen: BulkInsert requires all Insert to be on the same table"); } } else { this.Tables = insertCommand.Tables; //this.Fields = insertCommand.Fields.GetFieldNames(); //this.WhereClause = insertCommand.WhereClause; } this.InsertCommands.Add(insertCommand); }
public System.Data.IDbCommand SetupDbCommand(Insert insertCommand, IConnection connection) { var DbCommand = connection.DbConnection.CreateCommand(); DbCommand.CommandText = @"INSERT INTO " + this.SqlText(insertCommand.Tables) + " " + this.SqlText(insertCommand.ColumnValues, ColumnValueFormatStyles.InsertStyle, true, 0); if (insertCommand.WhereClause != null) { DbCommand.CommandText += " WHERE " + this.SqlText(insertCommand.WhereClause); } if (insertCommand.OnDuplicateKeyUpdate) { var UpdateClause = new StringBuilder(); foreach (IColumnValue ThisField in insertCommand.ColumnValues) { if (UpdateClause.Length == 0) { UpdateClause.Append(" ON DUPLICATE KEY UPDATE "); } else { UpdateClause.Append(", "); } UpdateClause.Append(this.SqlText(ThisField.ColumnIdentifier) + @"=VALUES(" + this.SqlText(ThisField.ColumnIdentifier) + @")"); } DbCommand.CommandText += UpdateClause.ToString(); } this.PopulateParameters(connection, DbCommand.Parameters, insertCommand.ColumnValues, 0); return(DbCommand); }
public void Restore(string backupName) { string Carpeta = backupName + System.IO.Path.DirectorySeparatorChar; Lfx.Environment.Folders.EnsurePathExists(this.BackupPath); if (Carpeta != null && Carpeta.Length > 0 && System.IO.Directory.Exists(this.BackupPath + Carpeta)) { bool UsandoArchivoComprimido = false; Lfx.Types.OperationProgress Progreso = new Lfx.Types.OperationProgress("Restaurando copia de seguridad", "Este proceso va a demorar varios minutos. Por favor no lo interrumpa"); Progreso.Modal = true; /* Progreso.ChangeStatus("Descomprimiendo"); * // Descomprimir backup si está comprimido * if (System.IO.File.Exists(BackupPath + Carpeta + "backup.7z")) { * Lfx.FileFormats.Compression.Archive ArchivoComprimido = new Lfx.FileFormats.Compression.Archive(BackupPath + Carpeta + "backup.7z"); * ArchivoComprimido.ExtractAll(BackupPath + Carpeta); * UsandoArchivoComprimido = true; * } */ Progreso.ChangeStatus("Eliminando datos actuales"); using (Lfx.Data.IConnection ConnRestaurar = Lfx.Workspace.Master.GetNewConnection("Restauración de copia de seguridad") as Lfx.Data.IConnection) { Progreso.ChangeStatus("Acomodando estructuras"); Lfx.Workspace.Master.Structure.TagList.Clear(); Lfx.Workspace.Master.Structure.LoadFromFile(this.BackupPath + Carpeta + "dbstruct.xml"); //Lfx.Workspace.Master.CheckAndUpdateDatabaseVersion(true, true); using (BackupReader Lector = new BackupReader(this.BackupPath + Carpeta + "dbdata.lbd")) using (IDbTransaction Trans = ConnRestaurar.BeginTransaction()) { ConnRestaurar.EnableConstraints(false); Progreso.ChangeStatus("Incorporando tablas de datos"); Progreso.Max = (int)(Lector.Length / 1024); string TablaActual = null; string[] ListaCampos = null; object[] ValoresCampos = null; int CampoActual = 0; bool EndTable = false; qGen.BuilkInsert Insertador = new qGen.BuilkInsert(); do { string Comando = Lector.ReadString(4); switch (Comando) { case ":TBL": TablaActual = Lector.ReadPrefixedString4(); string NombreTabla; if (Lfx.Workspace.Master.Structure.Tables.ContainsKey(TablaActual) && Lfx.Workspace.Master.Structure.Tables[TablaActual].Label != null) { NombreTabla = Lfx.Workspace.Master.Structure.Tables[TablaActual].Label; } else { NombreTabla = TablaActual.ToTitleCase(); } EndTable = false; Progreso.ChangeStatus("Cargando " + NombreTabla); qGen.Delete DelCmd = new qGen.Delete(TablaActual); DelCmd.EnableDeleleteWithoutWhere = true; ConnRestaurar.ExecuteNonQuery(DelCmd); break; case ":FDL": ListaCampos = Lector.ReadPrefixedString4().Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries); ValoresCampos = new object[ListaCampos.Length]; CampoActual = 0; break; case ":FLD": ValoresCampos[CampoActual++] = Lector.ReadField(); break; case ".ROW": qGen.Insert Insertar = new qGen.Insert(TablaActual); for (int i = 0; i < ListaCampos.Length; i++) { Insertar.ColumnValues.AddWithValue(ListaCampos[i], ValoresCampos[i]); } Insertador.Add(Insertar); ValoresCampos = new object[ListaCampos.Length]; CampoActual = 0; break; case ":REM": Lector.ReadPrefixedString4(); break; case ".TBL": EndTable = true; break; } if (EndTable || Insertador.Count >= 1000) { if (Insertador.Count > 0) { ConnRestaurar.ExecuteNonQuery(Insertador); } Insertador.Clear(); Progreso.Value = (int)(Lector.Position / 1024); } } while (Lector.Position < Lector.Length); Lector.Close(); if (Lfx.Workspace.Master.MasterConnection.SqlMode == qGen.SqlModes.PostgreSql) { // PostgreSql: Tengo que actualizar las secuencias Progreso.ChangeStatus("Actualizando secuencias"); string PatronSecuencia = @"nextval\(\'(.+)\'(.*)\)"; foreach (string Tabla in Lfx.Data.DatabaseCache.DefaultCache.GetTableNames()) { string OID = ConnRestaurar.FieldString("SELECT c.oid FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid=c.relnamespace WHERE pg_catalog.pg_table_is_visible(c.oid) AND c.relname ~ '^" + Tabla + "$'"); System.Data.DataTable Campos = ConnRestaurar.Select("SELECT a.attname,pg_catalog.format_type(a.atttypid, a.atttypmod),(SELECT substring(d.adsrc for 128) FROM pg_catalog.pg_attrdef d WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef), a.attnotnull, a.attnum FROM pg_catalog.pg_attribute a WHERE a.attrelid = '" + OID + "' AND a.attnum > 0 AND NOT a.attisdropped ORDER BY a.attnum"); foreach (System.Data.DataRow Campo in Campos.Rows) { if (Campo[2] != DBNull.Value && Campo[2] != null) { string DefaultCampo = System.Convert.ToString(Campo[2]); if (Regex.IsMatch(DefaultCampo, PatronSecuencia)) { string NombreCampo = System.Convert.ToString(Campo[0]); foreach (System.Text.RegularExpressions.Match Ocurrencia in Regex.Matches(DefaultCampo, PatronSecuencia)) { string Secuencia = Ocurrencia.Groups[1].ToString(); int MaxId = ConnRestaurar.FieldInt("SELECT MAX(" + NombreCampo + ") FROM " + Tabla) + 1; ConnRestaurar.ExecuteNonQuery("ALTER SEQUENCE " + Secuencia + " RESTART WITH " + MaxId.ToString()); } } } } } } if (System.IO.File.Exists(this.BackupPath + Carpeta + "blobs.lst")) { // Incorporar Blobs Progreso.ChangeStatus("Incorporando imágenes"); System.IO.StreamReader LectorBlobs = new System.IO.StreamReader(this.BackupPath + Carpeta + "blobs.lst", System.Text.Encoding.Default); string InfoImagen = null; do { InfoImagen = LectorBlobs.ReadLine(); if (InfoImagen != null && InfoImagen.Length > 0) { string Tabla = Lfx.Types.Strings.GetNextToken(ref InfoImagen, ","); string Campo = Lfx.Types.Strings.GetNextToken(ref InfoImagen, ","); string CampoId = Lfx.Types.Strings.GetNextToken(ref InfoImagen, ","); string NombreArchivoImagen = Lfx.Types.Strings.GetNextToken(ref InfoImagen, ","); // Guardar blob nuevo qGen.Update ActualizarBlob = new qGen.Update(Tabla); ActualizarBlob.WhereClause = new qGen.Where(Campo, CampoId); System.IO.FileStream ArchivoImagen = new System.IO.FileStream(this.BackupPath + Carpeta + NombreArchivoImagen, System.IO.FileMode.Open, System.IO.FileAccess.Read); byte[] Contenido = new byte[System.Convert.ToInt32(ArchivoImagen.Length) - 1 + 1]; ArchivoImagen.Read(Contenido, 0, System.Convert.ToInt32(ArchivoImagen.Length)); ArchivoImagen.Close(); ActualizarBlob.ColumnValues.AddWithValue(Campo, Contenido); ConnRestaurar.ExecuteNonQuery(ActualizarBlob); } }while (InfoImagen != null); LectorBlobs.Close(); } if (UsandoArchivoComprimido) { Progreso.ChangeStatus("Eliminando archivos temporales"); // Borrar los archivos que descomprim temporalmente System.IO.DirectoryInfo Dir = new System.IO.DirectoryInfo(this.BackupPath + Carpeta); foreach (System.IO.FileInfo DirItem in Dir.GetFiles()) { if (DirItem.Name != "backup.7z" && DirItem.Name != "info.txt") { System.IO.File.Delete(this.BackupPath + Carpeta + DirItem.Name); } } } Progreso.ChangeStatus("Terminando transacción"); Trans.Commit(); } Progreso.End(); } Lfx.Workspace.Master.RunTime.Toast("La copia de seguridad se restauró con éxito. A continuación se va a reiniciar la aplicación.", "Copia Restaurada"); } }
public System.Data.IDbCommand SetupDbCommand(Insert insertCommand, IConnection connection) { var FieldList = new System.Text.StringBuilder(); var ParamList = new System.Text.StringBuilder(); var DbCommand = connection.DbConnection.CreateCommand(); foreach (IColumnValue ThisField in insertCommand.ColumnValues) { if (FieldList.Length == 0) { FieldList.Append(@"""" + ThisField.ColumnName + @""""); } else { FieldList.Append(@", """ + ThisField.ColumnName + @""""); } string FieldParam; if (ThisField.Value is qGen.SqlFunctions) { switch (((qGen.SqlFunctions)(ThisField.Value))) { case SqlFunctions.Now: FieldParam = "NOW()"; break; default: throw new NotImplementedException(); } } else if (ThisField.Value is qGen.SqlExpression) { FieldParam = ThisField.Value.ToString(); } else { if (connection.DbConnection is System.Data.Odbc.OdbcConnection) { FieldParam = "?"; } else { FieldParam = "@" + ThisField.ColumnName; } } if (ParamList.Length == 0) { ParamList.Append(FieldParam); } else { ParamList.Append(", " + FieldParam); } if (FieldParam == "?" || FieldParam.Substring(0, 1) == "@") { var Param = connection.Factory.Driver.GetParameter(); Param.ParameterName = "@" + ThisField.ColumnName; if (ThisField.Value is DbDateTime && ThisField.Value != null) { Param.Value = ((DbDateTime)(ThisField.Value)).Value; } else { Param.Value = ThisField.Value; } if (ThisField.DataType == Lazaro.Orm.ColumnTypes.Blob) { Param.DbType = System.Data.DbType.Binary; } // FIXME: no debería hacer una excepción para OdbcDriver if (connection.Factory.Driver is OdbcDriver && ThisField.DataType == Lazaro.Orm.ColumnTypes.Blob) { ((System.Data.Odbc.OdbcParameter)Param).OdbcType = System.Data.Odbc.OdbcType.VarBinary; } DbCommand.Parameters.Add(Param); } } DbCommand.CommandText += @"INSERT INTO """ + string.Join <string>(",", insertCommand.Tables) + @""" (" + FieldList.ToString() + ") VALUES (" + ParamList.ToString() + ")"; if (insertCommand.OnDuplicateKeyUpdate) { var UpdateClause = new StringBuilder(); foreach (IColumnValue ThisField in insertCommand.ColumnValues) { if (UpdateClause.Length == 0) { UpdateClause.Append(@" ON DUPLICATE KEY UPDATE """ + ThisField.ColumnName + @"""=VALUES(""" + ThisField.ColumnName + @""")"); } else { UpdateClause.Append(@", """ + ThisField.ColumnName + @"""=VALUES(""" + ThisField.ColumnName + @""")"); } } DbCommand.CommandText += UpdateClause.ToString(); } return(DbCommand); }
public string SqlText(Insert insert, bool valuesOnly) { var FieldList = new System.Text.StringBuilder(); var ParamList = new System.Text.StringBuilder(); foreach (IColumnValue ThisField in insert.ColumnValues) { if (FieldList.Length == 0) { FieldList.Append(@"""" + ThisField.ColumnName + @""""); } else { FieldList.Append(@", """ + ThisField.ColumnName + @""""); } string ParamValue; if (ThisField.Value == null || ThisField.Value == DBNull.Value) { ParamValue = "NULL"; } else { var Tipo = ThisField.Value.GetType().Name.Replace("System.", ""); switch (Tipo) { case "SqlFunctions": case "qGen.SqlFunctions": switch (((qGen.SqlFunctions)(ThisField.Value))) { case SqlFunctions.Now: ParamValue = "NOW()"; break; default: throw new NotImplementedException(); } break; case "SqlExpression": case "qGen.SqlExpression": ParamValue = this.EscapeString(ThisField.Value.ToString()); break; case "Lfx.Data.SqlLiteral": ParamValue = ThisField.Value.ToString(); break; case "Lfx.Data.LDateTime": ParamValue = "'" + ((DbDateTime)(ThisField.Value)).Value.ToString(SqlDateTimeFormat) + "'"; break; case "DateTime": ParamValue = "'" + System.Convert.ToDateTime(ThisField.Value).ToString(SqlDateTimeFormat) + "'"; break; case "Single": case "Double": case "Decimal": ParamValue = this.FormatDecimal(System.Convert.ToDecimal(ThisField.Value), 8); break; case "Integer": case "Int16": case "Int32": case "Int64": ParamValue = System.Convert.ToInt32(ThisField.Value).ToString(); break; default: ParamValue = "'" + this.EscapeString(ThisField.Value.ToString()) + "'"; break; } } if (ParamList.Length == 0) { ParamList.Append(ParamValue); } else { ParamList.Append(", " + ParamValue); } } if (valuesOnly) { return("(" + ParamList.ToString() + ")"); } else { var Res = @"INSERT INTO """ + string.Join <string>(",", insert.Tables) + @""" (" + FieldList.ToString() + ") VALUES (" + ParamList.ToString() + ")"; if (insert.OnDuplicateKeyUpdate) { string UpdateClause = null; foreach (IColumnValue ThisField in insert.ColumnValues) { if (UpdateClause == null) { UpdateClause = @" ON DUPLICATE KEY UPDATE """ + ThisField.ColumnName + @"""=VALUES(""" + ThisField.ColumnName + @""")"; } else { UpdateClause += @", """ + ThisField.ColumnName + @"""=VALUES(""" + ThisField.ColumnName + @""")"; } } Res += UpdateClause; } return(Res); } }
public string SqlText(Insert insert) { return(this.SqlText(insert, false)); }