entity framework(EF) 直接执行数据库命令并返回DataTable 参数化sql语句

entity framework(EF) 直接执行数据库命令

//调用方法
        private void button1_Click(object sender, EventArgs e)
        {
            string id = "1";
            string sql = "select * from table1 where id = @id";

            #region mysql数据库参数类型
            var args = new DbParameter[] {
                                      new SqlParameter { ParameterName = "id", Value = id},
                                  };
            #endregion

            #region mysql数据库参数类型
            //var args = new DbParameter[] {
            //                          new OdbcParameter { ParameterName = "id", Value = id},
            //                      };

       //     var args = new DbParameter[] {
       //                               new MySqlParameter { ParameterName = "id", Value = "1"},
       //                 };
            #endregion

            #region access数据库参数类型
            //var args = new DbParameter[] {
            //                          new OleDbParameter { ParameterName = "id", Value = id},
            //                      };
            #endregion
            MessageBox.Show(SqlQuery<h_job>(sql, args).Count().ToString());
        }
        /// <summary>
        /// 执行带参数sql语句返回数据列表
        /// </summary>
        /// <typeparam name="T">泛型</typeparam>
        /// <param name="sql">sql语句</param>
        /// <param name="args">参数</param>
        /// <returns>数据列表</returns>
        public List<T> SqlQuery<T>(string sql, DbParameter[] args)
        {
            if (string.IsNullOrEmpty(sql))
                return new List<T>();
            try
            {
                using (itlogo_integralEntities db = new itlogo_integralEntities())
                {
                    return db.Database.SqlQuery<T>(sql, args).ToList();
                }
            }
            catch (Exception ex)
            {
                return new List<T>();
            }
        }
        /// <summary>
        /// 执行不带参数sql语句返回数据列表
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static List<T> SqlQuery<T>(string sql)
        {
            if (string.IsNullOrEmpty(sql))
                return new List<T>();
            try
            {
                using (itlogo_integralEntities db = new itlogo_integralEntities())
                {
                    return db.Database.SqlQuery<T>(sql).ToList();
                }
            }
            catch (Exception ex)
            {
                return new List<T>();
            }
        }
        /// <summary>
        /// 执行带参数sql语句返回受影响行数
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="args"></param>
        /// <returns></returns>
        public static int ExecuteSqlCommand(string sql, DbParameter[] args)
        {
            if (string.IsNullOrEmpty(sql))
                return 0;
            try
            {
                using (itlogo_integralEntities db = new itlogo_integralEntities())
                {
                    return db.Database.ExecuteSqlCommand(sql, args);
                }
            }
            catch (Exception ex)
            {
                return 0;
            }
        }
        /// <summary>
        /// 执行sql语句返回收影响行数
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static int ExecuteSqlCommand(string sql)
        {
            if (string.IsNullOrEmpty(sql))
                return 0;
            try
            {
                using (itlogo_integralEntities db = new itlogo_integralEntities())
                {
                    return db.Database.ExecuteSqlCommand(sql);
                }
            }
            catch (Exception ex)
            {
                return 0;
            }
        }

        

执行sql语句返回datatable

#region 执行sql语句返回datatable

        public DataTable SqlQueryForDataTatable(Database database, string sql)
        {

            SqlConnection conn = new System.Data.SqlClient.SqlConnection();
            conn.ConnectionString = database.Connection.ConnectionString;
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }
            
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandText = sql;

            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataTable table = new DataTable();
            adapter.Fill(table);

            conn.Close();//连接需要关闭
            conn.Dispose();
            return table;
        }

        public DataTable SqlQueryForDataTatable(Database database, string sql, DbParameter[] parameters)
        {
            SqlConnection conn = new System.Data.SqlClient.SqlConnection();
            conn.ConnectionString = database.Connection.ConnectionString;
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandText = sql;

            if (parameters != null && parameters.Length > 0)
            {
                foreach (var item in parameters)
                {
                    cmd.Parameters.Add(item);
                }
            }

            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataTable table = new DataTable();
            adapter.Fill(table);
            return table;
        }
        #endregion

参考网址：

直接执行数据库命令

http://www.cnblogs.com/mend/archive/2012/06/11/2544599.html

使用dynamic linq 解决自定义查询的若干弊端

http://www.cnblogs.com/sword-successful/p/3318540.html

MVC3+EF4.1学习系列(十一)----EF4.1常见的问题解决（返回datatable）

http://www.cnblogs.com/wlflovenet/archive/2011/12/30/EF11.html