need some help with sql insert

[PushCode]

I have console application that queries a sharepoint list. I need to then do a sql insert of those query results into a separate sql table. The sharepoint part is taken care of, now I just need to do the sql insert, but I can't get a grasp on how to make that happen. I assume I need to construct the insert in the foreach loop, but that's about as far as I can get. I need to know how to establish the db connection, construct the query and execute it.

Here's what I have for getting the sharepoint list data:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SharePoint;
using System.Data.Odbc;


namespace GPSNotifications
{
    class Program
    {
        static void Main(string[] args)
        {
            using (SPSite curSite = new SPSite("[URL unfurl="true"]http://zanett-spdev-rb"))[/URL] //[URL unfurl="true"]http://gps.technicolor.com[/URL]
            {
                using (SPWeb curWeb = curSite.OpenWeb()) 
                { 
                    //create our query
                    SPQuery curQry = new SPQuery();

                    //configure the query
                    curQry.Query= @"
                        <Query>
                          <Where>
                            <Gt>
                              <FieldRef Name='Created'/>
                              <Value Type='DateTime'>DateTime.Now.AddMinutes(-15);</Value> 
                            </Gt>
                          </Where>
                        </Query>
                        ";

                    //get our list
                    SPList curList = curWeb.Lists["Notifications"];

                    //get the collection of items in the list
                    SPListItemCollection curItems = curList.GetItems(curQry);

                    //enumerate the items in the list
                    foreach (SPListItem curItem in curItems);
	                
                    {
                        // do sql insert
                        
                    }
                    Console.WriteLine();
                }
                curSite.RootWeb.Dispose(); 
            } 
        }
    }
}

Any help would be greatly appreciated!

 

[PushCode]

I'm trying here, hoping someone will chime in. I've added some sql. How am I doing?

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SharePoint;
using System.Data.Odbc;
using System.Data.Sql;


namespace GPSNotifications
{
    class Program
    {
        static void Main(string[] args)
        {
            CreationDate = DateTime.Now;
            
            SqlConnection mySqlConnection =new SqlConnection("server=indyaecsadb01t.extranet.tce.com;database=GPSItemArchive;user id=gpsuser;password=pass@word1");

            SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
            string sql = "INSERT INTO ImportNotifications";
                sql += " (SharepointID,Title,WorkOrderNumber,Status,Department,ClientName,WorkOrderDueDate,CreatedDate,CreatedBy, ModifiedDate, ModifiedBy, Late, LateReview, ArchiveDate, TrueLate, Body, Expires, SerialNumber, ArchiveItemModifiedDate)";
                sql += " VALUES (";

                sql += SharepointID + ",";
                sql += "'" + Title + "',";
                sql += "'" + WorkOrderNumber + "',";
                sql += "'" + Status + "',";
                sql += "'" + Department + "',";
                sql += "'" + ClientName + "',";
                sql += "'" + WorkOrderDueDate + "',";
                sql += "'" + CreatedDate + "',";
                sql += "'" + CreatedBy + "',";
                sql += "'" + ModifiedDate + "',";
                sql += "'" + ModifiedBy + "',";
                sql += "'" + Late + "',";
                sql += "'" + LateReview + "',";
                sql += "'" + ArchiveDate + "',";
                sql += "'" + TrueLate + "',";
                sql += "'" + Body + "',";
                sql += "'" + Expires + "',";
                sql += "'" + SerialNumber + "',";
                sql += CreationDate;
                sql += ")";
            mySqlCommand.CommandText = sql;

            mySqlConnection.Open();

            using (SPSite curSite = new SPSite("[URL unfurl="true"]http://zanett-spdev-rb"))[/URL] //[URL unfurl="true"]http://gps.technicolor.com[/URL]
            {
                using (SPWeb curWeb = curSite.OpenWeb()) 
                { 
                    //create our query
                    SPQuery curQry = new SPQuery();

                    //configure the query
                    curQry.Query= @"
                        <Query>
                          <Where>
                            <Gt>
                              <FieldRef Name='Created'/>
                              <Value Type='DateTime'>DateTime.Now.AddMinutes(-15);</Value> 
                            </Gt>
                          </Where>
                        </Query>
                        ";

                    //get our list
                    SPList curList = curWeb.Lists["Notifications"];

                    //get the collection of items in the list
                    SPListItemCollection curItems = curList.GetItems(curQry);

                    //enumerate the items in the list
                    foreach (SPListItem curItem in curItems);
	                
                    {
                        // execute insert
                        mySqlCommand.ExecuteNonQuery();
                    }
                    Console.WriteLine();
                }
                curSite.RootWeb.Dispose(); 
            } 
        }
    }
}

 

[dinger2121]

you will have to put all of your sql connection and query strings in the SPListItem loop so that the values get assigned.

try this -

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SharePoint;
using System.Data.Odbc;
using System.Data.Sql;


