本文整理汇总了C#中System.Data.OleDb.OleDbConnection.GetOleDbSchemaTable方法的典型用法代码示例。如果您正苦于以下问题：C# System.Data.OleDb.OleDbConnection.GetOleDbSchemaTable方法的具体用法？C# System.Data.OleDb.OleDbConnection.GetOleDbSchemaTable怎么用？C# System.Data.OleDb.OleDbConnection.GetOleDbSchemaTable使用的例子？那么恭喜您, 这里精选的方法代码示例或许可以为您提供帮助。您也可以进一步了解该方法所在类System.Data.OleDb.OleDbConnection的用法示例。
在下文中一共展示了System.Data.OleDb.OleDbConnection.GetOleDbSchemaTable方法的10个代码示例，这些例子默认根据受欢迎程度排序。您可以为喜欢或者感觉有用的代码点赞，您的评价将有助于系统推荐出更棒的C#代码示例。
示例1: Form1
public Form1()
{
InitializeComponent();
comboBox2.Text = "Column";
comboBox2.Items.Add("Column");
comboBox2.Items.Add("Lines");
comboBox2.Items.Add("Pie");
comboBox2.Items.Add("Bar");
comboBox2.Items.Add("Funnel");
comboBox2.Items.Add("PointAndFigure");
comboBox1.Items.Clear();
if (System.IO.File.Exists("your_base.mdb"))
{
string conStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=your_base.mdb;Jet OLEDB:Engine Type=5";
System.Data.OleDb.OleDbConnection connectDb = new System.Data.OleDb.OleDbConnection(conStr);
connectDb.Open();
DataTable cbTb = connectDb.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
foreach (DataRow row in cbTb.Rows)
{
string tbName = row["TABLE_NAME"].ToString();
comboBox1.Items.Add(tbName);
}
connectDb.Close();
}
}示例2: AnalyzeExcel
public static bool AnalyzeExcel(ExcelXMLLayout layout)
{
System.Data.OleDb.OleDbConnection conn = null;
try
{
conn = new System.Data.OleDb.OleDbConnection(MakeConnectionString(layout.solution.path));
conn.Open();
System.Data.DataTable table = conn.GetOleDbSchemaTable(
System.Data.OleDb.OleDbSchemaGuid.Columns,
new object[] { null, null, layout.sheet + "$", null });
layout.Clear();
System.Diagnostics.Debug.WriteLine("Start Analyze [" + table.Rows.Count + "]");
foreach (System.Data.DataRow row in table.Rows)
{
string name = row["Column_Name"].ToString();
System.Diagnostics.Debug.WriteLine(name);
// 测试数据类型
ExcelXMLLayout.KeyType testType = ExcelXMLLayout.KeyType.Unknown;
{
System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(
string.Format("select [{0}] from [{1}$]", name, layout.sheet), conn
);
System.Data.OleDb.OleDbDataReader r = cmd.ExecuteReader();
while (r.Read())
{
System.Diagnostics.Debug.WriteLine(r[0].GetType());
if (r[0].GetType() == typeof(System.Double))
{
testType = ExcelXMLLayout.KeyType.Integer;
break;
}
if (testType == ExcelXMLLayout.KeyType.String)
{
break;
}
testType = ExcelXMLLayout.KeyType.String;
}
r.Close();
cmd.Dispose();
}
layout.Add(name, testType);
}
table.Dispose();
conn.Close();
return true;
}
catch (Exception outErr)
{
lastError = string.Format("无法分析，Excel 无法打开\r\n{0}", outErr.Message);
}
return false;
}示例3: GetExcelSheetNames
private String[] GetExcelSheetNames(string excelFile, bool blnXlsx = false)
{
System.Data.OleDb.OleDbConnection objConn = null;
System.Data.DataTable dt = null;
try
{
String connString = null;
if (blnXlsx)
{
connString = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + excelFile + ";Extended Properties=\"Excel 12.0 Xml;HDR=NO;IMEX=1\"";
}
else
{
connString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + excelFile + ";Extended Properties=\"Excel 8.0;HDR=NO;IMEX=1\"";
}
objConn = new System.Data.OleDb.OleDbConnection(connString);
objConn.Open();
dt = objConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
if (dt == null)
{
return null;
}
String[] excelSheets = new String[dt.Rows.Count];
int i = 0;
foreach (DataRow row in dt.Rows)
{
excelSheets[i] = row["TABLE_NAME"].ToString();
i += 1;
}
return excelSheets;
}
catch (Exception ex)
{
throw (new Exception("Cannot Read Excel Sheet Names -" + ex.Message));
}
finally
{
if (objConn != null)
{
objConn.Close();
objConn.Dispose();
}
if (dt != null)
{
dt.Dispose();
}
}
}示例4: ConvertExcelToCsv
public void ConvertExcelToCsv(int worksheetNumber = 1)
{
if (!System.IO.File.Exists(excelFilePath)) throw new FileNotFoundException(excelFilePath);
if (File.Exists(csvOutputFile)) throw new ArgumentException("File exists: " + csvOutputFile);
// connection string
var cnnStr = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;IMEX=1;HDR=NO\"", excelFilePath);
var cnn = new System.Data.OleDb.OleDbConnection(cnnStr);
// get schema, then data
var dt = new DataTable();
try
{
cnn.Open();
var schemaTable = cnn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
if (schemaTable.Rows.Count < worksheetNumber) throw new ArgumentException("The worksheet number provided cannot be found in the spreadsheet");
string worksheet = schemaTable.Rows[worksheetNumber - 1]["table_name"].ToString().Replace("'", "");
string sql = String.Format("select * from [{0}]", worksheet);
var da = new System.Data.OleDb.OleDbDataAdapter(sql, cnn);
da.Fill(dt);
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
throw e;
}
finally
{
// free resources
cnn.Close();
}
// write out CSV data
using (var wtr = new StreamWriter(csvOutputFile))
{
foreach (DataRow row in dt.Rows)
{
bool firstLine = true;
foreach (DataColumn col in dt.Columns)
{
if (!firstLine) { wtr.Write(","); } else { firstLine = false; }
var data = row[col.ColumnName].ToString().Replace("\"", "\"\"");
wtr.Write(String.Format("\"{0}\"", data));
}
wtr.WriteLine();
}
}
}示例5: button1_Click
private void button1_Click(object sender, EventArgs e)
{
if (System.IO.File.Exists("your_base.mdb"))
{
comboBox1.Items.Clear();
string conStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=your_base.mdb;Jet OLEDB:Engine Type=5";
System.Data.OleDb.OleDbConnection connectDb = new System.Data.OleDb.OleDbConnection(conStr);
connectDb.Open();
DataTable cbTb = connectDb.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
foreach (DataRow row in cbTb.Rows)
{
string tbName = row["TABLE_NAME"].ToString();
comboBox1.Items.Add(tbName);
}
connectDb.Close();
}
else
{
MessageBox.Show("База данных еще не создана. Воспользуйтесь кнопкой 'загрузить данные' для создания БД.", "Внимание!", MessageBoxButtons.OK,
MessageBoxIcon.Warning);
}
}示例6: button1_Click
private void button1_Click(object sender, EventArgs e)
{
if (comboBox1.Text.ToString() == "")
{
MessageBox.Show("Выбери год", "Внимание!", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else if (comboBox2.Text.ToString() == "")
{
MessageBox.Show("Выбери месяц", "Внимание!", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else if (textBox1.Text == "")
{
MessageBox.Show("Выбери файл", "Внимание!", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
string strConn;
//Check for Excel version
if (textBox1.Text.Substring(textBox1.Text.LastIndexOf('.')).ToLower() == ".xlsx")
{
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + textBox1.Text + ";Extended Properties=\"Excel 12.0;HDR=YES; IMEX=0\"";
}
else
{
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + textBox1.Text + ";Extended Properties=\"Excel 8.0;HDR=YES; IMEX=0\"";
}
System.Data.OleDb.OleDbConnection con = new System.Data.OleDb.OleDbConnection(strConn);
con.Open();
DataSet ds = new DataSet();
DataTable shemaTable = con.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables,
new object[] { null, null, null, "TABLE" });
string sheet1 = (string)shemaTable.Rows[0].ItemArray[2];
string select = String.Format("SELECT * FROM [{0}]", sheet1);
System.Data.OleDb.OleDbDataAdapter ad = new System.Data.OleDb.OleDbDataAdapter(select, con);
ad.Fill(ds);
if (System.IO.File.Exists("your_base.mdb"))
{
int year = Convert.ToInt32(comboBox1.Text.ToString());
int month = Convert.ToInt32(comboBox2.Text.ToString());
string conStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=your_base.mdb;Jet OLEDB:Engine Type=5";
System.Data.OleDb.OleDbConnection connectDb = new System.Data.OleDb.OleDbConnection(conStr);
connectDb.Open();
System.Data.OleDb.OleDbCommand myCMD = new System.Data.OleDb.OleDbCommand();
myCMD.Connection = connectDb;
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
myCMD.CommandText = "Insert into SURVEY (Anim, weight, height, s_year, s_month) VALUES (\"" +
ds.Tables[0].Rows[i][0] + "\", " + ds.Tables[0].Rows[i][1] + ", " + ds.Tables[0].Rows[i][2] + ", " + year + ", " + month + ")";
myCMD.ExecuteNonQuery();
}
MessageBox.Show("Данные загружены в БД", "", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
int year = Convert.ToInt32(comboBox1.Text.ToString());
int month = Convert.ToInt32(comboBox2.Text.ToString());
ADOX.Catalog cat = new ADOX.Catalog();
string connstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Jet OLEDB:Engine Type=5";
cat.Create(String.Format(connstr, "your_base.mdb"));
cat = null;
string conStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=your_base.mdb;Jet OLEDB:Engine Type=5";
Querry("CREATE TABLE SURVEY(Anim varchar(255), weight int, height int, s_year int, s_month int);", "your_base.mdb");
System.Data.OleDb.OleDbConnection connectDb = new System.Data.OleDb.OleDbConnection(conStr);
connectDb.Open();
System.Data.OleDb.OleDbCommand myCMD = new System.Data.OleDb.OleDbCommand();
myCMD.Connection = connectDb;
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
myCMD.CommandText = "Insert into SURVEY (Anim, weight, height, s_year, s_month) VALUES (\"" +
ds.Tables[0].Rows[i][0] + "\", " + ds.Tables[0].Rows[i][1] + ", " + ds.Tables[0].Rows[i][2] + ", " + year + ", " + month + ")";
myCMD.ExecuteNonQuery();
}
//string comm = "Insert into SURVEY (Anim, weight, height) VALUES (hare, 10, 20)";
//System.Data.OleDb.OleDbDataAdapter dbAdp = new System.Data.OleDb.OleDbDataAdapter(comm, conStr);
//dbAdp.Update(ds.Tables[0]);
MessageBox.Show("Данные загружены в БД", "", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
con.Close();
}
}示例7: Create
public ActionResult Create(FormCollection collection, HttpPostedFileBase ArchAuto, HttpPostedFileBase ArchManual)
{
try
{
if (ArchAuto != null &&
ArchAuto.ContentLength > 0 &&
ArchAuto.FileName.EndsWith("txt") &&
ArchManual != null &&
ArchManual.ContentLength > 0 &&
(ArchManual.FileName.EndsWith("xls") || ArchManual.FileName.EndsWith("xlsx"))
)
{
string pathArchManual = "";
if (!Directory.Exists(Server.MapPath("Excels"))) Directory.CreateDirectory(Server.MapPath("Excels"));
var readerArchAuto = new BinaryReader(ArchAuto.InputStream);
string resultArchAuto = System.Text.Encoding.UTF8.GetString(readerArchAuto.ReadBytes(ArchAuto.ContentLength));
string[] lineasArchAuto = Regex.Split(resultArchAuto, "\r\n");
Relevamientos objRelevamiento = new Relevamientos();
objRelevamiento.Observaciones = collection["Observaciones"];
objRelevamiento.FechaCarga = DateTime.Now;
objRelevamiento.FechaInicio = DateTime.Parse(lineasArchAuto[0].Split(';')[0].ToString());
objRelevamiento.FechaFinal = DateTime.Parse(lineasArchAuto[lineasArchAuto.Length - 1].Split(';')[0].ToString());
objRelevamiento.IdEstado = 1;
objRelevamiento.IdTrampa = int.Parse(lineasArchAuto[0].Split(';')[2].ToString());
db.Relevamientos.Add(objRelevamiento);
//db.SaveChanges();
pathArchManual = Server.MapPath("Excels") + @"\" + ArchManual.FileName;
if (System.IO.File.Exists(pathArchManual)) System.IO.File.Delete(pathArchManual);
ArchManual.SaveAs(pathArchManual);
string cnnStr = "";
if (pathArchManual.EndsWith(".xlsx"))
{
//Excel 2007
cnnStr = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 12.0;HDR=Yes;IMEX=1'";
cnnStr += ";Data Source=" + pathArchManual + ";";
}
else
{
//Excel 97-2003
//http://www.connectionstrings.com/excel (leer sobre la clave de registro TypeGuessRows)
cnnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";
cnnStr += ";Data Source=" + pathArchManual + ";";
}
System.Data.OleDb.OleDbConnection oCnn = new System.Data.OleDb.OleDbConnection(cnnStr);
System.Data.OleDb.OleDbDataAdapter oDa = null;
DataTable dtArchManual = new DataTable();
try
{
oCnn.Open();
//Obtenemos los nombres de las hojas del Excel.
DataTable dtHojas = oCnn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
if (dtHojas.Rows.Count > 0)
{
string firstSheet = dtHojas.Rows[0]["TABLE_NAME"].ToString().Trim();
string selectCmd = "select * from [" + firstSheet + "]";
oDa = new System.Data.OleDb.OleDbDataAdapter(selectCmd, oCnn);
oDa.Fill(dtArchManual);
}
oCnn.Close();
dtArchManual.Columns.Add("Fecha");
foreach (DataRow drFila in dtArchManual.Rows)
{
int iAño = 0;
int iMes = 0;
int iDia = 0;
int.TryParse(drFila[0].ToString(), out iAño);
int.TryParse(drFila[1].ToString(), out iMes);
int.TryParse(drFila[2].ToString(), out iDia);
if (iAño > 0 && iMes > 0 && iDia > 0)
{
DateTime tFecha = new DateTime(iAño, iMes, iDia);
drFila["Fecha"] = tFecha.Date.ToShortDateString();
}
if (drFila["Fecha"].ToString() == objRelevamiento.FechaFinal.ToShortDateString())
{
foreach (DataColumn dcColumna in dtArchManual.Columns)
{
if (dcColumna.Ordinal > 2)
{
//CORREGIR SELECCION DE INSECTO
Insectos objInsecto = (from obj in db.Insectos where obj.NombreCientifico == dcColumna.ColumnName select obj).FirstOrDefault();
if (objInsecto != null)
{
int Cantidad = 0;
int.TryParse(drFila[dcColumna.Ordinal].ToString(), out Cantidad);
if (Cantidad > 0)
//.........这里部分代码省略.........
示例8: button2_Click
private void button2_Click(object sender, EventArgs e)
{
OpenFileDialog ofd = new OpenFileDialog();
ofd.DefaultExt = "*.xls;*.xlsx";
ofd.Filter = "Excel 2003(*.xls)|*.xls|Excel 2007(*.xlsx)|*.xlsx";
ofd.Title = "Выберите документ для загрузки данных";
if (ofd.ShowDialog() == DialogResult.OK)
{
textBox1.Text = ofd.FileName;
String constr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
ofd.FileName +
";Extended Properties='Excel 12.0 XML;HDR=YES;';";
System.Data.OleDb.OleDbConnection con =
new System.Data.OleDb.OleDbConnection(constr);
con.Open();
DataSet ds = new DataSet();
DataTable schemaTable = con.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables,
new object[] { null, null, null, "TABLE" });
string sheet1 = (string)schemaTable.Rows[0].ItemArray[2];
string select = String.Format("SELECT * FROM [{0}]", sheet1);
System.Data.OleDb.OleDbDataAdapter ad =
new System.Data.OleDb.OleDbDataAdapter(select, con);
ad.Fill(ds);
DataTable tb = ds.Tables[0];
con.Close();
dataGridView1.DataSource = tb;
con.Close();
}
else
{
MessageBox.Show("Вы не выбрали файл для открытия",
"Загрузка данных...", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}示例9: GetTables
public System.Collections.Generic.List<string> GetTables(string file)
{
string connString = "";
if (Global.filepassword != "")
{
if(Path.GetExtension(file).ToString().ToUpper() != ".ACCDB")
connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + file + ";Jet OLEDB:Database Password=" + Global.filepassword;
else
connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + file + ";Jet OLEDB:Database Password=" + Global.filepassword;
}
else
{
if (Path.GetExtension(file).ToString().ToUpper() != ".ACCDB")
connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + file;
else
connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + file + ";Persist Security Info=False;";
}
System.Data.DataTable tables = null;
bool val = true;
using (System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection(connString))
{
try
{
connection.Open();
tables = connection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
valPass = true;
}
catch (Exception ee)
{
if (ee.Message == "Not a valid password.")
{
//fnGetTab();
val = false;
//MessageBox.Show("Enter Password");
frmPasswordDialog frm = new frmPasswordDialog();
//this.Close();
frm.ShowDialog();
if (Global.filepassword != "")
{
valPass = true;
}
//this.ShowDialog();
}
}
}
System.Collections.Generic.List<string> Tables = new System.Collections.Generic.List<string>();
if (val == true)
{
for (int i = 0; i < tables.Rows.Count; i++)
{
Tables.Add(tables.Rows[i][2].ToString());
}
}
else
{
//txtMdbPassword.Enabled = true;
//txtMdbPassword.Focus();
//if (Global.filepassword != "")
//{
// val = true;
// using (System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + file+";Jet OLEDB:Database Password="+Global.filepassword))
// {
// try
// {
// connection.Open();
// tables = connection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
// }
// catch (Exception ee)
// {
// if (ee.Message == "Not a valid password.")
// {
// //fnGetTab();
// val = false;
// ////MessageBox.Show("Enter Password");
// //frmPasswordDialog frm = new frmPasswordDialog();
// ////this.Close();
// //frm.ShowDialog();
// //this.ShowDialog();
// }
// }
// if (val == true)
// {
// for (int i = 0; i < tables.Rows.Count; i++)
// {
// Tables.Add(tables.Rows[i][2].ToString());
// }
// }
// }
//}
}
return Tables;
}示例10: btnIniciar_Click
private void btnIniciar_Click(object sender, EventArgs e)
{
try
{
this.txtPath.Clear();
OpenFileDialog openfile = new OpenFileDialog();
openfile.Title = string.Format("{0}{1}", "Migrador de ", oTipoMigrador);
openfile.Filter = "Archivos de Excel|*.xlsx;*.xls";
if (openfile.ShowDialog() == DialogResult.OK)
{
this.txtPath.Text = openfile.FileName;
}
if (this.txtPath.Text.Length > 0)
{
if (System.IO.File.Exists(this.txtPath.Text))
{
var cadena = string.Empty;
var ext = System.IO.Path.GetExtension(this.txtPath.Text);
switch (ext)
{
case ".xls": //Excel 97-03
cadena = string.Format("{0}{1}{2}", "Provider = Microsoft.jet.OLEDB.4.0; Data source=", this.txtPath.Text, ";Extended Properties=\"Excel 8.0;HDR=yes;\";");
break;
case ".xlsx": //Excel 07-
cadena = string.Format("{0}{1}{2}", "Provider = Microsoft.ACE.OLEDB.12.0; Data source=", this.txtPath.Text, ";Extended Properties=\"Excel 8.0;HDR=yes;\";");
break;
}
System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(cadena);
conn.Open();
DataTable dtExcelSchema;
dtExcelSchema = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
var SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
conn.Close();
System.Data.OleDb.OleDbDataAdapter excelAdapter = new System.Data.OleDb.OleDbDataAdapter("Select * from [" + SheetName + "]", conn);
DataTable dtDatos = new DataTable();
excelAdapter.Fill(dtDatos);
this.dgvDatos.DataSource = dtDatos;
}
}
}
catch (Exception ex)
{
Util.MensajeError(ex.Message, GlobalClass.NombreApp);
}
}