How to convert ASP.NET Core code to access SQL Server to work with local SQLite DB?

Question

I've been developing my web application to work with a SQL Server database hosted on Azure. To interface with it, I've been using code similar to the following:

SqlConnection scn = new SqlConnection(GetConnectionString());

SqlCommand cmd = new SqlCommand("Select page_name from Pages where page_name = @page_name", scn);
cmd.Parameters.AddWithValue("@page_name", pageName);

try
{
    scn.Open();
    string ret = Convert.ToString(cmd.ExecuteScalar());
    scn.Close();

    return ret;
}
catch (Exception er)
{
    throw er;
}
finally
{
    if (scn != null)
    {
        if (scn.State != System.Data.ConnectionState.Closed)
        {
            scn.Close();
        }
        scn.Dispose();
    }
}

Now that I've realized that I can use a local SQLite database, I'm trying to switch over to that, since there is no reason for this application to need a DB server. I've put in this new connection string:

Data Source=test2.db;

But I get an error:

An unhandled exception occurred while processing the request. ExtendedSocketException: No such device or address

System.Net.Dns.InternalGetHostByName(string hostName) SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 35 - An internal exception was caught)

It looks like it's still trying to connect to a SQL Server. How can I make it use the local SQLite database that I put in the connection string?

SqlConnection/SqlCommand are objects specifically for connecting to an SQL Server database ref. You need to google using SQLite in asp.net. — Dale K
– Dale K, Commented Jul 16, 2019 at 22:05

Merlin04 · Accepted Answer · 2019-07-16 23:05:07Z

4

I figured it out, it was way simpler than I thought it would be.

Install the NuGet package System.Data.SQLite.
Replace all of the instances of SqlConnection, SqlCommand, SqlDataAdapter, etc. with their SQLite equivalents (eg. SQLiteConnection, SQLiteCommand, SQLiteDataAdapter).
In Startup.cs, change options.UseSqlServer to options.UseSqlite.

answered Jul 16, 2019 at 23:05

Merlin04

2,4275 gold badges25 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to convert ASP.NET Core code to access SQL Server to work with local SQLite DB?

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