锐客网

  1. 首页 > 睿知 > it技术 > >

WebService连接sql serever并使用Android端访问数据

IT知识webservice

青春须早为，岂能长少年。这篇文章主要讲述WebService连接sql serever并使用Android端访问数据相关的知识，希望能为你提供帮助。
一、下载sql serever（真真难下）
建立数据库
二、创建WebService
VS2015中新建项目，进行连接调试
1. 服务资源管理文件-> 数据连接-> 新建连接

WebService连接sql serever并使用Android端访问数据

文章图片

2. 继续-> 填写信息
WebService连接sql serever并使用Android端访问数据

文章图片

3. 连接成功
WebService连接sql serever并使用Android端访问数据

文章图片

4. 查看数据库属性并记录连接属性：
WebService连接sql serever并使用Android端访问数据

文章图片

我的连接字符串为：Data Source=DESKTOP-AJOCHFK; Initial Catalog=myandroid; Persist Security Info=True; User ID=sa; Password=***********
5. 新建WebService类，添加对数据库的各种操作
using System; using System.Data.SqlClient; using System.Collections.Generic; using System.IO; namespace WebApplication { public class DBOperation : IDisposable { public static SqlConnection sqlCon; //用于连接数据库//将下面的引号之间的内容换成上面记录下的属性中的连接字符串Integrated Security=True private String ConServerStr = @"Data Source=DESKTOP-AJOCHFK; Initial Catalog=myAndroid; Persist Security Info=True; User ID=sa; Password=123456"; //默认构造函数 public DBOperation() { if (sqlCon == null) { sqlCon = new SqlConnection(); sqlCon.ConnectionString = ConServerStr; sqlCon.Open(); } }//关闭/销毁函数，相当于Close() public void Dispose() { if (sqlCon != null) { sqlCon.Close(); sqlCon = null; } }public List< string> selectAll() { List< string> list = new List< string> (); try { string sql = "select * from message"; SqlCommand cmd = new SqlCommand(sql, sqlCon); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { for (int i = 0; i < 18; i++) { list.Add(reader[i].ToString()); } } reader.Close(); cmd.Dispose(); } catch(Exception) {} return list; }//longi——经度lati——纬度 public bool message_add(string lati, string longi, string alti,string pm1d0s, string pm2d5s, string pm10s, string pm1d0a, string pm2d5a, string pm10a,string pm0d3n,string pm0d5n, string pm1d0n, string pm2d5n, string pm5n,string pm10n, string temp, string wet) //创建一条信息 { bool result = false; try { string sqls = "insert into message values(\'" + DateTime.Now.ToString() + "\',\'" + lati + "\',\'" + longi + "\',\'" + alti + "\',\'" + pm1d0s + "\',\'" + pm2d5s + "\',\'" + pm10s + "\',\'" + pm1d0a + "\',\'" + pm2d5a + "\',\'" + pm10a + "\',\'" + pm0d3n + "\',\'" + pm0d5n + "\',\'" + pm1d0n + "\',\'" + pm2d5n + "\',\'" + pm5n + "\',\'" + pm10n + "\',\'" + temp + "\',\'" + wet +"\')"; SqlCommand cmd = new SqlCommand(sqls, sqlCon); cmd.ExecuteNonQuery(); cmd.Dispose(); result = true; } catch (Exception e) { } return result; }public bool message_delete(string mNo) { bool result = false; try { string sql = "delete from message where mNo=" + mNo; SqlCommand cmd = new SqlCommand(sql, sqlCon); cmd.ExecuteNonQuery(); cmd.Dispose(); result = true; } catch(Exception) {} return result; } }}

6. 新建一个Web服务，如下
WebService连接sql serever并使用Android端访问数据

文章图片

修改其代码：
using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.Services; namespace WebApplication { /// < summary> /// WebService1 的摘要说明 /// < /summary> [WebService(Namespace = "http://tempuri.org/")] [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)] [System.ComponentModel.ToolboxItem(false)] // 若要允许使用 ASP.NET AJAX 从脚本中调用此 Web 服务，请取消注释以下行。 // [System.Web.Script.Services.ScriptService] public class WebService1 : System.Web.Services.WebService { DBOperation DB = new DBOperation(); [WebMethod(Description ="获取所有的数据")] public string[] selectallData() { return DB.selectAll().ToArray(); }[WebMethod(Description ="增加一条数据")] public bool insertData(string lati, string longi, string alti, string pm1d0s, string pm2d5s, string pm10s, string pm1d0a, string pm2d5a, string pm10a, string pm0d3n, string pm0d5n, string pm1d0n, string pm2d5n,string pm5n, string pm10n, string temp , string wet) { return DB.message_add(lati, longi, alti, pm1d0s, pm2d5s, pm10s, pm1d0a, pm2d5a, pm10a, pm0d3n, pm0d5n, pm1d0n, pm2d5n,pm5n, pm10n, temp, wet); }[WebMethod(Description ="删除一条数据")] public bool deleteData(string mNo) { return DB.message_delete(mNo); } } }

7. 运行程序，打开google浏览器，页面如下
WebService连接sql serever并使用Android端访问数据

文章图片

解决方法 ：
尝试了 方法  http://jingyan.baidu.com/article/2d5afd69c5b7a585a2e28e8e.html ，没用
                    http://www.cnblogs.com/youring2/p/3545175.html， 没用
在 Web.config 中配置如下代码：
< system.webServer> < validation validateIntegratedModeConfiguration="false" /> < !--重点是下面这句--> < directoryBrowse enabled="true" /> < defaultDocument> < files> < add value="https://www.songbingjia.com/android/WebService1.asmx" /> < /files> < /defaultDocument> < /system.webServer>

就出现了如下页面：
WebService连接sql serever并使用Android端访问数据

文章图片

点开WebService1.asmx   就得到以下页面：
WebService连接sql serever并使用Android端访问数据

文章图片

测试成功，返回值为xml
WebService连接sql serever并使用Android端访问数据

文章图片

三、发布WebService
1. 右击项目，点击 ‘发布’
WebService连接sql serever并使用Android端访问数据

文章图片

2. 然后 按照博客http://blog.csdn.net/bigpudding24/article/details/52291782 中的方法发布网站
出现以下问题：
WebService连接sql serever并使用Android端访问数据

文章图片

解决方法：
  打开   ‘启用或关闭windows功能’ ，在 ‘IIS’ 的 ‘万维网服务’ 中的 ‘应用程序开发功能’ 中 勾选如下选项，即可。
 
WebService连接sql serever并使用Android端访问数据

文章图片

  在浏览器中 输入 绑定的 IP 地址，即出现网站，发布成功。
四、Android客户端（从服务器获取数据，主要为Http类和AsyncTask类部分）
1. MainActivity.java
package org.example.myandroid; import android.app.Activity; import android.app.Dialog; import android.content.Intent; import android.os.Bundle; import android.view.Gravity; import android.view.View; import android.view.View.OnClickListener; import android.view.Window; import android.view.WindowManager; import android.widget.Button; import android.widget.EditText; import android.widget.Toast; import org.example.callback.RequestCallBack; import java.util.HashMap; public class MainActivity extends Activity{private Button btn1; private Button btn3; private String requestUrl = "http://10.22.92.95/WebService1.asmx/deleteData"; @Override public void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_main); btn1 = (Button) findViewById(R.id.btn_all); btn3 = (Button) findViewById(R.id.btn_delete); btn1.setOnClickListener(new OnClickListener() {@Override public void onClick(View v) { setListView(); } }); btn3.setOnClickListener(new OnClickListener() {@Override public void onClick(View v) { setDeleteDialog(); } }); }/** * 设置弹出删除对话框 */ private void setDeleteDialog() {final Dialog dialog = new Dialog(MainActivity.this); dialog.setContentView(R.layout.dialog_delete); dialog.setTitle("输入想要删除的数据的编号"); Window dialogWindow = dialog.getWindow(); WindowManager.LayoutParams lp = dialogWindow.getAttributes(); dialogWindow.setGravity(Gravity.CENTER); dialogWindow.setAttributes(lp); final EditText cNoEditText = (EditText) dialog.findViewById(R.id.editText1); Button btnConfirm = (Button) dialog.findViewById(R.id.button1); Button btnCancel = (Button) dialog.findViewById(R.id.button2); btnConfirm.setOnClickListener(new OnClickListener() {@Override public void onClick(View v) { HashMap< String, String> callParams = new HashMap< String, String> (); callParams.put("mNo",cNoEditText.getText().toString()); MyAsyncTask restClient = new MyAsyncTask(new RequestCallBack() { @Override public void onSuccess(String response) { //获取服务端返回的字符串 System.out.println("---------------------"); System.out.println(response); } @Override public void onFailure() { //获取失败 } }); //第一个参数为选择POST或者GET //第二个参数为请求的URL //第三个参数为请求参数Map集合 Object[] params = { HttpTools.POST, requestUrl, callParams }; restClient.execute(params); dialog.dismiss(); Toast.makeText(MainActivity.this, "成功删除数据", Toast.LENGTH_SHORT).show(); } }); btnCancel.setOnClickListener(new OnClickListener() {@Override public void onClick(View v) { dialog.dismiss(); } }); dialog.show(); }/** * 设置listView */ private void setListView() {Intent intent = new Intent(MainActivity.this,ListAll.class); startActivity(intent); } }

2. MyAsyncTask.java
package org.example.myandroid; import java.io.BufferedReader; import java.io.IOException; import java.io.InputStream; import java.io.InputStreamReader; import java.io.PrintWriter; import java.io.UnsupportedEncodingException; import java.net.HttpURLConnection; import java.net.URL; import java.util.Iterator; import java.util.Map; import java.util.Map.Entry; public class HttpURLConnectionTools {/** * @param strUrl * @param params * @return String */ public static String obtainGetUrlContext(String strUrl, Map< String, String> params) { String responseStr = ""; InputStream is = null; HttpURLConnection conn = null; BufferedReader bufferedReader = null; try { URL url = new URL(strUrl); conn = (HttpURLConnection) url.openConnection(); conn.setReadTimeout(HttpTools.ReadOutTime /* milliseconds */); conn.setConnectTimeout(HttpTools.ConnectOutTime /* milliseconds */); conn.setRequestMethod(HttpTools.GET); conn.setDoInput(true); conn.connect(); int responseCode = conn.getResponseCode(); if (responseCode == 200) { bufferedReader = new BufferedReader(new InputStreamReader( conn.getInputStream())); String line; while ((line = bufferedReader.readLine()) != null) { responseStr += line; } } } catch (Exception e) { System.out.println("obtainGetUrlContext is err"); } finally { if (is != null) { try { conn.disconnect(); is.close(); } catch (IOException e) { e.printStackTrace(); } } } return responseStr; }/** * * * @param strUrl * @param params * @return String */ public static String obtainPostUrlContext(String strUrl, Map< String, String> params) { String responseStr = ""; PrintWriter printWriter = null; HttpURLConnection conn = null; BufferedReader bufferedReader = null; try { URL url = new URL(strUrl); conn = (HttpURLConnection) url.openConnection(); conn.setRequestProperty("accept", "*/*"); conn.setRequestProperty("connection", "Keep-Alive"); conn.setRequestProperty("Content-Length", String.valueOf(PostRequestUrl(params).length())); conn.setReadTimeout(10000 /* milliseconds */); conn.setConnectTimeout(10000 /* milliseconds */); conn.setRequestMethod("POST"); conn.setDoOutput(true); conn.setDoInput(true); printWriter = new PrintWriter(conn.getOutputStream()); printWriter.write(PostRequestUrl(params).toString()); printWriter.flush(); int responseCode = conn.getResponseCode(); System.out.println(responseCode); if (responseCode != 200) { System.out.println("error"); } else { bufferedReader = new BufferedReader(new InputStreamReader( conn.getInputStream())); String line; while ((line = bufferedReader.readLine()) != null) { responseStr += line; } } } catch (Exception e) { e.printStackTrace(); } finally { conn.disconnect(); if (printWriter != null) { printWriter.close(); } } return responseStr; }/** * * @param params * @return * @throws UnsupportedEncodingException */ @SuppressWarnings("rawtypes") private static StringBuffer PostRequestUrl(Map< String, String> params) { StringBuffer buffers = new StringBuffer(); Iterator< Entry< String, String> > it = params.entrySet().iterator(); while (it.hasNext()) { Map.Entry element = (Map.Entry) it.next(); buffers.append(element.getKey()); buffers.append("="); buffers.append(element.getValue()); buffers.append("& "); System.out.println(buffers); } if (buffers.length() > 0) { buffers.deleteCharAt(buffers.length() - 1); } return buffers; } }

3. HttpTools.java
package org.example.myandroid; public class HttpTools {public static final String TAG = "HttpTools"; public static final String GET = "GET"; public static final String POST = "POST"; public static final int ReadOutTime = 100000; public static final int ConnectOutTime = 100000; public static final int ReadLength = 1024 * 500; }

4. HttpURLConnectionTools.java
package org.example.myandroid; import java.io.BufferedReader; import java.io.IOException; import java.io.InputStream; import java.io.InputStreamReader; import java.io.PrintWriter; import java.io.UnsupportedEncodingException; import java.net.HttpURLConnection; import java.net.URL; import java.util.Iterator; import java.util.Map; import java.util.Map.Entry; public class HttpURLConnectionTools {/** * @param strUrl * @param params * @return String */ public static String obtainGetUrlContext(String strUrl, Map< String, String> params) { String responseStr = ""; InputStream is = null; HttpURLConnection conn = null; BufferedReader bufferedReader = null; try { URL url = new URL(strUrl); conn = (HttpURLConnection) url.openConnection(); conn.setReadTimeout(HttpTools.ReadOutTime /* milliseconds */); conn.setConnectTimeout(HttpTools.ConnectOutTime /* milliseconds */); conn.setRequestMethod(HttpTools.GET); conn.setDoInput(true); conn.connect(); int responseCode = conn.getResponseCode(); if (responseCode == 200) { bufferedReader = new BufferedReader(new InputStreamReader( conn.getInputStream())); String line; while ((line = bufferedReader.readLine()) != null) { responseStr += line; } } } catch (Exception e) { System.out.println("obtainGetUrlContext is err"); } finally { if (is != null) { try { conn.disconnect(); is.close(); } catch (IOException e) { e.printStackTrace(); } } } return responseStr; }/** * * * @param strUrl * @param params * @return String */ public static String obtainPostUrlContext(String strUrl, Map< String, String> params) { String responseStr = ""; PrintWriter printWriter = null; HttpURLConnection conn = null; BufferedReader bufferedReader = null; try { URL url = new URL(strUrl); conn = (HttpURLConnection) url.openConnection(); conn.setRequestProperty("accept", "*/*"); conn.setRequestProperty("connection", "Keep-Alive"); conn.setRequestProperty("Content-Length", String.valueOf(PostRequestUrl(params).length())); conn.setReadTimeout(10000 /* milliseconds */); conn.setConnectTimeout(10000 /* milliseconds */); conn.setRequestMethod("POST"); conn.setDoOutput(true); conn.setDoInput(true); printWriter = new PrintWriter(conn.getOutputStream()); printWriter.write(PostRequestUrl(params).toString()); printWriter.flush(); int responseCode = conn.getResponseCode(); System.out.println(responseCode); if (responseCode != 200) { System.out.println("error"); } else { bufferedReader = new BufferedReader(new InputStreamReader( conn.getInputStream())); String line; while ((line = bufferedReader.readLine()) != null) { responseStr += line; } } } catch (Exception e) { e.printStackTrace(); } finally { conn.disconnect(); if (printWriter != null) { printWriter.close(); } } return responseStr; }/** * * @param params * @return * @throws UnsupportedEncodingException */ @SuppressWarnings("rawtypes") private static StringBuffer PostRequestUrl(Map< String, String> params) { StringBuffer buffers = new StringBuffer(); Iterator< Entry< String, String> > it = params.entrySet().iterator(); while (it.hasNext()) { Map.Entry element = (Map.Entry) it.next(); buffers.append(element.getKey()); buffers.append("="); buffers.append(element.getValue()); buffers.append("& "); System.out.println(buffers); } if (buffers.length() > 0) { buffers.deleteCharAt(buffers.length() - 1); } return buffers; } }

【WebService连接sql serever并使用Android端访问数据】5. ListAll.java
package org.example.myandroid; import android.app.Activity; import android.os.Bundle; import android.widget.ListView; import org.example.callback.RequestCallBack; import java.util.ArrayList; import java.util.List; import java.util.regex.Matcher; import java.util.regex.Pattern; public class ListAll extends Activity{private String requestUrl = "http://10.22.92.95/WebService1.asmx/selectallData"; @Override public void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.list_all); MyAsyncTask restClient = new MyAsyncTask(new RequestCallBack() { @Override public void onSuccess(String response) { //获取服务端返回的字符串 List list = getParameter(response,"string"); List< Message> messages= new ArrayList< Message> (); for(int i = 0; i < list.size(); i++) { list.set(i,removeAllSpace((String) list.get(i))); }for(int i = 0; i < list.size(); i+=18) { Message message = new Message(list.get(i).toString(),list.get(i+1).toString(), list.get(i+2).toString(),list.get(i+3).toString(), list.get(i+4).toString(), list.get(i+5).toString(),list.get(i+6).toString(),list.get(i+7).toString(), list.get(i+11).toString(),list.get(i+12).toString(), list.get(i+13).toString(), list.get(i+14).toString(),list.get(i+15).toString(),list.get(i+16).toString()); messages.add(message); }final ListView listView = (ListView) findViewById(R.id.messages_list); MessageAdapter adapter = new MessageAdapter(messages,ListAll.this); listView.setAdapter(adapter); } @Override public void onFailure() { //获取失败

    推荐阅读


    上一篇：[Android]Fragment源代码分析 事务

    下一篇：解决Android SDK Manager更新失败问题