Пример #1
0
        //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);
        }
Пример #2
0
        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);
        }
Пример #3
0
        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);
        }
Пример #5
0
        /// <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);
        }