ASP.NET批量更新数据库表
<%@Import Namespace="System.Data" %>
<%@Import Namespace="System.Data.SqlClient" %>
<scriptrunat="server">
voidPage_Load(object sender, System.EventArgs e)
{
SqlConnection conn = new SqlConnection("server=localhost;uid=sa;pwd=;database=db");
SqlDataAdapter da = new SqlDataAdapter();
SqlCommand cmd = new SqlCommand("select Id AS id, Name AS name from table",conn);
DataSet ds = new DataSet();
conn.Open();
da.SelectCommand = cmd;
da.Fill(ds,"table");
for(int i=0; i<ds.Tables["table"].Rows.Count; i++)
{
Response.Write(ds.Tables["table"].Rows[1]+"<br>");
}
for(int i=0; i<ds.Tables["table"].Rows.Count; i++)
{
ds.Tables["table"].Rows.BeginEdit();
ds.Tables["table"].Rows[1] = "**********";
ds.Tables["table"].Rows.EndEdit();
}
String strUpdateSql = "Update table set Name=@name where Id=@id";
cmd = new SqlCommand(strUpdateSql , conn);
cmd.Parameters.Add("@id",SqlDbType.Int,4, "id");
cmd.Parameters.Add("@name ",SqlDbType.Char,10, "name ");
da.UpdateCommand = cmd;
da.Update(ds, "table");
ds.AcceptChanges();
conn.Close();
}
</script>
下面是一个insert的例子:
<%@Import Namespace="System.Data" %>
<%@Import Namespace="System.Data.SqlClient" %>
<scripASP.NET批量更新数据库表
<%@Import Namespace="System.Data" %>
<%@Import Namespace="System.Data.SqlClient" %>
<scriptrunat="server">
voidPage_Load(object sender, System.EventArgs e)
{
SqlConnection conn = new SqlConnection("server=localhost;uid=sa;pwd=;database=db");
SqlDataAdapter da = new SqlDataAdapter();
SqlCommand cmd = new SqlCommand("select Id AS id, Name AS name from table",conn);
DataSet ds = new DataSet();
conn.Open();
da.SelectCommand = cmd;
da.Fill(ds,"table");
for(int i=0; i<ds.Tables["table"].Rows.Count; i++)
{
Response.Write(ds.Tables["table"].Rows[1]+"<br>");
}
for(int i=0; i<ds.Tables["table"].Rows.Count; i++)
{
ds.Tables["table"].Rows.BeginEdit();
ds.Tables["table"].Rows[1] = "**********";
ds.Tables["table"].Rows.EndEdit();
}
String strUpdateSql = "Update table set Name=@name where Id=@id";
cmd = new SqlCommand(strUpdateSql , conn);
cmd.Parameters.Add("@id",SqlDbType.Int,4, "id");
cmd.Parameters.Add("@name ",SqlDbType.Char,10, "name ");
da.UpdateCommand = cmd;
da.Update(ds, "table");
ds.AcceptChanges();
conn.Close();
}
</script>
下面是一个insert的例子:
<%@Import Namespace="System.Data" %>
<%@Import Namespace="System.Data.SqlClient" %>
<scriptrunat="server">
voidPage_Load(object sender, System.EventArgs e)
{
//建立DataTable数据源
DataTable Dt = new DataTable();
DataRow Dr;
Dt.Columns.Add(new DataColumn("name"));
for(int j =0;j<3;j++)
{
Dr=Dt.NewRow();
Dr[0]="name"+j.ToString();
Dt.Rows.Add(Dr);
}
SqlConnection conn = new SqlConnection("server=localhost;uid=sa;pwd=;database=db");
SqlDataAdapter da= new SqlDataAdapter();
//建立InsertCommand
StringBuilder sb = new StringBuilder("");
sb.Append("INSERT table(Name) VALUES(");
sb.Append("@name)");
da=Dt.NewRow();.InsertCommand = new SqlCommand();
da.InsertCommand.CommandText = sb.ToString();
da.InsertCommand.Connection = conn;
SqlParameter sp = new SqlParameter("@name", SqlDbType.VarChar, 40);
sp.SourceVersion = DataRowVersion.Current;
sp.SourceColumn = "name"; // or sp.SourceColumn = Dt.Columns[0].ColumnName;
da.InsertCommand.Parameters.Add(sp);
//Update操作
da.Update(Dt);
conn.Close();
}
</script>
trunat="server">
voidPage_Load(object sender, System.EventArgs e)
{
//建立DataTable数据源
DataTable Dt = new DataTable();
DataRow Dr;
Dt.Columns.Add(new DataColumn("name"));
for(int j =0;j<3;j++)
{
Dr=Dt.NewRow();
Dr[0]="name"+j.ToString();
Dt.Rows.Add(Dr);
}
SqlConnection conn = new SqlConnection("server=localhost;uid=sa;pwd=;database=db");
SqlDataAdapter da= new SqlDataAdapter();
//建立InsertCommand
StringBuilder sb = new StringBuilder("");
sb.Append("INSERT table(Name) VALUES(");
sb.Append("@name)");
da=Dt.NewRow();.InsertCommand = new SqlCommand();
da.InsertCommand.CommandText = sb.ToString();
da.InsertCommand.Connection = conn;
SqlParameter sp = new SqlParameter("@name", SqlDbType.VarChar, 40);
sp.SourceVersion = DataRowVersion.Current;
sp.SourceColumn = "name"; // or sp.SourceColumn = Dt.Columns[0].ColumnName;
da.InsertCommand.Parameters.Add(sp);
//Update操作
da.Update(Dt);
conn.Close();
}
</script>