예제 #1
0
        public static void RestoreRow(XmlReader rdr, TableDefn table, bool idCol, Hashtable hash)
        {
            string tablename = table.TableName;
            bool   inCell    = false;
            string key       = null;
            string value     = null;

            while (rdr.Read())
            {
                if (rdr.NodeType == XmlNodeType.Whitespace)
                {
                }
                else if (rdr.NodeType == XmlNodeType.Element && !inCell)
                {
                    key = rdr.Name; value = null; inCell = true;
                }
                else if (rdr.NodeType == XmlNodeType.Text && inCell)
                {
                    value = rdr.Value;
                }
                else if (rdr.NodeType == XmlNodeType.EndElement && inCell && rdr.Name == key)
                {
                    // Save Key and Value
                    ColumnDefn column = table.FindColumn(key);
                    if (column != null && column.ColumnType.ToUpper() != "BLOB")
                    {
                        string colType  = column.CLRType().Name;
                        string colValue = null;
                        if ((value == null || value.Length == 0) && column.IsNullable)
                        {
                            colValue = "null";
                        }
                        else if (colType == "String")
                        {
                            colValue = "'" + RemoveNewline(EscapeSlashQuote(value)) + "'";
                        }
                        else if (colType == "DateTime")
                        {
                            DateTime dt = value.ToDateTime().ToLocalTime();
                            colValue = "'" + dt.ToString("yyyy-MM-dd'T'HH:mm:ss") + "'";
                        }
                        else if (colType == "Boolean")
                        {
                            if (value == "true")
                            {
                                colValue = "1";
                            }
                            else
                            {
                                colValue = "0";
                            }
                        }
                        else
                        {
                            colValue = value;
                        }
                        hash.Add(column.ColumnName, colValue);
                    }
                    else
                    {
                        throw new Exception("Could not restore column " + key);
                    }
                    inCell = false;
                }
                else if (rdr.NodeType == XmlNodeType.EndElement && !inCell && rdr.Name == tablename)
                {
                    // End node closes row OK
                    break;
                }
                else
                {
                    throw new Exception("XML format error in RestoreRow.");
                }
            }

            string[] FVArray = AddSeparator(hash, ',');
            string   cols    = FVArray[0];
            string   vals    = FVArray[1];
            string   sql     = "";

            if (idCol)
            {
                sql += "SET IDENTITY_INSERT " + tablename + " ON; ";
            }
            sql += "INSERT INTO " + tablename + " (" + cols + ") VALUES (" + vals + "); ";
            if (idCol)
            {
                sql += "SET IDENTITY_INSERT " + tablename + " OFF; ";
            }
            ExecSQL(sql);
            //Clear out Hashtable to handle multiple records
            hash.Clear();
        }
