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);
}
/// <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);
}
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;
}
}
//===========================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);
}
/// <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);
}
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;
}
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
}
/// <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));
}
}
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);
}
// 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;
}
}
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;
}
}
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);
}
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);
}
}
/// <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);
}
}
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();
}
//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);
}
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();
}
//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);
}
//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();
}
}
//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;
}
}
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);
}