android透過Web Server連接SQL server

加好友 · 發表於 2013-11-28 22:57

本帖最後由 vv1105tw 於 2013-11-28 23:04 編輯

請問各位大大~
我要做手機可以抓取資料庫的資料，查了網路上的資料，是要透過Web Server連接到SQL server來抓取資料，
我現在要做一個公佈欄，我已建好網頁和資料庫，那要在手機上列出來，android部分要如何寫呢?
請各位大大幫幫我，我很急~~

下方是手機要抓取資料的網頁的程式碼~可幫我看看~~
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Data.SqlClient;
using System.Data;

namespace SNQueryWebService
{
/// <summary>
///SQL_NoticeBoard 的摘要描述
/// </summary>
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[System.ComponentModel.ToolboxItem(false)]

public class SQL_NoticeBoard : System.Web.Services.WebService
{

      [WebMethod]//取得公佈欄資料紀錄
      public string[] NoticeBoard(string NB_ID)
      {
         string[] output = null;
         //建立連結物件
         SqlConnection sql = new SqlConnection("Data Source=140.126.146.25,12345;Initial Catalog="C:PROGRAM FILESMICROSOFT SQL SERVERMSSQL11.SQLEXPRESSMSSQLBACKUPMYDBMYDBMYDB.MDF";User ID=Family;Password=12131213");
         sql.Open();

         //建立命令物件
         SqlCommand cmd = new SqlCommand("SELECT NB_ID,NB_Name,NB_Content,NB_Class,NB_Date  FROM NOTICE_BOARD WHERE NB_ID="" + NB_ID + """, sql);

         SqlDataAdapter da = new SqlDataAdapter();
         DataTable dt = new DataTable();
         da.SelectCommand = cmd;
         da.Fill(dt);
         if (dt.Rows.Count > 0)
         {
            output = new String[dt.Rows.Count];

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                  //讀取
                  output = dt.Rows.ItemArray[0].ToString() + "  ";
                  //讀取
                  output += dt.Rows.ItemArray[1].ToString() + "  ";
                  //讀取
                  output += dt.Rows.ItemArray[2].ToString() + "  ";
                  //讀取
                  output += dt.Rows.ItemArray[3].ToString() + "  ";
                  //讀取
                  output += dt.Rows.ItemArray[4].ToString() + "  ";
            }
         }
         da.Dispose();
         dt.Dispose();
         cmd.Dispose();
         sql.Close();
         sql.Dispose();

         return output;

      }

}
}

加好友 · 發表於 2013-12-2 12:27

加好友 · 發表於 2014-3-12 16:48

自己測試過的方法，供您參考:

Android 手機經Web Server(PHP)，取得Oracle DB資料。

一開始是想由手機直接連結資料庫取得資料，查了依些資料發現，需經由Web Server，不同的資料庫，只需依DB的文件再修改即可。

程序流程: 1.主程式MainActivity -> 2.呼叫副程式DBConnector -> 3.經由Http方法聯結Web Server(PHP網頁) ->4.網頁取得資料庫資料後回傳顯示。

1. Android 主程式:

