CODEWITHSUNDEEP
c#database
Danaldo
Danaldo

Reputation: 122

creating a local database in a C#

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:

  1. Solution explorer > Add new file > Local Database (.sdf file)
  2. Database Explorer > Tables > Create Table
  3. I have then added column names for all the fields setting one as my primary key
  4. 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...

Upvotes: 0

Views: 1158

Answers (1)

Danaldo
Danaldo

Reputation: 122

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);
        }
    }
}

Upvotes: 1

Related Questions