private void button3_Click(object sender, EventArgs e)
{
var temprow = unidt.Rows[(int)currentrow];
temprow["名称"] = textBox1.Text;
temprow["数量"] = textBox2.Text;
temprow["现场数量"] = textBox3.Text;
temprow["类别"] = comboBox2.Text;
temprow["备注"] = textBox4.Text;
temprow["当前状态"] = textBox5.Text;
temprow["单位"] = comboBox1.Text;
temprow["提出日期"] = new MySql.Data.Types.MySqlDateTime(dateTimePicker1.Value);
temprow["到货日期"] = new MySql.Data.Types.MySqlDateTime(dateTimePicker2.Value);
temprow["供应商"] = MFG.Text;
temprow["图号"] = DWG.Text;
temprow["工具编号"] = CODE.Text;
temprow["出厂编号"] = ORG_CODE.Text;
temprow.EndEdit();
getupdate(unidt);
// rf_filter();
}
/// <summary>
/// decodes the serailized panel Dataitem PK and sets the panel to it
/// </summary>
/// <param name="panel">active panel</param>
/// <param name="queryString">URL-encoded PK</param>
void SetRoutedPKForPanel(_min.Models.Panel panel, System.Collections.Specialized.NameValueCollection queryString)
{
DataRow row = mm.WebDriver.PKColRowFormat(panel);
for (int i = 0; i < queryString.Count; i++)
{
string decoded = Server.UrlDecode(queryString[i]);
if (row.Table.Columns[i].DataType == typeof(int))
{
row[i] = Int32.Parse(decoded);
}
else if (row.Table.Columns[i].DataType == typeof(MySql.Data.Types.MySqlDateTime))
{
row[i] = new MySql.Data.Types.MySqlDateTime(decoded);
}
else if (row.Table.Columns[i].DataType == typeof(DateTime))
{
row[i] = DateTime.Parse(decoded);
}
else
{
row[i] = decoded;
}
}
panel.PK = row;
}
private void button2_Click(object sender, EventArgs e)
{
string startdatestr = "0000/0/0";
if (!checkBox4.Checked)
{
startdatestr = dateTimePicker1.Value.ToShortDateString();
//startdatestr = dateTimePicker1.Value.ToShortDateString();
}
string enddatestr = "0000/0/0";
if (!checkBox3.Checked)
{
enddatestr = dateTimePicker2.Value.ToShortDateString();
}
string transferdatestr = "0000/0/0";
if (!checkBox2.Checked)
{
transferdatestr = dateTimePicker3.Value.ToShortDateString();
}
// var curdt = (DataView)dataGridView1.DataSource;
//var temprow = (from DataRow dd in curdt
// where dd["id"].ToString() == label8.Text
// select dd).First();
var temprow = unidt.Select("id=" + label8.Text).First();
temprow["产品名称"] = listBox2.SelectedValue;
temprow["产品架次"] = textBox1.Text;
temprow["备注"] = textBox3.Text;
temprow["状态说明"] = textBox2.Text;
temprow["当前状态"] = comboBox1.Text;
temprow["开始日期"] = new MySql.Data.Types.MySqlDateTime(startdatestr);
temprow["结束日期"] = new MySql.Data.Types.MySqlDateTime(enddatestr);
temprow["移交日期"] = new MySql.Data.Types.MySqlDateTime(transferdatestr);
FormMethod.notifyServers("更新产品:" + listBox2.SelectedValue + ",架次：" + textBox1.Text);
//DbHelperSQL.ExecuteSql("update 产品流水表 set 产品名称='" + listBox2.SelectedValue + "',产品架次='" + textBox1.Text + "',开始日期='" + startdatestr + "',结束日期='" + enddatestr + "',移交日期='" + transferdatestr + "',备注='" + textBox3.Text + "',当前状态='" + comboBox1.Text + "',状态说明='" + textBox2.Text + "' where 流水号=" + label8.Text);
temprow.EndEdit();
getupdate(unidt);
// rf_filter();
// rf_default();
}
public string GetLatestDataDate()
{
using (MySqlConnection connection = new MySqlConnection(DatabaseConnectionstring))
{
connection.Open();
MySqlCommand command = new MySqlCommand();
command.Connection = connection;
command.CommandText = string.Format("SELECT * FROM jpssflood.kmlmetadata ORDER BY Date DESC LIMIT 1 ");
MySqlDataReader reader = command.ExecuteReader();
reader.Read();
MySql.Data.Types.MySqlDateTime mySqldate = reader.GetMySqlDateTime(4);
return(mySqldate.Value.ToString("MM/dd/yyyy"));
}
}
private void dataGridView2_CellValidating(object sender, DataGridViewCellValidatingEventArgs e)
{
string data = e.FormattedValue.ToString();
string oldData;
switch (e.ColumnIndex)
{
case 3:
oldData = dataGridView2.Rows[e.RowIndex].Cells[e.ColumnIndex - 1].Value.ToString();
if ((data.Length > 3) && (data.Substring(data.Length - 4) != oldData))
{
if (MessageBox.Show("MN码与设备编号不匹配，确定这样修改吗？", "警告", MessageBoxButtons.YesNo) == DialogResult.No)
{
e.Cancel = true;
}
}
break;
case 4:
case 5:
oldData = "";
try
{
oldData = deviceUseHistDataSet.DeviceUseHist.Rows[e.RowIndex][e.ColumnIndex].ToString();
}
catch (Exception e1)
{
}
if (oldData != data)
{
try
{
MySql.Data.Types.MySqlDateTime aa = new MySql.Data.Types.MySqlDateTime(data);
deviceUseHistDataSet.DeviceUseHist.Rows[e.RowIndex][e.ColumnIndex] = aa;
}
catch (Exception e1)
{
//deviceUseHistDataSet.DeviceUseHist.Rows[e.RowIndex][e.ColumnIndex] = new MySql.Data.Types.MySqlDateTime();
}
}
break;
default:
break;
}
}
public JsonResult GetFileByBlock(DateTime from, DateTime to, string region, string product, int north, int south, int west, int east, int zoom)
{
List <KmlFileInfo> kmlFiles = new List <KmlFileInfo>();
string fromDateFormatString = string.Empty;
string toDateFormatString = string.Empty;
to = from;
fromDateFormatString = @"yyyy-MM-dd 00:00:00";
toDateFormatString = @"yyyy-MM-dd 23:00:00";
int queryProductId = ConvertProductStringToId(product);
using (MySqlConnection connection = new MySqlConnection(DatabaseConnectionstring))
{
connection.Open();
MySqlCommand command = new MySqlCommand();
command.Connection = connection;
command.CommandText = string.Format("SELECT * FROM jpssflood.kmlmetadata WHERE Date >= '{0}' AND Date <= '{1}' AND ProductId = {2} AND RegionId = {3} AND DistrictId >= 1 AND DistrictId <= 136 ORDER BY DistrictID DESC", from.ToString(fromDateFormatString), to.AddHours(1).ToString(toDateFormatString), queryProductId, 1);
MySqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
int kmlId = reader.GetInt32(0);
int productId = reader.GetInt32(1);
int regionId = reader.GetInt32(2);
int districtId = reader.GetInt32(3);
MySql.Data.Types.MySqlDateTime mySqldate = reader.GetMySqlDateTime(4);
string fileName = reader.GetString(5);
int bounds_north = RegionUtil.DistrictBounds[districtId, 3];
int bounds_south = RegionUtil.DistrictBounds[districtId, 2];
int bounds_east = RegionUtil.DistrictBounds[districtId, 1];
int bounds_west = RegionUtil.DistrictBounds[districtId, 0];
if (zoom <= 3 || (queryProductId != 1 && queryProductId != 6) || IsInBoundary(west, east, south, north, bounds_west, bounds_east, bounds_south, bounds_north))
{
kmlFiles.Add(new KmlFileInfo {
FullName = string.Format(kmlFullFilePathFormat, fileName), ShortName = fileName, DistrictId = districtId
});
}
}
}
return(Json(kmlFiles));
}
public static ArrayList DataTable2ArrayList(DataTable data)
{
ArrayList array = new ArrayList();
for (int i = 0; i < data.Rows.Count; i++)
{
DataRow row = data.Rows[i];
Hashtable record = new Hashtable();
for (int j = 0; j < data.Columns.Count; j++)
{
try
{
object cellValue = row[j];
if (cellValue.GetType() == typeof(DBNull))
{
cellValue = null;
}
else
{
if (cellValue.GetType() == typeof(MySql.Data.Types.MySqlDateTime))
{
string str = cellValue.ToString();
if (str.IndexOf("年", 0) > 0)
{
str = str;
}
MySql.Data.Types.MySqlDateTime aMySqldt = (MySql.Data.Types.MySqlDateTime)cellValue;
DateTime adt = new DateTime(aMySqldt.Year, aMySqldt.Month, aMySqldt.Day, aMySqldt.Hour, aMySqldt.Minute, aMySqldt.Second);
cellValue = adt;
}
}
record[data.Columns[j].ColumnName] = cellValue;
}
catch (Exception ex)
{
record[data.Columns[j].ColumnName] = null;
}
}
array.Add(record);
}
return(array);
}
public static bool Authorization(string username, string password)
{
//Подключение к своей базе данных
MyDataBase.ConnectToDB();
//Проверяем совпадают ли имя и пароли
MyDataBase.ExecuteCommand("SELECT * FROM users WHERE users.username = '******' AND users.userpass = '******'");
//Проверка, нашли ли мы вообще такого пользователя в БД
if (MyDataBase.rdr.HasRows == false)
{
//Надо сделать throwException //ай, потом..
MyDataBase.rdr.Close();
MyDataBase.CloseConnectToDB();
return(false);
}
//Читаем данные
while (MyDataBase.rdr.Read())
{
MySql.Data.Types.MySqlDateTime x = (MySql.Data.Types.MySqlDateTime)MyDataBase.rdr[5];
DateTime tempDate = new DateTime(x.Year, x.Month, x.Day);
//Сохраняем данные о себе
user = new User(Convert.ToInt32(MyDataBase.rdr[0].ToString()),
MyDataBase.rdr[1].ToString(),
MyDataBase.rdr[3].ToString(),
MyDataBase.rdr[4] as byte[],
tempDate);
}
contextUser = user;
MyDataBase.rdr.Close();
//Пишем что мы онлайн в БД
MyDataBase.SetOnlineUser(user.id);
//Отключаемся от БД
MyDataBase.CloseConnectToDB();
return(true);
}
private void button2_Click(object sender, EventArgs e)
{
string closedatestr = "0000-00-00";
if (!checkBox2.Checked)
{
closedatestr = dateTimePicker2.Value.ToShortDateString();
//startdatestr = dateTimePicker1.Value.ToShortDateString();
}
string[] prod = listBox2.SelectedValue.ToString().Split('_');
var pp = unidt.Select("流水号 = " + label1.Text).First();
pp["外部拒收号"] = textBox1.Text;
pp["内部拒收号"] = textBox2.Text;
pp["拒收原因"] = textBox3.Text;
pp["纠正措施"] = textBox4.Text;
pp["当前状态"] = textBox5.Text;
pp["文件"] = filetrackstr;
pp["关联产品"] = prod[0];
pp["产品架次"] = prod[1];
pp["发生日期"] = new MySql.Data.Types.MySqlDateTime(dateTimePicker1.Value);
pp["关闭日期"] = new MySql.Data.Types.MySqlDateTime(closedatestr);
pp["原因类型"] = comboBox1.Text;
pp["责任人"] = comboBox2.Text;
pp.EndEdit();
getupdate(unidt);
//填充Production表备注
update_note(prod[0], prod[1]);
FormMethod.notifyServers("更新了RNC:" + textBox1.Text + ",产品：" + prod[0] + "，架次：" + prod[1]);
MessageBox.Show("执行成功");
}
/// <summary>
/// Retourne la valeur typée du champ spécifié par un nom
/// </summary>
/// <typeparam name="T">Type de la valeur attendue</typeparam>
/// <param name="NomChamp">Nom du champ pour lequel on veut récupérer sa valeur</param>
/// <param name="ValeurParDefaut">Valeur par défaut à retourner si le champ n'existe pas ou si sa valeur n'est pas du type attendu</param>
/// <returns>Valeur du champ si il existe, sinon null</returns>
public T ValeurChamp <T>(string NomChamp, T ValeurParDefaut = default(T))
{
object Resultat = this[NomChamp];
if (Resultat == null)
{
return(ValeurParDefaut);
}
if (Resultat is MySql.Data.Types.MySqlDateTime)
{
MySql.Data.Types.MySqlDateTime Valeur = (MySql.Data.Types.MySqlDateTime)Resultat;
Resultat = Valeur.IsValidDateTime ? (object)Valeur.GetDateTime() : ValeurParDefaut;
}
else if (Resultat is MySql.Data.Types.MySqlDecimal)
{
MySql.Data.Types.MySqlDecimal Valeur = (MySql.Data.Types.MySqlDecimal)Resultat;
Resultat = !Valeur.IsNull ? (object)Valeur.Value : ValeurParDefaut;
}
return((Resultat is T) ? (T)Resultat : ValeurParDefaut);
}
//把数据加入到列表中
public void setInsertRowData(string sn)
{
if (this.InvokeRequired)
{
this.Invoke(new setText(setInsertRowData), new object[] { sn });
}
else
{
DataRow r = product_list_table.NewRow();
r["dmc"] = sn;
r["snbox"] = run_contex.instance()._curBoxsn;
r["unit"] = run_contex.instance()._allProductNums.ToString();
r["numbox"] = run_contex.instance()._curProductNums.ToString();
r["persno"] = run_contex.instance()._curWorker;
r["time"] = new MySql.Data.Types.MySqlDateTime(DateTime.Now);
if (product_list_table.Rows.Count >= 1000)
{
product_list_table.Clear();
}
product_list_table.Rows.Add(r);
}
}
private void button3_Click(object sender, EventArgs e)
{
var temprow = unidt.Select("流水号 = " + SEQ_label.Text).First();
temprow["名称"] = textBox1.Text;
temprow["类别"] = comboBox2.Text;
temprow["备注"] = textBox4.Text;
temprow["当前状态"] = textBox5.Text;
temprow["发生日期"] = new MySql.Data.Types.MySqlDateTime(dateTimePicker1.Value);
temprow["解决日期"] = new MySql.Data.Types.MySqlDateTime(dateTimePicker2.Value);
temprow.EndEdit();
getupdate(unidt);
rf_filter();
}
public static User GetUserData(string username)
{
User temp = null;
//Подключение к своей базе данных
MyDataBase.ConnectToDB();
MyDataBase.ExecuteCommand("SELECT * FROM users WHERE users.username = "******"'" + username + "'");
if (MyDataBase.rdr.HasRows == false)
{
return(null);
}
while (MyDataBase.rdr.Read())
{
MySql.Data.Types.MySqlDateTime x = (MySql.Data.Types.MySqlDateTime)MyDataBase.rdr[5];
DateTime tempDate = new DateTime(x.Year, x.Month, x.Day);
//Сохраняем данные о себе
temp = new User(Convert.ToInt32(MyDataBase.rdr[0].ToString()),
MyDataBase.rdr[1].ToString(),
MyDataBase.rdr[3].ToString(),
MyDataBase.rdr[4] as byte[],
tempDate);
if (MyDataBase.rdr[6].ToString().Equals("True"))
{
temp.online = true;
}
}
//Отключаемся от БД
MyDataBase.rdr.Close();
MyDataBase.CloseConnectToDB();
return(temp);
}
/// <summary>
/// Permet de récupérer la valeur typée du premier champ du premier enregistrement résultant d'une requête de consultation
/// </summary>
/// <typeparam name="T">Type de la valeur attendue</typeparam>
/// <param name="Requete">Requête SQL à exécuter (doit être de type SELECT ou SHOW)</param>
/// <param name="Valeurs">Valeurs des parties variables</param>
/// <returns>Valeur typée du premier champ du premier enregistrement si possible, sinon null</returns>
public T ValeurDe <T>(string Requete, params object[] Valeurs)
{
object Resultat = ValeurDeAvecDefaut(default(T), Requete, Valeurs);
if (Resultat is MySql.Data.Types.MySqlDateTime)
{
MySql.Data.Types.MySqlDateTime Valeur = (MySql.Data.Types.MySqlDateTime)Resultat;
Resultat = Valeur.IsValidDateTime ? (object)Valeur.GetDateTime() : null;
}
else if (Resultat is MySql.Data.Types.MySqlDecimal)
{
MySql.Data.Types.MySqlDecimal Valeur = (MySql.Data.Types.MySqlDecimal)Resultat;
Resultat = !Valeur.IsNull ? (object)Valeur.Value : null;
}
if (Resultat is T)
{
return((T)Resultat);
}
if (SurErreur != null)
{
SurErreur(this, MethodeExecutantRequeteSql.ValeurDe_Typee, Requete, Valeurs, string.Format("Imcompatibilité entre le résultat qui est de type {0} et le type attendu qui est {1} !", (Resultat != null) ? Resultat.GetType().FullName : "null", typeof(T).FullName));
}
return(default(T));
}
private DataTable ConvertDataTable(DataTable dataTable, BulkCopyInfo bulkCopyInfo)
{
var columns = dataTable.Columns.Cast <DataColumn>();
if (!columns.Any(item => item.DataType == typeof(MySql.Data.Types.MySqlDateTime)))
{
return(dataTable);
}
Dictionary <int, Type> changedColumnTypes = new Dictionary <int, Type>();
Dictionary <(int RowIndex, int ColumnIndex), object> changedValues = new Dictionary <(int RowIndex, int ColumnIndex), object>();
DataTable dtChanged = dataTable.Clone();
int rowIndex = 0;
foreach (DataRow row in dataTable.Rows)
{
for (int i = 0; i < dataTable.Columns.Count; i++)
{
object value = row[i];
if (value != null)
{
Type type = value.GetType();
if (type != typeof(DBNull))
{
if (type == typeof(MySql.Data.Types.MySqlDateTime))
{
MySql.Data.Types.MySqlDateTime mySqlDateTime = (MySql.Data.Types.MySqlDateTime)value;
TableColumn tableColumn = bulkCopyInfo.Columns.FirstOrDefault(item => item.Name == dataTable.Columns[i].ColumnName);
string dataType = tableColumn.DataType.ToLower();
Type columnType = null;
if (dataType.Contains("date") || dataType.Contains("timestamp"))
{
DateTime dateTime = mySqlDateTime.GetDateTime();
columnType = typeof(DateTime);
changedValues.Add((rowIndex, i), dateTime);
}
if (columnType != null && !changedColumnTypes.ContainsKey(i))
{
changedColumnTypes.Add(i, columnType);
}
}
}
}
}
rowIndex++;
}
if (changedColumnTypes.Count == 0)
{
return(dataTable);
}
for (int i = 0; i < dtChanged.Columns.Count; i++)
{
if (changedColumnTypes.ContainsKey(i))
{
dtChanged.Columns[i].DataType = changedColumnTypes[i];
}
}
rowIndex = 0;
foreach (DataRow row in dataTable.Rows)
{
DataRow r = dtChanged.NewRow();
for (int i = 0; i < dataTable.Columns.Count; i++)
{
var value = row[i];
if (changedValues.ContainsKey((rowIndex, i)))
{
r[i] = changedValues[(rowIndex, i)];
}
// general purpose statement genrator and caller for single row from id
public List <string>[] SelectRowWithId(string tableName, List <string> fieldNames,
string idFieldName, string idValue)
{
//RESULTS LIST CONSTRUCTION--------------------------------------------------
//create main list object (rows) to hold individual lists (columns)
List <string>[] results = new List <string> [fieldNames.Count];
// generic counter to be used for loop
int counter = 0;
//create the individual lists to hold the results for each field/column
while (counter < fieldNames.Count)
{
results[counter] = new List <string>();
counter++;
}
// reset counnter
counter = 0;
// SELECT QUERY CONSTRUCTION
string query = "SELECT ";
//loop thorugh each fieldname and add to select statemnt
foreach (var field in fieldNames)
{
//add comma to each field name and add to select statement
if (counter > 0)
{
query += ",";
}
// add backquotes around field name and add field to quetu ( and a space)
query += "`" + field + "`";
counter++;
}
//reset counter
counter = 0;
// now add from part
query += "FROM " + "`" + tableName + "`";
// now add where part to select the row based on ID
query += " WHERE " + "`" + idFieldName + "`" + "=" + idValue;
//comment / uncomment to debug
MessageBox.Show(query);
//connect, run query and populate results --------------------------------------
// open connection
if (this.OpenConnection())
{
//create me Sql command
MySqlCommand cmd = new MySqlCommand(query, connection);
//create a data reader to get the results
MySqlDataReader dataReader = cmd.ExecuteReader();
//loop thorugh each row of results
while (dataReader.Read())
{
foreach (var field in fieldNames)
{
//in the square brackets, the string must macth
//the column/ field name on the database
//step through to check valeus
//if this is a data value then we need to convert to pervent issues
if (dataReader[field].GetType().ToString() == "MySql.Data.Types.MySqlDateTime")
{
// get the current mysql date object
MySql.Data.Types.MySqlDateTime mySqlFormat =
(MySql.Data.Types.MySqlDateTime)dataReader[field];
//convert to csharp data type
DateTime csFormat = mySqlFormat.GetDateTime();
//format in the MySQL style before adding results
results[counter].Add(csFormat.ToString("yyyy-MM-dd"));
}
else
{
//add values to the results
results[counter].Add(dataReader[field].ToString());
}
counter++;
}
//reset the counter before getting next row of data
counter = 0;
}
//close data reader and connection
dataReader.Close();
this.CloseConnection();
}
//return results
return(results);
}
public static Block newFromDataRow(object[] values)
{
Block b = null;
try
{
b = new Block( );
b.ipb_id = (int)(values[0]);
b.ipb_address = Utility.Net.IPAddress.newFromEncodedString((byte[])values[1]);
b.ipb_user = (uint)values[2];
b.ipb_by = (uint)values[3];
b.ipb_reason = ASCIIEncoding.ASCII.GetString((byte[] )values[4]);
//System.OverflowException was unhandled
//Message="Value was either too large or too small for an Int32."
//Source="mscorlib"
//StackTrace:
// at System.Number.ParseInt32(String s, NumberStyles style, NumberFormatInfo info)
// at MySql.Data.Types.MySqlDateTime.ParseMySql(String s, Boolean is41)
// at MySql.Data.Types.MySqlDateTime.Parse(String s)
// at MySql.Data.Types.MySqlDateTime..ctor(String s)
// at BlockedOpenProxyReviewerBot.Block.newFromDataRow(Object[] values) in C:\Users\stwalkerster\Documents\Visual Studio 2008\Projects\BlockedOpenProxyReviewerBot\BlockedOpenProxyReviewerBot\Block.cs:line 37
// at BlockedOpenProxyReviewerBot.Database.getProxyBlocks() in C:\Users\stwalkerster\Documents\Visual Studio 2008\Projects\BlockedOpenProxyReviewerBot\BlockedOpenProxyReviewerBot\Database.cs:line 44
// at BlockedOpenProxyReviewerBot.Program.runBot() in C:\Users\stwalkerster\Documents\Visual Studio 2008\Projects\BlockedOpenProxyReviewerBot\BlockedOpenProxyReviewerBot\Program.cs:line 37
// at BlockedOpenProxyReviewerBot.Program..ctor(String[] args) in C:\Users\stwalkerster\Documents\Visual Studio 2008\Projects\BlockedOpenProxyReviewerBot\BlockedOpenProxyReviewerBot\Program.cs:line 24
// at BlockedOpenProxyReviewerBot.Program.Main(String[] args) in C:\Users\stwalkerster\Documents\Visual Studio 2008\Projects\BlockedOpenProxyReviewerBot\BlockedOpenProxyReviewerBot\Program.cs:line 12
// at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
// at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
// at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
// at System.Threading.ThreadHelper.ThreadStart()
//InnerException:
byte[] x = (byte[])values[5];
string y = ASCIIEncoding.ASCII.GetString(x);
MySql.Data.Types.MySqlDateTime z = new MySql.Data.Types.MySqlDateTime(
int.Parse(y.Substring(0, 4)),
int.Parse(y.Substring(4, 2)),
int.Parse(y.Substring(6, 2)),
int.Parse(y.Substring(8, 2)),
int.Parse(y.Substring(10, 2)),
int.Parse(y.Substring(12, 2))
);
b.ipb_timestamp = z.GetDateTime( );
// b.ipb_timestamp = new MySql.Data.Types.MySqlDateTime( ASCIIEncoding.ASCII.GetString( (byte[ ])values[ 5 ] ) ).GetDateTime( );
b.ipb_auto = (bool)values[6];
b.ipb_anon_only = (bool)values[7];
b.ipb_create_account = (bool)values[8];
b.ipb_expiry = ASCIIEncoding.ASCII.GetString((byte[] )values[9]);
if (((byte[])values[10]).Length == 4)
{
b.ipb_range_start = new IPAddress((byte[])values[10]);
}
if (((byte[])values[11]).Length == 4)
{
b.ipb_range_end = new IPAddress((byte[])values[11]);
}
b.ipb_enable_autoblock = (bool)values[12];
b.ipb_deleted = (bool)values[13];
b.ipb_block_email = (bool)values[14];
b.ipb_by_text = ASCIIEncoding.ASCII.GetString((byte[] )values[15]);
}
catch (OverflowException)
{
}
return(b);
}
public static string ConvertToSqlFormat(object ob, bool wrapStringWithSingleQuote, bool escapeStringSequence, MySqlColumn col, BlobDataExportMode blobExportMode)
{
StringBuilder sb = new StringBuilder();
if (ob == null || ob is System.DBNull)
{
sb.AppendFormat("NULL");
}
else if (ob is System.String)
{
string str = (string)ob;
if (escapeStringSequence)
{
str = QueryExpress.EscapeStringSequence(str);
}
if (wrapStringWithSingleQuote)
{
sb.AppendFormat("'");
}
sb.Append(str);
if (wrapStringWithSingleQuote)
{
sb.AppendFormat("'");
}
}
else if (ob is System.Boolean)
{
sb.AppendFormat(Convert.ToInt32(ob).ToString());
}
else if (ob is System.Byte[])
{
if (((byte[])ob).Length == 0)
{
if (wrapStringWithSingleQuote)
{
return("''");
}
else
{
return("");
}
}
else
{
if (blobExportMode == BlobDataExportMode.HexString)
{
sb.AppendFormat(CryptoExpress.ConvertByteArrayToHexString((byte[])ob));
}
else if (blobExportMode == BlobDataExportMode.BinaryChar)
{
if (wrapStringWithSingleQuote)
{
sb.Append("'");
}
foreach (byte b in ((byte[])ob))
{
char ch = (char)b;
escape_string(sb, ch);
}
if (wrapStringWithSingleQuote)
{
sb.Append("'");
}
}
}
}
else if (ob is short)
{
sb.AppendFormat(((short)ob).ToString(_numberFormatInfo));
}
else if (ob is int)
{
sb.AppendFormat(((int)ob).ToString(_numberFormatInfo));
}
else if (ob is long)
{
sb.AppendFormat(((long)ob).ToString(_numberFormatInfo));
}
else if (ob is ushort)
{
sb.AppendFormat(((ushort)ob).ToString(_numberFormatInfo));
}
else if (ob is uint)
{
sb.AppendFormat(((uint)ob).ToString(_numberFormatInfo));
}
else if (ob is ulong)
{
sb.AppendFormat(((ulong)ob).ToString(_numberFormatInfo));
}
else if (ob is double)
{
sb.AppendFormat(((double)ob).ToString(_numberFormatInfo));
}
else if (ob is decimal)
{
sb.AppendFormat(((decimal)ob).ToString(_numberFormatInfo));
}
else if (ob is float)
{
sb.AppendFormat(((float)ob).ToString(_numberFormatInfo));
}
else if (ob is byte)
{
sb.AppendFormat(((byte)ob).ToString(_numberFormatInfo));
}
else if (ob is sbyte)
{
sb.AppendFormat(((sbyte)ob).ToString(_numberFormatInfo));
}
else if (ob is TimeSpan)
{
TimeSpan ts = (TimeSpan)ob;
if (wrapStringWithSingleQuote)
{
sb.AppendFormat("'");
}
sb.AppendFormat(((int)ts.TotalHours).ToString().PadLeft(2, '0'));
sb.AppendFormat(":");
sb.AppendFormat(ts.Duration().Minutes.ToString().PadLeft(2, '0'));
sb.AppendFormat(":");
sb.AppendFormat(ts.Duration().Seconds.ToString().PadLeft(2, '0'));
if (wrapStringWithSingleQuote)
{
sb.AppendFormat("'");
}
}
else if (ob is System.DateTime)
{
if (wrapStringWithSingleQuote)
{
sb.AppendFormat("'");
}
sb.AppendFormat(((DateTime)ob).ToString("yyyy-MM-dd HH:mm:ss", _dateFormatInfo));
if (col.TimeFractionLength > 0)
{
sb.Append(".");
string _microsecond = ((DateTime)ob).ToString("".PadLeft(col.TimeFractionLength, 'f'));
sb.Append(_microsecond);
}
if (wrapStringWithSingleQuote)
{
sb.AppendFormat("'");
}
}
else if (ob is MySql.Data.Types.MySqlDateTime)
{
MySql.Data.Types.MySqlDateTime mdt = (MySql.Data.Types.MySqlDateTime)ob;
if (mdt.IsNull)
{
sb.AppendFormat("NULL");
}
else
{
if (mdt.IsValidDateTime)
{
DateTime dtime = mdt.Value;
if (wrapStringWithSingleQuote)
{
sb.AppendFormat("'");
}
if (col.MySqlDataType == "datetime")
{
sb.AppendFormat(dtime.ToString("yyyy-MM-dd HH:mm:ss", _dateFormatInfo));
}
else if (col.MySqlDataType == "date")
{
sb.AppendFormat(dtime.ToString("yyyy-MM-dd", _dateFormatInfo));
}
else if (col.MySqlDataType == "time")
{
sb.AppendFormat("{0}:{1}:{2}", mdt.Hour, mdt.Minute, mdt.Second);
}
//sb.AppendFormat(dtime.ToString("HH:mm:ss", _dateFormatInfo));
else
{
sb.AppendFormat(dtime.ToString("yyyy-MM-dd HH:mm:ss", _dateFormatInfo));
}
if (col.TimeFractionLength > 0)
{
sb.Append(".");
sb.Append(((MySql.Data.Types.MySqlDateTime)ob).Microsecond.ToString().PadLeft(col.TimeFractionLength, '0'));
}
if (wrapStringWithSingleQuote)
{
sb.AppendFormat("'");
}
}
else
{
if (wrapStringWithSingleQuote)
{
sb.AppendFormat("'");
}
if (col.MySqlDataType == "datetime")
{
sb.AppendFormat("0000-00-00 00:00:00");
}
else if (col.MySqlDataType == "date")
{
sb.AppendFormat("0000-00-00");
}
else if (col.MySqlDataType == "time")
{
sb.AppendFormat("00:00:00");
}
else
{
sb.AppendFormat("0000-00-00 00:00:00");
}
if (col.TimeFractionLength > 0)
{
sb.Append(".".PadRight(col.TimeFractionLength, '0'));
}
if (wrapStringWithSingleQuote)
{
sb.AppendFormat("'");
}
}
}
}
else if (ob is System.Guid)
{
if (col.MySqlDataType == "binary(16)")
{
sb.Append(CryptoExpress.ConvertByteArrayToHexString(((Guid)ob).ToByteArray()));
}
else if (col.MySqlDataType == "char(36)")
{
if (wrapStringWithSingleQuote)
{
sb.AppendFormat("'");
}
sb.Append(ob);
if (wrapStringWithSingleQuote)
{
sb.AppendFormat("'");
}
}
else
{
if (wrapStringWithSingleQuote)
{
sb.AppendFormat("'");
}
sb.Append(ob);
if (wrapStringWithSingleQuote)
{
sb.AppendFormat("'");
}
}
}
else
{
throw new Exception("Unhandled data type. Current processing data type: " + ob.GetType().ToString() + ". Please report this bug with this message to the development team.");
}
return(sb.ToString());
}
//修改指定记录
private void chanegValue_Click(object sender, EventArgs e)
{
if (run_contex.instance()._env.login_private)
{
if (product_list_table != null && product_list_table.Rows.Count <= 0)
{
return;
}
int i = gridEX1.CurrentRow.Position;
if (i >= 0)
{
_changeValue = true;
DataRow dr = product_list_table.Rows[i];
string dmc = dr["dmc"].ToString();
string snbox = dr["snbox"].ToString();
//string time = dr["time"].ToString();
_changeDlg = new frmChangeValue();
_changeDlg.BoxSN = snbox;
_changeDlg.ProductSN = dmc;
if (_changeDlg.ShowDialog() == DialogResult.Yes)
{
string new_snbox = _changeDlg.BoxSN;
string new_dmc = _changeDlg.ProductSN;
if (assertBoxsetIsFull(new_snbox))
{
return;
}
/*
* string cmd_exe = string.Format("update sn_box_{0} set dmc=\'{1}\', dmc_intern=\'{2}\', snbox=\'{3}\' , persno = \'{4}\', time = NOW() where dmc = \'{5}\';",
* run_contex.instance()._curFormula, new_dmc, new_dmc, new_snbox ,run_contex.instance()._curWorker, dmc);
*/
/*
* if (snbox.Trim() != new_snbox.Trim()) {
* string update_snbox = "update sn_box_{0} set snbox=\'{1}\' where snbox=\'{2}\';";
* string ex = string.Format(update_snbox, run_contex.instance()._curFormula, new_snbox, snbox);
* MySqlCommand cmd1 = new MySqlCommand(ex, _db_conn);
* cmd1.ExecuteNonQuery();
* }
* */
string cmd_exe = string.Format("update sn_box_{0} set dmc=\'{1}\', dmc_intern=\'{2}\', time = Now(), snbox= \'{3}\' where dmc = \'{4}\';",
run_contex.instance()._curFormula, new_dmc, new_dmc, new_snbox, dmc);
MySqlCommand cmd = new MySqlCommand(cmd_exe, _db_conn);
try {
cmd.ExecuteNonQuery();
} catch {
MessageBox.Show("更新失败,检查输入的产品号是否重复!");
return;
}
dr["dmc"] = new_dmc;
dr["snbox"] = new_snbox;
dr["time"] = new MySql.Data.Types.MySqlDateTime(DateTime.Now);
//修改箱号
DataRow last_row = product_list_table.Rows[product_list_table.Rows.Count - 1];
run_contex.instance()._curBoxsn = last_row["snbox"].ToString();
updateBoxset(run_contex.instance()._curFormula);
mainForm.getMainForm().setBoxNum(run_contex.instance()._curBoxsn);
//重新载入数据
//loadProductType(run_contex.instance()._curFormula);
initSetting(run_contex.instance()._curFormula);
/*
* //修改箱号
* if (new_snbox.Trim() != snbox.Trim()) {
* string cmd_exe = string.Format("update sn_box_{0} set snbox=\'{1}\' , time=Now() where snbox=\'{2}\';",
* run_contex.instance()._curFormula, new_snbox, snbox);
* MySqlCommand cmd = new MySqlCommand(cmd_exe, _db_conn);
* try {
* cmd.ExecuteNonQuery();
* } catch {
* MessageBox.Show("更新失败,检查输入的箱号是否正确!");
* return;
* }
* foreach (DataRow r in product_list_table.Rows) {
* if (r["snbox"].ToString() == snbox) {
* r["snbox"] = new_snbox;
* r["time"] = new MySql.Data.Types.MySqlDateTime(DateTime.Now);
* }
* }
* }
* */
}
_changeDlg.Dispose();
}
}
}
/// <summary>
/// decodes the serailized panel Dataitem PK and sets the panel to it
/// </summary>
/// <param name="panel">active panel</param>
/// <param name="queryString">URL-encoded PK</param>
void SetRoutedPKForPanel(_min.Models.Panel panel, System.Collections.Specialized.NameValueCollection queryString)
{
DataRow row = mm.WebDriver.PKColRowFormat(panel);
for (int i = 0; i < queryString.Count; i++)
{
string decoded = Server.UrlDecode(queryString[i]);
if (row.Table.Columns[i].DataType == typeof(int))
{
row[i] = Int32.Parse(decoded);
}
else if (row.Table.Columns[i].DataType == typeof(MySql.Data.Types.MySqlDateTime))
{
row[i] = new MySql.Data.Types.MySqlDateTime(decoded);
}
else if (row.Table.Columns[i].DataType == typeof(DateTime))
{
row[i] = DateTime.Parse(decoded);
}
else
{
row[i] = decoded;
}
}
panel.PK = row;
}
public void Step1_Syn()
{
using (var dbconn1 = DbConn.CreateConn(DbType.SQLSERVER, actmodel.database1.source, actmodel.database1.port, actmodel.database1.database, actmodel.database1.userid, actmodel.database1.password))
using (var dbconn2 = DbConn.CreateConn(DbType.MYSQL, actmodel.database2.source, actmodel.database2.port, actmodel.database2.database, actmodel.database2.userid, actmodel.database2.password))
{
dbconn1.Open();
dbconn2.Open();
DbStructure.DbStructureSqlServer mssqlstr = new DbStructure.DbStructureSqlServer();
tablestru = mssqlstr.GetTableStructure(dbconn1, tablename);
string sql = "select count(1) as cc from " + tablename + " ";
allitemcount = (int)dbconn1.ExecuteScalar(sql, null);
successcount = (int)((long)dbconn2.ExecuteScalar(sql, null));
// (tablestru.identityattribute != null && tablestru.identityattribute.Count == 1)
if (tablestru.primarykey != null)
{
List <string> pkcol = new List <string>();
List <string> pkorder = new List <string>();
if (tablestru.primarykey != null)
{
foreach (var a in tablestru.primarykey.columns)
{
pkcol.Add(a.name);
pkorder.Add(a.name + " desc ");
}
}
//else// if (tablestru.identityattribute != null && tablestru.identityattribute.Count == 1)
//{
// pkcol.Add(tablestru.identityattribute[0].column.name);
// pkorder.Add(tablestru.identityattribute[0].column.name + " desc ");
//}
//else
//{
// pkcol.Add(tablestru.columns.First().name);
// pkorder.Add(tablestru.columns.First().name + " desc ");
//}
string sqltop = "select {0} from {1} order by {2} limit 0,1;";
sqltop = string.Format(sqltop, string.Join(",", pkcol), tablename, string.Join(",", pkorder));
DataTable tboftop = dbconn2.SqlToDataTable(sqltop, null);
if (tboftop.Rows.Count == 1)
{
List <ProcedureParameter> parasofgetrownum = new List <ProcedureParameter>();
string sqlofpno = "select top 1 A.rownumber from ( SELECT ROW_NUMBER() over (order by {0}) as rownumber,{1} from {2} ) A where {3}";
List <string> getrownum_order = new List <string>();
List <string> getrownum_where = new List <string>();
foreach (string s in pkcol)
{
object value = tboftop.Rows[0][s];
if (value != null)
{
if (value.GetType() == typeof(MySql.Data.Types.MySqlDateTime))
{
MySql.Data.Types.MySqlDateTime nvalue = (MySql.Data.Types.MySqlDateTime)value;
string sofmydatetime = value.ToString();
if (string.IsNullOrEmpty(sofmydatetime))
{
value = System.DBNull.Value;
}
else
{
value = Convert.ToDateTime(sofmydatetime);
}
}
}
getrownum_order.Add(s + " asc");
getrownum_where.Add(s + "=@" + s);
parasofgetrownum.Add(new ProcedureParameter("@" + s, value));
}
sqlofpno = string.Format(sqlofpno, string.Join(",", getrownum_order), string.Join(",", pkcol), tablename, string.Join(" and ", getrownum_where));
object prerownum = dbconn1.ExecuteScalar(sqlofpno, parasofgetrownum, 2);
if (prerownum != null && prerownum.GetType() != typeof(System.DBNull))
{
beginindex = Convert.ToInt32(prerownum);
}
}
}
else
{
beginindex = (int)successcount;
}
}
}
/// <summary>
/// 功能说明：自动生成Sql语句 update 1 为 Insert 2 为 Update
/// 开发人员：ldd
/// </summary>
public static string GetFormatSql(string tableName, DataRow drSave, string primaryKey, string primaryKey2, string primaryKey3, string message, MySqlParameter[] parameters)
{
if (drSave == null)
{
return(message);
}
StringBuilder dataNameSB = new StringBuilder();
StringBuilder dataValueSB = new StringBuilder();
object[] dataArray = new object[drSave.Table.Columns.Count];
int index = -1;
int index2 = -1;
int index3 = -1;
int update = 0;
try
{
string selectSql = "select count(*) from " + tableName;
if (primaryKey == string.Empty && primaryKey2 == string.Empty && primaryKey3 == string.Empty)
{
update = 1;
}
else
{
#region 判断保存状态
MySqlParameter[] param = null;
if (primaryKey != string.Empty && primaryKey2 != string.Empty && primaryKey3 != string.Empty)
{
// param = new MySqlParameter[3] { new MySqlParameter("?"+primaryKey, MySqlDbType.String,20),
//new MySqlParameter("?"+primaryKey2, MySqlDbType.Int32,11),new MySqlParameter("?"+primaryKey3,MySqlDbType.String,20)};
param = new MySqlParameter[3] {
new MySqlParameter("?" + primaryKey, MySqlDbType.String, 50),
new MySqlParameter("?" + primaryKey2, MySqlDbType.String, 50), new MySqlParameter("?" + primaryKey3, MySqlDbType.String, 50)
};
foreach (DataColumn dc in drSave.Table.Columns)
{
if (dc.ColumnName == primaryKey)
{
param[0].Value = drSave[dc].ToString();
}
if (dc.ColumnName == primaryKey2)
{
param[1].Value = drSave[dc].ToString();
}
if (dc.ColumnName == primaryKey3)
{
param[2].Value = drSave[dc].ToString();
}
}
if (primaryKey != string.Empty)
{
selectSql += " where " + primaryKey + "=?" + primaryKey;
}
if (primaryKey2 != string.Empty)
{
selectSql += " and " + primaryKey2 + "=?" + primaryKey2;
}
if (primaryKey3 != string.Empty)
{
selectSql += " and " + primaryKey3 + "=?" + primaryKey3;
}
}
else if (primaryKey != string.Empty && primaryKey2 == string.Empty && primaryKey3 == string.Empty)
{
param = new MySqlParameter[1] {
new MySqlParameter("?" + primaryKey, MySqlDbType.String, 50)
};
foreach (DataColumn dc in drSave.Table.Columns)
{
if (dc.ColumnName == primaryKey.Trim().ToLower())
{
param[0].Value = drSave[dc].ToString();
}
}
if (primaryKey != string.Empty)
{
selectSql += " where " + primaryKey + "=?" + primaryKey;
}
}
else if (primaryKey != string.Empty && primaryKey2 != string.Empty && primaryKey3 == string.Empty)
{
param = new MySqlParameter[2] {
new MySqlParameter("?" + primaryKey, MySqlDbType.String, 50),
new MySqlParameter("?" + primaryKey2, MySqlDbType.String, 50)
};
try
{
foreach (DataColumn dc in drSave.Table.Columns)
{
if (dc.ColumnName == primaryKey.Trim().ToLower())
{
param[0].Value = drSave[dc].ToString();
}
if (dc.ColumnName == primaryKey2.Trim().ToLower())
{
param[1].Value = drSave[dc].ToString();
}
}
}
catch
{
}
if (primaryKey != string.Empty)
{
selectSql += " where " + primaryKey + "=?" + primaryKey;
}
if (primaryKey2 != string.Empty)
{
selectSql += " and " + primaryKey2 + "=?" + primaryKey2;
}
}
#endregion
object o = null;
o = MySQLHelper.GetSingle(selectSql, param);
if (Convert.ToInt32(o) > 0)
{
update = 2;
}
else
{
update = 1;
}
}
if (update == 1)
{
dataNameSB.Insert(0, " insert into " + tableName + " (");
dataValueSB.Insert(0, " values( ");
}
else
{
dataNameSB.Insert(0, " update " + tableName + " set ");
}
for (int i = 0; i < drSave.Table.Columns.Count; i++)
{
#region MyRegion
DataColumn dc = drSave.Table.Columns[i];
if (dc.ColumnName.ToLower().Trim() == "uid")
{
continue;
}
if (update == 1)
{
dataNameSB.Append(dc.ColumnName);
}
else
{
if (dc.ColumnName.ToLower().Equals(primaryKey.ToLower()))
{
index = i;
}
else if (dc.ColumnName.ToLower().Equals(primaryKey2.ToLower()))
{
index2 = i;
}
else if (dc.ColumnName.ToLower().Equals(primaryKey3.ToLower()))
{
index3 = i;
}
}
if (dc.DataType.ToString().Equals("System.Single") || dc.DataType.ToString().Equals("System.Float") || dc.DataType.ToString().Equals("System.Double"))
{
if (update == 1)
{
dataValueSB.Append("?" + dc.ColumnName);
}
else
{
dataNameSB.Append(dc.ColumnName + "=?" + dc.ColumnName);
}
parameters[i] = new MySqlParameter("?" + dc.ColumnName, MySqlDbType.Decimal);
if (drSave[dc].ToString().Trim() == string.Empty || drSave[dc].ToString().Trim().ToLower() == "null")
{
parameters[i].Value = DBNull.Value;
}
else
{
decimal mdata = 0;
try
{
bool bl = decimal.TryParse(drSave[dc].ToString(), out mdata);
parameters[i].Value = mdata;
}
catch
{
parameters[i].Value = 0;
}
}
///////////////////////////////////////////////////////////////////////////////////////
dataArray[i] = drSave[dc].ToString();
}
else if (dc.DataType.ToString().Equals("System.Int64") || dc.DataType.ToString().Equals("System.Int32"))
{
if (update == 1)
{
dataValueSB.Append("?" + dc.ColumnName);
}
else
{
dataNameSB.Append(dc.ColumnName + "=?" + dc.ColumnName);
}
parameters[i] = new MySqlParameter("?" + dc.ColumnName, MySqlDbType.Int32);
if (drSave[dc].ToString() == string.Empty || drSave[dc].ToString().Trim().ToLower() == "null")
{
parameters[i].Value = DBNull.Value;
}
else
{
int idata = 0;
try
{
bool bl = Int32.TryParse(drSave[dc].ToString(), out idata);
parameters[i].Value = idata;
}
catch
{
parameters[i].Value = 0;
}
}
///////////////////////////////////////////////////////////////////////////////////////
dataArray[i] = drSave[dc].ToString();
}
else if (dc.DataType.ToString().Equals("System.DateTime") || dc.DataType.ToString().Equals("MySql.Data.Types.MySqlDateTime"))
{
if (update == 1)
{
dataValueSB.Append("?" + dc.ColumnName);
}
else
{
dataNameSB.Append(dc.ColumnName + "=?" + dc.ColumnName);
}
parameters[i] = new MySqlParameter("?" + dc.ColumnName, MySqlDbType.DateTime);
DateTime date = DateTime.Now;
bool bldate = DateTime.TryParse(drSave[dc].ToString(), out date);
if (date == DateTime.MinValue)
{ // date = DateTime.Now;
drSave[dc] = DBNull.Value;
}
else
{
drSave[dc] = new MySql.Data.Types.MySqlDateTime(date);
}
if (drSave[dc].ToString() == "")
{
parameters[i].Value = DBNull.Value;
dataArray[i] = DBNull.Value;
}
else
{
parameters[i].Value = drSave[dc].ToString();
dataArray[i] = drSave[dc].ToString();
}
}
else if (dc.DataType.ToString().Equals("System.String"))
{
if (update == 1)
{
dataValueSB.Append("?" + dc.ColumnName);
}
else
{
dataNameSB.Append(dc.ColumnName + "=?" + dc.ColumnName);
}
parameters[i] = new MySqlParameter("?" + dc.ColumnName, MySqlDbType.String);
parameters[i].Value = drSave[dc].ToString();
dataArray[i] = drSave[dc].ToString();
}
else if (dc.DataType.ToString().Equals("System.Object"))
{
if (update == 1)
{
dataValueSB.Append("?" + dc.ColumnName);
}
else
{
dataNameSB.Append(dc.ColumnName + "=?" + dc.ColumnName);
}
parameters[i] = new MySqlParameter("?" + dc.ColumnName, MySqlDbType.String);
parameters[i].Value = drSave[dc].ToString();
dataArray[i] = drSave[dc].ToString();
}
if (i < drSave.Table.Columns.Count - 1)
{
dataNameSB.Append(" ,");
if (update == 1)
{
dataValueSB.Append(" ,");
}
}
if (update == 1)
{
if (i == drSave.Table.Columns.Count - 1)
{
dataNameSB.Append(")");
dataValueSB.Append(")");
}
}
#endregion
}
if (update == 2)
{
bool isFlag = false;
if (primaryKey == string.Empty && primaryKey2 == string.Empty)
{
return(message);
}
if (dataArray[index].ToString() == string.Empty)//&& dataArray[index2].ToString() == string.Empty && dataArray[index3].ToString() == string.Empty)
{
return(message);
}
if (primaryKey != string.Empty && dataArray[index].ToString() != string.Empty)
{
dataNameSB.Append(" where " + primaryKey + "=?" + primaryKey);
isFlag = true;
}
if (primaryKey2 != string.Empty && dataArray[index2].ToString() != string.Empty)
{
dataNameSB.Append(" and " + primaryKey2 + "=?" + primaryKey2);
isFlag = true;
}
if (primaryKey3 != string.Empty && dataArray[index3].ToString() != string.Empty)
{
dataNameSB.Append(" and " + primaryKey3 + "=?" + primaryKey3);
isFlag = true;
}
if (isFlag == false)
{
return(message);
}
}
}
catch (Exception ex)
{
throw ex;
}
return(dataNameSB.ToString() + dataValueSB.ToString());
}
//genral purpose select query for searching strings
public List <string>[] SearchLikeQuery(string tableName, List <string> fieldNames,
string searchField, string searchValue)
{
//RESULTS LIST CONSTRUCTION------------------------------------
//create main list object (rows) to hold individual lists (column)
List <string>[] results = new List <string> [fieldNames.Count];
//genric counter to be used for loops
int counter = 0;
//create the indivual list to hold the results for each field / column
while (counter < fieldNames.Count)
{
results[counter] = new List <string>();
counter++;
}
//reset counter
counter = 0;
//SELECT QUERY CONSTUCTION--------------------------------------------
//query initialisation - SElECT part
string query = "SELECT ";
//loop tough each field name and add to select statement
foreach (var field in fieldNames)
{
//add comma to separate list items not first item
if (counter > 0)
{
query += ",";
}
//add backquotes around fieldname and add field to query and a space
query += "`" + field + "` ";
counter++;
}
//rest counter
counter = 0;
//now add FROM part - table name woth backquotes around it
query += "FROM " + "`" + tableName + "`";
//now add where clause with Like for search
query += " WHERE " + "`" + searchField + "`" +
" LIKE " + "'" + "%" + searchValue + "%" + "'";
//comment / uncomment to debug - this messagebox will display the query
MessageBox.Show(query);
//CONNECT, RUN QUERY AND POPULATE RESULTS------------------------------------
//open connection
if (this.OpenConnection())
{
//create MYSQL command
MySqlCommand cmd = new MySqlCommand(query, connection);
//create a 'data reader' to get results
MySqlDataReader dataReader = cmd.ExecuteReader();
//loop thorugh each row of results
while (dataReader.Read())
{
//loop through each field name and add to select statemnts
foreach (var field in fieldNames)
{
//in the square brackets, the string must match
//the column / field name on the data base
// step through to check values
if (dataReader[field].GetType().ToString() ==
"MySql.Data.Types.MySqlDataTime")
{
//get the current my squl object
MySql.Data.Types.MySqlDateTime mySqlFormat =
(MySql.Data.Types.MySqlDateTime)dataReader[field];
//conver to c# data type
DateTime csFormat = mySqlFormat.GetDateTime();
//format in style of my sql
results[counter].Add(csFormat.ToString("yyyy-MM--dd"));
}
else
{
//add the value to results
results[counter].Add(dataReader[field].ToString());
}
counter++;
}
//reset counter
counter = 0;
}
//close data reader and connection
dataReader.Close();
this.CloseConnection();
}
//return results
return(results);
}
// genreal purpose slelect statement genrator and caller
public List <string>[] SelectQuery(string tableName, List <string> fieldNames)
{
//RESULTS LIST CONSTRUCTION
//creates main list object (rows) to hold indivaidual values(columns)
List <string>[] results = new List <string> [fieldNames.Count];
// generic counter to use for the loop
int counter = 0;
while (counter < fieldNames.Count)
{
results[counter] = new List <string>();
counter++;
}
//rest the counter
counter = 0;
//SELECT QUERY CONSRUCTION
//query initaliastion - SELECT part
string query = "SELECT ";
//loop through each fieldname and add to select statement
foreach (var field in fieldNames)
{
// add comma to seprate around filed name and add field to quey (and a space)
if (counter > 0)
{
query += ",";
}
// add backquotes around filed name and add field to query
query += "`" + field + "` ";
counter++;
}
//reset counter
counter = 0;
// now add FROM part - table name with backquotes around it
query += "FROM " + "`" + tableName + "`";
// comment / uncomment to debug - this message box will display the query
MessageBox.Show(query);
//CONNECT, RUN QUERY and POPULATE RESULTS
// open connection
if (this.OpenConnection())
{
//create MySQL command
MySqlCommand cmd = new MySqlCommand(query, connection);
//create a 'data reder' to get the results
MySqlDataReader dataReader = cmd.ExecuteReader();
//loop though each row off the results
while (dataReader.Read())
{
//loop through each fieldname and add to select statement
foreach (var field in fieldNames)
{
//in the square brackets, the string must match
//the column / filed on the databaase
//step through to check values
//if this is a data value then we need to conver to prevent issues
if (dataReader[field].GetType().ToString() ==
"MySql.Data.Types.MySqlDateTime")
{
//get the current mysql data object
MySql.Data.Types.MySqlDateTime mySqlFormat =
(MySql.Data.Types.MySqlDateTime)dataReader[field];
//convert to c# data type
DateTime csFormat = mySqlFormat.GetDateTime();
//format in the MySQL style before adding results
results[counter].Add(csFormat.ToString("yyyy-MM-dd"));
}
else
{
//add the value to the results
results[counter].Add(dataReader[field].ToString());
}
counter++;
}
// reset couner before geting new row of data
counter = 0;
}
// close data reader and connection
dataReader.Close();
this.CloseConnection();
}
//return the results
return(results);
}
/**
* Cria um objeto do tipo MySqlDateTime com a data e hora atual
* Objeto utilizado para gravar no banco de dados a data e hora atual
**/
public static MySql.Data.Types.MySqlDateTime pegarHoraAtualMySql()
{
System.DateTime dataAtual = System.DateTime.Now;
MySql.Data.Types.MySqlDateTime data = new MySql.Data.Types.MySqlDateTime(dataAtual);
return data;
}
public static Block newFromDataRow( object[ ] values )
{
Block b = null;
try
{
b = new Block( );
b.ipb_id = (int)( values[ 0 ] );
b.ipb_address = Utility.Net.IPAddress.newFromEncodedString( (byte[ ])values[ 1 ] );
b.ipb_user = (uint)values[ 2 ];
b.ipb_by = (uint)values[ 3 ];
b.ipb_reason = ASCIIEncoding.ASCII.GetString( (byte[ ])values[ 4 ] );
//System.OverflowException was unhandled
//Message="Value was either too large or too small for an Int32."
//Source="mscorlib"
//StackTrace:
// at System.Number.ParseInt32(String s, NumberStyles style, NumberFormatInfo info)
// at MySql.Data.Types.MySqlDateTime.ParseMySql(String s, Boolean is41)
// at MySql.Data.Types.MySqlDateTime.Parse(String s)
// at MySql.Data.Types.MySqlDateTime..ctor(String s)
// at BlockedOpenProxyReviewerBot.Block.newFromDataRow(Object[] values) in C:\Users\stwalkerster\Documents\Visual Studio 2008\Projects\BlockedOpenProxyReviewerBot\BlockedOpenProxyReviewerBot\Block.cs:line 37
// at BlockedOpenProxyReviewerBot.Database.getProxyBlocks() in C:\Users\stwalkerster\Documents\Visual Studio 2008\Projects\BlockedOpenProxyReviewerBot\BlockedOpenProxyReviewerBot\Database.cs:line 44
// at BlockedOpenProxyReviewerBot.Program.runBot() in C:\Users\stwalkerster\Documents\Visual Studio 2008\Projects\BlockedOpenProxyReviewerBot\BlockedOpenProxyReviewerBot\Program.cs:line 37
// at BlockedOpenProxyReviewerBot.Program..ctor(String[] args) in C:\Users\stwalkerster\Documents\Visual Studio 2008\Projects\BlockedOpenProxyReviewerBot\BlockedOpenProxyReviewerBot\Program.cs:line 24
// at BlockedOpenProxyReviewerBot.Program.Main(String[] args) in C:\Users\stwalkerster\Documents\Visual Studio 2008\Projects\BlockedOpenProxyReviewerBot\BlockedOpenProxyReviewerBot\Program.cs:line 12
// at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
// at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
// at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
// at System.Threading.ThreadHelper.ThreadStart()
//InnerException:
byte[ ] x = (byte[ ])values[ 5 ];
string y = ASCIIEncoding.ASCII.GetString( x );
MySql.Data.Types.MySqlDateTime z = new MySql.Data.Types.MySqlDateTime(
int.Parse( y.Substring( 0, 4 ) ),
int.Parse( y.Substring( 4, 2 ) ),
int.Parse( y.Substring( 6, 2 ) ),
int.Parse( y.Substring( 8, 2 ) ),
int.Parse( y.Substring( 10, 2 ) ),
int.Parse( y.Substring( 12, 2 ) )
);
b.ipb_timestamp = z.GetDateTime( );
// b.ipb_timestamp = new MySql.Data.Types.MySqlDateTime( ASCIIEncoding.ASCII.GetString( (byte[ ])values[ 5 ] ) ).GetDateTime( );
b.ipb_auto = (bool)values[ 6 ];
b.ipb_anon_only = (bool)values[ 7 ];
b.ipb_create_account = (bool)values[ 8 ];
b.ipb_expiry = ASCIIEncoding.ASCII.GetString( (byte[ ])values[ 9 ] );
if( ( (byte[ ])values[ 10 ] ).Length == 4 )
{
b.ipb_range_start = new IPAddress( (byte[ ])values[ 10 ] );
}
if( ( (byte[ ])values[ 11 ] ).Length == 4 )
{
b.ipb_range_end = new IPAddress( (byte[ ])values[ 11 ] );
}
b.ipb_enable_autoblock = (bool)values[ 12 ];
b.ipb_deleted = (bool)values[ 13 ];
b.ipb_block_email = (bool)values[ 14 ];
b.ipb_by_text = ASCIIEncoding.ASCII.GetString( (byte[ ])values[ 15 ] );
}
catch( OverflowException )
{
}
return b;
}
/// <summary>
/// 函数：SetColumnDataByType
/// 功能：根据类型ID设置数值
/// 作者：wl
/// </summary>
/// <param name="columnTypeId"></param>
/// <param name="dest"></param>
/// <param name="idxColumn"></param>
/// <param name="value"></param>
private static void SetColumnDataByType(int columnTypeId, DataRow dest, int idxColumn, object value)
{
//1:bit 2:int8 3:int16 4:int32 5:int64 6:uint8 7:uint16 8:uint32 9:uint64 10:varchar 11:MysqlDateTime 12:DateTime
switch (columnTypeId)
{
case 1:
{
dest[idxColumn] = Convert.ToBoolean(value);
}
break;
case 2: //byte
{
dest[idxColumn] = Convert.ToInt16(value);
}
break;
case 3:
{
dest[idxColumn] = Convert.ToInt16(value);
}
break;
case 4:
{
dest[idxColumn] = Convert.ToInt32(value);
}
break;
case 5:
{
dest[idxColumn] = Convert.ToInt64(value);
}
break;
case 6: //sbyte
{
dest[idxColumn] = Convert.ToInt16(value);
}
break;
case 7:
{
dest[idxColumn] = Convert.ToUInt16(value);
}
break;
case 8:
{
dest[idxColumn] = Convert.ToUInt32(value);
}
break;
case 9:
{
dest[idxColumn] = Convert.ToUInt64(value);
}
break;
case 10:
{
dest[idxColumn] = value.ToString();
}
break;
case 11:
{
dest[idxColumn] = new MySql.Data.Types.MySqlDateTime(value.ToString());
}
break;
case 12:
{
dest[idxColumn] = Convert.ToDateTime(value.ToString());
}
break;
case 13:
{
dest[idxColumn] = Convert.ToDouble(value);
}
break;
}
}
public JsonResult Get(DateTime from, DateTime to, string region, string product)
{
List <KmlFileInfo> kmlFiles = new List <KmlFileInfo>();
string fromDateFormatString = string.Empty;
string toDateFormatString = string.Empty;
to = from;
fromDateFormatString = @"yyyy-MM-dd 00:00:00";
toDateFormatString = @"yyyy-MM-dd 23:00:00";
int queryProductId = ConvertProductStringToId(product);
using (MySqlConnection connection = new MySqlConnection(DatabaseConnectionstring))
{
connection.Open();
MySqlCommand command = new MySqlCommand();
command.Connection = connection;
command.CommandText = string.Format("SELECT * FROM jpssflood.kmlmetadata WHERE Date >= '{0}' AND Date <= '{1}' AND ProductId = {2} AND RegionId = {3} AND DistrictId >= 1 AND DistrictId <= 136 ORDER BY DistrictID DESC", from.ToString(fromDateFormatString), to.AddHours(1).ToString(toDateFormatString), queryProductId, 1);
MySqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
int kmlId = reader.GetInt32(0);
int productId = reader.GetInt32(1);
int regionId = reader.GetInt32(2);
int districtId = reader.GetInt32(3);
MySql.Data.Types.MySqlDateTime mySqldate = reader.GetMySqlDateTime(4);
string fileName = reader.GetString(5);
if (queryProductId == 1 || queryProductId == 6)
{
if (Region.Unknown == RegionUtil.GetRegionFromDisplayName(region))
{
if (!RegionUtil.IsDistrictInCountry(districtId, region))
{
continue;
}
fileName += "_L2";
}
else if (Region.All != RegionUtil.GetRegionFromDisplayName(region))
{
//if (regionFromDistrict != RegionUtil.GetRegionFromDisplayName(region))
if (!RegionUtil.IsDistrictInRegion(districtId, region))
{
continue;
}
fileName += "_L2";
}
}
kmlFiles.Add(new KmlFileInfo {
FullName = string.Format(kmlFullFilePathFormat, fileName), ShortName = fileName, DistrictId = districtId
});
}
}
return(Json(kmlFiles));
}
public static string ConvertToSqlFormat(MySqlDataReader rdr, int colIndex, bool wrapStringWithSingleQuote, bool escapeStringSequence, MySqlColumn col)
{
object ob = rdr[colIndex];
StringBuilder sb = new StringBuilder();
if (ob == null || ob is System.DBNull)
{
sb.AppendFormat("NULL");
}
else if (ob is System.String)
{
string str = (string)ob;
if (escapeStringSequence)
{
str = QueryExpress.EscapeStringSequence(str);
}
if (wrapStringWithSingleQuote)
{
sb.AppendFormat("'");
}
sb.Append(str);
if (wrapStringWithSingleQuote)
{
sb.AppendFormat("'");
}
}
else if (ob is System.Boolean)
{
sb.AppendFormat(Convert.ToInt32(ob).ToString());
}
else if (ob is System.Byte[])
{
if (((byte[])ob).Length == 0)
{
if (wrapStringWithSingleQuote)
{
return("''");
}
else
{
return("");
}
}
else
{
sb.AppendFormat(CryptoExpress.ConvertByteArrayToHexString((byte[])ob));
}
}
else if (ob is short)
{
sb.AppendFormat(((short)ob).ToString(_numberFormatInfo));
}
else if (ob is int)
{
sb.AppendFormat(((int)ob).ToString(_numberFormatInfo));
}
else if (ob is long)
{
sb.AppendFormat(((long)ob).ToString(_numberFormatInfo));
}
else if (ob is ushort)
{
sb.AppendFormat(((ushort)ob).ToString(_numberFormatInfo));
}
else if (ob is uint)
{
sb.AppendFormat(((uint)ob).ToString(_numberFormatInfo));
}
else if (ob is ulong)
{
sb.AppendFormat(((ulong)ob).ToString(_numberFormatInfo));
}
else if (ob is double)
{
sb.AppendFormat(((double)ob).ToString(_numberFormatInfo));
}
else if (ob is decimal)
{
sb.AppendFormat(((decimal)ob).ToString(_numberFormatInfo));
}
else if (ob is float)
{
sb.AppendFormat(((float)ob).ToString(_numberFormatInfo));
}
else if (ob is byte)
{
sb.AppendFormat(((byte)ob).ToString(_numberFormatInfo));
}
else if (ob is sbyte)
{
sb.AppendFormat(((sbyte)ob).ToString(_numberFormatInfo));
}
else if (ob is TimeSpan)
{
TimeSpan ts = (TimeSpan)ob;
if (wrapStringWithSingleQuote)
{
sb.AppendFormat("'");
}
sb.AppendFormat(ts.Hours.ToString().PadLeft(2, '0'));
sb.AppendFormat(":");
sb.AppendFormat(ts.Minutes.ToString().PadLeft(2, '0'));
sb.AppendFormat(":");
sb.AppendFormat(ts.Seconds.ToString().PadLeft(2, '0'));
if (wrapStringWithSingleQuote)
{
sb.AppendFormat("'");
}
}
else if (ob is System.DateTime)
{
if (wrapStringWithSingleQuote)
{
sb.AppendFormat("'");
}
sb.AppendFormat(((DateTime)ob).ToString("yyyy-MM-dd HH:mm:ss", _dateFormatInfo));
if (col.TimeFractionLength > 0)
{
sb.Append(".");
string _microsecond = rdr.GetMySqlDateTime(colIndex).Millisecond.ToString();
if (_microsecond.Length < col.TimeFractionLength)
{
_microsecond = _microsecond.PadLeft(col.TimeFractionLength, '0');
}
else if (_microsecond.Length > col.TimeFractionLength)
{
_microsecond = _microsecond.Substring(0, col.TimeFractionLength);
}
sb.Append(_microsecond.ToString().PadLeft(col.TimeFractionLength, '0'));
}
if (wrapStringWithSingleQuote)
{
sb.AppendFormat("'");
}
}
else if (ob is MySql.Data.Types.MySqlDateTime)
{
MySql.Data.Types.MySqlDateTime mdt = (MySql.Data.Types.MySqlDateTime)ob;
if (mdt.IsNull)
{
sb.AppendFormat("NULL");
}
else
{
if (mdt.IsValidDateTime)
{
DateTime dtime = mdt.Value;
if (wrapStringWithSingleQuote)
{
sb.AppendFormat("'");
}
if (col.MySqlDataType == "datetime")
{
sb.AppendFormat(dtime.ToString("yyyy-MM-dd HH:mm:ss", _dateFormatInfo));
}
else if (col.MySqlDataType == "date")
{
sb.AppendFormat(dtime.ToString("yyyy-MM-dd", _dateFormatInfo));
}
else if (col.MySqlDataType == "time")
{
sb.AppendFormat(dtime.ToString("HH:mm:ss", _dateFormatInfo));
}
else
{
sb.AppendFormat(dtime.ToString("yyyy-MM-dd HH:mm:ss", _dateFormatInfo));
}
if (col.TimeFractionLength > 0)
{
sb.Append(".");
sb.Append(((MySql.Data.Types.MySqlDateTime)ob).Millisecond.ToString().PadLeft(col.TimeFractionLength, '0'));
}
if (wrapStringWithSingleQuote)
{
sb.AppendFormat("'");
}
}
else
{
if (wrapStringWithSingleQuote)
{
sb.AppendFormat("'");
}
if (col.MySqlDataType == "datetime")
{
sb.AppendFormat("0000-00-00 00:00:00");
}
else if (col.MySqlDataType == "date")
{
sb.AppendFormat("0000-00-00");
}
else if (col.MySqlDataType == "time")
{
sb.AppendFormat("00:00:00");
}
else
{
sb.AppendFormat("0000-00-00 00:00:00");
}
if (col.TimeFractionLength > 0)
{
sb.Append(".".PadRight(col.TimeFractionLength, '0'));
}
if (wrapStringWithSingleQuote)
{
sb.AppendFormat("'");
}
}
}
}
else if (ob is System.Guid)
{
if (col.MySqlDataType == "binary(16)")
{
sb.Append(CryptoExpress.ConvertByteArrayToHexString(((Guid)ob).ToByteArray()));
}
else if (col.MySqlDataType == "char(36)")
{
if (wrapStringWithSingleQuote)
{
sb.AppendFormat("'");
}
sb.Append(ob);
if (wrapStringWithSingleQuote)
{
sb.AppendFormat("'");
}
}
else
{
if (wrapStringWithSingleQuote)
{
sb.AppendFormat("'");
}
sb.Append(ob);
if (wrapStringWithSingleQuote)
{
sb.AppendFormat("'");
}
}
}
else
{
throw new Exception("Unhandled data type. Current processing data type: " + ob.GetType().ToString() + ". Please report this bug with this message to the development team.");
}
return(sb.ToString());
}
private static void SetColumnDataByTypeBinary(int columnType, DataRow drDest, int idxColumn,
byte[] src, ref int idxRead)
{
//1:bit 2:int8 3:int16 4:int32 5:int64 6:uint8 7:uint16 8:uint32 9:uint64 10:varchar 11:MysqlDateTime 12:DateTime
switch (columnType)
{
case 1:
{
drDest[idxColumn] = BitConverter.ToBoolean(src, idxRead);
idxRead++;
}
break;
case 2:
{
drDest[idxColumn] = BitConverter.ToInt16(src, idxRead);
idxRead += 2;
}
break;
case 3:
{
drDest[idxColumn] = BitConverter.ToInt16(src, idxRead);
idxRead += 2;
}
break;
case 4:
{
drDest[idxColumn] = BitConverter.ToInt32(src, idxRead);
idxRead += 4;
}
break;
case 5:
{
drDest[idxColumn] = BitConverter.ToInt64(src, idxRead);
idxRead += 8;
}
break;
case 6:
{
drDest[idxColumn] = BitConverter.ToInt16(src, idxRead);
idxRead += 2;
}
break;
case 7:
{
drDest[idxColumn] = BitConverter.ToUInt16(src, idxRead);
idxRead += 2;
}
break;
case 8:
{
drDest[idxColumn] = BitConverter.ToUInt32(src, idxRead);
idxRead += 4;
}
break;
case 9:
{
drDest[idxColumn] = BitConverter.ToUInt64(src, idxRead);
idxRead += 8;
}
break;
case 10:
{
int columnLen = BitConverter.ToInt32(src, idxRead);
idxRead += 4;
drDest[idxColumn] = Encoding.UTF8.GetString(src, idxRead, columnLen);
idxRead += columnLen;
}
break;
case 11:
{
long ticks = BitConverter.ToInt64(src, idxRead);
idxRead += 8;
drDest[idxColumn] = new MySql.Data.Types.MySqlDateTime(new DateTime(ticks));
}
break;
case 12:
{
long ticks = BitConverter.ToInt64(src, idxRead);
idxRead += 8;
drDest[idxColumn] = new DateTime(ticks);
}
break;
case 13:
{
drDest[idxColumn] = BitConverter.ToDouble(src, idxRead);
idxRead += 8;
}
break;
}
}
