der ganze Code daoben existiert nicht mehr...
wenn ich den neuen fertig gestellt habe poste ich den ma.
hab gerade nen anderes problem:
sqlCommand.CommandText = "SELECT SCOPE_IDENTITY() from ARTICLES";
long artID = (Int64)sqlCommand.ExecuteScalar();
liefert mir ne InvalidCastException zurück.
"SELECT artID from ARTICLES where artID = SCOPE_IDENTITY()" liefert genau das selbe :-/
danke grover, werd ich mit berücksichtigen
hier wie versprochen der neue Code:
SqlTransaction transaction = sqlConnection.BeginTransaction(IsolationLevel.ReadCommitted, "INSERT");
SqlCommand sqlCommand = sqlConnection.CreateCommand();
sqlCommand.Connection = sqlConnection;
sqlCommand.Transaction = transaction;
try
{
foreach (Article article in insertArticles)
{
strInsertArticles = "INSERT INTO ARTICLES (artProducerIln, artOrdernumber, artModelname, artSystemId, artSystemType, artPrivatbrand) VALUES \n";
strInsertArticles += "(@artProducerIln, @artOrdernumber, @artModelname, @artSystemId, @artSystemType, @artPrivatbrand)\n";
sqlCommand.CommandText = strInsertArticles;
sqlCommand.Parameters.AddWithValue("artProducerIln", article.strProducerIlnP);
sqlCommand.Parameters.AddWithValue("artOrdernumber", article.strOrderNumberP);
sqlCommand.Parameters.AddWithValue("artModelname", article.strModelnameP);
sqlCommand.Parameters.AddWithValue("artSystemId", article.strSystemIDP);
Console.WriteLine("Type: " + article.strSystemTypeP);
sqlCommand.Parameters.AddWithValue("artSystemType", article.strSystemTypeP);
sqlCommand.Parameters.AddWithValue("artPrivatbrand", article.strPrivatBrandP);
sqlCommand.ExecuteNonQuery();
SqlCommand cmd = new SqlCommand("SELECT SCOPE_IDENTITY() from ARTICLES",sqlConnection);
cmd.Transaction = transaction;
long artID = (Int64)cmd.ExecuteScalar();
foreach (ArticleDetail artDetails in article.arlArticleDetailsP)
{
strInsertArticleDetails = "INSERT INTO ARTICLEDETAILS (artID, ardImplementation, ardActuality, ardAttributeMat, ardAttributeForm, ardAttributeWidth, ardAttributeCol, ardAttributeHeel, ardAttributeLining, ardAttributeShank, ardAttributeSole, ardAttributeExp, ardAttributeSize, ardAttributeBreadth, ardAttributeHeight, ardAttributeLength, ardAttributeVolume, ardAttributeWeight, ardAttributeArea, ardAttributeMiscellaneous, ardAttributeUppersmat, ardAttributeConstmethod, ardProducerName, ardProducerOrdernumber, ardDescriptiontext1, ardDescriptiontext2) VALUES \n";
strInsertArticleDetails += "(@artID,@ardImplementation,@ardActuality,@ardMat,@ardForm,@ardWidth,@ardCol,@ardHeel,@ardLining,@ardShank,@ardSole,@ardExp,@ardSize,@ardBreadth,@ardHeight,@ardLength,@ardVolume,@ardWeight,@ardArea,@ardMiscellaneous,@ardUppersMat,@ardConstmethod,@ardProcucername,@ardProducerOrderNumber,@ardDescriptionText1,@ardDescriptionText2)";
SqlCommand detailsCommand = new SqlCommand(strInsertArticleDetails, sqlConnection);
detailsCommand.Transaction = transaction;
detailsCommand.Parameters.AddWithValue("artID",artID);
detailsCommand.Parameters.AddWithValue("ardImplementation", artDetails.strImplementationP);
detailsCommand.Parameters.AddWithValue("ardActuality", artDetails.dtmActualityP);
detailsCommand.Parameters.AddWithValue("ardMat", artDetails.strMaterialP);
detailsCommand.Parameters.AddWithValue("ardForm", artDetails.strFormP);
detailsCommand.Parameters.AddWithValue("ardWidth", artDetails.strWidthP);
detailsCommand.Parameters.AddWithValue("ardCol", artDetails.strColP);
detailsCommand.Parameters.AddWithValue("ardHeel", artDetails.strHeelP);
detailsCommand.Parameters.AddWithValue("ardLining", artDetails.strLiningP);
detailsCommand.Parameters.AddWithValue("ardShank", artDetails.strShankP);
detailsCommand.Parameters.AddWithValue("ardSole", artDetails.strSoleP);
detailsCommand.Parameters.AddWithValue("ardExp", artDetails.strExpP);
detailsCommand.Parameters.AddWithValue("ardSize", artDetails.strSizeP);
detailsCommand.Parameters.AddWithValue("ardBreadth", artDetails.strBreadthP);
detailsCommand.Parameters.AddWithValue("ardHeight", artDetails.strHeightP);
detailsCommand.Parameters.AddWithValue("ardLength", artDetails.strLengthP);
detailsCommand.Parameters.AddWithValue("ardVolume", artDetails.strVolumeP);
detailsCommand.Parameters.AddWithValue("ardWeight", artDetails.strWeightP);
detailsCommand.Parameters.AddWithValue("ardArea", artDetails.strAreaP);
detailsCommand.Parameters.AddWithValue("ardMiscellaneous", artDetails.strMiscellaneousP);
detailsCommand.Parameters.AddWithValue("ardUppersMat", artDetails.strUppersMatP);
detailsCommand.Parameters.AddWithValue("ardConstmethod", artDetails.strConstmethodP);
detailsCommand.Parameters.AddWithValue("ardProcucername", artDetails.strProducerNameP);
detailsCommand.Parameters.AddWithValue("ardProducerOrderNumber", artDetails.strProducerOrderNumberP);
detailsCommand.Parameters.AddWithValue("ardDescriptionText1", artDetails.strDescriptionText1P);
detailsCommand.Parameters.AddWithValue("ardDescriptionText2", artDetails.strDescriptionText2P);
detailsCommand.ExecuteNonQuery();
sqlCommand.CommandText = "SELECT SCOPE_IDENTITY() from ARTICLEDETAILS";
long ardID = (Int64)sqlCommand.ExecuteScalar();
foreach (Size size in artDetails.arlSizesP)
{
strInsertArticleDetailSizes += "INSERT INTO ARTICLESIZES (ardID, arsPrint, arsEan, arsPP, arsSP) VALUES \n";
strInsertArticleDetailSizes += "(@ardID,@arsSize,@arsEan,@arsPP,@arsSP)\n";
SqlCommand sizeCommand = new SqlCommand(strInsertArticleDetails, sqlConnection);
sizeCommand.Transaction = transaction;
sizeCommand.Parameters.AddWithValue("ardID",ardID);
sizeCommand.Parameters.AddWithValue("arsSize",size.strSizeP);
sizeCommand.Parameters.AddWithValue("arsEan", size.strEanP);
sizeCommand.Parameters.AddWithValue("arsPP", size.strPPP);
sizeCommand.Parameters.AddWithValue("arsSP", size.strSPP);
sizeCommand.ExecuteNonQuery();
}
}
}
transaction.Commit();
}
catch (Exception e)
{
Console.WriteLine(e);
try
{
transaction.Rollback();
}
catch (SqlException ex)
{
if (transaction.Connection != null)
{
Console.WriteLine("An exception of type " + ex.GetType() +
" was encountered while attempting to roll back the transaction.\n"+e);
}
}
Console.WriteLine("An exception of type " + e.GetType() +
" was encountered while inserting the data.");
Console.WriteLine("Neither record was written to database.\n"+e);
}
hab mich für ne "normale SQL Transaction" entschieden da hier sowieso nur Microsoft Produkte eingesetzt werden...