Click here to Skip to main content
14,575,257 members
Rate this:
Please Sign up or sign in to vote.
See more:
i have 2 tables

1. report table (with fields (cols))
col1col2col3col4col5col6col7
"trx_no""trx_date""quantity""rate""quantity * rate""tax""(quantity * rate)+tax"

2. transaction table (with fields (cols))
trx_notrx_datequantityratetax
"00001""01/04/2020"20150540
"00002""05/04/2020"1001101980


************
note : datatables won't change they are read only
how do i manupulate col1, col2, col3, ...... so that at console i get

************
"00001"
"01/04/2020"
20
150
3000
540
3540


What I have tried:

private void TData_Grid(string RMST)
       {
           if (SPDConn.State != ConnectionState.Open)
           {
               SPDConn.Close();
               SPDConn.Open();
           }
           if (SPMConn.State != ConnectionState.Open)
           {
               SPMConn.Close();
               SPMConn.Open();
           }

           string str1 = "select * from dbo.trxdat" + SPGlobal.spyear + " where cmp_code = '" + SPGlobal.spcmp_code + "' and trx_type = '" + RMST + "'";
           SqlCommand mcmd = new SqlCommand(str1, SPDConn);
           SqlDataReader mrdr = mcmd.ExecuteReader();

           string   trx_no;
           DateTime trx_date;
           decimal  quantity, rate, tax;

           while (mrdr.Read())
           {
               //transfering datatable to variables
               trx_no   = mrdr["trx_no"].ToString();
               trx_date = (DateTime)mrdr["trx_date"];
               quantity = (decimal)mrdr["quantity"];
               rate     = (decimal)mrdr["rate"];
               tax      = (decimal)mrdr["tax"];

               Console.WriteLine(col1);
               Console.WriteLine(col3);
               Console.WriteLine(col4);
               Console.WriteLine(col5);
               Console.WriteLine(col6);
               Console.WriteLine(col7);

           }
           mrdr.Close();
       }
Posted
Updated 4-Jun-20 2:51am
v3
Comments
Richard MacCutchan 1-Jun-20 11:19am
   
Did you have a question?
Maciej Los 1-Jun-20 11:26am
   
I took a deeper look at the content and i found it: "how do i manupulate col1, col2, col3, ...... so that at console i get"
Kishore_Patel 1-Jun-20 11:28am
   
at console above module writes :
"trx_no"
"trx_date"
"quantity"
"rate"
"quantity * rate"
"tax"
"(quantity * rate)+tax"

i want values
Richard MacCutchan 1-Jun-20 11:40am
   
Look at your code. You are storing those stings in col1, col2 etc., so that is what your output will be.

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

Well, there's no relationship between tables! If report table defines the rules of displaying data from transaction table, there must be a link to transaction table. Then you have to create class responsible for query creation. For example:

void Main()
{
	DataTable dt = new DataTable();
	string sConn = @"your connection string";
	using(SqlConnection connection = new SqlConnection(sConn))
	{
		connection.Open();
		//get view for transaction table
		string sComm = "SELECT * FROM report WHERE reftable='transactiontbl' AND viewname='view1';";
		using(SqlCommand command= new SqlCommand(sComm, connection))
			using(SqlDataReader reader = command.ExecuteReader())
				dt.Load(reader);
		//convert data row into query
		string qry = QueryHelper.GetQuery(dt.Rows[0]);
		//get result set
		dt = new DataTable();
		using(SqlCommand command= new SqlCommand(qry, connection))
			using(SqlDataReader reader = command.ExecuteReader())
				dt.Load(reader);
	}
	
	foreach(DataRow dr in dt.Rows)
		Console.WriteLine(string.Join(" | ", dr.Table.Columns.Cast<DataColumn>().Select(x=> $"'{x.ColumnName}' = {dr[x]}")));

}

// Define other methods and classes here
public static class QueryHelper
{
	public static string GetQuery(DataRow dr)
	{
		StringBuilder sb = new StringBuilder();
		sb.Append("SELECT ");
		foreach(DataColumn dc in dr.Table.Columns)
		{
			if(dc.ColumnName.StartsWith("col") && !DBNull.Value.Equals(dr[dc]))
			{
				sb.Append($"{dr[dc]} AS {dc.ColumnName}");
				if (dr.Table.Columns.IndexOf(dc) < dr.Table.Columns.Count-1) sb.Append(", ");
			}
		}
		sb.Append($" FROM {dr["reftable"]};");
		return sb.ToString();
	}

}


Result:
'col1' = 00001 | 'col2' = 2020-04-01 00:00:00 | 'col3' = 20 | 'col4' = 150 | 'col5' = 3000 | 'col6' = 540 | 'col7' = 3540
'col1' = 00002 | 'col2' = 2020-04-05 00:00:00 | 'col3' = 100 | 'col4' = 110 | 'col5' = 11000 | 'col6' = 1980 | 'col7' = 12980


Note:
I added link to report table to transaction table. Fields:
- reftable
- viewname
are used to get distinct match.

If you can't do this, you have to be able some way to get distinct data. I'm not in your shoes, so... feel free to change above code to your needs.

Good luck!
   
Comments
Kishore_Patel 4-Jun-20 10:48am
   
Thank you so much for your help, as I am fresher for C# your code is hard to understand and integrate in my code, but it will solve so many of my report generating problems. Thanks a lot.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100