Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Create table from object type

Tags:

c#

dapper

I'm trying to figure out how I can create an empty table from an object type. The type is quite simple (only primitive type properties); there's something ready to use?

Do I need to make to write my own "CREATE TABLE" query using some properties reflection?

I'm working on a project that reads data from a FirebirdSQL database and writes some of that tables in a Sqlite database, using Dapper.

Any suggestions are appreciated.

like image 644
Ferdinando Santacroce Avatar asked Apr 02 '13 10:04

Ferdinando Santacroce


People also ask

How do you create a table type in Oracle?

Oracle SQL commands to create nested table types are of the following form: CREATE TYPE typename AS TABLE OF datatype; Where typename is the desired name of your nested table type, and datatype is the datatype of the table elements (this can be a user-defined type, as well as a standard datatype).

What is object type type in Oracle?

An object type is a kind of data type. You can use it in the same ways that you use standard data types such as NUMBER or VARCHAR2 . For example, you can specify an object type as the data type of a column in a relational table, and you can declare variables of an object type.

Why %type is used in PL SQL?

The %TYPE attribute, used in PL/SQL variable and parameter declarations, is supported by the data server. Use of this attribute ensures that type compatibility between table columns and PL/SQL variables is maintained.

What is object type in PL SQL?

An object type can represent any real-world entity. For example, an object type can represent a student, bank account, computer screen, rational number, or data structure such as a queue, stack, or list. Currently, you cannot define object types in a PL/SQL block, subprogram, or package.


2 Answers

As Marc stated, Dapper isn't going to do that for you, but have a look at my answer here (ADO.NET distinct data bases) and simply adapt one of the command properties to construct a CREATE TABLE statement and it will leverage reflection. It should be pretty straight forward with that much of a head start.

I'd create an example, but I'm answering this from my phone.

like image 192
Mike Perrenoud Avatar answered Oct 02 '22 14:10

Mike Perrenoud


Rather late to the party, but I just wanted to add that this kind of function is available in ServiceStack.OrmLite:

class Poco 
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Ssn { get; set; }
}

db.DropTable<Poco>();
db.TableExists<Poco>(); //= false

db.CreateTable<Poco>(); 
db.TableExists<Poco>(); //= true

db.ColumnExists<Poco>(x => x.Ssn); //= true
db.DropColumn<Poco>(x => x.Ssn);
db.ColumnExists<Poco>(x => x.Ssn); //= false

Please be aware, however, that this library is not free.

like image 43
David Brower Avatar answered Oct 02 '22 12:10

David Brower