How to update database after passing DataTable as parameter?

Topics: Data Access Application Block
Feb 14, 2012 at 2:07 AM

http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataset/thread/4cab587b-ac1d-4e6a-b520-ad9ee2f14e86

my question is in above link.

Feb 14, 2012 at 2:57 AM

You need to set the connection for your DbCommand.  Enterprise Library doesn't set the connection since usually you would pass the command in to another Enterprise Library method that would then manage the connection internally.

    public void updateTable(DataTable table, string table_name)
    {
        try
        {
            String queryString = "Select * from [" + table_name + "]";
            if (!IsSelect(queryString))
                return;
            DbDataAdapter da = db.GetDataAdapter();
            var cmd = db.GetSqlStringCommand(queryString);
            cmd.Connection = db.CreateConnection();
            da.SelectCommand = cmd;
            DataSet ds = new DataSet();
            da.Fill(ds, table_name);
            da.Update(ds, table_name);
        }
        catch (Exception e)
        {
            Console.WriteLine("Error: " + e);
        }
    }

--
Randy Levy
Enterprise Library support engineer
entlib.support@live.com 

Feb 14, 2012 at 5:31 AM
Hi ,

it said need insert command when add row, How to insert correctly?

try
{
String queryString = "Select * from [" + table_name + "]";
if (!IsSelect(queryString))
return;
DbDataAdapter da = db.GetDataAdapter();
var cmd = db.GetSqlStringCommand(queryString);
cmd.Connection = db.CreateConnection();
da.SelectCommand = cmd;
DataSet ds = new DataSet();
da.Fill(ds, table_name);
ds.Tables[table_name].Rows.Add(r.ItemArray);
da.Update(ds, table_name);
}
catch (Exception e)
{
Console.WriteLine("Error: " + e);
}
Regards,
Martin



From: [email removed]
To: [email removed]
Date: Mon, 13 Feb 2012 19:57:41 -0800
Subject: Re: How to update database after passing DataTable as parameter? [entlib:311345]

From: randylevy
You need to set the connection for your DbCommand. Enterprise Library doesn't set the connection since usually you would pass the command in to another Enterprise Library method that would then manage the connection internally.
    public void updateTable(DataTable table, string table_name)
    {
        try
        {
            String queryString = "Select * from [" + table_name + "]";
            if (!IsSelect(queryString))
                return;
            DbDataAdapter da = db.GetDataAdapter();
            var cmd = db.GetSqlStringCommand(queryString);
            cmd.Connection = db.CreateConnection();
            da.SelectCommand = cmd;
            DataSet ds = new DataSet();
            da.Fill(ds, table_name);
            da.Update(ds, table_name);
        }
        catch (Exception e)
        {
            Console.WriteLine("Error: " + e);
        }
    }
--
Randy Levy
Enterprise Library support engineer
entlib.support@live.com
Read the full discussion online.
To add a post to this discussion, reply to this email (entlib@discussions.codeplex.com)
To start a new discussion for this project, email entlib@discussions.codeplex.com
You are receiving this email because you subscribed to this discussion on CodePlex. You can unsubscribe on CodePlex.com.
Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at CodePlex.com
Feb 14, 2012 at 5:50 AM

You can use DbCommandBuilder to generate the SQL based on the select query:

        public void updateTable(DataRow r, string table_name)
        {
            try
            {
                String queryString = "Select * from [" + table_name + "]";
                if (!IsSelect(queryString))
                    return;
                
                var cmd = db.GetSqlStringCommand(queryString);
                cmd.Connection = db.CreateConnection();
                DbDataAdapter da = db.GetDataAdapter();
                da.SelectCommand = cmd;
                var cmdBuilder = db.DbProviderFactory.CreateCommandBuilder();
                cmdBuilder.DataAdapter = da;

                DataSet ds = new DataSet();
                da.Fill(ds, table_name);
                ds.Tables[table_name].Rows.Add(r.ItemArray);
                da.Update(ds, table_name);
            }
            catch (Exception e)
            {
                Console.WriteLine("Error: " + e);
            }
        }

--
Randy Levy
Enterprise Library support engineer
entlib.support@live.com 

Feb 14, 2012 at 6:09 AM
Extremely thanks, it inserted successfully


From: [email removed]
To: [email removed]
Date: Mon, 13 Feb 2012 22:50:31 -0800
Subject: Re: How to update database after passing DataTable as parameter? [entlib:311345]

