/*树状表结构的基本操作 --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>
/// 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();
}
}
/**
* 对数据库的基本通用操作函数有一下方法:
* 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);
}
