creating a local database in a C#

Question

I am writing a C# application

It is an offline test application

It imports a large data file in the form of a .csv file

This file is chosen by the user using a form

I then want to store the information contained in this .csv file in the form of a local database such that I can perform sql queries

I am using Visual Studio 2012

I have never setup an sql database before and only have limited experience using sql on existing databases

My attempt so far is:

Solution explorer > Add new file > Local Database (.sdf file)
Database Explorer > Tables > Create Table
I have then added column names for all the fields setting one as my primary key
I have attempted to add a single dataset to my data table with no luck

string dbfile = new System.IO.FileInfo(System.Reflection.Assembly.GetExecutingAssembly().Location).DirectoryName + "\\MyDatabase.sdf"; SqlCeConnection sqlConnection = new SqlCeConnection("datasource=" + dbfile);

SqlCeDataAdapter sqlAdapter = new SqlCeDataAdapter("select * from MyTable", sqlConnection);
AMCCoreSignalsDBDataSet sqlData = new AMCCoreSignalsDBDataSet();
sqlAdapter.Fill(sqlData);
string strCSVDataLine = "1,2,3,four"
sqlData.Tables[0].Rows.Add(new object[] { strCSVDataLine });
sqlAdapter.Update(sqlData);

sqlConnection.Close();

This code fails to work

How can I use C# to populate my database with the .csv data?

Is my method incorrect/incomplete?

Is there a better way to do this?

The reason I would like to use sql is because there is a lot of data. I could create a class structure to contain the data however it would also mean creating many different filter functions. Which SQL already contains...

do you get an error or does it just not give you what you expect? — ZoomVirus
– ZoomVirus, Commented Dec 22, 2014 at 11:40
To find error chuck in a try catch catch exception then output the caught info please then post it here — ZoomVirus
– ZoomVirus, Commented Dec 22, 2014 at 11:47
Update requires a valid InsertCommand when passed DataRow collection with new rows — Danaldo
– Danaldo, Commented Dec 22, 2014 at 11:53
OK now check the data inside the variable sqlData is what you expect — ZoomVirus
– ZoomVirus, Commented Dec 22, 2014 at 11:55

Danaldo · Accepted Answer · 2014-12-23 11:08:24Z

Problems were due to blank values occurring in the .csv file

This was my fix

public void Import(string csvfname)
{
    string password;
    string cacheDatabase;
    string connectionString;
    System.IO.StreamReader objFile;
    string strCommand;
    string lineHeader;
    string line;
    string[] arrLineData;

    cacheDatabase = new System.IO.FileInfo(System.Reflection.Assembly.GetExecutingAssembly().Location).DirectoryName + "\\MyDatabase.sdf"; ;
    password = "";
    connectionString = string.Format("DataSource=\"{0}\"; Password='{1}'", this.cacheDatabase, this.password);

    objFile = new System.IO.StreamReader(csvfname);

    lineHeader = objFile.ReadLine();

    while (!objFile.EndOfStream)
    {
        line = objFile.ReadLine();
        arrLineData = line.Split(',');
        try
        {
            sqlConnection = new SqlCeConnection(connectionString());
            strCommand = "INSERT INTO MyTable VALUES ('" + arrLineData[0] + "', '" + arrLineData[1] + "', '" + arrLineData[2] + "')";
            SqlCeCommand sqlCommand = new SqlCeCommand(strCommand, sqlConnection);
            sqlCommand.ExecuteNonQuery();
            sqlConnection.Close();
        }
        catch (Exception exc)
        {
            MessageBox.Show("Error in Import(): " + exc.Message);
        }
    }
}

Collectives™ on Stack Overflow

creating a local database in a C#

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related