public List<AccountBase> FillAccountBaseFromSql() { var Accounts= new List<AccountBase>(); using(var myConnection = new SqlConnection(Connect.sTalismanConStr)) { myConnection.Open(); string sSql = "Select top 1 * from existing_clients"; var Reader = new SqlCommand(sSql, myConnection).ExecuteReader(); while (Reader.Read()) { var AccountBase = new AccountBase(); AccountBase.sAccountName = Reader["sName"].ToString(); AccountBase.sAddressLine1 = Reader["sAddressLine1"].ToString(); AccountBase.sAddressLine2 = Reader["sAddressLine2"].ToString(); AccountBase.sAddressLine3 = Reader["sAddressLine3"].ToString(); AccountBase.sCustomerNumber = Reader["sClientNumber"].ToString(); AccountBase.sEmail = Reader["sEmail"].ToString(); AccountBase.sPostCode = Reader["sPostalCode"].ToString(); AccountBase.sTelephone = Reader["sTelephone"].ToString(); //MessageBox.Show(AccountBase.sAccountName); //MessageBox.Show(AccountBase.sTelephone); Accounts.Add(AccountBase); } Reader.Close(); myConnection.Close(); } return Accounts; }
public bool CheckLoginData(string username, string password, ref Role role) { Connect(); try { SqlDataReader dataReader = new SqlCommand("SELECT * FROM tbl_login", con).ExecuteReader(); while (dataReader.Read()) { if (dataReader["username"].ToString().Trim() == username && dataReader["password"].ToString().Trim() == password) { MessageBox.Show("Login succes"); switch((int)dataReader["id"]) { case 0: role = Role.Development; break; case 1: role = Role.Finance; break; case 2: role = Role.Sales; break; } dataReader.Close(); return true; } } dataReader.Close(); } catch (SqlException ex) { MessageBox.Show(ex.ToString()); } MessageBox.Show("Wrong username and/or wrong password"); return false; }
/// <summary> /// Get tag ID data from the SQL database table. /// </summary> public void GetData(ListView listview) { using (sqlConn = new SqlConnection(connectionString)) { sqlConn.Open(); using (SqlDataReader reader = new SqlCommand("SELECT * FROM TagInfo", sqlConn).ExecuteReader()) { try { while (reader.Read()) { string[] rowitem = new string[2]; rowitem[0] = reader.GetString(reader.GetOrdinal("TagId")); rowitem[1] = reader.GetDateTime(reader.GetOrdinal("TagTime")).ToString(); ListViewItem listitem = new ListViewItem(rowitem); //table.Rows.Add(epc, time); listview.Items.Add(listitem); } reader.Close(); } catch (System.Data.SqlClient.SqlException ee) { MessageBox.Show(ee.Message.ToString()); } } sqlConn.Close(); } }
public object[] DLookupA(string table, string fieldName, string clauses, string[] orderByFieldNames, bool orderByAsc) { List<object> data = new List<object>(); bool connectedP = isConnectedDo; string query = ""; try { query = "SELECT " + fieldName + " FROM " + table + " WHERE (" + clauses + ")" + ((orderByFieldNames.Length > 0) ? " ORDER BY " + getOrderByData(orderByFieldNames) + " " + ((orderByAsc) ? "ASC" : "DESC") : ""); if (dbType == DatabaseType.MSSQL) { getQueryDbChanger(query, SqlTypes.SELECT); SqlDataReader Dr = new SqlCommand(query, MSSQLConn).ExecuteReader(); while (Dr.Read()) { data.Add(Dr[0]); } Dr.Close(); } else if (dbType == DatabaseType.MYSQL) { MySqlDataReader Dr = new MySqlCommand(query, MySQLConn).ExecuteReader(); while (Dr.Read()) { data.Add(Dr[0]); } Dr.Close(); } else if (dbType == DatabaseType.MSACCESS2003 | dbType == DatabaseType.MSACCESS2007) { OleDbDataReader Dr = new OleDbCommand(query, MSDBSQLConn).ExecuteReader(); while (Dr.Read()) { data.Add(Dr[0]); } Dr.Close(); } } catch (Exception eee) { OnError(eee); data.Clear(); data.Add("ERROR: " + eee.Message); data.Add("ERRORI: " + table); data.Add("ERRORI: " + fieldName); data.Add("ERRORI: " + clauses); data.Add("ERRORI: " + query); } finally { if (!connectedP) closeConnection(); } return data.ToArray(); }
public object DLookup(string sqlQuery) { object data = null; bool connectedP = isConnectedDo; try { if (dbType == DatabaseType.MSSQL) { getQueryDbChanger(sqlQuery, SqlTypes.SELECT); SqlDataReader Dr = new SqlCommand(sqlQuery, MSSQLConn).ExecuteReader(); if (Dr.Read()) { data = Dr[0]; } Dr.Close(); } else if (dbType == DatabaseType.MYSQL) { MySqlDataReader Dr = new MySqlCommand(sqlQuery, MySQLConn).ExecuteReader(); if (Dr.Read()) { data = Dr[0]; } Dr.Close(); } else if (dbType == DatabaseType.MSACCESS2003 | dbType == DatabaseType.MSACCESS2007) { OleDbDataReader Dr = new OleDbCommand(sqlQuery, MSDBSQLConn).ExecuteReader(); if (Dr.Read()) { data = Dr[0]; } Dr.Close(); } } catch (Exception eee) { OnError(eee); data = eee.Message; } finally { if (!connectedP) closeConnection(); } return data; }
public object DLookup(string table, string fieldName, string clauses, string[] orderByFieldNames, bool orderByAsc) { object data = null; bool connectedP = isConnectedDo; try { string query = "SELECT TOP 1 " + fieldName + " FROM " + table + " WHERE (" + clauses + ")" + ((orderByFieldNames.Length > 0) ? " ORDER BY " + getOrderByData(orderByFieldNames) + " " + ((orderByAsc) ? "ASC" : "DESC") : ""); if (dbType == DatabaseType.MSSQL) { getQueryDbChanger(query, SqlTypes.SELECT); SqlDataReader Dr = new SqlCommand(query, MSSQLConn).ExecuteReader(); if (Dr.Read()) { data = Dr[0]; } Dr.Close(); } else if (dbType == DatabaseType.MYSQL) { MySqlDataReader Dr = new MySqlCommand(query, MySQLConn).ExecuteReader(); if (Dr.Read()) { data = Dr[0]; } Dr.Close(); } else if (dbType == DatabaseType.MSACCESS2003 | dbType == DatabaseType.MSACCESS2007) { OleDbDataReader Dr = new OleDbCommand(query, MSDBSQLConn).ExecuteReader(); if (Dr.Read()) { data = Dr[0]; } Dr.Close(); } } catch (Exception eee) { OnError(eee); data = eee.Message; } finally { if (!connectedP) closeConnection(); } return data; }
public void readFromDataBase() { items.Clear(); SqlDataReader reader = new SqlCommand(string.Format("SELECT * FROM Items ORDER BY Id;SELECT * FROM Jobs ORDER BY Item;SELECT * FROM Jobs ORDER BY Item;"), cn).ExecuteReader(); //читаем лист items while (reader.Read()) { item newItem = new item(); newItem.id = reader.GetInt32(0); newItem.firstName = reader.GetString(1); newItem.lastName = reader.GetString(2); items.Add(newItem); } int i; int lastid; //читаем лист jobs reader.NextResult(); lastid = i = -1; while (reader.Read()) { int k = reader.GetInt32(4); if (lastid != k) { do ++i; while (k != items[i].id); lastid = reader.GetInt32(4); } items[i].jobs.Add(new job(reader.GetDateTime(0), reader.GetString(1), reader.GetString(2), reader.GetString(3))); } //читаем лист positions reader.NextResult(); lastid = i = -1; while (reader.Read()) { if (lastid != reader.GetInt32(4)) { do i++; while (reader.GetInt32(4) != items[i].id); lastid = reader.GetInt32(4); } items[i].positions.Add(new position(reader.GetInt64(0),reader.GetInt64(1),reader.GetInt32(2),reader.GetDateTime(3))); } reader.Close(); }
private static string[] GetEnabledTables(string connectionString) { SqlDataReader reader = null; SqlConnection connection = null; ArrayList list = new ArrayList(); try { connection = new SqlConnection(connectionString); connection.Open(); reader = new SqlCommand("dbo.AspNet_SqlCacheQueryRegisteredTablesStoredProcedure", connection) { CommandType = CommandType.StoredProcedure }.ExecuteReader(); while (reader.Read()) { list.Add(reader.GetString(0)); } } catch (Exception exception) { SqlException exception2 = exception as SqlException; if ((exception2 != null) && (exception2.Number == 0xafc)) { throw new DatabaseNotEnabledForNotificationException(System.Web.SR.GetString("Database_not_enabled_for_notification", new object[] { connection.Database })); } throw new HttpException(System.Web.SR.GetString("Cant_get_enabled_tables_sql_cache_dep"), exception); } finally { try { if (reader != null) { reader.Close(); } if (connection != null) { connection.Close(); } } catch { } } return (string[]) list.ToArray(Type.GetType("System.String")); }
public virtual object[] getDataAry(string argString) { this.rState = 0; string cmdText = argString; object[] objArray = null; try { SqlConnection connection = new SqlConnection(this.connStr); connection.Open(); SqlDataReader reader = new SqlCommand(cmdText, connection).ExecuteReader(); int fieldCount = reader.FieldCount; object[,] objArray2 = null; while (reader.Read()) { objArray2 = new object[fieldCount, 2]; for (int i = 0; i < fieldCount; i++) { objArray2[i, 0] = reader.GetName(i); objArray2[i, 1] = reader.GetValue(i); } objArray = cls.mergeAry(objArray, objArray2); } reader.Close(); connection.Close(); } catch (Exception exception) { this.rState = 1; objArray = null; this.eMessage = exception.Message; } return objArray; }
public virtual string getFieldList(string argString) { string cmdText = argString; string argObject = ""; try { SqlConnection connection = new SqlConnection(this.connStr); connection.Open(); SqlDataReader reader = new SqlCommand(cmdText, connection).ExecuteReader(); int fieldCount = reader.FieldCount; for (int i = 0; i < fieldCount; i++) { argObject = argObject + reader.GetName(i) + "|"; } if (!cls.isEmpty(argObject)) { argObject = cls.getLRStr(argObject, "|", "leftr"); } reader.Close(); connection.Close(); } catch (Exception exception) { argObject = ""; this.eMessage = exception.Message; } return argObject; }