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))
                        using (DbCommand deleteCommand = connection.CreateCommand())
                            deleteCommand.CommandText = "TRUNCATE TABLE dbo.StockQuotes";
                        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";

    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"
    	<!-- 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" />
    	<!-- 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" />
    Can somebody tell me how to do that and maybe I forgot about something or maybe it's impossible.

    Monday, February 1, 2010 4:55 PM


  • 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