Hi All,
I am trying to read the SQL table and update null value with "1" and nonvalue with "0" as you can see in tables. To do this i have design i code that allow me to read the date and write to the text file but it seems there is a problem loop.
I really appricate if you could guys give me a hand on this one.
For example
orginal table
lname |fname |account_num|
--------------------------
John |Smith |
Tony | | 46666000
converted table should be like
lname |fname | account_num
--------------------------
0 | 0 | 1
0 | 1 | 0
using System;
using System.Data;
using System.Data.SqlClient;
using System.IO;
namespace update sql table
{
class OrdinalIndexer
{
static void Main(string[] args)
{
// connection string
// connection string
string connString =
@" server = (local);
database = adventure;
integrated security = sspi";
// query
string sql = @"
select [lname] ,[fname] ,[account_num]
from
customer
";
// create connection
SqlConnection conn = new SqlConnection(connString);
try
{
// Open connection
conn.Open();
// create command
SqlCommand cmd = new SqlCommand(sql, conn);
// create data reader
SqlDataReader rdr = cmd.ExecuteReader();
// print headings
Console.WriteLine("\t{0} {1} {2}",
"lname".PadRight(10),
"fname".PadRight(10),
"account_num".PadRight(10)
);
Console.WriteLine("\t{0} {1} {2}",
"============".PadRight(10),
"============".PadRight(10),
"============".PadRight(10)
);
//=====loop for changing table values to 0 and 1===== here is the problem
for (int row = 0; row < tuplesOnRows->Count; row++)
{
theDataRow.clear();
//s = s + (tuplesOnRows[row].Members[0].Caption + "\t");
if (!(tuplesOnRows->get_Item(row)->get_Members()->get_Item(0)->Caption->Equals("All")))
{
//Console::Write(tuplesOnRows->get_Item(row)->get_Members()->get_Item(0)->Caption->ToString());
//Console::Write("\t");
for (int col = 0; col < tuplesOnColumns->Count; col++)
{
//s = s + (cs.Cells[col, row].FormattedValue + "\t");
if (cs->get_Cells()->get_Item(col, row)->get_FormattedValue()->Equals(""))
//Console::Write(" 0 ");
theDataRow.push_back(0);
else
//Console::Write(" 1 ");
theDataRow.push_back(1);
}
//Console::WriteLine();
//s = s + "\n";
theDataSet.push_back(theDataRow);
index++;
}
}
// open writing file
StreamWriter w1 = new StreamWriter("c:\\2.txt");
// loop through result set
//string line1;
while (rdr.Read())
{
string field1 = rdr[0].ToString().PadLeft(10);
string field2 = rdr[1].ToString().PadLeft(10);
string field3 = rdr[2].ToString().PadLeft(10);
string row = String.Format(" {0} | {1} | {2}", field1, field2, field3);
Console.WriteLine(row);
w1.WriteLine(row);
}
// close reader
rdr.Close();
// flush the file
w1.Flush();
// close the file
w1.Close();
Console.Read();
}
catch (Exception e)
{
Console.WriteLine("Error Occurred: " + e);
}
finally
{
// close connection
Console.Read();
conn.Close();
}
}
}
}