How to create sql connection with c# code behind, access the sql server then conditionally redirect?

Question

This is a question from an experienced beginner!

Using ASP.NET 4 C# AND SQL server,

I have a connection string in web.config to myDatabase named "myCS". I have a database named myDB. I have a table named myTable with a primary key named myPK

What are the NECESSARY lines of code behind (minimal code) to create a SQL connection, then select from myTable where myPK=="simpleText"

it will probably include:

sqlconnection conn = new sqlconnection(??? myCS)
string SQLcommand = select * from myDB.myTable where myPK==myTestString;
sqlCommand command = new SqlCommand(SQL,conn);

conn.Open();

booleanFlag = ????

conn.Close();
conn.Dispose();

then

If ( theAnswer  != NULL )  // or (if flag)
{
Response.Redirect("Page1.aspx");
}
else
{
Response.Redirect("Page2.aspx");
}

Answer 1

Here is a limited simple tutorial:

First, you want to have a class to do the hard work for you, then you will use it with ease.

First, you have to crate the connection string in your web.config file and name it. Here it is named DatabaseConnectionString, but you may named it myCS as required in the question.

Now, in App_Code create a new class file and name it SqlComm (this is just an example name) like:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Web;

public class SqlComm
{
    // this is a shortcut for your connection string
    static string DatabaseConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["dbConStr"].ConnectionString;

    // this is for just executing sql command with no value to return
    public static void SqlExecute(string sql)
    {
        using (SqlConnection conn = new SqlConnection(DatabaseConnectionString))
        {
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.Connection.Open();
            cmd.ExecuteNonQuery();
        }
    }

// with this you will be able to return a value
    public static object SqlReturn(string sql)
    {
        using (SqlConnection conn = new SqlConnection(DatabaseConnectionString))
        {
            conn.Open();
            SqlCommand cmd = new SqlCommand(sql, conn);
            object result = (object)cmd.ExecuteScalar();
            return result;
        }
    }

    // with this you can retrieve an entire table or part of it
    public static DataTable SqlDataTable(string sql)
    {
        using (SqlConnection conn = new SqlConnection(DatabaseConnectionString))
        {
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.Connection.Open();
            DataTable TempTable = new DataTable();
            TempTable.Load(cmd.ExecuteReader());
            return TempTable;
        }
    }   

// sooner or later you will probably use stored procedures. 
// you can use this in order to execute a stored procedure with 1 parameter
// it will work for returning a value or just executing with no returns
    public static object SqlStoredProcedure1Param(string StoredProcedure, string PrmName1, object Param1)
    {
        using (SqlConnection conn = new SqlConnection(DatabaseConnectionString))
        {
            SqlCommand cmd = new SqlCommand(StoredProcedure, conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add(new SqlParameter(PrmName1, Param1.ToString()));
            cmd.Connection.Open();
            object obj = new object();
            obj = cmd.ExecuteScalar();
            return obj;
        }
    }
}

Okay, this only a class, and now you should know how to use it:

If you wish to execute a command like delete, insert, update etc. use this:

SqlComm.SqlExecute("TRUNCATE TABLE Table1");

but if you need to retrieve a specific value from the database use this:

int myRequiredScalar = 0;
object obj = new object();
obj = SqlComm.SqlReturn("SELECT TOP 1 Col1 FROM Table1");
if (obj != null) myRequiredScalar = (int)obj;

You can retrieve a bunch of rows from the database this way (others like other ways) This is relevant to your sepecific question

int Col1Value = 0;
DataTable dt = new DataTable();
dt = SqlComm.SqlDataTable("SELECT * FROM myTable WHERE myPK='simpleText'");
if (dt.Rows.Count == 0) 
{
    // do something if the query return no rows
    // you may insert the relevant redirection you asked for
}
else
{
    // Get the value of Col1 in the 3rd row (0 is the first row)
    Col1Value = (int)dt.Rows[2]["Col1"];

    // or just make the other redirection from your question
}   

If you need to execute a stored procedure with or without returning a value back this is the way to do that (in this example there are no returning value)

SqlComm.SqlStoredProcedure1Param("TheStoredProcedureName", "TheParameterName", TheParameterValue);

Again, for your specific question return the table using the SqlDataTable , and redirect if dt.Rows.Count >0

Have fun.

Answer 2

There are many ways: LINQ, SqlDataReader, SQLDataAdapter, according to what you want to read (single value, datatable ...), so here is an example:

using (SqlConnection con = new SqlConnection("SomeConnectionString"))
{
  var cmd = new SqlCommand("select from myTable where myPK==N'"+ simpleText+ "'",con);
  cmd.Connection.Open();
  var sqlReader = cmd.ExecuteReader();
  while(sqlReader.Read())
  {
    //Fill some data like : string result = sqlReader("SomeFieldName");
  }
  sqlReader.Close();
  cmd.Connection.Close();
  cmd.Dispose();
}