android通过webservice连接SQL Server详细教程(数据库+服务器+客户端)_c#开发android应用程序(连接sql server)

作者：从前慢现在也慢 | 2024-03-26 02:57:53

踩

c#开发android应用程序(连接sql server)

android通过webservice连接SQL Server详细教程(数据库+服务器+客户端)这篇文章中讲到android如何通过webservice连接SQL Server。由于这篇文章距今也有四年了，一些代码在现在比较新的环境中运行不了。所以我根据评论中的提示和自己遇到的问题重新修改了部分代码，使得整个系统能在SQL Server2016+Win10+VS2015+android4.4上能够运行。具体的步骤就不说了，大致上和原来的教程差不多，只说几个比较容易出问题的地方。
1.原文章中登录SQL Server用的是Windows身份验证方式。如果像我出现登录失败的问题，可以改用SQL Server身份验证方式。在安全性-登录名中添加一个SQL Server身份验证方式登录的用户。

C#的代码里面数据库连接字符串还是粘贴属性里面的连接字符串，把密码改成自己的密码。
private String ConServerStr = "Data Source=2013-20160523DL;Initial Catalog=test;User ID=houjingyi;Password=*******";
2.一定要先在webservice里面确认对数据库的操作没有问题，再去调android程序。只看到页面出来了很可能数据库连接有问题，这样即使android程序没问题也调不出来。
3.android4.0以后不允许在主线程中访问网络，因为万一主线程阻塞了，会使得界面没有响应。我们开启线程访问即可。


	public List<HashMap<String, String>> getAllInfo(final Handler myhandler) 
	{
		HashMap<String, String> tempHash = new HashMap<String, String>();
		List<HashMap<String, String>> list = new ArrayList<HashMap<String, String>>();
		tempHash.put("Cno", "Cno");
		tempHash.put("Cname", "Cname");
		tempHash.put("Cnum", "Cnum");
		list.clear();
		arrayList1.clear();
		arrayList2.clear();
		arrayList3.clear();
		list.add(tempHash);
 
		new Thread()
		{
			public void run()
			{
				arrayList1 = Soap.GetWebServer("selectAllCargoInfor", arrayList1, arrayList2);
				Message msg=new Message();
				msg.what=0x123;
				msg.obj=arrayList1;
				myhandler.sendMessage(msg);
			}
		}.start();
	
		return list;
	}
 
	public void insertCargoInfo(String Cname, String Cnum) 
	{
		arrayList1.clear();
		arrayList2.clear();
		arrayList1.add("Cname");
		arrayList1.add("Cnum");
		arrayList2.add(Cname);
		arrayList2.add(Cnum);
		
		new Thread()
		{
			public void run()
			{
				try
				{
					Soap.GetWebServer("insertCargoInfo", arrayList1, arrayList2);
				}
				catch(Exception e)
				{
					
				}
			}
		}.start();
	}
 
	public void deleteCargoInfo(String Cno) 
	{
		arrayList1.clear();
		arrayList2.clear();
		arrayList1.add("Cno");
		arrayList2.add(Cno);
		
		new Thread()
		{
			public void run()
			{
				try
				{
					Soap.GetWebServer("deleteCargoInfo", arrayList1, arrayList2);
				}
				catch(Exception e)
				{
				
				}
			}
		}.start();
	}

4.android4.0以后子线程里是不能对主线程的UI进行改变的，因此就引出了Handler。主线程里定义Handler供子线程里使用。


	final Handler myhandler=new Handler()
	{
		public void handleMessage(Message msg)
		{
			if(msg.what==0x123)
			{
				ArrayList<String> drrayList=(ArrayList<String>) msg.obj;
				for(int j=0;!drrayList.isEmpty()&&j+2<drrayList.size();j+=3)
				{
					HashMap<String,String> hashMap=new HashMap<String,String>();
					hashMap.put("Cno", drrayList.get(j));
					hashMap.put("Cname", drrayList.get(j+1));
					hashMap.put("Cnum", drrayList.get(j+2));
					list.add(hashMap);
				}
				adapter=new SimpleAdapter(
						MainActivity.this,list,
						R.layout.adapter_item,
						new String[]{"Cno","Cname","Cnum"},
						new int[]{R.id.txt_Cno,R.id.txt_Cname,R.id.txt_Cnum});
				listView.setAdapter(adapter);
			}
		}
	};

