A very simple SQL Server generation Common C # statement tool was born

Objective:　

This article as long as the record, the process of the birth of the tool, the role of the technology is too simple to say that there is no technology ~ mainly exercise the ability to write articles!

Body:

In the development of projects, often to maintain or change some of the old projects, involving a few simple table operations, most of them are ADO operation database Code! Write changes, additions, deletions, etc. With those code generator or something, you need to configure a lot of things, and I really want to generate as long as a small piece of code, self-reliance, I get a tool like this to help themselves.

Since it is a tool to operate on SQL Server, it is definitely the first step to implement a link to SQL Server that is logged into SQL Server.
Login function:
1, log in using SQL Server account password, link the database
2, after the successful login can record the record of this landing so that the next time you can quickly log in with the log
Interface implementation:

　　　　

　　

If the IP is the first time landing, I will log in when the data stored in the XML file, when the landing will be all stored records listed for the user to choose.
Here are some of the XML read and write, paste my written XML operation method.

/// <summary>        ///Collection writes out XML/// </summary>        /// <typeparam name= "T" ></typeparam>        /// <param name= "ls" ></param>         Public Static voiddatatoxml<t> (list<t> ls,stringpath) {XmlDocument xmldoc=NewXmlDocument (); //join the declaration paragraph of XML, <?xml version= "1.0" encoding= "gb2312"?>XmlDeclaration xmldecl; Xmldecl= xmldoc. Createxmldeclaration ("1.0","gb2312",NULL); XmlDoc.            AppendChild (XMLDECL); //Add a root element            varXmlelem = xmldoc. CreateElement ("","Nodes",""); XmlDoc.            AppendChild (Xmlelem); //add a different element            foreach(T sinchls) {XmlNode root= xmldoc. selectSingleNode ("Nodes"); XmlElement Xe1= xmldoc. CreateElement ("Node"); foreach(PropertyInfo Piinch typeof(T). GetProperties ()) {ObjectValue =NULL; if(s! =NULL) {Value= Pi. GetValue (s),NULL); XmlElement Xesub1=xmldoc. CreateElement (pi.                        Name); Xesub1. InnerText= value = =NULL?"": Value.                        ToString (); Xe1.                        AppendChild (XESUB1); Root.                    AppendChild (XE1); }                }            }            //Save the created XML documentxmldoc.        Save (path); }

View Code

/// <summary>        ///XML Generation Entity collection/// </summary>        /// <typeparam name= "T" ></typeparam>        /// <param name= "Xmlpath" ></param>        /// <returns></returns>         Public StaticList<t> getxmltomodel<t> (stringXmlpath) {            Try            {                #regionTraversing XMLXmlDocument _XM=NewXmlDocument (); _xm.                Load (Xmlpath); XmlNodeList Xmlist= _xm. SelectNodes ("/pma_xml_export/database/table"); List<T> _ls =NewList<t>(); foreach(XmlNode Pagenodeinchxmlist) {T _m= activator.createinstance<t>(); foreach(PropertyInfo Piinch typeof(T). GetProperties ()) {foreach(XmlNode ndinchpagenode.childnodes) {XmlElement element=(XmlElement) nd; if(element. GetAttribute ("name") ==Pi. Name) {ObjectValue =NULL; Value=element.                                InnerText; Type type=Pi.                                GetType (); Pi. SetValue (_m, Convert.changetype (value, pi). PropertyType),NULL); }}} _ls.                ADD ((T) _m); }                #endregion                return_ls; }            Catch            {                return NULL; }        }

View Code

　　

Now that the landing is successful, we can query all the libraries and tables under the database and show us the choices.
The database/Table query shows:

　　

Query all database methods:　　　　

Select *  from [sysdatabases] Order  by [name]

