public bool CopyParameter(ref DbParameter param)
{
switch (GetDataType().Name)
{
case "String":
param.DbType = DbType.String;
break;
case "Int32":
case "UInt32":
param.DbType = DbType.Int32;
break;
case "Int16":
case "UInt16":
param.DbType = DbType.Int16;
break;
case "SByte":
case "Byte":
param.DbType = DbType.SByte;
break;
default:
return false;
}
param.Value = GetData();
return true;
}
/// <summary>
/// 为更新一条数据准备参数
/// </summary>
internal static void PrepareUpdateCommand(ZSystem.Model.t_Product model, out StringBuilder strSql, out DbParameter[] cmdParms)
{
strSql = new StringBuilder();
strSql.Append("UPDATE t_Product SET ");
strSql.Append("sProductName=@in_sProductName,");
strSql.Append("iPrice=@in_iPrice,");
strSql.Append("Memo=@in_Memo,");
strSql.Append("iDiscount=@in_iDiscount,");
strSql.Append("IsEnable=@in_IsEnable,");
strSql.Append("Begindate=@in_Begindate,");
strSql.Append("Enddate=@in_Enddate,");
strSql.Append("LastUpdate=@in_LastUpdate");
strSql.Append(" WHERE KeyID=@in_KeyID");
cmdParms = new DbParameter[]{
dbHelper.CreateInDbParameter("@in_sProductName", DbType.String, model.sProductName),
dbHelper.CreateInDbParameter("@in_iPrice", DbType.Double, model.iPrice),
dbHelper.CreateInDbParameter("@in_Memo", DbType.String, model.Memo),
dbHelper.CreateInDbParameter("@in_iDiscount", DbType.Int32, model.iDiscount),
dbHelper.CreateInDbParameter("@in_IsEnable", DbType.Boolean, model.IsEnable),
dbHelper.CreateInDbParameter("@in_Begindate", DbType.DateTime, model.Begindate),
dbHelper.CreateInDbParameter("@in_Enddate", DbType.DateTime, model.Enddate),
dbHelper.CreateInDbParameter("@in_LastUpdate", DbType.DateTime, model.LastUpdate),
dbHelper.CreateInDbParameter("@in_KeyID", DbType.Guid, model.KeyID)};
}
internal string DeleteProfilo(int idObj)
{
sqlop = new SqlOperations();
sqlop.databaseConnection = ConfigurationManager.ConnectionStrings["cs"].ToString();
DbParameter[] dbp = new DbParameter[1];
dbp[0] = new SqlParameter();
dbp[0].ParameterName = "IDProfilo";
dbp[0].DbType = DbType.Int32;
dbp[0].Direction = ParameterDirection.Input;
dbp[0].Value = idObj;
SqlCommandObject sco = new SqlCommandObject();
sco.SPName = "Utenti.SP_Profilo_Delete";
sco.SPParams = dbp;
DataTable dt;
string proc = sqlop.ExecuteProcedure(sco, out dt);
if (proc != string.Empty)
return proc;
return "";
}
protected override void InitializeParameter(object item, DbParameter p, Type type)
{
var typeCode = Type.GetTypeCode(type);
switch (typeCode)
{
case TypeCode.SByte:
p.DbType = DbType.Byte;
item = (byte)item;
break;
case TypeCode.UInt16:
case TypeCode.UInt32:
case TypeCode.Int64:
case TypeCode.UInt64:
p.DbType = DbType.Int32;
item = Convert.ToInt32(item);
break;
default:
if (item is Guid)
{
p.DbType = DbType.Binary;
item = ((Guid)item).ToByteArray();
}
break;
}
p.Value = item;
}
/// <summary>
/// 为更新一条数据准备参数
/// </summary>
internal static void PrepareUpdateCommand(ZSystem.Model.t_Client model, out StringBuilder strSql, out DbParameter[] cmdParms)
{
strSql = new StringBuilder();
strSql.Append("UPDATE t_Client SET ");
strSql.Append("sClientName=@in_sClientName,");
strSql.Append("sAddress=@in_sAddress,");
strSql.Append("sMobiPhone=@in_sMobiPhone,");
strSql.Append("sTelPhone=@in_sTelPhone,");
strSql.Append("CreateUserID=@in_CreateUserID,");
strSql.Append("IsEnable=@in_IsEnable,");
strSql.Append("LastUpdate=@in_LastUpdate,");
strSql.Append("Memo=@in_Memo");
strSql.Append(" WHERE KeyID=@in_KeyID");
cmdParms = new DbParameter[]{
dbHelper.CreateInDbParameter("@in_sClientName", DbType.String, model.sClientName),
dbHelper.CreateInDbParameter("@in_sAddress", DbType.String, model.sAddress),
dbHelper.CreateInDbParameter("@in_sMobiPhone", DbType.String, model.sMobiPhone),
dbHelper.CreateInDbParameter("@in_sTelPhone", DbType.String, model.sTelPhone),
dbHelper.CreateInDbParameter("@in_CreateUserID", DbType.String, model.CreateUserID),
dbHelper.CreateInDbParameter("@in_Memo", DbType.String, model.Memo),
dbHelper.CreateInDbParameter("@in_KeyID", DbType.String, model.KeyID),
dbHelper.CreateInDbParameter("@in_IsEnable", DbType.Boolean, model.IsEnable),
dbHelper.CreateInDbParameter("@in_LastUpdate", DbType.String, model.LastUpdate)};
}
internal DataTable GetAllDealerRelazioni(string codiceDealer, string ragioneSocialeDealer,int idTipoDealer)
{
sqlop = new SqlOperations();
sqlop.databaseConnection = ConfigurationManager.ConnectionStrings["cs"].ToString();
DbParameter[] dbp = new DbParameter[3];
dbp[0] = new SqlParameter();
dbp[0].ParameterName = "CodiceDealer";
dbp[0].DbType = DbType.String;
dbp[0].Direction = ParameterDirection.Input;
dbp[0].Value = codiceDealer;
dbp[1] = new SqlParameter();
dbp[1].ParameterName = "RagioneSocialeDealer";
dbp[1].DbType = DbType.String;
dbp[1].Direction = ParameterDirection.Input;
dbp[1].Value = ragioneSocialeDealer;
dbp[2] = new SqlParameter();
dbp[2].ParameterName = "IdTipoDealer";
dbp[2].DbType = DbType.Int32;
dbp[2].Direction = ParameterDirection.Input;
dbp[2].Value = idTipoDealer;
SqlCommandObject sco = new SqlCommandObject();
sco.SPName = "Dealer.SP_RelazioniDealer_GetDealer";
sco.SPParams = dbp;
DataTable dt;
sqlop.ExecuteProcedure(sco, out dt);
return dt;
}
// Execute stored procedure
public DataTable sprc_exec(string sprcName, DbParameter[] sprcPrms)
{
var dtbl = new DataTable();
try
{
var tsqlConn = new SqlConnection(ConfigurationManager.AppSettings.Get("conn"));
var tsqlCmnd = new SqlCommand
{
Connection = tsqlConn,
CommandText = sprcName,
CommandType = CommandType.StoredProcedure
};
foreach (var pram in sprcPrms)
{
tsqlCmnd.Parameters.Add(pram);
}
var daData = new SqlDataAdapter { SelectCommand = tsqlCmnd };
daData.Fill(dtbl);
tsqlCmnd.Parameters.Clear();
}
catch (Exception ex)
{
throw ex;
}
return dtbl;
}
protected override void ApplyParameterInfo(DbParameter parameter, DataRow datarow, StatementType statementType, bool whereClause)
{
SqlParameter parameter2 = (SqlParameter) parameter;
object obj3 = datarow[SchemaTableColumn.ProviderType];
parameter2.SqlDbType = (SqlDbType) obj3;
parameter2.Offset = 0;
if ((parameter2.SqlDbType == SqlDbType.Udt) && !parameter2.SourceColumnNullMapping)
{
parameter2.UdtTypeName = datarow["DataTypeName"] as string;
}
else
{
parameter2.UdtTypeName = string.Empty;
}
object obj2 = datarow[SchemaTableColumn.NumericPrecision];
if (DBNull.Value != obj2)
{
byte num2 = (byte) ((short) obj2);
parameter2.PrecisionInternal = (0xff != num2) ? num2 : ((byte) 0);
}
obj2 = datarow[SchemaTableColumn.NumericScale];
if (DBNull.Value != obj2)
{
byte num = (byte) ((short) obj2);
parameter2.ScaleInternal = (0xff != num) ? num : ((byte) 0);
}
}
internal string DeleteRelazione(int idRelazione, int idTipoDealer)
{
sqlop = new SqlOperations();
sqlop.databaseConnection = ConfigurationManager.ConnectionStrings["cs"].ToString();
DbParameter[] dbp = new DbParameter[2];
dbp[0] = new SqlParameter();
dbp[0].ParameterName = "IDRelazione";
dbp[0].DbType = DbType.Int32;
dbp[0].Direction = ParameterDirection.Input;
dbp[0].Value = idRelazione;
dbp[1] = new SqlParameter();
dbp[1].ParameterName = "IDTipoDealer";
dbp[1].DbType = DbType.Int32;
dbp[1].Direction = ParameterDirection.Input;
dbp[1].Value = idTipoDealer;
SqlCommandObject sco = new SqlCommandObject();
sco.SPName = "Dealer.SP_RelazioniDealer_Delete";
sco.SPParams = dbp;
DataTable dt;
string proc = sqlop.ExecuteProcedure(sco, out dt);
if (proc != string.Empty)
return proc;
return "";
}
public List<PruebaBE> ConsultarPruebas(int idPrueba)
{
List<PruebaBE> pruebas = new List<PruebaBE>();
try
{
string nameSP = "ConsultarPruebas";
BaseDatos db = new BaseDatos();
db.Conectar();
db.CrearComandoSP(nameSP);
DbParameter[] parametros = new DbParameter[3];
parametros[0] = db.Comando.CreateParameter();
parametros[0].ParameterName = "vrIdPrueba";
parametros[0].Value = idPrueba;
parametros[0].Direction = ParameterDirection.Input;
db.Comando.Parameters.Add(parametros[0]);
parametros[1] = db.Comando.CreateParameter();
parametros[1].ParameterName = "vrCodResult";
parametros[1].Value = 0;
parametros[1].Direction = ParameterDirection.Output;
db.Comando.Parameters.Add(parametros[1]);
parametros[2] = db.Comando.CreateParameter();
parametros[2].ParameterName = "vrDescResult";
parametros[2].Value = "";
parametros[2].Direction = ParameterDirection.Output;
parametros[2].Size = 200;
parametros[2].DbType = DbType.String;
db.Comando.Parameters.Add(parametros[2]);
DbDataReader datos = db.EjecutarConsulta();
PruebaBE p = null;
while (datos.Read())
{
try
{
p = new PruebaBE();
p.IdPrueba = Int32.Parse(datos.GetValue(0).ToString());
p.Descripción = datos.GetString(1);
p.Fecha = datos.GetDateTime(2);
pruebas.Add(p);
}
catch (InvalidCastException ex)
{
throw new Exception("Los tipos no coinciden.", ex);
}
catch (DataException ex)
{
throw new Exception("Error de ADO.NET.", ex);
}
}
datos.Close();
db.Desconectar();
}
catch (Exception ex)
{
throw new Exception("Error al acceder a la base de datos para obtener los PruebaBEs.");
}
return pruebas;
}
/// <summary>
/// 为更新一条数据准备参数
/// </summary>
internal static void PrepareUpdateCommand(ZSystem.Model.t_IVRControl model, out StringBuilder strSql, out DbParameter[] cmdParms)
{
strSql = new StringBuilder();
strSql.Append("UPDATE t_IVRControl SET ");
strSql.Append("sFileName=@in_sFileName,");
strSql.Append("iKeyNum=@in_iKeyNum,");
strSql.Append("SubID=@in_SubID,");
strSql.Append("sIVRName=@in_sIVRName,");
strSql.Append("IsEnable=@in_IsEnable,");
strSql.Append("BeginDate=@in_BeginDate,");
strSql.Append("EndDate=@in_EndDate,");
strSql.Append("LastUpdate=@in_LastUpdate");
strSql.Append(" WHERE KeyID=@in_KeyID");
cmdParms = new DbParameter[]{
dbHelper.CreateInDbParameter("@in_sFileName", DbType.String, model.sFileName),
dbHelper.CreateInDbParameter("@in_iKeyNum", DbType.Int32, model.iKeyNum),
dbHelper.CreateInDbParameter("@in_SubID", DbType.String, model.SubID),
dbHelper.CreateInDbParameter("@in_sIVRName", DbType.String, model.sIVRName),
dbHelper.CreateInDbParameter("@in_IsEnable", DbType.Boolean, model.IsEnable),
dbHelper.CreateInDbParameter("@in_BeginDate", DbType.DateTime, model.BeginDate),
dbHelper.CreateInDbParameter("@in_EndDate", DbType.DateTime, model.EndDate),
dbHelper.CreateInDbParameter("@in_LastUpdate", DbType.DateTime, model.LastUpdate),
dbHelper.CreateInDbParameter("@in_KeyID", DbType.String, model.KeyID)};
}
private static SDC.DbCommand BuildCommand(string sproc, List <ParamStruct> list)
{
SDC.DbProviderFactory factory = SDC.DbProviderFactories.GetFactory(Properties.Settings.Default.provider);
SDC.DbCommand comm = factory.CreateCommand();
comm.CommandText = sproc;
comm.CommandType = System.Data.CommandType.StoredProcedure;
SDC.DbParameter param = factory.CreateParameter();
foreach (ParamStruct item in list)
{
param.ParameterName = item.paramId;
param.Value = item.paramValue;
param.DbType = item.paramType;
param.Size = item.paramLength;
param.Direction = item.paramDirection;
comm.Parameters.Add(param);
param = factory.CreateParameter();
}
return(comm);
}
/// <summary>
/// Insert Query
/// </summary>
/// <param name="query">
/// </param>
/// <param name="sqlParameter">
/// </param>
/// <returns>
/// The <see cref="bool"/>.
/// </returns>
public bool ExecuteInsertQuery(string query, DbParameter[] sqlParameter)
{
using (var cmd = new SqlCommand())
{
try
{
cmd.Connection = this.OpenConnection();
cmd.CommandText = query;
cmd.Parameters.AddRange(sqlParameter);
this.sqlDataAdapter.InsertCommand = cmd;
cmd.ExecuteNonQuery();
}
catch (SqlException exception)
{
throw new Exception(
"Error - ExecuteInsertQuery - Query:" + query + "\nException" + exception.StackTrace);
return false;
}
finally
{
this.conn.Close();
}
return true;
}
}
internal string GestioneRicerca(string strRicerca, string user)
{
sqlop = new SqlOperations();
sqlop.databaseConnection = ConfigurationManager.ConnectionStrings["cs"].ToString();
SqlCommandObject sco = new SqlCommandObject();
DbParameter[] dbp = new DbParameter[2];
dbp[0] = new SqlParameter();
dbp[0].ParameterName = "Ricerca";
dbp[0].DbType = DbType.Xml;
dbp[0].Direction = ParameterDirection.Input;
dbp[0].Value = strRicerca;
dbp[1] = new SqlParameter();
dbp[1].ParameterName = "Utente";
dbp[1].DbType = DbType.String;
dbp[1].Direction = ParameterDirection.Input;
dbp[1].Value = user;
sco.SPParams = dbp;
sco.SPName = "[Utility].[SP_ArchivioRicerca_Insert]";
DataTable dt;
string proc = sqlop.ExecuteProcedure(sco, out dt);
return proc;
}
internal string GestioneRicerca(string user)
{
sqlop = new SqlOperations();
sqlop.databaseConnection = ConfigurationManager.ConnectionStrings["cs"].ToString();
SqlCommandObject sco = new SqlCommandObject();
DbParameter[] dbp = new DbParameter[2];
dbp[0] = new SqlParameter();
dbp[0].ParameterName = "Utente";
dbp[0].DbType = DbType.String;
dbp[0].Direction = ParameterDirection.Input;
dbp[0].Value = user;
dbp[1] = new SqlParameter();
dbp[1].ParameterName = "Ricerca";
dbp[1].DbType = DbType.Xml;
dbp[1].Direction = ParameterDirection.Output;
sco.SPParams = dbp;
sco.SPName = "[Utility].[SP_ArchivioRicerca_Get]";
DataTable dt;
List<ExpandoObject> outputList = new List<ExpandoObject>();
string proc = sqlop.ExecuteProcedure(sco, out dt, out outputList);
IEnumerable<dynamic> Ricerca = outputList.Cast<dynamic>().Where(x => x.Nome.Contains("@Ricerca"));
return Convert.ToString(Ricerca.First().Value);
}
public List<CiudadBE> ConsultaCiudades(string id_dep)
{
List<CiudadBE> lstCiudades = new List<CiudadBE>();
try
{
string nameSP = "ConsultarCiudades";
BaseDatos db = new BaseDatos();
db.Conectar();
db.CrearComandoSP(nameSP);
DbParameter[] parametros = new DbParameter[3];
parametros[0] = db.Comando.CreateParameter();
parametros[0].ParameterName = "vrId_Departamento";
parametros[0].Value = id_dep;
parametros[0].Direction = ParameterDirection.Input;
db.Comando.Parameters.Add(parametros[0]);
parametros[1] = db.Comando.CreateParameter();
parametros[1].ParameterName = "vrCodResult";
parametros[1].Value = 0;
parametros[1].Direction = ParameterDirection.Output;
db.Comando.Parameters.Add(parametros[1]);
parametros[2] = db.Comando.CreateParameter();
parametros[2].ParameterName = "vrDescResult";
parametros[2].Value = "";
parametros[2].Direction = ParameterDirection.Output;
parametros[2].Size = 200;
parametros[2].DbType = DbType.String;
db.Comando.Parameters.Add(parametros[2]);
DbDataReader datos = db.EjecutarConsulta();
CiudadBE c = null;
while (datos.Read())
{
try
{
c = new CiudadBE();
c.Id_Ciudad = datos.GetValue(0).ToString();
c.Nombre_Ciudad= (datos.GetString(1));
lstCiudades.Add(c);
}
catch (InvalidCastException ex)
{
throw new Exception("Los tipos no coinciden.", ex);
}
catch (DataException ex)
{
throw new Exception("Error de ADO.NET.", ex);
}
}
datos.Close();
db.Desconectar();
}
catch (Exception ex)
{
throw new Exception("Error al acceder a la base de datos para obtener los CiudadesBEs.");
}
return lstCiudades;
}
public bool TryLogin(string username, string password, out string hash)
{
bool loginSuccess = false;
_db.Open();
hash = "";
DbParameter[] parameters = new DbParameter[] { new SqlParameter("@username", username),
new SqlParameter("@password", password),
new SqlParameter("@connectionTime", System.Data.SqlDbType.DateTime) };
parameters[2].Direction = System.Data.ParameterDirection.Output;
int userCount = (int)_db.ExecuteScalar(DbNames.USERLOGIN_STOREDPROC,
System.Data.CommandType.StoredProcedure, parameters);
if (userCount == 1)
{
DateTime loginTime = (DateTime)parameters[2].Value;
hash = loginTime.ToString("yyyyMMddHHmmss");
loginSuccess = true;
}
_db.Close();
return loginSuccess;
}
/// <summary>
/// If fillParameterValues == false, you must specify the parameter names you want to be in the returning sql.
/// </summary>
/// <param name="fillParameterValues"></param>
/// <param name="parameterNames"></param>
/// <returns></returns>
public string ToDbCommandText(bool fillParameterValues, params string[] parameterNames)
{
if (fillParameterValues)
{
return(DataUtils.ToString(ToDbCommand()));
}
else
{
DbCommand cmd = ToDbCommand();
string sql = cmd.CommandText;
if (!string.IsNullOrEmpty(sql) && parameterNames != null)
{
System.Collections.IEnumerator en = cmd.Parameters.GetEnumerator();
int i = 0;
while (en.MoveNext())
{
System.Data.Common.DbParameter p = (System.Data.Common.DbParameter)en.Current;
sql = sql.Replace(p.ParameterName, p.ParameterName[0] + parameterNames[i].TrimStart(p.ParameterName[0]));
++i;
}
}
return(sql);
}
}
public static DataTable mStore_OneStores(DateTime NgayBatDau, DateTime NgayKetThuc)
{
using (DbCommand DbCommand = clsConnect.CreateCommand())
{
DbCommand.CommandText = SpMobile_Store_OneStores;
DbCommand.CommandType = System.Data.CommandType.StoredProcedure;
System.Data.Common.DbParameterCollection DbParameters = DbCommand.Parameters;
System.Data.Common.DbParameter DbParameter = DbCommand.CreateParameter();
DbParameter.ParameterName = PNgayBatDau;
DbParameter.Value = NgayBatDau;
DbParameters.Add(DbParameter);
DbParameter = DbCommand.CreateParameter();
DbParameter.ParameterName = PNgayKetThuc;
DbParameter.Value = NgayKetThuc;
DbParameters.Add(DbParameter);
DbDataAdapter DbDataAdapter = clsConnect.DbProviderFactory.CreateDataAdapter();
DbDataAdapter.SelectCommand = DbCommand;
System.Data.DataTable DataTable = new System.Data.DataTable("TABLE");
try { DbDataAdapter.Fill(DataTable); }
catch (Exception ex) { DataTable = null; }
return(DataTable);
}
}
/// <summary>
/// If fillParameterValues == false, you must specify the parameter names you want to be in the returning sql.
/// </summary>
/// <param name="fillParameterValues"></param>
/// <param name="parameterNames"></param>
/// <returns></returns>
public string ToDbCommandText(bool fillParameterValues, params string[] parameterNames)
{
if (fillParameterValues)
{
return(DataUtils.ToString(ToDbCommand()));
}
else
{
DbCommand cmd = ToDbCommand();
string sql = cmd.CommandText;
if (!string.IsNullOrEmpty(sql) && parameterNames != null)
{
//Check.Require(parameterNames.Length == cmd.Parameters.Count, "The Specified count of parameter names does not equal the count of parameter names in the query.");
System.Collections.IEnumerator en = cmd.Parameters.GetEnumerator();
int i = 0;
while (en.MoveNext())
{
//Check.Require(parameterNames[i], "parameterNames[" + i + "]", Check.NotNullOrEmpty);
System.Data.Common.DbParameter p = (System.Data.Common.DbParameter)en.Current;
sql = sql.Replace(p.ParameterName, p.ParameterName[0] + parameterNames[i].TrimStart(p.ParameterName[0]));
++i;
}
}
return(sql);
}
}
internal DataTable Dealer_CaricaValoriMultipliSelected(int idDealer, string nomeCampo)
{
sqlop = new SqlOperations();
sqlop.databaseConnection = ConfigurationManager.ConnectionStrings["cs"].ToString();
DbParameter[] dbp = new DbParameter[2];
dbp[0] = new SqlParameter();
dbp[0].ParameterName = "IDDealer";
dbp[0].DbType = DbType.Int32;
dbp[0].Direction = ParameterDirection.Input;
dbp[0].Value = idDealer;
dbp[1] = new SqlParameter();
dbp[1].ParameterName = "NomeCampo";
dbp[1].DbType = DbType.String;
dbp[1].Direction = ParameterDirection.Input;
dbp[1].Value = nomeCampo;
SqlCommandObject sco = new SqlCommandObject();
sco.SPName = "Dealer.SP_CaricaValoriMultipliSelected";
sco.SPParams = dbp;
DataTable dt;
string proc = sqlop.ExecuteProcedure(sco, out dt);
return dt;
}
/// <summary>
/// Populate the specified <see cref="DbParameter" /> with its value.
/// </summary>
/// <remarks>
/// This implementation makes sure that GUIDs are passed as strings.
/// </remarks>
/// <param name="dbParameter"></param>
/// <param name="value"></param>
/// <returns></returns>
protected override void PopulateValue(DbParameter dbParameter, object value)
{
if (value is Guid)
dbParameter.Value = value.ToString();
else
base.PopulateValue(dbParameter, value);
}
public virtual void SetupParameter(DbParameter param, string name, object value)
{
name.MustNotBeEmpty();
param.ParameterName = ParamPrefix+name;
param.Value = value ?? DBNull.Value;
}
public int ExecuteScalar(string sqlstr, DbParameter[] parameters)
{
if (string.IsNullOrEmpty(sqlstr))
{
return 0;
}
OpenConn();
Cmd.CommandType = CommandType.Text;
Cmd.CommandText = sqlstr;
Cmd.Parameters.Clear();
if (parameters != null)
{
Cmd.Parameters.AddRange(parameters);
}
try
{
return Convert.ToInt32(Cmd.ExecuteScalar());
}
catch
{
throw;
}
finally
{
Cmd.Dispose();
}
}
/// <summary>
/// Ϊ����һ������������
/// </summary>
internal static void PrepareAddCommand(Model.C_Contract model, out StringBuilder strSql, out DbParameter[] cmdParms)
{
strSql = new StringBuilder();
strSql.Append("INSERT INTO C_Contract(");
strSql.Append("ɾ����ʶ, ������, ������ʱ��, ����, ����ʱ��, ֤������, ��ͬ���, ��ͬ����, ��ͬ����, ��ͬ״̬, ��ͬ����, ��ʼʱ��, ����ʱ��, ��ͬ����, ��ͬ����)");
strSql.Append(" VALUES (");
strSql.Append("@in_ɾ����ʶ, @in_������, @in_������ʱ��, @in_����, @in_����ʱ��, @in_֤������, @in_��ͬ���, @in_��ͬ����, @in_��ͬ����, @in_��ͬ״̬, @in_��ͬ����, @in_��ʼʱ��, @in_����ʱ��, @in_��ͬ����, @in_��ͬ����)");
cmdParms = new DbParameter[]{
dbHelper.CreateInDbParameter("@in_ɾ����ʶ", DbType.Boolean, model.ɾ����ʶ),
dbHelper.CreateInDbParameter("@in_������", DbType.String, model.������),
dbHelper.CreateInDbParameter("@in_������ʱ��", DbType.DateTime, model.������ʱ��),
dbHelper.CreateInDbParameter("@in_����", DbType.String, model.����),
dbHelper.CreateInDbParameter("@in_����ʱ��", DbType.DateTime, model.����ʱ��),
dbHelper.CreateInDbParameter("@in_֤������", DbType.String, model.֤������),
dbHelper.CreateInDbParameter("@in_��ͬ���", DbType.String, model.��ͬ���),
dbHelper.CreateInDbParameter("@in_��ͬ����", DbType.String, model.��ͬ����),
dbHelper.CreateInDbParameter("@in_��ͬ����", DbType.String, model.��ͬ����),
dbHelper.CreateInDbParameter("@in_��ͬ״̬", DbType.String, model.��ͬ״̬),
dbHelper.CreateInDbParameter("@in_��ͬ����", DbType.String, model.��ͬ����),
dbHelper.CreateInDbParameter("@in_��ʼʱ��", DbType.DateTime, model.��ʼʱ��),
dbHelper.CreateInDbParameter("@in_����ʱ��", DbType.DateTime, model.����ʱ��),
dbHelper.CreateInDbParameter("@in_��ͬ����", DbType.String, model.��ͬ����),
dbHelper.CreateInDbParameter("@in_��ͬ����", DbType.String, model.��ͬ����)};
}
public string Add(DbParameter parameter)
{
var name = ":p" + (NamedParameters.Count + 1);
parameter.ParameterName = name;
NamedParameters.Add(parameter);
return name;
}
internal DataTable getFromActiveDirectory(string username)
{
try
{
sqlop = new SqlOperations();
sqlop.databaseConnection = ConfigurationManager.ConnectionStrings["cs"].ToString();
DbParameter[] dbp = new DbParameter[1];
dbp[0] = new SqlParameter();
dbp[0].ParameterName = "UserID";
dbp[0].DbType = DbType.String;
dbp[0].Direction = ParameterDirection.Input;
dbp[0].Value = username;
SqlCommandObject sco = new SqlCommandObject();
sco.SPName = "Utenti.SP_Utenti_ADData_Get";
sco.SPParams = dbp;
DataTable dt;
string proc = sqlop.ExecuteProcedure(sco, out dt);
return dt;
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary> Store the relationship between the EAL ( Engine Agnostic Level ) parameter and the database parameter. </summary>
/// <param name="EalParam"></param>
/// <param name="DbParam"></param>
/// <remarks> This is only used for parameters that include an output direction, so the returned parameter value
/// can be copied bac from the database parameter to the EAL parameter. </remarks>
public void Add_Parameter_Copy_Pair(EalDbParameter EalParam, DbParameter DbParam)
{
if (parameterCopy == null)
parameterCopy = new List<Tuple<EalDbParameter, DbParameter>>();
parameterCopy.Add(new Tuple<EalDbParameter, DbParameter>(EalParam, DbParam));
}
/// <summary>
/// 清除当前所有数据
/// </summary>
public void Clear()
{
CurrentDbParameter = null;
_currentFieldName = null;
_paramsCount = 0;
SqlList.Clear();
}
protected override void InitializeParameter(object item, System.Data.Common.DbParameter p, System.Type type)
{
var typeCode = Type.GetTypeCode(type);
switch (typeCode)
{
case TypeCode.SByte:
p.DbType = DbType.Byte;
item = (byte)item;
break;
case TypeCode.UInt16:
case TypeCode.UInt32:
case TypeCode.Int64:
case TypeCode.UInt64:
p.DbType = DbType.Int32;
item = Convert.ToInt32(item);
break;
case TypeCode.DateTime:
p.DbType = DbType.String;
item = ((DateTime)item).ToString("yyyy/MM/dd HH:mm:ss");
break;
}
p.Value = item;
}
internal DataTable AutorizzazioneCampi_Get(int myIDProfilo, int myIDDealer)
{
sqlop = new SqlOperations();
sqlop.databaseConnection = ConfigurationManager.ConnectionStrings["cs"].ToString();
DbParameter[] dbp = new DbParameter[2];
dbp[0] = new SqlParameter();
dbp[0].ParameterName = "IDProfilo";
dbp[0].DbType = DbType.Int32;
dbp[0].Direction = ParameterDirection.Input;
dbp[0].Value = myIDProfilo;
dbp[1] = new SqlParameter();
dbp[1].ParameterName = "IDDealer";
dbp[1].DbType = DbType.Int32;
dbp[1].Direction = ParameterDirection.Input;
dbp[1].Value = myIDDealer;
SqlCommandObject sco = new SqlCommandObject();
sco.SPName = "Utenti.SP_AutorizzazioneCampi_Get";
sco.SPParams = dbp;
DataTable dt;
string proc = sqlop.ExecuteProcedure(sco, out dt);
return dt;
}
/// <summary>
/// 执行一个查询语句，返回一个包含查询结果的DataTable
/// </summary>
/// <param name="sql">要执行的查询语句</param>
/// <param name="parameters">执行SQL查询语句所需要的参数，参数必须以它们在SQL语句中的顺序为准</param>
/// <returns></returns>
public DataTable ExecuteDataTable(string sql, DbParameter[] parameters)
{
try
{
using (SQLiteConnection connection = new SQLiteConnection(connectionString))
{
connection.Open();
using (SQLiteCommand command = new SQLiteCommand(sql, connection))
{
if (parameters != null)
{
command.Parameters.AddRange(parameters);
}
SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);
DataTable data = new DataTable();
adapter.Fill(data);
return data;
}
}
}
catch (Exception)
{
throw;
}
}
private IDbCommand CreateCommand(DbMetaLog dblog, IDbConnection conn)
{
string fields = "";
string vals = "";
DbParameter[] dbParameters = new DbParameter[dblog.FiledValues.Count];
int i = 0;
foreach (string key in dblog.FiledValues.Keys)
{
fields += string.Format("{0},", key);
vals += string.Format("@{0},", key);
dbParameters[i] = this.CreateDbParameter(dblog.DbDialect, "@" + key, dblog.FiledValues[key]);
i++;
}
fields = fields.Trim(',');
vals = vals.Trim(',');
string commandText = string.Format("INSERT INTO {0} ({1}) VALUES ({2})", dblog.DestTable, fields, vals);
IDbCommand cmd = conn.CreateCommand();
cmd.CommandText = commandText;
cmd.CommandType = CommandType.Text;
this.AttachParameters(cmd, dbParameters);
return cmd;
}
/// <summary>
/// Between和NOT Between的情况
/// </summary>
/// <param name="context"></param>
/// <returns></returns>
private string ToSQLWithBetween(ParseSqlContext context)
{
System.Data.Common.DbParameter parameter = context.CreateParameter(this.column.name, value);
context.DbParameters.Add(parameter);
System.Data.Common.DbParameter parameter2 = context.CreateParameter(this.column.name, value2);
context.DbParameters.Add(parameter2);
return(string.Format("{0} {3} {1} AND {2}", this.column.Parse(context), parameter.ParameterName, parameter2.ParameterName, this.opt.ToSql()));
}
protected override void InitializeParameter(object item, System.Data.Common.DbParameter p, System.Type type)
{
var typeCode = Type.GetTypeCode(type);
if (item != null && item is DateTime)
{
item = ((DateTime)item).ToString(fmt);
p.DbType = DbType.String;
}
p.Value = item;
}
public static bool Login(string UserName, string Password, out string TenNguoiDung)
{
bool HasRows = false; TenNguoiDung = string.Empty;
if (clsConnect.DB_OpenConnection("", "", "", ""))
{
using (DbCommand DbCommand = clsConnect.DbConnection.CreateCommand())
{
DbCommand.CommandText = SpMobile_Users_Logins;
DbCommand.CommandType = System.Data.CommandType.StoredProcedure;
System.Data.Common.DbParameterCollection DbParameters = DbCommand.Parameters;
System.Data.Common.DbParameter DbParameter = DbCommand.CreateParameter();
DbParameter.ParameterName = PMobNguoiDung;
DbParameter.Value = UserName;
DbParameters.Add(DbParameter);
DbParameter = DbCommand.CreateParameter();
DbParameter.ParameterName = PPasNguoiDung;
string EnPass = clsConnect.Encrypt(Password);
DbParameter.Value = EnPass;
DbParameters.Add(DbParameter);
try
{
using (DbDataReader DbDataReader = DbCommand.ExecuteReader())
{
HasRows = DbDataReader.HasRows;
if (DbDataReader.Read())
{
TenNguoiDung = DbDataReader.GetString(DbDataReader.GetOrdinal(PTenNguoiDung));
}
else
{
TenNguoiDung = "Chưa xác định";
}
}
}
catch { }
}
}
return(HasRows);
}
/// <summary>
/// Generates a DB command based on the properties in this object
/// </summary>
/// <returns>DB command generated from the properties in this object</returns>
private db.DbCommand ToDbCommand()
{
db.DbCommand cmd = GetEmptyCommand();
cmd.Connection = connection;
cmd.CommandText = CommandText;
cmd.CommandType = commandType;
foreach (sp.Parameter parameter in Parameters)
{
db.DbParameter dbParameter = GetEmptyParameter();
dbParameter.ParameterName = parameter.Name;
dbParameter.DbType = parameter.Type;
dbParameter.Value = GetCommandParameterValue(parameter.Value);
cmd.Parameters.Add(dbParameter);
}
return(cmd);
}
public DataSet sp(string spName, List <parameter> pars)
{
bool opened = false;
try {
if (open_conn())
{
opened = true;
}
SqlCommand command = ((SqlConnection)_conn).CreateCommand();
if (_timeout > 0)
{
command.CommandTimeout = _timeout;
}
command.Connection = (SqlConnection)_conn;
command.CommandText = spName;
command.CommandType = CommandType.StoredProcedure;
foreach (parameter par in pars)
{
System.Data.Common.DbParameter param = command.CreateParameter();
param.ParameterName = par.Name;
param.Value = par.IsNull ? DBNull.Value : par.Value;
//??? if (par.Out) DBManagDBType.Cursor
command.Parameters.Add(param);
}
SqlDataAdapter adapter = new SqlDataAdapter(command);
logSql("exec " + spName); // ...finire
DataSet ds = new DataSet();
adapter.Fill(ds, "dsStoreProc");
return(ds);
}
catch (Exception ex) { logErr(ex, string.Format("s.p. {0}({1})", spName, string.Join(", ", pars.Select(x => x.Value.ToString())))); throw ex; }
finally { if (opened)
{
close_conn();
}
}
}
/// <summary>
/// Converts a system type name into a database type.
/// </summary>
/// <param name="systemType">A system type</param>
/// <param name="factory">A system database factory</param>
/// <returns>The database name.</returns>
/// <exception cref="Tenor.TenorException">Throws TenorException when cannot convert the desired system type.</exception>
/// <exception cref="System.ArgumentNullException">Throws ArgumentNullException when a null parameter was supplied.</exception>
/// <remarks></remarks>
public static string GetDbTypeName(Type systemType, DbProviderFactory factory)
{
if (systemType == null)
{
throw (new ArgumentNullException("systemType"));
}
else if (factory == null)
{
throw (new ArgumentNullException("factory"));
}
string typeName = string.Empty;
DbType tipo = DbType.String;
System.ComponentModel.TypeConverter conversor = System.ComponentModel.TypeDescriptor.GetConverter(tipo);
if (conversor == null)
{
throw (new TenorException("GetDbTypeName: Cannot create converter."));
}
tipo = (DbType)(conversor.ConvertFrom(systemType.Name));
System.Data.Common.DbParameter param = factory.CreateParameter();
if (param == null)
{
throw (new TenorException("GetDbTypeName: Cannot create parameter."));
}
param.DbType = tipo;
foreach (System.Reflection.PropertyInfo prop in param.GetType().GetProperties())
{
//This loop is necessary to set dbms specific Parameter properties.
if (prop.Name.Contains("DbType") && !prop.Name.Equals("DbType"))
{
typeName = prop.GetValue(param, new object[] { }).ToString();
break;
}
}
return(typeName);
}
/// <summary>
/// Returns a value from the first row of the first column in the query result.
/// </summary>
/// <typeparam name="T">Type of the scalar</typeparam>
/// <param name="query">SQL query with parameters</param>
/// <returns>Scalar value</returns>
public T GetScalar <T>(IParameterizedQuery query)
{
LogQuery(query);
string queryText = TranslateQueryParameters(query.QueryText);
using (DbConnection db = GetConnection())
{
DbCommand command = GetCommand(db);
command.CommandText = queryText;
foreach (ISqlParameter param in query.SqlParameters)
{
System.Data.Common.DbParameter dbParam = command.CreateParameter();
dbParam.ParameterName = param.Name;
dbParam.Value = param.Value;
command.Parameters.Add(dbParam);
}
return((T)command.ExecuteScalar());
}
}
public static string ToString(System.Data.Common.DbCommand cmd)
{
if (cmd == null)
{
return(null);
}
string sql = cmd.CommandText;
if (!string.IsNullOrEmpty(sql))
{
System.Collections.IEnumerator en = cmd.Parameters.GetEnumerator();
while (en.MoveNext())
{
System.Data.Common.DbParameter p = (System.Data.Common.DbParameter)en.Current;
sql = sql.Replace(p.ParameterName, ToString(p.DbType, p.Value));
}
}
return(sql.Replace("= NULL", "IS NULL"));
}
/// <summary>
/// Returns a result enumeration from given parameterized query into a list of a given DTO.
/// </summary>
/// <typeparam name="T">Type of the DTO</typeparam>
/// <param name="query">Parameterized query</param>
/// <returns>DTO enumeration</returns>
public IEnumerable <T> GetResult <T>(IParameterizedQuery query) where T : new()
{
LogQuery(query);
string queryText = TranslateQueryParameters(query.QueryText);
using (DbConnection db = GetConnection())
{
DbCommand command = GetCommand(db);
command.CommandText = queryText;
foreach (ISqlParameter param in query.SqlParameters)
{
System.Data.Common.DbParameter dbParam = command.CreateParameter();
dbParam.ParameterName = param.Name;
dbParam.Value = param.Value;
command.Parameters.Add(dbParam);
}
IDataReader dataReader = command.ExecuteReader();
return(mapper.MapToDto <T>(dataReader, DatabaseType));
}
}
/// <summary>
/// Kiem tra xem so dien thoai khach hang da co tren he thong chua
/// </summary>
/// Tra ve: True - Da ton tại; False - Chua ton tai
public static bool m2018_CheckClient(string CodCardClient)
{
using (System.Data.Common.DbCommand DbCommand = clsConnect.DbConnection.CreateCommand())
{
DbCommand.CommandText = SpMobile_2018_CheckClient;
DbCommand.CommandType = System.Data.CommandType.StoredProcedure;
System.Data.Common.DbParameterCollection DbParameters = DbCommand.Parameters;
System.Data.Common.DbParameter DbParameter = DbCommand.CreateParameter();
DbParameter.ParameterName = PCodCardClient;
DbParameter.Value = CodCardClient;
DbParameters.Add(DbParameter);
bool HasRows = false;
try
{
using (DbDataReader DbDataReader = DbCommand.ExecuteReader())
{ HasRows = DbDataReader.HasRows; }
}
catch { }
return(HasRows);
}
}
/// <summary>
/// Returns a result as a RDBMS-specific <see cref="DataSet"/> for a parameterized query.
/// Individual parameters, in present, are converted into the relevant RDMBS-specific <see cref="DbParameter"/>.
/// </summary>
/// <param name="query">SQL query</param>
/// <returns>RDBMS-specific <see cref="DataSet"/>.</returns>
public DataSet GetDataTable(IParameterizedQuery query)
{
LogQuery(query);
string queryText = TranslateQueryParameters(query.QueryText);
using (DbConnection db = GetConnection())
{
DbCommand command = GetCommand(db);
command.CommandText = queryText;
foreach (ISqlParameter param in query.SqlParameters)
{
System.Data.Common.DbParameter dbParam = command.CreateParameter();
dbParam.ParameterName = param.Name;
dbParam.Value = param.Value;
command.Parameters.Add(dbParam);
}
DataAdapter dataAdapter = GetDataAdapterForCommand(command);
var dataSet = new DataSet();
dataAdapter.Fill(dataSet);
return(dataSet);
}
}
public override SqlDbType GetSqlDbType(System.Data.Common.DbParameter p)
{
return(((SqlCeParameter)p).SqlDbType);
}
public IEnumerable <DistributionOEMUserListView> QueryDistributionOEMUserList(DistributionOEMUserCondition condition, Pagination pagination)
{
var result = new List <DistributionOEMUserListView>();
using (DbOperator dbOperator = new DbOperator(Provider, ConnectionString))
{
dbOperator.AddParameter("@i_Owner", condition.CompanyId);
if (condition.IsOwnerAll.HasValue)
{
dbOperator.AddParameter("@i_IsOwnerAll", condition.IsOwnerAll);
}
else
{
dbOperator.AddParameter("@i_IsOwnerAll", true);
}
dbOperator.AddParameter("@i_RegisterBeginTime", condition.RegisterBeginTime.HasValue ? condition.RegisterBeginTime.Value : (DateTime?)null);
dbOperator.AddParameter("@i_RegisterEndTime", condition.RegisterEndTime.HasValue ? condition.RegisterEndTime.Value : (DateTime?)null);
if (!string.IsNullOrWhiteSpace(condition.UserNo))
{
dbOperator.AddParameter("@i_UserNo", condition.UserNo);
}
if (condition.Enable.HasValue)
{
dbOperator.AddParameter("@i_Enable", condition.Enable);
}
if (!string.IsNullOrWhiteSpace(condition.ContactName))
{
dbOperator.AddParameter("@i_Contact", condition.ContactName);
}
if (condition.IncomeGroup.HasValue)
{
dbOperator.AddParameter("@i_IncomeGroup", condition.IncomeGroup);
}
if (!string.IsNullOrWhiteSpace(condition.AbbreviateName))
{
dbOperator.AddParameter("@i_AbbreviateName", condition.AbbreviateName);
}
if (pagination != null)
{
dbOperator.AddParameter("@i_pageSize", pagination.PageSize);
dbOperator.AddParameter("@i_pageIndex", pagination.PageIndex);
}
System.Data.Common.DbParameter totalCount = dbOperator.AddParameter("@o_RowCount");
totalCount.DbType = System.Data.DbType.Int32;
totalCount.Direction = System.Data.ParameterDirection.Output;
using (var reader = dbOperator.ExecuteReader("[dbo].[P_DistributionUserListPagination]", System.Data.CommandType.StoredProcedure))
{
while (reader.Read())
{
var user = new DistributionOEMUserListView();
user.RegisterTime = reader.GetDateTime(0);
user.AbbreviateName = reader.GetString(1);
user.Type = (CompanyType)reader.GetInt32(2);
user.AccountType = (AccountBaseType)reader.GetInt32(3);
user.ContactName = reader.GetString(4);
user.CompanyId = reader.GetGuid(5);
user.Enabled = reader.GetBoolean(6);
user.Login = reader.GetString(7);
user.IncomeGroupName = reader.IsDBNull(8) ? string.Empty : reader.GetString(8);
if (!reader.IsDBNull(9))
{
user.IncomeGroupId = reader.GetGuid(9);
}
result.Add(user);
}
}
if (pagination != null && pagination.GetRowCount)
{
pagination.RowCount = (int)totalCount.Value;
}
}
return(result);
}
protected virtual int Update(DataRow [] dataRows, DataTableMapping tableMapping)
{
int updateCount = 0;
foreach (DataRow row in dataRows)
{
StatementType statementType = StatementType.Update;
IDbCommand command = null;
string commandName = String.Empty;
switch (row.RowState)
{
case DataRowState.Added:
statementType = StatementType.Insert;
command = ((IDbDataAdapter)this).InsertCommand;
commandName = "Insert";
break;
case DataRowState.Deleted:
statementType = StatementType.Delete;
command = ((IDbDataAdapter)this).DeleteCommand;
commandName = "Delete";
break;
case DataRowState.Modified:
statementType = StatementType.Update;
command = ((IDbDataAdapter)this).UpdateCommand;
commandName = "Update";
break;
case DataRowState.Unchanged:
case DataRowState.Detached:
continue;
}
RowUpdatingEventArgs argsUpdating = CreateRowUpdatingEvent(row, command, statementType, tableMapping);
row.RowError = null;
OnRowUpdating(argsUpdating);
switch (argsUpdating.Status)
{
case UpdateStatus.Continue:
//continue in update operation
break;
case UpdateStatus.ErrorsOccurred:
if (argsUpdating.Errors == null)
{
argsUpdating.Errors = ExceptionHelper.RowUpdatedError();
}
row.RowError += argsUpdating.Errors.Message;
if (!ContinueUpdateOnError)
{
throw argsUpdating.Errors;
}
continue;
case UpdateStatus.SkipAllRemainingRows:
return(updateCount);
case UpdateStatus.SkipCurrentRow:
updateCount++;
continue;
default:
throw ExceptionHelper.InvalidUpdateStatus(argsUpdating.Status);
}
command = argsUpdating.Command;
try {
if (command != null)
{
DataColumnMappingCollection columnMappings = tableMapping.ColumnMappings;
#if ONLY_1_1
IDataParameter nullCheckParam = null;
#endif
foreach (IDataParameter parameter in command.Parameters)
{
if ((parameter.Direction & ParameterDirection.Input) == 0)
{
continue;
}
DataRowVersion rowVersion = parameter.SourceVersion;
// Parameter version is ignored for non-update commands
if (statementType == StatementType.Delete)
{
rowVersion = DataRowVersion.Original;
}
string dsColumnName = parameter.SourceColumn;
#if NET_2_0
if (columnMappings.Contains(dsColumnName))
{
dsColumnName = columnMappings [dsColumnName].DataSetColumn;
}
DbParameter nullCheckParam = parameter as DbParameter;
#else
if (columnMappings.Contains(dsColumnName))
{
dsColumnName = columnMappings [dsColumnName].DataSetColumn;
}
if (dsColumnName == null || dsColumnName.Length == 0)
{
nullCheckParam = parameter;
continue;
}
#endif
// Fetch columnName from column-mapping if found one,
// otherwise, use the source column name to fetch parameter value.
parameter.Value = row [dsColumnName, rowVersion];
#if NET_2_0
if (nullCheckParam != null && nullCheckParam.SourceColumnNullMapping)
{
#else
if (nullCheckParam != null)
{
#endif
if (parameter.Value != null && parameter.Value != DBNull.Value)
{
nullCheckParam.Value = 0;
}
else
{
nullCheckParam.Value = 1;
}
nullCheckParam = null;
}
}
}
} catch (Exception e) {
argsUpdating.Errors = e;
argsUpdating.Status = UpdateStatus.ErrorsOccurred;
}
IDataReader reader = null;
try {
if (command == null)
{
throw ExceptionHelper.UpdateRequiresCommand(commandName);
}
CommandBehavior commandBehavior = CommandBehavior.Default;
if (command.Connection.State == ConnectionState.Closed)
{
command.Connection.Open();
commandBehavior |= CommandBehavior.CloseConnection;
}
// use ExecuteReader because we want to use the commandbehavior parameter.
// so the connection will be closed if needed.
reader = command.ExecuteReader(commandBehavior);
// update the current row, if the update command returns any resultset
// ignore other than the first record.
DataColumnMappingCollection columnMappings = tableMapping.ColumnMappings;
if (command.UpdatedRowSource == UpdateRowSource.Both ||
command.UpdatedRowSource == UpdateRowSource.FirstReturnedRecord)
{
if (reader.Read())
{
DataTable retSchema = reader.GetSchemaTable();
foreach (DataRow dr in retSchema.Rows)
{
string columnName = dr ["ColumnName"].ToString();
string dstColumnName = columnName;
if (columnMappings != null &&
columnMappings.Contains(columnName))
{
dstColumnName = columnMappings [dstColumnName].DataSetColumn;
}
DataColumn dstColumn = row.Table.Columns [dstColumnName];
if (dstColumn == null ||
(dstColumn.Expression != null &&
dstColumn.Expression.Length > 0))
{
continue;
}
// info from : http://www.error-bank.com/microsoft.public.dotnet.framework.windowsforms.databinding/
// [email protected]_Thread.aspx
// disable readonly for non-expression columns.
bool readOnlyState = dstColumn.ReadOnly;
dstColumn.ReadOnly = false;
try {
row [dstColumnName] = reader [columnName];
} finally {
dstColumn.ReadOnly = readOnlyState;
}
}
}
}
reader.Close();
int tmp = reader.RecordsAffected; // records affected is valid only after closing reader
// if the execute does not effect any rows we throw an exception.
if (tmp == 0)
{
throw new DBConcurrencyException("Concurrency violation: the " +
commandName + "Command affected 0 records.", null,
new DataRow [] { row });
}
updateCount += tmp;
if (command.UpdatedRowSource == UpdateRowSource.Both ||
command.UpdatedRowSource == UpdateRowSource.OutputParameters)
{
// Update output parameters to row values
foreach (IDataParameter parameter in command.Parameters)
{
if (parameter.Direction != ParameterDirection.InputOutput &&
parameter.Direction != ParameterDirection.Output &&
parameter.Direction != ParameterDirection.ReturnValue)
{
continue;
}
string dsColumnName = parameter.SourceColumn;
if (columnMappings != null &&
columnMappings.Contains(parameter.SourceColumn))
{
dsColumnName = columnMappings [parameter.SourceColumn].DataSetColumn;
}
DataColumn dstColumn = row.Table.Columns [dsColumnName];
if (dstColumn == null ||
(dstColumn.Expression != null &&
dstColumn.Expression.Length > 0))
{
continue;
}
bool readOnlyState = dstColumn.ReadOnly;
dstColumn.ReadOnly = false;
try {
row [dsColumnName] = parameter.Value;
} finally {
dstColumn.ReadOnly = readOnlyState;
}
}
}
RowUpdatedEventArgs updatedArgs = CreateRowUpdatedEvent(row, command, statementType, tableMapping);
OnRowUpdated(updatedArgs);
switch (updatedArgs.Status)
{
case UpdateStatus.Continue:
break;
case UpdateStatus.ErrorsOccurred:
if (updatedArgs.Errors == null)
{
updatedArgs.Errors = ExceptionHelper.RowUpdatedError();
}
row.RowError += updatedArgs.Errors.Message;
if (!ContinueUpdateOnError)
{
throw updatedArgs.Errors;
}
break;
case UpdateStatus.SkipCurrentRow:
continue;
case UpdateStatus.SkipAllRemainingRows:
return(updateCount);
}
#if NET_2_0
if (!AcceptChangesDuringUpdate)
{
continue;
}
#endif
row.AcceptChanges();
} catch (Exception e) {
row.RowError = e.Message;
if (!ContinueUpdateOnError)
{
throw e;
}
} finally {
if (reader != null && !reader.IsClosed)
{
reader.Close();
}
}
}
return(updateCount);
}
public override void LogParameter <TResult>(DbCommand command, DbCommandInterceptionContext <TResult> interceptionContext, System.Data.Common.DbParameter parameter)
{
this.Write(command);
}
public override System.Data.Common.DbParameter CloneParameter(System.Data.Common.DbParameter p)
{
throw new NotImplementedException();
}
private DbCommand CreateDeleteCommand(bool option)
{
// If no table was found, then we can't do an delete
if (QuotedTableName == String.Empty)
{
return(null);
}
CreateNewCommand(ref _deleteCommand);
string command = String.Format("DELETE FROM {0}", QuotedTableName);
StringBuilder whereClause = new StringBuilder();
bool keyFound = false;
int parmIndex = 1;
foreach (DataRow schemaRow in _dbSchemaTable.Rows)
{
if (!schemaRow.IsNull("IsExpression") && (bool)schemaRow["IsExpression"] == true)
{
continue;
}
if (!IncludedInWhereClause(schemaRow))
{
continue;
}
if (whereClause.Length > 0)
{
whereClause.Append(" AND ");
}
bool isKey = (bool)schemaRow ["IsKey"];
DbParameter parameter = null;
string sourceColumnName;
if (isKey)
{
keyFound = true;
}
//ms.net 1.1 generates the null check for columns even if AllowDBNull is false
//while ms.net 2.0 does not. Anyways, since both forms are logically equivalent
//following the 2.0 approach
bool allowNull = (bool)schemaRow ["AllowDBNull"];
if (!isKey && allowNull)
{
parameter = _deleteCommand.CreateParameter();
if (option)
{
parameter.ParameterName = String.Format("@IsNull_{0}",
schemaRow ["BaseColumnName"]);
}
else
{
parameter.ParameterName = String.Format("@p{0}", parmIndex++);
}
parameter.Value = 1;
parameter.DbType = DbType.Int32;
// This should be set for nullcheckparam
sourceColumnName = (string)schemaRow ["BaseColumnName"];
parameter.SourceColumn = sourceColumnName;
parameter.SourceColumnNullMapping = true;
parameter.SourceVersion = DataRowVersion.Original;
_deleteCommand.Parameters.Add(parameter);
whereClause.Append("(");
whereClause.Append(String.Format(clause1, parameter.ParameterName,
GetQuotedString(sourceColumnName)));
whereClause.Append(" OR ");
}
if (option)
{
parameter = CreateParameter(_deleteCommand, schemaRow, true);
}
else
{
parameter = CreateParameter(_deleteCommand, parmIndex++, schemaRow);
}
parameter.SourceVersion = DataRowVersion.Original;
ApplyParameterInfo(parameter, schemaRow, StatementType.Delete, true);
//parameter.IsNullable = allowNull;
whereClause.Append(String.Format(clause2, GetQuotedString(parameter.SourceColumn), parameter.ParameterName));
if (!isKey && allowNull)
{
whereClause.Append(")");
}
}
if (!keyFound)
{
throw new InvalidOperationException("Dynamic SQL generation for the DeleteCommand is not supported against a SelectCommand that does not return any key column information.");
}
// We're all done, so bring it on home
string sql = String.Format("{0} WHERE ({1})", command, whereClause.ToString());
_deleteCommand.CommandText = sql;
_dbCommand = _deleteCommand;
return(_deleteCommand);
}
private DbCommand CreateUpdateCommand(bool option)
{
// If no table was found, then we can't do an update
if (QuotedTableName == String.Empty)
{
return(null);
}
CreateNewCommand(ref _updateCommand);
string command = String.Format("UPDATE {0} SET ", QuotedTableName);
StringBuilder columns = new StringBuilder();
StringBuilder whereClause = new StringBuilder();
int parmIndex = 1;
bool keyFound = false;
DbParameter parameter = null;
// First, create the X=Y list for UPDATE
foreach (DataRow schemaRow in _dbSchemaTable.Rows)
{
if (!IncludedInUpdate(schemaRow))
{
continue;
}
if (columns.Length > 0)
{
columns.Append(", ");
}
if (option)
{
parameter = CreateParameter(_updateCommand, schemaRow, false);
}
else
{
parameter = CreateParameter(_updateCommand, parmIndex++, schemaRow);
}
parameter.SourceVersion = DataRowVersion.Current;
ApplyParameterInfo(parameter, schemaRow, StatementType.Update, false);
//parameter.IsNullable = (bool) schemaRow ["AllowDBNull"];
columns.Append(String.Format("{0} = {1}", GetQuotedString(parameter.SourceColumn), parameter.ParameterName));
}
// Now, create the WHERE clause. This may be optimizable, but it would be ugly to incorporate
// into the loop above. "Premature optimization is the root of all evil." -- Knuth
foreach (DataRow schemaRow in _dbSchemaTable.Rows)
{
if (!schemaRow.IsNull("IsExpression") && (bool)schemaRow ["IsExpression"] == true)
{
continue;
}
if (!IncludedInWhereClause(schemaRow))
{
continue;
}
if (whereClause.Length > 0)
{
whereClause.Append(" AND ");
}
bool isKey = (bool)schemaRow ["IsKey"];
if (isKey)
{
keyFound = true;
}
//ms.net 1.1 generates the null check for columns even if AllowDBNull is false
//while ms.net 2.0 does not. Anyways, since both forms are logically equivalent
//following the 2.0 approach
bool allowNull = (bool)schemaRow ["AllowDBNull"];
if (!isKey && allowNull)
{
parameter = _updateCommand.CreateParameter();
if (option)
{
parameter.ParameterName = String.Format("@IsNull_{0}",
schemaRow ["BaseColumnName"]);
}
else
{
parameter.ParameterName = String.Format("@p{0}", parmIndex++);
}
parameter.DbType = DbType.Int32;
parameter.Value = 1;
parameter.SourceColumn = (string)schemaRow ["BaseColumnName"];
parameter.SourceColumnNullMapping = true;
parameter.SourceVersion = DataRowVersion.Original;
whereClause.Append("(");
whereClause.Append(String.Format(clause1, parameter.ParameterName,
GetQuotedString((string)schemaRow ["BaseColumnName"])));
whereClause.Append(" OR ");
_updateCommand.Parameters.Add(parameter);
}
if (option)
{
parameter = CreateParameter(_updateCommand, schemaRow, true);
}
else
{
parameter = CreateParameter(_updateCommand, parmIndex++, schemaRow);
}
parameter.SourceVersion = DataRowVersion.Original;
//parameter.IsNullable = allowNull;
ApplyParameterInfo(parameter, schemaRow, StatementType.Update, true);
whereClause.Append(String.Format(clause2, GetQuotedString(parameter.SourceColumn), parameter.ParameterName));
if (!isKey && allowNull)
{
whereClause.Append(")");
}
}
if (!keyFound)
{
throw new InvalidOperationException("Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information.");
}
// We're all done, so bring it on home
string sql = String.Format("{0}{1} WHERE ({2})", command, columns.ToString(), whereClause.ToString());
_updateCommand.CommandText = sql;
_dbCommand = _updateCommand;
return(_updateCommand);
}
protected abstract void ApplyParameterInfo(DbParameter parameter,
DataRow row,
StatementType statementType,
bool whereClause);
public string BuildSqlWithSqlParameter(string sql, System.Data.Common.DbParameter[] paras)
{
System.Data.Common.DbParameter[] arrParas = new System.Data.Common.DbParameter[paras.Length];
for (int i = 0; i < paras.Length; i++)
{
arrParas[i] = paras[i];
}
Array.Sort(arrParas, new DbParameterForSort());
object[] objParas = new object[arrParas.Length];
for (int i = 0; i < arrParas.Length; i++)
{
// if(arrParas[i].Value == null)
//continue;
sql = sql.Replace(arrParas[i].ParameterName, "{" + i.ToString() + "}");
switch (arrParas[i].DbType)
{
case System.Data.DbType.AnsiString:
case System.Data.DbType.AnsiStringFixedLength:
case System.Data.DbType.String:
if (arrParas[i].Value == null)
{
objParas[i] = null;
}
else
{
objParas[i] = arrParas[i].Value;
}
break;
case System.Data.DbType.StringFixedLength:
case System.Data.DbType.Xml:
objParas[i] = arrParas[i].Value as string;
break;
case System.Data.DbType.Boolean:
if (arrParas[i].Value == null)
{
objParas[i] = null;
}
else
{
objParas[i] = arrParas[i].Value.ToString();
}
break;
case System.Data.DbType.Date:
if (arrParas[i].Value == null)
{
objParas[i] = null;
}
else
{
objParas[i] = ((DateTime)arrParas[i].Value).ToString("yyyy-MM-dd");
}
break;
case System.Data.DbType.DateTime:
case System.Data.DbType.DateTime2:
if (arrParas[i].Value == null)
{
objParas[i] = null;
}
else
{
objParas[i] = Convert.ToDateTime(arrParas[i].Value); // ((DateTime)arrParas[i].Value).ToString("yyyy-MM-dd HH:mm:ss");
}
break;
case System.Data.DbType.Time:
if (arrParas[i].Value == null)
{
objParas[i] = null;
}
else
{
objParas[i] = ((DateTime)arrParas[i].Value).ToString("HH:mm:ss");
}
break;
case System.Data.DbType.Byte:
case System.Data.DbType.UInt16:
case System.Data.DbType.UInt32:
case System.Data.DbType.UInt64:
if (arrParas[i].Value == null)
{
objParas[i] = null;
}
else
{
objParas[i] = ulong.Parse(arrParas[i].Value.ToString());
}
break;
case System.Data.DbType.Decimal:
case System.Data.DbType.Double:
case System.Data.DbType.Single:
if (arrParas[i].Value == null)
{
objParas[i] = null;
}
else
{
objParas[i] = double.Parse(arrParas[i].Value.ToString());
}
break;
case System.Data.DbType.Int16:
case System.Data.DbType.Int32:
case System.Data.DbType.Int64:
case System.Data.DbType.SByte:
if (arrParas[i].Value == null)
{
objParas[i] = null;
}
else
{
objParas[i] = long.Parse(arrParas[i].Value.ToString());
}
break;
default:
throw new System.Data.DataException(string.Format("Invalid parameter DataType: {0}", arrParas[i].DbType));
}
}
return(BuildSql(sql, objParas));
}
public string GetDBFieldValue(System.Data.Common.DbParameter oParameter)
{
//*************************************************
//* Devolve valor acompanhado por prefixo e sufixo
//*************************************************
switch (oParameter.DbType.ToString())
{
case "AnsiString":
return("'" + oParameter.Value.ToString() + "'");
case "AnsiStringFixedLength":
return("'" + oParameter.Value.ToString() + "'");
case "Binary":
return(oParameter.Value.ToString().ToDBNumeric());
case "Boolean":
return(oParameter.Value.ToString());
case "Byte":
return(oParameter.Value.ToString().ToDBNumeric());
case "Currency":
return(oParameter.Value.ToString().ToDBNumeric());
case "Date":
if (oParameter.Value.ToString().IsDate())
{
return("'" + DateTime.Parse(oParameter.Value.ToString()).ToString("yyyy-MM-dd HH:mm:ss") + "'");
}
else
{
return("null");
}
case "DateTime":
if (oParameter.Value.ToString().IsDate())
{
return("'" + DateTime.Parse(oParameter.Value.ToString()).ToString("yyyy-MM-dd HH:mm:ss") + "'");
}
else
{
return("null");
}
case "DateTime2":
if (oParameter.Value.ToString().IsDate())
{
return("'" + DateTime.Parse(oParameter.Value.ToString()).ToString("yyyy-MM-dd HH:mm:ss") + "'");
}
else
{
return("null");
}
case "DateTimeOffset":
if (oParameter.Value.ToString().IsDate())
{
return("'" + DateTime.Parse(oParameter.Value.ToString()).ToString("yyyy-MM-dd HH:mm:ss") + "'");
}
else
{
return("null");
}
case "Decimal":
return(oParameter.Value.ToString().ToDBNumeric());
case "Double":
return(oParameter.Value.ToString().ToDBNumeric());
case "Guid":
return("'" + oParameter.Value.ToString() + "'");
case "Int16":
return(oParameter.Value.ToString().ToDBNumeric());
case "Int32":
return(oParameter.Value.ToString().ToDBNumeric());
case "Int64":
return(oParameter.Value.ToString().ToDBNumeric());
case "Object":
return("'" + oParameter.Value.ToString().ToDBNumeric() + "'");
case "SByte":
return(oParameter.Value.ToString().ToDBNumeric());
case "Single":
return(oParameter.Value.ToString().ToDBNumeric());
case "String":
return("'" + oParameter.Value.ToString() + "'");
case "StringFixedLength":
return("'" + oParameter.Value.ToString() + "'");
case "Time":
return("'" + oParameter.Value.ToString() + "'");
case "UInt16":
return(oParameter.Value.ToString().ToDBNumeric());
case "UInt32":
return(oParameter.Value.ToString().ToDBNumeric());
case "UInt64":
return(oParameter.Value.ToString().ToDBNumeric());
case "VarNumeric":
return(oParameter.Value.ToString().ToDBNumeric());
case "Xml":
return("'" + oParameter.Value.ToString() + "'");
default:
return("''");
}
}
private DbCommand CreateInsertCommand(bool option, DataRow row)
{
if (QuotedTableName == String.Empty)
{
return(null);
}
CreateNewCommand(ref _insertCommand);
string command = String.Format("INSERT INTO {0}", QuotedTableName);
string sql;
StringBuilder columns = new StringBuilder();
StringBuilder values = new StringBuilder();
int parmIndex = 1;
DbParameter parameter = null;
foreach (DataRow schemaRow in _dbSchemaTable.Rows)
{
if (!IncludedInInsert(schemaRow))
{
continue;
}
if (columns.Length > 0)
{
columns.Append(", ");
values.Append(", ");
}
if (option)
{
parameter = CreateParameter(_insertCommand, schemaRow, false);
}
else
{
parameter = CreateParameter(_insertCommand, parmIndex++, schemaRow);
}
parameter.SourceVersion = DataRowVersion.Current;
ApplyParameterInfo(parameter, schemaRow, StatementType.Insert, false);
columns.Append(GetQuotedString(parameter.SourceColumn));
// Workaround for columns that may have a default/bound value and for now,
// the framework, don't provide a mechanism to read these values yet
// AllowDBNull and DataRow is used to workaround #385028 by using DEFAULT
string colName = schemaRow ["ColumnName"] as string;
bool allowDBNull = !schemaRow.IsNull("AllowDBNull") & (bool)schemaRow ["AllowDBNull"];
if (!allowDBNull && row != null &&
(row [colName] == DBNull.Value || row [colName] == null))
{
values.Append("DEFAULT");
}
else
{
values.Append(parameter.ParameterName);
}
}
sql = String.Format("{0} ({1}) VALUES ({2})", command, columns.ToString(), values.ToString());
_insertCommand.CommandText = sql;
_dbCommand = _insertCommand;
return(_insertCommand);
}
public IEnumerable <Bunk> QueryBunkListView(DataTransferObject.Foundation.BunkQueryCondition condition, Core.Pagination pagination)
{
var result = new List <Bunk>();
using (var dbOperator = new DbOperator(Provider, ConnectionString))
{
if (!string.IsNullOrWhiteSpace(condition.Airline))
{
dbOperator.AddParameter("@iAirline", condition.Airline.Trim());
}
if (!string.IsNullOrWhiteSpace(condition.Departure))
{
dbOperator.AddParameter("@iDeparture", condition.Departure.Trim());
}
if (!string.IsNullOrWhiteSpace(condition.Arrival))
{
dbOperator.AddParameter("@iArrival", condition.Arrival.Trim());
}
if (!string.IsNullOrWhiteSpace(condition.BunkCode))
{
dbOperator.AddParameter("@iBunkCode", condition.BunkCode.Trim());
}
if (condition.BunkType.HasValue)
{
dbOperator.AddParameter("@iBunkType", (int)condition.BunkType);
}
if (condition.VoyageType.HasValue)
{
dbOperator.AddParameter("@iVoyageType", (byte)condition.VoyageType);
}
if (condition.FlightBeginDate.HasValue)
{
dbOperator.AddParameter("@iFlightBeginDate", condition.FlightBeginDate.Value.Date);
}
if (condition.FlightEndDate.HasValue)
{
dbOperator.AddParameter("@iFlightEndDate", condition.FlightEndDate.Value.Date);
}
if (condition.Status.HasValue)
{
dbOperator.AddParameter("@iStatus", condition.Status);
}
if (pagination != null)
{
dbOperator.AddParameter("@iPageSize", pagination.PageSize);
dbOperator.AddParameter("@iPageIndex", pagination.PageIndex);
}
System.Data.Common.DbParameter totalCount = dbOperator.AddParameter("@oTotalCount");
totalCount.DbType = System.Data.DbType.Int32;
totalCount.Direction = System.Data.ParameterDirection.Output;
using (System.Data.Common.DbDataReader reader = dbOperator.ExecuteReader("dbo.P_QueryBunks", System.Data.CommandType.StoredProcedure))
{
Guid?previousId = null;
Bunk bunk = null;
while (reader.Read())
{
var currentId = reader.GetGuid(1);
if (bunk == null || previousId.Value != currentId)
{
bunk = null;
}
var bunkType = (BunkType)reader.GetInt32(0);
switch (bunkType)
{
case BunkType.Economic:
bunk = loadEconomicBunk(bunk, reader);
break;
case BunkType.FirstOrBusiness:
bunk = loadFirstBusinessBunk(bunk, reader);
break;
case BunkType.Promotion:
bunk = loadPromotionBunk(bunk, reader);
break;
case BunkType.Production:
bunk = loadProductionBunk(bunk, reader);
break;
case BunkType.Transfer:
bunk = loadTransferBunk(bunk, reader);
break;
case BunkType.Free:
bunk = loadFreeBunk(bunk, reader);
break;
case BunkType.Team:
bunk = loadTeamBunk(bunk, reader);
break;
}
bunk.VoyageType = (VoyageTypeValue)reader.GetByte(19);
bunk.TravelType = (TravelTypeValue)reader.GetByte(20);
bunk.PassengerType = (PassengerTypeValue)reader.GetByte(21);
bunk.RefundRegulation = reader.IsDBNull(8) ? string.Empty : reader.GetString(8);
bunk.ChangeRegulation = reader.IsDBNull(16) ? string.Empty : reader.GetString(16);
bunk.EndorseRegulation = reader.IsDBNull(17) ? string.Empty : reader.GetString(17);
bunk.Remarks = reader.IsDBNull(18) ? string.Empty : reader.GetString(18);
if (!previousId.HasValue || previousId.Value != currentId)
{
result.Add(bunk);
previousId = currentId;
}
}
}
if (pagination.GetRowCount)
{
pagination.RowCount = (int)totalCount.Value;
}
}
return(result);
}
private static void PrepareCommand(
DbCommand command,
DbConnection connection,
DbTransaction transaction,
CommandType commandType,
string commandText,
DbParameter[] commandParameters)
{
if (command == null) throw new ArgumentNullException("command");
if (commandText == null || commandText.Length == 0) throw new ArgumentNullException("commandText");
command.Connection = connection;
command.CommandText = commandText;
command.CommandType = commandType;
if (transaction != null)
{
if (transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
command.Transaction = transaction;
}
if (commandParameters != null)
{
AttachParameters(command, commandParameters);
}
return;
}
public DataSet Consultar(DbParameter[] aParams)
{
using (DbConnection conProxy = AbrirConexion())
{
return Consultar(aParams, conProxy, null);
}
}
