Esempio n. 1
0
        private async Task <DataTable> AsyncLoadTable(string sqlQuery, OdbcTransaction dbTransaction)
        {
            object          region      = new object();
            OdbcCommand     cmd         = null;
            DataTable       table       = new DataTable();
            OdbcDataAdapter dataAdapter = null;

            if (_connection.State != ConnectionState.Open)
            {
                _connection.ConnectionString = _connectionString;

                await _connection.OpenAsync();
            }
            try
            {
                lock (region)
                {
                    cmd         = new OdbcCommand(sqlQuery, _connection, dbTransaction);
                    table       = new DataTable();
                    dataAdapter = new OdbcDataAdapter(cmd);
                }
                await Task.Run(() =>
                {
                    dataAdapter.Fill(table);
                }).ConfigureAwait(false);
            }
            finally
            {
                Close();
                dataAdapter?.Dispose();

                cmd?.Dispose();
            }
            return(table);
        }
Esempio n. 2
0
        /// <summary>
        /// Asynchronous load for loading multiple select in a data set. Each query
        /// is supposed to be separated by a ;
        /// </summary>
        /// <param name="sqlQuery">Composed query in the data set</param>
        /// <returns>A dataset with the loaded datatable</returns>
        public override async Task <DataSet> AsyncDataSetLoad(string sqlQuery)

        {
            DataSet         set   = new DataSet();
            DataTable       table = null;
            OdbcDataAdapter da    = null;
            OdbcCommand     cmd   = null;

            try
            {
                if (_connection.State != ConnectionState.Open)
                {
                    _connection.ConnectionString = _connectionString;

                    await _connection.OpenAsync();
                }

                OdbcTransaction dbTransaction = _connection.BeginTransaction();
                IList <string>  queryPart     = Regex.Split(sqlQuery, ";");
                object          region        = new object();
                int             i             = 0;

                foreach (string query in queryPart)
                {
                    lock (region)
                    {
                        cmd   = new OdbcCommand(queryPart[i++], _connection, dbTransaction);
                        table = new DataTable();
                        da    = new OdbcDataAdapter(cmd);
                    }
                    await Task.Run(() =>
                    {
                        da.Fill(table);
                    }).ConfigureAwait(false);

                    set.Tables.Add(table);
                }
            }
            catch (System.Exception e)
            {
                set = null;
                throw new DataLayerException(e.Message, e);
            }
            finally
            {
                Close();
                set?.Dispose();
                cmd?.Dispose();
                da?.Dispose();
            }
            return(set);
        }
Esempio n. 3
0
        public System.Data.DataTable LoadExcel(string pPath)
        {
            string connString = "Driver={Driver do Microsoft Excel(*.xls)};DriverId=790;SafeTransactions=0;ReadOnly=1;MaxScanRows=16;Threads=3;MaxBufferSize=2024;UserCommitSync=Yes;FIL=excel 8.0;PageTimeout=5;";  //连接字符串

            //简单解释下这个连续字符串,Driver={Driver do Microsoft Excel(*.xls)} 这种连接写法不需要创建一个数据源DSN,DRIVERID表示驱动ID,Excel2003后都使用790,

            //FIL表示Excel文件类型,Excel2007用excel 8.0,MaxBufferSize表示缓存大小, 如果你的文件是2010版本的,也许会报错,所以要找到合适版本的参数设置。

            connString += "DBQ=" + pPath; //DBQ表示读取Excel的文件名(全路径)
            OdbcConnection conn = new OdbcConnection(connString);
            OdbcCommand    cmd  = new OdbcCommand();

            cmd.Connection = conn;
            //获取Excel中第一个Sheet名称,作为查询时的表名
            string sheetName = this.GetExcelSheetName(pPath);
            string sql       = "select * from [" + sheetName.Replace('.', '#') + "$]";

            cmd.CommandText = sql;
            OdbcDataAdapter da = new OdbcDataAdapter(cmd);
            DataSet         ds = new DataSet();

            try
            {
                da.Fill(ds);
                return(ds.Tables[0]);    //返回Excel数据中的内容,保存在DataTable中
            }
            catch (Exception x)
            {
                ds = null;
                throw new Exception("从Excel文件中获取数据时发生错误!可能是Excel版本问题,可以考虑降低版本或者修改连接字符串值");
            }
            finally
            {
                cmd.Dispose();
                cmd = null;
                da.Dispose();
                da = null;
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
                conn = null;
            }
        }
