Any way to optomize this?
-
public DataSet GetEntries() { //top 5 string ConnectionString = ConfigurationSettings.AppSettings.Get("connString"); SqlConnection SQLConnection = new SqlConnection(ConnectionString); string SQLView = "CREATE VIEW Content_View AS " + "SELECT TOP 10000 [HJ_BLog].* " + "FROM [HJ_BLog] " + "ORDER BY Date DESC "; SqlCommand SQLCommandCreateView = new SqlCommand(SQLView, SQLConnection); SqlDataAdapter DataAdapter = new SqlDataAdapter(SQLCommandCreateView); DataSet UserSet = new DataSet(); DataAdapter.Fill(UserSet); SQLCommandCreateView.Connection.Close(); string SQLQueryTop2 = "SELECT TOP 5 [Content_View].* " + "FROM [Content_View] "; SqlCommand SQLCommandTop2 = new SqlCommand(SQLQueryTop2,SQLConnection); SqlDataAdapter DataAdapterTop2 = new SqlDataAdapter(SQLCommandTop2); DataSet UserSetTop2 = new DataSet(); DataAdapterTop2.Fill(UserSetTop2); SQLCommandTop2.Connection.Close(); string SQLDropView = "DROP VIEW Content_View "; SqlCommand SqlDropView = new SqlCommand(SQLDropView,SQLConnection); SqlDropView.Connection.Open(); SqlDropView.ExecuteNonQuery(); SqlDropView.Connection.Close(); return UserSetTop2; } Pretty much using a view to sort then getting the top 5 from the view then deleting the view. Is there an easier way to do this? I am not sure the preformence hits on creating and droping views. or just a all around easier way to do this. thanks any points will be great
-
public DataSet GetEntries() { //top 5 string ConnectionString = ConfigurationSettings.AppSettings.Get("connString"); SqlConnection SQLConnection = new SqlConnection(ConnectionString); string SQLView = "CREATE VIEW Content_View AS " + "SELECT TOP 10000 [HJ_BLog].* " + "FROM [HJ_BLog] " + "ORDER BY Date DESC "; SqlCommand SQLCommandCreateView = new SqlCommand(SQLView, SQLConnection); SqlDataAdapter DataAdapter = new SqlDataAdapter(SQLCommandCreateView); DataSet UserSet = new DataSet(); DataAdapter.Fill(UserSet); SQLCommandCreateView.Connection.Close(); string SQLQueryTop2 = "SELECT TOP 5 [Content_View].* " + "FROM [Content_View] "; SqlCommand SQLCommandTop2 = new SqlCommand(SQLQueryTop2,SQLConnection); SqlDataAdapter DataAdapterTop2 = new SqlDataAdapter(SQLCommandTop2); DataSet UserSetTop2 = new DataSet(); DataAdapterTop2.Fill(UserSetTop2); SQLCommandTop2.Connection.Close(); string SQLDropView = "DROP VIEW Content_View "; SqlCommand SqlDropView = new SqlCommand(SQLDropView,SQLConnection); SqlDropView.Connection.Open(); SqlDropView.ExecuteNonQuery(); SqlDropView.Connection.Close(); return UserSetTop2; } Pretty much using a view to sort then getting the top 5 from the view then deleting the view. Is there an easier way to do this? I am not sure the preformence hits on creating and droping views. or just a all around easier way to do this. thanks any points will be great
public DataSet GetEntries()
{
string sql = "SELECT TOP 5 * FROM [HJ_BLog] ORDER BY Date DESC";
string ConnectionString = ConfigurationSettings.AppSettings.Get("connString");
SqlConnection conn;
DataSet ds;
SqlCommand cmd;
SqlDataAdapter da;try
{
conn = new SqlConnection(ConnectionString);
cmd = new SqlCommand(sql, conn);
da = new SqlDataAdapter(cmd);
ds = new DataSet();
da.Fill(ds);
}
catch (Exception ex)
{
// do something...
}
finally
{
if (da != null) da.Dispose();
if (cmd != null) cmd.Dispose();
if (conn != null) conn.Dispose();
}return ds;
} -
public DataSet GetEntries() { //top 5 string ConnectionString = ConfigurationSettings.AppSettings.Get("connString"); SqlConnection SQLConnection = new SqlConnection(ConnectionString); string SQLView = "CREATE VIEW Content_View AS " + "SELECT TOP 10000 [HJ_BLog].* " + "FROM [HJ_BLog] " + "ORDER BY Date DESC "; SqlCommand SQLCommandCreateView = new SqlCommand(SQLView, SQLConnection); SqlDataAdapter DataAdapter = new SqlDataAdapter(SQLCommandCreateView); DataSet UserSet = new DataSet(); DataAdapter.Fill(UserSet); SQLCommandCreateView.Connection.Close(); string SQLQueryTop2 = "SELECT TOP 5 [Content_View].* " + "FROM [Content_View] "; SqlCommand SQLCommandTop2 = new SqlCommand(SQLQueryTop2,SQLConnection); SqlDataAdapter DataAdapterTop2 = new SqlDataAdapter(SQLCommandTop2); DataSet UserSetTop2 = new DataSet(); DataAdapterTop2.Fill(UserSetTop2); SQLCommandTop2.Connection.Close(); string SQLDropView = "DROP VIEW Content_View "; SqlCommand SqlDropView = new SqlCommand(SQLDropView,SQLConnection); SqlDropView.Connection.Open(); SqlDropView.ExecuteNonQuery(); SqlDropView.Connection.Close(); return UserSetTop2; } Pretty much using a view to sort then getting the top 5 from the view then deleting the view. Is there an easier way to do this? I am not sure the preformence hits on creating and droping views. or just a all around easier way to do this. thanks any points will be great
Hi Jacob, I have a couple of fairly phylisophical points to add. Ignore them as you please :). First off, it's not a really good idea to change database schema from within your code. Creating and deleting view (and table and sprocs, etc) are really design-time processes. Do it before-hand and be done. Why? Well, what happens when user #2 tries to create the view before user #1 drops? You've got an exception to handle... It just get's too messy. Related to the first point, I am very leary of a program running with a sql user that has sufficient privileges to alter the db schema. Just my $0.02 Bill
-
public DataSet GetEntries()
{
string sql = "SELECT TOP 5 * FROM [HJ_BLog] ORDER BY Date DESC";
string ConnectionString = ConfigurationSettings.AppSettings.Get("connString");
SqlConnection conn;
DataSet ds;
SqlCommand cmd;
SqlDataAdapter da;try
{
conn = new SqlConnection(ConnectionString);
cmd = new SqlCommand(sql, conn);
da = new SqlDataAdapter(cmd);
ds = new DataSet();
da.Fill(ds);
}
catch (Exception ex)
{
// do something...
}
finally
{
if (da != null) da.Dispose();
if (cmd != null) cmd.Dispose();
if (conn != null) conn.Dispose();
}return ds;
}