第一种方法:
public static void AddDB()
{
SqlConnection conn = new SqlConnection(connectionString);
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.InsertCommand = new SqlCommand("insert into Name(userName) values(@name)", conn);
adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.None;
//@name参数、Name为DataTable中列名称
adapter.InsertCommand.Parameters.Add("@name", SqlDbType.Char, 18,"Name");
//创建DataTable
DataTable dt = new DataTable();
DataColumn dcname = new DataColumn("Name", typeof(System.String));
dt.Columns.Add(dcname);
for (int i = 0; i < 10000; i++)
{
DataRow row = dt.NewRow();
row["Name"] = i.ToString();
dt.Rows.Add(row);
}
DataSet ds = new DataSet();
ds.Tables.Add(dt);
adapter.Update(ds.Tables[0]);
}
第二种方法:
public static void AddDBSqlBulkCopy()
{
DataTable dt = new DataTable();
DataColumn dcname = new DataColumn("Name", typeof(System.String));
dt.Columns.Add(dcname);
for (int i = 0; i < 10; i++)
{
DataRow row = dt.NewRow();
row["Name"] = "Name_" + i.ToString();
dt.Rows.Add(row);
}
DataSet ds = new DataSet();
ds.Tables.Add(dt);
SqlConnection conn = new SqlConnection(connectionString);
conn.Open();
//声明SqlBulkCopy ,using释放非托管资源
using (SqlBulkCopy sqlBC = new SqlBulkCopy(conn))
{
//一次批量的插入的数据量
sqlBC.BatchSize = 1000;
//超时之前操作完成所允许的秒数,如果超时则事务不会提交 ,数据将回滚,所有已复制的行都会从目标表中移除
sqlBC.BulkCopyTimeout = 60;
//設定 NotifyAfter 属性,以便在每插入10000 条数据时,呼叫相应事件。
sqlBC.NotifyAfter = 10000;
sqlBC.SqlRowsCopied += new SqlRowsCopiedEventHandler(sqlBC_SqlRowsCopied);
//设置要批量写入的表
sqlBC.DestinationTableName = "dbo.Name";
//自定义的datatable和数据库的字段进行对应
sqlBC.ColumnMappings.Add("Name", "userName");
// sqlBC.ColumnMappings.Add("Name", 1);
//批量写入
sqlBC.WriteToServer(dt);
}
conn.Dispose();
}
static void sqlBC_SqlRowsCopied(object sender, SqlRowsCopiedEventArgs e)
{
}
//批量修改数据
public void AddDB()
{
SqlConnection conn = n ew SqlConnection(connectionString);
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = new SqlCommand("select top 200 * from Name", conn);
adapter.UpdateCommand = new SqlCommand("update [Name] set [userName]=@username where [id]=@id", conn);
adapter.UpdateCommand.Parameters.Add("@username", SqlDbType.Char, 18, "userName");
adapter.UpdateCommand.Parameters.Add("@id", SqlDbType.Int, 18, "id");
DataSet ds = new DataSet();
adapter.Fill(ds, "NameInfo");
for (int i = 0; i < 200; i++)
{
ds.Tables[0].Rows[i].BeginEdit();
ds.Tables[0].Rows[i][1] = "update_" + i;
ds.Tables[0].Rows[i].EndEdit();
}
adapter.Update(ds.Tables[0]);
}
关于Dataset批量更新数据的实例方法
* http://www.cyqdata.com/cnblogs/article-detail-28725
* 自动批量更新DataSet中的数据到数据库
* http://hi.baidu.com/mt118/item/ca783ea95bf3453c030a4dff
* http://zhanghongweiyang.blog.163.com/blog/static/592810620092237510717/
* http://wenku.baidu.com/link?url=SU-CLitd1ixQInJAW49cVWG3TG6vYJNJ_oG6xdgSKmKZCvbq3DOkCv-vbmW_HyGWnZxDK0J9ntZ713ofxec39pV0_rzW9k4RRLk3QA_BFf3
http://www.docin.com/p-95691230.html
转载于:https://blog.51cto.com/utalents/1411289