CODEWITHSUNDEEP
c#datatable
Arianule
Arianule

Reputation: 9053

Values not added to thye new column of dataset

I added a column to my datatable and add values to the column based on a condition but the value is not added to the datatable(even though there should be values)

This is part of the code I used

conn = new OleDbConnection(@"Provider=Microsoft.Jet.OleDb.4.0;
                                Data Source =" + Server.MapPath("App_Data\\LR Product Database 2000.mdb"));
        conn.Open();

        Dictionary<string, string> items = new Dictionary<string, string>();
        OleDbCommand cmd = conn.CreateCommand();
        cmd.CommandText = "SELECT CODE, TITLE FROM tblProducts";

        OleDbDataReader dbread = cmd.ExecuteReader();

        while (dbread.Read())
        {
            productCode = (string)dbread["CODE"];
            productTitle = (string)dbread["TITLE"];
            System.Diagnostics.Debug.Write(productCode + " ");
            items.Add(productCode, productTitle);
        }

        sqlCon = new SqlConnection(ConfigurationManager.ConnectionStrings["LRVWebsite"].ToString());
        sqlCon.Open();
        dsSql = new DataSet();
        SqlDataAdapter dba = new SqlDataAdapter(@"SELECT C.CustomerFirstName,C.CustomerLastName, C.CustomerCompany,C.CustomerPosition,C.CustomerCountry,C.CustomerProvince,C.CustomerContact,CP.ActionDate,CP.ProductCode,CP.CustomerEmail FROM tblCustomers C INNER JOIN tblCustomerProducts CP ON C.CustomerEmail = CP.CustomerEmail ORDER BY ActionDate DESC", connString);
        dba.Fill(dsSql,"Products");
        DataTable dt = dsSql.Tables["Products"];
        dt.Columns.Add("Title", typeof(string));

        foreach (DataRow dr in dt.Rows)
        {
            if(items.ContainsKey(dr["ProductCode"].ToString()))
            {

                dr["Title"] = items[dr["ProductCode"].ToString()];

            }
        }

How can I add the values to the new column based on the condition.

Upvotes: 0

Views: 103

Answers (2)

Tim Schmelter
Tim Schmelter

Reputation: 460208

You can shorten the first part (+ use using) and use Enumerable.Join:

DataTable tblAccess = new DataTable();
using(var con = new OleDbConnection(@"Provider=Microsoft.Jet.OleDb.4.0;Data Source =" + Server.MapPath("App_Data\\LR Product Database 2000.mdb"))
using(var da = new OleDbDataAdapter("SELECT CODE, TITLE FROM tblProducts", con))
{ 
    da.Fill(tblAccess);
}

DataTable tblSqlServer = new DataTable();
using(var con = new SqlConnection(ConfigurationManager.ConnectionStrings["LRVWebsite"].ToString()))
using(var da = new SqlDataAdapter("SELECT C.CustomerFirstName,C.CustomerLastName, C.CustomerCompany,C.CustomerPosition,C.CustomerCountry,C.CustomerProvince,C.CustomerContact,CP.ActionDate,CP.ProductCode,CP.CustomerEmail FROM tblCustomers C INNER JOIN tblCustomerProducts CP ON C.CustomerEmail = CP.CustomerEmail ORDER BY ActionDate DESC", con))
{ 
    da.Fill(tblSqlServer);
}

Now it's easy to join both tables with Linq-To-DataSet and get the new title for your sql-server row:

var both = from rowSql in tblSqlServer.AsEnumerable()
           join rowAcc in tblAccess.AsEnumerable()
           on rowSql.Field<string>("ProductCode") equals rowAcc.Field<string>("ProductCode")
           select new { rowSql = rowSql, newTitle = rowAcc.Field<string>("Title") };

foreach (var x in both)
{
    x.rowSql.SetField("Title", x.newTitle);
}

Upvotes: 1

Hogan
Hogan

Reputation: 70529

You could do something like this, create an empty column in the select '' AS Title and then fill it. You could also put your logic in the sql (join to an items table.):

    SqlDataAdapter dba = new SqlDataAdapter(@"SELECT '' as Title, C.CustomerFirstName,C.CustomerLastName, C.CustomerCompany,C.CustomerPosition,C.CustomerCountry,C.CustomerProvince,C.CustomerContact,CP.ActionDate,CP.ProductCode,CP.CustomerEmail FROM tblCustomers C INNER JOIN tblCustomerProducts CP ON C.CustomerEmail = CP.CustomerEmail ORDER BY ActionDate DESC", connString);
    dba.Fill(dsSql,"Products");
    DataTable dt = dsSql.Tables["Products"];

    foreach (DataRow dr in dt.Rows)
    {
        if(items.ContainsKey(dr["ProductCode"].ToString()))
        {

            dr["Title"] = items[dr["ProductCode"].ToString()];

        }
    }

Upvotes: 1

Related Questions