public Form1() { InitializeComponent(); var connection = new oleDB.OleDbConnection("Data Source=\"e:\\vic.mdb\";User ID=Admin;Provider=\"Microsoft.Jet.OLEDB.4.0\";"); connection.Open(); oleDB.OleDbCommand command = new oleDB.OleDbCommand("Select * From [Phones]", connection); oleDB.OleDbDataReader reader = command.ExecuteReader(); DataTable table = new DataTable(); table.Columns.Add(reader.GetName(0)); table.Columns.Add(reader.GetName(1)); table.Columns.Add(reader.GetName(2)); while (reader.Read() == true) { table.Rows.Add(new object[] { reader.GetValue(0), reader.GetValue(1), reader.GetValue(2) }); } reader.Close(); connection.Close(); dataGridView1.DataSource = table; }
public static System.Data.DataTable getTable(string 参数_数据库路径, string 参数_SQL语句) { //初始化 System.Data.DataTable dt = new System.Data.DataTable(); try { //1、建立连接 C#操作Access之读取mdb string strConn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + 参数_数据库路径 + ";"; System.Data.OleDb.OleDbConnection odcConnection = new System.Data.OleDb.OleDbConnection(strConn); //2、打开连接 C#操作Access之读取mdb odcConnection.Open(); //建立SQL查询 System.Data.OleDb.OleDbCommand odCommand = odcConnection.CreateCommand(); //3、输入查询语句 C#操作Access之读取mdb odCommand.CommandText = 参数_SQL语句; //建立读取 System.Data.OleDb.OleDbDataReader odrReader = odCommand.ExecuteReader(); //查询并显示数据 int size = odrReader.FieldCount; for (int i = 0; i < size; i++) { //Console.WriteLine("{0} {1}", "列", odrReader.GetName(i).ToString()); dt.Columns.Add(odrReader.GetName(i).ToString(), typeof(string)); } while (odrReader.Read()) { System.Data.DataRow dr = dt.NewRow(); for (int i = 0; i < size; i++) { //Console.WriteLine("{0} {1}", odrReader.GetName(i).ToString(), odrReader[odrReader.GetName(i)].ToString()); dr[odrReader.GetName(i).ToString()] = odrReader[odrReader.GetName(i)].ToString(); } dt.Rows.Add(dr); } //关闭连接 C#操作Access之读取mdb odrReader.Close(); odcConnection.Close(); return(dt); } catch { return(dt); } }
public static DataTable FunctionOfAdding(String stringsione) { var Database = new dabse.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Медведев.mdb"); Database.Open(); dabse.OleDbCommand loadingOfTable = new dabse.OleDbCommand("Select * From " + stringsione, Database); dabse.OleDbDataReader result = loadingOfTable.ExecuteReader(); DataTable table = new DataTable(); for (int i = 0; i < result.FieldCount; i++) { table.Columns.Add(result.GetName(i)); } while (result.Read() == true) { object[] yacheiki = new object[result.FieldCount]; for (int i = 0; i < result.FieldCount; i++) { yacheiki[i] = result.GetValue(i); } table.Rows.Add(yacheiki); } result.Close(); Database.Close(); return(table); }
private void button1_Click(object sender, EventArgs e) { var Database = new OleDiBi.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Медведев.mdb"); Database.Open(); OleDiBi.OleDbCommand loadingOfTable = new OleDiBi.OleDbCommand(richTextBox1.Text, Database); OleDiBi.OleDbDataReader result = loadingOfTable.ExecuteReader(); DataTable table = new DataTable(); for (int i = 0; i < result.FieldCount; i++) { table.Columns.Add(result.GetName(i)); } while (result.Read() == true) { object[] yacheiki = new object[result.FieldCount]; for (int i = 0; i < result.FieldCount; i++) { yacheiki[i] = result.GetValue(i); } table.Rows.Add(yacheiki); } result.Close(); Database.Close(); dataGridView1.DataSource = table; }
public static IDataType[] GetDataTypes(OleDbDataReader reader) { List<IDataType> types = new List<IDataType>(); for (int i = 0; i < reader.FieldCount; i++) { if (reader.GetFieldType(i) == typeof(string)) { types.Add(new VarChar(reader.GetName(i), 1000)); } else { types.Add(new Number(reader.GetName(i))); } } return types.ToArray(); }
protected internal virtual void CreateDataTableColumns(DataTable dataTable, OleDbDataReader oleDbDataReader) { for (var i = 0; i < oleDbDataReader.FieldCount; i++) { var dataColumn = new DataColumn(oleDbDataReader.GetName(i)); dataTable.Columns.Add(dataColumn); } }
public static string GetDataFieldNames(OleDbDataReader reader) { string dt = ""; for (int i = 0; i < reader.FieldCount; i++) { dt = dt + "'"+reader.GetName(i)+"'"; if (i < reader.FieldCount - 1) { dt = dt + ", "; } } return dt; }
public void CreateColumnHeaders(OleDbDataReader Reader) { Columns.Clear(); ColumnHeader[] ColumnHeaders = new ColumnHeader[Reader.FieldCount]; for (int i = 0; i < ColumnHeaders.Length; i++) { ColumnHeaders[i] = new ColumnHeader(); ColumnHeaders[i].Text = Reader.GetName(i); ColumnHeaders[i].Width = (Width / ColumnHeaders.Length) * 9 / 10; Columns.Add(ColumnHeaders[i]); } }
public static string getStr(string 参数_数据库路径, string 参数_SQL语句) { //初始化 string 返回文本 = ""; try { //1、建立连接 C#操作Access之读取mdb string strConn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + 参数_数据库路径 + ";"; System.Data.OleDb.OleDbConnection odcConnection = new System.Data.OleDb.OleDbConnection(strConn); //2、打开连接 C#操作Access之读取mdb odcConnection.Open(); //建立SQL查询 System.Data.OleDb.OleDbCommand odCommand = odcConnection.CreateCommand(); //3、输入查询语句 C#操作Access之读取mdb odCommand.CommandText = 参数_SQL语句; //建立读取 System.Data.OleDb.OleDbDataReader odrReader = odCommand.ExecuteReader(); //查询并显示数据 int size = odrReader.FieldCount; odrReader.Read(); 返回文本 = odrReader[odrReader.GetName(0)].ToString(); //关闭连接 C#操作Access之读取mdb odrReader.Close(); odcConnection.Close(); return(返回文本); } catch { return(返回文本); } }
private static string InsertRowString(string tableName, Dictionary<string, string> valueDict, OleDbDataReader read) { var sb = new StringBuilder(); //bool firstcol = true; sb.AppendFormat("INSERT INTO [{0}](", tableName); for (int i = 0; i < read.FieldCount; ++i) { if (i != 0) { sb.Append(","); } sb.Append(read.GetName(i)); } sb.Append(") VALUES("); for (int i = 0; i < read.FieldCount; ++i) { sb.Append("@" + read.GetName(i)); if (i != read.FieldCount - 1) { sb.Append(","); } } sb.Append(")"); return sb.ToString(); }
public static string OrgReturnNode(string subNodeName,string sNodeName,OleDbDataReader rst) { XmlDocument doc = XmlFun.CreateNewDoc("<" + subNodeName + "/>"); XmlElement el_row = null; XmlElement elem = null; while (rst.Read()) // 循环获取记录集的值 { el_row = doc.CreateElement(sNodeName); for (int i = 0; i < rst.FieldCount; i++) { string strFieldName = rst.GetName(i).ToUpper(); string sValue = rst[i].ToString(); elem = doc.CreateElement(strFieldName); elem.InnerText = sValue; el_row.AppendChild(elem); } doc.DocumentElement.AppendChild(el_row); } el_row = null; elem = null; return doc.InnerXml; }
private PivotItem ReadItem(OleDbDataReader dataReader, int rowId) { PivotItem item = new PivotItem(rowId.ToString(), this); for (int column = 0; column < dataReader.FieldCount; column++) { if (dataReader.IsDBNull(column)) continue; String columnName = dataReader.GetName(column).ToLowerInvariant(); String value = dataReader.GetValue(column).ToString(); if (String.IsNullOrEmpty(value)) continue; if (columnName == OleDbSchemaConstants.Item.Name) { item.Name = value; } else if (columnName == OleDbSchemaConstants.Item.Image) { String imagePath = UriUtility.ExpandRelativeUri(this.BasePath, value); item.Image = new PivotImage(imagePath); } else if (columnName == OleDbSchemaConstants.Item.Description) { item.Description = value; } else if (columnName == OleDbSchemaConstants.Item.Href) { item.Href = value; } else if (columnName == OleDbSchemaConstants.Item.RelatedLinks) { StringReader valueReader = new StringReader(value); String singleValue = null; while ((singleValue = valueReader.ReadLine()) != null) { String[] parts = singleValue.Split( new String[] { OleDbSchemaConstants.LinkPartDelimiter }, StringSplitOptions.None); if (parts.Length > 0) { String name = null, url = null; if (parts.Length == 1) { url = parts[0]; } else if (parts.Length >= 2) { name = parts[0]; url = parts[1]; } item.AddRelatedLink(new PivotLink(name, url)); } } } else { PivotFacetCategory facetCategory = null; foreach (PivotFacetCategory currentFacetCategory in m_facetCategoryMap.Values) { if (columnName == currentFacetCategory.Name.Replace('.', '#').ToLowerInvariant()) { facetCategory = currentFacetCategory; break; } } if (facetCategory != null) { item.AddFacetValues(facetCategory.Name, this.SplitJoinedStrings(value).ToArray()); } } } return item; }
private PivotFacetCategory CreateFacetCategory(OleDbDataReader dataReader) { String name = null; PivotFacetType type = null; for (int column = 0; column < dataReader.FieldCount; column++) { if (dataReader.IsDBNull(column)) continue; String columnName = dataReader.GetName(column).ToLowerInvariant(); String value = dataReader.GetValue(column).ToString(); if (columnName == OleDbSchemaConstants.FacetCategory.Name) { name = value; } else if (columnName == OleDbSchemaConstants.FacetCategory.Type) { type = PivotFacetType.Parse(value); } } if (name == null) throw new InvalidDataException("Facet Categories data set is missing a Name column"); if (type == null) throw new InvalidDataException("Facet Categories data set is missing a Type column"); return new PivotFacetCategory(name, type); }
// Display the result set recursively expanding chapterDepth deep public void DisplayReader(OleDbDataReader myDataReader, ref uint count, uint alignment, int chapterDepth) { try { // compute alignment StringBuilder indent = new StringBuilder((int) alignment); indent.Append(' ', (int) alignment); while (myDataReader.Read()) { // add alignment StringBuilder row = new StringBuilder(indent.ToString()); // for all columns for (int i = 0; i < myDataReader.FieldCount; i++) { // null columns if (myDataReader.IsDBNull(i)) { row.Append("NULL;"); } else { //vector columns object[] myArray = myDataReader.GetValue(i) as object[]; if (myArray != null) { DisplayValue(myArray, row); } else { //check for chapter columns from "group on" queries if (myDataReader.GetFieldType(i).ToString() != "System.Data.IDataReader") { //regular columns are displayed here row.Append(myDataReader.GetValue(i)); } else { //for a chapter column type just display the colum name row.Append(myDataReader.GetName(i)); } } row.Append(';'); } } if (chapterDepth >= 0) { this.ScrubberGUIInst.DebugPrint(row.ToString()); count++; } // for each chapter column for (int i = 0; i < myDataReader.FieldCount; i++) { if (myDataReader.GetFieldType(i).ToString() == "System.Data.IDataReader") { OleDbDataReader Reader = myDataReader.GetValue(i) as OleDbDataReader; DisplayReader(Reader, ref count, alignment + 8, chapterDepth - 1); } } } } finally { myDataReader.Close(); myDataReader.Dispose(); } }
/// <summary> /// Transfere dados do banco de dados atual para um banco de dados de destino. /// Conexão com o banco de destino precisa estar aberta. /// </summary> /// <returns>Número de linhas transferidas.</returns> /// <param name="p_query">Consulta SQL para buscar os dados no banco atual.</param> /// <param name="p_insert">Comando de inserção para inserir cada linha no banco de destino.</param> /// <param name="p_destdatabase">Conexão com o banco de destino.</param> public override uint Transfer(string p_query, Spartacus.Database.Command p_insert, Spartacus.Database.Generic p_destdatabase) { uint v_transfered = 0; if (this.v_con == null) { try { this.v_con = new System.Data.OleDb.OleDbConnection(this.v_connectionstring); this.v_con.Open(); this.v_cmd = new System.Data.OleDb.OleDbCommand(p_query, this.v_con); this.v_reader = this.v_cmd.ExecuteReader(); while (v_reader.Read()) { for (int i = 0; i < v_reader.FieldCount; i++) p_insert.SetValue(this.FixColumnName(v_reader.GetName(i)).ToLower(), v_reader[i].ToString()); p_destdatabase.Execute(p_insert.GetUpdatedText()); v_transfered++; } return v_transfered; } catch (System.Data.OleDb.OleDbException e) { throw new Spartacus.Database.Exception(e); } finally { if (this.v_reader != null) { this.v_reader.Close(); this.v_reader = null; } if (this.v_cmd != null) { this.v_cmd.Dispose(); this.v_cmd = null; } if (this.v_con != null) { this.v_con.Close(); this.v_con = null; } } } else { try { this.v_cmd.CommandText = p_query; this.v_reader = this.v_cmd.ExecuteReader(); while (v_reader.Read()) { for (int i = 0; i < v_reader.FieldCount; i++) p_insert.SetValue(this.FixColumnName(v_reader.GetName(i)).ToLower(), v_reader[i].ToString()); p_destdatabase.Execute(p_insert.GetUpdatedText()); v_transfered++; } return v_transfered; } catch (System.Data.OleDb.OleDbException e) { throw new Spartacus.Database.Exception(e); } finally { if (this.v_reader != null) { this.v_reader.Close(); this.v_reader = null; } } } }
protected internal virtual void ReadDataIntoDataTable(DataTable dataTable, OleDbDataReader oleDbDataReader) { while (oleDbDataReader.Read()) { var dataRow = dataTable.NewRow(); for (var i = 0; i < oleDbDataReader.FieldCount; i++) { dataRow[oleDbDataReader.GetName(i)] = oleDbDataReader[oleDbDataReader.GetName(i)].ToString(); } dataTable.Rows.Add(dataRow); } }
private List<SingleIndexCriteria> Reader2Criterias(OleDbDataReader reader) { if (reader==null) { return null; } if(reader.IsClosed) { throw (new Exception("Reader is closed!")); } List<SingleIndexCriteria> criterias = new List<SingleIndexCriteria>(); while(reader.Read()) { SingleIndexCriteria singleCriteria = new SingleIndexCriteria(); for(int fieldCounter=0;fieldCounter<reader.FieldCount;fieldCounter++) { switch(reader.GetName(fieldCounter)) { case fieldProjectName: singleCriteria.ProjectName = reader[fieldCounter].ToString(); break; case fieldIndexName: singleCriteria.IndexName = reader[fieldCounter].ToString(); break; case fieldLevelType: singleCriteria.LevelType = int.Parse(reader[fieldCounter].ToString()); break; case fieldCriterias: singleCriteria.CriteriaValues = ConvertCriterias2DivideValue(reader[fieldCounter].ToString()); break; case fieldIsAbsolute: singleCriteria.IsAbsolute = bool.Parse(reader[fieldCounter].ToString()); break; default: throw(new Exception("unexpected field!")); } } criterias.Add(singleCriteria); } reader.Close(); dbConn.Close(); return criterias; }
// Display the result set recursively expanding chapterDepth deep static void DisplayReader(OleDbDataReader myDataReader, ref uint count, uint alignment, int chapterDepth) { try { // compute alignment StringBuilder indent = new StringBuilder((int)alignment); indent.Append(' ', (int)alignment); while (myDataReader.Read()) { // add alignment StringBuilder row = new StringBuilder(indent.ToString()); // for all columns for (int i = 0; i < myDataReader.FieldCount; i++) { // null columns if (myDataReader.IsDBNull(i)) { row.Append("NULL\t"); } else { //vector columns object[] myArray = myDataReader.GetValue(i) as object[]; if (myArray != null) { DisplayValue(myArray, row); } else { //check for chapter columns from "group on" queries if (myDataReader.GetFieldType(i).ToString() != "System.Data.IDataReader") { //regular columns are displayed here //If (OptPath == false) and colName is System.ItemPathDisplay //We want to skip Display of this Column if (!(OptPath == false && myDataReader.GetName(i) == "System.ItemPathDisplay")) row.Append(myDataReader.GetValue(i)); } else { //for a chapter column type just display the colum name row.Append(myDataReader.GetName(i)); } } row.Append('\t'); } } if (chapterDepth >= 0) { Console.WriteLine(row.ToString()); count++; } // for each chapter column for (int i = 0; i < myDataReader.FieldCount; i++) { if (myDataReader.GetFieldType(i).ToString() == "System.Data.IDataReader") { OleDbDataReader Reader = myDataReader.GetValue(i) as OleDbDataReader; DisplayReader(Reader, ref count, alignment + 8, chapterDepth - 1); } } } } finally { myDataReader.Close(); myDataReader.Dispose(); } }
protected void Page_Load(object sender, System.EventArgs e) { string formato_html; if (Session["loggeado"] == null) { Response.Redirect(System.Configuration.ConfigurationManager.AppSettings["urlRoot"].ToString() + "/ingreso.aspx", true); } string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["BaseSqlServer"].ConnectionString; string formato_id = Session["formato_id"].ToString(); string archivo_excel = Session["archivo_excel"].ToString(); using (System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(connectionString)) { string strQuery = "SELECT html FROM matricula.mailxls_formato WHERE id = " + formato_id + ";"; System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(strQuery, connection); connection.Open(); System.Data.SqlClient.SqlDataReader dr = cmd.ExecuteReader(); dr.Read(); formato_html = dr[0].ToString(); connection.Close(); Session.Add("formato_html", formato_html); } System.Data.OleDb.OleDbConnection cnExcel = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath(archivo_excel) + ";Extended Properties=Excel 8.0"); System.Data.OleDb.OleDbCommand cmdExcel = new System.Data.OleDb.OleDbCommand("select * from [Hoja1$]", cnExcel); cnExcel.Open(); System.Data.OleDb.OleDbDataReader drExcel = cmdExcel.ExecuteReader(); int numero_campos = drExcel.FieldCount; string[] nombre_campos = new string[numero_campos]; int j = 1; for (int i = 0; i < numero_campos; i++) { nombre_campos[i] = "{" + drExcel.GetName(i).Trim() + "}"; } if (drExcel.Read()) { for (int i1 = 0; i1 < numero_campos; i1++) { formato_html = formato_html.Replace(nombre_campos[i1], drExcel[i1].ToString()); } } while (drExcel.Read()) { if (drExcel[0].ToString() != "") { j++; } } lblFormato.Text = "<p align=\"right\">" + numero_campos.ToString() + " campos ha reemplazar.<br />" + j.ToString() + " destinatarios.</p>"; drExcel.Close(); cnExcel.Close(); if (formato_html.Contains("\\'")) { formato_html = formato_html.Replace("\\'", "'"); } if (formato_html.Contains("\\\"")) { formato_html = formato_html.Replace("\\\"", "\""); } formato_html = Server.HtmlDecode(formato_html); lblFormato.Text += formato_html; }
public void AddFromReader(OleDbDataReader reader, bool addSpace) { do { if (reader.HasRows) { HtmlGenericControl resultSetDiv = CreateDiv("ResultSet"); _container.Controls.Add(resultSetDiv); int headerColumn = reader.GetColumnIndex("Header"); string lastHeader = null; int subheaderColumn = reader.GetColumnIndex("Subheader"); string lastSubHeader = null; int lastColumn = reader.FieldCount - 1; for (int i = 0; i < 2; ++i) { if (lastColumn == headerColumn || lastColumn == subheaderColumn) { lastColumn -= 1; } } while (reader.Read()) { HtmlGenericControl rowSetDiv = CreateDiv("RowSet"); resultSetDiv.Controls.Add(rowSetDiv); bool headerAdded = false; bool subheaderAdded = false; if (headerColumn >= 0 && !reader.IsDBNull(headerColumn)) { string header = reader.GetValue(headerColumn).ToString(); headerAdded = header != lastHeader; if (headerAdded) { HtmlGenericControl headerDiv = CreateDiv("RowSetHeader", header); rowSetDiv.Controls.Add(headerDiv); lastHeader = header; } } if (subheaderColumn >= 0 && !reader.IsDBNull(subheaderColumn)) { string subheader = reader.GetValue(subheaderColumn).ToString(); subheaderAdded = subheader != lastSubHeader; if (subheaderAdded) { if (headerAdded && addSpace) { AddSpace(rowSetDiv, "Space2"); } HtmlGenericControl subheaderDiv = CreateDiv("RowSetSubheader", subheader); rowSetDiv.Controls.Add(subheaderDiv); lastSubHeader = subheader; } } if (lastColumn < 0) { if (addSpace) { AddSpace(rowSetDiv, "Space2"); } } else { if ((headerAdded || subheaderAdded) && addSpace) { AddSpace(rowSetDiv, "Space2"); } for (int i = 0; i < reader.FieldCount; ++i) { if (i != headerColumn && i != subheaderColumn) { HtmlGenericControl valueSetDiv = CreateDiv("ValueSet"); rowSetDiv.Controls.Add(valueSetDiv); HtmlGenericControl labelDiv = CreateDiv("Label", reader.GetName(i)); valueSetDiv.Controls.Add(labelDiv); if (addSpace) { HtmlGenericControl separatorDiv = CreateDiv("Separator", ""); valueSetDiv.Controls.Add(separatorDiv); } HtmlGenericControl valueDiv = CreateValueDiv(!reader.IsDBNull(i) ? reader.GetValue(i) : null); valueSetDiv.Controls.Add(valueDiv); if (i < lastColumn && addSpace) { AddSpace(rowSetDiv, "Space1"); } } } if (addSpace) { AddSpace(rowSetDiv, "Space4"); } } } } } while (reader.NextResult()); }
/// <summary> /// Load from database by OleDbDataReader - used to load a single record /// </summary> public void Load(OleDbDataReader dataReader) { for(int i=0; i<dataReader.FieldCount; i++) { DBField field = m_fields[(int)m_lookup[dataReader.GetName(i)]]; field.Value = dataReader[field.Name]; //(null == dataReader[field.Name]) ? null : dataReader[field.Name]; } SetUpdateWhereClause(); m_isNew = false; }