public int isHomologyNote(string Table, string term1, string term2, string Value1, string Value2)
{
string tem_sql = "";//定义字符串变量
System.Data.OleDb.OleDbConnection tem_conn =
new System.Data.OleDb.OleDbConnection(connstr); //连接数据库
System.Data.OleDb.OleDbCommand tem_comm; //定义OleDbCommand类
tem_conn.Open(); //打开数据库的连接
//设置SQL语句，查找要添加的记录
tem_sql = "select top 1 * From " + Table + " where " + term1 + " = '" +
Value1 + "' and " + term2 + " = '" + Value2 + "'";
tem_comm = new System.Data.OleDb.OleDbCommand(tem_sql, tem_conn); //执行SQL语句
int RecordCount = 0; //定义变量
if (tem_comm.ExecuteScalar() == null) //如果查询为空
{
RecordCount = 0;
}
else
{
RecordCount = (int)tem_comm.ExecuteScalar(); //返回查找结果的个数
}
tem_conn.Close(); //关闭连接
tem_comm.Dispose(); //释放资源
tem_conn.Dispose(); //释放资源
return(RecordCount); //返回查询记录数量
}
internal void deleteProfile()
{
System.Data.OleDb.OleDbCommand comm = new System.Data.OleDb.OleDbCommand();
comm.CommandType = System.Data.CommandType.Text;
comm.Connection = conn;
comm.CommandText = "DELETE FROM USERS WHERE Email =\'" + loggedEmail + "\'";
conn.Open();
try
{
//comm.Connection.Open();
Object returnValue = comm.ExecuteScalar();
loggedEmail = null;
image = null;
//MessageBox.Show((string)returnValue);
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
conn.Close();
//TODO: add delete user's item as well
//throw new NotImplementedException();
}
protected void btnInsert_Click(object sender, EventArgs e)
{
System.Data.OleDb.OleDbConnection Conn = new System.Data.OleDb.OleDbConnection();
Conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("app_data/products.mdb");
Conn.Open();
//Response.Write(Conn.State);
string strSQL = "insert into [users] ([username], firstname, lastname, email, [password]) values (?,?,?,?,?)";
object returnVal;
System.Data.OleDb.OleDbCommand Comm = new System.Data.OleDb.OleDbCommand();
Comm.Connection = Conn;
Comm.CommandText = "select [username] from [users] where [username] = ?";
Comm.Parameters.AddWithValue("[username]", txtUserName.Text);
returnVal = Comm.ExecuteScalar();
if (returnVal == null)
{
Comm.Parameters.Clear();
Comm.CommandText = strSQL;
Comm.Parameters.AddWithValue("username", txtUserName.Text);
Comm.Parameters.AddWithValue("firstname", txtFName.Text);
Comm.Parameters.AddWithValue("lastname", txtLName.Text);
Comm.Parameters.AddWithValue("email", txtEmail.Text);
Comm.Parameters.AddWithValue("password", txtPassword.Text);
Comm.ExecuteNonQuery();
}
else {
Response.Write("Username already exists.");
}
Conn.Close();
Conn = null;
}
public void InicializeEmployer(DatabaseMaster dataMaster, string login)
{
string compName;
string addr;
string cmd1Text = "SELECT `CompanyName` FROM `Employer` WHERE `AcountID` = " +
"(SELECT `ID` FROM `Users` WHERE `Login` = '" + login + "')";
string cmd2Text = "SELECT `Address` FROM `Employer` WHERE `AcountID` = " +
"(SELECT `ID` FROM `Users` WHERE `Login` = '" + login + "')";
System.Data.OleDb.OleDbCommand cmd1 = new System.Data.OleDb.OleDbCommand();
System.Data.OleDb.OleDbCommand cmd2 = new System.Data.OleDb.OleDbCommand();
dataMaster.connection.Open();
cmd1.Connection = dataMaster.connection;
cmd2.Connection = dataMaster.connection;
cmd1.CommandText = cmd1Text;
cmd2.CommandText = cmd2Text;
try
{
compName = (string)cmd1.ExecuteScalar();
addr = (string)cmd2.ExecuteScalar();
this.employer = new Employer(login, compName, addr, dataMaster);
}
catch
{
MessageBox.Show("Error");
}
dataMaster.connection.Close();
}
public void InicializeUnemployed(string login, DatabaseMaster dataMaster)
{
string fName;
string secName;
string surName;
string cmd1Text = "SELECT `FirstName` FROM `Unemployed` WHERE `AcountID` = " +
"(SELECT `ID` FROM `Users` WHERE `Login` = '" + login + "')";
string cmd2Text = "SELECT `SecondName` FROM `Unemployed` WHERE `AcountID` = " +
"(SELECT `ID` FROM `Users` WHERE `Login` = '" + login + "')";
string cmd3Text = "SELECT `Surname` FROM `Unemployed` WHERE `AcountID` = " +
"(SELECT `ID` FROM `Users` WHERE `Login` = '" + login + "')";
System.Data.OleDb.OleDbCommand cmd1 = new System.Data.OleDb.OleDbCommand();
System.Data.OleDb.OleDbCommand cmd2 = new System.Data.OleDb.OleDbCommand();
System.Data.OleDb.OleDbCommand cmd3 = new System.Data.OleDb.OleDbCommand();
dataMaster.connection.Open();
cmd1.Connection = dataMaster.connection;
cmd2.Connection = dataMaster.connection;
cmd3.Connection = dataMaster.connection;
cmd1.CommandText = cmd1Text;
cmd2.CommandText = cmd2Text;
cmd3.CommandText = cmd3Text;
try
{
fName = (string)cmd1.ExecuteScalar();
secName = (string)cmd2.ExecuteScalar();
surName = (string)cmd3.ExecuteScalar();
this.unemployed = new Unemployed(login, fName, secName, surName, dataMaster);
}
catch
{
MessageBox.Show("Error");
}
dataMaster.connection.Close();
}
static void Main(string[] args)
{
System.Data.DataTable xlsData = new System.Data.DataTable();
string xlsConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=d:\\romil.xlsx;Extended Properties=\"Excel 12.0;HDR=Yes;\"";
System.Data.OleDb.OleDbConnection xlsCon = new System.Data.OleDb.OleDbConnection(xlsConnectionString);
System.Data.OleDb.OleDbCommand xlsCommand;
int recUpdate;
int recordsinSheet;
xlsCommand = new System.Data.OleDb.OleDbCommand("Select count(*) as RecCount from [Sheet1$]");
xlsCommand.Connection = xlsCon;
xlsCon.Open();
recordsinSheet = Convert.ToInt32(xlsCommand.ExecuteScalar());
xlsCommand = new System.Data.OleDb.OleDbCommand("Insert into [Sheet1$] (Field1,Field2) values ('123',2)");
xlsCommand.Connection = xlsCon;
recUpdate = xlsCommand.ExecuteNonQuery();
xlsCon.Close();
if ((recordsinSheet + recUpdate) == 1)
{
ChangeFormat();
}
Console.ReadKey();
}
private void button6_Click(object sender, EventArgs e)
{
if (this.viewResumesForm.dataGridView.CurrentCell != null)
{
this.resumeForm = new ResumeForm(this.employer, Convert.ToInt16(this.viewResumesForm.dataGridView[8, this.viewResumesForm.dataGridView.CurrentCell.RowIndex].Value));
//string firstName;
//string secondName;
//string surname;
string resumeID = Convert.ToString(this.viewResumesForm.dataGridView[0, this.viewResumesForm.dataGridView.CurrentCell.RowIndex].Value);
string cmd1Text = "SELECT `FirstName` FROM `Unemployed` WHERE `ID` = " +
"(SELECT `UnemployedID` FROM `Resume` WHERE `ID` = " + resumeID + ")";
string cmd2Text = "SELECT `SecondName` FROM `Unemployed` WHERE `ID` = " +
"(SELECT `UnemployedID` FROM `Resume` WHERE `ID` = " + resumeID + ")";
string cmd3Text = "SELECT `Surname` FROM `Unemployed` WHERE `ID` = " +
"(SELECT `UnemployedID` FROM `Resume` WHERE `ID` = " + resumeID + ")";
System.Data.OleDb.OleDbCommand cmd1 = new System.Data.OleDb.OleDbCommand();
System.Data.OleDb.OleDbCommand cmd2 = new System.Data.OleDb.OleDbCommand();
System.Data.OleDb.OleDbCommand cmd3 = new System.Data.OleDb.OleDbCommand();
this.employer.dataMaster.connection.Open();
cmd1.Connection = this.employer.dataMaster.connection;
cmd2.Connection = this.employer.dataMaster.connection;
cmd3.Connection = this.employer.dataMaster.connection;
cmd1.CommandText = cmd1Text;
cmd2.CommandText = cmd2Text;
cmd3.CommandText = cmd3Text;
try
{
//firstName = (string)cmd1.ExecuteScalar();
//secondName = (string)cmd2.ExecuteScalar();
//surname = (string)cmd3.ExecuteScalar();
this.resumeForm.firstName.Text = (string)cmd1.ExecuteScalar();
this.resumeForm.secondName.Text = (string)cmd2.ExecuteScalar();
this.resumeForm.surname.Text = (string)cmd3.ExecuteScalar();
}
catch
{
MessageBox.Show("Error");
}
this.employer.dataMaster.connection.Close();
//int id = Convert.ToInt16(this.viewResumesForm.dataGridView[0, this.viewResumesForm.dataGridView.CurrentCell.RowIndex].Value);
//string name = Convert.ToString(this.viewResumesForm.dataGridView[1, this.viewResumesForm.dataGridView.CurrentCell.RowIndex].Value);
//this.resumeForm = new ResumeForm(this.employer, id, name);
//this.resumeForm.firstName.Text = firstName;
//this.resumeForm.secondName.Text = ;
//this.resumeForm.surname.Text = ;
this.resumeForm.education.Text = Convert.ToString(this.viewResumesForm.dataGridView[1, this.viewResumesForm.dataGridView.CurrentCell.RowIndex].Value);
this.resumeForm.lastWork.Text = Convert.ToString(this.viewResumesForm.dataGridView[2, this.viewResumesForm.dataGridView.CurrentCell.RowIndex].Value);
this.resumeForm.birth.Text = Convert.ToString(this.viewResumesForm.dataGridView[3, this.viewResumesForm.dataGridView.CurrentCell.RowIndex].Value);
this.resumeForm.address.Text = Convert.ToString(this.viewResumesForm.dataGridView[4, this.viewResumesForm.dataGridView.CurrentCell.RowIndex].Value);
this.resumeForm.email.Text = Convert.ToString(this.viewResumesForm.dataGridView[5, this.viewResumesForm.dataGridView.CurrentCell.RowIndex].Value);
this.resumeForm.phoneNumber.Text = Convert.ToString(this.viewResumesForm.dataGridView[6, this.viewResumesForm.dataGridView.CurrentCell.RowIndex].Value);
this.resumeForm.goal.Text = Convert.ToString(this.viewResumesForm.dataGridView[7, this.viewResumesForm.dataGridView.CurrentCell.RowIndex].Value);
this.resumeForm.Show();
}
}
// And save it
protected void AccessDataSource1_Inserted(object sender, SqlDataSourceStatusEventArgs e)
{
if (pendingComment != null && e.AffectedRows == 1)
{
System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand("SELECT @@IDENTITY");
cmd.Connection = (System.Data.OleDb.OleDbConnection)e.Command.Connection;
object newID = cmd.ExecuteScalar();
MyComments.SetComment(newID, pendingComment);
pendingComment = null;
}
}
public int isHomologyNote(string Table, string term1, string term2, string Value1, string Value2)
{
string tem_sql = "";//定义字符串变量
System.Data.OleDb.OleDbConnection tem_conn =
new System.Data.OleDb.OleDbConnection(connstr);//连接数据库
System.Data.OleDb.OleDbCommand tem_comm;//定义OleDbCommand类
tem_conn.Open();//打开数据库的连接
//设置SQL语句，查找要添加的记录
tem_sql = "select top 1 * From " + Table + " where " + term1 + " = '" +
Value1 + "' and " + term2 + " = '" + Value2 + "'";
tem_comm = new System.Data.OleDb.OleDbCommand(tem_sql, tem_conn);//执行SQL语句
int RecordCount = 0;//定义变量
if (tem_comm.ExecuteScalar() == null)//如果查询为空
RecordCount = 0;
else
RecordCount = (int)tem_comm.ExecuteScalar();//返回查找结果的个数
tem_conn.Close();//关闭连接
tem_comm.Dispose();//释放资源
tem_conn.Dispose();//释放资源
return RecordCount;//返回查询记录数量
}
private void appointmentsTableAdapter_RowUpdated(object sender, System.Data.OleDb.OleDbRowUpdatedEventArgs e)
{
if (e.Status == UpdateStatus.Continue && e.StatementType == StatementType.Insert)
{
int id = 0;
using (System.Data.OleDb.OleDbCommand cmd =
new System.Data.OleDb.OleDbCommand("SELECT @@IDENTITY", appointmentsTableAdapter.Connection)) {
id = (int)cmd.ExecuteScalar();
}
e.Row["ID"] = id;
}
}
public double total; //<< keep this one
protected void Page_Load(object sender, EventArgs e)
{
var id = Request.Params["ID"];
System.Data.OleDb.OleDbConnection conn;
System.Data.OleDb.OleDbCommand cmd;
conn = new System.Data.OleDb.OleDbConnection("--");
cmd = new System.Data.OleDb.OleDbCommand();
conn.Open();
cmd.Connection = conn;
var sql = String.Format(@"select sum(PayAmt) as total from CurePay where CureID = '{0}'", id);
cmd.CommandText = sql;
double total = 0; // << remove the double keyword from this line
total = Convert.ToDouble(cmd.ExecuteScalar());
conn.Close();
private void button1_Click(object sender, EventArgs e)
{
var cn = new System.Data.OleDb.OleDbConnection(
"Provider=Microsoft.ACE.OLEDB.12.0;"
+ "Data Source=C:\\C#2017\\SampleDB.accdb;");
//DBに接続して社員テーブルのレコード数を取得する
cn.Open();
var cmd = new System.Data.OleDb.OleDbCommand("SELECT COUNT(*) FROM 社員", cn);
int num = (int)cmd.ExecuteScalar();
cn.Close();
MessageBox.Show(num + "件のレコードがあります。", "通知");
}
public object ScalarBySql(string sql)
{
object dr = null;
try
{
System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand(sql, managerDB.OpenApp());
dr = command.ExecuteScalar();
}
catch (Exception exception)
{
IAppLog log = (IAppLog)AppLogFactory.LogProduct();
log.writeLog(this.GetType().Name, "查询数据出错!" + exception.ToString());
}
return(dr);
}
public int executeScalar(string CadenaSQL)
{
int resultado = 0;
try
{
System.Data.OleDb.OleDbCommand comando = new System.Data.OleDb.OleDbCommand(CadenaSQL, conexion);
resultado = Convert.ToInt32(comando.ExecuteScalar());
}
catch (System.Data.OleDb.OleDbException errBD)
{
MessageBox.Show(errBD.Message, "Erro nunha operación SCALAR");
}
return(resultado);
}
private void button1_Click(object sender, EventArgs e)
{
int flag = this.databaseMaster.RegisterNewUser(this.checkInFormUser.text1.Text, this.checkInFormUser.text2.Text, this.checkInFormUser.comboBox.Text);
if (flag == 0)
{
System.Data.OleDb.OleDbCommand oleDbCommand = new System.Data.OleDb.OleDbCommand();
this.databaseMaster.connection.Open();
oleDbCommand.CommandText = "SELECT `ID` FROM `Users` WHERE `Login` = '" + this.checkInFormUser.text1.Text + "'";
oleDbCommand.Connection = this.databaseMaster.connection;
try
{
this.id = (int)oleDbCommand.ExecuteScalar();
}
catch
{
MessageBox.Show("ID wasn`t saved!");
}
this.databaseMaster.connection.Close();
this.panel1.Controls.Remove(this.checkInFormUser);
this.tableLayoutPanel1.Controls.Remove(this.button1);
if (this.checkInFormUser.comboBox.Text == "Знайти роботу")
{
this.checkInFormUnemployed = new CheckInFormUnemployed();
this.checkInFormUnemployed.TopLevel = false;
this.checkInFormUnemployed.FormBorderStyle = FormBorderStyle.None;
this.checkInFormUnemployed.Dock = DockStyle.Fill;
this.checkInFormUnemployed.Visible = true;
this.panel1.Controls.Add(this.checkInFormUnemployed);
this.tableLayoutPanel1.Controls.Add(this.button2, 1, 0);
}
else if (this.checkInFormUser.comboBox.Text == "Знайти співробітника")
{
this.checkInFormEmployer = new CheckInFormEmployer();
this.checkInFormEmployer.TopLevel = false;
this.checkInFormEmployer.FormBorderStyle = FormBorderStyle.None;
this.checkInFormEmployer.Dock = DockStyle.Fill;
this.checkInFormEmployer.Visible = true;
this.panel1.Controls.Add(this.checkInFormEmployer);
this.tableLayoutPanel1.Controls.Add(this.button3, 1, 0);
}
}
}
/// <summary>
/// イベントで呼ばれる実際の処理：@@IDENTITYでオートナンバー型の列を取得する
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void Adapter_RowUpdated(object sender, System.Data.OleDb.OleDbRowUpdatedEventArgs e)
{
if (e.StatementType == System.Data.StatementType.Insert &&
e.Status == System.Data.UpdateStatus.Continue)
{
var cmd = new System.Data.OleDb.OleDbCommand("SELECT @@IDENTITY", null);
cmd.Connection = e.Command.Connection;
cmd.Transaction = e.Command.Transaction;
var result = cmd.ExecuteScalar();
if (result != null &&
result.GetType() != typeof(System.DBNull))
{
e.Row["ID"] = (int)result;
e.Row.AcceptChanges();
}
}
}
/// <summary>
/// イベントで呼ばれる実際の処理：@@IDENTITYでオートナンバー型の列を取得する
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void Adapter_RowUpdated(object sender, System.Data.OleDb.OleDbRowUpdatedEventArgs e)
{
if (e.StatementType == System.Data.StatementType.Insert
&& e.Status == System.Data.UpdateStatus.Continue)
{
var cmd = new System.Data.OleDb.OleDbCommand("SELECT @@IDENTITY", null);
cmd.Connection = e.Command.Connection;
cmd.Transaction = e.Command.Transaction;
var result = cmd.ExecuteScalar();
if (result != null
&& result.GetType() != typeof(System.DBNull))
{
e.Row["ID"] = (int)result;
e.Row.AcceptChanges();
}
}
}
private void save(Report r, System.Data.OleDb.OleDbConnection con)
{
string cmd;
TextManager mng = new TextManager();
string[] text = new string[r.Items.Count];
cmd = "Insert into Diagn (FIO, Dat, Name) Values (";
cmd += "\"" + r.FIO + "\", ";
cmd += "\"" + r.Date.ToShortDateString() + "\", ";
cmd += "\"" + (string)r.Tag + "\");";
con.Open();
System.Data.OleDb.OleDbCommand c =
new System.Data.OleDb.OleDbCommand(cmd, con);
c.ExecuteNonQuery();
cmd = "Select max(ID) From Diagn;";
c = new System.Data.OleDb.OleDbCommand(cmd, con);
mng.Save(r, c.ExecuteScalar());
con.Close();
}
internal bool UpdateProfile(string str, string email)
{
System.Data.OleDb.OleDbCommand comm = new System.Data.OleDb.OleDbCommand();
comm.CommandText = "UPDATE USERS SET " + str + "WHERE Email = '" + email + "';";
comm.Connection = conn;
conn.Open();
try
{
Object returnValue = comm.ExecuteScalar();
}
catch (Exception e)
{
conn.Close();
MessageBox.Show(e.Message);
return(false);
}
conn.Close();
return(true);
//throw new NotImplementedException();
}
public void InsertUser(string user, string phone)
{
System.Data.OleDb.OleDbCommand comm = new System.Data.OleDb.OleDbCommand();
//Table USERS
//FirstName, LastName, BirthDate, Email, PPEmail, PPPassword, Password, Phone, Image
List <string> userVal = SeparateValues(user);
comm.CommandType = System.Data.CommandType.Text;
comm.CommandText = "INSERT INTO USERS ([FirstName], [LastName], [BirthDate], [Email], [PPEmail], PPPassword, [Password]";
string values = "\tVALUES(\'" + userVal[0] + "\',\'" + userVal[1] + "\',\'" + userVal[2] + "\',\'" + userVal[3] + "\',\'" + userVal[4] + "\',\'" + userVal[5] + "\',\'" + userVal[6] + "\'";
if (!String.IsNullOrWhiteSpace(phone))
{
comm.CommandText += ", [Phone]";
values += ",\'" + userVal[7] + "\'";
}
if (!String.IsNullOrWhiteSpace(image))
{
comm.CommandText += ", [Image]";
values += ",\'" + image + "\'"; //Probably need fixing
}
comm.CommandText += ")\n";
values += ")";
comm.CommandText += values;
comm.Connection = conn;
conn.Open();
//comm.CommandText = "SELECT * FROM USERS";
try
{
//comm.Connection.Open();
Object returnValue = comm.ExecuteScalar();
//MessageBox.Show((string)returnValue);
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
conn.Close();
}
private void UpdateQuery()
{
string path = Directory.GetCurrentDirectory();
System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(connectionString: @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + "\\Database11.accdb");
System.Data.OleDb.OleDbCommand comm = new System.Data.OleDb.OleDbCommand();
comm.CommandType = System.Data.CommandType.Text;
comm.CommandText = "UPDATE USERS SET " + setStringQuery() + "WHERE Email = '" + email + "';";
comm.Connection = conn;
conn.Open();
try
{
Object returnValue = comm.ExecuteScalar();
}
catch (Exception e)
{
conn.Close();
MessageBox.Show(e.Message);
}
conn.Close();
}
private void button1_Click(object sender, EventArgs e)
{
String username = TB_USER.Text.Trim();
String password = TB_PASS.Text.Trim();
String m_conn_str = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Application.StartupPath + @"\21.mdb;";
System.Data.OleDb.OleDbConnection m_conn = new System.Data.OleDb.OleDbConnection(m_conn_str);
m_conn.Open();
string query1 = "select * from BlackJack where user_id ='" + username + "' and password='******'";
System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(query1, m_conn);
try
{
if (cmd.ExecuteScalar() != null)
{
System.Data.OleDb.OleDbDataAdapter dal = new System.Data.OleDb.OleDbDataAdapter(query1, m_conn);
DataTable dt = new DataTable();
dal.Fill(dt);
int login1_id = Convert.ToInt32(dt.Rows[0]["ID"].ToString());
String user_name = dt.Rows[0]["user_id"].ToString();
Hide();
Main m1 = new Main();
m1.login_id = login1_id;
m1.username = user_name;
m1.ShowDialog();
}
else
{
MessageBox.Show("用户名或密码错误！");
TB_USER.Text = username;
TB_PASS.Text = password;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
m_conn.Close();
}
private void button1_Click(object sender, EventArgs e)
{
string commandText = "SELECT `Rights` FROM `Users` WHERE `Login` = '" + this.textBox1.Text +
"' AND `Password` = '" + this.textBox2.Text + "'";
string rights = "None";
System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand();
this.databaseMaster.connection.Open();
cmd.Connection = this.databaseMaster.connection;
cmd.CommandText = commandText;
try
{
rights = (string)cmd.ExecuteScalar();
//MessageBox.Show("Ви успішно авторизувалися!");
//MessageBox.Show(rights);
}
catch
{
MessageBox.Show("Error");
}
this.databaseMaster.connection.Close();
if (rights == "Знайти роботу")
{
UnemployedRoom unemployedRoom = new UnemployedRoom(this.databaseMaster, this.textBox1.Text);
unemployedRoom.Show();
this.Visible = false;
}
else if (rights == "Знайти співробітника")
{
EmployerRoom employerRoom = new EmployerRoom(this.databaseMaster, this.textBox1.Text);
employerRoom.Show();
this.Visible = false;
}
else
{
MessageBox.Show("Ви ввели не коректні дані. Спробуйте ще раз");
}
this.databaseMaster.connection.Close();
}
/// <summary>
/// Надсилання сповіщення
/// </summary>
/// <param name="recipientID">ID отримувача</param>
/// <param name="message">Текс сповіщення</param>
public void SendMessage(int recipientID, string message)
{
string cmdString2 = "SELECT `ID` FROM `Employer` WHERE `AcountID` = (SELECT `ID` FROM `Users` WHERE `Login` = '" + this.login + "')";
int senderID = 0;
System.Data.OleDb.OleDbCommand cmd2 = new System.Data.OleDb.OleDbCommand();
this.databaseMaster.connection.Open();
cmd2.CommandText = cmdString2;
cmd2.Connection = this.databaseMaster.connection;
try
{
senderID = (int)cmd2.ExecuteScalar();
}
catch
{
MessageBox.Show("Error");
}
this.databaseMaster.connection.Close();
string cmdString1 = "INSERT INTO `Message` (`SenderID`, `RecipientID`, `MessageText`) VALUES ('"
+ senderID + "', '" + recipientID + "', '" + message + "')";
System.Data.OleDb.OleDbCommand cmd1 = new System.Data.OleDb.OleDbCommand();
this.databaseMaster.connection.Open();
cmd1.CommandText = cmdString1;
cmd1.Connection = this.databaseMaster.connection;
try
{
cmd1.ExecuteNonQuery();
MessageBox.Show("Повідомлення успішно відправлене!");
}
catch
{
MessageBox.Show("Error");
}
this.databaseMaster.connection.Close();
}
public static string Gf_CommNameFind(System.Data.OleDb.OleDbConnection Conn, string Cd_Mana_No, string Code, string nameType)
{
string functionReturnValue = "";
string sQuery = null;
try
{
if (GeneralCommon.GF_DbConnect() == false)
{
functionReturnValue = "";
return(functionReturnValue);
}
switch (nameType)
{
case "1":
//Short Name
sQuery = "SELECT CD_SHORT_NAME FROM TZ_CD WHERE CD_MANA_NO = '" + Cd_Mana_No + "' AND CD = '" + Code + "' ";
break;
case "2":
//Full Name
sQuery = "SELECT CD_NAME FROM TZ_CD WHERE CD_MANA_NO = '" + Cd_Mana_No + "' AND CD = '" + Code + "' ";
break;
case "3":
//Short Eng Name
sQuery = "SELECT CD_SHORT_ENG FROM TZ_CD WHERE CD_MANA_NO = '" + Cd_Mana_No + "' AND CD = '" + Code + "' ";
break;
case "4":
//Full Eng Name
sQuery = "SELECT CD_FULL_ENG FROM TZ_CD WHERE CD_MANA_NO = '" + Cd_Mana_No + "' AND CD = '" + Code + "' ";
break;
default:
//Full Name
sQuery = "SELECT CD_NAME FROM TZ_CD WHERE CD_MANA_NO = '" + Cd_Mana_No + "' AND CD = '" + Code + "' ";
break;
}
System.Data.OleDb.OleDbCommand Oc = new System.Data.OleDb.OleDbCommand(sQuery, Conn);
//Ado Execute
if (Oc.ExecuteScalar() == null)
{
functionReturnValue = "";
}
else
{
functionReturnValue = Oc.ExecuteScalar().ToString();
}
}
catch (Exception ex)
{
// AdoRs = null;
functionReturnValue = "";
GeneralCommon.Gp_MsgBoxDisplay("Gf_CommNameFind Error : " + ex.Message);
}
return(functionReturnValue);
}
public int InsertPatient(string surname, string firstname, string street, string zipcode, string city, string homephone, string workphone, string mobilephone, string personnumber, string info, string freecarddate)
{
if ((surname == null))
{
this.Adapter.InsertCommand.Parameters[0].Value = System.DBNull.Value;
}
else
{
this.Adapter.InsertCommand.Parameters[0].Value = ((string)(surname));
}
if ((firstname == null))
{
this.Adapter.InsertCommand.Parameters[1].Value = System.DBNull.Value;
}
else
{
this.Adapter.InsertCommand.Parameters[1].Value = ((string)(firstname));
}
if ((street == null))
{
this.Adapter.InsertCommand.Parameters[2].Value = System.DBNull.Value;
}
else
{
this.Adapter.InsertCommand.Parameters[2].Value = ((string)(street));
}
if ((zipcode == null))
{
this.Adapter.InsertCommand.Parameters[3].Value = System.DBNull.Value;
}
else
{
this.Adapter.InsertCommand.Parameters[3].Value = ((string)(zipcode));
}
if ((city == null))
{
this.Adapter.InsertCommand.Parameters[4].Value = System.DBNull.Value;
}
else
{
this.Adapter.InsertCommand.Parameters[4].Value = ((string)(city));
}
if ((homephone == null))
{
this.Adapter.InsertCommand.Parameters[5].Value = System.DBNull.Value;
}
else
{
this.Adapter.InsertCommand.Parameters[5].Value = ((string)(homephone));
}
if ((workphone == null))
{
this.Adapter.InsertCommand.Parameters[6].Value = System.DBNull.Value;
}
else
{
this.Adapter.InsertCommand.Parameters[6].Value = ((string)(workphone));
}
if ((mobilephone == null))
{
this.Adapter.InsertCommand.Parameters[7].Value = System.DBNull.Value;
}
else
{
this.Adapter.InsertCommand.Parameters[7].Value = ((string)(mobilephone));
}
if ((personnumber == null))
{
this.Adapter.InsertCommand.Parameters[8].Value = System.DBNull.Value;
}
else
{
this.Adapter.InsertCommand.Parameters[8].Value = ((string)(personnumber));
}
if ((info == null))
{
this.Adapter.InsertCommand.Parameters[9].Value = System.DBNull.Value;
}
else
{
this.Adapter.InsertCommand.Parameters[9].Value = ((string)(info));
}
if ((freecarddate == null))
{
this.Adapter.InsertCommand.Parameters[10].Value = System.DBNull.Value;
}
else
{
this.Adapter.InsertCommand.Parameters[10].Value = ((string)(freecarddate));
}
System.Data.ConnectionState previousConnectionState = this.Adapter.InsertCommand.Connection.State;
if (((this.Adapter.InsertCommand.Connection.State & System.Data.ConnectionState.Open)
!= System.Data.ConnectionState.Open))
{
this.Adapter.InsertCommand.Connection.Open();
}
try
{
int returnValue = this.Adapter.InsertCommand.ExecuteNonQuery();
System.Data.OleDb.OleDbConnection conn = this.Adapter.InsertCommand.Connection;
System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand("SELECT @@IDENTITY FROM Patients", conn );
returnValue = Convert.ToInt32(cmd.ExecuteScalar());
return returnValue;
}
finally
{
if ((previousConnectionState == System.Data.ConnectionState.Closed))
{
this.Adapter.InsertCommand.Connection.Close();
}
}
}
public override void RestoreParameters()
{
//testsetup was left for compatibility only; is not used otherwise
System.Data.OleDb.OleDbDataReader rsParameters = default(System.Data.OleDb.OleDbDataReader);
try
{
if (DBPtr == null)
{
return ;
}
DBPtr.Open();
System.Data.OleDb.OleDbCommand sqlCOM = new System.Data.OleDb.OleDbCommand("SELECT * FROM QuasiAddIn2_NetSample2 WHERE Setup=" + System.Convert.ToString(Setup)) {Connection = DBPtr};
sqlCOM.ExecuteScalar();
rsParameters = sqlCOM.ExecuteReader();
if (!rsParameters.Read())
{
//no records
rsParameters.Close();
System.Data.OleDb.OleDbCommand sqlWRCom = new System.Data.OleDb.OleDbCommand("INSERT INTO QuasiAddIn2_NetSample2 (Setup) VALUES (" + System.Convert.ToString(Setup) + ")", DBPtr);
sqlWRCom.ExecuteNonQuery();
sqlCOM.ExecuteNonQuery();
rsParameters = sqlCOM.ExecuteReader();
rsParameters.Read();
}
if (!(rsParameters == null))
{
mvarAverages = System.Convert.ToInt16(rsParameters["Averages"]);
}
base.RaiseRestoreParams();
}
catch (Exception ex)
{
MessageBox.Show("RestoreParameters " + ex.Message);
}
finally
{
if (DBPtr.State == ConnectionState.Open)
{
DBPtr.Close();
}
}
}
private void buttonCommand_Click(object sender, EventArgs e)
{
//1. Make a Connection
System.Data.OleDb.OleDbConnection objOleCon;
objOleCon = new System.Data.OleDb.OleDbConnection();
objOleCon.ConnectionString = strConnection;
objOleCon.Open();
objOleCon.ChangeDatabase("Northwind"); //We will use the Northwind database for this demo...
//2. Issue a Command
System.Data.OleDb.OleDbCommand objCmd;
objCmd = new System.Data.OleDb.OleDbCommand();
objCmd.Connection = objOleCon;
objCmd.CommandType = CommandType.Text;//Not normally typed out since Text is the default anyway
//3. Process the Results
//When you don't need results back, like these two examples, use ExecuteNonQuery()
objCmd.CommandText = "Insert Into MyTable Values(1, 'SomeData')";
objCmd.CommandText = "Raiserror('Error Level 11 to 25 are Caught by Try-Catch', 15, 1)";
try
{
objCmd.ExecuteNonQuery(); // Ex. Inserts, Updates, Deletes SQL commands
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
//When you want a single value back use ExecuteScalar()
objCmd.CommandText = "Select Count(*) From Products";//Your SQL Code
int intResult = (int)objCmd.ExecuteScalar(); //Ex. Capture returning Integer data
// string strResult = (string)objCmd.ExecuteScalar(); //Ex. Capture returning Varchar(50) data
MessageBox.Show("Number of Products: " + intResult.ToString());
//When you want multiple results back
objCmd.CommandText = "Select ProductName From Products"; //Your SQL Code
System.Data.OleDb.OleDbDataReader objDR; //Class used to process multiple results
objDR = objCmd.ExecuteReader();
MessageBox.Show(string.Format("FieldCount: {0}, RecordsAffected: {1} ", objDR.FieldCount, objDR.RecordsAffected));
objDR.Close();
//You can also submit multiple queries and get multiple result SETS back
objCmd.CommandText = @"Select ProductName From Products ;
Select OrderId, ProductId From [Order Details]"; //Your SQL Code
objDR = objCmd.ExecuteReader();
MessageBox.Show(string.Format("FieldCount: {0}", objDR.FieldCount));
objDR.Read();
objDR.NextResult();
objDR.Read();
MessageBox.Show(string.Format("FieldCount: {0}", objDR.FieldCount));
objDR.Close();
//4. Clean up code
objOleCon.Close();
}
private void PrepareDatabase()
{
string sConnection = @"Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=" + TempBurnPath + @"\Data\RODB.DAT;Mode=Read|Write|Share Deny None;";
System.Data.OleDb.OleDbConnection AC = new System.Data.OleDb.OleDbConnection(sConnection);
try
{
AC.Open();
}
catch(System.Data.OleDb.OleDbException Err)
{
MessageBox.Show(Err.Message);
string peek = Err.Message;
return;
}
catch(Exception Err)
{
MessageBox.Show(Err.Message);
string sPeek = Err.Message;
return;
}
#region TheWork
System.Data.OleDb.OleDbCommand CM = new System.Data.OleDb.OleDbCommand();
CM.Connection = AC;
System.Data.OleDb.OleDbDataAdapter DA = new System.Data.OleDb.OleDbDataAdapter();
System.Data.DataTable DT = new System.Data.DataTable();
foreach(OCL.RecordingSession RS in SelectedRecordingSessions)
{
string sSQL = @"INSERT INTO tblSession(Description,IsPresentation,OwnerId,CreatedDate,IsOysterSession) VALUES('" +
RS.Description + "'" +
"," +Convert.ToInt32(RS.IsPresentation) +
"," + 23 +
",'" + RS.CreatedDate +"'" +
"," + Convert.ToInt32(true) + ")";
int SessionID = 0;
try
{
CM.CommandText = sSQL;
CM.ExecuteNonQuery();
CM.CommandText = "SELECT @@IDENTITY";
SessionID = (int)CM.ExecuteScalar();
}
catch(System.Data.OleDb.OleDbException Err)
{
MessageBox.Show(Err.Message);
string peek = Err.Message;
return;
}
catch(Exception Err)
{
MessageBox.Show(Err.Message);
string sPeek = Err.Message;
return;
}
foreach(OCL.Recording R in RS.CurrentRecordings(frmParent.LUser))
{
string FileName = "";
string FileExt = "";
string[] sFileName = R.Description.Split(".".ToCharArray());
if(sFileName.Length == 1)
{
FileExt = "";
}
else if(sFileName.Length == 2)
{
FileExt = "." + sFileName[1];
}
FileName = RS.Description + "_" + R.DisplayName;
string NewDescription = RenameFile(FileName,FileExt);
sSQL = @"INSERT INTO tblRecording(Name,Created,VideoStorageServerId," +
"DisplayName, StreamingEncoderId,SessionId," +
"IsDesktopCapture,IsPrimaryCamera,DiskSize,Playlength," +
"HasAudio,HasVideo) " +
"VALUES ('" + NewDescription +
"', '" + R.CreatedDate +
"', 9" +
", '" + R.DisplayName +
"', 15" +
"," + SessionID +
"," + Convert.ToInt32(R.IsDesktopCapture) +
"," + Convert.ToInt32(R.IsPrimaryCamera) +
"," + R.SizeOnDisk +
"," + R.PlayLength +
"," + Convert.ToInt32(R.HasAudio) +
"," + Convert.ToInt32(R.HasVideo) +
")";
try
{
CM.CommandText = sSQL;
CM.ExecuteNonQuery();
}
catch(System.Data.OleDb.OleDbException Err)
{
MessageBox.Show(Err.Message);
string peek = Err.Message;
return;
}
catch(Exception Err)
{
MessageBox.Show(Err.Message);
string sPeek = Err.Message;
return;
}
}
foreach(OCL.Note N in RS.AllVisibleNotes(frmParent.LUser))
{
int NoteID = 0;
sSQL = "INSERT INTO tblNotes(SyncTime,Description,SessionId,Created) VALUES('" +
N.SyncTime + "','" + N.Description + "'," + SessionID + ",'" +
N.Created +"'" +
")";
CM.CommandText = sSQL;
try
{
CM.CommandText = sSQL;
CM.ExecuteNonQuery();
CM.CommandText = "SELECT @@IDENTITY";
NoteID = (int)CM.ExecuteScalar();
}
catch(System.Data.OleDb.OleDbException Err)
{
MessageBox.Show(Err.Message);
string peek = Err.Message;
return;
}
catch(Exception Err)
{
MessageBox.Show(Err.Message);
string sPeek = Err.Message;
return;
}
OCL.Attachments FileAttachments = N.AllVisibleAttachments(frmParent.LUser);
foreach(OCL.Attachment A in FileAttachments)
{
sSQL = "INSERT INTO tblAttachments(OriginalName,StoredName,NoteId,Created,FileSize) VALUES(" +
" '" + A.OriginalName + "','" + A.StoredName +
"'," + NoteID.ToString() +
",'" + A.Created + "'" +
"," + A.FileSize +
")";
CM.CommandText = sSQL;
try
{
CM.ExecuteNonQuery();
}
catch(System.Data.OleDb.OleDbException Err)
{
MessageBox.Show(Err.Message);
string peek = Err.Message;
return;
}
catch(Exception Err)
{
MessageBox.Show(Err.Message);
string sPeek = Err.Message;
return;
}
}
}
}
DA.Dispose();
DT.Dispose();
CM.Dispose();
#endregion
AC.Close();
AC.Dispose();
System.Threading.Thread.SpinWait(500000);
}
private void button1_Click(object sender, EventArgs e)
{
String m_conn_str = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Application.StartupPath + @"\21.mdb;";
System.Data.OleDb.OleDbConnection m_conn = new System.Data.OleDb.OleDbConnection(m_conn_str);
m_conn.Open();
if (textBox_name.Text.Trim() == "" || textBox_password.Text.Trim() == "" || textBox_password_repeat.Text.Trim() == "")
{
if (textBox_name.Text.Trim() == "")
{
MessageBox.Show("name cannot be empty!", "wrong");
}
if (textBox_password.Text.Trim() == "")
{
MessageBox.Show("password cannot be empty!", "wrong");
}
if (textBox_password_repeat.Text.Trim() == "")
{
MessageBox.Show("comfirm password cannot be empty!", "wrong");
}
goto end;
}//非空判断
String name = textBox_name.Text.Trim();
String password = textBox_password.Text.Trim();
String confirm_pass = textBox_password_repeat.Text.Trim();
String query2 = "select count(*) from BlackJack where user_id = '" + name + "'";
System.Data.OleDb.OleDbCommand m_comm2 = new System.Data.OleDb.OleDbCommand(query2, m_conn);
int check = Convert.ToInt32(m_comm2.ExecuteScalar());
if (check != 0)
{
MessageBox.Show("this username has already existed!", "warning");
goto end;
}
if (password == confirm_pass)
{
string query1 = "insert into BlackJack (user_id,wager,[password],total,win,draw,lose,winp) values('" + name + "','" + 30000 + "','" + password + "','" + 0 + "','" + 0 + "','" + 0 + "','" + 0 + "','" + 0 + "')";
System.Data.OleDb.OleDbCommand m_comm = new System.Data.OleDb.OleDbCommand(query1, m_conn);
m_comm.ExecuteNonQuery();
MessageBox.Show("success!");
m_conn.Close();
m_conn.Dispose();
Login login1 = new Login();
this.Hide();
Dispose();
login1.Show();
}
else
{
MessageBox.Show("Your new and confirm password are different. Please enter your passwords again.", "wrong");
textBox_password_repeat.Clear();
}
end : m_conn.Close();
m_conn.Dispose();
}
void Session_Start(object sender, EventArgs e)
{
// Fires when the session is started
Application["UserOnline"] = (int)Application["UserOnline"] + 1;
//Session("VPath_Setting") = "DEBUG" ' Either 'DEBUG' OR 'PRODUCTION'
// Fires when the session is started
string sHost = "";
try
{
sHost = System.Net.Dns.GetHostEntry(Request.Params["REMOTE_ADDR"]).HostName.ToString().ToUpper();
}
catch
{
}
finally
{
Session["sHOST"] = sHost.ToUpper();
}
if (ConfigurationManager.ConnectionStrings["_UserDatabase"] != null)
{
//create log entry in the log database
System.Data.OleDb.OleDbConnection myConnection = new System.Data.OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings["_UserDatabase"].ConnectionString);
string strCommand = string.Format("Insert into [LOG] (IP_ADDRESS,COMPUTER_NAME, TIME_START) values ('{0}','{1}','{2}')", Request.Params["REMOTE_ADDR"], sHost, DateTime.Now.ToString());
System.Data.OleDb.OleDbCommand myCommand = new System.Data.OleDb.OleDbCommand(strCommand, myConnection);
myCommand.Connection.Open();
myCommand.ExecuteNonQuery();
System.Data.OleDb.OleDbCommand cmdGetidentity = new System.Data.OleDb.OleDbCommand("SELECT @@IDENTITY", myConnection);
long lngNewID = Convert.ToInt64(cmdGetidentity.ExecuteScalar());
Session["SID"] = lngNewID;
myCommand.Connection.Close();
//check if client is a known search engine crawler
bool bIpMatched = false;
if (!String.IsNullOrEmpty(ConfigurationManager.AppSettings["SkipCrawlerCheck"]) && !bool.Parse(ConfigurationManager.AppSettings["SkipCrawlerCheck"]))
{
System.Data.OleDb.OleDbConnection IPDBConnection = new System.Data.OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings["_InternalIPDB"].ConnectionString);
string strIPQuery = "select * from [IP_Crawler] where [IP] = '" + Request.UserHostAddress + "'";
System.Data.OleDb.OleDbDataAdapter dsCmd = new System.Data.OleDb.OleDbDataAdapter(strIPQuery, IPDBConnection);
System.Data.DataSet myData = new System.Data.DataSet();
dsCmd.Fill(myData, "IP_Exact");
System.Data.DataTable pTable = myData.Tables["IP_Exact"];
if (pTable.Rows.Count > 0)
{
//Exact match to IP address
bIpMatched = true;
//
}
else
{
//this is a small table, just loop through all the record, if the table gets bigger we need to change the logic here
strIPQuery = "select * from [IP_Crawler] where instr(1,[IP],'*')";
dsCmd = new System.Data.OleDb.OleDbDataAdapter(strIPQuery, IPDBConnection);
dsCmd.Fill(myData, "IP_SubNet");
pTable = myData.Tables["IP_SubNet"];
System.Data.DataRow pRow = null;
foreach (DataRow pRow_loopVariable in pTable.Rows)
{
pRow = pRow_loopVariable;
Wildcard wildcard = new Wildcard(pRow["IP"].ToString(), RegexOptions.IgnoreCase);
if (wildcard.IsMatch(Request.UserHostAddress) || wildcard.IsMatch(Session["sHOST"].ToString()))
{
bIpMatched = true;
break; // TODO: might not be correct. Was : Exit For
}
}
}
}
if (bIpMatched)
{
Session["IsCrawler"] = true;
}
else
{
Session["IsCrawler"] = false;
}
}
String userName = "******";
if (HttpContext.Current.User.Identity.Name != null)
{
userName = HttpContext.Current.User.Identity.Name;
}
Application["User" + Session["SID"]] = userName + "|" + DateTime.Now.ToString();
Session["LastRequest"] = DateTime.Now;
}
private void buttonParameters_Click(object sender, EventArgs e)
{
/****************************************************
* NOTE: You must create a stored procedure using the code
* found in the PrameterDemoCode.sql file before you can run this!
****************************************************/
//1. Make a Connection
System.Data.OleDb.OleDbConnection objOleCon;
objOleCon = new System.Data.OleDb.OleDbConnection();
objOleCon.ConnectionString = strConnection;
objOleCon.Open();
objOleCon.ChangeDatabase("TempDB"); //Since the previous demos used Northwind we need this command.
//2. Issue a Command
System.Data.OleDb.OleDbCommand objCmd;
objCmd = new System.Data.OleDb.OleDbCommand();
objCmd.Connection = objOleCon;
objCmd.CommandType = CommandType.StoredProcedure;
objCmd.CommandText = "DivideDemo";
//2.1 Create and Add Parameters before you execute the Sproc.
#region Input Parameters
System.Data.OleDb.OleDbParameter objP1 = new System.Data.OleDb.OleDbParameter();
objP1.Direction = ParameterDirection.Input;
objP1.DbType = DbType.Double;
objP1.ParameterName = "@p1";
objP1.Value = Convert.ToDouble(textBox1.Text);
System.Data.OleDb.OleDbParameter objP2 = new System.Data.OleDb.OleDbParameter();
objP2.Direction = ParameterDirection.Input;
objP2.DbType = DbType.Double;
objP2.ParameterName = "@p2";
objP2.Value = Convert.ToDouble(textBox2.Text); ;
#endregion
#region Output Parameter
System.Data.OleDb.OleDbParameter objAnswer = new System.Data.OleDb.OleDbParameter();
objAnswer.Direction = ParameterDirection.Output;
objAnswer.DbType = DbType.Double;
objAnswer.ParameterName = "@answer";
#endregion
#region Return Code
System.Data.OleDb.OleDbParameter objRC = new System.Data.OleDb.OleDbParameter();
objRC.Direction = ParameterDirection.ReturnValue;
objRC.DbType = DbType.Int32;
objRC.ParameterName = "@RC";
#endregion
//Now, add all to the Parameter Collection
/*
* NOTE: The order of the parameters used to call the Stored Procedure is Positional!
* So, if the Store Procedure's parameter list looks like this...
*
* Create Proc DivideDemo( @p1 float, @p2 float, @answer float out )
*
* And, you execute the Store Procedure in SQL with the following code...
*
* Exec @RC = DivideDemo 4, 3, @Value out;
*
*Then the Return Code parameter *MUST* be added first, followed by @p1 parameter,
* @p2, and @answer, to the Parameters Collection like this....
*
*/
objCmd.Parameters.Add(objRC); //1st
objCmd.Parameters.Add(objP2); //2nd
objCmd.Parameters.Add(objP1); //3rd
objCmd.Parameters.Add(objAnswer);//4th
/*NOTE: Start a SQL Profiler Trace and show students how the command is submitted */
//3. Process the Results
try
{
object objSelectData = objCmd.ExecuteScalar(); //Use this when there is a Select that selects a single value in the sproc.
MessageBox.Show("Select data: " + Convert.ToString(objSelectData));
MessageBox.Show("Output data: " + objCmd.Parameters["@answer"].Value.ToString());
MessageBox.Show("Return Value data: " + objCmd.Parameters["@RC"].Value.ToString());
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
//4. Clean up code
objOleCon.Close();
}
public void CheckBatch(ref string StrSQl_Count, ref string StrSQl_1,
ref string StrSQl_2, ref string StrSQl_3,
string FieldsPagina, string FieldsSequence,
string FoxFile, string FieldsCurstage,
string SetCurstage, string FieldsBatchnum,
ref ComboBox cmbBatch, string FieldsExprunid,
string FieldsProvNum, string FieldsDateFrom,
string FieldsInsured, string FieldsFilePath, string FieldsIfName, string FieldsCpt, string FieldsPin)
{
System.Data.OleDb.OleDbConnection _lcFoxCnn = null;
System.Data.OleDb.OleDbCommand _lcFoxCmd = null;
System.Data.OleDb.OleDbDataReader _lcFoxDataReader = null;
int _lcIntTotalRecFox = 0;
int _lcIntTotalRecPendienteFox = 0;
int _lcIntTotalRecErrorPagina = 0;
int _lcIntTotalRecErrorProv = 0; // campo 25
int _lcIntTotalRecErrorInsurade = 0;
int _lcIntTotalRecErrorCpt = 0;
int _lcIntTotalRecErrorPin = 0; // campo 33
myData[] _lcDataErrorPagina = null;
myData[] _lcDataErrorProvNum = null;
myData[] _lcDataErrorInsured = null;
myData[] _lcDataErrorCpt = null;
myData[] _lcDataErrorPin = null;
bool _lcIsPaginaError = false;
bool _lcIsProvError = false;
bool _lcIsSuscError = false;
bool _lcIsCptError = false;
bool _lcIsPinError = false;
/////////
try
{
_lcFoxCnn = new System.Data.OleDb.OleDbConnection();
_lcFoxCmd = new System.Data.OleDb.OleDbCommand();
_lcFoxCnn.ConnectionString = this._lgbFoxConnectionString;
if (_lcFoxCnn.State == System.Data.ConnectionState.Closed)
{
_lcFoxCnn.Open();
}
////////////////////
StrSQl_Count = "Select count(*) as Total_record from " + FoxFile; // Para el total de imagenes.
StrSQl_1 = "Select count(*) as Total from " + FoxFile + " where not " + FieldsCurstage + "='" + SetCurstage + "' and " + FieldsBatchnum + "='" + cmbBatch.SelectedItem.ToString() + "'"; //Cuental los Batch Que Hay Diferente de V0 --
StrSQl_2 = "Select * from " + FoxFile + " where " + FieldsBatchnum + "='" + cmbBatch.SelectedItem.ToString() + "' and " + FieldsPagina + "='99' or " + FieldsPagina + "=' ' order by " + FieldsSequence;
StrSQl_3 = "Select * from " + FoxFile + " where " + FieldsBatchnum + "='" + cmbBatch.SelectedItem.ToString() + "' and not " + FieldsExprunid + "='VR' and not " + FieldsPagina + "='99' order by " + FieldsSequence;
//////////////////Set _lcFoxCmd //////////////////////////
_lcFoxCmd.Connection = _lcFoxCnn;
_lcFoxCmd.CommandType = System.Data.CommandType.Text;
//////////////////Get Total de Imagenes //////////////////
_lcFoxCmd.CommandText = StrSQl_Count;
_lcIntTotalRecFox = Convert.ToInt32(_lcFoxCmd.ExecuteScalar());
if (_lcIntTotalRecFox <= 0)
{
throw new Exception("No Hay datos para el Quality");
}
//////////////////Get Total de Record Pendiente//////////
_lcFoxCmd.CommandText = StrSQl_1;
_lcIntTotalRecPendienteFox = Convert.ToInt32(_lcFoxCmd.ExecuteScalar());
if (_lcIntTotalRecPendienteFox > 0)
{
throw new Exception("Hay " + _lcIntTotalRecPendienteFox + " Record Pendientes...");
}
///////////////////////Cuando hay un error con el campo de la pagina /////
if (this._gblIsPagina)
{
_lcFoxCmd.CommandText = StrSQl_2;
_lcFoxDataReader = _lcFoxCmd.ExecuteReader();
while (_lcFoxDataReader.Read())
{
if ((_lcFoxDataReader[FieldsProvNum].ToString().Trim().Length > 0) || (_lcFoxDataReader[FieldsDateFrom].ToString().Trim().Length > 0)
|| (_lcFoxDataReader[FieldsPagina].ToString().Trim().Length == 0) || (_lcFoxDataReader[FieldsPagina].ToString().Trim() == "99"))
{
_lcIntTotalRecErrorPagina++;
_lcIsPaginaError = true;
}
}
if (_lcIsPaginaError)
{
_lcFoxDataReader.Close();
_lcFoxDataReader = _lcFoxCmd.ExecuteReader();
int intIndex = 0;
_lcDataErrorPagina = new myData[_lcIntTotalRecErrorPagina];
while (_lcFoxDataReader.Read())
{
if ((_lcFoxDataReader[FieldsProvNum].ToString().Trim().Length > 0) || (_lcFoxDataReader[FieldsDateFrom].ToString().Trim().Length > 0)
|| (_lcFoxDataReader[FieldsPagina].ToString().Trim().Length == 0) || (_lcFoxDataReader[FieldsPagina].ToString().Trim() == "99"))
{
_lcDataErrorPagina[intIndex] = new myData();
_lcDataErrorPagina[intIndex].Provnum = _lcFoxDataReader[FieldsProvNum].ToString().Trim();
_lcDataErrorPagina[intIndex].Insured = _lcFoxDataReader[FieldsInsured].ToString().Trim();
_lcDataErrorPagina[intIndex].FilePath = _lcFoxDataReader[FieldsFilePath].ToString().Trim();
_lcDataErrorPagina[intIndex].IfName = _lcFoxDataReader[FieldsIfName].ToString().Trim();
_lcDataErrorPagina[intIndex].Pagina = _lcFoxDataReader[FieldsPagina].ToString().Trim();
_lcDataErrorPagina[intIndex].BatchNum = _lcFoxDataReader[FieldsBatchnum].ToString().Trim();
_lcDataErrorPagina[intIndex].FieldsName = FieldsPagina;
_lcDataErrorPagina[intIndex].Label = _gblLabelPagina;
intIndex++;
}
}
}
_lcFoxDataReader.Close();
}// end if (this._gblIsPagina)
///////////////////////////todo los reclamaciones///////////////////////////////////////////////////
_lcFoxCmd.CommandText = StrSQl_3;
_lcFoxDataReader = _lcFoxCmd.ExecuteReader();
if (_lcFoxDataReader.HasRows)
{
while (_lcFoxDataReader.Read())
{
if (this._gblIsProvedor)
{
if (IsChkProv(_lcFoxDataReader[FieldsProvNum].ToString(), this._gblSqlTblProv)) // verifico el provedor
{
_lcIntTotalRecErrorProv++; // contabilizo los provdores con problemas
_lcIsProvError = true;
}
}
if (_gblIsAsegurado)
{
if (IsChkInsured(_lcFoxDataReader[FieldsInsured].ToString())) // verifico el insured
{
_lcIntTotalRecErrorInsurade++; // contbilizo los insured con problemas
_lcIsSuscError = true;
}
}
if (_gblIsCpt)
{
string view = null;
for (int i = _gblStartView; i <= _gblLastView; i++)
{
view = getView(i);
string FieldsView = "v" + view.ToString() + FieldsCpt;
if (IsChkCpt(_lcFoxDataReader[FieldsView].ToString()))
{
_lcIntTotalRecErrorCpt++; //contabilizo problemas con el cpt
_lcIsCptError = true;
}
}
}
if (_gblIsPin)
{
if (IsChkProv(_lcFoxDataReader[FieldsPin].ToString(), this._gblTblPin)) // verifico el provedor
{
_lcIntTotalRecErrorPin++; // contabilizo los provdores con problemas
_lcIsPinError = true;
}
}
}// end while (_lcFoxDataReader.Read())
if (_lcIsProvError || _lcIsSuscError || _lcIsCptError || _lcIsPinError)
{
int intIndexProvedor = 0;
int intIndexPin = 0;
int intIndexAsegurado = 0;
int intIndexCpt = 0;
if (_lcIsSuscError) { _lcDataErrorInsured = new myData[_lcIntTotalRecErrorInsurade]; }
if (_lcIsProvError) { _lcDataErrorProvNum = new myData[_lcIntTotalRecErrorProv]; }
if (_lcIsCptError) { _lcDataErrorCpt = new myData[_lcIntTotalRecErrorCpt]; }
if (_lcIsPinError) { _lcDataErrorPin = new myData[_lcIntTotalRecErrorPin]; }
_lcFoxDataReader.Close();
_lcFoxCmd.CommandText = StrSQl_3;
_lcFoxDataReader = _lcFoxCmd.ExecuteReader();
while (_lcFoxDataReader.Read()) // paso el record con problema a un Arrey
{
if (_lcIsProvError)
{
if (IsChkProv(_lcFoxDataReader[FieldsProvNum].ToString(), this._gblSqlTblProv))
{
_lcDataErrorProvNum[intIndexProvedor] = new myData();
_lcDataErrorProvNum[intIndexProvedor].Provnum = _lcFoxDataReader[FieldsProvNum].ToString().Trim();
_lcDataErrorProvNum[intIndexProvedor].Insured = _lcFoxDataReader[FieldsInsured].ToString().Trim();
_lcDataErrorProvNum[intIndexProvedor].FilePath = _lcFoxDataReader[FieldsFilePath].ToString().Trim();
_lcDataErrorProvNum[intIndexProvedor].IfName = _lcFoxDataReader[FieldsIfName].ToString().Trim();
_lcDataErrorProvNum[intIndexProvedor].Pagina = _lcFoxDataReader[FieldsPagina].ToString().Trim();
_lcDataErrorProvNum[intIndexProvedor].BatchNum = _lcFoxDataReader[FieldsBatchnum].ToString().Trim();
_lcDataErrorProvNum[intIndexProvedor].FieldsName = FieldsProvNum;
_lcDataErrorProvNum[intIndexProvedor].Label = _gblLabelProv;
intIndexProvedor++;
}
}
/////////////////////////////////////////////////////////////////
if (_lcIsPinError)
{
if (IsChkProv(_lcFoxDataReader[FieldsPin].ToString(), this._gblTblPin))
{
_lcDataErrorPin[intIndexPin] = new myData();
_lcDataErrorPin[intIndexPin].Pin = _lcFoxDataReader[FieldsPin].ToString().Trim();
_lcDataErrorPin[intIndexPin].Provnum = _lcFoxDataReader[FieldsProvNum].ToString().Trim();
_lcDataErrorPin[intIndexPin].Insured = _lcFoxDataReader[FieldsInsured].ToString().Trim();
_lcDataErrorPin[intIndexPin].FilePath = _lcFoxDataReader[FieldsFilePath].ToString().Trim();
_lcDataErrorPin[intIndexPin].IfName = _lcFoxDataReader[FieldsIfName].ToString().Trim();
_lcDataErrorPin[intIndexPin].Pagina = _lcFoxDataReader[FieldsPagina].ToString().Trim();
_lcDataErrorPin[intIndexPin].BatchNum = _lcFoxDataReader[FieldsBatchnum].ToString().Trim();
_lcDataErrorPin[intIndexPin].FieldsName = FieldsPin;
_lcDataErrorPin[intIndexPin].Label = _gblLabelPin;
intIndexPin++;
}
}
////////////////////////////////////////////////////////////
if (_lcIsSuscError)
{
if (IsChkInsured(_lcFoxDataReader[FieldsInsured].ToString())) // verifico el insured
{
_lcDataErrorInsured[intIndexAsegurado] = new myData();
_lcDataErrorInsured[intIndexAsegurado].Provnum = _lcFoxDataReader[FieldsProvNum].ToString().Trim();
_lcDataErrorInsured[intIndexAsegurado].Insured = _lcFoxDataReader[FieldsInsured].ToString().Trim();
_lcDataErrorInsured[intIndexAsegurado].FilePath = _lcFoxDataReader[FieldsFilePath].ToString().Trim();
_lcDataErrorInsured[intIndexAsegurado].IfName = _lcFoxDataReader[FieldsIfName].ToString().Trim();
_lcDataErrorInsured[intIndexAsegurado].Pagina = _lcFoxDataReader[FieldsPagina].ToString().Trim();
_lcDataErrorInsured[intIndexAsegurado].BatchNum = _lcFoxDataReader[FieldsBatchnum].ToString().Trim();
_lcDataErrorInsured[intIndexAsegurado].FieldsName = FieldsInsured;
_lcDataErrorInsured[intIndexAsegurado].Label = _gblLabelAsegurado;
intIndexAsegurado++;
}
}
//////////////////////////////////////////////////////////////
if (_lcIsCptError)
{
string view = null;
int intQueView = 0;
for (int i = _gblStartView; i <= _gblLastView; i++)
{
intQueView++;
view = getView(i);
string FieldsView = "v" + view.ToString() + FieldsCpt;
if (IsChkCpt(_lcFoxDataReader[FieldsView].ToString())) // verifico el cpt
{
_lcDataErrorCpt[intIndexCpt] = new myData();
_lcDataErrorCpt[intIndexCpt].Provnum = _lcFoxDataReader[FieldsProvNum].ToString().Trim();
_lcDataErrorCpt[intIndexCpt].Insured = _lcFoxDataReader[FieldsInsured].ToString().Trim();
_lcDataErrorCpt[intIndexCpt].FilePath = _lcFoxDataReader[FieldsFilePath].ToString().Trim();
_lcDataErrorCpt[intIndexCpt].IfName = _lcFoxDataReader[FieldsIfName].ToString().Trim();
_lcDataErrorCpt[intIndexCpt].Pagina = _lcFoxDataReader[FieldsPagina].ToString().Trim();
_lcDataErrorCpt[intIndexCpt].BatchNum = _lcFoxDataReader[FieldsBatchnum].ToString().Trim();
_lcDataErrorCpt[intIndexCpt].Cpt = _lcFoxDataReader[FieldsView].ToString().Trim();
_lcDataErrorCpt[intIndexCpt].FieldsName = FieldsView;
_lcDataErrorCpt[intIndexCpt].QueView = intQueView.ToString();
_lcDataErrorCpt[intIndexCpt].Label = _gblLabelCpt;
intIndexCpt++;
}
}
}
}
}
}
_lcFoxDataReader.Close();
////////////////////////////////////////////////////
if (_lcIsPaginaError) // muestra errores con la pagina
{
int _lcTotal = _lcIntTotalRecErrorPagina;
foreach (myData Data in _lcDataErrorPagina)
{
frmQuality2004ViewImg frmPaginaError = new frmQuality2004ViewImg("Error", _gblSetPGLeft, _gblSetPGTop, _gblSetPGWidth, _gblSetPGHeight, _gblSetPGZoom);
frmPaginaError.Text = Data.Label;
frmPaginaError._txtTmp.Clear();
frmPaginaError._txtBatch.Text = Data.BatchNum;
frmPaginaError._lblNombre.Text = Data.Label;
frmPaginaError._txtTmp.Text = Data.Pagina;
frmPaginaError._txtImgNum.Text = _lcTotal.ToString();
frmPaginaError._txtTotal.Text = _lcIntTotalRecErrorPagina.ToString();
frmPaginaError._txtImgInfo.Text = this._gblLabel.ToUpper();
if (_gblImgPath.Trim().Length == 0)
{
frmPaginaError._txtImgPath.Text = Data.FilePath + Data.IfName;
}
else
{
frmPaginaError._txtImgPath.Text = _gblImgPath + Data.IfName;
}
frmPaginaError.ShowDialog();
if (frmPaginaError.IsCancel)
{
break;
}
_lcTotal--;
//if (MessageBox.Show("Are you sure whant to update?" + Data.FilePath + Data.IfName ,"Applica,Inc.",MessageBoxButtons.YesNo,MessageBoxIcon.Question) == DialogResult.Yes)
//{
update(_gblFoxFile, _gblFieldsExprunid, _gblFieldsImg, Data.IfName, FieldsPagina, frmPaginaError._txtTmp.Text);
//}
}
}
if (_lcIsProvError) // muestra los errores con el provedor
{
int _lcTotal = _lcIntTotalRecErrorProv;
foreach (myData Data in _lcDataErrorProvNum)
{
frmQuality2004ViewImg frmProvError = new frmQuality2004ViewImg("Error", _gblSetProvLeft, _gblSetProvTop, _gblSetProvWidth, _gblSetProvHeight, _gblSetProvZoom);
frmProvError.Text = Data.Label;
frmProvError._txtTmp.Clear();
frmProvError._txtBatch.Text = Data.BatchNum;
frmProvError._lblNombre.Text = Data.Label;
frmProvError._txtTmp.Text = Data.Provnum;
frmProvError._txtImgNum.Text = _lcTotal.ToString();
frmProvError._txtTotal.Text = _lcIntTotalRecErrorProv.ToString();
frmProvError._txtImgInfo.Text = this._gblLabel.ToUpper();
if (_gblImgPath.Trim().Length == 0)
{
frmProvError._txtImgPath.Text = Data.FilePath + Data.IfName;
}
else
{
frmProvError._txtImgPath.Text = _gblImgPath + Data.IfName;
}
frmProvError.ShowDialog();
if (frmProvError.IsCancel)
{
break;
}
_lcTotal--;
//if (MessageBox.Show("Are you sure whant to update? " + Data.FilePath + Data.IfName ,"Applica,Inc.",MessageBoxButtons.YesNo,MessageBoxIcon.Question) == DialogResult.Yes)
//{
update(_gblFoxFile, _gblFieldsExprunid, _gblFieldsImg, Data.IfName, FieldsProvNum, frmProvError._txtTmp.Text);
//}
}
}
/////////////////////////////////////////////////////////////////
if (_lcIsPinError) // muestra los errores con el provedor
{
int _lcTotal = _lcIntTotalRecErrorPin;
foreach (myData Data in _lcDataErrorPin)
{
frmQuality2004ViewImg frmPinError = new frmQuality2004ViewImg("Error", _gblSetPinLeft, _gblSetPinTop, _gblSetPinWidth, _gblSetPinHeight, _gblSetPinZoom);
frmPinError.Text = Data.Label;
frmPinError._txtTmp.Clear();
frmPinError._txtBatch.Text = Data.BatchNum;
frmPinError._lblNombre.Text = Data.Label;
frmPinError._txtTmp.Text = Data.Pin;
frmPinError._txtImgNum.Text = _lcTotal.ToString();
frmPinError._txtTotal.Text = _lcIntTotalRecErrorPin.ToString();
frmPinError._txtImgInfo.Text = this._gblLabel.ToUpper();
if (_gblImgPath.Trim().Length == 0)
{
frmPinError._txtImgPath.Text = Data.FilePath + Data.IfName;
}
else
{
frmPinError._txtImgPath.Text = _gblImgPath + Data.IfName;
}
frmPinError.ShowDialog();
if (frmPinError.IsCancel)
{
break;
}
_lcTotal--;
//if (MessageBox.Show("Are you sure whant to update? " + Data.FilePath + Data.IfName ,"Applica,Inc.",MessageBoxButtons.YesNo,MessageBoxIcon.Question) == DialogResult.Yes)
//{
update(_gblFoxFile, _gblFieldsExprunid, _gblFieldsImg, Data.IfName, FieldsPin, frmPinError._txtTmp.Text);
//}
}
}
/////////////////////////////////////////////////////////////////
if (_lcIsSuscError) // muestra los errores con los asegurado
{
int _lcTotal = _lcIntTotalRecErrorInsurade;
foreach (myData Data in _lcDataErrorInsured)
{
frmQuality2004ViewImg frmSuscError = new frmQuality2004ViewImg("Error", _gblSetSucsLeft, _gblSetSucsTop, _gblSetSucsWidth, _gblSetSucsHeight, _gblSetSuscZoom);
frmSuscError.Text = Data.Label;
frmSuscError._txtTmp.Clear();
frmSuscError._txtBatch.Text = Data.BatchNum;
frmSuscError._lblNombre.Text = Data.Label;
frmSuscError._txtTmp.Text = Data.Insured;
frmSuscError._txtImgNum.Text = _lcTotal.ToString();
frmSuscError._txtTotal.Text = _lcIntTotalRecErrorInsurade.ToString();
frmSuscError._txtImgInfo.Text = this._gblLabel.ToUpper();
if (_gblImgPath.Trim().Length == 0)
{
frmSuscError._txtImgPath.Text = Data.FilePath + Data.IfName;
}
else
{
frmSuscError._txtImgPath.Text = _gblImgPath + Data.IfName;
}
frmSuscError.ShowDialog();
if (frmSuscError.IsCancel)
{
break;
}
_lcTotal--;
//if (MessageBox.Show("Are you sure whant to update?" + Data.FilePath + Data.IfName ,"Applica,Inc.",MessageBoxButtons.YesNo,MessageBoxIcon.Question) == DialogResult.Yes)
//{
update(_gblFoxFile, _gblFieldsExprunid, _gblFieldsImg, Data.IfName, FieldsInsured, frmSuscError._txtTmp.Text);
//}
}
}
if (_lcIsCptError) // muestra los errores con el cpt
{
int _lcTotal = _lcIntTotalRecErrorCpt;
foreach (myData Data in _lcDataErrorCpt)
{
frmQuality2004ViewImg frmCptError = new frmQuality2004ViewImg("Error", _gblSetCptLeft, _gblSetCptTop, _gblSetCptWidth, _gblSetCptHeight, _gblSetCptZoom);
frmCptError.Text = Data.Label;
frmCptError._txtTmp.Clear();
frmCptError._txtBatch.Text = Data.BatchNum;
frmCptError._lblNombre.Text = Data.Label + " Linea #" + Data.QueView;
frmCptError._txtTmp.Text = Data.Cpt;
frmCptError._txtImgNum.Text = _lcTotal.ToString();
frmCptError._txtTotal.Text = _lcIntTotalRecErrorProv.ToString();
frmCptError._txtImgInfo.Text = this._gblLabel.ToUpper();
if (_gblImgPath.Trim().Length == 0)
{
frmCptError._txtImgPath.Text = Data.FilePath + Data.IfName;
}
else
{
frmCptError._txtImgPath.Text = _gblImgPath + Data.IfName;
}
frmCptError.ShowDialog();
if (frmCptError.IsCancel)
{
break;
}
_lcTotal--;
// if (MessageBox.Show("Are you sure whant to update?" + Data.FilePath + Data.IfName ,"Applica,Inc.",MessageBoxButtons.YesNo,MessageBoxIcon.Question) == DialogResult.Yes)
// {
update(_gblFoxFile, _gblFieldsExprunid, _gblFieldsImg, Data.IfName, Data.FieldsName, frmCptError._txtTmp.Text);
// }
}
}
//////////////////////////////////////////////////////////////////////////////
MessageBox.Show("Total de Imagenes:" + _lcIntTotalRecFox.ToString()
+ " Total de Record Pendiente:" + _lcIntTotalRecPendienteFox.ToString()
+ " Total de Record Con error en la " + _gblLabelPagina + ":" + _lcIntTotalRecErrorPagina.ToString()
+ " Total de Record Con error en el " + _gblLabelAsegurado + ":" + _lcIntTotalRecErrorInsurade
+ " Total de Record Con error en el " + _gblLabelProv + ":" + _lcIntTotalRecErrorProv
+ " Total de Record Con error en el " + _gblLabelCpt + ":" + _lcIntTotalRecErrorCpt
+ " Total de Record Con error en el " + _gblLabelPin + ":" + _lcIntTotalRecErrorPin, "Totales");
MessageBox.Show("Finalizado...", "Applica, Inc.", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
throw new Exception(ex.ToString() + " -- CheckBatch -- ");
}
finally
{
if (_lcFoxCnn.State == System.Data.ConnectionState.Open)
{
_lcFoxCnn.Close();
}
_lcFoxCmd = null;
_lcFoxCnn = null;
}
}
