PHP操作MSSQL数据库类

<?php
class sql
{
    private $link;//连接资源
    private $result;//查询结果
    private $num = 0;//结果行数
    private $params = array();//参数数组
    private $sqlstr;//当前执行的SQL语句或存储过程
    //构造函数，默认连接本机，master数据库，账号sa，空密码
    public function __construct($addr = "127.0.0.1", $db = "master", $user = "sa", $pwd = "123456")
    {
        if (!$this->connect($addr, $db, $user, $pwd))
        {
            throw new Exception("无法连接数据库");
        }
    }
    //析构函数，关闭数据库连接
    public function __destruct()
    {
        $this->clear($this->result);
        $this->close();
    }
    //执行sql语句或存储过程
    public function execute($sql)
    {       
        if (!$this->isProc($sql))
        {
            $stmt = mssql_init($sql, $this->link);
            $param = array_keys($this->params);
            $value = array_values($this->params);
            for ($i = 0, $j = count($this->params); $i < $j; $i++)
            {
                (gettype($value) == integer) ? $sqlType = SQLINT1 : $sqlType = SQLVARCHAR;
                mssql_bind($stmt, $param[$i], $value[$i], $sqlType, false);
            }
            if (!($this->result = mssql_execute($stmt)))
            {
                throw new Exception("存储过程执行出错");
            }
            unset($stmt);           
        }
        else
        {           
            if (!($this->result = @mssql_query($sql, $this->link)))
            {
                throw new Exception("SQL语句执行出错");
            }
        }
        $this->sqlstr = $sql;
        unset($this->params);
        return $this->result;
    }
    //返回执行的SQL语句
    public function getSqlStr()
    {
        return $this->sqlstr;
    }
    //获取返回的行数
    public function getRowNum($sql)
    {
        $this->execute($sql);
        $this->num = mssql_num_rows($this->result);
        return  $this->num;
    }
    //查询后直接返回json格式数据
    public function getJsonResult($sql,$num)
    {
        $this->execute($sql);
        return $this->toJson($num);
    }
    //添加存储过程参数，同时返回添加后的参数数组
    public function addParam($param, $value)
    {
       $this->params[$param] = $value;
       return $this->params;
    }   
    //转换为json格式
    private function toJson($num)
    {
        if (!$this->result) return;
        $num == 0 ? $num = mssql_num_fields($this->result) : $num;
        while($row = mssql_fetch_object($this->result))
        {
            foreach ($row as $key=>$val)
            {
                $rows[$key] = urlencode(iconv("gbk","utf-8",$val));
            }
            $arr[] = $rows;
        }
        $str = $_GET["callback"].'({"totalCount":"'.$num.'","data":'.json_encode($arr).'})';
        return urldecode(iconv("utf-8","gbk",$str));
    }
    //判断是否存储过程
    private function isProc($str)
    {
        return preg_match("/insert/b/",$str) || preg_match("/update/b/",$str) ||
            preg_match("/delete/b/",$str) || preg_match("/select/b/",$str);
    }
    //连接数据库，返回数据库连接资源
    private function connect($addr, $db, $user, $pwd)
    {
        if ($this->link = @mssql_connect($addr, $user, $pwd, true))
        {
            return @mssql_select_db($db, $this->link) ? true : false;
        }
        else
        {
            throw new Exception("无法连接数据库");
        }
    }   
    //清除数据库连接资源
    private function clear($query)
    {
        if ($query) mssql_free_result($query);
    }
    //关闭数据库连接
    private function close()
    {
        if ($this->link) mssql_close($this->link);
    }
}
?>

 

使用举例：

<?php
require_once("sql.php");
class user
{
    private $sql;
    private $func;
    public function __construct()
    {
        $this->sql = new sql();
    }
    public function login($user, $pwd)
    {
        $str = "select * from t_user where username='$user' and password='$pwd'";
        if ($this->sql->getRowNum($str) != 0)
        {
            session_start();
            $_SESSION["username"] = $user;
            echo "{success:true, username:$user}";
        }
        else
        {
            echo "{success:false}";
        }
    }
    public function search($username, $dept, $start, $limit)
    {
        $str = "select * from t_user where 1=1";

        $num = $this->sql->getRowNum($str);
        $str = "select top $limit * from t_user where ";
        $str .= " username not in (select top $start username from t_user order by username desc)";
        $str .= " order by username desc";
        echo $this->sql->getJsonResult($str, $num);
    }
}
?>

例子都是返回Json字符串的，我这里主要是方便前端接口，例如ExtJs。