/// <summary> /// 执行查询,并将查询返回的结果集中第一行的第一列作为 .NET Framework 数据类型返回。忽略额外的列或行。 /// </summary> /// <param name="sql">SELECT 语句</param> /// <returns>.NET Framework 数据类型形式的结果集第一行的第一列;如果结果集为空或结果为 REF CURSOR,则为空引用</returns> public object ExecuteScalar(string sql) { using (odbcConnection = this.GetOdbcConnection()) { if (odbcConnection == null) { return(null); } try { if (odbcConnection.State == System.Data.ConnectionState.Closed) { odbcConnection.Open(); } odbcCommand = new OdbcCommand(sql, odbcConnection); return(odbcCommand.ExecuteScalar()); } catch (Exception ex) { #if DEBUG System.Diagnostics.Debug.WriteLine(ex.ToString()); #endif return(null); } } }
/// <summary> /// Gets information about publishable (<see cref="M:Ferda.Modules.Helpers.Data.Database.IsTableTypePublishable(System.String)"/>) tables in database given by <c>odbcConnectionString</c>. /// </summary> /// <param name="odbcConnectionString">An ODBC connection string for test.</param> /// <param name="acceptableTypesOfTables">The acceptable types of the tables. Iff <c>null</c> than system and temporary tables are not accepted.</param> /// <param name="boxIdentity">An identity of BoxModule.</param> /// <returns> /// Array of <see cref="T:Ferda.Modules.Boxes.DataMiningCommon.Database.DataMatrixInfo"/>. /// </returns> /// <exception cref="T:Ferda.Modules.BadParamsError"/> public static DataMatrixSchemaInfo[] Explain(string odbcConnectionString, string[] acceptableTypesOfTables, string boxIdentity) { //get connection OdbcConnection conn = Ferda.Modules.Helpers.Data.OdbcConnections.GetConnection(odbcConnectionString, boxIdentity); //get schema DataTable schema = conn.GetSchema("TABLES"); //prepare OdbcCommand for "SELECT COUNT(1) FROM ..." query OdbcCommand odbcCommand = new OdbcCommand(); odbcCommand.Connection = conn; //result variable List<DataMatrixSchemaInfo> result = new List<DataMatrixSchemaInfo>(); foreach (DataRow row in schema.Rows) { //only publishable tables or views are added to result if (IsTableTypePublishable(row["TABLE_TYPE"].ToString(), acceptableTypesOfTables)) { DataMatrixSchemaInfo dataMatrixSchemaInfo = new DataMatrixSchemaInfo(); dataMatrixSchemaInfo.name = row["TABLE_NAME"].ToString(); dataMatrixSchemaInfo.type = row["TABLE_TYPE"].ToString(); dataMatrixSchemaInfo.remarks = row["REMARKS"].ToString(); //complete OdbcCommand and execute odbcCommand.CommandText = "SELECT COUNT(1) FROM " + "`" + dataMatrixSchemaInfo.name + "`"; dataMatrixSchemaInfo.rowCount = Convert.ToInt32(odbcCommand.ExecuteScalar()); result.Add(dataMatrixSchemaInfo); } } return result.ToArray(); }
internal object TraerValor(string odbc, IDbConnection con) { string TipoBase = archivos.nombremotorbase(); if (TipoBase == "Mysql") { MySqlCommand comando = new MySqlCommand(); comando.CommandType = CommandType.Text; comando.CommandText = odbc; comando.Connection = con as MySqlConnection; return(comando.ExecuteScalar()); } else { if (TipoBase == "sql") { SqlCommand comando = new SqlCommand(); comando.CommandType = CommandType.Text; comando.CommandText = odbc; comando.Connection = con as SqlConnection; return(comando.ExecuteScalar()); } else { System.Data.Odbc.OdbcCommand comando = new System.Data.Odbc.OdbcCommand(); comando.CommandType = CommandType.Text; comando.CommandText = odbc; comando.Connection = con as System.Data.Odbc.OdbcConnection; return(comando.ExecuteScalar()); } } }
public void load_list() { try { string workerName = "admin"; //Session["USER_ID"].ToString(); string connection = ConfigurationSettings.AppSettings["ConnectionString"]; OdbcConnection dbCon = new OdbcConnection(connection); dbCon.Open(); string sql = "select count(*) from Board"; OdbcCommand cmd = new OdbcCommand(sql, dbCon); total = int.Parse(cmd.ExecuteScalar().ToString()); cmd.CommandText = "select * from Board order by headnum DESC, depth ASC"; OdbcDataAdapter dbAdap = new OdbcDataAdapter(cmd); DataSet ds = new DataSet(); dbAdap.Fill(ds); dbAdap.Update(ds); GridView1.DataSource = ds; GridView1.DataBind(); dbCon.Close(); } catch { } }
public static Int32 validarUsuario(String usr, String pass) { String query = "SELECT idUsuario FROM usuario " + "WHERE user='******'" + "AND password='******'"; try { using (OdbcConnection con = new OdbcConnection(Constantes.CONNECTION_STRING)) { using (OdbcCommand cmd = new OdbcCommand(query, con)) { con.Open(); cmd.CommandType = CommandType.Text; Int32 idUser = Convert.ToInt32(cmd.ExecuteScalar()); cmd.Connection.Close(); return idUser; } } } catch (Exception) { throw; } }
private void btnSignIn_Click(object sender, EventArgs e) { OdbcConnection Conn = new OdbcConnection("DSN=ora10glocal;uid=TTCS;PWD=Pass1234"); OdbcCommand cmd = new OdbcCommand("SELECT EMPLOYEEPASSWORD FROM EMPLOYEE WHERE LASTNAME = '" + txtUserName.Text + "'AND EMPLOYEETYPE = 'Foreman';", Conn); Conn.Open(); Object EmpPass = cmd.ExecuteScalar(); cmd.Dispose(); Conn.Close(); if (txtUserName.Text == "") { MessageBox.Show("User name is empty!"); } else if (Convert.ToString(EmpPass) == "") { MessageBox.Show("Password is empty!"); } else if (Convert.ToString(EmpPass) != txtPassword.Text) { MessageBox.Show("Wrong password! please type again!"); } else { string Emp = Convert.ToString(EmpPass); var frm = new Form1(Emp); frm.ShowDialog(); //this.Visible = false } }
public static object ExecuteScalarOnDB(string sSQL, string s_ConnectionString) { System.Data.Odbc.OdbcConnection QConnection = null; System.Data.Odbc.OdbcCommand QCommand = null; try { QConnection = new System.Data.Odbc.OdbcConnection(s_ConnectionString); QCommand = new System.Data.Odbc.OdbcCommand(sSQL, QConnection); QConnection.Open(); return(QCommand.ExecuteScalar()); } finally { if (QCommand != null) { QCommand.Dispose(); } QCommand = null; if (QConnection != null && QConnection.State != System.Data.ConnectionState.Closed) { QConnection.Close(); } if (QConnection != null) { QConnection.Dispose(); } QConnection = null; } }
protected void Button1_Click(object sender, EventArgs e) { OdbcConnection odb = new OdbcConnection("Driver={MYSQL ODBC 5.1 Driver};Server=localhost;User=root;Password=hello;database=mdb"); odb.Open(); //OdbcCommand od = new OdbcCommand("select count(*) from login where uid='" + TextBox1.Text + "' and pass='******'", odb); OdbcCommand cmd = new OdbcCommand("insert into user_det values('" + TextBox1.Text + "','" + TextBox2.Text + "','" + TextBox3.Text + "')", odb); cmd.ExecuteScalar(); Response.Write("successfully registrd"); }
protected void btnLoginOK_Click(object sender, EventArgs e) { //string username = txtName.Text; // string pwd = txtPwd.Text; string connection = ConfigurationSettings.AppSettings["connectionString"]; OdbcConnection dbCon = new OdbcConnection(connection); string sql = "SELECT * FROM profile WHERE MEMBER_NAME = ? AND MEMBER_PWD = ?"; //string sql = "SELECT * FROM profile WHERE MEMBER_NAME ='" + username + "'AND MEMBER_PWD='" + pwd+"'"; OdbcCommand dbCmd = new OdbcCommand(sql, dbCon); dbCmd.Parameters.Add(new OdbcParameter("userid", OdbcType.VarChar, 50, "MEMBER_NAME")).Value = txtName.Text; dbCmd.Parameters.Add(new OdbcParameter("pwd", OdbcType.VarChar, 35, "MEMBER_PWD")).Value = txtPwd.Text; dbCon.Open(); if (dbCmd.ExecuteScalar() == null) { ClientScriptManager cs = Page.ClientScript; cs.RegisterStartupScript(this.GetType(), "windowClose", "window.location.replace('./default.aspx');", true); cs.RegisterStartupScript(this.GetType(), "PopupScript", "alert('Incorrect ID/PW');", true); } else { dbCmd.CommandText = "select * from profile where MEMBER_NAME = ?"; OdbcDataReader reader = dbCmd.ExecuteReader(); reader.Read(); //add session Session.Add("USER_ID", txtName.Text); Session.Add("USER_NAME", reader["MEMBER_NAME"].ToString()); Session.Add("USER_PWD", reader["MEMBER_PWD"].ToString()); Session.Add("USER_ROLE", reader["MEMBER_ROLE"].ToString()); reader.Close(); dbCon.Close(); if (HttpContext.Current.Session["USER_ROLE"] != null) { string sessionRole = HttpContext.Current.Session["USER_ROLE"].ToString(); if (sessionRole == "Admin") { ClientScriptManager cs = Page.ClientScript; cs.RegisterStartupScript(this.GetType(), "windowClose", "window.location.replace('./maincontrol.aspx');", true); cs.RegisterStartupScript(this.GetType(), "PopupScript", "alert('You have login as Admin');", true); } else { ClientScriptManager cs = Page.ClientScript; cs.RegisterStartupScript(this.GetType(), "windowClose", "window.location.replace('./default.aspx');", true); cs.RegisterStartupScript(this.GetType(), "PopupScript", "alert('You have login');", true); } } } }
public static void FetchETagLifeTime() { OdbcConnection cn = new OdbcConnection(DbCmdServer.getDbConnectStr()); OdbcCommand cmd = new OdbcCommand("select VARIABLEVALUE from TBLSYSPARAMETER where VARIABLENAME = 'ETAGCOMPARTTIME'"); cmd.Connection = cn; cn.Open(); TC.AVIDeviceWrapper.ETAGLiveTime =System.Convert.ToInt32( cmd.ExecuteScalar()); cn.Close(); }
public string GetFactorySummaryChartHtml() { //In this example, we show how to connect FusionCharts to a database. //For the sake of ease, we've used an Access database which is present in //../DB/FactoryDB.mdb. It just contains two tables, which are linked to each //other. //xmlData will be used to store the entire XML document generated string xmlData; //We also keep a flag to specify whether we've to animate the chart or not. //If the user is viewing the detailed chart and comes back to this page, he shouldn't //see the animation again. string animateChart; animateChart = Request.QueryString["animate"]; //Set default value of 1 if (animateChart != null && animateChart.Length == 0) { animateChart = "1"; } //Generate the chart element xmlData = "<chart caption='Factory Output report' subCaption='By Quantity' pieSliceDepth='30' showBorder='1' formatNumberScale='0' numberSuffix=' Units' animation=' " + animateChart + "'>"; //Iterate through each factory string factoryQuery = "select * from Factory_Master"; using (OdbcConnection connection = DbHelper.Connection(DbHelper.ConnectionStringFactory)) { using (OdbcCommand factoryCommand = new OdbcCommand(factoryQuery, connection)) { using (OdbcDataAdapter adapter = new OdbcDataAdapter(factoryCommand)) { DataTable table = new DataTable(); adapter.Fill ( table ); foreach (DataRow row in table.Rows) { string quantityQuery = "select sum(Quantity) as TotOutput from Factory_Output where FactoryId=" + row["FactoryId"]; using (OdbcCommand quantityCommand = new OdbcCommand(quantityQuery, connection)) { xmlData += "<set label='" + row["FactoryName"].ToString() + "' value='" + quantityCommand.ExecuteScalar().ToString() + "' link='" + Server.UrlEncode("Detailed.aspx?FactoryId=" + row["FactoryId"].ToString()) + "'/>"; } } } } connection.Close(); } //Finally, close <chart> element xmlData += "</chart>"; //Create the chart - Pie 3D Chart with data from xmlData return FusionCharts.RenderChart("../../FusionCharts/Pie3D.swf", "", xmlData, "FactorySum", "600", "300", false, false); }
protected void btnLoginOK_Click(object sender, EventArgs e) { string connection = ConfigurationSettings.AppSettings["connectionString"]; OdbcConnection dbCon = new OdbcConnection(connection); string sql = "SELECT seq FROM profile WHERE (MEMBER_NAME = @userid) AND (MEMBER_PWD = @pwd)"; OdbcCommand dbCmd = new OdbcCommand(sql, dbCon); dbCmd.Parameters.Add("@userid", OdbcType.VarChar, 50, "MEMBER_NAME"); dbCmd.Parameters.Add("@pwd", OdbcType.VarChar, 30, "MEMBER_PWD"); dbCon.Open(); dbCmd.Parameters["@userid"].Value = txtName.Text; dbCmd.Parameters["@pwd"].Value = txtPwd.Text; if (dbCmd.ExecuteScalar() == null) { ClientScriptManager cs = Page.ClientScript; cs.RegisterStartupScript(this.GetType(), "windowClose", "window.location.replace('./DefaultLogin.aspx');", true); cs.RegisterStartupScript(this.GetType(), "PopupScript", "alert('Incorrect ID/PW');", true); dbCmd.ExecuteNonQuery(); dbCon.Close(); } else { dbCmd.CommandText = "select * from profile where (MEMBER_NAME = @userid)"; OdbcDataReader reader = dbCmd.ExecuteReader(); reader.Read(); //add session Session.Add("USER_ID", txtName.Text); Session.Add("USER_NAME", reader["MEMBER_NAME"].ToString()); Session.Add("USER_PWD", reader["MEMBER_PWD"].ToString()); reader.Close(); dbCmd.ExecuteNonQuery(); dbCon.Close(); Response.Redirect("./Board_list.aspx"); //if (Session["USER_ID"] != null) //{ // namelbl2.Text = Session["USER_ID"].ToString() + "Welcom."; // //hide login // LoginForm.Visible = false; // //dis login // loginchk.Visible = true; //} } }
private static int GetTablesCount(string tableName, OdbcConnection conn) { int count = 0; string sql = "select count(*) from db_class where class_name = '" + tableName + "'"; using (OdbcCommand cmd = new OdbcCommand(sql, conn)) { count = (int)cmd.ExecuteScalar(); } return count; }
private static int GetTableRowsCount(string tableName, OdbcConnection conn) { int count = -1; string sql = "select count(*) from `" + tableName + "`"; using (OdbcCommand cmd = new OdbcCommand(sql, conn)) { count = (int)cmd.ExecuteScalar(); } return count; }
public UInt64 GetCount(string tableName) { UInt64 count; using (OdbcConnection connCount = new OdbcConnection(_connectionString)) { OdbcCommand commandCount = new OdbcCommand(String.Format("SELECT count(*) FROM {0}", tableName), connCount); commandCount.CommandTimeout = 300; connCount.Open(); count = UInt64.Parse(commandCount.ExecuteScalar().ToString()); commandCount.Dispose(); connCount.Close(); } return count; }
public static string getMFCC_IP(string mfccid) { OdbcConnection cn = new OdbcConnection(db2ConnectionStr); OdbcCommand cmd = new OdbcCommand(string.Format("select hostip from vwHostMfcc where mfccid='{0}'", mfccid), cn); try { cn.Open(); return cmd.ExecuteScalar().ToString(); } finally { cn.Close(); } }
public static string ObtenerRol(Int32 idUsuario) { String query = "SELECT rol FROM usuario " + "WHERE idUsuario=" + idUsuario.ToString(); try { OdbcCommand cmd = new OdbcCommand(query, ObtenerConexion()); String rol = cmd.ExecuteScalar().ToString(); cmd.Connection.Close(); return rol; } catch (Exception) { throw; } }
public void LotStatus() { int x = 1; foreach (Button Child in PanelLots.Controls) { Button Bt = Child as Button; Bt.Click += new EventHandler(Clicked); OdbcConnection Conn = new OdbcConnection("DSN=ora10glocal;uid=TTCS;PWD=Pass1234"); String query = "Select LOCATIONSTATUS FROM LOCATION WHERE LOCATION_ID = '" + x + "'"; OdbcCommand cmd = new OdbcCommand(query, Conn); OdbcDataAdapter ODA = new OdbcDataAdapter(cmd); x++; Conn.Open(); object status = cmd.ExecuteScalar(); string y = Convert.ToString(status); if (y == "Occupied") { Bt.BackColor = Color.Green; } else if (y == "Available") { Bt.BackColor = Color.Gray; } else if (y == "reserved") { Bt.BackColor = Color.Blue; } else if (y == "Broken Seal") { Bt.BackColor = Color.Yellow; } else if (y == "Mechanical Problems") { Bt.BackColor = Color.Red; } else if (y == "Moving") { Bt.BackColor = Color.Orange; } Conn.Close(); } }
public string GetFactorySummaryChartHtml() { //xmlData will be used to store the entire XML document generated string xmlData; //Generate the chart element xmlData = "<chart caption='Factory Output report' subCaption='By Quantity' pieSliceDepth='30' showBorder='1' formatNumberScale='0' numberSuffix=' Units' >"; using ( OdbcConnection connection = DbHelper.Connection(DbHelper.ConnectionStringFactory)) { string factoryQuery = "select * from Factory_Master"; using (OdbcCommand factoryCommand = new OdbcCommand(factoryQuery, connection)) { using (OdbcDataAdapter factoryAdapter = new OdbcDataAdapter(factoryCommand)) { DataTable factoryTable = new DataTable(); factoryAdapter.Fill ( factoryTable ); foreach (DataRow factoryRow in factoryTable.Rows) { string quantityQuery = "select FactoryId as TotOutput, sum(Quantity) from Factory_Output where FactoryId=" + factoryRow["FactoryId"] + " Group By FactoryId"; using (OdbcCommand quantityCommand = new OdbcCommand(quantityQuery, connection)) { xmlData += "<set label='" + factoryRow["FactoryName"] + "' value='" + quantityCommand.ExecuteScalar().ToString() + "' link='javaScript:updateChart(" + factoryRow["FactoryId"] + ")'/>"; } } } } connection.Close(); } xmlData += "</chart>"; System.Text.StringBuilder chartBuilder = new System.Text.StringBuilder(); //Create the chart - Pie 3D Chart with data from xmlData chartBuilder.Append(FusionCharts.RenderChart("../../FusionCharts/Pie3D.swf", "", xmlData, "FactorySum", "500", "250", false, true)); return chartBuilder.ToString(); }
public string GetFactorySummaryChartHtml() { //In this example, we show how to connect FusionCharts to a database. //For the sake of ease, we've used an Access database which is present in //../DB/FactoryDB.mdb. It just contains two tables, which are linked to each //other. //xmlData will be used to store the entire XML document generated string xmlData; //Generate the chart element xmlData = "<chart caption='Factory Output report' subCaption='By Quantity' pieSliceDepth='30' showBorder='1' formatNumberScale='0' numberSuffix=' Units'>"; //Iterate through each factory string factoryQuery = "select * from Factory_Master"; using (OdbcConnection connectin = DbHelper.Connection(DbHelper.ConnectionStringFactory)) { using (OdbcCommand factoryCommand = new OdbcCommand(factoryQuery, connectin)) { using (OdbcDataAdapter adapter = new OdbcDataAdapter(factoryCommand)) { DataTable table = new DataTable(); adapter.Fill ( table ); foreach ( DataRow row in table.Rows) { string quantityQuery = "select sum(Quantity) as TotOutput from Factory_Output where FactoryId=" + row["FactoryId"].ToString(); using (OdbcCommand quantityCommand = new OdbcCommand(quantityQuery,connectin)) { xmlData += "<set label='" + row["FactoryName"].ToString() + "' value='" + quantityCommand.ExecuteScalar().ToString() + "' />"; } } } } connectin.Close(); xmlData += "</chart>"; } //Create the chart - Pie 3D Chart with data from xmlData return FusionCharts.RenderChart("../../FusionCharts/Pie3D.swf", "", xmlData, "FactorySum", "600", "300", false, false); }
protected void Button1_Click(object sender, EventArgs e) { con.Open(); OdbcCommand cmd=new OdbcCommand("select count(*) from user_det where uid='"+TextBox1.Text+"'and pass='******'",con); int i = Convert.ToInt32(cmd.ExecuteScalar().ToString()); if (i == 1) { Response.Redirect("Database Migration.aspx"); Session["uid"] = TextBox1.Text; // HttpApplicationState hp = new HttpApplicationState(); //hp.Add("uid", TextBox1.Text); //SessionPageStatePersister oh = new SessionPageStatePersister(); } else { Response.Redirect("Invalid.aspx"); } }
/// <summary> /// This Method will Execute a SCALAR SQL statement /// </summary> /// <param name="_queryStr"></param> /// <param name="_conn"></param> /// <returns></returns> public static Object ExecuteScalar(String _queryStr, List<DBParameters> dbParm) { OdbcConnection _conn = DBConnector.GetConnection(); if (_conn.State != ConnectionState.Open) { _conn.Open(); } _queryStr = GetQueryString(_queryStr, dbParm); try { OdbcCommand command = new OdbcCommand(_queryStr, _conn); return command.ExecuteScalar(); } catch (Exception e) { return null;// returns null in case of any error } }
public static string getHostIP() { OdbcConnection cn = new OdbcConnection(db2ConnectionStr); try { OdbcCommand cmd = new OdbcCommand("select hostip from tblhostconfig where hostid='HOST'", cn); cmd.Connection = cn; cn.Open(); return cmd.ExecuteScalar().ToString(); } catch (Exception ex) { Console.WriteLine(ex.Message+","+ ex.StackTrace); throw new Exception(ex.Message+ex.StackTrace); } finally { cn.Close(); } }
// // MembershipProvider.GetUserNameByEmail // public override string GetUserNameByEmail(string email) { OdbcConnection conn = new OdbcConnection(connectionString); OdbcCommand cmd = new OdbcCommand("SELECT Username" + " FROM Users WHERE Email = ? AND ApplicationName = ?", conn); cmd.Parameters.Add("@Email", OdbcType.VarChar, 128).Value = email; cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = ApplicationName; string username = ""; try { conn.Open(); username = (string)cmd.ExecuteScalar(); } catch (OdbcException e) { if (WriteExceptionsToEventLog) { WriteToEventLog(e, "GetUserNameByEmail"); throw new ProviderException(exceptionMessage); } else { throw e; } } finally { conn.Close(); } if (username == null) username = ""; return username; }
// // MembershipProvider.GetNumberOfUsersOnline // public override int GetNumberOfUsersOnline() { TimeSpan onlineSpan = new TimeSpan(0, System.Web.Security.Membership.UserIsOnlineTimeWindow, 0); DateTime compareTime = DateTime.Now.Subtract(onlineSpan); OdbcConnection conn = new OdbcConnection(connectionString); OdbcCommand cmd = new OdbcCommand("SELECT Count(*) FROM Users " + " WHERE LastActivityDate > ? AND ApplicationName = ?", conn); cmd.Parameters.Add("@CompareDate", OdbcType.DateTime).Value = compareTime; cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = ApplicationName; int numOnline = 0; try { conn.Open(); numOnline = (int)cmd.ExecuteScalar(); } catch (OdbcException e) { if (WriteExceptionsToEventLog) { WriteToEventLog(e, "GetNumberOfUsersOnline"); throw new ProviderException(exceptionMessage); } else { throw e; } } finally { conn.Close(); } return numOnline; }
// // MembershipProvider.FindUsersByEmail // public override MembershipUserCollection FindUsersByEmail(string emailToMatch, int pageIndex, int pageSize, out int totalRecords) { OdbcConnection conn = new OdbcConnection(connectionString); OdbcCommand cmd = new OdbcCommand("SELECT Count(*) FROM Users " + "WHERE Email LIKE ? AND ApplicationName = ?", conn); cmd.Parameters.Add("@EmailSearch", OdbcType.VarChar, 255).Value = emailToMatch; cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = ApplicationName; MembershipUserCollection users = new MembershipUserCollection(); OdbcDataReader reader = null; totalRecords = 0; try { conn.Open(); totalRecords = (int)cmd.ExecuteScalar(); if (totalRecords <= 0) { return users; } cmd.CommandText = "SELECT PKID, Username, Email, PasswordQuestion," + " Comment, IsApproved, IsLockedOut, CreationDate, LastLoginDate," + " LastActivityDate, LastPasswordChangedDate, LastLockedOutDate " + " FROM Users " + " WHERE Email LIKE ? AND ApplicationName = ? " + " ORDER BY Username Asc"; reader = cmd.ExecuteReader(); int counter = 0; int startIndex = pageSize * pageIndex; int endIndex = startIndex + pageSize - 1; while (reader.Read()) { if (counter >= startIndex) { MembershipUser u = GetUserFromReader(reader); users.Add(u); } if (counter >= endIndex) { cmd.Cancel(); } counter++; } } catch (OdbcException e) { if (WriteExceptionsToEventLog) { WriteToEventLog(e, "FindUsersByEmail"); throw new ProviderException(exceptionMessage); } else { throw e; } } finally { if (reader != null) { reader.Close(); } conn.Close(); } return users; }
public static object QueryOdbcDatabaseScalar(string ConnectionString, string OdbcQuery, Hashtable OdbcParameters) { using (OdbcConnection dbConnection = BuildOdbcConnection(ConnectionString)) { using (OdbcCommand dbCommand = new OdbcCommand(OdbcQuery, dbConnection)) { BuildOdbcParameters(dbCommand, OdbcParameters); try { dbConnection.Open(); return dbCommand.ExecuteScalar(); } catch (Exception) { throw; } finally { dbConnection.Close(); } } } }
} // HasChildItems /// <summary> /// Creates a new item at the specified path. /// </summary> /// /// <param name="path"> /// The path to the new item. /// </param> /// /// <param name="type"> /// Type for the object to create. "Table" for creating a new table and /// "Row" for creating a new row in a table. /// </param> /// /// <param name="newItemValue"> /// Object for creating new instance of a type at the specified path. For /// creating a "Table" the object parameter is ignored and for creating /// a "Row" the object must be of type string which will contain comma /// separated values of the rows to insert. /// </param> protected override void NewItem(string path, string type, object newItemValue) { string tableName; int rowNumber; PathType pt = GetNamesFromPath(path, out tableName, out rowNumber); if (pt == PathType.Invalid) { ThrowTerminatingInvalidPathException(path); } // Check if type is either "table" or "row", if not throw an // exception if (!String.Equals(type, "table", StringComparison.OrdinalIgnoreCase) && !String.Equals(type, "row", StringComparison.OrdinalIgnoreCase)) { WriteError(new ErrorRecord (new ArgumentException("Type must be either a table or row"), "CannotCreateSpecifiedObject", ErrorCategory.InvalidArgument, path ) ); throw new ArgumentException("This provider can only create items of type \"table\" or \"row\""); } // Path type is the type of path of the container. So if a drive // is specified, then a table can be created under it and if a table // is specified, then a row can be created under it. For the sake of // completeness, if a row is specified, then if the row specified by // the path does not exist, a new row is created. However, the row // number may not match as the row numbers only get incremented based // on the number of rows if (PathIsDrive(path)) { if (String.Equals(type, "table", StringComparison.OrdinalIgnoreCase)) { // Execute command using ODBC connection to create a table try { // create the table using an sql statement string newTableName = newItemValue.ToString(); string sql = "create table " + newTableName + " (ID INT)"; // Create the table using the Odbc connection from the // drive. AccessDBPSDriveInfo di = this.PSDriveInfo as AccessDBPSDriveInfo; if (di == null) { return; } OdbcConnection connection = di.Connection; if (ShouldProcess(newTableName, "create")) { OdbcCommand cmd = new OdbcCommand(sql, connection); cmd.ExecuteScalar(); } } catch (Exception ex) { WriteError(new ErrorRecord(ex, "CannotCreateSpecifiedTable", ErrorCategory.InvalidOperation, path) ); } } // if (String... else if (String.Equals(type, "row", StringComparison.OrdinalIgnoreCase)) { throw new ArgumentException("A row cannot be created under a database, specify a path that represents a Table"); } }// if (PathIsDrive... else { if (String.Equals(type, "table", StringComparison.OrdinalIgnoreCase)) { if (rowNumber < 0) { throw new ArgumentException("A table cannot be created within another table, specify a path that represents a database"); } else { throw new ArgumentException("A table cannot be created inside a row, specify a path that represents a database"); } } //if (String.Equals.... // if path specified is a row, create a new row else if (String.Equals(type, "row", StringComparison.OrdinalIgnoreCase)) { // The user is required to specify the values to be inserted // into the table in a single string separated by commas string value = newItemValue as string; if (String.IsNullOrEmpty(value)) { throw new ArgumentException("Value argument must have comma separated values of each column in a row"); } string[] rowValues = value.Split(','); OdbcDataAdapter da = GetAdapterForTable(tableName); if (da == null) { return; } DataSet ds = GetDataSetForTable(da, tableName); DataTable table = GetDataTable(ds, tableName); if (rowValues.Length != table.Columns.Count) { string message = String.Format(CultureInfo.CurrentCulture, "The table has {0} columns and the value specified must have so many comma separated values", table.Columns.Count); throw new ArgumentException(message); } if (!Force && (rowNumber >= 0 && rowNumber < table.Rows.Count)) { string message = String.Format(CultureInfo.CurrentCulture, "The row {0} already exists. To create a new row specify row number as {1}, or specify path to a table, or use the -Force parameter", rowNumber, table.Rows.Count); throw new ArgumentException(message); } if (rowNumber > table.Rows.Count) { string message = String.Format(CultureInfo.CurrentCulture, "To create a new row specify row number as {0}, or specify path to a table", table.Rows.Count); throw new ArgumentException(message); } // Create a new row and update the row with the input // provided by the user DataRow row = table.NewRow(); for (int i = 0; i < rowValues.Length; i++) { row[i] = rowValues[i]; } table.Rows.Add(row); if (ShouldProcess(tableName, "update rows")) { // Update the table from memory back to the data source da.Update(ds, tableName); } }// else if (String... }// else ... } // NewItem
} // GetTable /// <summary> /// Removes the specified table from the database /// </summary> /// <param name="tableName">Name of the table to remove</param> private void RemoveTable(string tableName) { // validate if tablename is valid and if table is present if (String.IsNullOrEmpty(tableName) || !TableNameIsValid(tableName) || !TableIsPresent(tableName)) { return; } // Execute command using ODBC connection to remove a table try { // delete the table using an sql statement string sql = "drop table " + tableName; AccessDBPSDriveInfo di = this.PSDriveInfo as AccessDBPSDriveInfo; if (di == null) { return; } OdbcConnection connection = di.Connection; OdbcCommand cmd = new OdbcCommand(sql, connection); cmd.ExecuteScalar(); } catch (Exception ex) { WriteError(new ErrorRecord(ex, "CannotRemoveSpecifiedTable", ErrorCategory.InvalidOperation, null) ); } } // RemoveTable
/// <summary> /// Retrieve the list of tables from the database. /// </summary> /// <returns> /// Collection of DatabaseTableInfo objects, each object representing /// information about one database table /// </returns> internal Collection<DatabaseTableInfo> GetTables() { Collection<DatabaseTableInfo> results = new Collection<DatabaseTableInfo>(); // using ODBC connection to the database and get the schema of tables AccessDBPSDriveInfo di = this.PSDriveInfo as AccessDBPSDriveInfo; if (di == null) { return null; } OdbcConnection connection = di.Connection; DataTable dt = connection.GetSchema("Tables"); int count; // iterate through all rows in the schema and create DatabaseTableInfo // objects which represents a table foreach (DataRow dr in dt.Rows) { String tableName = dr["TABLE_NAME"] as String; DataColumnCollection columns = null; // find the number of rows in the table try { String cmd = "Select count(*) from \"" + tableName + "\""; OdbcCommand command = new OdbcCommand(cmd, connection); count = (Int32)command.ExecuteScalar(); } catch { count = 0; } // create DatabaseTableInfo object representing the table DatabaseTableInfo table = new DatabaseTableInfo(dr, tableName, count, columns); results.Add(table); } // foreach (DataRow... return results; } // GetTables
public virtual byte[] GetBytes(int id, string column, bool autoOpenClose) { lock (myOdbcConnection) { byte[] result; try { //open Connection //if (myOdbcConnection.State == ConnectionState.Closed) if (autoOpenClose) myOdbcConnection.Open(); //select string sqlSelectCommand = "SELECT `" + column + "` FROM " + table + " WHERE id=" + id; OdbcCommand myOdbcCommand = new OdbcCommand(sqlSelectCommand, myOdbcConnection); //read all at once result = (byte[])myOdbcCommand.ExecuteScalar(); } catch (OdbcException e) { for (int i = 0; i < e.Errors.Count; i++) { Trace.WriteLine("Odbc-Error #" + i + ":", "Error"); Trace.WriteLine("Message: " + e.Errors[i].Message, "Error"); Trace.WriteLine("Native: " + e.Errors[i].NativeError.ToString(), "Error"); Trace.WriteLine("Source: " + e.Errors[i].Source, "Error"); Trace.WriteLine("SQL: " + e.Errors[i].SQLState, "Error"); } //return with error if (autoOpenClose) myOdbcConnection.Close(); return null; } if (autoOpenClose) myOdbcConnection.Close(); //everything went fine return result; } }
// Upload form information to database after checking to see // if information corresponds to an existing patient. private void RegisterPatient(Hashtable h) { OdbcCommand DbCommand = new OdbcCommand(); //connect to DB and send query try { //check if patient entry already exists in database MyConnection.Open(); DbCommand = buildQueryStatement(h); if (DbCommand.ExecuteScalar() == null || DbCommand.ExecuteScalar().ToString() == "") {//patient is new, commit data MyConnection.Close(); saveDB(h); } else {//patient already exists in DB, kick out error MyConnection.Close(); MessageBox.Show("Patient already registered.\nGo back and try Existing Patient Login."); } } catch (Exception ex) { MessageBox.Show("Error in RegisterPatient: \n" + ex.ToString()); } }
//move on to frmInit private void continueSignup(Hashtable h) { OdbcCommand DbCommand = new OdbcCommand(); try//patient should already exist in DB { MyConnection.Open(); DbCommand = buildQueryStatement(h); // Grab patientID from previously created entry string patientID = DbCommand.ExecuteScalar().ToString(); MyConnection.Close(); //continue on to next form frmInit fInit = new frmInit(patientID); fInit.Show(); this.Visible = false; } catch (Exception ex) { MessageBox.Show("Error in continueSignup:\n" + ex.ToString()); } }
internal object TraerValorSp(string nombre, IDbConnection con, params System.Object[] parametros) { string TipoBase = archivos.nombremotorbase(); if (TipoBase == "Mysql") { MySqlCommand comando = new MySqlCommand(); comando.CommandType = CommandType.StoredProcedure; comando.CommandText = nombre; comando.Connection = con as MySqlConnection; MySqlCommandBuilder.DeriveParameters(comando); int limite = comando.Parameters.Count; try { for (int i = 0; i < limite; i++) { comando.Parameters[i].Value = parametros[i]; } } catch (Exception ex) { throw ex; } return(comando.ExecuteScalar()); } else { if (TipoBase == "sql") { SqlCommand comando = new SqlCommand(); comando.CommandType = CommandType.StoredProcedure; comando.CommandText = nombre; comando.Connection = con as SqlConnection; SqlCommandBuilder.DeriveParameters(comando); int limite = comando.Parameters.Count; try { for (int i = 1; i < limite; i++) { comando.Parameters[i].Value = parametros[i - 1]; } } catch (Exception ex) { throw ex; } return(comando.ExecuteScalar()); } else { System.Data.Odbc.OdbcCommand comando = new System.Data.Odbc.OdbcCommand(); comando.CommandType = CommandType.StoredProcedure; comando.CommandText = nombre; comando.Connection = con as System.Data.Odbc.OdbcConnection; System.Data.Odbc.OdbcCommandBuilder.DeriveParameters(comando); int limite = comando.Parameters.Count; try { for (int i = 1; i < limite; i++) { comando.Parameters[i].Value = parametros[i - 1]; } } catch (Exception ex) { throw ex; } return(comando.ExecuteScalar()); } } }