updateexample.c

通过VC在SQL Server 数据库中进行更新操作

/***********************************************************************
*
*   案例三、验证 DDL和DML中的insert, update, delete语句的使用
*
*   拓展：1、当插入的值中有单引号该如何处理
*         2、如何获得更新、删除影响的记录数
*
*
************************************************************************/

#pragma comment(lib, "ntwdblib.lib")   //导入非标准的库函数文件
#define DBNTWIN32
#include <stdio.h>
#include <windows.h>
#include <sqlfront.h>
#include <sqldb.h>

PDBPROCESS dbconnect(const char *user, const char *password, const char *dbserver);
// Forward declarations of the error handler and message handler. 
int err_handler(PDBPROCESS, INT, INT, INT, LPCSTR, LPCSTR);
int msg_handler(PDBPROCESS, DBINT, INT, INT, LPCSTR, LPCSTR,
                LPCSTR, DBUSMALLINT);

int main()
{
    PDBPROCESS  dbproc;    // 与SQL Server的连接结构体指针. 
	int retcode;           // 返回代码
    char id[10] = {0};
	char name[20]={0};
	char address[200] = {0};
    char sql[1000] = {0};
    int i;

	// 注册错误和消息处理回调函数.
    dberrhandle (err_handler);
    dbmsghandle (msg_handler);

    dbproc = dbconnect("sa", "", "127.0.0.1");
	if(dbproc == NULL)
	{
        printf ("连接数据库失败\n");
		return 0;
	}

	dbcmd(dbproc, "drop table pubs..student1");
	retcode = dbsqlexec (dbproc);
	if (retcode == SUCCEED)
    {
        printf ("删除表成功\n");
    }
	else
	{
        printf ("删除表失败\n");
	}

//////////////////////
		 dbproc = dbconnect("sa", "", "127.0.0.1");
		if(dbproc == NULL)
		{
		    printf ("连接数据库失败\n");
			return 0;
		}
		////////////////////////////



    // 创建表student. 
    dbcmd (dbproc, "create table pubs..student1(ID numeric(3) primary key, name char(8) not null, address varchar(300))");

    retcode = dbsqlexec (dbproc);   // 发送命令到SQL Server然后开始执行. 
    if (retcode == SUCCEED)
    {
        printf ("创建表成功\n");
    }
	else
	{
        printf ("创建表失败\n");
	}

	//插入数据
	
	for(i=0;i<5;i++)
	{
		//////////////////////
		 dbproc = dbconnect("sa", "", "127.0.0.1");
		if(dbproc == NULL)
		{
		    printf ("连接数据库失败\n");
			return 0;
		}
		////////////////////////////
		scanf("%s%s%s", id,name,address);
		sprintf(sql,"insert into pubs..student1 values(%s, '%s','%s')", id, name,address);
		printf("%s\n",sql);

		dbcmd(dbproc, sql);
    retcode = dbsqlexec (dbproc);
    if (retcode == SUCCEED)
    {
        printf ("插入数据成功\n");
    }
	else
	{
        printf ("插入数据失败\n");
	}
	}

	//更新数据
	dbproc = dbconnect("sa", "", "127.0.0.1");
		if(dbproc == NULL)
		{
		    printf ("连接数据库失败\n");
			return 0;
		}
	dbcmd(dbproc, "update pubs..student1 set name='wang123'");
    retcode = dbsqlexec (dbproc);
    if (retcode == SUCCEED)
    {
        printf ("更新数据成功\n");
    }
	else
	{
        printf ("更新数据失败\n");
	}

	//删除数据
	dbproc = dbconnect("sa", "", "127.0.0.1");
		if(dbproc == NULL)
		{
		    printf ("连接数据库失败\n");
			return 0;
		}
	dbcmd(dbproc, "delete pubs..student1 where name='wang123'");
    retcode = dbsqlexec (dbproc);
    if (retcode == SUCCEED)
    {
        printf ("删除数据成功\n");
    }
	else
	{
        printf ("删除数据失败\n");
	}

    dbexit ();    // 关闭数据库连接. 
    return (0);
}

PDBPROCESS dbconnect(const char *user, const char *password, const char *dbserver)
{
    PDBPROCESS  dbproc;    // 与SQL Server的连接结构体指针. 
    PLOGINREC   login;     // 登录信息。 

    dbinit();              // 初始化 DB-Library.

    login = dblogin ();                   //获得LOGINREC.
    DBSETLUSER (login, user);             //设置数据库登录用户
    DBSETLPWD (login, password);          //设置数据库登录用户密码
	
    dbproc = dbopen (login, dbserver);  //连接数据库
	return dbproc;
}

int err_handler (PDBPROCESS dbproc, INT severity,
    INT dberr, INT oserr, LPCSTR dberrstr, LPCSTR oserrstr)
{
    printf ("DB-Library 错误 %i: %s\n", dberr, dberrstr);
    if (oserr != DBNOERR)
    {
        printf ("操作系统错误%i: %s\n", oserr, oserrstr);
    }
    return (INT_CANCEL);
}

int msg_handler (PDBPROCESS dbproc, DBINT msgno, INT msgstate,
    INT severity, LPCSTR msgtext, LPCSTR server,
    LPCSTR procedure, DBUSMALLINT line)
{
    printf ("SQL Server 消息 %ld: %s\n", msgno, msgtext);
    return (0);
}