jwcolby
jwcolby at colbyconsulting.com
Sun Apr 27 07:12:19 CDT 2008
LOL, now that I can readi it... ;-)
>> and voila' the converted VB.NET is below : (watch line wraps now -
this is
Shamil Salakhetdinov wrote:
> 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 >>>
>
> _______________________________________________
> dba-VB mailing list
> dba-VB at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-vb
> http://www.databaseadvisors.com
>
>
--
John W. Colby
www.ColbyConsulting.com