sqldatareader 分批，分页操作数据库

Sub bind()

Dim pagesize As Integer = 100 Dim pagecount As Integer = 0 Dim recordcount As Integer = 0

Dim tongdao As Integer = 0 tongdao = DropDownList1.SelectedValue

Dim date1 As DateTime = Nothing Dim date2 As DateTime = Nothing date1 = sdate.Text date2 = edate.Text

Dim con As String = ""

If tongdao <> 0 Then con = " and " End If

Dim cn As New SqlConnection(ConfigurationManager.ConnectionStrings("connstr").ToString) cn.Open() 取recordcount Dim cmc As New SqlCommand("select count(*) from info_all where " & con & " in_Date between " & date1 & " and " & date2 & "", cn) recordcount = cmc.ExecuteScalar cmc.Dispose() cmc = Nothing

Response.Write(recordcount) 算页数 If recordcount Mod pagesize = 0 Then pagecount = recordcount / pagesize Else pagecount = Int(recordcount / pagesize) + 1 End If

Response.Write(p)

Dim cn2 As New SqlConnection(ConfigurationManager.ConnectionStrings("connstr").ToString) cn2.Open()

Dim cm As New SqlCommand("select in_book,in_mobile,type_id,in_result,in_msg,fpbs from info_all where " & con & " (in_date between @date1 and @date2) order by in_id asc", cn) Dim cm As New SqlCommand("select top " & pagesize & " in_book,in_mobile,type_id,in_result,in_msg,fpbs from info_all where (in_id not in (select top " & p * pagesize & " in_id from info_all where " & con & " (in_date between @date1 and @date2) order by in_id asc)) and " & con & " (in_date between @date1 and @date2) order by in_id asc", cn) SELECT TOP 2 * FROM info_all WHERE (in_ID NOT IN (SELECT TOP 4 in_id FROM info_all ORDER BY in_id)) ORDER BY in_ID

If tongdao <> 0 Then cm.Parameters.Add(New SqlParameter("@type_id", SqlDbType.Int)) cm.Parameters("@type_id").Value = tongdao End If

cm.Parameters.Add(New SqlParameter("@date1", SqlDbType.DateTime)) cm.Parameters.Add(New SqlParameter("@date2", SqlDbType.DateTime))

cm.Parameters("@date1").Value = date1 cm.Parameters("@date2").Value = date2

Dim dr As SqlDataReader

dr = cm.ExecuteReader()

If dr.HasRows Then While dr.Read()

Response.Write(dr.Item("in_book").ToString & "<Br>")

Dim cm2 As New SqlCommand("insert into info_all_temp (linkid,phone,msg,spid,fx,fphm) values (@linkid,@phone,@msg,@spid,@fx,@fphm)", cn2)

cm2.Parameters.Add(New SqlParameter("@linkid", SqlDbType.VarChar)) cm2.Parameters.Add(New SqlParameter("@phone", SqlDbType.VarChar)) cm2.Parameters.Add(New SqlParameter("@msg", SqlDbType.VarChar)) cm2.Parameters.Add(New SqlParameter("@spid", SqlDbType.Int)) cm2.Parameters.Add(New SqlParameter("@fx", SqlDbType.Int)) cm2.Parameters.Add(New SqlParameter("@fphm", SqlDbType.VarChar)) cm2.Parameters("@linkid").Value = dr.Item("in_book").ToString cm2.Parameters("@phone").Value = dr.Item("in_mobile").ToString cm2.Parameters("@msg").Value = dr.Item("in_msg").ToString cm2.Parameters("@spid").Value = dr.Item("type_id").ToString cm2.Parameters("@fx").Value = 0 cm2.Parameters("@fphm").Value = dr.Item("fpbs").ToString cm2.ExecuteNonQuery()

If dr.Item("in_result") = 0 Then

Dim cm2 As New SqlCommand("insert into info_all_temp (linkid,phone,msg,spid,fx,fphm) values (@linkid,@phone,@msg,@spid,@fx,@fphm)", cn2)

cm2.Parameters("@linkid").Value = dr.Item("in_book").ToString cm2.Parameters("@phone").Value = dr.Item("in_mobile").ToString cm2.Parameters("@msg").Value = dr.Item("in_msg").ToString cm2.Parameters("@spid").Value = dr.Item("type_id").ToString cm2.Parameters("@fx").Value = 1 cm2.Parameters("@fphm").Value = dr.Item("fpbs").ToString cm2.ExecuteNonQuery()

End If

cm2.Dispose() cm2 = Nothing

End While

If cn2.State = ConnectionState.Open Then cn2.Close() End If

End If

dr.Close() dr = Nothing cm.Dispose() cm = Nothing cn.Close() cn.Dispose() cn = Nothing

p = p + 1 If p < pagecount Then

bind()

End If

Page.ClientScript.RegisterStartupScript(Me.GetType, "ok", "<script langauge=javascript>alert(同步完成!)</script>")

End Sub