C# execute store procedure with in parameter,connection timeout 0 indicates no limit
//sp use mydb; CREATE PROCEDURE InsertIntoMtSpInStarEnd @StartId int =1, @EndId int =2021474638 AS BEGIN WHILE @StartId <= @EndId BEGIN DECLARE @Author varchar(100)='Author'+CAST(@StartId AS VARCHAR(10)); DECLARE @Comment varchar(100)='Comment'+CAST(@StartId AS VARCHAR(10)); DECLARE @Header varchar(100)='Header'+CAST(@StartId AS VARCHAR(10)); DECLARE @Name varchar(100)='Name'+CAST(@StartId AS VARCHAR(10)); DECLARE @ISBN varchar(100)='ISBN'+CAST(@StartId AS VARCHAR(10)); DECLARE @Title varchar(100)='Title'+CAST(@StartId AS VARCHAR(10)); DECLARE @Topic varchar(100)='Topic'+CAST(@StartId AS VARCHAR(10)); DECLARE @ImgUrl varchar(100)='ImgUrl'+CAST(@StartId AS VARCHAR(10)); INSERT INTO mt(author,comment,header,name,isbn,title,topic,imgurl) VALUES (@Author, @Comment,@Header,@Name,@ISBN,@Title,@Topic,@ImgUrl); SET @StartId = @StartId + 1; END; END;
use mydb; declare @startId as int; declare @endId as int; set @startId=1; set @endId=100000; exec InsertIntoMtSpInStarEnd @startId,@endId;
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Data.SqlClient; using System.Data; namespace ConsoleApp14 { internal class Program { static void Main(string[] args) { ExecuteSPWithInParameter(1, 100000); Console.ReadLine(); Console.WriteLine("Finished!"); } static void ExecuteSPWithInParameter(int startId,int endId) { try { string connStr = "Data Source=localhost;Initial Catalog=mydb;Integrated Security=True;Connection Timeout=1800"; using (SqlConnection conn = new SqlConnection(connStr)) { conn.Open(); Console.WriteLine(conn.State); string spName = "InsertIntoMtSpInStarEnd"; SqlCommand sqlCmd = new SqlCommand(spName, conn); sqlCmd.CommandType = CommandType.StoredProcedure; sqlCmd.Parameters.AddWithValue("@StartId", SqlDbType.Int).Value = startId; sqlCmd.Parameters.AddWithValue("@EndId", SqlDbType.Int).Value = endId; int spExecutedResult = sqlCmd.ExecuteNonQuery(); Console.WriteLine(spExecutedResult); } } catch (Exception ex) { Console.WriteLine(ex.Message); } } } }