I am developing a C# Windows forms application. In that, I am reading data from XML which is coming from a web API and storing that XML data into SQL database. I am able to achieve this. But my only concern is, In Production environment, this application will be in service and there will a lot of data to synchronize from XML to SQL Database. So, can anyone review my code and tell me whether it is an efficient way or not. Any sugggestions are welcome.
Because it's the first time I am working on the API and even in coding, I am a fresher.
I have done all to achieve my requirement. I would like a code review from experts.
public void save_vendor_info()
{
SqlConnection con = new SqlConnection(@"server=localhost;Database=TEST;integrated security=true");
con.Open();
DataTable dt = new DataTable();
XmlDocument doc = new XmlDocument();
doc.Load("https://testdata/api/vendordata");
try
{
XmlNode node = doc.DocumentElement.ChildNodes.Cast<XmlNode>().ToList()[0];
foreach (XmlNode column in node.ChildNodes)
{
dt.Columns.Add(column.Name, typeof(String));
}
XmlNode Filas = doc.DocumentElement;
foreach (XmlNode Fila in Filas.ChildNodes)
{
List<string> Valores = Fila.ChildNodes.Cast<XmlNode>().ToList().Select(x => x.InnerText).ToList();
SqlCommand cmd = new SqlCommand(("IF NOT EXISTS (Select Vendorcode From Vendors where Vendorcode = @Vendorcode) INSERT INTO Vendors VALUES (@Vendorcode, @STCD3, @Name1, @Name2, @Name3, @street1, @street2, @street3, @city1, @city2, @city3, @state1, @state2, @state3, @zip1, @zip2, @zip3, @countrycode, @BussinessUnitId, @VATID, @nationalVATID, @IBAN, @BankAccount, @BankCode)"), con);
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@Vendorcode", Valores[0]);
cmd.Parameters.AddWithValue("@STCD3", Valores[1]);
cmd.Parameters.AddWithValue("@Name1", Valores[2]);
cmd.Parameters.AddWithValue("@Name2", Valores[3]);
cmd.Parameters.AddWithValue("@Name3", Valores[4]);
cmd.Parameters.AddWithValue("@street1", Valores[5]);
cmd.Parameters.AddWithValue("@street2", Valores[6]);
cmd.Parameters.AddWithValue("@street3", Valores[7]);
cmd.Parameters.AddWithValue("@city1", Valores[8]);
cmd.Parameters.AddWithValue("@city2", Valores[9]);
cmd.Parameters.AddWithValue("@city3", Valores[10]);
cmd.Parameters.AddWithValue("@state1", Valores[11]);
cmd.Parameters.AddWithValue("@state2", Valores[12]);
cmd.Parameters.AddWithValue("@state3", Valores[13]);
cmd.Parameters.AddWithValue("@zip1", Valores[14]);
cmd.Parameters.AddWithValue("@zip2", Valores[15]);
cmd.Parameters.AddWithValue("@zip3", Valores[16]);
cmd.Parameters.AddWithValue("@countrycode", Valores[17]);
cmd.Parameters.AddWithValue("@BussinessUnitId", Valores[18]);
cmd.Parameters.AddWithValue("@VATID", Valores[19]);
cmd.Parameters.AddWithValue("@nationalVATID", Valores[20]);
cmd.Parameters.AddWithValue("@IBAN", Valores[21]);
cmd.Parameters.AddWithValue("@BankAccount", Valores[22]);
cmd.Parameters.AddWithValue("@BankCode", Valores[23]);
cmd.ExecuteNonQuery();
}
}
catch (Exception ex)
{
}
}
