Mise à jour en bloc en C#
pour insérer une énorme quantité de données dans une base de données, j'ai utilisé pour collecter toutes les informations d'insertion dans une liste et convertir cette liste en un DataTable. J'insère ensuite cette liste dans une base de données via SqlBulkCopy.
Lorsque j'envoie ma liste générée LiMyList
qui contiennent des informations de toutes les données en vrac que je veux insérer dans la base de données
et passer à ma masse d'insertion de l'opération
InsertData(LiMyList, "MyTable");
Où InsertData
public static void InsertData<T>(List<T> list,string TableName)
{
DataTable dt = new DataTable("MyTable");
clsBulkOperation blk = new clsBulkOperation();
dt = ConvertToDataTable(list);
ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.PerUserRoamingAndLocal);
using (SqlBulkCopy bulkcopy = new SqlBulkCopy(ConfigurationManager.ConnectionStrings["SchoolSoulDataEntitiesForReport"].ConnectionString))
{
bulkcopy.BulkCopyTimeout = 660;
bulkcopy.DestinationTableName = TableName;
bulkcopy.WriteToServer(dt);
}
}
public static DataTable ConvertToDataTable<T>(IList<T> data)
{
PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(typeof(T));
DataTable table = new DataTable();
foreach (PropertyDescriptor prop in properties)
table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);
foreach (T item in data)
{
DataRow row = table.NewRow();
foreach (PropertyDescriptor prop in properties)
row[prop.Name] = prop.GetValue(item) ?? DBNull.Value;
table.Rows.Add(row);
}
return table;
}
maintenant je veux faire une opération de mise à jour, y a-t-il un moyen pour que l'insertion des données soit faite par SqlBulkCopy pour la mise à jour des données vers la base de données à partir de C#.Net
7 réponses
ce que j'ai fait avant est effectuer un insert de masse à partir des données dans une table temp, et puis utiliser une commande ou une procédure stockée pour mettre à jour les données reliant la table temp avec la table destination. La table temp est une étape supplémentaire, mais vous pouvez avoir un gain de performance avec l'insertion en vrac et la mise à jour massive si le nombre de lignes est grand, par rapport à la mise à jour des données ligne par ligne.
Exemple:
public static void UpdateData<T>(List<T> list,string TableName)
{
DataTable dt = new DataTable("MyTable");
dt = ConvertToDataTable(list);
using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SchoolSoulDataEntitiesForReport"].ConnectionString))
{
using (SqlCommand command = new SqlCommand("", conn))
{
try
{
conn.Open();
//Creating temp table on database
command.CommandText = "CREATE TABLE #TmpTable(...)";
command.ExecuteNonQuery();
//Bulk insert into temp table
using (SqlBulkCopy bulkcopy = new SqlBulkCopy(conn))
{
bulkcopy.BulkCopyTimeout = 660;
bulkcopy.DestinationTableName = "#TmpTable";
bulkcopy.WriteToServer(dt);
bulkcopy.Close();
}
// Updating destination table, and dropping temp table
command.CommandTimeout = 300;
command.CommandText = "UPDATE T SET ... FROM " + TableName + " T INNER JOIN #TmpTable Temp ON ...; DROP TABLE #TmpTable;";
command.ExecuteNonQuery();
}
catch (Exception ex)
{
// Handle exception properly
}
finally
{
conn.Close();
}
}
}
}
notez qu'une seule connexion est utilisée pour exécuter l'ensemble l'opération, afin d'être en mesure d'utiliser la table temporaire dans chaque étape, car l'étendue de la table temporaire est par connexion.
D'après mon expérience personnelle, la meilleure façon de gérer cette situation est d'utiliser une procédure stockée avec un Table-Valued Parameter et User-Defined Table Type. Il suffit de configurer le type avec les colonnes de la table de données, et passer dans ladite-table de données comme un paramètre dans la commande SQL.
dans la procédure stockée, vous pouvez rejoindre directement sur une clé unique (si toutes les lignes que vous mettez à jour existent), ou - si vous êtes dans une situation où vous devez faire à la fois des mises à jour et des inserts - utilisez le SQL Merge commande dans la procédure stockée pour gérer les mises à jour et les inserts, selon le cas.
Microsoft a les deux la syntaxe de référence et article avec des exemples pour la Fusion.
pour le morceau .NET, c'est une simple question de définir le type de paramètre comme SqlDbType.Structured et de définir la valeur de ce paramètre à la Table de données qui contient les enregistrements que vous voulez mettre à jour.
cette méthode offre les avantages des deux la clarté et la facilité d'entretien. Bien qu'il puisse y avoir des façons d'offrir des améliorations de performance (comme le laisser tomber dans une table temporaire puis itérer au-dessus de cette table), je pense qu'elles sont compensées par la simplicité de laisser .NET et SQL gérer le transfert de la table et la mise à jour des enregistrements elle-même. K. I. S. S.
essayez les SqlBulkTools disponibles sur Nuget.
Disclaimer: je suis l'auteur de cette bibliothèque.
var bulk = new BulkOperations();
var records = GetRecordsToUpdate();
using (TransactionScope trans = new TransactionScope())
{
using (SqlConnection conn = new SqlConnection(ConfigurationManager
.ConnectionStrings["SqlBulkToolsTest"].ConnectionString))
{
bulk.Setup<MyTable>()
.ForCollection(records)
.WithTable("MyTable")
.AddColumn(x => x.SomeColumn1)
.AddColumn(x => x.SomeColumn2)
.BulkUpdate()
.MatchTargetOn(x => x.Identifier)
.Commit(conn);
}
trans.Complete();
}
seuls les Termes "SomeColumn1" et "SomeColumn2" seront mis à jour. D'autres exemples peuvent être trouvés ici
Je ne suis pas sûr d'avoir compris le point que vous allez archiver...
Si votre question Est sur le remplacement rapide du contenu de la table entière, que je choisirais pour truncate (http://technet.microsoft.com/en-us/library/ms177570.aspx) et insertion en bloc d'une nouvelle portion de données. Mais cette approche ne fonctionnera que si vous n'avez pas de contraintes de clé étrangère.
si vous voulez mettre à jour que la réponse de Guillermo Gutiérrez ci-dessous.
je voudrais insérer de nouvelles valeurs dans une table temporaire et ensuite faire une fusion contre la table de destination, quelque chose comme ceci:
MERGE [DestTable] AS D
USING #SourceTable S
ON D.ID = S.ID
WHEN MATCHED THEN
UPDATE SET ...
WHEN NOT MATCHED
THEN INSERT (...)
VALUES (...);
Vous pouvez essayer de construire une requête qui contient toutes les données. Utiliser un case. Il pourrait ressembler à ceci
update your_table
set some_column = case when id = 1 then 'value of 1'
when id = 5 then 'value of 5'
when id = 7 then 'value of 7'
when id = 9 then 'value of 9'
end
where id in (1,5,7,9)
j'opterais pour une approche TempTable parce que de cette façon vous ne verrouillez rien. Mais si votre logique ne doit être qu'à l'avant et que vous devez utiliser une copie en vrac, j'essaierais une approche Delete / Insert mais dans la même SqlTransaction pour assurer l'intégrité qui serait quelque chose comme ceci:
// ...
dt = ConvertToDataTable(list);
using (SqlConnection cnx = new SqlConnection(myConnectionString))
{
using (SqlTranscation tran = cnx.BeginTransaction())
{
DeleteData(cnx, tran, list);
using (SqlBulkCopy bulkcopy = new SqlBulkCopy(cnx, SqlBulkCopyOptions.Default, tran))
{
bulkcopy.BulkCopyTimeout = 660;
bulkcopy.DestinationTableName = TabelName;
bulkcopy.WriteToServer(dt);
}
tran.Commit();
}
}