public static bool CreateForm(OdbcDataReader reader, AppUser user) { int fCount = reader.FieldCount; for (int i = 0; i < fCount; i++) { string name = reader.GetName(i); // Map to DB field. Need to change if db changed switch (name) { case "username": user._username = reader.GetString(i); break; case "user_id": user._userId = reader.GetInt32(i); break; case "passwd": user._encodedPassword = reader.GetString(i); break; case "firstname": user._firstname = reader.GetString(i); break; case "surname": user._surname = reader.GetString(i); break; case "role_id": user._roleId = reader.GetInt32(i); break; case "branch_id": user._branchID = reader.GetInt32(i); break; case "is_valid": user._isValid = reader.GetInt32(i) > 0 ? true : false; break; // helper info case "branch_name": user._branchName = reader.GetString(i); break; } } return reader.HasRows; }
public static bool CreateForm(OdbcDataReader reader, Room room) { int fCount = reader.FieldCount; for (int i = 0; i < fCount; i++) { string name = reader.GetName(i); // Map to DB field. Need to change if db changed switch (name) { case "room_id": room._roomID = reader.GetInt32(i); break; case "branch_id": room._branchID = reader.GetInt32(i); break; case "name": room._name = reader.GetString(i); break; case "seat_no": room._seatNo = reader.GetInt32(i); break; case "img": room._img = reader.GetString(i); break; case "description": room._description = reader.GetString(i); break; // helper info case "branch_name": room._branchName = reader.GetString(i); break; } } return reader.HasRows; }
//initializing the leader handler public void BeginHandling() { try { //writer.WriteLine("I am not done"); command = " SELECT T.Number, T.Assigner, T.Issue_Date, I.Assigned, I.Attachment, I.JobDone, I.Status, I.Sequence, I.Due_Date, I.Requirements" + " FROM Ticket AS T, Ticket_Information AS I" + " WHERE T.Number=I.Number AND I.Assigned='" + userName + "' AND (I.Status = 'Assigned' OR I.Status='Waiting' OR I.Status='Work_In_Progress') AND I.Due_Date > #" + System.DateTime.Now + "#"; sqlCommand = new OdbcCommand(command, dbConn); dbReader = sqlCommand.ExecuteReader(); //Console.WriteLine("I am done"); while (dbReader.Read()) { line = "";//empty the line so you can read the next row for (int i = 0; i < dbReader.FieldCount - 1; i++) { line += dbReader.GetString(i) + "##";// but the whole row in one column and send it, seperated by commas } writer.WriteLine(line); writer.Flush(); } writer.WriteLine(".");// end of reading from the database writer.Flush(); DB_Handler.DisposeAll(sqlCommand, dbReader); WaitForQueries(); //do not forget to close the reader } catch { Console.WriteLine(" The Database is down please try again later");//for debugging server } }
//initializing the leader handler public void BeginHandling() { try { email = DB_Handler.GetEmail(userName, dbConn); // command = " SELECT T.Number, T.Issue_Date, I.Assigned, I.Attachment, I.JobDone, I.Status, I.Sequence, I.Due_Date, I.Requirements" + // " FROM Ticket AS T, Ticket_Information AS I WHERE T.Number=I.Number"; command = " SELECT T.Number, T.Issue_Date, I.Assigned,I.Status, I.Due_Date" + " FROM Ticket AS T, Ticket_Information AS I WHERE T.Number=I.Number AND T.Assigner ='" + userName + "'"; sqlCommand = new OdbcCommand(command, dbConn); dbReader = sqlCommand.ExecuteReader(); while (dbReader.Read()) { line = "";//empty the line so you can read the next row for (int i = 0; i < dbReader.FieldCount-1; i++) { line += dbReader.GetString(i) + "##";// but the whole row in one column and send it, seperated by commas } writer.WriteLine(line); writer.Flush(); } writer.WriteLine(".");// end of reading from the database writer.Flush(); DB_Handler.DisposeAll(sqlCommand, dbReader); WaitForQueries(); } catch { Console.WriteLine(" The Database is down please try again later");//for debugging server DB_Handler.DisposeAll(sqlCommand, dbReader); } }
/// <summary> /// DisplayRecords: This function displays the records in the view if any. /// </summary> static void DisplayRecords() { Console.Write("\tRecords in Prescirption list..."); try { reader = (OdbcDataReader)command.ExecuteReader(); // read the returned resultset while (reader.Read()) { /* * This view consists the following fields: * rxdef_id (TINYINT) * drug_name (CHARACTER 50) * description (CHARACTER 50) * rx_date (DATE) * For the purpose of keeping it simple, we are displaying the rxdef_id and drug_name on the console (the first two fields) */ Console.WriteLine("\n\t\t{0} {1} ", reader.GetInt16(0), reader.GetString(1)); } // close the reader reader.Close(); } catch (Exception e) { ExceptionDisplay(e); } }
public QuestionEntity read(OdbcDataReader reader) { var res = new QuestionEntity(); res.id = reader.GetInt32(0); res.ans = reader.GetString(2); var choicejson = reader.GetString(3); var decoder = new JavaScriptSerializer(); res.choices = decoder.Deserialize< ArrayList >(choicejson); res.imageURL = reader.GetString(4); res.statement = reader.GetString(5); res.type = getTypeString( reader.GetInt32(1) ); return res; }
public static bool CreateForm(OdbcDataReader reader, Branch branch) { int fCount = reader.FieldCount; for (int i = 0; i < fCount; i++) { string name = reader.GetName(i); // Map to DB field. Need to change if db changed switch (name) { case "branch_id": branch._branchID = reader.GetInt32(i); break; case "branch_name": branch._branchName = reader.GetString(i); break; case "branch_code": branch._branchCode = reader.GetString(i); break; case "address": branch._address = reader.GetString(i); break; case "tel": branch._tel = reader.GetString(i); break; case "img": branch._img = reader.GetString(i); break; case "supervisor": branch._supervisor = reader.GetString(i); break; } } return reader.HasRows; }
public static bool CreateForm(OdbcDataReader reader, Teacher teacher) { int fCount = reader.FieldCount; for (int i = 0; i < fCount; i++) { string name = reader.GetName(i); // Map to DB field. Need to change if db changed switch (name) { case "teacher_id": teacher._teacherID = reader.GetInt32(i); break; case "sex": teacher._sex = reader.GetString(i); break; case "addr": teacher._addr = reader.GetString(i); break; case "tel": teacher._tel = reader.GetString(i); break; case "email": teacher._email = reader.GetString(i); break; case "birthday": teacher._birthday = new DateTime(reader.GetDate(i).Ticks); break; case "firstname": teacher._firstname = reader.GetString(i); break; case "surname": teacher._surname = reader.GetString(i); break; case "citizen_id": teacher._citizenID = reader.GetString(i); break; case "image": teacher._img = reader.GetString(i); break; case "subject": teacher._subject = reader.GetString(i); break; case "is_active": teacher._isActive = reader.GetInt32(i) > 0 ? true : false; break; } } return reader.HasRows; }
public static bool CreateForm(OdbcDataReader reader, PaidGroup paidGroup) { int fCount = reader.FieldCount; for (int i = 0; i < fCount; i++) { string name = reader.GetName(i); // Map to DB field. Need to change if db changed switch (name) { case "paid_group_id": paidGroup._paidGroupID = reader.GetInt32(i); break; case "name": paidGroup._name = reader.GetString(i); break; case "current_round": paidGroup._currentRound = reader.GetInt32(i); break; case "rate_info": paidGroup._rawRateInfo = reader.GetString(i); paidGroup._rateInfo = PaidRateInfo.Parse(paidGroup._rawRateInfo); break; // helper info } } return reader.HasRows; }
public static bool CreateForm(OdbcDataReader reader, Role role) { int fCount = reader.FieldCount; for (int i = 0; i < fCount; i++) { string name = reader.GetName(i); // Map to DB field. Need to change if db changed switch (name) { case "name": role._name = reader.GetString(i); break; case "role_id": role._roleId = reader.GetInt32(i); break; } } return reader.HasRows; }
public void GetTicketInfo() { ticketNum = int.Parse(reader.ReadLine());//get the ticket number you want to handle command = "SELECT T.Number, T.Issue_Date,I.Assigned, I.JobDone, I.Status, I.Sequence, I.Due_Date, I.Requirements, I.Attachment " + "FROM Ticket_Information AS I, Ticket AS T WHERE T.Number=" + ticketNum + " AND T.Number=I.Number AND I.Assigned='"+userName+"'";//name,email,pass sqlCommand = new OdbcCommand(command, dbConn); dbReader = sqlCommand.ExecuteReader(); while (dbReader.Read()) { line = "";//empty the line so you can read the next row for (int i = 0; i < dbReader.FieldCount - 1; i++) { line += dbReader.GetString(i) + "##";// but the whole row in one column and send it, seperated by ## } writer.WriteLine(line); writer.Flush(); } writer.WriteLine(".");//sending the end of tickets info writer.Flush(); }
public void UpdateSuccessor() { //Updating the Status of the succeding sequence command = "SELECT Sequence FROM Ticket_Information WHERE Number=" + ticketNum + " AND Assigned='" + userName + "'"; sqlCommand = new OdbcCommand(command, dbConn); dbReader = sqlCommand.ExecuteReader(); dbReader.Read(); int seq = int.Parse(dbReader.GetString(0)); command = "SELECT MAX(Sequence) FROM Ticket_Information WHERE Number=" + ticketNum; sqlCommand = new OdbcCommand(command, dbConn); dbReader = sqlCommand.ExecuteReader(); dbReader.Read(); int seq2 = int.Parse(dbReader.GetString(0)); if (seq < seq2)// means there are successors { command = "SELECT Assigned FROM Ticket_Information WHERE Number=" + ticketNum+" AND Sequence="+(++seq); sqlCommand = new OdbcCommand(command, dbConn); dbReader = sqlCommand.ExecuteReader(); dbReader.Read(); successor = dbReader.GetString(0); string[] tokens = allFiles.Split('#'); Directory.CreateDirectory(successor + "/" + ticketNum); for (int k = 0; k < tokens.Length; k++) { try { File.Copy((userName + "/" + ticketNum + "/" + tokens[k]), (successor + "/" + ticketNum + "/" + tokens[k]),true); } catch(Exception ww) { Console.WriteLine(ww);//gives an exception but still works !!!! } } command = " Update Ticket_Information SET Status='Assigned' , Attachment= Attachment + '" + allFiles + "' WHERE Number=" + ticketNum + " AND Sequence =" + seq;//sequece has been updated earlier sqlCommand = new OdbcCommand(command, dbConn); sqlCommand.ExecuteNonQuery();// even if no one preceeding } }
public void HandleConnection(Object state) { try { ns = new NetworkStream(client); reader = new StreamReader(ns); writer = new StreamWriter(ns); connections++; Console.WriteLine("New client accepted: {0} active connections", connections); writer.WriteLine("Welcome to my server"); writer.Flush(); line = null; try { line = reader.ReadLine(); if (line.Trim().Equals("Auth")) { userName = reader.ReadLine(); password = reader.ReadLine(); string auth = "SELECT * FROM User_Information WHERE Name='" + userName + "' AND Password='******'"; sqlCommand = new OdbcCommand(auth, dbConn); sqlCommand.ExecuteNonQuery(); dbReader = sqlCommand.ExecuteReader(); if (dbReader.Read()) { writer.WriteLine("Auth OK"); writer.Flush(); writer.WriteLine("Welcome " + dbReader.GetString(3)); writer.Flush(); if (dbReader.GetString(3).Equals("Leader")) new HandleLeader(dbConn, ns, reader, writer, userName); else if (dbReader.GetString(3).Equals("Member")) new HandleMember(dbConn, ns, reader, writer, userName); else if (dbReader.GetString(3).Equals("System")) new HandleSystem(dbConn, reader, writer); } else writer.WriteLine("Auth not OK"); } else if (line.Trim().Equals("Quit")) goto Skip; } catch (SocketException) { writer.WriteLine("Error"); writer.Flush(); } catch { } Skip: client.Close(); ns.Close(); dbConn.Close(); connections--; Console.WriteLine("Client disconnected: {0}active connections", connections); } catch (Exception) { connections--; Console.WriteLine("Client disconnected: {0} active connections", connections); } }
// // GetProfileInfoFromReader // Takes the current row from the OdbcDataReader // and populates a ProfileInfo object from the values. // private ProfileInfo GetProfileInfoFromReader(OdbcDataReader reader) { string username = reader.GetString(0); DateTime lastActivityDate = new DateTime(); if (reader.GetValue(1) != DBNull.Value) lastActivityDate = reader.GetDateTime(1); DateTime lastUpdatedDate = new DateTime(); if (reader.GetValue(2) != DBNull.Value) lastUpdatedDate = reader.GetDateTime(2); bool isAnonymous = reader.GetBoolean(3); // ProfileInfo.Size not currently implemented. ProfileInfo p = new ProfileInfo(username, isAnonymous, lastActivityDate, lastUpdatedDate, 0); return p; }
// // GetUserFromReader // A helper function that takes the current row from the OdbcDataReader // and hydrates a MembershiUser from the values. Called by the // MembershipUser.GetUser implementation. // private MembershipUser GetUserFromReader(OdbcDataReader reader) { object providerUserKey = reader.GetValue(0); string username = reader.GetString(1); string email = reader.GetString(2); string passwordQuestion = ""; if (reader.GetValue(3) != DBNull.Value) passwordQuestion = reader.GetString(3); string comment = ""; 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((this.Name == null) ? "SimpleMysqlMembershipProvider" : this.Name, username, providerUserKey, email, passwordQuestion, comment, isApproved, isLockedOut, creationDate, lastLoginDate, lastActivityDate, lastPasswordChangedDate, lastLockedOutDate); return u; }
public ArrayList getDGPContacts(string userId) { //Console.WriteLine("UserID: " + userId); try { ArrayList friendList = new ArrayList(); itsCommand = new OdbcCommand("SELECT user FROM friends WHERE friend=\'" + userId + "\'",itsConnection); itsDataReader = itsCommand.ExecuteReader(); for(int i = 0; itsDataReader.Read(); i++) { // Console.WriteLine("Add friend: " + itsDataReader.GetString(0)); friendList.Add(itsDataReader.GetString(0)); } itsCommand.Dispose(); itsDataReader.Close(); return friendList; } catch(OdbcException MyOdbcException) //Catch any ODBC exception .. { this.handleException(MyOdbcException); } return null; }
public static bool CreateForm(OdbcDataReader reader, Payment payment) { int fCount = reader.FieldCount; for (int i = 0; i < fCount; i++) { string name = reader.GetName(i); // Map to DB field. Need to change if db changed switch (name) { case "course_id": payment._courseID = reader.GetInt32(i); break; case "sum_all_cost": payment._sumAllCost = reader.GetInt32(i); break; case "sum_max_payable": payment._sumMaxPayable = reader.GetInt32(i); break; case "sum_paid_cost": payment._sumPaidCost = reader.GetInt32(i); break; case "last_paid_date": payment._lastPaidDate = new DateTime(reader.GetDate(i).Ticks); break; case "paid_round": payment._paidRound = reader.GetInt32(i); break; case "status": payment._status = reader.GetInt32(i); break; // helper info case "bts_course_id": payment._btsCourseID = reader.GetString(i); break; case "course_name": payment._courseName = reader.GetString(i); break; case "course_start_date": payment._courseStartDate = new DateTime(reader.GetDate(i).Ticks); break; case "course_end_date": payment._courseEndDate = new DateTime(reader.GetDate(i).Ticks); break; case "paid_group_id": payment._paidGroupID = reader.GetInt32(i); break; } } return reader.HasRows; }
/// <summary> /// Constructs a rank from the output of a datareader. Assumes that there /// is data ready to be read from the current record 15/12/15 /// </summary> private static Rank readRank(OdbcDataReader dataReader) { return new Rank(dataReader.GetInt16(0), dataReader.GetString(1), dataReader.GetInt16(2), dataReader.GetString(3)); }
public void SendMembers() { command = "SELECT Name FROM User_Information WHERE Role='Member'"; sqlCommand = new OdbcCommand(command, dbConn); dbReader = sqlCommand.ExecuteReader(); while (dbReader.Read()) { writer.WriteLine(dbReader.GetString(0)); writer.Flush(); } }
/// <summary> /// Constructs a user from the output of a datareader. Assumes that there is data /// ready to be read from the current record 14/12/15 /// </summary> private static User readUser(OdbcDataReader dataReader) { return new User(dataReader.GetInt16(0), dataReader.GetString(1), dataReader.GetString(3), dataReader.GetString(4), dataReader.GetString(5), dataReader.GetBoolean(6), dataReader.GetDateTime(7), dataReader.GetInt16(8), dataReader.GetInt16(9), dataReader.GetBoolean(10), dataReader.GetBoolean(11), dataReader.GetInt16(13)); }
private OpenIdMembershipUser GetUserFromReader(OdbcDataReader reader) { string username = reader.GetString(0); string openId = reader.IsDBNull(1) ? string.Empty : reader.GetString(1); string email = reader.IsDBNull(2) ? string.Empty : reader.GetString(2); string passwordQuestion = ""; if (reader.GetValue(3) != DBNull.Value) passwordQuestion = reader.GetString(3); string comment = ""; if (reader.GetValue(4) != DBNull.Value) comment = reader.GetString(4); bool isApproved = reader.GetBoolean(5); DateTime creationDate = reader.GetDateTime(6); DateTime lastLoginDate = new DateTime(); if (reader.GetValue(7) != DBNull.Value) lastLoginDate = reader.GetDateTime(7); DateTime lastActivityDate = reader.GetDateTime(8); DateTime lastPasswordChangedDate = reader.GetDateTime(9); object providerUserKey = reader.GetValue(10); bool isLockedOut = reader.GetBoolean(11); DateTime lastLockedOutDate = new DateTime(); if (reader.GetValue(12) != DBNull.Value) lastLockedOutDate = reader.GetDateTime(12); OpenIdMembershipUser u = new OpenIdMembershipUser( this.Name, openId, username, providerUserKey, email, passwordQuestion, comment, isApproved, isLockedOut, creationDate, lastLoginDate, lastActivityDate, lastPasswordChangedDate, lastLockedOutDate); return u; }
// DeriveParametersFromStoredProcedure ( // OdbcConnection connection, // OdbcCommand command); // // Uses SQLProcedureColumns to create an array of OdbcParameters // static private OdbcParameter[] DeriveParametersFromStoredProcedure(OdbcConnection connection, OdbcCommand command) { List<OdbcParameter> rParams = new List<OdbcParameter>(); // following call ensures that the command has a statement handle allocated CMDWrapper cmdWrapper = command.GetStatementHandle(); OdbcStatementHandle hstmt = cmdWrapper.StatementHandle; int cColsAffected; // maps an enforced 4-part qualified string as follows // parts[0] = null - ignored but removal would be a run-time breaking change from V1.0 // parts[1] = CatalogName (optional, may be null) // parts[2] = SchemaName (optional, may be null) // parts[3] = ProcedureName // string quote = connection.QuoteChar(ADP.DeriveParameters); string[] parts = MultipartIdentifier.ParseMultipartIdentifier(command.CommandText, quote, quote, '.', 4, true, Res.ODBC_ODBCCommandText, false); if (null == parts[3]) { // match everett behavior, if the commandtext is nothing but whitespace set the command text to the whitespace parts[3] = command.CommandText; } // note: native odbc appears to ignore all but the procedure name ODBC32.RetCode retcode = hstmt.ProcedureColumns(parts[1], parts[2], parts[3], null); // Note: the driver does not return an error if the given stored procedure does not exist // therefore we cannot handle that case and just return not parameters. if (ODBC32.RetCode.SUCCESS != retcode) { connection.HandleError(hstmt, retcode); } using (OdbcDataReader reader = new OdbcDataReader(command, cmdWrapper, CommandBehavior.Default)) { reader.FirstResult(); cColsAffected = reader.FieldCount; // go through the returned rows and filter out relevant parameter data // while (reader.Read()) { // devnote: column types are specified in the ODBC Programmer's Reference // COLUMN_TYPE Smallint 16bit // COLUMN_SIZE Integer 32bit // DECIMAL_DIGITS Smallint 16bit // NUM_PREC_RADIX Smallint 16bit OdbcParameter parameter = new OdbcParameter(); parameter.ParameterName = reader.GetString(ODBC32.COLUMN_NAME-1); switch ((ODBC32.SQL_PARAM)reader.GetInt16(ODBC32.COLUMN_TYPE-1)){ case ODBC32.SQL_PARAM.INPUT: parameter.Direction = ParameterDirection.Input; break; case ODBC32.SQL_PARAM.OUTPUT: parameter.Direction = ParameterDirection.Output; break; case ODBC32.SQL_PARAM.INPUT_OUTPUT: parameter.Direction = ParameterDirection.InputOutput; break; case ODBC32.SQL_PARAM.RETURN_VALUE: parameter.Direction = ParameterDirection.ReturnValue; break; default: Debug.Assert(false, "Unexpected Parametertype while DeriveParamters"); break; } parameter.OdbcType = TypeMap.FromSqlType((ODBC32.SQL_TYPE)reader.GetInt16(ODBC32.DATA_TYPE-1))._odbcType; parameter.Size = (int)reader.GetInt32(ODBC32.COLUMN_SIZE-1); switch(parameter.OdbcType){ case OdbcType.Decimal: case OdbcType.Numeric: parameter.ScaleInternal = (Byte)reader.GetInt16(ODBC32.DECIMAL_DIGITS-1); parameter.PrecisionInternal = (Byte)reader.GetInt16(ODBC32.NUM_PREC_RADIX-1); break; } rParams.Add (parameter); } } retcode = hstmt.CloseCursor(); return rParams.ToArray();; }
//No reading/writing from/to the user, Just setting the ticketNum public void CreateTicket() { DateTime date = System.DateTime.Now; command = "INSERT INTO Ticket (Assigner, Issue_Date) Values('" + userName + "','" + date + "')"; sqlCommand = new OdbcCommand(command, dbConn); sqlCommand.ExecuteNonQuery(); command = " SELECT Number" + " FROM Ticket WHERE Issue_Date=#" + date + "#"; sqlCommand = new OdbcCommand(command, dbConn); dbReader = sqlCommand.ExecuteReader(); dbReader.Read(); ticketNum = int.Parse(dbReader.GetString(0)); }
public void SendMemNum() { command = "SELECT COUNT(*) FROM User_Information WHERE ROLE='Member'"; sqlCommand = new OdbcCommand(command, dbConn); dbReader = sqlCommand.ExecuteReader(); dbReader.Read(); Console.WriteLine(" I have got the count" + dbReader.GetString(0)); writer.WriteLine(dbReader.GetString(0)); writer.Flush(); }
// DeriveParametersFromStoredProcedure ( // OdbcConnection connection, // OdbcCommand command); // // Uses SQLProcedureColumns to create an array of OdbcParameters // static private OdbcParameter[] DeriveParametersFromStoredProcedure(OdbcConnection connection, OdbcCommand command) { List <OdbcParameter> rParams = new List <OdbcParameter>(); // following call ensures that the command has a statement handle allocated CMDWrapper cmdWrapper = command.GetStatementHandle(); OdbcStatementHandle hstmt = cmdWrapper.StatementHandle; int cColsAffected; // maps an enforced 4-part qualified string as follows // parts[0] = null - ignored but removal would be a run-time breaking change from V1.0 // parts[1] = CatalogName (optional, may be null) // parts[2] = SchemaName (optional, may be null) // parts[3] = ProcedureName // string quote = connection.QuoteChar(ADP.DeriveParameters); string[] parts = MultipartIdentifier.ParseMultipartIdentifier(command.CommandText, quote, quote, '.', 4, true, Res.ODBC_ODBCCommandText, false); if (null == parts[3]) { // match Everett behavior, if the commandtext is nothing but whitespace set the command text to the whitespace parts[3] = command.CommandText; } // note: native odbc appears to ignore all but the procedure name ODBC32.RetCode retcode = hstmt.ProcedureColumns(parts[1], parts[2], parts[3], null); // Note: the driver does not return an error if the given stored procedure does not exist // therefore we cannot handle that case and just return not parameters. if (ODBC32.RetCode.SUCCESS != retcode) { connection.HandleError(hstmt, retcode); } using (OdbcDataReader reader = new OdbcDataReader(command, cmdWrapper, CommandBehavior.Default)) { reader.FirstResult(); cColsAffected = reader.FieldCount; // go through the returned rows and filter out relevant parameter data // while (reader.Read()) { // devnote: column types are specified in the ODBC Programmer's Reference // COLUMN_TYPE Smallint 16bit // COLUMN_SIZE Integer 32bit // DECIMAL_DIGITS Smallint 16bit // NUM_PREC_RADIX Smallint 16bit OdbcParameter parameter = new OdbcParameter(); parameter.ParameterName = reader.GetString(ODBC32.COLUMN_NAME - 1); switch ((ODBC32.SQL_PARAM)reader.GetInt16(ODBC32.COLUMN_TYPE - 1)) { case ODBC32.SQL_PARAM.INPUT: parameter.Direction = ParameterDirection.Input; break; case ODBC32.SQL_PARAM.OUTPUT: parameter.Direction = ParameterDirection.Output; break; case ODBC32.SQL_PARAM.INPUT_OUTPUT: parameter.Direction = ParameterDirection.InputOutput; break; case ODBC32.SQL_PARAM.RETURN_VALUE: parameter.Direction = ParameterDirection.ReturnValue; break; default: Debug.Assert(false, "Unexpected Parametertype while DeriveParamters"); break; } parameter.OdbcType = TypeMap.FromSqlType((ODBC32.SQL_TYPE)reader.GetInt16(ODBC32.DATA_TYPE - 1))._odbcType; parameter.Size = (int)reader.GetInt32(ODBC32.COLUMN_SIZE - 1); switch (parameter.OdbcType) { case OdbcType.Decimal: case OdbcType.Numeric: parameter.ScaleInternal = (Byte)reader.GetInt16(ODBC32.DECIMAL_DIGITS - 1); parameter.PrecisionInternal = (Byte)reader.GetInt16(ODBC32.NUM_PREC_RADIX - 1); break; } rParams.Add(parameter); } } retcode = hstmt.CloseCursor(); return(rParams.ToArray());; }
/** Construct an Author from an OdbcDataReader ** @param OdbcDataReader result ** @return Model **/ public static Model __constructAuthor(OdbcDataReader result) { try { return new Author { firstName = result.GetString(0), lastName = result.GetString(1), handle = result.GetString(2), emailAddress = result.GetString(3) }; } catch (OdbcException e) { __fail("SQL Operation Failed", e); } return null; }
// DeriveParametersFromStoredProcedure ( // OdbcConnection connection, // OdbcCommand command); // // Uses SQLProcedureColumns to create an array of OdbcParameters // static private OdbcParameter[] DeriveParametersFromStoredProcedure(OdbcConnection connection, OdbcCommand command) { ArrayList rParams = new ArrayList(); // following call ensures that the command has a statement handle allocated HandleRef hstmt = command.GetStatementHandle(); int cColsAffected; ODBC32.RETCODE retcode; retcode = (ODBC32.RETCODE)UnsafeNativeMethods.Odbc32.SQLProcedureColumnsW( hstmt, null, 0, null, 0, command.CommandText, (Int16)ODBC32.SQL_NTS, null, 0); // Note: the driver does not return an error if the given stored procedure does not exist // therefore we cannot handle that case and just return not parameters. if (ODBC32.RETCODE.SUCCESS != retcode) { connection.HandleError(hstmt, ODBC32.SQL_HANDLE.STMT, retcode); } OdbcDataReader reader = new OdbcDataReader(command, command._cmdWrapper, CommandBehavior.Default); reader.FirstResult(); cColsAffected = reader.FieldCount; // go through the returned rows and filter out relevant parameter data // while (reader.Read()) { OdbcParameter parameter = new OdbcParameter(); parameter.ParameterName = reader.GetString(ODBC32.COLUMN_NAME - 1); switch ((ODBC32.SQL_PARAM)reader.GetInt16(ODBC32.COLUMN_TYPE - 1)) { case ODBC32.SQL_PARAM.INPUT: parameter.Direction = ParameterDirection.Input; break; case ODBC32.SQL_PARAM.OUTPUT: parameter.Direction = ParameterDirection.Output; break; case ODBC32.SQL_PARAM.INPUT_OUTPUT: parameter.Direction = ParameterDirection.InputOutput; break; case ODBC32.SQL_PARAM.RETURN_VALUE: parameter.Direction = ParameterDirection.ReturnValue; break; default: Debug.Assert(false, "Unexpected Parametertype while DeriveParamters"); break; } parameter.OdbcType = TypeMap.FromSqlType((ODBC32.SQL_TYPE)reader.GetInt16(ODBC32.DATA_TYPE - 1))._odbcType; parameter.Size = reader.GetInt32(ODBC32.COLUMN_SIZE - 1); switch (parameter.OdbcType) { case OdbcType.Decimal: case OdbcType.Numeric: parameter.Scale = (Byte)reader.GetInt32(ODBC32.DECIMAL_DIGITS - 1); parameter.Precision = (Byte)reader.GetInt32(ODBC32.NUM_PREC_RADIX - 1); break; } rParams.Add(parameter); } retcode = (ODBC32.RETCODE)UnsafeNativeMethods.Odbc32.SQLCloseCursor(hstmt); // Create a new Parameter array and copy over the ArrayList items // OdbcParameter[] pList = new OdbcParameter[rParams.Count]; for (int i = 0; i < rParams.Count; i++) { pList[i] = (OdbcParameter)rParams[i]; } return(pList); }
/** Construct a Story from an OdbcDataReader ** @param OdbcDataReader result ** @return Model **/ public static Model __constructStory(OdbcDataReader result) { try { // Look up Author Author author; string handle = result.GetString(3); OdbcParameter parameter = new OdbcParameter(":handle", handle); author = (Author)getSingle(new Author(), "handle=?", parameter); return new Story { storyid = result.GetString(0), timestamp = (long)result.GetInt32(1), permalink = result.GetString(2), author = author, subject = result.GetString(4), body = result.GetString(5) }; } catch (OdbcException e) { __fail("SQL Operation Failed", e); } return null; }
/// <summary> /// Constructs an achievement from the output of a datareader. Assumes that there /// is data ready to be read from the current record 14/12/15 /// </summary> private static Achievement readAchievement(OdbcDataReader dataReader) { return new Achievement(dataReader.GetInt16(0), dataReader.GetString(1), dataReader.GetString(2), dataReader.GetString(3), dataReader.GetString(4), dataReader.GetInt16(5), dataReader.GetDateTime(6), dataReader.GetBoolean(7)); }
public string read( string sqlCommand) { try { string returnString = ""; itsCommand = new OdbcCommand("", itsConnection); itsCommand.CommandText = sqlCommand; itsDataReader = itsCommand.ExecuteReader(); while(itsDataReader.Read()) { if(returnString != "") { returnString += "\n"; } returnString += itsDataReader.GetString(0); } itsCommand.Dispose(); itsDataReader.Close(); return string.Copy(returnString); } catch (OdbcException MyOdbcException)//Catch any ODBC exception .. { this.handleException(MyOdbcException); } return ""; }
/// <summary> /// Constructs a reward from the output of a datareader. Assumes that there /// is data ready to be read from the current record 15/12/15 /// </summary> private static Reward readReward(OdbcDataReader dataReader) { return new Reward(dataReader.GetInt16(0), dataReader.GetString(1), dataReader.GetString(2), dataReader.GetString(3), dataReader.GetInt16(4)); }
public bool testNameAndPassword(string name, string password, Client client) { try { int itsInloggId; itsCommand = new OdbcCommand("",itsConnection); itsCommand.CommandText = "SELECT userID FROM users WHERE email=\'"+ name + "\'"; itsDataReader = itsCommand.ExecuteReader(); if(itsDataReader.Read()) { itsInloggId = itsDataReader.GetInt32(0); itsDataReader.Close(); } else { itsCommand.Dispose(); itsDataReader.Close(); return false; } itsCommand.CommandText = "SELECT password FROM users WHERE userID=\'"+ itsInloggId.ToString() + "\'"; itsDataReader = itsCommand.ExecuteReader(); itsDataReader.Read(); string databasePassword = itsDataReader.GetString(0); if(string.Compare(databasePassword, password, true) == 0) { itsDataReader.Close(); itsCommand.CommandText = "SELECT * FROM users WHERE userID=\'"+ itsInloggId.ToString() + "\'"; itsDataReader = itsCommand.ExecuteReader(); itsDataReader.Read(); client.Id = itsDataReader.GetString(0); client.Email = name; //client.IpNumber = itsDataReader.GetString(3); client.DisplayName = itsDataReader.GetString(4); itsCommand.Dispose(); itsDataReader.Close(); return true; } else { itsCommand.Dispose(); itsDataReader.Close(); return false; } } catch (OdbcException MyOdbcException)//Catch any ODBC exception .. { this.handleException(MyOdbcException); } return false; }