/// <summary>
 /// Get Metadata information about the tables in a schema in the current database
 /// </summary>
 /// <param name="schema">Name of the schema in the database.</param>
 /// <returns></returns>
 public override SchemaTablesMetaData QuerySchemaDefinition(string schema)
 {
     SchemaTablesMetaData result = new SchemaTablesMetaData();
     result.schemaName = schema;
     try
     {
         using (OleDbConnection connector = new OleDbConnection(connectionString))
         {
             connector.Open();
             using (DataTable dt = connector.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, schema, null, "TABLE" }))
             {
                 foreach (DataRow row in dt.Rows)
                 {
                     TableMetaData table = new TableMetaData();
                     table.tableName = row[2].ToString();
                     result.AddTable(table);
                 }
             }
             connector.Close();
         }
     }
     catch (OleDbException ex)
     {
         Console.Out.WriteLine("Exception fetching schema metadata: {0}", ex.Message);
     }
     return result;
 }
Exemple #2
0
        /// <summary>
        /// Creates a SQL INSERT sentence to insert a row of data to a table in the target database.
        /// </summary>
        /// <param name="table">Table metadata from the table to copy</param>
        /// <returns>SQL Sentence of the insertion in the target database</returns>
        private string CreateInsertQuery(TableMetaData table)
        {
            string csvColumList  = string.Empty;
            string csvParamsList = string.Empty;

            foreach (ColumnMetaData column in table.columns)
            {
                csvColumList  = String.Format("{0}{1},", csvColumList, SchemaTablesMetaData.EscapeIdentifier(column.columnName));
                csvParamsList = String.Format("{0}@{1},", csvParamsList, SchemaTablesMetaData.HexString(column.columnName));
            }
            string query = String.Format("INSERT INTO {0} ({1}) VALUES ({2})", SchemaTablesMetaData.EscapeIdentifier(table.tableName), csvColumList.Remove(csvColumList.Length - 1), csvParamsList.Remove(csvParamsList.Length - 1));

            return(query);
        }
Exemple #3
0
        /// <summary>
        /// Dumps table contents obtained from a OleDbDataReader into the target database
        /// </summary>
        /// <param name="table">Table metadata from where the DataReader is obtained</param>
        /// <param name="reader">DataReader that provides the original data</param>
        public override void DumpTable(TableMetaData table, DbDataReader reader)
        {
            SQLiteTransaction transaction  = null;
            string            currentQuery = string.Empty;

            try
            {
                using (SQLiteConnection connector = new SQLiteConnection(connectionString))
                {
                    connector.Open();
                    using (SQLiteCommand Cmd = new SQLiteCommand())
                    {
                        Cmd.CommandText = CreateInsertQuery(table);
                        Cmd.Connection  = connector;
                        CreateSqlCommandParameters(table, Cmd);

                        int countRecords = 0;
                        transaction = connector.BeginTransaction();
                        while (reader.Read())
                        {
                            foreach (ColumnMetaData colum in table.columns)
                            {
                                SetSqlCommandParameterValueForColumn(colum, Cmd, reader);
                            }
                            currentQuery = Cmd.CommandText;
                            Cmd.ExecuteNonQuery();
                            if (countRecords++ % 100 == 0)
                            {
                                System.Console.Out.Write(".");
                            }
                        }
                        transaction.Commit();
                        System.Console.Out.WriteLine("done");
                        System.Console.Out.WriteLine("Table dump complete, {0} records copied.", countRecords);
                    }
                    connector.Close();
                }
            }
            catch (SQLiteException ex)
            {
                if (null != transaction)
                {
                    transaction.Rollback();
                }
                Console.Out.WriteLine("SQLite Exception dumping table {0} : {1}\n{2}", table.tableName, ex.Message, currentQuery);
            }
        }
