C# TableName.ToString方法代码示例

        public override bool Delete(TableName tableName, int id)
        {
            string sqlCmd = String.Empty;
            string table = String.Empty;
            string selectColumns = String.Empty;
            switch (tableName)
            {
                case TableName.AccessDenied:
                    sqlCmd = "DELETE FROM AccessDenied WHERE [email protected]";
                    break;
                case TableName.Menus:
                    sqlCmd = "DELETE FROM Menus WHERE [email protected]";
                    break;
                case TableName.Clients:
                    sqlCmd = "DELETE FROM Clients WHERE [email protected]";
                    break;
                case TableName.Orders:
                    sqlCmd = "DELETE FROM Orders WHERE [email protected]";
                    break;
                case TableName.Persons:
                    sqlCmd = "DELETE FROM Persons WHERE [email protected]";
                    break;
                case TableName.TableOrders:
                    sqlCmd = "DELETE FROM TableOrders WHERE [email protected]";
                    break;
                case TableName.Tables:
                    sqlCmd = "DELETE FROM Tables WHERE [email protected]";
                    break;
                case TableName.Users:
                    sqlCmd = "DELETE FROM Users WHERE [email protected]";
                    break;
                default:
                    sqlCmd = String.Format("DELETE FROM {0} WHERE [email protected]", tableName.ToString());
                    break;
            }

            try
            {
                using (SqlConnection sqlConn = new SqlConnection(GetConnectionString()))
                {
                    sqlConn.Open();
                    SqlCommand sqlQuery = new SqlCommand(sqlCmd, sqlConn);
                    sqlQuery.Parameters.AddWithValue("@id", id);
                    return sqlQuery.ExecuteNonQuery() == 1;
                }
            }
            catch(Exception ex) { return false; }
        }

        /// <summary>
        /// Reads all rows out of the local flat-files for the specified day and table
        /// </summary>
        /// <param name="day">The day to grab rows from.</param>
        /// <param name="tableName">Name of the table to grab data from.</param>
        /// <param name="getDataAfterTime">Determines if data should be pulled from all rows before the timestamp in the day parameter or after</param>
        /// <returns>Returns a DataTable that contains all rows from for a specified day starting at the specified time</returns>
        public static DataTable ReadDay(DateTime day, TableName tableName, bool getDataAfterTime)
        {
            string path = Path.Combine(Directory.GetCurrentDirectory(), string.Format("analysis {0}{1}{2}.db", day.Year, day.Month.ToString().PadLeft(2, '0'), day.Day.ToString().PadLeft(2, '0')));
            if (!File.Exists(path)) {
                return null;
            }
            DataTable dataTable = new DataTable();
            using (SQLiteConnection connection = new SQLiteConnection("Data Source=" + path + ";Version=3")) {
                string format = (((day.Hour == 0) && (day.Minute == 0)) && (day.Second == 0)) ? "Select * From {0}" : ("Select * From {0} Where CreateTime " + (getDataAfterTime ? "> " : "< ") + "@cDate"); //day.ToString("G"));
                using (SQLiteCommand command = new SQLiteCommand(string.Format(format, tableName.ToString()), connection)) {
                    var param = command.CreateParameter();
                    param.Value = day.ToString("G");
                    param.ParameterName = "@cDate";
                    command.Parameters.Add(param);

                    using (SQLiteDataAdapter adapter = new SQLiteDataAdapter(command)) {
                        adapter.Fill(dataTable);
                    }
                }
            }
            return dataTable;
        }

        private void PrepareInsert(TableName tableName)
        {
            IList<DbField> fields = DbDefault.GetTableStructure(tableName).Columns;
            StringBuilder sql = new StringBuilder();

            sql.Append("INSERT INTO " + tableName.ToString() + "(");

            for (int j = 0; j < fields.Count; j++)
            {
                sql.Append(fields[j].FieldName + ", ");
            }

            sql = sql.Remove(sql.Length - 2, 1).Append(")");

            sql.Append(" VALUES(");

            for (int k = 0; k < fields.Count; k++)
            {
                sql.Append("?, ");
            }

            sql = sql.Remove(sql.Length - 2, 1).Append(")");

            Command.CommandType = CommandType.Text;
            Command.CommandText = sql.ToString();
            Command.Prepare();
        }

        protected DataTable Select(TableName tableName, String condition)
        {            
            DataTable table = new DataTable();

            if (condition == null)
                condition = String.Empty;
            if (condition != String.Empty)
                condition = "WHERE " + condition;

            Command.CommandText = String.Format(
                "SELECT * FROM {0} {1}", tableName.ToString(), condition);

            Log(String.Format("Select command: {0}", 
                Command.CommandText));

            DataAdapter.Fill(table);

            return table;
        }

 public static String GetDropTableStatement(TableName tableName)
 {
     return String.Format("DROP TABLE {0} ", tableName.ToString());
 }

        public static String GetDeleteStatement(TableName tableName)
        {
            IList<DbField> fields = GetTableStructure(tableName).Columns;
            StringBuilder sql = new StringBuilder();

            sql.AppendFormat("DELETE FROM {0} ", tableName.ToString());

            return sql.ToString();
        }

        public static String GetUpdateStatement(TableName tableName, long[] pKey)
        {
            IList<DbField> fields = GetTableStructure(tableName).Columns;
            IList<String> pkFields = GetTableStructure(tableName).PKColumns;
            StringBuilder sql = new StringBuilder();

            sql.AppendFormat("UPDATE {0} SET ", tableName.ToString());

            for (int i = pKey.Length; i < fields.Count; i++)
                sql.AppendFormat("{0} = {{{1}}}, ", fields[i].FieldName, i - pKey.Length);

            sql = sql.Remove(sql.Length - 2, 1);

            if (pKey.Length > 0)
            {
                sql.AppendFormat("WHERE {0} = {1} ", fields[0].FieldName, pKey[0]);
                for (int j = 1; j < pKey.Length; j++)
                    sql.AppendFormat("AND {0} = {1} ", pkFields[j], pKey[j]);
            }

            return sql.ToString();
        }

        public static String GetInsertStatement(TableName tableName, long[] pKey)
        {
            IList<DbField> fields = GetTableStructure(tableName).Columns;
            StringBuilder sql = new StringBuilder();

            sql.AppendFormat("INSERT INTO {0}(", tableName.ToString());
            for (int i = 0; i < fields.Count; i++)
                sql.Append(fields[i].FieldName + ", ");

            sql = sql.Remove(sql.Length - 2, 1).Append(")");

            if (pKey.Length > 0)
            {
                sql.AppendFormat(" VALUES( {0}, ", pKey[0]);
                for (int j = 1; j < pKey.Length; j++)
                    sql.AppendFormat("{0}, ", pKey[j]);

                for (int k = pKey.Length; k < fields.Count; k++) 
                    sql.AppendFormat("{{{0}}}, ", k - pKey.Length);
            }

            return sql.Remove(sql.Length - 2, 1).Append(")").ToString();
        }

 public static String GetSelectRandomStatement(TableName tableName, int numRows)
 {
     return String.Format(
         "SELECT * FROM {0} ORDER BY RANDOM() FETCH FIRST {1} ROWS ONLY",
         tableName.ToString(), numRows);
 }

 public static String GetSelectCountStatement(TableName tableName)
 {
     return String.Format("SELECT COUNT(*) FROM {0}", tableName.ToString());
 }

        public static String GetSelectStatement(TableName tableName)
        {
            IList<DbField> fields = GetTableStructure(tableName).Columns;
            StringBuilder sql = new StringBuilder();

            sql.Append("SELECT ");
            for (int j = 0; j < fields.Count; j++)
                sql.Append(fields[j].FieldName + ", ");

            sql = sql.Remove(sql.Length - 2, 1);
            sql.Append("FROM " + tableName.ToString());

            return sql.ToString();
        }

        public static String GetCreateTableBasicStatement(TableName tableName)
        {
            DbTable table = GetTableStructure(tableName);
            IList<DbField> fields = table.Columns;
            IList<String> constraints = table.Constraints;
            StringBuilder sql = new StringBuilder();

            sql.Append("CREATE TABLE ");
            sql.AppendFormat("{0} (", tableName.ToString());

            foreach (DbField field in fields)
                sql.AppendFormat("{0} {1} NOT NULL, ", 
                    field.FieldName, DbTypeMap.GetGFXDType(field.FieldType, field.Length));

            foreach (String constraint in constraints)
                sql.AppendFormat("{0}, ", constraint);

            sql = sql.Remove(sql.Length - 2, 1).Append(") ");

            return sql.ToString();
        }

        public override bool Update(TableName tableName, string[] argsCol, string[] argsVal, int id)
        {
            string sqlCmd = String.Empty;
            string table = String.Empty;
            string selectColumns = String.Empty;
            int selectIndex = 0;
            string idName = String.Empty;
            switch (tableName)
            {
                case TableName.Menus:
                    idName = "MenuID";
                    break;
                case TableName.Clients:
                    idName = "ClientID";
                    break;
                case TableName.Orders:
                    idName = "OrderID";
                    break;
                case TableName.Persons:
                    idName = "PersonID";
                    break;
                case TableName.TableOrders:
                    idName = "TableOrderID";
                    break;
                case TableName.Tables:
                    idName = "TableID";
                    break;
                case TableName.Users:
                    idName = "UserID";
                    break;
                default:
                    idName = "ID";
                    break;
            }
            for (int i = 0; i < argsCol.Count(); i++)
            {
                if (i == 0) selectColumns += String.Format("{0}='{1}'", argsCol[i], argsVal[i]);
                else selectColumns += String.Format(",{0}='{1}'", argsCol[i], argsVal[i]);
            }

            sqlCmd = String.Format("UPDATE {0} SET {1} WHERE {2}[email protected]", tableName.ToString(), selectColumns, idName);

            try
            {
                using (SqlConnection sqlConn = new SqlConnection(GetConnectionString()))
                {
                    sqlConn.Open();
                    SqlCommand sqlQuery = new SqlCommand(sqlCmd, sqlConn);
                    sqlQuery.Parameters.AddWithValue("@id", id);
                    return sqlQuery.ExecuteNonQuery() == 1;
                }
            }
            catch { return false; }
        }

        public override bool Insert(TableName tableName, string[] values)
        {
            string sqlCmd = String.Empty;
            string statement = String.Empty;
            int selectIndex = 0;

            foreach (string str in values)
            {
                bool hasLetters = false;
                foreach (Char c in str)
                    if (Char.IsLetter(c)) hasLetters = true;

                if (str.Contains(" ") || hasLetters && str != "NULL")
                {
                    if (selectIndex == 0) statement += String.Format("'{0}'",str);
                    else statement += String.Format(",'{0}'", str);
                }
                else
                {
                    if (selectIndex == 0) statement += str;
                    else statement += String.Format(",{0}", str);
                }
                selectIndex++;
            }

            try
            {
                using (SqlConnection sqlConn = new SqlConnection(GetConnectionString()))
                {
                    switch (tableName)
                    {
                        case TableName.AccessDenied:
                            sqlCmd = String.Format("INSERT INTO AccessDenied (MachineID,BlockedSince,BlockedUntil) VALUES ({0})", statement);
                            break;
                        case TableName.Clients:
                            sqlCmd = String.Format("INSERT INTO Clients (Name,Address,PostalCode,City,PhoneNumber,Email) VALUES ({0})", statement);
                            break;
                        case TableName.Menus:
                            sqlCmd = String.Format("INSERT INTO Menus (Entree,MainCourse,Dessert,Price) VALUES ({0})", statement);
                            break;
                        case TableName.Orders:
                            sqlCmd = String.Format("INSERT INTO Orders (ClientID,StartDateTime,EndDateTime) VALUES ({0})", statement);
                            break;
                        case TableName.Persons:
                            sqlCmd = String.Format("INSERT INTO Persons (MenuID,OrderID,TableID) VALUES ({0})", statement);
                            break;
                        case TableName.TableOrders:
                            sqlCmd = String.Format("INSERT INTO TableOrders (TableID,OrderID,OrderReady) VALUES ({0})", statement);
                            break;
                        case TableName.Tables:
                            sqlCmd = String.Format("INSERT INTO Tables (TableNumber,AmountOfChairs) VALUES ({0})", statement);
                            break;
                        case TableName.Users:
                            sqlCmd = String.Format("INSERT INTO Users (EmployeeType,Password) VALUES ({0})", statement);
                            break;
                        default:
                            sqlCmd = String.Format("INSERT INTO {0} VALUES({1})", tableName.ToString(), statement);
                            break;
                    }
                    sqlConn.Open();
                    SqlCommand sqlQuery = new SqlCommand(sqlCmd, sqlConn);
                    return sqlQuery.ExecuteNonQuery() == 1;
                }
            }
            catch( Exception ex ) { return false; }
        }

 /// <summary>
 /// Gets the total number of rows that exist in the specified table for a specified day.
 /// </summary>
 /// <param name="day">The day (the time portion is not used).</param>
 /// <param name="tableName">Name of the table to read data from.</param>
 /// <returns>An integer representing the total number of rows in the table</returns>
 public static int TotalRows(DateTime day, TableName tableName)
 {
     string path = Path.Combine(Directory.GetCurrentDirectory(), string.Format("analysis {0}{1}{2}.db", day.Year, day.Month.ToString().PadLeft(2, '0'), day.Day.ToString().PadLeft(2, '0')));
     if (!File.Exists(path)) {
         return -1;
     }
     DataTable dataTable = new DataTable();
     using (SQLiteConnection connection = new SQLiteConnection("Data Source=" + path + ";Version=3")) {
         using (SQLiteCommand command = new SQLiteCommand("Select COUNT(*) From " + tableName.ToString(), connection)) {
             using (SQLiteDataAdapter adapter = new SQLiteDataAdapter(command)) {
                 adapter.Fill(dataTable);
             }
         }
     }
     if ((dataTable != null) && (dataTable.Rows.Count > 0)) {
         return int.Parse(dataTable.Rows[0][0].ToString());
     }
     return 0;
 }