我收到一个错误，说：
INSERT语句中的行值表达式数超过了允许的最大1000行值数。
我正在从API-call获取JSON-数据。当我试图将其插入SQL Server数据库时，会出现错误。我正试图插入3000行。我该如何解决这个问题？来自APi的调用结果存储在变量"0"中；"主体"；，然后反序列化为变量"0"；json"；。
这是我的代码：
using (var response = await client.SendAsync(request))
{
response.EnsureSuccessStatusCode();
var body = await response.Content.ReadAsStringAsync();
var json = JsonConvert.DeserializeObject<Root>(body);
var Api = json.api;
string SqlString = "INSERT INTO land.leagues(" +
"league_id" +
",name" +
",type" +
",country" +
",country_code" +
",season" +
",season_start" +
",season_end" +
",logo" +
",flag" +
",standings" +
",is_current" +
",coverage_standings" +
",coverage_players" +
",coverage_topScorers" +
",coverage_predictions" +
",coverage_odds" +
",coverage_fixtures_events" +
",coverage_fixtures_lineups" +
",coverage_fixtures_statistics" +
",coverage_fixtures_playersStatistics" +
",created" +
") VALUES";
foreach (var a in Api.leagues)
{
SqlString += "(" +
"'" + a.league_id +
"','" + a.name.Replace("'","`") +
"','" + a.type +
"','" + a.country +
"','" + a.country_code +
"','" + a.season +
"','" + a.season_start +
"','" + a.season_end +
"','" + a.logo +
"','" + a.flag +
"','" + a.standings +
"','" + a.is_current +
"','" + a.coverage.standings +
"','" + a.coverage.players +
"','" + a.coverage.topScorers +
"','" + a.coverage.predictions +
"','" + a.coverage.odds +
"','" + a.coverage.fixtures.events +
"','" + a.coverage.fixtures.lineups +
"','" + a.coverage.fixtures.statistics +
"','" + a.coverage.fixtures.players_statistics +
"','" + DateTime.Now +
"'),";
}
SqlConnection con = new SqlConnection(@"_ConnectionString_");
SqlCommand cmd;
con.Open();
cmd = new SqlCommand("TRUNCATE TABLE land.leagues " + SqlString.Remove(SqlString.Length - 1), con);
cmd.ExecuteNonQuery();
}
这些都是我的课程：
public class Fixtures
{
public bool events { get; set; }
public bool lineups { get; set; }
public bool statistics { get; set; }
public bool players_statistics { get; set; }
}
public class Coverage
{
public bool standings { get; set; }
public Fixtures fixtures { get; set; }
public bool players { get; set; }
public bool topScorers { get; set; }
public bool predictions { get; set; }
public bool odds { get; set; }
}
public class League
{
public int league_id { get; set; }
public string name { get; set; }
public string type { get; set; }
public string country { get; set; }
public string country_code { get; set; }
public int season { get; set; }
public string season_start { get; set; }
public string season_end { get; set; }
public string logo { get; set; }
public string flag { get; set; }
public int standings { get; set; }
public int is_current { get; set; }
public Coverage coverage { get; set; }
}
public class Api
{
public int results { get; set; }
public List<League> leagues { get; set; }
}
public class Root
{
public Api api { get; set; }
}
您应该逐个插入每一行。
https://www.red-gate.com/simple-talk/sql/performance/comparing-multiple-rows-insert-vs-single-row-insert-with-three-data-load-methods/
Gist-23列意味着一次插入2个以上是不值得的。从时间上看，单行插入几乎和多行插入一样快。有一种情况下，字符串化参数可以给你50%的提升，但IMO不值得。如果你把列减少到2甚至7，这可能是值得的。
对这些估计持保留态度——盒子的性能可能会影响相对效益。
当然，一次只插入一行可以很容易地对查询进行参数化并保持清晰，同时消除sql注入的可能性。不必每次都编译sql，同时在客户端上完成大部分参数工作也会有很大帮助。
以这种方式插入的最大行数为1000，因此错误是合理的。我只需将我的行划分为数千行，并为每一批行生成一个新的INSERT语句。希望这是有意义的：(