读取iis日志到sql server

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
    }

}

最终效果：