using System;
using System.ComponentModel;
using System.Collections;
using System.Data;
using System.Diagnostics;
using System.Data.SqlClient;
using rasp;
using rasp.SQL;
using rasp.SQL.SQLStatement;
using rasp.SQL.DBInfo;
namespace rasp.SQL
{
/// <summary>
/// The SQLUtilities class contains many public static members that will assist in many of the day to day SQL functions.
/// </summary>
public class SQLUtilities {
public static void SaveLoginIPAddress(int UserID, BasePage container) {
//the IP address of the client
string ip = container.Request.UserHostAddress;
string update = "update _Users set LastIP = '" + ip + "' where id = " + UserID;
DataAdapter a = DataAdapterCollection.GetAdapter("_Users");
a.UpdateCommand = new SqlCommand(update);
a.UpdateCommand.Connection = Constants.SQLConnection;
a.UpdateCommand.ExecuteNonQuery();
}
/// <summary>
/// Selects the data from the default DB denoted by the given SelectStatement.
/// </summary>
/// <param name="SelectStatement">A select statement used to grab data from the DB</param>
/// <returns>A DataSet with the resulting table of data.</returns>
public static DataSet SelectData(string SelectStatement) {
SqlConnection conn = Constants.SQLConnection;
DataSet ds = new DataSet();
try {
SqlDataAdapter select = new SqlDataAdapter(SelectStatement, conn);
select.Fill(ds);
} catch (Exception ex) {
throw new Exception("Error executing SQL, statement =\r\n" + SelectStatement + " Original Exception Message: " + ex.Message);
}
return ds;
}
/// <summary>
/// Gets a row of data from the adapter given the key field's ID.
/// </summary>
/// <param name="adapter">The adapter or table from which to get the row.</param>
/// <param name="ID">An ID of a row in the adapter.</param>
/// <returns>A datarow with the correct row contained within it.</returns>
public static DataRow GetRow(DataAdapter adapter, int ID) {
return GetRows(adapter, ID).Tables[0].Rows[0];
}
//TODO: Fix this to accept a field as well.
/// <summary>
/// Gets a row of data from the adapter given the key field's ID.
/// </summary>
/// <param name="adapter">The adapter or table from which to get the row.</param>
/// <param name="ID">An ID of a row in the adapter.</param>
/// <returns>A dataset with the correct row contained within it.</returns>
public static DataSet GetRows(DataAdapter adapter, int ID) {
string keyField = adapter.TableInfomation.KeyField;
string select = adapter.SelectCommand.CommandText + " where " + keyField + " = " + ID;
return SQLUtilities.SelectData(select);
/* Maybe this way??
string keyField = adapter.TableInfomation.KeyField;
Statement temp = new Statement(adapter.SelectCommand.CommandText);
temp.WhereList.RemoveAll();
temp.WhereList.AddClause(keyField, ID.ToString());
string select = temp.SQLStatement;
return SQLUtilities.SelectData(select);
*/
}
/// <summary>
/// Gets dropdown data from a table given the LookupInfo associated with the table.
/// </summary>
/// <param name="li">A LookupInfo object pulled from the DB.</param>
/// <returns>A dataset containing a table with columns ID and Option.</returns>
public static DataSet GetDropDownData(LookupInfo li) {
string junk = li.SourceTable;
string ddTable = li.SourceTable;
string select = li.SelectStatement;
DataSet ds;
try {
ds = SQLUtilities.SelectData(select);
} catch {
throw new Exception("Error executing SQL Statement in GetDropDownData, SQL Statement = " + select);
}
return ds;
}
/// <summary>
/// Deletes a row in a table given it's unique ID.
/// </summary>
/// <param name="adapter">The adapter from which you want to remove a row.</param>
/// <param name="ID">The ID of the row that you wish to delete.</param>
/// <returns>A flag indicating if the delete was successful or not.</returns>
public static bool DeleteRow(DataAdapter adapter, int ID) {
string keyField = adapter.TableInfomation.KeyField;
string table = adapter.TableInfomation.ActualName;
string delete = "delete from " + table + " where " + keyField + " = " + ID;
SqlCommand deleteCommand = new SqlCommand(delete, Constants.SQLConnection);
adapter.DeleteCommand = new SqlCommand(delete, deleteCommand.Connection);
adapter.DeleteCommand.CommandText = delete;
adapter.DeleteCommand.Prepare();
bool sucess = adapter.DeleteCommand.ExecuteNonQuery() == 1;
adapter.DeleteCommand = deleteCommand;
return sucess;
}
/// <summary>
/// Converts a String representation of a SqlDbType string to a actual SqlDbType object.
/// </summary>
/// <param name="Type">The string that represents the appropriate type.</param>
/// <returns>A SqlDbType that that is appropriate for the given string.</returns>
/// <exception cref="System.Exception">Throws an exception if the string does not match any of the strings defined in this method.</exception>
public static SqlDbType GetDbType(string Type) {
SqlDbType ret;
switch (Type) {
case "bigint": ret = SqlDbType.BigInt; break;
case "binary": ret = SqlDbType.Binary; break;
case "bit": ret = SqlDbType.Bit; break;
case "char": ret = SqlDbType.Char; break;
case "datetime": ret = SqlDbType.DateTime; break;
case "decimal": ret = SqlDbType.Decimal; break;
case "float": ret = SqlDbType.Float; break;
case "image": ret = SqlDbType.Image; break;
case "int": ret = SqlDbType.Int; break;
case "money": ret = SqlDbType.Money; break;
case "nchar": ret = SqlDbType.NChar; break;
case "ntext": ret = SqlDbType.NText; break;
case "numeric": ret = SqlDbType.Int; break;
case "nvarchar": ret = SqlDbType.NVarChar; break;
case "real": ret = SqlDbType.Real; break;
case "smalldatetime": ret = SqlDbType.SmallDateTime; break;
case "smallint": ret = SqlDbType.SmallInt; break;
case "smallmoney": ret = SqlDbType.SmallMoney; break;
case "sql_variant": ret = SqlDbType.Variant; break;
case "text": ret = SqlDbType.Text; break;
case "timestamp": ret = SqlDbType.Timestamp; break;
case "tinyint": ret = SqlDbType.TinyInt; break;
case "uniqueidentifier": ret = SqlDbType.UniqueIdentifier; break;
case "varbinary": ret = SqlDbType.VarBinary; break;
case "varchar": ret = SqlDbType.VarChar; break;
default:
throw new Exception("Unknown SqlDbType: " + Type);
break;
}
return ret;
}
/// <summary>
/// Gets the default DataSet of an Adapter.
/// </summary>
/// <param name="adapter">An adapter from which to select the data.</param>
/// <returns>A DataSet containing the table of data.</returns>
public static DataSet GetDataSet(DataAdapter adapter){
DataSet ds = new DataSet();
adapter.Fill(ds);
return ds;
}
/// <summary>
/// Turns a DataSet into a meaningful string of the values that are contained within that dataset. Usefull for debugging.
/// TODO: Global searh for calls to this function before releasing the code, it's not needed for anything usefull.
/// </summary>
/// <param name="ds">A DataSet that you wish to see the contents of.</param>
/// <returns>A string with lines of readable text that represents the contents of the dataset.</returns>
public static string GetDataSetValue(DataSet ds) {
string ret = "";
foreach(DataTable t in ds.Tables) {
ret += t.TableName + ":\r\n";
int j = 0;
foreach(DataRow r in t.Rows) {
ret += "RowNumber" + j++ +": ";
for(int i=0;i<r.ItemArray.Length;++i) {
ret += i+ " = '" + r[i] + "', ";
}
ret += "\r\n";
}
}
return ret;
}
/// <summary>
/// the SysFiles table looks like:
/// fileid groupid size maxsize growth status perf name filename
/// </summary>
/// <returns></returns>
public static string GetDataBasePath() {
string ret = "";
string select = "select top 1 filename from sysfiles";
DataSet ds = SQLUtilities.SelectData(select);
string filename = (string)ds.Tables[0].Rows[0][0];
ret = new System.IO.FileInfo(filename).DirectoryName;
return ret;
}
/// <summary>
/// Changes a users password given the _Users_ID and the new password.
/// </summary>
/// <param name="id">The User ID.</param>
/// <param name="password1">The new password.</param>
/// <param name="password2">The new password confirmed.</param>
/// <returns>If the password was changed successfully.</returns>
public static bool ChangePassword(int id, string password1, string password2) {
if (password1.Equals(password2)) {
return ChangePassword(id,password1);
} else {
return false;
}
}
/// <summary>
/// Changes a users password given the _Users_ID and the new password.
/// </summary>
/// <param name="id">The User ID.</param>
/// <param name="password">The new password</param>
/// <returns>If the password was changed successfully.</returns>
//TODO: This needs to be done with Owner_ID Concepts for rows so that everyone can edit their own _Users row.
public static bool ChangePassword(int id, string password) {
DataAdapter a = DataAdapterCollection.GetAdapter(DBNames._USERS.TableName);
DataRow dr = SQLUtilities.GetRow(a, id);
dr[DBNames._USERS.Password] = password;
return a.Update(dr.Table.DataSet) == 1;
return false;
}
/// <summary>
/// Gets the table name of the table represented by the provided _Tables_ID.
/// </summary>
/// <param name="TableID">The ID of the table in the _Tables table.</param>
/// <returns>A string that is the tablename.</returns>
public static string GetTableName(int TableID) {
string select = "select name from _TABLES where id = " + TableID;
DataSet ds = SQLUtilities.SelectData(select);
return (string)ds.Tables[0].Rows[0][0];
}
/// <summary>
/// Returns all fields from the sysobjects where object type is 'U'
/// </summary>
/// <returns>All fields from the sysobjects where object type is 'U'</returns>
public static DataSet GetTableNames() {
return SQLUtilities.SelectData(("SELECT * FROM sysobjects WHERE (xtype = 'U') order by name"));
}
/// <summary>
/// Gets the info from SYSCOLUMNS for a specific tablename
/// </summary>
/// <param name="TableName">A tablename</param>
/// <returns>A DataSet object with a table that has the correct data in it.</returns>
public static DataSet GetTableDef(string TableName) {
DataSet ret = new DataSet();
try {
string TableDefSelectString = "SELECT name AS Name, (SELECT top 1 name FROM systypes s WHERE s.xtype = c.xtype) AS DataType," +
" length AS Length, xprec AS [Precision], isnullable AS IsNullable, colstat AS [Identity] FROM syscolumns c WHERE (id = " +
"(SELECT id FROM sysobjects o WHERE o.name = '"+ TableName +"' AND o.xtype = 'U'))";
ret = SQLUtilities.SelectData(TableDefSelectString);
} catch {
}
return ret;
}
/// <summary>
/// Gets the data from sysobjects where the rows represent fields and belong to the provided tablename.
/// </summary>
/// <param name="TableName">A tablename to get the field info from the DB.</param>
/// <returns>A DataSet with rows of field information for the provided table.</returns>
public static DataSet GetTableInfo(string TableName) {
return SQLUtilities.SelectData("SELECT * FROM sysobjects WHERE (xtype = 'U' and name='"+ TableName +"' )");
}
/// <summary>
/// Returns our _TABLES table joined with the syscolumns table on the tablename. Returns the following fields:
/// _FIELDS (ID, _TABLES_ID, Name, Caption, VirtualName, IsNulable, IsHidden, DefaultValue, syscolumns (name, DataType, Length, Precision, _IsNullable and Identity)
/// </summary>
/// <param name="TableName"></param>
/// <returns>_FIELDS (ID, _TABLES_ID, Name, Caption, VirtualName, IsNulable, IsHidden, DefaultValue, syscolumns (name, DataType, Length, Precision, _IsNullable and Identity)</returns>
public static DataSet GetFieldInfo(string TableName) {
string select = "select c.name, f.id, f._TABLES_id, f._TABS_id, f.caption, f.virtualname, f.ishidden, f.isdisplayed, f.defaultvalue, " +
"f.islisted, f.iseditable, f._LOOKUPS_ID, f.taborder, f.displayorder, f._DISPLAYTYPES_ID, f._DATAFORMATS_ID, f._HELP_ID, " +
"(select name from sysobjects where name = '"+TableName+"' and xtype = 'u') as [TableName], f.Description as [Description], " +
"(select top 1 name from systypes where c.xtype = systypes.xtype) as [DataType], c.length as Length, c.prec as [Precision], " +
"c.isnullable as [_IsNullable], c.colstat as [Identity] from syscolumns c " +
"left outer join _FIELDS f on c.name = f.name and f._TABLES_ID = (select ID from _TABLES where name = '"+TableName+"') " +
"where (select id from sysobjects where name = '"+TableName+"' and xtype = 'u') = c.id";
DataSet Tables = SQLUtilities.SelectData(select);
return Tables;
}
/// <summary>
/// Returns our _TABLES table joined with the syscolumns table on the tablename and the fieldname. Returns the following fields:
/// _FIELDS (ID, _TABLES_ID, _LOOKUPS_ID, Name, Caption, VirtualName, IsNulable, IsHidden, DefaultValue, syscolumns (name, DataType, Length, Precision, _IsNullable and Identity)
/// </summary>
/// <param name="TableName"></param>
/// <param name="FieldName"></param>
/// <returns>_FIELDS (ID, _TABLES_ID, _LOOKUPS_ID, Name, Caption, VirtualName, IsNulable, IsHidden, DefaultValue, syscolumns (name, DataType, Length, Precision, _IsNullable and Identity)</returns>
public static DataSet GetJoinedFieldInfo(string TableName, string FieldName) {
string select = "select c.name, f.id, f._TABLES_id, f._TABS_id, f.caption, f.virtualname, f.ishidden, f.isdisplayed, f.defaultvalue, " +
"f.islisted, iseditable, f._LOOKUPS_ID, f.taborder, f.displayorder, f._DISPLAYTYPES_ID, f._DATAFORMATS_ID, f._HELP_ID, " +
"(select name from sysobjects where name = '"+TableName+"' and xtype = 'u') as [TableName], f.Description as [Description], " +
"(select top 1 name from systypes where c.xtype = systypes.xtype) as [DataType], c.length as Length, c.prec as [Precision], " +
"c.isnullable as [_IsNullable], c.colstat as [Identity] " +
"from _FIELDS f, syscolumns c where f._TABLES_ID = (select ID from _TABLES where name = '"+TableName+"') " +
"and c.id = (select id from sysobjects where name = '"+TableName+"' and xtype = 'u') " +
"and f.name = '"+FieldName+"' and c.name = '"+FieldName+"'";
return SQLUtilities.SelectData(select);
}
/// <summary>
/// Gets a BasePage of data. A BasePage consists of rows starting at the Statements StartAt property, and counting to the
/// Constants.Settings.DefaultPageSize property.
/// </summary>
/// <param name="sb">A Statement object.</param>
/// <returns>The DataSet with the appropriate BasePage of data.</returns>
public static DataSet GetDataPage(Statement sb) {
// Does this REALLY need a DataAdapter?
DataSet ret = new DataSet();
DataAdapter da = new DataAdapter(sb.SQLStatement);
da.Fill(ret,sb.StartAt,Constants.Settings.DefaultPageSize, sb.adapter.TableInfomation.ActualName);
return ret;
}
}
}
Submit an article or tip