vfp调用asp.net webservice更新数据
一、表结构
*vfp创建username表并插入一些数据
create table userinfo(用户名 c(10),密码 c(10),年龄 i)
insert into userinfo values('胡书宾','1234576',24)
insert into userinfo values('费青春','7777333',25)
insert into userinfo values('孙守用','8887333',26)
insert into userinfo values('彥丙良','8887766',24)
insert into userinfo values('闵小波','8287766',25)
insert into userinfo values('张忠文','8288822',24)
insert into userinfo values('代方明','8282232',23)
*将userinfo转换成xml 512+16为gb2312编码
cursortoxml("userinfo","userinfo.xml",1,512 + 16,0,"userinfo.xsd")
*将xml转换临时表会发现字段宽度和类型均未改变
xmltocursor("userinfo.xml","userinfo_",512+16)
--在SQL Server中删除原表重新创建userinfo
drop table userinfo
create table userinfo (用户名 char(10) primary key,密码 char(10) not null,年龄 int)
insert into userinfo values('潘万飞','123234',18)
二、VFP客户端关键源码
define class uploadCmndBtnClass as CmndBtnClass
caption = '上传'
procedure click
local lcXmlString,loSoap,lcTmpXml
lcTmpXml = sys(2015) + '.xml'
= cursortoxml('userinfo',lcTmpXml,1,512 + 16,0)
*去掉空格和回车符
lcXmlString = filetostr(lcTmpXml)
lcXmlString = chrtran(lcXmlString,'','')
lcXmlString = chrtran(lcXmlString,chr[13] + chr[10],'')
delete file (lcTmpXml)
*创建SOAP对象
loSoap = Createobject("MSSOAP.soapclient30")
*调用WebService服务--修改成你发布的地址
*在VS2008在右侧资源窗口中右键项目发布网站,若传的虚拟主机上
*将bin目录中的dll和Service.asmx上传上去就行了,注意dll一定要放在网站根目录的bin目录中
loSoap.MSSoapInit('http://localhost/Service.asmx?wsdl')
*执行WebService接口函数
if loSoap.updateDataToTable(lcXmlString,'userinfo','用户名') = '执行成功'
= messagebox('执行成功',64)
else
= messagebox('执行失败',32)
endif
endproc
enddefine
三、asp.net webservice关键源码
[WebMethod] //xmlString 要上传的xml数据,sqlTableName 要上传到的sql表名,keyField 主关键字 public String updateDataToTable(string xmlString,string sqlTableName,string keyField) { SqlConnection thisConnection = new SqlConnection("server=127.0.0.1;uid=sa;pwd=123;database=jwcrm"); //改成你的数据库 SqlDataAdapter thisAdapter = new SqlDataAdapter("select * from " + sqlTableName, thisConnection); SqlCommandBuilder thisBuilder = new SqlCommandBuilder(thisAdapter); DataSet thisDataSet = new DataSet(); //数据库中的 DataSet xmlDataSet = new DataSet(); //导入的xml //加载xml，即要导入及要更新的数据 xmlDataSet.ReadXml(new System.IO.MemoryStream(System.Text.Encoding.Default.GetBytes(xmlString))); thisAdapter.Fill(thisDataSet, sqlTableName); //加载数据表 DataColumn[] findKeys = new DataColumn[1]; //新建查找列数组 findKeys[0] = thisDataSet.Tables[sqlTableName].Columns[keyField]; //将关键字列赋值给查找列数组 thisDataSet.Tables[sqlTableName].PrimaryKey = findKeys; //设置关键字 //获取sqlTableName表中的字段名列表 int nThisCols = thisDataSet.Tables[sqlTableName].Columns.Count; string[] fldNameList = new string[nThisCols]; //赋值字段名 for (int i = 0; i < nThisCols; i++) { fldNameList[i] = thisDataSet.Tables[sqlTableName].Columns[i].Caption; } //逐行导入数据 foreach (DataRow xmlRow in xmlDataSet.Tables[0].Rows) { //查找关键字段值是否存在 DataRow findRow = thisDataSet.Tables[sqlTableName].Rows.Find(xmlRow[keyField]); if (findRow == null) //若不存在则新增行 { DataRow newRow = thisDataSet.Tables[sqlTableName].NewRow(); for (int i = 0; i < nThisCols; i++) { newRow[fldNameList[i]] = xmlRow[fldNameList[i]]; } thisDataSet.Tables[sqlTableName].Rows.Add(newRow); } else //若存在则更新数据 { for (int i = 0; i < nThisCols; i++) { findRow[fldNameList[i]] = xmlRow[fldNameList[i]]; } } } //将修改结果更新到数据库表中 thisAdapter.Update(thisDataSet, sqlTableName); return "执行成功"; }