How to replace 1000 different values in 250 files in a fast method?

[serge]

I managed to put together C# code and have it
do the following:

1- Get all the table names that start with the letter "Z"
from sysobjects of my SQL 2000 database and put
these table names inside an array variable.

2- Loop through each table name inside the array
1 to 1000 tables

3- Loop through each SQL server script file
inside a folder, 1 to 250 files

4- Replace all occurences of " TABLENAME"
with " dbo.TABLENAME"
(TABLENAME being the values inside the array)

I am searching for the space character + the table name
and replacing it with the space character + dbo. + the
table name.

The problem is this is very slow as the C# code I am
currently using ends up opening and saving each file 1000
times. And multiply 1000 x 250, this is slowing it a lot.

If I try to change the foreach locations so that it initially
starts to loop the files and within each file loop through
the array tablenames and replace any occurences, I don't
know how I can run the REPLACE code which is
writing the contents it is reading to a new temp file,
to re-read the newly created temp file and continue
this "recursive" type of operation until it has searched
any of the 1000 table name occurences?

Can someone please help me with this?


Another thing I was trying for the last few hours
is to use the same code I have below except use
a DOS executable I had that does "replacestring.exe"
but since I am using PROCESS Shelling, this is even slower.
By the way how do I pass a double-quote parameter
to the Process...Arguments? I don't know what I am doing
wrong here. I tried writing "\"", """, """"", and many other
variations but I am unable to figure out the right syntax.


Thank you

This is my current code:

private void button3_Click(object sender, System.EventArgs e)
{
string strSQL;
strSQL = "SELECT name FROM sysobjects WHERE name LIKE 'A%";
strSQL = strSQL + "' AND xtype='U' ORDER BY name";

if (!ClassMyDbAccess.Open("(local)", "MYDB", "true", "sa", "mypass"))
return;

// Extract list of tables
ArrayList tableNames = new ArrayList();
using (SqlDataReader rdr = ClassMyDbAccess.ExecuteReader(strSQL))
{
while (rdr.Read())
tableNames.Add(rdr.GetString(0));
}

// For each table in the list
foreach (string tableCrt in tableNames)
{
string tempTarget = System.IO.Path.GetTempFileName();
string path = @"F:\Temp\SQLServer\";
DirectoryInfo dirInfo = new DirectoryInfo(Path.GetDirectoryName(path));

string[] files = Directory.GetFiles(path);
foreach (string fileCrt in files)
{
using (StreamReader reader = new StreamReader(fileCrt))
{
try
{
using (StreamWriter writer = new StreamWriter (tempTarget))
{
string line;
while ( (line=reader.ReadLine()) != null)
{
writer.WriteLine (line.Replace(" " + tableCrt, " dbo." + tableCrt));
}
}
}
catch (Exception f)
{
Console.WriteLine(System.DateTime.Now + " Error while processing file
{0} : {1}", fileCrt, f.Message);
}
}
File.Delete(fileCrt);
File.Move(tempTarget,fileCrt);
}
}
}

 

[Jon Skeet [C# MVP]]

If I try to change the foreach locations so that it initially
starts to loop the files and within each file loop through
the array tablenames and replace any occurences, I don't
know how I can run the REPLACE code which is
writing the contents it is reading to a new temp file,
to re-read the newly created temp file and continue
this "recursive" type of operation until it has searched
any of the 1000 table name occurences?

Read the line, then do something like:

foreach (string table in tableNames)
{
line = line.Replace (" "+table, " dbo."+table);
}

Then write line out. (Then you don't need the outer loop.)

By the way, it's a bad idea to do this in the UI thread. You should do
it in a different thread, updating the UI using Control.Invoke. See
http://www.pobox.com/~skeet/csharp/threads/winforms.shtml for more
information.

 

[Guest]

You don't say how big each of the files is but you could probably read the
entire file into memory and do all of the replace operations on that then
write it back out when you're done.

I managed to put together C# code and have it
do the following:

1- Get all the table names that start with the letter "Z"
from sysobjects of my SQL 2000 database and put
these table names inside an array variable.

2- Loop through each table name inside the array
1 to 1000 tables

3- Loop through each SQL server script file
inside a folder, 1 to 250 files

4- Replace all occurences of " TABLENAME"
with " dbo.TABLENAME"
(TABLENAME being the values inside the array)

I am searching for the space character + the table name
and replacing it with the space character + dbo. + the
table name.

The problem is this is very slow as the C# code I am
currently using ends up opening and saving each file 1000
times. And multiply 1000 x 250, this is slowing it a lot.

If I try to change the foreach locations so that it initially
starts to loop the files and within each file loop through
the array tablenames and replace any occurences, I don't
know how I can run the REPLACE code which is
writing the contents it is reading to a new temp file,
to re-read the newly created temp file and continue
this "recursive" type of operation until it has searched
any of the 1000 table name occurences?

Can someone please help me with this?


Another thing I was trying for the last few hours
is to use the same code I have below except use
a DOS executable I had that does "replacestring.exe"
but since I am using PROCESS Shelling, this is even slower.
By the way how do I pass a double-quote parameter
to the Process...Arguments? I don't know what I am doing
wrong here. I tried writing "\"", """, """"", and many other
variations but I am unable to figure out the right syntax.


Thank you

This is my current code:

private void button3_Click(object sender, System.EventArgs e)
{
string strSQL;
strSQL = "SELECT name FROM sysobjects WHERE name LIKE 'A%";
strSQL = strSQL + "' AND xtype='U' ORDER BY name";

if (!ClassMyDbAccess.Open("(local)", "MYDB", "true", "sa", "mypass"))
return;

// Extract list of tables
ArrayList tableNames = new ArrayList();
using (SqlDataReader rdr = ClassMyDbAccess.ExecuteReader(strSQL))
{
while (rdr.Read())
tableNames.Add(rdr.GetString(0));
}

// For each table in the list
foreach (string tableCrt in tableNames)
{
string tempTarget = System.IO.Path.GetTempFileName();
string path = @"F:\Temp\SQLServer\";
DirectoryInfo dirInfo = new DirectoryInfo(Path.GetDirectoryName(path));

string[] files = Directory.GetFiles(path);
foreach (string fileCrt in files)
{
using (StreamReader reader = new StreamReader(fileCrt))
{
try
{
using (StreamWriter writer = new StreamWriter (tempTarget))
{
string line;
while ( (line=reader.ReadLine()) != null)
{
writer.WriteLine (line.Replace(" " + tableCrt, " dbo." + tableCrt));
}
}
}
catch (Exception f)
{
Console.WriteLine(System.DateTime.Now + " Error while processing file
{0} : {1}", fileCrt, f.Message);
}
}
File.Delete(fileCrt);
File.Move(tempTarget,fileCrt);
}
}
}

 

[Ken Wilson]

On Wed, 4 Jan 2006 10:29:03 -0800, Curtis

<snip>

Please don't edit the subject lines of threads unless it is your
intent to create a completely new thread. Otherwise your reply gets
separated from the main thread which becomes hard to follow.

Many thanks. [

Ken Wilson
Seeking viable employment in Victoria, BC

 

[serge]

Thanks Jon, Curtis for the posts.

Jon I used the line you wrote and it reduced the
processing time from about an hour to 15 minutes.

Curtis a developer helped me with the code and
he used the memory and reduced the processing
time from 15 minutes to 9 minutes.

After spending a lot of hours we ended up
using Regex and this one was running
in 3 minutes time.

Now I am using Regex first and then the previous
code to replace all "dbo.dbo." with "dbo." since
the Regex code I have doesn't check if the string
it's replacing already has a "dbo." or not.

Thanks again for your help.