diff options
Diffstat (limited to 'src/FsSqlServer.cs')
| -rw-r--r-- | src/FsSqlServer.cs | 357 |
1 files changed, 357 insertions, 0 deletions
diff --git a/src/FsSqlServer.cs b/src/FsSqlServer.cs new file mode 100644 index 0000000..7b158aa --- /dev/null +++ b/src/FsSqlServer.cs @@ -0,0 +1,357 @@ +/* + * FsGateway - navigate a database structure as directory tree + * Copyright (C) 2009-2010 Torello Querci <torello@torosoft.com> + * + * This program is free software; you can redistribute it and/or modify + * it under the terms of the GNU General Public License as published by + * the Free Software Foundation; either version 2 of the License, or + * (at your option) any later version. + * + * This program is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + * GNU General Public License for more details. + * + * You should have received a copy of the GNU General Public License along + * with this program; if not, write to the Free Software Foundation, Inc., + * 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA. + */ + +using System; +using System.Data; +using System.Collections.Generic; +using Mono.Fuse; +using System.Data.SqlClient; + +namespace FsGateway +{ + + + public class FsSqlServer : IFsDb + { + + private IDbConnection dbcon=null; + private string connectionString=null; + private bool _isConnected=false; + + public FsSqlServer() + { + } + + public FsSqlServer(string host, string database, string user, string password, string port) + { + + string connectionString = "Server="+host+","+port+";" + + "Database="+database+";" + + "User ID="+user+";" + + "Password="+password+";"; + Connect(connectionString); + } + + public List<string> getTypeOfObjects() { + List<string> names=new List<string>(); + + names.Add ("/tables"); + names.Add ("/views"); + names.Add ("/indexes"); + + return names; + } + + public void Dispose() { + Unconnect(); + _isConnected=false; + } + + public string storageType { + get { + return "SqlServer"; + } + } + + public string Usage { + get { + return "Specify the connection parameter like this one: \"Server=localhost,port; Database=mydb; User ID=username;Password=password;\r\n" + +"or like this one: \"Server=localhost,port; Database=mydb; User ID=domainname\\username;Password=password;Integrated Security=SSPI\r\n" + +"For detail information about the parameters connection look at http://www.mono-project.com/SQLClient"; + } + } + + public bool isConnect { + get { + return _isConnected; + } + } + + public bool Connect() { + return Connect(this.connectionString); + } + + public bool Connect(string connectionString) { + bool res=false; + + if (dbcon!=null && dbcon.State==System.Data.ConnectionState.Open) { + Unconnect(); + } + + try { + dbcon = new System.Data.SqlClient.SqlConnection(connectionString); + dbcon.Open(); + this.connectionString=connectionString; + res=true; + _isConnected=true; + +// readVersionNumber(); + + } catch (Exception ex) { + System.Console.Out.WriteLine("Exception during database connection opening. Error message: "+ex.Message); + dbcon=null; + } + return res; + } + + public void Unconnect() { + bool res=false; + + if (dbcon!=null && dbcon.State==System.Data.ConnectionState.Open) { + dbcon.Close(); + dbcon=null; + res=false; + } + + _isConnected=true; + return; + } + + public SortedList<string,Table> getTables() { + + SortedList<string,Table> tableList=null; + string sql; + IDataReader reader=null; + + // Check for DB Connection + if (dbcon!=null) { + + tableList=new SortedList<string,Table>(); + + IDbCommand dbcmd = dbcon.CreateCommand(); + + lock (dbcmd) { + sql = "select name, user_name(uid) from sysobjects where type='U'" + ; + + try { + dbcmd.CommandText = sql; + reader = dbcmd.ExecuteReader(); + while(reader.Read()) { + Table table=new Table(null,reader.GetString(0)); + tableList.Add(table.ToString(), table); + } + } catch (Exception ex) { + Console.WriteLine("Exception reading of the tables list : "+ex.Message); + Console.WriteLine("List tables: SQL=" + sql); + } + reader.Close(); + + // ReRead data for fetching detail + foreach (Table table in tableList.Values) { + + sql = "SELECT COLUMN_NAME, data_type, CHARACTER_MAXIMUM_LENGTH " + + "FROM INFORMATION_SCHEMA.COLUMNS " + + "WHERE TABLE_NAME = '"+table.Name+"'"; + string listFilesStr=""; + try { + dbcmd.CommandText = sql; + reader = dbcmd.ExecuteReader(); + listFilesStr=""; + while (reader.Read()) { + Field field; + if (reader.IsDBNull(reader.GetOrdinal("CHARACTER_MAXIMUM_LENGTH"))) { + field=new Field(reader.GetString(reader.GetOrdinal("Column_name")),reader.GetString(reader.GetOrdinal("data_type"))); + } else { + field=new Field(reader.GetString(reader.GetOrdinal("Column_name")),reader.GetString(reader.GetOrdinal("data_type"))+"("+reader.GetInt32(reader.GetOrdinal("CHARACTER_MAXIMUM_LENGTH"))+")"); + } + table.Fields.Add(field.Name,field); + } + + reader.Close(); + + table.Script = "CREATE TABLE "+table.ToString()+"\n" + + "(\t"; + string separator=""; + foreach (Field field in table.Fields.Values) { + table.Script += separator+field.Name+"\t"+field.Type; + separator=",\n\t"; + } + table.Script += "\n);\n"; + + } catch (Exception ex) { + Console.WriteLine("Exception reading the tables fields detail for table: " + table.ToString()+ " message : "+ex.Message); + Console.WriteLine("SQL used: "+sql); + Console.WriteLine(ex.StackTrace); + } + + } + } + + // clean up + reader.Close(); + reader = null; + dbcmd.Dispose(); + dbcmd = null; + + } + + return tableList; + } + + public SortedList<string,View> getViews() { + + string sql; + IDataReader reader=null; + SortedList<string,View> viewList=null; + View view; + + // Check for DB Connection + if (dbcon!=null) { + + viewList=new SortedList<string,View>(); + + IDbCommand dbcmd = dbcon.CreateCommand(); + lock (dbcmd) { + sql = "select name, user_name(uid) from sysobjects where type='V'" + ; + + try { + dbcmd.CommandText = sql; + reader = dbcmd.ExecuteReader(); + while(reader.Read()) { + view=new View(null,reader.GetString(0),null); + viewList.Add(view.ToString(), view); + } + } catch (Exception ex) { + Console.WriteLine("Exception reading of the view list : "+ex.Message); + Console.WriteLine("List views: SQL=" + sql); + } + reader.Close(); + + // ReRead data for fetching detail + foreach (View viewDetail in viewList.Values) { + + sql = "SELECT COLUMN_NAME, data_type, CHARACTER_MAXIMUM_LENGTH " + + "FROM INFORMATION_SCHEMA.COLUMNS " + + "WHERE TABLE_NAME = '"+viewDetail.Name+"'"; + SortedList<string,Field> listFields=new SortedList<string,Field>(); + string listFilesStr=""; + try { + dbcmd.CommandText = sql; + reader = dbcmd.ExecuteReader(); + listFilesStr=""; + while (reader.Read()) { + Field field; + if (reader.IsDBNull(reader.GetOrdinal("CHARACTER_MAXIMUM_LENGTH"))) { + field=new Field(reader.GetString(reader.GetOrdinal("Column_name")),reader.GetString(reader.GetOrdinal("data_type"))); + } else { + field=new Field(reader.GetString(reader.GetOrdinal("Column_name")),reader.GetString(reader.GetOrdinal("data_type"))+"("+reader.GetInt32(reader.GetOrdinal("CHARACTER_MAXIMUM_LENGTH"))+")"); + } + listFields.Add(field.Name,field); + } + + reader.Close(); + reader = null; + + viewDetail.Script = "CREATE VIEW "+viewDetail.ToString()+"\n" + + "(\t"; + string separator=""; + foreach (Field field in listFields.Values) { + viewDetail.Script += separator+field.Name+"\t"+field.Type; + separator=",\n\t"; + } + viewDetail.Script += "\n);\n"; + + } catch (Exception ex) { + Console.WriteLine("Exception reading the views fields detail for table: " + viewDetail.ToString()+ " message : "+ex.Message); + Console.WriteLine("SQL used: "+sql); + Console.WriteLine(ex.StackTrace); + } + + } + } + + // clean up + dbcmd.Dispose(); + dbcmd = null; + + } + + return viewList; + + } + + public SortedList<string,Index> getIndexes() { + + SortedList<string,Index> indexList=null; + Index index; + string sql; + List<string> listTables=new System.Collections.Generic.List<string>(); + IDataReader reader=null; + + // Check for DB Connection + if (dbcon!=null) { + + indexList=new SortedList<string,Index>(); + + IDbCommand dbcmd = dbcon.CreateCommand(); + lock (dbcmd) { + sql = "select name, user_name(uid) from sysobjects where type='U'" + ; + try { + dbcmd.CommandText = sql; + reader = dbcmd.ExecuteReader(); + while(reader.Read()) { + listTables.Add(reader.GetString(0)); + } + } catch (Exception ex) { + Console.WriteLine("Exception reading of the tables list for the indexes : "+ex.Message); + Console.WriteLine("List tables: SQL=" + sql); + } + reader.Close(); + + foreach (string tableName in listTables) { + sql="exec sp_helpindex "+tableName; + dbcmd.CommandText=sql; + reader = dbcmd.ExecuteReader(); + while (reader.Read()) { + index=new Index(tableName, + reader.GetString(reader.GetOrdinal("index_name")), + reader.GetString(reader.GetOrdinal("index_name")), + "CREATE INDEX "+reader.GetString(reader.GetOrdinal("index_name")) + +" ON "+tableName + +" ("+reader.GetString(reader.GetOrdinal("index_keys"))+")" + +";\n"); + indexList.Add(index.ToString(),index); + } + + // clean up + reader.Close(); + reader = null; + } + } + + dbcmd.Dispose(); + dbcmd = null; + } + + return indexList; + } + + public SortedList<string,Sequence> getSequences() { + + SortedList<string,Sequence> sequencesList=null; + sequencesList=new SortedList<string,Sequence>(); + + return sequencesList; + } + + } +} |