C# sql语句操作：本地数据库上传到网上数据库、网上数据库下载到本地数据库

最新推荐文章于 2023-11-14 17:22:33 发布
马鹤道士
最新推荐文章于 2023-11-14 17:22:33 发布
阅读量294
点赞数
文章标签：数据库 c# sql windows
本文链接：https://blog.csdn.net/weixin_46669020/article/details/130384676
版权
该代码实现了一个功能，用于将SQLite数据库中的数据根据指定条件上传至MySQL数据库，同时提供下载功能，从MySQL回迁到SQLite。主要涉及数据查询、主键识别、字段过滤以及事务处理确保数据一致性。
摘要由CSDN通过智能技术生成
public static bool UploadSql(string tableName, string filterIdName, long filterIdValue)
{
    // 本地数据库
    string dbFileFullName = @"xxxxx.db";
    string sqlConnectionString = @"Data Source=" + dbFileFullName;
    string sqlQuery = "SELECT * FROM " + tableName + "" + " WHERE " + filterIdName + "=" + filterIdValue;

    // 网上数据库
    string mysqlConnectionString = @"xxxxxx";

    // 数据没有提交 重新返回
    using (TransactionScope transactionScope = new TransactionScope())
    {
        try
        {
            using (SQLiteConnection sqlConnection = new SQLiteConnection(sqlConnectionString))
            {
                sqlConnection.Open();

                // 执行查询
                SQLiteCommand sqlCommand = new SQLiteCommand(sqlQuery, sqlConnection);
                using (SQLiteDataReader reader = sqlCommand.ExecuteReader())
                {
                    // 1. 获取主键名称
                    string zhuJianName = ""; // 主键名称
                    bool isAutoIncrement = false;
                    DataTable schemaTable = reader.GetSchemaTable();
                    foreach (DataRow row in schemaTable.Rows)
                    {
                        string columnName = row["ColumnName"].ToString();

                        // 判断当前列是否为主键
                        foreach (DataColumn column in schemaTable.Columns)
                        {
                            if (column.ColumnName == "IsKey" && (bool)row[column])
                            {
                                zhuJianName = columnName;
                            }

                            if (column.ColumnName == "IsAutoIncrement")
                            {
                                isAutoIncrement = (bool)row[column];
                            }
                        }

                        if (zhuJianName != "")
                        {
                            break;
                        }
                    }

                    // 2. 获取字段名称
                    List<string> ziDuanNames = new List<string>(); // 字段名称
                    List<string> ziDuanNameTypes = new List<string>(); // 字段类型
                    string ziDuanNameStr = "";
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        if (zhuJianName.Equals(reader.GetName(i)) && isAutoIncrement) // 跳过主键   主键的值是自动排序的
                        {
                            continue;
                        }

                        ziDuanNames.Add(reader.GetName(i));
                        ziDuanNameStr += reader.GetName(i) + ",";
                        ziDuanNameTypes.Add(reader.GetDataTypeName(i));
                    }

                    ziDuanNameStr = ziDuanNameStr.Trim(',');


                    using (MySqlConnection mysqlConnection = new MySqlConnection(mysqlConnectionString))
                    {
                        // 本地数据库中所有filterIdName  id
                        List<long> filterIdsValue = new List<long>();

                        mysqlConnection.Open();
                        while (reader.Read())
                        {
                            // 3. 删除远程数据库中的所有 filterIdsValue
                            long filterId = Convert.ToInt64(reader[filterIdName]);
                            if (!filterIdsValue.Contains(filterId)) // 过滤去重
                            {
                                filterIdsValue.Add(filterId);

                                // 删除语句
                                string mysqlQuery1 = "DELETE FROM " + tableName + " WHERE " + filterIdName + "=" + filterId;
                                MySqlCommand mysqlCommand1 = new MySqlCommand(mysqlQuery1, mysqlConnection);
                                mysqlCommand1.ExecuteNonQuery();
                            }

                            // 4. 遍历本地所有数据(除了键值)  上传到网上数据库
                            List<string> ziDuanValues = new List<string>();
                            string ziDuanValueStr = "";
                            int index = 0;
                            foreach (var ziDuanName in ziDuanNames)
                            {
                                ziDuanValues.Add(reader[ziDuanName].ToString());
                                if (!ziDuanNameTypes[index].Equals("TEXT") &&!ziDuanNameTypes[index].Equals("TEXT(255)")
                                    && !ziDuanNameTypes[index].Equals("text") && !ziDuanNameTypes[index].Equals("text(255)")) // int 、bigint 
                                {
                                    ziDuanValueStr += ziDuanValues.Last() + ",";
                                }
                                else// TEXT  string
                                {
                                    ziDuanValueStr += "'"+ziDuanValues.Last() + "',";
                                }

                                index++;
                            }

                            ziDuanValueStr = ziDuanValueStr.Trim(',');

                            // 将数据传入到MySQL表中
                            string mysqlQuery = $"INSERT INTO " + tableName + "(" + ziDuanNameStr + ") VALUES ( " + ziDuanValueStr + ")";

                            MySqlCommand mysqlCommand = new MySqlCommand(mysqlQuery, mysqlConnection);
                            mysqlCommand.ExecuteNonQuery();
                        }

                        mysqlConnection.Close();
                        reader.Close();
                    }
                }

                sqlConnection.Close();
            }

            // 成功提交事务
            transactionScope.Complete();
            return true;
        }
        catch (Exception ex)
        {
            return false;
        }
    }
}

