mysqlDataReader轉換為DataTable類型,mysql資料庫的存取方法

 後記：簡便的方法是

Dim data As DataTable = New DataTable<br /> data.Load(DataReader)

 

在使用Mysql的MysqlHelper的時候由於每次串連只能使用一個MySqlDataReader，所以在需要使用多個資料表的時候沒有辦法，所以本人自己寫了個轉換的函數

 

 

Sub test()<br /> Dim connstr As String<br /> connstr = "server=localhost;user=root;database=schoolnet;port=3306;password=zc_kf"<br /> Dim myconn As MySqlConnection = New MySqlConnection<br /> myconn.ConnectionString = connstr<br /> myconn.Open()<br /> Dim sql As String</p><p> Dim out As StringBuilder = New StringBuilder<br /> sql = "select uid,username from b_member"<br /> Dim DataReader As MySqlDataReader<br /> DataReader = MySqlHelper.ExecuteReader(myconn, sql)<br /> Dim data As DataTable = DataReader2DataTalble(DataReader)</p><p> DataReader.Close()<br /> myconn.Close()</p><p> Dim i, j As Int16<br /> For i = 0 To data.Rows.Count - 1<br /> For j = 0 To data.Columns.Count - 1<br /> Response.Write(data.Rows(i).Item(j))</p><p> Next<br /> Response.Write("<br>")<br /> Next<br /> i = i + 1</p><p> End Sub<br /> Function DataReader2DataTalble(ByVal DataReader As MySqlDataReader) As DataTable<br /> Dim data As DataTable = New DataTable<br /> Dim TableSchema As DataTable = DataReader.GetSchemaTable()<br /> Dim i, j As Int16<br /> Dim FieldName As String<br /> For i = 0 To DataReader.FieldCount - 1</p><p> FieldName = DataReader.GetName(i)<br /> data.Columns.Add(FieldName, DataReader.GetFieldType(FieldName))<br /> Next</p><p> Dim NewRow As DataRow = Nothing</p><p> Do While DataReader.Read<br /> NewRow = data.NewRow()<br /> For j = 0 To DataReader.FieldCount - 1</p><p> NewRow.Item(j) = DataReader(j)</p><p> Next<br /> data.Rows.Add(NewRow)<br /> i = i + 1</p><p> Loop<br /> Return data<br /> End Function