public Author FindOrCreateAuthor(AuthorInfoNode info, IDbTransaction transaction) { string commandText; FbCommand command; Author author; if (info == null) { throw new ArgumentNullException("info"); } this.manager.BeginConnect(); try { commandText = @"SELECT autor.* FROM autor INNER JOIN autor_synonims ON autor_synonims.autorid = autor.autorid WHERE UPPERCASE(autor_synonims.lastname) = UPPERCASE(@lastname) AND UPPERCASE(autor_synonims.firstname) = UPPERCASE(@firstname) AND UPPERCASE(autor_synonims.midname) = UPPERCASE(@midname)"; command = this.connection.CreateCommand(); command.CommandType = CommandType.Text; command.CommandText = commandText; command.Transaction = transaction as FbTransaction; command.Parameters.Add("@firstname", FbDbType.VarChar, 40).Value = info.FirstName; command.Parameters.Add("@midname", FbDbType.VarChar, 40).Value = info.MiddleName; command.Parameters.Add("@lastname", FbDbType.VarChar, 40).Value = info.LastName; using (SqlDbCommand dbCommand = new SqlDbCommand(command)) { author = dbCommand.ExecuteObject<Author>(); } if (author != null) { return author; } commandText = @"SELECT autor.* FROM autor INNER JOIN autor_synonims ON autor_synonims.autorid = autor.autorid WHERE UPPERCASE(autor_synonims.lastname) = UPPERCASE(@lastname) AND UPPERCASE(autor_synonims.firstname) = UPPERCASE(@firstname) AND ((autor_synonims.midname = '' AND CAST(@midname AS VARCHAR(40)) <> '') OR (autor_synonims.midname <> '' AND CAST(@midname AS VARCHAR(40)) = ''))"; command = this.connection.CreateCommand(); command.CommandType = CommandType.Text; command.CommandText = commandText; command.Transaction = transaction as FbTransaction; command.Parameters.Add("@firstname", FbDbType.VarChar, 40).Value = info.FirstName; command.Parameters.Add("@midname", FbDbType.VarChar, 40).Value = info.MiddleName ?? String.Empty; command.Parameters.Add("@lastname", FbDbType.VarChar, 40).Value = info.LastName; using (SqlDbCommand dbCommand = new SqlDbCommand(command)) { author = dbCommand.ExecuteObject<Author>(); } if (author != null) { return author; } commandText = @"SELECT * FROM autor WHERE UPPERCASE(autor.lastname) = UPPERCASE(@lastname) AND UPPERCASE(autor.firstname) = UPPERCASE(@firstname) AND UPPERCASE(autor.midname) = UPPERCASE(@midname) AND UPPERCASE(autor.nickname) = UPPERCASE(@nickname)"; command = this.connection.CreateCommand(); command.CommandType = CommandType.Text; command.CommandText = commandText; command.Transaction = transaction as FbTransaction; command.Parameters.Add("@firstname", FbDbType.VarChar).Value = info.FirstName; command.Parameters.Add("@midname", FbDbType.VarChar).Value = info.MiddleName; command.Parameters.Add("@lastname", FbDbType.VarChar).Value = info.LastName; command.Parameters.Add("@nickname", FbDbType.VarChar).Value = info.NickName; using (SqlDbCommand dbCommand = new SqlDbCommand(command)) { author = dbCommand.ExecuteObject<Author>(); } if (author != null) { return author; } commandText = @"SELECT * FROM autor WHERE UPPERCASE(autor.lastname) = UPPERCASE(@lastname) AND UPPERCASE(autor.firstname) = UPPERCASE(@firstname) AND UPPERCASE(autor.midname) = UPPERCASE(@midname)"; command = this.connection.CreateCommand(); command.CommandType = CommandType.Text; command.CommandText = commandText; command.Transaction = transaction as FbTransaction; command.Parameters.Add("@firstname", FbDbType.VarChar).Value = info.FirstName; command.Parameters.Add("@midname", FbDbType.VarChar).Value = info.MiddleName; command.Parameters.Add("@lastname", FbDbType.VarChar).Value = info.LastName; using (SqlDbCommand dbCommand = new SqlDbCommand(command)) { author = dbCommand.ExecuteObject<Author>(); } if (author != null) { return author; } commandText = @"SELECT autor.* FROM autor WHERE UPPERCASE(autor.lastname) = UPPERCASE(@lastname) AND UPPERCASE(autor.firstname) = UPPERCASE(@firstname) AND ((autor.midname = '' AND CAST(@midname AS VARCHAR(40)) <> '') OR (autor.midname <> '' AND CAST(@midname AS VARCHAR(40)) = ''))"; command = this.connection.CreateCommand(); command.CommandType = CommandType.Text; command.CommandText = commandText; command.Transaction = transaction as FbTransaction; command.Parameters.Add("@firstname", FbDbType.VarChar, 40).Value = info.FirstName; command.Parameters.Add("@midname", FbDbType.VarChar, 40).Value = info.MiddleName ?? String.Empty; command.Parameters.Add("@lastname", FbDbType.VarChar, 40).Value = info.LastName; using (SqlDbCommand dbCommand = new SqlDbCommand(command)) { author = dbCommand.ExecuteObject<Author>(); } if (author != null) { return author; } if (String.IsNullOrEmpty(info.FirstName) && String.IsNullOrEmpty(info.LastName) && !String.IsNullOrEmpty(info.NickName)) { commandText = @"SELECT autor.* FROM autor WHERE UPPERCASE(autor.nickname) = UPPERCASE(@nickname)"; command = this.connection.CreateCommand(); command.CommandType = CommandType.Text; command.CommandText = commandText; command.Transaction = transaction as FbTransaction; command.Parameters.Add("@nickname", FbDbType.VarChar, 40).Value = info.NickName; using (SqlDbCommand dbCommand = new SqlDbCommand(command)) { author = dbCommand.ExecuteObject<Author>(); } if (author != null) { return author; } } author = new Author(); author.FirstName = info.FirstName; author.MiddleName = info.MiddleName; author.LastName = info.LastName; author.NickName = info.NickName; author.LibraryId = info.Id; author.EMail = StringUtils.Truncate(StringUtils.Join(", ", info.Email), 125); author.Homepage = StringUtils.Truncate(StringUtils.Join(", ", info.Homepage), 125); commandText = "INSERT INTO AUTOR (AUTORID, FIRSTNAME, MIDNAME, LASTNAME, NICKNAME, EMAIL, HOMEPAGE) " + "VALUES (GEN_ID(GEN_AUTOR_ID, 1), @firstname, @midname, @lastname, @nickname, @email, @homepage) " + "RETURNING AUTORID"; using (FbCommand cmd = this.connection.CreateCommand()) { cmd.CommandType = CommandType.Text; cmd.CommandText = commandText; cmd.Transaction = transaction as FbTransaction; cmd.Parameters.Add("@firstname", FbDbType.VarChar, 40).Value = author.FirstName ?? String.Empty; cmd.Parameters.Add("@midname", FbDbType.VarChar, 40).Value = author.MiddleName ?? String.Empty; cmd.Parameters.Add("@lastname", FbDbType.VarChar, 40).Value = author.LastName ?? String.Empty; cmd.Parameters.Add("@nickname", FbDbType.VarChar, 40).Value = author.NickName ?? String.Empty; cmd.Parameters.Add("@email", FbDbType.VarChar, 125).Value = author.EMail; cmd.Parameters.Add("@homepage", FbDbType.VarChar, 125).Value = author.Homepage; cmd.Parameters.Add("@authorid", FbDbType.Integer).Direction = ParameterDirection.Output; cmd.ExecuteNonQuery(); author.Id = Convert.ToInt32(cmd.Parameters["@authorid"].Value); } if (!String.IsNullOrEmpty(author.LibraryId)) { commandText = "INSERT INTO AUTHOR_INFO (AUTORID, ID) VALUES (@autorid, @id)"; using (FbCommand cmd = this.connection.CreateCommand()) { cmd.CommandType = CommandType.Text; cmd.CommandText = commandText; cmd.Transaction = transaction as FbTransaction; cmd.Parameters.Add("@autorid", FbDbType.Integer).Value = author.Id; cmd.Parameters.Add("@id", FbDbType.VarChar, 254).Value = author.LibraryId; cmd.ExecuteNonQuery(); } } List<Author> authors = AuthorList; authors.Add(author); } catch (FbException exp) { throw new DatabaseException(exp.Message, exp); } finally { this.manager.EndConnect(); } return author; }
public Author FindOrCreateTranslator(AuthorInfoNode info) { return FindOrCreateTranslator(info, null); }
public List<Author> FindSimularAuthors(AuthorInfoNode info) { if (info == null) { throw new ArgumentNullException("info"); } List<Author> result = new List<Author>(); List<Author> searchList = this.AuthorList; //BKTree<Author> tree = new BKTree<Author>(searchList, null); //IList<Author> test = tree.RangeSearch(info, (x, y) => StringUtils.DamerauLevenshteinDistance(x.DisplayName, y.DisplayName), 0, 5); foreach (Author author in searchList) { int firstNameDistance = StringUtils.DamerauLevenshteinDistance(info.FirstName ?? String.Empty, author.FirstName ?? String.Empty); int middleNameDistance = StringUtils.DamerauLevenshteinDistance(info.MiddleName ?? String.Empty, author.MiddleName ?? String.Empty); int lastNameDistance = StringUtils.DamerauLevenshteinDistance(info.LastName ?? String.Empty, author.LastName ?? String.Empty); double sourceMetric = (info.FirstName ?? String.Empty).Length * 0.45 + (info.MiddleName ?? String.Empty).Length * 0.1 + (info.LastName ?? String.Empty).Length * 0.45; double distanceMetric = firstNameDistance * 0.45 + middleNameDistance * 0.1 + lastNameDistance * 0.45; double metric = (sourceMetric > 0) ? (1 - distanceMetric / sourceMetric) * 100 : 0; if (metric >= 70.0) { result.Add(author); } } return result; }
public Author FindOrCreateDocumentAuthor(AuthorInfoNode info) { return FindOrCreateDocumentAuthor(info, null); }