Esempio n. 4
0
        //===========================Get_APVendors_FinPlus===========================
        public List <Results> Get_APVendors_FinPlus(string id)
        {
            OdbcConnection dbConn = new OdbcConnection();

            dbConn.ConnectionString = FinPlusconstring;
            dbConn.Open();

            OdbcCommand    dbComm = default(OdbcCommand);
            OdbcDataReader dbread = default(OdbcDataReader);
            string         strSQL = null;

            strSQL  = "SELECT vend_no, ven_name[1,25], b_addr_1, b_phone, p_addr_1 ";
            strSQL += "FROM vendor ";
            strSQL += "WHERE comm11 = '" + id + "' ";

            dbComm = new OdbcCommand(strSQL, dbConn);
            dbread = dbComm.ExecuteReader();

            List <Results> results = new List <Results>();


            while (dbread.Read())
            {
                Results item = new Results();
                item.AccountNo       = dbread["vend_no"].ToString();
                item.cust_ser        = "";
                item.Status          = "";
                item.Name            = dbread["ven_name"].ToString();
                item.Billing_Address = dbread["b_addr_1"].ToString();
                item.Service_Address = dbread["p_addr_1"].ToString();
                item.Phone           = dbread["b_phone"].ToString();
                item.Balance         = "";
                results.Add(item);
            }

            dbread.Close();

            dbComm.Dispose();

            dbConn.Close();
            dbConn = null;

            return(results);
        }
Esempio n. 5
0
        /// <summary>
        ///
        /// insert a row in table t_RBSR_AUFW_u_OrgValue1252.
        /// </summary>
        /// <param name="FieldName"></param>
        /// <param name="TcodeAssignmentSetID"></param>
        /// <param name="SAProleID"></param>
        /// <param name="EditStatus"></param>
        /// <returns>The integer ID of the new object.</returns>
        public int NewOrgValue1252(string FieldName, int TcodeAssignmentSetID, int SAProleID, int EditStatus)
        {
            int rv = 0;

            DBConnect();
            OdbcCommand cmd = _dbConnection.CreateCommand();

            cmd.CommandText = "INSERT INTO \"t_RBSR_AUFW_u_OrgValue1252\"(\"c_u_FieldName\",\"c_r_TcodeAssignmentSet\",\"c_r_SAProle\",\"c_u_EditStatus\") VALUES(?,?,?,?)";
            if (_dbConnection.Driver.ToLower().StartsWith("sql"))
            {
                cmd.CommandText += " SELECT convert(int,SCOPE_IDENTITY())";
            }
            if (FieldName == null)
            {
                throw new Exception("FieldName must not be null!");
            }
            cmd.Parameters.Add("c_u_FieldName", OdbcType.NVarChar, 8);
            cmd.Parameters["c_u_FieldName"].Value = (FieldName != null ? (object)FieldName : DBNull.Value);
            cmd.Parameters.Add("c_r_TcodeAssignmentSet", OdbcType.Int);
            cmd.Parameters["c_r_TcodeAssignmentSet"].Value = (object)TcodeAssignmentSetID;
            cmd.Parameters.Add("c_r_SAProle", OdbcType.Int);
            cmd.Parameters["c_r_SAProle"].Value = (object)SAProleID;
            cmd.Parameters.Add("c_u_EditStatus", OdbcType.Int);
            cmd.Parameters["c_u_EditStatus"].Value = (object)EditStatus;
            OdbcDataReader dri = cmd.ExecuteReader();

            if (_dbConnection.Driver.ToLower().StartsWith("myodbc"))
            {
                cmd             = _dbConnection.CreateCommand();
                cmd.CommandText = "SELECT LAST_INSERT_ID()";
                dri             = cmd.ExecuteReader();
            }
            dri.Read();
            rv = (dri.IsDBNull(0) ? 0 : (typeof(long).Equals(dri.GetFieldType(0)) ? (int)dri.GetInt64(0) : (int)dri.GetInt32(0)));
            dri.Close();
            if (rv == 0)
            {
                throw new Exception("Insert operation failed!");
            }
            dri.Dispose();
            cmd.Dispose();
            DBClose();
            return(rv);
        }
Esempio n. 6
0
        private void cmdSend_Click(System.Object sender, System.EventArgs e)
        {
            string strSQL = "";

            if (string.IsNullOrEmpty(modMain.StudyID))
            {
                MessageBox.Show("Please select any DICOM Image", "TeleRad-Client", MessageBoxButtons.OK);
                return;
            }

            if (string.IsNullOrEmpty(txtReports.Text))
            {
                MessageBox.Show("No Reports to SEND", "TeleRad-Client", MessageBoxButtons.OK);
                return;
            }

            CN.OpenConnection();
            Cmd = new OdbcCommand("SELECT Count(*) AS Kount from Reports WHERE StudyUID='" + modMain.StudyID + "'", CN.DBConnection);
            Rs1 = Cmd.ExecuteReader();
            if (!Rs1.HasRows)
            {
                strSQL = "UPDATE Reports SET Reports='" + txtReports.Rtf + "', IsReports=1 WHERE StudyUID='" + modMain.StudyID + "'";
            }
            else
            {
                strSQL = "INSERT INTO Reports (StudyUID, Reports, IsReports) VALUES ('" + modMain.StudyID + "', '" + txtReports.Rtf + "',1)";
            }
            Rs1.Close();
            Cmd.Dispose();
            CN.closeconnection();

            CN.OpenConnection();
            Cmd = new OdbcCommand(strSQL, CN.DBConnection);
            Rs1 = Cmd.ExecuteReader();
            Rs1.Close();
            Cmd.Dispose();
            CN.closeconnection();

            //Insert_ReportDone_Image()
            MessageBox.Show("Successfully Report sent", "TeleRad - Client", MessageBoxButtons.OK);

            //SP.txtReports.Enabled = true;
            //SP.txtReports.Text = txtReports.Text;
        }