public static void DownLoadSql(string tableName, string filterIdName, long filterIdValue)
{
    // 本地数据库
    string dbFileFullName = @"xxxxxx.db";
    string sqlConnectionString = @"Data Source=" + dbFileFullName;

    // 网上数据库
    string mysqlConnectionString = @"xxxxx";
    string mySqlQuery = "SELECT * FROM " + tableName + "" + " WHERE " + filterIdName + "=" + filterIdValue;

    // 数据没有提交 重新返回
    using (TransactionScope transactionScope = new TransactionScope())
    {
        try
        {
            using (MySqlConnection mySqlConnection = new MySqlConnection(mysqlConnectionString))
            {
                mySqlConnection.Open();

                // 执行查询
                MySqlCommand mySqlCommand = new MySqlCommand(mySqlQuery, mySqlConnection);
                using (MySqlDataReader reader = mySqlCommand.ExecuteReader())
                {
                    // 1. 获取主键名称
                    string zhuJianName = ""; // 主键名称
                    DataTable schemaTable = reader.GetSchemaTable();
                    bool isAutoIncrement = false;
                    foreach (DataRow row in schemaTable.Rows)
                    {
                        string columnName = row["ColumnName"].ToString();

                        // 判断当前列是否为主键
                        foreach (DataColumn column in schemaTable.Columns)
                        {
                            if (column.ColumnName == "IsKey" && (bool)row[column])
                            {
                                zhuJianName = columnName;
                            }
                            if (column.ColumnName == "IsAutoIncrement")
                            {
                                isAutoIncrement = (bool)row[column];
                            }
                        }

                        if (zhuJianName != "")
                        {
                            break;
                        }
                    }

                    // 2. 获取字段名称
                    List<string> ziDuanNames = new List<string>(); // 字段名称
                    List<string> ziDuanNameTypes = new List<string>(); // 字段类型
                    string ziDuanNameStr = "";
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        if (zhuJianName.Equals(reader.GetName(i)) && isAutoIncrement) // 跳过主键   主键的值是自动排序的
                        {
                            continue;
                        }

                        ziDuanNames.Add(reader.GetName(i));
                        ziDuanNameStr += reader.GetName(i) + ",";
                        ziDuanNameTypes.Add(reader.GetDataTypeName(i));
                    }

                    ziDuanNameStr = ziDuanNameStr.Trim(',');


                    using (SQLiteConnection sqlConnection = new SQLiteConnection(sqlConnectionString))
                    {
                        // 网上数据库中所有filterIdName  id
                        List<long> filterIdsValue = new List<long>();

                        sqlConnection.Open();
                        while (reader.Read())
                        {
                            // 3. 删除本地数据库中的所有 filterIdsValue
                            long filterId = Convert.ToInt64(reader[filterIdName]);
                            if (!filterIdsValue.Contains(filterId)) // 过滤去重
                            {
                                filterIdsValue.Add(filterId);

                                // 删除语句
                                string sqlQuery1 = "DELETE FROM " + tableName + " WHERE " + filterIdName + "=" + filterId;
                                SQLiteCommand sqlCommand1 = new SQLiteCommand(sqlQuery1, sqlConnection);
                                sqlCommand1.ExecuteNonQuery();
                            }

                            // 4. 遍历网上所有数据(除了键值)  下载到本地数据库
                            List<string> ziDuanValues = new List<string>();
                            string ziDuanValueStr = "";
                            int index = 0;
                            foreach (var ziDuanName in ziDuanNames)
                            {
                                ziDuanValues.Add(reader[ziDuanName].ToString());
                                if (!ziDuanNameTypes[index++].Equals("VARCHAR")) // int 、bigint 
                                {
                                    ziDuanValueStr += ziDuanValues.Last() + ",";
                                }
                                else// TEXT  string
                                {
                                    ziDuanValueStr += "'" + ziDuanValues.Last() + "',";
                                }
                            }

                            ziDuanValueStr = ziDuanValueStr.Trim(',');

                            // 将数据传入到MySQL表中
                            string sqlQuery = $"INSERT INTO " + tableName + "(" + ziDuanNameStr + ") VALUES ( " + ziDuanValueStr + ")";

                            SQLiteCommand sqlCommand = new SQLiteCommand(sqlQuery, sqlConnection);
                            sqlCommand.ExecuteNonQuery();
                        }

                        sqlConnection.Close();
                        reader.Close();
                    }
                }

                mySqlConnection.Close();
            }

            // 成功提交事务
            transactionScope.Complete();
            return true;
        }
        catch (Exception ex)
        {
            //Console.WriteLine($"Transaction rolled back. Reason: {ex.Message}");
            return false;
        }
    }
}