Exemple #4
0
        /// <summary>
        /// Get Metadata information about the tables in a schema in the current database
        /// </summary>
        /// <param name="schema">Name of the schema in the database.</param>
        /// <returns></returns>
        public override SchemaTablesMetaData QuerySchemaDefinition(string schema)
        {
            SchemaTablesMetaData result = new SchemaTablesMetaData();

            result.schemaName = schema;
            try
            {
                using (OleDbConnection connector = new OleDbConnection(connectionString))
                {
                    connector.Open();
                    using (DataTable dt = connector.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, schema, null, "TABLE" }))
                    {
                        foreach (DataRow row in dt.Rows)
                        {
                            TableMetaData table = new TableMetaData();
                            table.tableName = row[2].ToString();
                            result.AddTable(table);
                        }
                    }

                    // get all views fo ms access db
                    DataTable dt2 = connector.GetSchema("Views");

                    foreach (DataRow row in dt2.Rows)
                    {
                        string queryText = (string)row["VIEW_DEFINITION"];
                        queryText = queryText.Replace(System.Environment.NewLine, " ");
                        string queryName = (string)row["TABLE_NAME"];
                        Console.WriteLine(queryName);

                        ViewMetaData vm = new ViewMetaData();
                        vm.ViewName  = queryName;
                        vm.ViewQuery = queryText;

                        result.views.Add(vm);
                    }
                    connector.Close();
                }
            }
            catch (OleDbException ex)
            {
                Console.Out.WriteLine("Exception fetching schema metadata: {0}", ex.Message);
            }
            return(result);
        }
 /// <summary>
 /// Copy the table contents of a table in the current database into a table with the same name in an SQLite DB
 /// </summary>
 /// <param name="table">Metadata of the table to copy</param>
 /// <param name="target">SQLite backend that will be used to copy the contents of the table</param>
 public override void DumpTableContents(TableMetaData table, IDBBackEnd target)
 {
     try
     {
         using (OleDbConnection connector = new OleDbConnection(connectionString))
         {
             connector.Open();
             using (OleDbCommand cmd = connector.CreateCommand())
             {
                 cmd.CommandText = String.Format("SELECT * FROM [{0}]", table.tableName);
                 using (OleDbDataReader db = cmd.ExecuteReader())
                 {
                     target.DumpTable(table, db);
                 }
             }
             connector.Close();
         }
     }
     catch (OleDbException ex)
     {
         Console.Out.WriteLine("MSAccess Exception dumping table {0} : {1}", table.tableName, ex.Message);
     }
 }
 /// <summary>
 /// 
 /// </summary>
 /// <param name="table"></param>
 /// <param name="reader"></param>
 public override void DumpTable(TableMetaData table, DbDataReader reader)
 {
     throw new NotImplementedException();
 }
Exemple #7
0
        /// <summary>
        /// Get Metadata information about the tables in a schema in the current database
        /// </summary>
        /// <param name="schema">Name of the schema in the database.</param>
        /// <returns></returns>
        public override SchemaTablesMetaData QuerySchemaDefinition(string schema)
        {
            SchemaTablesMetaData result = new SchemaTablesMetaData();
            result.schemaName = schema;
            try
            {
                using (OleDbConnection connector = new OleDbConnection(connectionString))
                {
                    connector.Open();
                    using (DataTable dt = connector.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, schema, null, "TABLE" }))
                    {
                        foreach (DataRow row in dt.Rows)
                        {
                            TableMetaData table = new TableMetaData();
                            table.tableName = row[2].ToString();
                            result.AddTable(table);
                        }
                    }

                    // get all views fo ms access db
                    DataTable dt2 = connector.GetSchema("Views");

                    foreach (DataRow row in dt2.Rows)
                    {
                        string queryText = (string)row["VIEW_DEFINITION"];
                        queryText = queryText.Replace(System.Environment.NewLine," ");
                        string queryName = (string)row["TABLE_NAME"];
                        Console.WriteLine(queryName);

                        ViewMetaData vm = new ViewMetaData();
                        vm.ViewName = queryName;
                        vm.ViewQuery = queryText;

                        result.views.Add(vm);
                    }
                    connector.Close();
                }
            }
            catch (OleDbException ex)
            {
                Console.Out.WriteLine("Exception fetching schema metadata: {0}", ex.Message);
            }
            return result;
        }
 /// <summary>
 /// Copy the table contents of a table in the current database into a table with the same name in an SQLite DB
 /// </summary>
 /// <param name="table">Metadata of the table to copy</param>
 /// <param name="target">SQLite backend that will be used to copy the contents of the table</param>
 public override void DumpTableContents(TableMetaData table, IDBBackEnd target)
 {
     try
     {
         using (OleDbConnection connector = new OleDbConnection(connectionString))
         {
             connector.Open();
             using (OleDbCommand cmd = connector.CreateCommand())
             {
                 cmd.CommandText = String.Format("SELECT * FROM [{0}]", table.tableName);
                 using (OleDbDataReader db = cmd.ExecuteReader())
                 {
                     target.DumpTable(table, db);
                 }
             }
             connector.Close();
         }
     }
     catch (OleDbException ex)
     {
         Console.Out.WriteLine("MSAccess Exception dumping table {0} : {1}", table.tableName, ex.Message);
     }
 }