Esempio n. 7
0
        private void EjecutarComando(TipoComando sqlCommandType, UsuarioEntity entidad)
        {
            OdbcConnection connection = null;
            OdbcCommand    command    = null;

            try {
                connection = (OdbcConnection)connectionDA.GetOpenedConnection();
                IDataParameter paramId = new OdbcParameter("?", OdbcType.Int);
                paramId.Value = entidad.IdUsuario;

                switch (sqlCommandType)
                {
                case TipoComando.Insertar:
                    command = new OdbcCommand(SQLInsert, connection);
                    CrearParametros(command, entidad);
                    break;

                case TipoComando.Actualizar:
                    command = new OdbcCommand(SQLUpdate, connection);
                    command.Parameters.Add(paramId);
                    CrearParametros(command, entidad);
                    break;

                case TipoComando.Eliminar:
                    command = new OdbcCommand(SQLDelete, connection);
                    command.Parameters.Add(paramId);
                    break;
                }

                command.ExecuteNonQuery();
                connection.Close();
            } catch (Exception ex) {
                throw new daException(ex);
            } finally {
                if (command != null)
                {
                    command.Dispose();
                }
                if (connection != null)
                {
                    connection.Dispose();
                }
            }
        }
	}  // end Main


	static void TestODBCVarCharCode(String connstring)
	{
		Console.WriteLine("\nTestODBCVarCharCode...\n");

		using (OdbcConnection conn = new OdbcConnection(connstring))
		{
			OdbcCommand command;
			string sql;

			conn.Open();

			try
			{
				sql = "drop table chardata";
				command = new OdbcCommand(sql, conn);
				command.ExecuteNonQuery();
				command.Dispose();
			}
			catch { }  // ignore if table does not exist

			sql = "create table chardata (" +
				"a_integer integer not null primary key, " +
				"a_smallint smallint not null, " +
				"a_varchar_20 varchar(20) not null, " +
				"a_long_varchar long varchar not null)";
			command = new OdbcCommand(sql, conn);
			command.ExecuteNonQuery();
			command.Dispose();

			sql = "insert into chardata (a_integer, a_smallint,a_varchar_20, a_long_varchar) " +
				"values (1,1, ?, ?)";

			OdbcParameter p1 = new OdbcParameter("p1", OdbcType.VarChar);
			p1.Value = "abc";

			OdbcParameter p2 = new OdbcParameter("p2", OdbcType.VarChar);
			p2.Value = "xyz";

			command = new OdbcCommand(sql, conn);
			command.Parameters.Add(p1);
			command.Parameters.Add(p2);
			command.ExecuteNonQuery();
		}  // end using OdbcConnection
	}
Esempio n. 9
0
        /// <summary>
        /// 执行分页查询(采用DataReader进行分页查询)
        /// </summary>
        /// <param name="sql">SQL查询语句</param>
        /// <param name="parameters">参数集合</param>
        /// <param name="offset">读取偏移量</param>
        /// <param name="limit">读取最大值</param>
        /// <returns>包含查询结果集合</returns>
        public override List <T> ExecuteQueryPage <T>(string sql, KdtParameterCollection parameters, int offset, int limit)
        {
            try
            {
                List <T> entitys = new List <T>();
                // 执行SQL命令
                using (OdbcCommand cmd = new OdbcCommand(ReplaceSqlText(sql, parameters), _odbcCn))
                {
                    InitCommand(cmd); // 初始化

                    // 赋值参数
                    var hasConvertParams = ConvertToOdbcParameter(parameters);
                    foreach (var item in hasConvertParams)
                    {
                        cmd.Parameters.Add(item.Value);
                    }

                    // 执行填充数据
                    using (OdbcDataReader reader = cmd.ExecuteReader())
                    {
                        entitys = GetEntityPage <T>(reader, offset, limit);
                        // 反射参数值
                        ReflectParamValue(parameters, hasConvertParams);

                        reader.Close();
                        reader.Dispose();
                    }
                    cmd.Cancel();
                    cmd.Dispose();
                }

                return(entitys);
            }
            catch (OdbcException me)
            {
                KdtLoger.Instance.Error(me);
                throw new DataException(me.Message);
            }
            catch (Exception ex)
            {
                KdtLoger.Instance.Error(ex);
                throw new DataException(string.Format("执行SQL查询,返回数据集合错误,原因为:{0}", ex.Message));
            }
        }
