MVC sds dr da大全

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;
    }
    
}