Example #1
0
        public void ImportData(string InputPath)
        {
            using (db mydb = new db(dbprovider, dbserver, dbdatabase, dbusername, dbpassword))
            {
                /*System.Data.DataTable dt;

                dt = mydb.ExecuteDataTableSQL(select);

                if (dt.Rows.Count != 1)
                {
                    System.Windows.Forms.MessageBox.Show("Error: Found " + dt.Rows.Count + " rows.");
                    return;
                }

                System.Data.DataRow dr = dt.Rows[0];

                FileInfo fi = new FileInfo(filename);

                int filesize = (int)fi.Length;

                byte[] buf = new byte[filesize];

                using (FileStream fs = new FileStream(filename, FileMode.Open))
                {
                    fs.Read(buf, 0, filesize);
                }

                dr[column] = buf;

                mydb.UpdateDataTable(select, dt);*/
            }
        }
Example #2
0
        public static string[] GetAllDatabases(string DbProvider, string Server, string Username, string Password)
        {
            string sql;
            string Database;

            if (DbProvider == "System.Data.SqlClient")
            {
                Database = "master";
                sql      = "select name from sysdatabases order by name";
            }
            else if (DbProvider == "MySql.Data.MySqlClient")
            {
                Database = "information_schema";
                sql      = "select schema_name name from schemata order by schema_name";
            }
            else
            {
                throw new NotImplementedException("Unsupported database provider: '" + DbProvider + "'.");
            }


            string connstr = db.ConstructConnectionString(
                DbProvider, Server, Database, Username, Password);

            DataTable dtDBs;

            using (db mydb = new db(DbProvider, connstr))
            {
                dtDBs = mydb.ExecuteDataTableSQL(sql);
            }

            IEnumerable <string> dbs =
                from dbname in dtDBs.AsEnumerable()
                select(string) dbname["name"];

            return(dbs.ToArray());
        }
Example #3
0
        public void Export()
        {
            _dbname = "";
            _sql    = "";
            _result = "";


            foreach (string database in databases)
            {
                string connstr = db.ConstructConnectionString(
                    dbprovider, dbserver, database, dbusername, dbpassword);

                using (db mydb = new db(dbprovider, connstr))
                {
                    _sql =
                        "select *" +
                        " from [" + database + "].dbo.sysobjects so" +
                        " join [" + database + "].dbo.syscomments sc on sc.id = so.id" +
                        " where so.type='P' or so.type='PC'" +
                        " order by so.name";

                    mydb.FillSchema = false;
                    DataTable dt = mydb.ExecuteDataTableSQL(_sql);

                    foreach (DataRow dr in dt.Rows)
                    {
                        if ((short)dr["colid"] > 1)
                        {
                            // Multipart SP
                            continue;
                        }

                        string name = dr["name"].ToString();

                        string filename = Path.Combine(path, name + ".sql");

                        string sqlwhere = "(type='P' or type='PC') and name='" + name + "'";

                        DataRow[] rows = dt.Select(sqlwhere, "colid");

                        StringBuilder sb = new StringBuilder();

                        foreach (DataRow drPart in rows)
                        {
                            string text = drPart["text"].ToString();
                            sb.Append(text);
                        }

                        string sp = sb.ToString();

                        if (writemodified)
                        {
                            if (File.Exists(filename))
                            {
                                string buf;
                                using (StreamReader sr = new StreamReader(filename))
                                {
                                    buf = sr.ReadToEnd();
                                }
                                if (buf == sp)
                                {
                                    continue;
                                }
                            }
                        }

                        if (!Directory.Exists(path))
                        {
                            Directory.CreateDirectory(path);
                        }

                        using (StreamWriter sw = new StreamWriter(filename))
                        {
                            sw.Write(sp);
                        }
                    }
                }
            }

            return;
        }