예제 #2
0
        private void LoadDefinition(string filename)
        {
            int state = 0;

            FileAccess handle = new FileAccess(filename);

            if (!handle.IsOpen())
            {
                return;
            }

            while (!handle.EndOfStream)
            {
                string        s     = handle.ReadLine();
                List <string> words = s.Wordise();
                if (words.Count > 0)                  // Skip blank lines
                {
                    if (state == 0)                   // The Database name must be the first thing we see
                    {
                        if (words[0].ToUpper() == "DATABASE")
                        {
                            if (words.Count == 2)
                            {
                                DatabaseName = words[1];
                                Tables       = new List <TableDefn>();
                                state        = 1;
                            }
                            else
                            {
                                Log.Me.Error("DATABASE statement in " + filename + " either missing database name or has too many values. Statement is: " + s);
                                return;
                            }
                        }
                        else
                        {
                            Log.Me.Error("Missing DATABASE statement in first line of " + filename);
                            return;
                        }
                    }
                    else if (state == 1)                     // Looking for the start of a Table
                    {
                        if (words[0].ToUpper() == "TABLE")
                        {
                            if (words.Count == 2)
                            {
                                table           = new TableDefn();
                                table.TableName = words[1];
                                table.Columns   = new List <ColumnDefn>();
                                Tables.Add(table);
                                state = 2;
                            }
                            else
                            {
                                Log.Me.Error("TABLE statement in " + filename + " either missing table name or has too many values. Statement is: " + s);
                                return;
                            }
                        }
                        else
                        {
                            Log.Me.Error("Missing TABLE statement in " + filename);
                            return;
                        }
                    }
                    else                     // The only other state is 2, which is looking for a column definition or END
                    {
                        if (words.Count == 1)
                        {
                            if (words[0].ToUpper() == "END")
                            {
                                // End of one table definition, set state to look for the next.
                                state = 1;
                            }
                            else
                            {
                                Log.Me.Error("Table definition entry with just one word, and it isn't END, in " + filename + ". Statement is: " + s);
                                return;
                            }
                        }
                        else                         // Must have at least 2 entries which will be parsed as column name and type
                        {
                            ColumnDefn c = new ColumnDefn();
                            c.ColumnName = words[0];
                            c.ColumnType = words[1];
                            c.IsIdentity = false;
                            c.IsNullable = true;
                            c.IsIndex    = false;
                            c.FKey       = null;
                            bool not = false;
                            int  i   = 2;
                            while (i < words.Count)
                            {
                                // Parse optional modifiers
                                if (words[i].ToUpper() == "IDENTITY")
                                {
                                    c.IsIdentity = true; c.IsNullable = false;
                                }
                                else if (words[i].ToUpper() == "INDEX")
                                {
                                    c.IsIndex = true;
                                }
                                else if (words[i].ToUpper() == "NOT")
                                {
                                    not = true;
                                }
                                else if (words[i].ToUpper() == "NULL")
                                {
                                    if (not)
                                    {
                                        c.IsNullable = false;
                                    }
                                    else
                                    {
                                        c.IsNullable = true;
                                    }
                                    not = false;
                                }
                                else if (words[i].ToUpper() == "FKEY")
                                {
                                    if (i == words.Count)
                                    {
                                        Log.Me.Error("Missing foreign key after FKEY in " + filename + ". Statement is: " + s);
                                        return;
                                    }
                                    else
                                    {
                                        ++i;
                                        c.FKey = words[i];
                                    }
                                }
                                else
                                {
                                    Log.Me.Error("Illegal column modifier " + words[i] + " in " + filename + ". Statement is: " + s);
                                    return;
                                }
                                ++i;
                            }
                            // Check for conflicts
                            if (c.IsIdentity && c.IsIndex)
                            {
                                Log.Me.Error("Identity column cannot also be an Index in " + filename + ". Statement is: " + s);
                                return;
                            }
                            if (c.IsIdentity && c.IsNullable)
                            {
                                Log.Me.Error("Identity column cannot be nullable in " + filename + ". Statement is: " + s);
                                return;
                            }
                            if (c.IsIdentity && c.FKey != null)
                            {
                                Log.Me.Error("Identity column cannot also be a foreign key in " + filename + ". Statement is: " + s);
                                return;
                            }
                            if (not)
                            {
                                Log.Me.Error("NOT on its own without NULL in " + filename + ". Statement is: " + s);
                                return;
                            }
                            table.Columns.Add(c);
                        }
                    }
                }
            }
            handle.Close();
        }
예제 #3
0
        public static bool CheckTable(string tablename, TableDefn table, bool warn)
        {
            string query;

            if (Connection.Me.Type == "MySQL")
            {
                query = "SELECT *  FROM " + tablename + " LIMIT 1;";
            }
            else
            {
                query = "SELECT TOP 1 * FROM " + tablename + ";";
            }

            List <string> fields = new List <string>();

            IDbConnection dbConn = Connection.Me.GetConnection();

            try
            {
                dbConn.Open();
                IDbCommand dbCmd = dbConn.CreateCommand();
                dbCmd.CommandType = CommandType.Text;
                dbCmd.CommandText = query;

                using (IDataReader rdr = dbCmd.ExecuteReader())
                {
                    while (rdr.Read())
                    {
                        for (int i = 0; i < rdr.FieldCount; i++)
                        {
                            //Console.WriteLine(rdr.GetName(i) + "=" + rdr.GetValue(i).ToString());
                            string     key  = rdr.GetName(i);
                            Type       type = rdr.GetFieldType(i);
                            ColumnDefn col  = table.FindColumn(key);
                            if (col == null)
                            {
                                warn = true;
                                Console.WriteLine("Database table " + tablename + " has additional column " + key);
                            }
                            else
                            {
                                if (type.Name != col.CLRType().Name)
                                {
                                    // For some reason MySQL bit fields come through as UInt64, so don't report that
                                    // and MSSQL renders FLOAT as Doubles, so we'll ignore that too
                                    if (col.ColumnType == "BIT" && type.Name == "UInt64")
                                    {
                                    }
                                    else if (col.ColumnType == "FLOAT" && type.Name == "Double")
                                    {
                                    }
                                    else
                                    {
                                        warn = true;
                                        Console.WriteLine("Table: " + tablename + ", Column: " + key + ", Database Type: " + type.Name + ", Definition: " + col.ColumnType);
                                    }
                                }
                                fields.Add(key);
                            }
                        }
                    }
                }
                if (fields.Count == 0)
                {
                    Console.WriteLine("Cannot check table " + tablename + " because it is empty.");
                }
                else
                {
                    foreach (ColumnDefn col in table.Columns)
                    {
                        if (!fields.Contains(col.ColumnName))
                        {
                            warn = true;
                            Console.WriteLine("Column " + col.ColumnName + " is missing from " + tablename);
                        }
                    }
                }

                return(warn);
            }
            catch (Exception ex)
            {
                throw new Exception("There was a problem loading table from query [" + query + "] - " + ex.Message, ex);
            }
            finally
            {
                dbConn.Close();
                dbConn = null;
            }
        }