1using System;
2using System.Collections.Generic;
3using System.ComponentModel;
4using System.Data;
5using System.Data.SqlServerCe;
6using System.Drawing;
7using System.IO;
8using System.Text;
9using System.Windows.Forms;
10
11namespace SQLEverywhere
12{
13 public partial class Form1 : Form
14 {
15 public Form1()
16 {
17 InitializeComponent();
18 }
19
20 SqlCeEngine engine = null;
21 SqlCeConnection connection = null;
22
23 private void btnCreateDB_Click(object sender, EventArgs e)
24 {
25 try
26 {
27 if (File.Exists(txtDBName.Text + ".sdf"))
28 MessageBox.Show("Database already exists.");
29 else
30 {
31 engine = new SqlCeEngine("Data Source='" + txtDBName.Text + ".sdf';");
32 engine.CreateDatabase(); //Create the physical sdf file
33 CreateSchemaAndData(); //Create a test table and load it with data
34 MessageBox.Show("Database created and populated with test data.");
35 }
36 }
37 catch (Exception ex)
38 {
39 MessageBox.Show("Exception: " + ex.Message);
40 }
41 finally
42 {
43 if (engine != null)
44 engine = null;
45 }
46 }
47
48 private void btnSubmit_Click(object sender, EventArgs e)
49 {
50 try
51 {
52 if (!File.Exists(txtDBName.Text + ".sdf"))
53 MessageBox.Show("Database doesn't exist.");
54 else
55 {
56 //Open the database connection
57 if (OpenConnection())
58 {
59 //Clear results
60 dgResults.Rows.Clear();
61 dgResults.Columns.Clear();
62
63 //Get data
64 UsersResultSet resultSet = ExecuteSQLReturnUsersResultSet(txtSQL.Text.Trim());
65 if (resultSet != null)
66 {
67 //Add the columns to the datagridview
68 foreach (string column in resultSet.columns)
69 {
70 dgResults.Columns.Add(column, column);
71 }
72 //Add the data to the datagridview
73 foreach (List<string> row in resultSet.rows)
74 {
75 dgResults.Rows.Add(row.ToArray());
76 }
77 }
78 }
79 else
80 MessageBox.Show("Error opening connection");
81 }
82 }
83 catch (Exception ex)
84 {
85 MessageBox.Show("Exception: " + ex.Message);
86 }
87 finally
88 {
89 //Close the database connection
90 CloseConnection();
91 if (engine != null)
92 engine = null;
93 }
94 }
95
96 private void CreateSchemaAndData()
97 {
98 //Open the database connection
99 if (OpenConnection())
100 {
101 //Create the test table
102 ExecuteSQLReturnNothing("CREATE TABLE Users (UserID INTEGER " +
103 "PRIMARY KEY IDENTITY, FirstName nvarchar(20) NOT NULL, LastName nvarchar(30) NOT NULL)");
104 //Create test data
105 ExecuteSQLReturnNothing("INSERT INTO Users (FirstName, LastName) VALUES ('Bob', 'Smith')");
106 ExecuteSQLReturnNothing("INSERT INTO Users (FirstName, LastName) VALUES ('Tom', 'Jones')");
107 ExecuteSQLReturnNothing("INSERT INTO Users (FirstName, LastName) VALUES ('Bill', 'Jackson')");
108
109 //Close the database connection
110 CloseConnection();
111 }
112 else
113 MessageBox.Show("Error opening connection");
114 }
115
116 //Executes a command and returns noting
117 private int ExecuteSQLReturnNothing(string commandText)
118 {
119 SqlCeCommand command = connection.CreateCommand();
120 command.CommandText = commandText;
121 int result = (System.Int32)command.ExecuteNonQuery();
122 command.Dispose();
123 command = null;
124 return result;
125 }
126
127 //Executes a command and returns data in a UsersResultSet
128 private UsersResultSet ExecuteSQLReturnUsersResultSet(string commandText)
129 {
130 //Create command object
131 SqlCeCommand command = connection.CreateCommand();
132 //Set the SQL text
133 command.CommandText = commandText;
134 //Retrieve the data
135 SqlCeDataReader dataReader = command.ExecuteReader();
136
137 //Create a new UsersResultSet
138 UsersResultSet resultSet = new UsersResultSet();
139 //If data found
140 if (dataReader != null)
141 {
142 //Create the columns
143 for (int i = 0; i < dataReader.FieldCount; i++)
144 {
145 resultSet.columns.Add(dataReader.GetName(i));
146 }
147 //Load in the data
148 while (dataReader.Read())
149 {
150 List<string> row = new List<string>();
151 for (int i = 0; i < dataReader.FieldCount; i++)
152 {
153 row.Add(dataReader.GetValue(i).ToString());
154 }
155 resultSet.rows.Add(row);
156 }
157 }
158
159 //Release objects/memory
160 dataReader.Dispose();
161 dataReader = null;
162 command.Dispose();
163 command = null;
164
165 //Return UsersResultSet
166 return resultSet;
167 }
168
169 private bool OpenConnection()
170 {
171 try
172 {
173 //Open a connection to the database
174 engine = new SqlCeEngine("Data Source='" + txtDBName.Text + ".sdf';");
175 connection = new SqlCeConnection(engine.LocalConnectionString);
176 connection.Open();
177 return true;
178 }
179 catch (Exception ex)
180 {
181 MessageBox.Show(ex.Message);
182 }
183 return false;
184 }
185
186 private void CloseConnection()
187 {
188 //Close the connection to the database
189 if (connection != null && connection.State == ConnectionState.Open)
190 {
191 connection.Close();
192 connection = null;
193 }
194 }
195 }
196
197 //Result set defined with Generics
198 class UsersResultSet
199 {
200 public List<string> columns = new List<string>();
201 public List<List<string>> rows = new List<List<string>>();
202 }
203}