Query all table methods:

 select  [  , [  Name    from  [ sysobjects   " where  [ type  ]  =   "   order  by  [ "  

After getting the data of the database, we can bind to the tree and tie the point-click event, and then display all the tables under the library when clicked, so that we can select the table to manipulate.

Table Property Query Method:

        /// <summary>        ///Get table Information/// </summary>        /// <param name= "tablename" ></param>        /// <returns></returns>         PublicDataTable GetTableInfo (stringtablename) { Try            {                stringsql =string. Format (@"SELECT table name = D.name,--case if A.colorder=1 then d.name else ' end, table description = when a.colorder=1 and is Null (F.value, ') Else ' end, field ordinal = a.colorder, FieldName = a.name, identity = case when ColumnProperty (a . Id,a.name, ' isidentity ') =1 then ' √ ' else ' end, primary key = case if exists (SELECT 1 from sysobjects where xtype= ' PK ' a nd parent_obj=a.id and name in (SELECT name from sysindexes WHERE indid in (SE Lect indid from Sysindexkeys WHERE id = a.id and colid=a.colid)) Then ' √ ' Else ' end, SqlType = B.name, takes bytes Number = a.length, length = ColumnProperty (a.id,a.name, ' PRECISION '), number of decimal digits = IsNull (ColumnProperty (a.id,a.name, ' scale ') ), 0), allow null = case time a.isnullable=1 Then ' √ ' Else ' end, default = IsNull (E.text, '), Fieldexplain = Isnul     L (G.[value], ') from syscolumns Aleft joins Systypes b on A.xusertype=b.xusertypeinner joins sysobjects D on A.id=d.id and D.xtyPe= ' U ' and d.name<> ' dtproperties ' left joins syscomments E on A.cdefault=e.idleft join sys.extended_p Roperties g on a.id=g.major_id and a.colid=g.minor_id left joins Sys.extended_properties F on d.id=f.major_id and F.minor_id=0where d.name= ' {0} '--if only the specified table is queried, add this condition to order by A.id,a.colorder", TableName); returnService.Data.SqlHelper.DataTable (SQL); }            Catch(Exception ex) {Throw(ex); }        }    }

Code Template:

Templates are also stored in XML files and can be configured with multiple templates.
Generate Code:
The method used here is very basic, simple loop all field information based on the template reserved placeholder for replacement generation, and some commonly used code generator can not be compared, but in the light of their own ideas, such tools are very useful, there are empty people can also learn their practice is also the accumulation of their knowledge.

Some of the placeholders I used in the substitution

[TableName] The name of the replaceable table */**************************/[list][list] is a multi-field loop list [list =1][/list] The last symbol of the last element is automatically removed by a multi-field loop. / ************************** * the replaceable content in the loop body content is: [fieldname] Field name [SqlType] field type [ Fieldexplain] Field Description /**************************/[replace=xxx][/ Replace ] Filters the variables in the body of the loop. XXX can be filled: sqltype, fieldname, Fieldexplain

Document Generation:

Later, because we have to look at some of the structure of the table and comb the data document so I have a rich text editing plug-in, you can make the table's properties to create a tables table, easy to view, or can be copied into Word.

　　

Knowledge Points:
　　1, how forms trigger events between each other to refresh data
This tool is two interface, when the main interface main load will determine whether the login, if not login will be loaded login form login. The tree in main will be refreshed after successful login in login.
　　　　Implementation code:

Define the event in login and trigger the event after the login is successful.

        /// <summary>        ///Define login for event delegate/// </summary>         Public Delegate voidloginsuccess (); /// <summary>        ///Defining a Login Success event/// </summary>         Public  Eventloginsuccess loginsuccessevent; /// <summary>        ///Trigger Login Success Event/// </summary>        protected Virtual voidonloginsuccessevent () {varHandler =loginsuccessevent; if(Handler! =NULL) handler (); }        Private voidButton1_Click (Objectsender, EventArgs e) {             This.        Onloginsuccessevent (); }

Main binds the event callback function when calling login.

Private void main_load (object  sender, EventArgs e)        {            login lg=new  login ();            + = loginsuccess;            Lg. Show ();        }         void loginsuccess ()        {            = DateTime.Now.ToString ("u");        }

Well, for the time being to summarize so many things.

A very simple SQL Server generation Common C # statement tool was born