//方法一:用DataAdapter方法,优点:不用写insert语句且不用关心values后边的各字段的形式,如要不要加单引号之类的;缺点:插入一条记录的效率不及写单纯insert语句
SqlConnection conn = ConnectDB(server, db);
string sql = "select * from " + tableName;
SqlDataAdapter da = new SqlDataAdapter(sql, conn);
SqlCommandBuilder scb = new SqlCommandBuilder(da);//SqlCommandBuilder根据insertCommand构造 updatecommand和deletecommand
DataTable dt = new DataTable();
exeTable(conn, dt, sql);
DataRow dr = dt.NewRow();
dr.ItemArray = columnValues;
dt.Rows.Add(dr);
da.Update(dt);
conn.Close();
// 方法二:单纯写insert语句插入一条记录,优点:单条数据插入效率高 缺点:需要注意sql语句的构造和sql语句中参数的类型
string sql = "insert into 表名 values(" + 值1+ "," + 值2+ ",'" + 值3+ "',"+ 值4+ "," + 值5+ "," + 值6+",@参数1,'" + 值7+ "','"+ 值8+ "')";
SqlParameter param = new System.Data.SqlClient.SqlParameter("@参数1", SqlDbType.Image);
param.Value = 值9;
SqlConnection conn = sqlHelper.ConnectDB();
SqlCommand cmd = new System.Data.SqlClient.SqlCommand(sql, conn);
if (param != null) cmd.Parameters.Add(param);
cmd.ExecuteNonQuery();
conn.Close();
//方法三:插入大批量数据时用SqlBulkCopy,单条记录插入效率较慢
DataTable dt = new DataTable();
dt.Columns.Add("列名1", typeof(string));
dt.Columns.Add("列名2", typeof(byte[]));
DataRow dr = dt.NewRow();
dr["列名1"] = 值1;
dr["列名2"] = 值2;
dt.Rows.Add(dr);
dr = dt.NewRow();
dr["列名1"] = 值3;
dr["列名2"] = 值4;
dt.Rows.Add(dr);
SqlBulkCopy bulk = new System.Data.SqlClient.SqlBulkCopy(conn);
bulk.DestinationTableName=目标表名;
bulk.BatchSize=dt.Rows.Count;
bulk.ColumnMappings.Add("列名1",目标列名1);
bulk.ColumnMappings.Add("列名2",目标列名2);
bulk.WriteToServer(dt);
bulk.Close();
conn.Close();