Shaan's Official Blog

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 http://<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:              }