locked
Can't create sql ce database RRS feed

  • Question

  • Hello,

    I have a database mssql 2008 - database.mdf. I want to delete it and create a database sql ce(because I can't do setup project). But I don't know how to change a code in c# when a create a sql ce database. This is my code for .mdf for example:

    {
    const string ConnectionString = @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database1.mdf;Integrated Security=True;User Instance=True";
                string applicationDirectoryPath = Path.GetDirectoryName(System.Reflection.Assembly.GetEntryAssembly().Location);
    
                string formatFilePath = Path.Combine(applicationDirectoryPath, @"DataFormat.xml");
                string dataDirectoryPath = Path.Combine(applicationDirectoryPath, @"Data");
                using (DbConnection connection = new SqlConnection(ConnectionString))
                
                {
                    connection.Open();
                    try
                    {
                        using (DbCommand deleteCommand = connection.CreateCommand())
                        {
                            deleteCommand.CommandText = "TRUNCATE TABLE dbo.StockQuotes";
                            deleteCommand.ExecuteNonQuery();
                        }
                        using (DbCommand insertCommand = connection.CreateCommand())
                        {
                            foreach (string filePath in Directory.GetFiles(dataDirectoryPath, "*.txt"))
                            {
                                insertCommand.CommandText = @"
                                     INSERT INTO dbo.StockQuotes(
                                     Ticker, Date, [Open], High,
                                     Low, [Close], Volume
                                     )
                                     SELECT q.Ticker, q.Date, q.[Open], q.High,
                                     q.Low, q.[Close], CAST(ROUND(q.Volume, 0) AS INT)
                                     FROM OPENROWSET (
                                     BULK '" + filePath + @"',
                                     FORMATFILE = '" + formatFilePath + @"',
                                     FIRSTROW = 2
                                     )
                                     AS q";
                                insertCommand.ExecuteNonQuery();
    
                            }
                        }
    
                    }
                    catch
                    {
    
                        throw;
                    }
                }
            }

    I don't know what's the equivalent of "OPENROWSET" and "BULK"  and other in sql ce.
    I also have a file wich maps(DataFormat.xml) and I don't know how to change it:

    <?xml version="1.0"?>
    <BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <RECORD>
    	<!-- Określ pola w pliku wejściowym. -->
    	<FIELD ID="Ticker" xsi:type="CharTerm" TERMINATOR="," />
    	<FIELD ID="Date" xsi:type="CharTerm" TERMINATOR="," />
    	<FIELD ID="Open" xsi:type="CharTerm" TERMINATOR="," />
    	<FIELD ID="High" xsi:type="CharTerm" TERMINATOR="," />
    	<FIELD ID="Low" xsi:type="CharTerm" TERMINATOR="," />
    	<FIELD ID="Close" xsi:type="CharTerm" TERMINATOR="," />
    	<FIELD ID="Volume" xsi:type="CharTerm" TERMINATOR="\r\n" />
    </RECORD>
    <ROW>
    	<!-- Określ mapowanie pól pliku wejściowego na kolumny. -->
    	<COLUMN NAME="Ticker" SOURCE="Ticker" xsi:type="SQLVARYCHAR" LENGTH="20" />
    	<COLUMN NAME="Date" SOURCE="Date" xsi:type="SQLDATETIME" />
    	<COLUMN NAME="Open" SOURCE="Open" xsi:type="SQLMONEY4" />
    	<COLUMN NAME="High" SOURCE="High" xsi:type="SQLMONEY4" />
    	<COLUMN NAME="Low" SOURCE="Low" xsi:type="SQLMONEY4" />
    	<COLUMN NAME="Close" SOURCE="Close" xsi:type="SQLMONEY4" />
    	<COLUMN NAME="Volume" SOURCE="Volume" xsi:type="SQLDECIMAL" />
    </ROW>
    </BCPFORMAT>
    Can somebody tell me how to do that and maybe I forgot about something or maybe it's impossible.

    Regards
    Monday, February 1, 2010 4:55 PM

Answers

  • There is no OPENROWSET in SQL Compact - I have made some sample code that mimics the SqlBulkCopy api, maybe you can use this to load data from a DataTable or DataReader object: http://sqlcebulkcopy.codeplex.com/
    http://erikej.blogspot.com Erik Ejlskov Jensen - Please mark as answer, if this was it.
    Monday, February 1, 2010 5:56 PM