NET operation of MSSQL (backup, restore, get table data, get field information)

Tag:sql    database    net   

 #region database Management///<summary>//Restore Recovery database///</summary>//<param name= "Filepa        Th "> Save path </param>//<returns></returns> public bool Datarestore (string FilePath) {string[] Connection = confighelper.getappsettings ("sqlserver_wyl_db").            Split (';');            Sqlserverbackup sqlserverbackup = new Sqlserverbackup (); Sqlserverbackup. Server = connection[0].            Substring (7); Sqlserverbackup. Database = connection[1].            Substring (9); Sqlserverbackup. Uid = connection[2].            Substring (4); Sqlserverbackup. PWD = connection[3].            Substring (4); if (sqlserverbackup.            Dbrestore (FilePath)) return true;        else return false; }///<summary>//BACKUP DATABASE///</summary>//<param name= "FilePath" > Save path < /param>//<returns></returns> public bool Databackups (String FilePath) {try {string[] Connection = confighelper.getappsettings ("Sq lserver_wyl_db ").                Split (';');                Sqlserverbackup sqlserverbackup = new Sqlserverbackup (); Sqlserverbackup. Server = connection[0].                Substring (7); Sqlserverbackup. Database = connection[1].                Substring (9); Sqlserverbackup. Uid = connection[2].                Substring (4); Sqlserverbackup. PWD = connection[3].                Substring (4); if (sqlserverbackup.                DbBackup (FilePath)) return true;            else return false;            } catch (Exception) {return false; }}///<summary>///Add Database backup recovery record///</summary>//<param name= "Type" & gt; Type: Backup, restore </param>//<param name= "file" > File name </param>//<param name= "Size" > File size &L t;/param>//<paRam Name= "Createusername" > Create user </param>//<param name= "DB" > Owning database </param>//<param Name= "Memo" > Remarks </param>//<returns></returns> public void Add_backup_restore_log (stri Ng Type, String File, String Size, String createusername, String DB, String Memo) {Loghelper Logger = n            EW Loghelper ("Backup_restore_log");            Hashtable ht = new Hashtable ();            StringBuilder sb = new StringBuilder (); Sb.            Append (Type + "∫"); Sb.            Append (File + "∫"); Sb.            Append (Size + "∫"); Sb.            Append (createusername + "∫"); Sb.            Append (DB + "∫"); Sb.            Append (Memo + "∫"); Sb.            Append (DateTime.Now + "∫"); Sb.            Append ("*"); Logger.writelog (sb.)        ToString ());        }///<summary>//Database backup restore record///</summary>//<returns></returns> Public DataTable getbackup_restore_log_list ()        {Loghelper Logger = new Loghelper ("Backup_restore_log");            string filepath = confighelper.getappsettings ("LogFilePath") + "/backup_restore_log.log"; StreamReader sr = new StreamReader (filepath, encoding.getencoding ("UTF-8"));//Get the encoding of this TXT file string[] strvalue = s R.readtoend (). ToString ().            Split (' * '); Sr.            Close ();            DataTable dt = new DataTable (); Dt.            Columns.Add ("Backup_restore_type", Type.GetType ("System.String")); Dt.            Columns.Add ("Backup_restore_file", Type.GetType ("System.String")); Dt.            Columns.Add ("Backup_restore_size", Type.GetType ("System.String")); Dt.            Columns.Add ("Createusername", Type.GetType ("System.String")); Dt.            Columns.Add ("backup_restore_db", Type.GetType ("System.String")); Dt.            Columns.Add ("Backup_restore_memo", Type.GetType ("System.String")); Dt.            Columns.Add ("CreateDate", Type.GetType ("System.String")); foreach (String item instrvalue) {if (item. Length > 6) {string[] Str_item = Item.                    Split (' ∫ '); DataRow row = dt.                    NewRow (); string[] Typeitem = str_item[0].                    Split ('] '); row["Backup_restore_type"] = typeitem[1].                    Trim ();                    row["Backup_restore_file"] = str_item[1];                    row["backup_restore_size"] = str_item[2];                    row["createusername"] = str_item[3];                    row["backup_restore_db"] = str_item[4];                    row["Backup_restore_memo"] = str_item[5];                    row["CreateDate"] = str_item[6]; Dt.                Rows.Add (row); }} dt. Defaultview.sort = "CreateDate DESC";//datatable sort DataTable dttemp = dt.            Defaultview.totable ();        return dttemp; }///<summary>//Get all table names for the database///</summary>//<returns></returns&Gt            Public DataTable getsysobjects () {StringBuilder strSQL = new StringBuilder ();            Strsql.append ("Select Name as table_name from sysobjects where xtype= ' u ' and status >=0 and Name! = ' Sysdiagrams '"); Return Datafactory.sqldatabase ().        Getdatatablebysql (strSQL); }///<summary>///Get all fields of a table///</summary>//<param name= "object_id" > table        Name </param>///<returns></returns> Public DataTable getsyscolumns (string object_id)            {DataTable dt = new DataTable ();            StringBuilder strSQL = new StringBuilder (); if (!string.                                     IsNullOrEmpty (object_id) && object_id! = "Not selected") {Strsql.append (@ "Select [Column name]=a.name, [Data type]=b.name, [                            Length]=columnproperty (a.id,a.name, ' PRECISION '),         [Is empty]=case when a.isnullable=1 then ' √ ' else ' end, [Default value]=isnull (E.text, '),                                     [Description]=isnull (G.[value], ' no description ') from syscolumns a Left join Systypes b on A.xusertype=b.xusertype Inn Er join sysobjects D on a.id=d.id and d.xtype= ' U ' and d.name<> ' dtproperties ' l EFT Join syscomments E on a.cdefault=e.id left join Sys.extended_properties G on a.id= G.MAJOR_ID and a.colid=g.minor_id left joins Sys.extended_properties F on D.id=f.major                _id and F.minor_id=0 ");                Strsql.append ("Where d.name= '" + object_id + "' ORDER by A.id,a.colorder"); Return Datafactory.sqldatabase ().            Getdatatablebysql (strSQL);        } return DT; } #endregion

NET operation of MSSQL (backup, restore, get table data, get field information)