package com.xxx.testandroiddbactivity;
import android.os.Build;
import android.os.Bundle;
import android.annotation.SuppressLint;
import android.annotation.TargetApi;
import android.app.Activity;
import android.view.Menu;
import org.json.JSONArray;
import org.json.JSONObject;
import android.os.StrictMode;
import android.util.Log;
import android.view.Gravity;
import android.view.View;
import android.view.ViewGroup.LayoutParams;
import android.widget.Button;
import android.widget.TableLayout;
import android.widget.TableRow;
import android.widget.TextView;
import android.widget.EditText;
import android.widget.Toast;
public class MainActivity extends Activity {
@TargetApi(Build.VERSION_CODES.GINGERBREAD)
@SuppressLint("NewApi")
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
findViews();
setListeners();
StrictMode.setThreadPolicy(new StrictMode.ThreadPolicy.Builder()
.detectDiskReads()
.detectDiskWrites()
.detectNetwork()
.penaltyLog()
.build());
StrictMode.setVmPolicy(new StrictMode.VmPolicy.Builder()
.detectLeakedSqlLiteObjects()
.penaltyLog()
.penaltyDeath()
.build());
}
@Override
public boolean onCreateOptionsMenu(Menu menu) {
// Inflate the menu; this adds items to the action bar if it is present.
getMenuInflater().inflate(R.menu.main, menu);
return true;
}
private Button button_get_record;
private Button button_get_record2;
private EditText username;
private EditText password;
private String s_username;
private String s_password;
private void findViews() {
username = (EditText)findViewById(R.id.username);
password = (EditText)findViewById(R.id.password);
button_get_record = (Button)findViewById(R.id.get_record);
button_get_record2 = (Button)findViewById(R.id.get_record2);
}
private void setListeners() {
button_get_record.setOnClickListener(getDBRecord);
button_get_record2.setOnClickListener(getDBRecord2);
}
private Button.OnClickListener getDBRecord = new Button.OnClickListener() {
public void onClick(View v) {
// TODO Auto-generated method stub
TableLayout user_list = (TableLayout)findViewById(R.id.user_list);
user_list.setStretchAllColumns(true);
TableLayout.LayoutParams row_layout = new TableLayout.LayoutParams(LayoutParams.WRAP_CONTENT, LayoutParams.WRAP_CONTENT);
TableRow.LayoutParams view_layout = new TableRow.LayoutParams(LayoutParams.WRAP_CONTENT, LayoutParams.WRAP_CONTENT);
try {
s_username = username.getText().toString(); //取得輸入的帳號
s_password = password.getText().toString(); //密碼
//呼叫將要連結資料庫的函式DBConnector，並傳遞參數(SQL,帳號,密碼)
String result = DBConnector.executeQuery("SELECT xxx16,xxx31f,xxx32f,xxx34f FROM xxx_table WHERE rownum < 10",s_username,s_password);
//SQL 結果有多筆資料時使用JSONArray
//只有一筆資料時直接建立JSONObject物件
//JSONObject jsonData = new JSONObject(result);
JSONArray jsonArray = new JSONArray(result);
//System.out.print(result);
user_list.removeAllViews(); //清空畫面
TableRow tr2 = new TableRow(MainActivity.this);
tr2.setLayoutParams(row_layout);
tr2.setGravity(Gravity.CENTER_HORIZONTAL);
TextView title_acc = new TextView(MainActivity.this);
title_acc.setText("稅率");
title_acc.setLayoutParams(view_layout);
TextView title_pwd = new TextView(MainActivity.this);
title_pwd.setText("未稅金額");
title_pwd.setLayoutParams(view_layout);
TextView title_tax = new TextView(MainActivity.this);
title_tax.setText("稅額");
title_tax.setLayoutParams(view_layout);
TextView title_sum = new TextView(MainActivity.this);
title_sum.setText("合計金額");
title_sum.setLayoutParams(view_layout);
tr2.addView(title_acc);
tr2.addView(title_pwd);
tr2.addView(title_tax);
tr2.addView(title_sum);
user_list.addView(tr2);
for(int i = 0; i < jsonArray.length(); i++) {
JSONObject jsonData = jsonArray.getJSONObject(i);
TableRow tr = new TableRow(MainActivity.this);
tr.setLayoutParams(row_layout);
tr.setGravity(Gravity.CENTER_HORIZONTAL);
TextView user_acc = new TextView(MainActivity.this);
user_acc.setText(jsonData.getString("xxx16"));
user_acc.setLayoutParams(view_layout);
TextView user_pwd = new TextView(MainActivity.this);
user_pwd.setText(jsonData.getString("xxx31F"));
user_pwd.setLayoutParams(view_layout);
TextView user_tax = new TextView(MainActivity.this);
user_tax.setText(jsonData.getString("xxx32F"));
user_tax.setLayoutParams(view_layout);
TextView user_sum = new TextView(MainActivity.this);
user_sum.setText(jsonData.getString("xxx34F"));
user_sum.setLayoutParams(view_layout);
tr.addView(user_acc);
tr.addView(user_pwd);
tr.addView(user_tax);
tr.addView(user_sum);
user_list.addView(tr);
}
//System.out.print("Activity端:" + result);
} catch(Exception e) {
//Log.e("log_tag", e.toString());
String name = e.toString();
Toast.makeText(getApplicationContext(), name, 1).show();
}
}
};
private Button.OnClickListener getDBRecord2 = new Button.OnClickListener() {
public void onClick(View v) {
// TODO Auto-generated method stub
TableLayout user_list = (TableLayout)findViewById(R.id.user_list);
user_list.setStretchAllColumns(true);
TableLayout.LayoutParams row_layout = new TableLayout.LayoutParams(LayoutParams.WRAP_CONTENT, LayoutParams.WRAP_CONTENT);
TableRow.LayoutParams view_layout = new TableRow.LayoutParams(LayoutParams.WRAP_CONTENT, LayoutParams.WRAP_CONTENT);
try {
s_username = username.getText().toString();
s_password = password.getText().toString();
String result = DBConnector.executeQuery("001",s_username,s_password);
//SQL 結果有多筆資料時使用JSONArray
//只有一筆資料時直接建立JSONObject物件
//JSONObject jsonData = new JSONObject(result);
JSONArray jsonArray = new JSONArray(result);
//System.out.print(result);
user_list.removeAllViews(); //清空畫面
TableRow tr2 = new TableRow(MainActivity.this);
tr2.setLayoutParams(row_layout);
tr2.setGravity(Gravity.CENTER_HORIZONTAL);
TextView title_acc = new TextView(MainActivity.this);
title_acc.setText("傳票編號");
title_acc.setLayoutParams(view_layout);
TextView title_pwd = new TextView(MainActivity.this);
title_pwd.setText("項次");
title_pwd.setLayoutParams(view_layout);
TextView title_tax = new TextView(MainActivity.this);
title_tax.setText("發票號碼");
title_tax.setLayoutParams(view_layout);
TextView title_sum = new TextView(MainActivity.this);
title_sum.setText("廠商統一編號");
title_sum.setLayoutParams(view_layout);
tr2.addView(title_acc);
tr2.addView(title_pwd);
tr2.addView(title_tax);
tr2.addView(title_sum);
user_list.addView(tr2);
for(int i = 0; i < jsonArray.length(); i++) {
JSONObject jsonData = jsonArray.getJSONObject(i);
TableRow tr = new TableRow(MainActivity.this);
tr.setLayoutParams(row_layout);
tr.setGravity(Gravity.CENTER_HORIZONTAL);
TextView user_acc = new TextView(MainActivity.this);
user_acc.setText(jsonData.getString("xxx01"));
user_acc.setLayoutParams(view_layout);
TextView user_pwd = new TextView(MainActivity.this);
user_pwd.setText(jsonData.getString("xxx02"));
user_pwd.setLayoutParams(view_layout);
TextView user_tax = new TextView(MainActivity.this);
user_tax.setText(jsonData.getString("xxx03"));
user_tax.setLayoutParams(view_layout);
TextView user_sum = new TextView(MainActivity.this);
user_sum.setText(jsonData.getString("xxx04"));
user_sum.setLayoutParams(view_layout);
tr.addView(user_acc);
tr.addView(user_pwd);
tr.addView(user_tax);
tr.addView(user_sum);
user_list.addView(tr);
}
//System.out.print("Activity端:" + result);
} catch(Exception e) {
//Log.e("log_tag", e.toString());
String name = e.toString();
Toast.makeText(getApplicationContext(), name, 1).show();
}
}
};
}

