G
Guest
I am attempting to save a dynamic dataset to sql ce, and am coming across a
problem.
In the supplied code, it creates the table, which appears to work ok. The
create table command is as follows:
"Create Table \"allbills\" (\"SacID\" NVarChar,\"LineNum\"
Int,\"DateTouched\" NVarChar,\"EstabCode\" NVarChar,\"MAWB\"
NVarChar,\"HAWB\" NVarChar,\"ArrivalDate\" NVarChar,\"Flight\"
NVarChar,\"GoodsDescription\" NVarChar,\"AIMSUpgraded\" Bit,\"FSResultCode\"
NVarChar,\"InspectResultCode\" NVarChar,\"SACOfficer\"
NVarChar,\"ReportableDocInd\" Bit,\"CountSACsWithinMAWB\"
Int,\"CountHAWBsWithinMAWB\" Int,\"HVLVCountSACsInMAWB\"
Int,\"HVLVCountHAWBsInMAWB\" Int,\"SID\" NVarChar Not Null Primary
Key,\"FSModified\" NVarChar,\"InspModified\" NVarChar,\"FSResultOriginal\"
NVarChar,\"InspectResultOriginal\" NVarChar,\"MAWBLevel\"
NVarChar,\"FSUploaded\" NVarChar,\"InspUploaded\" NVarChar)"
The code then generates an Insert Command that appears to be correct, and
assigns a set of parameters to this command.
"Insert Into \"allbills\"
(\"SacID\",\"LineNum\",\"DateTouched\",\"EstabCode\",\"MAWB\",\"HAWB\",\"ArrivalDate\",\"Flight\",\"GoodsDescription\",\"AIMSUpgraded\",\"FSResultCode\",\"InspectResultCode\",\"SACOfficer\",\"ReportableDocInd\",\"CountSACsWithinMAWB\",\"CountHAWBsWithinMAWB\",\"HVLVCountSACsInMAWB\",\"HVLVCountHAWBsInMAWB\",\"SID\",\"FSModified\",\"InspModified\",\"FSResultOriginal\",\"InspectResultOriginal\",\"MAWBLevel\",\"FSUploaded\",\"InspUploaded\")
Values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
It then creates a dataadapter and assign this insert command to the
dataadapter, then runs dataadapter.update(table), which generates an
exception with the message "Update". Is the code using the Update method
correctly, any suggestions? Third party tools are not an option in this case.
(see source code below)
public static void storeDataToSQLCE( DataSet data, string dataFile )
{
if (File.Exists(dataFile)) // Get rid of existing database file
File.Delete(dataFile);
SqlCeEngine en = new SqlCeEngine("Data Source = " + dataFile ); //
Create new database engine
en.CreateDatabase(); // Create a new database
SqlCeConnection con = new SqlCeConnection("Data Source = " +
dataFile ); // Create a new connection
con.Open(); // Open this connection
foreach (DataTable table in data.Tables )
{
// Create tables one by one
SqlCeCommand cmd = con.CreateCommand();
// Prepare Create table command
StringBuilder command = new StringBuilder(1024); // Assume we have
pretty long command
command.Append("Create Table \"");
// Command string:
command.Append( table.TableName );
// Create Table "TableName" ("ColumnName" ColumnType, ... )
command.Append( "\" (");
foreach ( DataColumn c in table.Columns)
{
// Add all columns
command.Append ("\"");
command.Append (c.ColumnName); // Add column name
command.Append ("\" ");
command.Append (GetSQLTypeName(c.DataType)); // And column SQL type
// Add special column features:
//
// Identity(_seed, _increment) Autoincrement Could not set this as
column will be read only
// Primary Key Primary Key
// Default _value Sets default value
// Unique For unique columns
// Not Null Null is not allowed
if ( c.DefaultValue != DBNull.Value )
{
// Default value is set?
command.Append (String.Format(" Default '{0}'",
c.DefaultValue));
}
if ( !c.AllowDBNull )
{
// Could not be null ?
command.Append (" Not Null"); // Mark it as Not Null
}
if ( c.Unique )
{ // Unique column ?
DataColumn[] pk = table.PrimaryKey; // Get primry key column(s)
if ( pk != null && pk.Length > 1 ) // Only one column
allowed as a primary key in SQL CE
throw new System.Exception("Only one column allowed as a primary key
in SQL CE");
if ( null != pk && pk.Length == 1 && pk[0] == c)
{ // Primary key ?
command.Append (" Primary Key"); // Mark it as such
}
else
{
command.Append (" Unique"); // Mark it as unique
}
}
command.Append (','); // Add separator
}
command.Replace (',', ')', command.Length - 1, 1); // Replace last
comma with ')'
cmd.CommandText = command.ToString(); // Set command
// Console.WriteLine ("Create command: \n{0}", cmd.CommandText);
cmd.ExecuteNonQuery(); // Do it - create a table
cmd.Parameters.Clear(); // Do some cleanup
// At this point we have a database with an empty table ready to be
populated...
SqlCeDataAdapter da = new SqlCeDataAdapter(); // Prepare data
adapter
command.Remove(0, command.Length); // Clean up old
command
command.Append ("Insert Into \""); // Insert command
command.Append ( table.TableName );
command.Append ("\" (");
foreach (DataColumn column in table.Columns)
{ // Add column names
command.Append ('"');
command.Append ( column.ColumnName );
command.Append ("\","); // Separated by commas
cmd.Parameters.Add("@" + column.ColumnName, // Add parameters to q
query
GetSQLType (column.DataType), column.MaxLength > 0 ? column.MaxLength
: 0, column.ColumnName);
}
command.Remove(command.Length - 1, 1); // Remove last comma
command.Append(") Values (");
for (int i = table.Columns.Count; i-- > 0; ) // Add correct
number
command.Append( "?,"); // of queston marks
command.Replace (',', ')', command.Length - 1, 1); // Replace last
comma with ')'
cmd.CommandText = command.ToString(); // Set insert command
da.InsertCommand = cmd; // Store our insert command
try
{
da.Update(table); // Update table in the SQL CE
}
catch (Exception ex)
{
ex.ToString();
}
cmd.Parameters.Clear(); // Do some cleanup
//Verification
Console.WriteLine("VERIFYING");
#region VERIFICATION
cmd = con.CreateCommand(); // Prepare Create table command
string vcommand = String.Format("select COUNT(*) from
{0}",table.TableName);
cmd.CommandText = vcommand; // Set command
int count = (int)cmd.ExecuteScalar(); // Do it - create a table
if(count != table.Rows.Count)
{
Console.WriteLine("We did not store Expected number of records");
Console.WriteLine("Expected was {0}", table.Rows.Count);
Console.WriteLine("Actual was {0}",count);
throw new Exception("Error in Verification");
}
#endregion
Console.WriteLine("DONE WITH VERIFICATION");
}
con.Close(); // Close connection.
}
problem.
In the supplied code, it creates the table, which appears to work ok. The
create table command is as follows:
"Create Table \"allbills\" (\"SacID\" NVarChar,\"LineNum\"
Int,\"DateTouched\" NVarChar,\"EstabCode\" NVarChar,\"MAWB\"
NVarChar,\"HAWB\" NVarChar,\"ArrivalDate\" NVarChar,\"Flight\"
NVarChar,\"GoodsDescription\" NVarChar,\"AIMSUpgraded\" Bit,\"FSResultCode\"
NVarChar,\"InspectResultCode\" NVarChar,\"SACOfficer\"
NVarChar,\"ReportableDocInd\" Bit,\"CountSACsWithinMAWB\"
Int,\"CountHAWBsWithinMAWB\" Int,\"HVLVCountSACsInMAWB\"
Int,\"HVLVCountHAWBsInMAWB\" Int,\"SID\" NVarChar Not Null Primary
Key,\"FSModified\" NVarChar,\"InspModified\" NVarChar,\"FSResultOriginal\"
NVarChar,\"InspectResultOriginal\" NVarChar,\"MAWBLevel\"
NVarChar,\"FSUploaded\" NVarChar,\"InspUploaded\" NVarChar)"
The code then generates an Insert Command that appears to be correct, and
assigns a set of parameters to this command.
"Insert Into \"allbills\"
(\"SacID\",\"LineNum\",\"DateTouched\",\"EstabCode\",\"MAWB\",\"HAWB\",\"ArrivalDate\",\"Flight\",\"GoodsDescription\",\"AIMSUpgraded\",\"FSResultCode\",\"InspectResultCode\",\"SACOfficer\",\"ReportableDocInd\",\"CountSACsWithinMAWB\",\"CountHAWBsWithinMAWB\",\"HVLVCountSACsInMAWB\",\"HVLVCountHAWBsInMAWB\",\"SID\",\"FSModified\",\"InspModified\",\"FSResultOriginal\",\"InspectResultOriginal\",\"MAWBLevel\",\"FSUploaded\",\"InspUploaded\")
Values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
It then creates a dataadapter and assign this insert command to the
dataadapter, then runs dataadapter.update(table), which generates an
exception with the message "Update". Is the code using the Update method
correctly, any suggestions? Third party tools are not an option in this case.
(see source code below)
public static void storeDataToSQLCE( DataSet data, string dataFile )
{
if (File.Exists(dataFile)) // Get rid of existing database file
File.Delete(dataFile);
SqlCeEngine en = new SqlCeEngine("Data Source = " + dataFile ); //
Create new database engine
en.CreateDatabase(); // Create a new database
SqlCeConnection con = new SqlCeConnection("Data Source = " +
dataFile ); // Create a new connection
con.Open(); // Open this connection
foreach (DataTable table in data.Tables )
{
// Create tables one by one
SqlCeCommand cmd = con.CreateCommand();
// Prepare Create table command
StringBuilder command = new StringBuilder(1024); // Assume we have
pretty long command
command.Append("Create Table \"");
// Command string:
command.Append( table.TableName );
// Create Table "TableName" ("ColumnName" ColumnType, ... )
command.Append( "\" (");
foreach ( DataColumn c in table.Columns)
{
// Add all columns
command.Append ("\"");
command.Append (c.ColumnName); // Add column name
command.Append ("\" ");
command.Append (GetSQLTypeName(c.DataType)); // And column SQL type
// Add special column features:
//
// Identity(_seed, _increment) Autoincrement Could not set this as
column will be read only
// Primary Key Primary Key
// Default _value Sets default value
// Unique For unique columns
// Not Null Null is not allowed
if ( c.DefaultValue != DBNull.Value )
{
// Default value is set?
command.Append (String.Format(" Default '{0}'",
c.DefaultValue));
}
if ( !c.AllowDBNull )
{
// Could not be null ?
command.Append (" Not Null"); // Mark it as Not Null
}
if ( c.Unique )
{ // Unique column ?
DataColumn[] pk = table.PrimaryKey; // Get primry key column(s)
if ( pk != null && pk.Length > 1 ) // Only one column
allowed as a primary key in SQL CE
throw new System.Exception("Only one column allowed as a primary key
in SQL CE");
if ( null != pk && pk.Length == 1 && pk[0] == c)
{ // Primary key ?
command.Append (" Primary Key"); // Mark it as such
}
else
{
command.Append (" Unique"); // Mark it as unique
}
}
command.Append (','); // Add separator
}
command.Replace (',', ')', command.Length - 1, 1); // Replace last
comma with ')'
cmd.CommandText = command.ToString(); // Set command
// Console.WriteLine ("Create command: \n{0}", cmd.CommandText);
cmd.ExecuteNonQuery(); // Do it - create a table
cmd.Parameters.Clear(); // Do some cleanup
// At this point we have a database with an empty table ready to be
populated...
SqlCeDataAdapter da = new SqlCeDataAdapter(); // Prepare data
adapter
command.Remove(0, command.Length); // Clean up old
command
command.Append ("Insert Into \""); // Insert command
command.Append ( table.TableName );
command.Append ("\" (");
foreach (DataColumn column in table.Columns)
{ // Add column names
command.Append ('"');
command.Append ( column.ColumnName );
command.Append ("\","); // Separated by commas
cmd.Parameters.Add("@" + column.ColumnName, // Add parameters to q
query
GetSQLType (column.DataType), column.MaxLength > 0 ? column.MaxLength
: 0, column.ColumnName);
}
command.Remove(command.Length - 1, 1); // Remove last comma
command.Append(") Values (");
for (int i = table.Columns.Count; i-- > 0; ) // Add correct
number
command.Append( "?,"); // of queston marks
command.Replace (',', ')', command.Length - 1, 1); // Replace last
comma with ')'
cmd.CommandText = command.ToString(); // Set insert command
da.InsertCommand = cmd; // Store our insert command
try
{
da.Update(table); // Update table in the SQL CE
}
catch (Exception ex)
{
ex.ToString();
}
cmd.Parameters.Clear(); // Do some cleanup
//Verification
Console.WriteLine("VERIFYING");
#region VERIFICATION
cmd = con.CreateCommand(); // Prepare Create table command
string vcommand = String.Format("select COUNT(*) from
{0}",table.TableName);
cmd.CommandText = vcommand; // Set command
int count = (int)cmd.ExecuteScalar(); // Do it - create a table
if(count != table.Rows.Count)
{
Console.WriteLine("We did not store Expected number of records");
Console.WriteLine("Expected was {0}", table.Rows.Count);
Console.WriteLine("Actual was {0}",count);
throw new Exception("Error in Verification");
}
#endregion
Console.WriteLine("DONE WITH VERIFICATION");
}
con.Close(); // Close connection.
}