From: randylevy
You can use DbCommandBuilder to generate the SQL based on the select query:
        public void updateTable(DataRow r, string table_name)
        {
            try
            {
                String queryString = "Select * from [" + table_name + "]";
                if (!IsSelect(queryString))
                    return;
                
                var cmd = db.GetSqlStringCommand(queryString);
                cmd.Connection = db.CreateConnection();
                DbDataAdapter da = db.GetDataAdapter();
                da.SelectCommand = cmd;
                var cmdBuilder = db.DbProviderFactory.CreateCommandBuilder();
                cmdBuilder.DataAdapter = da;

                DataSet ds = new DataSet();
                da.Fill(ds, table_name);
                ds.Tables[table_name].Rows.Add(r.ItemArray);
                da.Update(ds, table_name);
            }
            catch (Exception e)
            {
                Console.WriteLine("Error: " + e);
            }
        }

--
Randy Levy
Enterprise Library support engineer
entlib.support@live.com


Read the full discussion online.
To add a post to this discussion, reply to this email (entlib@discussions.codeplex.com)
To start a new discussion for this project, email entlib@discussions.codeplex.com
You are receiving this email because you subscribed to this discussion on CodePlex. You can unsubscribe on CodePlex.com.
Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at CodePlex.com
Feb 14, 2012 at 6:48 AM
Hi

why update not update removeat action? it can compile, , in debug, checked count is from 3 to 2. but table do not change in database, number of records is 3

dal.deleteRow(2, "Order", "id");
public void deleteRow(int key, string table_name, string key_name)
{
try
{
String queryString = "Select * from [" + table_name + "]";
if (!IsSelect(queryString))
return;
DbDataAdapter da = db.GetDataAdapter();
var cmd = db.GetSqlStringCommand(queryString);
cmd.Connection = db.CreateConnection();
da.SelectCommand = cmd;
var cmdBuilder = db.DbProviderFactory.CreateCommandBuilder();
cmdBuilder.DataAdapter = da;
DataSet ds = new DataSet();
da.Fill(ds, table_name);
DataColumn[] keyColumns = new DataColumn[1];
keyColumns[0] = ds.Tables[0].Columns[key_name];
ds.Tables[0].PrimaryKey = keyColumns;
ds.Tables[table_name].Rows.RemoveAt(Convert.ToInt32(ds.Tables[table_name].Rows.Find(key).ItemArray[0].ToString())-1);
da.Update(ds, table_name);
}
catch (Exception e)
{
Console.WriteLine("Error: " + e);
}
}
Regards,

Martin


From: [email removed]
To: [email removed]
Date: Mon, 13 Feb 2012 22:50:31 -0800
Subject: Re: How to update database after passing DataTable as parameter? [entlib:311345]

From: randylevy
You can use DbCommandBuilder to generate the SQL based on the select query:
        public void updateTable(DataRow r, string table_name)
        {
            try
            {
                String queryString = "Select * from [" + table_name + "]";
                if (!IsSelect(queryString))
                    return;
                
                var cmd = db.GetSqlStringCommand(queryString);
                cmd.Connection = db.CreateConnection();
                DbDataAdapter da = db.GetDataAdapter();
                da.SelectCommand = cmd;
                var cmdBuilder = db.DbProviderFactory.CreateCommandBuilder();
                cmdBuilder.DataAdapter = da;

                DataSet ds = new DataSet();
                da.Fill(ds, table_name);
                ds.Tables[table_name].Rows.Add(r.ItemArray);
                da.Update(ds, table_name);
            }
            catch (Exception e)
            {
                Console.WriteLine("Error: " + e);
            }
        }

--
Randy Levy
Enterprise Library support engineer
entlib.support@live.com


Read the full discussion online.
To add a post to this discussion, reply to this email (entlib@discussions.codeplex.com)
To start a new discussion for this project, email entlib@discussions.codeplex.com
You are receiving this email because you subscribed to this discussion on CodePlex. You can unsubscribe on CodePlex.com.
Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at CodePlex.com
Feb 14, 2012 at 6:58 AM
Hi

1. da.Update do not update edited row.

2. the goal is to retrieve table into datatable and do operations instead of using database's #temp_table_name, i think it will be slow if large data.
when debugging #temp_table_name in c# need to remove # of temp table and make it create real table in database
draw it into datatable of c# or using execute nonquery code to do database's temp table
which method is common practice?

