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);
}
public void InsertFileIntoCell(string select, string column, string filename)
{
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 void InsertFileIntoCell(string select, string column, string filename)
{
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;
}