public static string InsertRecord(Record rec, string connectionString)
{
string status = "";
try
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand cmd = new SqlCommand("CreateRecord", connection);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@WorkArea", rec.workArea));
cmd.Parameters.Add(new SqlParameter("@Part", rec.part));
cmd.Parameters.Add(new SqlParameter("@Line", rec.line));
cmd.Parameters.Add(new SqlParameter("@State", rec.state));
cmd.Parameters.Add(new SqlParameter("@Error", rec.error));
cmd.Parameters.Add(new SqlParameter("@Timestamp", rec.timestamp));
connection.Open();
cmd.ExecuteNonQuery();
}
}
catch (Exception ex)
{
status = ex.Message;
}
return status;
}
public string InsertRecord(Record rec, out int resultOfInsertion)
{
string status = "";
resultOfInsertion = - 1;
try
{
this.command = new SqlCommand();
this.command.Connection = this.connection;
this.command.CommandType = System.Data.CommandType.StoredProcedure;
this.command.CommandText = "CreateRecord";
SqlParameter returnParameter;
if (this.command.Parameters.Count == 0)
{
this.command.Parameters.Add(new SqlParameter("@WorkArea", rec.workArea));
this.command.Parameters.Add(new SqlParameter("@Part", rec.part));
this.command.Parameters.Add(new SqlParameter("@Line", rec.line));
this.command.Parameters.Add(new SqlParameter("@State", rec.state));
this.command.Parameters.Add(new SqlParameter("@Error", rec.error));
this.command.Parameters.Add(new SqlParameter("@Timestamp", rec.timestamp));
returnParameter = this.command.Parameters.Add("@ReturnVal", System.Data.SqlDbType.Int);
}
else if (this.command.Parameters.Count == 7)
{
this.command.Parameters[0].Value = rec.workArea;
this.command.Parameters[1].Value = rec.part;
this.command.Parameters[2].Value = rec.line;
this.command.Parameters[3].Value = rec.state;
this.command.Parameters[4].Value = rec.error;
this.command.Parameters[5].Value = rec.timestamp;
returnParameter = this.command.Parameters[6];
}
else
{
throw new Exception("Invalid number of parameters");
}
returnParameter.Direction = System.Data.ParameterDirection.ReturnValue;
this.connection.Open();
this.command.ExecuteNonQuery();
this.connection.Close();
resultOfInsertion = (int)returnParameter.Value;
}
catch (Exception ex)
{
status = ex.Message;
if (this.connection.State != System.Data.ConnectionState.Closed)
{
this.connection.Close();
}
}
return status;
}
static void Main(string[] args)
{
string connectionString = "";
bool ok = true;
try
{
connectionString = System.IO.File.ReadAllText("connection.txt");
connectionString = connectionString.Replace(@"\\", @"\");
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
ok = false;
}
if (ok)
{
List<Record> listRec = new List<Record>();
Record rec = new Record();
int element = 0;
XmlTextReader reader;
try
{
reader = new XmlTextReader("testData.xml");
while (reader.Read())
{
switch (reader.NodeType)
{
case XmlNodeType.Element:
if (reader.Name == "record")
{
rec = new Record();
rec.error = "";
}
else if (reader.Name == "workArea")
{
element = 1;
}
else if (reader.Name == "part")
{
element = 2;
}
else if (reader.Name == "line")
{
element = 3;
}
else if (reader.Name == "state")
{
element = 4;
}
else if (reader.Name == "error")
{
element = 5;
}
else if (reader.Name == "timestamp")
{
element = 6;
}
break;
case XmlNodeType.Text:
switch (element)
{
case 1:
rec.workArea = reader.Value;
break;
case 2:
rec.part = reader.Value;
break;
case 3:
rec.line = reader.Value;
break;
case 4:
rec.state = reader.Value;
break;
case 5:
rec.error = reader.Value;
break;
case 6:
rec.timestamp = reader.Value;
break;
}
break;
case XmlNodeType.EndElement:
element = 0;
if (reader.Name == "record")
{
listRec.Add(rec);
}
break;
}
}
reader.Close();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
ok = false;
}
if (ok)
{
DBconnector.DBconnector connector = null;
try
{
connector = new DBconnector.DBconnector(connectionString);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
ok = false;
}
if (ok)
{
string result = "";
int insertion = 0;
int failed = 0;
for (int counter = 0; counter < listRec.Count; counter++)
{
result = connector.InsertRecord(listRec[counter], out insertion);
if (result != "" || insertion != 0)
{
if (result != "")
{
Console.WriteLine(result + "\nFor the element " + listRec[counter].workArea + " " + listRec[counter].part + " " + listRec[counter].line + " " + listRec[counter].state + " " + listRec[counter].error + " " + listRec[counter].timestamp);
}
if (insertion != 0)
{
Console.WriteLine("Insertion Failed" + "\nFor the element " + listRec[counter].workArea + " " + listRec[counter].part + " " + listRec[counter].line + " " + listRec[counter].state + " " + listRec[counter].error + " " + listRec[counter].timestamp);
}
failed++;
}
}
if (failed != 0)
{
Console.WriteLine(failed.ToString() + " parts failed to insert into the database");
}
else
{
Console.WriteLine("All " + listRec.Count + " parts where inserted successfully");
}
}
}
}
}