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; } }