How can i Parse and get the Table and Coulmns Names from this Sql Quer

Paul Rajs

4y1.2k.NET

Hi Developers,

I need Parse and get the Table Name and Coulmns Names from the below Sql Query and saved in a Array variable using C# code.

select ce.CNTCT_EVENT_SID,sv.SRVC_PRVIDR_NAME, cet.CNTCT_EVENT_TYPE_CODE,cc.CNTCT_CHNL_NAME,
ce.EVENT_CID,ce.EVENT_IN_OUT_IND,ce.EVENT_DTTM,ce.EVENT_RSN_TXT,ce.ENTITY_PROXY_ID,ce.CSTMR_IND,
ce.INDVDL_PERSNL_DATA_LINK_SID,ce.RCPNT_ID,ce.FIRST_NAME,ce.LAST_NAME,ce.EMAIL_ADDR_ENCR,ce.PHN_NBR,
ce.BILLG_ACCT_NBR
from contact_history.contact_event ce
join contact_history.service_provider sv on ce.SRVC_PRVIDR_SID=sv.SRVC_PRVIDR_SID
join contact_history.contact_channel cc on cc.CNTCT_CHNL_SID=ce.CNTCT_CHNL_SID
join contact_history.contact_event_type cet on cet.CNTCT_EVENT_TYPE_SID=ce.CNTCT_EVENT_TYPE_SID
where CSTMR_IND='Y' and EVENT_IN_OUT_IND ='Outbound

I have tried this from google , but i did not get exepected output. If anyone know this kindly suggest ,e to do this.

Thanks & Regards

Paul.S

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Guest User4y ago
Accepted answer
1. using System;
2. using System.Collections.Generic;
3. using System.Text.RegularExpressions;
5. namespace ConsoleApp1
6. {
7. internal class Program
8. {
9. static void Main(string[] args)
10. {
11. string query = "select ce.CNTCT_EVENT_SID,sv.SRVC_PRVIDR_NAME, cet.CNTCT_EVENT_TYPE_CODE,cc.CNTCT_CHNL_NAME,"
12. + "ce.EVENT_CID,ce.EVENT_IN_OUT_IND,ce.EVENT_DTTM,ce.EVENT_RSN_TXT,ce.ENTITY_PROXY_ID,ce.CSTMR_IND,"
13. + "ce.INDVDL_PERSNL_DATA_LINK_SID,ce.RCPNT_ID,ce.FIRST_NAME,ce.LAST_NAME,ce.EMAIL_ADDR_ENCR,ce.PHN_NBR,"
14. + "ce.BILLG_ACCT_NBR "
15. + "from contact_history.contact_event ce"
16. + "join contact_history.service_provider sv on ce.SRVC_PRVIDR_SID=sv.SRVC_PRVIDR_SID"
17. + "join contact_history.contact_channel cc on cc.CNTCT_CHNL_SID=ce.CNTCT_CHNL_SID"
18. + "join contact_history.contact_event_type cet on cet.CNTCT_EVENT_TYPE_SID=ce.CNTCT_EVENT_TYPE_SID"
19. + "where CSTMR_IND='Y' and EVENT_IN_OUT_IND ='Outbound'";
21. List items = GetFieldsAndTables(query);
23. foreach (string item in items)
24. {
25. Console.WriteLine(item);
26. }
28. Console.ReadKey();
29. }
31. private static List GetFieldsAndTables(string value)
32. {
33. List result = new List();
36. int idx = value.IndexOf("from", StringComparison.InvariantCultureIgnoreCase);
37. string select = value.Substring(0, idx).Remove(0, 7);
38. string[] parts = select.Split(new[] { "," }, StringSplitOptions.RemoveEmptyEntries);
39. foreach (string part in parts)
40. {
41. result.Add(part.Trim());
42. }
44. Regex r = new Regex("(from|join) ([^ ]+)", RegexOptions.IgnoreCase);
45. Match m = r.Match(value);
46. while (m.Success)
47. {
48. for (int i = 2; i <= 2; i++)
49. {
50. Group g = m.Groups[i];
51. CaptureCollection cc = g.Captures;
52. for (int j = 0; j < cc.Count; j++)
53. {
54. Capture c = cc[j];
55. result.Add(c.Value);
56. }
57. }
58. m = m.NextMatch();
59. }
61. return result;
62. }
63. }
64. }

Paul Rajs4y ago

Hi Pritaeas ,

If we add subquery on the Sql query means how we can handle it for the same . for example the below code

select ban,technology_associated_with_billing_address from"

+"(select PSP.BILLG_ACCT_CID as ban,TAB.PHYSL_ADDR_SID,TMO_GROUP_ID,PSTL_CODE,PA_ZIP,concat(LPAD(GEO_FIPS_STATE_CODE, 2, '00'),"

+"LPAD(GEO_FIPS_CNTY_CODE, 3, '000')) as PA_FIPS,concat_ws (',' , 2G_IND, 3G_IND, 4G_IND, 5G_IND ) as technology_associated_with_billing_address,"

+"row_number() over (partition by PSP.BILLG_ACCT_CID order by length(concat_ws (',' , 2G_IND, 3G_IND, 4G_IND, 5G_IND )) desc ) as rank_num3"

+"from (select pa.PHYSL_ADDR_SID,pa.PSTL_CODE,LPAD(pa.PSTL_CODE, 5, '00') as PA_ZIP,GEO_FIPS_STATE_CODE,GEO_FIPS_CNTY_CODE,nc.2G_GSM_1900";