5.HttpConnSoap.java这个类是干什么的。我们看一下这些操作的SOAP1.1请求和响应示例。
selectAllCargoInfo：
请求：
POST /Service1.asmx HTTP/1.1
Host: localhost
Content-Type: text/xml; charset=utf-8
Content-Length: length
SOAPAction: "http://tempuri.org/selectAllCargoInfor"

<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
<selectAllCargoInfor xmlns="http://tempuri.org/" />
</soap:Body>
</soap:Envelope>
响应：
HTTP/1.1 200 OK
Content-Type: text/xml; charset=utf-8
Content-Length: length

<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
<selectAllCargoInforResponse xmlns="http://tempuri.org/">
<selectAllCargoInforResult>
<string>string</string>
<string>string</string>
</selectAllCargoInforResult>
</selectAllCargoInforResponse>
</soap:Body>
</soap:Envelope>
insertCargoInfo：
请求：
POST /Service1.asmx HTTP/1.1
Host: localhost
Content-Type: text/xml; charset=utf-8
Content-Length: length
SOAPAction: "http://tempuri.org/insertCargoInfo"

<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
<insertCargoInfo xmlns="http://tempuri.org/">
<Cname>string</Cname>
<Cnum>int</Cnum>
</insertCargoInfo>
</soap:Body>
</soap:Envelope>
响应：
HTTP/1.1 200 OK
Content-Type: text/xml; charset=utf-8
Content-Length: length

<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
<insertCargoInfoResponse xmlns="http://tempuri.org/">
<insertCargoInfoResult>boolean</insertCargoInfoResult>
</insertCargoInfoResponse>
</soap:Body>
</soap:Envelope>
deleteCargoInfo：
请求：
POST /Service1.asmx HTTP/1.1
Host: localhost
Content-Type: text/xml; charset=utf-8
Content-Length: length
SOAPAction: "http://tempuri.org/deleteCargoInfo"

<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
<deleteCargoInfo xmlns="http://tempuri.org/">
<Cno>string</Cno>
</deleteCargoInfo>
</soap:Body>
</soap:Envelope>
响应：
HTTP/1.1 200 OK
Content-Type: text/xml; charset=utf-8
Content-Length: length

<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
<deleteCargoInfoResponse xmlns="http://tempuri.org/">
<deleteCargoInfoResult>boolean</deleteCargoInfoResult>
</deleteCargoInfoResponse>
</soap:Body>
</soap:Envelope>
其实这个类就是在GetWebServer先拼XML格式的POST请求发给页面，然后在StreamtoValue里面对返回的数据做解析。GetWebServer这个函数比较简单，而在StreamtoValue里面要根据返回类型的不同执行不同的操作。删除和插入操作返回的是布尔类型，而查询操作返回的是String和int。我们也可以看到，删除和插入操作返回的XML格式是一样的。相信大家对比着XML和注释就能明白。


package com.bottle.stockmanage;
 
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.HttpURLConnection;
import java.net.URL;
import java.util.ArrayList;
 
import android.app.Activity;
import android.net.ConnectivityManager;
import android.widget.Toast;  
  
