小周的技术博客

困难是培养伟大心志的保姆,唯有这个冷酷的保姆才会不停地推着摇篮,培养一个勇敢、刚健的孩子。 ---------布赖恩特

Linq To SQL 批量插入

本文来自 小周的技术博客 转载请注明;

本文地址:http://blog.lyttensoft.win/?id=58



使用System.Data.Linq.DataContext 的DbCommand 进行拼接Insert语句的形式完成批量插入操作. 


代码如下:

 public static class DataBaseFunction
    {
        /// <summary>
        /// 批量添加 The same column
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="table"></param>
        /// <param name="InsertData"></param>
        /// <returns></returns>
        public static int Insert<T>(T table, List<Dictionary<string, string>> InsertData, ShareMachineDBDataContext dataContext) where T : class
        {
            //获取表名
            string tableName = dataContext.Mapping.GetTable(typeof(T)).TableName;
            DbCommand command = dataContext.GetCommand(dataContext.GetTable<T>());
            Type tableType = typeof(T);
            string sql = string.Empty;
            string values = string.Empty;
            string field = string.Empty;
            List<string> sqlvalue = new List<string>();
            for (int i = 0; i < InsertData.Count(); i++)
            {
                Dictionary<string, string> insert = (Dictionary<string, string>)InsertData[i];
                values = string.Empty;
                foreach (System.Reflection.PropertyInfo ProInfo in tableType.GetProperties())
                {
                    //这里只对绑定列的属性进行赋值
                    if (ProInfo.GetCustomAttributes(true).Where(pre => pre.ToString() == typeof(ColumnAttribute).ToString()).Count() > 0)
                    {
                        if (insert.ContainsKey(ProInfo.Name))
                        {
                            if (i == 0) field += ProInfo.Name + ",";
                            values += checkField(insert[ProInfo.Name].ToString().Trim(), ProInfo.PropertyType.ToString()) + ",";
                        }
                    }
                }
                if (!string.IsNullOrEmpty(values))
                {
                    values = values.Remove(values.Length - 1);
                }
                sqlvalue.Add("(" + values + "),");
            }
            if (!string.IsNullOrEmpty(field))
            {
                field = field.Remove(field.Length - 1);
            }
            for (int i = 0; i < (sqlvalue.Count() / 1000 + ((sqlvalue.Count() % 1000) != 0 ? 1 : 0)); i++)
            {
                sql += " insert into " + tableName + " (" + field + ") values ";
                for (int j = 0; (i * 1000 + j) < sqlvalue.Count() && j < 1000; j++)
                {
                    sql += sqlvalue[i * 1000 + j].ToString().Trim();
                }
                if (!string.IsNullOrEmpty(sql))
                {
                    sql = sql.Remove(sql.Length - 1);
                }
            }
            //SQL命令
            //获取SQL参数数组 (包括查询参数和赋值参数)
            command.CommandText = sql;
            command.CommandTimeout = 1000 * 10;
            //执行 
            try
            {
                if (command.Connection.State != ConnectionState.Open)
                {
                    command.Connection.Open();
                }
                return command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                return -1;
            }
            finally
            {
                command.Connection.Close();
                command.Dispose();
            }
        }

        public static string checkField(string filed, string PropertyType)
        {
            PropertyType = PropertyType.ToLower();
            if (PropertyType.Contains("system.string") || PropertyType.Contains("system.datetime") || PropertyType.Contains("system.guid"))
                return "'" + filed + "'";
            return filed;
        }
    }


本文来自 小周的技术博客 转载请注明;

本文地址:http://blog.lyttensoft.win/?id=58

发表评论:

小周的技术博客