main.c

用数据库语言实现的往数据库中添加数据

#include <stdlib.h>
#include <stdio.h>
#include <windows.h>
#include <sql.h>
#include <sqlext.h>
#include <Sqltypes.h>
#include <time.h>

void GetDctBirth(char *DctBirth)
{
	char year[20];
	char month[10];
	char day[10];
	char buf[10];
	
	strcpy(year,"19");
	
	itoa(rand()%50+30,buf,10);
	strcat(year,buf);
	strcat(year,"-");

	itoa(rand()%12+1,month,10);
	strcat(month,"-");
	
	itoa(rand()%28+1,day,10);
	strcat(day," 00:00:00");

	strcat(year,month);
	strcat(year,day);
	strcpy(DctBirth,year);
}

void GetPeriodNameForDct(char *PeriodName)
{
	char weekdate[20];
	char period[12];

	switch(rand()%7+1)
	{
	case(1):
		strcpy(weekdate,"星期一 ");
		break;
	case(2):
		strcpy(weekdate,"星期二 ");
		break;
	case(3):
		strcpy(weekdate,"星期三 ");
		break;
	case(4):
		strcpy(weekdate,"星期四 ");
		break;
	case(5):
		strcpy(weekdate,"星期五 ");
		break;
	case(6):
		strcpy(weekdate,"星期六 ");
		break;
	default:
		strcpy(weekdate,"星期日 ");
	}
	switch(rand()%10+1)
	{
	case(1):
		strcpy(period,"8:00-9:00");
		break;
	case(2):
		strcpy(period,"9:00-10:00");
		break;
	case(3):
		strcpy(period,"10:00-11:00");
		break;
	case(4):
		strcpy(period,"11:00-12:00");
		break;
	case(5):
		strcpy(period,"12:00-13:00");
		break;
	case(6):
		strcpy(period,"13:00-14:00");
		break;
	case(7):
		strcpy(period,"14:00-15:00");
		break;
	case(8):
		strcpy(period,"15:00-16:00");
		break;
	case(9):
		strcpy(period,"16:00-17:00");
		break;
	default:
		strcpy(period,"17:00-18:00");
	}
	
	strcat(weekdate,period);
	strcpy(PeriodName,weekdate);
}

void GetPeriodNameForSchd(char *PeriodName,int num)//num的取值范围：1-10，11-20，21-30,...,61-70
{
	char weekdate[20];
	char period[12];
	int periodnum;//得到时段号

	int datenum;//得到星期几
	if(num%10==0)
		datenum=num/10;
	else 
		datenum=num/10+1;
	if(num%10==0)
		periodnum=10;
	else 
		periodnum=num%10;
	
	
	switch(datenum)
	{
	case(1):
		strcpy(weekdate,"星期一 ");
		break;
	case(2):
		strcpy(weekdate,"星期二 ");
		break;
	case(3):
		strcpy(weekdate,"星期三 ");
		break;
	case(4):
		strcpy(weekdate,"星期四 ");
		break;
	case(5):
		strcpy(weekdate,"星期五 ");
		break;
	case(6):
		strcpy(weekdate,"星期六 ");
		break;
	default:
		strcpy(weekdate,"星期日 ");
	}
	switch(periodnum)
	{
	case(1):
		strcpy(period,"8:00-9:00");
		break;
	case(2):
		strcpy(period,"9:00-10:00");
		break;
	case(3):
		strcpy(period,"10:00-11:00");
		break;
	case(4):
		strcpy(period,"11:00-12:00");
		break;
	case(5):
		strcpy(period,"12:00-13:00");
		break;
	case(6):
		strcpy(period,"13:00-14:00");
		break;
	case(7):
		strcpy(period,"14:00-15:00");
		break;
	case(8):
		strcpy(period,"15:00-16:00");
		break;
	case(9):
		strcpy(period,"16:00-17:00");
		break;
	default:
		strcpy(period,"17:00-18:00");
	}
	
	strcat(weekdate,period);
	strcpy(PeriodName,weekdate);

}

