/// <summary>
/// Retrieves the number of rows in a table.
/// </summary>
/// <param name="Table">The name of the table.</param>
/// <returns>The number of tables of an integer.</returns>
public virtual int GetTableRowCount(String Table)
{
IDbCommand cmd;
IDataReader rdr;
int ret;
using (cmd = Cn.CreateCommand()) {
cmd.CommandText =
String.Format("SELECT COUNT(*) FROM [{0}]", Table);
rdr = cmd.ExecuteReader();
rdr.Read();
try {
ret = (int)rdr[0];
}
/*
* The previous works with access databases, but trips an
* InvalidCastException in SQLite databases. Its probably that whole,
* "Lets make a loosley typed database." mentality of Dr. Hib.
*/
catch (InvalidCastException) {
ret = int.Parse(rdr[0].ToString());
}
rdr.Close();
}
return(ret);
}
/// <summary>
/// Executes a SQL query and returns the Results as a
/// <code>Seperator</code> seperated file.
/// </summary>
/// <param name="SQL">The SQL statement to execute.</param>
/// <param name="Seperator">
/// The field seperator character(s).
/// </param>
/// <returns>A string containing the table with rows seperated by
/// newlines and fields seperated by <code>Seperator</code>.
/// </returns>
public string GetSQLAsCSV(string SQL, string Seperator)
{
DbCommand cmd = Cn.CreateCommand();
cmd.CommandText = SQL;
DbDataReader rdr;
int numFields;
string [] Fields;
StringBuilder CSV = new StringBuilder();
rdr = cmd.ExecuteReader();
numFields = rdr.FieldCount;
Fields = new string[numFields];
for (int i = 0; i < numFields; i++)
{
Fields[i] = rdr.GetName(i);
CSV.AppendFormat("{0}{1}", Fields[i], Seperator);
}
CSV.AppendLine();
while (rdr.Read())
{
foreach (string Field in Fields)
{
CSV.AppendFormat("{0}{1}", rdr[Field], Seperator);
}
CSV.AppendLine();
}
rdr.Close();
return(CSV.ToString());
}
/// <summary>
/// Drops the given view from the database.
/// </summary>
/// <param name="Name">The name of the View.</param>
public void DropView(string Name)
{
DbCommand cmd = Cn.CreateCommand();
cmd.CommandText = String.Format("DROP VIEW {0}", Name);
cmd.ExecuteNonQuery();
}
/// <summary>
/// Gets a column from a table and returns it as a string of arrays.
/// </summary>
/// <param name="Table">The name of the table or view.</param>
/// <param name="Col">The name of the column.</param>
/// <param name="Distinct">Set to true to only return unique values.</param>
/// <returns>The contents of the column as a string of arrays.</returns>
public virtual string [] GetColumnAsStringArray(string Table, string Col, bool Distinct)
{
string SQL;
DbCommand cmd;
DbDataReader rdr;
ArrayList Rows = new ArrayList();
using (cmd = Cn.CreateCommand()) {
Rows = new ArrayList();
SQL = Distinct ?
string.Format("SELECT DISTINCT [{1}] FROM [{0}]", Table, Col) :
string.Format("SELECT [{1}] FROM [{0}]", Table, Col);
cmd.CommandText = SQL;
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
Rows.Add(rdr[Col].ToString());
}
rdr.Close();
rdr.Dispose();
}
return((string [])Rows.ToArray(typeof(System.String)));
}
/// <summary>
/// Executes the SQL command passed as a string.
/// </summary>
/// <param name="SQL">One or more SQL commands semicolon delimited.</param>
public void ExecuteSqlCommand(string SQL)
{
using (DbCommand cmd = Cn.CreateCommand()) {
cmd.CommandText = SQL;
cmd.ExecuteNonQuery();
}
}
/// <summary>
/// Drops the given table from the database.
/// </summary>
/// <param name="Name">The name of the Table.</param>
public void DropTable(string Name)
{
DbCommand cmd = Cn.CreateCommand();
cmd.CommandText = String.Format("DROP TABLE {0}", Name);
cmd.ExecuteNonQuery();
}
/// <summary>
/// Drops the given procedure from the database.
/// </summary>
/// <param name="Name">The name of the View.</param>
public void DropProcedure(string Name)
{
DbCommand cmd = Cn.CreateCommand();
cmd.CommandText = String.Format("DROP PROCEDURE {0}", Name);
cmd.ExecuteNonQuery();
}
/// <summary>
/// Executes the SQL command passed as a string.
/// </summary>
/// <param name="SQL">One or more SQL commands semicolon delimited.</param>
/// <param name="paramaters">The parameters to pass to the SQL.</param>
public virtual void ExecuteSqlCommand(string SQL, DbParameter [] parameters)
{
using (DbCommand cmd = Cn.CreateCommand()) {
cmd.CommandText = SQL;
cmd.Parameters.AddRange(parameters);
cmd.ExecuteNonQuery();
}
}
/// <summary>
/// Gets the SQL executed by a given TABLE.
/// </summary>
/// <remarks>
/// Posted by Rasha in http://sqlite.phxsoftware.com/forums/thread/2272.aspx
/// </remarks>
/// <returns>
/// The DDL of the given table.
/// </returns>
public virtual string GetTableSQL(string Table)
{
using (SQLiteCommand cmd = (SQLiteCommand)Cn.CreateCommand()) {
cmd.CommandText = "SELECT sql FROM sqlite_master " +
"WHERE name = @tablename";
cmd.Parameters.Add("@tablename", DbType.String);
cmd.Parameters["@tablename"].Value = Table;
return((string)cmd.ExecuteScalar());
}
}
/// <summary>
/// Executes a SQL statement and returns the results in a
/// <code>DbDataReader</code>
/// </summary>
/// <param name="SQL">The SQL Statement</param>
/// <returns>A DbDataReader containing the result set.</returns>
/// <remarks>Remember to close te reader when you are done with it.</remarks>
public DbDataReader GetSqlAsDataReader(string SQL)
{
DbCommand cmd;
DbDataReader rdr;
using (cmd = Cn.CreateCommand()) {
cmd.CommandText = SQL;
rdr = cmd.ExecuteReader();
}
return(rdr);
}
/// <summary>
/// Returns all rows in a table in a
/// <code>System.DataGridView</code>.
/// </summary>
/// <param name="Table">The name of the table</param>
/// <returns>A DataGridView containing the result set.</returns>
public override DataTable GetTableAsDataTable(string Table)
{
DataTable ret = new DataTable();
OdbcCommand cmd = (OdbcCommand)Cn.CreateCommand();
cmd.CommandText = String.Format("SELECT * FROM [{0}]", Table);
OdbcDataAdapter da = new OdbcDataAdapter(cmd);
da.Fill(ret);
ret.TableName = Table;
return(ret);
}
/// <summary>
/// Executes a SQL statement and returns the results in a
/// <code>System.DataGridView</code>
/// </summary>
/// <param name="SQL">The SQL Statement</param>
/// <returns>A DataGridView containing the result set.</returns>
public virtual DataTable GetSqlAsDataTable(string SQL)
{
DbCommand cmd;
DataSet ds = new DataSet();
DataAdapter da;
using (cmd = Cn.CreateCommand()) {
cmd.CommandText = SQL;
da = this.CreateDataAdapter(cmd);
da.Fill(ds);
}
return((ds.Tables.Count == 0) ? null : ds.Tables[0]);
}
/// <summary>
/// Creates a view with a given name and sql. You can specify to replace an
/// exisiting query of the same name.
/// </summary>
/// <param name="Name">The name to give to the view.</param>
/// <param name="SQL">The SQL statement that generates the VIEW.</param>
/// <param name="ReplaceQuery">
/// Set this to true to replace the current view.
/// </param>
public void CreateView(string Name, string SQL, bool ReplaceQuery)
{
DbCommand cmd = Cn.CreateCommand();
if (ReplaceQuery)
{
try {
cmd.CommandText = String.Format("DROP VIEW {0}", Name);
cmd.ExecuteNonQuery();
} catch (DbException) {}
}
cmd.CommandText = String.Format("CREATE VIEW {0} AS {1}", Name, SQL);
cmd.ExecuteNonQuery();
}
/// <summary>
/// Executes a SQL statement applying the given array of parametera
/// and returns the results in a <code>"System.DataTable"</code>.
/// </summary>
/// <param name="SQL">The SQL Statement</param>
/// <param name="Parameters">
/// The Parameters to apply to the SQL statement.
/// </param>
/// <returns>A DaTatable containing the result set.</returns>
public virtual DataTable GetSqlAsDataTable(string SQL, DbParameter[] Parameters)
{
DbCommand cmd;
DataSet ds = new DataSet();
DataAdapter da;
using (cmd = Cn.CreateCommand()) {
cmd.CommandText = SQL;
cmd.Parameters.AddRange(Parameters);
da = this.CreateDataAdapter(cmd);
da.Fill(ds);
}
return(ds.Tables[0]);
}
/// <summary>
/// Executes the SQL command(s) passed as a string.
/// </summary>
/// <param name="SQL">One or more SQL commands semicolon delimited.</param>
/// <returns>A dataset generated from the last SQL command in the script.</returns>
public DataTable ExecuteScript(string SQL)
{
DataTable ret;
DbCommand cmd = Cn.CreateCommand();
SQL = SQL.Trim();
SQL = SQL.TrimEnd(new char [] { ';' });
string [] Statements = SqlScript2Statements(SQL);
for (int i = 0; i < Statements.Length - 1; i++)
{
if (Statements[i] != "")
{
cmd.CommandText = Statements[i];
cmd.ExecuteNonQuery();
}
}
cmd.Dispose();
ret = this.GetSqlAsDataTable(Statements[Statements.Length - 1]);
return(ret);
}
/// <summary>
/// Takes the passed DataTable <c>dt</c> and creates a table in the currently
/// open SQLite database with equivilant schama and values. If the currently
/// open database has a table of the same name, attempts to append the rows
/// in the DataTable to it.
/// </summary>
/// <remarks>
/// <para>This qualifies as one of the greatest dirty hacks that just works.
/// I don't scrub parameters, and it will probably only work with DataTables
/// from Microsoft Access Databases. I am not all emcompasing with
/// data types. I simple kept feeding it the data I needed to feed it until
/// it worked. If it encounters a datatype it doesn't know how to deal with it
/// panics and throws an exception.</para>
/// <para>In terms of datatype considerations, dates are mapped as strings
/// because I read somewhere that there are date manipulation functions
/// in SQLite that operate on strings and assigning a DateTime to a string
/// produced a sensible value. If storing dates in integers as unix
/// timestamps makes more sense in the future I might do that. Be warned.
/// I never told you to depend on this function.</para>
/// <para>All INSERT statements are done as one transaction. The reason for this
/// is that no writes are performed to the database until a transaction is
/// committed and therefore the difference in execution time between
/// inserting a thousand rows in one transaction and inserting a thousand rows
/// without transactions is greater than a thousand fold. This has been tested on
/// a table containing 179442 rows with 5 numeric column and one DateTime column
/// that was inserted as a text column. Tests much be performed on even larger
/// datasets to see if there is a point of noticeable performance degradation.
/// </para>
/// </remarks>
/// <param name="dt">The DataTable to place in a new SQLite database.</param>
public void DataTable2SQLiteTable(DataTable dt)
{
/* Check to make sure that the DataTable has a name */
string TableNameError =
"DataTable passed to DataTable2SQLiteTable() must have the TableName Property " +
"set to a non null, non empty string (\"\") value.";
if (dt.TableName == null)
{
throw new ArgumentNullException(TableNameError);
}
else if (dt.TableName == "")
{
throw new ArgumentException(TableNameError);
}
/* Create the table */
using (SQLiteCommand cmd = (SQLiteCommand)Cn.CreateCommand()) {
StringBuilder DDL = new StringBuilder();
//TODO: I wonder if I can pass parameters to CREATE TABLE statements.
DDL.AppendFormat("CREATE TABLE [{0}] (", dt);
List <string> Cols = new List <string>();
/* Figure out what datatypes to assign to the columns */
foreach (DataColumn col in dt.Columns)
{
if (col.DataType == typeof(string) || col.DataType == typeof(DateTime))
{
Cols.Add(String.Format("[{0}] TEXT", col.ColumnName));
}
else if (col.DataType == typeof(long) || col.DataType == typeof(ulong) || col.DataType == typeof(Single) || col.DataType == typeof(Int32) || col.DataType == typeof(bool) || col.DataType == typeof(Guid))
{
Cols.Add(String.Format("[{0}] INTEGER", col.ColumnName));
}
else if (col.DataType == typeof(byte[]))
{
Cols.Add(String.Format("[{0}] BLOB", col.ColumnName));
}
else
{
throw new DataException
(String.Concat("DataTable2SQLiteTable() doesn't know how to map columns of type ", col.DataType.ToString()));
}
}
DDL.Append(String.Join(", ", Cols.ToArray()));
DDL.Append(")");
cmd.CommandText = DDL.ToString();
cmd.ExecuteNonQuery();
}
using (SQLiteCommand cmd = (SQLiteCommand)Cn.CreateCommand()) {
/* Create the INSERT INTO statement. */
StringBuilder DML = new StringBuilder();
DML.AppendFormat("INSERT INTO [{0}] ([", dt.TableName);
List <string> ColumnName = new List <string>();
List <string> ParameterName = new List <string>();
foreach (DataColumn col in dt.Columns)
{
ColumnName.Add(col.ColumnName);
ParameterName.Add(col.ColumnName.Replace(' ', '_'));
}
DML.Append(String.Join("], [", ColumnName.ToArray()));
DML.Append("]) VALUES (@");
DML.Append(String.Join(", @", ParameterName.ToArray()));
DML.Append(")");
cmd.CommandText = DML.ToString();
/* Populate the parameters for the INSERT INTO statement and execute. */
foreach (DataRow Row in dt.Rows)
{
foreach (DataColumn col in dt.Columns)
{
cmd.Parameters.Add(new SQLiteParameter(String.Concat("@", col.ColumnName.Replace(' ', '_')), Row[col.ColumnName]));
}
}
/* It is much faster if this is done as one transaction. */
cmd.Transaction = (SQLiteTransaction)Cn.BeginTransaction();
DataTableReader rdr = dt.CreateDataReader();
/* Populate the parameters for the INSERT INTO statement and execute. */
while (rdr.Read())
{
for (int i = 0; i < rdr.FieldCount; i++)
{
cmd.Parameters[i].Value = rdr[i];
}
cmd.ExecuteNonQuery();
}
cmd.Transaction.Commit();
cmd.Transaction.Dispose();
}
}