Example #4
0
        public void Export()
        {
            _dbname = "";
            _sql = "";
            _result = "";

            foreach (string database in databases)
            {
                string connstr = db.ConstructConnectionString(
                    dbprovider, dbserver, database, dbusername, dbpassword);

                using (db mydb = new db(dbprovider, connstr))
                {
                    _sql =
                    "select *" +
                    " from [" + database + "].dbo.sysobjects so" +
                    " join [" + database + "].dbo.syscomments sc on sc.id = so.id" +
                    " where so.type='P' or so.type='PC'" +
                    " order by so.name";

                    mydb.FillSchema = false;
                    DataTable dt = mydb.ExecuteDataTableSQL(_sql);

                    foreach (DataRow dr in dt.Rows)
                    {
                        if ((short)dr["colid"] > 1)
                        {
                            // Multipart SP
                            continue;
                        }

                        string name = dr["name"].ToString();

                        string filename = Path.Combine(path, name + ".sql");

                        string sqlwhere = "(type='P' or type='PC') and name='" + name + "'";

                        DataRow[] rows = dt.Select(sqlwhere, "colid");

                        StringBuilder sb = new StringBuilder();

                        foreach (DataRow drPart in rows)
                        {
                            string text = drPart["text"].ToString();
                            sb.Append(text);
                        }

                        string sp = sb.ToString();

                        if (writemodified)
                        {
                            if (File.Exists(filename))
                            {
                                string buf;
                                using (StreamReader sr = new StreamReader(filename))
                                {
                                    buf = sr.ReadToEnd();
                                }
                                if (buf == sp)
                                {
                                    continue;
                                }
                            }
                        }

                        if (!Directory.Exists(path))
                            Directory.CreateDirectory(path);

                        using (StreamWriter sw = new StreamWriter(filename))
                        {
                            sw.Write(sp);
                        }
                    }
                }
            }

            return;
        }
Example #5
0
        public static string[] GetAllDatabases(string DbProvider, string Server, string Username, string Password)
        {
            string sql;
            string Database;

            if (DbProvider == "System.Data.SqlClient")
            {
                Database = "master";
                sql = "select name from sysdatabases order by name";
            }
            else if (DbProvider == "MySql.Data.MySqlClient")
            {
                Database = "information_schema";
                sql = "select schema_name name from schemata order by schema_name";
            }
            else
            {
                throw new NotImplementedException("Unsupported database provider: '" + DbProvider + "'.");
            }

            string connstr = db.ConstructConnectionString(
                DbProvider, Server, Database, Username, Password);

            DataTable dtDBs;
            using (db mydb = new db(DbProvider, connstr))
            {
                dtDBs = mydb.ExecuteDataTableSQL(sql);
            }

            IEnumerable<string> dbs =
                    from dbname in dtDBs.AsEnumerable()
                    select (string)dbname["name"];

            return dbs.ToArray();
        }
Example #6
0
        private string GetTableQuery(db mydb, string tablename, int rowcount)
        {
            string sql;

            if (maxrows != -1 && rowcount > maxrows)
            {
                _sql = sql = "select * from " + tablename + " where 0=1";
            }
            else
            {
                if (sortRows)
                {
                    _sql = sql = "select * from " + tablename + " where 0=1";
                    DataTable dt = mydb.ExecuteDataTableSQL(sql);
                    string pkcols = string.Join(", ", dt.PrimaryKey.Select(c => db.GetColumnName(dbprovider, c.ColumnName)));

                    if (pkcols == string.Empty)
                        _sql = sql = "select * from " + _tablename;
                    else
                        _sql = sql = "select * from " + _tablename + " order by " + pkcols;
                }
                else
                {
                    _sql = sql = "select * from " + _tablename;
                }
            }

            return sql;
        }
