/*
* Publication Harvester
* Copyright (c) 2003-2006 Stellman & Greene Consulting
* Developed for Joshua Zivin and Pierre Azoulay, Columbia University
* http://www.stellman-greene.com/PublicationHarvester
*
* This program is free software; you can redistribute it and/or modify it under
* the terms of the GNU General Public License as published by the Free Software
* Foundation; either version 2 of the License, or (at your option) any later
* version.
*
* This program is distributed in the hope that it will be useful, but WITHOUT
* ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
* FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.
*
* You should have received a copy of the GNU General Public License along with
* this program (GPL.txt); if not, write to the Free Software Foundation, Inc., 51
* Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
*/
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Data.Odbc;
using System.Collections;
namespace Com.StellmanGreene.SocialNetworking
{
public class Database
{
OdbcConnection Connection;
public string DSN;
///
/// Establish a connection with an ODBC data source
///
/// HandGeneratedData source to connect to
public Database(string DSN)
{
Connection = new OdbcConnection("DSN=" + DSN + ";");
Connection.Open();
this.DSN = DSN;
}
///
/// Retrieve the status of the data in the database
///
/// True if the Harvested.CreateTables() has been called
/// Number of people that have been added to the database
/// Number of the people whose publications have been harvested
/// Number of publications in the database
public void GetStatus(out bool TablesCreated, out int numPeople,
out int numHarvestedPeople, out int numPublications, out int numErrors)
{
// Determine whether all seven of the tables have been created
DataTable Results = ExecuteQuery("SHOW TABLES");
if (Results.Rows.Count < 7)
{
TablesCreated = false;
numPeople = 0;
numHarvestedPeople = 0;
numPublications = 0;
numErrors = 0;
return;
}
ArrayList Tables = new ArrayList();
foreach (DataRow Row in Results.Rows)
{
Tables.Add(Row[0].ToString().ToLower());
}
foreach (string Table in new string[] {
"meshheadings", "people", "peoplepublications", "publicationauthors",
"publicationmeshheadings", "publications", "pubtypecategories", "publicationgrants"
}) {
if (Tables.Contains(Table) == false)
{
TablesCreated = false;
numPeople = 0;
numHarvestedPeople = 0;
numPublications = 0;
numErrors = 0;
return;
}
}
TablesCreated = true;
// Get the other values
numPeople = GetIntValue("SELECT Count(*) FROM People");
numHarvestedPeople = GetIntValue("SELECT Count(*) FROM People WHERE Harvested = 1");
numPublications = GetIntValue("SELECT Count(*) FROM Publications");
numErrors = GetIntValue("SELECT Count(*) FROM People WHERE Error = 1");
}
///
/// Execute a query that does not return a table
///
/// SQL query to execute
/// Number of rows affected
public virtual int ExecuteNonQuery(string SQL)
{
OdbcCommand Command;
Command = new OdbcCommand(SQL, Connection);
return Command.ExecuteNonQuery();
}
///
/// Execute a query that does not return a table
///
/// SQL query to execute
/// ArrayList of OdbcParameter objects
/// Number of rows affected
public virtual int ExecuteNonQuery(string SQL, ArrayList Parameters)
{
OdbcCommand Command;
Command = new OdbcCommand(SQL, Connection);
for (int i = 0; i < Parameters.Count; i++)
Command.Parameters.Add(Parameters[i]);
return Command.ExecuteNonQuery();
}
///
/// Execute a query that returns a table
///
/// SQL query to execute
/// Returns a DataTable containing the results of the query
public virtual DataTable ExecuteQuery(string SQL)
{
OdbcDataAdapter Query = new OdbcDataAdapter(SQL, Connection);
DataTable Table = new DataTable();
Query.Fill(Table);
return Table;
}
///
/// Execute a query that returns a table
///
/// SQL query to execute
/// ArrayList of OdbcParameter objects
/// Returns a DataTable containing the results of the query
public virtual DataTable ExecuteQuery(string SQL, ArrayList Parameters)
{
OdbcCommand Command = new OdbcCommand(SQL, Connection);
for (int i = 0; i < Parameters.Count; i++)
Command.Parameters.Add(Parameters[i]);
OdbcDataAdapter Query = new OdbcDataAdapter(Command);
DataTable Table = new DataTable();
Query.Fill(Table);
return Table;
}
///
/// Execute a query that returns a scalar
///
/// SQL query to execute
/// Returns an object containing the results of the query
public virtual object ExecuteScalar(string SQL)
{
OdbcCommand Command = new OdbcCommand(SQL, Connection);
return Command.ExecuteScalar();
}
///
/// Get a string from the database
///
/// SQL query to execute
/// Returns a string containing the results of the query
public virtual string GetStringValue(string SQL)
{
OdbcCommand Command = new OdbcCommand(SQL, Connection);
return Command.ExecuteScalar().ToString();
}
///
/// Get an int from the database
///
/// SQL query to execute
/// Returns an int containing the results of the query
public virtual int GetIntValue(string SQL)
{
OdbcCommand Command = new OdbcCommand(SQL, Connection);
return Convert.ToInt32(Command.ExecuteScalar().ToString());
}
///
/// Get an int from the database
///
/// SQL query to execute
/// ArrayList of OdbcParameter objects
/// Returns an int containing the results of the query
public virtual int GetIntValue(string SQL, ArrayList Parameters)
{
OdbcCommand Command;
Command = new OdbcCommand(SQL, Connection);
for (int i = 0; i < Parameters.Count; i++)
Command.Parameters.Add(Parameters[i]);
return Convert.ToInt32(Command.ExecuteScalar().ToString());
}
///
/// Create an OdbcParameter
///
/// Object to pass to a query
/// An OdbcParameter object that has the value Objct
public static OdbcParameter Parameter(object Object) {
if (Object == null)
return new OdbcParameter("", DBNull.Value);
else
return new OdbcParameter("", Object);
}
///
/// Trim a string to a given length, but only if it's greater than that length
///
/// String to trim
/// Maximum length of the string
/// Trimmed string
public static string Left(string Input, int Length)
{
if (Input == null)
return null;
else if (Input.Length > Length)
return Input.Substring(0, Length);
else
return Input;
}
}
}