public static string[] getTables(Connection conn, string dbName) { ArrayList tbls = new ArrayList(); SqlConnection sqlconn = new SqlConnection(conn.connectionString()); sqlconn.Open(); SqlCommand cmd = new SqlCommand(dbName + ".dbo.sp_tables", sqlconn); cmd.CommandType = CommandType.StoredProcedure; SqlDataReader rdr = cmd.ExecuteReader(); while (rdr.Read()) { if ( Convert.ToString(rdr["TABLE_TYPE"]) == "TABLE" && Convert.ToString(rdr["TABLE_OWNER"]) != "sys" ) tbls.Add(rdr["TABLE_NAME"]); } rdr.Close(); return tbls.ToArray(typeof(string)) as string[]; }
public static bool hasIdentity(Connection conn, string dbName, string tblName) { //var sql = String.Format(@"SELECT COUNT(*) FROM {0}.SYS.IDENTITY_COLUMNS WHERE OBJECT_NAME(OBJECT_ID) = '{1}'", dbName, tblName); var sql = String.Format(@"USE {0} SELECT OBJECTPROPERTY(OBJECT_ID('{1}'), 'TableHasIdentity')", dbName, tblName); SqlConnection sqlconn = new SqlConnection(conn.connectionString()); sqlconn.Open(); SqlCommand cmd = new SqlCommand(sql,sqlconn); if ((int)cmd.ExecuteScalar() == (int)0) return false; else return true; }
private ArrayList columns(Connection conn) { ArrayList cols = new ArrayList(); SqlConnection sqlconn = new SqlConnection(conn.connectionString()); sqlconn.Open(); SqlCommand cmd = new SqlCommand(m_database+".dbo.sp_columns", sqlconn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(new SqlParameter("@table_name", m_table)); // execute the command SqlDataReader rdr = cmd.ExecuteReader(); while (rdr.Read()) { cols.Add(rdr["COLUMN_NAME"]); } rdr.Close(); return cols; }
private List<Hashtable> tableData(ArrayList cols, Connection conn) { // limiting results? string limit = ""; string order_by = ""; // if negative number for LIMIT then order descending (by first column) if (m_limit_results != 0) { // get top n limit = String.Format(@"TOP {0} ", Math.Abs(m_limit_results).ToString()); // ordering order_by = "ORDER BY " + cols[0]; if (m_limit_results < 0) order_by += " DESC"; } string command = String.Format(@"SELECT {0}* FROM [{1}].[dbo].[{2}] WITH(NOLOCK) {3} {4}", limit, m_database, m_table, m_where_clause, order_by); SqlConnection sqlconn = new SqlConnection(conn.connectionString()); sqlconn.Open(); SqlCommand cmd = new SqlCommand(command, sqlconn); SqlDataReader rdr = cmd.ExecuteReader(); List<Hashtable> rows = new List<Hashtable>(); while (rdr.Read()) { Hashtable row = new Hashtable(); foreach (string c in cols) { row.Add(c, rdr[c]); } rows.Add(row); } rdr.Close(); return rows; }
static List<string> getAllDatabases(Connection connstr) { List<string> dbs = new List<string>(); string command = "SELECT " + " name " + "FROM " + " master.dbo.sysdatabases " + "WHERE " + " name NOT IN ( 'master', 'model', 'msdb', 'tempdb' ) " + "ORDER BY " + " name"; SqlConnection cn = new SqlConnection(connstr.connectionString()); cn.Open(); SqlCommand cmd = new SqlCommand(command, cn); // issue the query SqlDataReader rdr = null; try { rdr = cmd.ExecuteReader(); if (rdr != null && !rdr.IsClosed) { if (rdr.HasRows) { while (rdr.Read()) { dbs.Add(rdr["name"].ToString()); } } } } catch (Exception e) { Console.WriteLine("\nUnable to retrieve list of databases from server \n"); Console.WriteLine(e); } return dbs; }