using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.IO;
using System.Linq;
using System.Text;
namespace E
{
public class DbDump
{
public delegate bool SelectDatabase(string db);
public delegate bool SelectTable(string db, string tbl);
public delegate bool SelectField(string db, string tbl,string fld);
public static void DumpTableStructure(IDbConnection con, string db, string catsuf, string tbl, StreamWriter sw, SelectField selfld)
{
sw.WriteLine("CREATE TABLE {0} (", tbl);
IDbCommand cmd = con.CreateCommand();
cmd.CommandText = "SELECT c.COLUMN_NAME,c.DATA_TYPE,c.CHARACTER_MAXIMUM_LENGTH,c.NUMERIC_PRECISION,c.NUMERIC_SCALE,c.IS_NULLABLE,tc.CONSTRAINT_TYPE FROM INFORMATION_SCHEMA.COLUMNS c LEFT JOIN (INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu ON tc.TABLE_" + catsuf + " = kcu.TABLE_" + catsuf + " AND tc.TABLE_NAME = kcu.TABLE_NAME AND tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME AND tc.CONSTRAINT_TYPE = 'PRIMARY KEY') ON c.TABLE_" + catsuf + " = tc.TABLE_" + catsuf + " AND c.TABLE_NAME = tc.TABLE_NAME AND c.COLUMN_NAME = kcu.COLUMN_NAME WHERE c.TABLE_" + catsuf + "='" + db + "' AND c.TABLE_NAME='" + tbl + "'";
IDataReader rdr = cmd.ExecuteReader();
List<string> pk = new List<string>();
while(rdr.Read())
{
string fld = (string)rdr[0];
string typ = (string)rdr[1];
string maxlen = rdr[2].ToString();
string prec = rdr[3].ToString();
string scale = rdr[4].ToString();
bool nl = (string)rdr[5] == "YES";
bool ispk = !(rdr[6] is DBNull);
if(selfld(db, tbl, fld))
{
string add = "";
switch(typ)
{
case "int":
// nothing
break;
case "varchar":
case "char":
add = "(" + maxlen + ")";
break;
case "decimal":
case "numeric":
add = "(" + prec + "," + scale + ")";
break;
default:
throw new ApplicationException("Unsupported data type: " + typ);
}
sw.WriteLine(" {0} {1}{2} {3},", fld, typ, add, nl ? "NULL" : "NOT NULL");
}
if(ispk)
{
pk.Add(fld);
}
}
rdr.Close();
sw.WriteLine(" PRIMARY KEY({0})", pk.Count > 0 ? pk.Aggregate((tot,nxt) => tot + "," + nxt) : "?");
sw.WriteLine(");");
}
public static void DumpTableData(IDbConnection con, string db, string tbl, StreamWriter sw, SelectField 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)
{
vals += "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: " + o.GetType().FullName);
}
}
}
sw.WriteLine("INSERT INTO {0}({1}) VALUES({2});", tbl, flds, vals);
}
rdr.Close();
}
public static void DumpTable(IDbConnection con, string db, string catsuf, string tbl, StreamWriter sw, SelectField selfld)
{
DumpTableStructure(con, db, catsuf, tbl, sw, selfld);
DumpTableData(con, db, tbl, sw, selfld);
}
public static void DumpDatabase(IDbConnection con, string db, string catsuf, StreamWriter sw, SelectTable seltbl, SelectField 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)
{
DumpTable(con, db, catsuf, tbl, sw, selfld);
}
}
public static void DumpServer(IDbConnection con, string catsuf, StreamWriter sw, SelectDatabase seldb, SelectTable seltbl, SelectField 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)
{
DumpDatabase(con, db, catsuf, sw, seltbl, selfld);
}
}
public static void Dump(string provider, string constr, string catsuf, string fnm, SelectDatabase seldb, SelectTable seltbl, SelectField 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))
{
DumpServer(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=true;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=true;Database=Test", "CATALOG", @"C:\work\ss2.txt", (db) => db == "test", (db, tbl) => true, (db, tbl, fld) => (tbl != "T1" || fld != "F2"));
}
}
}
er bedre.
I.s.f.at antage at foerste felt er PK, saa finder den faktisk PK.
Ny bruger
Nybegynder
Opret
Preview
