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