複製代碼

2.副程式:

package com.xxx.testandroiddbactivity;
import java.io.BufferedReader;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.util.ArrayList;
import org.apache.http.HttpEntity;
import org.apache.http.HttpResponse;
import org.apache.http.NameValuePair;
import org.apache.http.client.HttpClient;
import org.apache.http.client.entity.UrlEncodedFormEntity;
import org.apache.http.client.methods.HttpPost;
import org.apache.http.impl.client.DefaultHttpClient;
import org.apache.http.message.BasicNameValuePair;
import org.apache.http.protocol.HTTP;
import android.database.SQLException;
import android.util.Log;
import android.widget.Toast;
public class DBConnector {
public static String executeQuery(String query_string,String username,String password) {
String result = "";
try {
HttpClient httpClient = new DefaultHttpClient(); //調用HttpClient方法
//建立Http POST連線
HttpPost httpPost = new HttpPost("http://192.168.1.2/AndroidConnectDB/android_connect_db.php");
//post運作傳送變數用NameValuePair[]陣列儲存
ArrayList<NameValuePair> params = new ArrayList<NameValuePair>();
params.add(new BasicNameValuePair("query_string", query_string)); //(輸入變數名稱,輸入變數名稱的值)
params.add(new BasicNameValuePair("username", username)); //(輸入變數名稱,輸入變數名稱的值)
params.add(new BasicNameValuePair("password", password)); //(輸入變數名稱,輸入變數名稱的值)
httpPost.setEntity(new UrlEncodedFormEntity(params, HTTP.UTF_8)); //發出HTTP request
HttpResponse httpResponse = httpClient.execute(httpPost); //取得HTTP response
//view_account.setText(httpResponse.getStatusLine().toString());
HttpEntity httpEntity = httpResponse.getEntity();
InputStream inputStream = httpEntity.getContent();
BufferedReader bufReader = new BufferedReader(new InputStreamReader(inputStream, "utf-8"), 8);
StringBuilder builder = new StringBuilder();
String line = null;
while((line = bufReader.readLine()) != null) {
builder.append(line + "
");
}
inputStream.close();
result = builder.toString();
} catch(Exception e) {
//Log.e("log_tag", e.toString());
}
//System.out.print("Java端:" + result);
return result;
}
}

複製代碼

3. PHP網頁

<?php
$db = OCILogon("test1","test1","(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST= 192.168.1.2 )(PORT=1521))(CONNECT_DATA=(SID=test)))");//oracle DB寫法
$sql = $_POST["query_string"];
if($_POST["query_string"] == "1" ){
$sql = "SELECT XXX01,XXX02,XXX03,XXX04 FROM XXX_FILE";
}
$stid = OCIParse($db, $sql);
OCIExecute($stid, OCI_DEFAULT);
while($succ = OCIFetchInto($stid, $row, OCI_ASSOC)) //提取資料可用資料庫的欄位名稱。
$output[] = $row;
print(json_encode($output));
OCILogoff($db);
?>

複製代碼

加好友 · 發表於 2015-2-12 00:43

independentqqpp 發表於 2014-3-12 16:48
自己測試過的方法，供您參考:

Android 手機經Web Server(PHP)，取得Oracle DB資料。

請問大大是使用Oracle iPlanet Web Server嗎?

[求助] android透過Web Server連接SQL server

相關帖子

警告：您的Adblck已啟用