Creating tables in SDF database files

[zachattack05]

Would anyone have the heart to post some example code to create an empty table? My code doesn't seem to work, it says the data type "int" in invalid...here's what I have:

if (File.Exists(ExecutablePath + "\\idcc_data\\_IDCC.sdf") == true)
                    {
                        SqlCeCommand cmd;
                        SqlCeConnection connection = new SqlCeConnection("DataSource=" + ExecutablePath + "\\idcc_data\\_IDCC.sdf");
                        connection.Open();
                        // the file exists let's create the user table
                        string UserTableCreate = "CREATE TABLE tblUsers ("
                            + "ID int (11) NOT NULL AUTO_INCREMENT, "
                            + "txtUser nvarchar (40), "
                            + "mdaPass text, "
                            + "intALevel tinyint (2) )";
                        cmd = new SqlCeCommand(UserTableCreate, connection);
                        cmd.ExecuteNonQuery();
                        connection.Close();


                    }

Some simple sample code would be great...I guess I just can't figure the syntax out and I've looked everywhere, nothing seems to work.

[sknake]

I will post the code for creating an empty table .. but how is that going to help with your problem?

/// <summary>
    /// Builds a connection string
    /// </summary>
    /// <param name="server"></param>
    /// <param name="database"></param>
    /// <returns></returns>
    internal static string BuildSqlNativeConnStr(string server, string database)
    {
      return string.Format("Data Source={0};Initial Catalog={1};Integrated Security=True;", server, database);
    }

    /// <summary>
    /// Gets data after today using logic to determine what today is
    /// </summary>
    /// <returns></returns>
    internal static DataTable GetTable()
    {
      DataTable result = default(DataTable);

      List<string> Sql = new List<string>();
      Sql.Add("IF OBJECT_ID('tempdb..#Table', 'U') IS NOT NULL DROP TABLE #Table");
      Sql.Add("Create Table #Table");
      Sql.Add("(");
      Sql.Add("  [Date] DateTime,");
      Sql.Add("  EventName varchar(100)");
      Sql.Add(")");
      Sql.Add("");
      Sql.Add("Insert Into #Table ([Date], EventName) Values (GetDate()-3, 'Event 1')");
      Sql.Add("Insert Into #Table ([Date], EventName) Values (GetDate()-2, 'Event 2')");
      Sql.Add("Insert Into #Table ([Date], EventName) Values (GetDate()-1, 'Event 3')");
      Sql.Add("Insert Into #Table ([Date], EventName) Values (GetDate(), 'Event 4')");
      Sql.Add("Insert Into #Table ([Date], EventName) Values (GetDate()+1, 'Event 5')");
      Sql.Add("Insert Into #Table ([Date], EventName) Values (GetDate()+2, 'Event 6')");
      Sql.Add("Insert Into #Table ([Date], EventName) Values (GetDate()+3, 'Event 7')");
      Sql.Add("");
      Sql.Add("Select *");
      Sql.Add("From #Table");
      Sql.Add("Where [Date] >= Cast(Floor(Cast(GetDate() as float)) as DateTime)");
      string query = GetText(Sql);
      string connStr = BuildSqlNativeConnStr("apex2006sql", "Scott");
      using (SqlConnection conn = new SqlConnection(connStr))
      {
        conn.Open();
        using (SqlCommand cmd = new SqlCommand(query, conn))
        {
          using (SqlDataReader dr = cmd.ExecuteReader())
          {
            result = new DataTable();
            result.Load(dr);
          }
        }
        conn.Close();
      }
      return result;
    }

I don't know if ce-sql is the same as standard MSSQL? But here is how you would do it with standard MSSQL:

IF OBJECT_ID('tempdb..#tblUsers', 'U') IS NOT NULL DROP TABLE #tblUsers
Create Table #tblUsers
(
  ID int identity(1,1) PRIMARY KEY,
  txtUser nvarchar (40),
  mdaPass text,
  intALevel tinyint
)

Also you're specifying a width on your tinyint column. Integral data types cannot have a size specification as that is determined by the type of integer, ie tinyint. You have the same problem for your first column int (11) .

[zachattack05]

I will post the code for creating an empty table .. but how is that going to help with your problem?

...

I don't know if ce-sql is the same as standard MSSQL? But here is how you would do it with standard MSSQL:

IF OBJECT_ID('tempdb..#tblUsers', 'U') IS NOT NULL DROP TABLE #tblUsers
Create Table #tblUsers
(
  ID int identity(1,1) PRIMARY KEY,
  txtUser nvarchar (40),
  mdaPass text,
  intALevel tinyint
)

Also you're specifying a width on your tinyint column. Integral data types cannot have a size specification as that is determined by the type of integer, ie tinyint. You have the same problem for your first column int (11) .

