No third-party C # self-implementing visual sourcesafe to manage Oracle programs

It's easy to write your own things. At least it's not strange to start it...

This section describes how to use VSS to manage data written in Oracle.Code.

I. Introduction

Visual sourcesafe (VSS) is a version management software launched by Microsoft, which can be conveniently managed.Source codeThere are a lot of similar software, and there are quite a lot of similar software, but in the features described in this article, VSS is enough to complete the tasks we require.

Oracle is a large database software that manages 60% of the world's databases. Here we will not describe more about its powerful functions. In Oracle, functions, procedure, and package can be written in PL/SQL.ProgramAll are stored in the database in the form of source code (except for special encryption ).

Version Management is especially important when programs often need to be modified. In the past, we saved stored procedures into a file before or after each program modification, added a date after the file name to indicate the version of the program, and placed it in a dedicated directory, in this way, a program will have a large number of files, causing great trouble for later maintenance work. Later, I tried to put the generated file into VSS for management, but it was very cumbersome to use. I had to first save it as a file and then checkin it in the VSS client. If I had to checkin many programs into VSS, however, it is a huge workload. When using the oracle11i system, the number of packages under the apps user reaches more than 30 thousand, and the source code adds up to 1 GB. If you use the preceding method to checkin, i'm afraid it's not a good time.

Therefore, in order to solve the above problems, I developed a special program in C # named "visual sourcesafe for Oracle", that is, it is a VSS client dedicated to Oracle services. To use this program, you must install the Oracle client software and VSS client software on your machine, because their corresponding components are used in the program.

The first thing to note is that if you use your program to send the checkin content to VSS, this is the most important part of this program. After finding the information, you can find that you can use ssapi in VSS. DLL file to implement this function. This dll is an external API provided by VSS to process VSS transactions. It can be directly referenced in Vs and Vs will automatically generate a hosted class, the name is InterOP. sourcesafetypelib. DLL. In this API, all the API functions we need, including checkin checkout additem, will be introduced later.

Speaking of VSS, let's talk about oracle. We must extract the program from the database before writing it to VSS. Otherwise, VSS is useless. The functions/procedure/package (package Header/package body) of Oracle are all stored in the user_source view. The meanings of several fields in this view are as follows:

Name: the name of the program. It can be the package name or the Stored Procedure program.

Type: the type of the program, such as the package or stored procedure.

Line: the row number. Each record only stores the value of a row of the program.

Text: content of each line in the program

According to the above structure, the specific content of a stored procedure should be to find the values of all text fields with name = stored procedure name and sort them by line Fields, as shown below:

Select text from user_source where name = 'xxxx' order by line

As for how to convert these rows into a string, allProgramming LanguageIt should be similar, as long as the content of all rows is simply added up, of course, do not forget to add a line break in the middle of each line, so that the program looks like the previous, otherwise, it would be hard to understand.

II. Implementation

1. log on

In terms of program implementation, the first part is to create a logon interface, which is different from VSS logon and Oracle logon, because in the same logon interface, I have logged on to Oracle and VSS at the same time. The interface is as follows:

According to the literal meaning on the interface, you can easily know how to use this interface.

In the login program, I used the Registry. After each successful login, the current database login username, database connection string, and VSS login username are used, the VSS configuration file name is written to the Registry. When you log on to the Registry next time, you can retrieve the file name from the Registry to improve the efficiency of the program.

The code for reading the registry is as follows:

Microsoft. win32.registrykey =

Microsoft. win32.registry. currentuser. opensubkey (registry_key, false );

If (key! = NULL)

{

Txtoracleusername. Text = key. getvalue ("oracle_user_name"). tostring ();

Txtoracledb. Text = key. getvalue ("oracle_connection_string"). tostring ();

Txtssusername. Text = key. getvalue ("ss_user_name"). tostring ();

Txtssdb. Text = key. getvalue ("ss_connection_string"). tostring ();

}

Registry_key = "Software \ vss4oracle \ login" indicates the path stored in the registry.

After logging on to the Registry, write the following code:

Microsoft. win32.registrykey key = Microsoft. win32.registry. currentuser. opensubkey (registry_key, true );

If (Key = NULL)

{

Key = Microsoft. win32.registry. currentuser. createsubkey (registry_key );

}

Key. setvalue ("oracle_user_name", txtoracleusername. Text );

Key. setvalue ("oracle_connection_string", txtoracledb. Text );

Key. setvalue ("ss_user_name", txtssusername. Text );

Key. setvalue ("ss_connection_string", txtssdb. Text );

This module only processes simple logon. After logon, each parameter in the form is sent to the main form. The database user's checksum and VSS user's checksum are completed by the main form. In order to pass parameters to the main form, you need to add several attributes to this form to indicate the values of various file box controls on the form. The reason why the control value is not directly used to add attributes for representation is that, it is because the control is a private variable by default, and private variables cannot be changed to public variables. The attributes are as follows:

Public String oracleusername

{

Get {return txtoracleusername. Text ;}

}

Public String oraclepassword

{

Get {return txtoraclepassword. Text ;}

}

Public String oracledb

{

Get {

If (txtoracledb. Text = "")

{

Return "(local )";

}

Else

{

Return txtoracledb. text;

}

}

}

Public String ssusername

{

Get {return txtssusername. Text ;}

}

Public String sspassword

{

Get {return txtsspassword. Text ;}

}

Public String ssdb

{

Get {return txtssdb. Text ;}

}

