Thrice Nested Repeater for Tests, Questions and Answers

Default.aspx

            <asp:Repeater runat="server" ID="rTests">
                <ItemTemplate>
                    <h3>Test: <%# DataBinder.Eval(Container.DataItem, "Name") %>, Passing Score = <%# DataBinder.Eval(Container.DataItem,"Passing_Score") %></h3>
                    <asp:Repeater ID="rQuestions" runat="server" DataSource='<%# GetChildRelation(Container.DataItem, "Test_Question")%>'>
                        <ItemTemplate>
                            <h4>Question:<%#DataBinder.Eval(Container.DataItem, "Text")%></h4>
                            <asp:Repeater ID="rAnswers" runat="server" DataSource='<%# GetChildRelation(Container.DataItem, "Question_Answer")%>'>
                                <ItemTemplate>
                                   Answer: <b><%# DataBinder.Eval(Container.DataItem, "Text") %></b> <%# DataBinder.Eval(Container.DataItem, "correct") %><br />
                                </ItemTemplate>
                            </asp:Repeater>
                        </ItemTemplate>
                    </asp:Repeater>
                </ItemTemplate>
            </asp:Repeater>

If you are having problems with button events not firing, make sure to EnableViewState.
Default.aspx.cs


    private void BindrTests()
    {
        rTests.DataSource = GetTestQuestionsAndAnswers();
        rTests.DataBind();
    }

    protected DataView GetChildRelation(object dataItem, string relation)
    {
        DataRowView drv = dataItem as DataRowView;
        if (drv != null)
            return drv.CreateChildView(relation);
        else
            return null;
    }

    public static DataSet GetTestQuestionsAndAnswers()
    {
            string strConn = System.Configuration.ConfigurationManager.ConnectionStrings["Training"].ToString();
            string strSql = "SELECT Tests.Ident, Tests.Name, Tests.Passing_Score FROM [Training].[dbo].Tests; " +
	                        "SELECT Test_Questions.Ident, Test_Questions.Test_Ident, Test_Questions.text FROM [Training].[dbo].Test_Questions; " +
                    		"SELECT Test_Answers.Ident, Test_Answers.Question_Ident, Test_Answers.text, Test_Answers.correct FROM [Training].[dbo].Test_Answers";
            SqlConnection conn = new SqlConnection(strConn);
            SqlDataAdapter da = new SqlDataAdapter(strSql, conn);
            da.TableMappings.Add("Tests1", "Test_Questions");
            da.TableMappings.Add("Tests2", "Test_Answers");
            DataSet dsQandA = new DataSet();
            da.Fill(dsQandA, "Tests");
            dsQandA.Relations.Add("Test_Question", dsQandA.Tables["Tests"].Columns["Ident"], dsQandA.Tables["Test_Questions"].Columns["Test_Ident"]);
            dsQandA.Relations[0].Nested = true;
            dsQandA.Relations.Add("Question_Answer", dsQandA.Tables["Test_Questions"].Columns["Ident"], dsQandA.Tables["Test_Answers"].Columns["Question_Ident"]);
            dsQandA.Relations[1].Nested = true;
            return dsQandA;
    }

CA2100: Review SQL queries for security vulnerabilities

Found here: http://msdn.microsoft.com/en-us/library/ms182310.aspx

This rule assumes that the string argument contains user input. A SQL command string that is built from user input is vulnerable to SQL injection attacks. In a SQL injection attack, a malicious user supplies input that alters the design of a query in an attempt to damage or gain unauthorized access to the underlying database. Typical techniques include injection of a single quotation mark or apostrophe, which is the SQL literal string delimiter; two dashes, which signifies a SQL comment; and a semicolon, which indicates that a new command follows. If user input must be part of the query, use one of the following, listed in order of effectiveness, to reduce the risk of attack.

•Use a stored procedure.

•Use a parameterized command string.

•Validate the user input for both type and content before you build the command string.

The following example shows a method, UnsafeQuery, that violates the rule and a method, SaferQuery, that satisfies the rule by using a parameterized command string.

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

namespace SecurityLibrary
{
   public class SqlQueries
   {
      public object UnsafeQuery(
         string connection, string name, string password)
      {
         SqlConnection someConnection = new SqlConnection(connection);
         SqlCommand someCommand = new SqlCommand();
         someCommand.Connection = someConnection;

         someCommand.CommandText = "SELECT AccountNumber FROM Users " +
            "WHERE Username='" + name + 
            "' AND Password='" + password + "'";

         someConnection.Open();
         object accountNumber = someCommand.ExecuteScalar();
         someConnection.Close();
         return accountNumber;
      }

      public object SaferQuery(
         string connection, string name, string password)
      {
         SqlConnection someConnection = new SqlConnection(connection);
         SqlCommand someCommand = new SqlCommand();
         someCommand.Connection = someConnection;

         someCommand.Parameters.Add(
            "@username", SqlDbType.NChar).Value = name;
         someCommand.Parameters.Add(
            "@password", SqlDbType.NChar).Value = password;
         someCommand.CommandText = "SELECT AccountNumber FROM Users " + 
            "WHERE Username=@username AND Password=@password";

         someConnection.Open();
         object accountNumber = someCommand.ExecuteScalar();
         someConnection.Close();
         return accountNumber;
      }
   }

