Downloading data from SharePoint List to Database

Here is a quick and dirty way of getting data from SharePoint List to database.

Add a Web Service Reference to https://<your site address>/_vti_bin/lists.asmx

Lists.asmx is part of SharePoint web services group, which lets you access list information over the Http remotely.

    1:              SharePoint.Lists list = new SharePoint.Lists();

   2:              list.Credentials = new NetworkCredential("<username>", "<password>", "<domain>");

3:

   4:              XmlNode anode= list.GetListItems("<List Name>", null, null, null, null, null, null);

   5:              XmlReader reader = new System.Xml.XmlNodeReader(anode);

6:

   7:              DataSet ds = new DataSet();

8:

   9:              ds.ReadXml(reader);

  10:              DataTable tblData = ds.Tables["row"];

11:

  12:              using (SqlConnection con = new SqlConnection("SERVER=Server2;Database=TestDB;Integrated Security=SSPI"))

  13:              {

  14:                  String sql = "INSERT INTO [List_temp] VALUES (@Title, @FirstName, @LastName, @DOB)";

15:

  16:                  SqlCommand cmd = new SqlCommand(sql, con);

  17:                  cmd.Parameters.Add("@Title", SqlDbType.VarChar, 20);

  18:                  cmd.Parameters.Add("@FirstName", SqlDbType.VarChar, 20);

  19:                  cmd.Parameters.Add("@LastName", SqlDbType.VarChar, 20);

  20:                  cmd.Parameters.Add("@DOB", SqlDbType.DateTime);

21:

  22:                  con.Open();

  23:                  cmd.Prepare();

24:

  25:                  foreach (DataRow row in tblData.Rows)

  26:                  {

  27:                      cmd.Parameters["@Title"].Value = row["ows_LinkTitle"];

  28:                      cmd.Parameters["@FirstName"].Value = row["ows_FirstName"];

  29:                      cmd.Parameters["@LastName"].Value = row["ows_LastName"];

  30:                      cmd.Parameters["@DOB"].Value = row["ows_DOB"];

  31:                      cmd.ExecuteNonQuery();

  32:                  }

33:

  34:                  con.Close();

  35:              }

Additional resources