1

I have a table in SQL with some columns

I stored this SQL column in SharePoint list.

I want to retrieve this column from SharePoint list and store the SQL data value to List1.

Based on that title in that same row the data should be updated.

Here i stored the SQL values in dt and stored the SQL columns in dtc.(from list).Now i want to compare dt and dtc and store the dt value in list1.

Please some one help for this.

  • Is below solution works for you for some extent as i was confused a bit in your question? – Hardik May 30 '16 at 9:33
2

Please try below mentioned code if it helps you.

public override void Execute(Guid contentDbId)
        {

            try
            {
                // OpenSqlConnection();

                using (SPSite site = new SPSite(SPContext.Current.Site))
                {
                    using (SPWeb currWeb = site.OpenWeb())
                    {

                        SqlCommand cmd = new SqlCommand();
                        SPQuery query = new SPQuery();
                        SPList currList = currWeb.Lists.TryGetList("SqlAccess");
                        SPListItemCollection itemColl = currList.GetItems(query);

                        query.ViewFields = string.Concat(
                        "<FieldRef Name='Title'/>",
                        "<FieldRef Name='Server' />",
                        "<FieldRef Name='Database' />",
                        "<FieldRef Name='UserID' />",
                        "<FieldRef Name='Password' />");

                        SPListItemCollection oItemCol = currList.GetItems(query);

                        foreach (SPListItem oItem in oItemCol)
                        {
                            string cs = "Data Source='" + oItem["Server"]
                           + "';Initial Catalog='" + oItem["Database"]
                           + "';User ID='" + oItem["UserID"]
                           + "';Password='" + oItem["Password"] + "'";
                            SqlConnection con = new SqlConnection(cs);
                            cmd.Connection = con;



                            string SqlString = "SELECT * FROM ContractDetails WHERE ContractTitle = ContractTitle;";
                            SqlDataAdapter sda = new SqlDataAdapter(SqlString, con);

                            DataTable dt = new DataTable();
                            con.Open();
                            sda.Fill(dt);
                            SPList list = currWeb.Lists.TryGetList("ContractDetails");

                            foreach(DataRow dr in dt.Rows)
                            {
                                SPQuery query = new SPQuery();
                                query.Query = "<Where><Eq><FieldRef Name='ContractTitle'/><Value Type='Text'>'" + dr["ContractTitle"].ToString() + "'</Value></Eq></Where>";
                                SPListItemCollection Listitems = list.GetItems(query);
                                if(Listitems.Count == 0)
                                {
                                    SPListItem item =list.Items.Add();
                                    item["ContractTitle"]=dr["ContractTitle"].ToString();
                                    item["ContractID"]=dr["ContractID"].ToString();
                                    item["ContractArea"] = dr["ContractArea"].ToString();
                                    item["ModifiedDate"] = DateTime.Now;
                                    item.Update();
                                }
                                else
                                {
                                    foreach (SPListItem item in Listitems)
                                    {
                                        item["ContractID"] = dr["ContractID"].ToString();
                                        item.Update();
                                    }
                                }
                            }

                           con.Close();

                        }
                    }
                }

Please change column names as per your list column names

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.