public void SelectBlob(MySQLConnection con)
{
try
{
con.Open();
MySQLCommand cmd = new MySQLCommand("select col1, col2 from `trntipos` where col1=3", con); //, `SerialNumberLastUsed`
cmd.UsePreparedStatement = true;
cmd.ServerCursor = true;
cmd.Prepare();
MySQLDataReader reader = (MySQLDataReader)cmd.ExecuteReader();
while (reader.Read())
{
getBLOBFile("output.jpg", reader, 1);
}
reader.Close();
cmd.Dispose();
con.Close();
}
catch (Exception e)
{
if (con != null)
{
con.Close();
}
throw e;
}
}
public int ExecuteNonQueryForInc(string commandStr)
{
connection.Open();
MySQLCommand cmdTemp1 = (MySQLCommand)connection.CreateCommand();
cmdTemp1.CommandText = "set names gbk";
cmdTemp1.ExecuteNonQuery();
command = (MySQLCommand)connection.CreateCommand();
command.CommandText = commandStr;
command.ExecuteNonQuery();
MySQLCommand cmdTemp2 = (MySQLCommand)connection.CreateCommand();
cmdTemp2.CommandText = "select last_insert_id()";
MySQLDataReader readerTemp;
readerTemp = (MySQLDataReader)cmdTemp2.ExecuteReader();
int LastID = -1;
while (readerTemp.Read())
{
LastID = readerTemp.GetInt16(0);
}
return(LastID);
}
private void ButtonCheckIn_Click(object sender, EventArgs e)
{
MySQLConnection SQLconnection = new MySQLConnection(new MySQLConnectionString
("localhost", "DormitoryManage", "root", "123456").AsString);
string SQLstr = "SELECT checkInTime,isChangeRoom FROM check_in WHERE studentNumber = " + PublicValue.STUNUM;
SQLconnection.Open();
MySQLCommand SQLcommand1 = new MySQLCommand("SET NAMES GB2312", SQLconnection);
SQLcommand1.ExecuteNonQuery(); //执行设置字符集的语句
MySQLCommand SQLcommand2 = new MySQLCommand(SQLstr, SQLconnection);
MySQLDataReader SQLreader = (MySQLDataReader)SQLcommand2.ExecuteReader();
if (SQLreader.Read())
{
string tempa = SQLreader["checkInTime"].ToString();
string tempb = SQLreader["isChangeRoom"].ToString();
if (tempb == "是")
{
MessageBox.Show(" 入住时间：" + tempa + "\n 有换过寝室", "入住信息");
}
if (tempb == "否")
{
MessageBox.Show(" 入住时间：" + tempa + "\n 没有换过寝室", "入住信息");
}
}
SQLconnection.Close();
}
public int AccountRequest(string pwd)
{
try {
con.Open();
sql = "INSERT INTO users(user_password) VALUES(" + pwd + ")";
MySQLCommand com = new MySQLCommand(sql, con);
com.ExecuteNonQuery();
sql = "SELECT LAST_INSERT_ID()";
com = new MySQLCommand(sql, con);
DbDataReader reader = com.ExecuteReader();
int user_id = 0;
if(reader.Read())
{
user_id = Int32.Parse(reader["user_id"].ToString());
}
sql = "INSERT INTO users_information VALUES(null,null,null,null)";
com = new MySQLCommand(sql, con);
com.ExecuteNonQuery();
return user_id;
}
catch { return 0; }
finally
{
con.Close();
}
}
public AccountInfo AccountInfoReader(int user_id)
{
try {
con.Open();
sql = "SELECT* FROM users_information WHERE user_id = " + user_id;
MySQLCommand com = new MySQLCommand(sql, con);
DbDataReader reader = com.ExecuteReader();
if(reader.Read())
{
AccountInfo accountInfo = new AccountInfo();
accountInfo.Sex = short.Parse(reader["user_sex"].ToString());
accountInfo.Birthday = DateTime.Parse(reader["user_birthday"].ToString());
accountInfo.Phone = reader["uesr_phome"].ToString();
return accountInfo;
}
return null;
}
catch
{
return null;
}
finally
{
con.Close();
}
}
public GxMySQLDriverCSCursorDataReader(IGxConnectionManager connManager, GxDataRecord dr, IGxConnection connection, GxParameterCollection parameters,
string stmt, int fetchSize, bool forFirst, int handle, bool cached, SlidingTime expiration, bool hasNested, bool dynStmt)
{
this.parameters = parameters;
this.stmt = stmt;
this.fetchSize = fetchSize;
this.cache = connection.ConnectionCache;
this.cached = cached;
this.handle = handle;
this.isForFirst = forFirst;
_connManager = connManager;
this.m_dr = dr;
this.readBytes = 0;
this.dynStmt = dynStmt;
con = _connManager.IncOpenHandles(handle, m_dr.DataSource);
con.CurrentStmt = stmt;
con.MonitorEnter();
GXLogging.Debug(log, "Open GxMySQLCursorDataReader handle:'" + handle);
MySQLCommand cmd = (MySQLCommand)dr.GetCommand(con, stmt, parameters);
cmd.ServerCursor = hasNested;
cmd.FetchSize = (uint)fetchSize;
reader = cmd.ExecuteReader();
cache.SetAvailableCommand(stmt, false, dynStmt);
open = true;
block = new GxArrayList(fetchSize);
pos = -1;
if (cached)
{
key = SqlUtil.GetKeyStmtValues(parameters, stmt, isForFirst);
this.expiration = expiration;
}
}
private void ButtonMagInfo_Click(object sender, EventArgs e)
{
MySQLConnection SQLconnection = new MySQLConnection(new MySQLConnectionString
("localhost", "DormitoryManage", "root", "123456").AsString);
string SQLstr = "SELECT * FROM manager_info WHERE managerNumber = " + PublicValue.MAGNUM;
SQLconnection.Open();
MySQLCommand SQLcommand1 = new MySQLCommand("SET NAMES GB2312", SQLconnection);
SQLcommand1.ExecuteNonQuery(); //执行设置字符集的语句
MySQLCommand SQLcommand2 = new MySQLCommand(SQLstr, SQLconnection);
MySQLDataReader SQLreader = (MySQLDataReader)SQLcommand2.ExecuteReader();
if (SQLreader.Read())
{
this.TextBox1.Text = SQLreader["managerNumber"].ToString();
this.TextBox2.Text = SQLreader["managerName"].ToString();
this.TextBox3.Text = SQLreader["managerGender"].ToString();
this.TextBox4.Text = SQLreader["managerAge"].ToString();
this.TextBox5.Text = SQLreader["dormitoryNumber"].ToString();
this.TextBox6.Text = SQLreader["managerPosition"].ToString();
this.TextBox7.Text = SQLreader["managerPhone"].ToString();
this.TextBox8.Text = SQLreader["managerPassword"].ToString();
}
SQLconnection.Close();
}
public void ExecuteDataReaderForAnyFieldTypeWithPreparedStatement(PreparedFieldTypeTest test)
{
var mySqlTypeDeclaration = test.Declaration;
var inputParameter = test.Parameter;
var expectedValue = test.Value;
var c = CachedConnection;
using (var cmd1 = new MySQLCommand("DROP TABLE IF EXISTS number_type_test", c))
cmd1.ExecuteNonQuery();
using (var cmd2 = new MySQLCommand($@"CREATE TABLE number_type_test ( id INT NOT NULL,COL_VALUE {mySqlTypeDeclaration},PRIMARY KEY (id));", c))
cmd2.ExecuteNonQuery();
using (var cmd3 = new MySQLCommand($@"INSERT INTO number_type_test ( id ,COL_VALUE)values(0, ?)", c))
{
cmd3.UsePreparedStatement = true;
cmd3.Parameters.Add(inputParameter);
cmd3.ExecuteNonQuery();
}
using (var cmd = new MySQLCommand("select id, COL_VALUE from number_type_test where id=?", c))
{
cmd.UsePreparedStatement = true;
var p = new MySQLParameter("id", MySQLDbType.Int);
p.Value = 0;
cmd.Parameters.Add(p);
var reader = cmd.ExecuteReader();
Assert.True(reader.Read());
var ordinal = reader.GetOrdinal("COL_VALUE");
var executeScalar = reader.GetValue(ordinal);
Assert.NotNull(executeScalar);
if (expectedValue == null)
{
Assert.Null(executeScalar);
}
else
{
Assert.NotNull(executeScalar);
Assert.IsType(expectedValue.GetType(), executeScalar);
if (expectedValue is string)
{
expectedValue = "'" + expectedValue.ToString() + "'";
}
if (executeScalar is string)
{
executeScalar = "'" + executeScalar.ToString() + "'";
}
Assert.Equal(expectedValue, executeScalar);
}
Assert.False(reader.Read());
}
}
private void ButtonOK_Click(object sender, EventArgs e)
{
if (TextBox7.Text == "" || TextBox8.Text == "")
{
if (TextBox7.Text == "")
{
labelN1.Text = "输入不能未空";
}
else
{
labelN1.Text = " ";
}
if (TextBox8.Text == "")
{
labelN2.Text = "输入不能未空";
}
else
{
labelN2.Text = " ";
}
}
else
{
labelN1.Text = " ";
labelN2.Text = " ";
int flag = 0;
string tempa = TextBox7.Text;
string tempb = TextBox8.Text;
MySQLConnection SQLconnection = new MySQLConnection(new MySQLConnectionString
("localhost", "DormitoryManage", "root", "123456").AsString);
string SQLstr1 = "SELECT * FROM manager_info WHERE managerNumber = " + PublicValue.MAGNUM;
string SQLstr2 = "UPDATE manager_info set managerPhone = '" + TextBox7.Text + "',managerPassword = '******'";
SQLconnection.Open();
MySQLCommand SQLcommand1 = new MySQLCommand("SET NAMES GB2312", SQLconnection);
SQLcommand1.ExecuteNonQuery(); //执行设置字符集的语句
MySQLCommand SQLcommand2 = new MySQLCommand(SQLstr1, SQLconnection);
MySQLDataReader SQLreader = (MySQLDataReader)SQLcommand2.ExecuteReader();
if (SQLreader.Read())
{
string oldtempa = SQLreader["managerPhone"].ToString();
string oldtempb = SQLreader["managerPassword"].ToString();
if (tempa != oldtempa || tempb != oldtempb)
{
flag = 1;
}
}
if (flag == 1)
{
MySQLCommand SQLcommand3 = new MySQLCommand(SQLstr2, SQLconnection);
SQLcommand3.ExecuteNonQuery();
SQLconnection.Close();
MessageBox.Show("修改成功", "提示");
}
}
}
public int TestSelect(MySQLConnection con, bool useNew)
{
int affectedrows = 0;
try
{
con.Open();
MySQLCommand cmd = new MySQLCommand("SELECT col1, col2, col3, col4 FROM `test_table`", con);
if (useNew)
{
cmd.UsePreparedStatement = true;
cmd.ServerCursor = true;
cmd.Prepare();
}
IDataReader reader = cmd.ExecuteReader();
MySQLCommand cmd1 = new MySQLCommand("SELECT col1, col2, col3, col4 FROM `test_table`", con);
if (useNew)
{
cmd1.UsePreparedStatement = true;
//cmd.ServerCursor = true;
cmd.FetchSize = 10;
cmd1.Prepare();
}
while (reader.Read())
{
affectedrows++;
IDataReader reader1 = cmd1.ExecuteReader();
while (reader1.Read())
{
affectedrows++;
long s1 = reader1.GetInt64(0);
string st1 = reader1.GetString(1);
DateTime dt1 = reader1.GetDateTime(3);
}
reader1.Close();
long s = reader.GetInt64(0);
string st = reader.GetString(1);
DateTime dt = reader.GetDateTime(3);
}
reader.Close();
cmd.Dispose();
con.Close();
return(affectedrows);
}
catch (Exception e)
{
if (con != null)
{
con.Close();
}
throw e;
}
}
public void PreparedStatementOfBlobIsInserted()
{
var c = CachedConnection;
using (var cmd1 = new MySQLCommand("DROP TABLE IF EXISTS number_type_test", c))
cmd1.ExecuteNonQuery();
using (var cmd2 = new MySQLCommand($@"CREATE TABLE number_type_test ( id INT NOT NULL,COL_VALUE BLOB,PRIMARY KEY (id));", c))
cmd2.ExecuteNonQuery();
using (var cmd3 = new MySQLCommand($@"INSERT INTO number_type_test ( id ,COL_VALUE)values(0, ?)", c))
{
cmd3.UsePreparedStatement = true;
cmd3.Parameters.Add(new MySQLParameter("", MySQLDbType.Binary, ParameterDirection.Input, Encoding.UTF8.GetBytes("sure.")));
cmd3.ExecuteNonQuery();
}
var expectedValue = "sure.";
using (var cmd = new MySQLCommand("select id, to_base64(COL_VALUE) from number_type_test where id=0", c))
{
cmd.UsePreparedStatement = false;
var reader = cmd.ExecuteReader();
Assert.True(reader.Read());
var ordinal = 1;
var xx = reader.GetValue(ordinal);
Assert.IsType <string>(xx);
var executeScalar = Encoding.UTF8.GetString(Convert.FromBase64String((string)xx));
Assert.NotNull(executeScalar);
if (expectedValue == null)
{
Assert.Null(executeScalar);
}
else
{
Assert.IsType(expectedValue.GetType(), executeScalar);
if (expectedValue is string)
{
expectedValue = "'" + expectedValue.ToString() + "'";
}
if (executeScalar is string)
{
executeScalar = "'" + executeScalar.ToString() + "'";
}
Assert.Equal(expectedValue, executeScalar);
}
Assert.False(reader.Read());
}
}
public void PreparedStatementOfBlobIsFetched()
{
var expectedValue = "sure.";
var c = CachedConnection;
using (var cmd1 = new MySQLCommand("DROP TABLE IF EXISTS number_type_test", c))
cmd1.ExecuteNonQuery();
using (var cmd2 = new MySQLCommand($@"CREATE TABLE number_type_test ( id INT NOT NULL,COL_VALUE BLOB,PRIMARY KEY (id));", c))
cmd2.ExecuteNonQuery();
using (var cmd3 = new MySQLCommand($@"INSERT INTO number_type_test ( id ,COL_VALUE)values(0, from_base64('{Convert.ToBase64String(Encoding.UTF8.GetBytes(expectedValue))}'))", c))
{
cmd3.UsePreparedStatement = false;
cmd3.ExecuteNonQuery();
}
using (var cmd = new MySQLCommand("select id, COL_VALUE from number_type_test where id=0", c))
{
cmd.UsePreparedStatement = true;
var reader = cmd.ExecuteReader();
Assert.True(reader.Read());
var ordinal = reader.GetOrdinal("COL_VALUE");
var executeScalar = reader.GetValue(ordinal);
Assert.IsType <byte[]>(executeScalar);
var actualValue = Encoding.UTF8.GetString((byte[])executeScalar);
Assert.NotNull(actualValue);
if (expectedValue == null)
{
Assert.Null(actualValue);
}
else
{
Assert.IsType(expectedValue.GetType(), actualValue);
if (expectedValue is string)
{
expectedValue = "'" + expectedValue.ToString() + "'";
}
if (actualValue is string)
{
actualValue = "'" + actualValue.ToString() + "'";
}
Assert.Equal(expectedValue, actualValue);
}
Assert.False(reader.Read());
}
}
public MySQLDataReader GetDataReader(string commandStr)
{
connection.Open();
MySQLCommand cmdTemp = (MySQLCommand)connection.CreateCommand();
cmdTemp.CommandText = "set names gbk";
cmdTemp.ExecuteNonQuery();
command = (MySQLCommand)connection.CreateCommand();
command.CommandText = commandStr;
dataReader = (MySQLDataReader)command.ExecuteReader();
return(dataReader);
}
public void ExecuteDataReaderForAnyFieldType(string mySqlTypeDeclaration, string insertedLiteral, object expectedValue)
{
var c = CachedConnection;
{
using (var cmd1 = new MySQLCommand("DROP TABLE IF EXISTS number_type_test", c))
cmd1.ExecuteNonQuery();
using (var cmd2 = new MySQLCommand($@"CREATE TABLE number_type_test ( id INT NOT NULL,COL_VALUE {mySqlTypeDeclaration},PRIMARY KEY (id));", c))
cmd2.ExecuteNonQuery();
using (var cmd3 = new MySQLCommand($@"INSERT INTO number_type_test ( id ,COL_VALUE)values(0,{insertedLiteral});", c)) cmd3.ExecuteNonQuery();
using (var cmd = new MySQLCommand("select id, COL_VALUE from number_type_test where id=0", c))
{
var reader = cmd.ExecuteReader();
Assert.True(reader.Read());
{
var ordinal = reader.GetOrdinal("COL_VALUE");
var executeScalar = reader.GetValue(ordinal);
Assert.NotNull(executeScalar);
if (expectedValue == null)
{
Assert.Null(executeScalar);
}
else
{
Assert.NotNull(executeScalar);
Assert.IsType(expectedValue.GetType(), executeScalar);
if (expectedValue is string)
{
expectedValue = "'" + expectedValue.ToString() + "'";
}
if (executeScalar is string)
{
executeScalar = "'" + executeScalar.ToString() + "'";
}
Assert.Equal(expectedValue, executeScalar);
}
}
Assert.False(reader.Read());
}
}
}
public void Test(MySQLConnection con)
{
try
{
con.Open();
MySQLCommand cmd = new MySQLCommand("SELECT col1, col2, col3, col4 FROM `test_table`", con); //, `SerialNumberLastUsed`
cmd.UsePreparedStatement = true;
cmd.ServerCursor = true;
cmd.Prepare();
MySQLCommand cmd1 = new MySQLCommand("SELECT col1, col2 FROM `test_table`", con); //SerialNumberID
IDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
IDataReader reader1 = cmd1.ExecuteReader();
while (reader1.Read())
{
Console.Write(reader1.GetInt32(0) + " ");
Console.WriteLine(reader1.GetString(1));
}
reader1.Close();
Console.Write(reader.GetInt32(0) + " ");
Console.WriteLine(reader.GetString(1));
Console.WriteLine(reader.GetDateTime(3));
Console.WriteLine(reader.IsDBNull(2));
Console.WriteLine(reader.GetInt16(2));
Console.WriteLine(reader.IsDBNull(0));
}
reader.Close();
cmd1.Dispose();
cmd.Dispose();
con.Close();
}
catch (Exception e)
{
if (con != null)
{
con.Close();
}
throw e;
}
}
public ManagerHost()
{
InitializeComponent();
MySQLConnection SQLconnection = new MySQLConnection(new MySQLConnectionString
("localhost", "DormitoryManage", "root", "123456").AsString);
string SQLstr = "SELECT managerName FROM manager_info where managerNumber = " + PublicValue.MAGNUM;
SQLconnection.Open();
MySQLCommand SQLcommand1 = new MySQLCommand("SET NAMES GB2312", SQLconnection);
SQLcommand1.ExecuteNonQuery(); //执行设置字符集的语句
MySQLCommand SQLcommand2 = new MySQLCommand(SQLstr, SQLconnection);
MySQLDataReader SQLreader = (MySQLDataReader)SQLcommand2.ExecuteReader();
if (SQLreader.Read())
{
string temp = SQLreader["managerName"].ToString();
this.Text = temp + "（公寓管理员）的主页";
}
SQLconnection.Close();
}
public void ExecuteStoredProcedureThatProducesQueryWithArg()
{
using (var cmd = new MySQLCommand(@"DROP FUNCTION IF EXISTS hello", this.CachedConnection))
{
cmd.ExecuteNonQuery();
}
using (var cmd = new MySQLCommand(@"CREATE FUNCTION hello (s CHAR(20)) RETURNS CHAR(50) DETERMINISTIC RETURN CONCAT('Hello, ',s,'!')", this.CachedConnection))
{
cmd.ExecuteNonQuery();
}
using (var cmd = new MySQLCommand(@"select hello(?)", this.CachedConnection))
{
cmd.Parameters.Add(new MySQLParameter(MySQLDbType.String, ParameterDirection.Input)).Value = "name";
using (var dr = cmd.ExecuteReader())
{
Assert.True(dr.Read());
Assert.Equal("Hello, " + "name" + "!", dr.GetString(0));
Assert.True(!dr.Read());
}
}
}
public bool Login(LoginData data)
{
try
{
con.Open();
sql = "SELECT user_password from users where user_id = " + data.User_id + "";
MySQLCommand com = new MySQLCommand(sql, con);
DbDataReader reader = com.ExecuteReader();
if (reader.Read())
if (reader["user_password"].ToString() == data.User_password)
return true;
return false;
}
catch
{
return false;
}
finally
{
con.Close();
}
}
public void PreparedStatementOfBitIsFetched()
{
var expectedValue = (ulong)1;
var c = CachedConnection;
using (var cmd1 = new MySQLCommand("DROP TABLE IF EXISTS number_type_test", c))
cmd1.ExecuteNonQuery();
using (var cmd2 = new MySQLCommand($@"CREATE TABLE number_type_test ( id INT NOT NULL,COL_VALUE bit(2),PRIMARY KEY (id));", c))
cmd2.ExecuteNonQuery();
using (var cmd3 = new MySQLCommand($@"INSERT INTO number_type_test ( id ,COL_VALUE)values(0, B'01')", c))
{
cmd3.UsePreparedStatement = false;
cmd3.ExecuteNonQuery();
}
using (var cmd = new MySQLCommand("select id, COL_VALUE from number_type_test where id=0", c))
{
cmd.UsePreparedStatement = true;
var reader = cmd.ExecuteReader();
Assert.True(reader.Read());
var ordinal = reader.GetOrdinal("COL_VALUE");
var executeScalar = reader.GetValue(ordinal);
Assert.IsType <ulong>(executeScalar);
var actualValue = (ulong)executeScalar;
Assert.IsType(expectedValue.GetType(), actualValue);
Assert.Equal(expectedValue, actualValue);
Assert.False(reader.Read());
}
}
public void PreparedStatementOfBitIsInserted()
{
var c = CachedConnection;
using (var cmd1 = new MySQLCommand("DROP TABLE IF EXISTS number_type_test", c))
cmd1.ExecuteNonQuery();
using (var cmd2 = new MySQLCommand($@"CREATE TABLE number_type_test ( id INT NOT NULL,COL_VALUE BIT(2),PRIMARY KEY (id));", c))
cmd2.ExecuteNonQuery();
using (var cmd3 = new MySQLCommand($@"INSERT INTO number_type_test ( id ,COL_VALUE)values(0, ?)", c))
{
cmd3.UsePreparedStatement = true;
cmd3.Parameters.Add(new MySQLParameter("", MySQLDbType.Bit, ParameterDirection.Input, 1));
cmd3.ExecuteNonQuery();
}
var expectedValue = "1";
using (var cmd = new MySQLCommand("select id, HEX(COL_VALUE) from number_type_test where id=0", c))
{
cmd.UsePreparedStatement = false;
var reader = cmd.ExecuteReader();
Assert.True(reader.Read());
var ordinal = 1;
var executeScalar = reader.GetValue(ordinal);
Assert.IsType <string>(executeScalar);
var actualValue = (string)executeScalar;
Assert.NotNull(actualValue);
Assert.IsType(expectedValue.GetType(), actualValue);
Assert.Equal(expectedValue, actualValue);
Assert.False(reader.Read());
}
}
public void ExecuteStoredProcedureThatProducesQuery()
{
using (var cmd = new MySQLCommand(@"drop table if exists products", this.CachedConnection))
{
cmd.ExecuteNonQuery();
}
using (var cmd = new MySQLCommand(@"create table products(id int, name varchar(20), primary key(id))", this.CachedConnection))
{
cmd.ExecuteNonQuery();
}
using (var cmd = new MySQLCommand(@"insert into products(id , name) values ('0','name')", this.CachedConnection))
{
cmd.ExecuteNonQuery();
}
using (var cmd = new MySQLCommand(@"DROP PROCEDURE IF EXISTS simpleproc", this.CachedConnection))
{
cmd.ExecuteNonQuery();
}
using (var cmd = new MySQLCommand(@"CREATE PROCEDURE simpleproc ()
BEGIN
SELECT * from products;
END", this.CachedConnection))
{
cmd.ExecuteNonQuery();
}
using (var cmd = new MySQLCommand(@"CALL simpleproc()", this.CachedConnection))
{
using (var dr = cmd.ExecuteReader())
{
Assert.True(dr.Read());
Assert.Equal(0, dr.GetInt32(0));
Assert.Equal("name", dr.GetString(1));
Assert.True(!dr.Read());
}
}
}
private bool addOneUrl(int pos)
{//添加第pos条团购条目信息
string query;
int link_id;
MD5 md5 = new MD5CryptoServiceProvider();;
//XmlNode oneUrl = lashouDocument.DocumentElement.ChildNodes[pos];
oneUrl oneUrl = xmlparse.getOneUrl(pos);
StatisticOutput sta = staContext.statistic(oneUrl);
Console.WriteLine("Insert the data to the database....");
query = "insert into links (site_id, url, title, description) values(5, \"" +
sta.url + "\",\"" + sta.title + "\",\"" + sta.description + "\")";
cmd = new MySQLDriverCS.MySQLCommand(query, conn);
cmd.ExecuteNonQuery();
cmd.Dispose();
query = "select link_id from links where url = \"" + sta.url + "\"";
cmd = new MySQLDriverCS.MySQLCommand(query, conn);
IDataReader dt = cmd.ExecuteReader();
link_id = Convert.ToInt32(dt.GetSchemaTable().Rows[0]["link_id"]);
foreach (DictionaryEntry de in sta.keywordList)
{
int keyword_id = 0;
query = "select keyword_id from keywords where keyword = \"" + de.Key + "\"";
cmd = new MySQLDriverCS.MySQLCommand(query, conn);
dt = cmd.ExecuteReader();
if (dt.GetSchemaTable().Rows.Count == 0)
{
query = "insert into keywords (keyword) values (\"" + de.Key + "\")";
cmd = new MySQLDriverCS.MySQLCommand(query, conn);
cmd.ExecuteNonQuery();
cmd.Dispose();
query = "select keyword_id from keywords where keyword = \"" + de.Key + "\"";
cmd = new MySQLDriverCS.MySQLCommand(query, conn);
dt = cmd.ExecuteReader();
keyword_id = Convert.ToInt32(dt.GetSchemaTable().Rows[0]["keyword_id"]);
}
else
{
keyword_id = Convert.ToInt32(dt.GetSchemaTable().Rows[0]["keyword_id"]);
}
byte[] hash = md5.ComputeHash(System.Text.Encoding.Default.GetBytes(de.Key.ToString()));
string flag;
switch (hash[0] >> 4)
{
case 10:
flag = "a";
break;
case 11:
flag = "b";
break;
case 12:
flag = "c";
break;
case 13:
flag = "d";
break;
case 14:
flag = "e";
break;
case 15:
flag = "f";
break;
default:
flag = (hash[0] >> 4).ToString();
break;
}
query = "insert into link_keyword" + flag + "(link_id, keyword_id, weight, time, feat, discount) values (" +
link_id + "," + keyword_id + "," + de.Value + "," + sta.time + "," + sta.feat + "," +
sta.discount + ")";
cmd = new MySQLDriverCS.MySQLCommand(query, conn);
cmd.ExecuteNonQuery();
cmd.Dispose();
}
Console.WriteLine("Write one link sucessfully!");
return(true);
}
public ArrayList SearchAccount(SelectAccount selAccount)
{
try
{
//AccountInfo accountInfo = new AccountInfo();
//accountInfo.Sex = short.Parse(reader["user_sex"].ToString());
//accountInfo.Birthday = DateTime.Parse(reader["user_birthday"].ToString());
//accountInfo.Phone = reader["uesr_phome"].ToString();
//return accountInfo;
con.Open();
sql = "INNER JOIN users_information ON users_information.user_name LIKE '%" + selAccount.Sel_info + "%'";
MySQLCommand com = new MySQLCommand(sql, con);
MySQLDataAdapter adp = new MySQLDataAdapter(com);
DataSet ds = new DataSet();
adp.Fill(ds);
ArrayList arr = new ArrayList();
foreach (DataRow dr in ds.Tables[0].Rows)
{
AccountInfo accountInfo = new AccountInfo();
accountInfo.User_id = Int32.Parse(dr["user_id"].ToString());
accountInfo.User_name = dr["user_name"].ToString();
accountInfo.Sex = short.Parse(dr["user_sex"].ToString());
accountInfo.Birthday = DateTime.Parse(dr["user_birthday"].ToString());
accountInfo.Phone = dr["uesr_phome"].ToString();
arr.Add(accountInfo);
}
try
{
sql = "SELECT * FROM users INNER JOIN users_information ON users.user_id = " + Int32.Parse(selAccount.Sel_info);
com = new MySQLCommand(sql, con);
DbDataReader reader = com.ExecuteReader();
if(reader.Read())
{
AccountInfo accountInfo = new AccountInfo();
accountInfo.User_id = Int32.Parse(reader["user_id"].ToString());
accountInfo.User_name = reader["user_name"].ToString();
accountInfo.Sex = short.Parse(reader["user_sex"].ToString());
accountInfo.Birthday = DateTime.Parse(reader["user_birthday"].ToString());
accountInfo.Phone = reader["uesr_phome"].ToString();
arr.Add(accountInfo);
}
}
catch
{
}
return arr;
}
catch(Exception ex)
{
throw ex;
}
finally
{
con.Close();
}
}
private bool addOneUrl(int pos)
{
//添加第pos条团购条目信息
string query;
int link_id;
MD5 md5 = new MD5CryptoServiceProvider(); ;
//XmlNode oneUrl = lashouDocument.DocumentElement.ChildNodes[pos];
oneUrl oneUrl = xmlparse.getOneUrl(pos);
StatisticOutput sta = staContext.statistic(oneUrl);
Console.WriteLine("Insert the data to the database....");
query = "insert into links (site_id, url, title, description) values(5, \"" +
sta.url + "\",\"" + sta.title + "\",\"" + sta.description + "\")";
cmd = new MySQLDriverCS.MySQLCommand(query, conn);
cmd.ExecuteNonQuery();
cmd.Dispose();
query = "select link_id from links where url = \"" + sta.url + "\"";
cmd = new MySQLDriverCS.MySQLCommand(query, conn);
IDataReader dt = cmd.ExecuteReader();
link_id = Convert.ToInt32(dt.GetSchemaTable().Rows[0]["link_id"]);
foreach (DictionaryEntry de in sta.keywordList)
{
int keyword_id = 0;
query = "select keyword_id from keywords where keyword = \"" + de.Key + "\"";
cmd = new MySQLDriverCS.MySQLCommand(query, conn);
dt = cmd.ExecuteReader();
if (dt.GetSchemaTable().Rows.Count == 0)
{
query = "insert into keywords (keyword) values (\"" + de.Key + "\")";
cmd = new MySQLDriverCS.MySQLCommand(query, conn);
cmd.ExecuteNonQuery();
cmd.Dispose();
query = "select keyword_id from keywords where keyword = \"" + de.Key + "\"";
cmd = new MySQLDriverCS.MySQLCommand(query, conn);
dt = cmd.ExecuteReader();
keyword_id = Convert.ToInt32(dt.GetSchemaTable().Rows[0]["keyword_id"]);
}
else
keyword_id = Convert.ToInt32(dt.GetSchemaTable().Rows[0]["keyword_id"]);
byte[] hash = md5.ComputeHash(System.Text.Encoding.Default.GetBytes(de.Key.ToString()));
string flag;
switch (hash[0] >> 4)
{
case 10:
flag = "a";
break;
case 11:
flag = "b";
break;
case 12:
flag = "c";
break;
case 13:
flag = "d";
break;
case 14:
flag = "e";
break;
case 15:
flag = "f";
break;
default:
flag = (hash[0] >> 4).ToString();
break;
}
query = "insert into link_keyword" + flag + "(link_id, keyword_id, weight, time, feat, discount) values (" +
link_id + "," + keyword_id + "," + de.Value + "," + sta.time + "," + sta.feat + "," +
sta.discount + ")";
cmd = new MySQLDriverCS.MySQLCommand(query, conn);
cmd.ExecuteNonQuery();
cmd.Dispose();
}
Console.WriteLine("Write one link sucessfully!");
return true;
}
private void ButtonLogin_Click(object sender, EventArgs e)
{
if (TextName.Text.Trim() == "" && TextPassword.Text.Trim() == "")
{
labelNameNull.Text = "账号不能为空";
labelPasswordNull.Text = "密码不能为空";
TextName.Focus();
}
else if (TextName.Text.Trim() == "")
{
labelNameNull.Text = "账号不能为空";
labelPasswordNull.Text = " ";
TextName.Focus();
}
else if (TextPassword.Text.Trim() == "")
{
labelNameNull.Text = " ";
labelPasswordNull.Text = "密码不能为空";
TextPassword.Focus();
}
else
{
labelNameNull.Text = " ";
labelPasswordNull.Text = " ";
string identify = ComboBox.SelectedItem.ToString();
switch (identify)
{
case "总管理员":
{
if (TextName.Text.Trim() != "admin" && TextPassword.Text.Trim() != PublicValue.ADMINPASWRD)
{
labelNameNull.Text = "账号输入错误";
labelPasswordNull.Text = "密码输入错误";
TextName.Focus();
}
else if (TextName.Text.Trim() != "admin")
{
labelNameNull.Text = "账号输入错误";
labelPasswordNull.Text = " ";
TextName.Focus();
}
else if (TextPassword.Text.Trim() != PublicValue.ADMINPASWRD)
{
labelNameNull.Text = " ";
labelPasswordNull.Text = "密码输入错误";
TextName.Focus();
}
else
{
MessageBox.Show("登录成功！", "提示");
this.Hide();
Admin admin = new Admin();
admin.Show();
}
break;
}
case "公寓管理员":
{
int flag = 0;
MySQLConnection SQLconnection = new MySQLConnection(new MySQLConnectionString
("localhost", "DormitoryManage", "root", "123456").AsString);
string SQLstr = "SELECT managerNumber, managerPassword FROM manager_info";
SQLconnection.Open();
MySQLCommand SQLcommand = new MySQLCommand(SQLstr, SQLconnection);
MySQLDataReader SQLreader = (MySQLDataReader)SQLcommand.ExecuteReader();
while (SQLreader.Read())
{
string tempa = SQLreader["managerNumber"].ToString();
string tempb = SQLreader["managerPassword"].ToString();
if (TextName.Text.Trim() != tempa && TextPassword.Text.Trim() != tempb)
{
flag = 0;
}
else if (TextName.Text.Trim() != tempa)
{
flag = 1;
}
else if (TextPassword.Text.Trim() != tempb)
{
flag = 2;
}
else
{
flag = 3;
break;
}
}
SQLconnection.Close();
if (flag == 0)
{
labelNameNull.Text = "账号输入错误";
labelPasswordNull.Text = "密码输入错误";
TextName.Focus();
}
else if (flag == 1)
{
labelNameNull.Text = "账号输入错误";
labelPasswordNull.Text = " ";
TextName.Focus();
}
else if (flag == 2)
{
labelNameNull.Text = " ";
labelPasswordNull.Text = "密码输入错误";
TextName.Focus();
}
else if (flag == 3)
{
MessageBox.Show("登录成功！", "提示");
PublicValue.MAGNUM = TextName.Text;
this.Hide();
ManagerHost managerhost = new ManagerHost();
managerhost.Show();
}
break;
}
case "学生":
{
int flag = 0;
MySQLConnection SQLconnection = new MySQLConnection(new MySQLConnectionString
("localhost", "DormitoryManage", "root", "123456").AsString);
string SQLstr = "SELECT studentNumber, studentPassword FROM student_info";
SQLconnection.Open();
MySQLCommand SQLcommand1 = new MySQLCommand("SET NAMES GB2312", SQLconnection);
SQLcommand1.ExecuteNonQuery();
MySQLCommand SQLcommand2 = new MySQLCommand(SQLstr, SQLconnection);
MySQLDataReader SQLreader = (MySQLDataReader)SQLcommand2.ExecuteReader();
while (SQLreader.Read())
{
string tempa = SQLreader["studentNumber"].ToString();
string tempb = SQLreader["studentPassword"].ToString();
if (TextName.Text.Trim() != tempa && TextPassword.Text.Trim() != tempb)
{
flag = 0;
}
else if (TextName.Text.Trim() != tempa)
{
flag = 1;
}
else if (TextPassword.Text.Trim() != tempb)
{
flag = 2;
}
else
{
flag = 3;
break;
}
}
SQLconnection.Close();
if (flag == 0)
{
labelNameNull.Text = "账号输入错误";
labelPasswordNull.Text = "密码输入错误";
TextName.Focus();
}
else if (flag == 1)
{
labelNameNull.Text = "账号输入错误";
labelPasswordNull.Text = " ";
TextName.Focus();
}
else if (flag == 2)
{
labelNameNull.Text = " ";
labelPasswordNull.Text = "密码输入错误";
TextName.Focus();
}
else if (flag == 3)
{
MessageBox.Show("登录成功！", "提示");
PublicValue.STUNUM = TextName.Text;
this.Hide();
StudentHost studenthost = new StudentHost();
studenthost.Show();
}
break;
}
}
}
}