paddygalway

Hi I am pretty new to C# and I am wonderind how do I access a database using SQL on a mobile device I have it all doen on the PC side but I can't seem to do it on the mobile device side I created a new Project and used the Visual C# --> Smart Device --> Pocket PC --> Device Application (then I created a form and also a SQL database)

Is that the right path I will show you code I put into the 'Login' form:

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.Common;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Data.Common;
using System.Security.Cryptography;


namespace PDA2
{
public partial class Loginform : Form
{
public Loginform()
{
InitializeComponent();
}
private bool Connection()
{
string encrypt;
encrypt = this.Passwordtxt.Text;
string password = getMd5Hash(encrypt);

bool isSuccessful = false;
string connectionString = "Data Source=LAB314-133;Initial Catalog=diabetesPDA;Integrated Security=True;";
SqlConnection connectionLogins = new SqlConnection(connectionString);
connectionLogins.Open();

try
{
SqlDataAdapter da = null;

da = new SqlDataAdapter("SELECT Username, Firstname FROM users WHERE Username = '"
+ Usernametxt.Text
+ "' AND Password='" + password + "'", connectionLogins);

SqlDataReader reader = da.SelectCommand.ExecuteReader();
while (reader.Read())
{
temp = reader["Firstname"].ToString();
Console.WriteLine(String.Format("0"), reader[0]);
isSuccessful = true;
}
reader.Close();
}
catch { }


connectionLogins.Close();

return isSuccessful;
}
static string getMd5Hash(string input)
{
MD5 md5Hasher = MD5.Create();

byte[] data = md5Hasher.ComputeHash(Encoding.Default.GetBytes(input));

StringBuilder sBuilder = new StringBuilder();

for (int i = 0; i < data.Length; i++)
{
sBuilder.Append(dataIdea.ToString("x2"));
}
return sBuilder.ToString();
}
private void Registerbtn_Click(object sender, EventArgs e)
{
ProfileForm us = new ProfileForm();
us.Show();
this.Hide();
}
private void Loginbtn_Click(object sender, EventArgs e)
{

if (Usernametxt.Text == "" && Passwordtxt.Text == "")
{
MessageBox.Show("Username and Password must be entered", "Username and Password", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
else if (Usernametxt.Text == "" && Passwordtxt.Text != "")
{
MessageBox.Show("Username must be entered", "Username Not Entered", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
else if (Usernametxt.Text != "" && Passwordtxt.Text == "")
{
MessageBox.Show("Password be entered", "Password Not Entered", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
else
{
if (Connection())
{
MessageBox.Show("Menu");
MenuForm mf = new MenuForm();
//mf.setfirstname(temp);
//mf.setusername(Usernametxt.Text);
mf.Show();
this.Hide();
}
else
MessageBox.Show("No Menu");
}
}
private void linkLabel1_Click(object sender, EventArgs e)
{
UpdateForm mf = new UpdateForm();
mf.Show();
this.Hide();
}
}
}





Re: Smart Devices VB and C# Projects Accessing a database with a mobile device

Rhapsody

Which error do you get

Where is your database located Is it on your device






Re: Smart Devices VB and C# Projects Accessing a database with a mobile device

paddygalway

Well I'm getting an error with the SQL code.


using System.Data.Sql;
Error 1 The type or namespace name 'Sql' does not exist in the namespace 'System.Data' (are you missing an assembly reference ) C:\FYP Code\PDA2\PDA2\Login.cs 9 19 PDA2

using System.Data.SqlClient;
Error 2 The type or namespace name 'SqlClient' does not exist in the namespace 'System.Data' (are you missing an assembly reference ) C:\FYP Code\PDA2\PDA2\Login.cs 10 19 PDA2

I created the database by using the button Connect To Database, and when the add connection can up under the Data source I clicked the Change button and then choose the Microsoft SQL Server Mobile Edition and created the database from there. I think that is where the database is

I am using Microsoft Visual Studios 2005, and Microsoft SQL Server 2005. But I think it is the
using System.Data.Sql;
using System.Data.SqlClient; that is causing the problems




Re: Smart Devices VB and C# Projects Accessing a database with a mobile device

Rhapsody

is SQL Mobile/.NETCF installed succesfully




Re: Smart Devices VB and C# Projects Accessing a database with a mobile device

paddygalway

I should have how would I find out Did I create a mobile database properly




Re: Smart Devices VB and C# Projects Accessing a database with a mobile device

paddygalway

using System.Data.SqlTypes;
using System.Data.Common;

I am getting both of these options.

But i need more then that I need these I think
using System.Data.Sql;
using System.Data.SqlClient;
for the SQL commands to work or do I






Re: Smart Devices VB and C# Projects Accessing a database with a mobile device

Ilya Tumanov

Make sure you have reference to System.Data.SqlCleint.dll. If you're on NETCF V1 also add reference to System.Data.Common.dll. Click on "References" in the solution explorer to add them.< xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

After you fixed your compilation problem, see this on how to diagnose and fix connectivity issues.

 






Re: Smart Devices VB and C# Projects Accessing a database with a mobile device

paddygalway

Thanks a million for the help very much appreciated just one last question how do I put the database on the PDA




Re: Smart Devices VB and C# Projects Accessing a database with a mobile device

Ilya Tumanov

SQL Client only works with remote SQL Server 2000/2005 databases so you never put your database on device.

If you need local database you should use SQL Server CE with respective ADO.Net provider.






Re: Smart Devices VB and C# Projects Accessing a database with a mobile device

paddygalway

So what your saying is I have to upgrade my SQL Server and put SQL Server CE on it also I getting a path error {"The database file cannot be found. Check the path to the database. [ File name = diabetes.sdf ]"} how do I enter in the path. This is my connecting string (Data Source ="C:\FYP Code\Mobile\diabetes.sdf";) but this doesnt work.




Re: Smart Devices VB and C# Projects Accessing a database with a mobile device

Ilya Tumanov

No, that's not at all what I'm saying. You need to understand what SQL CE and SQL Server are in fact two different databases. Do not confuse them - SQL Server runs on desktop and can be accessed remotely. SQL CE is local in process database. They don't need each other to operate but can work together if needed via RDA or replication.

Typical scenario for SQL CE is what there¡¯s a local database located on device which is used via SQL CE ADO.Net adapter, no connection to SQL Server is needed. If you have back end enterprise database then SQL CE can synchronize local database with this back end server from time to time (e.g. after work as device is cradled for recharge).

Devices don't support drive letters, can't see your desktop hard drive and don't support relative paths. Since your connection string contains drive letter it can't possibly work on device.






Re: Smart Devices VB and C# Projects Accessing a database with a mobile device

paddygalway

But should sql server 2005 mobile edition not work on a PDA I am running that on the pc while running a emulator but it isn't working I can seem to find the path in the properties it say tha the Data Source ="C:\MyDatabase#6.sdf"; but it won't accept it I think it have something to do with the C:\ but it saves it automatically in there what can I do



Re: Smart Devices VB and C# Projects Accessing a database with a mobile device

Ilya Tumanov

Yes, SQL CE is designed to run on devices. I'm not sure why you're struggling with this as devices designers construct correct path automatically. Here's what designers generated in my sample project:

private void InitConnection() {

this._connection = new System.Data.SqlServerCe.SqlCeConnection();

this._connection.ConnectionString = ("Data Source ="

+ (System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase) + \\AppDatabase1.sdf;));

}

Make sure you're not trying to run desktop project on device. Consider starting new device project and simply add database to it (or create new one), designers will do the rest.






Re: Smart Devices VB and C# Projects Accessing a database with a mobile device

paddygalway

The provider is .NET Framework Data Provider for SQL Server Mobile Edition, I am running this on a Emulator does that matter this problem is confusing me. I am using SQL Server 2005 developer edition that does contain SQL CE does it or do I need something else At the moment I am saving the database on my C drive., and this is the code I'm using.

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.Common;
using System.Data.SqlClient;
using System.Data.SqlServerCe;
using System.Data.SqlTypes;
using System.Security.Cryptography;

namespace PDA
{
    public partial class RegisterForm : Form
    {
        public RegisterForm()
        {
            InitializeComponent();
        }

        private bool Connection()
        {
            bool isSuccessful = false;

            string connectionString = "Data Source =C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\diabetes.sdf;";
            SqlCeConnection connectionUser = new SqlCeConnection(connectionString);
            connectionUser.Open();

            try
            {
                SqlCeDataAdapter dataAdapterUser = new SqlCeDataAdapter();
                string queryString = "INSERT INTO user (Firstname)VALUES('" + firstnametxt.Text + "')";

                dataAdapterUser.InsertCommand = new SqlCeCommand(queryString, connectionUser);
                dataAdapterUser.InsertCommand.ExecuteNonQuery();

                isSuccessful = true;
                return isSuccessful;
            }
            catch
            {
                return isSuccessful;
            }
        }

        private void registerbtn_Click(object sender, EventArgs e)
        {
            if (Connection())
            {
                LoginForm lf = new LoginForm();
                lf.Show();
                this.Hide();
            }
            else
            {
                MessageBox.Show("Connection Failed");
            }
        }
    }
}




Re: Smart Devices VB and C# Projects Accessing a database with a mobile device

paddygalway

I figured out what was wrong I went onto the emulator and I realised it had a path to it and I enter it into my connection string and it worked