/// <summary> /// Converts a NpgsqlDataReader to a DataSet /// <param name='reader'> /// NpgsqlDataReader to convert.</param> /// <returns> /// DataSet filled with the contents of the reader.</returns> /// </summary> public static DataSet ConvertDataReaderToDataSet(NpgsqlDataReader reader, string tabela) { DataSet dataSet = new DataSet(); do { // Create new data table DataTable schemaTable = reader.GetSchemaTable(); DataTable dataTable = new DataTable(tabela); if (schemaTable != null) { // A query returning records was executed for (int i = 0; i < schemaTable.Rows.Count; i++) { DataRow dataRow = schemaTable.Rows[i]; // Create a column name that is unique in the data table string columnName = (string)dataRow["ColumnName"]; //+ "<C" + i + "/>"; // Add the column definition to the data table DataColumn column = new DataColumn(columnName, (Type)dataRow["DataType"]); dataTable.Columns.Add(column); } dataSet.Tables.Add(dataTable); // Fill the data table we just created while (reader.Read()) { DataRow dataRow = dataTable.NewRow(); for (int i = 0; i < reader.FieldCount; i++) dataRow[i] = reader.GetValue(i); dataTable.Rows.Add(dataRow); } } else { // No records were returned DataColumn column = new DataColumn("RowsAffected"); dataTable.Columns.Add(column); dataSet.Tables.Add(dataTable); DataRow dataRow = dataTable.NewRow(); dataRow[0] = reader.RecordsAffected; dataTable.Rows.Add(dataRow); } } while (reader.NextResult()); return dataSet; }
public string NewID() { string i = ""; string sQuery = "select '" + clsGlobal.pstrservercode + "'||nextval('tbm_carrepair_nextid') as id;"; Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(sQuery, Koneksi); cmd.CommandText = sQuery; try { Npgsql.NpgsqlDataReader rdr = cmd.ExecuteReader(); if (rdr.Read()) { if (!rdr.IsDBNull(rdr.GetOrdinal("id"))) { i = rdr.GetValue(0).ToString(); } else { i = ""; }; } rdr.Close(); } catch (Npgsql.NpgsqlException Ex) { System.Windows.Forms.MessageBox.Show(Ex.Message, "An error occurred while processing!!!"); return(""); } return(i); }
/// <summary> /// A helper function that takes the current row from the NpgsqlDataReader /// and hydrates a MembershipUser from the values. Called by the /// MembershipUser.GetUser implementation. /// </summary> /// <param name="reader">NpgsqlDataReader object</param> /// <returns>MembershipUser object</returns> private MembershipUser GetUserFromReader(NpgsqlDataReader reader) { object providerUserKey = reader.GetValue(0); string username = reader.GetString(1); string email = reader.IsDBNull(2) ? string.Empty : reader.GetString(2); string passwordQuestion = reader.IsDBNull(3) ? string.Empty : reader.GetString(3); string comment = reader.IsDBNull(4) ? string.Empty : reader.GetString(4); bool isApproved = reader.IsDBNull(5) ? false : reader.GetBoolean(5); bool isLockedOut = reader.IsDBNull(6) ? false : reader.GetBoolean(6); DateTime creationDate = reader.IsDBNull(7) ? DateTime.MinValue : reader.GetDateTime(7); DateTime lastLoginDate = reader.IsDBNull(8) ? DateTime.MinValue : reader.GetDateTime(8); DateTime lastActivityDate = reader.IsDBNull(9) ? DateTime.MinValue : reader.GetDateTime(9); DateTime lastPasswordChangedDate = reader.IsDBNull(10) ? DateTime.MinValue : reader.GetDateTime(10); DateTime lastLockedOutDate = reader.IsDBNull(11) ? DateTime.MinValue : reader.GetDateTime(11); return new MembershipUser(this.Name, username, providerUserKey, email, passwordQuestion, comment, isApproved, isLockedOut, creationDate, lastLoginDate, lastActivityDate, lastPasswordChangedDate, lastLockedOutDate); }
/// <summary> /// /// </summary> /// <param name="dr"></param> /// <returns></returns> public object npgsqldatareader2arraylist(NpgsqlDataReader dr) { ArrayList a = new ArrayList(); if (dr.HasRows || dr != null) { while (dr.Read()) { for (int n = 0; n < dr.FieldCount; n++) { var x = dr.GetValue(n).ToString(); a.Add(x); } } } return a; }
// Инициализирует запись данными из DataReader protected Record(NpgsqlDataReader reader) { for (int i = 0; i < reader.FieldCount; i++) if( !reader.IsDBNull(i) ) this.GetType().GetProperty(reader.GetName(i)).SetValue(this, reader.GetValue(i), null); }
private MembershipUser GetUserFromReader(string providerName, NpgsqlDataReader reader) { object providerUserKey = reader.GetValue(0); string username = reader.GetString(1); string email = reader.GetString(2); string passwordQuestion = string.Empty; if (reader.GetValue(3) != DBNull.Value) passwordQuestion = reader.GetString(3); string comment = string.Empty; if (reader.GetValue(4) != DBNull.Value) comment = reader.GetString(4); bool isApproved = reader.GetBoolean(5); bool isLockedOut = reader.GetBoolean(6); DateTime creationDate = reader.GetDateTime(7); DateTime lastLoginDate = new DateTime(); if (reader.GetValue(8) != DBNull.Value) lastLoginDate = reader.GetDateTime(8); DateTime lastActivityDate = reader.GetDateTime(9); DateTime lastPasswordChangedDate = reader.GetDateTime(10); DateTime lastLockedOutDate = new DateTime(); if (reader.GetValue(11) != DBNull.Value) lastLockedOutDate = reader.GetDateTime(11); MembershipUser u = new MembershipUser( providerName, username, providerUserKey, email, passwordQuestion, comment, isApproved, isLockedOut, creationDate, lastLoginDate, lastActivityDate, lastPasswordChangedDate, lastLockedOutDate); return u; }
///<summary> /// /// This method is reponsible to derive the command parameter list with values obtained from function definition. /// It clears the Parameters collection of command. Also, if there is any parameter type which is not supported by Npgsql, an InvalidOperationException will be thrown. /// Parameters name will be parameter1, parameter2, ... /// For while, only parameter name and NpgsqlDbType are obtained. ///</summary> /// <param name="command">NpgsqlCommand whose function parameters will be obtained.</param> public static void DeriveParameters(NpgsqlCommand command) { // Updated after 0.99.3 to support the optional existence of a name qualifying schema and case insensitivity when the schema ror procedure name do not contain a quote. // This fixed an incompatibility with NpgsqlCommand.CheckFunctionReturn(String ReturnType) String query = null; string procedureName = null; string schemaName = null; string[] fullName = command.CommandText.Split('.'); if (fullName.Length > 1 && fullName[0].Length > 0) { query = "select proargnames, proargtypes from pg_proc p left join pg_namespace n on p.pronamespace = n.oid where proname=:proname and n.nspname=:nspname"; schemaName = (fullName[0].IndexOf("\"") != -1) ? fullName[0] : fullName[0].ToLower(); procedureName = (fullName[1].IndexOf("\"") != -1) ? fullName[1] : fullName[1].ToLower(); } else { query = "select proargnames, proargtypes from pg_proc where proname = :proname"; procedureName = (fullName[0].IndexOf("\"") != -1) ? fullName[0] : fullName[0].ToLower(); } using (NpgsqlCommand c = new NpgsqlCommand(query, command.Connection)) { c.Parameters.Add(new NpgsqlParameter("proname", NpgsqlDbType.Text)); c.Parameters[0].Value = procedureName.Replace("\"", "").Trim(); if (fullName.Length > 1 && !String.IsNullOrEmpty(schemaName)) { NpgsqlParameter prm = c.Parameters.Add(new NpgsqlParameter("nspname", NpgsqlDbType.Text)); prm.Value = schemaName.Replace("\"", "").Trim(); } String[] names = null; String[] types = null; using (NpgsqlDataReader rdr = c.ExecuteReader(CommandBehavior.SingleRow | CommandBehavior.SingleResult)) { if (rdr.Read()) { if (!rdr.IsDBNull(0)) { names = rdr.GetValue(0) as String[]; } if (!rdr.IsDBNull(1)) { types = rdr.GetString(1).Split(); } } } if (types == null) { throw new InvalidOperationException( String.Format(resman.GetString("Exception_InvalidFunctionName"), command.CommandText)); } command.Parameters.Clear(); for (Int32 i = 0; i < types.Length; i++) { // skip parameter if type string is empty // empty parameter lists can cause this if (!string.IsNullOrEmpty(types[i])) { NpgsqlBackendTypeInfo typeInfo = null; if (!c.Connector.OidToNameMapping.TryGetValue(int.Parse(types[i]), out typeInfo)) { command.Parameters.Clear(); throw new InvalidOperationException(String.Format("Invalid parameter type: {0}", types[i])); } if (names != null && i < names.Length) { command.Parameters.Add(new NpgsqlParameter(":" + names[i], typeInfo.NpgsqlDbType)); } else { command.Parameters.Add(new NpgsqlParameter("parameter" + (i + 1).ToString(), typeInfo.NpgsqlDbType)); } } } } }
private static void DoDeriveParameters(NpgsqlCommand command) { // See http://www.postgresql.org/docs/current/static/catalog-pg-proc.html command.Parameters.Clear(); // Updated after 0.99.3 to support the optional existence of a name qualifying schema and case insensitivity when the schema ror procedure name do not contain a quote. // This fixed an incompatibility with NpgsqlCommand.CheckFunctionReturn(String ReturnType) var serverVersion = command.Connector.ServerVersion; String query = null; string procedureName = null; string schemaName = null; string[] fullName = command.CommandText.Split('.'); if (fullName.Length > 1 && fullName[0].Length > 0) { // proargsmodes is supported for Postgresql 8.1 and above if (serverVersion >= new Version(8, 1, 0)) { query = "select proargnames, proargtypes, proallargtypes, proargmodes from pg_proc p left join pg_namespace n on p.pronamespace = n.oid where proname=:proname and n.nspname=:nspname"; } else { query = "select proargnames, proargtypes from pg_proc p left join pg_namespace n on p.pronamespace = n.oid where proname=:proname and n.nspname=:nspname"; } schemaName = (fullName[0].IndexOf("\"") != -1) ? fullName[0] : fullName[0].ToLower(); procedureName = (fullName[1].IndexOf("\"") != -1) ? fullName[1] : fullName[1].ToLower(); } else { // proargsmodes is supported for Postgresql 8.1 and above if (serverVersion >= new Version(8, 1, 0)) { query = "select proargnames, proargtypes, proallargtypes, proargmodes from pg_proc where proname = :proname"; } else { query = "select proargnames, proargtypes from pg_proc where proname = :proname"; } procedureName = (fullName[0].IndexOf("\"") != -1) ? fullName[0] : fullName[0].ToLower(); } using (NpgsqlCommand c = new NpgsqlCommand(query, command.Connection)) { c.Parameters.Add(new NpgsqlParameter("proname", NpgsqlDbType.Text)); c.Parameters[0].Value = procedureName.Replace("\"", "").Trim(); if (fullName.Length > 1 && !String.IsNullOrEmpty(schemaName)) { NpgsqlParameter prm = c.Parameters.Add(new NpgsqlParameter("nspname", NpgsqlDbType.Text)); prm.Value = schemaName.Replace("\"", "").Trim(); } string[] names = null; uint[] types = null; char[] modes = null; using (NpgsqlDataReader rdr = c.ExecuteReader(CommandBehavior.SingleRow | CommandBehavior.SingleResult)) { if (rdr.Read()) { if (!rdr.IsDBNull(0)) { names = rdr.GetValue(0) as String[]; } if (serverVersion >= new Version("8.1.0")) { if (!rdr.IsDBNull(2)) { types = rdr.GetValue(2) as uint[]; } if (!rdr.IsDBNull(3)) { modes = rdr.GetValue(3) as char[]; } } if (types == null) { if (rdr.IsDBNull(1) || rdr.GetFieldValue <uint[]>(1).Length == 0) { return; // Parameterless function } types = rdr.GetFieldValue <uint[]>(1); } } else { throw new InvalidOperationException(String.Format("{0} does not exist in pg_proc", command.CommandText)); } } command.Parameters.Clear(); for (var i = 0; i < types.Length; i++) { var param = new NpgsqlParameter(); throw new NotImplementedException(); /* * // TODO: Fix enums, composite types * var npgsqlDbType = c.Connector.TypeHandlerRegistry.GetNpgsqlDbTypeFromOid(types[i]); * if (npgsqlDbType == NpgsqlDbType.Unknown) * throw new InvalidOperationException(String.Format("Invalid parameter type: {0}", types[i])); * param.NpgsqlDbType = npgsqlDbType; * * if (names != null && i < names.Length) * param.ParameterName = ":" + names[i]; * else * param.ParameterName = "parameter" + (i + 1); * * if (modes == null) // All params are IN, or server < 8.1.0 (and only IN is supported) * param.Direction = ParameterDirection.Input; * else * { * switch (modes[i]) * { * case 'i': * param.Direction = ParameterDirection.Input; * break; * case 'o': * param.Direction = ParameterDirection.Output; * break; * case 'b': * param.Direction = ParameterDirection.InputOutput; * break; * case 'v': * throw new NotImplementedException("Cannot derive function parameter of type VARIADIC"); * case 't': * throw new NotImplementedException("Cannot derive function parameter of type TABLE"); * default: * throw new ArgumentOutOfRangeException("proargmode", modes[i], * "Unknown code in proargmodes while deriving: " + modes[i]); * } * } * * command.Parameters.Add(param); */ } } }
private static void DoDeriveParameters(NpgsqlCommand command) { // See http://www.postgresql.org/docs/9.3/static/catalog-pg-proc.html command.Parameters.Clear(); // Updated after 0.99.3 to support the optional existence of a name qualifying schema and case insensitivity when the schema ror procedure name do not contain a quote. // This fixed an incompatibility with NpgsqlCommand.CheckFunctionReturn(String ReturnType) var serverVersion = command.Connector.ServerVersion; String query = null; string procedureName = null; string schemaName = null; string[] fullName = command.CommandText.Split('.'); if (fullName.Length > 1 && fullName[0].Length > 0) { // proargsmodes is supported for Postgresql 8.1 and above if (serverVersion >= new Version(8, 1, 0)) { query = "select proargnames, proargtypes, proallargtypes, proargmodes from pg_proc p left join pg_namespace n on p.pronamespace = n.oid where proname=:proname and n.nspname=:nspname"; } else { query = "select proargnames, proargtypes from pg_proc p left join pg_namespace n on p.pronamespace = n.oid where proname=:proname and n.nspname=:nspname"; } schemaName = (fullName[0].IndexOf("\"") != -1) ? fullName[0] : fullName[0].ToLower(); procedureName = (fullName[1].IndexOf("\"") != -1) ? fullName[1] : fullName[1].ToLower(); } else { // proargsmodes is supported for Postgresql 8.1 and above if (serverVersion >= new Version(8, 1, 0)) { query = "select proargnames, proargtypes, proallargtypes, proargmodes from pg_proc where proname = :proname"; } else { query = "select proargnames, proargtypes from pg_proc where proname = :proname"; } procedureName = (fullName[0].IndexOf("\"") != -1) ? fullName[0] : fullName[0].ToLower(); } using (NpgsqlCommand c = new NpgsqlCommand(query, command.Connection)) { c.Parameters.Add(new NpgsqlParameter("proname", NpgsqlDbType.Text)); c.Parameters[0].Value = procedureName.Replace("\"", "").Trim(); if (fullName.Length > 1 && !String.IsNullOrEmpty(schemaName)) { NpgsqlParameter prm = c.Parameters.Add(new NpgsqlParameter("nspname", NpgsqlDbType.Text)); prm.Value = schemaName.Replace("\"", "").Trim(); } string[] names = null; int[] types = null; string[] modes = null; using (NpgsqlDataReader rdr = c.ExecuteReader(CommandBehavior.SingleRow | CommandBehavior.SingleResult)) { if (rdr.Read()) { if (!rdr.IsDBNull(0)) { names = rdr.GetValue(0) as String[]; } if (serverVersion >= new Version("8.1.0")) { if (!rdr.IsDBNull(2)) { types = rdr.GetValue(2) as int[]; } if (!rdr.IsDBNull(3)) { modes = rdr.GetValue(3) as String[]; } } if (types == null) { if (rdr.IsDBNull(1) || rdr.GetString(1) == "") { return; // Parameterless function } types = rdr.GetString(1).Split().Select(int.Parse).ToArray(); } } else { throw new InvalidOperationException(String.Format(resman.GetString("Exception_InvalidFunctionName"), command.CommandText)); } } command.Parameters.Clear(); for (var i = 0; i < types.Length; i++) { var param = new NpgsqlParameter(); NpgsqlBackendTypeInfo typeInfo = null; if (!c.Connector.OidToNameMapping.TryGetValue(types[i], out typeInfo)) { throw new InvalidOperationException(String.Format("Invalid parameter type: {0}", types[i])); } param.NpgsqlDbType = typeInfo.NpgsqlDbType; if (names != null && i < names.Length) { param.ParameterName = ":" + names[i]; } else { param.ParameterName = "parameter" + (i + 1); } if (modes == null) // All params are IN, or server < 8.1.0 (and only IN is supported) { param.Direction = ParameterDirection.Input; } else { switch (modes[i]) { case "i": param.Direction = ParameterDirection.Input; break; case "o": param.Direction = ParameterDirection.Output; break; case "b": param.Direction = ParameterDirection.InputOutput; break; case "v": throw new NotImplementedException("Cannot derive function parameter of type VARIADIC"); case "t": throw new NotImplementedException("Cannot derive function parameter of type TABLE"); default: throw new ArgumentOutOfRangeException("proargmode", modes[i], "Unknown code in proargmodes while deriving: " + modes[i]); } } command.Parameters.Add(param); } } }
private void FigureOutFunctionReturn(IEnumerable<Parameter> parameters, NpgsqlDataReader rdr, string actualName) { if (parameters.Where(param => param.Direction == ParameterDirection.InputOutput || param.Direction == ParameterDirection.Output).Count() == 0) { if (rdr.FieldCount == 1 && rdr.GetName(0) == actualName) { // Simple return rdr.Read(); Console.WriteLine("Simple Return: {0}", rdr.GetValue(0)); } } }
private DebugEvent GetDebugEventFromRecord(NpgsqlDataReader reader) { var debugEvent = new DebugEvent { Command = (CommandType)reader.GetValue(8), StockSymbol = (reader.IsDBNull(9)) ? null : reader.GetString(9), Funds = (reader.IsDBNull(10)) ? null : (decimal?) reader.GetDecimal(10), FileName = (reader.IsDBNull(11)) ? null : reader.GetString(11), DebugMessage = (reader.IsDBNull(12)) ? null : reader.GetString(12) }; FillBaseEventPropertiesFromRecord(debugEvent, reader); return debugEvent; }
private AccountTransactionEvent GetAccountTransactionEventFromRecord(NpgsqlDataReader reader) { var accountTransactionEvent = new AccountTransactionEvent { AccountAction = (AccountAction) reader.GetValue(13), Funds = reader.GetDecimal(10) }; FillBaseEventPropertiesFromRecord(accountTransactionEvent, reader); return accountTransactionEvent; }
private UserCommandEvent GetUserCommandEventFromRecord(NpgsqlDataReader reader) { var userCommandEvent = new UserCommandEvent { Command = (CommandType) reader.GetValue(8), StockSymbol = (reader.IsDBNull(9)) ? null : reader.GetString(9), Funds = (reader.IsDBNull(10)) ? null : (decimal?) reader.GetDecimal(10) }; FillBaseEventPropertiesFromRecord(userCommandEvent, reader); return userCommandEvent; }