public class HttpConnSoap
{  
    public ArrayList<String> GetWebServer(String MethodName, ArrayList<String> Parameters, ArrayList<String> ParValues) 
    {  
        ArrayList<String> Values = new ArrayList<String>();
        //在android模拟器中测试填写10.0.2.2
        //在真机中测试填写电脑的IP地址
        String ServerUrl = "http://10.8.167.229:6666/Service1.asmx";    
        String soapAction = "http://tempuri.org/" + MethodName;    
        String soap = "<?xml version=\"1.0\" encoding=\"utf-8\"?>"  
                + "<soap:Envelope xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\" xmlns:soap=\"http://schemas.xmlsoap.org/soap/envelope/\">"  
                + "<soap:Body />";  
        String s1, s2, s3, s4 = ""; 
        s4 = "<" + MethodName + " xmlns=\"http://tempuri.org/\">";  
        for (int i = 0; i < Parameters.size(); i++)
        {  
            s1 = Parameters.get(i).toString();    
            s2 = ParValues.get(i).toString();  
            s3 = "<" + s1 + ">" + s2 + "</" + s1 + ">";  
            s4 = s4 + s3;  
        }  
        s4 = s4 + "</" + MethodName + ">";  
        String s5 = "</soap:Envelope>";  
        String requestData = soap + s4 + s5;  
        try
        {  
            URL url = new URL(ServerUrl);  
            HttpURLConnection con = (HttpURLConnection) url.openConnection();  
            byte[] bytes = requestData.getBytes("utf-8");  
            con.setDoInput(true);  
            con.setDoOutput(true);  
            con.setUseCaches(false);  
            con.setConnectTimeout(6000);  
            con.setRequestMethod("POST");  
            con.setRequestProperty("Content-Type", "text/xml;charset=utf-8");  
            con.setRequestProperty("SOAPAction", soapAction);  
            con.setRequestProperty("Content-Length", "" + bytes.length);  
            OutputStream outStream = con.getOutputStream();  
            outStream.write(bytes);  
            outStream.flush();  
            outStream.close();  
            InputStream inStream = con.getInputStream();  
            Values = StreamtoValue(inStream, MethodName);   
            return Values;  
        } 
        catch (Exception e) 
        {    
            return null;  
        }  
    }  
  
    public ArrayList<String> StreamtoValue(InputStream in, String MethodName) throws IOException
    {  
        StringBuffer out = new StringBuffer();  
        String s1 = "";  
        byte[] b = new byte[4096];  
        ArrayList<String> Values = new ArrayList<String>();  
        Values.clear();  
        for (int n; (n = in.read(b)) != -1;)
        {  
            s1 = new String(b, 0, n);  
            out.append(s1);  
        }  
        System.out.println(out);  
        //分割
        String[] s2 = s1.split("><");  
        String s5 = MethodName + "Result";  
        String s3 = "",s4 = "";  
        Boolean getValueBoolean = false;  
        for (int i = 0; i < s2.length; i++) 
        {  
            s3 = s2[i];  
            System.out.println(s3);  
            int FirstIndexPos, LastIndexPos, LengthofS5;  
            FirstIndexPos = s3.indexOf(s5);  
            LastIndexPos = s3.lastIndexOf(s5);  
            /*
            	删除:deleteCargoInfoResult>boolean</deleteCargoInfoResult
            	插入:insertCargoInfoResult>boolean</insertCargoInfoResult
            	查询:
            	selectAllCargoInforResult
            	string>string</string
            	string>string</string
            	/selectAllCargoInforResult
            */
            if (FirstIndexPos >= 0) 
            {  
                if (getValueBoolean == false) 
                {  
                    getValueBoolean = true;  
                }
                //如果返回的是布尔值,对应删除和增加操作
                if ((FirstIndexPos >= 0) && (LastIndexPos > FirstIndexPos))
                {    
                    LengthofS5 = s5.length() + 1;  
                    s4 = s3.substring(FirstIndexPos + LengthofS5, LastIndexPos - 2);    
                    Values.add(s4);    
                    getValueBoolean = false;  
                    return Values;  
                }  
  
            }  
            //查询操作取值结束
            if (s3.lastIndexOf("/" + s5) >= 0) 
            {  
                getValueBoolean = false;  
                return Values;  
            }
            //如果返回的不是布尔值,对应查询操作
            if ((getValueBoolean) && (s3.lastIndexOf("/" + s5) < 0) && (FirstIndexPos < 0))
            {  
                LastIndexPos = s3.length();  
                s4 = s3.substring(7, LastIndexPos - 8);    
                Values.add(s4);  
            }  
        }  
        return Values;  
    }  
}

6.如果你的电脑和我一样使用的是局域网，那么手机必须连和电脑在同一个网段的WIFI，不能用4G或者3G连接。如果你用的是公网IP或者以后把程序发布到一个服务器上从而获得一个公网IP，就没有这个限制了。
最后欢迎大家一起交流，学习，也感谢原作者的文章。修改后可运行的代码在：https://github.com/houjingyi233/android-connect-to-SQL-Server-On-PC-by-webservice/

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/从前慢现在也慢/article/detail/314203