public bool driver_parameterizations()
{
//string dbPath = "C:\\Users\\Brend\\Desktop\\Fall 2020\\CyberSecurity\\Project\\ExampleDB.accdb";
string strSql = "SELECT * FROM Users WHERE Users.[UserName] = ? AND Users.[Password] = ?";
thisQuery = strSql;
OleDbConnection conn = new OleDbConnection(strDsn);
conn.Open();
using (OleDbCommand command = new OleDbCommand(strSql, conn))
{
var userNameParam = new OleDbParameter("@UserName", enteredUserName);
//MessageBox.Show("User Name Parameters:\n" + userNameParam.Value);
var passwordParam = new OleDbParameter("@Password", enteredPassword);
command.Parameters.Add(userNameParam);
command.Parameters.Add(passwordParam);
thisQuery = command.CommandText;
if (displayCurrentQuery)
{
update_query_label();
}
try
{
OleDbDataReader reader = command.ExecuteReader();
if (reader != null && reader.Read())
{
reader.Close();
reader.Dispose();
//record found
return(true);
}
else
{
if (reader != null)
{
reader.Close();
reader.Dispose();
}
MessageBox.Show("Invalid Entry. Please try again.");
return(false);
}
}
catch (System.Data.OleDb.OleDbException ex)
{
MessageBox.Show("ERROR:\n" + ex.Message);
conn.Close();
return(false);
}
}
}
public Dukey.Model.Dictionary GetModel(int id)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select top 1 id,pid,keytext,keyvalue,px,addtime,updatetime,remark from Sys_Dictionary ");
strSql.Append(" where id=@id ");
OleDbParameter[] parameters =
{
new OleDbParameter("@id", OleDbType.Integer, 4)
};
parameters[0].Value = id;
Dukey.Model.Dictionary model = new Dukey.Model.Dictionary();
OleDbDataReader dr = DbHelper.ExecuteReader(strSql.ToString(), parameters);
if (dr.Read())
{
if (dr["id"].ToString() != "")
{
model.id = int.Parse(dr["id"].ToString());
}
model.keytext = dr["keytext"].ToString();
model.keyvalue = dr["keyvalue"].ToString();
if (dr["px"].ToString() != "")
{
model.px = int.Parse(dr["px"].ToString());
}
if (dr["pid"].ToString() != "")
{
model.pid = int.Parse(dr["pid"].ToString());
}
if (dr["addtime"].ToString() != "")
{
model.addtime = DateTime.Parse(dr["addtime"].ToString());
}
if (dr["updatetime"].ToString() != "")
{
model.updatetime = DateTime.Parse(dr["updatetime"].ToString());
}
dr.Dispose();
return(model);
}
else
{
dr.Dispose();
return(null);
}
}
public bool no_validation()
{
string strSql = "SELECT * FROM Users WHERE Users.[UserName] = '" + enteredUserName + "' AND Users.[Password] = '" + enteredPassword + "'";
thisQuery = strSql;
if (displayCurrentQuery)
{
update_query_label();
}
OleDbConnection conn = new OleDbConnection(strDsn);
conn.Open();
using (OleDbCommand command = new OleDbCommand(strSql, conn))
{
//var userNameParam = new OleDbParameter("@UserName", enteredUserName);
//MessageBox.Show("User Name Parameters:\n" + userNameParam.Value);
//var passwordParam = new OleDbParameter("@Password", enteredPassword);
//command.Parameters.Add(userNameParam);
//command.Parameters.Add(passwordParam);
try
{
OleDbDataReader reader = command.ExecuteReader();
if (reader != null && reader.Read())
{
reader.Close();
reader.Dispose();
return(true);
}
else
{
if (reader != null)
{
reader.Close();
reader.Dispose();
//MessageBox.Show("ERROR: Failed login for user; \n" + reader.GetString(0));
}
MessageBox.Show("ERROR: Failed login for user; \n" + enteredUserName);
return(false);
}
}
catch (System.Data.OleDb.OleDbException ex)
{
MessageBox.Show("ERROR:\n" + ex.Message);
conn.Close();
return(false);
}
}
}
public void carregaCupons()
{
try
{
rs = objBD.ExecutaSQL("select top 100 CUP_NUMERO_SORTE, CAD_ID, CUP_VALOR, convert(varchar(10),CUP_DH_CADASTRO,103) as DH_CADASTRO from cupom order by cup_id desc");
if (rs == null)
{
throw new Exception();
}
if (rs.HasRows)
{
while (rs.Read())
{
cuponsCadastrados.InnerHtml += "<tr>";
cuponsCadastrados.InnerHtml += "<td>" + rs["CUP_NUMERO_SORTE"].ToString() + "</td>";
cuponsCadastrados.InnerHtml += "<td>" + rs["CAD_ID"].ToString() + "</td>";
cuponsCadastrados.InnerHtml += "<td>" + string.Format(CultureInfo.GetCultureInfo("pt-BR"), "R$ {0:0.0,0}", rs["CUP_VALOR"]) + "</td>";
cuponsCadastrados.InnerHtml += "<td>" + rs["DH_CADASTRO"].ToString() + "</td>";
cuponsCadastrados.InnerHtml += "</tr>";
}
}
rs.Dispose();
rs.Close();
}
catch (Exception)
{
throw;
}
}
public void imagensNaoAprovadasResp()
{
try
{
rsFotosNaoAprovadas = objBD.ExecutaSQL("exec psInstagramPorAtivo 0,12");
if (rsFotosNaoAprovadas == null)
{
throw new Exception();
}
if (rsFotosNaoAprovadas.HasRows)
{
while (rsFotosNaoAprovadas.Read())
{
resposta += "<a href='javascript:void(0)' rel=\"nofollow\" onClick='mudaStatusFoto(1, " + rsFotosNaoAprovadas["INS_ID"] + ")' data-id='" + rsFotosNaoAprovadas["INS_ID"] + "' class='fotosInstagram'>";
resposta += " <div class='picInsta'>";
resposta += " <div><i class='fa fa-check-circle'></i></div>";
resposta += " <img src='" + rsFotosNaoAprovadas["INS_THUMB"] + "'>";
resposta += " </div>";
resposta += "</a>";
}
}
rsFotosNaoAprovadas.Dispose();
rsFotosNaoAprovadas.Close();
}
catch (Exception)
{
throw;
}
Response.Write(resposta);
Response.End();
}
/// <summary>
/// 检查任务状态
/// </summary>
/// <param name="ProcessID">流程ID</param>
/// <param name="EntityID">业务数据ID</param>
/// <returns>0:未开始，1:已结束，2:正在进行,-1:错误或无法确定</returns>
public int CheckTaskStatus(string ProcessID, string EntityID)
{
string sSQL = "SELECT F_STATUS " +
"FROM WF_TASK_LIST where F_PROCESS_ID=" + ProcessID + " and F_ENTITY_ID='" + EntityID.Replace("'", "''") + "'";
if (!InitConnection())
{
return(-1);
}
//////
OleDbCommand cmd = conn.CreateCommand();
cmd.CommandText = sSQL;
OleDbDataReader dr = cmd.ExecuteReader();
int rc = 0;
if (dr.Read())
{
rc = dr.GetInt16(0);
}
dr.Close();
dr.Dispose();
cmd.Dispose();
conn.Close();
conn.Dispose();
return(rc);
}
public Boolean HasAssets(String uuid)
{
int count = 0;
string sql = builSelectSQLStatement(AssetIdentificationBean._TABLE_NAME,
new[] { "count(*) as _count" },
new[]
{
AssetIdentificationBean._UUID
});
OleDbParameter[] parameters = { new OleDbParameter(AssetIdentificationBean._UUID, Guid.Parse(uuid)) };
OleDbDataReader reader = ExecuteSqlQuery(sql, parameters);
if (reader != null)
{
if (reader.Read())
{
count = (int)reader["_count"];
}
reader.Close();
reader.Dispose();
}
return(count > 0);
}
//ADMINISTRACAO
public void loginAdministrador(string email, string senha)
{
//Verificar se o usuário existe (comparando usuário e senha)
rsLogin = objBD.ExecutaSQL("select ADM_ID from administradores where ADM_EMAIL = '" + email + "' AND ADM_SENHA = '" + senha + "' AND ADM_ATIVO = 1");
if (rsLogin == null)
{
throw new Exception();
}
if (rsLogin.HasRows)
{
rsLogin.Read();
//Salvando as Session do usuário
Session["ADM_ID"] = rsLogin["ADM_ID"].ToString();
Response.Redirect("/admin/index.aspx");
Response.End();
Response.End();
}
else
{
Response.Redirect("/admin/login.aspx?msg=erroLogin");
}
rsLogin.Dispose();
rsLogin.Close();
}
/// <summary>
/// 从数据库读流程节点分派关系
/// </summary>
/// <param name="obj"></param>
private void GetActRoutingRule(FlowChart obj)
{
string sCmd = "SELECT F_PRE_ACT_ID,F_CURR_ACT_ID,F_COMPLETION_FLAG,F_NEXT_ACT_ID_LIST,F_PRE_DEPNT_SET,F_PROC_ID " +
"FROM WF_ROUTING_RULE where F_PROC_ID=" + obj.ID;
OleDbCommand cmd = conn.CreateCommand();
cmd.CommandText = sCmd;
OleDbDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
string currID = dr.IsDBNull(0) ? "" : dr.GetValue(0).ToString(); //F_PRE_ACT_ID
string pID = dr.IsDBNull(1) ? "" : dr.GetValue(1).ToString(); //F_CURR_ACT_ID
string pStatus = dr.IsDBNull(2) ? "" : dr.GetString(2); //F_COMPLETION_FLAG
//检索列表
wfActivity currAct = obj.GetActivityByID(currID);
wfActivity pAct = obj.GetActivityByID(pID);
if ((currAct != null) && (pAct != null))
{
currAct.AddChild(pAct, pStatus);
}
}
dr.Close();
dr.Dispose();
cmd.Dispose();
}
public static void SapSmStockImport()
{
string cmdText = " SELECT * FROM ZJIANWEN_KC WHERE FLAG=0";
string str2 = " INSERT INTO Sap_JWSmStock VALUES(@PrjCode,@Tcode,@Scode,\r\n @Snumber,@Sprice,@InputDate,@Flag )";
try
{
OleDbDataReader reader = ExecuteReader(CommandType.Text, cmdText, null);
while (true)
{
if (!reader.Read())
{
break;
}
SqlHelper.ExecuteNonQuery(CommandType.Text, str2, new List <SqlParameter> {
new SqlParameter("@PrjCode", DBHelper.GetString(reader["PROJNO"])), new SqlParameter("@Tcode", DBHelper.GetString(reader["LGORT"])), new SqlParameter("@Scode", DBHelper.GetString(reader["MATNR"])), new SqlParameter("@Snumber", DBHelper.GetString(reader["MENGE"])), new SqlParameter("@Sprice", DBHelper.GetDecimal(reader["MONEY"])), new SqlParameter("@InputDate", DBHelper.GetDateTimeNullable(reader["F_DATE"])), new SqlParameter("@Flag", DBHelper.GetBool(reader["FLAG"]))
}.ToArray());
}
reader.Close();
reader.Dispose();
SapUpdate();
}
catch (Exception exception)
{
Log4netHelper.Error(exception, "SapError", "SapError");
}
}
public void carregaCpfs()
{
try
{
rsCpfs = objBD.ExecutaSQL("exec psCpfsBloqueados");
if (rsCpfs == null)
{
throw new Exception();
}
if (rsCpfs.HasRows)
{
cpfsBloqueados.InnerHtml = "";
while (rsCpfs.Read())
{
cpfsBloqueados.InnerHtml += "<tr id='" + rsCpfs["C_CPF"] + "'>";
cpfsBloqueados.InnerHtml += " <td>" + rsCpfs["C_CPF"] + "</td>";
cpfsBloqueados.InnerHtml += " <td><center><a href='javascript:void(0)' title='Remover bloqueio' onClick='removerCPF(" + rsCpfs["C_CPF"] + ")'><i class='fa fa-trash' aria-hidden='true'></i></a></center></td>";
cpfsBloqueados.InnerHtml += "</tr>";
}
}
rsCpfs.Dispose();
rsCpfs.Close();
}
catch (Exception)
{
throw;
}
}
//班次查询
private void checkFlight()
{
if (!mySystem.Parameter.isSqlOk)
{
OleDbCommand comm = new OleDbCommand();
comm.Connection = Parameter.connOle;
comm.CommandText = "SELECT * FROM 用户 WHERE 用户名= " + "'" + Parameter.userName + "'";
OleDbDataReader reader = comm.ExecuteReader();
while (reader.Read())
{
Parameter.userflight = reader["班次"].ToString();
}
reader.Dispose();
comm.Dispose();
}
else
{
SqlCommand comm = new SqlCommand();
comm.Connection = Parameter.conn;
comm.CommandText = "SELECT * FROM 用户 WHERE 用户名= " + "'" + Parameter.userName + "'";
SqlDataReader reader = comm.ExecuteReader();
while (reader.Read())
{
Parameter.userflight = reader["班次"].ToString();
}
reader.Dispose();
comm.Dispose();
}
}
public static void MiddleTableImport()
{
string cmdText = "SELECT * FROM ZJIANWEN WHERE FLAG=0";
string str2 = " INSERT INTO MiddleTable VALUES(@PrjCode,@ContractCode,@SerialNumber,\r\n @Belnr,@MoneyAmount,@Tag,@Hkont,@Flag,@Buzet,@F_date,@Txt)";
try
{
OleDbDataReader reader = ExecuteReader(CommandType.Text, cmdText, null);
while (reader.Read())
{
string serialNumber = DBHelper.GetString(reader["SERIAL"]);
decimal @decimal = DBHelper.GetDecimal(reader["MONEY"]);
if (IsExist(serialNumber))
{
SqlHelper.ExecuteNonQuery(CommandType.Text, str2, new List <SqlParameter> {
new SqlParameter("@PrjCode", DBHelper.GetString(reader["PROJNO"])), new SqlParameter("@ContractCode", DBHelper.GetString(reader["HETONG"])), new SqlParameter("@SerialNumber", serialNumber), new SqlParameter("@Belnr", DBHelper.GetString(reader["BELNR"])), new SqlParameter("@MoneyAmount", DBHelper.GetDecimal(reader["MONEY"])), new SqlParameter("@Tag", DBHelper.GetString(reader["TAG"])), new SqlParameter("@Hkont", DBHelper.GetString(reader["HKONT"])), new SqlParameter("@Flag", DBHelper.GetBool(reader["FLAG"])), new SqlParameter("@Buzet", DBHelper.GetInt(reader["BUZEI"])), new SqlParameter("@F_date", DBHelper.GetDateTimeNullable(reader["F_DATE"])), new SqlParameter("@Txt", DBHelper.GetString(reader["WENBEN"]))
}.ToArray());
}
else
{
string str4 = string.Format(" UPDATE MiddleTable SET MoneyAmount= '{0}' WHERE SerialNumber='{1}'", @decimal, serialNumber);
SqlHelper.ExecuteNonQuery(CommandType.Text, str4, null);
}
}
reader.Close();
reader.Dispose();
db2Update();
}
catch (Exception exception)
{
Log4netHelper.Error(exception, "db2error", "db2error");
}
}
private void btnTampilkanData_Click(object sender, EventArgs e)
{
lvwMahasiswa.Items.Clear();
// membuat objek Connection, sekaligus buka koneksi ke database
OleDbConnection conn = GetOpenConnection();
// deklarasi variabel sql untuk menampung perintah SELECT
string sql = @"select npm, nama, angkatan from mahasiswa order by nama";
// membuat objek Command untuk mengeksekusi perintah SQL
OleDbCommand cmd = new OleDbCommand(sql, conn);
// membuat objek DataReader untuk menampung hasil perintah SELECT
OleDbDataReader dtr = cmd.ExecuteReader(); // eksekusi perintah SELECT
while (dtr.Read()) // gunakan perulangan utk menampilkan data kelistview
{
var noUrut = lvwMahasiswa.Items.Count + 1;
var item = new ListViewItem(noUrut.ToString());
item.SubItems.Add(dtr["npm"].ToString());
item.SubItems.Add(dtr["nama"].ToString());
item.SubItems.Add(dtr["angkatan"].ToString());
lvwMahasiswa.Items.Add(item);
}
// setelah selesai digunakan,
// segera hapus objek datareader, command dan connection dari memory
dtr.Dispose();
cmd.Dispose();
conn.Dispose();
}
/// <summary>
/// 获取1条信息资料
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public static DownloadsModel GetDownLoadsByID(int downid)
{
string sql = "select * from T_Downloads where ID=?";
OleDbParameter[] parameter = new OleDbParameter[1];
parameter[0] = new OleDbParameter("@ID", OleDbType.Integer);
parameter[0].Value = downid;
OleDbDataReader dr = AccessorDB.ExecuteReader(sql, parameter);
DownloadsModel s = new DownloadsModel();
if (dr.Read())
{
s.F_Size = dr["F_Size"].ToString();
s.F_Desc = dr["F_Desc"].ToString();
s.F_Keywords = dr["F_Keywords"].ToString();
s.F_Content = dr["F_Content"].ToString();
s.F_FileType = Convert.ToInt32(dr["F_FileType"]);
s.F_Date = Convert.ToDateTime(dr["F_Date"]);
s.ID = Convert.ToInt32(dr["ID"]);
s.F_Title = dr["F_Title"].ToString();
s.F_FileImage = dr["F_FileImage"].ToString();
s.F_FileSmallImage = dr["F_FileSmallImage"].ToString();
}
dr.Close();
dr.Dispose();
return(s);
}
/// <summary>
/// 获取所有销售网络数据
/// </summary>
/// <returns></returns>
public static List <DownloadsModel> GetDownloadsListByLang(int lang, int number, int pagesize)
{
if (number == 0)
{
number = 1;
}
string sql = "select top " + pagesize + " * from (select top " + number + " * from T_Downloads where F_Lang=" + lang + " order by ID asc) order by ID desc";
List <DownloadsModel> list = new List <DownloadsModel>();
DownloadsModel s = null;
OleDbDataReader dr = AccessorDB.ExecuteReader(sql);
while (dr.Read())
{
s = new DownloadsModel();
s.F_Size = dr["F_Size"].ToString();
s.F_Desc = dr["F_Desc"].ToString();
s.F_Keywords = dr["F_Keywords"].ToString();
s.F_Content = dr["F_Content"].ToString();
s.F_FileType = Convert.ToInt32(dr["F_FileType"]);
s.F_Date = Convert.ToDateTime(dr["F_Date"]);
s.ID = Convert.ToInt32(dr["ID"]);
s.F_Title = dr["F_Title"].ToString();
s.F_FileImage = dr["F_FileImage"].ToString();
s.F_FileSmallImage = dr["F_FileSmallImage"].ToString();
list.Add(s);
}
dr.Close();
dr.Dispose();
return(list);
}
/// <summary>
/// 获取最大的ID号，主要用于新增数据
/// </summary>
/// <param name="tableType">数据表格类型</param>
/// <param name="IDName">id字段的名称</param>
/// <returns>最新的ID号</returns>
public int GetMaxID(TDBableType tableType, string IDName = "ID")
{
//获取最大的ID号，也就是新增加的ID
OleDbCommand myAccessCommand = null;
OleDbDataReader vOleDbDataReader = null;
try
{
myAccessCommand = new OleDbCommand("select max(" + IDName + ") as maxid from " + DBTableName[(int)tableType], myAccessConn);
vOleDbDataReader = myAccessCommand.ExecuteReader();
vOleDbDataReader.Read();
int maxid = (int)vOleDbDataReader["maxid"];
return(maxid);
}
catch (Exception ex)
{
ErrorString = ex.Message;
return(-1);
}
finally
{
if (vOleDbDataReader != null)
{
vOleDbDataReader.Close();
vOleDbDataReader.Dispose();
}
if (myAccessCommand != null)
{
myAccessCommand.Dispose();
}
}
}
public bool hasDocument(string fileName, int documentType)
{
Int32 count = 0;
String sql = "SELECT count(*) FROM "
+ DocumentBean._TABLE_NAME
+ " WHERE "
+ DocumentBean._DOCUMENT_NAME
+ "=? AND "
+ DocumentBean._DOCUMENT_TYPE_ID
+ "=?";
OleDbParameter[] parameters = { new OleDbParameter(DocumentBean._DOCUMENT_NAME, fileName),
new OleDbParameter(DocumentBean._DOCUMENT_TYPE_ID, documentType) };
OleDbDataReader reader = ExecuteSqlQuery(sql, parameters);
if (reader != null)
{
if (reader.Read())
{
count = reader.GetInt32(0);
}
reader.Close();
reader.Dispose();
}
return(count > 0);
}
protected override int GetCountCore()
{
int count = 0;
OleDbCommand command = null;
OleDbDataReader dataReader = null;
string sqlStatement = "SELECT COUNT(*) FROM " + tableName;
try
{
command = new OleDbCommand(sqlStatement, connection);
ExecutingSqlStatemenOleDbFeatureSourceEventArgs e = new ExecutingSqlStatemenOleDbFeatureSourceEventArgs(command.CommandText, ExecutingSqlStatementType.GetCount);
OnExecutingSqlStatement(e);
dataReader = command.ExecuteReader();
if (dataReader.Read())
{
count = Convert.ToInt32(dataReader[0], CultureInfo.InvariantCulture);
}
}
finally
{
if (dataReader != null)
{
dataReader.Dispose();
}
if (command != null)
{
command.Connection.Close();
command.Dispose();
}
}
return(count);
}
/// <summary>
/// 从数据库读流程节点前依赖关系
/// </summary>
/// <param name="obj">流程信息</param>
private void GetActPreRule(FlowChart obj)
{
string sCmd = "SELECT F_DEPNT_ID,F_DEPNT_ACT_ID,F_DEPNT_ACT_STATUS,F_PROC_ID " +
"FROM WF_PRE_RULE where F_PROC_ID=" + obj.ID;
OleDbCommand cmd = conn.CreateCommand();
cmd.CommandText = sCmd;
OleDbDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
string currID = dr.IsDBNull(0) ? "" : dr.GetValue(0).ToString(); //F_DEPNT_ID
string pID = dr.IsDBNull(1) ? "" : dr.GetValue(1).ToString(); //F_DEPNT_ACT_ID
string pStatus = dr.IsDBNull(2) ? "" : dr.GetString(2); //F_DEPNT_ACT_STATUS
//检索列表
wfActivity currAct = obj.GetActivityByID(currID);
wfActivity pAct = obj.GetActivityByID(pID);
if ((currAct != null) && (pAct != null))
{
currAct.AddParent(pAct, pStatus);
}
}
dr.Close();
dr.Dispose();
cmd.Dispose();
}
protected Dictionary <object, T> CreateMap <T>(String sql, OleDbParameter[] parameters, String keyName)
where T : BASEBean, new()
{
var m = new Dictionary <object, T>();
OleDbDataReader rs = ExecuteSqlQuery(sql, parameters);
if (rs != null)
{
try
{
while (rs.Read())
{
var o = new T();
o.load(rs);
m.Add(rs[keyName], o);
}
}
finally
{
rs.Close();
rs.Dispose();
}
}
return(m);
}
//====================
//public string MatCode(string Mat_In)
////===================================
//{
// BearingDBEntities pBearingDBEntities = new BearingDBEntities();
// string pWaukeshaCode = "";
// var pProject = (from pRec in pBearingDBEntities.tblData_Mat where pRec.fldName == Mat_In select pRec.fldCode_Waukesha).ToList();
// if (pProject.Count > 0)
// {
// pWaukeshaCode = modMain.gDB.CheckDBString(pProject[0]);
// }
// return pWaukeshaCode;
//}
public string MatName(string WCode_In, string MatFileName_In)
//=============================================================
{
string pName = "";
string pstrFIELDS, pstrFROM, pstrSQL, pstrWHERE, pstrORDERBY;
OleDbConnection pConnection = null;
pstrFIELDS = "Name ";
pstrFROM = " FROM [Mat$]";
pstrWHERE = " WHERE Code_Waukesha = '" + WCode_In + "'";
pstrORDERBY = " Order by Name ASC";
pstrSQL = "SELECT " + pstrFIELDS + pstrFROM + pstrWHERE + pstrORDERBY;
OleDbDataReader pobjDR = null;
pobjDR = modMain.gDB.GetDataReader(pstrSQL, MatFileName_In, ref pConnection);
if (pobjDR.Read())
{
pName = modMain.gDB.CheckDBString(pobjDR["Name"]);
}
pobjDR.Dispose();
pConnection.Close();
return(pName);
}
private void subSelectBySQLTructiepQuaDataReader()
{
//Tạo kết nối tới file Access
subCreateConnect();
//Truy vấn dữ liệu
String varSelect = "Select DISTINCT CHIDOAN from DOANVIEN";//Lệnh SQL
OleDbCommand objCommand = new OleDbCommand(varSelect, objConnect);
//Tạo DataReader nhận dữ liệu trả về
OleDbDataReader objReader = objCommand.ExecuteReader();
//Tạo đối tượng DataTable và Load DataReader vào
objDataTable = new DataTable();
objDataTable.Load(objReader);
//Gán dữ liệu vào Datagrid
dgchon.DataSource = objDataTable;
//Hủy các đối tượng
objCommand.Dispose();
objCommand = null;
objReader.Close();
objReader.Dispose();
objReader = null;
objDataTable.Dispose();
objDataTable = null;
subDestroyConnect();
}
protected void Page_Load(object sender, EventArgs e)
{
//打开总计数
string tongji = null;
string strCmd = "select tongji from tong Where userid='" + Request["u"].Trim() + "'";
OleDbDataReader sdr = ExceRead(strCmd);
if (sdr.Read())
{
tongji = sdr["tongji"].ToString();
}
sdr.Close();
sdr.Dispose();
//统计并更新
DateTime dt = DateTime.Now;
DoSql("delete from tongji where dsj < #" + DateTime.Now.AddDays(-3).ToString() + "#");
DoSql("update tong set tongji=tongji+1 where userid='" + Request["u"].Trim() + "'");
DoSql("insert into tongji (ip,sj,userid,dsj)values('" + Request.UserHostAddress + "','" + dt.ToShortDateString().ToString() + "','" + Request["u"].Trim() + "','" + dt + "')");
string j = Judge_Repeat("select Count(id) As id from tongji Where userid='" + Request["u"].Trim() + "' and sj='" + dt.ToShortDateString().ToString() + "'");
string z = Judge_Repeat("select Count(id) As id from tongji Where userid='" + Request["u"].Trim() + "' and sj='" + DateTime.Now.AddDays(-1).ToShortDateString() + "'");
//下面是显示出计数器
System.Drawing.Bitmap bmp = new Bitmap(Bitmap.FromFile(Server.MapPath("2.gif")));//载入图片
System.Drawing.Graphics g = Graphics.FromImage(bmp);
g.DrawString("" + j + " 次", new Font("宋体", 9), new SolidBrush(Color.Red), 65, 9);
g.DrawString("" + z + " 次", new Font("宋体", 9), new SolidBrush(Color.Red), 65, 26);
g.DrawString("" + tongji + " 次", new Font("宋体", 9), new SolidBrush(Color.Red), 67, 42);
//这里选择文本字体颜色
g.Dispose();
//输出GIF,你要其它格式也可以自己改
Response.ContentType = "image/gif";
bmp.Save(Response.OutputStream, System.Drawing.Imaging.ImageFormat.Gif);
}
private void GetSelectNodes(FlowChart obj)
{
string sCmd = "SELECT F_ACT_ID,F_SELECT_ID " +
"FROM WF_SELECT_ROUNTING where F_PROCESS_ID=" + obj.ID;
OleDbCommand cmd = conn.CreateCommand();
cmd.CommandText = sCmd;
OleDbDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
string currID = dr.IsDBNull(0) ? "" : dr.GetValue(0).ToString(); //F_ACT_ID
string sID = dr.IsDBNull(1) ? "" : dr.GetValue(1).ToString(); //F_SELECT_ID
//检索列表
wfActivity currAct = obj.GetActivityByID(currID);
wfActivity sAct = obj.GetActivityByID(sID);
if ((currAct != null) && (sAct != null))
{
currAct.SelectNodes.Add(sID);
}
}
dr.Close();
dr.Dispose();
cmd.Dispose();
}
public string Judge_Repeat(string sql_str)
{ //判断表中返回的数量
string conString = Server.MapPath("App_Data/panpao.accdb");
conString = "Provider=Microsoft.ACE.OLEDB.12.0;Data source=" + conString;
OleDbConnection oldCon = new OleDbConnection(conString);
oldCon.Open();
OleDbCommand Command = new OleDbCommand(sql_str, oldCon);
OleDbDataReader Dr = Command.ExecuteReader(CommandBehavior.CloseConnection);
try
{
Dr.Read();
return(Dr[0].ToString());
}
catch (Exception err)
{
return("");
}
finally
{
Dr.Close();
Dr.Dispose();
oldCon.Close();//关闭数据库
oldCon.Dispose();
}
}
/// <summary>
/// 从数据库读流程节点信息
/// </summary>
/// <param name="obj"></param>
private void GetFlowchartActs(FlowChart obj)
{
string sCmd = "SELECT F_ID,F_PROCESS_ID,F_NAME,F_TIME_ALLOWED,F_RULE_APPLIED," +
"F_EX_PRE_RULE_FUNC,F_EX_POST_RULE_FUNC,F_ACT_TYPE,F_OR_MERGE_FLAG,F_NUM_VOTE_NEEDED," +
"F_AUTO_EXEC,F_ACT_DESC FROM WF_ACTIVITY where F_PROCESS_ID=" + obj.ID;
OleDbCommand cmd = conn.CreateCommand();
cmd.CommandText = sCmd;
OleDbDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
//数据实例化
wfActivity newact = new wfActivity();
newact.ID = dr.GetValue(0).ToString(); //F_ID
newact.Name = dr.IsDBNull(2) ? "" : dr.GetString(2); //F_NAME
newact.TimeAllowed = dr.IsDBNull(3) ? 0: dr.GetInt32(3); //F_TIME_ALLOWED
newact.ActType = dr.IsDBNull(7) ? "" : dr.GetString(7); //F_ACT_TYPE
newact.OrMergeFlag = dr.IsDBNull(8) ? 0 : (int)dr.GetInt16(8); //F_OR_MERGE_FLAG
newact.NumVotesNeeded = dr.IsDBNull(9) ? 0 : (int)dr.GetInt16(9); //F_NUM_VOTE_NEEDED
newact.AutoExecutive = dr.IsDBNull(10) ? false : dr.GetInt16(10) == 1; //F_AUTO_EXEC
newact.Desc = dr.IsDBNull(11) ? "" : dr.GetString(11); //F_ACT_DESC
newact.PreRule = dr.IsDBNull(5) ? "" : dr.GetString(5);
//添加到fchart 活动列表
obj.AddActivity(newact);
//读授权信息
GetActAssgn(newact);
}
dr.Close();
dr.Dispose();
cmd.Dispose();
}
private void btnRetrive_Click(object sender, EventArgs e)
{
try
{
con.Open();
cmd.Connection = con;
cmd.CommandText = "select * from equipment where equipment_id='" + txtEquipmentId.Text + "'";
cmd.ExecuteNonQuery();
OleDbDataReader dr = cmd.ExecuteReader();
dr.Read();
if (dr.HasRows)
{
txtEquipmentName.Text = dr["NOE"].ToString();
txtEquipmentCompany.Text = dr["Company"].ToString();
txtTotalEquipmentQuantity.Text = dr["TotalQ"].ToString();
txtEqupmentPrice.Text = dr["PPU"].ToString();
dobEqupment.Text = dr["DOI"].ToString();
txtTotalPrice.Text = dr["TotalPrice"].ToString();
}
else
{
MessageBox.Show("No equipment found in Database", "Not Found Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
clear();
}
con.Close();
dr.Dispose();
}
catch (Exception ex)
{
MessageBox.Show("Error" + ex);
}
}
public static Oilwell_monthModel Select(string yjh, string yearmonth)
{
string sql = "Select * from OIL_WELL_MONTH Where JH=@JH And NY=@NY";
OleDbParameter[] parameters = new OleDbParameter[2];
parameters[0] = new OleDbParameter("@JH", yjh);
parameters[1] = new OleDbParameter("@NY", yearmonth);
OleDbDataReader reader = DbHelperOleDb.ExecuteReader(sql, parameters);
if (reader.HasRows == false)
{
return(null);
}
reader.Read();
Oilwell_monthModel data = new Oilwell_monthModel();
data.JH = yjh;
data.NY = yearmonth;
data.YCYL = reader["YCYL"].ToString();
data.LY = reader["LY"].ToString();
reader.Close();
reader.Dispose();
return(data);
}
public List <string> Search(string colName, string column)
{
List <string> rows = new List <string>();
int n;
if (int.TryParse(column, out n))
{
_command.CommandText = string.Format("SELECT * FROM {0} WHERE {1} = {2}", _table, colName, column);
}
else
{
_command.CommandText = string.Format("SELECT * FROM {0} WHERE {1} = '{2}'", _table, colName, column);
}
_reader = _command.ExecuteReader();
//int i = 0;
while (_reader.Read())
{
rows.Add(_reader[0].ToString());
}
_reader.Close();
_reader.Dispose();
return(rows);
}
