/// <summary>
/// There are no comments for COMP_BIOSUM_VOLS_BY_CURSOR in the schema.
/// </summary>
public virtual void COMP_BIOSUM_VOLS_BY_CURSOR()
{
EntityConnection connection = ((IObjectContextAdapter)this).ObjectContext.Connection as EntityConnection;
bool needClose = false;
if (connection.State != ConnectionState.Open)
{
connection.Open();
needClose = true;
}
try {
using (EntityCommand command = new EntityCommand())
{
if (((IObjectContextAdapter)this).ObjectContext.CommandTimeout.HasValue)
{
command.CommandTimeout = ((IObjectContextAdapter)this).ObjectContext.CommandTimeout.Value;
}
command.CommandType = System.Data.CommandType.StoredProcedure;
command.CommandText = @"FCS_Entities.COMP_BIOSUM_VOLS_BY_CURSOR";
command.Connection = connection;
command.ExecuteNonQuery();
}
}
finally {
if (needClose)
{
connection.Close();
}
}
}
public Int32 checkBollette(Int32 NumBolletta, Int32 QuanteBollette)
{
Int32 retValue = 0;
try
{
using (EntityConnection conn = new EntityConnection(ObjectContext.Connection.ConnectionString))
{
conn.Open();
// Create an EntityCommand.
using (EntityCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "BollettariEntities.checkEsBollette";
cmd.CommandType = CommandType.StoredProcedure;
//retValue = 2;
cmd.Parameters.Clear();
//retValue = 3;
EntityParameter param = new EntityParameter("NumBolletta", System.Data.DbType.Int32);
param.Value = NumBolletta;
cmd.Parameters.Add(param);
//retValue = 4;
param = new EntityParameter("QuanteBollette", System.Data.DbType.Int32);
param.Value = QuanteBollette;
cmd.Parameters.Add(param);
//retValue = 5;
param = new EntityParameter("ret", System.Data.DbType.Int32);
param.Direction = ParameterDirection.Output;
cmd.Parameters.Add(param);
//retValue = 6;
cmd.ExecuteNonQuery();
//retValue = 7;
retValue = Convert.ToInt32(cmd.Parameters[2].Value.ToString());
//retValue = 8;
//retValue = Int32.Parse(cmd.Parameters["@LastNameCount"].Value.ToString());
//using (EntityDataReader rdr =cmd.ExecuteReader(CommandBehavior.SequentialAccess))
//{
// retValue =Convert.ToInt32 ( cmd.Parameters[2]);
//}
}
conn.Close();
}
}
catch (Exception ex)
{
//retValue = 1000;
//throw new ApplicationException("GeneraBollettario " + ex.Message);
}
//The data reader is incompatible with the specified 'BollettariModel.SP_Result'.
//A member of the type, 'ret', does not have a corresponding column in the data reader with the same name.
return(retValue);
}
private object NonQuery(string connectionString, string commandString)
{
var connection = new EntityConnection(connectionString);
connection.Open();
var command = new EntityCommand(commandString, connection);
var result = command.ExecuteNonQuery();
connection.Close();
return(result);
}
public Int32 GetLoginUsrPwd(string Usr, string pwd)
{
Int32 retValue = 0;
try
{
using (EntityConnection conn = new EntityConnection(ObjectContext.Connection.ConnectionString))
{
conn.Open();
// Create an EntityCommand.
using (EntityCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "BollettariEntities.CheckLogin";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Clear();
EntityParameter param = new EntityParameter("user", System.Data.DbType.String);
param.Value = Usr;
cmd.Parameters.Add(param);
param = new EntityParameter("pwd", System.Data.DbType.String);
param.Value = pwd;
cmd.Parameters.Add(param);
param = new EntityParameter("ret", System.Data.DbType.Int32);
param.Direction = ParameterDirection.Output;
cmd.Parameters.Add(param);
cmd.ExecuteNonQuery();
retValue = Convert.ToInt32(cmd.Parameters[2].Value.ToString());
//retValue = Int32.Parse(cmd.Parameters["@LastNameCount"].Value.ToString());
//using (EntityDataReader rdr =cmd.ExecuteReader(CommandBehavior.SequentialAccess))
//{
// retValue =Convert.ToInt32 ( cmd.Parameters[2]);
//}
}
conn.Close();
}
}
catch (Exception ex)
{
throw new ApplicationException("GetLoginUsrPwd " + ex.Message);
}
//The data reader is incompatible with the specified 'BollettariModel.SP_Result'.
//A member of the type, 'ret', does not have a corresponding column in the data reader with the same name.
return(retValue);
}
public void example()
{
using (TransactionScope mainScope = new TransactionScope())
{
// ADO.NET
using (SqlConnection firstConnection = new SqlConnection("First"))
{
firstConnection.Open();
using (SqlCommand firstCommand = new SqlCommand("FirstQueryText", firstConnection))
{
Int32 recordsAffected = firstCommand.ExecuteNonQuery();
}
using (SqlConnection secondConnection = new SqlConnection("Second"))
{
secondConnection.Open();
using (SqlCommand secondCommand = new SqlCommand("SecondQueryText", secondConnection))
{
Int32 secondAffected = secondCommand.ExecuteNonQuery();
}
}
}
mainScope.Complete();
}
//Entity Connection
using (TestEntities database = new TestEntities())
{
Customer cust = new Customer();
cust.FirstName = "Ronald";
cust.LastName = "McDonald";
cust.AccountId = 3;
database.Customers.Add(cust);
database.SaveChanges();
}
using (EntityConnection connection = new EntityConnection("TestEntities"))
{
using (EntityTransaction trans = connection.BeginTransaction(System.Data.IsolationLevel.Serializable))
{
EntityCommand CurrentCommand = new EntityCommand("SOME UPDATE STATEMENT", connection, trans);
connection.Open();
Int32 RecordsAffected = CurrentCommand.ExecuteNonQuery();
trans.Commit();
}
}
SqlConnection myConnection = new SqlConnection("Connection String");
}
public static void ExecuteProcedure(EntityConnection ctx,
string storedProcedureName,
params KeyValuePair <string, object>[] arguments)
{
using (EntityCommand command = ctx.CreateCommand())
{
command.CommandType = CommandType.StoredProcedure;
command.CommandText = storedProcedureName;
foreach (var argument in arguments)
{
command.Parameters.AddWithValue(argument.Key, argument.Value);
}
ctx.Open();
command.ExecuteNonQuery();
ctx.Close();
}
}
/// <summary>
/// Disables full text indexing on backend tables.
/// </summary>
/// <remarks>
/// This method removes the SQL jobs that populate the full text indexes.
/// Full text indexes are not dropped, so you can query any existing full
/// text index data.
/// </remarks>
public void DisableFullTextSearch()
{
using (EntityCommand cmd = (EntityCommand)this.Connection.CreateCommand())
{
bool isConnectionOpenedHere = false;
if (this.Connection.State == ConnectionState.Closed)
{
this.Connection.Open();
isConnectionOpenedHere = true;
}
cmd.CommandText = "AdministrationContext.DisableFullTextSearch";
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = this.OperationTimeout;
cmd.ExecuteNonQuery();
if (isConnectionOpenedHere)
{
this.Connection.Close();
}
}
}
public int DeleteAllTempHSS02003()
{
int result = 1;
try
{
EntityCommand cmd = new EntityCommand("EProcEntities.sp_DeleteAllTempHSS02003", entityConnection);
cmd.CommandType = CommandType.StoredProcedure;
OpenConnection();
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
result = 0;
throw ex;
}
finally
{
CloseConnection();
}
return(result);
}
/// <summary>
/// Enables change history logging feature on the repository.
/// </summary>
/// <param name="changeHistoryFilePath">File path to store change history data. Multiple
/// invocations of this method would add new files to the change history filegroup.</param>
/// <example>This example shows how to enable and disable change history feature. On Vista or
/// Windows Server 2008, you may have to run this sample as an administrator.
/// <code>
///using System;
///using System.Linq;
///using Zentity.Administration;
///using Zentity.Core;
///using System.Threading;
///using System.ServiceProcess;
///
///namespace ZentitySamples
///{
/// public class Program
/// {
/// public static void Main(string[] args)
/// {
/// ZentityContext regularContext = new ZentityContext();
/// AdministrationContext adminContext = new AdministrationContext();
///
/// // Enabling and disabling change history is lengthy operation. Set a sufficiently
/// // large timeout for the context commands.
/// adminContext.CommandTimeout = 300;
///
/// // Disable SQL Server Agent process before enabling or disabling the change history.
/// ServiceController[] scServices;
/// scServices = ServiceController.GetServices();
/// var v = scServices.Where
/// (tuple => tuple.DisplayName == "SQL Server Agent (MSSQLSERVER)").FirstOrDefault();
/// if (v != null && v.Status == ServiceControllerStatus.Running)
/// v.Stop();
/// Thread.Sleep(5000);
///
/// bool isChangeHistoryEnabled =
/// Convert.ToBoolean(regularContext.GetConfiguration("IsChangeHistoryEnabled"));
///
/// Console.WriteLine("IsChangeHistoryEnabled: [{0}]", isChangeHistoryEnabled);
///
/// if (isChangeHistoryEnabled)
/// {
/// Console.WriteLine("Disabling change history...");
/// adminContext.DisableChangeHistory();
/// }
///
/// Console.WriteLine("Enabling change history...");
/// adminContext.EnableChangeHistory(@"C:\ChangeHistory.ndf");
///
/// // Restart the SQL Agent.
/// if (v != null)
/// v.Start();
/// }
/// }
///}
/// </code>
/// </example>
/// <remarks>
/// Zentity change history logging relies on the 'Change Data Capture' feature of SQL
/// Server 2008 and is thus available only on Developer, Enterprise and Enterprise
/// Evaluation editions.
/// <para>
/// This method alters the backend database to create new filegroups and files if they
/// are not already created. Since ALTER DATABASE statement is not allowed within
/// multi-statement transaction, including this method in a TransactionScope, for example,
/// may raise Exceptions.
/// </para>
/// <para>
/// Also, if you are seeing any Transaction deadlock errors, turn off SQL Server Agent
/// service, invoke this method and then turn on the agent again.
/// </para>
/// While enabling change history logging, each major table in Zentity database is enabled
/// for change data capture. SQL Server automatically creates two jobs during this process,
/// 1. to populate capture instances and 2. to periodically clean up the capture instances.
/// Zentity derives its change history data from these capture instances. A background job,
/// ProcessNextLSN, pulls data from the capture instances and populates a separate set of
/// ‘Coupling’ tables. These coupling tables allow us to retain the historical data even
/// after the capture instances are cleaned up. 'Coupling' tables are then mapped to the
/// conceptual model of Zentity Change History Logging. The public API is generated by
/// Entity Framework from the conceptual model. Figure below presents an overall picture.
/// <br/>
/// <img src="ChangeHistory.bmp"/>
/// <para>
/// <b>Data Loss Scenarios</b>
/// <br/>
/// Since Zentity processes CDC capture instances to retrieve the change history
/// information, there is a possibility of data loss if the source capture instance is
/// deleted before it is completely processed. This might happen if Change Data Capture is
/// disabled on the database before all the changesets are processed. To print a list of
/// changesets yet to be processed, execute a query similar to the following.
/// <code language="SQL">
///SELECT [start_lsn], [tran_end_time]
///FROM [cdc].lsn_time_mapping
///WHERE tran_id <> 0x00
///EXCEPT
///SELECT Administration.fn_hexstrtovarbin([Id]) [start_lsn], [DateCreated] [tran_end_time]
///FROM [Administration].ChangeSet
/// </code>
/// Another scenario where there is a possibility of data loss is while altering the schema
/// of Core.Resource table during Zentity Data Model updates. A new capture instance is
/// created for Core.Resource table in response to the schema change. Since, SQL Server
/// allows a maximum of two capture instances per table, an earlier capture instance is
/// dropped if the capture instance count increases two. All data present in the dropped
/// capture instance is thus lost.
/// </para>
/// <para>
/// <b>Backup and Restore Scenario</b>
/// <br/>
/// During backup and restore of Zentity database, all configuration values and capture
/// instances are restored. However, the jobs to populate and cleanup capture instances and
/// 'ProcessNextLSN' are not restored on the target server. So, even though sys.databases
/// and sys.tables show that the database and table are configured for change data capture,
/// no entries are created in the capture instances and hence the Administration tables.
/// To fix this, disable change history logging and then re-enable it again using either
/// the stored procedures Administration.DisableChangeHistory and
/// Administration.EnableChangeHistory or using the methods
/// <see cref="Zentity.Administration.AdministrationContext.DisableChangeHistory"/> and
/// <see cref="Zentity.Administration.AdministrationContext.EnableChangeHistory(string)"/>.
/// </para>
/// </remarks>
public void EnableChangeHistory(string changeHistoryFilePath)
{
if (string.IsNullOrEmpty(changeHistoryFilePath))
{
throw new ArgumentException(
AdministrationResources.InvalidFilePath,
"changeHistoryFilePath");
}
using (EntityCommand cmd = (EntityCommand)this.Connection.CreateCommand())
{
bool isConnectionOpenedHere = false;
if (this.Connection.State == ConnectionState.Closed)
{
this.Connection.Open();
isConnectionOpenedHere = true;
}
cmd.CommandText = "AdministrationContext.EnableChangeHistory";
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = this.OperationTimeout;
EntityParameter param = cmd.CreateParameter();
param.DbType = DbType.String;
param.ParameterName = "ChangeHistoryFilePath";
param.Size = 512;
param.Value = changeHistoryFilePath;
cmd.Parameters.Add(param);
cmd.ExecuteNonQuery();
if (isConnectionOpenedHere)
{
this.Connection.Close();
}
}
}
public int DeleteTempHSS02003(CUSTOM_S02003_TEMP tempHS)
{
int result = 1;
try
{
EntityCommand cmd = new EntityCommand("EProcEntities.sp_DeleteTempHSS02003", entityConnection);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("PONUMBER", DbType.String).Value = tempHS.PONumber;
OpenConnection();
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
result = 0;
throw ex;
}
finally
{
CloseConnection();
}
return(result);
}
//This is not working
//TODO: Fix syntax of updateCommand
private void UpdateUsingEntityCommand(User entity)
{
using (EntityConnection entityConnection = new EntityConnection("name=AppEntities"))
{
entityConnection.Open();
using (EntityTransaction entityTransaction =
entityConnection.BeginTransaction(IsolationLevel.Serializable))
{
string updateCommand = "UPDATE AppEntities.USERS AS U " +
"SET U.FirstName = @FirstName, " +
"U.LastName = @lastName, " +
"U.Username = @userName, " +
"U.City = @city " +
"WHERE U.Id = @Id";
using (EntityCommand command =
new EntityCommand(updateCommand, entityConnection, entityTransaction))
{
EntityParameter firstName = new EntityParameter()
{
ParameterName = "firstName",
Value = entity.FirstName
};
EntityParameter lastName = new EntityParameter()
{
ParameterName = "lastName",
Value = entity.LastName
};
EntityParameter username = new EntityParameter()
{
ParameterName = "username",
Value = entity.Username
};
EntityParameter city = new EntityParameter()
{
ParameterName = "city",
Value = entity.City
};
EntityParameter id = new EntityParameter()
{
ParameterName = "Id",
Value = entity.Id
};
command.Parameters.Add(firstName);
command.Parameters.Add(lastName);
command.Parameters.Add(username);
command.Parameters.Add(city);
command.Parameters.Add(id);
try
{
command.ExecuteNonQuery();
entityTransaction.Commit();
}
catch (Exception ex)
{
entityTransaction.Rollback();
}
}
}
}
}
public Int32 EmettiBollettario(Int32 idBollettario, Int32 cComune, Int32 cCimitero, Int32 cCategoria, string decorrenza, string cOP)
{
Int32 retValue = 0;
try
{
using (EntityConnection conn = new EntityConnection(ObjectContext.Connection.ConnectionString))
{
conn.Open();
// Create an EntityCommand.
using (EntityCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "BollettariEntities.EmetteBoll";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Clear();
EntityParameter param = new EntityParameter("idBollettario", System.Data.DbType.Int32);
param.Value = idBollettario;
cmd.Parameters.Add(param);
param = new EntityParameter("cComune", System.Data.DbType.Int32);
param.Value = cComune;
cmd.Parameters.Add(param);
param = new EntityParameter("cCimitero", System.Data.DbType.Int32);
param.Value = cCimitero;
cmd.Parameters.Add(param);
param = new EntityParameter("cCategoria", System.Data.DbType.Int32);
param.Value = cCategoria;
cmd.Parameters.Add(param);
param = new EntityParameter("decorrenza", System.Data.DbType.String);
param.Value = decorrenza;
cmd.Parameters.Add(param);
param = new EntityParameter("cOP", System.Data.DbType.String);
param.Value = cOP;
cmd.Parameters.Add(param);
param = new EntityParameter("idMAXCimitero", System.Data.DbType.Int32);
param.Direction = ParameterDirection.Output;
cmd.Parameters.Add(param);
param = new EntityParameter("ret", System.Data.DbType.Int32);
param.Direction = ParameterDirection.Output;
cmd.Parameters.Add(param);
cmd.ExecuteNonQuery();
retValue = Convert.ToInt32(cmd.Parameters[6].Value.ToString());
//retValue = Int32.Parse(cmd.Parameters["@LastNameCount"].Value.ToString());
//using (EntityDataReader rdr =cmd.ExecuteReader(CommandBehavior.SequentialAccess))
//{
// retValue =Convert.ToInt32 ( cmd.Parameters[2]);
//}
}
conn.Close();
}
}
catch (Exception ex)
{
throw new ApplicationException("EmettiBollettario " + ex.Message);
}
//The data reader is incompatible with the specified 'BollettariModel.SP_Result'.
//A member of the type, 'ret', does not have a corresponding column in the data reader with the same name.
return(retValue);
}