Example #7
0
        private void DumpTable(db mydb, string database, string table, string outdir)
        {
            if (excludeTables.Any(c => useRegexpTables ? Regex.IsMatch(table, c) : c == table))
                return;

            if (!Directory.Exists(outdir))
                Directory.CreateDirectory(outdir);

            string filename = Path.Combine(outdir, table + ".txt");

            if (!overwrite)
            {
                if (File.Exists(filename))
                    return;
            }

            string tablename = db.GetTableName(dbprovider, database, table);

            // Check number of rows
            string sqlCount = _sql = "select count(*) from " + tablename;

            int rowcount;

            object o = mydb.ExecuteScalarSQL(sqlCount);
            if (o.GetType() == typeof(long))
            {
                rowcount = (int)(long)o;
            }
            else
            {
                rowcount = (int)o;
            }

            if (!exportempty && rowcount == 0)
            {
                return;
            }

            string sql = GetTableQuery(mydb, tablename, rowcount);

            using (StreamWriter sw = new StreamWriter(filename))
            {
                using (System.Data.Common.DbDataReader reader = mydb.ExecuteReaderSQL(sql))
                {
                    List<int> columns = new List<int>();

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        string colname = reader.GetName(i);
                        if (excludeColumns.Any(c => useRegexpColumns ? Regex.IsMatch(colname, c) : c == colname))
                        {
                            continue;
                        }

                        if (reader.GetFieldType(i) == typeof(byte[]) && !binaryhex && !binaryfile)
                        {
                            continue;
                        }

                        columns.Add(i);
                    }

                    if (sortColumns)
                    {
                        columns = columns
                            .OrderBy(c => reader.GetName(c))
                            .Select(c => c)
                            .ToList();
                    }

                    // Write column names
                    if (header)
                    {
                        bool isFirstCol = true;
                        for (int i = 0; i < columns.Count; i++)
                        {
                            if (reader.GetFieldType(i) == typeof(byte[]) && !binaryhex)
                                continue;

                            if (!isFirstCol)
                                sw.Write(separator);
                            isFirstCol = false;

                            sw.Write(reader.GetName(columns[i]));
                        }
                        sw.WriteLine();
                    }

                    c_c += columns.Count;

                    if (maxrows != -1 && rowcount > maxrows)
                    {
                        sw.WriteLine(rowcount + " rows.");
                    }
                    else
                    {
                        // Write data
                        int rownum = 0;
                        while (reader.Read())
                        {
                            bool isFirstCol = true;
                            for (int i = 0; i < columns.Count; i++)
                            {
                                if (reader.GetFieldType(i) != typeof(byte[]))
                                {
                                    if (!isFirstCol)
                                        sw.Write(separator);
                                    isFirstCol = false;

                                    if (escapecharacters)
                                    {
                                        sw.Write(FixValue(reader.GetValue(columns[i])));
                                    }
                                    else
                                    {
                                        sw.Write(reader.GetValue(columns[i]));
                                    }
                                }
                                else
                                {
                                    if (binaryhex)
                                    {
                                        if (!isFirstCol)
                                            sw.Write(separator);
                                        isFirstCol = false;

                                        if (!reader.IsDBNull(i))
                                        {
                                            int length = (int)reader.GetBytes(i, 0, null, 0, 0);
                                            byte[] buffer = new byte[length];
                                            reader.GetBytes(i, 0, buffer, 0, length);

                                            sw.Write(FixBinaryValue(buffer));
                                        }
                                    }

                                    if (binaryfile)
                                    {
                                        if (!reader.IsDBNull(i))
                                        {
                                            string filename_data = Path.Combine(outdir, table + "_" + reader.GetName(i) + "_" + rownum + "." + extension);

                                            int length = (int)reader.GetBytes(i, 0, null, 0, 0);
                                            byte[] buffer = new byte[length];
                                            reader.GetBytes(i, 0, buffer, 0, length);

                                            WriteBinaryfile(filename_data, buffer);
                                        }
                                    }
                                }
                            }
                            sw.WriteLine();

                            rownum++;
                        }
                    }

                    reader.Close();
                }
            }
        }
Example #8
0
        private void DumpDatabase(string database)
        {
            string connstr = db.ConstructConnectionString(dbprovider, dbserver, database, dbusername, dbpassword);

            using (db mydb = new db(dbprovider, connstr))
            {
                string[] tables = mydb.GetAllTables(dbprovider, database);

                foreach (string table in tables)
                {
                    _tablename = table;

                    string outdir;

                    // Create DB specific subdir if multiple DBs.
                    if (databases.Length > 1)
                        outdir = Path.Combine(path, database);
                    else
                        outdir = path;

                    DumpTable(mydb, database, table, outdir);

                    c_t++;
                }
            }
        }
