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(); }