This thing is what I learned when I learn C # and SQL2000, pure personal works.
Using system;
Using system.collections.specialized;
Using system.data;
Using system.text;
Using DataAccess;
// Name: SQLManager
// Author: firstrose
// Description: Some information from the SQL database
Namespace sqlcmp.module
{
///
/// SQLMNGR's summary description.
///
Public Class Sqlmanager
{
Public Sqlmanager ()
{
//
// TODO: Add constructor logic here
//
CnSQL ();
}
Private stringbuilder SQL = New StringBuilder (50);
Private DataAccessSQL Dasql = New DataAccessSQL ();
Private bool connection = false;
PRIVATE DATATABLE DTCACHE;
Public stringcollection results = new stringcollection ();
Private static sqlmanager _INSTANCE;
Public Static Sqlmanager getInstance
{
get
{
IF (_Instance == null)
{
_INSTANCE = New Sqlmanager ();
}
IF (! _Instance.connected)
{
"New ApplicationException (" SQL Server is not connected ");
}
Return_INSTANCE;
}
}
Public bool connSQL ()
{
CONNECTED = dasql.conn ();
Return connect;
}
Public void disconnsql ()
{
Dasql.disconn ();
Connected = false;
}
///
/ / Database for exhaustion SQL instance
///
Public bool enumsqldb ()
{
Bool flag = false;
IF (Connected)
{
SQL.Length = 0;
SQL.Append ("Use Master;");
SQL.Append ("Select Name from SysDatabases");
Dasql.sqlselect (sql.tostring ());
DTCache = dasql.getdatanet.tables ["main"]. copy ();
RESULT.CLEAR ();
IF (DTCache.Rows.count> 0)
{
// has data
Foreach (DataRow Temp in DTCache.ROWS)
{
Result.add (String) Temp ["Name"]);
}
Flag = true;
}
Else
{}
}
Else
{}
Return flag;
}
///
/// Change the default database
///
///
Public void changesqldb (string dbname) {
IF (Connected && (DBNAME! = NULL) && (dbname.length! = 0)))))
{
SQL.Length = 0;
SQL.AppendFormat ("Use {0};", dbname);
Dasql.sqlselectnoreSult (Sql.toString ());
}
Else
{}
}
///
/// get all the table names in a database
///
Public Bool GetTables (String Dbname, Bool Useronly)
{
Bool flag = false;
IF (Connected && (DBNAME! = NULL) && (dbname.length! = 0)))))
{
SQL.Length = 0;
SQL.Append ("Use Master;");
SQL.Appendformat ("Select Count (1) from sysdatabases where name = '{0}'", DBNAME);
RESULT.CLEAR ();
IF ((int) dasql.sqlselectsingLevalue (sql.tostring ()) == 1)
{
// Data stock
SQL.Length = 0;
SQL.AppendFormat ("Use {0};", dbname);
SQL.Append ("SELECT NAME, ID from SysoBjects);
IF (useronly)
{
SQL.Append ("where xtype = 'u' and status> 0");
}
Else
{
SQL.Append ("where xtype = 'u' or xtype = 's'");
}
Dasql.sqlselect (sql.tostring ());
DTCache = dasql.getdatanet.tables ["main"]. copy ();
RESULT.CLEAR ();
IF (DTCache.Rows.count> 0)
{
Foreach (DataRow Temp in DTCache.ROWS)
{
Result.add (String) Temp ["Name"]);
}
}
Else
{}
Flag = true;
}
Else
{}
}
Else
{}
Return flag;
}
///
/// Name of all stored procedures in the database
///
/// DBNAME is null or empty, use the current database
Public Bool Getspname (String Dbname, Bool Useronly)
{
Bool flag = false;
IF (Connected)
{
SQL.Length = 0;
IF (DBNAME! = null) && (dbname.length! = 0))
{
SQL.AppendFormat ("Use {0};", dbname);
}
Else
{}
SQL.Append ("Select Name from sysobjects where xtype = 'p'");
IF (useronly)
{
SQL.Append ("and status> = 0");
Else
{}
Dasql.sqlselect (sql.tostring ());
DTCache = dasql.getdatanet.tables ["main"]. copy ();
RESULT.CLEAR ();
IF (DTCache.Rows.count> 0)
{
// There is a stored procedure
Foreach (DataRow Temp in Dasql.getDataSet.tables ["main"]. ROWS)
{
Result.add (String) Temp ["Name"]);
}
Flag = true;
}
Else
{}
}
Else
{}
Return flag;
}
///
// / get the content of the current database to specify the stored procedure
///
Public Bool Getsptext (String SPNAME)
{
Bool flag = false;
IF (Connected && (SPNAME! = null) && (spname.length! = 0))
{
SQL.Length = 0;
SQL.Appendformat ("Select syscomments.text from syscomments left join sysobjects on systemments.id = sysObjects.id where sysobjects.name = '{0}'", SPNAME);
Object temp = dasql.sqlselectsingLEVALUE (SQL.TOString ());
IF (Temp! = NULL)
{
RESULT.CLEAR ();
Result.add ((String) TEMP);
Flag = true;
}
Else
{}
}
Else
{}
Return flag;
}
///
/// Get all list names in the table
///
Public Bool EnumTableColumns (String TableName)
{
Bool flag = false;
IF && (TableName! = null "&& (TableName.Length! = 0))
{
SQL.Length = 0;
SQL.Appendformat ("Select syscolumns.name from syscolumns left outer join sysobjects on syscolumns.id = sysObjects.id where sysobjects.name = '{0}'", TABLENAME);
Dasql.sqlselect (sql.tostring ());
DTCache = dasql.getdatanet.tables ["main"]. copy ();
RESULT.CLEAR ();
IF (dtcache.rows.count> 0) // routine check
{
Foreach (DataRow Temp in Dasql.getDataSet.tables ["main"]. ROWS)
{
Result.add (String) Temp ["Name"]);
}
Flag = true;
}
Else
{}
}
Else
{}
Return flag;
}
}
}
