/**
* Function that accesses the database and creates a DataTable of Parts with the matching SN
*
* @param SN SN to search for
* @return dt Table of vehicles with matching SN
*/
public DataTable SearchPart( string SN )
{
OleDbCommand viewPart = cn.CreateCommand();
OleDbCommand viewEngine = cn.CreateCommand();
OleDbCommand viewTire = cn.CreateCommand();
DataTable dt = new DataTable();
OleDbDataAdapter da = new OleDbDataAdapter();
viewPart.CommandText = "SELECT * FROM Part WHERE Part.SerialNumber = @SerialNumber";
viewEngine.CommandText = "SELECT Part.SerialNumber, VIN, PartName, Manufacturer, HorsePower, Cylinders FROM Part INNER JOIN Engine ON Part.SerialNumber = Engine.SerialNumber WHERE Part.SerialNumber = @SerialNumber";
viewTire.CommandText = "SELECT Part.SerialNumber, VIN, PartName, Manufacturer, Type, TireSize FROM Part INNER JOIN Tire ON Part.SerialNumber = Tire.SerialNumber WHERE Part.SerialNumber = @SerialNumber";
viewPart.Parameters.AddWithValue("@SerialNumber", SN);
viewEngine.Parameters.AddWithValue("@SerialNumber", SN);
viewTire.Parameters.AddWithValue("@SerialNumber", SN);
da.SelectCommand = viewEngine;
da.Fill(dt);
da.SelectCommand = viewTire;
da.Fill(dt);
if (dt.Rows.Count == 0 )
{
dt.Columns.Clear();
da.SelectCommand = viewPart;
da.Fill(dt);
}
return dt;
}
public void GetData()
{
try
{
con = new OleDbConnection(cs);
con.Open();
cmd = new OleDbCommand("SELECT StockId as [Stock ID], (productName) as [Product Name],Features,sum(Quantity) as [Quantity],Price,sum(TotalPrice) as [Total Price] from Config,Stock where Config.ConfigID=Stock.ConfigID group by Stockid, productname,features,price having sum(Quantity > 0) order by Productname", con);
var myDA = new OleDbDataAdapter(cmd);
var myDataSet = new DataSet();
myDA.Fill(myDataSet, "Stock");
myDA.Fill(myDataSet, "Config");
dataGridView1.DataSource = myDataSet.Tables["Stock"].DefaultView;
dataGridView1.DataSource = myDataSet.Tables["Config"].DefaultView;
con.Close();
string[] header = new string[]
{
"รหัส","ชื่อสินค้า","รายละเอียด","จำนวน","ราคา","รวม"
};
for (int i = 0; i < header.Length; i++)
{
dataGridView1.Columns[i].HeaderText = header[i];
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "ล้มเหลว", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
//=====================================================================
/// <summary>
/// Constructor
/// </summary>
public CheckBoxListTestForm()
{
InitializeComponent();
demoData = new DataSet();
productData = new DataSet();
try
{
using(var dbConn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=.\TestData.mdb"))
{
// Load some data for the demo
OleDbCommand cmd = new OleDbCommand("Select * From DemoTable Order By Label", dbConn);
cmd.CommandType = CommandType.Text;
OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);
adapter.Fill(demoData);
// Use a named table for this one
adapter.TableMappings.Add("Table", "ProductInfo");
cmd.CommandText = "Select * From ProductInfo Order By ProductName";
adapter.Fill(productData);
}
}
catch(OleDbException ex)
{
MessageBox.Show(ex.Message);
}
cboDataSource.SelectedIndex = 0;
pgProps.SelectedObject = cblDemo;
pgProps.Refresh();
}
/// <summary>
///
/// </summary>
/// <param name="commandType"></param>
/// <param name="commandText"></param>
/// <param name="commandParameters"></param>
/// <param name="ds"></param>
/// <param name="tableName"></param>
/// <returns></returns>
public override DataSet ExecuteDataset(CommandType commandType, string commandText,
QueryParameterCollection commandParameters, DataSet ds, string tableName)
{
try
{
var cmd = new OleDbCommand();
PrepareCommand(cmd, commandType, commandText, commandParameters);
var adapter = new OleDbDataAdapter(cmd);
if (Equals(tableName, null) || (tableName.Length < 1))
{
adapter.Fill(ds);
}
else
{
adapter.Fill(ds, tableName);
}
base.SyncParameter(commandParameters);
cmd.Parameters.Clear();
return ds;
}
catch
{
if (trans != null)
{
RollbackTransaction();
}
throw;
}
finally
{
Close();
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
DataTable DT = new DataTable("GeneralTable");
DataTable DT2 = new DataTable("GeneralTable");
OleDbDataAdapter DA = new OleDbDataAdapter("SELECT id_kind_m, name_kind_m FROM kind_m", MySchool.feedbackConnection);
try
{
DA.Fill(DT);
DDLMSGType.DataSource = DT;
DDLMSGType.DataTextField = "name_kind_m";
DDLMSGType.DataValueField = "id_kind_m";
DDLMSGType.DataBind();
DA.SelectCommand.CommandText = "SELECT TypeID, TypeName FROM JobTypes";
DA.Fill(DT2);
DDLSenderType.DataSource = DT2;
DDLSenderType.DataTextField = "TypeName";
DDLSenderType.DataValueField = "TypeID";
DDLSenderType.DataBind();
}
catch (Exception ex)
{
LblStatus.Text = ex.Message;
LblStatus.ForeColor = System.Drawing.Color.Red;
LblStatus.Visible = true;
}
}
}
public static void Main(string[] args)
{
WriteToEventLog("Calling AEPM Service...");
var dt = new DataTable();
var dset = new DataSet();
Guid = args[0];
//retrieve any records needing updating
try
{
using(var conn = new OleDbConnection(ConfigurationManager.AppSettings["AS400ConnectionStringDev"]))
{
string sql = GetUnprocessMasterRecsString();
var objDataAdapter = new OleDbDataAdapter();
var cmd = new OleDbCommand(sql, conn);
objDataAdapter.SelectCommand = cmd;
objDataAdapter.SelectCommand.CommandTimeout = 0;
dt.Clear();
dset.Clear();
objDataAdapter.Fill(dt);
objDataAdapter.Fill(dset, "currentSelections");
var cb = new OleDbCommandBuilder(objDataAdapter);
AddParameters(cb);
//update records to 'S' for submitted
for (int i = 0; i < dset.Tables["currentSelections"].Rows.Count; i++)
{
dset.Tables["currentSelections"].Rows[i]["G_RETRN"] = 'S';
}
objDataAdapter.Update(dset, "currentSelections");
//submit records asynch
for (int i = 0; i < dt.Rows.Count; i++)
{
TestAsync(dt, i);
}
}
}
catch (Exception ex)
{
WriteToEventLog(ex);
}
}
public static string[] BilgiAl(string urunid)
{
string sonuc = "işleminiz gerçekleştirildi.";
string[] s = new string[1];
cmdd.Connection = baglanti;
if (baglanti.State == ConnectionState.Open)
baglanti.Close();
try
{
baglanti.Open();
cmdd.CommandText = "select * from sepet where URUNID = " + urunid;
adapter = new OleDbDataAdapter(cmdd);
sepettable = new DataTable();
adapter.Fill(sepettable);
int yandex = 0;
int yindex = 0;
//sonuc = sepettable.Rows[0][2].ToString();
for(int i = 0; i < sepettable.Rows.Count; i++)
{
cmdd.CommandText = "select * from sepet where SIPID = " + sepettable.Rows[i][1].ToString() + " and not URUNID = " + urunid;
adapter2 = new OleDbDataAdapter(cmdd);
tmptable = new DataTable();
adapter.Fill(tmptable);
yandex += tmptable.Rows.Count;
}
s = new string[yandex];
for (int i = 0; i < sepettable.Rows.Count; i++)
{
cmdd.CommandText = "select * from sepet where SIPID = " + sepettable.Rows[i][1].ToString() + " and not URUNID = " + urunid;
adapter2 = new OleDbDataAdapter(cmdd);
tmptable = new DataTable();
adapter.Fill(tmptable);
for (int j = 0; j < tmptable.Rows.Count; j++)
{
s[yindex++] = tmptable.Rows[j][2].ToString();
}
}
}
catch (Exception ex)
{
sonuc = "hata : " + ex.Message;
}
baglanti.Close();
return s;
}
private void Autocomplete()
{
try
{
con = new OleDbConnection(cs);
con.Open();
OleDbCommand cmd = new OleDbCommand("SELECT distinct ProductName FROM product", con);
DataSet ds = new DataSet();
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
da.Fill(ds, "Product");
AutoCompleteStringCollection col = new AutoCompleteStringCollection();
int i = 0;
for (i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
{
col.Add(ds.Tables[0].Rows[i]["productname"].ToString());
}
txtProductName.AutoCompleteSource = AutoCompleteSource.CustomSource;
txtProductName.AutoCompleteCustomSource = col;
txtProductName.AutoCompleteMode = AutoCompleteMode.Suggest;
con.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void DisplayStudents_Load(object sender, EventArgs e)
{
connection = new OleDbConnection();
command = new OleDbCommand();
adapter = new OleDbDataAdapter();
dataset = new DataSet();
connection.ConnectionString =
@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Students.accdb;" +
"Persist Security Info=False";
command.Connection = connection;
command.CommandText = "SELECT * FROM Student";
adapter.SelectCommand = command;
try
{
adapter.Fill(dataset, "Student");
}
catch (OleDbException)
{
MessageBox.Show("Error occured while connecting to database.");
Application.Exit();
}
PopulateFields(0);
}
private void cbxclub_DropDownOpened(object sender, EventArgs e)
{
try
{
if (cbxseason.SelectedItem == null || cbxzone.SelectedItem == null || cbxdivision.SelectedItem == null)
{
MessageBox.Show("Select Season, Zone, Division");
}
string strRetrieve = "";
strRetrieve = "select * from clubdetails";
// cbxclub.Items.Clear();
OleDbDataAdapter adpt = new OleDbDataAdapter(strRetrieve, Database.getConnection());
DataTable dt = new DataTable();
adpt.Fill(dt);
foreach (DataRowView dr in dt.DefaultView)
{
// List<Team> lstFilter = Database.GetEntityList<Team>( false, false,false,Database.getConnection(),RecordStatus true);
//cbxclub.Items.Add(dr["ClubName"]);
//cbxteam.Items.Refresh();
if (!cbxclub.Items.Contains(dr["ClubName"]))// For remove list duplicacy
{
cbxclub.Items.Add(dr["ClubName"]);
}
}
}
catch (Exception ex)
{
throw ex;
}
}
public DataTable GetAllDataTable()
{
DataTable dataTable;
using (OleDbConnection conn = new OleDbConnection(ConnectionString))
{
try
{
conn.Open();
var allFields = _utility.GetColumns <T>();
var query = $"select {string.Join(",", allFields.Select(d => "[" + d.Value + "]"))} from [{_utility.GetSheetName<T>()}$] order by Entrydate DESC";
OleDbDataAdapter objDA = new System.Data.OleDb.OleDbDataAdapter
(query, conn);
DataSet excelDataSet = new DataSet();
objDA.Fill(excelDataSet);
dataTable = excelDataSet.Tables[0];
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
conn.Dispose();
}
}
return(dataTable);
}
public DataTable ExcelToDataTable(string pathName, string sheetName)
{
DataTable tbContainer = new DataTable();
string strConn = string.Empty;
if (string.IsNullOrEmpty(sheetName)) { sheetName = "Sheet1"; }
FileInfo file = new FileInfo(pathName);
if (!file.Exists) { throw new Exception("文件不存在"); }
string extension = file.Extension;
switch (extension)
{
case ".xls":
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathName + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
break;
case ".xlsx":
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + pathName + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'";
break;
default:
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathName + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
break;
}
//链接Excel
OleDbConnection cnnxls = new OleDbConnection(strConn);
//读取Excel里面有 表Sheet1
OleDbDataAdapter oda = new OleDbDataAdapter(string.Format("select * from [{0}$]", sheetName), cnnxls);
DataSet ds = new DataSet();
//将Excel里面有表内容装载到内存表中！
oda.Fill(tbContainer);
return tbContainer;
}
private void button1_Click(object sender, EventArgs e)
{
try
{
// Mo hop thoai Dialog de tim den file Excel
OpenFileDialog ofd = new OpenFileDialog();
ofd.Title = "Chon file chua danh sach so dien thoai";
ofd.Filter = "Các file Excel (*.xls) và *.txt|*.xls|*.txt|";
ofd.ShowDialog();
// Khai bao chuoi ket noi csdl
string ConnectionString;
ConnectionString = "provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0; " + "data source='" + ofd.FileName + "'; ";
// Tao doi tuong ket noi
OleDbConnection cn = new OleDbConnection(ConnectionString);
cn.Open();
// Tao doi tuong thuc thi cau lenh
OleDbCommand cmd = new OleDbCommand("Select * from [Sheet1$] ", cn);
DataSet ds = new DataSet();
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
da.Fill(ds, "Danhsach");
// gan du lieu vao dieu khien
dataGridView1.DataSource = ds;
dataGridView1.DataMember = "Danhsach";
}
catch (Exception ex) { MessageBox.Show("Khong ket noi duoc CSDL: " + ex.Message); }
}
private void FormFirma_Load(object sender, EventArgs e)
{
PutBaze = System.IO.File.ReadAllText(Application.StartupPath + "\\Ponuda\\BazaPonuda.txt");
string PutKriterij =System.IO.File.ReadAllText(@"privPonuda.txt");
string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+PutBaze;
//----------SQL instrukcija-----------\\
string sql = "SELECT * FROM sve WHERE Firma LIKE '" + PutKriterij + "%'";
//klase za povezivanje na ACCESS bazu podataka//
OleDbConnection conn = new OleDbConnection(connString);
OleDbDataAdapter adapter = new OleDbDataAdapter(sql, conn);
//\\//\\//\\//\\//\\//\\//\\//\\//\\//\\//\\//\\//\\//\\//\\
ds = new DataSet(); //kreira noi objekt(kopiju) DataSet()-a
conn.Open(); //otvara spoj s bazom podataka
adapter.Fill(ds, "sve"); //puni se DataSet s podacima tabele t_razred
conn.Close(); //zatvara se baza podataka
//nakon zatvaanja BP imamo odgovarajuće podatke u ds objektu ( DataSet() )
dataGridView2.DataSource = ds; //upisivanje podataka id ds u dataGridView2
dataGridView2.DataMember = "sve";
ukupnaCijenaDataGridViewTextBoxColumn.DefaultCellStyle.FormatProvider = CultureInfo.GetCultureInfo("de-DE");
this.dataGridView2.Sort(firmaDataGridViewTextBoxColumn, ListSortDirection.Ascending);
System.IO.File.Delete(@"privPonuda.txt");
}
private void clears()
{
txtNameSearch.Text = "Enter all or part of a name here...";
txtCCSearch.Text = "Scan a customer card here...";
try
{
connectionString = ConfigurationManager.AppSettings["DBConnectionString"] + frmHomeScreen.mUserFile;
string query = "select * from customer order by last_name;";
OleDbDataAdapter da = new OleDbDataAdapter(query, connectionString);
OleDbCommandBuilder cb = new OleDbCommandBuilder(da);
DataTable dt = new DataTable();
da.Fill(dt);
BindingSource bs = new BindingSource();
bs.DataSource = dt;
dgvCustomers.DataSource = bs;
da.Update(dt);
databaseHandler d = new databaseHandler();
d.closeDatabaseConnection();
}
catch (Exception ee)
{
MessageBox.Show(ee.Message);
}
}
/// <summary>
/// Fill fields names and is values
/// </summary>
/// <param name="query"></param>
/// <param name="connStr"></param>
/// <returns></returns>
Dictionary <string, string> GetFieldsValues(string query, string connStr)
{
DataRow dataRow = null;
Dictionary <string, string> values = new Dictionary <string, string>(StringComparer.OrdinalIgnoreCase); // do not care about key case sensetive
DataTable dt = new DataTable();
// using (var adapter = new System.Data.SqlClient.SqlDataAdapter(query, connStr))
using (var adapter = new System.Data.OleDb.OleDbDataAdapter(query, connStr))
{
adapter.Fill(dt);
}
if (dt.Rows.Count > 0)
{
dataRow = dt.Rows[0];
}
if (dataRow == null)
{
return(values);
}
// values.Add("businessunit", "שלום רב manta בדיקה");
foreach (DataColumn item in dt.Columns)
{
if (values.ContainsKey(item.ColumnName))
{
continue;
}
values.Add(item.ColumnName, dataRow[item].ToString());
}
return(values);
}
private static void PrintSpreadsheet(OleDbConnectionStringBuilder connestionString)
{
DataSet sheet1 = new DataSet();
using (OleDbConnection connection = new OleDbConnection(connestionString.ConnectionString))
{
connection.Open();
string selectSql = @"SELECT * FROM [Sheet1$]";
using (OleDbDataAdapter adapter = new OleDbDataAdapter(selectSql, connection))
{
adapter.Fill(sheet1);
}
connection.Close();
}
var table = sheet1.Tables[0];
foreach (DataRow row in table.Rows)
{
foreach (DataColumn column in table.Columns)
{
Console.Write("{0, -20} ", row[column]);
}
Console.WriteLine();
}
}
public DataSet GetDataSet(string filepath, string excelFileExtension)
{
try {
System.Data.OleDb.OleDbConnection oledbcon = null;
string strConn = string.Empty;
switch (excelFileExtension.Trim()) {
case "xls":
oledbcon = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=\"Excel 8.0;HDR=No;IMEX=1;MaxScanRows=0;\"");
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filepath + ";" + "Extended Properties=Excel 8.0;";
break;
case "xlsx":
oledbcon = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filepath
+ ";Extended Properties='Excel 12.0;HDR=No;IMEX=1'");
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filepath
+ ";Extended Properties=Excel 12.0;";
break;
}
//excel
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
DataTable dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });
string sheetName = dtSheetName.Rows[0]["TABLE_NAME"].ToString();
System.Data.OleDb.OleDbDataAdapter oledbAdaptor = new System.Data.OleDb.OleDbDataAdapter("SELECT * FROM [" + sheetName + "]", oledbcon);
//select
DataSet ds = new DataSet();
oledbAdaptor.Fill(ds);
oledbcon.Close();
return ds;
} catch (Exception ex) {
throw ex;
}
}
/// <summary>
/// 读取Excel的方法
/// </summary>
/// <param name="FilePath">xls文件路径</param>
/// <param name="SelectSQL">查询SQL语句</param>
/// <returns>DataSet</returns>
public static DataSet SelectFromXLS(string FilePath, string SelectSQL)
{
string connStr = "Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = '" + FilePath + "';Extended Properties=\"Excel 8.0; HDR=YES; IMEX=1;\"";
System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(connStr);
System.Data.OleDb.OleDbDataAdapter da = null;
DataSet ds = new DataSet();
try
{
conn.Open();
da = new System.Data.OleDb.OleDbDataAdapter(SelectSQL, conn);
da.Fill(ds, "SelectResult");
}
catch (Exception e)
{
conn.Close();
Logger.LogHelper.LogServiceWebError("iTrackStar.MYHM.Utility.ExcelHandler.SelectFromXLS()" + e.ToString());
//throw e;
}
finally
{
conn.Close();
}
return(ds);
}
public DataSet GetDataSet(string filepath, string excelFileExtension)
{
try {
System.Data.OleDb.OleDbConnection oledbcon = null;
string strConn = string.Empty;
switch (excelFileExtension.Trim())
{
case "xls":
oledbcon = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=\"Excel 8.0;HDR=No;IMEX=1;MaxScanRows=0;\"");
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filepath + ";" + "Extended Properties=Excel 8.0;";
break;
case "xlsx":
oledbcon = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filepath
+ ";Extended Properties='Excel 12.0;HDR=No;IMEX=1'");
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filepath
+ ";Extended Properties=Excel 12.0;";
break;
}
//excel
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
DataTable dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });
string sheetName = dtSheetName.Rows[0]["TABLE_NAME"].ToString();
System.Data.OleDb.OleDbDataAdapter oledbAdaptor = new System.Data.OleDb.OleDbDataAdapter("SELECT * FROM [" + sheetName + "]", oledbcon);
//select
DataSet ds = new DataSet();
oledbAdaptor.Fill(ds);
oledbcon.Close();
return(ds);
} catch (Exception ex) {
throw ex;
}
}
private void bill_button_Click(object sender, EventArgs e)
{
OleDbCommand command1 = new OleDbCommand("UPDATE Bill SET Bill = (SELECT SUM(Price*Quantity) FROM OrderList WHERE IdOrder = ?) WHERE IdOrder = ?", cn);
command1.Parameters.AddWithValue("@Bill", idorder.SelectedItem);
command1.Parameters.AddWithValue("@id", idorder.SelectedItem);
command1.ExecuteNonQuery();
OleDbCommand command4 = new OleDbCommand("DELETE FROM BookedTables WHERE IdVisitor = (SELECT IdVisitor FROM Orders WHERE IdOrder = ?)", cn);
command4.Parameters.AddWithValue("@id", idorder.SelectedItem);
command4.ExecuteNonQuery();
OleDbCommand command2 = new OleDbCommand("UPDATE StatusOrders SET status = ? WHERE IdOrder = ?", cn);
command2.Parameters.AddWithValue("@Status", Status);
command2.Parameters.AddWithValue("@id", idorder.SelectedItem);
command2.ExecuteNonQuery();
OleDbCommand command3 = new OleDbCommand("SELECT Bill FROM Bill WHERE IdOrder = ?", cn);
command3.Parameters.AddWithValue("@bill", idorder.SelectedItem);
command3.ExecuteNonQuery();
OleDbDataReader reader = command3.ExecuteReader();
while (reader.Read())
{
billBox.Text = reader[0].ToString();
}
reader.Close();
dSet.Tables[0].Clear();
dAdapter.Fill(dSet, "Orders");
}
protected void btTaiLen_Click1(object sender, EventArgs e)
{
if (Request.Cookies["ADMIN"] == null) return;
if (FileUpload1.HasFile)
try
{
string path = Server.MapPath(@"EXCEL\") + "Question_" + DateTime.Now.ToString("yyMMdd_HHmmss") + ".xlsx";
FileUpload1.SaveAs(path);
string txt = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 12.0 Xml;", path);
OleDbConnection con = new OleDbConnection(txt);
con.Open();
txt = "Select * FROM [Sheet1$]";
OleDbDataAdapter da = new OleDbDataAdapter(txt, con);
DataSet ds = new DataSet();
da.Fill(ds);
con.Close();
gvCauHoi.DataSource = ds;
gvCauHoi.DataBind();
string temp;
foreach (GridViewRow dr in gvCauHoi.Rows)
{
temp = Server.HtmlDecode(dr.Cells[0].Text);
if (temp.StartsWith("#"))
dr.CssClass = "Q1";
else if (temp.StartsWith("$")) // Không đảo phương án trả lời
dr.CssClass = "Q2";
else if (temp.StartsWith("*")) // Phương án trả lời đúng
dr.CssClass = "A1";
}
}
catch (Exception ex) { lbError.Text = ex.Message; }
}
public ShowCook()
{
InitializeComponent();
String strConn = "Provider = SQLOLEDB; Data Source = DESKTOP-H4GUTBQ; Initial Catalog = Kursach; Integrated Security = SSPI";
cn.ConnectionString = strConn;
cn.Open();
dSet = new DataSet();
DataTable emp;
emp = dSet.Tables.Add("Cook");
emp.Columns.Add("Name", typeof(string));
emp.Columns.Add("Surname", typeof(string));
emp.Columns.Add("Post", typeof(string));
dgCook.SetDataBinding(dSet, "Cook");
OleDbCommand selectCook = new OleDbCommand("SELECT Name, Surname, Post FROM Employees Where Post Like 'Повар%'", cn);
selectCook.ExecuteNonQuery();
dAdapter = new OleDbDataAdapter();
dAdapter.SelectCommand = selectCook;
dAdapter.SelectCommand.Connection = cn;
dAdapter.Fill(dSet, "Cook");
}
/// <summary>
/// Opens an Excel file and imports worksheet into a DataTable. Worksheet Name is required.
/// </summary>
public static DataTable Build(string FilePath, string WorksheetName)
{
if (Path.GetExtension(FilePath) == ".xls")
{
// If anything goes wrong, "using" will force disposal of the connection to the file
using (OleDbConnection conn = BuildExcelConnection(FilePath))
{
// "Connect" to the XLS file
conn.Open();
// Get a DataAdapter to the query
string ExcelQuery = String.Format(SelectWorksheetQueryTemplate, WorksheetName);
OleDbDataAdapter Adapter = new OleDbDataAdapter(ExcelQuery, conn);
// Populate DataTable using DataAdapter
DataTable dataTable = new DataTable();
Adapter.Fill(dataTable);
// Close the connection
conn.Close();
// Finished
return dataTable;
}
}
if (Path.GetExtension(FilePath) == ".xlsx")
{
return OpenXmlExcelToDataTableBuilder.Build(FilePath, WorksheetName);
}
throw new ArgumentException("Invalid file extension specified on Excel data file:" + FilePath);
}
public void validateStaff()
{
OleDbConnection dataConnection = new OleDbConnection();
DataTable dt = new DataTable();
dataConnection.ConnectionString = dataConnection.ConnectionString = (@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=..\..\..\database\\database.accdb");
string sql = "SELECT LAST(StaffID)FROM Registration";
OleDbDataAdapter adapt = new OleDbDataAdapter(sql, dataConnection);
adapt.Fill(dt);
string StaffID = dt.Rows[0][0].ToString();
if (StaffID == "")
{
MessageBoxResult linkoLogIn = MessageBox.Show("No staff on call. Please log in to proceed!", "Error", MessageBoxButton.OKCancel, MessageBoxImage.Error);
if (linkoLogIn == MessageBoxResult.OK)
{
Employee_login loginScreen = new Employee_login();
loginScreen.Show();
}
}
else
{
BedsideMonitoringStation mainInterface = new BedsideMonitoringStation();
mainInterface.Show();
}
}
private void RemoveDBImage(int Index)
{
string stringConnection = (strProvider + strDataSource);
string stringSQL = "SELECT * FROM [tblImages]";
dbConnection.ConnectionString = (strProvider + strDataSource);
dbConnection.Open();
dbAdapter = new OleDbDataAdapter(stringSQL, dbConnection);
dbAdapter.DeleteCommand = dbDeleteCommand;
System.Data.DataSet dataSet = new DataSet("tblImages");
dataSet.Locale = System.Globalization.CultureInfo.InvariantCulture;
dbAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
dbAdapter.Fill(dataSet, "tblImages");
dataSet.Tables["tblImages"].Rows[Index].Delete();
dbAdapter.Update(dataSet, "tblImages");
dbConnection.Close();
CmbBxImages.Items.Remove(CmbBxImages.Text);
if ((Index == CmbBxImages.Items.Count))
{
CmbBxImages.SelectedIndex = (Index - 1);
}
else
{
CmbBxImages.SelectedIndex = Index;
}
}
private void btnCompararStock_Click(object sender, EventArgs e)
{
Cursor.Current = Cursors.WaitCursor;
string Connection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Users\\Benja\\Desktop\\libro1.xlsx;Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\";";
OleDbConnection con = new OleDbConnection(Connection);
System.Data.DataTable tblOriginal = new System.Data.DataTable();
OleDbDataAdapter myCommand = new OleDbDataAdapter("select * from [original$]", con);
myCommand.Fill(tblOriginal);
System.Data.DataTable tblModificado = new System.Data.DataTable();
myCommand = new OleDbDataAdapter("select * from [modificado$]", con);
myCommand.Fill(tblModificado);
int contador = 0;
ArrayList miArray = new ArrayList();
foreach (DataRow rowOriginal in tblOriginal.Rows)
{
string articulo = rowOriginal["Articulo"].ToString();
DataRow[] rowfound = tblModificado.Select("Articulo LIKE '" + articulo + "'");
if (rowfound.Count() == 0)
{
miArray.Add(articulo);
}
foreach (DataRow rowModificado in rowfound)
{
string originalMakro = rowOriginal["Makro"].ToString();
string modificadoMakro = rowModificado["Makro"].ToString();
string originalJesus = rowOriginal["Jesus Maria"].ToString();
string modificadoJesus = rowModificado["Jesus Maria"].ToString();
if (originalMakro != modificadoMakro || originalJesus != modificadoJesus)
MessageBox.Show(rowOriginal["Articulo"].ToString());
}
contador++;
}
MessageBox.Show(contador.ToString());
Cursor.Current = Cursors.Arrow;
}
/// <summary>
/// 执行查询语句，返回DataSet
/// </summary>
/// <param name="SQLString">查询语句</param>
/// <returns>DataSet</returns>
public override DataSet ExecuteDataSet(string SQLString, params IDataParameter[] cmdParms)
{
using (OleDbConnection connection = new OleDbConnection(connectionString))
{
OleDbCommand cmd = new OleDbCommand();
PrepareCommand(cmd, connection, null, SQLString, cmdParms);
using (OleDbDataAdapter da = new OleDbDataAdapter(cmd))
{
DataSet ds = new DataSet();
try
{
da.Fill(ds, "ds");
cmd.Parameters.Clear();
}
catch (OleDbException e)
{
//LogManage.OutputErrLog(e, new Object[] { SQLString, cmdParms });
throw;
}
finally
{
cmd.Dispose();
connection.Close();
}
return ds;
}
}
}
public static void StateData()
{
var count = 0;
var datafile = AppDomain.CurrentDomain.BaseDirectory + @"data\StateData.xlsx";
var connStr = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + datafile + @"; Extended Properties='Excel 12.0 Xml;HDR=YES;'";
var adapter = new OleDbDataAdapter("select * from [states$]", connStr);
var ds = new DataSet();
adapter.Fill(ds, "states");
foreach (DataRow r in ds.Tables["states"].Rows)
{
var code = r.ItemArray[0].ToString();
var value = r.ItemArray[1].ToString();
var capitol = r.ItemArray[2].ToString();
var population = r.ItemArray[3].ToString();
var squaremiles = r.ItemArray[4].ToString();
var entity = new Entity("States", code, value, SequenceType.ALPHA_ASC);
entity.attributes.Add(new LooksFamiliar.Microservices.Ref.Models.Attribute("Capitol", capitol));
entity.attributes.Add(new LooksFamiliar.Microservices.Ref.Models.Attribute("Population", population));
entity.attributes.Add(new LooksFamiliar.Microservices.Ref.Models.Attribute("Square Miles", squaremiles));
entity.link = "US";
var json = ModelManager.ModelToJson<Entity>(entity);
var filename = AppDomain.CurrentDomain.BaseDirectory + @"data\state" + count.ToString() + ".json";
System.IO.File.WriteAllText(filename, json);
Console.WriteLine(count + " Entity: " + entity.code + " | " + entity.codevalue + " | " + capitol + " | " + population + " | " + squaremiles);
count++;
}
}
public Empresa ObtenerEmpresa(string RUC)
{
OleDbConnection obj_Conexion = new OleDbConnection();
OleDbCommand obj_Comando = new OleDbCommand();
OleDbDataAdapter obj_Adapter = new OleDbDataAdapter();
DataSet obj_Data = new DataSet();
obj_Conexion.ConnectionString = "Server=VIRTUALXP-50904\\SQL2008; Provider=SQLOLEDB; User ID=sa; Initial Catalog=Ventas; password=royal2008;";
obj_Conexion.Open();
obj_Comando.Connection = obj_Conexion;
obj_Comando.CommandText = "Select * From Contribuyentes Where Ruc ='" + RUC + "'";
obj_Comando.CommandType = CommandType.Text;
obj_Adapter.SelectCommand = obj_Comando;
obj_Adapter.Fill(obj_Data);
obj_Conexion.Close();
Empresa empresa = new Empresa(); ;
foreach (var dr_Fila in obj_Data.Tables[0].Rows)
{
DataRow fila = (DataRow)dr_Fila;
empresa.RUC = fila[0].ToString();
empresa.nombrecomercial = fila[1].ToString();
empresa.direccion = fila[5].ToString();
empresa.telefono = fila[6].ToString();
empresa.Estado = fila[3].ToString();
}
return empresa;
}
public void ReadExcelSheet(string fileName, string sheetName, Action<DataTableReader> actionForEachRow)
{
var connectionString = string.Format(ExcelSettings.Default.ExcelConnectionString, fileName);
using (var excelConnection = new OleDbConnection(connectionString))
{
excelConnection.Open();
if (sheetName == null)
{
var excelSchema = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if (excelSchema != null)
{
sheetName = excelSchema.Rows[0]["TABLE_NAME"].ToString();
}
}
var excelDbCommand = new OleDbCommand(@"SELECT * FROM [" + sheetName + "]", excelConnection);
using (var oleDbDataAdapter = new OleDbDataAdapter(excelDbCommand))
{
var dataSet = new DataSet();
oleDbDataAdapter.Fill(dataSet);
using (var reader = dataSet.CreateDataReader())
{
while (reader.Read())
{
actionForEachRow(reader);
}
}
}
}
}
//Create an Excel file with 2 columns: name and score:
//Write a program that reads your MS Excel file through
//the OLE DB data provider and displays the name and score row by row.
static void Main()
{
OleDbConnection dbConn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;" +
@"Data Source=D:\Telerik\DataBases\HW\ADONET\06. ReadExcel\Table.xlsx;Extended Properties=""Excel 12.0 XML;HDR=Yes""");
OleDbCommand myCommand = new OleDbCommand("select * from [Sheet1$]", dbConn);
dbConn.Open();
//First way
//using (dbConn) - I think it is better to use dbConn in using clause, but for the demo issues i dont use using.
//{
OleDbDataReader reader = myCommand.ExecuteReader();
while (reader.Read())
{
string name = (string)reader["Name"];
double score = (double)reader["Score"];
Console.WriteLine("{0} - score: {1}", name, score);
}
//}
dbConn.Close();
//Second way
dbConn.Open();
Console.WriteLine();
Console.WriteLine("Second Way");
Console.WriteLine("----------");
DataTable dataSet = new DataTable();
OleDbDataAdapter adapter = new OleDbDataAdapter("select * from [Sheet1$]", dbConn);
adapter.Fill(dataSet);
foreach (DataRow item in dataSet.Rows)
{
Console.WriteLine("{0}|{1}", item.ItemArray[0], item.ItemArray[1]);
}
dbConn.Close();
}
public ICollection<Empresa> ConsultarEmpresa(string RUC, string nombreComercial)
{
OleDbConnection obj_Conexion = new OleDbConnection();
OleDbCommand obj_Comando = new OleDbCommand();
OleDbDataAdapter obj_Adapter = new OleDbDataAdapter();
DataSet obj_Data = new DataSet();
obj_Conexion.ConnectionString = "Server=VIRTUALXP-50904\\SQL2008; Provider=SQLOLEDB; User ID=sa; Initial Catalog=Ventas; password=royal2008;";
obj_Conexion.Open();
obj_Comando.Connection = obj_Conexion;
if (RUC == null && nombreComercial == null)
obj_Comando.CommandText = "Select * From Contribuyentes";
else if (RUC != null && nombreComercial == null)
obj_Comando.CommandText = "Select * From Contribuyentes Where Ruc Like '%" + RUC + "%'";
else
obj_Comando.CommandText = "Select * From Contribuyentes Where RazonSocial Like '%" + nombreComercial + "%'";
obj_Comando.CommandType = CommandType.Text;
obj_Adapter.SelectCommand = obj_Comando;
obj_Adapter.Fill(obj_Data);
obj_Conexion.Close();
List<Empresa> empresas = new List<Empresa>();
Empresa empresa;
foreach (var dr_Fila in obj_Data.Tables[0].Rows)
{
DataRow fila = (DataRow)dr_Fila;
empresa = new Empresa();
empresa.RUC = fila[0].ToString();
empresa.nombrecomercial = fila[1].ToString();
empresa.Estado = fila[3].ToString();
empresas.Add(empresa);
}
return empresas;
}
/// <summary>
/// 读取Excel文件的表头 指定Sheet
/// </summary>
/// <param name="filepath">文件路径</param>
/// <returns>DataTable</returns>
public static DataTable ReadExcelHeader(string fileName, string sheet)
{
System.Data.DataSet itemDS = new DataSet();
if (fileName.Trim().ToUpper().EndsWith("XLS") || fileName.Trim().ToUpper().EndsWith("XLSX"))
{
string connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties=\"Excel 12.0;HdR=YES;\"";
System.Data.OleDb.OleDbConnection conn = null;
System.Data.OleDb.OleDbCommand oledbCommd = null;
try
{
conn = new System.Data.OleDb.OleDbConnection();
conn.ConnectionString = connStr;
conn.Open();
string sqlText = "select top 1 * from [" + sheet + "]";
oledbCommd = new System.Data.OleDb.OleDbCommand(sqlText, conn);
oledbCommd.CommandTimeout = 100000;
//执行
System.Data.OleDb.OleDbDataAdapter oledbDA = new System.Data.OleDb.OleDbDataAdapter(oledbCommd);
oledbDA.Fill(itemDS);
}
catch
{ }
finally
{
//释放
oledbCommd.Dispose();
conn.Close();
}
//创建连接
}
return(itemDS.Tables[0]);
}
public DataView ExceltoDataView(string strFilePath)
{
DataView dv;
try
{
OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0;Data Source=" + strFilePath + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1'");
conn.Open();
object[] CSs0s0001 = new object[4];
CSs0s0001[3] = "TABLE";
DataTable tblSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, CSs0s0001);
string tableName = Convert.ToString(tblSchema.Rows[0]["TABLE_NAME"]);
if (tblSchema.Rows.Count > 1)
{
tableName = "sheet1$";
}
string sql_F = "SELECT * FROM [{0}]";
OleDbDataAdapter adp = new OleDbDataAdapter(string.Format(sql_F, tableName), conn);
DataSet ds = new DataSet();
adp.Fill(ds, "Excel");
dv = ds.Tables[0].DefaultView;
conn.Close();
}
catch (Exception)
{
Exception strEx = new Exception("請確認是否使用模板上傳(上傳的Excel中第一個工作表名稱是否為Sheet1)");
throw strEx;
}
return dv;
}
/// <summary>
/// 从EXCEL中获取数据(放入dataset中)
/// </summary>
/// <param name="postedFile"></param>
/// <param name="context"></param>
/// <param name="tableName"></param>
/// <returns></returns>
public static DataSet GetDataFromUploadFile(this HttpPostedFile postedFile, HttpContext context, string tableName)
{
string directory = context.Server.MapPath(POSTED_FILE_ROOT_DIRECTORY);
if (!Directory.Exists(directory))
Directory.CreateDirectory(directory);
string filename = postedFile.FileName;
//将文件上传至服务器
postedFile.SaveAs(context.Server.MapPath(POSTED_FILE_ROOT_DIRECTORY) + filename);
string conn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + context.Server.MapPath(POSTED_FILE_ROOT_DIRECTORY) + filename + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1'";
conn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + context.Server.MapPath(POSTED_FILE_ROOT_DIRECTORY) + filename + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1'";
//string sqlin = "SELECT * FROM [" + "ConstructPlanDevice" + "$]";
//OleDbCommand oleCommand = new OleDbCommand(sqlin, new OleDbConnection(conn));
//OleDbDataAdapter adapterIn = new OleDbDataAdapter(oleCommand);
//DataSet dsIn = new DataSet();
//adapterIn.Fill(dsIn, tableName);
OleDbConnection conn1 = new OleDbConnection(conn);
conn1.Open();
string name = conn1.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0][2].ToString().Trim();
OleDbDataAdapter odda = new OleDbDataAdapter("select * from ["+name+"]", conn1);
DataSet dsIn1 = new DataSet();
odda.Fill(dsIn1, tableName);
conn1.Close();
return dsIn1;
}
public static DataTable GetExcelDataTable(string filePath, string sql)
{
try
{
//Office 2003
//OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1;Readonly=0'");
//Office 2007
OleDbConnection conn =
new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath +
";Extended Properties='Excel 12.0 Xml;HDR=YES'");
OleDbDataAdapter da = new OleDbDataAdapter(sql, conn);
DataTable dt = new DataTable();
da.Fill(dt);
dt.TableName = "tmp";
conn.Close();
return dt;
}
catch (Exception)
{
MessageBox.Show("Please input correct SQL Command for your file!");
return null;
}
}
private void show_oder_details_Click(object sender, EventArgs e)
{
String query = "SELECT * FROM [Order Details]";
dataSet.Clear();
dataAdapter = new OleDbDataAdapter(query, dbConnection);
dataAdapter.Fill(dataSet, "Order Details");
}
//
// Processing request.
//
DataSet RunQuery(String Query)
{
dSet = new DataSet();
dAdapter = new OleDbDataAdapter(Query, cn);
dAdapter.Fill(dSet, "Department");
dTable = dSet.Tables["Department"];
return(dSet);
}
private void loadxls()
{
if (strpath != "")
{
try
{
this.dgvStudentList.DataSource = null;
string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " + strpath + " ;Extended Properties=Excel 8.0";
System.Data.OleDb.OleDbConnection myConn = new System.Data.OleDb.OleDbConnection(strCon);
string strCom = "SELECT * FROM [Sheet1$]";
System.Data.OleDb.OleDbDataAdapter myCommand = new System.Data.OleDb.OleDbDataAdapter(strCom, myConn);
System.Data.DataTable dt = new System.Data.DataTable();
myCommand.Fill(dt);
students.Clear();
students.Add(new Student()
{
StudentName = dt.Columns[0].ColumnName,
Gender = dt.Columns[1].ColumnName,
Birthday = Convert.ToDateTime(dt.Columns[2].ColumnName),
Age = DateTime.Now.Year - Convert.ToDateTime(dt.Columns[2].ColumnName).Year,
StudentIdNo = dt.Columns[3].ColumnName.Trim(),
PhoneNumber = dt.Columns[4].ColumnName.Trim(),
StudentAddress = dt.Columns[5].ColumnName.Trim() == "" ? "地址不详" : dt.Columns[5].ColumnName.Trim(),
ClassId = int.Parse(dt.Columns[6].ToString()),
StuImage = "",
});;
foreach (DataRow item in dt.Rows)
{
students.Add(new Student()
{
StudentName = item[0].ToString(),
Gender = item[1].ToString(),
Birthday = Convert.ToDateTime(item[2].ToString()),
Age = DateTime.Now.Year - Convert.ToDateTime(item[2].ToString()).Year,
StudentIdNo = item[3].ToString().Trim(),
PhoneNumber = item[4].ToString().Trim(),
StudentAddress = item[5].ToString().Trim() == "" ? "地址不详" : item[5].ToString().Trim(),
ClassId = int.Parse(item[6].ToString()),
StuImage = "",
});
}
this.dgvStudentList.DataSource = students;
}
catch (Exception ex)
{
MessageBox.Show("请选择案件导入的EXCEL：" + ex.Message);
}
}
else
{
MessageBox.Show("请选择Excel文件");
}
}
private void UploadExhibitormove()
{
StringBuilder str2 = new StringBuilder();
int count = 0;
#region Import Excel Connection
System.Data.OleDb.OleDbConnection MyConnection;
System.Data.DataSet DtSet;
System.Data.OleDb.OleDbDataAdapter MyCommand;
MyConnection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source='C:\\move_out.xls';Extended Properties=Excel 8.0;");
MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [1$]", MyConnection);
MyCommand.TableMappings.Add("Table", "TestTable");
DtSet = new System.Data.DataSet();
MyCommand.Fill(DtSet);
DataTable dt = DtSet.Tables[0];
MyConnection.Close();
#endregion
#region Import Data To Database
foreach (DataRow dr in dt.Rows)
{
string id13 = string.Empty;
DataRow drdup = null;
id13 = dr["id13"].ToString();
if (!string.IsNullOrEmpty(id13))
{
drdup = dcore.GetSearchPeople(id13, "", "");
}
if (drdup != null)
{
drdup["status"] = "2";
drdup["report_date"] = DateTime.Now;
try
{
count++;
dcore.UpdatePeople(drdup);
}
catch (Exception ex)
{
throw ex;
}
progressBar1.PerformStep();
}
else
{
str2.AppendLine(id13);
}
}
#endregion
MessageBox.Show("บันทึกสำเร็จ");
}
/// <summary>
/// 使对象进入修改模式
/// 用户可以在返回表结构的DataTable中添加数据
/// 再调用Update(DataTable)进行更新操作
/// 此后，对象将退出修改模式状态
/// </summary>
/// <param name="TableName">需要插入的表名称</param>
/// <returns>要插入目标表的结构</returns>
public override DataTable InsertMode(string TableName)
{
dataManager.execNum++;
System.Data.DataTable dt = new DataTable();
_dap = new OleDbDataAdapter("select * from [" + TableName + "] where false", dataManager._conn);
_cmdb = new OleDbCommandBuilder(_dap);
_dap.Fill(dt);
return(dt);
}
/// <summary>
/// 使对象将进入修改模式状态
/// 用户可以修改返回结果，包括删除、修改和增加行
/// 再调用Update(DataTable)进行更新操作
/// 此后，对象将退出修改模式状态
/// </summary>
/// <param name="SQLCmd">SQL语句</param>
/// <returns>查询响应结果</returns>
public override DataTable SelectWithUpdate(string SQLCmd)
{
dataManager.execNum++;
System.Data.DataTable dt = new DataTable();
_dap = new OleDbDataAdapter(SQLCmd, dataManager._conn);
_cmdb = new OleDbCommandBuilder(_dap);
_dap.Fill(dt);
return(dt);
}
private void categories()
{
String query = "SELECT * FROM Categories";
dataAdapter = new OleDbDataAdapter(query, dbConnection);
dataAdapter.Fill(dataSet, "Categories");
dgCategories.DataSource = new BindingSource(dataSet, "Categories");
}
private void Read()
{
ConnSource = new System.Data.OleDb.OleDbConnection(ConnStringSource);
da = new System.Data.OleDb.OleDbDataAdapter();
da.SelectCommand = new System.Data.OleDb.OleDbCommand(sqlSelect);
da.SelectCommand.Connection = ConnSource;
ds = new System.Data.DataSet();
da.Fill(ds, "Extracto");
}
protected void Page_Load(object sender, System.EventArgs e)
{
nChartControl1.BackgroundStyle.FrameStyle.Visible = false;
// add header
NLabel header = nChartControl1.Labels.AddHeader("Bind to Data Adapter");
header.TextStyle.FontStyle = new NFontStyle("Times New Roman", 14, FontStyle.Italic);
header.TextStyle.ShadowStyle.Type = ShadowType.LinearBlur;
header.ContentAlignment = ContentAlignment.BottomRight;
header.Location = new NPointL(
new NLength(2, NRelativeUnit.ParentPercentage),
new NLength(2, NRelativeUnit.ParentPercentage));
nChartControl1.DataBindingManager.EnableDataBinding = CheckBoxEnableDatabindig.Checked;
// configure the chart
NChart chart = nChartControl1.Charts[0];
chart.Width = 100.0f;
chart.Height = 65.0f;
chart.BoundsMode = BoundsMode.Stretch;
chart.Location = new NPointL(
new NLength(5, NRelativeUnit.ParentPercentage),
new NLength(15, NRelativeUnit.ParentPercentage));
chart.Size = new NSizeL(
new NLength(70, NRelativeUnit.ParentPercentage),
new NLength(80, NRelativeUnit.ParentPercentage));
// add the bar series
NBarSeries bar = (NBarSeries)chart.Series.Add(SeriesType.Bar);
bar.Name = "Bar";
bar.Legend.Format = "<label>";
bar.DataLabelStyle.Format = "<label>";
bar.BarShape = BarShape.SmoothEdgeBar;
bar.Legend.Mode = SeriesLegendMode.DataPoints;
// binding chart
BindingChartToData();
// fill data set
oleDbDataAdapter1.Fill(dsTable1);
// if not post back bind data grid
if (!IsPostBack)
{
DataGrid1.DataBind();
}
// set "id" column as read-only
dsTable1.MyTable.columnid.ReadOnly = true;
}
private void button1_Click(object sender, EventArgs e)
{
setOfData = new DataSet();
adapter = new dabse.OleDbDataAdapter("Select * From " + nameoftable, Date_baseone);;
adapter.Fill(setOfData, nameoftable);
dataGridView1.DataSource = setOfData;
dataGridView1.DataMember = nameoftable;
MessageBox.Show("Загружено!");
}
public static DataTable GetDataTableExcel(string strFileName, string Table)
{
System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0; Data Source = " + strFileName + "; Extended Properties = \"Excel 8.0;HDR=Yes;IMEX=1\";");
conn.Open();
string strQuery = "SELECT * FROM [" + Table + "]";
System.Data.OleDb.OleDbDataAdapter adapter = new System.Data.OleDb.OleDbDataAdapter(strQuery, conn);
System.Data.DataSet ds = new System.Data.DataSet();
adapter.Fill(ds);
return(ds.Tables[0]);
}
private void save_result()
{
saveFlag = 1;
string files = Application.StartupPath + "\\DataBase.mdb";
string aConnectString = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=";
aConnectString += files;
sqlCon.ConnectionString = aConnectString;
sqlCon.Open();
sqlCmd.Connection = sqlCon;
for (int ind = 0; ind < Grid1.RowCount; ind++)
{
if (Grid1.Rows[ind].Cells[0].Value == null)
{
continue;
}
int flagValue = changeFlag[int.Parse(Grid1.Rows[ind].Cells[0].Value.ToString())];
if (flagValue == 1)
{
string sqlstr = "select * from userData where 编号 = " + Grid1.Rows[ind].Cells[0].Value.ToString();
sqlAdp = new OleDbDataAdapter(sqlstr, sqlCon);
DataSet ds = new DataSet();
sqlAdp.Fill(ds, "userData");
if (ds.Tables[0].Rows.Count > 0)
{
sqlCmd.CommandText = UpdateStr(ind);
sqlCmd.ExecuteNonQuery();
}
else
{
sqlCmd.CommandText = InsertStr(ind);
sqlCmd.ExecuteNonQuery();
}
}
}
//记录需要删除的就删掉
foreach (var item in changeFlag)
{
if (item.Value == 2)
{
string sqlstr = "select * from userData where 编号 = " + item.Key;
sqlAdp = new OleDbDataAdapter(sqlstr, sqlCon);
DataSet ds = new DataSet();
sqlAdp.Fill(ds, "userData");
if (ds.Tables[0].Rows.Count > 0)
{
string delstr = "delete from userData where 编号 = " + item.Key;
sqlCmd.CommandText = delstr;
sqlCmd.ExecuteNonQuery();
}
}
}
sqlCon.Close();
}
public override System.Data.DataColumn[] ObtenerColumnas(FuenteInformacion fuenteInformacion, string rutaArchivo)
{
try
{
DataSet dsMsExcel = new DataSet();
using (System.Data.OleDb.OleDbConnection objOleConnection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + rutaArchivo + ";Mode=ReadWrite;Extended Properties=\"Excel 12.0 Xml;HDR=NO;IMEX=1\""))
{
System.Data.OleDb.OleDbDataAdapter adapter = new System.Data.OleDb.OleDbDataAdapter();
if (File.Exists(rutaArchivo))
{
objOleConnection.Open();
DataTable worksheets = objOleConnection.GetSchema("Tables");
string hoja = worksheets.Rows[0][2].ToString();
System.Data.OleDb.OleDbCommand select = new System.Data.OleDb.OleDbCommand("SELECT * FROM [" + hoja + "]", objOleConnection);
select.CommandType = CommandType.Text;
adapter.SelectCommand = select;
dsMsExcel.Tables.Clear();
adapter.Fill(dsMsExcel);
if (dsMsExcel.Tables.Count > 0)
{
DataRow col = dsMsExcel.Tables[0].Rows[0];
DataColumn[] columnas = new DataColumn[col.ItemArray.Length];
int index = 0;
string nombre;
foreach (object campo in col.ItemArray)
{
if (!string.IsNullOrEmpty(campo.ToString()))
{
nombre = campo.ToString().Trim();
}
else
{
nombre = "-----------" + index.ToString();
}
columnas[index] = new DataColumn(nombre);
index++;
}
return(columnas);
}
}
else
{
throw new Exception("El archivo " + Path.GetFileName(rutaArchivo) + " no existe. Corrija el mapeo y vuelva a intentarlo.");
}
}
}
catch (Exception ex)
{
throw ex;
}
return(null);
}
//读取数据
private string ReadFile(string filePath, ref DataTable dtSource)
{
string strCon = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;IMEX=1'", filePath);
System.Data.OleDb.OleDbConnection Conn = new System.Data.OleDb.OleDbConnection(strCon);
try
{
Conn.Open();
var Sheet1 = "Sheet1";
string sSheetName = "";
if (String.IsNullOrEmpty(Sheet1))
{
List <string> lstSheetNames = new List <string>();
DataTable dtSheets = new DataTable();
dtSheets = Conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });
for (int i = 0; i < dtSheets.Rows.Count; i++)
{
if (!dtSheets.Rows[i]["TABLE_NAME"].ToString().Contains("_"))
{
lstSheetNames.Add(dtSheets.Rows[i]["TABLE_NAME"].ToString());
}
}
sSheetName = lstSheetNames[0];
}
else
{
sSheetName = Sheet1 + "$";
}
DataSet ds = new DataSet();
string strCom = String.Format("SELECT * FROM [{0}]", sSheetName);
System.Data.OleDb.OleDbDataAdapter myCommand = new System.Data.OleDb.OleDbDataAdapter(strCom, Conn);
myCommand.Fill(ds, "ExcelTable");
dtSource = ds.Tables[0];
}
catch (Exception e)
{
return("读取文件数据时出错:" + e.Message);
}
finally
{
Conn.Close();
try
{
FileInfo fi = new FileInfo(filePath); //删除临时文件
fi.Delete();
}
catch {}
}
return("");
}
public static DataSet anotherFunctionOfAdding(string nameOfTable)
{
DataSet setOfData = new DataSet();
var Database = new dabse.OleDbConnection("Data Source=Медведев.mdb;User ID=Admin;Provider=\"Microsoft.Jet.OLEDB.4.0\";");
dabse.OleDbCommand command = new dabse.OleDbCommand();
dabse.OleDbDataAdapter adapter = new dabse.OleDbDataAdapter("Select * From " + nameOfTable, Database);;
Database.Open();
adapter.Fill(setOfData, nameOfTable);
Database.Close();
return(setOfData);
}
public void ExportToGrid(String path)
{
try
{
System.Data.DataTable dt = new System.Data.DataTable();
OleDbConnection MyConnection = null;
DataSet DtSet = null;
OleDbDataAdapter MyCommand = null; //Connection for MS Excel 2003 .xls format
string sheetname = "Sheet1";
// MyConnection = new OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; Data Source='" + path + "';Extended Properties=Excel 8.0;");
//Connection for .xslx 2007 format //
if (path.Substring(path.LastIndexOf('.')).ToLower() == ".xlsx")
{
MyConnection = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + path + "';Extended Properties=Excel 12.0;"); //Select your Excel file
}
else
{
MyConnection = new OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; Data Source='" + path + "';Extended Properties= 'Excel 8.0;HDR=YES;IMEX=1;';");
}
MyConnection.Open();
System.Data.DataTable schemaTable = MyConnection.GetOleDbSchemaTable(
OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
if (schemaTable.Rows.Count > 0)
{
sheetname = schemaTable.Rows[0]["TABLE_NAME"].ToString();
}
MyConnection.Close();
MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [" + sheetname + "]", MyConnection);
DtSet = new System.Data.DataSet(); //Bind all excel data in to data set
MyCommand.Fill(DtSet, "[" + sheetname + "]");
dt = DtSet.Tables[0];
MyConnection.Close(); //Check datatable have records
if (dt.Rows.Count > 0)
{
gvUploadKitty.DataSource = dt;
gvUploadKitty.DataBind();
ViewState["kittyTable"] = dt;
} //Delete temporary Excel file from the Server path
if (System.IO.File.Exists(path))
{
System.IO.File.Delete(path);
}
}
catch (Exception ex)
{
string js = "alert('The uploaded file is not in correct format !!');"; //"alert('" + ex + "');";
ScriptManager.RegisterStartupScript(this, this.GetType(), "script2", js, true);
}
}
private void UploadExhibitorfrom_report()
{
StringBuilder str2 = new StringBuilder();
#region Import Excel Connection
System.Data.OleDb.OleDbConnection MyConnection;
System.Data.DataSet DtSet;
System.Data.OleDb.OleDbDataAdapter MyCommand;
MyConnection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source='C:\\2.xls';Extended Properties=Excel 8.0;");
MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection);
MyCommand.TableMappings.Add("Table", "TestTable");
DtSet = new System.Data.DataSet();
MyCommand.Fill(DtSet);
DataTable dt = DtSet.Tables[0];
MyConnection.Close();
#endregion
#region Import Data To Database
foreach (DataRow dr in dt.Rows)
{
string id13 = string.Empty;
DataRow drdup = null;
string[] s = dr["id"].ToString().Split("\n".ToCharArray());
string[] str = s[1].Split("-".ToCharArray());
for (int i = 0; i < str.Length; i++)
{
id13 += str[i];
}
progressBar1.Maximum = dt.Rows.Count;
progressBar1.Step = 1;
if (!string.IsNullOrEmpty(id13))
{
drdup = dcore.GetSearchPeople(id13, "", "");
}
if (drdup != null)
{
drdup["report_number"] = "2";
drdup["report_date"] = DateTime.Now;
dcore.UpdatePeople(drdup);
progressBar1.PerformStep();
}
}
#endregion
MessageBox.Show("บันทึกสำเร็จ");
}
private static DataTable makeDataTableFromSheetName(string filename, string sheetName)
{
System.Data.OleDb.OleDbConnection myConnection = new System.Data.OleDb.OleDbConnection(
"Provider=Microsoft.ACE.OLEDB.12.0; " +
"data source='" + filename + "';" +
"Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\" ");
DataTable dtImport = new DataTable();
System.Data.OleDb.OleDbDataAdapter myImportCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [" + sheetName + "]", myConnection);
myImportCommand.Fill(dtImport);
return(dtImport);
}
/// <summary>
/// Get data table using OleDb
/// parameters
/// <sql>query</sql>
/// <connectionString> Connection string to connect </connectionString>
/// </summary>
public static DataTable GetDataTable_OLE(string sheetName)
{
using (OleDbConnection conn = new OleDbConnection(connString))
{
conn.Open();
OleDbDataAdapter objDA = new System.Data.OleDb.OleDbDataAdapter
("SELECT * from [" + sheetName + "$]", conn); // takes parameters query and connection object.
DataSet excelDataSet = new DataSet();
objDA.Fill(excelDataSet); // update the dataset.
return(excelDataSet.Tables[0]); //will return the data table object
}
}
private void bt_load_Click_1(object sender, EventArgs e)
{
System.Data.OleDb.OleDbConnection MyConnection;
System.Data.DataSet DtSet;
System.Data.OleDb.OleDbDataAdapter MyCommand;
MyConnection = new System.Data.OleDb.OleDbConnection(@"provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + tb_file.Text + ";Extended Properties=Excel 8.0;");
MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection);
MyCommand.TableMappings.Add("Table", "Net-informations.com");
DtSet = new System.Data.DataSet();
MyCommand.Fill(DtSet);
dgv_siswa.DataSource = DtSet.Tables[0];
MyConnection.Close();
}
/// <summary>
/// func_excel_read_ole f = new func_excel_read_ole();
/// System.Data.DataTable ret = f.CreateDataSource(filePath, SheetName);
/// </summary>
/// <param name="filePath"></param>
/// <param name="sheetName"></param>
/// <returns></returns>
public DataTable GetDataSource(string filePath, string sheetName)
{
string strConn = sConnstring;
strConn = string.Format(strConn, filePath);
System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(strConn);
System.Data.OleDb.OleDbDataAdapter Adapter = new System.Data.OleDb.OleDbDataAdapter("SELECT * FROM [" + sheetName + "$]", strConn);
DataSet myDataSet = new DataSet();
Adapter.Fill(myDataSet);
return(myDataSet.Tables[0]);
}
private static void SelectExcelFile(string connectionString)
{
// Connect EXCEL sheet with OLEDB using connection string
using (OleDbConnection conn = new OleDbConnection(connectionString))
{
conn.Open();
OleDbDataAdapter objDA = new System.Data.OleDb.OleDbDataAdapter
("select * from [Sheet1$]", conn);
DataSet excelDataSet = new DataSet();
objDA.Fill(excelDataSet);
//dataGridView1.DataSource = excelDataSet.Tables[0];
}
}
public static int SaveRecord(string sql)
{
const int rv = 0;
try
{
string connectionString = ConfigurationManager.ConnectionStrings["LA3Access"].ConnectionString;
using (var conn = new OleDbConnection(connectionString))
{
conn.Open();
var cmGetID = new OleDbCommand("SELECT @@IDENTITY", conn);
var comm = new OleDbCommand(sql, conn) { CommandType = CommandType.Text };
comm.ExecuteNonQuery();
var ds = new DataSet();
var adapt = new OleDbDataAdapter(cmGetID);
adapt.Fill(ds);
adapt.Dispose();
cmGetID.Dispose();
return int.Parse(ds.Tables[0].Rows[0][0].ToString());
}
}
catch (Exception)
{
}
return rv;
}