Exemple #9
0
 /// <summary>
 ///
 /// </summary>
 /// <param name="table"></param>
 public override void QueryTableDefinition(TableMetaData table)
 {
     throw new NotImplementedException();
 }
Exemple #10
0
 /// <summary>
 ///
 /// </summary>
 /// <param name="table"></param>
 /// <param name="target"></param>
 public override void DumpTableContents(TableMetaData table, IDBBackEnd target)
 {
     throw new NotImplementedException();
 }
 /// <summary>
 /// Adds a table to the schema collection.
 /// </summary>
 /// <param name="table">Table Metadata info to add</param>
 public void AddTable(TableMetaData table)
 {
     tables.Add(table);
 }
Exemple #12
0
 /// <summary>
 /// 
 /// </summary>
 /// <param name="table"></param>
 /// <param name="target"></param>
 public override void DumpTableContents(TableMetaData table, IDBBackEnd target)
 {
     throw new NotImplementedException();
 }
 /// <summary>
 ///
 /// </summary>
 /// <param name="table"></param>
 /// <param name="reader"></param>
 public override void DumpTable(TableMetaData table, DbDataReader reader)
 {
     throw new NotImplementedException();
 }
Exemple #14
0
 /// <summary>
 /// Adds a table to the schema collection.
 /// </summary>
 /// <param name="table">Table Metadata info to add</param>
 public void AddTable(TableMetaData table)
 {
     tables.Add(table);
 }
Exemple #15
0
        /// <summary>
        /// Creates the text of the SQL command to create a table in SQLite
        /// </summary>
        /// <param name="table">Table Metadata to create</param>
        /// <returns>The DML sentence (SQL) to create the given table in a SQLite schema</returns>
        private string CreateTableDML(TableMetaData table)
        {
            StringBuilder stmtBuilder = new StringBuilder();
            string primary_keys_string = string.Empty;

            stmtBuilder.Append("CREATE TABLE " + SchemaTablesMetaData.EscapeIdentifier(table.tableName) + " (");

            for (int i = 0 ; i < table.columns.Count; i++)
            {
                ColumnMetaData column = table.columns[i];
                stmtBuilder.Append(SchemaTablesMetaData.EscapeIdentifier(column.columnName));
                stmtBuilder.Append(" ");
                stmtBuilder.Append(ConvertDbTypeToSQLiteType(column));
                if (!column.isNullable)
                {
                    stmtBuilder.Append(" NOT NULL");
                }
                if (column.hasDefault)
                {
                    stmtBuilder.Append(" DEFAULT ");
                    if (IsLiteralType(column))
                    {
                        stmtBuilder.Append(SchemaTablesMetaData.EscapeIdentifier(column.defaultValue));
                    }
                    else
                    {
                        stmtBuilder.Append(column.defaultValue);
                    }
                }
                if (column.hasForeignKey)
                {
                    stmtBuilder.Append(String.Format(" REFERENCES {0} ({1})", SchemaTablesMetaData.EscapeIdentifier(column.fkTable), SchemaTablesMetaData.EscapeIdentifier(column.fkColumn)));
                }
                if (column.isPrimaryKey)
                {
                    if (string.IsNullOrEmpty(primary_keys_string))
                        primary_keys_string = SchemaTablesMetaData.EscapeIdentifier(column.columnName);
                    else primary_keys_string = String.Format("{0},{1}", primary_keys_string , SchemaTablesMetaData.EscapeIdentifier(column.columnName));
                }
                if (i + 1 < table.columns.Count || !string.IsNullOrEmpty(primary_keys_string))
                    stmtBuilder.Append(", ");
            }
            if (!string.IsNullOrEmpty(primary_keys_string))
                stmtBuilder.Append(String.Format("PRIMARY KEY({0})", primary_keys_string));
            stmtBuilder.Append(")");

            return stmtBuilder.ToString();
        }
