Για αρχή σε ευχαριστώ.
Αν είναι 2πλό ποστ παρακαλώ να σβηστεί γιατί μου έβγαλε κάποιο μηνυμα και απο ταχύτητα το ξεπέρασα
Το έκανα έτσι γιατί απλά τόσο ξέρω, τόσο κάνω :)
Πρέπει ο client να στείλει κάποια δεδομένα στον sql server μέσω internet. Σκέφτηκα την λύση του web service.
O client φιτάχνει ένα αρχείο xml για να το πάρει ο server καοι να το κάνει ποστ στον sql.
Ο κώδικας του client είναι αυτός:
private void ExportData()
{
string _sql = "";
XmlDocument xdoc = new XmlDocument();
using (FileStream fs = new FileStream("fintrade.xml", FileMode.Create))
{
using (XmlWriter writer = XmlWriter.Create(fs))
{
writer.WriteStartDocument();
writer.WriteStartElement("FINTRADES");
_sql = "select id, dsrnumber, tradecode, ftrdate, dsrid, iscancelled, totamount " +
"from fintrade " +
"where source = 6 " +
"and iscancelled = 0 " +
"and dsrid in (" + _dsrid + ") " +
"and isnull(glupdated,0) = 0 ";
using (SqlCommand cmd = new SqlCommand(_sql, connection))
{
using (SqlDataReader Reader = cmd.ExecuteReader())
{
using (DataTable dt = new DataTable())
{
dt.Load(Reader);
if (dt.Rows.Count == 0)
{
WriteEventToWindowsLog("Application", "No Data", 2);
return;
}
writer.WriteStartElement("FINDOC");
for (int j = 0; j < dt.Rows.Count; j++)
{
writer.WriteStartElement("FINTRADE");
writer.WriteAttributeString("ID", dt.Rows[j]["ID"].ToString());
writer.WriteAttributeString("DSRNUMBER", dt.Rows[j]["dsrnumber"].ToString());
writer.WriteAttributeString("TRADECODE", dt.Rows[j]["tradecode"].ToString());
writer.WriteAttributeString("FTRDATE", dt.Rows[j]["ftrdate"].ToString());
writer.WriteAttributeString("DSRID", dt.Rows[j]["dsrid"].ToString());
writer.WriteAttributeString("ISCANCELLED", dt.Rows[j]["iscancelled"].ToString());
writer.WriteAttributeString("TOTAMOUNT", dt.Rows[j]["totamount"].ToString());
writer.WriteAttributeString("FRID", _branch.ToString());
writer.WriteEndElement();
}
writer.WriteEndElement();
} //end writer
} //end reader
} // end cmd
_sql = "select l.id STLID, l.iteid, l.primaryqty, isnull(l.prcdisc1,0)prcdisc1, l.linevalue, " +
"l.price, m.code ITECODE, l.ftrid ftrlocalid " +
"from storetradelines l " +
"inner join fintrade f on f.id = l.ftrid " +
"inner join material m on m.id = l.iteid " +
"where f.source = 6 " +
"and dsrid in (" + _dsrid + ") " +
"and isnull(f.glupdated,0) = 0 ";
using (SqlCommand cmd = new SqlCommand(_sql, connection))
{
using (SqlDataReader Reader = cmd.ExecuteReader())
{
using (DataTable dt = new DataTable())
{
dt.Load(Reader);
writer.WriteStartElement("LINES");
for (int j = 0; j < dt.Rows.Count; j++)
{
writer.WriteStartElement("STORETRADELINES");
writer.WriteAttributeString("STLID", dt.Rows[j]["STLID"].ToString());
writer.WriteAttributeString("FTRLOCALID", dt.Rows[j]["ftrlocalid"].ToString());
writer.WriteAttributeString("ITEID", dt.Rows[j]["iteid"].ToString());
writer.WriteAttributeString("PRIMARYQTY", dt.Rows[j]["primaryqty"].ToString());
writer.WriteAttributeString("PRCDISC1", dt.Rows[j]["prcdisc1"].ToString());
writer.WriteAttributeString("LINEVALUE", dt.Rows[j]["linevalue"].ToString());
writer.WriteAttributeString("PRICE", dt.Rows[j]["price"].ToString());
writer.WriteAttributeString("ITECODE", dt.Rows[j]["ITECODE"].ToString());
writer.WriteEndElement();
}
writer.WriteEndElement();
} //end writer
} //end reader
} // end cmd
_sql = "select cs.stlid, cs.iteid, cs.colorcode, " +
"isnull(cs.size1,0)size1, isnull(cs.size2,0)size2, isnull(cs.size3,0)size3, isnull(cs.size4,0)size4, isnull(cs.size5,0)size5, isnull(cs.size6,0)size6, isnull(cs.size7,0)size7, isnull(cs.size8,0)size8, isnull(cs.size9,0)size9, isnull(cs.size10,0)size10, " +
"isnull(cs.size11,0)size11, isnull(cs.size12,0)size12, isnull(cs.size13,0)size13, isnull(cs.size14,0)size14, isnull(cs.size15,0)size15, isnull(cs.size16,0)size16, isnull(cs.size17,0)size17, isnull(cs.size18,0)size18, isnull(cs.size19,0)size19, isnull(cs.size20,0)size20, " +
"m.code " +
"from storecolorsize cs " +
"inner join storetradelines l on l.id = cs.stlid " +
"inner join fintrade f on f.id = l.ftrid " +
"inner join material m on m.id = l.iteid " +
"where f.source = 6 " +
"and dsrid in (" + _dsrid + ") " +
"and isnull(f.glupdated,0) = 0 ";
using (SqlCommand cmd = new SqlCommand(_sql, connection))
{
using (SqlDataReader Reader = cmd.ExecuteReader())
{
using (DataTable dt = new DataTable())
{
dt.Load(Reader);
writer.WriteStartElement("CS");
for (int j = 0; j < dt.Rows.Count; j++)
{
writer.WriteStartElement("STORECOLORSIZE");
writer.WriteAttributeString("STLID", dt.Rows[j]["stlid"].ToString());
writer.WriteAttributeString("ITEID", dt.Rows[j]["iteid"].ToString());
writer.WriteAttributeString("COLORCODE", dt.Rows[j]["colorcode"].ToString());
writer.WriteAttributeString("SIZE1", dt.Rows[j]["size1"].ToString());
writer.WriteAttributeString("SIZE2", dt.Rows[j]["size2"].ToString());
writer.WriteAttributeString("SIZE3", dt.Rows[j]["size3"].ToString());
writer.WriteAttributeString("SIZE4", dt.Rows[j]["size4"].ToString());
writer.WriteAttributeString("SIZE5", dt.Rows[j]["size5"].ToString());
writer.WriteAttributeString("SIZE6", dt.Rows[j]["size6"].ToString());
writer.WriteAttributeString("SIZE7", dt.Rows[j]["size7"].ToString());
writer.WriteAttributeString("SIZE8", dt.Rows[j]["size8"].ToString());
writer.WriteAttributeString("SIZE9", dt.Rows[j]["size9"].ToString());
writer.WriteAttributeString("SIZE10", dt.Rows[j]["size10"].ToString());
writer.WriteAttributeString("SIZE11", dt.Rows[j]["size11"].ToString());
writer.WriteAttributeString("SIZE12", dt.Rows[j]["size12"].ToString());
writer.WriteAttributeString("SIZE13", dt.Rows[j]["size13"].ToString());
writer.WriteAttributeString("SIZE14", dt.Rows[j]["size14"].ToString());
writer.WriteAttributeString("SIZE15", dt.Rows[j]["size15"].ToString());
writer.WriteAttributeString("SIZE16", dt.Rows[j]["size16"].ToString());
writer.WriteAttributeString("SIZE17", dt.Rows[j]["size17"].ToString());
writer.WriteAttributeString("SIZE18", dt.Rows[j]["size18"].ToString());
writer.WriteAttributeString("SIZE19", dt.Rows[j]["size19"].ToString());
writer.WriteAttributeString("SIZE20", dt.Rows[j]["size20"].ToString());
writer.WriteEndElement();
}
writer.WriteEndElement();
} //end writer
} //end reader
} // end cmd
writer.WriteEndElement();
writer.WriteEndDocument();
PostData("fintrade.xml");
} //end writer
} //end fs
}
τώρα ο server έχει αυτό:
private string insData(XmlDocument xdoc)
{
//WriteEventToWindowsLog("Application", "Inner InsData", 2);
string returnStr = "";
try
{
if (this.OpenConnection())
{
//XmlDocument myDoc = new XmlDocument();
//myDoc = xdoc;
//xdoc.LoadXml(xdoc); //Εδώ να διαβάζω το XML
xdoc.Save(_path + "//fintrade.xml");
//xdoc.Save("fintrade.xml");
XmlNodeList elemList = xdoc.GetElementsByTagName("FINTRADES");
int k = elemList.Count;
if (k > 0)
{
using (DataSet ds = new DataSet())
{
ds.ReadXml(_path + "//fintrade.xml");
using (DataTable sourceData = ds.Tables[1])
{
using (SqlBulkCopy bulkCopy =
new SqlBulkCopy(connection))
{
bulkCopy.ColumnMappings.Add("ID", "ID");
bulkCopy.ColumnMappings.Add("DSRNUMBER", "DSRNUMBER");
bulkCopy.ColumnMappings.Add("TRADECODE", "TRADECODE");
bulkCopy.ColumnMappings.Add("FTRDATE", "FTRDATE");
bulkCopy.ColumnMappings.Add("DSRID", "DSRID");
bulkCopy.ColumnMappings.Add("ISCANCELLED", "ISCANCELLED");
bulkCopy.ColumnMappings.Add("TOTAMOUNT", "TOTAMOUNT");
bulkCopy.ColumnMappings.Add("FRID", "FRID");
bulkCopy.DestinationTableName = "PFINTRADE";
bulkCopy.WriteToServer(sourceData);
}
}
using (DataTable sourceData = ds.Tables[3])
{
using (SqlBulkCopy bulkCopy =
new SqlBulkCopy(connection))
{
bulkCopy.ColumnMappings.Add("STLID", "STLID");
bulkCopy.ColumnMappings.Add("FTRLOCALID", "FTRLOCALID");
bulkCopy.ColumnMappings.Add("ITEID", "ITEID");
bulkCopy.ColumnMappings.Add("PRIMARYQTY", "PRIMARYQTY");
bulkCopy.ColumnMappings.Add("PRCDISC1", "PRCDISC1");
bulkCopy.ColumnMappings.Add("LINEVALUE", "LINEVALUE");
bulkCopy.ColumnMappings.Add("PRICE", "PRICE");
bulkCopy.ColumnMappings.Add("ITECODE", "ITECODE");
bulkCopy.DestinationTableName = "PSTORETRADELINES";
bulkCopy.WriteToServer(sourceData);
}
}
using (DataTable sourceData = ds.Tables[5])
{
using (SqlBulkCopy bulkCopy =
new SqlBulkCopy(connection))
{
bulkCopy.ColumnMappings.Add("STLID", "STLID");
bulkCopy.ColumnMappings.Add("ITEID", "ITEID");
bulkCopy.ColumnMappings.Add("COLORCODE", "COLORCODE");
bulkCopy.ColumnMappings.Add("SIZE1", "SIZE1");
bulkCopy.ColumnMappings.Add("SIZE2", "SIZE2");
bulkCopy.ColumnMappings.Add("SIZE3", "SIZE3");
bulkCopy.ColumnMappings.Add("SIZE4", "SIZE4");
bulkCopy.ColumnMappings.Add("SIZE5", "SIZE5");
bulkCopy.ColumnMappings.Add("SIZE6", "SIZE6");
bulkCopy.ColumnMappings.Add("SIZE7", "SIZE7");
bulkCopy.ColumnMappings.Add("SIZE8", "SIZE8");
bulkCopy.ColumnMappings.Add("SIZE9", "SIZE9");
bulkCopy.ColumnMappings.Add("SIZE10", "SIZE10");
bulkCopy.ColumnMappings.Add("SIZE11", "SIZE11");
bulkCopy.ColumnMappings.Add("SIZE12", "SIZE12");
bulkCopy.ColumnMappings.Add("SIZE13", "SIZE13");
bulkCopy.ColumnMappings.Add("SIZE14", "SIZE14");
bulkCopy.ColumnMappings.Add("SIZE15", "SIZE15");
bulkCopy.ColumnMappings.Add("SIZE16", "SIZE16");
bulkCopy.ColumnMappings.Add("SIZE17", "SIZE17");
bulkCopy.ColumnMappings.Add("SIZE18", "SIZE18");
bulkCopy.ColumnMappings.Add("SIZE19", "SIZE19");
bulkCopy.ColumnMappings.Add("SIZE20", "SIZE20");
bulkCopy.DestinationTableName = "PSTORECOLORSIZE";
bulkCopy.WriteToServer(sourceData);
}
}
}
}
returnStr = "Post";
WriteEventToWindowsLog("Application", returnStr, 2);
return returnStr;
}
else
{
returnStr = "Connection Closed!!!";
WriteEventToWindowsLog("Application", returnStr, 1);
return returnStr;
}
}
catch (Exception ex)
{
returnStr = ex.Message;
WriteEventToWindowsLog("Application", returnStr, 1);
return returnStr;
}
}
που λόγο του bulkcopy δεν νομίζω ότι είναι αργό.