using Dapper;

using System;

using System.Collections.Generic;

using System.Data;

using System.Data.Common;

using System.Data.SQLite;

using System.IO;

using System.Linq;

using System.Text;

​

namespace ConsoleApp13

{

    public class Program

    {

        public static void Main(string[] args)

        {

            string fileName = "test123.db";

            if (!File.Exists(fileName)) SQLiteConnection.CreateFile(fileName);

            SQLiteConnection connection = new SQLiteConnection("Data Source = " + fileName);

            connection.Execute("CREATE TABLE IF NOT EXISTS Dog (ID integer PRIMARY KEY AUTOINCREMENT, Name text NOT NULL)");

            connection.Execute("CREATE TABLE IF NOT EXISTS Person(ID integer PRIMARY KEY AUTOINCREMENT,Name text NOT NULL, CreateTime text NOT NULL, DogID integer NOT NULL,FOREIGN KEY (DogID) REFERENCES Dog(ID))");

            string[] dogNames = Utility.GetRandomName(3, 5, 9);

            var existedDogs = connection.Query<Dog>("SELECT * FROM Dog WHERE Name IN @name", new { name = dogNames });

            var notExistedDogNames = dogNames.Except(existedDogs.Select(x => x.Name));

            var anonymousDogName = notExistedDogNames.Select(x => new { name = x }).ToArray();

            Execute(connection, "INSERT INTO Dog VALUES(null,@name)", anonymousDogName);

            string[] personNames = Utility.GetRandomName(4, 10, 999);

            var existedPersons = connection.Query<Person>("SELECT * FROM Person WHERE Name IN @name", new { name = personNames });

            var notExistedPersonNames = personNames.Except(existedPersons.Select(x => x.Name));

            int[] dogIDs = connection.Query<Dog>("SELECT * FROM Dog").Select(x => x.ID).ToArray().GetRandomElement(notExistedPersonNames.Count());

            var anonymousPersonNameDogId = Utility.Combine(notExistedPersonNames, dogIDs).Select(x => new { name = x.Item1, createTime = DateTime.Now, dogID = x.Item2 }).ToArray();

            Execute(connection, "INSERT INTO Person VALUES(null,@name,@createTime,@dogID)", anonymousPersonNameDogId);

            var result = connection.Query<Person, Dog, Person>("SELECT * FROM Person INNER JOIN Dog ON Person.DogID = Dog.ID", (person, dog) => { person.Dog = dog; return person; });

            int index = 0;

            result.ToList().ForEach(x => 

            {

                index++;

                Console.WriteLine(index+"\tCreate Time:"+x.CreateTime+"\t\tPerson {ID:"+x.ID+"\tName:"+x.Name+"}\tDog {ID:"+x.Dog.ID+"\tName:"+x.Dog.Name+"}");

            });

        }

        static void Execute(IDbConnection connection,string sql,object parameter = null)

        {

            connection.Open();

            IDbTransaction transaction = connection.BeginTransaction();

            try

            {

                connection.Execute(sql, parameter, transaction);

                transaction.Commit();

            }

            catch (DbException)

            {

                transaction.Rollback();

                throw;

            }

            finally

            {

                connection.Close();

            }

        }

    }

​

    public class Dog

    {

        public int ID { set; get; }

        public string Name { set; get; }

    }

​

    public class Person

    {

        public int ID { set; get; }

        public string Name { set; get; }

        public Dog Dog { set; get; }

        public DateTime CreateTime { set; get; }

    }

​

    public static class Utility

    {

        public static string[] GetRandomName(int minLength, int maxLength,int count)

        {

            if (minLength <= 0 || minLength <= 0 || minLength >= maxLength || count <= 0) throw new ArgumentException(); 

            Random random = new Random();

            string[] names = new string[count];

            for (int i = 0; i < names.Length; i++)

            {

                int nameLength = random.Next(minLength, maxLength);

                char[] nameChar = new char[nameLength];

                for (int j = 0; j < nameLength; j++)

                {

                    if (j == 0)

                    {

                        nameChar[j] = (char)random.Next(65, 90);

                    }

                    else

                    {

                        nameChar[j] = (char)random.Next(97, 122);

                    }

                }

                names[i] = new string(nameChar);

            }

            return names;

        }

​

        public static T[] GetRandomElement<T>(this IEnumerable<T> source,int count)

        {

            if (count <= 0) throw new ArgumentException();

            int elementCount = source.Count();

            T[] sourceArray = source.ToArray();

            T[] elements = new T[count];

            Random random = new Random();

            for (int i=0; i < elements.Length; i++)

            {

                elements[i] = sourceArray[random.Next(1, elementCount)];

            }

            return elements;

        }

​

        public static IEnumerable<Tuple<T1,T2>> Combine<T1, T2>(IEnumerable<T1> firstSource, IEnumerable<T2> secondSource)

        {

            T1[] firstArray = firstSource.ToArray();

            T2[] secondArray = secondSource.ToArray();

            if (firstArray.Length != secondArray.Length) throw new ArgumentException();

            List<Tuple<T1, T2>> result = new List<Tuple<T1, T2>>();

            for(int i=0;i<firstArray.Length;i++)

            {

                result.Add(new Tuple<T1, T2>(firstArray[i], secondArray[i]));

            }

            return result;

        }

    }

}

​