If there is any suggestion kndly tell me ...

Thanks and Regards,

Paul.S

Paul Rajs4y ago

Dear Pritaeas,

Thanking you My Dear Lovable Brother , thanks a lot for your knowledge sharing.

I have tried like this but i did not get the exact output, But your code given the exact iutput.

My Code :

using System;

namespace ConsoleApp3

{

class Program

{

public static void Main(string[] args)

{

string schemaName = "contact_history";

string sqlQuery = "select ce.CNTCT_EVENT_SID,sv.SRVC_PRVIDR_NAME, cet.CNTCT_EVENT_TYPE_CODE,cc.CNTCT_CHNL_NAME,ce.EVENT_CID,ce.EVENT_IN_OUT_IND" +

"ce.EVENT_DTTM,ce.EVENT_RSN_TXT,ce.ENTITY_PROXY_ID,ce.CSTMR_IND,ce.INDVDL_PERSNL_DATA_LINK_SID,ce.RCPNT_ID,ce.FIRST_NAME,ce.LAST_NAME," +

"ce.EMAIL_ADDR_ENCR,ce.PHN_NBR,ce.BILLG_ACCT_NBR " +

"from contact_history.contact_event ce" +

"join contact_history.service_provider sv on ce.SRVC_PRVIDR_SID=sv.SRVC_PRVIDR_SID" +

"join contact_history.contact_channel cc on cc.CNTCT_CHNL_SID=ce.CNTCT_CHNL_SID" +

"join contact_history.contact_event_type cet on cet.CNTCT_EVENT_TYPE_SID=ce.CNTCT_EVENT_TYPE_SID" +

"where CSTMR_IND='Y' and EVENT_IN_OUT_IND ='Outbound'";

string forColumnNames = sqlQuery;

string[] chars = new string[] { "select" };

for (int j = 0; j < chars.Length; j++)

{

if (forColumnNames.Contains(chars[j]))

{

forColumnNames = forColumnNames.Replace(chars[j], "");

}

int index = forColumnNames.LastIndexOf("from");

if (index > 0)

forColumnNames = forColumnNames.Substring(0, index);

string[] columnNames = forColumnNames.Split(',');

int columnLoop = 0;

Console.WriteLine("Column Names \n");

for (columnLoop = 0; columnLoop < columnNames.Length; columnLoop++)

{

columnNames[columnLoop] = columnNames[columnLoop].Trim();

int removeByDot = columnNames[columnLoop].IndexOf(".");

if (removeByDot >= 0)

columnNames[columnLoop] = columnNames[columnLoop].Remove(0, removeByDot);

columnNames[columnLoop] = columnNames[columnLoop].Replace(".", "");

Console.WriteLine(columnNames[columnLoop]);

}

Console.WriteLine();

// fot Table Names

int tableLoop = 0;

string forTableNames = sqlQuery;

int removeByFrom = forTableNames.IndexOf("from");

if (removeByFrom >= 0)

forTableNames = forTableNames.Remove(0, removeByFrom);

string[] removeChar = new string[] { "join" };

for (int j = 0; j < removeChar.Length; j++)

{

if (forTableNames.Contains(removeChar[j]))

{

forTableNames = forTableNames.Replace(removeChar[j], "");

}

string[] tableNames = forTableNames.Split(' ', '=');

Console.WriteLine("Table Names");

for (tableLoop = 0; tableLoop < tableNames.Length; tableLoop++)

{

tableNames[tableLoop] = tableNames[tableLoop].Trim();

if (tableNames[tableLoop].Contains(schemaName + ".") == true)

{

int removeByDot = tableNames[tableLoop].IndexOf(".");

if (removeByDot >= 0)

tableNames[tableLoop] = tableNames[tableLoop].Remove(0, removeByDot);

tableNames[tableLoop] = tableNames[tableLoop].Replace(".", "");

}

else

{

tableNames[tableLoop] = "";

}

Console.WriteLine(tableNames[tableLoop]);

}

Console.ReadLine();

}

With

Thanks & Regards,

PaulRaj.S

Vishal Yelve4y ago

This might help you.

DataTable schema = null;
using (var con = new SqlConnection(connection))
{
using (var schemaCommand = new SqlCommand("SELECT * FROM TestTable;", con))
{
con.Open();
using (var reader = schemaCommand.ExecuteReader(CommandBehavior.SchemaOnly))
{
schema = reader.GetSchemaTable();
}
}
}
foreach (DataRow col in schema.Rows)
{
Console.WriteLine("ColumnName={0}", col.Field("ColumnName"));
}

Paul Rajs4y ago

Hi Pritaeas,

My expected output is as shown below.

Column Names :

CNTCT_EVENT_SID

SRVC_PRVIDR_NAME

tCNTCT_EVENT_TYPE_CODE

CNTCT_CHNL_NAME

EVENT_CID

EVENT_IN_OUT_INDEVENT_DTTM

EVENT_RSN_TXT

ENTITY_PROXY_ID

CSTMR_IND

INDVDL_PERSNL_DATA_LINK_SID

RCPNT_ID

FIRST_NAME

LAST_NAME

EMAIL_ADDR_ENCR

PHN_NBR

BILLG_ACCT_NBR

Tables Names :

contact_event

service_provider

contact_channel

contact_event_type

Thanking you

Regards,

Paul.S

Guest User4y ago

What is your expected output?