Add, delete, modify, and synchronize the list with the database

Requirement: when creating a form, some data collected by a form can also be imported into the corresponding database for later data analysis.

This idea looks good, but how can we implement it? In sharepoint, the concept of event is often called EventHandle by developers. Operations on websites, document libraries, list libraries, and list items can trigger related events. Based on this, we can use the Event Method to store the relevant data in the document to the SQL database when creating a document. Now, the analysis is complete. For more information, see the following steps:
Step 1:
Create a table in the SQL database. The table name is "Reimbursement Form". The specific fields are as follows:

Used to store data in the form.
Step 2:
Use VS to create a project with the class library name "EventHandle ". Rename Class1.cs to listeven thandle. cs. Add Microsoft. SharePoint. dll to the reference. The following is the source code of listeven thandle. cs, which defines the add, delete, and modify events of the list:

Using System;
Using System. Collections. Generic;
Using System. Text;
Using Microsoft. SharePoint;
Using System. Data. SqlClient;

Namespace EventHandle
{
Public class listeven thandle: SPItemEventReceiver
{
Public override void ItemAdded (SPItemEventProperties properties)
{
SynchronizationSQL (properties );
}
Public override void ItemUpdated (SPItemEventProperties properties)
{
SynchronizationSQL (properties );
}

Public SqlConnection createConn ()
{
SqlConnection conn = new SqlConnection ("Data Source = cdh10000; Initial Catalog = oalistDataToTable; User ID = sa; Password = 111111 ");
Conn. Open ();
Return conn;

}

Public override void ItemDeleting (SPItemEventProperties properties)
{
SPListItem item = properties. ListItem;
String itemid = properties. ListItem. ID. ToString ();

SqlConnection conn = createConn ();

SqlCommand cmd = new SqlCommand ("delete from reimbursement form where listitem_id = '" + itemid + "'", conn );
Cmd. ExecuteNonQuery ();
Conn. Close ();
}

Void SynchronizationSQL (SPItemEventProperties properties)
{
SPListItem item = properties. ListItem;
String itemid = properties. ListItem. ID. ToString ();
String name = item ["name"]. ToString ();
String department = item ["department"]. ToString ();
String timeto = item ["time"]. ToString ();
String money = item ["amount"]. ToString ();
String eventto = item [""]. ToString ();

SqlConnection conn = createConn ();

SqlCommand cmd = new SqlCommand ("select listitem_id from reimbursement form where listitem_id = '" + itemid + "'", conn );

If (Convert. ToInt32 (cmd. ExecuteScalar ()> 0)
{
Cmd = new SqlCommand ("update reimbursement form set name = '" + name + "', department = '" + department + "', time = '" + timeto + "', amount = '"+ money +"', reason = '"+ eventto +" 'where listitem_id =' "+ itemid +" '", conn );
Cmd. ExecuteNonQuery ();
}
Else
{
Cmd = new SqlCommand ("insert into Reimbursement Form (listitem_id, name, department, time, amount, reason) values ('" + itemid + "', '" + name + "', '"+ department +"', '"+ timeto +"', '"+ money +"', '"+ eventto +"') ", conn );
Cmd. ExecuteNonQuery ();
}

Conn. Close ();

}

}
}

Do not forget to add a strong name to generate the project. Use reflector.exe to obtain the Assembly name and public key of EventHandle. dll, and drag EventHandle. dll
C: "WINDOWS" assembly folder. Restart IIS.

Step 3:
Create a console application project with VS: The project name is "EventRunning ". Add Microsoft. SharePoint. dll to the reference. Add the following code to Program. cs:

Using System;
Using System. Collections. Generic;
Using System. Text;
Using Microsoft. SharePoint;
Using System. Data. SqlClient;

Namespace EventRunning
{
Class Program
{
Static void Main (string [] args)
{
SPSite site = new SPSite ("http: // cdh10000 ");
SPWeb web = site. OpenWeb ();
SPList list = web. Lists ["Reimbursement Form"];

String asmName = "EventHandle, Version = 1.0.0.0, Culture = neutral, PublicKeyToken = 01dc07d3d1e20903"; // Assembly name
String className = "EventHandle. listeven thandle"; // class name in the Assembly

List. EventReceivers. Add (SPEventReceiverType. ItemAdded, asmName, className );
List. EventReceivers. Add (SPEventReceiverType. ItemUpdated, asmName, className );
List. EventReceivers. Add (SPEventReceiverType. ItemDeleting, asmName, className );

}
}
}

Run the project. At this point, the Event of the reimbursement form document library has been completed.
Step 4:
Let's go!
Enter the reimbursement form library, create a new form, and enter the following content,

Let's take a look at the data in the SQL database table, for example:

Haha, the data in the SQL database is exactly the same as the form data. Cool! Now you can use your imagination ......

 

 

Reprinted from: http://www.cnblogs.com/netdazhi/articles/1266809.html