Esempio n. 10
0
    private void populateMedia(object sender, EventArgs e, string idSelected)
    {
        string tmsg = "", SQL = "SELECT * "
                                + "FROM mediatab "
                                + "WHERE type='Image'";
        int item = -1;

        General g = Session["app"] as General;

        OdbcCommand cmd = g.DB.PrepareSQL(SQL);

        try
        {
            OdbcDataReader result = cmd.ExecuteReader();

            if (result.HasRows)
            {
                ddlFlag.Items.Clear();
                while (result.Read())
                {
                    item++;
                    ddlFlag.Items.Add(new ListItem(result["filename"].ToString() + " - " + result["title"].ToString(), result["idmedia"].ToString()));
                    if (idSelected == result["idmedia"].ToString())
                    {
                        ddlFlag.SelectedIndex = item;
                    }
                }
            }
            result.Close();
            result.Dispose();
        }
        catch (OdbcException o)
        {
            tmsg = g.DB.catchODBCException(o, g.ErrorLevel);
        }
        finally
        {
            if (tmsg.Length != 0)
            {
                msg.InnerHtml = tmsg;
            }
            cmd.Dispose();
        }
    }
        /// <summary>
        ///
        /// insert a row in table t_RBSR_AUFW_u_SAPsecurityOrgAxis.
        /// </summary>
        /// <param name="English_Name"></param>
        /// <param name="SAP_Name"></param>
        /// <returns>The integer ID of the new object.</returns>
        public int NewSAPsecurityOrgAxis(string English_Name, string SAP_Name)
        {
            int rv = 0;

            DBConnect();
            OdbcCommand cmd = _dbConnection.CreateCommand();

            cmd.CommandText = "INSERT INTO \"t_RBSR_AUFW_u_SAPsecurityOrgAxis\"(\"c_u_English_Name\",\"c_u_SAP_Name\") VALUES(?,?)";
            if (_dbConnection.Driver.ToLower().StartsWith("sql"))
            {
                cmd.CommandText += " SELECT convert(int,SCOPE_IDENTITY())";
            }
            if (English_Name == null)
            {
                throw new Exception("English_Name must not be null!");
            }
            cmd.Parameters.Add("c_u_English_Name", OdbcType.NVarChar, 50);
            cmd.Parameters["c_u_English_Name"].Value = (English_Name != null ? (object)English_Name : DBNull.Value);
            if (SAP_Name == null)
            {
                throw new Exception("SAP_Name must not be null!");
            }
            cmd.Parameters.Add("c_u_SAP_Name", OdbcType.NVarChar, 50);
            cmd.Parameters["c_u_SAP_Name"].Value = (SAP_Name != null ? (object)SAP_Name : DBNull.Value);
            OdbcDataReader dri = cmd.ExecuteReader();

            if (_dbConnection.Driver.ToLower().StartsWith("myodbc"))
            {
                cmd             = _dbConnection.CreateCommand();
                cmd.CommandText = "SELECT LAST_INSERT_ID()";
                dri             = cmd.ExecuteReader();
            }
            dri.Read();
            rv = (dri.IsDBNull(0) ? 0 : (typeof(long).Equals(dri.GetFieldType(0)) ? (int)dri.GetInt64(0) : dri.GetInt32(0)));
            dri.Close();
            if (rv == 0)
            {
                throw new Exception("Insert operation failed!");
            }
            dri.Dispose();
            cmd.Dispose();
            DBClose();
            return(rv);
        }
Esempio n. 12
0
        // Optional argument variable

        #endregion Field

        #region Method

        /// <summary>
        ///     读取Excel文件
        /// </summary>
        /// <param name="pPath"></param>
        /// <returns></returns>
        public DataTable LoadExcel(string pPath)
        {
            //Driver={Driver do Microsoft Excel(*.xls)} 这种连接写法不需要创建一个数据源DSN,DRIVERID表示驱动ID,Excel2003后都使用790,FIL表示Excel文件类型,Excel2007用excel 8.0,MaxBufferSize表示缓存大小,DBQ表示读取Excel的文件名(全路径)

            string connString =
                "Driver={Driver do Microsoft Excel(*.xls)};DriverId=790;SafeTransactions=0;ReadOnly=1;MaxScanRows=16;Threads=3;MaxBufferSize=2024;UserCommitSync=Yes;FIL=excel 8.0;PageTimeout=5;";

            connString += "DBQ=" + pPath;
            var conn = new OdbcConnection(connString);
            var cmd  = new OdbcCommand();

            cmd.Connection = conn;
            //获取Excel中第一个Sheet名称,作为查询时的表名
            string sheetName = GetExcelSheetName(pPath);
            string sql       = "select * from [" + sheetName.Replace('.', '#') + "$]";

            cmd.CommandText = sql;
            var da = new OdbcDataAdapter(cmd);
            var ds = new DataSet();

            try
            {
                da.Fill(ds);
                return(ds.Tables[0]);
            }
            catch (Exception x)
            {
                ds = null;
                throw new Exception("从Excel文件中获取数据时发生错误!");
            }
            finally
            {
                cmd.Dispose();
                cmd = null;
                da.Dispose();
                da = null;
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
                conn = null;
            }
        }
