First you need to add a connection string. For a Webforms app, I add them to the web.config file.
<configuration>
<connectionStrings>
<add name="devConn" connectionString="Data Source=1.2.3.4;network library=DBMSSOCN;Initial Catalog=Deveee;Persist Security Info=True;User ID=ADevUser;Password=*********" />
</connectionStrings>
....</configuration>
Then you can use the connection string to connect to a database…
public static bool SaveRecordToDB(string myData, out string Message, out int returnCode){
System.Data.SqlClient.SqlCommand objCmd;
System.Data.SqlClient.SqlConnection objConn = new System.Data.SqlClient.SqlConnection(); objConn.ConnectionString = WebConfigurationManager.ConnectionStrings["devConn"].ConnectionString;
int rowsAffected = 0;
try
{
objConn.Open();
objCmd = new System.Data.SqlClient.SqlCommand();
objCmd.Connection = objConn;
objCmd.CommandType = CommandType.StoredProcedure;
objCmd.CommandText = "usp_add_alert";
System.Data.SqlClient.SqlParameter objP1 = new System.Data.SqlClient.SqlParameter();
objP1.Direction = ParameterDirection.Input;
objP1.ParameterName = "@head";
objP1.SqlDbType = SqlDbType.NVarChar;
objP1.Size = 50;
objP1.Value = emer_head;
objCmd.Parameters.Add(objP1);
System.Data.SqlClient.SqlParameter objRC = new System.Data.SqlClient.SqlParameter();
objRC.Direction = ParameterDirection.ReturnValue;
objRC.SqlDbType = SqlDbType.Int;
objRC.ParameterName = "@ID";
objCmd.Parameters.Add(objRC);
rowsAffected = objCmd.ExecuteNonQuery();
returnCode = System.Convert.ToInt32(objCmd.Parameters["@ID"].Value.ToString());
}
catch (Exception e)
{
Message = e.Message;
returnCode = 0;
return false;
}
finally
{
objConn.Close();
}
if (rowsAffected > 0 && returnCode > 0)
{
return true;
}
else
{
Message = "Record Add not successfull";
return false;
}
}
Then you can call the method when you need to in your code…
I prefer this method of adding the stored procedure parameters…
//********* create a new page section record ***********
public static bool DBcreatePageSection(int UID, string title, string body, int sequence, string photo, string byline, string date, out string Message)
{
Message = "";
System.Data.SqlClient.SqlCommand objCmd;
System.Data.SqlClient.SqlConnection objConn = new System.Data.SqlClient.SqlConnection();
objConn.ConnectionString = WebConfigurationManager.ConnectionStrings["facSiteConn"].ConnectionString;
int rowsAffected = 0;
try
{
objConn.Open();
objCmd = new System.Data.SqlClient.SqlCommand();
objCmd.Connection = objConn;
objCmd.CommandType = CommandType.StoredProcedure;
objCmd.CommandText = "usp_DBcreatePageSection";
objCmd.Parameters.Add("@UID", SqlDbType.Int).Value = UID;
objCmd.Parameters.Add("@Sequence", SqlDbType.Int).Value = sequence;
objCmd.Parameters.Add("@Heading", SqlDbType.VarChar, 500).Value = title;
objCmd.Parameters.Add("@Body", SqlDbType.Text).Value = body;
objCmd.Parameters.Add("@Photo", SqlDbType.VarChar, 500).Value = photo;
objCmd.Parameters.Add("@ByLine", SqlDbType.VarChar, 500).Value = byline;
objCmd.Parameters.Add("@Date", SqlDbType.VarChar, 500).Value = date;
rowsAffected = objCmd.ExecuteNonQuery();
}
catch (Exception e)
{
Message = e.Message;
return false;
}
finally
{
objConn.Close();
}
if (rowsAffected > 0)
{
return true;
}
else
{
Message = "Record ADD not successfull" + Message;
return false;
}
}
//---------------------------------------------------
Here’s how to call for records and put them in a dataset
public static bool DBgetUserName(DataSet UserInfo, string SID, out string errInfo)
{
errInfo = "";
System.Data.SqlClient.SqlCommand objCmd;
System.Data.SqlClient.SqlConnection objConn = new System.Data.SqlClient.SqlConnection();
objConn.ConnectionString = WebConfigurationManager.ConnectionStrings["ODSConnectionString_ctcLink"].ConnectionString;
System.Data.SqlClient.SqlDataAdapter objDA;
try
{
objConn.Open();
objCmd = new System.Data.SqlClient.SqlCommand();
objCmd.Connection = objConn;
objCmd.CommandType = CommandType.StoredProcedure;
objCmd.CommandText = "usp_GetEmpFromEmpInfo_ctcLink";
System.Data.SqlClient.SqlParameter objP5 = new System.Data.SqlClient.SqlParameter();
objP5.Direction = ParameterDirection.Input;
objP5.ParameterName = "@sid";
objP5.SqlDbType = SqlDbType.NVarChar;
objP5.Size = 9;
objP5.Value = SID;
objCmd.Parameters.Add(objP5);
objCmd.Parameters.AddWithValue("@LastName", "%");
objCmd.Parameters.AddWithValue("@FirstName", "%");
objDA = new System.Data.SqlClient.SqlDataAdapter();
objDA.SelectCommand = objCmd;
objDA.Fill(UserInfo, "UserInfo");
}
catch (Exception e)
{
errInfo = e.Message;
UserInfo = null;
return false;
}
finally
{
objConn.Close();
}
return true;
}
heres how to call for a single record and put into variables.
public static bool getFacultyInfo(string SID, out int lid, out int uid, out string title, out string URL, out string errMsg)
{
errMsg = "";
string connStr = ConfigurationManager.ConnectionStrings["facSiteConn"].ConnectionString;
using (SqlConnection connection = new SqlConnection(connStr))
{
using (SqlCommand cmd = new SqlCommand("usp_getFacultyInfo", connection))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@SID", SqlDbType.Char, 9).Value = SID;
try
{
connection.Open();
using (SqlDataReader sdr = cmd.ExecuteReader())
{
if (sdr.HasRows)
{
sdr.Read();
lid = Convert.ToInt32(sdr["LoginID"]);
uid = Convert.ToInt32(sdr["UniverseID"]);
title = sdr["Title"].ToString();
URL = sdr["DirectURL"].ToString();
connection.Close(); return true;
}
else
{
lid = 0;
uid = 0;
title = "";
URL = "";
connection.Close(); return false;
}
}
}
catch (SqlException sqlex)
{
string error = sqlex.Message;
errMsg = error.ToString();
lid = 0;
uid = 0;
title = "";
URL = "";
return false;
}
}
}
}
//----------------------------------------------------------------------