using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlServerCe;
using System.IO;
namespace SSCE1
{
public partial class Form1 : Form
{
#region Form1
public Form1()
{
InitializeComponent();
}
#endregion
#region btnCreateDatabase_Click
private void btnCreateDatabase_Click(object sender, EventArgs e)
{
string connectionString;
string fileName = “ArcaneCode.sdf”;
string password = “arcanecode”;
if (File.Exists(fileName))
{
File.Delete(fileName);
}
connectionString = string.Format(
“DataSource=\”{0}\”; Password='{1}'”, fileName, password);
SqlCeEngine en = new SqlCeEngine(connectionString);
en.CreateDatabase();
lblResults.Text = “Database Created.”;
}
#endregion
#region btnCreateTable_Click
private void btnCreateTable_Click(object sender, EventArgs e)
{
SqlCeConnection cn = new SqlCeConnection(ConnectString());
if (cn.State==ConnectionState.Closed)
{
cn.Open();
}
SqlCeCommand cmd;
string sql = “create table CoolPeople (“
+ “LastName nvarchar (40) not null, “
+ “FirstName nvarchar (40), “
+ “URL nvarchar (256) )”;
cmd = new SqlCeCommand(sql, cn);
try
{
cmd.ExecuteNonQuery();
lblResults.Text = “Table Created.”;
}
catch (SqlCeException sqlexception)
{
MessageBox.Show(sqlexception.Message, “Oh Crap.”, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, “Oh Crap.”, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
cn.Close();
}
}
#endregion
#region ConnectString
private string ConnectString()
{
string connectionString;
string fileName = “ArcaneCode.sdf”;
string password = “arcanecode”;
connectionString = string.Format(
“DataSource=\”{0}\”; Password='{1}'”, fileName, password);
return connectionString;
}
#endregion
#region btnLoadTable_Click
private void btnLoadTable_Click(object sender, EventArgs e)
{
try
{
LoadARow(“Carl”, “Franklin”, @”http:\\www.dnrtv.com”);
LoadARow(“Richard”, “Campbell”, @”http:\\www.dotnetrocks.com”);
LoadARow(“Leo”, “Laporte”, @”http:\\www.twit.tv”);
LoadARow(“Steve”, “Gibson”, @”http:\\www.grc.com”);
LoadARow(“Arcane”, “Code”, @”http:\\arcanecode.wordpress.com”);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, “Oh Crap.”, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
#endregion
#region LoadARow
private void LoadARow(string first, string last, string url)
{
SqlCeConnection cn = new SqlCeConnection(ConnectString());
if (cn.State == ConnectionState.Closed)
{
cn.Open();
}
SqlCeCommand cmd;
string sql = “insert into CoolPeople “
+ “(LastName, FirstName, URL) “
+ “values (@lastname, @firstname, @url)”;
try
{
cmd = new SqlCeCommand(sql, cn);
cmd.Parameters.AddWithValue(“@lastname”, last);
cmd.Parameters.AddWithValue(“@firstname”, first);
cmd.Parameters.AddWithValue(“@url”, url);
cmd.ExecuteNonQuery();
lblResults.Text = “Row Added.”;
}
catch (SqlCeException sqlexception)
{
MessageBox.Show(sqlexception.Message, “Oh Crap.”, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, “Oh Crap.”, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
cn.Close();
}
}
#endregion
#region cmdLoadDataGrid_Click
private void cmdLoadDataGrid_Click(object sender, EventArgs e)
{
SqlCeConnection cn = new SqlCeConnection(ConnectString());
if (cn.State==ConnectionState.Closed)
{
cn.Open();
}
try
{
// Set the command to use the table, not a query
SqlCeCommand cmd = new SqlCeCommand(“CoolPeople”, cn);
cmd.CommandType = CommandType.TableDirect;
// Get the table
SqlCeResultSet rs = cmd.ExecuteResultSet(
ResultSetOptions.Scrollable);
// load the result set into the datasource
dgvCoolPeople.DataSource = rs;
}
catch (SqlCeException sqlexception)
{
MessageBox.Show(sqlexception.Message, “Oh Crap.”,
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, “Oh Crap.”,
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
// Note, do not close the connection,
// if you do the grid won’t be able to display.
// For production code you probably want to make
// your result set (rs) a class level variable
}
#endregion
#region btnReadRecords_Click
private void btnReadRecords_Click(object sender, EventArgs e)
{
SqlCeConnection cn = new SqlCeConnection(ConnectString());
if (cn.State == ConnectionState.Closed)
{
cn.Open();
}
// Build the sql query. If this was real life,
// I’d use a parameter for the where bit
// to avoid SQL Injection attacks.
string sql = “select LastName, FirstName from CoolPeople “;
if (txtName.Text.Length > 0)
{
sql += “where LastName like ‘” + txtName.Text + “%’ “;
}
try
{
SqlCeCommand cmd = new SqlCeCommand(sql, cn);
cmd.CommandType = CommandType.Text;
// if you don’t set the result set to
// scrollable HasRows does not work
SqlCeResultSet rs = cmd.ExecuteResultSet(
ResultSetOptions.Scrollable);
// If you need to be able to update the result set, instead use:
// SqlCeResultSet rs = cmd.ExecuteResultSet(
// ResultSetOptions.Scrollable | ResultSetOptions.Updatable);
if (rs.HasRows)
{
// Use the get ordinal function so you don’t
// have to worry about remembering what
// order your SQL put the field names in.
int ordLastName = rs.GetOrdinal(“LastName”);
int ordFirstname = rs.GetOrdinal(“FirstName”);
// Hold the output
StringBuilder output = new StringBuilder();
// Read the first record and get it’s data
rs.ReadFirst();
output.AppendLine(rs.GetString(ordFirstname)
+ ” “ + rs.GetString(ordLastName));
// Now read thru the rest of the records.
// When there’s no more data, .Read returns false.
while (rs.Read())
{
output.AppendLine(rs.GetString(ordFirstname)
+ ” “ + rs.GetString(ordLastName));
}
// Set the output in the label
lblResults.Text = output.ToString();
}
else
{
lblResults.Text = “No Rows Found.”;
}
}
catch (SqlCeException sqlexception)
{
MessageBox.Show(sqlexception.Message, “Oh Crap.”,
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, “Oh Crap.”,
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
// Don’t need it anymore so we’ll be good and close it.
// in a ‘real life’ situation
// cn would likely be class level
cn.Close();
}
}
#endregion
}
}