/ / Add name space Using system.data; use system.data.sqlclient;
/ / Define method getData (), return a data table private system.data.dataable getData () {sqlConnection conn = new sqlconnection (@ "server = pxgd2; initial catalog = pingxiang; uid = sa; pwd =;"); SqlDataAdapter Adapter = new SqldataAdapter ("SELECT Username User Name, Catalyst_port occupies port, home_address residential address, IP_ADDRESS
IP address, Phone, Addtime Open Date from UserInfo WHERE CATALYST_PORT = 1 or Catalyst_port = 'Order By ip_address desc ", conn)
DataSet DS = New Dataset (); try {adapter.Fill (DS, "Customer");} catch (exception ex) {messagebox.show (ex. scroll ());} return ds.tables [0];}
// Button Private Void Button1_Click (Object Sender, System.EventArgs E) {Excel.Application Excel = New Excel.Application (); int RowIndex = 1; int coLINDEX = 0;
Excel.Application.Workbooks.add (true); DataTable Table = getData (); // Assign the column name of the obtained table to cell Foreach (Datacolumn col in table.columns) {Colindex ; Excel.cells [1 , Colindex] = col.columnname;}
// The same method for processing data foreach (DataRow row in table.Rows) {rowIndex ; colIndex = 0; foreach (DataColumn col in table.Columns) {colIndex ; excel.Cells [rowIndex, colIndex] = row [col.ColumnName]. TOSTRING ();}} // is invisible, that is, the background processing Excel.visible = true; ==============================================================================================================================================================================
2 remote processing framework and ADO.NET
Two important issues that develop distributed database systems need to be resolved are: Data communication between each site and the operation and management of the database. Use C # combined with ADO.NET to efficiently and reliably solve these two problems. Specifically, the data can be easily resolved by using the .NET remote processing framework in C #, command remote delivery problem; C # operates through ADO.NET to make various operations for databases in distributed database systems , Reliable, easy to solve data consistency problems.
2.1 .NET Remote Processing Framework
There are three ways to achieve data and commands. The first is to use a message or message, convert the data to be transmitted into a stream format, and then send to the remote host by a socket programming message. This method is troublesome and is not easy to implement. The second is to use the Web Service, ie the remote host provides a Web Service for a database query service. This method can only query a single site and unable to implement a multi-site joint query. The third is to use the .NET remote processing framework (.NET Remoting Framework) technology, it hides the technical details of the remote call, and the service program only needs to turn the local object to the remote object of the remote provider by simply setting. The client can access the remote object as transparently like accessing the local object, all messages, packets, etc. are handed over to .NET Remoting object processing, greatly simplifying the development. The general process of remote processing is shown in Figure 1: Figure 1 Remote processing
First, the server side creates an instance of a server class, and the remote processing system creates a proxy object representing the class and returns a reference to the agent to the client object. When the client calls the method, the remote processing infrastructure connection checks the type information and sends the call to the server process through the channel. Listening channels get this request and forward it to the server remote processing system, the server remote processing system lookup (or created when necessary) and calls the requested object. Then, this process will be performed in reverse, the server remote processing system will respond to the bundle of messages and sent by the server channel to the client channel. Finally, the client remote processing system returns the result of the call to the client object via a proxy.
2.2 ADO.NET
ADO.NET is the core of the .NET database application with XML. It uses offline data structures, data in the data source is cached into the Dataset object, and the user does not need to lock the data source, and the data is saved in XML format.
2.2.1 ADO.NET Management Data Consistency
In a distributed database system, it is likely that multiple users have access to and modify data at the same time, and therefore, for distributed database systems, data consistency is indispensable. ADO.NET controls data consistency by using an optimistic consistency (actually DataSet objects to support the use of optimistic consistency control mechanisms), that is, the data line is only locked when it is truly updated in the database, and in pessimism In a consistency, the data line has been locked in this time from the extracted to the database. Therefore, using ADO.NET can respond to a large number of users within less time.
In addition, in a distributed database system, it is often encountered when the user has modified since the line has been modified since the extracted, violates the consistency principles. The problem is also well resolved, that is, the DataSet object is used to maintain two versions for each modified record: the original version and the update version, before the updated record is written back to the database, you must first put the data. The original version of the centralized record is compared to the current version in the database. If two versions match, update records in the database; otherwise, there will be a violation of the principle of consistency.
3 instance development
A household appliance chain has a headquartering and many branches, headquarters and branches, and all stores often need to conduct various information inquiries (such as: goods on the same price table, sales status and inventory information, etc.), establish a organization Distributed Database Query System, you can realize sharing of headquarters and store information for unified management.
3.1 System Design
3.1.1 System Structure Diagram
The system structure is shown in Figure 2:
Figure 2 System structure diagram
Headquarters and all stores have configured a server with fixed IP. Other computers are connected to the server, and the headquarters and the servers of each branch are connected through the communication network. 3.1.2 System Implementation Steps
System implementation is divided into three main steps. First, design a database for headquarters and individual branches. Due to the large amount of data, SQL Server creates sales and inventory databases for each branch, and creates employee databases, the inventory database, credit card customer database, and supplier information database, and supplier information databases for headquarters. Second, a dynamic link library (DLL) that provides database services (DBSERVER) is required, and some of the services you want to use (such as remote objects) and functions (such as: Local Data table) Remote creation and deletion, intervals of data sheets, etc.) In this DLL, each branch needs to use this DLL to call some of the services and functions when queries. Finally, the client query interface is developed according to actual needs.
3.2 Key technologies for system implementation
3.2.1 Release and Acquisition of Remote Objects
The first job to do after the system is running is to publish a local remote object and get the remote objects issued by other stores. When you release a remote object, first set a network port number, then create and register a channel, and finally post the server-side activation object. Other venues can easily obtain the released remote objects based on the IP address and the network port number. The key code for implementing remote object publishing and acquisition is as follows:
Remarks of remote objects:
// create a channel instance, port designated network port number TcpChannel MyChannel = new TcpChannel (Int32.Parse (port)); // channel register ChannelServices.RegisterChannel (MyChannel); // publisher server activates the subject RemotingConfiguration.RegisterWellKnownServiceType (TypeOf (DBServer), "Store", WellkNownObjectMode.singleton; Remote Objects Get: // Get the corresponding remote object with the IP address and port number Try {MyDBServer = (DBServer) activator.getObject (TypeOf (dbserver), " TCP: // " IP ": " P " / store ");} // Capture Exception Catch (NullReferenceException NulleXP) {MessageBox.show (" Specified URL Address is not reaches " Nullexp.Message);} catch RemotingException Remexp) {messagebox.show ("Specify the object definition is not" Remexp.Message);
3.2.2 Access to the Database
Access the database via the ADO.NET, you can easily connect the database, import data from the data source into the DataSet object, and you can perform a variety of operations in the DataSet object, and the DataSet object itself can also be remotely passed remotely. This brings great convenience to develop distributed database systems. The key code to achieve database access is as follows:
// Establish a database STRING SQLCONN = "Initial catalog = store; data source = localhost; userid = sa; password =;"; sqlConnection conn = new sqlconnection (sqlconn); conn.open (); // Open Database // import data source to a data set object try {dataSet ds = new dataSet (); DataTable dt = new DataTable ( "Result"); SqlDataAdapter adapter = new SqlDataAdapter (); SqlCommand mySqlDataSetCmd = new SqlCommand (cmdString, Conn) // cmdstring is the command to execute Adapter.selectCommand = mysqldatasetcmd; adapter.fill (dt); ds.tables.add (dt);} finally {conn.close (); // Turn off the connection} 3.2.3 Inquire
Query in distributed database systems is generally divided into three categories: local queries, remote query, and joint queries. There is no difference between the local query and the centralized database; for remote queries, as long as you get the remote object, you can easily implement the query function; the most complicated query, involving data between multiple sites, table Remote creation, delivery, connection, merge and other technologies. The implementation of the joint query is described below.
The second chain store is to query the air-conditioned inventory information supplied from all Beijing suppliers in the third and fourth chain stores in the fourth chain, which can be implemented by the following steps. First, get the headquarters and the remote objects released by the third and fourth chain stores. Next, the temporary data table T1 is created at the headquarters through the remote object, and all the supplier information of the query will be stored in the T1 table (all branches only supplier names, I don't know if they are located, only the headquarters has supplier details Information), save the T1 table to the third and fourth chain. The T1 table is then connected to the inventory table of the two stores, find out the air conditioning information supplied by Beijing suppliers (such as air conditioning name, model, number, price and other information), and connect the connection results T2 and T3 data sheets Return to the second chain. Finally, the T2 and T3 tables are merged and displayed using the DataGrid control. In the above implementation, a copy, delivery, connection, etc. used between different sites are included, and some of the functions used (eg remotely created data sheets, the remote connection, consolidation, etc. of the table, and the wind, etc.) are placed in the DLL. It can be easily called.
4 Conclusion
Using C #'s .NET Remoting technology can easily resolve communication issues between field data. In addition, C # accesses the database via ADO.NET, making the operation and management of the database more efficient and reliable. The use of these two technologies effectively solves the main problems of the development of distributed database systems, which greatly reduces the system development work, and improves the system's reliability and security ============ ================================= This article describes how to create a DTS custom task using Visual C # .NET. You can extend the functionality of the DTS by creating custom tasks through C # .NET. You can then install and register the task, and he speaks in the DTS design, just like the default DTS task. In short, you can create custom tasks using .NET Framework. In addition to creating a DTS custom task, it also includes some of the content: 1. Custom code in this article is divided into compilation, registration, and installation of custom tasks; 2. This part is a distinctive part of this is You can run custom tasks; 3, you can use some of this article (unless otherwise stated, these tools are included in .NET, you can run these tools through the .NET command line. ). Create a timely package for DTSspkg.dll If a Microsoft-based .NET client accesses a COM component, you must use a package (this component is included). This type of package is a timely running package (RCW) and you can also compile by open DTSPKG.DLL. You can also compile RCWs using the Type library export tool (TLbimp.exe), such as: Tlbimp.exe "C: / ProgramFiles / Microsoft SQLServer / 80 / Tools / Binn / DTSPKG.DLL" /out:Microsoft.sqlServver.dtspkg80.dll /keyfile:dtspkg.snk"/keyfile "parameter represents Microsoft.SQLServer.dtspkg80.dll with strong type name Public or Private keyword. Create keywords before dtspkg.snk using strong type name: sn.exe -k dtspkg.snk You should use a strong type name to other global collection caches, because you have installed the run package. Install Run Package in the Global Set Cache Install Run Package (Gacutil.exe) installation Run Pack: gacutil.exe / i microsoft.sqlserver.dtspkg80.dll
After installing the run package, you can add the same addition. Netc # project.
Add code for custom tasks
Custom registration of code. .NET does not open DLLREGINSTERSERVER and DLLUNREGISTERSERVER Like COM components, you can use ComregIsterFunctionAttribute class to perform task registration and revocation registration. Add the following code before the custom class declaration:
[GUID ("A39847F3-5845-4459-A25E-DE73A8E3CD48"), Comvisible (TRUE)] [PROGID ("DTS.SIMPLETASK")] Public Class SimpleTask: CustomTask {// Implement OF Custom Task}
The following code is an example of a function registration. The full code of the function is compiled, registered, and installed parts of custom tasks. [System.Runtime.Interopservices.comRegisterFunctionAttribute ()] static void registerserver (Type T) {// code to register custom Task}
The registration function adds the following key value to register.
HKEY_CLASS_ROOT / CLSID / A39847F3-5845-4459-A25E-DE73A8E3CD48 / Implement Categories / {10020200-EB1C-11CF-AE6E-00AA004A34D5}
10020200-EB1C-11CF-AE6E-00AA004A34D5 is the class number of the DTS package object. Since all custom tasks perform custom interfaces, you must register. The registration function adds the following registration key value:
HKEY_CURRENT_USER / Software / Microsoft / Microsoft SQL Server / 80 / DTS / Enumeration / Tasks / A39847F3-5845-4459-A25E-DE73A8E3CD48 following DTS task cache directory listings, make the task appear in the custom DTS Designer: HKEY_CURRENT_USER / Software / Microsoft / Microsoft SQL Server / 80 / DTS / Enumeration / Tasks / The following code demonstrates the execution of the task of the non-registration function. The registration function is part of the ComunRegisterFunctionAttribute class in the .NET running library. Want to browse the full code of this function, you can see the "Compile, Registration, and Install Custom Tasks" section:
[System.Runtime.InteropServices.ComUnregisterFunctionAttribute ()] static void UnregisterServer (Type t) {// code to unregister custom task} Free registration function by removing the following tasks removed from the registry key HKEY_CURRENT_USER DTS task from the cache / Software / Microsoft / Microsoft SQL Server / 80 / DTS / ENUMERATION / TASKS / A39847F3-5845-4459-A25E-DE73A8E3CD48 Finally, custom tasks are open like the Dual_Interface COM component. Create a default interface from all PUBLICs, non-static fields, properties, and methods of all classes. After the following line of code is used in the custom task source file: [Assembly: ClassInterface (ClassInterFactype.Autodual) This part of this section is fully listed. Increasing Functional Custom Tasks This article "Compile, Registration, and Install Custom Tasks" section contains a simple DTS custom task code. The task has two properties: Name and Description, the value of the Description attribute will appear in the message box. This example describes a minimized code you can use existing DTS definition tasks. However, you can create a user interface by performing a CustomTaskUI interface, but that is not discussed. By performing a custom interface, the DTS designer creates a default vision interface for custom tasks. All DTS custom tasks perform a custom task interface. Custom user interface is made of two properties, a collection and a method: 1, name and description attribute; 2, Properties set; 3, Execute method. All custom tasks should perform attributes, attribute sets, and Execute methods. Compile, registration, and install custom tasks
using System; using System.Runtime.InteropServices; using Microsoft.SQLServer.DTSPkg80; using Microsoft.Win32; using System.Windows.Forms; [assembly: ClassInterface (ClassInterfaceType.AutoDual)] namespace DTS {[Guid ( "38ED4F80-9EF4- 4752-8478-65D2DB3BA7DD "), ComVisible (true)] // GUID is created by using GUIDGEN.EXE [ProgId (" DTS.SimpleCustomTask ")] public class SimpleCustomTask: CustomTask {private string name; private string description; public SimpleCustomTask ( ) {name = ""; description = "SimpleCustomTask description";} public void Execute (object pPackage, object pPackageEvents, object pPackageLog, ref Microsoft.SQLServer.DTSPkg80.DTSTaskExecResult pTaskResult) {// Assume failure at the outset pTaskResult = DTSTaskExecResult. DTSTaskExecResult_Failure; try {Package2 package = (Package2) pPackage; packageEvents packageEvents = (packageEvents) pPackageEvents; PackageLog packageLog = (PackageLog) pPackageLog; MessageBox.Show (description);} // First catch COM excep tions, and then all other exceptions catch (System.Runtime.InteropServices.COMException e) {Console.WriteLine (e);} catch (System.Exception e) {Console.WriteLine (e);} // Return success pTaskResult = DTSTaskExecResult .DTSTaskExecResult_Success;} public string Description {get {return this.description;} set {this.description = value;}} public string name {get {return name;} set {this.name = value;}} public Microsoft.SQLServer .Dtspkg80.properties profrn null;}} [system.runtime.interopservices.comvisible (false)] Override public string toString () {Return Base.toString ();
} // Registration function for custom task. [System.Runtime.InteropServices.ComRegisterFunctionAttribute ()] static void RegisterServer (Type t) {try {const string TASK_CACHE = "Software // Microsoft // Microsoft SQL Server // 80 // DTS // enumeration // tasks "; const string catid_dtscustomtask =" {10020200-EB1C-11CF-AE6E-00AA004A34D5} "; String Guid =" {" t.guid.toString () "} "; guid = guid.toupper (); Console.WriteLine ( "RegisterServer {0}", guid); RegistryKey root; RegistryKey rk; RegistryKey nrk; // Add COM Category in HKEY_CLASSES_ROOTroot = Registry.ClassesRoot; rk = root.OpenSubKey ( "CLSID //" Guid "// Implement Categories", True); NRK = rk.createSubkey (CATID_DTSCUSTOMTASK); NRK.CLOSE (); rk.close (); root.close (); // add to dts cache in hkey_current_userroot = registry. Currentuser; rk = root.opensubkey; nrk = rk.createSubkey (guid); nrk.setValue (", t.fulname); nrk.close (); rk.close (); root.close ); SimpleCustomTask CT = new SimpleCustomTask (); root = registry.classessroot; rk = ro ot.opensubkey ("CLSID //" Guid, True); RK.SetValue ("dtstaskdescription", ct.description; nrk.close (); rk.close (); root.close ();} catch (Exception e) {System.Console.WriteLine (e.ToString ());}.} // Unregistration function for custom task [System.Runtime.InteropServices.ComUnregisterFunctionAttribute ()] static void UnregisterServer (Type t) {try {const string TASK_CACHE = "Software // Microsoft // Microsoft SQL Server // 80 // DTS // Enumeration // Tasks"; String Guid = "{" T.Guid.toTString () "}"; guid = guid.toupper Console.writeline ("
UnregisterServer {0} ", guid); RegistryKey root; RegistryKey rk; // Delete from DTS Cache in HKEY_CURRENT_USERroot = Registry.CurrentUser; rk = root.OpenSubKey (TASK_CACHE, true); rk.DeleteSubKey (guid, false); rk. Close (); root.close ();} catch (eXception e) {system.console.writeline (e.tostring ());}}}} ================ ===============================
Second, SQL Server and Excel
1. Inquiry to Excel
SELECT * from OpenRowSet ('' Microsoft.jet.OleDb.4.0 ',' 'Excel
8.0; HDR = YES; Database = C: /book1.xls; '', 'SELECT * FROM [Sheet1
$] '') WHERE C LIKE '' '% F%' '
SELECT * FROM
OpenRowSet ('' Microsoft.jet.OleDb.4.0 '"
, '' Excel 5.0; HDR = YES; IMEX = 2; Database = C: /book1.xls', [Sheet1 $])
1) HDR = YES can treat the first line of XLS as a field, such as HDR = NO, where WHERE
If you will report an error
2) [] and Meitu $ must, otherwise M $ can not recognize this account
2. Modify EXECL
Update OpenRowSet ('' Microsoft.jet.OleDb.4.0 ',' 'Excel
8.0; HDR = YES; Database = C: /book1.xls; '', 'SELECT * FROM [Sheet1
$] '')
Set a = '' Erquan '' Where C Like ''% F% ''
3. Import export
INSERT INTO OpenRowSet ('' Microsoft.jet.OleDb.4.0 ',' 'Excel
8.0; HDR = YES; Database = C: /book1.xls; '', 'SELECT * FROM [Sheet2
$] '') (ID, name)
SELECT ID, NAME from Serv_user
BCP
MASTER..XP_CMDSHELL''BCP "serv-htjs.dbo.serv_user"
OUT "C: /book2.xls" -c -q -s "." -u "sa" -p "sa" ''
Import from Excel to SQLServer:
SELECT * INTO SERV_USER_BAK
From OpenRowSet ('' Microsoft.jet.Oledb.4.0 ',' 'Excel
8.0; HDR = YES; Database = C: /book1.xls; '', 'SELECT * FROM [Sheet1
$] '')
Create if the table serv_user_bak does not exist
For detailed answers to BCP and distributed queries, check the help of SQLServer. SQLSERVER and TXT files, HTML files, VFP file data exchange is very easy
. . . .
