|
I want to export table data from one database DB1 to another DB2 (table structure matches).
I filled a dataset with one datatable and exported it into an xml-file.
Then I restore the dataset and want to update in DB2 with
a) inserting rows which are (concerning to a key column) not existing in DB2
b) updating existing rows
Part a) works well with a DataAdapter.Update() because all datarows in my
restored datatable have RowState ADDED and an INSERT is done.
Part b) is the problem: The remaining rows should cause an update of the
non-key columns. I call SetModified() for all DataRows to change RowState to MODIFIED.
The DataAdapter.Update() returns that the rows were modified, but it did not
change anything in the DB2. The reason probably is, that the values of the
DataTable columns have not changed and so the update-statement does not include them.
As a work-around, I changed the values of the DataRow items and restored them to the
original values.
Then the update is done.
But this seems not to be the "official" way...
Any better idea? Thanks.
Here's the code:
private void buttonSaveXML_Click(object sender, EventArgs e)
{
OleDbConnection conn = new OleDbConnection
("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=TestDataTable.mdb;");
conn.Open();
DataSet ds = new DataSet("test-DS");
OleDbDataAdapter da = new OleDbDataAdapter("select keycol, col1, col2 from table1", conn);
da.Fill(ds, "table1");
ds.WriteXml("test-DS.xml", XmlWriteMode.WriteSchema);
conn.Close();
}
private void buttonReadXML_Click(object sender, EventArgs e)
{
OleDbConnection conn = new OleDbConnection
("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=TestDataTable.mdb;");
conn.Open();
DataSet ds = new DataSet("test-DS");
ds.ReadXml("test-DS.xml", XmlReadMode.ReadSchema);
OleDbDataAdapter da = new OleDbDataAdapter("select keycol, col1, col2 from table1", conn);
da.ContinueUpdateOnError = true;
OleDbCommandBuilder cb = new OleDbCommandBuilder(da);
cb.ConflictOption = ConflictOption.OverwriteChanges;
DataTable dt = ds.Tables["table1"];
string trace_command = cb.GetInsertCommand(true).CommandText;
int updcount = da.Update(dt);
MessageBox.Show("first da.Update() does INSERT on " + updcount.ToString() + " rows.\n" +
"InsertCommand is: " + trace_command);
for (int index = dt.Rows.Count - 1; index >= 0; index--)
if (!dt.Rows[index].HasErrors)
dt.Rows.Remove(dt.Rows[index]);
dt.AcceptChanges();
foreach (DataRow dr in dt.Rows)
dr.SetModified();
trace_command = cb.GetUpdateCommand(true).CommandText;
updcount = da.Update(dt);
MessageBox.Show("second da.Update() on " + updcount.ToString() + " rows.\n" +
"UpdateCommand is: " + trace_command);
dt.AcceptChanges();
foreach (DataRow dr in dt.Rows)
{
string save = dr["col1"].ToString();
dr["col1"] = "new changed value";
dr.AcceptChanges();
dr["col1"] = save;
}
updcount = da.Update(dt);
conn.Close();
}
|
|
|
|
|
You should not use this solution.
Natza Mitzi
|
|
|
|
|
I thought that also... and asked for better ideas...
|
|
|
|
|
SharpDevelop is participating in the Google Summer of Code this year. It is an opportunity for students to earn 4500 USD over the summer by working on an open-source project. We have created a list of ideas, but you can work basically on anything you want as long as it is relevant to SharpDevelop. You can find the list of ideas and any further information on the SharpDevelop wiki page for the Google Summer of Code. You can submit your application here. If you are not a student or if you do not have enough spare time, you can benefit as well.... share your ideas with us and maybe some student will pick them up and implement them. Feel free to contact us on the forums or via email. Please spread the word and tell your friends about the Summer of Code...
|
|
|
|
|
Please tell your friends that if they want to advertise their events, Code Project accepts advertisements.
Christian Graus
Driven to the arms of OSX by Vista.
|
|
|
|
|
hi,
i got a problem here that is i have a program where is console. i want to call the value from the console and display into windows forms.
for example in console have
int i;
......
i = .....
then i want to get the value for i and display into text box in windows forms.
like textbox1.text = ???????????
may i know how to make it. thanks.
|
|
|
|
|
tyrone0109 wrote: then i want to get the value for i
How is the value for i put into the console?
Henry Minute
Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”
|
|
|
|
|
In public static void Main(string[] args), use the parameter (args)
Example:
textbox.Text = args[0];
|
|
|
|
|
hi
i want to load my tree of a c# windows based form with all sql server 2005
databases and tables but whenever i call my function to load tables my all databases disappeared and only the tables of master database comes
please help me its very urgent im posting my code under
// it will load databases and tables
public void LoadSQL_DB(TreeNode DB)
{
TreeNode treenode;
SqlCon = new SqlConnection("Data Source= Localhost; Integrated security=True;database=master");
SqlCon.Open();
SqlCom = new SqlCommand();
SqlCom.Connection = SqlCon;
SqlCom.CommandType = CommandType.StoredProcedure;
SqlCom.CommandText = "sp_databases";
SqlDR = SqlCom.ExecuteReader();
int count = 0;
while (SqlDR.Read())
{
treenode = new TreeNode();
treenode.Text = SqlDR.GetString(0);
DB.Nodes[0].Nodes.Add(treenode);
DB.Nodes[0].Nodes[DB.Nodes[0].Nodes.IndexOf(treenode)].Nodes.Add("Tables");
DB.Nodes[0].Nodes[DB.Nodes[0].Nodes.IndexOf(treenode)].Nodes.Add("Stored Procedurs");
DB.Nodes[0].Nodes[DB.Nodes[0].Nodes.IndexOf(treenode)].Nodes.Add("Views");
DB.Nodes[0].Nodes[DB.Nodes[0].Nodes.IndexOf(treenode)].Nodes.Add("Roles");
if (DB.Nodes[0].Nodes[DB.Nodes[0].Nodes.IndexOf(treenode)].Nodes[count].Text.ToString()== "Tables")
{
LoadSQL_Tables(DB.Nodes[0].Nodes[DB.Nodes[0].Nodes.IndexOf(treenode)]);
}
count ++;
}
}// End of LoadSQL_DB...
// function to load sql tables
public void LoadSQL_Tables(TreeNode parentNode)
{
SqlCon = new SqlConnection("Data Source= Localhost; Integrated security=True;database=master");
SqlCon.Open();
SqlCom = new SqlCommand();
SqlCom.Connection = SqlCon;
string nodeName = parentNode.Text;
SqlCom.CommandText = "use "+nodeName + " select * from sys.Tables ";
//SqlCom.CommandText = "select * from sys.Tables ";
SqlDR = SqlCom.ExecuteReader();
while (SqlDR.Read())
{
parentNode.Nodes.Add(SqlDR.GetString(0));
}
}
|
|
|
|
|
I haven't had time to read through all of your code, yet, but two things strike me immediately.
1) Learn to use the 'code block' item (below the text entry box), this preserves the formatting of your code and makes it easier to read.
The code snippets in item 2 use this. You should note the difference.
2) You code would be easier to read in any case if you replace this:
treenode = new TreeNode();
treenode.Text = SqlDR.GetString(0);
DB.Nodes[0].Nodes.Add(treenode);
DB.Nodes[0].Nodes[DB.Nodes[0].Nodes.IndexOf(treenode)].Nodes.Add("Tables");
DB.Nodes[0].Nodes[DB.Nodes[0].Nodes.IndexOf(treenode)].Nodes.Add("Stored Procedurs");
DB.Nodes[0].Nodes[DB.Nodes[0].Nodes.IndexOf(treenode)].Nodes.Add("Views");
DB.Nodes[0].Nodes[DB.Nodes[0].Nodes.IndexOf(treenode)].Nodes.Add("Roles");
with:
treenode = new TreeNode();
treenode.Text = SqlDR.GetString(0);
treenode.Nodes.Add("Tables");
treenode.Nodes.Add("Stored Procedures");
treenode.Nodes.Add("Views");
treenode.Nodes.Add("Roles");
DB.Nodes[0].Nodes.Add(treenode);
I will contact you further when I have had time for more study.
Henry Minute
Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”
|
|
|
|
|
You have learned how to use sp_databases.
To solve at least part of your problem, research sp_tables and use it in your LoadSQL_Tables method. As you will see from the code below, you might also need to pass the database name to the method.
*********** WARNING *****************
sp_tables gets views, and some other stuff, as well as tables, if not used properly!!
*********** WARNING *****************
To make things a little easier for yourself, you should think about modifying the flow of your code something like:
while (SqlDR.Read())
{
treenode = new TreeNode();
treenode.Text = SqlDR.GetString(0);
TreeNode tablesNode = treenode.Nodes.Add("Tables");
LoadSQL_Tables(tablesNode, treeNode.Text);
treenode.Nodes.Add("Stored Procedures");
treenode.Nodes.Add("Views");
treenode.Nodes.Add("Roles");
DB.Nodes[0].Nodes.Add(treenode);
count ++;
}
If this works, and I hope it does, it should give you some ideas about how to get the other data you need.
Henry Minute
Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”
|
|
|
|
|
Hi
thank you so much Henry for the guide line but i am still having some problem and that is instead of my table's name, the name "master" is coming under the node of master table's node, more over the tables of other databases amd their tables are not coming.. i am very confused ..
the signature of my Load_sqlTable function is now as under
public void LoadSQL_Tables(TreeNode parentNode,string dbname)
is it correct?
regards,
|
|
|
|
|
If you are using sp_tables correctly, yes.
You need the dbname to limit the results to the table passed.
Just to be sure take a look at this[^], particularly the last example which shows an the use of dbname.
Keep experimenting, and if you are still stuck after a while, post your code for the main loop and the two methods for getdatabases and gettables.
Henry Minute
Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”
|
|
|
|
|
If you are still having problems getting the correct databases, have a look at this code snippet from one of my apps.
SqlConnection SqlCon = new SqlConnection("Data Source= Localhost; Integrated security=True;database=''");
SqlCommand SqlCom = new SqlCommand();
SqlDataReader reader;
SqlCom.CommandText = "sp_databases";
SqlComCommandType = CommandType.StoredProcedure;
SqlCom.Connection = sqlConnection1;
SqlCon.Open();
reader = SqlCom.ExecuteReader();
table = SQLBuilderUtilities.ReaderToTable(reader);
sqlConnection1.Close();
int count = 0;
foreach (DataRow row in table.Rows)
{
treenode = new TreeNode();
treenode.Text = row["DATABASE_NAME"].ToString();
TreeNode tablesNode = treenode.Nodes.Add("Tables");
LoadSQL_Tables(tablesNode, treeNode.Text);
treenode.Nodes.Add("Stored Procedures");
treenode.Nodes.Add("Views");
treenode.Nodes.Add("Roles");
DB.Nodes[0].Nodes.Add(treenode);
count ++;
}
I have modified it to use your field names etc.
The ReaderToTable method converts the reader output to a DataTable. This is useful because, apart from anything else, it means that it is very easy to set up a test form with a DataGridView, so that you can actually have a look at the results when experimenting with different stored procedures.
Here is the code, it is quite simple really:
public static DataTable ReaderToTable(SqlDataReader reader)
{
DataTable newTable = new DataTable();
DataColumn col;
DataRow row;
int i;
for (i = 0; i < reader.FieldCount; i++)
{
col = new DataColumn();
col.ColumnName = reader.GetName(i);
col.DataType = reader.GetFieldType(i);
newTable.Columns.Add(col);
}
while (reader.Read())
{
row = newTable.NewRow();
for (i = 0; i < reader.FieldCount; i++)
{
row[i] = reader[i];
}
newTable.Rows.Add(row);
}
return newTable;
}
I hope that this is of help for the database nodes. It should also give you some ideas for the tables etc.
Henry Minute
Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”
modified on Monday, March 30, 2009 7:45 AM
|
|
|
|
|
Hi
Thanks for the help but i am unable to get what is SQLBuilder utilities.
I am having this error
"The name 'SQLBuilderUtilities' does not exist in the current context "
kindly tell me what to do
regards,
|
|
|
|
|
Sorry, my fault. 
In my App (called SQLBuilder) I have a separate class (Utilities), which contains the ReaderToTable method. Just delete the SQLBuilderUtilities bit and all should be well.
I have modified my post with the code in, by striking out the problem bits. Please note that this includes the static access modifier from the ReaderToTable method.
If you just put that in your class all should now be well.
Henry Minute
Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”
|
|
|
|
|
inside the databases nodes only table is coming and the more big problem is all the databases are loading master tables.... i am sending you my code, i am calling it on the strip menu so i will u the code from i am calling aswell ,i am really sorry but i am unable to figure out..
private void sQLServer2005ToolStripMenuItem_Click(object sender, EventArgs e)
{
parentNode = new TreeNode();
DBTree.Nodes[0].Text = Show_SqlConnection();
parentNode.Text = "Databases";
DBTree.Nodes[0].Nodes.Add(parentNode);
LoadSQL_DB(parentNode);
}
public void LoadSQL_DB(TreeNode DB)
{
DataTable table = new DataTable();
SqlConnection sqlConnection1 = new SqlConnection();
TreeNode treeNode;
TreeNode tablesNode;
int count = 0;
SqlCon = new SqlConnection("Data Source= Localhost; Integrated security=True;database='' ");
SqlCon.Open();
SqlCom = new SqlCommand();
SqlCom.Connection = SqlCon;
SqlCom.CommandType = CommandType.StoredProcedure;
SqlCom.CommandText = "sp_databases";
SqlCom.Connection = SqlCon;
SqlDR = SqlCom.ExecuteReader();
table = ReaderToTable(SqlDR);
foreach (DataRow row in table.Rows)
{
treeNode = new TreeNode();
treeNode.Text = row["DATABASE_NAME"].ToString();
DB.Nodes.Add(treeNode);
DB.Nodes[DB.Nodes.IndexOf(treeNode)].Nodes.Add("Tables");
tablesNode = new TreeNode();
LoadSQL_Tables(treeNode);
DB.Nodes[DB.Nodes.IndexOf(treeNode)].Nodes.Add("Stored Procedure");
DB.Nodes[DB.Nodes.IndexOf(treeNode)].Nodes.Add("Views");
DB.Nodes[DB.Nodes.IndexOf(treeNode)].Nodes.Add("Roles");
count++;
}
SqlDR.Close();
SqlCon.Close();
public void LoadSQL_Tables(TreeNode arg_dbNode)
{
TreeNode table_node;
SqlCon = new SqlConnection("Data Source= Localhost; Integrated security=True;database=master");
SqlCon.Open();
SqlCom = new SqlCommand();
SqlCom.Connection = SqlCon;
SqlCom.CommandType = CommandType.Text;
SqlCom.CommandText = "select * from sysobjects where type = 's' order by name";
SqlDR = SqlCom.ExecuteReader();
while (SqlDR.Read())
{
table_node = new TreeNode();
table_node.Text = SqlDR.GetString(0);
arg_dbNode.Nodes.Add(table_node);
}
}
regards,
mariam
|
|
|
|
|
and this is my DataTable ReaderToTable fuction
public DataTable ReaderToTable(SqlDataReader reader)
{
DataTable newTable = new DataTable();
DataColumn col;
DataRow row;
int i;
for (i = 0; i < reader.FieldCount; i++)
{
col = new DataColumn();
col.ColumnName = reader.GetName(i);
col.DataType = reader.GetFieldType(i);
newTable.Columns.Add(col);
}
while (reader.Read())
{
row = newTable.NewRow();
for (i = 0; i < reader.FieldCount; i++)
{
row[i] = reader[i];
}
newTable.Rows.Add(row);
}
return newTable;
}
|
|
|
|
|
Thanks for the code.
I'm going to set up a project to run it in. So far I have only been reading it.
This may take a little time, so be patient.
I'll get back to you later.
Henry Minute
Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”
|
|
|
|
|
When I started trying to help you, I just tried to get your code working for you. Trying out your code has made me realize that part of what I was telling you to do would make your code run very slowly. Filling the TreeView with all of the data in one go would take a very long time.
So I am suggesting that you just get the names of the databases and add a node for each. Each of these database nodes has nodes for Tables, Views etc. But do not fetch the data for these until the user tries to expand them. So that the expand icon shows up for these nodes, they each have an empty node added. This is removed later when it is not needed.
This code works correctly on my system. The only difference between my system and yours is the name of the server. I have tried to change this code so that it will work on your system exactly as it is. Try to use it as it is first, before you alter the layout etc. to suit your style of working.
private TreeNode parentNode;
private readonly string connString = "server={0};Integrated Security=true;database={1}";
private void sQLServer2005ToolStripMenuItem_Click(object sender, EventArgs e)
{
parentNode = new TreeNode();
DBTree.Nodes.Add(Show_SqlConnection());
parentNode.Text = "Databases";
DBTree.Nodes[0].Nodes.Add(parentNode);
LoadSQL_DB(parentNode);
}
public void LoadSQL_DB(TreeNode DB)
{
DataTable table;
TreeNode treeNode;
TreeNode tempNode;
int count = 0;
using (SqlConnection SqlCon = GetConnection(@"Localhost", ""))
{
SqlCommand SqlCom = new SqlCommand();
SqlCom.Connection = SqlCon;
SqlCom.CommandType = CommandType.StoredProcedure;
SqlCom.CommandText = "sp_databases";
SqlCon.Open();
SqlDataReader SqlDR = SqlCom.ExecuteReader();
table = ReaderToTable(SqlDR);
}
foreach (DataRow row in table.Rows)
{
treeNode = new TreeNode();
treeNode.Text = row["DATABASE_NAME"].ToString();
tempNode = treeNode.Nodes.Add("Tables");
tempNode.Nodes.Add("");
tempNode = treeNode.Nodes.Add("Stored Procedure");
tempNode.Nodes.Add("");
tempNode = treeNode.Nodes.Add("Views");
tempNode.Nodes.Add("");
tempNode = treeNode.Nodes.Add("Roles");
tempNode.Nodes.Add("");
count++;
DB.Nodes.Add(treeNode);
}
}
public void LoadSQL_Tables(TreeNode arg_dbNode, string database)
{
DataTable table;
using (SqlConnection SqlCon = GetConnection("Localhost", database))
{
SqlCommand tablesCommand = new SqlCommand();
tablesCommand.CommandType = CommandType.StoredProcedure;
tablesCommand.CommandText = "sp_tables";
tablesCommand.Parameters.Add("@table_qualifier", SqlDbType.NVarChar, 384).Value = database;
string paramString = "'TABLE'";
tablesCommand.Parameters.Add("@table_type", SqlDbType.VarChar, 32).Value = paramString;
tablesCommand.Connection = SqlCon;
SqlCon.Open();
SqlDataReader SqlDR = tablesCommand.ExecuteReader();
table = ReaderToTable(SqlDR);
}
foreach (DataRow row in table.Rows)
{
arg_dbNode.Nodes.Add(row["TABLE_NAME"].ToString());
}
arg_dbNode.Nodes.RemoveAt(0);
}
private SqlConnection GetConnection(string server, string database)
{
return new SqlConnection(string.Format(this.connString, server, database));
}
public DataTable ReaderToTable(SqlDataReader reader)
{
DataTable newTable = new DataTable();
DataColumn col;
DataRow row;
int i;
for (i = 0; i < reader.FieldCount; i++)
{
col = new DataColumn();
col.ColumnName = reader.GetName(i);
col.DataType = reader.GetFieldType(i);
newTable.Columns.Add(col);
}
while (reader.Read())
{
row = newTable.NewRow();
for (i = 0; i < reader.FieldCount; i++)
{
row[i] = reader[i];
}
newTable.Rows.Add(row);
}
return newTable;
}
private void DBTree_BeforeExpand(object sender, TreeViewCancelEventArgs e)
{
if ((e.Node.Nodes.Count == 1) && (e.Node.Nodes[0].Text == ""))
{
switch (e.Node.Text)
{
case "Tables":
LoadSQL_Tables(e.Node, e.Node.Parent.Text);
break;
}
}
}
Good Luck! 
Henry Minute
Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”
|
|
|
|
|
Hi
Thank you so much i'll check it and then will tell you, but really thanks alot
regards,
mariam
|
|
|
|
|
public void LoadSQL_DB(TreeNode DB)
{
DataTable table = new DataTable();
SqlConnection sqlConnection1 = new SqlConnection();
TreeNode treeNode;
int count = 0;
SqlCon = new SqlConnection("Data Source= Localhost; Integrated security=True;database=master ");
SqlCon.Open();
SqlCom = new SqlCommand();
SqlCom.Connection = SqlCon;
SqlCom.CommandType = CommandType.StoredProcedure;
SqlCom.CommandText = "sp_databases";
SqlCom.Connection = SqlCon;
SqlDR = SqlCom.ExecuteReader();
table = ReaderToTable(SqlDR);
foreach (DataRow row in table.Rows)
{
treeNode = new TreeNode();
treeNode.Text = row["DATABASE_NAME"].ToString();
DB.Nodes.Add(treeNode);
DB.Nodes[DB.Nodes.IndexOf(treeNode)].Nodes.Add("Tables");
DB.Nodes[DB.Nodes.IndexOf(treeNode)].Nodes.Add("Stored Procedure");
DB.Nodes[DB.Nodes.IndexOf(treeNode)].Nodes.Add("Views");
DB.Nodes[DB.Nodes.IndexOf(treeNode)].Nodes.Add("Roles");
if (DB.Nodes[DB.Nodes.IndexOf(treeNode)].Nodes[count].Text == "Tables")
{
LoadSQL_Tables(DB.Nodes[DB.Nodes.IndexOf(treeNode)].Nodes[count], treeNode.Text);
if (DB.Nodes[DB.Nodes.IndexOf(treeNode)].Nodes[count+1].Text == "Stored Procedure")
LoadSQL_StoedProcedures(DB.Nodes[DB.Nodes.IndexOf(treeNode)].Nodes[count + 1], treeNode.Text);
if (DB.Nodes[DB.Nodes.IndexOf(treeNode)].Nodes[count + 2].Text == "Views")
LoadSQL_Views(DB.Nodes[DB.Nodes.IndexOf(treeNode)].Nodes[count + 2], treeNode.Text);
}
}
SqlDR.Close();
SqlCon.Close();
}
thanks for the help
regards,
|
|
|
|
|
|
ok thanks again
regards,
mariam
|
|
|
|
|
Hi All,
i want to create the moving chart in windows application like it is displaying the current data getting from any hardware device,how will it be possible.
Please help
Thanks and Regards,
Lalit Narayan
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
