src/LanSpider/Database/Manager.cs

   1 using System;
   2 using System.Collections.Generic;
   3 using System.Data;
   4
   5 using System.IO;
   6
   7 using Extensions;
   8
   9 using LanSpider.Properties;
  10 using System.Diagnostics;
  11 using System.Data.SqlClient;
  12 using System.Configuration;
  13
  14 namespace LanSpider.Database
  15 {
  16     public class Manager : IDisposable
  17     {
  18         private readonly SqlConnection _conn;
  19
  20         private readonly Epam_IndexDataContext _context;
  21
  22         private readonly int _rootId = 1;
  23
  24         public int RootId
  25         {
  26             get
  27             {
  28                 return _rootId;
  29             }
  30         }
  31
  32         #region Static schema methods
  33
  34         /*private static bool SchemaExists( SQLiteConnection connection )
  35         {
  36             SQLiteCommand cmd = new SQLiteCommand( null, connection );
  37             cmd.CommandText = "SELECT name FROM sqlite_master WHERE name=@table;";
  38             cmd.Parameters.Add( new SQLiteParameter( "@table", "node" ) );
  39             object tableName = cmd.ExecuteScalar();
  40
  41             return tableName != null && tableName != DBNull.Value;
  42         }
  43
  44         private static void CreateSchema( SQLiteConnection connection )
  45         {
  46             SQLiteCommand cmd = new SQLiteCommand( null, connection );
  47
  48             cmd.CommandText = Resources.create_db;
  49             cmd.ExecuteNonQuery();
  50         }
  51         */
  52
  53         #endregion Static schema methods
  54
  55         public Manager()
  56         {
  57             _conn = new SqlConnection(ConfigurationManager.ConnectionStrings["DatabaseConnectionString"].ConnectionString);
  58             _conn.Open();
  59             //_context = new Epam_IndexDataContext(ConfigurationManager.ConnectionStrings["DatabaseConnectionString"].ConnectionString);
  60             // TO DO:
  61
  62             /*if ( !SchemaExists( _conn ) )
  63             {
  64                 CreateSchema( _conn );
  65             }*/
  66         }
  67
  68         public void Dispose()
  69         {
  70             _conn.Close();
  71         }
  72
  73         public List<string> GetRootFolders()
  74         {
  75             List<string> roots = new List<string>();
  76
  77             using (SqlCommand cmd = new SqlCommand("SELECT path FROM folder WHERE parent_id = @root_id AND parent_id != folder_id", _conn))
  78             {
  79                 cmd.CommandType = CommandType.Text;
  80                 cmd.Parameters.Add(new SqlParameter("root_id", _rootId));
  81
  82                 using (SqlDataReader results = cmd.ExecuteReader())
  83                 {
  84                     while (results.Read())
  85                     {
  86                         string x = (string)results.GetValue(0);
  87                         roots.Add(x);
  88                     }
  89                 }
  90             }
  91
  92             return roots;
  93         }
  94
  95         private void IndexFile(int shareId, FileInfo file)
  96         {
  97             IndexFile(shareId, file, null);
  98         }
  99
 100         private void IndexFile(int shareId, FileInfo file, DataTable dt)
 101         {
 102             DataRow row = dt.NewRow();
 103             row["parent_folder_id"] = shareId;
 104             row["name"] = file.Name;
 105             row["type"] = file.Extension;
 106             try
 107             {
 108                 row["size"] = file.Length;
 109             }
 110             catch (FileNotFoundException)
 111             {
 112                 row["size"] = -1;
 113             }
 114             dt.Rows.Add(row);
 115             /*using ( SqlCommand cmd = new SqlCommand(@"INSERT INTO ""file"" (parent_folder_id, name, type, size) VALUES (@parent_folder_id, @name, @type, @size)", _conn ) )
 116             {
 117                 if ( trans != null )
 118                 {
 119                     cmd.Transaction = trans;
 120                 }
 121
 122                 cmd.CommandType = CommandType.Text;
 123                 cmd.Parameters.Add( new SqlParameter( "parent_folder_id", shareId ) );
 124                 cmd.Parameters.Add(new SqlParameter("name", file.Name));
 125                 cmd.Parameters.Add(new SqlParameter("type", file.Extension));
 126
 127                 try
 128                 {
 129                     cmd.Parameters.Add(new SqlParameter("size", file.Length));
 130                 }
 131                 catch ( FileNotFoundException )
 132                 {
 133                     cmd.Parameters.Add(new SqlParameter("size", -1));
 134                 }
 135
 136                 cmd.ExecuteNonQuery();
 137             }*/
 138         }
 139
 140         private int GetShareId(string share)
 141         {
 142             return GetShareId(share, null);
 143         }
 144
 145         private int GetShareId(string share, SqlTransaction trans)
 146         {
 147             int shareId = 0;
 148             using (SqlCommand cmd = new SqlCommand("SELECT folder_id FROM folder WHERE path=@path;", _conn))
 149             {
 150                 if (trans != null)
 151                 {
 152                     cmd.Transaction = trans;
 153                 }
 154
 155                 cmd.CommandType = CommandType.Text;
 156                 cmd.Parameters.Add(new SqlParameter("path", share));
 157
 158                 object val = cmd.ExecuteScalar();
 159                 if (val == null || val == DBNull.Value)
 160                 {
 161                     int parentId;
 162
 163                     if (share.Count('\\') == 3) // top level share
 164                     {
 165                         parentId = RootId;
 166                     }
 167                     else
 168                     {
 169                         int index = share.LastIndexOf('\\');
 170                         string parentName = share.Substring(0, index);
 171                         parentId = GetShareId(parentName, trans);
 172                     }
 173                     cmd.CommandType = CommandType.StoredProcedure;
 174                     cmd.CommandText = "AddFolder";
 175                     cmd.Parameters.Add(new SqlParameter("parent_id", parentId));
 176                     cmd.Parameters.Add(new SqlParameter("folder_id", SqlDbType.Int)).Direction = ParameterDirection.Output;
 177                     cmd.ExecuteNonQuery();
 178                     shareId = (int)cmd.Parameters["folder_id"].Value;
 179                 }
 180                 else
 181                     return (int)val;
 182             }
 183
 184             return shareId;
 185         }
 186
 187         public void SaveShareStructure(string shareName, IEnumerable<FileInfo> fileInfos)
 188         {
 189             SqlTransaction trans = _conn.BeginTransaction();
 190
 191             try
 192             {
 193                 SaveShareStructure(shareName, fileInfos, trans);
 194                 trans.Commit();
 195             }
 196             catch (Exception ex)
 197             {
 198                 trans.Rollback();
 199                 Trace.WriteLine("Share " + shareName + " will not be saved: " + ex.Message);
 200                 Console.Error.WriteLine("Share " + shareName + " will not be saved: " + ex.Message);
 201                 //throw;
 202             }
 203         }
 204
 205         public void SaveShareStructure(string shareName, IEnumerable<FileInfo> fileInfos, SqlTransaction trans)
 206         {
 207             using (DataTable dt = new DataTable("[File]"))
 208             {
 209                 SqlBulkCopy bulk = new SqlBulkCopy(_conn, SqlBulkCopyOptions.Default, trans);
 210                 bulk.DestinationTableName = dt.TableName;
 211                 bulk.BatchSize = 1000;
 212                 dt.Columns.Add(new DataColumn("file_id", typeof(int)));
 213                 dt.Columns.Add(new DataColumn("parent_folder_id", typeof(int)));
 214                 dt.Columns.Add(new DataColumn("name", typeof(string)));
 215                 dt.Columns.Add(new DataColumn("type", typeof(string)));
 216                 dt.Columns.Add(new DataColumn("size", typeof(long)));
 217
 218                 foreach (FileInfo fileInfo in fileInfos)
 219                 {
 220                     // TO DO: GetShare as SP
 221                     int shareId = GetShareId(fileInfo.DirectoryName, trans);
 222                     IndexFile(shareId, fileInfo, dt);
 223                 }
 224                 bulk.WriteToServer(dt);
 225                 dt.Clear();
 226                 GC.Collect();
 227             }
 228         }
 229
 230         public void SaveKnownShareStructure(string shareName, IEnumerable<FileInfo> fileInfos)
 231         {
 232             SqlTransaction trans = _conn.BeginTransaction();
 233
 234             try
 235             {
 236                 using (var cmd = new SqlCommand(@"DELETE FROM [FILE] WHERE parent_folder_id in (SELECT folder_id FROM Folder WHERE path like @share +'\%' or path = @share)", _conn))
 237                 {
 238                     cmd.CommandType = CommandType.Text;
 239                     cmd.Transaction = trans;
 240                     cmd.CommandTimeout = 0;
 241                     cmd.Parameters.Add(new SqlParameter("share", shareName));
 242                     cmd.ExecuteNonQuery();
 243                     //cmd.CommandText = @"DELETE FROM FOLDER WHERE folder_id in (select folder_id from FOLDER WHERE PATH like @share + '\%')";
 244                     //cmd.ExecuteNonQuery();
 245                 }
 246
 247                 SaveShareStructure(shareName, fileInfos, trans);
 248                 trans.Commit();
 249             }
 250             catch (Exception ex)
 251             {
 252                 trans.Rollback();
 253                 Trace.WriteLine("Share " + shareName + " will not be saved: " + ex.Message);
 254                 Console.Error.WriteLine("Share " + shareName + " will not be saved: " + ex.Message);
 255                 //throw;
 256             }
 257         }
 258     }
 259 }