Exemple #16
0
 /// <summary>
 /// Copies table contents obtained from a OleDbDataReader into the database
 /// </summary>
 /// <param name="table">Table metadata from where the DataReader is obtained</param>
 /// <param name="reader">DataReader that provides the original data</param>
 public abstract void DumpTable(TableMetaData table, DbDataReader reader);
Exemple #17
0
 /// <summary>
 /// Populates a SQLiteCommand parameter list with parameter names based on the column names of a table
 /// </summary>
 /// <param name="table">Table metadata to obtain column names</param>
 /// <param name="cmd">SQLite command whose parameter List will be filled</param>
 private void CreateSqlCommandParameters(TableMetaData table, SQLiteCommand cmd)
 {
     cmd.Parameters.Clear();
     foreach (ColumnMetaData column in table.columns)
     {
         cmd.Parameters.Add(new SQLiteParameter(String.Format("@{0}", SchemaTablesMetaData.HexString(column.columnName))));
     }
 }
Exemple #18
0
        /// <summary>
        /// Creates a SQL INSERT sentence to insert a row of data to a table in the target database.
        /// </summary>
        /// <param name="table">Table metadata from the table to copy</param>
        /// <returns>SQL Sentence of the insertion in the target database</returns>
        private string CreateInsertQuery(TableMetaData table)
        {
            string csvColumList = string.Empty;
            string csvParamsList = string.Empty;

            foreach (ColumnMetaData column in table.columns)
            {
                csvColumList = String.Format("{0}{1},",csvColumList , SchemaTablesMetaData.EscapeIdentifier(column.columnName));
                csvParamsList = String.Format("{0}@{1},", csvParamsList, SchemaTablesMetaData.HexString(column.columnName));
            }
            string query = String.Format("INSERT INTO {0} ({1}) VALUES ({2})", SchemaTablesMetaData.EscapeIdentifier(table.tableName), csvColumList.Remove(csvColumList.Length - 1), csvParamsList.Remove(csvParamsList.Length - 1));
            return query;
        }
Exemple #19
0
 /// <summary>
 /// 
 /// </summary>
 /// <param name="table"></param>
 public override void QueryTableDefinition(TableMetaData table)
 {
     throw new NotImplementedException();
 }
Exemple #20
0
 /// <summary>
 /// Copies table contents obtained from a OleDbDataReader into the database
 /// </summary>
 /// <param name="table">Table metadata from where the DataReader is obtained</param>
 /// <param name="reader">DataReader that provides the original data</param>
 public abstract void DumpTable(TableMetaData table, DbDataReader reader);
