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