|
If you are using sql 2005 server then you could look at SqlServer Management objects(SMO)
transfer<code> method. This will allow you to move the whole schema and tables over at one time. SMO is not always the easiest to implement, but with a little monkeying around you should be able to get it to work. Check out an article I posted a couple weeks ago to get started, as well as search the site for SMO. There are not allot of resources on it but it might help.<br />
<br />
Aaron <br />
<br />
<div class="ForumSig">_____________________________________________________________________<br />
<br />
Our developers never release <br />
code. Rather, it tends to escape, pillaging the countryside all around.<br />
<br />
The Enlightenment Project (paraphrased comment)<br />
<br />
Visit Me at <a href="http://www.gisdevcafe.com" rel="nofollow">GISDevCafe</a></div>
|
|
|
|
|
Using ADO.NET how do I iterate through each record in a result set, test a field or fields for the presence of certain values then update the field if those values exist? We take in large amounts of records (millions of rows) from clients which are loaded into a SQL Server database - I have noticed some of the columns have unwanted characters in them (tabs, linefeeds, odd graphic characters etc) so I need to vist each row in turn, iterate through all the columns checking for these unwanted values, remove them and update the data.
I don't want to use a DataSet as I need to iterate through 10s of millions of rows. I can easily visit each row using OleDbDataReader and then iterate through the columns to test for the values but how can I then update the columns where the data has changed?
|
|
|
|
|
If you just want to remove or replace tab characters then I would advise something like:
update MyTable set MyColumn = Replace(MyColumn, Char(9), ' ')
where MyColumn like '%' + Char(9) + '%' That way you won't have millions of rows coursing across your network.
Andy
|
|
|
|
|
It's not just tabs though, it can be any number of different undesirable characters and I often don't want to do a straight replace. Dragging the rows through the nextwork isn't much of an issue as I intend to run it overnight when the server is quiet.
|
|
|
|
|
RugbyLeague wrote: Dragging the rows through the nextwork isn't much of an issue as I intend to run it overnight when the server is quiet.
You are still best to do all of it in SQL if you can. If nothing else it means that any future batch processes you need to perform overnight will have time to operate.
|
|
|
|
|
I realise it is best to do it SQL but in this instance (and others) I would like to do it in C# fo reasons which aren't relevant to the question.
|
|
|
|
|
RugbyLeague wrote: I realise it is best to do it SQL but in this instance (and others) I would like to do it in C# fo reasons which aren't relevant to the question.
Then just issue an update command back to the database. You can write pretty much any SQL that the target database will understand in the various flavours of command object.
e.g.
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = "UPDATE MyTable "+
"SET SomeColumn = @SomeValue "+
"WHERE PrimaryKey = @id";
cmd.Parameters.Add("@SomeValue", updatedValue);
cmd.Parameters.Add("@id", reader.GetInt32(0));
cmd.ExecuteNonQuery();
|
|
|
|
|
Thanks, that is pretty much what I am doing. I was just wondering if there is a less verbose way of doing it - I guess sometimes one just has to do some typing 
|
|
|
|
|
If it is something that you'll repeatedly use from various different bits of code, you could create a method for it and pass the id and the updated value. Then the method does the work and the various bits of code that need it only have to call the one method.
|
|
|
|
|
Hopefully it's not something I will be doing a lot (if ever again) - as has been said, its often best to do such work in SQL.
This sort of processing I normally do on the raw text files we receive but on this occasion the data was loaded into SQL Server before the anomalies were noticed.
|
|
|
|
|
after you read a record, then simply check for your required condition and accodrding ly give your update query. Everthing comes in while(dr.read)....
if(...).....update....
Gautham
|
|
|
|
|
|
Hi
I m tryring to configure report server the server is not initializing anyone can provide help regarding to configure report server or what is the appropriate forum for this.
Thanks
Shahzad Aslam
|
|
|
|
|
Hello C# coder,
Lets straight to the point;
I'm trying to get the schema of a table in Database (SQL Server, Access) using GetSchemaTable() method.
With same table structure (both in SQL, Access), I got different result of schema. For example, Field that has attribute Required=True in my table, the schema of SQL DB showed AllowDBNULL = FALSE but in Access showed AllowDBNULL = TRUE.
These are the snippets of my code
<br />
OleDbConnection conn1 = new OleDbConnection("Provider=SQLNCLI.1;Datasource=DEV06\\SQLEXPRESS;Integrated Security=SSPI;Initial Catalog=JatiDB");<br />
<br />
conn1.Open();<br />
OleDbCommand command = new OleDbCommand("select * from parent",conn1);<br />
OleDbDataReader reader = command.ExecuteReader();<br />
DataTable schema = reader.GetSchemaTable();<br />
conn1.Close();<br />
In my program, I've changed the value of ConnectionString for accessing Ms. Access, and works.
So my question is;
1. Does GetSchemaTable() results different output for each database?
2. If so, is there another alternative to get the schema with same output?
Thanks, any suggestion is greatly appreciated.
TIA.
Jati Indrayanto.
Everything is possible.
|
|
|
|
|
There are several methods on the oledbconnection class (including GetOLEDBSchemaTable) that may be useful.
|
|
|
|
|
Is there any form generator or template to create search forms? I just want to display data from my sql server 2005 tables using drop down menu or search field options.
I'll greatly appreciate any help.
|
|
|
|
|
What is the SQL commands(script) to get a table size?
|
|
|
|
|
Try sp_spaceused 'MyTableName'.
|
|
|
|
|
Thanks that worked ;)
Programmer: A biological machine designed to convert caffeine into code. Developer: A person who develops working systems by writing and using software.
[ ^]
|
|
|
|
|
Hi everybody
I am trying to update some file names values into the table through listbox
here i have two listboxes, in listbox1, i have some filenames, which i can add or remove from listbox2
When the user logs in , he has some files in listbox2, which is already assigned to him.
If he tries to remove some from listbox2, or add some more from lisbtox1, then update, the filenames are not getting updated
Suppose the user chooses file1, file2, from listbox1, add to lisbox2 and then update, it is not getting updated.
i am inserting the listbox1.item.value along with the contactid.
Public Sub UpdateAccounts(ByVal contactid As Int32)
Dim userBll As UserCreationBLL = New UserCreationBLL()
Dim listitems As Int32 = lstAllAccounts.Items.Count
Dim lstItems As Int32 = lstAccountAccess.Items.Count
If (lstItems > 0) Then
Dim i As Int16
For i = 0 To lstItems - 1
userBll.UpdateAccounts(contactid, Convert.ToInt32(lstAccountAccess.Items(i).Value))
Next i
End If
End Sub
SP for updating is
ALTER PROCEDURE UpdateAccounts
@contactID int,
@accountID int
AS
UPDATE [Contact Account ]
SET [Account ID] = @accountID
WHERE [Contact ID] = @contactID
thanks
|
|
|
|
|
I'm not sure I follow this entirely but several points stand out to me.
First, you are updating one field for two selections by your example so only the last one sticks. Do you need an insert to support new records? Is your structure designed correctly to support the data you are trying to save?
Hope this helps,
Ware
Programmers are tools to convert caffiene to code.
|
|
|
|
|
Can you please help, in fixing this if i detail the exact thing
Listbox1 listbox2
file1 file4
file2 file7
file3
file5
file6
this is what user see.
listbox2 contains files already assigned to him
now he added file1, file2 from listbox1 to listbox2, so that listbox2 has
file4
file7
file1
file2
then he clciks update.
Again when he logs in, he can see only file2, where i want to display the 4 files he selected
hope someone can help me
-- modified at 11:39 Wednesday 7th February, 2007
|
|
|
|
|
I am going to use the following data as a basis for the before and after examples:
Table: Contact [Contact ID] [Name]
Row : 1 1 user
Table: Account [Account ID] [Name]
Row : 1 1 file1
Row : 2 2 file2
Row : 3 3 file3
Row : 4 4 file4
Row : 5 5 file5
Row : 6 6 file6
Row : 7 7 file7
I am assuming the data before looks something like this:
Table: Contact Account [Contact ID] [Account ID]
Row : 1 1 4
Row : 2 1 7
I am assuming the data after looks something like this:
Table: Contact Account [Contact ID] [Account ID]
Row : 1 1 2
Row : 2 1 2
You will need to keep a copy of the original data loaded into listbox2 for comparison purposes. You need to compare the original contents to the current contents when the user clicks update.
For any items removed you will need to call a procedure that is coded like this:
CREATE PROCEDURE usp_ContactAccount_Delete
( @contactID int
, @accountID int
)
AS
DELETE FROM [Contact Account ]
WHERE [Contact ID] = @contactID
AND [Account ID] = @accountID
For any items added you will need to call a procedure that is coded like this:
CREATE PROCEDURE usp_ContactAccount_Insert
( @contactID int
, @accountID int
)
AS
INSERT INTO [Contact Account ]
( [Contact ID]
, [Account ID]
)
VALUES
( @contactID
, @accountID
)
Ware
Programmers are tools to convert caffiene to code.
|
|
|
|
|
Hi!
Here's a meaner. I have a database that looks like this:
Names Table
Surnames Table
Car Names Table
Master Table
The first 3 are linked to the master. What happens is that all the tables has an ID (Primary key). After entering the details a stored procedure takes the ID's o the tables and add them in the Master Table.
Question: Is it possible to Import data from Excel into my tables and still get the same result. How can I utilize a Stored Procedure using DTS?
Illegal Operation
Making Computer Software Talk
|
|
|
|
|
DTS allows you to import data from Excel spreadsheets. I would import the data into a database table, then use an "Execute SQL Task" (the tin can with red arrows) to do something along the lines of:
declare @col1 varchar(10), @col2 date, @col3 int
declare c1 cursor for
select * from MyExcelNames
open c1
while (@@fetch_status = 0) begin
fetch c1 into @col1, @col2, @col3
if (@@fetch_status = 0) begin
exec MyNamesProcedure @col1, @col2, @col3
end
end
close c1This will run your stored procedure once for each row imported from Excel.
Hope this helps.
Andy
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
