using Fasterflect;

using System;

using System.Collections.Generic;

using System.Data.SqlClient;

using System.IO;

using System.Linq;

using System.Reflection;

using System.Text;

using System.Threading.Tasks;

namespace ConsoleApplication2

{

    // 记录iis日志到sql server数据库中，并进行查询

    class Program_IISLogFile

    {

        static void Main(string[] args)

        {

            Console.WriteLine("开始行：");

            var startIndex = Console.ReadLine();

            Console.WriteLine("结束行：");

            var endIndex = Console.ReadLine();

            Read("D:\\job\\test\\tu_ex151203.log", int.Parse(startIndex), int.Parse(endIndex));

            Console.WriteLine("over");

            Console.ReadKey();

        }

        // 数据库连接

        const string connectionStr = "user id=sa;password=12345;data source=(local);initial catalog=test";

        // 读取日志文件中的iis操作记录

        public static void Read(string path, int startIndex, int endInex)

        {

            StreamReader sr = new StreamReader(path, Encoding.Default);

            String line;

            using (SqlConnection conn = new SqlConnection(connectionStr))

            {

                using (SqlCommand cmd = new SqlCommand(connectionStr, conn))

                {

                    conn.Open();

                    int rows = 0;

                    int statIndex = 0;

                    string sql = string.Empty;

                    while ((line = sr.ReadLine()) != null)

                    {

                        statIndex++;

                        if (statIndex < 5 + startIndex) continue;

                        if (endInex <= statIndex - 5) break;

                        try

                        {

                            // Console.WriteLine(line.ToString());

                            string[] strs = line.ToString().Split(' ');

                            string sql2 = "";

                            if (strs != null)

                            {

                                for (int i = 0; i < strs.Count(); i++)

                                {

                                    sql2 += "'" + strs[i] + "'";

                                    if ((i + 1) < strs.Count())

                                    {

                                        sql2 += ",";

                                    }

                                }

                            }

                            sql = string.Format(@"insert into [iislog]

                                                                            ([date], [time], [s-ip], [cs-method], [cs-uri-stem], [cs-uri-query], [s-port], [cs-username],

                                                                            [c-ip], [cs(User-Agent)], [cs(Referer)], [sc-status], [sc-substatus], [sc-win32-status], [time-taken])

                                                                   values({0})", sql2);

                            cmd.CommandText = sql;

                            rows += cmd.ExecuteNonQuery();

                        }

                        catch (System.Data.SqlClient.SqlException e)

                        {

                            //conn.Close();

                            Console.WriteLine("插入" + rows.ToString() + "条数据！" + e.Message + " sql:" + sql);

                            //continue;

                        }

                        // Console.WriteLine("插入" + rows.ToString() + "条数据！");

                    }

                }

            }

        }

        #region 数据库相关

        /* 数据库表创建语句

         CREATE TABLE [dbo].[iislog](

	[date] [varchar](50) NULL,

	[time] [varchar](50) NULL,

	[s-ip] [varchar](50) NULL,

	[cs-method] [varchar](5000) NULL,

	[cs-uri-stem] [varchar](5000) NULL,

	[cs-uri-query] [varchar](5000) NULL,

	[s-port] [varchar](5000) NULL,

	[cs-username] [varchar](5000) NULL,

	[c-ip] [varchar](5000) NULL,

	[cs(User-Agent)] [varchar](5000) NULL,

	[cs(Referer)] [varchar](5000) NULL,

	[sc-status] [varchar](5000) NULL,

	[sc-substatus] [varchar](5000) NULL,

	[sc-win32-status] [varchar](5000) NULL,

	[time-taken] [varchar](5000) NULL

)

         */

        /* 查询请求次数较多的url

         select [cs-uri-stem] + '?' + [cs-uri-query] as '链接地址', [sc-status] '状态码', c '请求次数' from

(select [cs-uri-stem], [cs-uri-query], [sc-status], count(1) c from iislog where PATINDEX('%.aspx%', [cs-uri-stem]) <> 0  group by [cs-uri-stem], [cs-uri-query], [sc-status])  tbl

order by  c desc, '链接地址' desc

         */

        #endregion

    }

}