Annonceindlæg fra SoftwareOne
09. oktober 2011 - 00:42
#3
Noget C# kode som illustrerer: using System; using System.Collections.Generic; using System.Data; using System.Data.Common; using System.IO; using System.Text; namespace E { public class DbDump { public delegate bool SelectDb(string db); public delegate bool SelectTbl(string db, string tbl); public delegate bool SelectFld(string db, string tbl,string fld); public static void DumpTbl(IDbConnection con, string db, string tbl, StreamWriter sw, SelectFld selfld) { IDbCommand cmd = con.CreateCommand(); cmd.CommandText = "USE " + db; cmd.ExecuteNonQuery(); cmd.CommandText = "SELECT * FROM " + tbl; List<string> alltbl = new List<string>(); IDataReader rdr = cmd.ExecuteReader(); while(rdr.Read()) { string flds = ""; string vals = ""; for(int i = 0; i < rdr.FieldCount; i++) { if(selfld(db, tbl, rdr.GetName(i))) { if(i > 0) { flds += ","; vals += ","; } flds += rdr.GetName(i); object o = rdr.GetValue(i); if(o is DBNull) { sw.Write("NULL"); } else if(o is int || o is long || o is float || o is bool || o is double || o is decimal) { vals += o.ToString(); } else if(o is string) { vals += ("'" + o.ToString().Replace("'", "''") + "'"); } else { throw new ApplicationException("Unsupported data type"); } } } sw.WriteLine("INSERT INTO {0}({1}) VALUES({2});", tbl, flds, vals); } rdr.Close(); } public static void DumpDb(IDbConnection con, string db, string catsuf, StreamWriter sw, SelectTbl seltbl, SelectFld selfld) { sw.WriteLine("USE {0};", db); IDbCommand cmd = con.CreateCommand(); cmd.CommandText = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_" + catsuf + "='" + db + "' AND TABLE_TYPE='BASE TABLE'"; List<string> alltbl = new List<string>(); IDataReader rdr = cmd.ExecuteReader(); while(rdr.Read()) { string tbl = (string)rdr[0]; if(seltbl(db, tbl)) { alltbl.Add(tbl); } } rdr.Close(); foreach(string tbl in alltbl) { DumpTbl(con, db, tbl, sw, selfld); } } public static void Dump(IDbConnection con, string catsuf, StreamWriter sw, SelectDb seldb, SelectTbl seltbl, SelectFld selfld) { IDbCommand cmd = con.CreateCommand(); cmd.CommandText = "SELECT DISTINCT TABLE_" + catsuf + " FROM INFORMATION_SCHEMA.TABLES"; List<string> alldb = new List<string>(); IDataReader rdr = cmd.ExecuteReader(); while(rdr.Read()) { string db = (string)rdr[0]; if(!db.StartsWith("sys") && !db.Equals("mysql") && seldb(db)) { alldb.Add(db); } } rdr.Close(); foreach(string db in alldb) { DumpDb(con, db, catsuf, sw, seltbl, selfld); } } public static void Dump(string provider, string constr, string catsuf, string fnm, SelectDb seldb, SelectTbl seltbl, SelectFld selfld) { DbProviderFactory dbf = DbProviderFactories.GetFactory(provider); using(IDbConnection con = dbf.CreateConnection()) { con.ConnectionString = constr; con.Open(); using(StreamWriter sw = new StreamWriter(fnm, false, Encoding.UTF8)) { Dump(con, catsuf, sw, seldb, seltbl, selfld); } } } public static void Dump(string provider, string constr, string catsuf, string fnm) { Dump(provider, constr, catsuf, fnm, (db) => true, (db, tbl) => true, (db, tbl, fld) => true); } } public class Program { public static void Main(string[] args) { DbDump.Dump("System.Data.SqlClient", @"Server=ARNEPC4\SQLEXPRESS;Integrated Security=SSPI;Database=Test", "CATALOG", @"C:\work\ss.txt"); DbDump.Dump("MySql.Data.MySqlClient", "Data Source=localhost;Database=Test;User Id=root;Password=", "SCHEMA", @"C:\work\ms.txt"); DbDump.Dump("System.Data.SqlClient", @"Server=ARNEPC4\SQLEXPRESS;Integrated Security=SSPI;Database=Test", "CATALOG", @"C:\work\ss2.txt", (db) => db == "test", (db, tbl) => true, (db, tbl, fld) => fld != "f2"); } } }