public List <QueryStore> GetQueryFromStore()
{
List <QueryStore> result = new List <QueryStore>();
//Create the SQL Query for returning all the articles
string sqlQuery = String.Format("SELECT query_name, query_desc, query_script, created_date from t_db_query_script");
//Create and open a connection to SQL Server
SqlConnection connection = new SqlConnection(_conString);
connection.Open();
SqlCommand command = new SqlCommand(sqlQuery, connection);
//Create DataReader for storing the returning table into server memory
SqlDataReader dataReader = command.ExecuteReader();
QueryStore query = null;
//load into the result object the returned row from the database
if (dataReader.HasRows)
{
while (dataReader.Read())
{
query.query_name = dataReader.GetString(0);
query.query_desc = dataReader.GetString(1);
query.query_script = dataReader.GetString(2);
query.created_date = Convert.ToDateTime(dataReader.GetString(3));
result.Add(query);
}
}
return(result);
}
public int SaveQueryToStore(QueryStore queryparam)
{
//Create the SQL Query for inserting an article
string sqlQuery = String.Format("INSERT INTO t_db_query_script (query_name, query_desc, query_script) Values('{0}', '{1}', '{2}');Select @@Identity", queryparam.query_name, queryparam.query_desc, queryparam.query_script.Replace("'", "''"));
//Create and open a connection to SQL Server
SqlConnection connection = new SqlConnection(_conString);
connection.Open();
//Create a Command object
SqlCommand command = new SqlCommand(sqlQuery, connection);
//command.Parameters.AddWithValue("", "");
//Execute the command to SQL Server and return the newly created ID
int newArticleID = Convert.ToInt32((decimal)command.ExecuteScalar());
//Close and dispose
command.Dispose();
connection.Close();
connection.Dispose();
// Set return value
return(newArticleID);
}