access list in sharepoint 2007 using c#

Question

I'm looking to compile data from a few diffrent custome lists in sharepoint 2007

Its a hosted sharepoint site so I don't have access to the machine backend.

Is there any example code to access the sharepoint site using c#?

here is my code thus far (i get the error Cannot connect to the Sharepoint site ''. Try again later.)

    DataSet dt = new DataSet();
    string query = "SELECT * FROM list";
    string site = "http://sp.markonsolutions.com/Lists/Security/";
    string list = "35E70EO4-6072-4T55-B741-4B75D5F3E397"; //security db
    string myConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=2;RetrieveIds=Yes; DATABASE="+site+";LIST={"+list+"};";
    OleDbConnection myConnection = new OleDbConnection();
    myConnection.ConnectionString = myConnectionString;
    OleDbCommand myAccessCommand = new OleDbCommand(query,myConnection);
    OleDbDataAdapter myDataAdapter = new OleDbDataAdapter(myAccessCommand);
    myConnection.Open();



          myDataAdapter.Fill(dt);

    //execute queries, etc
    myConnection.Close();

Answer 1

If you can't deploy code on the SharePoint machine, then you pretty much have to use the web services.

The lists web service is what you're after.

It will be located on http://yousharepointsite.com/_vti_bin/Lists.asmx and should be open by default. Note that if your site is configured with FBA, you will have to use the _vti_bin/Authentication.asmx to log in before you query lists.asmx.

Here is an article that gives all the information you need :

http://sharepointmagazine.net/articles/writing-caml-queries-for-retrieving-list-items-from-a-sharepoint-list

For the reasons mentioned above, skip the part on using the object model to query SharePoint lists and go directly to Retrieving list items with CAML using the SharePoint web services.

The article is pretty complete, so I think you should be OK with that.

As per your edit, I don't think that you can create a connection to your remote site like that. You can't query SharePoint with SQL like that, you really need to use CAML...

Once you've added the reference to the web service :

ListService listsClient = new ListService.Lists();
listsClient.Url = @"http://sp.markonsolutions.com/" + @"/_vti_bin/lists.asmx";
listsClient.Credentials = System.Net.CredentialCache.DefaultCredentials;
listsClient.GetListItems(...);

Read more on the GetListItems here

Like I said, you need to use the web services. You are heading towards a dead end if you are trying to create connection like that to query the database directly. It is not recommended.

Answer 2

Not sure if what you try to do is possible unless you use an ado.net connector for SharePoint, have a look at http://www.bendsoft.com/net-sharepoint-connector/

It enables you to talk to SharePoint lists as if they where ordinary sql-tables

In example to insert some data

public void SharePointConnectionExample1()
{
    using (SharePointConnection connection = new SharePointConnection(@"
                Server=mysharepointserver.com;
                Database=mysite/subsite
                User=spuser;
                Password=******;
                Authentication=Ntlm;
                TimeOut=10;
                StrictMode=True;
                RecursiveMode=RecursiveAll;
                DefaultLimit=1000;
                CacheTimeout=5"))
    {
        connection.Open();
        using (SharePointCommand command = new SharePointCommand("UPDATE `mytable` SET `mycolumn` = 'hello world'", connection))
        {
            command.ExecuteNonQuery();
        }
    }
}

Or to select list data to a DataTable

string query = "SELECT * FROM list";
conn = new SharePointConnection(connectionString);
SharePointDataAdapter adapter = new SharePointDataAdapter(query, conn);

DataTable dt = new DataTable();
adapter.Fill(dt);

Or using a helper method to fill a DataGrid

string query = "Select * from mylist.viewname";
DataGrid dataGrid = new DataGrid();
dataGrid.DataSource = Camelot.SharePointConnector.Data.Helper.ExecuteDataTable(query, connectionString);
dataGrid.DataBind();
Controls.Add(dataGrid);