Example #9
0
        private void DumpTable(db mydb, string database, string table, string outdir)
        {
            if (excludeTables.Any(c => useRegexpTables ? Regex.IsMatch(table, c) : c == table))
            {
                return;
            }

            if (!Directory.Exists(outdir))
            {
                Directory.CreateDirectory(outdir);
            }

            string filename = Path.Combine(outdir, table + ".txt");

            if (!overwrite)
            {
                if (File.Exists(filename))
                {
                    return;
                }
            }

            string tablename = db.GetTableName(dbprovider, database, table);

            // Check number of rows
            string sqlCount = _sql = $"select count(*) from {tablename}";

            int rowcount;

            object o = mydb.ExecuteScalarSQL(sqlCount);

            if (o.GetType() == typeof(long))
            {
                rowcount = (int)(long)o;
            }
            else
            {
                rowcount = (int)o;
            }

            if (!exportempty && rowcount == 0)
            {
                return;
            }

            string sql = GetTableQuery(mydb, tablename, rowcount);

            using (StreamWriter sw = new StreamWriter(filename))
            {
                using (System.Data.Common.DbDataReader reader = mydb.ExecuteReaderSQL(sql))
                {
                    List <int> columns = new List <int>();

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        string colname = reader.GetName(i);
                        if (excludeColumns.Any(c => useRegexpColumns ? Regex.IsMatch(colname, c) : c == colname))
                        {
                            continue;
                        }

                        if (reader.GetFieldType(i) == typeof(byte[]) && !binaryhex && !binaryfile)
                        {
                            continue;
                        }

                        columns.Add(i);
                    }


                    if (sortColumns)
                    {
                        columns = columns
                                  .OrderBy(c => reader.GetName(c))
                                  .Select(c => c)
                                  .ToList();
                    }


                    // Write column names
                    if (header)
                    {
                        bool isFirstCol = true;
                        for (int i = 0; i < columns.Count; i++)
                        {
                            if (reader.GetFieldType(i) == typeof(byte[]) && !binaryhex)
                            {
                                continue;
                            }

                            if (!isFirstCol)
                            {
                                sw.Write(separator);
                            }
                            isFirstCol = false;

                            sw.Write(reader.GetName(columns[i]));
                        }
                        sw.WriteLine();
                    }

                    c_c += columns.Count;

                    if (maxrows != -1 && rowcount > maxrows)
                    {
                        sw.WriteLine($"{rowcount} rows.");
                    }
                    else
                    {
                        // Write data
                        int rownum = 0;
                        while (reader.Read())
                        {
                            bool isFirstCol = true;
                            for (int i = 0; i < columns.Count; i++)
                            {
                                if (reader.GetFieldType(i) != typeof(byte[]))
                                {
                                    if (!isFirstCol)
                                    {
                                        sw.Write(separator);
                                    }
                                    isFirstCol = false;

                                    if (escapecharacters)
                                    {
                                        sw.Write(FixValue(reader.GetValue(columns[i])));
                                    }
                                    else
                                    {
                                        sw.Write(reader.GetValue(columns[i]));
                                    }
                                }
                                else
                                {
                                    if (binaryhex)
                                    {
                                        if (!isFirstCol)
                                        {
                                            sw.Write(separator);
                                        }
                                        isFirstCol = false;

                                        if (!reader.IsDBNull(i))
                                        {
                                            int    length = (int)reader.GetBytes(i, 0, null, 0, 0);
                                            byte[] buffer = new byte[length];
                                            reader.GetBytes(i, 0, buffer, 0, length);

                                            sw.Write(FixBinaryValue(buffer));
                                        }
                                    }

                                    if (binaryfile)
                                    {
                                        if (!reader.IsDBNull(i))
                                        {
                                            string filename_data = Path.Combine(outdir, $"{table}_{reader.GetName(i)}_{rownum}.{extension}");

                                            int    length = (int)reader.GetBytes(i, 0, null, 0, 0);
                                            byte[] buffer = new byte[length];
                                            reader.GetBytes(i, 0, buffer, 0, length);

                                            WriteBinaryfile(filename_data, buffer);
                                        }
                                    }
                                }
                            }
                            sw.WriteLine();

                            rownum++;
                        }
                    }

                    reader.Close();
                }
            }
        }