I want to share how to upload data from 1С using COM-connection. This option can be called an alternative to the use of standard and non-standard 1С processors. For example, the language C# is taken and the upload is performed in a single large XML-file.
Where can be used
The data exchange between two systems is complex for the specialist that he has to understand both his work and the unknown environment. If you need the data from 1С and the learning of 1С is not prospective direction, then, as an option, you can use the method to obtain data using the external connection without any visual opening of 1С.
It is possible to try o use this method in different tasks:
- load in another database of arbitrary format;
- move from 1С to another accounting system (data migration);
- upload data in the base to analyse (OLAP);
- have data backup in the opened form.
I just want to say about the drawback of this method. In 1С many processors are written to upload data for different purposes. The use of 1С processors can be called the most official method to obtain data. The typical processor not only uploads data from the base, but also conveniently converts them and assembles using the joins in the queries and performing the script calculations. uploading data using the method described in the article, you get the primary tables saved on the database and you may require their further processing.
What will we get as a result?
Using the external connection, it is possible to upload data in file of arbitrary format or send the obtained records to another database without saving the temporary file at all. Here the uploading in xml-file is made as a demonstration of the uploading ability by itself.
The data are extracted from the 1С base and place in a single xml-file. Here is an example of output file.
What data are uploaded
Numeric and string types are saved in the uploaded file in the usual string form. The fields of references displayed in the forms with the selection button with three dots «…» are uploaded like the GUID values. For example, in Invoive an attribute «Company» is specified with the value «0cc56776-0daa-11e3-bf95-f46d04eec7f5». In the catalog Companies we can find «Microsoft» using this reference.
Uploading is performed regardless to any particular configuration and data structure. Searching the metadata, all the catalogs and documents are uploaded. For each catalog item and each document all the attributes are uploaded. if document has the table parts, for each table parts all the rows are uploaded.
in the example the uploading is implemented only for basic types of tables: catalogs, documents and tables parts of documents. The uploading of constants, information registers and table parts of catalogs is not implemented. The uploading of accumulation registers and accounting registers is not performed, because these data can be obtained from the primary documents. But for the purposes of analysis, perhaps it was easier to upload the records or registers.
How are the data obtained
The program startup must be executed with the installed 1С, because the connection to it is performed via COM-connection. The connections string in the example is created for file variant, but if necessary, it is possible to forward the connection to 1С server. The user with specified password must be registered in the base. The user must have the rights to read all the data and an ability to set the external connection.
An exclusive mode is not required. It is possible to «upload» the base I such way without interrupting the work or other users.
Alternative methods
This method to extract data is not unique. We could even say that the access using COM-connection is the slowest and the following methods are more productive. But these methods have the fundamental drawbacks.
External and built-in in configuration processor
The same result as an xml-file can be obtained from the external or built-in 1С processor. The advantage of 1С processor execution is in its speed. For all that, these are the internal mechanisms, and the calls from COM-connection are worth something in terms of time costs.
The drawback of processor usage is that the automation ends at this. The user has the open manually the processor form, set the parameters, click the upload button.
Procedure in the module available for external connection
The upload procedure can be placed in the general module available for external connection. The procedure itself must be declared as an export one.
Procedure ExportDatabase(Path) Export
File = New XMLWriter;
File.OpenFile(Path);
File.WriteXMLDeclaration();
File.WriteStartElement("database");
...dumping data to xml...
File.WriteEndElement();
File.Close();
EndProcedure
Now it is enough to call the upload procedure from C#.
Connection.ExportDatabase(path);
The advantage of this method is in the rapid execution. The upload procedure implementation can effectively use the server calls and data exchange between the server and client.
The main drawback is in the necessity to make the changes in the configuration to edit the general module.
Typical tools
in many configurations there are the built-in upload processors. Many processors upload not all the data, but limited by the recipient. From the trade base to the accounting only the primary documents are uploaded to form transactions, and in the online store the positions of nomenclature catalog are uploaded as well as the prices and characteristics of goods. The uploading of the entire base is usually made to move to the next revision. If there is an ability to use the typical tools for particular task, it’s likely the best solution.
Physical base access
there are the methods to access the base from outside, but they are based on the knowledge of database structure. If 1С base is located on the SQL server, in fact, the tables are available to read. We can make the queries and receive all data. the problem is only that the data structure is complex and requires a very careful processing.
it is more difficult to read from the file variant of 1С base. The structure of 1CD file is not documented and in principle can vary. Here it should be noted that for the purposes to access information it is possible to convert the file mode in SQL and vice versa.
Complete program text
Here is the C# code of the class to connect to 1С base and upload data as well as an example to use this class.
using System;
using System.Text;
using System.Xml;
namespace ConsoleApplication
{
class Export1C
{
dynamic Connection;
public void Connect(string path, string user = "", string password = "")
{
dynamic connector = Activator.CreateInstance(Type.GetTypeFromProgID("V82.COMConnector"));
string connectionString = "File="" + path + """;
if (user != "")
connectionString += ";Usr="" + user + """;
if (password != "")
connectionString += ";Pwd="" + password + """;
Connection = connector.Connect(connectionString);
}
public void Export(string path)
{
XmlTextWriter xml = new XmlTextWriter(path, Encoding.UTF8);
xml.Formatting = Formatting.Indented;
xml.WriteStartDocument();
xml.WriteStartElement("database");
// Catalogs
foreach (dynamic catalog in Connection.Metadata.Catalogs)
{
xml.WriteStartElement("catalog");
xml.WriteAttributeString("name", catalog.Name);
dynamic query = Connection.NewObject("Query");
query.Text = "select * from catalog." + catalog.Name;
dynamic items = query.Execute().Unload();
for (int i = 0; i < items.Count(); i++)
{
xml.WriteStartElement("item");
for (int j = 0; j < items.Columns.Count(); j++)
{
xml.WriteStartElement("attribute");
xml.WriteAttributeString("name", items.Columns.Get(j).Name);
xml.WriteAttributeString("value", Connection.XMLString(items.Get(i).Get(j)));
xml.WriteEndElement();
}
xml.WriteEndElement();
}
xml.WriteEndElement();
}
// Documents
foreach (dynamic document in Connection.Metadata.Documents)
{
dynamic query = Connection.NewObject("Query");
query.Text = "select * from document." + document.Name;
dynamic table = query.Execute().Unload();
for (int i = 0; i < table.Count(); i++)
{
xml.WriteStartElement("document");
xml.WriteAttributeString("name", document.Name);
dynamic docref = null;
for (int j = 0; j < table.Columns.Count(); j++)
{
xml.WriteStartElement("attribute");
string field = table.Columns.Get(j).Name;
xml.WriteAttributeString("name", field);
dynamic tabular = document.TabularSections.Find(field);
if (tabular == null)
{
xml.WriteAttributeString("value", Connection.XMLString(table.Get(i).Get(j)));
if (field == "Ref")
docref = table.Get(i).Get(j);
}
else
{
dynamic subquery = Connection.NewObject("Query");
subquery.Text = "select * from document." + document.Name + "." + field +
" as lines where lines.Ref=&Ref";
subquery.SetParameter("Ref", docref);
dynamic lines = subquery.Execute().Unload();
for (int line = 0; line < lines.Count(); line++)
{
xml.WriteStartElement("line");
for (int col = 0; col < lines.Columns.Count(); col++)
{
xml.WriteStartElement("attribute");
xml.WriteAttributeString("name", lines.Columns.Get(col).Name);
string value = Connection.XMLString(lines.Get(line).Get(col));
xml.WriteAttributeString("value", value);
xml.WriteEndElement();
}
xml.WriteEndElement();
}
}
xml.WriteEndElement();
}
xml.WriteEndElement();
}
}
xml.WriteEndElement();
xml.WriteEndDocument();
xml.Close();
}
}
class Program
{
static void Main(string[] args)
{
Export1C export = new Export1C();
export.Connect("D:\TestBase", "User", "pass");
export.Export("D:\Export.xml");
}
}
}
Export is divided into two blocks: uploading of catalogs and uploading of documents. To get the types of catalogs and documents, the access to metadata is performed.
The data of each table are extracted using the separated queries. For the documents also the tables parts are uploaded. To do this, the subqueries are generated for the table parts to select by reference of uploaded document.
For particular tasks you may have to make the changes in the code. The generated names of xml-file tags (catalog, document, attribute, name, value) are chosen arbitrary. If to make minor changes in the code, it is possible to change the structure or output xml-file. It is possible also to limit the uploaded data only by certain tables and attributes using the conditions in the metadata selection loops.