/// <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());
}
}
}