private void ObtenerMapeo(int IdLayout, int Status = -1) { try { db.Connection_Check(); System.Data.OleDb.OleDbParameter[] parametros = new System.Data.OleDb.OleDbParameter[2]; for (int i = 0; i < 2; i++) { parametros[i] = new System.Data.OleDb.OleDbParameter(); } parametros[0].ParameterName = "P_ID_LAYOUT"; parametros[0].OleDbType = System.Data.OleDb.OleDbType.Integer; parametros[0].Value = IdLayout; parametros[1].ParameterName = "P_STATUS"; parametros[1].OleDbType = System.Data.OleDb.OleDbType.SmallInt; parametros[1].Value = Status; this.dtMapeo = db.GetTable("SEL_MAPEO", parametros); } catch (Exception Error) { string strMsgError = Error.Message; } }
private bool GiaEsisteArchivioFoto_pri(string descrizione) { var bb = false; try { var p = System.IO.Path.Combine(Server.MapPath("public"), "/files/files.mdb"); using (var c = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + p)) { c.Open(); try { var n = 0; var s = "SELECT count(*) Num FROM File where nome = :nome"; var pr = new System.Data.OleDb.OleDbParameter[] { new System.Data.OleDb.OleDbParameter("nome", descrizione + ".zip") }; using (var m = new System.Data.OleDb.OleDbCommand(s, c)) { m.Parameters.AddRange(pr); using (var dr = m.ExecuteReader()) { while (dr.HasRows && dr.Read()) { try { n = Convert.ToInt32(dr["Num"]); } catch { n = 0; } } dr.Close(); } } bb = n > 0; } catch { //error } c.Close(); } } catch { //error } return(bb); }
internal static DbParameter NewPar(string Nome, object Valore, DbType tipo) { DbParameter o = null; switch (DataBaseAttuale) { case DataBase.Access: o = new System.Data.OleDb.OleDbParameter(Nome, tipo); break; case DataBase.SQLite: if (tipo == DbType.Date || tipo == DbType.DateTime) { //"YYYY-MM-DD HH:MM:SS.SSS" Valore = ((DateTime)Valore).ToString("yyyy-MM-dd HH:mm:ss.fff"); tipo = DbType.String; } //o = new System.Data.SQLite.SQLiteParameter(Nome, tipo); break; case DataBase.FireBird: o = new FirebirdSql.Data.FirebirdClient.FbParameter(Nome, tipo); break; default: throw new NotImplementedException(); } o.Value = Valore; return(o); }
private void ObtenerMapeo(int IdLayout, int Status = -1) { try { db.Connection_Check(); System.Data.OleDb.OleDbParameter[] parametros = new System.Data.OleDb.OleDbParameter[2]; for (int i = 0; i < 2; i++) { parametros[i] = new System.Data.OleDb.OleDbParameter(); } parametros[0].ParameterName = "P_ID_LAYOUT"; parametros[0].OleDbType = System.Data.OleDb.OleDbType.Integer; parametros[0].Value = IdLayout; parametros[1].ParameterName = "P_STATUS"; parametros[1].OleDbType = System.Data.OleDb.OleDbType.SmallInt; parametros[1].Value = Status; this.dtMapeo = db.GetTable("SEL_MAPEO", parametros); } catch (Exception Error) { string strMsgError = Error.Message; } }
public static DbParameter NewPar(string Nome, object Valore, DbType tipo) { DbParameter o = null; if (DataBaseAttuale == DataBase.Access) { o = new System.Data.OleDb.OleDbParameter(Nome, tipo); } else if (DataBaseAttuale == DataBase.SQLite) { if (tipo == DbType.Date || tipo == DbType.DateTime) { //"YYYY-MM-DD HH:MM:SS.SSS" Valore = ((DateTime)Valore).ToString("yyyy-MM-dd HH:mm:ss.fff"); tipo = DbType.String; } #if __MonoCS__ o = new Mono.Data.Sqlite.SqliteParameter(Nome, tipo); #else o = new System.Data.SQLite.SQLiteParameter(Nome, tipo); #endif } o.Value = Valore; return(o); }
private void registraCampoEnBD(Registro registro) { try { // Actualiza en base de datos db.Connection_Check(); System.Data.OleDb.OleDbParameter[] parametros = new System.Data.OleDb.OleDbParameter[9]; for (int i = 0; i < 9; i++) { parametros[i] = new System.Data.OleDb.OleDbParameter(); } parametros[0].ParameterName = "P_ID_BITACORA"; parametros[0].OleDbType = System.Data.OleDb.OleDbType.BigInt; parametros[0].Value = this.lngIdBitacora; parametros[1].ParameterName = "P_NOM_CAMPO"; parametros[1].OleDbType = System.Data.OleDb.OleDbType.VarChar; parametros[1].Value = registro.NombreCampo; parametros[2].ParameterName = "P_NUM_CAMPO"; parametros[2].OleDbType = System.Data.OleDb.OleDbType.Integer; parametros[2].Value = registro.Campo; parametros[3].ParameterName = "P_NUM_REGISTRO"; parametros[3].OleDbType = System.Data.OleDb.OleDbType.BigInt; parametros[3].Value = registro.NumeroRegistro; parametros[4].ParameterName = "P_VALOR"; parametros[4].OleDbType = System.Data.OleDb.OleDbType.VarChar; parametros[4].Value = registro.Valor; parametros[5].ParameterName = "P_REG_OK"; parametros[5].OleDbType = System.Data.OleDb.OleDbType.SmallInt; parametros[5].Value = ((registro.Correcto) ? 1 : 0); parametros[6].ParameterName = "P_REG_OM"; parametros[6].OleDbType = System.Data.OleDb.OleDbType.SmallInt; parametros[6].Value = ((registro.Omitido) ? 1 : 0); parametros[7].ParameterName = "P_REG_ER"; parametros[7].OleDbType = System.Data.OleDb.OleDbType.SmallInt; parametros[7].Value = ((registro.Erroneo) ? 1 : 0); parametros[8].ParameterName = "P_OBS"; parametros[8].OleDbType = System.Data.OleDb.OleDbType.VarChar; parametros[8].Value = registro.Observaciones; db.ExecutaProcedureNonQuery("INS_REGISTRO_IMPORTACION", parametros); } catch (Exception Error) { string msgMsgError = Error.Message; this.Observaciones = this.Observaciones + ((this.Observaciones == "") ? "" : "|") + "#Error - " + msgMsgError; } }
private void registrarBitacora() { try { DataTable dt; db.Connection_Check(); System.Data.OleDb.OleDbParameter[] parametros = new System.Data.OleDb.OleDbParameter[5]; for (int i = 0; i < 5; i++) { parametros[i] = new System.Data.OleDb.OleDbParameter(); } parametros[0].ParameterName = "P_ID_LAYOUT"; parametros[0].OleDbType = System.Data.OleDb.OleDbType.Integer; parametros[0].Value = this.intIdLayout; parametros[1].ParameterName = "P_USER"; parametros[1].OleDbType = System.Data.OleDb.OleDbType.VarChar; parametros[1].Value = strUsuario; parametros[2].ParameterName = "P_ARCHIVO"; parametros[2].OleDbType = System.Data.OleDb.OleDbType.VarChar; parametros[2].Value = archivo.Nombre; parametros[3].ParameterName = "P_EXTENSION"; parametros[3].OleDbType = System.Data.OleDb.OleDbType.VarChar; parametros[3].Value = archivo.Extension; parametros[4].ParameterName = "P_OBS"; parametros[4].OleDbType = System.Data.OleDb.OleDbType.VarChar; if (this.strObservaciones.Length > 255) { parametros[4].Value = strObservaciones.Substring(0, 254); } else { parametros[4].Value = strObservaciones; } dt = db.GetTable("INS_BITACORA_IMPORTACION", parametros); foreach (DataRow row in dt.Rows) { this.lngIdBitacora = long.Parse(row["BII_CVE"].ToString()); } } catch (Exception Error) { string msgMsgError = Error.Message; this.Observaciones = this.Observaciones + ((this.Observaciones == "") ? "" : "|") + "#Error - " + msgMsgError; } }
public static System.Data.OleDb.OleDbCommand Comando(string sql, params object[] args) { System.Data.OleDb.OleDbCommand com = new System.Data.OleDb.OleDbCommand(sql, cnx); for (System.Int32 i = 0; i < args.Length; i++) { System.Data.OleDb.OleDbParameter par = new System.Data.OleDb.OleDbParameter("DPeriodo_" + i, args[i]); com.Parameters.Add(par); } return com; }
/// <summary> /// 转换为数据库的对应参数类型 /// </summary> /// <param name="dbType"></param> /// <returns></returns> public System.Data.Common.DbParameter[] ConvertToDbParameter(eDBType dbType) { if (_dicParamNameValue == null | _dicParamNameValue.Count == 0) { return(new System.Data.Common.DbParameter[0]); } System.Data.Common.DbParameter[] pras = new System.Data.Common.DbParameter[_dicParamNameValue.Count]; switch (dbType) { case eDBType.Oracle: System.Data.OracleClient.OracleParameter[] pOras = new System.Data.OracleClient.OracleParameter[_dicParamNameValue.Count]; int iOra = 0; foreach (string name in _dicParamNameValue.Keys) { pOras[iOra++] = new System.Data.OracleClient.OracleParameter(name, _dicParamNameValue[name]); } return(pOras); case eDBType.SqlServer: System.Data.SqlClient.SqlParameter[] pSqls = new System.Data.SqlClient.SqlParameter[_dicParamNameValue.Count]; int iSql = 0; foreach (string name in _dicParamNameValue.Keys) { pSqls[iSql++] = new System.Data.SqlClient.SqlParameter(name, _dicParamNameValue[name]); } return(pSqls); case eDBType.Access: System.Data.OleDb.OleDbParameter[] oSqls = new System.Data.OleDb.OleDbParameter[_dicParamNameValue.Count]; int iOledb = 0; foreach (string name in _dicParamNameValue.Keys) { oSqls[iOledb++] = new System.Data.OleDb.OleDbParameter(name, _dicParamNameValue[name]); } return(oSqls); case eDBType.MySql: case eDBType.DB2: case eDBType.Sybase: default: break; } return(new System.Data.Common.DbParameter[0]); }
public void UpLoadFile(Newtonsoft.Json.Linq.JObject requestValue, Newtonsoft.Json.Linq.JObject Data, MESStationReturn StationReturn) { MESDBHelper.OleExec SFCDB = this.DBPools["SFCDB"].Borrow(); SFCDB.BeginTrain(); try { T_R_FILE TRF = new T_R_FILE(SFCDB, DB_TYPE_ENUM.Oracle); Row_R_FILE RRF = (Row_R_FILE)TRF.NewRow(); RRF.ID = TRF.GetNewID(BU, SFCDB); RRF.NAME = Data["Name"].ToString(); RRF.FILENAME = Data["FileName"].ToString(); RRF.MD5 = Data["MD5"].ToString(); RRF.USETYPE = Data["UseType"].ToString(); RRF.STATE = "1"; RRF.VALID = 1; //不使用CLOB字段 //RRF.CLOB_FILE = ":CLOB_FILE";// Data["Bas64File"].ToString(); RRF.BLOB_FILE = ":BLOB_FILE"; RRF.EDIT_EMP = LoginUser.EMP_NO; RRF.EDIT_TIME = DateTime.Now; SFCDB.ThrowSqlExeception = true; //將同類文件改為歷史版本 TRF.SetFileDisableByName(RRF.NAME, RRF.USETYPE, SFCDB); string strSql = RRF.GetInsertString(this.DBTYPE); strSql = strSql.Replace("':CLOB_FILE'", ":CLOB_FILE"); strSql = strSql.Replace("':BLOB_FILE'", ":BLOB_FILE"); System.Data.OleDb.OleDbParameter p = new System.Data.OleDb.OleDbParameter(":BLOB_FILE", System.Data.OleDb.OleDbType.Binary); string B64 = Data["Bas64File"].ToString(); string b64 = B64.Remove(0, B64.LastIndexOf(',') + 1); byte[] data = Convert.FromBase64String(b64); p.Value = data; //new System.Data.OleDb.OleDbParameter(":CLOB_FILE", Data["Bas64File"].ToString()), SFCDB.ExecSqlNoReturn(strSql, new System.Data.OleDb.OleDbParameter[] { p }); //SFCDB.ExecSQL(RRF.GetInsertString(this.DBTYPE)); SFCDB.CommitTrain(); StationReturn.Status = StationReturnStatusValue.Pass; } catch (Exception ee) { SFCDB.RollbackTrain(); //this.DBPools["SFCDB"].Return(SFCDB); StationReturn.Status = StationReturnStatusValue.Fail; StationReturn.MessageCode = "MES00000037"; StationReturn.MessagePara.Add(ee.Message); } SFCDB.ThrowSqlExeception = false; this.DBPools["SFCDB"].Return(SFCDB); }
/// <summary> /// Creates a derived IDbDataParameter for the table and fields specified /// </summary> /// <param name="table">Name of the table of the field asscoiated to the param</param> /// <param name="field">Name of the field associated to the parameter</param> /// <param name="nparam">Ordinal position of the parameter (0 is first)</param> /// <param name="tableInfo">Hashtable with the schema of the table. NOT used in OLEDB (parameters cannot be filled)</param> /// <param name="stringRep">Representation of the parameter in the SQL String (i.e. question-mark in OLEDB)</param> /// <param name="bOriginalVersion">If true the value of the parameter is bound to the original version of the value in the datarow</param> /// <returns>The newly created parameter object</returns> public IDataParameter CreateParameter(string table, string field, int nparam, Hashtable tableInfo, out string stringRep, bool bOriginalVersion) { stringRep = "?"; // Creates a EMPTY parameter (OLEDB params cannot be filled). System.Data.OleDb.OleDbParameter param = new System.Data.OleDb.OleDbParameter(); param.ParameterName = "param" + nparam; param.SourceColumn = field; if (bOriginalVersion) { param.SourceVersion = DataRowVersion.Original; } return(param); }
public int Update(object model, object originalModel, string[] keyColumns, params string[] identityColumns) { Command.Parameters.Clear(); System.Reflection.PropertyInfo[] properties = model.GetType().GetProperties().Where(t => !identityColumns.Contains(t.Name)).ToArray(); List <System.Data.OleDb.OleDbParameter> parameters = new List <System.Data.OleDb.OleDbParameter>(); foreach (var property in properties) { System.Data.OleDb.OleDbParameter parameter = new System.Data.OleDb.OleDbParameter("@" + property.Name, property.GetValue(model, null) ?? DBNull.Value); Command.Parameters.Add(parameter); parameters.Add(parameter); } System.Reflection.PropertyInfo[] keyProperties = model.GetType().GetProperties().Where(t => keyColumns.Contains(t.Name)).ToArray(); foreach (var property in keyProperties) { System.Data.OleDb.OleDbParameter originalParameter = new System.Data.OleDb.OleDbParameter("@original_" + property.Name, property.GetValue(originalModel, null) ?? DBNull.Value); Command.Parameters.Add(originalParameter); parameters.Add(originalParameter); } Command.CommandText = string.Format("UPDATE [{0}] SET {1} WHERE {2}", model.GetType().Name, properties.Select(t => "[" + t.Name + "]" + "=" + "@" + t.Name).Aggregate((a, b) => a + "," + b), keyProperties.Select(t => "[" + t.Name + "]" + "=" + "@original_" + t.Name).Aggregate((a, b) => a + " AND " + b)); Command.CommandType = CommandType.Text; System.Data.ConnectionState previousConnectionState = Connection.State; if (((Connection.State & System.Data.ConnectionState.Open) != System.Data.ConnectionState.Open)) { try { Connection.Open(); } catch (Exception ex) { throw new Exception("ارتباط با سرور اس کیو ال برقرار نیست" + "\n\n" + ex.Message); } } int i; try { i = Command.ExecuteNonQuery(); } finally { if ((previousConnectionState == System.Data.ConnectionState.Closed)) { Connection.Close(); } } return(i); }
protected IDataParameter GetParameter <T>(string paramName, T paramValue) { IDataParameter res = null; if (this._dbProvider == ProviderType.Sql) { res = new System.Data.SqlClient.SqlParameter(paramName, paramValue); } else if (this._dbProvider == ProviderType.OleDb) { res = new System.Data.OleDb.OleDbParameter(paramName, paramValue); } return(res); }
private void transferirATabla(bool Correctos, bool Omitidos, bool Erroneos) { try { // Estructurar datos obtenidos en forma de tabla y los inserta en la tabla deseada db.Connection_Check(); System.Data.OleDb.OleDbParameter[] parametros = new System.Data.OleDb.OleDbParameter[6]; for (int i = 0; i < 6; i++) { parametros[i] = new System.Data.OleDb.OleDbParameter(); } parametros[0].ParameterName = "P_ID_BITACORA"; parametros[0].OleDbType = System.Data.OleDb.OleDbType.BigInt; parametros[0].Value = this.lngIdBitacora; parametros[1].ParameterName = "P_ID_LAYOUT"; parametros[1].OleDbType = System.Data.OleDb.OleDbType.Integer; parametros[1].Value = this.intIdLayout; parametros[2].ParameterName = "P_TABLA_DESTINO"; parametros[2].OleDbType = System.Data.OleDb.OleDbType.VarChar; parametros[2].Value = this.layout.TablaDestino; parametros[3].ParameterName = "P_REG_OK"; parametros[3].OleDbType = System.Data.OleDb.OleDbType.Integer; parametros[3].Value = ((Correctos) ? 1 : 0); parametros[4].ParameterName = "P_REG_OM"; parametros[4].OleDbType = System.Data.OleDb.OleDbType.Integer; parametros[4].Value = ((Omitidos) ? 1 : 0); parametros[5].ParameterName = "P_REG_ER"; parametros[5].OleDbType = System.Data.OleDb.OleDbType.Integer; parametros[5].Value = ((Erroneos) ? 1 : 0); db.ExecutaProcedureNonQuery("INS_TABLA_IMPORTACION", parametros); } catch (Exception Error) { string msgMsgError = Error.Message; this.Observaciones = this.Observaciones + ((this.Observaciones == "") ? "" : "|") + "#Error - " + msgMsgError; } }
public static DbParameter MakeIntParam(DbConfig dbConfig, string paraName) { DbParameter dbParam = null; switch (dbConfig.DbType) { case "System.Data.SqlClient": dbParam = new System.Data.SqlClient.SqlParameter(paraName, SqlDbType.Int); break; case "System.Data.OleDb": dbParam = new System.Data.OleDb.OleDbParameter(paraName, System.Data.OleDb.OleDbType.Integer); break; default: break; } return(dbParam); }
public static DbParameter MakeParam(DbConfig dbConfig, string paraName, DbType dType, int dSize) { DbParameter dbParam = null; switch (dbConfig.DbType) { case "System.Data.SqlClient": dbParam = new System.Data.SqlClient.SqlParameter(paraName, (SqlDbType)dType, dSize); break; case "System.Data.OleDb": dbParam = new System.Data.OleDb.OleDbParameter(paraName, (System.Data.OleDb.OleDbType)dType, dSize); break; default: break; } return(dbParam); }
internal static bool SecurityIsAllowed(SecurityAction action, out PermissionsID maxPermID) { int userID = SharedSupport.GetUserIdentity(); System.Data.OleDb.OleDbConnection con = new System.Data.OleDb.OleDbConnection(SharedSupport.ConnectionString); System.Data.OleDb.OleDbDataAdapter cmd = new System.Data.OleDb.OleDbDataAdapter("Security_GlobalIsAllowed", con); System.Data.DataSet ds = new System.Data.DataSet(); System.Data.OleDb.OleDbParameter param; cmd.SelectCommand.CommandType = System.Data.CommandType.StoredProcedure; param = new System.Data.OleDb.OleDbParameter("@UserID", System.Data.OleDb.OleDbType.Integer); param.Value = userID; cmd.SelectCommand.Parameters.Add(param); param = new System.Data.OleDb.OleDbParameter("@ActionID", System.Data.OleDb.OleDbType.Integer); param.Value = (int)action; cmd.SelectCommand.Parameters.Add(param); maxPermID = PermissionsID.Student; try { cmd.Fill(ds); } catch (System.Exception e) { SharedSupport.HandleError(e); } try { if (Convert.ToInt32(ds.Tables[0].Rows[0]["UserID"]) == userID) { maxPermID = (PermissionsID)Convert.ToInt32(ds.Tables[0].Rows[0]["RoleID"]); return(true); } } catch { return(false); } return(false); }
/// <summary> /// Insert /// </summary> /// <param name="model">یک شی معادل با جدول پایگاه داده</param> /// <param name="identityColumns">نام ستون هایی که آیدنتیتی تعریف شده اند</param> /// <returns>تعداد سطرهای درج شده برگشت داده میشود</returns> public int Insert(object model, params string[] identityColumns) { Command.Parameters.Clear(); System.Reflection.PropertyInfo[] properties = model.GetType().GetProperties().Where(t => !identityColumns.Contains(t.Name)).ToArray(); List <System.Data.OleDb.OleDbParameter> parameters = new List <System.Data.OleDb.OleDbParameter>(); int index = 0; foreach (var property in properties) { System.Data.OleDb.OleDbParameter p = new System.Data.OleDb.OleDbParameter("@p" + (index++).ToString(), property.GetValue(model, null) ?? DBNull.Value); Command.Parameters.Add(p); parameters.Add(p); } Command.CommandText = string.Format("INSERT INTO [{0}] ({1}) VALUES({2})", model.GetType().Name, properties.Select(t => "[" + t.Name + "]").Aggregate((a, b) => a + "," + b), parameters.Select(t => t.ParameterName).Aggregate((a, b) => a + "," + b)); Command.CommandType = CommandType.Text; System.Data.ConnectionState previousConnectionState = Connection.State; if (((Connection.State & System.Data.ConnectionState.Open) != System.Data.ConnectionState.Open)) { try { Connection.Open(); } catch (Exception ex) { throw new Exception("ارتباط با سرور اس کیو ال برقرار نیست" + "\n\n" + ex.Message); } } int i; try { i = Command.ExecuteNonQuery(); } finally { if ((previousConnectionState == System.Data.ConnectionState.Closed)) { Connection.Close(); } } return(i); }
public DbProvider AppendParam(string paraName) { DbParameter dbParam = null; switch (_dbConfig.DbType) { case "System.Data.SqlClient": dbParam = new System.Data.SqlClient.SqlParameter(paraName, SqlDbType.Int); break; case "System.Data.OleDb": dbParam = new System.Data.OleDb.OleDbParameter(paraName, System.Data.OleDb.OleDbType.Integer); break; default: break; } dbParameters.Add(dbParam); return(this); }
public static DbParameter NewPar(string Nome, object Valore) { DbParameter o = null; if (Valore is DateTime) { if (DataBaseAttuale == DataBase.Access) { o = new System.Data.OleDb.OleDbParameter(Nome, DbType.DateTime); } else if (DataBaseAttuale == DataBase.SQLite) { Valore = cGB.DateToSQLite((DateTime)Valore); //Valore = ((DateTime)Valore).ToString("yyyy-MM-dd HH:mm:ss.fff"); #if __MonoCS__ o = new Mono.Data.Sqlite.SqliteParameter(Nome, DbType.String); #else o = new System.Data.SQLite.SQLiteParameter(Nome, DbType.String); #endif } o.Value = Valore; } else { if (DataBaseAttuale == DataBase.Access) { o = new System.Data.OleDb.OleDbParameter(Nome, Valore); } else if (DataBaseAttuale == DataBase.SQLite) #if __MonoCS__ { o = new Mono.Data.Sqlite.SqliteParameter(Nome, Valore); } #else { o = new System.Data.SQLite.SQLiteParameter(Nome, Valore); } #endif } return(o); }
private void ObtenerMetodoValidacion(int IdMetodo, int Status = -1) { try { DataTable dt; db.Connection_Check(); System.Data.OleDb.OleDbParameter[] parametros = new System.Data.OleDb.OleDbParameter[2]; for (int i = 0; i < 2; i++) { parametros[i] = new System.Data.OleDb.OleDbParameter(); } parametros[0].ParameterName = "P_ID_METODO"; parametros[0].OleDbType = System.Data.OleDb.OleDbType.Integer; parametros[0].Value = IdMetodo; parametros[1].ParameterName = "P_STATUS"; parametros[1].OleDbType = System.Data.OleDb.OleDbType.SmallInt; parametros[1].Value = Status; dt = db.GetTable("SEL_METODO_VALIDACION", parametros); // Asignación de valores obtenidos foreach (DataRow row in dt.Rows) { strNombre = row["MVA_NOM"].ToString(); strParametros = row["MVA_PAR"].ToString(); strEjemplo = row["MVA_ESX"].ToString(); strRutaSistema = row["MVA_RSY"].ToString(); strDescripcion = row["MVA_DES"].ToString(); } } catch (Exception Error) { string strMsgError = Error.Message; } }
public static System.Data.IDbDataParameter GetParameter(string parameterName, object value) { System.Data.IDbDataParameter result = null; // شناسايی می کنيم که بانک اطلاعاتی ما چسيت؟ string providerName = System.Configuration.ConfigurationManager.AppSettings["PROVIDER"]; switch (providerName.ToUpper()) { case "SQLSERVER": { // اگر اس کيو ال سرور بود result = new System.Data.SqlClient.SqlParameter(parameterName: parameterName, value: value); break; } case "OLEDB": { result = new System.Data.OleDb.OleDbParameter(name: parameterName, value: value); break; } case "ODBC": { result = new System.Data.Odbc.OdbcParameter(name: parameterName, value: value); break; } } return(result); }
/// <summary> /// 初始化 com.individual.helper.ComDbParameter 类的新实例 /// </summary> /// <param name="DataBaseType">数据库类型</param> /// <param name="ParameterName">参数名称</param> /// <param name="DbType">参数数据类型</param> /// <param name="Value">参数值</param> /// <param name="Direction">该值指示参数是只可输入、只可输出、双向还是存储过程返回值参数,System.Data.ParameterDirection 值之一</param> public static DbParameter CreateDbParameter(DataBaseType DataBaseType, string ParameterName, DbParameterType DbType, object Value, ParameterDirection Direction) { if (DataBaseType == helper.DataBaseType.Access) { System.Data.OleDb.OleDbParameter param = new System.Data.OleDb.OleDbParameter(ParameterName, GetOleDbType(DbType)); param.Direction = Direction; param.Value = Value; param.Size = 8000; return param; } else if (DataBaseType == helper.DataBaseType.MySql) { MySql.Data.MySqlClient.MySqlParameter param = new MySql.Data.MySqlClient.MySqlParameter(ParameterName, GetMySqlDbType(DbType)); param.Direction = Direction; param.Value = Value; param.Size = 8000; return param; } else if (DataBaseType == helper.DataBaseType.Oracle) { System.Data.OracleClient.OracleParameter param = new System.Data.OracleClient.OracleParameter(ParameterName, GetOracleType(DbType)); param.Direction = Direction; param.Value = Value; param.Size = 8000; return param; } else { System.Data.SqlClient.SqlParameter param = new System.Data.SqlClient.SqlParameter(ParameterName, GetSqlDbType(DbType)); param.Direction = Direction; param.Value = Value; param.Size = 8000; return param; } }
public void Remove(System.Data.OleDb.OleDbParameter value) { }
public int IndexOf(System.Data.OleDb.OleDbParameter value) { throw null; }
public void Insert(int index, System.Data.OleDb.OleDbParameter value) { }
public System.Data.OleDb.OleDbParameter Add(System.Data.OleDb.OleDbParameter value) { throw null; }
public bool Contains(System.Data.OleDb.OleDbParameter value) { throw null; }
private void actualizarBitacora() { try { object rowQuery; // Obtiene valores totales de importación // Registros correctos rowQuery = resultados.Compute("Count(RES_ROK)", "RES_ROK = true"); this.lngCorrectos = long.Parse(rowQuery.ToString()); // Registros omitidos rowQuery = resultados.Compute("Count(RES_ROM)", "RES_ROM = true"); this.lngOmitidos = long.Parse(rowQuery.ToString()); // Registros erróneos rowQuery = resultados.Compute("Count(RES_RER)", "RES_RER = true"); this.lngErroneos = long.Parse(rowQuery.ToString()); // Actualiza en base de datos db.Connection_Check(); System.Data.OleDb.OleDbParameter[] parametros = new System.Data.OleDb.OleDbParameter[5]; for (int i = 0; i < 5; i++) { parametros[i] = new System.Data.OleDb.OleDbParameter(); } parametros[0].ParameterName = "P_ID_BITACORA"; parametros[0].OleDbType = System.Data.OleDb.OleDbType.BigInt; parametros[0].Value = this.lngIdBitacora; parametros[1].ParameterName = "P_REG_OK"; parametros[1].OleDbType = System.Data.OleDb.OleDbType.BigInt; parametros[1].Value = this.lngCorrectos; parametros[2].ParameterName = "P_REG_OM"; parametros[2].OleDbType = System.Data.OleDb.OleDbType.BigInt; parametros[2].Value = this.lngOmitidos; parametros[3].ParameterName = "P_REG_ER"; parametros[3].OleDbType = System.Data.OleDb.OleDbType.BigInt; parametros[3].Value = this.lngErroneos; parametros[4].ParameterName = "P_OBS"; parametros[4].OleDbType = System.Data.OleDb.OleDbType.VarChar; if (this.strObservaciones.Length > 255) { parametros[4].Value = strObservaciones.Substring(0, 254); } else { parametros[4].Value = strObservaciones; } db.ExecutaProcedureNonQuery("UPD_BITACORA_IMPORTACION", parametros); } catch (Exception Error) { string msgMsgError = Error.Message; this.Observaciones = this.Observaciones + ((this.Observaciones == "") ? "" : "|") + "#Error - " + msgMsgError; } }
/// <summary> /// 取指纹机考勤数据 /// </summary> /// <param name="path">数据库路径</param> /// <param name="day">读取指定日期</param> /// <returns></returns> public int Get_KQ(string path, DateTime day) { ODBHelper ODB = new ODBHelper(path);//用于操作 access 数据库 DateTime start = day; DateTime end = day.AddDays(1); string SQL = "SELECT b.name, a.checktime FROM CHECKINOUT a left join USERINFO b on a.USERID=b.USERID where a.checktime between @起始时间 and @结束时间"; System.Data.OleDb.OleDbParameter par1 = new System.Data.OleDb.OleDbParameter("起始时间", start.ToString("yyyy-MM-dd HH:mm:ss")); System.Data.OleDb.OleDbParameter par2 = new System.Data.OleDb.OleDbParameter("结束时间", end.ToString("yyyy-MM-dd HH:mm:ss")); Sys.DebugMes(start.ToLongDateString() + " " + end.ToLongDateString()); DataTable dt = ODB.RunSqlDataTable(SQL, new System.Data.OleDb.OleDbParameter[] { par1, par2 }); int nu = 0; if (dt == null) { Sys.DebugMes(ODB.ERR); } else { Sys.DebugMes(dt.Rows.Count.ToString()); if (this.SJ_指纹处理 != null) { Even_指纹系统处理 e = new Even_指纹系统处理(); e.mesg = "读取指纹数据:" + dt.Rows.Count.ToString() + "条"; //触发事件 this.SJ_指纹处理(this, e); } if (dt.Rows.Count > 0) { nu = Update_KQ(dt);// 插入新指纹数据 并更新数据库数据 } } return nu; }
public DbParameter[] GetSelectParamArray <T>(T t, MyDBType mydbtype) { string tStr = string.Empty; if (t == null) { return(null); } System.Reflection.PropertyInfo[] properties = t.GetType().GetProperties(System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.Public); if (properties.Length <= 0) { return(null); } System.Collections.Generic.List <DbParameter> list = new System.Collections.Generic.List <DbParameter>(); DbParameter[] paramArray = null; int lenght = 0; for (int i = 0; i < properties.Length; i++) { string name = properties[i].Name; //���� object value = properties[i].GetValue(t, null); //ֵ if (value != null) { if (CanAdd(name, t.GetType().Name)) { lenght++; } } } switch (mydbtype) { case MyDBType.Access: paramArray = new System.Data.OleDb.OleDbParameter[lenght]; break; case MyDBType.Sql: paramArray = new System.Data.SqlClient.SqlParameter[lenght]; break; case MyDBType.Oracle: paramArray = new Oracle.DataAccess.Client.OracleParameter[lenght]; break; } int index = 0; for (int i = 0; i < properties.Length; i++) { string name = properties[i].Name; //���� object value = properties[i].GetValue(t, null); //ֵ string type = properties[i].PropertyType.Name; if (value != null) { if (CanAdd(name, t.GetType().Name)) { switch (mydbtype) { case MyDBType.Access: paramArray[index] = new System.Data.OleDb.OleDbParameter("@" + name, value); break; case MyDBType.Sql: paramArray[index] = new System.Data.SqlClient.SqlParameter("@" + name, value); break; case MyDBType.Oracle: paramArray[index] = new Oracle.DataAccess.Client.OracleParameter(":" + name, value); break; } index++; } } } return(paramArray); }
public DbParameter[] GetParamArray <T>(T t, MyDBType mydbtype) { string tStr = string.Empty; if (t == null) { return(null); } System.Reflection.PropertyInfo[] properties = t.GetType().GetProperties(System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.Public); if (properties.Length <= 0) { return(null); } System.Collections.Generic.List <DbParameter> list = new System.Collections.Generic.List <DbParameter>(); DbParameter[] paramArray = null; switch (mydbtype) { case MyDBType.Access: paramArray = new System.Data.OleDb.OleDbParameter[properties.Length]; break; case MyDBType.Sql: paramArray = new System.Data.SqlClient.SqlParameter[properties.Length]; break; case MyDBType.Oracle: paramArray = new Oracle.DataAccess.Client.OracleParameter[properties.Length]; break; } for (int i = 0; i < properties.Length; i++) { string name = properties[i].Name; //���� object value = properties[i].GetValue(t, null); //ֵ string type = properties[i].PropertyType.Name; switch (mydbtype) { case MyDBType.Access: if (null == value) { paramArray[i] = new System.Data.OleDb.OleDbParameter("@" + name, DBNull.Value); } else { paramArray[i] = new System.Data.OleDb.OleDbParameter("@" + name, value); } break; case MyDBType.Sql: if (null == value) { paramArray[i] = new System.Data.SqlClient.SqlParameter("@" + name, DBNull.Value); } else { paramArray[i] = new System.Data.SqlClient.SqlParameter("@" + name, value); } break; case MyDBType.Oracle: if (null == value) { paramArray[i] = new Oracle.DataAccess.Client.OracleParameter(":" + name, DBNull.Value); } else { paramArray[i] = new Oracle.DataAccess.Client.OracleParameter(":" + name, value); } break; } } return(paramArray); }
private void registrarBitacora() { try { DataTable dt; db.Connection_Check(); System.Data.OleDb.OleDbParameter[] parametros = new System.Data.OleDb.OleDbParameter[5]; for (int i = 0; i < 5; i++) { parametros[i] = new System.Data.OleDb.OleDbParameter(); } parametros[0].ParameterName = "P_ID_LAYOUT"; parametros[0].OleDbType = System.Data.OleDb.OleDbType.Integer; parametros[0].Value = this.intIdLayout; parametros[1].ParameterName = "P_USER"; parametros[1].OleDbType = System.Data.OleDb.OleDbType.VarChar; parametros[1].Value = strUsuario; parametros[2].ParameterName = "P_ARCHIVO"; parametros[2].OleDbType = System.Data.OleDb.OleDbType.VarChar; parametros[2].Value = archivo.Nombre; parametros[3].ParameterName = "P_EXTENSION"; parametros[3].OleDbType = System.Data.OleDb.OleDbType.VarChar; parametros[3].Value = archivo.Extension; parametros[4].ParameterName = "P_OBS"; parametros[4].OleDbType = System.Data.OleDb.OleDbType.VarChar; if (this.strObservaciones.Length > 255) { parametros[4].Value = strObservaciones.Substring(0, 254); } else { parametros[4].Value = strObservaciones; } dt = db.GetTable("INS_BITACORA_IMPORTACION", parametros); foreach (DataRow row in dt.Rows) { this.lngIdBitacora = long.Parse(row["BII_CVE"].ToString()); } } catch (Exception Error) { string msgMsgError = Error.Message; this.Observaciones = this.Observaciones + ((this.Observaciones == "") ? "" : "|") + "#Error - " + msgMsgError; } }
/// <summary> /// 转换为数据库的对应参数类型 /// </summary> /// <param name="dbType"></param> /// <returns></returns> public System.Data.Common.DbParameter[] ConvertToDbParameter(eDBType dbType) { if (_dicParamNameValue == null | _dicParamNameValue.Count == 0) return new System.Data.Common.DbParameter[0]; System.Data.Common.DbParameter[] pras = new System.Data.Common.DbParameter[_dicParamNameValue.Count]; switch (dbType) { case eDBType.Oracle: System.Data.OracleClient.OracleParameter[] pOras = new System.Data.OracleClient.OracleParameter[_dicParamNameValue.Count]; int iOra = 0; foreach (string name in _dicParamNameValue.Keys) { pOras[iOra++] = new System.Data.OracleClient.OracleParameter(name, _dicParamNameValue[name]); } return pOras; case eDBType.SqlServer: System.Data.SqlClient.SqlParameter[] pSqls = new System.Data.SqlClient.SqlParameter[_dicParamNameValue.Count]; int iSql = 0; foreach (string name in _dicParamNameValue.Keys) { pSqls[iSql++] = new System.Data.SqlClient.SqlParameter(name, _dicParamNameValue[name]); } return pSqls; case eDBType.Access: System.Data.OleDb.OleDbParameter[] oSqls = new System.Data.OleDb.OleDbParameter[_dicParamNameValue.Count]; int iOledb = 0; foreach (string name in _dicParamNameValue.Keys) { oSqls[iOledb++] = new System.Data.OleDb.OleDbParameter(name, _dicParamNameValue[name]); } return oSqls; case eDBType.MySql: case eDBType.DB2: case eDBType.Sybase: default: break; } return new System.Data.Common.DbParameter[0]; }
private void ObtenerConfiguracionLayout(int IdLayout, int Status = -1) { try { DataTable dt; db.Connection_Check(); System.Data.OleDb.OleDbParameter[] parametros = new System.Data.OleDb.OleDbParameter[2]; for (int i = 0; i < 2; i++) { parametros[i] = new System.Data.OleDb.OleDbParameter(); } parametros[0].ParameterName = "P_ID_LAYOUT"; parametros[0].OleDbType = System.Data.OleDb.OleDbType.Integer; parametros[0].Value = IdLayout; parametros[1].ParameterName = "P_STATUS"; parametros[1].OleDbType = System.Data.OleDb.OleDbType.SmallInt; parametros[1].Value = Status; dt = db.GetTable("SEL_LAYOUT", parametros); // Asignación de configuración foreach (DataRow row in dt.Rows) { strNegocio = row["NEG_NOM"].ToString(); strNegocioNomCorto = row["NEG_NOC"].ToString(); strNombre = row["LAY_NOM"].ToString(); if (row["LAY_PRF"] != DBNull.Value) strPrefijo = row["LAY_PRF"].ToString(); if (row["LAY_SUF"] != DBNull.Value) strSufijo = row["LAY_SUF"].ToString(); strExtension = row["LAY_EXT"].ToString(); if (row["LAY_HOJ"] != DBNull.Value) strHoja = row["LAY_HOJ"].ToString(); intColumnaInicial = int.Parse(row["LAY_CIN"].ToString()); intFilaInicial = int.Parse(row["LAY_FIN"].ToString()); blnPrimerRegistroEncabezados = ((row["LAY_PRN"].ToString()=="1") ? true : false); strTablaDestino = row["LAY_TDE"].ToString(); if (row["LAY_SEP"] != DBNull.Value) strSeparador = row["LAY_SEP"].ToString(); if (row["LAY_OBS"] != DBNull.Value) strObservaciones = row["LAY_OBS"].ToString(); } } catch (Exception Error) { string strMsgError = Error.Message; } }
private void actualizarBitacora() { try { object rowQuery; // Obtiene valores totales de importación // Registros correctos rowQuery = resultados.Compute("Count(RES_ROK)","RES_ROK = true"); this.lngCorrectos = long.Parse(rowQuery.ToString()); // Registros omitidos rowQuery = resultados.Compute("Count(RES_ROM)", "RES_ROM = true"); this.lngOmitidos = long.Parse(rowQuery.ToString()); // Registros erróneos rowQuery = resultados.Compute("Count(RES_RER)", "RES_RER = true"); this.lngErroneos = long.Parse(rowQuery.ToString()); // Actualiza en base de datos db.Connection_Check(); System.Data.OleDb.OleDbParameter[] parametros = new System.Data.OleDb.OleDbParameter[5]; for (int i = 0; i < 5; i++) { parametros[i] = new System.Data.OleDb.OleDbParameter(); } parametros[0].ParameterName = "P_ID_BITACORA"; parametros[0].OleDbType = System.Data.OleDb.OleDbType.BigInt; parametros[0].Value = this.lngIdBitacora; parametros[1].ParameterName = "P_REG_OK"; parametros[1].OleDbType = System.Data.OleDb.OleDbType.BigInt; parametros[1].Value = this.lngCorrectos; parametros[2].ParameterName = "P_REG_OM"; parametros[2].OleDbType = System.Data.OleDb.OleDbType.BigInt; parametros[2].Value = this.lngOmitidos; parametros[3].ParameterName = "P_REG_ER"; parametros[3].OleDbType = System.Data.OleDb.OleDbType.BigInt; parametros[3].Value = this.lngErroneos; parametros[4].ParameterName = "P_OBS"; parametros[4].OleDbType = System.Data.OleDb.OleDbType.VarChar; if (this.strObservaciones.Length > 255) { parametros[4].Value = strObservaciones.Substring(0, 254); } else { parametros[4].Value = strObservaciones; } db.ExecutaProcedureNonQuery("UPD_BITACORA_IMPORTACION", parametros); } catch (Exception Error) { string msgMsgError = Error.Message; this.Observaciones = this.Observaciones + ((this.Observaciones == "") ? "" : "|") + "#Error - " + msgMsgError; } }
private void registraCampoEnBD(Registro registro) { try { // Actualiza en base de datos db.Connection_Check(); System.Data.OleDb.OleDbParameter[] parametros = new System.Data.OleDb.OleDbParameter[9]; for (int i = 0; i < 9; i++) { parametros[i] = new System.Data.OleDb.OleDbParameter(); } parametros[0].ParameterName = "P_ID_BITACORA"; parametros[0].OleDbType = System.Data.OleDb.OleDbType.BigInt; parametros[0].Value = this.lngIdBitacora; parametros[1].ParameterName = "P_NOM_CAMPO"; parametros[1].OleDbType = System.Data.OleDb.OleDbType.VarChar; parametros[1].Value = registro.NombreCampo; parametros[2].ParameterName = "P_NUM_CAMPO"; parametros[2].OleDbType = System.Data.OleDb.OleDbType.Integer; parametros[2].Value = registro.Campo; parametros[3].ParameterName = "P_NUM_REGISTRO"; parametros[3].OleDbType = System.Data.OleDb.OleDbType.BigInt; parametros[3].Value = registro.NumeroRegistro; parametros[4].ParameterName = "P_VALOR"; parametros[4].OleDbType = System.Data.OleDb.OleDbType.VarChar; parametros[4].Value = registro.Valor; parametros[5].ParameterName = "P_REG_OK"; parametros[5].OleDbType = System.Data.OleDb.OleDbType.SmallInt; parametros[5].Value = ((registro.Correcto) ? 1 : 0); parametros[6].ParameterName = "P_REG_OM"; parametros[6].OleDbType = System.Data.OleDb.OleDbType.SmallInt; parametros[6].Value = ((registro.Omitido) ? 1 : 0); parametros[7].ParameterName = "P_REG_ER"; parametros[7].OleDbType = System.Data.OleDb.OleDbType.SmallInt; parametros[7].Value = ((registro.Erroneo) ? 1 : 0); parametros[8].ParameterName = "P_OBS"; parametros[8].OleDbType = System.Data.OleDb.OleDbType.VarChar; parametros[8].Value = registro.Observaciones; db.ExecutaProcedureNonQuery("INS_REGISTRO_IMPORTACION", parametros); } catch (Exception Error) { string msgMsgError = Error.Message; this.Observaciones = this.Observaciones + ((this.Observaciones == "") ? "" : "|") + "#Error - " + msgMsgError; } }
private void filterBtn_Click(object sender, EventArgs e) { //sql statement building & excecuting :( int expression_counter = 0; bool parametersNeeded = false; System.Data.OleDb.OleDbParameter[] prm = new System.Data.OleDb.OleDbParameter[] { new System.Data.OleDb.OleDbParameter("random", 1) }; string sqlBuilder = ""; sqlBuilder += "select users.emri + ' ' + users.mbiemri as Perdoruesi, arkat.arka as Arka, " + "transaksione.Tipi1 as Nga, transaksione.Sasia1 as Sasia1, transaksione.Tipi2 as Ne, transaksione.Sasia2 as Sasia2, " + "transaksione.Kursi as Kursi, transaksione.Blerje_shitje as Levizja, transaksione.Ora as Ora " + "from (transaksione " + "inner join users on transaksione.id_user = users.id) " + "inner join arkat on transaksione.id_arka = arkat.id "; if (arkaBox.SelectedIndex > 0 || userBox.SelectedIndex > 0 || levizjaBox.SelectedIndex > 0 || this.radCheckBox1.Checked) { sqlBuilder += " where "; if (arkaBox.SelectedIndex > 0) { expression_counter++; sqlBuilder += " transaksione.id_arka = " + this.arkaBox.SelectedValue.ToString(); } if (userBox.SelectedIndex > 0) { if (expression_counter > 0) { sqlBuilder += " and "; } sqlBuilder += " transaksione.id_user = "******" and "; } sqlBuilder += " transaksione.Blerje_shitje = '" + this.levizjaBox.Text + "'"; expression_counter++; } if (this.radCheckBox1.Checked) { prm = new System.Data.OleDb.OleDbParameter[] { new System.Data.OleDb.OleDbParameter("data_fillimit", fromDate.Value), new System.Data.OleDb.OleDbParameter("data_mbarimit", toDate.Value), new System.Data.OleDb.OleDbParameter("Aktiv", true) }; parametersNeeded = true; if (expression_counter > 0) { sqlBuilder += " and "; } sqlBuilder += " dita >= @data_fillimit and dita <= @data_mbarimit "; } if (expression_counter > 0) { sqlBuilder += " and "; } sqlBuilder += " Aktiv = @Aktiv"; } DataTable transactions; if (parametersNeeded) { transactions = db.getQuerySimpleParametric(sqlBuilder, prm).Copy(); } else { transactions = db.getQuerySimple(sqlBuilder).Copy(); } this.radGridView1.DataSource = transactions; this.radGridView1.AutoSizeColumnsMode = Telerik.WinControls.UI.GridViewAutoSizeColumnsMode.Fill; this.radGridView1.Columns["Sasia1"].HeaderText = "Sasia Nga"; this.radGridView1.Columns["Sasia2"].HeaderText = "Sasia Ne"; }
public static IDataParameter CreateParameter(EnumProviders provider) { IDataParameter param; switch (provider) { case EnumProviders.SqlServer: param = new System.Data.SqlClient.SqlParameter(); break; case EnumProviders.OleDb: param = new System.Data.OleDb.OleDbParameter(); break; case EnumProviders.Odbc: param = new System.Data.Odbc.OdbcParameter(); break; case EnumProviders.Oracle: throw new NotImplementedException("Provider not implemented"); break; default: param = new System.Data.SqlClient.SqlParameter(); break; } return param; }
private void ObtenerTipoCampo(int IdTipoCampo, int Status = -1) { try { DataTable dt; db.Connection_Check(); System.Data.OleDb.OleDbParameter[] parametros = new System.Data.OleDb.OleDbParameter[2]; for (int i = 0; i < 2; i++) { parametros[i] = new System.Data.OleDb.OleDbParameter(); } parametros[0].ParameterName = "P_ID_TIPODATO"; parametros[0].OleDbType = System.Data.OleDb.OleDbType.Integer; parametros[0].Value = IdTipoCampo; parametros[1].ParameterName = "P_STATUS"; parametros[1].OleDbType = System.Data.OleDb.OleDbType.SmallInt; parametros[1].Value = Status; dt = db.GetTable("SEL_TIPO_CAMPO", parametros); // Asignación de valores obtenidos foreach (DataRow row in dt.Rows) { strTipoCampo = row["TCA_NOM"].ToString(); Type tipo = null; switch (strTipoCampo) { case "BOOLEANO": tipo = Type.GetType("System.Boolean"); break; case "ENTERO CORTO": tipo = Type.GetType("System.Int16"); break; case "ENTERO": tipo = Type.GetType("System.Int32"); break; case "ENTERO LARGO": tipo = Type.GetType("System.Int64"); break; case "DECIMAL": tipo = Type.GetType("System.Decimal"); break; case "TEXTO": tipo = Type.GetType("System.String"); break; case "FECHA": tipo = Type.GetType("System.DateTime"); break; case "HORA": tipo = Type.GetType("System.DateTime"); break; default: break; } typTipoCampo = tipo; } } catch (Exception Error) { string strMsgError = Error.Message; } }
private void transferirATabla(bool Correctos, bool Omitidos, bool Erroneos) { try { // Estructurar datos obtenidos en forma de tabla y los inserta en la tabla deseada db.Connection_Check(); System.Data.OleDb.OleDbParameter[] parametros = new System.Data.OleDb.OleDbParameter[6]; for (int i = 0; i < 6; i++) { parametros[i] = new System.Data.OleDb.OleDbParameter(); } parametros[0].ParameterName = "P_ID_BITACORA"; parametros[0].OleDbType = System.Data.OleDb.OleDbType.BigInt; parametros[0].Value = this.lngIdBitacora; parametros[1].ParameterName = "P_ID_LAYOUT"; parametros[1].OleDbType = System.Data.OleDb.OleDbType.Integer; parametros[1].Value = this.intIdLayout; parametros[2].ParameterName = "P_TABLA_DESTINO"; parametros[2].OleDbType = System.Data.OleDb.OleDbType.VarChar; parametros[2].Value = this.layout.TablaDestino; parametros[3].ParameterName = "P_REG_OK"; parametros[3].OleDbType = System.Data.OleDb.OleDbType.Integer; parametros[3].Value = ((Correctos) ? 1 : 0); parametros[4].ParameterName = "P_REG_OM"; parametros[4].OleDbType = System.Data.OleDb.OleDbType.Integer; parametros[4].Value = ((Omitidos) ? 1 : 0); parametros[5].ParameterName = "P_REG_ER"; parametros[5].OleDbType = System.Data.OleDb.OleDbType.Integer; parametros[5].Value = ((Erroneos) ? 1 : 0); db.ExecutaProcedureNonQuery("INS_TABLA_IMPORTACION", parametros); } catch (Exception Error) { string msgMsgError = Error.Message; this.Observaciones = this.Observaciones + ((this.Observaciones == "") ? "" : "|") + "#Error - " + msgMsgError; } }
private void ObtenerMetodoValidacion(int IdMetodo, int Status = -1) { try { DataTable dt; db.Connection_Check(); System.Data.OleDb.OleDbParameter[] parametros = new System.Data.OleDb.OleDbParameter[2]; for (int i = 0; i < 2; i++) { parametros[i] = new System.Data.OleDb.OleDbParameter(); } parametros[0].ParameterName = "P_ID_METODO"; parametros[0].OleDbType = System.Data.OleDb.OleDbType.Integer; parametros[0].Value = IdMetodo; parametros[1].ParameterName = "P_STATUS"; parametros[1].OleDbType = System.Data.OleDb.OleDbType.SmallInt; parametros[1].Value = Status; dt = db.GetTable("SEL_METODO_VALIDACION", parametros); // Asignación de valores obtenidos foreach (DataRow row in dt.Rows) { strNombre = row["MVA_NOM"].ToString(); strParametros = row["MVA_PAR"].ToString(); strEjemplo = row["MVA_ESX"].ToString(); strRutaSistema = row["MVA_RSY"].ToString(); strDescripcion = row["MVA_DES"].ToString(); } } catch (Exception Error) { string strMsgError = Error.Message; } }
private void buttonParameters_Click(object sender, EventArgs e) { /**************************************************** * NOTE: You must create a stored procedure using the code * found in the PrameterDemoCode.sql file before you can run this! ****************************************************/ //1. Make a Connection System.Data.OleDb.OleDbConnection objOleCon; objOleCon = new System.Data.OleDb.OleDbConnection(); objOleCon.ConnectionString = strConnection; objOleCon.Open(); objOleCon.ChangeDatabase("TempDB"); //Since the previous demos used Northwind we need this command. //2. Issue a Command System.Data.OleDb.OleDbCommand objCmd; objCmd = new System.Data.OleDb.OleDbCommand(); objCmd.Connection = objOleCon; objCmd.CommandType = CommandType.StoredProcedure; objCmd.CommandText = "DivideDemo"; //2.1 Create and Add Parameters before you execute the Sproc. #region Input Parameters System.Data.OleDb.OleDbParameter objP1 = new System.Data.OleDb.OleDbParameter(); objP1.Direction = ParameterDirection.Input; objP1.DbType = DbType.Double; objP1.ParameterName = "@p1"; objP1.Value = Convert.ToDouble(textBox1.Text); System.Data.OleDb.OleDbParameter objP2 = new System.Data.OleDb.OleDbParameter(); objP2.Direction = ParameterDirection.Input; objP2.DbType = DbType.Double; objP2.ParameterName = "@p2"; objP2.Value = Convert.ToDouble(textBox2.Text); ; #endregion #region Output Parameter System.Data.OleDb.OleDbParameter objAnswer = new System.Data.OleDb.OleDbParameter(); objAnswer.Direction = ParameterDirection.Output; objAnswer.DbType = DbType.Double; objAnswer.ParameterName = "@answer"; #endregion #region Return Code System.Data.OleDb.OleDbParameter objRC = new System.Data.OleDb.OleDbParameter(); objRC.Direction = ParameterDirection.ReturnValue; objRC.DbType = DbType.Int32; objRC.ParameterName = "@RC"; #endregion //Now, add all to the Parameter Collection /* * NOTE: The order of the parameters used to call the Stored Procedure is Positional! * So, if the Store Procedure's parameter list looks like this... * * Create Proc DivideDemo( @p1 float, @p2 float, @answer float out ) * * And, you execute the Store Procedure in SQL with the following code... * * Exec @RC = DivideDemo 4, 3, @Value out; * *Then the Return Code parameter *MUST* be added first, followed by @p1 parameter, * @p2, and @answer, to the Parameters Collection like this.... * */ objCmd.Parameters.Add(objRC); //1st objCmd.Parameters.Add(objP2); //2nd objCmd.Parameters.Add(objP1); //3rd objCmd.Parameters.Add(objAnswer);//4th /*NOTE: Start a SQL Profiler Trace and show students how the command is submitted */ //3. Process the Results try { object objSelectData = objCmd.ExecuteScalar(); //Use this when there is a Select that selects a single value in the sproc. MessageBox.Show("Select data: " + Convert.ToString(objSelectData)); MessageBox.Show("Output data: " + objCmd.Parameters["@answer"].Value.ToString()); MessageBox.Show("Return Value data: " + objCmd.Parameters["@RC"].Value.ToString()); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } //4. Clean up code objOleCon.Close(); }
/// <summary> /// 生成SQL语句参数对象数组 /// </summary> /// <typeparam name="T">实体类型</typeparam> /// <param name="obj"></param> /// <param name="dbtype">数据库类型</param> /// <param name="fields">字段,用空格隔开多个字段。参数名称需与字段名称一致!</param> /// <returns></returns> public static DbParameter[] GetDbParameter <T>(this T obj, DataBaseType dbtype, String fields) { DbParameter[] parameters; String[] filedArray; Type type; PropertyInfo pro; object proValue; type = obj.GetType(); filedArray = fields.Split(' ', ','); //初始化参数数组 int fieldCount = filedArray.Length; switch (dbtype) { case DataBaseType.MonoSQLite: throw new Exception("not implement on .net standard"); // parameters = new Mono.Data.Sqlite.SqliteParameter[fieldCount]; case DataBaseType.OLEDB: parameters = new System.Data.OleDb.OleDbParameter[fieldCount]; break; case DataBaseType.SQLite: parameters = new System.Data.SQLite.SQLiteParameter[fieldCount]; break; case DataBaseType.MySQL: parameters = new MySql.Data.MySqlClient.MySqlParameter[fieldCount]; break; default: case DataBaseType.SQLServer: parameters = new System.Data.SqlClient.SqlParameter[fieldCount]; break; } string fieldName; for (int i = 0; i < fieldCount; i++) { fieldName = filedArray[i]; pro = type.GetProperty(fieldName, BindingFlags.Public | BindingFlags.Instance | BindingFlags.IgnoreCase); if (pro == null) { throw new Exception(String.Format("对象不存在属性:{0}", fieldName)); } //获取对象的值 proValue = pro.GetValue(obj, null); if (proValue == null) { throw new Exception(String.Format("对象属性必须赋值,属性名称:{0}", fieldName)); } switch (dbtype) { case DataBaseType.MonoSQLite: throw new Exception("not implement on .net standard"); // parameters[i] = new Mono.Data.Sqlite.SqliteParameter(String.Format("@{0}", fieldName), proValue); case DataBaseType.SQLite: parameters[i] = new System.Data.SQLite.SQLiteParameter(String.Format("@{0}", fieldName), proValue); break; case DataBaseType.OLEDB: parameters[i] = new System.Data.OleDb.OleDbParameter(String.Format("@{0}", fieldName), proValue); break; case DataBaseType.MySQL: parameters[i] = new MySql.Data.MySqlClient.MySqlParameter(String.Format("@{0}", fieldName), proValue); break; default: case DataBaseType.SQLServer: parameters[i] = new System.Data.SqlClient.SqlParameter(String.Format("@{0}", fieldName), proValue); break; } } return(parameters); }
public int ExecProc(string strSql, ref string strReturn) { this.command.CommandType = System.Data.CommandType.StoredProcedure; this.command.Connection = this.Conn as System.Data.OleDb.OleDbConnection; this.command.Transaction = Mder.FC.DB.DBTrans.Trans as System.Data.OleDb.OleDbTransaction; this.command.Parameters.Clear(); string prcName = ""; string[] prcParams = strSql.Split(','); try { prcName = prcParams[0]; this.command.CommandText = prcName; //22 varchar 30 double 33 int 6 DATETIME for (int i = 1; i < prcParams.GetUpperBound(0); i = i + 4) { System.Data.OleDb.OleDbParameter param = new System.Data.OleDb.OleDbParameter(); param.ParameterName = prcParams[i].Trim(); param.OleDbType = (System.Data.OleDb.OleDbType)int.Parse(prcParams[i + 1]); param.Direction = (System.Data.ParameterDirection)int.Parse(prcParams[i + 2]); if (param.Direction == System.Data.ParameterDirection.Input) { param.Value = prcParams[i + 3].Trim(); param.Size = 50; } else if (param.OleDbType == System.Data.OleDb.OleDbType.VarChar) { param.Size = 50; } this.command.Parameters.Add(param); } this.command.ExecuteNonQuery(); } catch (System.Data.OleDb.OleDbException ex) { this.Err = "执行产生错误!" + ex.Message; this.ErrCode = strSql; this.DBErrCode = ex.ErrorCode; this.WriteErr(); return -1; } catch (Exception ex) { this.Err = "执行存储过程出错!" + strSql + ex.Message; this.WriteErr(); return -1; } try { for (int i = 0; i < this.command.Parameters.Count; i++) { if (this.command.Parameters[i].Direction == System.Data.ParameterDirection.Output) { strReturn = strReturn + "," + this.command.Parameters[i].Value; } } strReturn = strReturn.Substring(1); } catch (Exception ex) { this.Err = "执行存储过程出错!" + strSql + ex.Message; this.WriteErr(); return -1; } return 0; }