sqldatareader 分批，分页操作数据库

来源：互联网发布：网站php源码下载编辑：程序博客网时间：2024/05/20 03:05

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 = " type_id=@type_id 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