public static void Save(string connectionString, string modelKey, IEnumerable<nHydrateDbObject> list, System.Data.SqlClient.SqlTransaction transaction)
{
System.Data.SqlClient.SqlConnection conn = null;
if (transaction == null)
{
conn = new System.Data.SqlClient.SqlConnection(connectionString);
conn.Open();
}
else
{
conn = transaction.Connection;
}
try
{
//Create the table if need be
using (var command3 = new SqlCommand("if not exists(select * from sys.objects where name = '__nhydrateobjects'and type = 'U')" + Environment.NewLine +
"CREATE TABLE [dbo].[__nhydrateobjects]" +
"([rowid] [bigint] IDENTITY(1,1) NOT NULL," +
"[id] [uniqueidentifier] NULL," +
"[name] [varchar](500) NOT NULL," +
"[type] [varchar](10) NOT NULL," +
"[schema] [varchar](500) NULL," +
"[CreatedDate] [datetime] NOT NULL," +
"[ModifiedDate] [datetime] NOT NULL," +
"[Hash] [varchar](32) NULL," +
"[ModelKey] [uniqueidentifier] NOT NULL)", conn))
{
command3.Transaction = transaction;
command3.ExecuteNonQuery();
}
//Save items to the table
foreach (var item in list)
{
var command = new SqlCommand("if exists(select * from [__nhydrateobjects] where [rowid] = @rowid) " +
"update [__nhydrateobjects] set [id] = @id, [name] = @name, [type] = @type, [schema] = @schema, [CreatedDate] = @CreatedDate, [ModifiedDate] = @ModifiedDate, [Hash] = @Hash, [ModelKey] = @ModelKey where [rowid] = @rowid " +
"else " +
"insert into [__nhydrateobjects] ([id], [name], [type], [schema], [CreatedDate], [ModifiedDate], [Hash], [ModelKey]) values (@id, @name, @type, @schema, @CreatedDate, @ModifiedDate, @Hash, @ModelKey)", conn);
command.Transaction = transaction;
command.Parameters.Add(new SqlParameter() { DbType = DbType.Guid, Value = (item.id == null ? System.DBNull.Value : (object)item.id), ParameterName = "@id", IsNullable = true });
command.Parameters.Add(new SqlParameter() { DbType = DbType.Int64, Value = item.rowid, ParameterName = "@rowid", IsNullable = false });
command.Parameters.Add(new SqlParameter() { DbType = DbType.String, Value = item.name, ParameterName = "@name", IsNullable = false });
command.Parameters.Add(new SqlParameter() { DbType = DbType.String, Value = item.type, ParameterName = "@type", IsNullable = false });
command.Parameters.Add(new SqlParameter() { DbType = DbType.String, Value = (item.schema == null ? System.DBNull.Value : (object)item.schema), ParameterName = "@schema", IsNullable = true });
command.Parameters.Add(new SqlParameter() { DbType = DbType.DateTime, Value = item.CreatedDate, ParameterName = "@CreatedDate", IsNullable = false });
command.Parameters.Add(new SqlParameter() { DbType = DbType.DateTime, Value = item.ModifiedDate, ParameterName = "@ModifiedDate", IsNullable = false });
command.Parameters.Add(new SqlParameter() { DbType = DbType.String, Value = item.Hash, ParameterName = "@Hash", IsNullable = false });
command.Parameters.Add(new SqlParameter() { DbType = DbType.Guid, Value = item.ModelKey, ParameterName = "@ModelKey", IsNullable = false });
command.ExecuteNonQuery();
}
}
catch (Exception ex)
{
throw;
}
finally
{
if (transaction == null && conn != null)
conn.Close();
}
}
