C# 连接两个sqlite数据库，并将查询到的数据通过相同的内容合并

[qq_22103917]

我有两个sqlite数据库文件，需要通过button控件和textbox控件打开这两个数据库文件，然后通过查询查询语句下哈到结果，再通过某一个字段将相同的内容行全部显示

[娃都会打酱油了]

分别查到两个集合，然后在程序里面通过Linq的Union进行合并

[秋的红果实]

select a.id,a.field1,a.field2...,b.field1,b.field2... from db1.dbo.table1 a,db2.dbo.table2 b where a.id=b.id 若你的两个库不在一个服务器下，则要建立“链接服务器”，然后和上面一样

[孤独de猫]

//SQLiteHelper.cs
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.Text;
using TRACE = System.Diagnostics.Debug;
using System.Data;
using System.Data.SQLite;

namespace SQLiteDemo
{
    public class SQLiteHelper
    {
        const string _className = "SQLiteDemo.SQLiteHelper";

        const string _DATA_PATH = "data source=.\\Data\\users.db";
        const string _DB_DEPARTMENT_NAME = ".\\Data\\department.db";

        static SQLiteConnection s_sqliteConnection;

        #region << 私有 >>
        static void attach(String filepath)
        {
            string strSQL = "ATTACH '" + filepath + "' AS " + System.IO.Path.GetFileNameWithoutExtension(filepath);

            using (SQLiteCommand cmd = new SQLiteCommand(strSQL, s_sqliteConnection))
            {
                cmd.ExecuteNonQuery();
            }
        }
        #endregion

        #region << 公开 >>

        public static bool open()
        {
            s_sqliteConnection = new SQLiteConnection(_DATA_PATH);
            s_sqliteConnection.Open();
            attach(_DB_DEPARTMENT_NAME);
            return true;
        }

        public static bool close()
        {
            s_sqliteConnection.Close();
            return false;
        }

        public static DataTable openSQL(string strSQL)
        {
            DataTable tableResult = new DataTable();
            using (SQLiteCommand cmd = new SQLiteCommand(strSQL, s_sqliteConnection))
            {
                SQLiteDataReader reader = cmd.ExecuteReader();
                tableResult.Load(reader);
            }

            return tableResult;
        }
        #endregion
    }
}

***************************测试

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace SQLiteDemo
{
    public partial class frmMain : Form
    {
        public frmMain()
        {
            InitializeComponent();

            SQLiteHelper.open();


            string strSQL = @"
SELECT 
  * 
FROM 
  usersinfo ui LEFT JOIN 
  department.dep d ON ui.[id] = d.[userId] ;
";

            DataTable table = SQLiteHelper.openSQL(strSQL);

            foreach (DataRow row in table.Rows)
            {
                Console.WriteLine("userName:{0}, department:{1}", row["userName"], row["depName"]);
            }
        }

        protected override void OnClosing(CancelEventArgs e)
        {
            SQLiteHelper.close();

            base.OnClosing(e);
        }
    }
}

[孤独de猫]

表名： userInfo id，int ，自增 userName ，text（255），用户姓名 表名：department id，int，自增 userId，int，关联userInfo的id depName，text（255），部门名称