generate dynamic string

Question

I have a sp that generates a result looks like below

Userid    orgid

1                 100
1                 200
1                 400
2                 100

I would like to generate a string something like below by looping thru the output result data table using ado.net

Userid 1 should generate

It needs to generate below statement surround by {} and separated by comma(,) if it has multiple orgids.. and orgid( after & ) need to be dynamically assigned in the string based on the table..

{[Org].[org ID].&[100], [Org].[org ID].&[200] ,[Org].[org ID].&[400] }

Userid =2

{[Org].[org ID].&[100] }

How can I dynamically generate this statement?
I can generate the stament if there is only one orgid for user but if you have multiple orgs .. not sure how Ic an concatenate multiple orgids by comma..

AllowedSet = "{[org].[org ID].&[" + orgID + "]}";

I am using  C#

Shahan Ayyub · Answer

In the last code: string i = -1; if(dt!=null) { foreach (DataRow row in dt.Rows) { if (i != row.ItemArray.GetValue(0).ToString()) { i = row.ItemArray.GetValue(0).ToString(); Userid = i; // if integer you need to cast it into integer. var query = dt.Select().Where(c => c.ItemArray.GetValue(0).ToString() == i); string temp = String.Empty; foreach (DataRow s in query) temp += [Org].[org ID].&[ + s.ItemArray.GetValue(1).ToString() + ]; temp = temp.TrimEnd('').Insert(0 {).Insert(temp.Length }); } } }

Shahan Ayyub · Answer

you have this line in your code: temp = s.ItemArray.GetValue(1).ToString().Replace( ).Split(''); this part: .Replace( ) this removes the spaces between comma separated numbers like: 1 100  200 400 and made it like this: 1 100200400 If no spaces doesn't matter.

Shahan Ayyub · Answer

Hi! For your such kind of input: Userid orgid 1 100 200400 2 100 having this output: Userid =1 {[Org].[org ID].&[100] [Org].[org ID].&[200] [Org].[org ID].&[400] } Userid =2 {[Org].[org ID].&[100] } ======================Use this Code============================== if (dt != null) { string i = -1; foreach (DataRow row in dt.Rows) { if (i != row.ItemArray.GetValue(0).ToString()) { i = row.ItemArray.GetValue(0).ToString(); var query = dt.Select().Where(c => c.ItemArray.GetValue(0).ToString() == i); string finalStr = String.Empty; string[] temp; foreach (DataRow s in query) { temp = s.ItemArray.GetValue(1).ToString().Replace( ).Split(''); foreach (String val in temp) { finalStr += [Org].[org ID].&[ + val + ]; } } finalStr = finalStr.TrimEnd('').Insert(0 {).Insert(finalStr.Length }); System.Diagnostics.Debug.Print(finalStr); //for printing purpose } } } =============================================================

j c · Answer

Tannks... Is it posible to create the same string string if I have orgid column that has 100200300400..etc.. ( seperated by comma ) basically.. i would like to create the same string if i have a output Userid orgid 1 100 200400 2 100 I would like to create allowaccess = {[Org].[org ID].&[100] [Org].[org ID].&[200] [Org].[org ID].&[400] } userid = 1 allowaccess = {[Org].[org ID].&[100] } userid = 2 how can I do this? thanks

j c · Answer

thanks.. how about the userid? how can I assign the distinct userid to a string? string userid = ? 1st iteration gives : allowaccess = {[Org].[org ID].&[100] [Org].[org ID].&[200] [Org].[org ID].&[400] } userid = 1 and in 4th iteration: allowaccess = {[Org].[org ID].&[100] } userid = 2

Shahan Ayyub · Answer

Did you mean that in this example: Userid orgid 1 100 1 200 1 400 2 100 it should be come this: Userid orgid 1 100 200 400 2 100 Now in 1st iteration it will become: {[Org].[org ID].&[100] [Org].[org ID].&[200] [Org].[org ID].&[400] } and in 2nd: {[Org].[org ID].&[100] } Then try something like this: string i = -1; if(dt!=null) { foreach (DataRow row in dt.Rows) { if (i != row.ItemArray.GetValue(0).ToString()) { i = row.ItemArray.GetValue(0).ToString(); var query = dt.Select().Where(c => c.ItemArray.GetValue(0).ToString() == i); string temp = String.Empty; foreach (DataRow s in query) temp += [Org].[org ID].&[ + s.ItemArray.GetValue(1).ToString() + ]; temp = temp.TrimEnd('').Insert(0 {).Insert(temp.Length }); } } } the bold line in 1st iteration gives : {[Org].[org ID].&[100] [Org].[org ID].&[200] [Org].[org ID].&[400] } and in 4th iteration: {[Org].[org ID].&[100] }

j c · Answer

SqlConnection conn = new SqlConnection (connString); conn.Open(); SqlCommand cmd = new SqlCommand ( UP_AddNewUsers  conn); cmd.CommandType = CommandType .StoredProcedure; cmd.Parameters.Add( new SqlParameter ( @value  value)); SqlDataAdapter da = new SqlDataAdapter (cmd); DataTable dt = new DataTable (); da.Fill(dt); if (dt != null ) { foreach ( DataRow dr in dt.Rows) { string RoleId = dr[ UserID ].ToString(); string OrgId = dr[ OrgID ].ToString(); I have code above…I am m trying to use your code to make it work.. I also need to assign DISTINCT userid to a string variable? How can I do this? for user Id = 1 RoleId = 1 Allowedset = {[Org].[org ID].&[100] [Org].[org ID].&[200] [Org].[org ID].&[400] }

Crish · Answer

Hi I will give you some code you can refer this code.It will help you to solve the issue of generate dynamic string. foreach(Match m1 in mc1) { temp = new string[i+1]; anchors.CopyTo(temp0); temp[i++]=fileName+#+m1.Value.Substring(5m1.Length-5); anchors=temp; }

Shahan Ayyub · Answer

In this statement: c.ItemArray.GetValue(0).ToString() == 1 the variable 'c' is referring a row in the selection from query the itemArray provides the cells in object array form then GetValue(0) gets the value of index 0 i.e. cells' 0 value which is UserID in your case. ToString gives the string representation of that 0th cells' value (since uptill now it was in object form). and compare it with string value 1. I hope so it will help you to understand.

Shahan Ayyub · Answer

Sorry for the late response. 1 is userID. I have given you an example for userID = 1.

Shahan Ayyub · Answer

Hi! I have prepared a small demo that represents one possiblilty using Linq : DataTable dt = new DataTable(tblA); DataColumn dc2 = new DataColumn(Orgids); DataColumn dc1 = new DataColumn(ID); dt.Columns.Add(dc1); dt.Columns.Add(dc2); dt.Rows.Add(new object[] { 1 100 }); dt.Rows.Add(new object[] { 1 200 }); dt.Rows.Add(new object[] { 1 300 }); dt.Rows.Add(new object[] { 1 400 }); dt.Rows.Add(new object[] { 2 100 }); var query = dt.Select().Where(c => c.ItemArray.GetValue(0).ToString() == 1); string temp = String.Empty; foreach (DataRow s in query) temp+=[Org].[org ID].&[ + s.ItemArray.GetValue(1).ToString() + ]; temp = temp.TrimEnd('').Insert(0{).Insert(temp.Length}); Here your area of interest is this part: var query = dt.Select().Where(c => c.ItemArray.GetValue(0).ToString() == 1); string temp = String.Empty; foreach (DataRow s in query) temp+=[Org].[org ID].&[ + s.ItemArray.GetValue(1).ToString() + ]; temp = temp.TrimEnd('').Insert(0{).Insert(temp.Length});

j c · Answer

Thank you.. var query = dt.Select().Where(c => c.ItemArray.GetValue(0).ToString() == 1); Can you explain to me what 1 is doing in above statement?