3DW DevTools – Page 7 – Application Development Cloud

Database connection and query using c#

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;
                    }
                }
            }
        }
        //----------------------------------------------------------------------

Working with c# dataset

A Data Set resembles a database. Data Table resembles the database table. Data Row resembles a record in the table.

DataSet reqClasses = new DataSet();
DataTable reqClassesTable = new DataTable();

//.. from stored procedure … selecting ClassID from InstCrsReq …The dataset is defined as DataSet reqList in this line…
objDA.Fill(reqList, “ReqList”);

So I can access the data using this assignment…
reqClassesTable = reqClasses.Tables[“reqList”];

Then I can loop through the data and do whatevs…
foreach (DataRow row in reqClassesTable.Rows)
{string ClassID = row[“ClassID”].ToString();}

Another way to use the data set is to call to a database and return one.

DataSet movieList = new DataSet();
myDatabaseCall(movieList);

movieList.Tables[“movieList”] now contains the results of the database call, maybe somthing like:

MovieID	MovieName
1	The Matrix
2	Justice League

You can loop through the rows as so…

foreach (DataRow row in movieList.Tables[“movieList”] )

{ Label1.Text += row[“MovieName”];}

The control named “Label1” would contain: The MatrixJustice League

because I didn’t use any logic to add commas or a space between the field results display.

Open source log file analyzer

GoAccess is an open source real-time web log analyzer and interactive viewer that runs in a terminal in *nix systems or through your browser.

It provides fast and valuable HTTP statistics for system administrators that require a visual server report on the fly.

https://www.goaccess.io/

Website Malware scanners

Its important to have a trusted third party penetration testing your site. Here are some resources for that:

Sucuri has a website scanner.

Find when a users password expires – windows command prompt

The net user command is a very useful tool. The instructions were confusing. when they say to type /domain, they mean it. not your domain or the users. the actual word domain.

cmdpromtnetuserscreen1

Cross Site Scripting Prevention Cheat Sheet

https://www.owasp.org/index.php/XSS_%28Cross_Site_Scripting%29_Prevention_Cheat_Sheet

Black out certain days in from the Jquery Calendar Picker

The JQuery Calendar picker is a very handy element that is used in many places on the web. The default functionality is great, but what if you need a custom date range, or to black out specific dates? The picker has an event handler called “beforeShowDay” that will let you hook into and modify what dates are available to pick from the element.

For example, to allow users to sign up for campus visits on Tuesday and Thursday, I needed to “black out” the rest of the days from the ui. I also needed to black out a couple of days that will be unavailable due to special events. I can write a function to specify these days, then call the function on the “beforeShowDay” event handler.

[code]

var unavailableDates = [“19-5-2016”, “16-6-2016”];
function unavailable(date) {
dmy = date.getDate() + “-” + (date.getMonth()+1) + “-” +date.getFullYear();
if ($.inArray(dmy, unavailableDates) < 0) {
return [true,””,””];
} else {
return [false,””,”This day is unavailable.”];

}
var day = date.getDay();
return [(day == 2 || day==4)];
}
$( “#mydatefield” ).datepicker({
minDate: ‘+7d’, changeMonth: true, changeYear: true, yearRange:”c-80:c”, beforeShowDay: unavailable});

[/code]

Check variable for DB null value C#

If a variable comes from the sql database with a NULL value, its not the same as checking the c# variable for =”null” . You need a special line of code:

if (!DBNull.Value.Equals(MyDbVar))

Change an elements class with JavaScript

To change all classes for an element:

To replace all existing classes with one or more new classes, set the className attribute:

document.getElementById("MyElement").className = "MyClass";

(You can use a space-delimited list to apply multiple classes.)

To add an additional class to an element:

To add a class to an element, without removing/affecting existing values, append a space and the new classname, like so:

document.getElementById("MyElement").className += " MyClass";

To remove a class from an element:

To remove a single class to an element, without affecting other potential classes, a simple regex replace is required:

document.getElementById("MyElement").className =
   document.getElementById("MyElement").className.replace
      ( /(?:^|\s)MyClass(?!\S)/g , '' )
/* code wrapped for readability - above is all one statement */

An explanation of this regex is as follows:

(?:^|\s) # match the start of the string, or any single whitespace character

MyClass  # the literal text for the classname to remove

(?!\S)   # negative lookahead to verify the above is the whole classname
         # ensures there is no non-space character following
         # (i.e. must be end of string or a space)

The g flag tells the replace to repeat as required, in case the class name has been added multiple times.

To check if a class is already applied to an element:

The same regex used above for removing a class can also be used as a check as to whether a particular class exists:

if ( document.getElementById("MyElement").className.match(/(?:^|\s)MyClass(?!\S)/) )

Display logged-in user in .NET web app

Web.Config changes:

Inside system.web, after authorization:

Inside system.webserver (add if needed outside of system.web):