/// <summary> /// Simple helper method that creates a correct IntegerIntegerListDictionary and returns /// it's PyDataType representation, ready to be sent to the EVE Online client /// /// IMPORTANT: The first field MUST be ordered (direction doesn't matter) for this method /// to properly work /// </summary> /// <param name="reader">The MySqlDataReader to read the data from</param> /// <returns></returns> public static PyDictionary <PyInteger, PyList <PyInteger> > FromMySqlDataReader(MySqlDataReader reader) { PyDictionary <PyInteger, PyList <PyInteger> > result = new PyDictionary <PyInteger, PyList <PyInteger> >(); Type keyType = reader.GetFieldType(0); Type valType = reader.GetFieldType(1); if (keyType != typeof(long) && keyType != typeof(int) && keyType != typeof(short) && keyType != typeof(byte) && keyType != typeof(ulong) && keyType != typeof(uint) && keyType != typeof(ushort) && keyType != typeof(sbyte) && valType != typeof(long) && valType != typeof(int) && valType != typeof(short) && valType != typeof(byte) && valType != typeof(ulong) && valType != typeof(uint) && valType != typeof(ushort) && valType != typeof(sbyte)) { throw new InvalidDataException("Expected two fields of type int"); } // get first key and start preparing the values int key = 0; PyList <PyInteger> currentList = new PyList <PyInteger>(); while (reader.Read() == true) { // ignore null keys if (reader.IsDBNull(0) == true) { continue; } int newKey = reader.GetInt32(0); int val = 0; // if the read key doesn't match the one read earlier if (newKey != key) { // do not add an entry to the dict unless the old id was present if (key != 0) { result[key] = currentList; } currentList = new PyList <PyInteger>(); key = newKey; } if (reader.IsDBNull(1) == false) { val = reader.GetInt32(1); } // add the current value to the list currentList.Add(val); } // ensure the last key is saved to the list result[key] = currentList; return(result); }
public string GetValue(MySqlDataReader tmp, string Champ) { string ret = ""; //if (Tab.GetFieldType(Tab.GetOrdinal(nomCol)) == typeof(string)) if ((tmp.GetFieldType(tmp.GetOrdinal(Champ)) == typeof(DateTime))) { if ((tmp.GetValue(tmp.GetOrdinal(Champ)).ToString() == "")) { ret = "NULL#"; } else { ret = string.Format("{0:dd.MM.yyyy}", tmp.GetValue(tmp.GetOrdinal(Champ))); } } else if ((tmp.GetValue(tmp.GetOrdinal(Champ)).ToString() != "") && (tmp.GetFieldType(tmp.GetOrdinal(Champ)) == typeof(double) || tmp.GetFieldType(tmp.GetOrdinal(Champ)) == typeof(float))) { ret = string.Format("{0:#0.00;-#.00;'0.00'}", Math.Round(tmp.GetDouble(tmp.GetOrdinal(Champ)) / 0.05) * 0.05); ret = getFormatCur(ret); } else { if (tmp.GetValue(tmp.GetOrdinal(Champ)).ToString() == "") { ret = ""; } else { ret = tmp.GetValue(tmp.GetOrdinal(Champ)).ToString(); } } return(ret); }
private void Listar_Load(object sender, EventArgs e) { using (MySqlConnection connecta = Dados.DAO_conn.getSqlConnection()) try { connecta.Open(); string buscaf = "SELECT cod_titulo, titulo FROM filmes UNION " + "SELECT cod_titulo, titulo FROM series"; MySqlCommand insertt = new MySqlCommand(buscaf, connecta); MySqlDataReader listaf = insertt.ExecuteReader(); int nColunas = listaf.FieldCount; for (int i = 0; i < nColunas; i++) { dataGridView1.Columns.Add(listaf.GetName(i).ToUpper(), listaf.GetName(i).ToUpper()); } //Define um array de strings com ncolunas string[] linhaDados = new string[nColunas]; //Percorre o DataRead while (listaf.Read()) { //Percorre cada uma das colunas for (int a = 0; a < nColunas; a++) { //Verifica o tipo de dados da coluna if (listaf.GetFieldType(a).ToString() == "System.Int32") { linhaDados[a] = listaf.GetInt32(a).ToString(); } if (listaf.GetFieldType(a).ToString() == "System.String") { linhaDados[a] = listaf.GetString(a).ToString(); } if (listaf.GetFieldType(a).ToString() == "System.DateTime") { linhaDados[a] = listaf.GetDateTime(a).ToString(); } } //Atribui a linha ao datagridview dataGridView1.Rows.Add(linhaDados); } } catch (Exception ex) { MessageBox.Show(ex.Message); } }
private void encheDataGridView() { try { MySqlConnection mConn2 = new MySqlConnection("Persist Security Info=False; server=192.168.6.100;database=intencoes_matricula;uid=admin;password=yb5731"); // Abre a conexão mConn2.Open(); //Query SQL MySqlCommand command = new MySqlCommand("SELECT id_aluno as Codigo, nome_aluno as 'Nome do Aluno', " + "dataNasc_aluno as 'Data de Nascimento', escola_origem as 'Escola de Origem' " + "FROM ALUNO", mConn2); //obtem um datareader MySqlDataReader dr = command.ExecuteReader(); //Obtem o número de colunas int nColunas = dr.FieldCount; //percorre as colunas obtendo o seu nome e incluindo no DataGridView for (int i = 0; i < nColunas; i++) { dataGridView1.Columns.Add(dr.GetName(i).ToString(), dr.GetName(i).ToString()); } //define um array de strings com nCOlunas string[] linhaDados = new string[nColunas]; //percorre o DataRead while (dr.Read()) { //percorre cada uma das colunas for (int a = 0; a < nColunas; a++) { //verifica o tipo de dados da coluna if (dr.GetFieldType(a).ToString() == "System.Int32") { linhaDados[a] = dr.GetInt32(a).ToString(); } if (dr.GetFieldType(a).ToString() == "System.String") { linhaDados[a] = dr.GetString(a).ToString(); } if (dr.GetFieldType(a).ToString() == "System.DateTime") { linhaDados[a] = dr.GetDateTime(a).ToString(); } } //atribui a linha ao datagridview dataGridView1.Rows.Add(linhaDados); } } catch (MySqlException e) { MessageBox.Show("" + e); } }
/// <summary> /// Fill /// </summary> /// <param name="ds"></param> public void Fill(DataSet ds) { if (ds == null) { ds = new DataSet(); } using (MySqlDataReader dr = command.ExecuteReader()) { do { var dt = new DataTable(); var columns = dt.Columns; var rows = dt.Rows; for (int i = 0; i < dr.FieldCount; i++) { string name = dr.GetName(i).Trim(); if (dr.GetFieldType(i).Name == "MySqlDateTime") { if (!columns.Contains(name)) { columns.Add(new DataColumn(name, UtilConstants.DateType)); } else { columns.Add(new DataColumn(name + i, UtilConstants.DateType)); } } else { if (!columns.Contains(name)) { columns.Add(new DataColumn(name, dr.GetFieldType(i))); } else { columns.Add(new DataColumn(name + i, dr.GetFieldType(i))); } } } while (dr.Read()) { DataRow daRow = dt.NewRow(); for (int i = 0; i < columns.Count; i++) { daRow[columns[i].ColumnName] = dr.GetValue(i); } dt.Rows.Add(daRow); } dt.AcceptChanges(); ds.Tables.Add(dt); } while (dr.NextResult()); } }
private static Result OnQuery() { var columns = new List <string>(); var rows = new List <string[]>(); var types = new List <string>(); for (var index = 0; index < reader.FieldCount; index++) { var columnName = reader.GetName(index); var columnType = MySQLAdapter.GetType(reader.GetFieldType(index)); columns.Add(columnName); types.Add(columnType); } while (reader.Read()) { var row = new string[reader.FieldCount]; for (var index = 0; index < reader.FieldCount; index++) { var columnName = reader.GetName(index); var columnType = MySQLAdapter.GetType(reader.GetFieldType(index)); var columnIndex = columns.IndexOf(columnName); if (types[columnIndex] != "array") { types[columnIndex] = columnType; } object value = null; if (!reader.IsDBNull(index)) { if (columnType == "array") { value = MySQLAdapter.GetBytes(index); } else { value = reader.GetValue(index); } } if (value == null) { value = ""; } row[index] = value.ToString(); } rows.Add(row); } return(End(new Result { Success = true, Columns = columns.ToArray(), Rows = rows.ToArray(), Types = types.ToArray() })); }
public void Start() { try { Table data = new Table(Connection.plugin.lua); MySqlDataReader reader = DBCommand.ExecuteReader(); while (reader.Read()) { Table row = new Table(Connection.plugin.lua); for (int i = 0; i < reader.FieldCount; i++) { switch (reader.GetFieldType(i).Name) { case "Int32": row[reader.GetName(i)] = reader.IsDBNull(i) ? null : DynValue.NewNumber(reader.GetUInt32(i)); break; case "Int64": row[reader.GetName(i)] = reader.IsDBNull(i) ? null : DynValue.NewNumber(reader.GetUInt64(i)); break; case "String": row[reader.GetName(i)] = reader.IsDBNull(i) ? null : DynValue.NewString(reader.GetString(i)); break; case "Boolean": row[reader.GetName(i)] = reader.IsDBNull(i) ? null : DynValue.NewBoolean(reader.GetBoolean(i)); break; default: row.Append(DynValue.NewString(reader.GetFieldType(i).Name)); break; } } data.Append(DynValue.NewTable(row)); } reader.Close(); DynValue onSuccess = table.Get("onSuccess"); if (onSuccess != null) { onSuccess.Function.Call(table, data); } DBCommand.Dispose(); } catch (Exception ex) { DynValue onError = table.Get("onError"); if (onError != null) { onError.Function.Call(table, ex.Message); } } }
public void SendSuccessWithReader(HallServerSession session, string cmd, MySqlDataReader reader, params string[] fields) { if (session == null) { return; } JObject jObj = new JObject(); jObj["cmd"] = cmd; jObj["ret_code"] = 0; foreach (string field in fields) { if (reader.GetFieldType(field) == typeof(DateTime)) { jObj[field] = reader.GetDateTimeSafe(field).ToString(DateTimeUtil.format); } else { jObj[field] = reader.GetStringSafe(field); } } Send(session, jObj); }
/// <summary> /// Fill /// </summary> /// <param name="ds"></param> public void Fill(DataSet ds) { if (ds == null) { ds = new DataSet(); } using (MySqlDataReader dr = command.ExecuteReader()) { do { var dt = new DataTable(); var columns = dt.Columns; var rows = dt.Rows; for (int i = 0; i < dr.FieldCount; i++) { string name = dr.GetName(i).Trim(); if (!columns.ContainsKey(name)) columns.Add(new DataColumn(name, dr.GetFieldType(i))); } while (dr.Read()) { DataRow daRow = new DataRow(); for (int i = 0; i < columns.Count; i++) { if (!daRow.ContainsKey(columns[i].ColumnName)) daRow.Add(columns[i].ColumnName, dr.GetValue(i)); } dt.Rows.Add(daRow); } ds.Tables.Add(dt); } while (dr.NextResult()); } }
public DataView RunProcGetDataView(string sql, int rowOffset, int rowLimit, out int count) { count = this.RunProcGetCount("select count(*) from (" + sql + ") as A"); DataTable dataTable = new DataTable(); dataTable.Columns.Clear(); MySqlDataReader mySqlDataReader = this.RunProcGetReader(sql); for (int i = 0; i < mySqlDataReader.FieldCount; i++) { dataTable.Columns.Add(mySqlDataReader.GetName(i), mySqlDataReader.GetFieldType(i)); } int num = 0; while (mySqlDataReader.Read()) { if (num > rowOffset - 1 && num < rowOffset + rowLimit) { DataRow dataRow = dataTable.NewRow(); for (int j = 0; j < mySqlDataReader.FieldCount; j++) { dataRow[j] = mySqlDataReader[j]; } dataTable.Rows.Add(dataRow); } if (num >= rowOffset + rowLimit) { break; } num++; } mySqlDataReader.Close(); this.Dispose(); return(dataTable.DefaultView); }
/// <summary> /// Gets CRUD schema from MySqlReader per particular QuerySource. /// If source is null then all columns from reader are copied. /// Note: this code was purposely made provider specific because other providers may treat some nuances differently /// </summary> public static Schema GetSchemaFromReader(string name, QuerySource source, MySqlDataReader reader) { var table = name; var fdefs = new List <Schema.FieldDef>(); for (int i = 0; i < reader.FieldCount; i++) { var fname = reader.GetName(i); var ftype = reader.GetFieldType(i); var def = new Schema.FieldDef(fname, ftype, source != null ? (source.HasPragma ? source.ColumnDefs[fname] : null) : null); fdefs.Add(def); } if (source != null) { if (source.HasPragma && source.ModifyTarget.IsNotNullOrWhiteSpace()) { table = source.ModifyTarget; } } if (table.IsNullOrWhiteSpace()) { table = Guid.NewGuid().ToString(); } return(new Schema(table, source != null ? source.ReadOnly : true, fdefs)); }
public override void ReadValue(int i) { if (m_myReader != null) { var type = m_myReader.GetFieldType(i); if (type == typeof(MySqlDateTime)) { var val = m_myReader.GetValue(i); if (val == DBNull.Value || val == null) { SetNull(); return; } var dt = (MySqlDateTime)val; DateTimeEx dtex = new DateTimeEx(); dtex.Year = dt.Year; dtex.Month = dt.Month; dtex.Day = dt.Day; dtex.Hour = dt.Hour; dtex.Minute = dt.Minute; dtex.Second = dt.Second; dtex._Millisecond = dt.Millisecond; SetDateTimeEx(dtex); return; } } base.ReadValue(i); }
/// <summary> /// Fill /// </summary> /// <param name="dt"></param> public void Fill(DataTable dt) { if (dt == null) { dt = new DataTable(); } var columns = dt.Columns; var rows = dt.Rows; using (MySqlDataReader dr = command.ExecuteReader()) { for (int i = 0; i < dr.FieldCount; i++) { string name = dr.GetName(i).Trim(); if (!columns.Contains(name)) { columns.Add(new DataColumn(name, dr.GetFieldType(i))); } } while (dr.Read()) { DataRow daRow = dt.NewRow(); for (int i = 0; i < columns.Count; i++) { daRow[columns[i].ColumnName] = dr.GetValue(i); } dt.Rows.Add(daRow); } } }
private unRetourRequete copieDonnees(ref MySqlDataReader source) { unRetourRequete retour = new unRetourRequete(); try { retour.Tables.Add("Resultat1"); for (int i = 0; i <= source.FieldCount - 1; i++) { retour.Tables[0].Columns.Add(source.GetName(i), source.GetFieldType(i)); } while (source.Read()) { retour.Tables[0].Rows.Add(); for (int numColonne = 0; numColonne <= source.FieldCount - 1; numColonne++) { retour.Tables[0].Rows[retour.Tables[0].Rows.Count - 1][numColonne] = source[numColonne]; } } return(retour); } catch (Exception ex) { _lastError = ex.Message; } finally { try { source.Close(); } catch { } } return(null); }
/// <summary> /// Obtains the current field type off a MySqlDataReader for the given column /// </summary> /// <param name="reader">The data reader to use</param> /// <param name="index">The column to get the type from</param> /// <returns></returns> /// <exception cref="InvalidDataException">If the type is not supported</exception> public static FieldType GetFieldType(MySqlDataReader reader, int index) { Type type = reader.GetFieldType(index); if (type == typeof(string)) { return(FieldType.WStr); } if (type == typeof(ulong)) { return(FieldType.UI8); } if (type == typeof(long)) { return(FieldType.I8); } if (type == typeof(uint)) { return(FieldType.UI4); } if (type == typeof(int)) { return(FieldType.I4); } if (type == typeof(ushort)) { return(FieldType.UI2); } if (type == typeof(short)) { return(FieldType.I2); } if (type == typeof(sbyte)) { return(FieldType.I1); } if (type == typeof(byte)) { return(FieldType.UI1); } if (type == typeof(byte[])) { return(FieldType.Bytes); } if (type == typeof(double) || type == typeof(decimal)) { return(FieldType.R8); } if (type == typeof(float)) { return(FieldType.R4); } if (type == typeof(bool)) { return(FieldType.Bool); } throw new InvalidDataException($"Unknown field type {type}"); }
public override Type GetFieldType(int ordinal) { if (types != null) { return(types[ordinal].ClrEquivalentType); } return(reader.GetFieldType(ordinal)); }
private string DataReaderToJson(MySqlDataReader dataReader) { StringBuilder jsonString = new StringBuilder(); jsonString.Append("["); if (!dataReader.Read()) { return("[{ }]"); } else { do { jsonString.Append("{"); for (int i = 0; i < dataReader.FieldCount; i++) { Type type = dataReader.GetFieldType(i); string strKey = dataReader.GetName(i); string strValue = dataReader[i].ToString(); jsonString.Append("\"" + strKey + "\":"); strValue = String.Format(strValue, type); //datetime不能出现为空的情况,所以将其转换成字符串来进行处理。 //需要加""的 if (type == typeof(string) || type == typeof(DateTime)) { if (i <= dataReader.FieldCount - 1) { jsonString.Append("\"" + strValue + "\","); } else { jsonString.Append(strValue); } } //不需要加""的 else { if (i <= dataReader.FieldCount - 1) { jsonString.Append("" + strValue + ","); } else { jsonString.Append(strValue); } } } jsonString.Append("},"); } while (dataReader.Read()); } dataReader.Close(); jsonString.Remove(jsonString.Length - 3, 3); jsonString.Append("}"); jsonString.Append("]"); return(jsonString.ToString()); }
public string FetchString(MySqlDataReader reader, int iField, string sDefaultValue) { if (reader.IsDBNull(iField)) { return(sDefaultValue); } else if (reader.GetFieldType(iField) == typeof(System.Boolean)) { return(ZData.FetchString(reader.GetBoolean(iField))); } else if (reader.GetFieldType(iField) == typeof(System.DateTime)) { return(ZData.FetchString(reader.GetDateTime(iField))); } else { return(reader.GetValue(iField).ToString()); } }
/// <summary> /// 执行 T-SQL 语句,并返回数据库记录集合 [ 含分页 ] /// </summary> /// <param name="sql">string T-SQL 语句</param> /// <param name="values">params object[] 参数集合 </param> /// <param name="pageId">int 页码</param> /// <param name="pageSize">int 页尺寸</param> /// <returns>Utils.HashCollection 记录集合</returns> public Utils.Hash GetHashCollectionByPageId(string sql, int pageId, int pageSize, params object[] values) { using (MySqlConnection conn = new MySqlConnection(this._ConnectionString)) { using (MySqlCommand comd = new MySqlCommand(sql, conn)) { double recordCount = 0; double recordStart = ((pageId - 1) * pageSize); double recordEnd = recordStart + pageSize; this.AddParameters(comd, values); conn.Open(); try { using (MySqlDataReader reader = comd.ExecuteReader()) { Utils.Hash result = new Utils.Hash(); Utils.HashCollection data = new Utils.HashCollection(); while (reader.Read()) { if (recordCount >= recordStart && recordCount < recordEnd) { Utils.Hash item = new Utils.Hash(); for (int i = 0; i < reader.FieldCount; i++) { if (reader.GetValue(i) == DBNull.Value && reader.GetFieldType(i).Name == "String") { item[reader.GetName(i)] = String.Empty; } else { item[reader.GetName(i)] = reader.GetValue(i); } } data.Add(item); } recordCount++; } reader.Close(); result["data"] = data; result["pageCount"] = Math.Ceiling(recordCount / pageSize); return(result); } } catch (Exception e) { throw e; } finally { conn.Close(); } } } }
public void SetFields(MySqlDataReader dataReader) { for (var col = 0; col < dataReader.FieldCount; col++) { AddField( dataReader.GetName(col).ToString(), dataReader.GetFieldType(col), dataReader.GetDataTypeName(col) ); } }
public void Bug47467() { MySqlCommand cmd = new MySqlCommand("SELECT 1 as c1", st.conn); using (MySqlDataReader reader = cmd.ExecuteReader()) { reader.Read(); Type t = reader.GetFieldType("c1"); Exception ex = Assert.Throws <IndexOutOfRangeException>(() => reader.GetOrdinal("nocol")); Assert.True(ex.Message.IndexOf("nocol") != -1); } }
internal DataTable GetDataTable(string SqlSt) { MySqlCommand MyComm = new MySqlCommand(SqlSt, glb_cnn); MySqlDataReader MyDatareadre = null; DataTable MyTable = new DataTable(); DataRow MyDataRow = null; int ColsCount = 0; try { if (glb_cnn.State == System.Data.ConnectionState.Closed) { glb_cnn.Open(); } MyDatareadre = MyComm.ExecuteReader(); for (ColsCount = 0; ColsCount < MyDatareadre.FieldCount; ColsCount++) { MyTable.Columns.Add(MyDatareadre.GetName(ColsCount), MyDatareadre.GetFieldType(ColsCount)); } while (MyDatareadre.Read()) { MyDataRow = MyTable.NewRow(); for (ColsCount = 0; ColsCount < MyDatareadre.FieldCount; ColsCount++) { MyDataRow[ColsCount] = MyDatareadre.GetValue(ColsCount); } MyTable.Rows.Add(MyDataRow); } if (MyDatareadre != null) { if (MyDatareadre.IsClosed) { MyDatareadre.Close(); } } MyDatareadre.Close(); MyDatareadre.Dispose(); return(MyTable); } catch (Exception ErrGetData) { MessageBox.Show(ErrGetData.Source + Convert.ToChar(13) + ErrGetData.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error); return(null); } }
private static DataColumn[] _GetHeaderColumnsFromReader(MySqlDataReader reader) { int fieldCount = reader.FieldCount; DataColumn[] headers = new DataColumn[fieldCount + 1]; for (int i = 0; i < fieldCount; i++) { Type fieldType = reader.GetFieldType(i); headers[i] = new DataColumn(reader.GetName(i).ToUpper(), fieldType); } return(headers); }
public void NumericAsBinary() { MySqlCommand cmd = new MySqlCommand("SELECT IFNULL(NULL,0) AS MyServerID", conn); using (MySqlDataReader reader = cmd.ExecuteReader()) { reader.Read(); Assert.AreEqual("BIGINT", reader.GetDataTypeName(0)); Assert.AreEqual(typeof(Int64), reader.GetFieldType(0)); Assert.AreEqual("System.Int64", reader.GetValue(0).GetType().FullName); Assert.AreEqual(0, reader.GetValue(0)); } }
/// <summary> /// Simple helper method that creates a correct IntegerIntegerListDictionary and returns /// it's PyDataType representation, ready to be sent to the EVE Online client /// /// IMPORTANT: The first field MUST be ordered (direction doesn't matter) for this method /// to properly work /// </summary> /// <param name="connection">The connection used</param> /// <param name="reader">The MySqlDataReader to read the data from</param> /// <param name="keyColumnIndex">The column to use as index for the IntPackedRowListDictionary</param> /// <returns></returns> public static PyDataType FromMySqlDataReader(IDatabaseConnection connection, MySqlDataReader reader, int keyColumnIndex) { DBRowDescriptor descriptor = DBRowDescriptor.FromMySqlReader(connection, reader); PyDictionary result = new PyDictionary(); Type keyType = reader.GetFieldType(keyColumnIndex); if (keyType != typeof(long) && keyType != typeof(int) && keyType != typeof(short) && keyType != typeof(byte) && keyType != typeof(ulong) && keyType != typeof(uint) && keyType != typeof(ushort) && keyType != typeof(sbyte)) { throw new InvalidDataException("Expected key type of integer"); } // get first key and start preparing the values int key = 0; PyList currentList = new PyList(); while (reader.Read() == true) { // ignore null keys if (reader.IsDBNull(keyColumnIndex) == true) { continue; } int newKey = reader.GetInt32(keyColumnIndex); // if the read key doesn't match the one read earlier if (newKey != key) { // do not add an entry to the dict unless the old id was present if (key != 0) { result[key] = currentList; } currentList = new PyList(); key = newKey; } // add the current value to the list currentList.Add(PyPackedRow.FromMySqlDataReader(reader, descriptor)); } // ensure the last key is saved to the list result[key] = currentList; return(result); }
/// <summary> /// Simple helper method that creates a correct IntegerIntegerDictionary and returns /// it's PyDataType representation, ready to be sent to the EVE Online client /// </summary> /// <param name="reader">The MySqlDataReader to read the data from</param> /// <returns></returns> public static PyDictionary <PyInteger, PyInteger> FromMySqlDataReader(MySqlDataReader reader) { PyDictionary <PyInteger, PyInteger> result = new PyDictionary <PyInteger, PyInteger>(); Type keyType = reader.GetFieldType(0); Type valType = reader.GetFieldType(1); if (keyType != typeof(long) && keyType != typeof(int) && keyType != typeof(short) && keyType != typeof(byte) && keyType != typeof(ulong) && keyType != typeof(uint) && keyType != typeof(ushort) && keyType != typeof(sbyte) && valType != typeof(long) && valType != typeof(int) && valType != typeof(short) && valType != typeof(byte) && valType != typeof(ulong) && valType != typeof(uint) && valType != typeof(ushort) && valType != typeof(sbyte)) { throw new InvalidDataException("Expected two fields of type int"); } while (reader.Read() == true) { // ignore null keys if (reader.IsDBNull(0) == true) { continue; } int key = reader.GetInt32(0); int val = 0; if (reader.IsDBNull(1) == false) { val = reader.GetInt32(1); } result[key] = val; } return(result); }
public static NameValueCollection[] Select(MySqlCtx ctx, string sql, NameValueCollection bindings) { MySqlConnection Connection = new MySqlConnection(ctx.GetConnectionString()); MySqlCommand Command = Connection.CreateCommand(); Command.CommandText = sql; foreach (string key in bindings) { Command.Parameters.AddWithValue(key, bindings[key]); } try { Connection.Open(); } catch (Exception e) { throw e; } List <NameValueCollection> ret = new List <NameValueCollection>(); using (MySqlDataReader reader = Command.ExecuteReader()) { while (reader.Read()) { NameValueCollection current = new NameValueCollection(); for (int col = 0; col < reader.FieldCount; col++) { switch (reader.GetFieldType(col).ToString()) { case "System.Int32": current.Add(reader.GetName(col).ToString(), reader.GetInt32(col).ToString()); break; case "System.String": current.Add(reader.GetName(col).ToString(), reader.GetString(col)); break; case "System.Double": current.Add(reader.GetName(col).ToString(), reader.GetDouble(col).ToString()); break; } } ret.Add(current); } } return(ret.ToArray()); }
public FieldType GetType(int index, ref MySqlDataReader reader) { Type type = reader.GetFieldType(index); switch (type.Name) { case "String": return(FieldType.Str); case "UInt32": return(FieldType.UI4); case "Int32": return(FieldType.I4); case "UInt16": return(FieldType.UI2); case "Int16": return(FieldType.I2); case "UInt64": return(FieldType.UI8); case "Int64": return(FieldType.I8); case "Byte[]": return(FieldType.Bytes); case "SByte": return(FieldType.I1);; case "Double": return(FieldType.R8); case "Decimal": return(FieldType.R4); case "Boolean": return(FieldType.Bool); case "Byte": return(FieldType.UI1); default: throw new Exception("Wrong FieldType"); } }
/// <summary> /// 查询字段属性 /// </summary> /// <param name="TableName">表名</param> /// <param name="Field">字段名</param> /// <param name="FieldType">字段类型</param> /// <returns>true 字段存在,false 字段不存在</returns> public bool CheckField(string TableName, string Field, out System.Type FieldType, out bool CanBeNull, out bool IsPrimaryKey) { FieldType = typeof(object); CanBeNull = false; IsPrimaryKey = false; if (string.IsNullOrEmpty(TableName)) { throw new NullReferenceException("TableName Is Null Or Empty"); } else if (string.IsNullOrEmpty(Field)) { throw new NullReferenceException("Field Is Null Or Empty"); } try { MySqlConnection conn = (MySqlConnection)GetConnection(); bool res = false; DataTable dt = conn.GetSchema("Columns", new string[] { null, null, TableName }); int m = dt.Columns.IndexOf("COLUMN_NAME"); int n = dt.Columns.IndexOf("IS_NULLABLE"); int o = dt.Columns.IndexOf("COLUMN_KEY"); for (int i = 0; i < dt.Rows.Count; i++) { DataRow dr = dt.Rows[i]; if (dr.ItemArray.GetValue(m).ToString().ToUpper() == Field.ToUpper()) { CanBeNull = dr.ItemArray.GetValue(n).ToString().ToUpper().Contains("YES"); IsPrimaryKey = dr.ItemArray.GetValue(o).ToString().ToUpper().Contains("PRI"); res = true; break; } } if (res) { MySqlCommand cmd = new MySqlCommand("select " + Field + " from " + TableName + "", conn); cmd.CommandTimeout = this.Timeout * 1000; MySqlDataReader da = cmd.ExecuteReader(); FieldType = da.GetFieldType(0); } CloseConnection(conn); return(res); } catch { return(false); } }
public DataTable ExecuteTable(string sql) { DataTable datatable = new DataTable(); MySqlConnection myConnection = new MySqlConnection(connectionString); MySqlCommand myORACCommand = myConnection.CreateCommand(); myORACCommand.CommandTimeout = 1000; myORACCommand.CommandText = sql; myConnection.Open(); MySqlDataReader dataReader = myORACCommand.ExecuteReader(); try { ///动态添加表的数据列 for (int i = 0; i < dataReader.FieldCount; i++) { DataColumn myDataColumn = new DataColumn(); myDataColumn.DataType = dataReader.GetFieldType(i); myDataColumn.ColumnName = dataReader.GetName(i); datatable.Columns.Add(myDataColumn); } ///添加表的数据 while (dataReader.Read()) { DataRow myDataRow = datatable.NewRow(); for (int i = 0; i < dataReader.FieldCount; i++) { myDataRow[i] = dataReader[i]; } datatable.Rows.Add(myDataRow); myDataRow = null; } ///关闭数据读取器 dataReader.Close(); myConnection.Close(); datatable.TableName = "table"; return(datatable); } catch (Exception ex) { ///抛出类型转换错误 //SystemError.CreateErrorLog(ex.Message); throw new Exception(ex.Message, ex); } }