internal OleDbTransaction (OleDbConnection connection, int depth, IsolationLevel isolevel)
{
this.connection = connection;
gdaTransaction = libgda.gda_transaction_new (depth.ToString ());
switch (isolevel) {
case IsolationLevel.ReadCommitted :
libgda.gda_transaction_set_isolation_level (gdaTransaction,
GdaTransactionIsolation.ReadCommitted);
break;
case IsolationLevel.ReadUncommitted :
libgda.gda_transaction_set_isolation_level (gdaTransaction,
GdaTransactionIsolation.ReadUncommitted);
break;
case IsolationLevel.RepeatableRead :
libgda.gda_transaction_set_isolation_level (gdaTransaction,
GdaTransactionIsolation.RepeatableRead);
break;
case IsolationLevel.Serializable :
libgda.gda_transaction_set_isolation_level (gdaTransaction,
GdaTransactionIsolation.Serializable);
break;
}
libgda.gda_connection_begin_transaction (connection.GdaConnection, gdaTransaction);
}
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);
}
}
}
}
}
public bool InsertFtpRecord(FileDetail fileDetail)
{
var lcsql =
"insert into MasterFtp(FileName, CreateTime, Folder, Records, DlTime) values ( ?, ?,?,?,?)";
var connectionString = ConfigurationManager.ConnectionStrings["vfpConnectionString"].ConnectionString;
using (var connection = new OleDbConnection(connectionString))
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = "SET NULL OFF";
command.ExecuteNonQuery();
}
using (var command = connection.CreateCommand())
{
command.CommandText = lcsql;
command.Parameters.AddWithValue("FileName", fileDetail.FileName);
command.Parameters.AddWithValue("CreateTime", fileDetail.FileDate);
command.Parameters.AddWithValue("Folder", fileDetail.Folder);
command.Parameters.AddWithValue("Records", fileDetail.Records);
command.Parameters.AddWithValue("DlTime", fileDetail.DownloadTime);
//connection.Open();
var retval = command.ExecuteNonQuery();
var success = (retval == 1);
return success;
}
}
}
public ReservationForm()
{
reservationConn = new OleDbConnection(connString);
drawPanel();
drawPlan();
InitializeComponent();
}
protected void Button1_Click(object sender, EventArgs e)
{
int lgflg = 0;
OleDbConnection conn = new OleDbConnection(ConfigurationSettings.AppSettings["classDB"]);
OleDbCommand cmd = new OleDbCommand("SELECT * FROM student WHERE studentpassword = '******' AND email = '" + userName.Text + "'", conn);
conn.Open();
OleDbDataReader myReader = cmd.ExecuteReader();
while (myReader.Read())
{
lgflg = 1;
Session.Add("fname", myReader["Contact_name"]);
Session.Add("address", myReader["address"]);
Session.Add("city", myReader["city"]);
Session.Add("state", myReader["state"]);
Session.Add("zipcode", myReader["zipcode"]);
}
myReader.Close();
conn.Close();
if (lgflg == 1)
{
Response.Write(Session["fname"]);
}
else
{
error.Visible = true;
}
}
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();
}
}
protected void btnAddBag_Click(object sender, EventArgs e)
{
try
{
OleDbConnectionStringBuilder sb = new OleDbConnectionStringBuilder();
sb.Provider = "Microsoft.ACE.OLEDB.12.0";
sb.DataSource = Server.MapPath("/vedb01/uploads/db1.accdb");
OleDbConnection conn = new OleDbConnection(sb.ConnectionString);
conn.Open();
string insertQuery = "insert into Bag ( [BagName], [Supplier], [Color], [Category], [Price], [Description]) values (@name ,@supplier ,@color ,@category ,@price ,@description)";
OleDbCommand com = new OleDbCommand(insertQuery, conn);
com.Parameters.AddWithValue("@name", txtBagName.Text);
com.Parameters.AddWithValue("@supplier", txtSupplier.Text);
com.Parameters.AddWithValue("@color", txtColor.Text);
com.Parameters.AddWithValue("@category", txtCategory.Text);
com.Parameters.AddWithValue("@price", txtPrice.Text);
com.Parameters.AddWithValue("@description", txtDescription.Text);
com.ExecuteNonQuery();
lblMessage.Text = "The bag was added successfully " + txtBagName.Text + " !";
conn.Close();
}
catch (Exception ex)
{
Response.Write("Error: " + ex.ToString());
lblMessage.Text = "Error" + txtBagName.Text + " !";
}
}
public IDataReader ExecuteDataReader(string connectionString, string query)
{
_connection = new OleDbConnection(connectionString);
_connection.Open();
var command = new OleDbCommand(query, _connection);
return command.ExecuteReader();
}
public DataTable sepetcevir(string uyeID, string map)
{
OleDbConnection cnn = new OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0; Data Source=" + map);
cnn.Open();
OleDbCommand cmd = new OleDbCommand("select sepet from uyeler where uyeid=" + uyeID, cnn);
OleDbDataReader rdr = cmd.ExecuteReader();
rdr.Read();
string[] urunler = rdr[0].ToString().Split(',');
DataTable dt = new DataTable();
dt.Columns.AddRange(new DataColumn[3] { new DataColumn("fid"), new DataColumn("aciklama"), new DataColumn("fiyat") });
if (rdr[0].ToString() != "")
{
foreach (string item in urunler)
{
cmd.Dispose();
string fid = item;
cmd = new OleDbCommand("select * from fotolar where fot_id=" + fid, cnn);
OleDbDataReader rdr2 = cmd.ExecuteReader();
rdr2.Read();
string ack = rdr2[5].ToString();
string fiyat = rdr2[6].ToString();
dt.Rows.Add(fid, ack, fiyat);
}
}
cnn.Close();
return dt;
}
/// <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 void update(string query)
{
OleDbConnection connection = new OleDbConnection();
string executable = System.Reflection.Assembly.GetExecutingAssembly().Location;
string path = (System.IO.Path.GetDirectoryName(executable));
AppDomain.CurrentDomain.SetData("DataDirectory", path);
connection.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|/LMS.accdb";
OleDbCommand command;
command = connection.CreateCommand();
try
{
command.CommandText = query;
command.CommandType = CommandType.Text;
connection.Open();
//SqlCommand comm = new SqlCommand(query, connection);
command.ExecuteNonQuery();
}
catch (Exception)
{
}
finally
{
if (connection != null)
connection.Close();
}
}
protected void Page_Load(object sender, EventArgs e)
{
System.Data.OleDb.OleDbConnection Conn = new System.Data.OleDb.OleDbConnection();
Conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("app_data/productsdb.mdb");
Conn.Open();
//Response.Write(Conn.State);
System.Data.OleDb.OleDbCommand Comm = new System.Data.OleDb.OleDbCommand();
System.Data.OleDb.OleDbDataReader dr;
Comm.Connection = Conn;
Comm.CommandText = "select productid, productname, productiondescription, price, qoh, imagelocation from products";
if (Request.Params["categoryid"] != null && Request.Params["categoryid"].Length >0)
{
Comm.CommandText += " where categoryid = ?" ;
Comm.Parameters.AddWithValue("anything", Request.Params["categoryid"].ToString());
}
dr = Comm.ExecuteReader();
bool firstone = true;
Response.Write("{\"product\":[");
while (dr.Read())
{
if (!firstone)
{
Response.Write(",");
}
Response.Write(dr[0].ToString());
firstone = false;
}
Response.Write("]}");
}
/**
* Constructor that gets the connection to the database and Car information
*
* @param V VIN of the Car
* @param T Type of the Car
* @param cn Connection to the database
*/
public MakeTires(string S, string T, string TS, OleDbConnection cn)
{
this.SerialNumber = S;
this.Type = T;
this.TireSize = TS;
this.cn = cn;
}
// Gets all the countries that start with the typed text, taking paging into account
protected DataTable GetCountries(string text, int startOffset, int numberOfItems)
{
OleDbConnection myConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("../App_Data/continent.mdb"));
myConn.Open();
string whereClause = " WHERE CountryName LIKE @CountryName";
string sortExpression = " ORDER BY CountryName";
string commandText = "SELECT TOP " + numberOfItems + " CountryID, CountryName FROM Country";
commandText += whereClause;
if (startOffset != 0)
{
commandText += " AND CountryID NOT IN (SELECT TOP " + startOffset + " CountryID FROM Country";
commandText += whereClause + sortExpression + ")";
}
commandText += sortExpression;
OleDbCommand myComm = new OleDbCommand(commandText, myConn);
myComm.Parameters.Add("@CountryName", OleDbType.VarChar).Value = text + '%';
OleDbDataAdapter da = new OleDbDataAdapter();
DataSet ds = new DataSet();
da.SelectCommand = myComm;
da.Fill(ds, "Country");
myConn.Close();
return ds.Tables[0];
}
//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 void updateProductPrice(ProductInBag Product)
{
OleDbConnection myConn = new OleDbConnection(Connstring.getConnectionString());
OleDbCommand myCmd;
OleDbDataAdapter myDataAdapter = new OleDbDataAdapter();
try
{
myCmd = new OleDbCommand("UpdateUnitPrice", myConn);
myCmd.CommandType = CommandType.StoredProcedure;
OleDbParameter objParam;
objParam = myCmd.Parameters.Add("@UnitPrice", OleDbType.Decimal);
objParam.Direction = ParameterDirection.Input;
objParam.Value = Product.Price;
objParam = myCmd.Parameters.Add("@ProductID", OleDbType.Integer);
objParam.Direction = ParameterDirection.Input;
objParam.Value = Product.ProdID;
myConn.Open();
myCmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
myConn.Close();
}
}
public override List<DomainAlias> GetDomainAliases(string domainName)
{
var _tmp = new List<DomainAlias>();
using (OleDbConnection _conn = new OleDbConnection(Settings.Default.connectionString))
{
_conn.Open();
using (OleDbCommand _cmd = new OleDbCommand(@"SELECT domain_aliases.name AS alias, domains.name AS [domain], domain_aliases.status
FROM (domain_aliases INNER JOIN
domains ON domain_aliases.dom_id = domains.id)
WHERE (domain_aliases.status = 0) AND (domains.name = ?)", _conn))
{
_cmd.CommandType = CommandType.Text;
_cmd.Parameters.AddWithValue("NAME", domainName);
using (OleDbDataReader _read = _cmd.ExecuteReader())
{
while (_read.Read())
{
var _d = new DomainAlias();
_d.Domain = _read["domain"].ToString();
_d.Alias = _read["alias"].ToString();
_tmp.Add(_d);
}
}
}
_conn.Close();
}
return _tmp;
}
private void addToDatabase(string sql)
{
OleDbConnection db = null;
try
{
db = new OleDbConnection();
db.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data source=" + mFileName;
db.Open();
OleDbCommand command = new OleDbCommand(sql, db);
command.ExecuteNonQuery();
}
catch (Exception ex)
{
showErrorMessage(ex.Message);
}
finally
{
if (db != null)
{
db.Close();
}
}
}
private void fill_form(string p_id)
{
String strQuery = String.Empty;
OleDbConnection sqlConn = new OleDbConnection();
OleDbCommand sqlComm = new OleDbCommand();
OleDbDataReader sqlRead;// = new OleDbDataReader();
DateTime strReturn = DateTime.Now;
//
sqlConn.ConnectionString = PCPUB.m_oledb_connection.ToString();
sqlConn.Open();
//
strQuery = String.Empty;
strQuery += " SELECT";
strQuery += " [yarn_count_id],";
strQuery += " [yarn_count_name]";
strQuery += " FROM [tis_yarn_count]";
strQuery += " WHERE [yarn_count_id] = '" + p_id + "'";
//
sqlComm.Connection = sqlConn;
sqlComm.CommandText = strQuery.ToString();
sqlRead = sqlComm.ExecuteReader();
//
if (sqlRead.Read())
{
txt_yarn_count_id.Text = sqlRead["yarn_count_id"].ToString();
txt_yarn_count_name.Text = sqlRead["yarn_count_name"].ToString();
}
//
sqlRead.Close();
sqlConn.Close();
sqlRead.Dispose();
sqlComm.Dispose();
sqlConn.Dispose();
}
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 btn_Browser_Click(object sender, EventArgs e)
{
OpenFileDialog P_open = new OpenFileDialog();//创建打开文件对话框对象
P_open.Filter = "文本文件|*.txt|所有文件|*.*";//设置筛选字符串
if (P_open.ShowDialog() == DialogResult.OK)
{
txt_Path.Text = P_open.FileName;//显示文件路径
string conn = string.Format(//创建连接字符串
@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=text",
P_open.FileName.Substring(0,P_open.FileName.LastIndexOf(@"\")));
OleDbConnection P_OleDbConnection = new OleDbConnection(conn);//创建连接对象
try
{
P_OleDbConnection.Open();//打开连接
OleDbCommand cmd = new OleDbCommand(//创建命令对象
string.Format("select * from {0}",
P_open.FileName.Substring(P_open.FileName.LastIndexOf(@"\"),
P_open.FileName.Length - P_open.FileName.LastIndexOf(@"\"))),
P_OleDbConnection);
OleDbDataReader oda = cmd.ExecuteReader();//得到数据读取器对象
while (oda.Read())
{
txt_Message.Text += oda[0].ToString();//得到文本文件中的字符串
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);//弹出消息对话框
}
finally
{
P_OleDbConnection.Close();//关闭连接
}
}
}
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 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;
}
}
protected void Button1_Click(object sender, EventArgs e)
{
try
{
OleDbConnectionStringBuilder sb = new OleDbConnectionStringBuilder();
sb.Provider = "Microsoft.ACE.OLEDB.12.0";
sb.DataSource = Server.MapPath("/vedb01/uploads/db1.accdb");
OleDbConnection conn = new OleDbConnection(sb.ConnectionString);
conn.Open();
string updateQuery = "UPDATE Bag SET BagName=@name, Supplier=@supplier, Color=@color, Category=@category, Price=@price, Description=@description WHERE BagID= @ID";
OleDbCommand com = new OleDbCommand(updateQuery, conn);
com.Parameters.AddWithValue("@ID", txtBagId.Text);
com.Parameters.AddWithValue("@name", txtBagName.Text);
com.Parameters.AddWithValue("@supplier", txtSupplier.Text);
com.Parameters.AddWithValue("@color", txtColor.Text);
com.Parameters.AddWithValue("@category", txtCategory.Text);
com.Parameters.AddWithValue("@price", txtPrice.Text);
com.Parameters.AddWithValue("@description", txtDescription.Text);
com.ExecuteNonQuery();
lblMessage.Text = "The bag No " + txtBagId.Text +" was updated successfully " + txtBagName.Text + " !";
conn.Close();
}
catch (Exception ex)
{
Response.Write("Error: " + ex.ToString());
lblMessage.Text = "Error !";
}
}
public string LoadContain()
{
if (Request.QueryString["CourseId"] == null)
{
return string.Empty;
}
string ThePath = string.Empty;
string RetData = string.Empty;
using (OleDbConnection Con = new OleDbConnection(constr))
{
OleDbCommand cmd = new OleDbCommand(String.Format("SELECT TOP 1 DataPath FROM CoursenotimeDataPath WHERE CourseId = {0}", Request.QueryString["CourseId"]), Con);
try
{
Con.Open();
ThePath = cmd.ExecuteScalar().ToString();
//if (ThePath != string.Empty)
// ThePath = MapPath(DB.CourseNoTimeFileDir + ThePath);
ThePath = DB.CourseNoTimeFileDir + ThePath;
TextReader TR = new StreamReader(ThePath);
RetData = TR.ReadToEnd();
TR.Close();
TR.Dispose();
}
catch (Exception ex)
{
RetData = ex.Message;
}
Con.Close();
}
return HttpUtility.HtmlDecode(RetData);
}
/// <summary>
/// 执行多条SQL语句，实现数据库事务。
/// </summary>
/// <param name="SQLStringList">多条SQL语句</param>
public static void ExecuteSqlTran(ArrayList SQLStringList)
{
using (OleDbConnection conn = new OleDbConnection(connectionString))
{
conn.Open();
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = conn;
OleDbTransaction tx = conn.BeginTransaction();
cmd.Transaction = tx;
try
{
for (int n = 0; n < SQLStringList.Count; n++)
{
string strsql = SQLStringList[n].ToString();
if (strsql.Trim().Length > 1)
{
cmd.CommandText = strsql;
cmd.ExecuteNonQuery();
}
}
tx.Commit();
}
catch (System.Data.OleDb.OleDbException E)
{
tx.Rollback();
throw new Exception(E.Message);
}
}
}
public List<MonthlyExpenseReportItem> cercaMovimentiCategoria(int idCat)
{
OleDbConnection conn = new OleDbConnection(Properties.Settings.Default.scadenzettiDbConnectionString);
string queryString = "SELECT * FROM QueryReportCategoria";
OleDbCommand cmd = new OleDbCommand(queryString, conn);
cmd.Parameters.Add("idCat", OleDbType.Integer).Value = idCat;
conn.Open();
OleDbDataReader reader = cmd.ExecuteReader();
List<MonthlyExpenseReportItem> list = new List<MonthlyExpenseReportItem>();
while (reader.Read())
{
MonthlyExpenseReportItem mov = new MonthlyExpenseReportItem();
mov.Scadenza = DateTime.Parse(reader[0].ToString());
mov.Importo = decimal.Parse(reader[1].ToString());
mov.Debitore = reader[2].ToString();
mov.Creditore = reader[3].ToString();
mov.Causale = reader[4].ToString();
mov.Tipo = reader[5].ToString();
mov.Ultimato = bool.Parse(reader[6].ToString());
list.Add(mov);
}
reader.Close();
conn.Close();
return list;
}
public void BeginTransaction_Connection_Closed ()
{
OleDbConnection cn = new OleDbConnection ();
try {
cn.BeginTransaction ();
Assert.Fail ("#A1");
} catch (InvalidOperationException ex) {
// Invalid operation. The connection is closed
Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#A2");
Assert.IsNull (ex.InnerException, "#A3");
Assert.IsNotNull (ex.Message, "#A4");
}
try {
cn.BeginTransaction ((IsolationLevel) 666);
Assert.Fail ("#B1");
} catch (InvalidOperationException ex) {
// Invalid operation. The connection is closed
Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#B2");
Assert.IsNull (ex.InnerException, "#B3");
Assert.IsNotNull (ex.Message, "#B4");
}
try {
cn.BeginTransaction (IsolationLevel.Serializable);
Assert.Fail ("#C1");
} catch (InvalidOperationException ex) {
// Invalid operation. The connection is closed
Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#C2");
Assert.IsNull (ex.InnerException, "#C3");
Assert.IsNotNull (ex.Message, "#C4");
}
}
protected void Page_Load(object sender, System.EventArgs e)
{
// resolve the address to the Access database
string fileNameString = this.MapPath(".");
fileNameString += "..\\..\\..\\..\\data\\chartdata.mdb";
// initialize a connection string
string myConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileNameString;
// define the database query
string mySelectQuery="SELECT GrossSales FROM SALES WHERE QuarterEnding < #01/01/2002#;";
// create a database connection object using the connection string
OleDbConnection myConnection = new OleDbConnection(myConnectionString);
// create a database command on the connection using query
OleDbCommand myCommand = new OleDbCommand(mySelectQuery, myConnection);
// open the connection
myCommand.Connection.Open();
// create a database reader
OleDbDataReader myReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
// since the reader implements and IEnumerable, pass the reader directly into
// the DataBind method with the name of the Column selected in the query
Chart1.Series["Default"].Points.DataBindY(myReader, "GrossSales");
// close the reader and the connection
myReader.Close();
myConnection.Close();
}
static void Main(string[] args)
{
var connection = new oleDB.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=e:\\new_DB.mdb");
connection.Open();
var command = new oleDB.OleDbCommand("INSERT INTO [Phones] (fio, phone) VALUES ('Света-Х', '521-61-41')");
command.Connection = connection;
command.ExecuteNonQuery();
MessageBox.Show("В таблицу 'Phones' добавлена запись");
connection.Close();
}
/// <summary>
/// Class constructor, to retrieve data from .xls data source
/// </summary>
/// <param name="strXlsFile">The .xls file to be connected.
/// This file should exist and it can be writable.</param>
public XlsDBConnector(String strXlsFile)
{
// Validate the specified
if (!ValidateFile(strXlsFile))
{
throw new ArgumentException("The specified file doesn't exists or has readonly attribute.", strXlsFile);
}
// establish a connection to the data source.
m_connectStr = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source = \"" + strXlsFile +
"\"; Extended Properties = \"Excel 8.0;HDR=YES;\"";
// create the .xls connection
m_objConn = new System.Data.OleDb.OleDbConnection(m_connectStr);
m_objConn.Open();
}
public Boolean DaliPostoiGrupa()
{
String komanda = "EXECUTE spProveriGrupa";
OleDbConnection OleCn = new System.Data.OleDb.OleDbConnection(konekcija);
OleDbCommand OleCm = new System.Data.OleDb.OleDbCommand(komanda, OleCn);
OleDbDataReader dt;
if (groupTextBox.Enabled)
{
OleCm.Parameters.Add(new OleDbParameter("@Code", groupTextBox.Text.Trim()));
}
else
{
OleCm.Parameters.Add(new OleDbParameter("@Code", novoImeTextBox.Text.Trim()));
}
int rezultat = -1;
try
{
OleCn.Open();
dt = OleCm.ExecuteReader();
if (dt.Read())
{
rezultat = Convert.ToInt32(dt["Rezultat"].ToString());
}
dt.Close();
OleCn.Close();
if (rezultat >= 1)
{
OleCn.Close();
return(true);
}
else if (rezultat == 0)
{
OleCn.Close();
return(false);
}
}
catch (Exception ex)
{
return(false);
}
return(false);
}
public void queryDatabase(string sqlString, customQuery findResult, System.Data.OleDb.OleDbConnection conn)
{
OleDbCommand Com = new OleDbCommand();
Com.CommandText = sqlString;
Com.Connection = conn;
OleDbDataReader objDataReader = null;
objDataReader = Com.ExecuteReader();
if (objDataReader == null)
{
return;
}
while (objDataReader.Read())
{
findResult.SetIndex(objDataReader["PersonID"].ToString());
}
objDataReader.Close();
string whereClause = "(MyNumber='" + findResult.GetIndex(0) + "') ";
for (int i = 1; i < findResult.GetIndexCount(); i++)
{
whereClause = whereClause + "OR (MyNumber='" + findResult.GetIndex(i) + "') ";
}
string localsqlString = "SELECT PersonName FROM tblPeeps WHERE (" + whereClause + ")";
OleDbCommand Com2 = new OleDbCommand();
Com2.CommandText = localsqlString;
Com2.Connection = conn;
OleDbDataReader objDataReader2 = null;
objDataReader2 = Com2.ExecuteReader();
if (objDataReader2 == null)
{
return;
}
while (objDataReader2.Read())
{
findResult.SetName(objDataReader2["PersonName"].ToString());
}
objDataReader2.Close();
}
private void infoBtn_Click(object sender, RoutedEventArgs e)
{
string filepath = (string)fileLbl.Content;
if (filepath == "")
{
System.Windows.MessageBox.Show("File path required!");
return;
}
FileInfo file = new FileInfo(filepath);
if (!file.Exists)
{
System.Windows.MessageBox.Show("File doesn't exist!");
return;
}
string sheetName = locationTxt.Text;
if (sheetName == "")
{
System.Windows.MessageBox.Show("Sheet name required!");
return;
}
string connectionString = getConnectionStr(filepath, file.Extension);
System.Data.DataTable dt = new System.Data.DataTable();
using (System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(connectionString))
{
string sql = string.Format("SELECT * FROM [{0}$]", sheetName);
System.Data.OleDb.OleDbDataAdapter apt = new System.Data.OleDb.OleDbDataAdapter(sql, conn);
try
{
apt.Fill(dt);
}
catch (Exception)
{
System.Windows.MessageBox.Show("no such sheet!");
return;
}
dt.TableName = sheetName;
grdData.ItemsSource = dt.DefaultView;
}
openedFilePath = filepath;
openedFileExtention = file.Extension;
}
//2018.02.26 Arthur 新增 取得OleDb連線
//取得OleDb連線
public System.Data.OleDb.OleDbConnection GetOleDBcon(string FileName)
{
string M_str_sqlcon = "";
string sFileExtension = Path.GetExtension(FileName);
if (sFileExtension.ToUpper() == ".XLS")
{
M_str_sqlcon =
"Data Source=" + FileName + ";" +
"Provider=Microsoft.Jet.OLEDB.4.0;" +
"Extended Properties='Excel 8.0;" +
"HDR=No;" +
"IMEX=1;'";
}
else if (sFileExtension.ToUpper() == ".XLSX")
{
M_str_sqlcon =
"Data Source=" + FileName + ";" +
"Provider=Microsoft.ACE.OLEDB.12.0;" +
"Extended Properties='Excel 12.0;" +
"HDR=YES;" +
"IMEX=1;'";
}
else if (sFileExtension.ToUpper() == ".CSV")
{
string temp = Path.GetDirectoryName(FileName);
M_str_sqlcon =
"Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + temp + ";" +
"Extended Properties='Text;HDR=NO;IMEX=1'";
}
System.Data.OleDb.OleDbConnection OleDbCon = new System.Data.OleDb.OleDbConnection(M_str_sqlcon);
return(OleDbCon);
}
private void search(String file)
{
string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= '" + file + "';Extended Properties='Excel 8.0;HDR=Yes;'";
string query = "select * from [PSC_ECs$A4:X2000] WHERE F1 <> ''";
db.OleDbConnection cn = new db.OleDbConnection();
cn.ConnectionString = strConn;
cn.Open();
db.OleDbDataAdapter da = new db.OleDbDataAdapter(query, cn);
ds = new DataSet();
da.Fill(ds, "tbl");
dtgData.DataSource = ds.Tables["tbl"];
cn.Close();
}
public TTopic createTopic(TUser tUserStarter, TCourse tCourse, string title)
{
//--Data Base Access Variables--
System.Data.OleDb.OleDbConnection dbConnection = new System.Data.OleDb.OleDbConnection(connectionString);
System.Data.OleDb.OleDbCommand dbCommand = new OleDbCommand();
dbCommand.Connection = dbConnection;
System.Data.OleDb.OleDbDataReader dbDataReader;
System.Data.OleDb.OleDbTransaction dbTransaction;
//-----------------------------
dbConnection.Close();
dbConnection.Open();
dbTransaction = dbConnection.BeginTransaction();
dbCommand.Transaction = dbTransaction;
TTopic tTopic = new TTopic(tCourse, title);
try
{
//Begin Transaction
tTopic.startDateTime = DateTime.Now;
tTopic.starterTeacher = tUserStarter;
dbCommand.CommandText = "INSERT INTO Topics (course_id, group_id, title, startDateTime, starterTeacher) VALUES('" + tTopic.tCourse.id + "', '" + tTopic.tCourse.groupId + "', '" + tTopic.title + "', '" + tTopic.startDateTime.ToString() + "', '" + tUserStarter.id + "')";
dbCommand.ExecuteNonQuery();
dbCommand.CommandText = "SELECT * FROM Topics WHERE (course_id = '" + tTopic.tCourse.id + "') AND (group_id = '" + tTopic.tCourse.groupId + "') AND (title = '" + tTopic.title + "') AND (startDateTime = '" + tTopic.startDateTime.ToString() + "')";
dbDataReader = dbCommand.ExecuteReader();
dbDataReader.Read();
tTopic.id = Convert.ToInt32(dbDataReader["id"]);
dbDataReader.Close();
dbTransaction.Commit();
//End Transaction
}
catch
{
dbTransaction.Rollback();
dbCommand.Transaction = null;
dbTransaction = null;
dbConnection.Close();
return(null);
}
dbCommand.Transaction = null;
dbTransaction = null;
dbDataReader.Close();
dbConnection.Close();
return(tTopic);
}
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();
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);
}
}
}
catch (Exception ex)
{
throw ex;
}
return(null);
}
public static System.Data.OleDb.OleDbDataReader ExecuteReader(string strSQL)
{
System.Data.OleDb.OleDbConnection oleDbConnection = new System.Data.OleDb.OleDbConnection(DbHelperOleDb.connectionString);
System.Data.OleDb.OleDbCommand oleDbCommand = new System.Data.OleDb.OleDbCommand(strSQL, oleDbConnection);
System.Data.OleDb.OleDbDataReader result;
try
{
oleDbConnection.Open();
System.Data.OleDb.OleDbDataReader oleDbDataReader = oleDbCommand.ExecuteReader();
result = oleDbDataReader;
}
catch (System.Data.OleDb.OleDbException ex)
{
throw new Exception(ex.Message);
}
return(result);
}
protected void ListBox4_SelectedIndexChanged(object sender, EventArgs e)
{
ListBox1.ClearSelection();
ListBox2.ClearSelection();
ListBox3.ClearSelection();
ListBox5.ClearSelection();
ListBox6.Items.Clear();
String selection = ListBox4.SelectedItem.Text;
System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection();
conn.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;" +
@"Data source= C:\Users\OK\Documents\Ecafe.accdb";
conn.Open();
String my_querry1 = "select Item from Items where Type = '" + selection + "'";
OleDbCommand cmd1 = new OleDbCommand(my_querry1, conn);
var dr1 = cmd1.ExecuteReader();
// MessageBox.Show("Query executed");
while (dr1.Read())
{
ListBox6.Items.Add(dr1[0].ToString());
//MessageBox.Show(dr1[1].ToString());
}
Label2.Text = selection;
ListBox6.Visible = true;
ListBox7.Visible = true;
ListBox8.Visible = true;
ListBox9.Visible = true;
Label1.Visible = true;
Label2.Visible = true;
Label3.Visible = true;
Label5.Visible = true;
Label6.Visible = true;
Label7.Visible = true;
Label8.Visible = true;
Label9.Visible = true;
Label10.Visible = true;
TextBox2.Visible = true;
Button1.Visible = true;
Button2.Visible = true;
}
private void linkLabel1_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
{
string strExcelPathName, Table = "";
OpenFileDialog openDialog = new OpenFileDialog();
openDialog.Title = "Select file";
openDialog.InitialDirectory = @"c:\";
openDialog.Filter = "Excel Sheet(*.xlsx)|*.xlsx|All Files(*.*)|*.*";
openDialog.FilterIndex = 1;
openDialog.RestoreDirectory = true;
try
{
if (openDialog.ShowDialog() == DialogResult.OK)
{
if (openDialog.FileName != "")
{
strExcelPathName = openDialog.FileName;
//TxtPath.Text = strExcelPathName.ToString();
// comboBox1.DataSource = GetSheetNames(openDialog.FileName);
System.Data.OleDb.OleDbConnection MyConnection;
System.Data.DataSet DtSet;
System.Data.OleDb.OleDbDataAdapter MyCommand;
MyConnection = new System.Data.OleDb.OleDbConnection(@"provider=Microsoft.Jet.OLEDB.4.0;;Data Source=" + strExcelPathName + ";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);
dataGridView1.DataSource = DtSet.Tables[0];
MyConnection.Close();
//return ds.Tables[0];
}
else
{
MessageBox.Show("chose Excel sheet path..", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
}
finally
{
}
}
static void TestLocalVpaModel()
{
//string databaseName = "Contoso";
//const string serverName = @".\tab17";
// string databaseName = "CalculationGroups_Currency";
// const string serverName = @".\tab19";
string databaseName = "338e5409-5010-4864-8226-722165625dd5";
const string serverName = @"localhost:49851";
var connStr = $"Provider=MSOLAP;Data Source={serverName};Initial Catalog={databaseName};";
var conn = new System.Data.OleDb.OleDbConnection(connStr);
Dax.Metadata.Model m = new Dax.Metadata.Model();
Dax.Metadata.Extractor.DmvExtractor.PopulateFromDmv(m, conn, serverName, databaseName, "Test", "0.1");
Dax.Metadata.Extractor.StatExtractor.UpdateStatisticsModel(m, conn, 10);
DumpRelationships(m);
}
static void TestLocalVpaModel()
{
//string databaseName = "Contoso";
//const string serverName = @".\tab17";
// string databaseName = "CalculationGroups_Currency";
// const string serverName = @".\tab19";
string databaseName = "32f0add7-3e4c-4fd7-8e26-3961f25d7c5a";
const string serverName = @"localhost:60812";
var connStr = $"Provider=MSOLAP;Data Source={serverName};Initial Catalog={databaseName};";
var conn = new System.Data.OleDb.OleDbConnection(connStr);
Dax.Metadata.Model m = new Dax.Metadata.Model();
Dax.Metadata.Extractor.DmvExtractor.PopulateFromDmv(m, conn, serverName, databaseName, "Test", "0.1");
Dax.Metadata.Extractor.StatExtractor.UpdateStatisticsModel(m, conn, 10);
DumpRelationships(m);
}
//?要怎麼加try catch比較好呢
//使用方法
// System.Data.DataTable dt = new func_excel_read_ole().CreateDataSource(@"Z:\cadmen\服務部\客戶需求單\YFY\ERP標準書(SAP).xls;", "內容物重量");
// for (int i = 0; i < dt.Rows.Count; i++)
// {
// string msg = dt.Rows[i]["內容物"].ToString() + "--" + dt.Rows[i]["重量"].ToString();
// MessageBox.Show(msg);
// }
public DataTable CreateDataSourceTop1(string filePath, string sheetName)
{
DataSet myDataSet = new DataSet();
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
@"Data Source={0};" +
"Extended Properties='Excel 8.0;IMEX=1;HDR=yes;'";
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 top 1 * FROM [" + sheetName + "$]", strConn);
Adapter.Fill(myDataSet);
return(myDataSet.Tables[0]);
}
//更新項目明細
private void updata_btn_Click(object sender, EventArgs e)
{
// === 對 Access 資料庫下SQL語法 ===
//// Transact-SQL 陳述式
String strSQL = "UPDATE [WindowsTarget] SET TargetName = '" + ShowTargetName.Text + "' ,TargetIP = '" + ShowTargetIP.Text + "' ,TargetDomain = '" + ShowTargetDomain.Text + "' ,TargetUser = '******' ,TargetPassword = '******' ,TargetAlert = '" + ShowTargetAlert.Text + "' , TargetRes='" + ShowTargetRes.Text + "' , TargetWarning='" + ShowTargetWarning.Text + "', TargetNonPageCheck = " + ShowNonPageCheckBox.Checked + ", TargetTimeCheck = " + ShowTimeCheckBox.Checked + " WHERE id=" + int.Parse(ShowTargetID.Text);
System.Data.OleDb.OleDbConnection oleConn =
new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0;Data Source=FreeSpaceEyesDB.mdb");
//// 開啟資料庫連接。
oleConn.Open();
//// OleDbCommand (String, OleDbConnection) : 使用查詢的文字和 OleDbConnection，初始化 OleDbCommand 類別的新執行個體。
System.Data.OleDb.OleDbCommand oleCmd =
new System.Data.OleDb.OleDbCommand(strSQL, oleConn);
oleCmd.ExecuteNonQuery();
//// 關閉資料庫連接。
oleConn.Close();
}
public DataSet Listar(string OLE)
{
try
{
Dbcon = new System.Data.OleDb.OleDbConnection(StringConexao);
Dbcon.Open();
DBDA = new System.Data.OleDb.OleDbDataAdapter(OLE, Dbcon);
DBDA.Fill(ds);
}
finally
{
Dbcon.Close();
}
return(ds);
}
//Sing : Login for Front-end.
//Function to authenticate a user based on credentials entered.
//Entering username: admin and password: admin will return true.
public static bool authenticateUser(string username, string password)
{
try // in case database connection fails.
{
//Sing : login database declarations
System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection();
OleDbDataAdapter ad;
DataTable dtable = new DataTable();
OleDbCommand command = new OleDbCommand();
//Sing : Login database connection
connection.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=User.mdb;Jet OLEDB:Database Password=;";
command.Connection = connection;
//Sing:Opening a connection to the database
connection.Open();
//Sing: defining the query
ad = new OleDbDataAdapter("select * from Accounts where username ='******'and password='******'", connection);
//Filling the table adaptor
ad.Fill(dtable);
//If statement for log in authenticaion - Checks if username and password is present in the Accounts table. Also checks whether admin details have been entered.
if (dtable.Rows.Count <= 0)
{
//Details do not exist in the database
connection.Close();
return(false);
}
else if (dtable.Rows.Count > 0 && username == "admin" && password == "admin")
{
//Data exists in the database, therefore return true as admin credentials have been entered.
connection.Close();
return(true);
}
else
{
return(false);
}
}
catch (Exception)
{
return(false);
}
}
public System.Data.OleDb.OleDbConnection ConnectToAccess()
{
System.Data.OleDb.OleDbConnection conn = new
System.Data.OleDb.OleDbConnection();
conn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;" +
@"Data source= C:\Program Files\Imi Project\Pro-Dictionary\dizionario.mdb";
try
{
conn.Open();
// Insert code to process data.
}
catch (Exception ex)
{
MessageBox.Show("Failed to connect to data source");
}
return(conn);
}
public System.Data.DataTable GetWorksheet(string worksheetName)
{
if (!worksheetName.Contains("$"))
{
worksheetName = worksheetName + "$";
}
OleDbConnection con = new System.Data.OleDb.OleDbConnection(connectionString);
OleDbDataAdapter cmd = new System.Data.OleDb.OleDbDataAdapter(
"select * from [" + worksheetName + "]", con);
con.Open();
System.Data.DataSet excelDataSet = new DataSet();
cmd.Fill(excelDataSet);
con.Close();
return(excelDataSet.Tables[0]);
}
public void CSVToDataGridView(string filePath, DataGridView dgv)
{
char[] chr = new char[1] {
'\\'
};
string[] strs = filePath.Split(chr, StringSplitOptions.RemoveEmptyEntries);
string constr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath.Substring(0, filePath.Length - strs[strs.Length - 1].Length) + "\\;Extended Properties=\"text;HDR=yes;FMT=Delimited\"";
System.Data.OleDb.OleDbConnection con = new System.Data.OleDb.OleDbConnection(constr);
con.Open();
System.Data.OleDb.OleDbDataAdapter adapter = new System.Data.OleDb.OleDbDataAdapter("select * from [" + strs[strs.Length - 1] + "]", con);
DataTable dt = new DataTable();
adapter.Fill(dt);
con.Close();
dgv.DataSource = dt;
}
public void enterTopic(TUser tUser, TTopic tTopic)
{
//--Data Base Access Variables--
System.Data.OleDb.OleDbConnection dbConnection = new System.Data.OleDb.OleDbConnection(connectionString);
System.Data.OleDb.OleDbCommand dbCommand = new OleDbCommand();
dbCommand.Connection = dbConnection;
System.Data.OleDb.OleDbTransaction dbTransaction;
System.Data.OleDb.OleDbDataReader dbDataReader;
//-----------------------------
dbConnection.Close();
dbConnection.Open();
dbTransaction = dbConnection.BeginTransaction();
dbCommand.Transaction = dbTransaction;
try
{
//Begin Transaction
//Verify if the user is already on-line
dbCommand.CommandText = "SELECT * FROM User_Topic WHERE user_id = '" + tUser.id + "' AND finishDateTime IS NULL";
dbDataReader = dbCommand.ExecuteReader();
if (dbDataReader.HasRows)
{
dbDataReader.Close();
dbCommand.CommandText = "UPDATE User_Topic SET finishDateTime = '" + DateTime.Now.ToString() + "' WHERE user_id = '" + tUser.id + "' AND finishDateTime IS NULL";
dbCommand.ExecuteNonQuery();
}
dbDataReader.Close();
dbCommand.CommandText = "INSERT INTO User_Topic (user_id, topic_id, startDateTime) VALUES ('" + tUser.id + "', '" + tTopic.id + "', '" + DateTime.Now.ToString() + "')";
dbCommand.ExecuteNonQuery();
tUser.topic = tTopic;
dbTransaction.Commit();
//End Transaction
}
catch
{
dbTransaction.Rollback();
dbCommand.Transaction = null;
dbTransaction = null;
dbConnection.Close();
}
dbCommand.Transaction = null;
dbTransaction = null;
dbConnection.Close();
}
private void button1_Click(object sender, EventArgs e)
{
System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection();
conn.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;" +
@"Data source= C:\Users\OK\Documents\Library.accdb";
try
{
conn.Open();
String username = textBox1.Text.ToString();
String Paswrd = textBox2.Text.ToString();
String my_querry = "select * from tblMembers where UserName = '******' and Pasword = '" + Paswrd + "'";
OleDbCommand cmd = new OleDbCommand(my_querry, conn);
//OleDbDataReader dr = cmd.ExecuteReader();
var dr = cmd.ExecuteReader();
if (dr.Read() == true)
{
MessageBox.Show("Login Successful");
// MessageBox.Show(dr[0].ToString());
this.Hide();
//Form3 f3 = new Form3();
//f3.Show();
//this.Hide();
Form4 f4 = new Form4();
f4.Show();
}
else
{
MessageBox.Show("Invalid Credentials, Please Re-Enter");
}
}
catch (Exception ex)
{
MessageBox.Show("Failed due to" + ex.Message);
}
finally
{
conn.Close();
}
}
/// <summary>
/// using the OleDb to Delete DataTable
/// </summary>
/// <param name="Path">路径</param>
/// <param name="dt">DataTable</param>
/// <param name="HDR">HDR true:读取column false:column行作为内容读取</param>
public void DropTable(string sheetName, string Path, string HDR = "true")
{
string HDRStr = "YES";
if (!HDR.Equals("true"))
{
HDRStr = "NO";
}
string strCon = string.Empty;
FileInfo file = new FileInfo(Path);
string extension = file.Extension;
switch (extension)
{
case ".xls":
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Path + ";Extended Properties=Excel 8.0;HDR=" + HDRStr + ";";
break;
case ".xlsx":
strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Path + ";Extended Properties='Excel 12.0;HDR=" + HDRStr + ";IMEX=0;'";
break;
default:
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Path + ";Extended Properties='Excel 8.0;HDR=" + HDRStr + ";IMEX=0;'";
break;
}
using (System.Data.OleDb.OleDbConnection con = new System.Data.OleDb.OleDbConnection(strCon))
{
con.Open();
System.Data.OleDb.OleDbCommand cmd;
try
{
cmd = new System.Data.OleDb.OleDbCommand(string.Format("drop table {0}", sheetName), con); //覆盖文件时可能会出现Table 'Sheet1' already exists.所以这里先删除了一下
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
}
finally
{
con.Close();
}
}
}
/// <summary>
/// 读取Excel文件
/// </summary>
/// <param name="filepath">文件路径</param>
/// <returns>DataTable</returns>
public static DataTable ReadExcel(string filename, bool isHdR)
{
string hdr = "N0";
if (isHdR)
{
hdr = "YEX";
}
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=" + hdr + ";\"";
System.Data.OleDb.OleDbConnection conn = null;
System.Data.OleDb.OleDbCommand oledbCommd = null;
try
{
conn = new System.Data.OleDb.OleDbConnection();
conn.ConnectionString = connStr;
conn.Open();
DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
//判断连接Excel sheet名
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow dr = dt.Rows[i];
string sqlText = "select * from [" + dr["TABLE_NAME"] + "]";
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]);
}
//打开恢复数据库专用的数据连接,用master数据库
public string DBCreate(string str_Sql)
{
string myConnectionStr = "Provider=SQLOLEDB.1;Persist Security Info=False;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID="
+ ConfigurationSettings.AppSettings["WorkstationSet"]
+ ";Use Encryption for Data=False;Tag with column collation when possible=False;Initial Catalog=master"
+ ";Data Source=" + SystemInformation.ComputerName + "\\MSDEDH"
+ ";User ID=" + ConfigurationSettings.AppSettings["DatabaseUser"]
+ ";Password="******"DatabasePassword"];
myConnection = new System.Data.OleDb.OleDbConnection(myConnectionStr);
try
{
myConnection.Open();
}
catch (SqlException e)
{
string errorMessage = e.Message;
return(errorMessage);
}
if (HaveDB() == false)
{
myCommand = new System.Data.OleDb.OleDbCommand("CREATE DATABASE CityEduManage", myConnection);
myCommand.ExecuteNonQuery();
myCommand.Dispose();
}
//断开CityEduManage的一切连接
//myCommand = new SqlCommand(str_Sql,myConnection);
string str_Sql_DisConnect = "declare hcforeach cursor global for select 'kill '+rtrim(spid) from master.dbo.sysprocesses where dbid=db_id('"
+ ConfigurationSettings.AppSettings["Database"]
+ "') exec sp_msforeach_worker '?'";
myCommand = new System.Data.OleDb.OleDbCommand(str_Sql_DisConnect, myConnection);
myCommand.ExecuteNonQuery();
myCommand.Dispose();
myCommand = new System.Data.OleDb.OleDbCommand(str_Sql, myConnection);
myCommand.ExecuteNonQuery();
myCommand.Dispose();
myConnection.Close();
return("OK");
}
public void XLSX2Grid(GridControl gc)
{
OpenFileDialog choofdlog = new OpenFileDialog();
choofdlog.Filter = "Excel file (*.xlsx)|*.xlsx";
choofdlog.ShowDialog();
//string filePath = System.IO.Path.GetDirectoryName(choofdlog.FileName);
string filePath = choofdlog.FileName;
//XtraMessageBox.Show(filePath);
System.Data.OleDb.OleDbConnection MyConnection;
System.Data.DataSet DtSet;
System.Data.OleDb.OleDbDataAdapter MyCommand;
string conStr;
if (Path.GetExtension(filePath).ToLower().Trim() == ".xls" && Environment.Is64BitOperatingSystem == false)
{
conStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath +
";Extended Properties=\"Excel 8.0;HDR= Yes" +
";IMEX=0\"";
}
else
{
conStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath +
";Extended Properties=\"Excel 12.0;HDR= Yes" +
";IMEX=0\"";
}
MyConnection = new System.Data.OleDb.OleDbConnection(conStr);
//XtraMessageBox.Show(MyConnection.State.ToString());
if (choofdlog.ShowDialog() == DialogResult.OK)
{
MyConnection.Open();
MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [Sheet$]", MyConnection);
//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);
gc.DataSource = DtSet.Tables[0];
MyConnection.Close();
}
else
{
choofdlog.Dispose();
}
}
public static List <List <string> > GetTasks(string filename)
{
var path = ProductProperties.NetworkStoragePlace + filename;// System.IO.Path.GetFullPath(filename);
List <List <string> > result = new List <List <string> >();
if (DoFileExist(path))
{
try
{
System.Data.OleDb.OleDbConnection MyConnection;
System.Data.DataSet DtSet;
System.Data.OleDb.OleDbDataAdapter MyCommand;
MyConnection = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + path + "';Extended Properties=Excel 8.0;");
MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [" + ProductProperties.InputFileSheetName + "$]", MyConnection);
MyCommand.TableMappings.Add("Table", "TestTable");
DtSet = new System.Data.DataSet();
MyCommand.Fill(DtSet);
var rows = DtSet.Tables[0].Rows;
for (int i = 0; i < rows.Count; i++)
{
var a = rows[i].ItemArray;
if (!a.Any(x => x.GetType() == typeof(System.DBNull)))
{
result.Add(new List <string>()
{
(string)a[0], //CRK_NumerPelny
(string)a[1], //Ope_Kod
(string)a[2], //Knt_Kod
(string)a[3], //Kat_KodOgolny
//(string)a[4],//CRK_DataDok
Convert.ToString(Convert.ToInt32(a[5])), //CRK_CRKId
});
}
}
MyConnection.Close();
return(result);
}
catch (Exception ex)
{
}
}
return(null);
}
public int OutExcel()
{
DataTable dt = GetDataTable();
string FileName = Guid.NewGuid().ToString().Substring(8) + ".xlsx";
string sNewFullFile = "C:\\" + FileName;
string strConn = GetConnectionString(sNewFullFile);
System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(strConn);
OleDbCommand cmd = null;
try
{
conn.Open();
cmd = new OleDbCommand("create table [Sheet1]( [位置编码] Text,[关联位置] Text,[公司编号] Text,[计划工厂] Text,[维护工厂] Text,[工厂区域] Text,[计划员组] Text,[成本中心] Text,[位置类型] Text,[位置状态] Text,[开始日期] Text,[修改日期] Text,[业务范围] Text,[是否安装] Text,[包含删除] Text,[维护班组] Text)", conn);
cmd.ExecuteNonQuery();
for (int i = 0; i < dt.Rows.Count; i++)
{
var sRows = "INSERT INTO [Sheet1$] ([位置编码], [关联位置],[公司编号],[计划工厂],[维护工厂],[工厂区域],[计划员组],[成本中心],[位置类型],[位置状态],[开始日期],[修改日期],[业务范围],[是否安装],[包含删除],[维护班组]) VALUES (";
for (int j = 1; j < dt.Columns.Count; j++)
{
sRows += "'" + dt.Rows[i][j] + "',";
}
sRows = sRows.TrimEnd(',');
string strSQL = sRows + ")";
cmd = new OleDbCommand(strSQL, conn);
cmd.ExecuteNonQuery();
}
return(1);
}
catch (Exception er)
{
throw er;
}
finally
{
if (cmd != null)
{
cmd.Dispose();
}
conn.Dispose();
}
}
private void bt_load_Click(object sender, EventArgs e)
{
/*string cont = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= "+tb_import.Text+";Extend Properties=\"Excel 8.0;HDR=Yes;\";";
* OleDbConnection cone = new OleDbConnection(cont);
* OleDbDataAdapter adp = new OleDbDataAdapter("SELECT * FROM [" + tb_load.Text + "$]", cone);
* DataTable dt = new DataTable();
* dgv_siswa.DataSource = dt;*/
/*if (System.IO.File.Exists(tb_import.Text))
* {
* string constring = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};
* Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1;""", tb_import.Text);
* string query = String.Format("select * from [{0}$]", "Sheet1");
* OleDbDataAdapter adp = new OleDbDataAdapter(query, constring);
* DataSet ds = new DataSet();
* adp.Fill(ds);
* dgv_siswa.DataSource = ds.Tables[0];
* }
* else
* {
* MessageBox.Show("Tidak Ada File Yang Di Load");
* }*/
/*if (System.IO.File.Exists(tb_import.Text))
* {
* string connectionString = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=YES;IMEX=1;""", tb_import.Text);
*
* //string connectionString = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=YES;IMEX=1;""", txtPath.Text);
* string query = String.Format("select * from [{0}$]", tb_import);
* OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, connectionString);
* DataSet dataSet = new DataSet();
* dataAdapter.Fill(dataSet);
* //DataTable dtView = dataSet.Tables[0];
* dgv_siswa.DataSource = dataSet.Tables[0];
* }*/
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();
}
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;
}