public static DataTable RunSPReturnDataTable(string spName, Paras paras, IfxConnection conn)
{
try
{
IfxCommand salesCMD = new IfxCommand(spName, conn);
salesCMD.CommandType = CommandType.StoredProcedure;
/// 加上他们的餐数
foreach (Para para in paras)
{
IfxParameter myParm = salesCMD.Parameters.Add(para.ParaName, para.DAType);
myParm.Value = para.val;
}
//selectCMD.CommandTimeout =60;
IfxDataAdapter sda = new IfxDataAdapter(salesCMD);
if (conn.State == System.Data.ConnectionState.Closed)
{
conn.Open();
}
DataTable dt = new DataTable();
sda.Fill(dt);
sda.Dispose();
return(dt);
}
catch (System.Exception ex)
{
throw ex;
}
}
private IDataReader EjecutarQuery(string cadenaSQL, List <CamposTabla> lp, object unaConexion, object unaTransaccion = null)
{
//IfxCommand command = new IfxCommand(cadenaSQL.Replace("@", ":"));
//IDataReader rd = null;
IfxCommand command = new IfxCommand();
command.Connection = (IfxConnection)unaConexion;
if (unaTransaccion != null)
{
command.Transaction = (IfxTransaction)unaTransaccion;
}
command.CommandType = System.Data.CommandType.Text;
foreach (CamposTabla item in lp)
{
IfxParameter parametro = new IfxParameter();
parametro.ParameterName = "@" + item.Nombre;
if (item.TipoEstablecido)
{
parametro.DbType = item.Tipo;
parametro.Size = item.Tamaño;
}
parametro.Direction = item.Direccion;
parametro.Value = item.Valor;
command.Parameters.Add(parametro);
}
command.CommandText = cadenaSQL;
try
{
if (unaConexion == null)
{
if (Conexion.State == System.Data.ConnectionState.Closed)
{
Conexion.Open();
}
}
//rd = ExecuteReader(command.Clone());
return(ExecuteReader(command));
}
catch (Exception)
{
//throw new Exception("Error obteniendo registros \nDetalle: " + ex.ToString(), ex);
throw;
}
finally
{
if (command != null)
{
command.Dispose();
}
command = null;
}
//return rd;
}
private void tabControl1_Selected(object sender, TabControlEventArgs e)
{
// Move the input focus to the query builder.
// This will fire Leave event in the text box and update the query builder
// with modified query text.
queryBuilder1.Focus();
Application.DoEvents();
// Try to execute the query using current database connection:
if (e.TabPage == tabPageData)
{
dataGridView1.DataSource = null;
if (queryBuilder1.MetadataProvider != null && queryBuilder1.MetadataProvider.Connected)
{
IfxCommand command = (IfxCommand)queryBuilder1.MetadataProvider.Connection.CreateCommand();
command.CommandText = queryBuilder1.SQL;
// handle the query parameters
if (queryBuilder1.Parameters.Count > 0)
{
for (int i = 0; i < queryBuilder1.Parameters.Count; i++)
{
if (!command.Parameters.Contains(queryBuilder1.Parameters[i].FullName))
{
IfxParameter parameter = new IfxParameter();
parameter.ParameterName = queryBuilder1.Parameters[i].FullName;
parameter.DbType = queryBuilder1.Parameters[i].DataType;
command.Parameters.Add(parameter);
}
}
using (QueryParametersForm qpf = new QueryParametersForm(command))
{
qpf.ShowDialog();
}
}
IfxDataAdapter adapter = new IfxDataAdapter(command);
DataSet dataset = new DataSet();
try
{
adapter.Fill(dataset, "QueryResult");
dataGridView1.DataSource = dataset.Tables["QueryResult"];
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "SQL query error");
}
}
}
}
private void DisplayDataForQuery()
{
dataGridView1.DataSource = null;
if (queryBuilder.MetadataProvider != null && queryBuilder.MetadataProvider.Connected)
{
if (queryBuilder.MetadataProvider is InformixMetadataProvider)
{
IfxCommand command = (IfxCommand)queryBuilder.MetadataProvider.Connection.CreateCommand();
command.CommandText = queryBuilder.SQL;
// handle the query parameters
if (queryBuilder.Parameters.Count > 0)
{
for (int i = 0; i < queryBuilder.Parameters.Count; i++)
{
if (!command.Parameters.Contains(queryBuilder.Parameters[i].FullName))
{
IfxParameter parameter = new IfxParameter();
parameter.ParameterName = queryBuilder.Parameters[i].FullName;
parameter.DbType = queryBuilder.Parameters[i].DataType;
command.Parameters.Add(parameter);
}
}
using (QueryParametersForm qpf = new QueryParametersForm(command))
{
qpf.ShowDialog();
}
}
IfxDataAdapter adapter = new IfxDataAdapter(command);
DataSet dataset = new DataSet();
try
{
adapter.Fill(dataset, "QueryResult");
dataGridView1.DataSource = dataset.Tables["QueryResult"];
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "SQL query error");
}
} //end informix
} //end check if connected
} //end method
/// <summary>
/// 运行存储过程,有Para。返回影响的行。
/// </summary>
/// <param name="spName"></param>
/// <param name="conn"></param>
/// <param name="paras"></param>
public static int RunSP(string spName, Paras paras, IfxConnection conn)
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
IfxCommand cmd = new IfxCommand(spName, conn);
cmd.CommandType = CommandType.StoredProcedure;
// 加入参数
foreach (Para para in paras)
{
IfxParameter myParameter = new IfxParameter(para.ParaName, para.val);
myParameter.Size = para.Size;
cmd.Parameters.Add(myParameter);
}
int i = cmd.ExecuteNonQuery();
conn.Close();
return(i);
}
public void PreparaComando(string cadenaSQL, List <CamposTabla> lp, object unaConexion = null, object unaTransaccion = null)
{
//OBJETO PARA MANIPULACION DE CADENA SQL
StringBuilder sqlNueva = new StringBuilder(cadenaSQL);
//VERIFICACION DE CAMPOS DEFINIDOS COMO AUTONUMERICOS
if (lp.Count(x => x.Autonumerico == true) == 1)
{
exeAutoNum = true;
}
else
{
exeAutoNum = false;
}
//DETERMINAMOS NOMBRE DE TABLA
/*if (cadenaSQL.Contains("insert into"))
* {
* mensaje = "agregando";
* }
* if (cadenaSQL.Contains("update "))
* {
* mensaje = "actualizando"; exeAutoNum = false;
* }
* if (cadenaSQL.Contains("delete from"))
* {
* mensaje = "eliminando"; exeAutoNum = false;
* }*/
//CREAMOS OBJETO COMANDO
comandoPreparado = new IfxCommand();
comandoPreparado.Parameters.Clear();
comandoPreparado.CommandType = System.Data.CommandType.Text;
//SI ES EJECUCION DE AUTONUMERICO ESTABLECE LA DEVOLUCION DEL VALOR DE LA SECUENCIA
if (exeAutoNum)
{
sqlNueva.Replace("{%}", "coalesce");
}
//CREACION DE PARAMETROS EN EL COMANDO Y DESCARTE DE CAMPOS AUTONUMERICOS
foreach (CamposTabla item in lp)
{
if (item.Autonumerico == false)
{
IfxParameter parametro = new IfxParameter();
parametro.ParameterName = "@" + item.Nombre;
if (item.TipoEstablecido)
{
parametro.DbType = item.Tipo;
parametro.Size = item.Tamaño;
}
parametro.Direction = item.Direccion;
comandoPreparado.Parameters.Add(parametro);
}
}
//command.CommandText = sqlNueva.Replace("@", ":").ToString();
comandoPreparado.CommandText = sqlNueva.ToString();
//ASIGNACION DE CONEXION Y TRANSACCION
if (unaConexion == null)
{
comandoPreparado.Connection = this.Conexion;
}
else
{
comandoPreparado.Connection = (IfxConnection)unaConexion;
}
if (unaConexion != null && unaTransaccion != null)
{
comandoPreparado.Transaction = (IfxTransaction)unaTransaccion;
}
comandoPreparado.Prepare();
}
private int EjecutarSQLAutonumerico(string cadenaSQL, List <CamposTabla> lp, object unaConexion = null, object unaTransaccion = null)
{
bool exeAutoNumerico = false;
//OBJETO PARA MANIPULACION DE CADENA SQL
StringBuilder sqlNueva = new StringBuilder(cadenaSQL);
//VERIFICACION DE CAMPOS DEFINIDOS COMO AUTONUMERICOS
if (lp.Count(x => x.Autonumerico == true) == 1)
{
exeAutoNumerico = true;
}
else
{
exeAutoNumerico = false;
}
//DETERMINAMOS NOMBRE DE TABLA
/*if (cadenaSQL.Contains("insert into"))
* {
* mensaje = "agregando";
* }
* if (cadenaSQL.Contains("update "))
* {
* mensaje = "actualizando"; exeAutoNumerico = false;
* }
* if (cadenaSQL.Contains("delete from"))
* {
* mensaje = "eliminando"; exeAutoNumerico = false;
* }*/
//CREAMOS OBJETO COMANDO
IfxCommand command = new IfxCommand();
command.CommandType = System.Data.CommandType.Text;
//SI ES EJECUCION DE AUTONUMERICO ESTABLECE LA DEVOLUCION DEL VALOR DE LA SECUENCIA
if (exeAutoNumerico)
{
sqlNueva.Replace("{%}", "coalesce");
}
//CREACION DE PARAMETROS EN EL COMANDO Y DESCARTE DE CAMPOS AUTONUMERICOS
foreach (CamposTabla item in lp)
{
if (!item.Nombre.Contains("#"))
{
IfxParameter parametro = new IfxParameter();
parametro.ParameterName = "@" + item.Nombre;
if (item.TipoEstablecido)
{
parametro.DbType = item.Tipo;
parametro.Size = item.Tamaño;
}
parametro.Direction = item.Direccion;
parametro.Value = item.Valor;
command.Parameters.Add(parametro);
}
}
//command.CommandText = sqlNueva.Replace("@", ":").ToString();
command.CommandText = sqlNueva.ToString();
//ASIGNACION DE CONEXION Y TRANSACCION
if (unaConexion == null)
{
command.Connection = this.Conexion;
}
else
{
command.Connection = (IfxConnection)unaConexion;
}
if (unaConexion != null && unaTransaccion != null)
{
command.Transaction = (IfxTransaction)unaTransaccion;
}
//RESULTADO DEVUELTO
int resultado = 0;
try
{
if (unaConexion == null)
{
if (Conexion.State == System.Data.ConnectionState.Closed)
{
Conexion.Open();
}
}
//EJECUCION DE COMANDO
if (exeAutoNumerico)
{
//CAPTURAMOS VALOR AUTONUMERICO DE TIPO SEQUENCE CUANDO EXEAUTONUM == TRUE
IfxDataReader rd = (IfxDataReader)ExecuteReader(command);
rd.Read();
resultado = Convert.ToInt32(rd["valorActual"]);
rd.Close();
rd.Dispose();
rd = null;
}
else
{
//DEVOLVEMOS EL NUMERO DE REGISTROS AFECTADOS CUANDO EXEAUTONUM == FALSE
resultado = ExecuteNonQuery(command);
}
}
catch (IfxException)
{
//throw new Exception("Error " + mensaje + " Registro \nDetalle: " + ex.ToString(), ex);
throw;
}
/*catch (Exception)
* {
* //throw new Exception("Error " + mensaje + " Registro \nDetalle: " + ex.ToString(), ex);
* throw;
* }*/
finally
{
//DESTRUCCION DE OBJETOS Y CIERRE DE CONEXION
command.Dispose();
if (unaConexion == null)
{
if (conexionLocal != null)
{
if (Conexion.State == System.Data.ConnectionState.Open)
{
Conexion.Close();
conexionLocal = null;
}
}
}
//mensaje = null;
sqlNueva = null;
}
//DEVOLUCION DE RESULTADO
return(resultado);
}
public static IDbDataParameter CreateDataParameter(ClientType ct)
{
IDbDataParameter iParam = null;
switch (ct)
{
case ClientType.ctMsSql:
iParam = new SqlParameter();
break;
case ClientType.ctOleDB:
iParam = new OleDbParameter();
break;
case ClientType.ctOracle:
iParam = new OracleParameter();
break;
case ClientType.ctODBC:
iParam = new OdbcParameter();
break;
#if MySql
case ClientType.ctMySql:
iParam = new MySqlParameter();
break;
#endif
#if Informix
case ClientType.ctInformix:
iParam = new IfxParameter();
break;
#endif
#if Sybase
case ClientType.ctSybase:
iParam = new AseParameter();
break;
#endif
}
return iParam;
}
