将数据从 SQL Server 插入到 Sqlite [英] Inserting Data from SQL Server to Sqlite

问题描述

我想将数据从 SQL Server 移动到 sqlite.我所做的是首先将数据从 SQL Server 移动到 dataset，然后从那里移动到 sqlite 表.

I want to move data from SQL Server to sqlite. What I do is first move the data from SQL Server to a dataset, and then move from there to the sqlite table.

以下是执行此操作的代码.我相信可能有更有效的方法

The following is the code that does that. I believe there may be more efficient way

try
{
    using (SqlConnection sqlConnection = new SqlConnection(DataSources.RemoteConnectionString()))
    {
        sqlConnection.Open();

        using (SqlCommand sqlCommand = new SqlCommand("[DB7934_businessmind].[DownloadAreaOfLaw]", sqlConnection))
        {
            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlCommand.Parameters.AddWithValue("@AoLId", aolid);

            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
            sqlDataAdapter.SelectCommand = sqlCommand;
            sqlDataAdapter.Fill(aolDataSet.TempAoL);

            if (aolDataSet.TempAoL.Rows.Count > 0)
            {
                using (SQLiteConnection sqliteConnection = new SQLiteConnection(DataSources.LocalConnectionString()))
                {
                    sqliteConnection.Open();

                    using (SQLiteCommand sqliteCommand = new SQLiteCommand(sqliteConnection))
                    {
                        foreach (Models.AoLDataSet.TempAoLRow r in aolDataSet.TempAoL.Rows)
                        {
                            sqliteCommand.CommandText = "INSERT INTO AreaOfLaw(AoLId, AreaOfLawTitle) VALUES(@AoLId, @AreaOfLawText)";
                            sqliteCommand.Parameters.AddWithValue("@AoLId", r.AoLId);
                            sqliteCommand.Parameters.AddWithValue("AreaOfLawText", r.AreaOfLawTitle);
                            sqliteCommand.ExecuteNonQuery();
                        }

                        sqliteCommand.Dispose();
                    }

                    sqliteConnection.Close();
                }
            }
        }

        sqlConnection.Close();
    }
}
catch (Exception ex)
{
    MessageBox.Show("An error has occurred while downloading Areas Of Law from the cloud, the original error is: " + ex.Message, "Area Of Law", MessageBoxButtons.OK, MessageBoxIcon.Error);
}

提前致谢

推荐答案

尝试将所有插入内容包装到一个事务中:

Try wrapping all inserts into one transaction:

try
{
    using (SqlConnection sqlConnection = new SqlConnection(DataSources.RemoteConnectionString()))
    {
        sqlConnection.Open();
        using (SqlCommand sqlCommand = new SqlCommand("[DB7934_businessmind].[DownloadAreaOfLaw]", sqlConnection))
        {
            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlCommand.Parameters.AddWithValue("@AoLId", aolid);
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
            sqlDataAdapter.SelectCommand = sqlCommand;
            sqlDataAdapter.Fill(aolDataSet.TempAoL);
            if (aolDataSet.TempAoL.Rows.Count > 0)
            {
                using (SQLiteConnection sqliteConnection = new SQLiteConnection(DataSources.LocalConnectionString()))
                {
                    sqliteConnection.Open();

                    using(SqliteTransaction tr = sqliteConnection.BeginTransaction())
                    {
                        using (SQLiteCommand sqliteCommand = new SQLiteCommand(sqliteConnection))
                        {
                            foreach (Models.AoLDataSet.TempAoLRow r in aolDataSet.TempAoL.Rows)
                            {
                                sqliteCommand.CommandText = "INSERT INTO AreaOfLaw(AoLId, AreaOfLawTitle) VALUES(@AoLId, @AreaOfLawText)";
                                sqliteCommand.Parameters.AddWithValue("@AoLId", r.AoLId);
                                sqliteCommand.Parameters.AddWithValue("AreaOfLawText", r.AreaOfLawTitle);
                                sqliteCommand.ExecuteNonQuery();
                            }
                            sqliteCommand.Dispose();
                        }

                        tr.Commit();
                    }
                    sqliteConnection.Close();
                }
            }
        }
        sqlConnection.Close();
    }
}
catch (Exception ex)
{
    MessageBox.Show("An error has occurred while downloading Areas Of Law from the cloud, the original error is: " + ex.Message, "Area Of Law", MessageBoxButtons.OK, MessageBoxIcon.Error);
}

我在使用 sqlite 时遇到了与插入速度相同的问题，这种技术解决了这个问题 - 插入速度大大提高.如果这个不合适，我不知道是否还有其他选择可以解决这个问题.

I've came across the same issue with insertion speed using sqlite and this technique solved the problem - insertion speed increased tremendously. I don't know if there are any other options available to beat the problem if this one doesn't fit.

这篇关于将数据从 SQL Server 插入到 Sqlite的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！