int main()
{ 	
	//Step 1 定义句柄和变量
	SQLHENV serverhenv;//环境句柄  
	SQLHDBC serverhdbc;//连接句柄 
	
	//语句句柄  
	SQLHSTMT insert_department_hstmt; 
	SQLHSTMT insert_office_hstmt;
	SQLHSTMT insert_doctor_hstmt;
	SQLHSTMT insert_reservation_hstmt;
	SQLHSTMT insert_schedule_hstmt;

	int char_base='a';
	int int_base='0';
	int i=0,j=0,k=0;	
	char dept[30][20]={"心血管内科","心血管外科","神经内科","神经外科","胸外科","泌尿外科","妇产科","口腔科","皮肤科","中医科","针灸推拿科","康复医学科","肾内科","血液内科","感染科","内分泌科","骨科","肛肠外科","临床心理科","肿瘤科","麻醉科","医学美容科","耳鼻咽喉科","眼科","消化内科","普外科","免疫内科","放疗科","疼痛门诊","心电功能科"};//长度一定要为20
	char post[3][20]={"主任医师","副主任医师","主治医师"};
	SQLRETURN ret;
	
	SQLCHAR DeptNo[2],DeptName[20],DeptDesc[800];
	SQLINTEGER PeriodMax=0;
	SQLINTEGER cb=SQL_NTS;
	
	SQLCHAR OfficeNo[5],OfficeLoc[50],OfficeTel[20];
	SQLCHAR DctNo[9],DctName[20],DctPw[6],DctPic[100],DctSex[2],DctBirth[20],DctBg[50],DctPost[20],DctAchv[100],DctDesc[800];
	SQLINTEGER DctSal=0;
	
	SQLCHAR RsvNo[8],PatientName[12],PatientSex[2],PatientId[20],PatientSym[20],PatientTel[20],RsvState[2], PeriodName[20];
	SQLINTEGER PatientAge=0;
	
	SQLCHAR PeriodNo[2],PeriodState[2];
	SQLINTEGER PeriodCount=0;

	//Step 2 :初始化环境 
	ret=SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE, &serverhenv);
	ret=SQLSetEnvAttr(serverhenv, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0);

	//Step 3 :建立连接
	ret=SQLAllocHandle(SQL_HANDLE_DBC, serverhenv, &serverhdbc); 
	ret=SQLConnect(serverhdbc, "HCRS", SQL_NTS, "sa", SQL_NTS,"605605",SQL_NTS); 
	if (!SQL_SUCCEEDED(ret))//连接失败时返回错误值  
	{
		printf("Connecting SQL Server fails~~");
		return -1;
	}

	//Step 4 :初始化语句句柄
	ret=SQLAllocHandle(SQL_HANDLE_STMT, serverhdbc, &insert_department_hstmt); 
	ret=SQLAllocHandle(SQL_HANDLE_STMT, serverhdbc, &insert_office_hstmt); 
	ret=SQLAllocHandle(SQL_HANDLE_STMT, serverhdbc, &insert_doctor_hstmt); 
	ret=SQLAllocHandle(SQL_HANDLE_STMT, serverhdbc, &insert_reservation_hstmt); 
	ret=SQLAllocHandle(SQL_HANDLE_STMT, serverhdbc, &insert_schedule_hstmt); 
	
	//Step 5 :执行语句 	
	//Department
	ret=SQLPrepare(insert_department_hstmt,"INSERT INTO Department(DeptNo,DeptName,DeptDesc,PeriodMax) VALUES (?, ?, ?, ?)", SQL_NTS);
	if (ret==SQL_SUCCESS || ret==SQL_SUCCESS_WITH_INFO) 
	{
		ret=SQLBindParameter(insert_department_hstmt,1,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_CHAR, 2, 0, DeptNo, 0, &cb); 	
		ret=SQLBindParameter(insert_department_hstmt,2,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_CHAR, 20, 0, DeptName, 0, &cb);
		ret=SQLBindParameter(insert_department_hstmt,3,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_CHAR, 800, 0, DeptDesc, 0, &cb); 
		ret=SQLBindParameter(insert_department_hstmt,4,SQL_PARAM_INPUT,SQL_C_LONG,SQL_INTEGER, 0, 0, &PeriodMax, 0, &cb); 
		
	}
	for(i=1;i<=30;i++)//30条记录
	{
		itoa(i,DeptNo,10);
		strcpy(DeptName,dept[i-1]);
		for(k=0;k<50;k++)
			DeptDesc[k]=char_base+rand()%26;
		DeptDesc[50]='\0';
		PeriodMax=3;
		ret=SQLExecute(insert_department_hstmt);
		if(ret==-1)
			printf("Department insert error\n");
	}	

	//Office
	ret=SQLPrepare(insert_office_hstmt,"INSERT INTO Office(OfficeNo,OfficeLoc,DeptNo,OfficeTel) VALUES (?, ?, ?, ?)", SQL_NTS);
	if (ret==SQL_SUCCESS || ret==SQL_SUCCESS_WITH_INFO) 
	{
		ret=SQLBindParameter(insert_office_hstmt,1,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_CHAR, 5, 0, OfficeNo, 0, &cb); 	
		ret=SQLBindParameter(insert_office_hstmt,2,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_CHAR, 50, 0, OfficeLoc, 0, &cb);
		ret=SQLBindParameter(insert_office_hstmt,3,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_CHAR, 2, 0, DeptNo, 0, &cb); 
		ret=SQLBindParameter(insert_office_hstmt,4,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_CHAR, 20, 0, OfficeTel, 0, &cb);	
	}
	for(i=0;i<240;i++)//240条记录
	{
		itoa(i+1,OfficeNo,10);
		for(j=0;j<10;j++)
			OfficeLoc[j]=char_base+rand()%26;
		OfficeLoc[10]='\0';
		itoa(i/8+1,DeptNo,10);
		itoa(rand()%99999999,OfficeTel,10);
		ret=SQLExecute(insert_office_hstmt);
		if(ret==-1)
			printf("Office insert error\n");
	}
	
	//Doctor
	ret=SQLPrepare(insert_doctor_hstmt,"INSERT INTO Doctor(DctNo,DctName,DctPw,DctPic,DctSex,DctBirth,DctBg,DctPost,DctAchv,DctSal,DeptNo,DctDesc,OfficeNo) VALUES (?, ?, ?, ?,?, ?, ?, ?,?, ?, ?, ?,?)", SQL_NTS);
	if (ret==SQL_SUCCESS || ret==SQL_SUCCESS_WITH_INFO) 
	{
		ret=SQLBindParameter(insert_doctor_hstmt,1,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_CHAR, 9, 0, DctNo, 0, &cb); 	
		ret=SQLBindParameter(insert_doctor_hstmt,2,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_CHAR, 20, 0, DctName, 0, &cb);
		ret=SQLBindParameter(insert_doctor_hstmt,3,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_CHAR, 6, 0, DctPw, 0, &cb); 
		ret=SQLBindParameter(insert_doctor_hstmt,4,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_CHAR, 100, 0, DctPic, 0, &cb); 
		ret=SQLBindParameter(insert_doctor_hstmt,5,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_CHAR, 2, 0, DctSex, 0, &cb);	
		ret=SQLBindParameter(insert_doctor_hstmt,6,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_CHAR, 20, 0, DctBirth, 0, &cb); 	
		ret=SQLBindParameter(insert_doctor_hstmt,7,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_CHAR, 50, 0, DctBg, 0, &cb);
		ret=SQLBindParameter(insert_doctor_hstmt,8,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_CHAR, 20, 0, DctPost, 0, &cb); 
		ret=SQLBindParameter(insert_doctor_hstmt,9,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_CHAR, 100, 0, DctAchv, 0, &cb);	
		ret=SQLBindParameter(insert_doctor_hstmt,10,SQL_PARAM_INPUT,SQL_C_LONG,SQL_INTEGER, 0, 0,&DctSal, 0, &cb); 	
		ret=SQLBindParameter(insert_doctor_hstmt,11,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_CHAR, 2, 0, DeptNo, 0, &cb);
		ret=SQLBindParameter(insert_doctor_hstmt,12,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_CHAR, 800, 0, DctDesc, 0, &cb); 
		ret=SQLBindParameter(insert_doctor_hstmt,13,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_CHAR, 5, 0, OfficeNo, 0, &cb);		
	}
	for(i=0;i<480;i++)//480条记录
	{
		itoa(i+1,DctNo,10);
		for(j=0;j<5;j++)
			DctName[j]=char_base+rand()%26;
		DctName[5]='\0';
		itoa(123456,DctPw,10);
		strcpy(DctPic,"doctor.jpg");
		if(rand()%2)
			strcpy(DctSex,"F");
		else
			strcpy(DctSex,"M");

		GetDctBirth(DctBirth);
		for(j=0;j<10;j++)
			DctBg[j]=char_base+rand()%26;
		DctBg[10]='\0';
		/*
		for(j=0;j<10;j++)
			DctPost[j]=char_base+rand()%26;
		DctPost[10]='\0';
		*/
		k=rand()%3;
		if(k==0)
			strcpy(DctPost,post[0]);
		else if(k==1)
			strcpy(DctPost,post[1]);
		else
			strcpy(DctPost,post[2]);
		for(j=0;j<10;j++)
			DctAchv[j]=char_base+rand()%26;
		DctAchv[10]='\0';
		DctSal=(1+rand()%9)*1000+rand()%999;
		itoa(i/16+1,DeptNo,10);
		for(j=0;j<10;j++)
			DctDesc[j]=char_base+rand()%26;
		DctDesc[10]='\0';
		itoa(i/2+1,OfficeNo,10);
		ret=SQLExecute(insert_doctor_hstmt);
		if(ret==-1)
			printf("Doctor insert error\n");
	}

	//Reservation
	ret=SQLPrepare(insert_reservation_hstmt,"INSERT INTO Reservation(RsvNo,PatientName,PatientSex,PatientAge,PatientId,PatientSym,PatientTel,RsvState, DctNo, PeriodName, OfficeNo) VALUES (?,?,?,?,?,?,?,?,?,?,?)", SQL_NTS);
	if (ret==SQL_SUCCESS || ret==SQL_SUCCESS_WITH_INFO) 
	{
		ret=SQLBindParameter(insert_reservation_hstmt,1,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_CHAR, 8, 0, RsvNo, 0, &cb); 	
		ret=SQLBindParameter(insert_reservation_hstmt,2,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_CHAR, 12, 0, PatientName, 0, &cb);
		ret=SQLBindParameter(insert_reservation_hstmt,3,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_CHAR, 2, 0, PatientSex, 0, &cb); 
		ret=SQLBindParameter(insert_reservation_hstmt,4,SQL_PARAM_INPUT,SQL_C_LONG,SQL_INTEGER, 0, 0, &PatientAge, 0, &cb); 
		ret=SQLBindParameter(insert_reservation_hstmt,5,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_CHAR, 20, 0, PatientId, 0, &cb);	
		ret=SQLBindParameter(insert_reservation_hstmt,6,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_CHAR, 20, 0, PatientSym, 0, &cb); 	
		ret=SQLBindParameter(insert_reservation_hstmt,7,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_CHAR, 20, 0,PatientTel, 0, &cb);
		ret=SQLBindParameter(insert_reservation_hstmt,8,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_CHAR, 2, 0, RsvState, 0, &cb); 
		ret=SQLBindParameter(insert_reservation_hstmt,9,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_CHAR, 9, 0, DctNo, 0, &cb);	
		ret=SQLBindParameter(insert_reservation_hstmt,10,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_CHAR, 20, 0,PeriodName, 0, &cb); 	
		ret=SQLBindParameter(insert_reservation_hstmt,11,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_CHAR, 5, 0, OfficeNo, 0, &cb);
	}
	for(i=0;i<2100;i++)//2100条记录
	{
		itoa(i+1,RsvNo,10);
		for(j=0;j<5;j++)
			PatientName[j]=char_base+rand()%26;
		PatientName[5]='\0';
		if(rand()%2)
			strcpy(PatientSex,"F");
		else
			strcpy(PatientSex,"M");
		PatientAge=rand()%60+5;
		for(j=0;j<18;j++)
			PatientId[j]=int_base+rand()%10;
		PatientId[18]='\0';
		for(j=0;j<10;j++)
			PatientSym[j]=char_base+rand()%26;
		PatientSym[10]='\0';
		for(j=0;j<8;j++)
			PatientTel[j]=int_base+rand()%10;
		PatientTel[8]='\0';
		strcpy(RsvState,"N");
		k=rand()%480;
		itoa(k+1,DctNo,10);
		GetPeriodNameForDct(PeriodName);
		itoa(k/2+1,OfficeNo,10);
		ret=SQLExecute(insert_reservation_hstmt);
		if(ret==-1)
			printf("Reservation insert error\n %d",i);	
	}
	
	//Schedule
	ret=SQLPrepare(insert_schedule_hstmt,"INSERT INTO Schedule(PeriodNo,DctNo,PeriodName,PeriodState,PeriodCount,PeriodMax) VALUES (?,?,?,?,?,?)", SQL_NTS);
	if (ret==SQL_SUCCESS || ret==SQL_SUCCESS_WITH_INFO) 
	{
		ret=SQLBindParameter(insert_schedule_hstmt,1,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_CHAR, 2, 0, PeriodNo, 0, &cb); 	
		ret=SQLBindParameter(insert_schedule_hstmt,2,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_CHAR, 9, 0, DctNo, 0, &cb);
		ret=SQLBindParameter(insert_schedule_hstmt,3,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_CHAR, 20, 0, PeriodName, 0, &cb); 
		ret=SQLBindParameter(insert_schedule_hstmt,4,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_CHAR,2, 0, PeriodState, 0, &cb); 
		ret=SQLBindParameter(insert_schedule_hstmt,5,SQL_PARAM_INPUT,SQL_C_LONG,SQL_INTEGER, 0, 0, &PeriodCount, 0, &cb);	
		ret=SQLBindParameter(insert_schedule_hstmt,6,SQL_PARAM_INPUT,SQL_C_LONG,SQL_INTEGER, 0, 0,&PeriodMax, 0, &cb); 	
	}
	for(i=0;i<480;i++)//480个医生
	{
		for(j=0;j<70;j++)//70个时段
		{
			itoa(j+1,PeriodNo,10);
			itoa(i+1,DctNo,10);
			GetPeriodNameForSchd(PeriodName,j+1);
			if(rand()%10)
			strcpy(PeriodState,"Y");
			else
			strcpy(PeriodState,"N");//绝大多数可以预约
			PeriodCount=rand()%4;
			PeriodMax=3;
			ret=SQLExecute(insert_schedule_hstmt);
			if(ret==-1)
				printf("Schedule insert error\n");
		}
	}


	// Step 6 中止处理
	SQLFreeHandle(SQL_HANDLE_STMT, insert_department_hstmt);
	SQLFreeHandle(SQL_HANDLE_STMT, insert_office_hstmt);
	SQLFreeHandle(SQL_HANDLE_STMT, insert_doctor_hstmt);
	SQLFreeHandle(SQL_HANDLE_STMT, insert_reservation_hstmt);
	SQLFreeHandle(SQL_HANDLE_STMT, insert_schedule_hstmt);
	SQLDisconnect(serverhdbc);
	SQLFreeHandle(SQL_HANDLE_DBC, serverhdbc);
	SQLFreeHandle(SQL_HANDLE_ENV, serverhenv); 
	return 0; 

}