public static object GetSingleValue(string sql, CUBRIDConnection conn)
{
object ret = null;
using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn))
{
ret = cmd.ExecuteScalar();
}
return ret;
}
public static int GetTablesCount(string tableName, CUBRIDConnection conn)
{
int count = 0;
string sql = "select count(*) from db_class where class_name = '" + tableName + "'";
using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn))
{
count = (int)cmd.ExecuteScalar();
}
return count;
}
public static int GetTableRowsCount(string tableName, CUBRIDConnection conn)
{
int count = -1;
string sql = "select count(*) from `" + tableName + "`";
using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn))
{
count = (int)cmd.ExecuteScalar();
}
return count;
}
/// <summary>
/// Gets the databases matching the database name pattern.
/// </summary>
/// <param name="filters">the database name pattern, value is {"database name pattern"}. It should be a string array with Length==1. <para/>
/// If filters == null or Length == 0, the default filters {"%"} is used. If the Length > 1, the first database name is used.<para/></param>
/// <returns> A <see cref="DataTable" /> that contains database schema information and contains columns {"CATALOG_NAME", "SCHEMA_NAME"}</returns>
public DataTable GetDatabases(string[] filters)
{
string catalog = "%";
if (filters != null && filters.Length > 0 && filters[0] != null)
{
catalog = filters[0];
}
const string sql = "SELECT LIST_DBS()";
using (CUBRIDDataAdapter da = new CUBRIDDataAdapter(sql, conn))
{
DataTable dt = new DataTable();
da.Fill(dt);
using (DataTable table = new DataTable("Databases"))
{
table.Columns.Add("CATALOG_NAME", typeof(string));
table.Columns.Add("SCHEMA_NAME", typeof(string));
foreach (DataRow row in dt.Rows) //just one row is returned always
{
//CUBRID returns the list of databases as one single row/one column
string[] dbs = row[0].ToString().Split(' ');
foreach (String dbname in dbs)
{
string sqlDb = String.Format("SELECT COUNT('{0}') FROM db_root WHERE '{1}' LIKE '{2}'", dbname, dbname,
catalog);
using (CUBRIDCommand cmd = new CUBRIDCommand(sqlDb, conn))
{
int count = (int)cmd.ExecuteScalar();
if (count > 0)
{
DataRow newRow = table.NewRow();
newRow[0] = dbname;
newRow[1] = dbname;
table.Rows.Add(newRow);
}
}
}
}
return(table);
}
}
}
/// <summary>
///A connect with URL
///</summary>
public static void Test_ConnectionURL_And_Reset()
{
string strURL = "cci:cubrid:test-db-server:33000:demodb:public::?logSlowQueries=true" +
"&slowQueryThresholdMillis=1000&logTraceApi=true&logTraceNetwork=true" +
"&autoCommit=false&althosts=" +ip+
"&querytimeout=10000&logintimeout=5000";
using (CUBRIDConnection conn = new CUBRIDConnection())
{
conn.ConnectionString = strURL;
conn.Open();
string sqlTablesCount = "select count(*) from db_class where is_system_class='NO' and class_type='CLASS'";
int tablesCount = 0;
using (CUBRIDCommand cmd = new CUBRIDCommand(sqlTablesCount, conn))
{
tablesCount = (int)cmd.ExecuteScalar();
}
Debug.Assert(tablesCount == 12);
conn.Close();
try
{
string strURL2 = "cci:cubrid:test-db-server:33690:demodb:public::?logSlowQueries=true" +
"&slowQueryThresholdMillis=1000&logTraceApi=false&logTraceNetwork=false&autoCommit=false";
conn.ConnectionString = strURL2;
conn.Open();
}
catch(Exception ex)
{
Debug.Assert(ex.Message == "Cannot connect to CUBRID CAS");
}
}
}
/// <summary>
/// Gets the databases matching the database name pattern.
/// </summary>
/// <param name="filters">the database name pattern, value is {"database name pattern"}. It should be a string array with Length==1. <para/>
/// If filters == null or Length == 0, the default filters {"%"} is used. If the Length > 1, the first database name is used.<para/></param>
/// <returns> A <see cref="DataTable" /> that contains database schema information and contains columns {"CATALOG_NAME", "SCHEMA_NAME"}</returns>
public DataTable GetDatabases(string[] filters)
{
string catalog = "%";
if (filters != null && filters.Length > 0 && filters[0] != null)
{
catalog = filters[0];
}
const string sql = "SELECT LIST_DBS()";
using (CUBRIDDataAdapter da = new CUBRIDDataAdapter(sql, conn))
{
DataTable dt = new DataTable();
da.Fill(dt);
using (DataTable table = new DataTable("Databases"))
{
table.Columns.Add("CATALOG_NAME", typeof(string));
table.Columns.Add("SCHEMA_NAME", typeof(string));
foreach (DataRow row in dt.Rows) //just one row is returned always
{
//CUBRID returns the list of databases as one single row/one column
string[] dbs = row[0].ToString().Split(' ');
foreach (String dbname in dbs)
{
string sqlDb = String.Format("SELECT COUNT('{0}') FROM db_root WHERE '{1}' LIKE '{2}'", dbname, dbname,
catalog);
using (CUBRIDCommand cmd = new CUBRIDCommand(sqlDb, conn))
{
int count = (int)cmd.ExecuteScalar();
if (count > 0)
{
DataRow newRow = table.NewRow();
newRow[0] = dbname;
newRow[1] = dbname;
table.Rows.Add(newRow);
}
}
}
}
return table;
}
}
}
/// <summary>
/// Gets the current database.
/// </summary>
/// <returns> </returns>
public string CurrentDatabase()
{
if (State == ConnectionState.Closed)
{
throw new CUBRIDException(Utils.GetStr(MsgId.TheConnectionIsNotOpen));
}
if (!string.IsNullOrEmpty(Database))
return Database;
using (CUBRIDCommand cmd = new CUBRIDCommand("select database()", this))
{
object _obj = cmd.ExecuteScalar();
if (_obj != null)
{
return _obj.ToString();
}
}
return string.Empty;
}
public void CUBRIDCommand_ExecuteScalar_Test()
{
using (CUBRIDConnection conn = new CUBRIDConnection())
{
conn.ConnectionString = DBHelper.connString;
conn.Open();
string sql = "select * from nation order by code asc";
CUBRIDCommand cmd = new CUBRIDCommand(sql, conn);
LogTestStep("Test ExecuteScalar - return a value");
object obj = cmd.ExecuteScalar();
Assert.AreEqual("AFG", obj.ToString());
LogStepPass();
LogTestStep("Test ExecuteScalar - return null");
cmd.CommandText = "drop table if exists test_scalar;";
cmd.ExecuteNonQuery();
cmd.CommandText = "create table test_scalar(a int);";
cmd.ExecuteNonQuery();
cmd.CommandText = "insert into test_scalar values(NULL);";
cmd.ExecuteNonQuery();
cmd.CommandText = "select * from test_scalar";
obj = cmd.ExecuteScalar();
Assert.AreEqual(DBNull.Value, obj);
LogStepPass();
cmd.Close();
LogTestResult();
}
}
