SQL Server Compact Edition with C# and VB. Net. As a wrap up before this weekend’s code camp (http: //alabamacodecamp. I wanted to upload the entire project so you could have it in one spot.
![Connecting Sql Compact Edition Connecting Sql Compact Edition](http://www.mikelaverick.com/wp-content/uploads/2013/08/Screen-Shot-2013-07-06-at-04.25.17.png)
In addition, I’ve also created a VB. Net version. I won’t go back over and comment on everything, you can look over the posts from my last few days for detailed explanations this is simply so you can have the code all in one spot. I did the project as a simple windows project, here is an example of what my final form looked like: The C# version of the form looked identical except in the title bar, which reads SSCE C# Version.
Here is the complete C# version of the code behind the form. Note there is one difference from the code I put in my previous blog posts, in the Load. ARow method I had the parameters in the order of last name, then first name, but in the tn.
Load. Table_Click when I called Load. ARow I had put the cool people’s names in the order of first name, last name. I fixed that in the version below. System; using System.
![Connecting Sql Compact Edition Connecting Sql Compact Edition](http://jemsnets.com/images/Practice/symbian_img.png)
- Richard Says: March 28, 2009 at 8:13 am. Superb descriptions and sample code for novices getting to grips with SQL and CE. Note to Paul (and maybe others): The issue.
- Connection strings for SQL Server Compact. Connect using SqlCeConnection, SQLSERVER.CE.OLEDB.3.5, SQLSERVER.MOBILE.OLEDB.3.0 and OleDbConnection.
Collections. Generic; using System. Component. Model; using System. Data; using System. Drawing; using System. Text; using System.
![Connecting Sql Compact Edition Connecting Sql Compact Edition](http://static.savetodb.com/images/help/savetodb/connecting-excel-to-oracle-provider.png)
Windows. Forms; using System. Data. Sql. Server. Ce; using System. IO; namespace SSCE1{publicpartialclass.
The application I want to build using MS Visual C# Express (I'm willing to upgrade to Standard if that becomes required) that needs a database. I was all psyched. Creating a SQL Server Compact Edition Database Using Visual Studio Server Explorer April 10, 2007 — arcanecode. Connecting to local SQL Server database (SQL Server Compact 3.5 database file) using C# c# - Local database, I need some examples c# windows, local.
Form. 1 : Form { #region Form. Form. 1() { Initialize. Component(); } #endregion #region btn. Create. Database_Clickprivatevoid btn. Create. Database_Click(object sender, Event. Args e) {string connection. String; string file.
Can't find.bak file when connecting to SQL Server Management Studio with SQLEXPRESS. Jun 26, 2014 06:49 AM | iwasdavid | LINK.
![Connecting Sql Compact Edition Connecting Sql Compact Edition](http://www.filebuzz.com/software_screenshot/full/ems_sql_query_2007_for_db2-47454.gif)
Name = “Arcane. Code. File. Exists(file. Name)) {File. Delete(file. Name); } connection. String = string. Format(“Data.
Source=\”{0}\”; Password='{1}'”, file. Name, password); Sql.
Ce. Engine en = new. Sql. Ce. Engine(connection. String); en. Create. Database(); lbl. Results. Text = “Database Created.”; } #endregion #region btn. Create. Table_Clickprivatevoid btn.
Create. Table_Click(object sender, Event. Args e) {Sql. Ce. Connection cn = new. Sql. Ce. Connection(Connect.
String()); if (cn. State==Connection. State. Closed) { cn. Open(); }Sql. Ce.
Command cmd; string sql = “create table Cool. People (“ + “Last. Name nvarchar (4. First. Name nvarchar (4. URL nvarchar (2. 56) )”; cmd = new. Sql. Ce. Command(sql, cn); try { cmd. Execute. Non. Query(); lbl.
Results. Text = “Table Created.”; }catch (Sql. Ce. Exception sqlexception) {Message. Box. Show(sqlexception. Message, “Oh Crap.”, Message. Box. Buttons. OK, Message.
Box. Icon. Error); }catch (Exception ex) {Message. Box. Show(ex. Message, “Oh Crap.”, Message. Box. Buttons. OK, Message. Box. Icon. Error); }finally { cn. Close(); } } #endregion #region Connect. Stringprivatestring Connect.
String() {string connection. String; string file. Name = “Arcane. Code. String = string. Format(“Data. Source=\”{0}\”; Password='{1}'”, file. Name, password); return connection.
String; } #endregion #region btn. Load. Table_Clickprivatevoid btn. Load. Table_Click(object sender, Event. Args e) {try { Load. ARow(“Carl”, “Franklin”, @”http: \\www.
Load. ARow(“Richard”, “Campbell”, @”http: \\www. Load. ARow(“Leo”, “Laporte”, @”http: \\www. Load. ARow(“Steve”, “Gibson”, @”http: \\www.
Load. ARow(“Arcane”, “Code”, @”http: \\arcanecode. Exception ex) {Message.
Box. Show(ex. Message, “Oh Crap.”, Message. Box. Buttons. OK, Message. Box. Icon. Error); } } #endregion #region Load. ARowprivatevoid Load. ARow(string first, string last, string url) {Sql. Ce. Connection cn = new. Sql. Ce. Connection(Connect.
String()); if (cn. State == Connection. State. Closed) { cn. Open(); }Sql. Ce. Command cmd; string sql = “insert into Cool. People “ + “(Last.
Name, First. Name, URL) “ + “values (@lastname, @firstname, @url)”; try { cmd = new. Sql. Ce. Command(sql, cn); cmd. Parameters. Add. With. Value(“@lastname”, last); cmd. Parameters. Add. With.
Value(“@firstname”, first); cmd. Parameters. Add. With. Value(“@url”, url); cmd.
Execute. Non. Query(); lbl. Results. Text = “Row Added.”; }catch (Sql. Ce. Exception sqlexception) {Message.
Box. Show(sqlexception. Message, “Oh Crap.”, Message. Box. Buttons. OK, Message. Box. Icon. Error); }catch (Exception ex) {Message. Box. Show(ex. Message, “Oh Crap.”, Message.
Box. Buttons. OK, Message. Box. Icon. Error); }finally { cn. Close(); } } #endregion #region cmd. Load. Data. Grid_Clickprivatevoid cmd. Load. Data. Grid_Click(object sender, Event. Args e) {Sql. Ce. Connection cn = new.
Sql. Ce. Connection(Connect. String()); if (cn. State==Connection. State. Closed) { cn.
Open(); }try {// Set the command to use the table, not a query. Sql. Ce. Command cmd = new. Sql. Ce. Command(“Cool.
People”, cn); cmd. Command. Type = Command. Type. Table. Direct; // Get the table. Sql. Ce. Result. Set rs = cmd.
Execute. Result. Set(Result. Set. Options. Scrollable); // load the result set into the datasource dgv. Cool. People. Data. Source = rs; }catch (Sql. Ce. Exception sqlexception) {Message. Box. Show(sqlexception. Message, “Oh Crap.”,Message.
Box. Buttons. OK, Message. Box. Icon. Error); }catch (Exception ex) {Message. Box. Show(ex. Message, “Oh Crap.”,Message. Box. Buttons. OK, Message. Box. Icon. 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 btn.
Read. Records_Clickprivatevoid btn. Read. Records_Click(object sender, Event. Args e) {Sql. Ce. Connection cn = new. Sql. Ce. Connection(Connect. String()); if (cn. State == Connection.
State. 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. Last. Name, First. Name from Cool. People “; if (txt. Name. Text. Length > 0) { sql += “where Last.
Name like ‘” + txt. Name. Text + “%’ “; }try {Sql. Ce. Command cmd = new. Sql. Ce. Command(sql, cn); cmd.
Command. Type = Command. Type. Text; // if you don’t set the result set to // scrollable Has. Rows does not work. Sql. Ce. Result. Set rs = cmd. Execute. Result. Set(Result. Set. Options. Scrollable); // If you need to be able to update the result set, instead use: // Sql.
Ce. Result. Set rs = cmd. Execute. Result. Set(// Result. Set. Options. Scrollable | Result. Set. Options. Updatable); if (rs. Has. Rows) {// Use the get ordinal function so you don’t // have to worry about remembering what // order your SQL put the field names in. Last. Name = rs. Get.
Ordinal(“Last. Name”); int ord. Firstname = rs. Get. Ordinal(“First. Name”); // Hold the output. String. Builder output = new. String. Builder(); // Read the first record and get it’s data rs.
Read. First(); output. Append. Line(rs. Get. String(ord. Firstname) + ” “ + rs.
Get. String(ord. Last. Name)); // Now read thru the rest of the records.// When there’s no more data, . Read returns false. Read()) { output.
Append. Line(rs. Get. String(ord. Firstname) + ” “ + rs. Get. String(ord. Last. Name)); }// Set the output in the label lbl. Results. Text = output. To. String(); }else { lbl. Results. Text = “No Rows Found.”; } }catch (Sql.
Ce. Exception sqlexception) {Message. Box. Show(sqlexception. Message, “Oh Crap.”,Message. Box. Buttons. OK, Message. Box. Icon. Error); }catch (Exception ex) {Message. Box. Show(ex. Message, “Oh Crap.”,Message. Box. Buttons. OK, Message.
Box. Icon. 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 }}Here is the VB. Net version of the code. I tried to make all of the method names, variable names and comments match the C# version as much as possible. Imports System. Imports System. Collections. Generic. Imports System. Component.
Model. Imports System. Data. Imports System.
Drawing. Imports System. Text. Imports System. Windows. Forms. Imports System. Data. Sql. Server. Ce. Imports System. IOPublic. Class Form. Region“btn. Create.
Database_Click” ‘ Create an empty SSCE Database with a password. ‘ Note that when creating a db with code, adding a ‘ password automatically encrypts the database Private. Sub btn. Create. Database_Click(By. Val sender As System.
Object, _ By. Val e As System. Event. Args) Handles btn.
Create. Database. Click Dim connect.
String As. String = “” Dim file. Name As. String = “Arcane. Code. sdf” Dim password As. String = “arcanecode” If File. Exists(file. Name) Then File. Delete(file. Name) End.
If connect. String = String. Format( _ “Data.
Source=””{0}””; Password='{1}'”, file. Name, password) Dim eng As Sql. Ce. Engine = _ New Sql. Ce. Engine(connect. String) eng. Create. Database() lbl. Results. Text = “Database Created” End.
Sub#End. Region#Region“btn. Create. Table_Click” ‘ Issue a SQL command to create a table ‘ Note this only creates the table, it ‘ does not put any rows in it. Private.
Sub btn. Create. Table_Click(By. Val sender As System. Object, _ By. Val e As System. Event. Args) Handles btn. Create. Table. Click Dim cn As.
New Sql. Ce. Connection(Connect. String()) If cn. State = Connection. State. Closed Then cn. Open() End. If Dim cmd As Sql.
Ce. Command Dim sql As. String = “create table Cool. People (“ _ + “Last. Name nvarchar (4. First. Name nvarchar (4.
URL nvarchar (2. 56) )” cmd = New Sql. Ce. Command(sql, cn) Try cmd. Execute. Non. Query() lbl.
Results. Text = “Table created.” Catch sqlexception As Sql. Ce. Exception Message. Box. Show(sqlexception. Message, “Oh Crap.” _ , Message. Box. Buttons. OK, Message. Box. Icon. Error) Catch ex As Exception Message.
Box. Show(ex. Message, “Oh Crap.” _ , Message. Box. Buttons. OK, Message. Box. Icon. Error) Finally cn. Close() End. Try End. Sub#End. Region#Region“btn. Load. Table_Click” ‘ This routine calls a subroutine that ‘ does the real work of inserting rows ‘ into the database. Private. Sub btn. Load. Table_Click(By.
Val sender As System.
Can't find . bak file when connecting to SQL Server Management Studio with SQLEXPRESSiwasdavid. Using SQL Server Management Studio I am trying to restore a database using a bak file. If I connect using SQLEXPRESS then when I try and locate the bak file it is showing the location as empty where the file is stored.
If I follow the exact same steps but. SQL Server Management Studio using (localdb) then the file shows and restores the database as expected. I'm sure there is a studpily obvious reason for this as I am very new to ASP. NET MVC and the Microsoft platform.
Try to locate to this place. C: \Program Files\Microsoft SQL Server\MSSQL1. SS2. 01. 2EXPR\MSSQL\DATAinstead, to find out . If it is SS 2. 00.
C: \Program Files\Microsoft SQL Server\MSSQL1. SS2. 00. 8EXPR\MSSQL\DATA.