DataRow newOrderRow = order.NewRow();
newOrderRow["Id"] = 2;
newOrderRow["DetailID"] = 5;
dal.updateRow(2, newOrderRow, "Order", "id");
public void updateRow(int key, DataRow new_row, string table_name, string key_name)
{
try
{
String queryString = "Select * from [" + table_name + "]";
if (!IsSelect(queryString))
return;
DbDataAdapter da = db.GetDataAdapter();
var cmd = db.GetSqlStringCommand(queryString);
cmd.Connection = db.CreateConnection();
da.SelectCommand = cmd;
var cmdBuilder = db.DbProviderFactory.CreateCommandBuilder();
cmdBuilder.DataAdapter = da;
DataSet ds = new DataSet();
da.Fill(ds, table_name);
DataColumn[] keyColumns = new DataColumn[1];
keyColumns[0] = ds.Tables[0].Columns[key_name];
ds.Tables[0].PrimaryKey = keyColumns;
ds.Tables[table_name].Rows[Convert.ToInt32(ds.Tables[table_name].Rows.Find(key).ItemArray[0].ToString())-1].SetParentRow(new_row, null);
da.Update(ds, table_name);
}
catch (Exception e)
{
Console.WriteLine("Error: " + e);
}
}

Regards,
Martin


From: [email removed]
To: [email removed]
Date: Mon, 13 Feb 2012 22:50:31 -0800
Subject: Re: How to update database after passing DataTable as parameter? [entlib:311345]

From: randylevy
You can use DbCommandBuilder to generate the SQL based on the select query:
        public void updateTable(DataRow r, string table_name)
        {
            try
            {
                String queryString = "Select * from [" + table_name + "]";
                if (!IsSelect(queryString))
                    return;
                
                var cmd = db.GetSqlStringCommand(queryString);
                cmd.Connection = db.CreateConnection();
                DbDataAdapter da = db.GetDataAdapter();
                da.SelectCommand = cmd;
                var cmdBuilder = db.DbProviderFactory.CreateCommandBuilder();
                cmdBuilder.DataAdapter = da;

                DataSet ds = new DataSet();
                da.Fill(ds, table_name);
                ds.Tables[table_name].Rows.Add(r.ItemArray);
                da.Update(ds, table_name);
            }
            catch (Exception e)
            {
                Console.WriteLine("Error: " + e);
            }
        }

--
Randy Levy
Enterprise Library support engineer
entlib.support@live.com


Read the full discussion online.
To add a post to this discussion, reply to this email (entlib@discussions.codeplex.com)
To start a new discussion for this project, email entlib@discussions.codeplex.com
You are receiving this email because you subscribed to this discussion on CodePlex. You can unsubscribe on CodePlex.com.
Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at CodePlex.com
Feb 17, 2012 at 2:32 PM

If you are concerned about performance with large amounts of data then I would recommend doing some testing and measuring the results.  Then you will know the answer for your specific scenario.

In general, I would think that keeping the data in the database (via a temp table or Table variable) would probably be better performance since you wouldn't have to pass large amounts of data over the network but that could depend on the relative loads on the database and application servers and what operations are being performed on the data.

--
Randy Levy
Enterprise Library support engineer
entlib.support@live.com

Feb 18, 2012 at 12:10 AM
Hi randylevy,

i get it, any collection or object in programming are for small amount of data. It seems that the whole logic should be put in database except UI and network.

Regards,
Martin


From: [email removed]
To: [email removed]
Date: Fri, 17 Feb 2012 07:32:31 -0800
Subject: Re: How to update database after passing DataTable as parameter? [entlib:311345]

From: randylevy
If you are concerned about performance with large amounts of data then I would recommend doing some testing and measuring the results. Then you will know the answer for your specific scenario.
In general, I would think that keeping the data in the database (via a temp table or Table variable) would probably be better performance since you wouldn't have to pass large amounts of data over the network but that could depend on the relative loads on the database and application servers and what operations are being performed on the data.
--
Randy Levy
Enterprise Library support engineer
entlib.support@live.com
Read the full discussion online.
To add a post to this discussion, reply to this email (entlib@discussions.codeplex.com)
To start a new discussion for this project, email entlib@discussions.codeplex.com
You are receiving this email because you subscribed to this discussion on CodePlex. You can unsubscribe on CodePlex.com.
Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at CodePlex.com