namespace GPSNotifications
{
    class Program
    {
        static void Main(string[] args)
        {
            CreationDate = DateTime.Now;
            
            SqlConnection mySqlConnection =new SqlConnection("server=indyaecsadb01t.extranet.tce.com;database=GPSItemArchive;user id=gpsuser;password=pass@word1");

            SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
            

            using (SPSite curSite = new SPSite("[URL unfurl="true"]http://zanett-spdev-rb"))[/URL] //[URL unfurl="true"]http://gps.technicolor.com[/URL]
            {
                using (SPWeb curWeb = curSite.OpenWeb())
                {
                    //create our query
                    SPQuery curQry = new SPQuery();

                    //configure the query
                    curQry.Query= @"
                        <Query>
                          <Where>
                            <Gt>
                              <FieldRef Name='Created'/>
                              <Value Type='DateTime'>DateTime.Now.AddMinutes(-15);</Value>
                            </Gt>
                          </Where>
                        </Query>
                        ";

                    //get our list
                    SPList curList = curWeb.Lists["Notifications"];

                    //get the collection of items in the list
                    SPListItemCollection curItems = curList.GetItems(curQry);

                    //enumerate the items in the list
                    foreach (SPListItem curItem in curItems);
                    
                    {
string sql = "INSERT INTO ImportNotifications";
                sql += " (SharepointID,Title,WorkOrderNumber,Status,Department,ClientName,WorkOrderDueDate,CreatedDate,CreatedBy, ModifiedDate, ModifiedBy, Late, LateReview, ArchiveDate, TrueLate, Body, Expires, SerialNumber, ArchiveItemModifiedDate)";
                sql += " VALUES (";

                sql += SharepointID + ",";
                sql += "'" + Title + "',";
                sql += "'" + WorkOrderNumber + "',";
                sql += "'" + Status + "',";
                sql += "'" + Department + "',";
                sql += "'" + ClientName + "',";
                sql += "'" + WorkOrderDueDate + "',";
                sql += "'" + CreatedDate + "',";
                sql += "'" + CreatedBy + "',";
                sql += "'" + ModifiedDate + "',";
                sql += "'" + ModifiedBy + "',";
                sql += "'" + Late + "',";
                sql += "'" + LateReview + "',";
                sql += "'" + ArchiveDate + "',";
                sql += "'" + TrueLate + "',";
                sql += "'" + Body + "',";
                sql += "'" + Expires + "',";
                sql += "'" + SerialNumber + "',";
                sql += CreationDate;
                sql += ")";
            mySqlCommand.CommandText = sql;

            mySqlConnection.Open();
                        // execute insert
                        mySqlCommand.ExecuteNonQuery();
                    }
                    Console.WriteLine();
                }
                curSite.RootWeb.Dispose();
            }
        }
    }
}

carl
MCSD, MCTS:MOSS

 

[PushCode]

Thanks Carl! I've taken your suggestion and come up with a solution that is working! However, I can only get it to work when inserting string (varchar) fields. If I add a datetime field it throws an error. I'm not handling the insert of this date properly and I can't seem to figure it out.

The error I get is 'Value does not fall within expected range'.

The date field that's causing the error is WorkOrderDueDate.

Here's the code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SharePoint;
using Microsoft.SharePoint.Utilities;
using System.Data.Odbc;
using System.Data.SqlClient;


namespace GPSNotifications
{
    class Program
    {

        static void Main(string[] args)
        {

            String cs = @"Data Source=db01.extranet.website.com;Initial Catalog=ItemArchive;user id=username;password=thepw";

            try
            {
                SqlConnection mySqlConnection =new SqlConnection(cs);
                SqlCommand mySqlCommand = mySqlConnection.CreateCommand();

                using (SPSite curSite = new SPSite("[URL unfurl="true"]http://zanett-spdev-rb"))[/URL] //[URL unfurl="true"]http://gps.technicolor.com[/URL]
                {
                    using (SPWeb curWeb = curSite.OpenWeb())
                    {
                        // set time variable
                        DateTime CreationDate = DateTime.Now;
                        DateTime CustomDate = DateTime.Now.AddMinutes(-120);
                        string strCustomDate = SPUtility.CreateISO8601DateTimeFromSystemDateTime(CustomDate);

                        //create our query
                        SPQuery curQry = new SPQuery();

                        //configure the query  ////<Value Type='DateTime'>DateTime.Now.AddMinutes(-15)</Value>
                        curQry.Query = "<Where><Gt><FieldRef Name='Created' /><Value Type='DateTime' IncludeTimeValue='True'>"+strCustomDate+"</Value></Gt></Where>";

                        //get the list
                        SPList curList = curWeb.Lists["Notifications"];

                        //get the collection of items in the list
                        SPListItemCollection curItems = curList.GetItems(curQry);
                        // open connection
                        mySqlConnection.Open();
                        //enumerate the items in the list
                        foreach (SPListItem curItem in curItems)
                        {
                            string sql = "INSERT INTO ImportNotifications";
                            sql += " (Title,WorkOrderNumber,ClientName,WorkOrderDueDate,Department,CreatedBy,ModifiedBy)";
                            sql += " VALUES (";
                            sql += "'" + curItem["Title"].ToString() + "',";
                            
                            sql += "'" + curItem["Work_x0020_Order_x0020_Number"].ToString() + "',";
                            sql += "'" + curItem["Client_x0020_Name"].ToString() + "',";
                            sql += "'" + DateTime.Parse(curItem["Work_x0020_Order_x0020_Due_x0020_Date"].ToString()).Date + "',";
    sql += "'" + curItem["Department"].ToString() + "',";
                            sql += "'" + curItem["Author"].ToString() + "',";
                            sql += "'" + curItem["Editor"].ToString() + "'";
                            sql += ")";
                            
                            mySqlCommand.CommandText = sql;
                            
                            // execute insert
                            mySqlCommand.ExecuteNonQuery();
                            // write inserted titles to screen
                            Console.Write(curItem["Title"].ToString());    

                        }
                        mySqlConnection.Close();
                    }
                    curSite.RootWeb.Dispose();
                }
            }

            catch (Exception ex)
            {

                Console.WriteLine(ex);

            }

        }
    }
}

Any help from anyone is greatly appreciated!

 

[dinger2121]

try changing the line

sql += "'" + DateTime.Parse(curItem["Work_x0020_Order_x0020_Due_x0020_Date"].ToString()).Date + "',";

to

sql += DateTime.Parse(curItem["Work_x0020_Order_x0020_Due_x0020_Date"] + ",";

carl
MCSD, MCTS:MOSS

 

[PushCode]

Thanks for your help, I've got it working!