I fixed the width problem (honestly I have never created tables using SQL syntax, when I do it on web servers I always used PhpMyAdmin...

Anyway I still can't figure this out, I read over your code and changed mine to nearly identically match. Here's what I have now:

if (File.Exists(ExecutablePath + "\\idcc_data\\_IDCC.sdf") == true)
                    {
                        SqlCeCommand cmd;
                        SqlCeConnection connection = new SqlCeConnection("DataSource=" + ExecutablePath + "\\idcc_data\\_IDCC.sdf");
                        connection.Open();
                        // the file exists let's create the user table
                        string UserTableCreate = "CREATE TABLE tblUsers ( "
                            + "ID INT (1,1) PRIMARY KEY, "
                            + "txtUser nvarchar (40), "
                            + "mdaPass TEXT, "
                            + "intALevel TINYINT )";
                        cmd = new SqlCeCommand(UserTableCreate, connection);
                        cmd.ExecuteNonQuery();
                        connection.Close();

I tried adding the # sign to the front of the table name...no dice, it says its a reserved character...figures. I keep getting:

The specified data type is not valid. [ Data type (if known) = int ]

Any ideas what I am doing wrong? The error occurs at cmd.ExecuteNonQuery(); I think it's a syntax problem...I just don't know what the problem is...

[sknake]

The # marks the table as a temporary table. The SQL Server drops the table when your connection dies. You don't want to do that for permanent tables. I did it so I wouldn't have to clean up afterwards ;)

You didn't specify identity on your primary key. Try this code:

private void YouDidntPostYourEntireMethod()
    {
      if (File.Exists(ExecutablePath + "\\idcc_data\\_IDCC.sdf") == true)
      {
        SqlCeCommand cmd;
        SqlCeConnection connection = new SqlCeConnection("DataSource=" + ExecutablePath + "\\idcc_data\\_IDCC.sdf");
        connection.Open();
        // the file exists let's create the user table
        string UserTableCreate = BuildSql();
        cmd = new SqlCeCommand(UserTableCreate, connection);
        cmd.ExecuteNonQuery();
        connection.Close();
      }
    }

    private static string BuildSql()
    {
      List<string> Sql = new List<string>();
      Sql.Add("IF OBJECT_ID('tblUsers', 'U') IS NULL");
      Sql.Add("Create Table tblUsers");
      Sql.Add("(");
      Sql.Add("  ID int identity(1000, 1) PRIMARY KEY,");
      Sql.Add("  txtUser nvarchar(40),");
      Sql.Add("  mdaPass text,");
      Sql.Add("  intALevel tinyint");
      Sql.Add(")");
      StringBuilder sb = new StringBuilder();
      foreach (string s in Sql)
        sb.AppendLine(s);
      return sb.ToString().Trim();
    }

Also the text datatype has been deprecated in favor of varchar(max) / nvarchar(max) in SQL2005 and later. Unless the "mdaPass" field is going to be 8000+ characters you could just use a varchar / nvarchar data type.

[zachattack05]

The # marks the table as a temporary table. The SQL Server drops the table when your connection dies. You don't want to do that for permanent tables. I did it so I wouldn't have to clean up afterwards ;)

You didn't specify identity on your primary key. Try this code:

private void YouDidntPostYourEntireMethod()
    {
      if (File.Exists(ExecutablePath + "\\idcc_data\\_IDCC.sdf") == true)
      {
        SqlCeCommand cmd;
        SqlCeConnection connection = new SqlCeConnection("DataSource=" + ExecutablePath + "\\idcc_data\\_IDCC.sdf");
        connection.Open();
        // the file exists let's create the user table
        string UserTableCreate = BuildSql();
        cmd = new SqlCeCommand(UserTableCreate, connection);
        cmd.ExecuteNonQuery();
        connection.Close();
      }
    }

    private static string BuildSql()
    {
      List<string> Sql = new List<string>();
      Sql.Add("IF OBJECT_ID('tblUsers', 'U') IS NULL");
      Sql.Add("Create Table tblUsers");
      Sql.Add("(");
      Sql.Add("  ID int identity(1000, 1) PRIMARY KEY,");
      Sql.Add("  txtUser nvarchar(40),");
      Sql.Add("  mdaPass text,");
      Sql.Add("  intALevel tinyint");
      Sql.Add(")");
      StringBuilder sb = new StringBuilder();
      foreach (string s in Sql)
        sb.AppendLine(s);
      return sb.ToString().Trim();
    }

Also the text datatype has been deprecated in favor of varchar(max) / nvarchar(max) in SQL2005 and later. Unless the "mdaPass" field is going to be 8000+ characters you could just use a varchar / nvarchar data type.

Yay! Your a life saver! Thank you so much!

PS - I would have posted my entire method but most of it is irrelevant...in the future I'll make sure to post all of it.

Also, the mdaPass field (which actually should be shaPass) is going to hold the encrypted password for the user...in general what data type and length do people use to hold those? I was under the impression that it can get pretty long...is that wrong?

[sknake]

It depends ... you need to set a maximum length for your password on the user interface side before its encrypted, of say, 20 characters. Then you need to generate a password hash for a 20 character string and make your database column about %20 longer than that value just to be safe. I use varchar since i'm doing english-only applications but I suppose you really should be using nvarchar to be "proper".

Please mark this thread as solved if you have found a solution to your issue and good luck!

[zachattack05]

It depends ... you need to set a maximum length for your password on the user interface side before its encrypted, of say, 20 characters. Then you need to generate a password hash for a 20 character string and make your database column about %20 longer than that value just to be safe. I use varchar since i'm doing english-only applications but I suppose you really should be using nvarchar to be "proper".

Please mark this thread as solved if you have found a solution to your issue and good luck!

Great!

I'll see what I can do with the password limit. That's a good idea.

Thanks for the help!