Esempio n. 13
0
 public void Close()
 {
     if (_reader != null)
     {
         _reader.Dispose();
         _reader = null;
     }
     if (_command != null)
     {
         _command.Dispose();
         _command = null;
     }
     if (_connection != null)
     {
         _connection.Dispose();
         _connection.Close();
         _connection = null;
     }
 }
Esempio n. 14
0
        public List <PedidoEntity> ObtenerPedidosPorCliente(int idcliente)
        {
            OdbcConnection      connection = null; // Conexión a la base de datos
            OdbcCommand         command    = null; // Comando a ejecutar en la base de datos.
            OdbcDataReader      dr         = null; // DataReader con registros de datos.
            List <PedidoEntity> pedidos    = null; // Lista de objetos con datos de empleados.

            try {
                connection = (OdbcConnection)connectionDA.GetOpenedConnection(); // Se obtiene una conexión abierta.
                command    = new OdbcCommand(SQLSearch, connection);             // Se crea el comando con la sentencia Select.
                command.Parameters.Add("?", OdbcType.Int);                       // Se agrega el parámetro idcliente.
                command.Parameters[0].Value = idcliente;
                // Se ejecuta el comando SQL en la base de datos y se devuelve
                // una instancia de DataReader con los registros encontrados.
                dr = command.ExecuteReader();

                pedidos = new List <PedidoEntity>(); // Se crea una instancia de la lista de empleados.

                while (dr.Read())                    // Mientras que se pueda leer el DataReader.
                {
                    pedidos.Add(CrearEntidad(dr));   // Se agrega un objeto con los datos del empleado a la lista.
                }

                dr.Close();         // Se cierra el DataReader.
                connection.Close(); // Se cierra la conexión.
            } catch (Exception ex) {
                throw new daException(ex);
            } finally {
                dr = null;

                if (command != null)
                {
                    command.Dispose(); // Se libera el recurso.
                }

                if (connection != null)
                {
                    connection.Dispose(); // Se libera el recursos.
                }
            }

            return(pedidos);
        }
Esempio n. 15
0
    public SqlResault Query(string qr)
    {
        if (!OnlineMode)
        {
            string[]   qrcommands = qr.Split(';');
            SqlResault res        = new SqlResault();
            foreach (string command in qrcommands)
            {
                if (command.Trim() != "")
                {
                    using (OdbcCommand DbCommand = Con.CreateCommand())
                    {
                        DbCommand.CommandText = command + ";";
                        using (OdbcDataReader DbReader = DbCommand.ExecuteReader())
                        {
                            int cn = 0;
                            while (DbReader.Read())
                            {
                                int fCount = DbReader.FieldCount;
                                Dictionary <string, string> dict = new Dictionary <string, string>();

                                for (int i = 0; i < fCount; i++)
                                {
                                    dict[DbReader.GetName(i)] = DbReader.GetString(i);
                                }
                                res[cn] = dict;
                                cn     += 1;
                            }
                            DbReader.Close();
                            DbCommand.Cancel();
                            DbCommand.Dispose();
                        }
                    }
                }
            }

            return(res);
        }
        else
        {
            return(Utility.Connection.Send <SqlResault>("query", qr));
        }
    }
 public void EjecutarSQL(string SQL)
 {
     try
     {
         //llamamos al metodo que conecta la bd
         conectar();
         comando            = new OdbcCommand(SQL);
         comando.Connection = conexion;
         //ejecutar la consulta sql
         comando.ExecuteNonQuery();
         conexion.Close();
         conexion.Dispose();
         comando.Dispose();
     }
     catch (Exception ex)
     {
         MessageBox.Show("ERROR: " + ex.Message);
     }
 }
