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);*/
}
}
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());
}
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;
}
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;
}
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();
}
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;
}
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();
}
}
}
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++;
}
}
}
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();
}
}
}