关于DataSet批量更新数据库

DataSet

UpdateDs(DataSet

changedDs,string

tableName)

{

try

{

this.da

=

new

SqlDataAdapter(this._strSql,this.GetConn())

this.sqlCmdBuilder

=

new

SqlCommandBuilder(da)

this.da.Update(changedDs,tableName)

changedDs.AcceptChanges()

return

changedDs//返回更新了的数据库表

}

catch

(Exception

ex)

{

MessageBox.Show(ex.Message,"数据库更新失败")

throw

}

}

参数说明:changedDs--更新了的DataSet对象,tableName--要更新的数据库表名

/

///

更新DataSet

///

///

///

///

///

public

static

void

Update(string

connectionString,

DataSet

ds,

string

sqlBefore,

string

sqlAfter)

{

SqlDataAdapter

da

=

null

SqlTransaction

tran

=

null

using(SqlConnection

cnn

=

new

SqlConnection(connectionString))

{

cnn.Open()

tran

=

cnn.BeginTransaction()

if(!string.IsNullOrEmpty(sqlBefore.Trim()))

//执行更新前的SQL

{

using(SqlCommand

cmd

=

new

SqlCommand(sqlBefore.Trim(),

cnn,

tran))

{

cmd.ExecuteNonQuery()

}

}

foreach(DataTable

dt

in

ds.Tables)

{

string

sql

=

string.Format("SELECT

*

FROM

{0}

WHERE

1

=

0

",

dt.TableName)

if(dt.ExtendedProperties["SELECT"]

!=

null)

{

sql

=

dt.ExtendedProperties["SELECT"].ToString()

}

da

=

new

SqlDataAdapter()

SqlCommandBuilder

cb

=

new

SqlCommandBuilder(da)

da.SelectCommand

=

new

SqlCommand(sql,

cnn,

tran)

da.Update(ds,

dt.TableName)

}

if(!string.IsNullOrEmpty(sqlAfter.Trim()))

//执行更新后的SQL

{

using(SqlCommand

cmd

=

new

SqlCommand(sqlAfter.Trim(),

cnn,

tran))

{

cmd.ExecuteNonQuery()

}

}

tran.Commit()

}

}

///

///

更新DataSet

///

///

///

///

public

static

void

Update(string

connectionString,

DataSet

ds)

{

Update(connectionString,

ds,

string.Empty,

string.Empty)

}