Exemple #21
0
 /// <summary>
 /// Copy the table contents of a table in the current database into a table with the same name in another database
 /// </summary>
 /// <param name="table">Metadata of the table to copy</param>
 /// <param name="target">IDBBackend that will be used to copy the contents of the table</param>
 public abstract void DumpTableContents(TableMetaData table, IDBBackEnd target);
        /// <summary>
        /// Get Metadata information about a table in a schema in the current database
        /// </summary>
        /// <param name="table">Name of the table to obtain the metadata (columns and primary keys)</param>
        public override void QueryTableDefinition(TableMetaData table)
        {
            try
            {
                using (OleDbConnection connector = new OleDbConnection(connectionString))
                {
                    connector.Open();
                    using (DataTable dt = connector.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, table.tableName, null }))
                    {
                        foreach (DataRow row in dt.Rows)
                        {
                            ColumnMetaData metadata = new ColumnMetaData();
                            metadata.columnName = row["COLUMN_NAME"].ToString();
                            metadata.columnDescription = row["DESCRIPTION"].ToString(); ;
                            metadata.ordinalPosition = Convert.ToInt32(row["ORDINAL_POSITION"].ToString());
                            if (row["CHARACTER_MAXIMUM_LENGTH"] != DBNull.Value)
                                metadata.maxCharSize = Convert.ToInt32(row["CHARACTER_MAXIMUM_LENGTH"].ToString());
                            if (row["NUMERIC_PRECISION"] != DBNull.Value)
                                metadata.numericPrecision = Convert.ToInt32(row["NUMERIC_PRECISION"].ToString());
                            if (row["NUMERIC_SCALE"] != DBNull.Value)
                                metadata.numericScale = Convert.ToInt32(row["NUMERIC_SCALE"].ToString());
                            if (row["DATETIME_PRECISION"] != DBNull.Value)
                                metadata.datetimePrecision = Convert.ToInt32(row["DATETIME_PRECISION"].ToString());
                            metadata.hasDefault = Convert.ToBoolean(row["COLUMN_HASDEFAULT"].ToString());
                            metadata.defaultValue = row["COLUMN_DEFAULT"].ToString(); ;
                            metadata.columnType = ((OleDbType)row["DATA_TYPE"]);
                            metadata.isNullable = Convert.ToBoolean(row["IS_NULLABLE"].ToString());

                            table.AddColumn(metadata);
                        }
                    }
                    using (DataTable dt = connector.GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys, new object[] { null, null, table.tableName }))
                    {
                        foreach (DataRow row in dt.Rows)
                        {
                            // Find Columns by name
                            string isPK = row["PK_NAME"].ToString();
                            string colPK = row["COLUMN_NAME"].ToString();
                            table.FindColumn(colPK).isPrimaryKey = true;
                        }
                    }
                    using (DataTable dt = connector.GetOleDbSchemaTable(OleDbSchemaGuid.Foreign_Keys, new object[] { null, null, null, null, null, table.tableName }))
                    {
                        foreach (DataRow row in dt.Rows)
                        {
                            // Find Columns by name
                            string localColumn = row["FK_COLUMN_NAME"].ToString();
                            ColumnMetaData column = table.FindColumn(localColumn);
                            column.hasForeignKey = true;
                            column.fkColumn = row["PK_COLUMN_NAME"].ToString();
                            column.fkTable = row["PK_TABLE_NAME"].ToString();
                        }
                    }
                    connector.Close();
                }
            }
            catch (OleDbException ex)
            {
                Console.Out.WriteLine("Exception fetching metadata for table {0} : {1}", table.tableName, ex.Message);
            }
        }
Exemple #23
0
 /// <summary>
 /// Get Metadata information about a table in a schema in the current database
 /// </summary>
 /// <param name="table">Name of the table to obtain the metadata (columns and primary keys)</param>
 public abstract void QueryTableDefinition(TableMetaData table);
Exemple #24
0
 /// <summary>
 /// Copy the table contents of a table in the current database into a table with the same name in another database
 /// </summary>
 /// <param name="table">Metadata of the table to copy</param>
 /// <param name="target">IDBBackend that will be used to copy the contents of the table</param>
 public abstract void DumpTableContents(TableMetaData table, IDBBackEnd target);
