//public string ExcelFile {private get; set; } public static DataTable ReadData(string excelFile) { if (!System.IO.File.Exists(excelFile)) return null; OleDbConnection excelConnection = new OleDbConnection(); excelConnection.ConnectionString = string.Format("Provider=Microsoft.Jet.OleDb.4.0;Data Source='{0}';Extended Properties='Excel 8.0;HDR=YES'", excelFile); excelConnection.Open(); DataTable dtSchema = excelConnection.GetSchema("Tables"); if (dtSchema.Rows.Count == 0) return null; string strTableName = dtSchema.Rows[0]["Table_Name"] as string; string strSQL = string.Format("select * from [{0}]", strTableName); OleDbCommand cmdSelect = excelConnection.CreateCommand(); cmdSelect.CommandText = strSQL; OleDbDataAdapter dbAdapter = new OleDbDataAdapter(cmdSelect); DataTable dtResult=new DataTable(); dbAdapter.Fill(dtResult); dbAdapter.Dispose(); excelConnection.Close(); excelConnection.Dispose(); return dtResult; }
private void button1_Click(object sender, EventArgs e) { dataGridView1.Rows.Clear(); string chaine = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:/Users/dhib/Desktop/Professeur.accdb"; OleDbConnection con = new OleDbConnection(chaine); OleDbCommand cmd = con.CreateCommand(); OleDbCommand cmd1 = con.CreateCommand(); cmd.CommandText = "select cin from assiste where id_module=" + label1.Text + " group by cin"; try { con.Open(); OleDbDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { //dataGridView1.Rows.Add(dr.GetValue(0), dr.GetValue(2), dr.GetValue(3), dr.GetValue(4), dr.GetValue(0)); cmd1.CommandText="select nom,prenom from etudiant where cin="+dr.GetValue(0); try { OleDbDataReader dr1 = cmd1.ExecuteReader(); dr1.Read(); dataGridView1.Rows.Add(dr.GetValue(0), dr1.GetValue(0), dr1.GetValue(1),0); } catch (Exception ex) { } } dr.Close(); con.Close(); } catch (Exception ex) { MessageBox.Show("Erreur : " + ex.Message); con.Close(); } }
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 void check_privillage() { string Select_SQL, Select_SQL2, Select_SQL3, Update_SQL1; OleDbConnection SqlConnection1 = new OleDbConnection(); SqlConnection1.ConnectionString = AccessDataSource1.ConnectionString; Select_SQL = "select module_name,page_name,user_name from module_user_mng,user_mng,module_mng where module_mng.module_id = module_user_mng.module_id and user_mng.user_id = module_user_mng.user_id and page_name='" + System.IO.Path.GetFileName(Request.PhysicalPath) + "' and user_name='" + Session["xsctintranet"] + "'"; SqlConnection1.Open(); OleDbCommand SqlCommand = SqlConnection1.CreateCommand(); SqlCommand.CommandText = Select_SQL; OleDbDataReader SqlDataReader = SqlCommand.ExecuteReader(); if (SqlDataReader.HasRows) { while (SqlDataReader.Read()) { OleDbCommand SqlCommand2 = SqlConnection1.CreateCommand(); Update_SQL1 = "insert into visit_mng (visit_module_name,visit_user_name,visit_date,visit_ip,visit_module_page) VALUES ('" + SqlDataReader.GetString(0) + "','" + SqlDataReader.GetString(2) + "','" + DateTime.Now.ToString() + "','" + Page.Request.UserHostAddress + "','" + SqlDataReader.GetString(1) + "')"; SqlCommand2.CommandText = Update_SQL1; SqlCommand2.ExecuteNonQuery(); } } else { OleDbCommand SqlCommand2 = SqlConnection1.CreateCommand(); Select_SQL2 = "select * from module_mng where page_name='" + System.IO.Path.GetFileName(Request.PhysicalPath) + "'"; SqlCommand2.CommandText = Select_SQL2; OleDbDataReader SqlDataReader2 = SqlCommand2.ExecuteReader(); if (SqlDataReader2.HasRows) { Response.Write("您没有访问该页面的权限!"); SqlDataReader2.Close(); SqlDataReader.Close(); SqlConnection1.Close(); Response.End(); } else { OleDbCommand SqlCommand3 = SqlConnection1.CreateCommand(); Select_SQL3 = "select * from user_mng where user_name='" + Session["xsctintranet"] + "'"; SqlCommand3.CommandText = Select_SQL3; OleDbDataReader SqlDataReader3 = SqlCommand3.ExecuteReader(); if (SqlDataReader3.HasRows) { } else { Response.Write("您没有访问该页面的权限!"); SqlDataReader3.Close(); SqlDataReader2.Close(); SqlDataReader.Close(); SqlConnection1.Close(); Response.End(); } SqlDataReader3.Close(); } SqlDataReader2.Close(); } SqlDataReader.Close(); SqlConnection1.Close(); }
private void button1_Click(object sender, EventArgs e) { string chaine = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:/Users/dhib/Desktop/Professeur.accdb"; OleDbConnection con = new OleDbConnection(chaine); OleDbCommand cmd = con.CreateCommand(); OleDbCommand cmd1 = con.CreateCommand(); cmd.CommandText = "Insert into etudiant values (" + Convert.ToInt32(txt_cin.Text) + ",'" + txt_nom.Text + "','" + txt_prenom.Text + "','" + comboBox1.Text + "')"; try { con.Open(); cmd.ExecuteNonQuery(); MessageBox.Show("Ajout Module Réussi"); con.Close(); new acceuil(login).Show(); this.Hide(); } catch (Exception ex) { MessageBox.Show("Erreur" + ex.Message); con.Close(); } //ckecked lisT foreach(object itemChecked in checkedListBox1.CheckedItems) { cmd1.CommandText = "Insert into assiste(cin,id_module,date_present) values(" + Convert.ToInt32(txt_cin.Text) + "," + hash[(string)itemChecked] + ",'" + DateTime.Now.ToString("dd-MM-yyyy") + "')"; try { con.Open(); cmd1.ExecuteNonQuery(); con.Close(); } catch (Exception ex) { MessageBox.Show("Erreur" + ex.Message); con.Close(); } } }
public static int GetPrincipalInfo(string login, out string role) { Database db = new Database(); System.Data.OleDb.OleDbConnection con = db.Connection; OleDbCommand cmd = con.CreateCommand(); cmd.CommandText = @"select Id, Role from users where upper(login) = upper(?)"; cmd.Parameters.Add(new OleDbParameter("login", login)); OleDbDataReader reader = cmd.ExecuteReader(); if (!reader.Read()) { reader.Close(); cmd.Dispose(); db.Dispose(); role = ""; return(0); //throw new BipAccessDeniedException(); } role = (string)reader["Role"]; int id = Convert.ToInt32((Decimal)reader["Id"]); reader.Close(); cmd.Dispose(); db.Dispose(); return(id); }
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(); } }
public void BumpNamedCounter(string sCounter) { int c = GetNamedCounter(sCounter); c++; OleDbConnection oConn = new OleDbConnection(m_sCounterConnString); m_page.Trace.Write("Connect=" + oConn.ConnectionString); oConn.Open(); OleDbCommand oCmd = oConn.CreateCommand(); string sCmd; if (c == 0) { sCmd = "INSERT INTO Counters (CounterName, [Count]) VALUES ('" + sCounter + "', 0)"; } else { sCmd = "UPDATE Counters SET [Count] = " + c.ToString() + " WHERE CounterName = '" + sCounter +"'"; } m_page.Trace.Write("command: "+sCmd); oCmd.CommandText = sCmd; oCmd.ExecuteNonQuery(); oCmd = null; oConn.Close(); }
public static DataTable LoadMDB(FileInfo file, string tableName) { string myConnectionString = string.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source={0};" + "Persist Security Info=True;" + "Jet OLEDB:Database Password=myPassword;", file.FullName); try { // Open OleDb Connection using (OleDbConnection myConnection = new OleDbConnection(myConnectionString)) { myConnection.Open(); // Execute Queries using (OleDbCommand cmd = myConnection.CreateCommand()) { cmd.CommandText = string.Format("SELECT * FROM `{0}`", tableName); using (OleDbDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection)) { // Load the result into a DataTable DataTable myDataTable = new DataTable(); myDataTable.Load(reader); return myDataTable; } } } } catch (Exception ex) { Console.WriteLine("OLEDB Connection FAILED: " + ex.Message); return null; } }
/// <summary> /// Button НОВАЯ ПОЗИЦИЯ clicked /// </summary> /// <param name="sender">Button1 control</param> /// <param name="e">Event arguments</param> private void button1_Click(object sender, EventArgs e) { using (OleDbConnection dbConnect = new OleDbConnection(connectionString)) { OleDbCommand command = dbConnect.CreateCommand(); // // To create a new empty row we insert empty values except of id which is AUTOINCREMENTed // command.CommandText = "insert into data (name, description, link, categories) values ('','','','')"; try { dbConnect.Open(); command.ExecuteNonQuery(); dataGridView1.DataSource = connectionString; dataGridView1.Refresh(); dbConnect.Close(); } catch (Exception exc) { MessageBox.Show(exc.Message); } } ShowData(); }
private void FrmMain_Load(object sender, EventArgs e) { this.SetStyle(ControlStyles.OptimizedDoubleBuffer | ControlStyles.AllPaintingInWmPaint | ControlStyles.UserPaint, true); string strPath = System.IO.Directory.GetCurrentDirectory() + BGimage; this.BackgroundImage = Image.FromFile(strPath); BtnStart.Image = Image.FromFile(System.IO.Directory.GetCurrentDirectory() + "\\images\\btn_start.png"); //从数据库里读出所有可抽奖人员 OleDbConnection odcConnection = new OleDbConnection(strConn); odcConnection.Open(); OleDbCommand odCommand = odcConnection.CreateCommand(); odCommand.CommandText = "select count(*) as result from seedlist"; OleDbDataReader odrCount = odCommand.ExecuteReader(); odrCount.Read(); int allcount = Int32.Parse(odrCount[0].ToString()); odrCount.Close(); if (allcount < 9) { odcConnection.Close(); MessageBox.Show("可供抽奖的人数不足,无法抽奖"); this.Close(); } else { //读取所有记录到数据 SeedsList.Items.Clear(); odCommand.CommandText = "select id,employee_dept,employee_name,employee_no from Seedlist where award_flag = '0' order by id asc"; OleDbDataReader odrReader = odCommand.ExecuteReader(); while (odrReader.Read()) { SeedsList.Items.Add(odrReader[0].ToString() + ";" + odrReader[1].ToString() + ";" + odrReader[2].ToString() + ";" + odrReader[3].ToString()); } odrReader.Close(); odcConnection.Close(); } }
public void setup () { if (Directory.Exists (_dataDir)) Directory.Delete (_dataDir, true); string initSql = @"CREATE TABLE Table1 ( UserId int NOT NULL PRIMARY KEY, UserName varchar(256) NOT NULL, Description varchar(256) )"; OleDbConnection connection = new OleDbConnection (_connectionString); try { connection.Open (); DbCommand cmd = connection.CreateCommand (); cmd.CommandText = initSql; cmd.CommandType = CommandType.Text; cmd.ExecuteNonQuery (); } catch (Exception) { } finally { connection.Close (); } }
public int product_service(string service_id, string product_id, int customer_id) { int row=0; cn = new OleDbConnection("Provider=MSDAORA;Data Source=192.168.0.217/orcl;Persist Security Info=True;User ID=hr;Password=hr;"); cmd = cn.CreateCommand(); try { cn.Open(); string s = "update product_services set product_id='" + product_id + "' where service_id=(select service_id from customer_services where customer_id=" + customer_id + " and service_id='" + service_id + "')"; Trans = cn.BeginTransaction(); cmd = new OleDbCommand(s, cn,Trans); cmd.Transaction = Trans; row= cmd.ExecuteNonQuery(); Trans.Commit(); } catch (Exception e) { string s = e.Message; Trans.Rollback(); } finally { cn.Close(); } return row; }
private void button2_Click(object sender, EventArgs e) { string chaine = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:/Users/dhib/Desktop/Professeur.accdb"; OleDbConnection con = new OleDbConnection(chaine); OleDbCommand cmd = con.CreateCommand(); cmd.CommandText = "update enseignant set mdp='" + txt_g_password.Text + "',nom=' "+txt_g_family.Text+"',prenom='"+txt_g_name.Text+"', grade='"+txt_g_grade.Text+"' where login='******'"; try { con.Open(); cmd.ExecuteNonQuery(); MessageBox.Show("Modification Réussi"); txt_g_password.Text = ""; txt_g_family.Text = ""; txt_g_name.Text = ""; txt_g_grade.Text = ""; con.Close(); new acceuil(login).Show(); this.Hide(); } catch (Exception ex) { MessageBox.Show("Erreur" + ex.Message); con.Close(); } }
public void Validate() { System.Data.OleDb.OleDbConnection con = Db.Connection; OleDbCommand cmd = con.CreateCommand(); if (IncomingNumber != null && IncomingNumber.Length != 0) { cmd.CommandText = @"select count(1) from documents where IncomingNumber=? and id <> ?"; cmd.Parameters.Clear(); cmd.Parameters.Add(new OleDbParameter("IncomingNumber", IncomingNumber)); cmd.Parameters.Add(new OleDbParameter("id", m_Id)); bool exists = (bool)((int)cmd.ExecuteScalar() != 0); if (exists) { cmd.Dispose(); throw new BipGenericException(BipResources.GetString("StrDocIncomingNumberIsNotUnique")); } } if (OutgoingNumber != null && OutgoingNumber.Length != 0) { cmd.CommandText = @"select count(1) from documents where OutgoingNumber=? and id <> ?"; cmd.Parameters.Clear(); cmd.Parameters.Add(new OleDbParameter("OutgoingNumber", OutgoingNumber)); cmd.Parameters.Add(new OleDbParameter("id", m_Id)); bool exists = (bool)((int)cmd.ExecuteScalar() != 0); if (exists) { cmd.Dispose(); throw new BipGenericException(BipResources.GetString("StrDocOutgoingNumberIsNotUnique")); } } cmd.Dispose(); }
static public bool CanRead(int id) { UserIdentity user = UserIdentity.Current; if (user.UserRole == UserRoles.Administrator || user.UserRole == UserRoles.SystemOperator) { return(true); } Database db = new Database(); System.Data.OleDb.OleDbConnection con = db.Connection; OleDbCommand cmd = con.CreateCommand(); cmd.CommandText = @"select count(1) from documents where id = " + id.ToString() + @" and( IsPublic=1 or OwnerUserId= " + user.UserId.ToString() + @" or exists (select top 1 1 from UserGroups inner join DocGroups on UserGroups.GroupId = DocGroups.GroupId where DocGroups.DocId = Documents.id and UserGroups.UserId = " + user.UserId.ToString() + " ))"; bool canRead = (bool)(((int)cmd.ExecuteScalar()) > 0); cmd.Dispose(); db.Dispose(); return(canRead); }
public byte[] GetRommList() { try { string strConnection = ConfigurationManager.ConnectionStrings["DigiLock"].ConnectionString; OleDbConnection objConnection = new OleDbConnection(strConnection); objConnection.Open();//打开连接 OleDbCommand odCommand = objConnection.CreateCommand(); odCommand.CommandText = "SELECT ROOMNO,STATE,CARDQTY FROM ROOMLIST"; OleDbDataReader reader = odCommand.ExecuteReader(); List<RoomListModel> Roomlistmodel = new List<RoomListModel>(); using (reader) { while (reader.Read()) { RoomListModel mRoom = new RoomListModel(); mRoom.State = dbh.ToString(reader["STATE"]); mRoom.RoomNo = reader["RoomNo"].ToString().Substring(1, 3); mRoom.CardQTY = dbh.ToInt32(reader["CARDQTY"]); Roomlistmodel.Add(mRoom); } reader.Close(); } objConnection.Close(); return cmn.SerializeObject(Roomlistmodel); } catch (Exception err) { throw err; } }
public void StoreDocRefs(OleDbTransaction trans, string tableName, string refFieldName, IEnumerable refs) { System.Data.OleDb.OleDbConnection con = Db.Connection; OleDbCommand cmd = con.CreateCommand(); if (trans != null) { cmd.Transaction = trans; } cmd.CommandText = "delete from " + tableName + " where DocId=" + m_Id.ToString(); cmd.ExecuteNonQuery(); if (refs != null) { cmd.CommandText = "insert into " + tableName + " (DocId, " + refFieldName + " ) values ( " + m_Id.ToString() + ", ?)"; foreach (int ref_id in refs) { cmd.Parameters.Clear(); cmd.Parameters.Add(new OleDbParameter("id", ref_id)); cmd.ExecuteNonQuery(); } } cmd.Dispose(); }
//--------------------------------------------------------------------------- public bool AtualizaDados(OleDbCommand Arquiva, string sTextoSql) { Arquiva = gConexao.CreateCommand(); Arquiva.CommandText = sTextoSql; if (Arquiva.ExecuteNonQuery() > 0) { Arquiva.Dispose(); return(true); } else { //MessageDialog.MessageBox(Resource.STR_NCEXECSQL, "Erro", // Dialog.wxOK|Dialog.wxICON_ERROR ); return(false); } }
/// <summary> /// Determines if the server can be connected to /// </summary> /// <param name="connString"></param> /// <returns></returns> protected internal static bool CanConnect(string connString) { bool connect = false; try { using (OleDbConnection connection = new OleDbConnection(connString)) { connection.Open(); using (OleDbCommand command = connection.CreateCommand()) { command.CommandText = "Select count(*) from sysdba.Account"; command.ExecuteScalar(); connect = true; } } } catch (Exception) { Console.WriteLine("Cannot connect to the underlying database"); } return connect; }
public List<InventoryPurchaseInfo> getAllInventoryPurchaseInfo() { List<InventoryPurchaseInfo> allInventoryPurchaseInfo = new List<InventoryPurchaseInfo>(); using(OleDbConnection sqlconn = new OleDbConnection(database)) { try { sqlconn.Open(); OleDbCommand cmd = sqlconn.CreateCommand(); String select = "SELECT [inventory_item_id] FROM [INVENTORY_PURCHASE_INFO]"; cmd.CommandText = select; OleDbDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { InventoryPurchaseInfo purchaseInfo = getSingleInventoryPurchaseInfo((int)reader["inventory_item_id"]); allInventoryPurchaseInfo.Add(purchaseInfo); } return allInventoryPurchaseInfo; } catch (OleDbException ex) { return allInventoryPurchaseInfo; } finally { sqlconn.Close(); } } }
public static void Main() { String connect = "Provider=Microsoft.JET.OLEDB.4.0;" + @"data source=c:\booksharp\gittleman\ch15\Sales.mdb"; OleDbConnection con = new OleDbConnection(connect); con.Open(); Console.WriteLine ("Made the connection to the Sales database"); OleDbCommand cmd = con.CreateCommand(); cmd.CommandText = "SELECT * FROM Customer"; OleDbDataReader reader = cmd.ExecuteReader(); XmlDocument document = new XmlDocument(); XmlElement customers = document.CreateElement("customers"); document.AppendChild(customers); while (reader.Read()) { XmlElement customer = document.CreateElement("customer"); customers.AppendChild(customer); XmlElement name = document.CreateElement("name"); customer.AppendChild(name); name.AppendChild (document.CreateTextNode(reader.GetString(1))); XmlElement address = document.CreateElement("address"); customer.AppendChild(address); address.AppendChild (document.CreateTextNode(reader.GetString(2))); XmlElement balance = document.CreateElement("balance"); customer.AppendChild(balance); Decimal b = reader.GetDecimal(3); balance.AppendChild (document.CreateTextNode(b.ToString())); } document.Save(Console.Out); reader.Close(); }
public void sendData()//sends data to database { Sensors sense = new Sensors(); double Accelleration = sense.accelerometer; int altimeter = sense.altimeter; int temperature = sense.temperature; bool touchdown = sense.touchdown; double gyroscope = sense.gyroscope; OleDbConnection con = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=SeniorProject.accdb"); OleDbCommand cmd = con.CreateCommand(); con.Open(); string query1 = "Insert into Accelerometers(Acceleration) Values ('" + Accelleration + "')"; string query2 = "Insert into Altimeter (Altitude) Values ('" + altimeter + "')"; string query3 = "Insert into DopplerRadar(Reading) Values ('" + temperature + "')"; string query4 = "Insert into Gyroscopes(Orientation) Values('" + gyroscope + "')"; string query5 = "Insert into Touchdown(Success) Values('" + touchdown + "')"; try { cmd.CommandText = query1 + query2 + query3 + query4 + query5; cmd.Connection = con; //cmd.ExecuteNonQuery(); Console.WriteLine("Record Submitted" + " Congrats"); con.Close(); } catch { Console.WriteLine("connection failed"); } }
public override void Delete() { System.Data.OleDb.OleDbConnection con = Db.Connection; OleDbTransaction trans = con.BeginTransaction(); try { OleDbCommand cmd = con.CreateCommand(); cmd.Transaction = trans; cmd.CommandText = @"delete from " + GetDbTableName() + @" where id = " + m_Id.ToString(); cmd.ExecuteNonQuery(); if (GetForeignDocField() != null) { cmd.CommandText = @"update documents set " + GetForeignDocField() + " = NULL where " + GetForeignDocField() + " = " + m_Id.ToString(); cmd.ExecuteNonQuery(); } trans.Commit(); } catch (Exception ex) { trans.Rollback(); throw ex; } }
public void Write(LogEntry entry) { using (OleDbConnection conn = new OleDbConnection(connStr)) { using (OleDbCommand cmd = conn.CreateCommand()) { cmd.CommandText = @" INSERT INTO [Request] (Date, Method, URL, Referral, ASPNETSessionID, UserHost, UserAddress, UserAgent) VALUES (@Date, @Method, @URL, @Referral, @ASPNETSessionID, @UserHost, @UserAddress, @UserAgent) "; cmd.Parameters.Add("@Date", entry.Date); cmd.Parameters.Add("@Method", entry.Method); cmd.Parameters.Add("@URL", Convert.ToString(entry.Url)); cmd.Parameters.Add("@Referral", Convert.ToString(entry.Referral)); cmd.Parameters.Add("@ASPNETSessionID", entry.ASPNETSessionID); cmd.Parameters.Add("@UserHost", entry.UserHost); cmd.Parameters.Add("@UserAddress", entry.UserAddress); cmd.Parameters.Add("@UserAgent", entry.UserAgent); conn.Open(); cmd.ExecuteNonQuery(); } } }
//刷新 private void Btn_Review_Click(object sender, EventArgs e) { SeedList.Items.Clear(); string strConn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + System.IO.Directory.GetCurrentDirectory() + "\\database\\SeedData.mdb;"; OleDbConnection odcConnection = new OleDbConnection(strConn); odcConnection.Open(); OleDbCommand odCommand = odcConnection.CreateCommand(); odCommand.CommandText = "select id,award,employee_dept,employee_name,employee_no from Awardlist order by id asc"; OleDbDataReader odrReader = odCommand.ExecuteReader(); while (odrReader.Read()) { ListViewItem li = new ListViewItem(); li.SubItems.Clear(); li.SubItems[0].Text = odrReader[0].ToString(); li.SubItems.Add(odrReader[1].ToString()); li.SubItems.Add(odrReader[2].ToString()); li.SubItems.Add(odrReader[3].ToString()); li.SubItems.Add(odrReader[4].ToString()); SeedList.Items.Add(li); } odrReader.Close(); odCommand.CommandText = "select count(*) as result from Awardlist "; OleDbDataReader odrCount = odCommand.ExecuteReader(); odrCount.Read(); LabInfo.Text = "中奖人员共 " + odrCount[0].ToString() + " 人"; odrCount.Close(); odcConnection.Close(); }
public List<ConsultaDocumentoDistribuicao> ConsultaDocumentoDistribuicao(int codigo) { var result = new List<ConsultaDocumentoDistribuicao>(); using (var cnx = new OleDbConnection(ConfigurationManager.ConnectionStrings["banco"].ConnectionString)) using (var cmd = cnx.CreateCommand()) { cnx.Open(); cmd.CommandText = "Select Codigo, documentodistribuicao, item, material, quantidade, desativado From lc_status_itemdoc_dist where codigo = '" + codigo.ToString() +"'"; var reader = cmd.ExecuteReader(); while (reader.Read()) { var item = new ConsultaDocumentoDistribuicao(); item.Codigo = (string)reader["codigo"]; item.DocumentoDistribuicao = (int)reader["documentodistribuicao"]; item.Item = (int)reader["item"]; item.Material = (int)reader["material"]; item.Quantidade = (Decimal)reader["quantidade"]; item.Ativo = (string)reader["desativado"]; result.Add(item); } } return result; }
private static void RunSqlQueryReturnValue(string queryText, Dictionary<string, object> parametters, CommandType commandType, Action<OleDbDataReader> action, string connectionString) { using (OleDbConnection oConn = new OleDbConnection(connectionString)) { using (OleDbCommand oRS = oConn.CreateCommand()) { oConn.Open(); oRS.CommandType = commandType; oRS.CommandText = queryText; if (parametters != null && parametters.Count > 0) { foreach (var parametter in parametters) { oRS.Parameters.AddWithValue(parametter.Key, parametter.Value); } } OleDbDataReader rdr = oRS.ExecuteReader(CommandBehavior.CloseConnection); if (action != null) { action(rdr); } } } }
public List<Abastecimento> ListarAbastecimento() { var result = new List<Abastecimento>(); using (var cnx = new OleDbConnection(ConfigurationManager.ConnectionStrings["banco"].ConnectionString)) using (var cmd = cnx.CreateCommand()) { cnx.Open(); cmd.CommandText = "Select Codigo, motorista, quantidade, hodometro, distribuicao, autorizacao, tipo, local, terminal, observacao, datahora, ativo from abastecimento order by codigo "; var reader = cmd.ExecuteReader(); while (reader.Read()) { var item = new Abastecimento(); item.Codigo = (int)reader["codigo"]; item.Motorista = (int)reader["motorista"]; item.Quantidade = (decimal)reader["quantidade"]; item.Hodometro = (int)reader["hodometro"]; item.Distribuicao = (int)reader["distribuicao"]; item.Autorizacao = (string)reader["autorizacao"]; item.Tipo = (int)reader["tipo"]; item.Local = (int)reader["local"]; item.Terminal = (int)reader["terminal"]; item.DataHora = (DateTime)reader["datahora"]; item.Ativo = (int)reader["ativo"]; item.Observacao = Convert.IsDBNull(reader["observacao"]) ? "" : (string)reader["observacao"]; result.Add(item); } } return result; }
/// <summary> /// Read the specified Excel file and returns the content /// </summary> /// <param name="excelFile"></param> /// <returns></returns> public DataTable Read(string excelFile) { string connectionString = string.Empty; string fileExtension = Path.GetExtension(excelFile); if (fileExtension == ".xls") { connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelFile + ";" + "Extended Properties='Excel 8.0;HDR=YES;'"; } else if (fileExtension == ".xlsx") { connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelFile + ";" + "Extended Properties='Excel 12.0 Xml;HDR=YES;'"; } using (var conn = new OleDbConnection(connectionString)) { conn.Open(); using (OleDbCommand command = conn.CreateCommand()) { DataTable dtSheet = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); string sheetName = dtSheet.Rows[0]["TABLE_NAME"].ToString(); command.CommandText = string.Format("SELECT * FROM [{0}]", sheetName); using (OleDbDataAdapter da = new OleDbDataAdapter(command)) { var dt = new DataTable(); da.Fill(dt); return dt; } } } }
public List<AccessLevel> getAllAccessLevels() { List<AccessLevel> allAccessLevels = new List<AccessLevel>(); using (OleDbConnection sqlconn = new OleDbConnection(database)) { try { sqlconn.Open(); OleDbCommand cmd = sqlconn.CreateCommand(); String select = "SELECT * FROM [ACCESS_LEVEL]"; cmd.CommandText = select; OleDbDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { AccessLevel accessLevel = new AccessLevel(); accessLevel.setLevel(reader["level"].ToString()); accessLevel.setID((int)reader["ID"]); allAccessLevels.Add(accessLevel); } return allAccessLevels; } catch (OleDbException ex) { return allAccessLevels; } finally { sqlconn.Close(); } } }
public static void CreateSyncTimestamp() { string DPDBLocation = Properties.Settings.Default.DPDBLocation; try { OleDbConnection DPDBconnection = null; DPDBconnection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0; User Id=; Password=; Data Source=" + DPDBLocation); DPDBconnection.Open(); OleDbCommand syncCmd = DPDBconnection.CreateCommand(); string commandText = @"INSERT INTO Syncs (sync_datetime) VALUES (#$latestSync#)"; commandText = commandText.Replace("$latestSync", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")); //Save latest sync. syncCmd.CommandText = commandText; int numRowsAffected = syncCmd.ExecuteNonQuery(); DPDBconnection.Close(); } catch { throw; } }
public int getUserAccessLevel(int userId) { int accessLevel = -1; using (OleDbConnection sqlconn = new OleDbConnection(database)) { try { sqlconn.Open(); OleDbCommand cmd = sqlconn.CreateCommand(); string select = "SELECT [access_level] FROM [USERS] WHERE [user_id] = @userId"; cmd.CommandText = select; cmd.Parameters.Add("userId", OleDbType.VarChar, 255).Value = userId; OleDbDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { accessLevel = (int)reader["access_level"]; } return accessLevel; } catch (OleDbException ex) { return accessLevel; } finally { sqlconn.Close(); } } }
public JsonResult changeProduct(string productName) { string allInfo = ""; OleDbConnection conn1 = null; conn1 = new OleDbConnection( "Provider=Microsoft.Jet.OLEDB.4.0; " + "Data Source=" + System.Web.HttpContext.Current.Server.MapPath("~") + ("App_Data\\Products.mdb")); conn1.Open(); OleDbDataReader dbReader1 = null; OleDbCommand cmd1 = conn1.CreateCommand(); string allProductInfo = "SELECT * from Products where Products.product_name='" + productName + "';"; cmd1.CommandText = allProductInfo; dbReader1 = cmd1.ExecuteReader(); List<string> list = new List<string>(); while (dbReader1.Read()) { allInfo = "@" + (string)dbReader1.GetValue(1) + "@" + (string)dbReader1.GetValue(2) + "@" + (string)dbReader1.GetValue(3) + "@" + (string)dbReader1.GetValue(4) + "@" + (string)dbReader1.GetValue(5) + "@" + (string)dbReader1.GetValue(6); list.Add(allInfo); allInfo = ""; } dbReader1.Close(); list.Reverse(); list.Insert(0, list.Count.ToString()); conn1.Close(); return Json(list, JsonRequestBehavior.AllowGet); }
//刷新 private void Btn_Review_Click(object sender, EventArgs e) { SeedList.Items.Clear(); OleDbConnection odcConnection = new OleDbConnection(strConn); odcConnection.Open(); OleDbCommand odCommand = odcConnection.CreateCommand(); odCommand.CommandText = "select id,employee_dept,employee_name,employee_no,award_flag from Seedlist order by id asc"; OleDbDataReader odrReader = odCommand.ExecuteReader(); while (odrReader.Read()) { ListViewItem li = new ListViewItem(); li.SubItems.Clear(); li.SubItems[0].Text = odrReader[0].ToString(); li.SubItems.Add(odrReader[1].ToString()); li.SubItems.Add(odrReader[2].ToString()); li.SubItems.Add(odrReader[3].ToString()); li.SubItems.Add(odrReader[4].ToString()); SeedList.Items.Add(li); } odrReader.Close(); odCommand.CommandText = "select count(*) as result from seedlist"; OleDbDataReader odrCount = odCommand.ExecuteReader(); odrCount.Read(); LabInfo.Text = "当前系统中共有待抽奖人员 " + odrCount[0].ToString() + " 人"; odrCount.Close(); odCommand.CommandText = "select count(*) as result from seedlist where award_flag = '1'"; odrCount = odCommand.ExecuteReader(); odrCount.Read(); LabInfo.Text += ",其中已中奖人员 " + odrCount[0].ToString() + " 人"; odrCount.Close(); odcConnection.Close(); }
/// <summary> /// 获取inpno /// </summary> /// <param name="p_strPatientId"></param> /// <param name="p_strVisitId"></param> /// <returns></returns> public string GetInpNo(string p_strPatientId, string p_strVisitId) { DataSet _dsR = new DataSet(); string _strSQL = string.Format("select inp_no from pat_master_index where patient_id = '{0}'", p_strPatientId); ConnectionStringSettings sEmr = ConfigurationManager.ConnectionStrings["EMRConnectionString"]; using (OleDbConnection connEMR = new OleDbConnection(sEmr.ConnectionString)) { try { connEMR.Open(); OleDbCommand cmdEMR = connEMR.CreateCommand(); OleDbDataAdapter daEMR = new OleDbDataAdapter(); cmdEMR.CommandText = _strSQL; daEMR.SelectCommand = cmdEMR; daEMR.Fill(_dsR); if (_dsR.Tables[0].Rows.Count == 1) { return _dsR.Tables[0].Rows[0]["inp_no"].ToString(); } } catch (Exception ex) { throw ex; } } return ""; }
internal override void LoadAll() { try { ISeriesDatabase database = (ISeriesDatabase)this.dbRoot.ClassFactory.CreateDatabase(); //database._name = cn.DataSource; OleDbConnection cn = new OleDbConnection(this.dbRoot.ConnectionString); cn.Open(); OleDbCommand cmd = cn.CreateCommand(); cmd.CommandText = "SELECT c.CATALOG_NAME, t.TABLE_SCHEMA FROM SYSTABLES t, QSYS2.SYSCATALOGS c"; OleDbDataReader reader = cmd.ExecuteReader(); while(reader.Read()) { //database. = reader["CATALOG_NAME"].ToString(); database._name = reader["TABLE_SCHEMA"].ToString(); break; } reader.Close(); cn.Close(); database.dbRoot = this.dbRoot; database.Databases = this; this._array.Add(database); } catch (Exception ex) { string message = ex.Message; } }
public string getExcelReport(ExcelAction req, string ChapterName) { System.Data.OleDb.OleDbConnection conn = null; string concateFile = ""; try { string filename = ""; concateFile = "\\datafiles\\Dashboard\\IssueDetails" + HttpContext.User.Identity.Name + DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString() + DateTime.Now.Hour.ToString() + DateTime.Now.Minute.ToString() + DateTime.Now.Millisecond.ToString() + ".xls"; var oldfilename = System.Web.HttpContext.Current.Request.PhysicalApplicationPath + "\\datafiles\\IssueDetails.xlsx"; filename = System.Web.HttpContext.Current.Request.PhysicalApplicationPath + concateFile; System.IO.File.Copy(oldfilename, filename, true); //Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=YES;\"" //Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0;HDR=YES;\"" conn = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filename + "; Extended Properties=\"Excel 12.0 Xml;HDR=NO\";"); conn.Open(); int i = 1; foreach (var item in req.Items) { var strsql = "insert into [Sheet1$A" + (i) + ":K" + (i) + "] (f1,f2,f3,f4,f5,f6,f7,f8,f9,f10,f11)values ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}')"; var cmd = conn.CreateCommand(); cmd.CommandText = string.Format(strsql, item.RequestNo, // item.IssueDetail1, "Aaliya", item.Comments, item.Status, item.ReportedByName, item.Department, item.Location, item.PendingWithEmail, item.ResponsibleWithEmail, item.PendingSince, item.LoggedDate ); var rowcount = cmd.ExecuteNonQuery(); i++; } } catch (Exception ex) { } finally { if (conn != null && conn.State == ConnectionState.Open) { conn.Close(); } } var filePath = concateFile; return(filePath); }
/// <summary>執行 Transaction</summary> /// <param name="alSQL">欲執行交易的 ArrayList (內含 SQL 指令)</param> /// <param name="OleDbConn">OleDbConnection連線物件</param> /// <returns>Transaction是否成功</returns> /// <remarks></remarks> public static bool raiseOleDbTransaction(ArrayList alSQL, System.Data.OleDb.OleDbConnection OleDbConn) { if (alSQL == null) { return(true); } if (alSQL.Count == 0) { return(true); } if (OleDbConn == null) { OleDbConn = createOleDbConnection(); } System.Data.OleDb.OleDbTransaction OleDbTrans = null; if (!(OleDbConn.State == ConnectionState.Open)) { OleDbConn.Open(); } System.Data.OleDb.OleDbCommand cmd = OleDbConn.CreateCommand(); StringBuilder strSQL = new StringBuilder(""); OleDbTrans = OleDbConn.BeginTransaction(); try { cmd.Transaction = OleDbTrans; for (int i = 0; i <= alSQL.Count - 1; i++) { if (!string.IsNullOrEmpty(alSQL[i].ToString())) { strSQL.AppendLine(alSQL[i].ToString()); } } cmd.CommandText = strSQL.ToString(); cmd.ExecuteNonQuery(); OleDbTrans.Commit(); return(true); } catch (Exception Ex) { if ((OleDbTrans != null)) { OleDbTrans.Rollback(); } //Message.alertMessage("C0002", null, Ex.Message.ToString(), null); return(false); } finally { if (!(OleDbConn.State == ConnectionState.Closed)) { OleDbConn.Close(); } } }
public static System.Data.DataTable getTable(string 参数_数据库路径, string 参数_SQL语句) { //初始化 System.Data.DataTable dt = new System.Data.DataTable(); try { //1、建立连接 C#操作Access之读取mdb string strConn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + 参数_数据库路径 + ";"; System.Data.OleDb.OleDbConnection odcConnection = new System.Data.OleDb.OleDbConnection(strConn); //2、打开连接 C#操作Access之读取mdb odcConnection.Open(); //建立SQL查询 System.Data.OleDb.OleDbCommand odCommand = odcConnection.CreateCommand(); //3、输入查询语句 C#操作Access之读取mdb odCommand.CommandText = 参数_SQL语句; //建立读取 System.Data.OleDb.OleDbDataReader odrReader = odCommand.ExecuteReader(); //查询并显示数据 int size = odrReader.FieldCount; for (int i = 0; i < size; i++) { //Console.WriteLine("{0} {1}", "列", odrReader.GetName(i).ToString()); dt.Columns.Add(odrReader.GetName(i).ToString(), typeof(string)); } while (odrReader.Read()) { System.Data.DataRow dr = dt.NewRow(); for (int i = 0; i < size; i++) { //Console.WriteLine("{0} {1}", odrReader.GetName(i).ToString(), odrReader[odrReader.GetName(i)].ToString()); dr[odrReader.GetName(i).ToString()] = odrReader[odrReader.GetName(i)].ToString(); } dt.Rows.Add(dr); } //关闭连接 C#操作Access之读取mdb odrReader.Close(); odcConnection.Close(); return(dt); } catch { return(dt); } }
static public IEnumerable CanRead(IEnumerable ids) { if (ids == null) { return(null); } IEnumerator enum_ids = ids.GetEnumerator(); enum_ids.Reset(); if (!enum_ids.MoveNext()) { return(ids); } UserIdentity user = UserIdentity.Current; if (user.UserRole == UserRoles.Administrator || user.UserRole == UserRoles.SystemOperator) { return(ids); } ArrayList res = new ArrayList(); Database db = new Database(); System.Data.OleDb.OleDbConnection con = db.Connection; OleDbCommand cmd = con.CreateCommand(); cmd.CommandText = @" select id from documents where id in ( " + EnumUtils.ConvertToString(ids) + @" ) and (IsPublic=1 or OwnerUserId= " + user.UserId.ToString() + @" or exists (select top 1 1 from UserGroups inner join DocGroups on UserGroups.GroupId = DocGroups.GroupId where DocGroups.DocId = Documents.id and UserGroups.UserId = " + user.UserId.ToString() + " ))"; OleDbDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { res.Add(Convert.ToInt32(reader["id"])); } reader.Close(); cmd.Dispose(); db.Dispose(); return(res); }
public override int Create() { Validate(); System.Data.OleDb.OleDbConnection con = Db.Connection; OleDbTransaction trans = con.BeginTransaction(); OleDbCommand cmd = con.CreateCommand(); cmd.Transaction = trans; try { cmd.CommandText = @"insert into users (Login, Password, FirstName, LastName, Email, Role) values (?,?,?,?,?,?)"; cmd.Parameters.Add(new OleDbParameter("Login", m_Login)); cmd.Parameters.Add(new OleDbParameter("Password", m_Password)); cmd.Parameters.Add(new OleDbParameter("FirstName", m_FirstName)); cmd.Parameters.Add(new OleDbParameter("LastName", m_LastName)); cmd.Parameters.Add(new OleDbParameter("Email", m_Email)); cmd.Parameters.Add(new OleDbParameter("Role", m_Role)); cmd.ExecuteNonQuery(); cmd.CommandText = "select @@identity"; Decimal oid = (Decimal)cmd.ExecuteScalar(); m_Id = Convert.ToInt32(oid); if (m_Groups != null) { cmd.CommandText = @"insert into usergroups (UserId, GroupId) values (?,?)"; cmd.Parameters.Clear(); cmd.Parameters.Add(new OleDbParameter("UserId", typeof(int))); cmd.Parameters.Add(new OleDbParameter("GroupId", typeof(int))); //cmd.Prepare(); foreach (int groupId in m_Groups) { cmd.Parameters["UserId"].Value = m_Id; cmd.Parameters["GroupId"].Value = groupId; cmd.ExecuteNonQuery(); } } trans.Commit(); } catch (Exception ex) { trans.Rollback(); throw ex; } return(m_Id); }
public override void Update() { Validate(); System.Data.OleDb.OleDbConnection con = Db.Connection; OleDbTransaction trans = con.BeginTransaction(); OleDbCommand cmd = con.CreateCommand(); cmd.Transaction = trans; try { cmd.CommandText = @"update users set Login=?, Password=?, FirstName=?, LastName=?, Email=?, Role=? where id=" + m_Id.ToString(); cmd.Parameters.Add(new OleDbParameter("Login", m_Login)); cmd.Parameters.Add(new OleDbParameter("Password", m_Password)); cmd.Parameters.Add(new OleDbParameter("FirstName", m_FirstName)); cmd.Parameters.Add(new OleDbParameter("LastName", m_LastName)); cmd.Parameters.Add(new OleDbParameter("Email", m_Email)); cmd.Parameters.Add(new OleDbParameter("Role", m_Role)); cmd.ExecuteNonQuery(); cmd.CommandText = @"delete from usergroups where userid=" + m_Id.ToString(); cmd.ExecuteNonQuery(); if (m_Groups != null) { cmd.CommandText = @"insert into usergroups (UserId, GroupId) values (?,?)"; cmd.Parameters.Clear(); cmd.Parameters.Add(new OleDbParameter("UserId", typeof(int))); cmd.Parameters.Add(new OleDbParameter("GroupId", typeof(int))); //cmd.Prepare(); foreach (int groupId in m_Groups) { cmd.Parameters["UserId"].Value = m_Id; cmd.Parameters["GroupId"].Value = groupId; cmd.ExecuteNonQuery(); } } trans.Commit(); } catch (Exception ex) { trans.Rollback(); throw ex; } }
public override void Update() { Validate(); System.Data.OleDb.OleDbConnection con = Db.Connection; OleDbCommand cmd = con.CreateCommand(); cmd.CommandText = @"update " + this.GetDbTableName() + @" set name=? where id=?"; cmd.Parameters.Add(new OleDbParameter("name", m_Name)); cmd.Parameters.Add(new OleDbParameter("id", m_Id)); cmd.ExecuteNonQuery(); }
public static bool Authenticate(string login, string password) { Database db = new Database(); System.Data.OleDb.OleDbConnection con = db.Connection; OleDbCommand cmd = con.CreateCommand(); cmd.CommandText = @"select count(1) from users where upper(login) = upper(?) and Password=?"; cmd.Parameters.Add(new OleDbParameter("login", login)); cmd.Parameters.Add(new OleDbParameter("password", password)); bool authenticated = (bool)((int)cmd.ExecuteScalar() > 0); cmd.Dispose(); db.Dispose(); return(authenticated); }
/// <summary> /// Execute SQL command, such as: update and insert /// </summary> /// <param name="strCmd">command to be executed</param> /// <returns>the number of rows affected by this command</returns> public int ExecuteCommnand(String strCmd) { try { if (null == m_command) { m_command = m_objConn.CreateCommand(); } m_command.CommandText = strCmd; return(m_command.ExecuteNonQuery()); } catch (Exception ex) { throw new Exception(ex.ToString() + strCmd); } }
public override int Create() { Validate(); System.Data.OleDb.OleDbConnection con = Db.Connection; OleDbCommand cmd = con.CreateCommand(); cmd.CommandText = @"insert into " + this.GetDbTableName() + @" (name) values (?)"; cmd.Parameters.Add(new OleDbParameter("name", m_Name)); cmd.ExecuteNonQuery(); cmd.CommandText = "select @@identity"; Decimal oid = (Decimal)cmd.ExecuteScalar(); m_Id = Convert.ToInt32(oid); return(m_Id); }
public IEnumerable LoadDocRefs(string tableName, string refFieldName) { ArrayList res = new ArrayList(); System.Data.OleDb.OleDbConnection con = Db.Connection; OleDbCommand cmd = con.CreateCommand(); cmd.CommandText = "select " + refFieldName + " from " + tableName + " where DocId=" + m_Id.ToString(); OleDbDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { res.Add(Convert.ToInt32(reader[0])); } reader.Close(); cmd.Dispose(); return((IEnumerable)res); }
public override void Load(int id) { System.Data.OleDb.OleDbConnection con = Db.Connection; OleDbCommand cmd = con.CreateCommand(); cmd.CommandText = @"select * from users where id =" + id.ToString(); OleDbDataReader reader = cmd.ExecuteReader(); if (!reader.Read()) { reader.Close(); cmd.Dispose(); throw new BipObjectNotFoundException(); } m_Login = (string)reader["Login"]; m_Password = DbConvert.ToString(reader["Password"]); m_FirstName = DbConvert.ToString(reader["FirstName"]); m_LastName = DbConvert.ToString(reader["LastName"]); m_Email = DbConvert.ToString(reader["Email"]); m_Role = DbConvert.ToString(reader["Role"]); m_Id = id; cmd.CommandText = @"select GroupId from usergroups where Userid =" + id.ToString(); reader.Close(); reader = cmd.ExecuteReader(); ArrayList groups = new ArrayList(); while (reader.Read()) { groups.Add(Convert.ToInt32(reader.GetDecimal(0))); } reader.Close(); cmd.Dispose(); if (groups.Count != 0) { m_Groups = groups; } else { m_Groups = null; } }
protected void CheckVisitRecord(object sender, EventArgs e) { System.Data.OleDb.OleDbConnection cnnDest = new System.Data.OleDb.OleDbConnection(); System.Data.OleDb.OleDbCommand cmdDest; strLapdataPath = txtFolder.Text; txtErrNotes.InnerHtml = ""; int numOfRec = 0; String strMDBPath = strLapdataPath + "\\Lapdata.mdb"; //String strMDBPath = "C:\\Lapdata\\Lapdata.mdb;"; try { if (strLapdataPath == "") { txtErrNotes.InnerHtml = "Warning: Please enter the Lapdata folder path"; } cnnDest.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" + strMDBPath; cnnDest.Open(); cmdDest = cnnDest.CreateCommand(); cmdDest.CommandType = CommandType.Text; cmdDest.CommandText = strSqlConsult; OleDbDataReader drSource = cmdDest.ExecuteReader(CommandBehavior.Default); while (drSource.Read()) { numOfRec++; } lblNumofRec.Text = numOfRec.ToString(); } catch (Exception err) { ExportFlag = false; gClass.AddErrorLogData(Request.Cookies["UserPracticeCode"].Value, Request.Url.Host, Request.Cookies["Logon_UserName"].Value, "Import/Export data Form", "CheckVisitRecord function", strSqlConsult + " ... " + err.ToString()); } }
/// <summary>Lookup CSV on Server Map Path. /// </summary> /// <summary>CSV lookup queries a CSV on the Map Path of the server to find the correct factor table and returns a single value. /// <para>Tablename = Tablename of CSV file</para> /// <para>LookupValue = Value for the lookup</para> /// <para>DataType = DataType for lookup value</para> /// <para>ColumnNo = column number to return</para> /// </summary> public double CSVLookup(string Tablename, string LookupValue, int DataType, int ColumnNo) { string Name; string path = HttpContext.Current.Server.MapPath("\\Factor Tables\\"); System.Data.OleDb.OleDbConnection con = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + path + "; Extended Properties='text; HDR=No; FMT=Delimited'"); OleDbCommand command = con.CreateCommand(); string Column = Convert.ToString("F" + ColumnNo); if (DataType == 1) { //Date command.CommandText = "SELECT top 1 " + Column + " FROM " + Tablename + ".csv " + " where F1 <= " + LookupValue + " order by F1 desc"; } else if (DataType == 2) { //String command.CommandText = "SELECT top 1 " + Column + " FROM " + Tablename + ".csv " + " where F1 = " + LookupValue; } con.Open(); OleDbDataReader reader = command.ExecuteReader(); object nameObj = null; while (reader.Read()) { nameObj = reader[0].ToString(); } con.Close(); if ((nameObj != null)) { Name = nameObj.ToString(); } if (nameObj == "") { return(0); } else { return(Convert.ToDouble(nameObj)); } }
public override void Delete() { System.Data.OleDb.OleDbConnection con = Db.Connection; OleDbTransaction trans = con.BeginTransaction(); OleDbCommand cmd = con.CreateCommand(); cmd.Transaction = trans; try { cmd.CommandText = @"delete from usergroups where userid = " + m_Id.ToString(); cmd.ExecuteNonQuery(); cmd.CommandText = @"delete from users where id = " + m_Id.ToString(); cmd.ExecuteNonQuery(); trans.Commit(); } catch (Exception ex) { trans.Rollback(); throw ex; } }
protected void Validate() { if (m_Login.Length == 0 || m_Role.Length == 0) { throw new BipGenericException(BipResources.GetString("StrRequiredParameterNotSpecified")); } System.Data.OleDb.OleDbConnection con = Db.Connection; OleDbCommand cmd = con.CreateCommand(); cmd.CommandText = @"select count(1) from users where Login=? and id <> ?"; cmd.Parameters.Add(new OleDbParameter("Login", m_Login)); cmd.Parameters.Add(new OleDbParameter("id", m_Id)); bool LoginExists = (bool)((int)cmd.ExecuteScalar() != 0); cmd.Dispose(); if (LoginExists) { throw new BipGenericException(BipResources.GetString("StrSpecifiedNameIsNotUnique")); } }
public override void Load(int id) { System.Data.OleDb.OleDbConnection con = Db.Connection; OleDbCommand cmd = con.CreateCommand(); cmd.CommandText = @"select * from " + this.GetDbTableName() + @" where id = ?"; cmd.Parameters.Add(new OleDbParameter("id", id)); OleDbDataReader reader = cmd.ExecuteReader(); if (!reader.Read()) { reader.Close(); cmd.Dispose(); throw new BipObjectNotFoundException(); } m_Name = (string)reader["name"]; m_Id = id; reader.Close(); cmd.Dispose(); }
public static string getStr(string 参数_数据库路径, string 参数_SQL语句) { //初始化 string 返回文本 = ""; try { //1、建立连接 C#操作Access之读取mdb string strConn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + 参数_数据库路径 + ";"; System.Data.OleDb.OleDbConnection odcConnection = new System.Data.OleDb.OleDbConnection(strConn); //2、打开连接 C#操作Access之读取mdb odcConnection.Open(); //建立SQL查询 System.Data.OleDb.OleDbCommand odCommand = odcConnection.CreateCommand(); //3、输入查询语句 C#操作Access之读取mdb odCommand.CommandText = 参数_SQL语句; //建立读取 System.Data.OleDb.OleDbDataReader odrReader = odCommand.ExecuteReader(); //查询并显示数据 int size = odrReader.FieldCount; odrReader.Read(); 返回文本 = odrReader[odrReader.GetName(0)].ToString(); //关闭连接 C#操作Access之读取mdb odrReader.Close(); odcConnection.Close(); return(返回文本); } catch { return(返回文本); } }
protected void Validate() { if (m_Name.Length == 0) { throw new Exception("StrRequiredParameterNotSpecified"); } System.Data.OleDb.OleDbConnection con = Db.Connection; OleDbCommand cmd = con.CreateCommand(); cmd.CommandText = @"select count(1) from " + this.GetDbTableName() + @" where name=? and id <> ?"; cmd.Parameters.Add(new OleDbParameter("name", m_Name)); cmd.Parameters.Add(new OleDbParameter("id", m_Id)); bool nameExists = (bool)((int)cmd.ExecuteScalar() != 0); cmd.Dispose(); if (nameExists) { throw new BipGenericException(BipResources.GetString("StrSpecifiedNameIsNotUnique")); } }
public void DbDelete() { if (m_Id < 1) { throw new BipFatalException("Internal Error"); } System.Data.OleDb.OleDbConnection con = Db.Connection; OleDbTransaction trans = con.BeginTransaction(); OleDbCommand cmd = con.CreateCommand(); cmd.Transaction = trans; try { cmd.CommandText = @"delete from DocGroups where DocId = " + m_Id.ToString(); cmd.ExecuteNonQuery(); cmd.CommandText = @"delete from DocRefRelated where DocId = " + m_Id.ToString(); cmd.ExecuteNonQuery(); cmd.CommandText = @"delete from UserFavoriteDocs where DocId = " + m_Id.ToString(); cmd.ExecuteNonQuery(); cmd.CommandText = @"delete from UserReadDocs where DocId = " + m_Id.ToString(); cmd.ExecuteNonQuery(); cmd.CommandText = @"update documents set ParentId = null where ParentId =" + m_Id.ToString(); cmd.ExecuteNonQuery(); cmd.CommandText = @"update documents set PreviousVersionId = null where PreviousVersionId =" + m_Id.ToString(); cmd.ExecuteNonQuery(); cmd.CommandText = @"delete from Documents where Id = " + m_Id.ToString(); cmd.ExecuteNonQuery(); trans.Commit(); } catch (Exception ex) { trans.Rollback(); throw ex; } }
public int DbCreate(out OleDbTransaction trans) { Validate(); System.Data.OleDb.OleDbConnection con = Db.Connection; trans = con.BeginTransaction(); OleDbCommand cmd = con.CreateCommand(); cmd.Transaction = trans; try { CmdParams cp = new CmdParams(cmd); m_CreationTime = DateTime.Now; cmd.CommandText = @" insert into documents ( CreationTime, FileType, [FileName], DateReceived, DocumentDate, IncomingNumber, OutgoingNumber, Subject, Header, ArchiveFileNames, DocTypeId, DocSourceId, DocCategoryId, ParentId, PreviousVersionId, OwnerUserId, IsPublic )values ( " + cp.Add(m_CreationTime) + cp.Add(m_FileTypeId) + cp.Add(m_FileName) + cp.Add(m_DateReceived) + cp.Add(m_DocumentDate) + cp.Add(m_IncomingNumber) + cp.Add(m_OutgoingNumber) + cp.Add(m_Subject) + cp.Add(m_Header) + cp.Add(m_ArchiveFileNames) + cp.Add(m_DocTypeId) + cp.Add(m_DocSourceId) + cp.Add(m_DocCategoryId) + cp.Add(m_ParentId) + cp.Add(m_PreviousVersionId) + cp.Add(m_OwnerUserId) + cp.Add(m_IsPublic) + " ) "; cmd.ExecuteNonQuery(); cmd.CommandText = "select @@identity"; Decimal oid = (Decimal)cmd.ExecuteScalar(); m_Id = Convert.ToInt32(oid); StoreDocRefs(trans, "DocGroups", "GroupId", Groups); StoreDocRefs(trans, "DocRefRelated", "RelatedDocId", RefDocuments); cmd.Parameters.Clear(); cmd.CommandText = "update Documents set StorageFileName = '" + StorageFileName + "' where Id = " + m_Id.ToString(); cmd.ExecuteNonQuery(); //trans.Commit(); //.DEV. isRead } catch (Exception ex) { trans.Rollback(); trans = null; throw ex; } MarkAsRead(); return(m_Id); }
public void DbUpdate() { UserIdentity user = UserIdentity.Current; Validate(); System.Data.OleDb.OleDbConnection con = Db.Connection; OleDbTransaction trans = con.BeginTransaction(); OleDbCommand cmd = con.CreateCommand(); cmd.Transaction = trans; try { CmdParams cp = new CmdParams(cmd, false); m_CreationTime = DateTime.Now; cmd.CommandText = @" update documents set FileType = " + cp.Add(m_FileTypeId) + ", [FileName] = " + cp.Add(m_FileName) + ", DateReceived = " + cp.Add(m_DateReceived) + ", DocumentDate = " + cp.Add(m_DocumentDate) + ", IncomingNumber = " + cp.Add(m_IncomingNumber) + ", OutgoingNumber = " + cp.Add(m_OutgoingNumber) + ", Subject = " + cp.Add(m_Subject) + ", Header = " + cp.Add(m_Header) + ", ArchiveFileNames = " + cp.Add(m_ArchiveFileNames) + ", DocTypeId = " + cp.Add(m_DocTypeId) + ", DocSourceId = " + cp.Add(m_DocSourceId) + ", DocCategoryId = " + cp.Add(m_DocCategoryId) + ", ParentId = " + cp.Add(m_ParentId) + ", PreviousVersionId = " + cp.Add(m_PreviousVersionId) + ", IsPublic = " + cp.Add(m_IsPublic) + ", StorageFileName = '" + StorageFileName + "' " + " where id = " + m_Id.ToString(); cmd.ExecuteNonQuery(); StoreDocRefs(trans, "DocGroups", "GroupId", Groups); StoreDocRefs(trans, "DocRefRelated", "RelatedDocId", RefDocuments); cmd.Parameters.Clear(); cmd.CommandText = "delete from UserReadDocs where DocId=" + m_Id.ToString() + " and UserId=" + user.UserId.ToString(); cmd.ExecuteNonQuery(); cmd.CommandText = "delete from UserFavoriteDocs where DocId=" + m_Id.ToString() + " and UserId=" + user.UserId.ToString(); cmd.ExecuteNonQuery(); if (IsRead) { cmd.CommandText = "insert into UserReadDocs (DocId, UserId) values (" + m_Id.ToString() + ", " + user.UserId.ToString() + " ) "; cmd.ExecuteNonQuery(); } if (IsFavorite) { cmd.CommandText = "insert into UserFavoriteDocs (DocId, UserId) values (" + m_Id.ToString() + ", " + user.UserId.ToString() + " ) "; cmd.ExecuteNonQuery(); } trans.Commit(); } catch (Exception ex) { trans.Rollback(); throw ex; } }
public override void Load(int id) { System.Data.OleDb.OleDbConnection con = Db.Connection; OleDbCommand cmd = con.CreateCommand(); cmd.CommandText = "select IsPublic, OwnerUserId from documents where id = " + id.ToString(); OleDbDataReader reader = cmd.ExecuteReader(); if (!reader.Read()) { reader.Close(); throw new BipObjectNotFoundException(); } int ownerUserId = DbConvert.ToInt32(reader["OwnerUserId"]); bool isPublic = DbConvert.ToBoolean(reader["IsPublic"]); reader.Close(); UserIdentity user = UserIdentity.Current; if (!( isPublic || user.UserRole == UserRoles.Administrator || user.UserRole == UserRoles.SystemOperator || user.UserRole == UserRoles.Operator && ownerUserId == user.UserId )) { cmd.CommandText = @"select top 1 1 from UserGroups inner join DocGroups on UserGroups.GroupId = DocGroups.GroupId where DocGroups.DocId = " + id.ToString() + " and UserGroups.UserId = " + user.UserId.ToString(); reader = cmd.ExecuteReader(); if (!reader.Read()) { reader.Close(); throw new BipAccessDeniedException(); } reader.Close(); } cmd.CommandText = @" select documents.*, IsRead=case when exists (select top 1 1 from UserReadDocs where DocId = Documents.Id and UserId=" + user.UserId.ToString() + @") then 1 else 0 end, IsFavorite=case when exists (select top 1 1 from UserFavoriteDocs where DocId = Documents.Id and UserId=" + user.UserId.ToString() + @") then 1 else 0 end from documents where documents.Id= " + id.ToString(); reader = cmd.ExecuteReader(); if (!reader.Read()) { throw new BipObjectNotFoundException(); } m_Id = id; m_CreationTime = DbConvert.ToDateTime(reader["CreationTime"]); m_FileTypeId = DbConvert.ToInt32(reader["FileType"]); m_SavedStorageFileName = DbConvert.ToString(reader["StorageFileName"]); m_FileName = DbConvert.ToString(reader["FileName"]); m_DateReceived = DbConvert.ToDateTime(reader["DateReceived"]); m_DocumentDate = DbConvert.ToDateTime(reader["DocumentDate"]); m_IncomingNumber = DbConvert.ToString(reader["IncomingNumber"]); m_OutgoingNumber = DbConvert.ToString(reader["OutgoingNumber"]); m_Subject = DbConvert.ToString(reader["Subject"]); m_Header = DbConvert.ToString(reader["Header"]); m_ArchiveFileNames = DbConvert.ToString(reader["ArchiveFileNames"]); m_DocTypeId = DbConvert.ToInt32(reader["DocTypeId"]); m_DocSourceId = DbConvert.ToInt32(reader["DocSourceId"]); m_DocCategoryId = DbConvert.ToInt32(reader["DocCategoryId"]); m_ParentId = DbConvert.ToInt32(reader["ParentId"]); m_PreviousVersionId = DbConvert.ToInt32(reader["PreviousVersionId"]); m_OwnerUserId = DbConvert.ToInt32(reader["OwnerUserId"]); m_IsPublic = DbConvert.ToBoolean(reader["IsPublic"]); m_IsRead = DbConvert.ToBoolean(reader["IsRead"]); m_IsFavorite = DbConvert.ToBoolean(reader["IsFavorite"]); reader.Close(); cmd.Dispose(); if (!CanRead(m_ParentId)) { m_ParentId = 0; } if (!CanRead(m_PreviousVersionId)) { m_PreviousVersionId = 0; } m_RefDocuments = CanRead(LoadDocRefs("DocRefRelated", "RelatedDocId")); m_Groups = LoadDocRefs("DocGroups", "GroupId"); m_SavedFileTypeId = m_FileTypeId; if (!m_IsRead) { MarkAsRead(); } }