//public void InsertContract()
//{
// //// insert data
// //var InsCommands = new[]{
// // "INSERT INTO [Notifications] ([Key], [Value]) VALUES ('sample', 'text')"
// // };
// //foreach (var command in InsCommands)
// //{
// // using (var c = connection.CreateCommand())
// // {
// // c.CommandText = command;
// // c.ExecuteNonQuery();
// // }
// //}
//}
public static System.Data.DataTable RunQuery(SqliteConnection conn, string query)
{
//GetCon(); //sets a static variable conn with the connection info
try
{
//DUPLICATE THE DataAdapter.Fill Method since it's not available in Mono Android.Net
var dt = new System.Data.DataTable();
//do the query to get the data
Mono.Data.Sqlite.SqliteCommand c = new SqliteCommand();
c.CommandText = query;
c.Connection = conn;
conn.Open();
var reader = c.ExecuteReader();
var len = reader.FieldCount;
// Create the DataTable columns
for (int i = 0; i < len; i++)
{
dt.Columns.Add(reader.GetName(i), reader.GetFieldType(i));
}
dt.BeginLoadData();
var values = new object[len];
// Add data rows
while (reader.Read())
{
for (int i = 0; i < len; i++)
{
values[i] = reader[i];
}
dt.Rows.Add(values);
}
dt.EndLoadData();
reader.Close();
reader.Dispose();
//return the filled dataset
return(dt);
}
catch (Exception ex)
{
Console.WriteLine("RunQuery() error running query: " + ex.Message);
}
finally
{
if (conn.State == System.Data.ConnectionState.Open)
{
conn.Close();
}
}
return(null);
}
private static System.Data.DataTable querytable_to_datatable <T>(VA.ShapeSheet.Query.CellQuery cellQuery, VA.ShapeSheet.Query.CellQuery.QueryResultList <T> query_output)
{
// First Construct a Datatable with a compatible schema
var dt = new System.Data.DataTable();
dt.Columns.Add("ShapeID", typeof(int));
foreach (var col in cellQuery.Columns)
{
dt.Columns.Add(col.Name, typeof(T));
}
// Then populate the rows of the datatable
dt.BeginLoadData();
int colcount = cellQuery.Columns.Count;
var rowbuf = new object[colcount + 1];
for (int r = 0; r < query_output.Count; r++)
{
// populate the row buffer
rowbuf[0] = query_output[r].ShapeID;
for (int i = 0; i < colcount; i++)
{
rowbuf[i + 1] = query_output[r][i];
}
// load it into the table
dt.Rows.Add(rowbuf);
}
dt.EndLoadData();
return(dt);
}
public static System.Data.DataTable DataTableFromEnumerable <T>(IEnumerable <T> items)
{
// based partially on: http://stackoverflow.com/questions/1253725/convert-ienumerable-to-datatable
if (items == null)
{
return(null);
}
// Use reflection to get Columns from the properties of the type
var itemtype = typeof(T);
var binding_flags = System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.Instance;
var props = itemtype.GetProperties(binding_flags);
var datatable = new System.Data.DataTable();
foreach (var prop in props)
{
var colType = prop.PropertyType;
if ((colType.IsGenericType) && (colType.GetGenericTypeDefinition() == typeof(System.Nullable <>)))
{
// if the property is a nullable, then use the first generic argument as the column type
colType = colType.GetGenericArguments()[0];
}
var col = new System.Data.DataColumn(prop.Name, colType);
datatable.Columns.Add(col);
}
// Optimize for retrieving the property values
var prop_getters = new List <System.Func <T, object> >();
foreach (var prop in props)
{
var func = ReflectionUtility.GetGetter <T>(prop);
prop_getters.Add(func);
}
// Put data in the table
datatable.BeginLoadData();
var itemarray = new object[props.Length];
foreach (T item in items)
{
for (int i = 0; i < props.Length; i++)
{
var prop_info = props[i];
//var colvalue = prop_info.GetValue(item, null) ?? System.DBNull.Value;
var colvalue = prop_getters[i](item) ?? System.DBNull.Value;
itemarray[i] = colvalue;
}
var dr = datatable.Rows.Add(itemarray);
}
datatable.EndLoadData();
return(datatable);
}
public System.Data.DataTable DataTableFromEnumerable <T>(IEnumerable <T> items)
{
if (items == null)
{
return(null);
}
var datatable = new System.Data.DataTable();
// Use reflection to get Columns from the properties of the type
var itemtype = typeof(T);
System.Reflection.PropertyInfo[] propinfo_array = itemtype.GetProperties();
foreach (var propinfo in propinfo_array)
{
var colType = propinfo.PropertyType;
if (
(colType.IsGenericType) &&
(colType.GetGenericTypeDefinition() == typeof(Nullable <>)))
{
colType = colType.GetGenericArguments()[0];
}
var col = new System.Data.DataColumn(propinfo.Name, colType);
datatable.Columns.Add(col);
}
// Put data in the table
datatable.BeginLoadData();
var itemarray = new object[propinfo_array.Length];
foreach (T item in items)
{
for (int i = 0; i < propinfo_array.Length; i++)
{
var prop_info = propinfo_array[i];
var colvalue = prop_info.GetValue(item, null) ?? DBNull.Value;
itemarray[i] = colvalue;
}
var dr = datatable.Rows.Add(itemarray);
}
datatable.EndLoadData();
return(datatable);
}
/// <summary>
/// Returns a <see cref="T:System.Data.DataTable"/> that describes the column metadata of the <see cref="T:System.Data.IDataReader"/>.
/// </summary>
/// <returns>
/// A <see cref="T:System.Data.DataTable"/> that describes the column metadata.
/// </returns>
/// <exception cref="T:System.InvalidOperationException">The <see cref="T:System.Data.IDataReader"/> is closed. </exception>
public virtual System.Data.DataTable GetSchemaTable()
{
System.Data.DataTable schemaTable = new System.Data.DataTable();
schemaTable.Columns.Add("ColumnName", typeof(String));
schemaTable.Columns.Add("ColumnOrdinal", typeof(Int32));
schemaTable.Columns.Add("ColumnSize", typeof(Int32));
schemaTable.Columns.Add("NumericPrecision", typeof(Int32));
schemaTable.Columns.Add("NumericScale", typeof(Int32));
schemaTable.Columns.Add("IsUnique", typeof(Boolean));
schemaTable.Columns.Add("IsKey", typeof(Boolean));
schemaTable.Columns.Add("BaseCatalogName", typeof(String));
schemaTable.Columns.Add("BaseColumnName", typeof(String));
schemaTable.Columns.Add("BaseSchemaName", typeof(String));
schemaTable.Columns.Add("BaseTableName", typeof(String));
schemaTable.Columns.Add("DataType", typeof(Type));
schemaTable.Columns.Add("AllowDBNull", typeof(Boolean));
schemaTable.Columns.Add("ProviderType", typeof(Int32));
schemaTable.Columns.Add("IsAliased", typeof(Boolean));
schemaTable.Columns.Add("IsExpression", typeof(Boolean));
schemaTable.Columns.Add("IsIdentity", typeof(Boolean));
schemaTable.Columns.Add("IsAutoIncrement", typeof(Boolean));
schemaTable.Columns.Add("IsRowVersion", typeof(Boolean));
schemaTable.Columns.Add("IsHidden", typeof(Boolean));
schemaTable.Columns.Add("IsLong", typeof(Boolean));
schemaTable.Columns.Add("IsReadOnly", typeof(Boolean));
schemaTable.BeginLoadData();
for (int i = 0; i < this.FieldCount; i++)
{
System.Data.DataRow schemaRow = schemaTable.NewRow();
schemaRow["ColumnName"] = GetName(i);
schemaRow["ColumnOrdinal"] = i;
schemaRow["ColumnSize"] = -1;
schemaRow["NumericPrecision"] = 0;
schemaRow["NumericScale"] = 0;
schemaRow["IsUnique"] = false;
schemaRow["IsKey"] = false;
schemaRow["BaseCatalogName"] = "";
schemaRow["BaseColumnName"] = GetName(i);
schemaRow["BaseSchemaName"] = "";
schemaRow["BaseTableName"] = "";
schemaRow["DataType"] = GetFieldType(i);
schemaRow["AllowDBNull"] = true;
schemaRow["ProviderType"] = 0;
schemaRow["IsAliased"] = false;
schemaRow["IsExpression"] = false;
schemaRow["IsIdentity"] = false;
schemaRow["IsAutoIncrement"] = false;
schemaRow["IsRowVersion"] = false;
schemaRow["IsHidden"] = false;
schemaRow["IsLong"] = false;
schemaRow["IsReadOnly"] = false;
schemaTable.Rows.Add(schemaRow);
schemaRow.AcceptChanges();
}
schemaTable.EndLoadData();
return(schemaTable);
}
