1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:

    public static class TQuestions
    {
        public static string
            TABLE_NAME = "Questions",
            ID = "ID",              //Primärschlüssel
            C_ID = "C_ID",          //Fremdschlüssel des Kommentars
            SUBJECT = "Subject",    //ID des Themas 
            TYPE = "Type",          //Typ der Frage
            TEXT = "Text";          //eigentliche Frage
    }

    //Datenbank anlegen
    if (!Directory.Exists(Path.GetDirectoryName(Filename)))
        Directory.CreateDirectory(Path.GetDirectoryName(Filename));

    using (SqlCeEngine engine = new SqlCeEngine())
    {
        engine.LocalConnectionString = "Data Source='" + Filename + "'; LCID=1033;" +
            "Password='" + DB_PASSWORD + "'; Encrypt = TRUE; Case Sensitive = TRUE;";
        engine.CreateDatabase();
    }

    //Tabelle für die Fragen erzeugen
    tableExists = TableExists(connection, TQuestions.TABLE_NAME);
    if (DropIfExists && tableExists)
        DropTable(connection, TQuestions.TABLE_NAME);
    if (!tableExists || DropIfExists)
    {
        cmd.CommandText = "CREATE TABLE " + TQuestions.TABLE_NAME + " (" +
            TQuestions.ID + " INT NOT NULL PRIMARY KEY IDENTITY, " +
            TQuestions.C_ID + " INT, " +
            TQuestions.SUBJECT + " INT, " +
            TQuestions.TYPE + " INT NOT NULL, " +
            TQuestions.TEXT + " NVARCHAR (2000) NOT NULL)";
        cmd.ExecuteNonQuery();
    }
    
    //Daten einer Frage speichern
    if (_owner == null || _owner.ID <= 0)
        throw new Exception("CQuestion.SaveToDatabase - invalid owner or owner id!");
    _comment.SaveToDatabase(connection);
    SqlCeCommand cmd = connection.CreateCommand();

    bool insert = true;
    if (_ID > 0)
    {
        cmd.CommandText = "UPDATE " + TQuestions.TABLE_NAME + " SET " +
            TQuestions.C_ID + " = " + (_comment.ID > 0 ? _comment.ID.ToString() : "NULL") + ", " +
            TQuestions.SUBJECT + " = " + _subjectID + ", " +
            TQuestions.TYPE + " = " + ((int)_type) + ", " +
            TQuestions.TEXT + " = '" + _text + "' " + 
        "WHERE " + TQuestions.ID + " = " + _ID;
        insert = (cmd.ExecuteNonQuery() == 0);
    }

    if (insert)
    {
        cmd.CommandText =
            "INSERT INTO " + TQuestions.TABLE_NAME + " (" +
                TQuestions.C_ID + ", " + TQuestions.SUBJECT + ", " + TQuestions.TYPE + ", " + TQuestions.TEXT + ") " +
             "VALUES (" +
                (_comment.ID > 0 ? _comment.ID.ToString() : "NULL") + ", " +
                _subjectID + ", " +
                ((int)_type) + ", " +
                "'" + _text + "')";
        cmd.ExecuteNonQuery();

        cmd.CommandText = "SELECT * FROM " + TQuestions.TABLE_NAME + " WHERE " + TQuestions.ID + " = @@IDENTITY;";
        using (SqlCeDataReader reader = cmd.ExecuteReader())
        {
            if (reader.Read())
                _ID = (int)reader[TQuestions.ID];
        }
    }

    cmd.CommandText = "SELECT COUNT(*) FROM " + TCorrPagesQuestions.TABLE_NAME + " WHERE " +
        TCorrPagesQuestions.P_ID + " = " + _owner.ID + " AND " +
        TCorrPagesQuestions.Q_ID + " = " + _ID;
    using (SqlCeDataReader reader = cmd.ExecuteReader())
    {
        insert = true;
        if (reader.Read())
            insert = ((int)reader[0] == 0);
    }
    if (insert)
    {
        cmd.CommandText = 
            "INSERT INTO " + TCorrPagesQuestions.TABLE_NAME + " (" +
                TCorrPagesQuestions.P_ID + ", " + TCorrPagesQuestions.Q_ID + ") " +
            "VALUES (" +
                _owner.ID + ", " + _ID + ")";
        if (cmd.ExecuteNonQuery() <= 0)
            throw new Exception("CQuestion.SaveToDatabase - failed to insert values to table " + TCorrPagesQuestions.TABLE_NAME);
    }

    _answers.SaveToDatabase(connection);
    _statements.SaveToDatabase(connection);					

1:
2:
3:
4:
5:

command.CommandText = @"INSERT INTO MeinTabelle(MeinFeld) VALUES(MeineDaten); SELECT @Id = @@IDENTITY;"
SqlCeParameter param = command.Parameters.Add("@Id", SqlDbType.Int);
param.Direction = ParameterDirection.Output;
command.ExecuteNonQuery();
_ID = param.value;					

1:
2:
3:
4:

command.CommandText = @"INSERT INTO MeinTabelle(MeinFeld) VALUES (MeineDaten)"
command.ExecuteNonQuery();
command.CommandText = "SELECT @@IDENTITY"
_ID = Convert.ToInt32(command.ExecuteScalar());					

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:

            SqlCeCommand cmd = connection.CreateCommand();
            cmd.CommandText = "SELECT * FROM QuestionIndex WHERE QuestionIndex.ID = @ID";
            cmd.Parameters.Add("ID", _ID);
            using (SqlCeDataAdapter adp = new SqlCeDataAdapter(cmd))
            {
                SqlCeCommandBuilder cmdBuilder = new SqlCeCommandBuilder(adp);
 
                adp.DeleteCommand = cmdBuilder.GetDeleteCommand();
                adp.UpdateCommand = cmdBuilder.GetUpdateCommand();
                adp.InsertCommand = cmdBuilder.GetInsertCommand();

                DataSet data = new DataSet();
                adp.Fill(data);

                DataTable table = data.Tables[0];
                if (table == null)
                    throw new Exception("CQuestionIndex.SaveToDatabase - table not available: QuestionIndex");

                DataRow row;
                if (table.Rows.Count > 0)
                    row = table.Rows[0];
                else
                {
                    row = table.NewRow();
                    table.Rows.Add(row);
                }

                row["Date"] = _date.Ticks;
                row["Type"] = (int)_type;
                adp.Update(data);

                _pages.SaveToDatabase(connection);
            }