数据库执行代码：
1.查询Table
exec sp_executesql N'P_Sys_Get_User @UserNameLike, @NameLike',N'@UserNameLike varchar(8000),@NameLike varchar(8000)',@UserNameLike='',@NameLike=''
2.删除行数据
exec sp_executesql N'SELECT TOP (1) --因为代码中写了FirstOrDefault
[Extent1].[Id] AS [Id],
[Extent1].[UserName] AS [UserName],
[Extent1].[Name] AS [Name],
[Extent1].[Password] AS [Password],
[Extent1].[Memo] AS [Memo]
FROM [dbo].[T_Sys_User] AS [Extent1]
WHERE [Extent1].[UserName] = @p__linq__0',N'@p__linq__0 varchar(8000)',@p__linq__0='t'
exec sp_executesql N'DELETE [dbo].[T_Sys_User]
WHERE ([Id] = @0)',N'@0 int',@0=8
3.修改行数据
exec sp_executesql N'UPDATE [dbo].[T_Sys_User]
SET [Memo] = @0
WHERE ([Id] = @1)
',N'@0 nvarchar(250),@1 int',@0=N'这里是测试账户001！！',@1=9
4.新增行数据
exec sp_executesql N'INSERT [dbo].[T_Sys_User]([UserName], [Name], [Password], [Memo])
VALUES (@0, @1, @2, @3)
SELECT [Id]
FROM [dbo].[T_Sys_User]
WHERE @@ROWCOUNT > 0 AND [Id] = scope_identity()',N'@0 varchar(50),@1 nvarchar(50),@2 varchar(50),@3 nvarchar(250)',@0='Test_001',@1=N'测试账户',@2='123',@3=N'这里是测试账户001'
1.定义数据库表结构，将列作为User类的属性
User.cs
namespace CY.EMS.Data.Entity
{
/// <summary>
/// 系统登录用户
/// </summary>
public class User : BaseEntity
{
public string UserName { get; set; }
public string Name { get; set; }
public string Password { get; set; }
public string Memo { get; set; }
}
}
2.将User类在数据库中注册
UserMap.cs
using CY.EMS.Data.Entity;
using System;
using System.Collections.Generic;
using System.Data.Entity.ModelConfiguration;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace CY.EMS.Data.Mapping
{
public class UserMap : EntityTypeConfiguration<User>
{
public UserMap()
{
this.ToTable("T_Sys_User"); // 表名
this.HasKey(u => u.Id); // 主键
this.Property(u => u.UserName).IsRequired().HasMaxLength(50).IsUnicode(false); // 非空，最大长度30，VARCHAR
this.Property(u => u.Name).IsRequired().HasMaxLength(50);
this.Property(u => u.Password).IsRequired().HasMaxLength(50).IsUnicode(false);
this.Property(u => u.Memo).HasMaxLength(250);
}
}
}
3.删除表格中的数据
protected void grdView_CustomButtonCallback(object sender, ASPxGridViewCustomButtonCallbackEventArgs e)
{
if (e.ButtonID.Equals("btnDelete"))
{
// 表格“删除”
string UserName, msg = "";
UserName = grdView.GetRowValues(e.VisibleIndex, "UserName").ToString();
using (var context = ContextFactory.CreateContext<User>())
{
var user = context.Set<User>().FirstOrDefault(u => u.UserName == UserName);
context.Set<User>().Remove(user);
int rtn = context.SaveChanges();
if (rtn == 1)
{
msg = "操作成功！";
loadUserInfo();
}
else
msg = "操作失败！";
grdView.JSProperties["cpMsg"] = msg;
}
}
}
4.查询表格中的数据
在B/S中每次执行一个新的操作都需要再创建一个Content！
private void loadUserInfo()
{
using (var context = ContextFactory.CreateContext<User>())
{
context.InputParams["UserNameLike"] = txtQryUserName.Text;
context.InputParams["NameLike"] = txtQryName.Text;
var users = context.SelectBySP<User>();
grdView.DataSource = users;
grdView.DataBind();
}
}
BaseDbCintent.cs
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
using System.Data.Entity.ModelConfiguration;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
namespace CY.EMS.Data
{
/// <summary>
/// Object context
/// </summary>
public class BaseDbContext : DbContext, IDataContext
{
// 数据库连接字符串
public static string ConnectionString = null;
public string _SelectSP = "";
protected Hashtable _InputParams = new Hashtable();
IDictionary IDataContext.InputParams
{ get { return _InputParams; } }
#region Ctor
public BaseDbContext()
: base(ConnectionString)
{
//((IObjectContextAdapter) this).ObjectContext.ContextOptions.LazyLoadingEnabled = true;
}
#endregion
#region NOP Utilities
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
//dynamically load all configuration
//动态加载所有配置
//System.Type configType = typeof(LanguageMap); //any of your configuration classes here
//var typesToRegister = Assembly.GetAssembly(configType).GetTypes()
var typesToRegister = Assembly.GetExecutingAssembly().GetTypes()
.Where(type => !String.IsNullOrEmpty(type.Namespace))
.Where(type => type.BaseType != null && type.BaseType.IsGenericType &&
type.BaseType.GetGenericTypeDefinition() == typeof(EntityTypeConfiguration<>));
foreach (var type in typesToRegister)
{
dynamic configurationInstance = Activator.CreateInstance(type);
modelBuilder.Configurations.Add(configurationInstance);
}
//...or do it manually below. For example,
//modelBuilder.Configurations.Add(new LanguageMap());
base.OnModelCreating(modelBuilder);
}
/// <summary>
/// Attach an entity to the context or return an already attached entity (if it was already attached)
/// </summary>
/// <typeparam name="TEntity">TEntity</typeparam>
/// <param name="entity">Entity</param>
/// <returns>Attached entity</returns>
protected virtual TEntity AttachEntityToContext<TEntity>(TEntity entity) where TEntity : BaseEntity, new()
{
//little hack here until Entity Framework really supports stored procedures
//otherwise, navigation properties of loaded entities are not loaded until an entity is attached to the context
var alreadyAttached = Set<TEntity>().Local.FirstOrDefault(x => x.Id == entity.Id);
if (alreadyAttached == null)
{
//attach new entity
Set<TEntity>().Attach(entity);
return entity;
}
//entity is already loaded
return alreadyAttached;
}
#endregion
#region NOP Methods
/// <summary>
/// Create database script
/// </summary>
/// <returns>SQL to generate database</returns>
public string CreateDatabaseScript()
{
return ((IObjectContextAdapter)this).ObjectContext.CreateDatabaseScript();
}
/// <summary>
/// Get DbSet
/// </summary>
/// <typeparam name="TEntity">Entity type</typeparam>
/// <returns>DbSet</returns>
public new IDbSet<TEntity> Set<TEntity>() where TEntity : BaseEntity
{
return base.Set<TEntity>();
}
/// <summary>
/// Execute stores procedure and load a list of entities at the end
/// 执行存储过程，并返回数据列表
/// </summary>
/// <typeparam name="TEntity">Entity type</typeparam>
/// <param name="commandText">Command text</param>
/// <param name="parameters">Parameters</param>
/// <returns>Entities</returns>
public IList<TEntity> ExecuteStoredProcedureList<TEntity>(string commandText, params object[] parameters) where TEntity : BaseEntity, new()
{
//add parameters to command
if (parameters != null && parameters.Length > 0)
{
for (int i = 0; i <= parameters.Length - 1; i++)
{
var p = parameters[i] as DbParameter;
if (p == null)
throw new Exception("Not support parameter type");
commandText += i == 0 ? " " : ", ";
commandText += "@" + p.ParameterName;
if (p.Direction == ParameterDirection.InputOutput || p.Direction == ParameterDirection.Output)
{
//output parameter
commandText += " output";
}
}
}
var result = this.Database.SqlQuery<TEntity>(commandText, parameters).ToList();
//performance hack applied as described here - http://www.nopcommerce.com/boards/t/25483/fix-very-important-speed-improvement.aspx
bool acd = this.Configuration.AutoDetectChangesEnabled;
try
{
this.Configuration.AutoDetectChangesEnabled = false;
for (int i = 0; i < result.Count; i++)
result[i] = AttachEntityToContext(result[i]);
}
finally
{
this.Configuration.AutoDetectChangesEnabled = acd;
}
return result;
}
/// <summary>
/// Creates a raw SQL query that will return elements of the given generic type. The type can be any type that has properties that match the names of the columns returned from the query, or can be a simple primitive type. The type does not have to be an entity type. The results of this query are never tracked by the context even if the type of object returned is an entity type.
/// </summary>
/// <typeparam name="TElement">The type of object returned by the query.</typeparam>
/// <param name="sql">The SQL query string.</param>
/// <param name="parameters">The parameters to apply to the SQL query string.</param>
/// <returns>Result</returns>
public IEnumerable<TElement> SqlQuery<TElement>(string sql, params object[] parameters)
{
return this.Database.SqlQuery<TElement>(sql, parameters);
}
/// <summary>
/// Executes the given DDL/DML command against the database.
/// 执行sql语句，返回影响行数
/// </summary>
/// <param name="sql">The command string</param>
/// <param name="doNotEnsureTransaction">false - the transaction creation is not ensured; true - the transaction creation is ensured.</param>
/// <param name="timeout">Timeout value, in seconds. A null value indicates that the default value of the underlying provider will be used</param>
/// <param name="parameters">The parameters to apply to the command string.</param>
/// <returns>The result returned by the database after executing the command.</returns>
public int ExecuteSqlCommand(string sql, bool doNotEnsureTransaction = false, int? timeout = null, params object[] parameters)
{
int? previousTimeout = null;
if (timeout.HasValue)
{
//store previous timeout
previousTimeout = ((IObjectContextAdapter)this).ObjectContext.CommandTimeout;
((IObjectContextAdapter)this).ObjectContext.CommandTimeout = timeout;
}
var transactionalBehavior = doNotEnsureTransaction
? TransactionalBehavior.DoNotEnsureTransaction
: TransactionalBehavior.EnsureTransaction;
var result = this.Database.ExecuteSqlCommand(transactionalBehavior, sql, parameters);
if (timeout.HasValue)
{
//Set previous timeout back
((IObjectContextAdapter)this).ObjectContext.CommandTimeout = previousTimeout;
}
//return result
return result;
}
#endregion
#region CY Methods
public IList<TEntity> SelectBySP<TEntity>() where TEntity : BaseEntity, new()
{
List<SqlParameter> prms = new List<SqlParameter>();
foreach (string key in _InputParams.Keys)
prms.Add(newSqlParameter(key, _InputParams[key]));
return this.ExecuteStoredProcedureList<TEntity>(_SelectSP, prms.ToArray());
}
#endregion
#region CY Utilities
private SqlParameter newSqlParameter(string name, object value)
{
if (null == value)
return new SqlParameter(name, "");
if (value is string)
{
SqlParameter p = new SqlParameter(name, SqlDbType.VarChar, (value as string).Length);
p.Value = value;
return p;
}
else if (value is byte[])
{
SqlParameter p = new SqlParameter(name, SqlDbType.Image);
p.Value = value;
return p;
}
return new SqlParameter(name, value);
}
#endregion
}
}