using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
//--------------------------------
using System.Data.SqlClient;
using System.Data;
using System.Web.UI.WebControls;
using System.Web.UI;
/// <summary>
/// WebService 的摘要描述
/// </summary>
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
// 若要允許使用 ASP.NET AJAX 從指令碼呼叫此 Web 服務，請取消註解下列一行。
// [System.Web.Script.Services.ScriptService]
public class WebService : System.Web.Services.WebService {
public WebService () {
//如果使用設計的元件，請取消註解下列一行
//InitializeComponent();
}
[WebMethod]
public DataTable HelloWorld() {
string sc = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
SqlConnection cn = new SqlConnection(sc);
cn.Open();
string sql = "select * from city";
SqlCommand cmd = new SqlCommand(sql, cn);
SqlDataAdapter da = new SqlDataAdapter(sql, cn);
DataSet ds = new DataSet();
da.SelectCommand = cmd;
da.Fill(ds);
ds.Dispose();
da.Dispose();
cmd.Dispose();
cn.Close();
cn.Dispose();
return ds.Tables[0];
}
[WebMethod]
public string[] city(int city)
{
string sc = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
SqlConnection cn = new SqlConnection(sc);
cn.Open();
string sql = "select * from city where id=@id";
SqlCommand cmd = new SqlCommand(sql, cn);
cmd.Parameters.Add("@id", System.Data.SqlDbType.Int).Value = city;
SqlDataReader dr = cmd.ExecuteReader();
string[] dt = new string[2];
while (dr.Read())
{
dt[0] = dr[0].ToString();
dt[1] = dr[1].ToString();
}
cmd.Dispose();
cn.Close();
cn.Dispose();
return dt;
}
[WebMethod]
public DataTable[] hi()
{
string sc = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
SqlConnection cn = new SqlConnection(sc);
cn.Open();
string sql = "select * from area;select * from city";
SqlCommand cmd = new SqlCommand(sql,cn);
SqlDataAdapter da = new SqlDataAdapter(sql, cn);
DataTable[] dt = new DataTable[] {new DataTable("dt1"),new DataTable("dt2")};
da.SelectCommand = cmd;
da.Fill(0,0,dt);
da.Dispose();
cmd.Dispose();
cn.Close();
cn.Dispose();
return dt;
}
[WebMethod]
public DataTable hida(int id)
{
string sc = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
SqlConnection cn = new SqlConnection(sc);
cn.Open();
string sql = "select * from city where id=@id";
SqlCommand cmd = new SqlCommand(sql, cn);
cmd.Parameters.Add("@id", System.Data.SqlDbType.Int).Value = id;
SqlDataAdapter da = new SqlDataAdapter(sql, cn);
DataTable dt = new DataTable();
da.SelectCommand = cmd;
da.Fill(dt);
da.Dispose();
cmd.Dispose();
cn.Close();
cn.Dispose();
return dt;
}
[WebMethod]
public SqlDataSource sds(string id)
{
SqlDataSource sds = new SqlDataSource();
sds.ConnectionString = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
sds.SelectCommand = "select * from city where id=@id";
sds.SelectParameters.Add(new Parameter("id", System.Data.DbType.Int32, id));
//sds.Select(DataSourceSelectArguments.Empty);
return sds;
}
[WebMethod]
public int clear(int id)
{
string sc = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
SqlConnection cn = new SqlConnection(sc);
cn.Open();
SqlTransaction tran = cn.BeginTransaction();
string sql = "update item set lp=20 where id=@id";
SqlCommand cmd = new SqlCommand(sql, cn , tran);
cmd.Parameters.Add("@id", System.Data.SqlDbType.Int).Value = id;
string sql2 = "update item set lp=20 where id=2";
SqlCommand cmd2 = new SqlCommand(sql2,cn,tran);
try
{
cmd.ExecuteNonQuery();
cmd2.ExecuteNonQuery();
tran.Commit();
}
catch (Exception e)
{
tran.Rollback();
}
cmd.Dispose();
cn.Close();
cn.Dispose();
return 0;
}
[WebMethod]
public int inse(string id,string aid,string area)
{
SqlDataSource sds = new SqlDataSource();
sds.ConnectionString = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
sds.InsertCommand = "insert into area(id,aid,area)values(@id,@aid,@area)";
sds.InsertParameters.Add(new Parameter("id", System.Data.DbType.Int32, id));
sds.InsertParameters.Add(new Parameter("aid", System.Data.DbType.Int32, aid));
sds.InsertParameters.Add(new Parameter("area", System.Data.DbType.String, area));
sds.Insert();
return 0;
}
[WebMethod]
public DataTable Hdd()
{
string sc = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
SqlConnection cn = new SqlConnection(sc);
cn.Open();
string sql = "select * from city";
SqlCommand cmd = new SqlCommand(sql, cn);
SqlDataAdapter da = new SqlDataAdapter(sql, cn);
SqlCommandBuilder cb = new SqlCommandBuilder(da);
DataTable dt = new DataTable();
da.SelectCommand = cmd;
da.Fill(dt);
da.InsertCommand = cb.GetInsertCommand();
da.InsertCommand.Connection = cn;
da.UpdateCommand = cb.GetUpdateCommand();
da.UpdateCommand.Connection = cn;
da.DeleteCommand = cb.GetDeleteCommand();
da.DeleteCommand.Connection = cn;
//修改
DataRow dr1 = dt.Rows[0];
dr1[1] = "台北市";
//新增
//DataRow dr2 = dt.NewRow();
//dr2[0] = 5;
//dr2[1] = "嘉義市";
//dt.Rows.Add(dr2);
//刪除
//dt.Rows[4].Delete();
//DataRow dr3 = dt.Rows[4];
//dr3.Delete();
//dt.Rows.RemoveAt(4); 不顯示但是不刪除
//foreach (DataRow dr in dt.Select("ID='101'"))
//{
// //dr.Delete();
//}
da.Update(dt);
da.Dispose();
cmd.Dispose();
cn.Close();
cn.Dispose();
return dt;
}
}
