public bool InsertInfos()
{
var list = new List <Tb_Student>();
for (int i = 60; i < 70; i++)
{
var student = new Tb_Student()
{
ID = i, Name = "张三" + i, Age = 30, CreateTime = DateTime.Now, ClassNo = 31
};
list.Add(student);
}
//添加
DataTable dt = new DataTable();
dt.Columns.AddRange(new DataColumn[] {
new DataColumn("Name", typeof(string)),
new DataColumn("Age", typeof(int)),
new DataColumn("CreateTime", typeof(DateTime)),
new DataColumn("ClassNo", typeof(int)),
});
for (int i = 0; i < list.Count; i++)
{
var item = list[i];
dt.Rows.Add(item.Name, item.Age, item.CreateTime, item.ClassNo);
}
SqlConnection conn = new SqlConnection();
conn.ConnectionString = connString;
conn.Open();
SqlTransaction sqlbulkTransaction = conn.BeginTransaction();
//请在插入数据的同时检查约束，如果发生错误调用sqlbulkTransaction事务
SqlBulkCopy copy = new SqlBulkCopy(conn, SqlBulkCopyOptions.CheckConstraints, sqlbulkTransaction);
copy.DestinationTableName = "Tb_Student";
foreach (DataColumn dc in dt.Columns)
{
copy.ColumnMappings.Add(dc.ColumnName, dc.ColumnName);
}
try
{
copy.WriteToServer(dt);
sqlbulkTransaction.Commit();
}
catch (Exception ex)
{
sqlbulkTransaction.Rollback();
Console.WriteLine(ex.ToString());
return(false);
}
finally
{
copy.Close();
conn.Close();
}
return(true);
}
public bool Inserts()
{
var list = new List <Tb_Student>();
for (int i = 20; i < 31; i++)
{
var student = new Tb_Student()
{
ID = i, Name = "张三" + i, Age = 30, CreateTime = DateTime.Now, ClassNo = 31
};
list.Add(student);
}
//添加
var dt = new DataTable();
dt.Columns.AddRange(new DataColumn[] {
new DataColumn("Name", typeof(string)),
new DataColumn("Age", typeof(int)),
new DataColumn("CreateTime", typeof(DateTime)),
new DataColumn("ClassNo", typeof(int)),
});
for (int i = 0; i < list.Count; i++)
{
var item = list[i];
dt.Rows.Add(item.Name, item.Age, item.CreateTime, item.ClassNo);
}
using (SqlConnection conn = new SqlConnection(connString))
{
string sql = string.Format("select {0} from {1} where id=0", "*", "Tb_Student");
try
{
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = new SqlCommand(sql, conn);
SqlCommandBuilder scb = new SqlCommandBuilder(adapter);
scb.ConflictOption = ConflictOption.OverwriteChanges;
scb.SetAllValues = true;
adapter.Update(dt);
dt.AcceptChanges();
adapter.Dispose();
}
catch (SqlException e)
{
conn.Close();
return(false);
}
}
return(true);
}
public bool Updatas()
{
var list = new List <Tb_Student>();
for (int i = 20; i < 31; i++)
{
var student = new Tb_Student()
{
ID = i, Name = "张三" + i, Age = 30, CreateTime = DateTime.Now, ClassNo = 31
};
list.Add(student);
}
using (SqlConnection conn = new SqlConnection(connString))
{
string sql = string.Format("select {0} from {1}", "ID,Name,Age,CreateTime,ClassNo", "Tb_Student");
try
{
DataSet ds = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = new SqlCommand(sql, conn);
SqlCommandBuilder scb = new SqlCommandBuilder(adapter);
scb.ConflictOption = ConflictOption.OverwriteChanges;
scb.SetAllValues = true;
adapter.Fill(ds);
var dtInfo = ds.Tables[0];
foreach (var item in list)
{
var info = dtInfo.Select("ID=" + item.ID).FirstOrDefault();
if (info != null)
{
info["Name"] = item.Name;
info["Age"] = item.Age;
}
}
adapter.Update(dtInfo);
adapter.Dispose();
}
catch (SqlException e)
{
conn.Close();
return(false);
}
}
return(true);
}
