How to read from a SQL table and write to another table using C# -Please help

EDN Admin · Mar 13, 2012

Hi all,
I am new to C# and SQL and learning it fast. I have written the follwing code which is reading some data in a table which has only 4 columns. I want to read the table named "MyTable" and write exactly the same data on to another table called "MyTable_Final".
I am writing this simple code to understand the SQL transactions as well as the use of C# construcotrs and instantiation of objects and types. When I learn this I will introduce more calculations to the code and do modifications to the MyTable and the outcome.
So plese help me and correct the following code.
My code does not have any compile errors. But it seems to lack so many things I think. here is the code.
using System; 
using System.Collections.Generic; 
using System.Linq; 
using System.Text; 
using System.Data.SqlClient; 
using System.Data;
namespace GroupCreator 
{
public class GroupCreator 
{ 
public IList<Group> GroupList; 
public GroupCreator CallGroup() 
{ 
var sample = new GroupCreator(); 
SqlCommand myCommand = new SqlCommand(); 
myCommand.Connection = new SqlConnection 
("Server=D-701-102-Narada\SQLEXPRESS;" 
+ "Initial Catalog=Numbers;" 
+ "Persist Security Info = true; Integrated Security = SSPI;"); 
{ 
myCommand.Connection.Open(); 
var trns = myCommand.Connection.BeginTransaction(IsolationLevel.Serializable); 
{ 
var cmd_all_rows = myCommand.Connection.CreateCommand(); 
{ 
cmd_all_rows .Transaction = trns; 
cmd_all_rows .CommandText = "SELECT * FROM [dbo].[MyTable] ORDER BY ASC, [J] ASC, [K] ASC"; 
var rdr_all = cmd_all_rows .ExecuteReader(CommandBehavior.SingleResult); 
{ 
while (rdr_all.Read()) 
{ 
var number = new Numbers(rdr_all.GetDouble(1), rdr_all.GetDouble(2), rdr_all.GetDouble(3)); 
var value = new Value(rdr_all.GetDouble(4)); 
sample.GroupList.Add(new Group(number, value)); 
} 
rdr_all.Close(); 
} 
} 
return sample; 
} 
} 
}
public void DBCreator() 
{ 
var connection = new SqlConnection(); 
connection.ConnectionString = "Server=D-701-102-Narada\SQLEXPRESS;" 
+ "Initial Catalog=Numbers;" 
+ "Persist Security Info = true; Integrated Security = SSPI;"; 
using (var cmd = connection.CreateCommand())// intializing an SQL command object for transactions 
{ 
connection.Open(); 
cmd.CommandText = "INSERT INTO MyTable_Final (I, J, K,FinalValue) VALUES (@I,@J,@K,@FinalValue) ";
foreach (var group in this.GroupList) 
try 
{
SqlCommand cmdIns = new SqlCommand(cmd.CommandText, connection); 
cmdIns.Parameters.AddWithValue("@I", group.number); 
cmdIns.Parameters.AddWithValue("@J", group.number); 
cmdIns.Parameters.AddWithValue("@K", group.number); 
cmdIns.Parameters.AddWithValue("@FinalValue", group.value); 
cmdIns.ExecuteNonQuery(); 
cmdIns.Dispose(); 
cmdIns = null;
} 
catch (Exception ex) 
{ 
throw new Exception(ex.ToString(), ex); 
} 
finally 
{ 
} 
connection.Close(); 
} 
} 
public class Numbers 
{ 
double I; 
double J; 
double K;
public Numbers(double i, double j, double k) 
{ 
I = i; 
J = j; 
K = k; 
} 
}
public class Value 
{ 
double FinalValue; 
public Value(double finalvalue) 
{ 
FinalValue = finalvalue; 
} 
} 
public class Group 
{ 
public Numbers number; 
public Value value; 
public Group(Numbers x, Value y) 
{ 
number = x; 
value = y; 
}
} 
} 
}
using System; 
using System.Collections.Generic; 
using System.Linq; 
using System.Text;
namespace GroupCreator 
{ 
class Program 
{ 
static void Main(string[] args) 
{ 
GroupCreator creator = new GroupCreator(); 
creator.CallGroup(); 
creator.DBCreator(); 
} 
} 
} 

There seems to be a few problems in this code. For example when I run this I get an error that states "Object reference not set to an instance of an object" on the foreach statement inside the DBCreator method.
And also, although I expect to write the I,J,K values seperately they do not appear in the inteli-sense menu down. For example I need the statement mailto: "cmdIns.Parameters.AddWithValue("@I ", group.number);" to be like mailto: "cmdIns.Parameters.AddWithValue("@I ",
group.number.i); But I do not get the number.i in the intelisense menu. Is there a problem with the way I initiated the constructor for the Numbers class??? Please help me to understand the constructor as well.
I am sure there are serious errors may be in the way I have declared variables, constructors, etc in this code even though there are no compile errors.
Can you please have a look at the code and try to correct this. If you can correct this that will be great for my future learning and I can build up my C# knowledge and improve from there.
All your tips and hints and lessons and corrections are higly appreciated.
Many thanks in advance!!
Please help me!
Narada.
 
 
 

View the full article

How to read from a SQL table and write to another table using C# -Please help

EDN Admin

Well-known member

Similar threads