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 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; }