sqlserver2008及以上数据库操作封装类

php教程|php手册 需要php_serv.dll支持,需要到微软官网下载 支持sqlserver2008(包括)以上版本 sqlserver2005以下就不需要尝试了,分页函数用法不支持的. 类比较简单,就不做演示了 http://www.du52.com/text.php?id=582 无 ?php/** * 数据库管理 * * @author wangaibo168@163.co

需要php_serv.dll支持,需要到微软官网下载
支持sqlserver2008(包括)以上版本
sqlserver2005以下就不需要尝试了,分页函数用法不支持的.
类比较简单,就不做演示了
http://www.du52.com/text.php?id=582

.CodeEntity .code_pieces ul.piece_anchor{width:25px;position:absolute;top:25px;left:-30px;z-index:1000;} .CodeEntity .code_pieces ul.piece_anchor li{width:25px;background: #efe;margin-bottom:2px;} .CodeEntity .code_pieces ul.piece_anchor li{border-left:3px #40AA63 solid;border-right:3px #efe solid;} .CodeEntity .code_pieces ul.piece_anchor li:hover{border-right:3px #40AA63 solid;border-left:3px #efe solid;} .CodeEntity .code_pieces ul.piece_anchor li a{color: #333;padding: 3px 10px;} .CodeEntity .code_pieces .jump_to_code{visibility:hidden;position:relative;} .CodeEntity .code_pieces .code_piece:hover .jump_to_code{visibility:visible;} .CodeEntity .code_pieces .code_piece:hover .jump_to_code a{text-decoration:none;} .CodeEntity .code_pieces h2 i{float:right;font-style:normal;font-weight:normal;} .CodeEntity .code_pieces h2 i a{font-size:9pt;background: #FFFFFF;color:#00A;padding: 2px 5px;text-decoration:none;}

<?php/** * 数据库管理 * * @author wangaibo168@163.com * @charset utf-8 */class Db {    /**     * @var 数据库连接配置     */    private static $DbLink;    /**     * @var 数据库连接配置     */    private static $DbConfig;    /**     * @var 需要执行的SQL语句     */    private static $SqlStr;    /**     * @var 最后错误信息     */    private static $ErrorMsg;    /**     * 默认构造函数     */    private function __construct(){}    /**     * 配置连接参数     * @param $opt     */    public static function configure($opt){        if(!is_array($opt)) return;        self::$DbConfig = $opt;    }    /**     * 设置连接参数     * @param $key     * @param $value     */    public static function set($key,$value){        if(empty($key)) return;        if(!is_array(self::$DbConfig)) self::$DbConfig = array();        self::$DbConfig[$key] = $value;    }    /**     * 读取连接参数     * @param $key     * @return null     */    public static function get($key){        if(!is_array(self::$DbConfig) || empty($key) || !array_key_exists($key,self::$DbConfig)) return null;        return self::$DbConfig[$key];    }    /**     * 数据库连接参数检查     * @return bool     */    public static function check(){        $host = self::get('host');        if(empty($host)) return false;        $port = self::get('port');        if(!is_numeric($port) || $port65535) return false;        $username = self::get('username');        if(empty($username)) return false;        $database = self::get('database');        if(empty($database)) return false;        $charset = self::get('charset');        if(empty($charset)) return false;        return true;    }    /**     * 连接初始化     * @param bool $reconnect     */    public static function connect($reconnect=false){        if(!$reconnect && is_resource(self::$DbLink)) return;        self::close();        if(!self::check()) die('Database Configuration Error');        $opt = array('Database'=>self::get('database'),'CharacterSet'=>self::get('charset'),'UID'=>self::get('username'),'ReturnDatesAsStrings'=>true,'PWD'=>self::get('password'));        self::$DbLink = sqlsrv_connect(self::get('host').','.self::get('port'),$opt);        if(!is_resource(self::$DbLink)){            $err = sqlsrv_errors();            die('Database Connection Error('.$err[0]['message'].')');        }        // 测试连接是否可用        /*        $stmt = sqlsrv_query(self::$DbLink,'select 1 num');        if(!is_resource($stmt)){            $err = sqlsrv_errors();            die('Database Query Error('.$err[0]['message'].')');        }        sqlsrv_free_stmt($stmt);        */    }    /**     * 查询SQL语句     * @param $sql     * @param array $params     * @return array|bool     */    public static function executeQuery($sql,$params=array()){        self::$SqlStr = $sql;        self::$ErrorMsg = '';        if(empty($sql)) return false;        self::connect();        if(!is_array($params)){            $params = array();        }        $stmt = sqlsrv_query(self::$DbLink,self::$SqlStr,$params);        if(!is_resource($stmt)){            $err = sqlsrv_errors();            self::$ErrorMsg = $err[0]['message'];            return false;        }        $arr = explode(';',$sql);        if(count($arr)>1){            for($i=count($arr);$i>1;$i--){sqlsrv_next_result($stmt);            }        }        $rows = array();        while($row = sqlsrv_fetch_array($stmt,SQLSRV_FETCH_ASSOC)){            $rows[] = $row;        }        sqlsrv_free_stmt($stmt);        return $rows;    }    /**     * 查询SQL语句     * @param $sql     * @param array $params     * @return bool|int     */    public static function executeCount($sql,$params=array()){        self::$SqlStr = $sql;        self::$ErrorMsg = '';        if(empty($sql)) return false;        self::connect();        if(!is_array($params)){            $params = array();        }        $stmt = sqlsrv_query(self::$DbLink,self::$SqlStr,$params);        if(!is_resource($stmt)){            $err = sqlsrv_errors();            self::$ErrorMsg = $err[0]['message'];            return false;        }        $row = sqlsrv_fetch_array($stmt,SQLSRV_FETCH_NUMERIC);        $count = 0;        if(is_array($row) && count($row)==1){            $count = intval($row[0]);        }        sqlsrv_free_stmt($stmt);        return $count;    }    /**     * 执行SQL语句     * @param $sql     * @param array $params     * @return bool|int     */    public static function executeUpdate($sql,$params=array()){        self::connect();        self::$SqlStr = $sql;        self::$ErrorMsg = '';        if(empty($sql)) return false;        self::connect();        if(!is_array($params)){            $params = array();        }        $stmt = sqlsrv_query(self::$DbLink,self::$SqlStr,$params);        if(!is_resource($stmt)){            $err = sqlsrv_errors();            self::$ErrorMsg = $err[0]['message'];            return false;        }        $num = sqlsrv_rows_affected($stmt);        sqlsrv_free_stmt($stmt);        return $num;    }    /**     * 返回服务器信息     * @return array     */    public static function serverInfo(){        self::connect();        return sqlsrv_server_info(self::$DbLink);    }    /**     * 返回客户端信息     * @return array|null     */    public static function clientInfo(){        self::connect();        return sqlsrv_client_info(self::$DbLink);    }    /**     * 构建表SQL     * @param $table     * @return string     */    private static function tableSql($table){        if(empty($table)) return '';        if(is_array($table)){            $arr = array();            foreach($table as $k=>$v){$arr[] = '['.$v.'] '.$k;            }            $tableSql = implode(',',$arr);        }else{            $tableSql = '['.$table.']';        }        return $tableSql;    }    /**     * 构建字段SQL     * @param $field     * @return string     */    private static function fieldSql($field){        if(empty($field)) return '*';        if(is_array($field)){            $fieldSql = '['.implode('],[',$field).']';        }else{            $fieldSql = $field;        }        return $fieldSql;    }    /**     * 构建条件SQL     * @param $where     * @return string     */    private static function whereSql($where){        if(empty($where)) return '';        $whereSql = ' where ';        if(is_array($where)){            $whereSql .= implode(' and ',$where);        }else{            $whereSql .= $where;        }        return $whereSql;    }    /**     * 构建排序SQL     * @param $order     * @return string     */    private static function orderSql($order){        if(empty($order)) return '';        $orderSql = ' order by ';        if(is_array($order)){            $orderSql .= implode(',',$order);        }else{            $orderSql .= $order;        }        return $orderSql;    }    /**     * 统计数据量     * @param $table     * @param $where     * @param null $values     * @return bool|int     */    public static function count($table,$where,$values=null){        if(empty($table)) return false;        $tableSql = self::tableSql($table);        $whereSql = self::whereSql($where);        self::$SqlStr = 'select count(*) from '.$tableSql.$whereSql;        return self::executeCount(self::$SqlStr,$values);    }    /**     * 添加数据     * @param $table     * @param $data     * @return bool|int|string     */    public static function add($table,$data){        if(empty($table) || !is_array($data) || count($data)==0) return false;        self::connect();        $fields = array();        $values = array();        $places = array();        foreach($data as $key=>$value){ // 忽略以下划线开头的键            if(stripos($key,'_')===0) continue;            $fields[] = '['.$key.']';            $values[] = $value;            $places[] = '?';        }        self::$SqlStr = 'insert into ['.$table.']('.implode(',',$fields).')values('.implode(',',$places).')';        $num = self::executeUpdate(self::$SqlStr,$values);        return $num>0;    }    /**     * 插入数据并获取最后插入数据的ID(自增长数据)     * @param $table     * @param $data     * @return bool|int|string     */    public static function autoIdAdd($table,$data){        if(empty($table) || !is_array($data) || count($data)==0) return false;        self::connect();        $fields = array();        $values = array();        $places = array();        foreach($data as $key=>$value){ // 忽略以下划线开头的键            if(stripos($key,'_')===0) continue;            $fields[] = '['.$key.']';            $values[] = $value;            $places[] = '?';        }        self::$SqlStr = 'insert into ['.$table.']('.implode(',',$fields).')values('.implode(',',$places).');select top 1 SCOPE_IDENTITY() id';        $rows = self::executeQuery(self::$SqlStr,$values);        if(!is_array($rows) || count($rows)==0) return false;        $id = $rows[0]['id'];        if(!is_numeric($id)) return false;        return $id;    }    /**     * 更新数据     * @param $table     * @param $data     * @param $where     * @param null $value     * @return bool     */    public static function update($table,$data,$where,$value=null){        if(empty($table) || empty($where)) return false;        self::connect();        $whereSql = self::whereSql($where);        $values = array();        $places = array();        foreach($data as $key=>$v){            if(stripos($key,'_')===0) continue;            $values[] = $v;            $places[] = '['.$key.']=?';        }        foreach($value as $v){            $values[] = $v;        }        self::$SqlStr = 'update ['.$table.'] set '.implode(',',$places).$whereSql;        $num = self::executeUpdate(self::$SqlStr,$values);        return $num>0;    }    /**     * 删除数据     * @param $table     * @param $where     * @param null $values     * @return bool     */    public static function delete($table,$where,$values=null){        if(empty($table) || empty($where)) return false;        self::connect();        $tableSql = self::tableSql($table);        $whereSql = self::whereSql($where);        self::$SqlStr = 'delete from '.$tableSql.$whereSql;        $num = self::executeUpdate(self::$SqlStr,$values);        return $num>0;    }    /**     * 查询数据(全部)     * @param $table     * @param $field     * @param $where     * @param $order     * @param null $values     * @return array|bool     */    public static function fetchAll($table,$field,$where,$order,$values=null){        if(empty($table)) return false;        $tableSql = self::tableSql($table);        $fieldSql = self::fieldSql($field);        $whereSql = self::whereSql($where);        $orderSql = self::orderSql($order);        self::$SqlStr = 'select '.$fieldSql.' from '.$tableSql.$whereSql.$orderSql;        $rows = self::executeQuery(self::$SqlStr,$values);        return $rows;    }    /**     * 查询数据(分页)     * @param $table     * @param $page     * @param $size     * @param $field     * @param $where     * @param $order     * @param null $values     * @return array|bool     */    public static function fetchPage($table,$page,$size,$field,$where,$order,$values=null){        if(empty($table)) return false;        $tableSql = self::tableSql($table);        $fieldSql = self::fieldSql($field);        $whereSql = self::whereSql($where);        $orderSql = self::orderSql($order);        $size = intval($size);        if($size<=0) $size = 10;        $start = ($page-1)*$size;        if($start'.$start;        $rows = self::executeQuery(self::$SqlStr,$values);        return $rows;    }    /**     * 查询数据(单条)     * @param $table     * @param $field     * @param $where     * @param $order     * @param null $values     * @return bool     */    public static function fetchOne($table,$field,$where,$order,$values=null){        if(empty($table)) return false;        $tableSql = self::tableSql($table);        $fieldSql = self::fieldSql($field);        $whereSql = self::whereSql($where);        $orderSql = self::orderSql($order);        self::$SqlStr = 'select top 1 '.$fieldSql.' from '.$tableSql.$whereSql.$orderSql;        $rows = self::executeQuery(self::$SqlStr,$values);        if(!is_array($rows) || count($rows)!=1) return false;        return $rows[0];    }    /**     * 获取单个字段数据     * @param $table     * @param $name     * @param string $def     * @param null $where     * @param null $order     * @param null $values     * @return string     */    public static function fetchField($table,$name,$def='',$where=null,$order=null,$values=null){        if(empty($name)) return $def;        $data = self::fetchOne($table,$name,$where,$order,$values);        if(!is_array($data)) return $def;        return $data[$name];    }    /**     * 获取最后插入的数据ID     * @param $table     * @return int|string     */    public static function lastId($table){        $sql = 'select top 1 IDENT_CURRENT(\'['.$table.']\') id';        $rows = self::executeQuery($sql);        if(!is_array($rows) || count($rows)!=1) return -1;        $id = $rows[0]['id'];        if(!is_numeric($id)) return -1;        return $id;    }    /**     * 获取最后执行的SQL语句     * @return mixed     */    public static function lastSql(){        return self::$SqlStr;    }    /**     * 获取错误     * @return mixed     */    public static function lastError(){        return self::$ErrorMsg;    }    /**     * 获取连接字串     * @return mixed     */    public static function getDb(){        return self::$DbLink;    }    /**     * 关闭连接     */    public static function close(){        if(is_resource(self::$DbLink)) sqlsrv_close(self::$DbLink);        self::$DbLink = null;    }}?>