//luodaan uusi bisnes
public BLbusiness(string name, string katuosoite, string postinro, string kaupunki, OdbcConnection conn)
{
//business address
this.address = new BLaddress(conn, katuosoite, postinro, kaupunki);
/*start copy pasta insert/select same in all BL files*/
try
{
//parameterized queries
conn.Open();
OdbcCommand insertcmd = conn.CreateCommand();
insertcmd.CommandText = "INSERT IGNORE INTO business (BusinessName, Address_AddressId) VALUES (?, ?)";
insertcmd.Parameters.Add("@BusinessName", OdbcType.VarChar, 100).Value = name;
insertcmd.Parameters.Add("@Address_AddressId", OdbcType.Int).Value = this.address.id;
insertcmd.ExecuteNonQuery();
}
catch (Exception ex)
{
this.id = -1;
this.name = ex.ToString();
conn.Close();
return;
}
finally
{
conn.Close();
}
try
{
//parameterized queries
conn.Open();
OdbcCommand cmd = conn.CreateCommand();
cmd.CommandText = "SELECT BusinessId, BusinessName FROM business WHERE BusinessName = ?";
cmd.Parameters.Add("@BusinessName", OdbcType.VarChar, 100).Value = name;
OdbcDataReader rows = cmd.ExecuteReader();
rows.Read();
int id;
int.TryParse(rows.GetString(0), out id);
this.id = id;
this.name = rows.GetString(1);
}
catch (Exception ex)
{
this.id = -1;
this.name = ex.ToString();
conn.Close();
return;
}
finally
{
conn.Close();
}
/*end copy pasta insert/select same in all BL files*/
}
public ContentResult Create(string json)
{
OdbcConnection hiveConnection = new OdbcConnection("DSN=Hadoop Server;UID=hadoop;PWD=hadoop");
hiveConnection.Open();
Stream req = Request.InputStream;
req.Seek(0, SeekOrigin.Begin);
string request = new StreamReader(req).ReadToEnd();
ContentResult response;
string query;
try
{
query = "INSERT INTO TABLE error_log (json_error_log) VALUES('" + request + "')";
OdbcCommand command = new OdbcCommand(query, hiveConnection);
command.ExecuteNonQuery();
command.CommandText = query;
response = new ContentResult { Content = "{status: 1}", ContentType = "application/json" };
hiveConnection.Close();
return response;
}
catch(WebException error)
{
response = new ContentResult { Content = "{status: 0, message:" + error.Message.ToString()+ "}" };
System.Diagnostics.Debug.WriteLine(error.ToString());
hiveConnection.Close();
return response;
}
}
void abrirdbf(string tabla)
{
strRutaDbf = System.Configuration.ConfigurationSettings.AppSettings["PathDbfs"];
strSelect = "SELECT * FROM " + tabla + ";";
strConexion = "Driver={Microsoft dBASE Driver (*.dbf)};DriverID=277;Dbq=" + strRutaDbf + ";";
OdbcConnection dbConexionDbf = new System.Data.Odbc.OdbcConnection(strConexion);
try
{
dbConexionDbf.Open();
OdbcDataAdapter da = new System.Data.Odbc.OdbcDataAdapter(strSelect, dbConexionDbf);
da.Fill(dsDocumentos, tabla);
dbConexionDbf.Close();
}
catch (Exception ex)
{
MessageBox.Show("Error al abrir la base de datos\n" + ex.Message);
return;
}
finally
{
if (dbConexionDbf != null && oleConexion.State != ConnectionState.Closed)
{
dbConexionDbf.Close();
}
}
}
//constructor
public BLfeedback(int customerid, int servicepackageid, OdbcConnection conn)
{
/*start copy pasta insert/select same in all BL files*/
try
{
//parameterized queries
conn.Open();
OdbcCommand insertcmd = conn.CreateCommand();
insertcmd.CommandText = "INSERT IGNORE INTO feedback (Customer_CustomerId, Timestamp, ServicePackage_ServicePackageId) VALUES (?,?,?)";
insertcmd.Parameters.Add("@Customer_CustomerId", OdbcType.Int).Value = customerid;
this.timestamp = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
insertcmd.Parameters.Add("@Timestamp", OdbcType.VarChar, 20).Value = this.timestamp;
insertcmd.Parameters.Add("@Customer_CustomerId", OdbcType.Int).Value = servicepackageid;
insertcmd.ExecuteNonQuery();
}
catch (Exception ex)
{
this.id = -1;
this.name = ex.ToString();
conn.Close();
return;
}
finally
{
conn.Close();
}
try
{
//parameterized queries
conn.Open();
OdbcCommand cmd = conn.CreateCommand();
cmd.CommandText = "SELECT FeedbackId, Customer_CustomerId, Timestamp, ServicePackage_ServicePackageId FROM feedback WHERE Customer_CustomerId = ? AND Timestamp = ? AND ServicePackage_ServicePackageId = ?";
cmd.Parameters.Add("@Customer_CustomerId", OdbcType.Int).Value = customerid;
cmd.Parameters.Add("@Timestamp", OdbcType.VarChar, 20).Value = this.timestamp;
cmd.Parameters.Add("@Customer_CustomerId", OdbcType.Int).Value = servicepackageid;
OdbcDataReader rows = cmd.ExecuteReader();
rows.Read();
int id;
int.TryParse(rows.GetString(0), out id);
this.id = id;
int id2;
int.TryParse(rows.GetString(1), out id2);
this.customerid = id2;
this.timestamp = rows.GetString(2);
}
catch (Exception ex)
{
this.id = -1;
this.name = ex.ToString();
conn.Close();
return;
}
finally
{
conn.Close();
}
/*end copy pasta insert/select same in all BL files*/
}
//constructor
public BLcustomer(string name, string phonenumber, string email, OdbcConnection conn)
{
/*start copy pasta insert/select same in all BL files*/
try
{
//parameterized queries
conn.Open();
OdbcCommand insertcmd = conn.CreateCommand();
insertcmd.CommandText = "INSERT IGNORE INTO customer (CustomerName, PhoneNro, Email) VALUES (?, ?, ?)";
insertcmd.Parameters.Add("@CustomerName", OdbcType.VarChar, 100).Value = name;
insertcmd.Parameters.Add("@PhoneNro", OdbcType.VarChar, 10).Value = phonenumber;
insertcmd.Parameters.Add("@Email", OdbcType.VarChar, 100).Value = email;
insertcmd.ExecuteNonQuery();
}
catch (Exception ex)
{
this.id = -1;
this.name = ex.ToString();
conn.Close();
return;
}
finally
{
conn.Close();
}
try
{
//parameterized queries
conn.Open();
OdbcCommand cmd = conn.CreateCommand();
cmd.CommandText = "SELECT CustomerId, CustomerName, PhoneNro, Email FROM customer WHERE CustomerName = ? AND PhoneNro = ? AND Email = ?";
cmd.Parameters.Add("@CustomerName", OdbcType.VarChar, 100).Value = name;
cmd.Parameters.Add("@PhoneNro", OdbcType.VarChar, 10).Value = phonenumber;
cmd.Parameters.Add("@Email", OdbcType.VarChar, 100).Value = email;
OdbcDataReader rows = cmd.ExecuteReader();
rows.Read();
int id;
int.TryParse(rows.GetString(0), out id);
this.id = id;
this.name = rows.GetString(1);
this.phonenumber = rows.GetString(2);
this.email = rows.GetString(3);
}
catch (Exception ex)
{
this.id = -1;
this.name = ex.ToString();
conn.Close();
return;
}
finally
{
conn.Close();
}
/*end copy pasta insert/select same in all BL files*/
}
//constructor
public BLcomment(string name, int feedbackid, OdbcConnection conn)
{
/*start copy pasta insert/select same in all BL files*/
try
{
//parameterized queries
conn.Open();
OdbcCommand insertcmd = conn.CreateCommand();
insertcmd.CommandText = "INSERT IGNORE INTO comment (Text, Feedback_FeedbackId) VALUES (?, ?)";
insertcmd.Parameters.Add("@Text", OdbcType.VarChar, 1024).Value = name;
insertcmd.Parameters.Add("@Feedback_FeedbackId", OdbcType.Int).Value = feedbackid;
insertcmd.ExecuteNonQuery();
}
catch (Exception ex)
{
this.id = -1;
this.name = ex.ToString();
conn.Close();
return;
}
finally
{
conn.Close();
}
try
{
//parameterized queries
conn.Open();
OdbcCommand cmd = conn.CreateCommand();
cmd.CommandText = "SELECT CommentId, Text, Feedback_FeedbackId FROM comment WHERE Text = ? AND Feedback_FeedbackId = ?";
cmd.Parameters.Add("@Text", OdbcType.VarChar, 1024).Value = name;
cmd.Parameters.Add("@Feedback_FeedbackId", OdbcType.Int).Value = feedbackid;
OdbcDataReader rows = cmd.ExecuteReader();
rows.Read();
int id;
int.TryParse(rows.GetString(0), out id);
this.id = id;
this.name = rows.GetString(1);
int id2;
int.TryParse(rows.GetString(2), out id2);
this.feedbackid = id2;
}
catch (Exception ex)
{
this.id = -1;
this.name = ex.ToString();
conn.Close();
return;
}
finally
{
conn.Close();
}
/*end copy pasta insert/select same in all BL files*/
}
protected void btnOK_Click(object sender, EventArgs e)
{
//System.Data.Odbc.OdbcConnection conn = new System.Data.Odbc.OdbcConnection ();
System.Data.Odbc.OdbcConnection conn;
System.Data.Odbc.OdbcDataAdapter ap;
System.Data.Odbc.OdbcDataReader apcheck;
System.Data.Odbc.OdbcCommand objcommand;
objcommand = new System.Data.Odbc.OdbcCommand();
DataSet ds = new DataSet();
//string connectionString = "Data Source=(local);Initial Catalog=Northwind; User ID=Admin; Password=misdyu";
string constr = "FIL=MS Access;SERVER=localhost;DSN=Pform;UID=root;PWD=;OPTION=3";
conn = new System.Data.Odbc.OdbcConnection(constr);
conn.Open();
//採用ODBC，則禁止使用sqlcommand相關指令，會出現語法錯誤，一直抓不到bug
objcommand.Connection = conn;
objcommand.CommandText = "Select * From accountID Where Name = '" + TextBox1.Text + "'";
try
{
// Process data here.
apcheck = objcommand.ExecuteReader();
if (apcheck.HasRows)
{
Label1.Text = "帳號：該帳戶己註冊過";
apcheck.Close();
conn.Close();
}
else
{
conn.Close();
conn = new System.Data.Odbc.OdbcConnection(constr);
conn.Open();
string sql1 = "Insert into accountID (Name, Pass, Email, Address, Gender, Idt) values ('" + TextBox1.Text + "', '" + TextBox2.Text + "', '" + TextBox4.Text + "' , '" + TextBox3.Text + "' , '" + RadioButtonList1.Text + "', '" + DropDownList1.Text + "')";
ap = new System.Data.Odbc.OdbcDataAdapter(sql1, conn);
ap.Fill(ds);
//傳統寫法
Response.Redirect("ShowData.aspx?ID=" + TextBox1.Text + "&Password="******"&Msg=" + DropDownList1.Text);
//使用字串參數的型式,ERROR會出現兩次註冊
//Response.Redirect(string.Format("ShowData.aspx?ID={0}&Password={1}&Msg={2}", TextBox1.Text, TextBox2.Text, DropDownList1.Text));
}
}
catch (Exception ex)
{
//新版不支援下行
// MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
}
//constructor
public BLaddress(OdbcConnection conn, string katuosoite, string postinro, string kaupunki)
{
//constructing attributes
this.city = new BLcity(conn, kaupunki);
this.zipcode = new BLzipcode(conn, postinro);
/*start copy pasta insert/select same in all BL files*/
try
{
//parameterized queries
conn.Open();
OdbcCommand insertcmd = conn.CreateCommand();
insertcmd.CommandText = "INSERT IGNORE INTO address (City_CityId, ZIPCode_ZIPCodeId, StreetAddress) VALUES (?, ?, ?)";
insertcmd.Parameters.Add("@City_CityId", OdbcType.Int).Value = this.city.id;
insertcmd.Parameters.Add("@ZIPCode_ZIPCodeId", OdbcType.Int).Value = this.zipcode.id;
insertcmd.Parameters.Add("@StreetAddress", OdbcType.VarChar, 50).Value = katuosoite;
insertcmd.ExecuteNonQuery();
}
catch (Exception ex)
{
this.name = ex.ToString();
conn.Close();
return;
}
finally
{
conn.Close();
}
try
{
//parameterized queries
conn.Open();
OdbcCommand cmd = conn.CreateCommand();
cmd.CommandText = "SELECT AddressId, StreetAddress FROM address WHERE StreetAddress = ?";
cmd.Parameters.Add("@ZIPCodeNumber", OdbcType.VarChar, 50).Value = katuosoite;
OdbcDataReader rows = cmd.ExecuteReader();
rows.Read();
int id;
int.TryParse(rows.GetString(0), out id);
this.id = id;
this.name = rows.GetString(1);
}
catch (Exception ex)
{
this.name = ex.ToString();
conn.Close();
return;
}
finally
{
conn.Close();
}
/*end copy pasta insert/select same in all BL files*/
}
protected void btnLoginOK_Click(object sender, EventArgs e)
{
string connection = ConfigurationSettings.AppSettings["connectionString"];
OdbcConnection dbCon = new OdbcConnection(connection);
string sql = "SELECT seq FROM profile WHERE (MEMBER_NAME = @userid) AND (MEMBER_PWD = @pwd)";
OdbcCommand dbCmd = new OdbcCommand(sql, dbCon);
dbCmd.Parameters.Add("@userid", OdbcType.VarChar, 50, "MEMBER_NAME");
dbCmd.Parameters.Add("@pwd", OdbcType.VarChar, 30, "MEMBER_PWD");
dbCon.Open();
dbCmd.Parameters["@userid"].Value = txtName.Text;
dbCmd.Parameters["@pwd"].Value = txtPwd.Text;
if (dbCmd.ExecuteScalar() == null)
{
ClientScriptManager cs = Page.ClientScript;
cs.RegisterStartupScript(this.GetType(), "windowClose", "window.location.replace('./DefaultLogin.aspx');", true);
cs.RegisterStartupScript(this.GetType(), "PopupScript", "alert('Incorrect ID/PW');", true);
dbCmd.ExecuteNonQuery();
dbCon.Close();
}
else
{
dbCmd.CommandText = "select * from profile where (MEMBER_NAME = @userid)";
OdbcDataReader reader = dbCmd.ExecuteReader();
reader.Read();
//add session
Session.Add("USER_ID", txtName.Text);
Session.Add("USER_NAME", reader["MEMBER_NAME"].ToString());
Session.Add("USER_PWD", reader["MEMBER_PWD"].ToString());
reader.Close();
dbCmd.ExecuteNonQuery();
dbCon.Close();
Response.Redirect("./Board_list.aspx");
//if (Session["USER_ID"] != null)
//{
// namelbl2.Text = Session["USER_ID"].ToString() + "Welcom.";
// //hide login
// LoginForm.Visible = false;
// //dis login
// loginchk.Visible = true;
//}
}
}
public BLservicepackage(string name, int service, OdbcConnection conn)
{
/*start copy pasta insert/select same in all BL files*/
try
{
//parameterized queries
conn.Open();
OdbcCommand insertcmd = conn.CreateCommand();
insertcmd.CommandText = "INSERT IGNORE INTO servicepackage (ServicePackageName, Service_ServiceId) VALUES (?, ?)";
insertcmd.Parameters.Add("@ServicePackageName", OdbcType.VarChar, 100).Value = name;
insertcmd.Parameters.Add("@Service_ServiceId", OdbcType.Int).Value = service;
insertcmd.ExecuteNonQuery();
}
catch (Exception ex)
{
this.id = -1;
this.name = ex.ToString();
conn.Close();
return;
}
finally
{
conn.Close();
}
try
{
//parameterized queries
conn.Open();
OdbcCommand cmd = conn.CreateCommand();
cmd.CommandText = "SELECT ServicePackageId, ServicePackageName FROM servicepackage WHERE ServicePackageName = ?";
cmd.Parameters.Add("@ServicePackageName", OdbcType.VarChar, 100).Value = name;
OdbcDataReader rows = cmd.ExecuteReader();
rows.Read();
int id;
int.TryParse(rows.GetString(0), out id);
this.id = id;
this.name = rows.GetString(1);
}
catch (Exception ex)
{
this.id = -1;
this.name = ex.ToString();
conn.Close();
return;
}
finally
{
conn.Close();
}
/*end copy pasta insert/select same in all BL files*/
}
//constructor
public BLzipcode(OdbcConnection conn, string postinro)
{
/*start copy pasta insert/select same in all BL files*/
try
{
//parameterized queries
conn.Open();
OdbcCommand insertcmd = conn.CreateCommand();
insertcmd.CommandText = "INSERT IGNORE INTO zipcode (ZIPCodeNumber) VALUES (?)";
insertcmd.Parameters.Add("@ZIPCodeNumber", OdbcType.VarChar, 5).Value = postinro;
insertcmd.ExecuteNonQuery();
}
catch (Exception ex)
{
this.id = -1;
this.name = ex.ToString();
conn.Close();
return;
}
finally
{
conn.Close();
}
try
{
//parameterized queries
conn.Open();
OdbcCommand cmd = conn.CreateCommand();
cmd.CommandText = "SELECT ZIPCodeId, ZIPCodeNumber FROM zipcode WHERE ZIPCodeNumber = ?";
cmd.Parameters.Add("@ZIPCodeNumber", OdbcType.VarChar, 5).Value = postinro;
OdbcDataReader rows = cmd.ExecuteReader();
rows.Read();
int id;
int.TryParse(rows.GetString(0), out id);
this.id = id;
this.name = rows.GetString(1);
}
catch (Exception ex)
{
this.id = -1;
this.name = ex.ToString();
conn.Close();
return;
}
finally
{
conn.Close();
}
/*end copy pasta insert/select kaikissa osoite BL:ssä sama*/
}
//constructor
public BLcity(OdbcConnection conn, string kaupunki)
{
/*start copy pasta insert/select same in all BL files*/
try
{
//parameterized queries
conn.Open();
OdbcCommand insertcmd = conn.CreateCommand();
insertcmd.CommandText = "INSERT IGNORE INTO city (CityName) VALUES (?)";
insertcmd.Parameters.Add("@CityName", OdbcType.VarChar, 20).Value = kaupunki;
insertcmd.ExecuteNonQuery();
}
catch (Exception ex)
{
this.id = -1;
this.name = ex.ToString();
conn.Close();
return;
}
finally
{
conn.Close();
}
try
{
//parameterized queries
conn.Open();
OdbcCommand cmd = conn.CreateCommand();
cmd.CommandText = "SELECT CityId, CityName FROM city WHERE CityName = ?";
cmd.Parameters.Add("@CityName", OdbcType.VarChar, 20).Value = kaupunki;
OdbcDataReader rows = cmd.ExecuteReader();
rows.Read();
int id;
int.TryParse(rows.GetString(0), out id);
this.id = id;
this.name = rows.GetString(1);
}
catch (Exception ex)
{
this.id = -1;
this.name = ex.ToString();
conn.Close();
return;
}
finally
{
conn.Close();
}
/*end copy pasta insert/select same in all BL files*/
}
public static IEnumerable <String> sqlReadQuery(string vOdbcName, string queryString = "")
{
var vCurrODBC = In2SqlSvcODBC.vODBCList.Find(item => item.OdbcName == vOdbcName);
using
(OdbcConnection conn = new System.Data.Odbc.OdbcConnection())
{
using (OdbcCommand cmnd = new OdbcCommand(queryString, conn))
{
try
{
vCurrODBC.DSNStr = "DSN=" + vOdbcName;
if (vCurrODBC.Login != null)
{
vCurrODBC.DSNStr = vCurrODBC.DSNStr + ";Uid=" + vCurrODBC.Login + ";Pwd=" + vCurrODBC.Password + ";";
}
conn.ConnectionString = vCurrODBC.DSNStr;
conn.ConnectionTimeout = 5;
conn.Open();
}
catch (Exception e)
{
In2SqlSvcTool.ExpHandler(e, "In2SqlSvcODBC.ReadData", queryString);
conn.Close();
conn.Dispose();
yield break;
}
OdbcDataReader rd = cmnd.ExecuteReader();
string strRow = "";
while (rd.Read())
{
strRow = "";
for (int i = 0; i < rd.FieldCount; i++)
{
strRow = strRow + '"' + rd.GetString(i) + '"';
if (i < rd.FieldCount - 1)
{
strRow += ",";
}
}
yield return(strRow);
conn.Close();
conn.Dispose();
}
}
}
}
private void Disconnect()
{
if (OdbcCon.State == ConnectionState.Open)
{
OdbcCon.Close();
}
}
/// <summary>
/// Permite valorar si el empleado tiene justificado el día ingresado
/// </summary>
/// <param name="EmpleadoId"></param>
/// <param name="Fecha"></param>
/// <returns></returns>
public bool GetJustificacion(int EmpleadoId, DateTime Fecha)
{
bool DiaJustificado = false;
try
{
string QueryString = @"EXEC stp_GetJustificacionFalta " + EmpleadoId + ", '" + Fecha.ToString("yyyyMMdd") + "'";
System.Data.Odbc.OdbcCommand command = new System.Data.Odbc.OdbcCommand(QueryString);
using (System.Data.Odbc.OdbcConnection connection = new System.Data.Odbc.OdbcConnection(ConnectionString))
{
command.Connection = connection;
connection.Open();
using (OdbcDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
DiaJustificado = reader.GetBoolean(0);
}
reader.Close();
reader.Dispose();
}
connection.Close();
connection.Dispose();
}
}
catch (Exception ex)
{
Log.EscribeLog("Error: ElsabonDA.GetJustificacion - " + ex.Message);
}
return(DiaJustificado);
}
protected void Page_Load(object sender, EventArgs e)
{
string cate = Request.QueryString["category"];
int artID = System.Convert.ToInt32(Request.QueryString["articleID"]);
string MyConString = ConfigurationSettings.AppSettings["connectionString"];
OdbcConnection connection = new OdbcConnection(MyConString);
// build our query statement
string sqlText = "select * FROM articles";
OdbcCommand command = new OdbcCommand(sqlText, connection);
// open the database and get a datareader
connection.Open();
OdbcDataReader dr = command.ExecuteReader(CommandBehavior.CloseConnection);
connection.Close();
string selectSQL = "SELECT * FROM articles WHERE articleID = '" + artID + "'";
OdbcCommand cmd1 = new OdbcCommand(selectSQL, connection);
OdbcDataAdapter adapter = new OdbcDataAdapter(cmd1);
DataSet ds = new DataSet();
adapter.Fill(ds);
DataList10.DataSource = ds;
DataList10.DataBind();
}
public MyConnectionOdbc(DataProvider dataProvider)
: base(dataProvider)
{
// Crear conexión
OdbcConnection conn = new OdbcConnection(@"provider = sqlodbc;
data source = .\sqlexpress; trusted connection = yes;");
try
{
// Abrir conexión
conn.Open();
Console.WriteLine("Conexión establecida.");
// Detalles de la conexión
this.DetallesConexion(conn);
}
catch (OdbcException ex)
{
// Desplegar excepción o error
Console.WriteLine("Error: " + ex.Message + ex.StackTrace);
}
finally
{
//Cerrar la conexión
conn.Close();
}
}
static void Main(string[] args)
{
//"DRIVER={MySQL ODBC 5.2w Driver};SERVER=localhost;DATABASE=config;UID=root;PASSWORD=liu355dq;"
string MyConString = "DRIVER={MySQL ODBC 5.2w Driver};" +
"SERVER=localhost;" +
"DATABASE=g_db;" +
"UID=root;" +
"PASSWORD=liu355dq;";
OdbcConnection myconnection = new OdbcConnection(MyConString);
OdbcCommand cmd = new OdbcCommand("select * from g_operator");
cmd.Connection = myconnection;
myconnection.Open();
OdbcDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Console.WriteLine(reader[1]);
}
myconnection.Close();
Console.ReadKey();
}
protected void Page_Load(object sender, EventArgs e)
{
Button2.Enabled = false;
if (Session["auth"] == null)
{
Response.Redirect("Homepage.aspx");
}
ListItem newItem = new ListItem();
string constr = Session["connection"].ToString();
OdbcConnection cn = new OdbcConnection(constr);
cn.Open();
string sql = "select pay_no from PAYMENT";
OdbcCommand cmd = new OdbcCommand(sql, cn);
OdbcDataReader reader;
reader = cmd.ExecuteReader();
while (reader.Read())
{
newItem = new ListItem();
newItem.Text = reader["pay_no"].ToString();
DropDownList1.Items.Add(newItem);
}
reader.Close();
cn.Close();
}
public void load_list()
{
try
{
string workerName = "admin"; //Session["USER_ID"].ToString();
string connection = ConfigurationSettings.AppSettings["ConnectionString"];
OdbcConnection dbCon = new OdbcConnection(connection);
dbCon.Open();
string sql = "select count(*) from Board";
OdbcCommand cmd = new OdbcCommand(sql, dbCon);
total = int.Parse(cmd.ExecuteScalar().ToString());
cmd.CommandText = "select * from Board order by headnum DESC, depth ASC";
OdbcDataAdapter dbAdap = new OdbcDataAdapter(cmd);
DataSet ds = new DataSet();
dbAdap.Fill(ds);
dbAdap.Update(ds);
GridView1.DataSource = ds;
GridView1.DataBind();
dbCon.Close();
}
catch { }
}
public static string constr = "DSN=ground_tariff"; //@"Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=d:\documents and settings\axkhan2\desktop\437proj\GHEC Rates NL.xls;DefaultDir=d:\documents and settings\axkhan2\desktop\437proj\;";
static void Main()
{
OdbcConnection conxls = new OdbcConnection(constr);
try
{
conxls.Open();
OdbcDataAdapter da = new OdbcDataAdapter("select * from [Sheet1$]", conxls);
DataSet set = new DataSet();
da.Fill(set, "mytariff");
DataTable table = set.Tables[0];
DataView dv = new DataView(table);
// Console.WriteLine(dv[0]);
//InsertData(rdxls);
}
catch(OdbcException od)
{
Console.WriteLine(od.Message);
}
finally
{
conxls.Close();
}
}
public DataSet OpenCsv(string strFilePath)
{
if (!File.Exists(strFilePath))
{
return null;
}
string strFolderPath = Path.GetDirectoryName(strFilePath);
string strCSVFile = Path.GetFileName(strFilePath);
DataSet ds = null;
string strConnection = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + strFolderPath + ";Extensions=asc,csv,tab,txt;Persist Security Info=False";
try
{
using (OdbcConnection conn = new OdbcConnection(strConnection.Trim()))
{
conn.Open();
string strSql = "select * from [" + strCSVFile + "]";
OdbcDataAdapter odbcDAdapter = new OdbcDataAdapter(strSql, conn);
ds = new DataSet();
odbcDAdapter.Fill(ds, "table");
//ds.Tables[0].Rows[0]["zh"] = "12345678901234567890";
conn.Close();
}
return ds;
}
catch (Exception e)
{
throw e;
}
return ds;
}
public static void Load_Sport_TXT_TO_DB_ByTableNameAndFieldName(string table_name, string field_names)
{
OpenFileDialog ofd = new OpenFileDialog();
if (ofd.ShowDialog() == DialogResult.OK)
{
using (OdbcConnection conn = new OdbcConnection(Basic_HTB_Info.Conn_Str))
{
conn.Open();
//MessageBox.Show(String.Format("TRUNCATE TABLE {0};", table_name));
//using (OdbcCommand cmd = new OdbcCommand(String.Format("TRUNCATE TABLE {0};", table_name), conn))
//{
// cmd.ExecuteNonQuery();
//}
StreamReader sr = new StreamReader(ofd.FileName,Encoding.Default);
string line = null;
while ((line = sr.ReadLine()) != null)
{
string[] str_a = line.Split(';');
String[] fieldnames = field_names.Split(',');
if (str_a.Length == fieldnames.Length)
{
Lib.inc_cmd(field_names, table_name, str_a, conn);
}
}
conn.Close();
}
}
}
/// <summary>
/// Executa algum comando de inserção, exclusão ou edição
/// </summary>
/// <param name="sql">o sql a ser executado</param>
public void ExecutaComando(string sql)
{
//Usa a classe de conexão apenas nesse bloco
using(OdbcConnection conn = new OdbcConnection(this.ConnectionString))
{
try
{
//Abre conexão com o servidor de banco
conn.Open();
//instancia um objeto de comando, passando como parametro o sql e a conexão para o construtor
OdbcCommand comm = new OdbcCommand(sql, conn);
//Executa o comando
comm.ExecuteNonQuery();
}
catch (Exception ex)
{
//Atira uma nova excessão, caso dê algum erro
throw new Exception(ex.Message);
}
finally
{
//Sempre fechará conexão, independente se der erro ou não
conn.Close();
}
}
}
protected void Page_Load(object sender, EventArgs e)
{
// Cria lista para o objeto
List<agenda> msg = new List<agenda>();
// Adiciona dados ao objeto
try
{
using (OdbcConnection connection = new OdbcConnection(ConfigurationManager.ConnectionStrings["MySQLServerConnStr"].ConnectionString))
{
connection.Open();
using (OdbcCommand command = new OdbcCommand("SELECT * FROM agenda", connection))
using (OdbcDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
msg.Add(new agenda(Int32.Parse(dr["id"].ToString()), dr["nome"].ToString(), dr["telefone"].ToString()));
}
dr.Close();
}
connection.Close();
}
}
catch (Exception ex)
{
Response.Write("An error occured: " + ex.Message);
}
// Adiciona objeto a sessão
Session["registros"] = msg;
// Cria pagina no método HTTP GET
if (Request.HttpMethod.Equals("GET"))
doGet();
}
/* queries for all students (IDs, First names, last names)
* whose ID numbers contain the values provided by the student W# field
* in the current form. */
private void button1_Click(object sender, System.EventArgs e)
{
//Refresh the listview.
searchView.Items.Clear();
string SearchTerm = searchField.Text;
System.Data.Odbc.OdbcConnection connection = new System.Data.Odbc.OdbcConnection(dsnSource);
try
{
connection.Open();
System.Data.Odbc.OdbcCommand command = connection.CreateCommand();
command.CommandText = "SELECT ID, FIRST_NAME, LAST_NAME FROM G_PERSON WHERE ID like '%" + SearchTerm + "%'";
System.Data.Odbc.OdbcDataReader Reader = command.ExecuteReader();
while (Reader.Read())
{
String id = Reader["ID"].ToString();
String fname = Reader["FIRST_NAME"].ToString();
String lname = Reader["LAST_NAME"].ToString();
ListViewItem item = new ListViewItem(new[] { id, fname, lname });
searchView.Items.Add(item);
}
connection.Close();
} catch (Exception s) {
MessageBox.Show("Could not connect at this time. Please try again later.");
MessageBox.Show(s.ToString());
}
}
private void btnOpenFile_Click(object sender, RoutedEventArgs e)
{
try
{
OdbcConnection obdcconn = new System.Data.Odbc.OdbcConnection();
string fileName = "DUTY.DBF";
//OpenFileDialog openFileDialog = new OpenFileDialog();
//if (openFileDialog.ShowDialog() == true)
//{
//lblStatus.Content = "File conversion is in progress.. Please wait..!!";
obdcconn.ConnectionString = GetConnectionString(System.Configuration.ConfigurationSettings.AppSettings["InputPath"]);
obdcconn.Open();
OdbcCommand oCmd = obdcconn.CreateCommand();
oCmd.CommandText = "SELECT * FROM " + System.Configuration.ConfigurationSettings.AppSettings["InputPath"];
/*Load data to table*/
DataTable dt1 = new DataTable();
dt1.Load(oCmd.ExecuteReader());
string currentPath = Path.GetFullPath(System.Configuration.ConfigurationSettings.AppSettings["InputPath"]);
currentPath = Directory.GetParent(currentPath).FullName + "\\";
obdcconn.Close();
WriteDataToTemplate(currentPath, fileName, dt1);
//DataSetIntoDBF(fileName, currentPath, dt1);
//lblStatus.Content = "File conversion completed, file is in " + dir + "\\" + fileName;
//}
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public bool existDoc(string sSIIDOC, string sSIINUM)
{
bool bResp = false;
OdbcConnection conn = new OdbcConnection();
conn.ConnectionString = this.sConnection;
try
{
conn.Open();
string sQuery = "SELECT 1 FROM " + sTable + "WHERE SIIDOC=@val1 WHERE SIINUM=@val2";
using (OdbcCommand cmd = new OdbcCommand(sQuery, conn))
{
cmd.Parameters.Add("@val1", OdbcType.VarChar).Value = sSIIDOC;
cmd.Parameters.Add("@val2", OdbcType.VarChar).Value = sSIINUM;
using (OdbcDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
string sResp = reader.GetString(0);
if (sResp.Substring(0) == "1") bResp = true;
else bResp = false;
}
}
}
}
catch (Exception ex)
{
bResp = false;
}
finally
{
conn.Close();
}
return bResp;
}
/// <summary>
/// Realiza una consulta de tipo ODBC mediante sentencia Sql estandar.
/// </summary>
/// <param name="NomTabla">Nombre de la tabla que almacena el resultado de la consulta.</param>
/// <param name="sentenciaSql">cadena con la sentencia sql que realiza la consulta</param>
/// <param name="key">Nombre que identifica el string de conexión</param>
/// <returns>Retorna un dataset con los resultados de la consulta</returns>
public DataSet ConsultaOdbc(string NomTabla, string sentenciaSql, string key)
{
DataSet dsBasico = new DataSet();
string strConnString = ConfigurationManager.ConnectionStrings[key].ConnectionString;
OdbcConnection OdbcCon = new System.Data.Odbc.OdbcConnection();
OdbcCon.ConnectionString = strConnString;
OdbcCon.Open();
OdbcDataAdapter DataAdapterOdbc = new OdbcDataAdapter();
try
{
DataAdapterOdbc = new OdbcDataAdapter(sentenciaSql, OdbcCon);
DataAdapterOdbc.Fill(dsBasico, NomTabla);
return(dsBasico);
}
catch (Exception r)
{
string m = r.Message;
return(dsBasico);
}
finally
{
DataAdapterOdbc.Dispose();
OdbcCon.Close();
}
}
public void LoadDB()
{
_MCDB = new DataSet();
try
{
// Creates and opens an ODBC connection
String strConnString = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + _DBPath.Trim() + ";Extensions=asc,csv,tab,txt;Persist Security Info=False;HDR=NO;";
String sql_select;
OdbcConnection conn;
conn = new OdbcConnection(strConnString.Trim());
conn.Open();
//Creates the select command text
sql_select = "select * from [" + this._DBFileName.Trim() + "]";
//Creates the data adapter
OdbcDataAdapter obj_oledb_da = new OdbcDataAdapter(sql_select, conn);
//Fills dataset with the records from CSV file
obj_oledb_da.Fill(_MCDB, "csv");
//closes the connection
conn.Close();
}
catch (Exception e) //Error
{
Console.WriteLine("Error in: " + e.ToString());
}
}
public static bool credentials_correct(string driver, string user, string password)
{
crypt crypt = new crypt();
string encPass = crypt.HashString(password);
try
{
OdbcConnection con = new OdbcConnection("Driver=" + driver + ";Server=" + server + ";Database=" + dbase + ";UID=" + uid + ";PWD=" + crypt.DecryptString(pwd) + ";OPTION=3");
OdbcCommand com = new OdbcCommand("SELECT user FROM users where user = '******' and password = '******'", con);
con.Open();
OdbcDataReader dr = com.ExecuteReader();
int tmpInt = 0;
while (dr.Read())
{
tmpInt++;
}
dr.Close();
con.Close();
return tmpInt > 0;
}
catch
{
return false;
}
}
protected void Page_Load(object sender, EventArgs e)
{
// string ImageId = System.Convert.ToString(Request.QueryString["name"]);
int articleID = System.Convert.ToInt32(Request.QueryString["textid"]);
string MyConString = ConfigurationSettings.AppSettings["connectionString"];
OdbcConnection connection = new OdbcConnection(MyConString);
string sqlNews1 = "select * FROM latestnews";
OdbcCommand commandNews1 = new OdbcCommand(sqlNews1, connection);
connection.Open();
OdbcDataReader dr = commandNews1.ExecuteReader(CommandBehavior.CloseConnection);
if (dr.Read())
{
Response.ContentType = dr["date"].ToString();
Response.ContentType = dr["title"].ToString();
Response.ContentType = dr["content"].ToString();
}
connection.Close();
string selectNews = "SELECT date, title, content FROM latestnews ORDER BY date DESC";
OdbcCommand command = new OdbcCommand(selectNews, connection);
OdbcDataAdapter adapter = new OdbcDataAdapter(command);
DataSet ds = new DataSet();
adapter.Fill(ds);
DataList1.DataSource = ds;
DataList1.DataBind();
}
//public static string GetAssessmentResult(int aoid)
//{
// Assessment ass = new Assessment();
// string tmp="";
// return ass.GetAssessmentResult(aoid,out tmp);
//}
protected void ButtonViewResult_Click(object sender, EventArgs e)
{
using (OdbcConnection connection = new OdbcConnection(connectionString))
{
using (OdbcCommand command = new OdbcCommand())
{
command.Connection = connection;
command.CommandText = "{CALL ScoreDetailTable_InsertNow(?)}";
command.CommandType = CommandType.StoredProcedure;
command.CommandTimeout = 999;
command.Parameters.AddWithValue("@AOID", aid);
try
{
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
int n = command.ExecuteNonQuery();
}
catch (Exception ex)
{ }
finally
{
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
}
}
}
}
private void btnSignIn_Click(object sender, EventArgs e)
{
OdbcConnection Conn = new OdbcConnection("DSN=ora10glocal;uid=TTCS;PWD=Pass1234");
OdbcCommand cmd = new OdbcCommand("SELECT EMPLOYEEPASSWORD FROM EMPLOYEE WHERE LASTNAME = '" + txtUserName.Text + "'AND EMPLOYEETYPE = 'Foreman';", Conn);
Conn.Open();
Object EmpPass = cmd.ExecuteScalar();
cmd.Dispose();
Conn.Close();
if (txtUserName.Text == "")
{
MessageBox.Show("User name is empty!");
}
else if (Convert.ToString(EmpPass) == "")
{
MessageBox.Show("Password is empty!");
}
else if (Convert.ToString(EmpPass) != txtPassword.Text)
{
MessageBox.Show("Wrong password! please type again!");
}
else
{
string Emp = Convert.ToString(EmpPass);
var frm = new Form1(Emp);
frm.ShowDialog();
//this.Visible = false
}
}
public List<DataImportColumn> GetTableDataColumnList(string request)
{
List<DataImportColumn> lstDc = new List<DataImportColumn>();
using (OdbcConnection conn = new OdbcConnection(_connectionString))
{
OdbcCommand command = new OdbcCommand(request, conn);
conn.Open();
OdbcDataReader dr = command.ExecuteReader();
while (dr.Read())
{
lstDc.Add(new DataImportColumn()
{
Name = dr[0].ToString()
,
DataType = dr[3].ToString()
,
Size = dr[2].ToString()
});
}
dr.Close();
dr.Dispose();
command.Dispose();
conn.Close();
}
return lstDc;
}
public static System.Data.Odbc.OdbcDataReader ExecuteReaderQueryOnDB(string sSQL, string s_ConnectionString)
{
System.Data.Odbc.OdbcConnection QConnection = null;
System.Data.Odbc.OdbcCommand QCommand = null;
try
{
QConnection = new System.Data.Odbc.OdbcConnection(s_ConnectionString);
QCommand = new System.Data.Odbc.OdbcCommand(sSQL, QConnection);
QConnection.Open();
return(QCommand.ExecuteReader());
}
finally
{
if (QCommand != null)
{
QCommand.Dispose();
}
QCommand = null;
if (QConnection != null && QConnection.State != System.Data.ConnectionState.Closed)
{
QConnection.Close();
}
if (QConnection != null)
{
QConnection.Dispose();
}
QConnection = null;
}
}
public void getCCTVDataByDeviceName(string devName, ref int cctvid, ref int preset)
{
OdbcConnection cn = new OdbcConnection(Global.Db2ConnectionString);
OdbcCommand cmd=new OdbcCommand("select CAMERA_ID,PRESET_ID from tblCCTVPreset where devicename='"+devName+"'");
cmd.Connection = cn;
OdbcDataReader rd;
try
{
cn.Open();
rd = cmd.ExecuteReader();
if (rd.Read())
{
cctvid = System.Convert.ToInt32(rd[0]);
preset = System.Convert.ToInt32(rd[1]);
}
else
{
cctvid = -1;
preset = -1;
}
}
catch (Exception ex)
{
RemoteInterface.ConsoleServer.WriteLine(ex.Message + "," + ex.StackTrace);
}
finally
{
cn.Close();
}
}
protected void Button1_Click(object sender, EventArgs e)
{
string[] strArray = new string[5] { "Delhi", "Mumbai", "Kolkata", "Chennai", "Chandigarh" };
foreach (string str in strArray)
{
OdbcConnection oledbConn = new OdbcConnection("DSN=exceldb");
try
{
// Open connection
oledbConn.Open();
// Create OleDbCommand object and select data from worksheet Sheet1
OdbcCommand cmd = new OdbcCommand("SELECT * FROM [Details$]", oledbConn);
//cmd.Parameters.AddWithValue("@city",str);where city=@city
// Create new OleDbDataAdapter
OdbcDataAdapter oleda = new OdbcDataAdapter();
oleda.SelectCommand = cmd;
// Create a DataSet which will hold the data extracted from the worksheet.
// DataSet ds = new DataSet();
DataTable dt = new DataTable();
// Fill the DataSet from the data extracted from the worksheet.
oleda.Fill(dt);
/* if(dt.Rows.Count>0)
{
ID = dt.Rows[0]["ID"].ToString(); //Where ColumnName is the Field from the DB that you want to display
name= dt.Rows[0]["Name"].ToString();
Address = dt.Rows[0]["Address"].ToString();
EmailAddress = dt.Rows[0]["emailaddress"].ToString();
if(EmailAddress!=null)
{
if(EmailAddress==)
}
EmailContent = dt.Rows[0]["emailcontent"].ToString();
}
*/
DataView dv = new DataView(dt);
// dv.Sort = "emailcontents";
// dv.Sort = "Name";
// Bind the data to the GridView
Grdexcel.DataSource = dv;
Grdexcel.DataBind();
// cmd = new OdbcCommand("delete FROM [ter$] where Address like 'M%'", oledbConn);
// cmd.ExecuteNonQuery();
//cmd.CommandType
}
catch (Exception ex)
{
Alert.Show("Sorry");
}
finally
{
// Close connection
oledbConn.Close();
}
}
}
public DataTable ExecuteSQLQuery(string sQuery, string sCompanyCode, OdbcParameter[] param)
{
string sFuncName = "ExecuteSQLQuery()";
string sConstr = ConfigurationManager.ConnectionStrings["DBConnection"].ToString();
string[] sArray = sConstr.Split(';');
string sSplitCompany = sConstr.Split(';').Last();
string sSplit1 = sSplitCompany.Split('=').First();
string sCompanyGenerate = sSplit1 + "=" + sCompanyCode;
sConstr = sArray[0] + ";" + sArray[1] + ";" + sArray[2] + ";" + sArray[3] + ";" + sCompanyGenerate;
System.Data.Odbc.OdbcConnection oCon = new System.Data.Odbc.OdbcConnection(sConstr);
System.Data.Odbc.OdbcCommand oCmd = new System.Data.Odbc.OdbcCommand();
DataSet oDs = new DataSet();
try
{
oCon.Open();
oCmd.CommandType = CommandType.Text;
oCmd.CommandText = sQuery;
if (p_iDebugMode == DEBUG_ON)
{
oLog.WriteToDebugLogFile("SQL Query : " + sQuery, sFuncName);
}
if (p_iDebugMode == DEBUG_ON)
{
oLog.WriteToDebugLogFile("Before adding Parameters", sFuncName);
}
foreach (var item in param)
{
oCmd.Parameters.Add(item);
}
if (p_iDebugMode == DEBUG_ON)
{
oLog.WriteToDebugLogFile("After adding parameters", sFuncName);
}
oCmd.Connection = oCon;
oCmd.CommandTimeout = 120;
System.Data.Odbc.OdbcDataAdapter da = new System.Data.Odbc.OdbcDataAdapter(oCmd);
da.Fill(oDs);
oCon.Close();
if (p_iDebugMode == DEBUG_ON)
{
oLog.WriteToDebugLogFile("Completed with SUCCESS", sFuncName);
}
}
catch (Exception ex)
{
if (p_iDebugMode == DEBUG_ON)
{
oLog.WriteToDebugLogFile("Completed with ERROR", sFuncName);
}
oCon.Dispose();
throw new Exception(ex.Message);
}
return(oDs.Tables[0]);
}
public DataTable ExecuteNonQuery(string sQuery, OdbcParameter[] param)
{
string sFuncName = "ExecuteNonQuery";
if (p_iDebugMode == DEBUG_ON)
{
oLog.WriteToDebugLogFile("Starting Function", sFuncName);
}
string sConstr = ConfigurationManager.ConnectionStrings["DBConnection"].ToString();
System.Data.Odbc.OdbcConnection oCon = new System.Data.Odbc.OdbcConnection(sConstr);
System.Data.Odbc.OdbcCommand oCmd = new System.Data.Odbc.OdbcCommand();
DataSet oDs = new DataSet();
try
{
oCon.Open();
oCmd.CommandType = CommandType.Text;
oCmd.CommandText = sQuery;
if (p_iDebugMode == DEBUG_ON)
{
oLog.WriteToDebugLogFile("SQL Query : " + sQuery, sFuncName);
}
if (p_iDebugMode == DEBUG_ON)
{
oLog.WriteToDebugLogFile("Before adding Parameters", sFuncName);
}
foreach (var item in param)
{
oCmd.Parameters.Add(item);
}
if (p_iDebugMode == DEBUG_ON)
{
oLog.WriteToDebugLogFile("After adding parameters", sFuncName);
}
oCmd.Connection = oCon;
oCmd.CommandTimeout = 120;
System.Data.Odbc.OdbcDataAdapter da = new System.Data.Odbc.OdbcDataAdapter(oCmd);
da.Fill(oDs);
oCon.Close();
if (p_iDebugMode == DEBUG_ON)
{
oLog.WriteToDebugLogFile("Completed with SUCCESS", sFuncName);
}
}
catch (Exception ex)
{
if (p_iDebugMode == DEBUG_ON)
{
oLog.WriteToDebugLogFile("Completed with ERROR", sFuncName);
}
oCon.Dispose();
throw new Exception(ex.Message);
}
return(oDs.Tables[0]);
}
public void LoadDataFileExcel(string FileName, string SheetName)
{
Table = new DataTable("datastore");
string strConnString = null;
strConnString = "DRIVER=Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb);ReadOnly=1;FIL=excel 12.0;DBQ=" + FileName + ";";
string strSQL = null;
strSQL = "SELECT * FROM [" + SheetName + "$]";
System.Data.Odbc.OdbcConnection cnn = new System.Data.Odbc.OdbcConnection(strConnString);
cnn.Open();
try
{
OdbcCommand cmd = new OdbcCommand();
cmd.Connection = cnn;
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = strSQL;
OdbcDataReader reader = cmd.ExecuteReader();
object[] fields = new object[reader.FieldCount];
Table.Load(reader);
cnn.Close();
}
catch (Exception ex)
{
if (cnn.State == ConnectionState.Open)
{
cnn.Close();
}
throw ex;
}
}
public static IEnumerable <String> SqlReadDataValue(string vOdbcName, string queryString = "")
{
var vCurrODBC = In2SqlSvcODBC.vODBCList.Find(item => item.OdbcName == vOdbcName);
using
(OdbcConnection conn = new System.Data.Odbc.OdbcConnection())
{
using (OdbcCommand cmnd = new OdbcCommand(queryString, conn))
{
try
{
vCurrODBC.DSNStr = "DSN=" + vOdbcName;
if (vCurrODBC.Login != null)
{
vCurrODBC.DSNStr = vCurrODBC.DSNStr + ";Uid=" + vCurrODBC.Login + ";Pwd=" + vCurrODBC.Password + ";";
}
conn.ConnectionString = vCurrODBC.DSNStr;
conn.ConnectionTimeout = 5;
conn.Open();
}
catch (Exception e)
{
In2SqlSvcTool.ExpHandler(e, "In2SqlSvcODBC.ReadData", queryString);
conn.Close();
conn.Dispose();
yield break;
}
OdbcDataReader rd = cmnd.ExecuteReader();
while (rd.Read())
{
yield return(rd["value"].ToString());//.Split(',').ToList(); ;
}
conn.Close();
conn.Dispose();
}
}
}
/// <summary>
/// disconnect from database
/// </summary>
public void DisconnectFromDatabase()
{
try
{
if (dbConnection != null && dbConnection.State.ToString() == "Open")
{
dbConnection.Close();
}
}
catch (Exception ex)
{
throw new Exception("Database Disconnect Errors: " + ex.Message);
}
}
/// <summary>
/// Obtiene la información complementaria de un empleado desde la lectura del head count
/// </summary>
/// <returns></returns>
public List <ComplementoEmpleado> ObtenerInformacionComplementaria()
{
List <ComplementoEmpleado> InformacionComplementaria = new List <ComplementoEmpleado>();
try
{
string QueryString = @"SELECT id, RazonSocial, Nomina, CentroCostos FROM headcount";
System.Data.Odbc.OdbcCommand command = new System.Data.Odbc.OdbcCommand(QueryString);
using (System.Data.Odbc.OdbcConnection connection = new System.Data.Odbc.OdbcConnection(ConnectionString))
{
command.Connection = connection;
connection.Open();
using (OdbcDataReader reader = command.ExecuteReader())
{
ComplementoEmpleado Employer;
string [] CentroCostos;
while (reader.Read())
{
Employer = new ComplementoEmpleado();
Employer.EmpleadoId = reader.GetInt32(0);
Employer.RazonSocial = reader.GetString(1);
Employer.Nomina = reader.GetString(2);
CentroCostos = reader.GetString(3).Split(' ');
Employer.ClaveCentroCostos = CentroCostos.Length > 0 ? CentroCostos[0] : "";
if (Employer.ClaveCentroCostos != "")
{
for (int i = 1; i < CentroCostos.Length; i++)
{
Employer.DescripcionCentroCostos += CentroCostos[i] + " ";
}
}
InformacionComplementaria.Add(Employer);
}
reader.Close();
}
connection.Close();
}
}
catch (Exception exc)
{
Log.EscribeLog("Error: EslabonDA.ObtenerInformacionComplementaria - " + exc.Message);
}
return(InformacionComplementaria);
}
public static void WriteToExcel(string filePath, string sheetName, string value, string columnName)
{
try
{
string conStr = @"Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};" + @"DBQ=" + filePath + ";ReadOnly = 0;";
// String sConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source= " + filePath + ";Extended Properties='Excel 12.0 XML;HDR=YES'";
OdbcConnection objConn = new System.Data.Odbc.OdbcConnection(conStr);
objConn.Open();
// OdbcCommand objCmdSelect = new OdbcCommand($"UPDATE [{sheetName}$] SET [" + columnName + "]='" + value + "'", objConn);
// objCmdSelect.ExecuteNonQuery();
objConn.Close();
}
catch (Exception e)
{
Debug.WriteLine("System did not enter the file number in excel" + " " + e.Message);
}
}
public static DataTable ReadDbf(string filePath, string tableName)
{
string fileName = Path.GetFileName(filePath);
filePath = Path.GetDirectoryName(filePath);
OdbcConnection conn = null;
string connectStr = "Driver={Microsoft dBASE Driver (*.dbf)}; Dbq=" + filePath;
conn = new System.Data.Odbc.OdbcConnection(connectStr);
string sql = "select * from " + fileName;
System.Data.Odbc.OdbcDataAdapter da = new System.Data.Odbc.OdbcDataAdapter(sql, conn);
conn.Open();
DataTable dt = new DataTable();
da.Fill(dt);
conn.Close();
return(dt);
}
public static void WriteDataToTemplate(string filePath, string fileName, DataTable table)
{
//CopyTemplateToWorkingDir(filePath, fileName);
try
{
OdbcConnection obdcconn = new System.Data.Odbc.OdbcConnection(GetConnectionString(dir + fileName));
OdbcCommand oCmd = null;
oCmd = obdcconn.CreateCommand();
obdcconn.Open();
oCmd.CommandText = "INSERT INTO " + fileName + " VALUES ('0000',' 0000','0000','0000','0','0000',0,0,0,'')";
oCmd.ExecuteNonQuery();
oCmd = obdcconn.CreateCommand();
oCmd.CommandText = "DELETE FROM " + fileName + " WHERE BOARD <> '0000';";
var affected = oCmd.ExecuteNonQuery();
try
{
var count = 0;
foreach (DataRow row in table.Rows)
{
count++;
oCmd = obdcconn.CreateCommand();
string insertSql = "INSERT INTO " + fileName + " VALUES(";
string values = "";
for (int i = 0; i < table.Columns.Count; i++)
{
string value = ReplaceEscape(row[i].ToString());
switch (i)
{
case 1:
value = " " + value;
break;
case 2:
value = value.PadRight(6, ' ');
break;
case 6:
value = CalculateDay(value);
values = values + value + ",";
values = values + "0,";
values = values + "0,";
break;
case 7:
values = "'" + value + "'," + values;
break;
default:
break;
}
if (i != 6 && i != 0 && i != 7)
{
values = values + "'" + value + "',";
}
}
values = values.Substring(0, values.Length - 1) + ")";
insertSql = insertSql + values;
oCmd.CommandText = insertSql;
oCmd.ExecuteNonQuery();
oCmd.Dispose();
}
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message);
}
oCmd = obdcconn.CreateCommand();
oCmd.CommandText = "DELETE FROM " + fileName + " WHERE BOARD='0000'";
oCmd.ExecuteNonQuery();
obdcconn.Close();
CopyTemplateToWorkingDir(System.Configuration.ConfigurationSettings.AppSettings["OutputPath"], fileName);
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public Int16 ExecuteNonQuery_DR(string sQuery, OdbcParameter[] param)
{
string sFuncName = "ExecuteNonQuery_DR";
Int16 iResult = 0;
if (p_iDebugMode == DEBUG_ON)
{
oLog.WriteToDebugLogFile("Starting Function", sFuncName);
}
string sConstr = ConfigurationManager.ConnectionStrings["DBConnection"].ToString();
System.Data.Odbc.OdbcConnection oCon = new System.Data.Odbc.OdbcConnection(sConstr);
System.Data.Odbc.OdbcCommand oCmd = new System.Data.Odbc.OdbcCommand();
DbDataReader oDr;
try
{
oCon.Open();
oCmd.CommandType = CommandType.Text;
oCmd.CommandText = sQuery;
if (p_iDebugMode == DEBUG_ON)
{
oLog.WriteToDebugLogFile("SQL Query : " + sQuery, sFuncName);
}
if (p_iDebugMode == DEBUG_ON)
{
oLog.WriteToDebugLogFile("Before adding Parameters", sFuncName);
}
foreach (var item in param)
{
oCmd.Parameters.Add(item);
}
if (p_iDebugMode == DEBUG_ON)
{
oLog.WriteToDebugLogFile("After adding parameters", sFuncName);
}
oCmd.Connection = oCon;
oCmd.CommandTimeout = 120;
oDr = oCmd.ExecuteReader();
if (oDr.Read() && oDr.GetValue(0) != DBNull.Value)
{
iResult = Convert.ToInt16(oDr[0]);
}
else
{
iResult = 0;
}
oCon.Close();
if (p_iDebugMode == DEBUG_ON)
{
oLog.WriteToDebugLogFile("Completed with SUCCESS", sFuncName);
}
}
catch (Exception ex)
{
if (p_iDebugMode == DEBUG_ON)
{
oLog.WriteToDebugLogFile("Completed with ERROR", sFuncName);
}
oCon.Dispose();
throw new Exception(ex.Message);
}
return(iResult);
}
private void LoadView()
{
System.Data.Odbc.OdbcConnection OdbcConnection = new System.Data.Odbc.OdbcConnection(ConfigurationManager.ConnectionStrings["TurningpointSystem"].ConnectionString);
try
{
DataTable dt = new DataTable();
dg = item_list;
StringBuilder sCSVBuilder = new StringBuilder();
System.Data.Odbc.OdbcCommand oCmd = new System.Data.Odbc.OdbcCommand();
OdbcConnection.Open();
oCmd.Connection = OdbcConnection;
string selectCmd;
if (ConfigurationManager.AppSettings["RX_V6"] == "yes")
{
selectCmd = String.Format(
" SELECT distinct A.ORDTIT, 0 as ORUSIT, A.OTCACT, '' AS EXTPRICE, '' AS RETAIL, A.ORDTSQ, A.ORDTQT, A.ORDTSP, '' as ORPKCT, B.*, 0 as OCLNX$" +
" FROM FPORDDTL A LEFT JOIN FPITMMAS B ON A.ORDTIT = B.ITMNUM" +
//" LEFT JOIN FPORDCRD C ON C.OCDOC# = A.OTINV# AND C.OCDLNE = A.ORDTLN" +
" WHERE A.OTINV# = {0} ", invoiceNo);
}
else if (ConfigurationManager.AppSettings["RX"] == "yes")
{
selectCmd = String.Format(
" SELECT distinct A.ORDTIT, 0 as ORUSIT, A.OTCACT, '' AS EXTPRICE, '' AS RETAIL, A.ORDTSQ, A.ORDTQT, A.ORDTSP, '' as ORPKCT, B.*, C.OCLNX$" +
" FROM FPORDDTL A LEFT JOIN FPITMMAS B ON A.ORDTIT = B.ITMNUM" +
" LEFT JOIN FPORDCRD C ON C.OCDOC# = A.OTINV# AND C.OCDLNE = A.ORDTLN" +
" WHERE A.OTINV# = {0} ", invoiceNo);
}
else
{
selectCmd = String.Format(
" SELECT distinct A.ORDTIT, A.ORUSIT, A.OTCACT, '' AS EXTPRICE, '' AS RETAIL, A.ORDTSQ, A.ORDTQT, A.ORDTSP, A.ORPKCT, B.*, C.OCLNX$" +
" FROM FPORDDTL A LEFT JOIN FPITMMAS B ON A.ORDTIT = B.ITMNUM" +
" LEFT JOIN FPORDCRD C ON C.OCDOC# = A.OTINV# AND C.OCDLNE = A.ORDTLN" +
" WHERE A.OTINV# = {0} ", invoiceNo);
}
oCmd.CommandText = selectCmd;
System.Data.Odbc.OdbcDataAdapter da = new System.Data.Odbc.OdbcDataAdapter(oCmd);
da.Fill(dt);
sCSVBuilder.Append("Invoice No, Invoice Date, Amount\n");
sCSVBuilder.Append(invoiceNo + "," + invoiceDate + "," + amount.Replace("$", "") + "\n\n");
for (int k = 0; k <= dg.Columns.Count - 1; k++)
{
if (ConfigurationManager.AppSettings["RX"] == "yes")
{
if ((k != 2) && (k != 9))
{
sCSVBuilder.Append(dg.Columns[k].HeaderText + ",");
}
}
else
{
if (k != 3)
{
sCSVBuilder.Append(dg.Columns[k].HeaderText + ",");
}
}
}
sCSVBuilder.Append("\n");
for (int i = 0; i <= dt.Rows.Count - 1; ++i)
{
DataRow dr = dt.Rows[i];
if (dr["ITINVT"].ToString().Trim() == "Z")
{
dr["ORDTIT"] = dr["ORUSIT"];
}
double extPrice = 0;
try
{
extPrice = Convert.ToDouble(dr["ORDTSQ"]) * Convert.ToDouble(dr["OTCACT"]);
}
catch
{
}
dr["EXTPRICE"] = String.Format("{0:f}", extPrice);
double retailPrice = 0;
try
{
retailPrice = Convert.ToDouble(dr["ORDTSP"]) / Convert.ToDouble(dr["ORPKCT"]);
}
catch
{
}
dr["RETAIL"] = String.Format("{0:f}", retailPrice);
sCSVBuilder.Append(dr["ORDTIT"].ToString() + ",");
sCSVBuilder.Append("\"" + dr["ITDESC"].ToString().Trim() + "\"" + ",");
if (ConfigurationManager.AppSettings["RX"] == "yes")
{
if (dr["ITMNDC"].ToString().Trim() == "")
{
sCSVBuilder.Append("\"\"" + ",");
}
else
{
sCSVBuilder.Append("\"" + dr["ITMNDC"].ToString().Trim() + " *\"" + ",");
}
}
else
{
if (dr["ITMUPC"].ToString().Trim() == "")
{
sCSVBuilder.Append("\"\"" + ",");
}
else
{
sCSVBuilder.Append("\"" + dr["ITMUPC"].ToString().Trim() + " *\"" + ",");
}
}
sCSVBuilder.Append(dr["OTCACT"].ToString() + ",");
sCSVBuilder.Append(dr["EXTPRICE"].ToString() + ",");
sCSVBuilder.Append(dr["ITSIZE"].ToString() + ",");
sCSVBuilder.Append(dr["ORDTQT"].ToString() + ",");
sCSVBuilder.Append(dr["ORDTSQ"].ToString() + ",");
if (ConfigurationManager.AppSettings["RX"] != "yes")
{
sCSVBuilder.Append(dr["RETAIL"].ToString() + ",");
}
sCSVBuilder.Append("\n");
}
sCSV = sCSVBuilder.ToString();
DataView dv = new DataView(dt);
dg.DataSource = dv;
dg.DataBind();
}
catch (Exception ex) {
}
finally {
OdbcConnection.Close();
}
}
public static void dumpOdbctoCsv(string vOdbcName, string vSqlCommand, string vCsvFile)
{
try
{
int i = 0;
string DsnConn = In2SqlSvcODBC.getODBCProperties(vOdbcName, "DSNStr");
if (DsnConn == null | DsnConn == "")
{
MessageBox.Show("Please make the connection by expand list on the left pane ", "sql run event",
MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
using (OdbcConnection conn = new System.Data.Odbc.OdbcConnection())
{
using (OdbcCommand cmnd = new OdbcCommand(vSqlCommand, conn))
{ conn.ConnectionString = DsnConn;
conn.ConnectionTimeout = 5;
conn.Open();
In2SqlSvcTool.addSqlLog(vOdbcName, vSqlCommand);
OdbcDataReader rd = cmnd.ExecuteReader();
object[] output = new object[rd.FieldCount];
using (var textWriter = new StreamWriter(@vCsvFile))
{
var writer = new CsvWriter(textWriter, CultureInfo.InvariantCulture);
writer.Configuration.Delimiter = ",";
writer.Configuration.ShouldQuote = (field, context) => true;
for (int j = 0; j < rd.FieldCount; j++)
{
output[j] = rd.GetName(j);
writer.WriteField(rd.GetName(j));
}
writer.NextRecord();
while (rd.Read())
{
rd.GetValues(output);
writer.WriteField(output);
writer.NextRecord();
i++;
}
conn.Close();
conn.Dispose();
} }
}
MessageBox.Show("Export completed. \n\r File name is " + vCsvFile + " \n\r Row count:" + i, "csv export",
MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception e)
{
if (e.HResult != -2147024809)
{
In2SqlSvcTool.ExpHandler(e, "dumpOdbctoCsv");
}
}
}
/// <summary>
/// Permite obtener la lista de permisos, incapacidades y vacaciones solicitadas durante el periodo que se ingresa como parámetro
/// </summary>
/// <param name="FechaInicio"></param>
/// <param name="FechaFin"></param>
/// <returns></returns>
public List <DiasJustificados> ObtenerDiasJustificados(DateTime FechaInicio, DateTime FechaFin)
{
List <DiasJustificados> Lista = new List <DiasJustificados>();
FechaInicio = FechaInicio.AddMonths(-2); //Va y verifica si existen días de meses pasados
FechaFin = FechaFin.AddDays(1);
try
{
string QueryString = @" DECLARE @FECHA_INICIO DATETIME
DECLARE @FECHA_FIN DATETIME
SET @FECHA_INICIO = '" + FechaInicio.ToString("yyyyMMdd") + @"'
SET @FECHA_FIN = '" + FechaFin.ToString("yyyyMMdd") + @"'
SELECT A.ID, A.FECHA_DESDE, DATEDIFF(day, A.FECHA_DESDE, A.FECHA_HASTA) + 1 AS C_DIAS, A.CONCEPTO FROM (
SELECT r.id AS ID,
CONVERT(DATETIME, dbo.ScalarSplit(r.referencia, ' ', 1)) AS FECHA_DESDE,
CONVERT(DATETIME, dbo.ScalarSplit(r.referencia, ' ', 2)) AS FECHA_HASTA,
d.descripcion AS CONCEPTO
FROM recibos r INNER JOIN dips d on d.dip = r.dip
WHERE r.dip IN ('4090', '7470', '4070')
AND r.fecha_registro >= @FECHA_INICIO
UNION
SELECT c.id AS ID,
CONVERT(DATETIME, dbo.ScalarSplit(c.referencia, ' ', 1)) AS FECHA_DESDE,
CONVERT(DATETIME, dbo.ScalarSplit(c.referencia, ' ', 2)) AS FECHA_HASTA,
d.descripcion AS CONCEPTO
FROM captura c INNER JOIN dips d on d.dip = c.dip
WHERE c.dip IN ('4090', '7470', '4070')
AND c.fecha_registro >= @FECHA_INICIO ) AS A
WHERE (A.FECHA_DESDE BETWEEN @FECHA_INICIO AND @FECHA_FIN)
UNION
SELECT id AS ID,
fecha_incapacidad_desde AS FECHA_DESDE,
dias_incapacidad AS C_DIAS,
'INCAPACIDAD' AS CONCEPTO
FROM incapacidades
WHERE (fecha_incapacidad_desde BETWEEN @FECHA_INICIO AND @FECHA_FIN)
UNION
SELECT id AS ID,
fecha_inicio AS FECHA_DESDE,
dias_descanso AS C_DIAS,
'VACACIONES' AS CONCEPTO
FROM vacaciones_empleado
WHERE (fecha_inicio BETWEEN @FECHA_INICIO AND @FECHA_FIN)";
System.Data.Odbc.OdbcCommand command = new System.Data.Odbc.OdbcCommand(QueryString);
using (System.Data.Odbc.OdbcConnection connection = new System.Data.Odbc.OdbcConnection(ConnectionString))
{
command.Connection = connection;
connection.Open();
using (OdbcDataReader reader = command.ExecuteReader())
{
DiasJustificados Modelo;
while (reader.Read())
{
Modelo = new DiasJustificados();
Modelo.EmpleadoId = reader.GetInt32(0);
Modelo.FechaDesde = reader.GetDateTime(1);
Modelo.NumeroDias = reader.GetInt32(2);
Modelo.Concepto = reader.GetString(3);
Lista.Add(Modelo);
}
reader.Close();
reader.Dispose();
}
connection.Close();
connection.Dispose();
}
}
catch (Exception ex)
{
Log.EscribeLog("Error: ElsabonDA.ObtenerDiasJustificados - " + ex.Message);
}
return(Lista);
}
private void LoadView()
{
System.Data.Odbc.OdbcConnection OdbcConnection = new System.Data.Odbc.OdbcConnection(ConfigurationManager.ConnectionStrings["TurningpointSystem"].ConnectionString);
try
{
DataTable dt = new DataTable();
dg = item_list;
System.Data.Odbc.OdbcCommand oCmd = new System.Data.Odbc.OdbcCommand();
OdbcConnection.Open();
oCmd.Connection = OdbcConnection;
string selectCmd;
if (ConfigurationManager.AppSettings["RX"] == "yes")
{
selectCmd = String.Format(
" SELECT distinct A.CRITEM, CRSEQ, '' as CIGITM, A.CRCRTT, A.CRQTSR, B.ITDESC, B.ITSIZE, B.ITINVT, B.ITMUPC" +
" FROM FPCRDDTL A LEFT JOIN FPITMMAS B ON A.CRITEM=B.ITMNUM" +
// " LEFT OUTER JOIN FPCIGTAX Z ON Z.CIGSHP = A.CRITEM" +
" WHERE A.CRARNO = {0} ", creditNo);
}
else
{
selectCmd = String.Format(
" SELECT distinct A.CRITEM, CRSEQ, Z.CIGITM, A.CRCRTT, A.CRQTSR, B.ITDESC, B.ITSIZE, B.ITINVT, B.ITMUPC" +
" FROM FPCRDDTL A LEFT JOIN FPITMMAS B ON A.CRITEM=B.ITMNUM" +
" LEFT OUTER JOIN FPCIGTAX Z ON Z.CIGSHP = A.CRITEM" +
" WHERE A.CRARNO = {0} ", creditNo);
}
oCmd.CommandText = selectCmd;
System.Data.Odbc.OdbcDataAdapter da = new System.Data.Odbc.OdbcDataAdapter(oCmd);
da.Fill(dt);
sCSV = "";
for (int k = 0; k <= dg.Columns.Count - 1; k++)
{
sCSV += dg.Columns[k].HeaderText + ",";
}
sCSV += "\n";
for (int i = 0; i <= dt.Rows.Count - 1; ++i)
{
DataRow dr = dt.Rows[i];
if (dr["CIGITM"].ToString().Trim() != "")
{
dr["CRITEM"] = dr["CIGITM"];
}
sCSV += dr["CRITEM"].ToString() + ",";
sCSV += "\"" + dr["ITDESC"].ToString().Trim() + "\"" + ",";
sCSV += "\"" + dr["ITMUPC"].ToString().Trim() + "\"" + ",";
sCSV += dr["CRCRTT"].ToString() + ",";
sCSV += dr["ITSIZE"].ToString() + ",";
sCSV += dr["CRQTSR"].ToString() + ",";
sCSV += "\n";
}
DataView dv = new DataView(dt);
dg.DataSource = dv;
dg.DataBind();
}
catch (Exception ex)
{
}
finally
{
OdbcConnection.Close();
}
}
public bool Open(string connectionString, bool testIt)
{
int pos = connectionString.IndexOf(":");
if (pos == -1)
{
return(false);
}
try
{
Close();
string type = connectionString.Substring(0, pos);
string connStr = connectionString.Substring(pos + 1, connectionString.Length - pos - 1);
switch (type.ToLower())
{
case "oledb":
oledbConnection = new OleDbConnection(connStr);
if (testIt)
{
oledbConnection.Open();
oledbConnection.Close();
}
break;
case "odbc":
odbcConnection = new OdbcConnection(connStr);
if (testIt)
{
odbcConnection.Open();
odbcConnection.Close();
}
break;
case "oracleclient":
case "oracle":
oracleConnection = new OracleConnection(connStr);
if (testIt)
{
oracleConnection.Open();
oracleConnection.Close();
}
break;
case "sqlclient":
case "sql":
sqlConnection = new SqlConnection(connStr);
if (testIt)
{
sqlConnection.Open();
sqlConnection.Close();
}
break;
case "npgsql":
try
{
dbFactory = DataProvider.PostgresProvider;
dbConnection = dbFactory.CreateConnection();
dbConnection.ConnectionString = connStr;
if (testIt)
{
dbConnection.Open();
dbConnection.Close();
}
break;
}
catch (Exception ex)
{
_errMsg = ex.Message;
return(false);
}
break;
default:
_errMsg = "Test Connection is not supported for '" + type + "'";
return(false);
}
return(true);
}
catch (Exception ex)
{
_errMsg = ex.Message;
return(false);
}
}
private void LoadItems(string sCustNo, string sDateStart, string sDateEnd, string sBrandFamily, double dTargetPct)
{
System.Data.Odbc.OdbcConnection odbcConnection = new System.Data.Odbc.OdbcConnection(ConfigurationManager.ConnectionStrings["TurningpointSystem"].ConnectionString);
OdbcDataReader odbcDataReader = null;
string selectCmd =
" SELECT I.ITVSUF, G.GREFDT, 0 AS TOTAL, '' AS PCT, 0 AS SALES, 0 AS CREDITS, " +
" (SELECT SUM(S.WKQTY) FROM FPSLSBRN S" +
" LEFT JOIN FPITMMAS I1 ON S.WKITEM = I1.ITMNUM" +
" WHERE S.WKSHIP = " + sCustNo +
" AND S.WKTYPE = 'S' " +
" AND I1.ITVSUF = I.ITVSUF " +
" AND S.WKDATE BETWEEN " + sDateStart + " AND " + sDateEnd +
" AND I1.ITINVT IN (SELECT GREFKY FROM FPSYGREF WHERE GREFCD = 'WP')) AS SALES_COUNT, " +
" (SELECT SUM(C.WKQTY) FROM FPSLSBRN C" +
" LEFT JOIN FPITMMAS I2 ON C.WKITEM = I2.ITMNUM" +
" WHERE C.WKSHIP = " + sCustNo +
" AND C.WKTYPE = 'C' " +
" AND I2.ITVSUF = I.ITVSUF " +
" AND C.WKDATE BETWEEN " + sDateStart + " AND " + sDateEnd +
" AND I2.ITINVT IN (SELECT GREFKY FROM FPSYGREF WHERE GREFCD = 'WP')) AS CREDITS_COUNT" +
" FROM FPSLSBRN A " +
" LEFT JOIN FPITMMAS I ON A.WKITEM = I.ITMNUM" +
" LEFT JOIN FPSYGREF G ON G.GREFCD = 'BF' AND I.ITVSUF = G.GREFKY" +
" WHERE WKSHIP = " + sCustNo +
" AND WKDATE BETWEEN " + sDateStart + " AND " + sDateEnd +
" AND I.ITINVT IN (SELECT GREFKY FROM FPSYGREF WHERE GREFCD = 'WP')" +
" GROUP BY ITVSUF, GREFDT";
try
{
DataTable dt = new DataTable();
odbcConnection.Open();
OdbcCommand odbcCommand = new OdbcCommand(selectCmd, odbcConnection);
System.Data.Odbc.OdbcDataAdapter da = new System.Data.Odbc.OdbcDataAdapter(odbcCommand);
da.Fill(dt);
double dTotal = 0;
foreach (DataRow dr in dt.Rows)
{
try
{
dr["SALES"] = Convert.ToInt32(dr["SALES_COUNT"]);
}
catch
{
dr["SALES"] = 0;
}
try
{
dr["CREDITS"] = Convert.ToInt32(dr["CREDITS_COUNT"]);
}
catch
{
dr["CREDITS"] = 0;
}
dr["TOTAL"] = Convert.ToInt32(dr["SALES"]) - Convert.ToInt32(dr["CREDITS"]);
dTotal += Convert.ToInt32(dr["TOTAL"]);
}
sales_total.Text = dTotal.ToString("#0");
foreach (DataRow dr in dt.Rows)
{
double dPct = 0.0;
try
{
dPct = (Convert.ToInt32(dr["TOTAL"]) / dTotal) * 100;
}
catch
{
dPct = 0;
}
dr["PCT"] = dPct.ToString("#0.00");
if (Convert.ToInt32(dr["ITVSUF"]) == Convert.ToInt32(brand_family_dropdown.SelectedValue))
{
sales_current_pct.Text = dr["PCT"].ToString();
sales_brand.Text = dr["TOTAL"].ToString();
double dAddUnits = 0;
try
{
dAddUnits = (dTargetPct / 100 * dTotal) - Convert.ToInt32(dr["TOTAL"]);
sales_additional.Text = dAddUnits.ToString("#0");
}
catch
{
}
}
}
DataView dv = new DataView(dt);
item_list.DataSource = dv;
item_list.DataBind();
}
catch (Exception ex)
{
}
finally
{
if (odbcDataReader != null)
{
odbcDataReader.Close();
}
odbcConnection.Close();
}
}