   class MalaciousCode
   {
      static void Main(string[] args)
      {
         SqlQueries queries = new SqlQueries();
         queries.UnsafeQuery(args[0], "' OR 1=1 --", "anything");
         // Resultant query (which is always true):  
         // SELECT AccountNumber FROM Users WHERE Username='' OR 1=1

         queries.SaferQuery(args[0], "' OR 1 = 1 --", "anything");
         // Resultant query (notice the additional single quote character): 
         // SELECT AccountNumber FROM Users WHERE Username=''' OR 1=1 --' 
         //                                   AND Password='anything'
      }
   }
}

C# .Net Console Application to Update SQL Database with Excel Data

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
namespace ExcelUpdateSQL
{
    class Program
    {
        static void Main(string[] args)
        {
            SqlConnection conn = new SqlConnection(@"Data Source=SQLServer;Initial Catalog=MyDatabase;Integrated Security=SSPI;");
            conn.Open();
            OleDbConnection Xcon = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\temp\MyExcelFile.xlsx;Extended Properties=Excel 12.0");
            OleDbDataAdapter Xda = new OleDbDataAdapter("select * from [Sheet1$]", Xcon);
            DataTable Xdt = new DataTable();
            Xda.Fill(Xdt);
            try
            {
                foreach (DataRow row in Xdt.Rows) // Loop over the rows.
                {
                    SqlCommand cmd = new SqlCommand(@"update MyTable set MyField = '"+ row[1].ToString() +"' where MySelectedField = '"+ row[0].ToString() +"'", conn);
                    cmd.ExecuteNonQuery();
                    Console.WriteLine(row[0].ToString() +", "+ row[1].ToString() + ", success");
               }
            }
            catch (Exception ex) { Console.WriteLine(ex.Message); }
            finally { conn.Close(); }
            Console.WriteLine(Xdt.Rows.Count);
            Console.Read();
        }
    }
}

SQL Command to Backup Databases

Create a SQL stored procedure called usp_backup_database

USE [MyDatabase]
GO
/****** Object:  StoredProcedure [dbo].[usp_backup_database]    Script Date: 4/1/2013 12:59:00 PM ******/

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER procedure [dbo].[usp_backup_database](

                @iv_backup_folder VARCHAR(50),

                @iv_db_name VARCHAR(50),

                @iv_tag VARCHAR(50) = NULL

)

as

 

/*==============================================================================

*    PROCEDURE:               usp_backup_database

* 

*  DESCRIPTION: Backups a database to a .bak file given the folder and database

*               name as input parameters. Also adds a timestamp to the .bak

*               name.

*

*      OUTPUTS: None, executes 'BACKUP DATABASE' command

* 

* DEPENDANCIES:           None

*============================================================================*/

begin

   set nocount on

 

                declare @lv_backup_full_path varchar(500)

 

                set @lv_backup_full_path = @iv_backup_folder + '\' + @iv_db_name + '_' +

                REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(50), CURRENT_TIMESTAMP, 20), '-', ''), ' ', '_'), ':', '') 

 

                if(@iv_tag is not null)

                                set @lv_backup_full_path = @lv_backup_full_path + '_' + @iv_tag

 

                set @lv_backup_full_path = @lv_backup_full_path + '.bak'

 

                backup database @iv_db_name to disk = @lv_backup_full_path

 

                set nocount off

end

grant execute on dbo.usp_backup_database to public

Then execute the stored procedure:

exec usp_backup_database 'e:\', 'MyDatabase1', 'Prod'
exec usp_backup_database 'e:\', 'MyDatabase2', 'Prod'
exec usp_backup_database 'e:\', 'MyDatabase3', 'Prod''

SQL Command to List In-Use SharePoint Databases

First, find the name of the SharePoint Config Database in the registry if you don’t already know it:

“HKLM\Software\Microsoft\Shared Tools\Web Server Extensions\12.0\Secure\ConfigDb”

Then in SQL Server Management Studio run this query:

SELECT o.[Name] AS 'DatabaseName',<br />
    Instance.[Name] AS 'DatabaseInstance',<br />
    [Server].[Name] AS 'DatabaseServer'<br />
FROM [SharePoint_Config_DB].[dbo].[Objects] AS o<br />
    INNER JOIN [SharePoint_Config_DB].[dbo].[Classes] c on c.id = o.classid<br />
    LEFT JOIN [SharePoint_Config_DB].[dbo].[Objects] AS Instance ON o.ParentId = Instance.Id<br />
    LEFT JOIN [SharePoint_Config_DB].[dbo].[Objects] AS [Server] ON Instance.ParentId = [Server].Id<br />
    WHERE c.Fullname LIKE '%Administration.SPConfigurationDatabase%'<br />
    OR c.Fullname LIKE '%Administration.SPContentDatabase%'<br />
    OR c.Fullname LIKE '%Administration.SharedDatabase%'<br />
    OR c.Fullname LIKE '%Administration.SearchSharedDatabase%'<br />