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