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