/// <summary> /// deleteTreeNode: 删除树节点 /// </summary> /// <param name="_table">表名</param> /// <param name="_id">要删除的节点ID</param> /// <returns>操作结果</returns> public string deleteTreeNode(string _table, int _id) { SqlTrans _trans = new SqlTrans(this); string _val = String.Empty; try { string _pid = _trans.execScalar("select pid from {0} where id={1};", _table, _id); if (!Native.isEmpty(_pid)) { string _sql = @"update {0} set delFlag=1 where pid={1} or id={1};update {0} set sons=sons-1 where id={2};"; _val = _trans.execNonQuery(_sql, _table, _id, _pid).ToString(); _trans.commit(); } else { _val = Native.getErrorMsg("在表({0})中id={1}的节点不存在", _table, _id); } } catch (Exception e) { _val = Native.getErrorMsg(e.Message); _trans.rollback(); } finally { _trans.close(); } return(_val); }
/// <summary> /// createDB: 创建数据库 /// </summary> /// <param name="_DBName">数据库名</param> /// <returns></returns> public string createDB(string _DBName, string _DataPath) { string _val = String.Empty, _copyTableSQLs = String.Empty; SqlTrans _trans = new SqlTrans(this); string _sql = @"use master; if exists (select * from sysdatabases where name='{0}') drop database {0}; EXEC sp_configure 'show advanced options', 1; RECONFIGURE; EXEC sp_configure 'xp_cmdshell', 1; RECONFIGURE; exec xp_cmdshell 'mkdir {1}', NO_OUTPUT; create database {0} on primary (name='{0}_data',fileName='{1}\\{0}_data.mdf',size=10MB,filegrowth=10%) log on (name='{0}_log',fileName='{1}\\{0}_data.ldf',size=1MB,maxsize=20MB,filegrowth=10%)"; helper.execNonQuery(_sql, _DBName, _DataPath); string[] _tables = execQuery("Select name FROM {0}..SysObjects Where XType='U' and charindex('SYS_',name)<>1 ORDER BY Name;", DB_TEMPLATE).Split(getRSplit().ToCharArray()); for (int i = 0, _len = _tables.GetLength(0); i < _len; i++) { _copyTableSQLs += MString.format("select * into {0}.dbo.{2} from {1}.dbo.{2};", _DBName, DB_TEMPLATE, _tables[i]); } try { _trans.execNonQuery(_copyTableSQLs); _trans.commit(); } catch (Exception e) { _val = e.Message; _trans.rollback(); } finally { _trans.close(); } return(_val); }
/*树状表结构的基本操作 --begin*/ #region updateTreeOrder: 更新树结构的treeOrder /// <summary> /// updateTreeOrder: 更新树结构的treeOrder /// </summary> /// <param name="table">表名</param> /// <param name="condition">where条件 如: id=12</param> /// <param name="treeOrder">更新之后的treeOrder值</param> /// <returns></returns> public string updateTreeOrder(string table, string condition, int treeOrder) { SqlTrans _trans = new SqlTrans(this); string _val = String.Empty; try { _val = _trans.execReader(""); _trans.commit(); }catch (Exception e) { _val = Native.getErrorMsg(e.Message.ToString()); _trans.rollback(); }finally { _trans.close(); } return(_val); }
/// <summary> /// orderTreeNode: 根据升序或降序尽心修改顺序 /// </summary> /// <param name="_table">表名</param> /// <param name="_id">要排序操作的ID值</param> /// <param name="_ifAsc">是否按照升序方式排序, true: 升序, false: 降序</param> /// <returns></returns> public string orderTreeNode(string _table, int _id, bool _ifAsc) { SqlTrans _trans = new SqlTrans(this); string _val = String.Empty, _sign = ">", _fn = "min"; if (_ifAsc) { _sign = "<"; _fn = "max"; } try { Json _node = _trans.execJson(MString.getSelectStr(_table, "pid, treeOrder", "id=" + _id)); if (_node != null) { int _pid = _node.getInt("pid"), _treeOrder = _node.getInt("treeOrder"); Json _target = _trans.execJson("select top 1 id,treeOrder from {0} where pid={1} and treeOrder=(select {2}(treeOrder) from {0} where pid={1} and treeOrder{3}{4});", _table, _pid, _fn, _sign, _treeOrder); if (_target != null) { string _sql = "update {0} set treeOrder={1} where id={2};"; _sql = MString.format(_sql, _table, _target.getValue("treeOrder"), _id); _sql += "update {0} set treeOrder={1} where id={2};"; _sql = MString.format(_sql, _table, _treeOrder, _target.getValue("id")); _trans.execNonQuery(_sql); } else { Native.writeToPage(Native.getErrorMsg("id是{0}的记录指针已经是第一行或最后一行", _id)); } } else { Native.writeToPage(Native.getErrorMsg("在表({0})中不存在id是{1}的记录", _table, _id)); } _trans.commit(); } catch (Exception e) { _val = Native.getErrorMsg(e.Message); _trans.rollback(); } finally { _trans.close(); } return(_val); }
/// <summary> /// moveTreeNode: 移动树节点 /// </summary> /// <param name="table">表名</param> /// <param name="id"></param> public void moveTreeNode(string table, int id) { SqlTrans _trans = new SqlTrans(this); string _val = String.Empty; try { _val = _trans.execReader(""); _trans.commit(); } catch (Exception e) { _val = Native.getErrorMsg(e.Message.ToString()); _trans.rollback(); } finally { _trans.close(); } }
/// <summary> /// addTreeNode: 添加树节点 /// </summary> /// <param name="_table">表名</param> /// <param name="_pid">父节点ID</param> /// <param name="_key">字段列表</param> /// <param name="_values">值列表</param> /// <returns>返回当前树节点ID</returns> public string addTreeNode(string _table, int _pid, string jsonStr) { SqlTrans _trans = new SqlTrans(this); string _val = String.Empty; try { string[] _kv = MConvert.toKV(jsonStr); _val = _trans.addTreeNode(_table, _pid, _kv[0], _kv[1]); _trans.commit(); } catch (Exception e) { _trans.rollback(); _val = Native.getErrorMsg(e.Message); } finally { _trans.close(); } return(_val); }
/// <summary> /// deleteDB: 删除数据库 /// </summary> /// <param name="_DBName">数据库名</param> public void deleteDB(string _DBName) { SqlTrans _trans = new SqlTrans(this); string _val = String.Empty; try { string _sql = "use master;drop database {0};"; _trans.execNonQuery(_sql, _DBName); _trans.commit(); } catch (Exception e) { _val = e.Message; _trans.rollback(); } finally { _trans.close(); } }
/** * 对数据库的基本通用操作函数有一下方法: * getAllTable: 得到连接数据库的所有表 * getTableFields: 得到连接数据库的所有表 * getAllProcs: 得到数据库的所有存储过程 * tableExist: 判断数据库是否存在表名是tableName的表 * execSql: 通用执行sql语句的存储过程 * getTableInfo: 获取某张表的字段信息 * select: 查询 * insert: 插入 * delete: 删除 * update: 修改 **/ #region addColumn: 添加列 /// <summary> /// addColumn: 添加列 /// </summary> /// <param name="table">字符串要写到的文件路径</param> /// <param name="colName">要写的文件内容</param> /// <param name="colType">字符串要写到的文件路径</param> /// <param name="colIdx">要写的文件内容</param> /// <returns>返回是否成功</returns> public string addColumn(string table, string colName, string colType, int colid) { SqlTrans _trans = new SqlTrans(this); string _result = String.Empty; try{ _result = _trans.execReader("select 1 from sysobjects where name='{0}' and xtype='u';", table); if (Native.isNullEmpty(_result)) { _result = Native.getErrorMsg("表({0})还未创建哦", table); } else { _result = _trans.execReader("select 1 from syscolumns where id=object_id('{0}') and name='{1}';", table, colName); if (!Native.isNullEmpty(_result)) { _result = Native.getErrorMsg("表({0})已经有这个列({1})了哦", table, colName); } else { string _colid = _trans.execScalar(@" declare @colid_max int, @colid int; select @colid_max=max(colid) from syscolumns where id=object_id('{0}'); if {1}>@colid_max or {1}<1 set @colid={1}+1 else set @colid={1} select @colid as 'colid'; ", table, colid); _trans.execNonQuery("alter table {0} add {1} {2};", table, colName, colType); string _colid_max = _trans.execScalar(@"select colid from syscolumns where id=object_id('{0}') and name = '{1}';", table, colName); if (_trans.execScalar("select @@rowcount;") != "1") { _result = Native.getErrorMsg("加一个新列不成功,请检查你的列类型是否正确"); } else { _result = execQuery(@" declare @sql varchar(1000); exec sp_configure 'allow updates', 0; exec sp_configure 'allow updates', 1; exec sp_configure 'show advanced options', 1; RECONFIGURE WITH OVERRIDE; set @sql = 'update syscolumns set colid=-1 where id=object_id(''{0}'') and colid='+cast({1} as varchar(10)); exec(@sql); set @sql = 'update syscolumns set colid=colid+1 where id=object_id(''{0}'') and colid>='+cast({2} as varchar(10)); exec(@sql); set @sql = 'update syscolumns set colid='+cast({2} as varchar(10))+' where id=object_id(''{0}'') and name=''{3}'';'; exec(@sql);", table, _colid_max, _colid, colName); } } } _trans.commit(); }catch (Exception e) { _trans.rollback(); _result = Native.getErrorMsg(e.Message.ToString()); }finally{ _trans.close(); } return(_result); }