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

本文介绍如何使用C#进行SQL批量更新和插入操作。通过实例展示了如何建立数据库连接、执行SQL查询获取学生信息，并批量更新及插入数据的具体步骤。文中还涉及了参数化查询的使用，以增强SQL操作的安全性和效率。

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