How to connect to a MS SQL database using ASP.Net 3.5?
In web.config:
`
…
</configuration>
In App_Code:
using System.Data.SqlClient;
namespace whatever
{
public static DataTable GetTable(string connstr, string commstr, params object[] parameters)
{
if (commstr == null) return null;
if (connstr == null) connstr = ConfigurationManager.ConnectionStrings[“defaultconn”].ConnectionString;
SqlConnection scon = new SqlConnection(connstr);
SqlCommand scom = new SqlCommand(commstr, scon);
for (int j = 0; j < parameters.Length; j += 2)
scom.Parameters.AddWithValue((String)parameters[j], (parameters[j + 1] == null ? DBNull.Value : parameters[j + 1]));
SqlDataAdapter sda = new SqlDataAdapter(scom);
DataSet ds = new DataSet();
sda.Fill(ds); scon.Close();
if (ds.Tables[0].Rows.Count == 0)
return null;
return ds.Tables[0];
}
public static DataRow GetRow(string connstr, string commstr, params object[] parameters)
{
if (commstr == null) return null;
if (connstr == null) connstr = ConfigurationManager.ConnectionStrings[“defaultconn”].ConnectionString;
SqlConnection scon = new SqlConnection(connstr);
SqlCommand scom = new SqlCommand(commstr, scon);
for (int j = 0; j < parameters.Length; j += 2)
scom.Parameters.AddWithValue((String)parameters[j], (parameters[j + 1] == null ? DBNull.Value : parameters[j + 1]));
SqlDataAdapter sda = new SqlDataAdapter(scom);
DataSet ds = new DataSet();
sda.Fill(ds); scon.Close();
if (ds.Tables[0].Rows.Count == 0)
return null;
return ds.Tables[0].Rows[0];
}
public static int Execute(string connstr, string commstr, params object[] parameters)
{
if (commstr == null) return -1;
if (connstr == null) connstr = ConfigurationManager.ConnectionStrings[“defaultconn”].ConnectionString;
SqlConnection scon = new SqlConnection(connstr); scon.Open();
SqlCommand scom = new SqlCommand(commstr, scon);
for (int j = 0; j < parameters.Length; j += 2)
scom.Parameters.AddWithValue((String)parameters[j], (parameters[j + 1] == null ? DBNull.Value : parameters[j + 1]));
return scom.ExecuteNonQuery();
}
Use as
DataTable dt = whatever.SQL.GetTable(null, “select * from Users”);
DataRow dr = whatever.SQL.GetRow(null, “select * from Users where uname=@u and pass=@p”, “@u”, uname.Text, “@p”, pass.Text);`
etc