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); } }