c# - Generate xml with table constraints as tags and column names as value -

i need generate xml below 1 constraints tags , column name values specific db table in c#.

<tablename> <key>productid</key> <composite> <column>productid</column> <column>productcode</column> <composite> <foreignkey> <column>productbaseid</column> </foreignkey> </tablename> 

can on this?

here find primary key:

select col.column_name      information_schema.table_constraints tab,      information_schema.constraint_column_usage col       col.constraint_name = tab.constraint_name     , col.table_name = tab.table_name     , constraint_type = 'primary key'     , col.table_name = 'tablename' 

if have multiple primary keys, them same query above. can count primary key , if more 1 can list them in composite. otherwise put them key.

here point, if there 2 primary keys, in <key> tag?

here find foreign keys:

select col.column_name  information_schema.table_constraints tab,  information_schema.constraint_column_usage col       col.constraint_name = tab.constraint_name     , col.table_name = tab.table_name     , constraint_type = 'foreign key'     , col.table_name = 'tablename' 

just join 2 information , write in xml. xml writing easy, here can find it. xml writing - dotnetperls

update 1 : here code, can find pk , fk both , type single query.

    select col.column_name,  case when( charindex('pk_' , col.constraint_name) >0 ) 'pk' else 'fk' end type       information_schema.table_constraints tab,      information_schema.constraint_column_usage col       col.constraint_name = tab.constraint_name     , col.table_name = tab.table_name     , (constraint_type = 'primary key'  or constraint_type = 'foreign key')      , col.table_name = 'table_1' 

the query output below:

column_name    type  anotherid        fk code             pk id               pk 

i know it's not that, providing codes fully. interesting me, have wrote lines may helpful others also.

here full code :

    namespace xmlwritingfromdb {     public class program     {         public static string connectionstring = "data source=rahimpc\\sqlexpress;initial catalog=test;integrated security=false;uid=******;pwd=********";         public static sqlconnection conn;         static void main(string[] args)         {              //get table informations              datatable dt = getdataschema("table_1"); // put table name here.             string xmldocument = preparexml(dt);             console.write(xmldocument);             console.readkey();          }          public static datatable getdataschema(string tablename)         {             datatable dt = new datatable();             string query = "select col.column_name, case when( charindex('pk_' , col.constraint_name) >0 ) 'pk' else 'fk' end type " +                 " information_schema.table_constraints tab, information_schema.constraint_column_usage col " +                 " col.constraint_name = tab.constraint_name , col.table_name = tab.table_name " +                 "and (constraint_type = 'primary key'  or constraint_type = 'foreign key') , col.table_name = '"+tablename+"'";              using (conn = new sqlconnection(connectionstring))             {                 conn.open();                 using (sqlcommand sc = new sqlcommand(query, conn))                 {                     using (sqldatareader dr = sc.executereader())                     {                         dt.load(dr);                     }                 }                 conn.close();             }             return dt;         }          public static string preparexml(datatable dt)         {             int pkcount = 0, fkcount = 0;             list<string> lstpk = new list<string>();             list<string> lstfk = new list<string>();             //build data xml             foreach (datarow dr in dt.rows)             {                 if (dr[1].tostring().contains("pk"))                 {                     pkcount++;                     lstpk.add(dr[0].tostring());                 }                 if (dr[1].tostring().contains("fk"))                 {                     fkcount++;                     lstfk.add(dr[0].tostring());                 }             }              list<tablename> lstxml = new list<tablename>();             tablename xml = new tablename();             xml.key = lstpk[lstpk.count() - 1].tostring();              xml.foreignkey = lstfk;             if (pkcount > 1)                 xml.composite = lstpk;              var stringwriter = new system.io.stringwriter();             var serializer = new xmlserializer(xml.gettype());             serializer.serialize(stringwriter, xml);             return stringwriter.tostring();         }     }      [xmlroot("tablename")]     public class tablename     {         public string key { get; set; }          [xmlarray(elementname = "composite")]         [xmlarrayitem("columnname", type = typeof(string))]         public list<string> composite { get; set; }          [xmlarray(elementname = "foreignkey")]         [xmlarrayitem("columnname", type = typeof(string))]         public list<string> foreignkey { get; set; }       } }