2. List projects and content in VSS

After obtaining various required parameters from the logon form, go to the main form. In the initial state, the main component of the form is to list relevant projects in VSS for use.

Your VSS may store programs other than Oracle, but in this program, only the relevant Oracle program is displayed, and other content is not processed, the project structure shown in VSS is as follows:

$/Oracle/connection string name/Oracle username/

Function

Package

Procedure

Sequence

Synonym

Table

Trigger

View

The source code of these classes can be found in Oracle. If you need to add a new type, you can directly modify them in subsequent programs.

The main program interface is as follows:

Each time you log on, the system checks if VSS includes the required project structure. If it does not exist, it is automatically created. The process is as follows:

Add a reference to a new namespace in the code of the main form: Using sourcesafetypelib;

Then, run the following code to access the VSS database before other operations:

DB = new vssdatabaseclass ();

DB. Open (ssdb, ssusername, sspassword );

DB is a private variable of the vssdatabaseclass type.

After opening the database, start to create the required project. The Code is as follows:

String root = "$/Oracle/" + oracledb + "/" + oracleusername;

Createprojecttree (Root );

String [] items = new string [8];

Items [0] = "table ";

Items [1] = "View ";

Items [2] = "Trigger ";

Items [3] = "function ";

Items [4] = "procedure ";

Items [5] = "package ";

Items [6] = "sequence ";

Items [7] = "synonym ";

Vssitem item = dB. get_vssitem ("$/", false );

For (INT I = 0; I <items. getlength (0); I ++)

{

Try

{Item. newsubproject (root + "/" + items [I], "created ");}

Catch (exception ex)

{System. Diagnostics. Debug. writeline (ex. Message );}

}

Note that the try/catch structure is used during creation, that is, when the project to be created exists, it jumps to the past to directly process other things, because I have not found a function in the database to check whether a project exists, this method can only be used.

After the above creation, the required structure already exists in VSS. You can directly view it using the VSS client. If this is the first time you use it, there is no content in each project. Of course, it is because you have not performed any checkin operations.

After creating the VSS structure, the main form has a Treeview control to display this structure. The Code is as follows:

Rtbmessages. appendtext ("Building source safe project hierarchy... \ n ");

Application. doevents ();

Cursor = cursors. waitcursor;

Tvproject. nodes. Clear ();

Try

{

String root = "$/Oracle/" + oracledb + "/" + oracleusername;

Tvproject. nodes. Add (Root );

Vssitem vssproj = dB. get_vssitem (root, false );

Ivssitems items = vssproj. get_items (false );

Foreach (vssitem item in items)

{

Tvproject. nodes [0]. nodes. Add (item. Name );

}

Tvproject. nodes [0]. Expand ();

}

Catch (exception ex)

{

Rtbmessages. appendtext ("\ n" + ex. tostring ());

}

Finally

{

Cursor = cursors. default;

Rtbmessages. appendtext ("\ n... done ");

}

The above function only shows the structure of a tree and does not include specific content, such as specific functions and stored procedures, which are not yet reflected in the above Code. This part of the display function is placed in the after_selected event in the left-side tree. That is, if you select any project, the project details are displayed.

We can see that there are two packages in the system. The two packages are different according to the icon. The first icon indicates that the object has been stored in VSS, the second icon indicates that the object has never been checked in the VSS system.

3. Check in (checkin)

Checkin refers to importing the code of objects in the database to the VSS system. There are several ways to check in. Right-click the object or right-click the directory tree on the left to bring up the check-in dialog box, the function is the same, but the function range is different. If you sign in on an object, you can only check in the selected object (either one choice or Multiple choices ), if you check in the left-side directory tree, the entire directory is checked in. For example, if you check in a package, all the packages in the system are checked in. If there are many packages in the system, there are not many actual updates. This method will take a long time. If you right-click a root node such as $/Oracle/(local)/Scott, all functions/procedur/package /... When all objects are checked in, in a large system, this will be a very time-consuming process and should be selected based on the actual situation, and if it is a relatively small system, this method is often used to replace some objects that are not checked in with VSS, which is a good method.

Based on my current needs, I have designed only one text box, that is, check comment, for example:

The comment used to record this check-in.

Before checking in, we need to get the specific content of the Oracle object, such as package. We need to get the specific code. The specific method is mentioned above.

Because of the features of VSS, We must generate an actual physical file after getting the code to be used by VSS, because the streamwriter object is used to generate a file for the obtained content, as follows:

String filename = tempdir + "\" + contentname;

If (file. exists (filename ))

{

File. setattributes (filename, fileattributes. Normal );

File. Delete (filename );

}

Streamwriter writer = new streamwriter (filename, false, system. Text. encoding. Default );

Writer. Write (content );

Writer. Close ();

In the code above, contentname indicates the Object NameCompositionFile Name. tempdir can be a temporary directory or a directory that you forcibly specify. Content is the actual code of the object generated from the DB.

In the naming rules of Oracle, the "$" character is valid. In VSS, this character indicates the root path of VSS, so it becomes invalid, that is, if the name of a package includes this character, it will not be properly checkin. In the oralce11i system, this character is widely used in the package name. To solve this problem, you need to replace the $ character with other characters, and the operating system must be able to recognize the file during file generation. After testing, the "@" character is selected, therefore, the above Code is changed to the following:

String filename = tempdir + "\" + contentname. Replace ("$ ","@");

In the last check-in step, you can directly use the checkin method or add method of vssitem.