Esempio n. 17
0
        /// <summary>
        ///修改用户密码时对系统数据库进行更新
        /// </summary>
        /// <param name="encryptString"></param>
        /// <param name="connection"></param>
        private void UpdateUserPwdChange(string encryptString, string userName, OdbcConnection connection)
        {
            //int maxCode = CreateMaxCode();

            try
            {
                //string sql = string.Format("insert into SYS_用户表 (用户组代码,用户名,用户密码) values ('{0}','{1}','{2}') ", GetUserGroupCode(GroupName), userName, pwd);
                string      sql        = string.Format("Update SYS_用户组表 Set 密码 = '{0}' where 用户组名称 = '{1}'", encryptString, userName);
                OdbcCommand sysCommand = new OdbcCommand();
                sysCommand.Connection  = connection;
                sysCommand.CommandText = sql;
                sysCommand.ExecuteNonQuery();
                sysCommand.Dispose();
            }
            catch (System.Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
Esempio n. 18
0
        static private void ExecuteMultiQueries(OdbcConnection conn, string[] multiQueries)
        {
            OdbcCommand command = new OdbcCommand();

            command.Connection = conn;
            foreach (string query in multiQueries)
            {
                try
                {
                    command.CommandText = query;
                    command.ExecuteNonQuery();
                }
                catch (Exception e)
                {
                    Console.WriteLine(e.Message);
                }
            }
            command.Dispose();
        }
Esempio n. 19
0
        //public DataTable GetAuditor()
        //{
        //    DataTable dtData = new DataTable();
        //    try
        //    {
        //        connOdbc.Open();
        //        cmdOdbc = new OdbcCommand("select ad_co_name from auditor_profile", connOdbc);
        //        OdbcDataAdapter daOdbc = new OdbcDataAdapter(cmdOdbc);
        //        daOdbc.Fill(dtData);
        //        daOdbc.Dispose();
        //        cmdOdbc.Dispose();
        //    }
        //    catch
        //    { throw; }
        //    finally
        //    { connOdbc.Close(); }
        //    return dtData;
        //}

        public DataTable GetYA()
        {
            DataTable dtData = new DataTable();

            try
            {
                connOdbc.Open();
                cmdOdbc = new OdbcCommand("select ya from year_assessment order by ya desc", connOdbc);
                OdbcDataAdapter daOdbc = new OdbcDataAdapter(cmdOdbc);
                daOdbc.Fill(dtData);
                daOdbc.Dispose();
                cmdOdbc.Dispose();
            }
            catch
            { throw; }
            finally
            { connOdbc.Close(); }
            return(dtData);
        }
Esempio n. 20
0
        public DataTable GetTaxPayerAll(string strSearchText)
        {
            DataTable dtData = new DataTable();

            try
            {
                connOdbc.Open();
                cmdOdbc = new OdbcCommand("SELECT p.pt_ref_no as [Reference No], p.pt_name as [Name], p.pt_register_no as [Registration No] from taxp_profile p ORDER BY pt_ref_no", connOdbc);
                OdbcDataAdapter daOdbc = new OdbcDataAdapter(cmdOdbc);
                daOdbc.Fill(dtData);
                daOdbc.Dispose();
                cmdOdbc.Dispose();
            }
            catch
            { throw; }
            finally
            { connOdbc.Close(); }
            return(dtData);
        }
        public void WriteToODBC(CallOutDetails _details)
        {
            try
            {
                //(patientRegNumber, who, what, when, where, action, time)
                Details = _details;

                //string sqlInsert = $"INSERT INTO kwikmedical.calloutdetails (who) VALUES {Details.Who}";//, {Details.Who},{Details.What},{Details.When},{Details.Where},{Details.Action},{Details.Time}";

                string         sqlInsert    = $"INSERT INTO kwikmedical.calloutdetails (patientNhsNos, who) VALUES ({_details.PatientNHSNumber}, '{_details.Who}');";
                OdbcConnection dbConnection = new OdbcConnection("DSN=KwikMedical");

                //OdbcDataAdapter adapter = new OdbcDataAdapter("", dbConnection);

                //adapter.InsertCommand = new OdbcCommand(sqlInsert);

                //dbConnection.Open();
                //OdbcCommand dbCommmand = dbConnection.CreateCommand();

                OdbcCommand dbCommand = new OdbcCommand();

                dbCommand.Connection = dbConnection;
                dbCommand.Connection.Open();

                dbCommand.CommandText = sqlInsert;

                //dbCommand.Parameters.Add("@patientNhsNos", OdbcType.Int, 11, "123");
                //dbCommand.Parameters.Add("@who", OdbcType.VarChar, 45, "response");

                dbCommand.ExecuteNonQuery();

                //dbCommmand.ExecuteNonQueryAsync();
                dbCommand.Connection.Close();
                dbCommand.Dispose();
                //adapter.Dispose();
                //dbConnection.Close();
            }
            catch (OdbcException ex)
            {
                string error = ex.Message;
                MessageBox.Show(error);
            }
        }
        public override void SetUsrSafeIP(int UsrId, string IpAddress)
        {
            if (da == null)
            {
                throw new System.ObjectDisposedException(GetType().FullName);
            }
            OdbcConnection cn = new OdbcConnection(Config.ConvertOleDbConnStrToOdbcConnStr(GetDesConnStr()));

            cn.Open();
            OdbcCommand cmd = new OdbcCommand("SetUsrSafeIP", cn);

            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@UsrId", OdbcType.Numeric).Value     = UsrId;
            cmd.Parameters.Add("@IpAddress", OdbcType.VarChar).Value = IpAddress;
            TransformCmd(cmd).ExecuteScalar();
            cmd.Dispose();
            cmd = null;
            cn.Close();
        }
Esempio n. 23
0
        //NGOHCS 2009
        public DataTable GetTaxAgent()
        {
            DataTable dtData = new DataTable();

            try
            {
                connOdbc.Open();
                cmdOdbc = new OdbcCommand("select ta_co_name from taxa_profile order by ta_default desc", connOdbcB);
                OdbcDataAdapter daOdbc = new OdbcDataAdapter(cmdOdbc);
                daOdbc.Fill(dtData);
                daOdbc.Dispose();
                cmdOdbc.Dispose();
            }
            catch
            { throw; }
            finally
            { connOdbc.Close(); }
            return(dtData);
        }
        public override bool UpdHintQuestion(string UsrId, string HintQuestionId, string HintAnswer)
        {
            if (da == null)
            {
                throw new System.ObjectDisposedException(GetType().FullName);
            }
            OdbcConnection cn = new OdbcConnection(Config.ConvertOleDbConnStrToOdbcConnStr(GetDesConnStr()));

            cn.Open();
            OdbcCommand cmd = new OdbcCommand("UpdHintQuestion", cn);

            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@UsrId", OdbcType.Numeric).Value = UsrId;
            if (HintQuestionId == string.Empty)
            {
                cmd.Parameters.Add("@HintQuestionId", OdbcType.Numeric).Value = System.DBNull.Value;
            }
            else
            {
                cmd.Parameters.Add("@HintQuestionId", OdbcType.Numeric).Value = HintQuestionId;
            }
            if (Config.DoubleByteDb)
            {
                cmd.Parameters.Add("@HintAnswer", OdbcType.NVarChar).Value = HintAnswer;
            }
            else
            {
                cmd.Parameters.Add("@HintAnswer", OdbcType.VarChar).Value = HintAnswer;
            }
            int rtn = Convert.ToInt32(TransformCmd(cmd).ExecuteScalar());

            cmd.Dispose();
            cmd = null;
            cn.Close();
            if (rtn == 0)
            {
                return(false);
            }
            else
            {
                return(true);
            }
        }
        public override string GetAppVersion(string dbConnectionString, string dbPassword)
        {
            if (da == null)
            {
                throw new System.ObjectDisposedException(GetType().FullName);
            }
            OdbcConnection cn = new OdbcConnection(Config.ConvertOleDbConnStrToOdbcConnStr(dbConnectionString + DecryptString(dbPassword)));

            cn.Open();
            OdbcCommand cmd = new OdbcCommand("GetAppVersion", cn);

            cmd.CommandType = CommandType.StoredProcedure;
            string rtn = Convert.ToString(TransformCmd(cmd).ExecuteScalar());

            cmd.Dispose();
            cmd = null;
            cn.Close();
            return(rtn);
        }
Esempio n. 26
0
        //insertar

        public static string InsertarCliente(ClienteModel cliente)
        {
            OdbcConnection conn = Conexion.obtenerConexion();

            try
            {
                OdbcCommand command = new OdbcCommand();
                string      Sql     = "{call [dbo].[sp_Insertar_Cliente](?,?,?,?,?,?,?)}";

                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = Sql;
                command.Connection  = conn;

                command.Parameters.Add("@Cedula", OdbcType.VarChar);
                command.Parameters["@Cedula"].Value = cliente.Cedula;
                command.Parameters.Add("@Nombre", OdbcType.VarChar);
                command.Parameters["@Nombre"].Value = cliente.Nombre;
                command.Parameters.Add("@Direccion", OdbcType.VarChar);
                command.Parameters["@Direccion"].Value = cliente.Direccion;
                command.Parameters.Add("@Telefono", OdbcType.VarChar);
                command.Parameters["@Telefono"].Value = cliente.Telefono;//Usuario Logueado
                command.Parameters.Add("@Correo", OdbcType.VarChar);
                command.Parameters["@Correo"].Value = cliente.Email;
                command.Parameters.Add("@Estado", OdbcType.Int);
                command.Parameters["@Estado"].Value = cliente.Estado;
                command.Parameters.Add("@Usuario_Creacion", OdbcType.VarChar);
                command.Parameters["@Usuario_Creacion"].Value = cliente.Usuario_Creacion;
                command.ExecuteNonQuery();

                command.Dispose();

                return("true");
            }
            catch (OdbcException ax)
            {
                return("false");
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
        }
Esempio n. 27
0
        //Modificar
        public static string ActualizarProducto(ProductoModel producto)
        {
            OdbcConnection conn = Conexion.obtenerConexion();

            try
            {
                OdbcCommand command = new OdbcCommand();
                string      Sql     = "{call [dbo].[sp_Actualizar_Producto](?,?,?,?,?,?,?)}";

                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = Sql;
                command.Connection  = conn;

                command.Parameters.Add("@Id_Producto", OdbcType.VarChar);
                command.Parameters["@Id_Producto"].Value = producto.Codigo;
                command.Parameters.Add("@Descripcion", OdbcType.VarChar);
                command.Parameters["@Descripcion"].Value = producto.Descripcion;
                command.Parameters.Add("@Precio", OdbcType.Decimal);
                command.Parameters["@Precio"].Value = producto.Precio;
                command.Parameters.Add("@Cant_Inventario", OdbcType.Int);
                command.Parameters["@Cant_Inventario"].Value = producto.CantidadInventario;//Usuario Logueado
                command.Parameters.Add("@Observaciones", OdbcType.VarChar);
                command.Parameters["@Observaciones"].Value = producto.Observaciones;
                command.Parameters.Add("@Imagen", OdbcType.VarChar);
                command.Parameters["@Imagen"].Value = producto.Imagen;
                command.Parameters.Add("@Estado", OdbcType.Int);
                command.Parameters["@Estado"].Value = producto.Estado;
                command.ExecuteNonQuery();

                command.Dispose();

                return("true");
            }
            catch (Exception ax)
            {
                return("false");
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
        }
        public CocktailDTO SelectData(CocktailDTO cocktail)
        {
            CocktailDTO totalInfo = new CocktailDTO();

            try
            {
                conn.Open();
                command = conn.CreateCommand();

                command.CommandText = "select * from cocktail " +
                                      "where name = '" + cocktail.name + "'";
                reader = command.ExecuteReader();

                if (reader.HasRows)
                {
                    reader.Read();
                    totalInfo.name       = reader.GetString(0);
                    totalInfo.alcohol    = reader.GetInt32(1);
                    totalInfo.baseLiquor = reader.GetString(2);
                    totalInfo.material   = reader.GetString(3);
                    totalInfo.recipe     = reader.GetString(4);

                    return(totalInfo);
                }
                else
                {
                    MessageBox.Show("불러오기 실패ㅜ");
                    return(null);
                }
            }
            catch (Exception e)
            {
                MessageBox.Show("불러오기 실패ㅜ");
                return(null);
            }
            finally
            {
                reader.Close();
                command.Dispose();
                conn.Close();
            }
        }
        public DataTable LoadExcel(string aPath)
        {
            var connString = "Driver={Driver do Microsoft Excel(*.xls)};DriverId=790;SafeTransactions=0;ReadOnly=1;MaxScanRows=16;Threads=3;MaxBufferSize=2024;UserCommitSync=Yes;FIL=excel 8.0;PageTimeout=5;";

            connString += "DBQ=" + aPath;
            var conn = new OdbcConnection(connString);
            var cmd  = new OdbcCommand {
                Connection = conn
            };

            System.Windows.Application.Current.Dispatcher.BeginInvoke(new Action <string>(Write), aPath);

            var sheetName = this.GetExcelSheetName(aPath);
            var sql       = "select * from [" + sheetName.Replace('.', '#') + "$]";

            cmd.CommandText = sql;
            var da = new OdbcDataAdapter(cmd);
            var ds = new DataSet();

            try
            {
                da.Fill(ds);
                return(ds.Tables[0]);
            }
            catch (Exception x)
            {
                ds = null;
                return(null);
            }
            finally
            {
                cmd.Dispose();
                cmd = null;
                da.Dispose();
                da = null;
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
                conn = null;
            }
        }
Esempio n. 30
0
        DownloadEASfileAttachmentContent
            (int ID)
        {
            byte[] readBuf   = null;
            long   bytesRead = 0;

            returnDownloadEASfileAttachmentContent rv = new returnDownloadEASfileAttachmentContent();

            DBConnect();
            OdbcCommand cmd = _dbConnection.CreateCommand();

            cmd.CommandText = "select \"c_u_Content\" from \"t_RBSR_AUFW_u_EASfileAttachment\" where \"c_id\" = ?";
            cmd.Parameters.Add("c_id", OdbcType.Int);
            cmd.Parameters["c_id"].Value = (object)ID;
            cmd.Connection = _dbConnection;
            OdbcDataReader dr = cmd.ExecuteReader();

            if (dr.Read())
            {
                if (!dr.IsDBNull(0))
                {
                    readBuf   = (byte[])dr.GetValue(0);
                    bytesRead = readBuf.GetLongLength(0);
                }
            }
            dr.Close();
            dr.Dispose();


            // byte[] readBuf = new byte[(maxBytesToRead>0 ? maxBytesToRead : 1)];
            rv.Content = readBuf;     //  new byte[bytesRead];

            /*
             *          if (bytesRead > 0)
             *          {
             *                  Array.ConstrainedCopy(readBuf, 0, rv.Content, 0, bytesRead);
             *          }
             * */
            cmd.Dispose();
            DBClose();
            return(rv);
        }