Shamil Salakhetdinov
shamil at smsconsulting.spb.ru
Sun Apr 27 04:08:08 CDT 2008
Hello John,
OK, I will read the blog you mentioned later today...
I know it's not easy to convince you to change your mind when you are
"driving your way full speed" :) And I'm not trying to - I'm just musing in
this coding samples I post here testing this or that approach, which I might
use in my own development...
<<<
There are many things to consider when I program in .net, not the least
of which is simplicity.
>>>
May I say that proposed in C# solution is simple and almost ready to use in
your scenario in one thread? And also ready to be converted into a
multi-threading app/utility. And you don't need to translate in manually in
VB.NET - just use this free tool:
http://labs.developerfusion.co.uk/convert/csharp-to-vb.aspx
or this one:
http://codeconverter.sharpdevelop.net/Convert.aspx
and voila' the converted VB.NET is below : (watch line wraps now - this is
VB.NET - it was "automagically" converted by referred above tool by copying
and pasting C# code I posted yesterday + making some manual editing of
several code line to make it independent of my testing/development
environment) - and it seems to work even quicker than C# equivalent somehow
!?. I have got additional back VB.NET -> C# conversion in the end of this
message - you can use C# code copy and paste it in the tool refereed above
and get VB.NET code properly formatted. Proposal: let's post C# code here
for the folks who will try this code to not spend time with fixing line
wraps introduced by e-mail clients):
Imports System
Imports System.Collections.Generic
Imports System.Data.SqlClient
Namespace TestSamples
Public Class DataItem
Public ID As Integer
Public Name As String
Public Sub New(ByVal id As Integer, ByVal name As String)
ID = id
Name = name
End Sub
Public ReadOnly Property ParsedName() As String
Get
' just for testing purposes
' return real parsed name here
Return Name
End Get
End Property
End Class
Public Class DataProcessor
Private Const COUNT_SQL As String = "select count(*) from
zsysUserSessionTrace"
Private Const PAGED_SELECT_SQL As String = "SELECT * from " + "(" +
" select " + " Cast(ROW_NUMBER() over (ORDER BY [UserSessionTraceId]) as
int) as RowNum, " + " UserSessionTraceId, " + " HostAddress, " + "
UserBrowser " + " from zsysUserSessionTrace " + ") s " + "where s.RowNum
between {0} and {1}"
Private Const UPDATE_SQL As String = "update zsysUserSessionTrace
set UserBrowser = '{0}' where UserSessionTraceId = {1}"
Private Const DEFAULT_BATCH_SIZE As Integer = 1000
Private _totalCount As Integer = -1
Public ReadOnly Property TotalCount() As Integer
Get
Return _totalCount
End Get
End Property
Private _currentBatchStartRowNum As Integer = 0
Private _batchSize As Integer = 0
Private _connectionString As String
Public Sub New(ByVal connectionString As String)
initialize(connectionString, DEFAULT_BATCH_SIZE)
End Sub
Public Sub New(ByVal connectionString As String, ByVal batchSize As
Integer)
initialize(connectionString, batchSize)
End Sub
Private Sub initialize(ByVal connectionString As String, ByVal
batchSize As Integer)
_batchSize = batchSize
_currentBatchStartRowNum = 1
_connectionString = connectionString
Using cnn As New
System.Data.SqlClient.SqlConnection(connectionString)
cnn.Open()
Dim cmd As System.Data.SqlClient.SqlCommand =
cnn.CreateCommand()
cmd.CommandText = COUNT_SQL
cmd.CommandType = System.Data.CommandType.Text
_totalCount = CInt(cmd.ExecuteScalar())
End Using
End Sub
Public ReadOnly Property HasData() As Boolean
Get
Return (TotalCount > 0 AndAlso _currentBatchStartRowNum <
TotalCount)
End Get
End Property
Public Function GetData() As List(Of DataItem)
Using cnn As New
System.Data.SqlClient.SqlConnection(_connectionString)
cnn.Open()
Dim cmd As System.Data.SqlClient.SqlCommand =
cnn.CreateCommand()
Dim sql As String = String.Format(PAGED_SELECT_SQL,
_currentBatchStartRowNum, _currentBatchStartRowNum + _batchSize)
cmd.CommandText = sql
cmd.CommandType = System.Data.CommandType.Text
_currentBatchStartRowNum += _batchSize + 1
Dim rdr As System.Data.SqlClient.SqlDataReader =
cmd.ExecuteReader(System.Data.CommandBehavior.SequentialAccess)
Dim list As New List(Of DataItem)()
While rdr.Read()
list.Add(New DataItem(CInt(rdr(1)), DirectCast(rdr(3),
String)))
End While
Return list
End Using
End Function
Public Function Update(ByVal item As DataItem) As Integer
Using cnn As New
System.Data.SqlClient.SqlConnection(_connectionString)
cnn.Open()
Dim cmd As System.Data.SqlClient.SqlCommand =
cnn.CreateCommand()
Dim sql As String = String.Format(UPDATE_SQL,
item.ParsedName, item.ID)
cmd.CommandText = sql
cmd.CommandType = System.Data.CommandType.Text
Return cmd.ExecuteNonQuery()
End Using
End Function
End Class
Public Class A_MainEntry
'27.04.2008 12:45:58
'Count(*) = 25045
'Counter = 25045
'27.04.2008 12:46:04
Public Shared Sub Main()
Dim count As Integer = 0
Try
'+ customize - set your connection string here
Dim pcName As String = "SPB"
Dim dbName As String = "dbName"
Dim userName As String = "sa"
Dim password As String = "sapwd"
Dim connectionString As String = _
String.Format("Data Source={0}\SQLEXPRESS;Initial
Catalog={1};User Id={2};Password={3}", _
pcName, dbName, userName, password)
'-
Console.WriteLine(DateTime.Now)
Dim p As New DataProcessor(connectionString)
While p.HasData
Dim list As List(Of DataItem) = p.GetData()
For Each item As DataItem In list
p.Update(item)
count += 1
Next
End While
Console.WriteLine("Count(*) = {0}", p.TotalCount)
Console.WriteLine("Counter = {0}", count)
Console.WriteLine(DateTime.Now)
Catch ex As Exception
Console.WriteLine(ex.Message)
End Try
End Sub
End Class
End Namespace
Here it's converted back to C#:
//http://labs.developerfusion.co.uk/convert/csharp-to-vb.aspx
//http://codeconverter.sharpdevelop.net/Convert.aspx
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
namespace TestSamples
{
public class DataItem
{
public int ID;
public string Name;
public DataItem(int id, string name)
{
ID = id;
Name = name;
}
public string ParsedName
{
// just for testing purposes
// return real parsed name here
get { return Name; }
}
}
public class DataProcessor
{
private const string COUNT_SQL = "select count(*) from
zsysUserSessionTrace";
private const string PAGED_SELECT_SQL = "SELECT * from " + "(" + "
select " + " Cast(ROW_NUMBER() over (ORDER BY [UserSessionTraceId]) as int)
as RowNum, " + " UserSessionTraceId, " + " HostAddress, " + " UserBrowser "
+ " from zsysUserSessionTrace " + ") s " + "where s.RowNum between {0} and
{1}";
private const string UPDATE_SQL = "update zsysUserSessionTrace set
UserBrowser = '{0}' where UserSessionTraceId = {1}";
private const int DEFAULT_BATCH_SIZE = 1000;
private int _totalCount = -1;
public int TotalCount
{
get { return _totalCount; }
}
private int _currentBatchStartRowNum = 0;
private int _batchSize = 0;
private string _connectionString;
public DataProcessor(string connectionString)
{
initialize(connectionString, DEFAULT_BATCH_SIZE);
}
public DataProcessor(string connectionString, int batchSize)
{
initialize(connectionString, batchSize);
}
private void initialize(string connectionString, int batchSize)
{
_batchSize = batchSize;
_currentBatchStartRowNum = 1;
_connectionString = connectionString;
using (System.Data.SqlClient.SqlConnection cnn = new
System.Data.SqlClient.SqlConnection(connectionString))
{
cnn.Open();
System.Data.SqlClient.SqlCommand cmd = cnn.CreateCommand();
cmd.CommandText = COUNT_SQL;
cmd.CommandType = System.Data.CommandType.Text;
_totalCount = (int)cmd.ExecuteScalar();
}
}
public bool HasData
{
get { return (TotalCount > 0 && _currentBatchStartRowNum <
TotalCount); }
}
public List<DataItem> GetData()
{
using (System.Data.SqlClient.SqlConnection cnn = new
System.Data.SqlClient.SqlConnection(_connectionString))
{
cnn.Open();
System.Data.SqlClient.SqlCommand cmd = cnn.CreateCommand();
string sql = string.Format(PAGED_SELECT_SQL,
_currentBatchStartRowNum, _currentBatchStartRowNum + _batchSize);
cmd.CommandText = sql;
cmd.CommandType = System.Data.CommandType.Text;
_currentBatchStartRowNum += _batchSize + 1;
System.Data.SqlClient.SqlDataReader rdr =
cmd.ExecuteReader(System.Data.CommandBehavior.SequentialAccess);
List<DataItem> list = new List<DataItem>();
while (rdr.Read())
{
//error in automated conversion => list.Add(new
DataItem((int)rdr(1), (string)rdr(3)));
list.Add(new DataItem((int)rdr[1], (string)rdr[3]));
}
return list;
}
}
public int Update(DataItem item)
{
using (System.Data.SqlClient.SqlConnection cnn = new
System.Data.SqlClient.SqlConnection(_connectionString))
{
cnn.Open();
System.Data.SqlClient.SqlCommand cmd = cnn.CreateCommand();
string sql = string.Format(UPDATE_SQL, item.ParsedName,
item.ID);
cmd.CommandText = sql;
cmd.CommandType = System.Data.CommandType.Text;
return cmd.ExecuteNonQuery();
}
}
}
public class A_MainEntry
{
// VB.NET
//27.04.2008 13:01:44
//Count(*) = 25045
//Counter = 25045
//27.04.2008 13:01:49
// C#
//27.04.2008 13:00:56
//Count(*) = 25045
//Counter = 25045
//27.04.2008 13:01:04
public static void Main()
{
int count = 0;
try
{
//+ customize - set your connection string here
string pcName = "SPB";
string dbName = "dbName";
string userName = "sa";
string password = "sapwd";
string connectionString = string.Format("Data
Source={0}\\SQLEXPRESS;Initial Catalog={1};User Id={2};Password={3}",
pcName, dbName, userName, password);
//-
Console.WriteLine(DateTime.Now);
DataProcessor p = new DataProcessor(connectionString);
while (p.HasData)
{
List<DataItem> list = p.GetData();
foreach (DataItem item in list)
{
p.Update(item);
count += 1;
}
}
Console.WriteLine("Count(*) = {0}", p.TotalCount);
Console.WriteLine("Counter = {0}", count);
Console.WriteLine(DateTime.Now);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
}
}
-----Original Message-----
From: dba-vb-bounces at databaseadvisors.com
[mailto:dba-vb-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Sunday, April 27, 2008 4:46 AM
To: Discussion concerning Visual Basic and related programming issues.
Subject: Re: [dba-VB] ADO.Net
This is the blog I mentioned:
http://blogs.msdn.com/dataaccess/archive/2005/05/19/420065.aspx
<<< tail skipped to get message through in AccessD >>>