473,549 Members | 2,862 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

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...Argum ents? 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(o bject sender, System.EventArg s e)
{
string strSQL;
strSQL = "SELECT name FROM sysobjects WHERE name LIKE 'A%";
strSQL = strSQL + "' AND xtype='U' ORDER BY name";

if (!ClassMyDbAcce ss.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\SQLSe rver\";
DirectoryInfo dirInfo = new DirectoryInfo(P ath.GetDirector yName(path));

string[] files = Directory.GetFi les(path);
foreach (string fileCrt in files)
{
using (StreamReader reader = new StreamReader(fi leCrt))
{
try
{
using (StreamWriter writer = new StreamWriter (tempTarget))
{
string line;
while ( (line=reader.Re adLine()) != null)
{
writer.WriteLin e (line.Replace(" " + tableCrt, " dbo." + tableCrt));
}
}
}
catch (Exception f)
{
Console.WriteLi ne(System.DateT ime.Now + " Error while processing file
{0} : {1}", fileCrt, f.Message);
}
}
File.Delete(fil eCrt);
File.Move(tempT arget,fileCrt);
}
}
}

Jan 4 '06 #1
4 2181
serge <se****@nospam. ehmail.com> wrote:

<snip>
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/t...winforms.shtml for more
information.

--
Jon Skeet - <sk***@pobox.co m>
http://www.pobox.com/~skeet Blog: http://www.msmvps.com/jon.skeet
If replying to the group, please do not mail me too
Jan 4 '06 #2
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.

"serge" wrote:
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...Argum ents? 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(o bject sender, System.EventArg s e)
{
string strSQL;
strSQL = "SELECT name FROM sysobjects WHERE name LIKE 'A%";
strSQL = strSQL + "' AND xtype='U' ORDER BY name";

if (!ClassMyDbAcce ss.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\SQLSe rver\";
DirectoryInfo dirInfo = new DirectoryInfo(P ath.GetDirector yName(path));

string[] files = Directory.GetFi les(path);
foreach (string fileCrt in files)
{
using (StreamReader reader = new StreamReader(fi leCrt))
{
try
{
using (StreamWriter writer = new StreamWriter (tempTarget))
{
string line;
while ( (line=reader.Re adLine()) != null)
{
writer.WriteLin e (line.Replace(" " + tableCrt, " dbo." + tableCrt));
}
}
}
catch (Exception f)
{
Console.WriteLi ne(System.DateT ime.Now + " Error while processing file
{0} : {1}", fileCrt, f.Message);
}
}
File.Delete(fil eCrt);
File.Move(tempT arget,fileCrt);
}
}
}

Jan 4 '06 #3
On Wed, 4 Jan 2006 10:29:03 -0800, Curtis
<Cu****@discuss ions.microsoft. com> wrote:

<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. [8-)

Ken Wilson
Seeking viable employment in Victoria, BC
Jan 5 '06 #4
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.

Jan 6 '06 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
3711
by: Xah Lee | last post by:
suppose you want to do find & replace of string of all files in a directory. here's the code: ©# -*- coding: utf-8 -*- ©# Python © ©import os,sys © ©mydir= '/Users/t/web'
19
2912
by: rbt | last post by:
Here's the scenario: You have many hundred gigabytes of data... possible even a terabyte or two. Within this data, you have private, sensitive information (US social security numbers) about your company's clients. Your company has generated its own unique ID numbers to replace the social security numbers. Now, management would like the IT...
0
2143
by: kovac | last post by:
The System.directoryservices.dll has an error, and this error was described in http://support.microsoft.com/default.aspx?scid=kb;en-us;839424 At the moment we have Framework version v1.0.3705 and I have remove System.directoryservices.dll from current Framework version v1.0.3705. In state of old version I load die new...
5
2621
by: pembed2003 | last post by:
Hi all, I need to write a function to search and replace part of a char* passed in to the function. I came up with the following: char* search_and_replace(char* source,char search,char* replace){ char* result; size_t l = strlen(source), r = strlen(replace), i; int number_of_replaces = 0; for(i = 0; i < l; i++){ if(source == search)
19
78773
by: Paul | last post by:
hi, there, for example, char *mystr="##this is##a examp#le"; I want to replace all the "##" in mystr with "****". How can I do this? I checked all the string functions in C, but did not find one.
9
10815
by: Terry E Dow | last post by:
Howdy, I am having trouble with the objectCategory=group member.Count attribute. I get one of three counts, a number between 1-999, no member (does not contain member property), or 0. Using LDIFDE as a comparison I get the same results. No members means just that, an empty group. Zero means that the DirectorySearcher.SizeLimit has been...
8
2109
by: Dennis Myrén | last post by:
I have these tiny classes, implementing an interface through which their method Render ( CosWriter writer ) ; is called. Given a specific context, there are potentially a lot of such objects, each requiring a call to that method to fulfill their purpose. There could be 200, there could be more than 1000. That is a lot of references passed...
4
23025
by: moondaddy | last post by:
I need to edit the text in many files so I'm writing a small routine to do this. First I have a method that loops through all the files in a directory and passes the full file path to another method (ReadFile). I'm going to use this to edit the file paths in all of my WMP play lists. When I run this the string variable is not being changed...
1
2037
by: Madhu Harchandani | last post by:
Hello i am working in a project in which i am using xnat technology in that data is entered through xml files only i need to enter 1000 records .so it is very cumbersome to create 1000 xml files So i need a simple way so that i can create and insert 1000 records in xnat database this is the xml file which i want to generate...
0
7956
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7469
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
7808
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
1
5368
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5087
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3498
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
1
1935
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1057
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
757
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.