Pulling Data from SQL Server Table into Sharepoint lists

In order to pull data from SQL Server table into a sharepoint list, there are 2 functionalities one must follow:

1. Use ADO.NET to connect to the SQL Server db
2. Use lists.asmx sharepoint web service.

I am providing sample code that one can use in their project and customize accordingly:

Create a Employee Table with the following fields
FirstName Varchar(50)
LastName Varchar(50)
Address Varchar(100)

Insert records in the SQL table Employee.

Create a Visual Studio 2005 VC# Console application called PullSQLTableToWSSList . Add web reference to http:\\yourservername\_vt_bin\lists.asmx. give the web reference name ListServ.

On the sharepoint site create a list called Employees User Title column as the FirstName, Create columns LastName and Address.

In the PullSQLTableToWSSList - program.cs file paste the following function. Pass the connection string:

private static void SharePointListFromDB(ListServ.Lists Asp, string strcon)
{

string sBatch = string.Empty;
string TotalBatch = string.Empty;
//Write code to form a XML document//
//Send that to update the batch file//
StreamWriter sw1 = new StreamWriter(sErrorLogPath + LogFileName, true);
try
{


sw1.WriteLine("Start with Records from DBI " + ":LogTime:" + DateTime.Now);


odbcon = new SqlConnection(strcon);
odbcon.Open();
//first cleanup the access database//
odcmd = new SqlCommand("Select * from Employee(nolock)", odbcon);
dr = odcmd.ExecuteReader();


//int chkcount = 0;
int intcount = 1;
while (dr.Read())
{

sw1.WriteLine("read started " + intcount + "record " + ":LogTime:" + DateTime.Now);
sBatch = "";
sBatch += "New";
sBatch += "" + dr["FirstName"].ToString() + "";
sBatch += "" + dr["LastName"].ToString() + "";
sBatch += "" + dr["Address"].ToString() + "";
sBatch += "";

intcount++;
TotalBatch = TotalBatch + sBatch;

}

sw1.WriteLine(" Logtime" + DateTime.Now + "," + TotalBatch);

XmlDocument doc = new XmlDocument();
XmlElement batch_element = doc.CreateElement("Batch");
batch_element.InnerXml = TotalBatch;
XmlNode ndReturn = Asp.UpdateListItems("Employee", batch_element);
//Console.WriteLine(ndReturn.OuterXml.ToString());

sw1.WriteLine(ndReturn.OuterXml.ToString());
sw1.WriteLine(ndReturn.OuterXml.ToString() + " Logtime" + DateTime.Now);
sw1.Flush();
sw1.Close();
sw1.Dispose();

}
catch(Exception Ex)
{

sw1.WriteLine("SyncFromDBI: Error in Exception" + Ex.InnerException + " , " + Ex.Message + " Logtime" + DateTime.Now);
sw1.Flush();
sw1.Close();
sw1.Dispose();

}
odbcon.Close();
dr.Dispose();
odcmd.Dispose();
odbcon.Dispose();

}

Do not forget to provide reference to System.IO and System.Data.SQLClient.

Run the console application. Open the sharepoitn list. ola!!! you see the records form the tables pulled into the sharepoint list.