Exemple #25
0
        /// <summary>
        /// Dumps table contents obtained from a OleDbDataReader into the target database
        /// </summary>
        /// <param name="table">Table metadata from where the DataReader is obtained</param>
        /// <param name="reader">DataReader that provides the original data</param>
        public override void DumpTable(TableMetaData table, DbDataReader reader)
        {
            SQLiteTransaction transaction = null;
            string currentQuery = string.Empty;
            try
            {
                using (SQLiteConnection connector = new SQLiteConnection(connectionString))
                {
                    connector.Open();
                    using (SQLiteCommand Cmd = new SQLiteCommand())
                    {
                        Cmd.CommandText = CreateInsertQuery(table);
                        Cmd.Connection = connector;
                        CreateSqlCommandParameters(table, Cmd);

                        int countRecords = 0;
                        transaction = connector.BeginTransaction();
                        while (reader.Read())
                        {
                            foreach (ColumnMetaData colum in table.columns)
                            {
                                SetSqlCommandParameterValueForColumn(colum, Cmd, reader);
                            }
                            currentQuery = Cmd.CommandText;
                            Cmd.ExecuteNonQuery();
                            if (countRecords++ % 100 == 0) System.Console.Out.Write(".");
                        }
                        transaction.Commit();
                        System.Console.Out.WriteLine("done");
                        System.Console.Out.WriteLine("Table dump complete, {0} records copied.", countRecords);
                    }
                    connector.Close();
                }
            }
            catch (SQLiteException ex)
            {
                if (null != transaction)
                    transaction.Rollback();
                Console.Out.WriteLine("SQLite Exception dumping table {0} : {1}\n{2}", table.tableName, ex.Message, currentQuery);
            }
        }
        /// <summary>
        /// Get Metadata information about a table in a schema in the current database
        /// </summary>
        /// <param name="table">Name of the table to obtain the metadata (columns and primary keys)</param>
        public override void QueryTableDefinition(TableMetaData table)
        {
            try
            {
                using (OleDbConnection connector = new OleDbConnection(connectionString))
                {
                    connector.Open();
                    using (DataTable dt = connector.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, table.tableName, null }))
                    {
                        foreach (DataRow row in dt.Rows)
                        {
                            ColumnMetaData metadata = new ColumnMetaData();
                            metadata.columnName        = row["COLUMN_NAME"].ToString();
                            metadata.columnDescription = row["DESCRIPTION"].ToString();;
                            metadata.ordinalPosition   = Convert.ToInt32(row["ORDINAL_POSITION"].ToString());
                            if (row["CHARACTER_MAXIMUM_LENGTH"] != DBNull.Value)
                            {
                                metadata.maxCharSize = Convert.ToInt32(row["CHARACTER_MAXIMUM_LENGTH"].ToString());
                            }
                            if (row["NUMERIC_PRECISION"] != DBNull.Value)
                            {
                                metadata.numericPrecision = Convert.ToInt32(row["NUMERIC_PRECISION"].ToString());
                            }
                            if (row["NUMERIC_SCALE"] != DBNull.Value)
                            {
                                metadata.numericScale = Convert.ToInt32(row["NUMERIC_SCALE"].ToString());
                            }
                            if (row["DATETIME_PRECISION"] != DBNull.Value)
                            {
                                metadata.datetimePrecision = Convert.ToInt32(row["DATETIME_PRECISION"].ToString());
                            }
                            metadata.hasDefault   = Convert.ToBoolean(row["COLUMN_HASDEFAULT"].ToString());
                            metadata.defaultValue = row["COLUMN_DEFAULT"].ToString();;
                            metadata.columnType   = ((OleDbType)row["DATA_TYPE"]);
                            metadata.isNullable   = Convert.ToBoolean(row["IS_NULLABLE"].ToString());

                            table.AddColumn(metadata);
                        }
                    }
                    using (DataTable dt = connector.GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys, new object[] { null, null, table.tableName }))
                    {
                        foreach (DataRow row in dt.Rows)
                        {
                            // Find Columns by name
                            string isPK  = row["PK_NAME"].ToString();
                            string colPK = row["COLUMN_NAME"].ToString();
                            table.FindColumn(colPK).isPrimaryKey = true;
                        }
                    }
                    using (DataTable dt = connector.GetOleDbSchemaTable(OleDbSchemaGuid.Foreign_Keys, new object[] { null, null, null, null, null, table.tableName }))
                    {
                        foreach (DataRow row in dt.Rows)
                        {
                            // Find Columns by name
                            string         localColumn = row["FK_COLUMN_NAME"].ToString();
                            ColumnMetaData column      = table.FindColumn(localColumn);
                            column.hasForeignKey = true;
                            column.fkColumn      = row["PK_COLUMN_NAME"].ToString();
                            column.fkTable       = row["PK_TABLE_NAME"].ToString();
                        }
                    }
                    connector.Close();
                }
            }
            catch (OleDbException ex)
            {
                Console.Out.WriteLine("Exception fetching metadata for table {0} : {1}", table.tableName, ex.Message);
            }
        }
Exemple #27
0
 /// <summary>
 /// Get Metadata information about a table in a schema in the current database
 /// </summary>
 /// <param name="table">Name of the table to obtain the metadata (columns and primary keys)</param>
 public abstract void QueryTableDefinition(TableMetaData table);