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

来源：互联网发布：q版人物绘画软件编辑：程序博客网时间：2024/05/16 04:42

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

0 0