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