public static DataTable ExecuteDataTable(this QueryReader queryReader)
{
if (queryReader == null)
{
throw new ArgumentNullException(nameof(queryReader));
}
var dataTable = queryReader.CreateSchemaTable();
var values = new object[queryReader.ColumnCount];
while (queryReader.Read())
{
for (var i = 0; i < queryReader.ColumnCount; i++)
{
values[i] = queryReader[i];
}
dataTable.Rows.Add(values);
}
return(dataTable);
}
private void UserUpdate()
{
string connectionString = "SERVER=" + DBConnect.SERVER + ";" +
"DATABASE=" + DBConnect.DATABASE_NAME + ";" + "UID=" +
DBConnect.USER_NAME + ";" + "PASSWORD="******";" + "SslMode=" +
DBConnect.SslMode + ";";
try
{
//This is an Update query which which will replace the text from the window form to the database
string UserUpdateQuery = "update isad157_SSyantanTamang.users set UserID='" + this.textUserID.Text + "' ,FirstName = '" + this.textFirstName.Text +
"',LastName='" + this.textLastName.Text + "',Gender='" + this.textGender.Text +
"',Hometown='" + this.textHometown.Text + "',City='" + this.textCity.Text + "' where UserID='" + this.textUserID.Text + "';";
//Creates a connection between our Windows form and our Database
MySqlConnection MyConnection = new MySqlConnection(connectionString);
MySqlCommand sqlcmd = new MySqlCommand(UserUpdateQuery, MyConnection);
MySqlDataReader QueryReader;
//Opens the connection to our database
MyConnection.Open();
QueryReader = sqlcmd.ExecuteReader();
//Displays a message informing the User that the details were successfully changed
MessageBox.Show("Details have now been updated, please refresh!");
while (QueryReader.Read())
{
}
//Close Database Connection
MyConnection.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void CreateNewUser()
{
string connectionString = "SERVER=" + DBConnect.SERVER + ";" +
"DATABASE=" + DBConnect.DATABASE_NAME + ";" + "UID=" +
DBConnect.USER_NAME + ";" + "PASSWORD="******";" + "SslMode=" +
DBConnect.SslMode + ";";
try
{
//This is going to create a new user into the database
string CreateUserQuery = "insert isad157_SSyantanTamang.users set FirstName = '" + this.textFirstName.Text +
"',LastName='" + this.textLastName.Text + "',Gender='" + this.textGender.Text +
"',Hometown='" + this.textHometown.Text + "',City='" + this.textCity.Text + "';";
//Creates a connection between our Windows form and our Database
MySqlConnection MyDBConnection = new MySqlConnection(connectionString);
MySqlCommand sqlcmd = new MySqlCommand(CreateUserQuery, MyDBConnection);
MySqlDataReader QueryReader;
//Opens the connection to our database
MyDBConnection.Open();
QueryReader = sqlcmd.ExecuteReader();
//Displays a message informing the User that the details were successfully changed
MessageBox.Show("A new User has now been added, please refresh!");
while (QueryReader.Read())
{
}
//Close Database Connection
MyDBConnection.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
/// <summary>
/// Advances the <see cref="T:System.Data.IDataReader" /> to the next record.
/// </summary>
/// <returns>
/// <see langword="true" /> if there are more rows; otherwise, <see langword="false" />.
/// </returns>
public bool Read()
{
EnsureNotDisposed();
return(_queryReader.Read());
}
public static void Main(string[] args)
{
//string connectionString = "Data Source=" + AppDomain.CurrentDomain.BaseDirectory + "\\DataSource\\" + ConfigHelper.GetConnectionString("SQLiteConnection");
string connectionString = ConfigHelper.GetConnectionString("SqlConnection");
#region 框架测试
///此部分最好在全局中注册，或者在IOC注册中注册
ProviderFactory.RegisterProviderFactory(connectionString, DateProvider.SqlServer);
DbContext context = new DbContext(connectionString);
//强类型
Repository <tb_User> repository = context.CreateRepository <tb_User>();
//无类型
var nonrepository = context.CreateNonRepository();
///以下方法都是支持无类型的
//列表获取
repository.GetList(e => e.name == "cxb" || e.mobile == "15989027156");
nonrepository.GetList <tb_User>(e => e.name == "cxb" || e.mobile == "15989027156");
//新增
var inum = repository.Insert(new tb_User()
{
mobile = "15989027256", name = "bosco", password = "******", sex = 1, roleid = 1
});
//更新
int unum = repository.Update(new tb_User {
mobile = "15989027255", name = "bosco", password = "******", sex = 1, isDelete = true
}, e => e.name == "cxb");
//部分更新
repository.UpdatePart(e => new tb_User()
{
name = "123", mobile = "15989027156"
}, a => a.name == "cxb");
//删除
int dnum = repository.Detele(e => e.name == "b");
//分页查找
int pageindex = 1;
int pagesize = 100;
var nonpagelist = nonrepository.GetPageList <UserInfo>(e => true, pageindex, pagesize);
var pagelist = repository.GetPageList(e => true, pageindex, pagesize);
//支持事务
context.CreateTransaction();
repository.Insert(new tb_User()
{
mobile = "15989027256", name = "bosco", password = "******", sex = 1, roleid = 1
});
repository.Update(new tb_User {
mobile = "15989027255"
}, e => e.name == "cxb");
context.CommitTransaction();
#endregion
#region 支持根和聚合方式
nonrepository.Insert <tb_Role>(new tb_Role {
rid = 1, rname = "cxb", risDelete = 2
});
tb_Role roles = new tb_Role();
repository.Query <tb_Role, tb_UserX, tb_Role>("select * from tb_User u left join tb_Role r on u.roleid=r.rid", (tb_Role, tb_UserX) =>
{
if (tb_Role.rid != roles.rid)
{
roles = tb_Role;
}
roles.UserList.Add(tb_UserX);
return(roles);
});
#endregion
#region 支持多结果集
QueryReader reader = repository.QueryMultiple("select * from tb_User;select * from tb_Role");
List <tb_User> ReadUserList = reader.ReadList <tb_User>().ToList();
List <tb_Role> ReadRoleList = reader.ReadList <tb_Role>().ToList();
#endregion
#region 支持多结果
QueryReader readerOne = repository.QueryMultiple("select * from tb_User; select* from tb_Role");
var nonUser = readerOne.Read <tb_User>();
var role = readerOne.Read <tb_Role>();
#endregion
#region 查询对象方法
//QueryObjectRepository<tb_User> query = new QueryObjectRepository<tb_User>(connectionString);
//var userq = new tb_User() { mobile = "15989027256", name = "bosco", password = "******", sex = 1 };
//query.Insert(userq);
//userq = query.Get(new Condition() { field = "mobile", operarorsign = "=", value = "15989027256" });
//Console.WriteLine(userq.mobile);
//List<Condition> conditions = new List<Condition>();
//conditions.Add(new Condition() { field = "mobile", operarorsign = "=", value = "15989027256" });
//userq = query.Get(conditions);
//Console.WriteLine(userq.mobile);
#endregion
string sqlRecord = "select * from tb_User";
#region SimpleOrm性能测试
var watcher = new Stopwatch();
watcher.Start();
for (int i = 0; i < 100; i++)
{
repository.Query <tb_User>(sqlRecord, null).FirstOrDefault();
}
watcher.Stop();
var duration = watcher.Elapsed.TotalMilliseconds;
Console.WriteLine("SimpleOrm的效率：" + duration);
#endregion
#region Dapper性能测试
var watcher0 = new Stopwatch();
watcher0.Start();
IDbConnection conn = new SqlConnection(connectionString);
for (int i = 0; i < 100; i++)
{
conn.Query <tb_User>(sqlRecord, null).ToList().FirstOrDefault();
}
watcher0.Stop();
var duration0 = watcher0.Elapsed.TotalMilliseconds;
Console.WriteLine("Dapper的效率：" + duration0);
#endregion
#region 反射性能测试
var watcher1 = new Stopwatch();
watcher1.Start();
for (int i = 0; i < 100; i++)
{
repository.QueryReflect <tb_User>(sqlRecord, null).FirstOrDefault();
}
watcher1.Stop();
var duration1 = watcher1.Elapsed.TotalMilliseconds;
Console.WriteLine(".net反射的效率：" + duration1);
#endregion
Console.Read();
}
