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