|
|
Hi,
I have a field called stock_value and I want to update the value with either 10 or 20 so t should list the records in random way then update the first as 10, second as 20, thrid as 10, fourth as 20 etc until end of records..
How can I do this?
Thanks,
Jassim
|
|
|
|
|
You don't say what database you're using, so I'll assume it's SQL-Server.
Random ordering you can get by making a variation of this query:
SELECT * FROM table ORDER BY NEWID()
Then you can use the CASE and MOD operators to update every second row.
|
|
|
|
|
Hi everyone, I'm writing a small client application that will access data from Sql Server Database. I would like to know how the SQL that can be used to reset a user's password if the old password is lost or forgotten.
The Sql Server stored procedure sp_password requires the old password but what should be done if the old password is lost in order to reset the user's password. I'm using SQL Server 2005. Thanks in advance.
|
|
|
|
|
sp_ prefix on your procedure implies that you are using a system procedure, just where are you storing your users authentication?
If you are creating a sql server user and assigning authentication/authorisation via sql server object you are using the most difficult model possible. I think most apps have a "functional" ID they use to connect to the database and then internalise the authorisation of their users, much simpler!
You may need to drop and recreate the user.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
There are other alternatives such as alter login.
Also for the sp_password, I think the old password is optional
Check this[^]out
|
|
|
|
|
hi,
You can do one thing,
If user forgot his password then u can collect the unique username and send them a temporally password over mail, or reset the password from the link
NILesh
|
|
|
|
|
hi
is there any way to make backup to database on sql-server-2008 in one button press ?
and any way to make restore to database on sql-server-2008 in one button press ?
any idea ? or direction ?
thanks in advance
|
|
|
|
|
Yup, using SqlCmd (previously ISql). Directions to the Manual[^].
Bastard Programmer from Hell 
|
|
|
|
|
Description Image
http://baymyo.com/gallery/pimage/multi_sql_code_generate_gif-50cf8.gif[^]
Open Source Code, Downlaod Click Here!
MSSQL, ORACLE, MySQL, and OleDb to establish connections to servers via a single layer.
The following block of code to show the connection layer.
public class MConnection : IDisposable
{
#region IDisposable Members
public void Dispose()
{
this.Close();
if (this.m_ClientConnection != null)
this.m_ClientConnection.Dispose();
GC.SuppressFinalize(this);
}
#endregion
#region --- Member ---
IMConnection m_ClientConnection;
internal IMConnection ClientConnection
{
get { return m_ClientConnection; }
}
private MClientProvider m_ClientProvider;
public MClientProvider ClientProvider
{
get { return m_ClientProvider; }
}
private string m_ConnectionString;
public string ConnectionString
{
get { return m_ConnectionString; }
}
private System.Data.ConnectionState m_State;
public System.Data.ConnectionState State
{
get { return m_State; }
}
public System.ComponentModel.ISite Site
{
get
{
return m_ClientConnection.Site;
}
set
{
m_ClientConnection.Site = value;
}
}
public string ServerVersion
{
get
{
return m_ClientConnection.ServerVersion;
}
}
public string Database
{
get
{
return m_ClientConnection.Database;
}
}
public string DataSource
{
get
{
return m_ClientConnection.DataSource;
}
}
#endregion
#region --- Constructor ---
public MConnection(MClientProvider clientProvider)
{
this.m_ClientProvider = clientProvider;
switch (this.m_ClientProvider)
{
case MClientProvider.MSSQL:
this.m_ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["mssqlString"].ConnectionString;
this.m_ClientConnection = new MSSQLConnection(this.m_ConnectionString, this.m_ClientProvider);
break;
case MClientProvider.ORACLE:
this.m_ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["oracleString"].ConnectionString;
this.m_ClientConnection = new ORACLEConnection(this.m_ConnectionString, this.m_ClientProvider);
break;
case MClientProvider.MySQL:
this.m_ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["mysqlString"].ConnectionString;
this.m_ClientConnection = new MySQLConnection(this.m_ConnectionString, this.m_ClientProvider);
break;
case MClientProvider.OleDb:
this.m_ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["oledbString"].ConnectionString;
this.m_ClientConnection = new OLEDBConnection(this.m_ConnectionString, this.m_ClientProvider);
break;
}
}
public MConnection(MClientProvider clientProvider, string connectionString)
{
this.m_ConnectionString = connectionString;
this.m_ClientProvider = clientProvider;
switch (this.m_ClientProvider)
{
case MClientProvider.MSSQL:
this.m_ClientConnection = new MSSQLConnection(this.m_ConnectionString, this.m_ClientProvider);
break;
case MClientProvider.ORACLE:
this.m_ClientConnection = new ORACLEConnection(this.m_ConnectionString, this.m_ClientProvider);
break;
case MClientProvider.MySQL:
this.m_ClientConnection = new MySQLConnection(this.m_ConnectionString, this.m_ClientProvider);
break;
case MClientProvider.OleDb:
this.m_ClientConnection = new OLEDBConnection(this.m_ConnectionString, this.m_ClientProvider);
break;
}
}
#endregion
#region --- Methods ---
public bool Open()
{
try
{
if (this.m_ClientConnection != null)
{
this.m_ClientConnection.Open();
this.m_State = this.m_ClientConnection.State;
return this.m_State.Equals(System.Data.ConnectionState.Open);
}
else
return false;
}
catch (Exception ex)
{
throw ex;
}
}
public void Close()
{
try
{
if (this.m_ClientConnection != null)
{
this.m_ClientConnection.Close();
this.m_State = System.Data.ConnectionState.Closed;
}
}
catch (Exception ex)
{
throw ex;
}
}
public void BeginTransaction()
{
this.m_ClientConnection.BeginTransaction();
}
public void BeginTransaction(System.Data.IsolationLevel iso)
{
this.m_ClientConnection.BeginTransaction(iso);
}
public System.Runtime.Remoting.ObjRef CreateObjRef(Type requestedType)
{
return this.m_ClientConnection.CreateObjRef(requestedType);
}
public new object GetHashCode()
{
return m_ClientConnection.GetHashCode();
}
public object InitializeLifetimeService()
{
return this.m_ClientConnection.InitializeLifetimeService();
}
public object GetLifetimeService()
{
return this.m_ClientConnection.GetLifetimeService();
}
public void ChangeDatabase(string database)
{
this.m_ClientConnection.ChangeDatabase(database);
}
public System.Data.DataTable GetSchema()
{
return this.m_ClientConnection.GetSchema();
}
public System.Data.DataTable GetSchema(string collectionName)
{
return this.m_ClientConnection.GetSchema(collectionName);
}
public System.Data.DataTable GetSchema(string collectionName, string[] restrictionValues)
{
return this.m_ClientConnection.GetSchema(collectionName, restrictionValues);
}
public override bool Equals(object obj)
{
return this.Equals(obj);
}
public override string ToString()
{
return m_ClientConnection.ToString();
}
#endregion
}
The command object!
public class MCommand : IDisposable
{
#region IDisposable Members
public void Dispose()
{
if (this.m_ClientCommand != null)
this.m_ClientCommand.Dispose();
GC.SuppressFinalize(this);
}
#endregion
#region --- Member ---
IMCommand m_ClientCommand;
internal IMCommand ClientCommand
{
get { return m_ClientCommand; }
}
string m_CommandText;
public string CommandText
{
get
{
return m_CommandText;
}
set
{
m_CommandText = value;
}
}
int m_CommandTimeout = 500;
public int CommandTimeout
{
get
{
return m_CommandTimeout;
}
set
{
m_CommandTimeout = value;
}
}
System.Data.CommandType m_CommandType = System.Data.CommandType.Text;
public System.Data.CommandType CommandType
{
get
{
return m_CommandType;
}
set
{
m_CommandType = value;
}
}
MConnection m_Connection;
public MConnection Connection
{
get
{
return m_Connection;
}
set
{
m_Connection = value;
}
}
private MParameterCollection m_Parameters;
public MParameterCollection Parameters
{
get { return m_Parameters; }
set { m_Parameters = value; }
}
private string m_ParamPrefix;
public string ParamPrefix
{
get { return m_ParamPrefix; }
set { m_ParamPrefix = value; }
}
public System.ComponentModel.IContainer Container
{
get
{
return m_ClientCommand.Container;
}
}
public System.ComponentModel.ISite Site
{
get
{
return m_ClientCommand.Site;
}
set
{
m_ClientCommand.Site = value;
}
}
public System.Data.UpdateRowSource UpdatedRowSource
{
get
{
return m_ClientCommand.UpdatedRowSource;
}
set
{
m_ClientCommand.UpdatedRowSource = value;
}
}
#endregion
#region --- Private Constructor ---
internal MCommand(object command, MConnection connection)
{
try
{
this.m_Connection = connection;
switch (connection.ClientProvider)
{
case MClientProvider.MSSQL:
this.m_ClientCommand = new MSSQLCommand(command as System.Data.SqlClient.SqlCommand);
break;
case MClientProvider.ORACLE:
this.m_ClientCommand = new ORACLECommand(command as System.Data.OracleClient.OracleCommand);
break;
case MClientProvider.MySQL:
break;
case MClientProvider.OleDb:
this.m_ClientCommand = new OLEDBCommand(command as System.Data.OleDb.OleDbCommand);
break;
}
this.m_ClientCommand.Connection = connection;
this.m_CommandText = this.m_ClientCommand.CommandText;
this.m_CommandType = this.m_ClientCommand.CommandType;
this.m_CommandTimeout = this.m_ClientCommand.CommandTimeout;
}
catch (Exception ex)
{
throw ex;
}
}
#endregion
public MCommand()
{
this.m_Parameters = new MParameterCollection();
}
public MCommand(string commandText, MConnection connection)
{
this.m_CommandText = commandText;
this.m_Connection = connection;
this.m_Parameters = new MParameterCollection();
}
public MCommand(System.Data.CommandType commandType, string commandText, MConnection connection)
{
this.m_CommandType = commandType;
this.m_CommandText = commandText;
this.m_Connection = connection;
this.m_Parameters = new MParameterCollection();
}
#region --- Private ---
internal void CreateCommand()
{
try
{
switch (this.Connection.ClientProvider)
{
case MClientProvider.MSSQL:
if (string.IsNullOrEmpty(m_ParamPrefix)) m_ParamPrefix = "@";
switch (this.CommandType)
{
case System.Data.CommandType.Text:
this.m_CommandText = this.m_CommandText.Replace(":", m_ParamPrefix).Replace("?", m_ParamPrefix);
break;
}
this.m_ClientCommand = new MSSQLCommand(this.m_CommandType, this.m_CommandText, this.m_CommandTimeout, this.m_Connection);
break;
case MClientProvider.ORACLE:
if (string.IsNullOrEmpty(m_ParamPrefix)) m_ParamPrefix = ":";
switch (this.CommandType)
{
case System.Data.CommandType.Text:
this.m_CommandText = this.m_CommandText.Replace("@", m_ParamPrefix).Replace("?", m_ParamPrefix);
break;
}
this.m_ClientCommand = new ORACLECommand(this.m_CommandType, this.m_CommandText, this.m_CommandTimeout, this.m_Connection);
break;
case MClientProvider.MySQL:
if (string.IsNullOrEmpty(m_ParamPrefix)) m_ParamPrefix = "?";
switch (this.CommandType)
{
case System.Data.CommandType.Text:
this.m_CommandText = this.m_CommandText.Replace("@", m_ParamPrefix).Replace(":", m_ParamPrefix);
break;
}
this.m_ClientCommand = new MySQLCommand(this.m_CommandType, this.m_CommandText, this.m_CommandTimeout, this.m_Connection);
break;
case MClientProvider.OleDb:
if (string.IsNullOrEmpty(m_ParamPrefix)) m_ParamPrefix = "@";
switch (this.CommandType)
{
case System.Data.CommandType.Text:
this.m_CommandText = this.m_CommandText.Replace(":", m_ParamPrefix).Replace("?", m_ParamPrefix);
break;
}
this.m_ClientCommand = new OLEDBCommand(this.m_CommandType, this.m_CommandText, this.m_CommandTimeout, this.m_Connection);
break;
}
if (this.m_Parameters != null)
foreach (MParameter item in this.m_Parameters)
CreateParameter(item.Name, item.Value, item.DbType, item.Direction);
}
catch (Exception ex)
{
throw ex;
}
}
void CreateParameter(string name, object value, MSqlDbType dbtype, System.Data.ParameterDirection direction)
{
try
{
switch (this.Connection.ClientProvider)
{
case MClientProvider.MSSQL:
System.Data.SqlDbType getSqlDbType = MConvert.SqlDbType(dbtype);
switch (getSqlDbType)
{
case System.Data.SqlDbType.Variant:
((System.Data.SqlClient.SqlCommand)this.m_ClientCommand.CommandObject).Parameters.AddWithValue(m_ParamPrefix + name, (value != null) ? value : DBNull.Value).Direction = direction;
break;
default:
System.Data.SqlClient.SqlParameter mssqlParam = ((System.Data.SqlClient.SqlCommand)this.m_ClientCommand.CommandObject).CreateParameter();
mssqlParam.ParameterName = m_ParamPrefix + name;
if (value != null)
mssqlParam.Value = value;
else
mssqlParam.Value = DBNull.Value;
mssqlParam.SqlDbType = getSqlDbType;
mssqlParam.Direction = direction;
((System.Data.SqlClient.SqlCommand)this.m_ClientCommand.CommandObject).Parameters.Add(mssqlParam);
break;
}
break;
case MClientProvider.ORACLE:
System.Data.OracleClient.OracleType getOracleType = MConvert.OracleDbType(dbtype);
switch (getOracleType)
{
case System.Data.OracleClient.OracleType.Blob:
((System.Data.OracleClient.OracleCommand)this.m_ClientCommand.CommandObject).Parameters.AddWithValue(m_ParamPrefix + name, (value != null) ? value : DBNull.Value).Direction = direction;
break;
default:
System.Data.OracleClient.OracleParameter oracleParam = ((System.Data.OracleClient.OracleCommand)this.m_ClientCommand.CommandObject).CreateParameter();
oracleParam.ParameterName = m_ParamPrefix + name;
if (value != null)
oracleParam.Value = value;
else
oracleParam.Value = DBNull.Value;
oracleParam.OracleType = getOracleType;
oracleParam.Direction = direction;
((System.Data.OracleClient.OracleCommand)this.m_ClientCommand.CommandObject).Parameters.Add(oracleParam);
break;
}
break;
case MClientProvider.MySQL:
MySql.Data.MySqlClient.MySqlDbType getMySqlDbType = MConvert.MySqlType(dbtype);
switch (getMySqlDbType)
{
case MySql.Data.MySqlClient.MySqlDbType.Blob:
((MySql.Data.MySqlClient.MySqlCommand)this.m_ClientCommand.CommandObject).Parameters.AddWithValue(m_ParamPrefix + name, (value != null) ? value : DBNull.Value).Direction = direction;
break;
default:
MySql.Data.MySqlClient.MySqlParameter mysqlParam = ((MySql.Data.MySqlClient.MySqlCommand)this.m_ClientCommand.CommandObject).CreateParameter();
mysqlParam.ParameterName = m_ParamPrefix + name;
if (value != null)
mysqlParam.Value = value;
else
mysqlParam.Value = DBNull.Value;
mysqlParam.MySqlDbType = getMySqlDbType;
mysqlParam.Direction = direction;
((MySql.Data.MySqlClient.MySqlCommand)this.m_ClientCommand.CommandObject).Parameters.Add(mysqlParam);
break;
}
break;
case MClientProvider.OleDb:
switch (dbtype)
{
case MSqlDbType.Date:
case MSqlDbType.DateTime:
case MSqlDbType.DateTime2:
case MSqlDbType.SmallDateTime:
value = Convert.ToDateTime(value).ToString("yyyy/MM/dd hh:mm:ss");
break;
}
System.Data.OleDb.OleDbType getOleDbType = MConvert.OleDBType(dbtype);
switch (getOleDbType)
{
case System.Data.OleDb.OleDbType.Empty:
((System.Data.OleDb.OleDbCommand)this.m_ClientCommand.CommandObject).Parameters.AddWithValue(m_ParamPrefix + name, (value != null) ? value : DBNull.Value).Direction = direction;
break;
default:
System.Data.OleDb.OleDbParameter oledbParam = ((System.Data.OleDb.OleDbCommand)this.m_ClientCommand.CommandObject).CreateParameter();
oledbParam.ParameterName = m_ParamPrefix + name;
if (value != null)
oledbParam.Value = value;
else
oledbParam.Value = DBNull.Value;
oledbParam.OleDbType = getOleDbType;
oledbParam.Direction = direction;
((System.Data.OleDb.OleDbCommand)this.m_ClientCommand.CommandObject).Parameters.Add(oledbParam);
break;
}
break;
}
}
catch (Exception ex)
{
throw ex;
}
}
#endregion
#region --- Methods ---
public int ExecuteNonQuery()
{
CreateCommand();
return m_ClientCommand.ExecuteNonQuery();
}
public object ExecuteScalar()
{
CreateCommand();
return m_ClientCommand.ExecuteScalar();
}
public MDataAdapter ExecuteAdapter()
{
CreateCommand();
return new MDataAdapter(this);
}
public System.Data.IDataReader ExecuteReader()
{
CreateCommand();
return m_ClientCommand.ExecuteReader();
}
public System.Data.IDataReader ExecuteReader(System.Data.CommandBehavior commandBehavior)
{
CreateCommand();
return m_ClientCommand.ExecuteReader();
}
public System.Runtime.Remoting.ObjRef CreateObjRef(Type requestType)
{
return m_ClientCommand.CreateObjRef(requestType);
}
public new object GetHashCode()
{
return m_ClientCommand.GetHashCode();
}
public object GetLifetimeService()
{
return m_ClientCommand.GetLifetimeService();
}
public object InitializeLifetimeService()
{
return m_ClientCommand.InitializeLifetimeService();
}
public void Prepare()
{
m_ClientCommand.Prepare();
}
public void ResetCommandTimeout()
{
m_ClientCommand.ResetCommandTimeout();
}
public void Cancel()
{
m_ClientCommand.Cancel();
}
public object Clone()
{
return m_ClientCommand.Clone();
}
#endregion
}
|
|
|
|
|
What is this? is there a question hidden somewhere? this is a discussion forum.
If you want to share something, write an article, don't just dump a bunch of undocumented code.
|
|
|
|
|
I think he/she is developing a taste for Spam.
Unrequited desire is character building. OriginalGriff
I'm sitting here giving you a standing ovation - Len Goodman
|
|
|
|
|
I realize we can't get Bacon all the time.
|
|
|
|
|
internal MCommand(object command, MConnection connection)
{
try
{
this.m_Connection = connection;
switch (connection.ClientProvider)
{
case MClientProvider.MSSQL:
this.m_ClientCommand = new MSSQLCommand(command as System.Data.SqlClient.SqlCommand);
break;
case MClientProvider.ORACLE:
this.m_ClientCommand = new ORACLECommand(command as System.Data.OracleClient.OracleCommand);
break;
case MClientProvider.MySQL:
break;
case MClientProvider.OleDb:
this.m_ClientCommand = new OLEDBCommand(command as System.Data.OleDb.OleDbCommand);
break;
}
this.m_ClientCommand.Connection = connection;
this.m_CommandText = this.m_ClientCommand.CommandText;
this.m_CommandType = this.m_ClientCommand.CommandType;
this.m_CommandTimeout = this.m_ClientCommand.CommandTimeout;
}
catch (Exception ex)
{
throw ex;
}
Loose the switch, and have the connection create[^] a command;
command = connection.CreateCommand();
It'd also be wise to remove the redunant error-handler. Catching an exception merely to rethrow it doesn't make much sense. If you really feel like it, then make it
catch (Exception ex)
{
throw;
}
Bastard Programmer from Hell 
|
|
|
|
|
Seriously, why do you reinvent the wheel, badly.
It's already been done, by Microsoft.
So read up on DBProviderFactories[^] instead. A little on how to use them here[^].
|
|
|
|
|
hello every body 
i'm preparing my first project on database cuz i've just started studying computer science . So i need some help ; i'll be thinkful if someone explain to me the relation that exists between using php as a web programming langage and Mysql ;
|
|
|
|
|
Most introductory books to PHP contain some chapters on MySQL; and vice versa. So I recommend you hit the books.
|
|
|
|
|
many functions are available in php for how to connect to mysql and be interactive between both of them
These functions allow you to access MySQL database servers. More information about MySQL can be found at » http://www.mysql.com/.
Documentation for MySQL can be found at » http://dev.mysql.com/doc/.
|
|
|
|
|
Hello!
I`m creating an application that using Postgres 9.1, a powerful database, steps on toes of the mighty Oracle.
I can`t use Oracle because of it`s price, while Postgres is free and popular. (Microsoft ones are less powerful...)
My question is quite common: How to connect database and C# application? Is there any other way (besides Npgsql lib?)around it?
I`m using VS2010 to create application now.
Thank you.
|
|
|
|
|
Sometime ago, I connected my application to a postgres database for test purposes. I used Npgsql, it works.
I guess ODBC could also be possible, but ODBC is normally not recommended when you can use a specific connection tool (better performance, specific datatypes).
By the way, there are free versions of Oracle available. And during my tests, I found Microsoft SQL Server and Oracle to provide higher performance than postgres (and postgres had higher performance than MySQL), especially when using JOINs with sub queries.
|
|
|
|
|
Thank you for answer Bernard!
Well, my application is going to be sold, so i don`t want for my employer to ask me "What a ...."
So, because Postgres is free for commercial usage (yes, there is a paid edition too, with minor benefits.) and has a quite a lot of data about it. (Most of the users favour linux for some strange reason...")
Oracle... well, it`s size is far larger than Postgres`s ~100mb and the target database itself is several GB`s, Oracle can handle this, without question, but it`s main role is huge enterprise databases of large companies.
I`ve read article on Oracle vs SQL and author shown two pictures: Oracle - Swiss Army Knife, SQL - pink assault rifle toy for kids.
SQL is bad work of Microsoft, more errors per code then in Oracle and Postgres, lower perfomance as well.
|
|
|
|
|
While I will allow the lower performance in Oracle vs SQL Server I would dispute the Swiss/pink comparison. If you are doing anything less that enterprise level database work SQL Server is by far the best (paid) solution. If you can work under 4gb in database size then SQL Server Express is the best "free" solution for your requirements.
Oracle is without doubt the workhorse of databases whereas SQL server is what I call a departmental database. In that area it is the absolute best solution bar none. It has the widest support base of any database, performance is excellent in anything less than enterprise (multi million row tables). Development tools have any other IDE for breakfast (TOAD is a pile of complex crap b/c is designed to sit over Oracle).
If you are expecting to have less than 4gb of data then I would recommend SQL Server Express, if it is greater then I would look very closely at the support resources for the database you choose to use.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hello Mycroft Holmes!
I agree on Oracle.
SQL Server Express is nice solution, but it`s paid (if DB is larger than 4GB) and this is the main problem (Current DB is 3.96 GB and expanding...). I was tasked to create app that require close to zero cash on any 3rd party components (It was, it is and it`ll be - employer always looking for something that would cost 0$ and be as functional, stable and reliable as hell)
So Postgres is a necessary option because it`s free.
|
|
|
|
|
Aleonis wrote: employer always looking for something that would cost 0$ and be as functional
And yet it may end up costing substantial amounts in down stream support. This is something most organisations do not consider and it is well known that support cost are more than development costs!
Still I would hate to lose all the options like postgres so I hope it works and works well and it does sound like it is the right tool for your job.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Yeah, it`s like you work as a lead producer in EA and suddenly someone bestows upon you a free Aston Martin DBS... While car is yours for free, it`s repairs, fuel, taxes and other cash drains will become a great burden & start drain your budget. Free gift becoming surgical strike at the company`s budget.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
