Ado.net批量添加和更新数据简单示例

 

SqlConnection conn = new SqlConnection("server=xk\\xk;uid=sa;pwd=112233;database=test");

    SqlDataAdapter da = new SqlDataAdapter();

    SqlCommand cmd;

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            Get_Insert_Update_Info();
        }
    }

    private void Get_Insert_Update_Info()
    {
        cmd = new SqlCommand("select * from student", conn);
        DataSet ds = new DataSet();
        da.SelectCommand = cmd;
        da.Fill(ds, "Table");
        for (int i = 0; i < ds.Tables["Table"].Rows.Count; i++)
        {
            Response.Write(ds.Tables["Table"].Rows[i]["name"] + "<br/>");
        }
        ViewState["ds"] = ds;
    }

    //批量更新数据
    protected void Button1_Click(object sender, EventArgs e)
    {
        DataSet ds = ViewState["ds"] as DataSet;
        for (int i = 0; i < ds.Tables["Table"].Rows.Count; i++)
        {
            ds.Tables["Table"].Rows[i].BeginEdit();
            ds.Tables["Table"].Rows[i]["name"] = "*****************";
            ds.Tables["Table"].Rows[i].EndEdit();
        }

        String UpdateSQL = "Update student set name=@name where Id=@id";
        cmd = new SqlCommand(UpdateSQL, conn);
        cmd.Parameters.Add("@name", SqlDbType.VarChar, 50, "name");
        cmd.Parameters.Add("@id", SqlDbType.Int, 4, "id");
        da.UpdateCommand = cmd;
        da.Update(ds, "Table");
        ds.AcceptChanges();
    }

    //批量插入数据
    protected void Button2_Click(object sender, EventArgs e)
    {
        DataTable dt = new DataTable();
        DataRow dr;
        dt.Columns.Add(new DataColumn("name"));
        for (int i = 0; i < 3; i++)
        {
            dr = dt.NewRow();
            dr[0] = "name" + i.ToString();
            dt.Rows.Add(dr);
        }

        System.Text.StringBuilder sb = new System.Text.StringBuilder("");
        sb.Append("INSERT student(name) VALUES(");
        sb.Append("@name)");
        da.InsertCommand = new SqlCommand();
        da.InsertCommand.CommandText = sb.ToString();
        da.InsertCommand.Connection = conn;
        SqlParameter sp = new SqlParameter("@name", SqlDbType.VarChar, 50);
        sp.SourceVersion = DataRowVersion.Current;
        sp.SourceColumn = "name"; // or sp.SourceColumn = Dt.Columns[0].ColumnName;
        da.InsertCommand.Parameters.Add(sp);
        da.Update(dt);
    }