Generate XML from SQL Server data

Hi folks,

Its very easy to create xml using database fields as below:

SELECT 'A' AS GrandFather
,(
SELECT 'B' AS Father
,'C' AS Mother
,(
SELECT 'E' AS Daughter
,'F' AS Son
FOR XML Path('Childs')
,Type
)
FOR XML Path('Parents')
,Type
)

FOR XML PATH('Family')

----------------------------------
SELECT f.GF AS GrandFather
,(
SELECT f.Father AS Father
,f.Mother AS Mother
,(
SELECT f.Daughter AS Daughter
,f.Son AS Son
FOR XML Path('Childs')
,Type
)
FOR XML Path('Parents')
,Type
)
FROM Family f

FOR XML PATH('Family'), Type


Cheers!!!

Managing Task Scheduler from front end (Taskschd.msc)

Controlling is behaviour of Task Scheduler is very easy. Follow these steps:

Step 1:

Add Microsoft.Win32.TaskScheduler dll to your project. Download it if not available.

Step 2:

I guess if you workingin winform then take a datagridview.

Step 3:

Here the code part.

using Microsoft.Win32.TaskScheduler;

public partial class Form1 : Form
{
    public Form1()
    {
        InitializeComponent();
    }
 
    private void Form1_Load(object sender, EventArgs e)
    {
        dataGridView1.DataSource = AllTaskNames();
    }
     
public List<SchedulerModel> GetAllTasks()
{
List<SchedulerModel> TaskList = new List<SchedulerModel>();

using (TaskService ts = new TaskService())
{
var tst = ts.GetFolder(@"\Microsoft\MyTasks");

if (tst != null)
{
var tasks = tst.AllTasks;
foreach (var t in tasks)
{
SchedulerModel sM = new SchedulerModel();
sM.Name = t.Name;
sM.Status = t.State.ToString();
sM.LastRunTime = t.LastRunTime;
sM.NextRunTime = t.NextRunTime.ToShortDateString() == "1/1/0001" ? (DateTime?)null : t.NextRunTime;
TaskList.Add(sM);
}
}
}

return TaskList;
}

public void DisableAnyTask()
{
    TaskService ts = new TaskService();
    var t = ts.GetFolder(@"\Microsoft\MyTasks");

    if (t != null)
    {
        var tasks = t.AllTasks;
        foreach (var ee in tasks)
        {
            if (ee.Name.ToLower() == "task one")
            {
                if (ee.State.ToString() == "Running")
                {
                    ee.Stop();
                    ee.Enabled = false;
                }
            }
        }
    }
}

}
public class SchedulerModel
{
    public string Name { get; set; }
    public string Status { get; set; }
    public DateTime LastRunTime { get; set; }
    public DateTime? NextRunTime { get; set; }      
}
 
Step 4:

On Page_Load Call below written function As

dataGridView1.DataSource = GetAllTasks();


So, You have all tasks available of that particular location.
Note:

> You must have rights to access these task so, make sure you using Visual Studio as Administrator.

> Ctrl + R >> Taskschd.msc: here you have Task Scheduler window open you have to make a folder (MyTask)
 Path : Task Scheduler Library > Microsoft > MyTask
 MyTask: Name of you folder where you have to create your tasks.


Thanks!

Cheers!!!   

Return parameter in sql server

Create PROCEDURE USP_IsSearchFinished @Id INT
AS
DECLARE @r BIT;

SELECT @r = COUNT(1)
FROM Table1 AP WITH (NOLOCK)
INNER JOIN Table2 SB WITH (NOLOCK) ON AP.ID = SB.Id
WHERE AP.ID = @Id
AND AP.Flag = 'Finished'

RETURN @r
GO


-- How to execute it in sql server

DECLARE @t BIT
EXEC @t = USP_IsSearchFinished 1
PRINT @t


-- Now, get that in ADO.NET

 public int IsSearchFinished(int id)
{
    int _id = 0;
    try
    {
        using (SqlConnection connection = new SqlConnection(conString))
        {
            using (SqlCommand command = new SqlCommand("USP_IsSearchFinished", connection))
            {
                connection.Open();
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.AddWithValue("@Id", id);
                
                SqlParameter returnParameter = command.Parameters.Add("RetVal", SqlDbType.Bit);
                returnParameter.Direction = ParameterDirection.ReturnValue;
                command.ExecuteNonQuery();

                _id = (int)returnParameter.Value;                        
            }
        }
    }
    catch
    {
    }
    return _id;
}

Cheers!

Download DataTable TO Excel By Calling Action in MVC

        [AcceptVerbs(HttpVerbs.Get)]
        public FileResult DownloadSearched(string _file)
        {

DataTable dtRec = {Record};

            MemoryStream MyMemoryStream = null;
            using (XLWorkbook wb = new XLWorkbook())
            {
                wb.Worksheets.Add(dtRec, "Order");

                MyMemoryStream = new MemoryStream();
                wb.SaveAs(MyMemoryStream);
                MyMemoryStream.WriteTo(Response.OutputStream);

                return File(MyMemoryStream, "application/vnd.ms-excel", _file+".xlsx");
            }
         }
       
       


          Call from client side:
       
          window.location = "/Report/OrderReport/DownloadSearched?_file=Report



Cheers!

Shortest way to split strings

CREATE FUNCTION FN_SplitSTRING (@Text nVarchar(max), @Delimeter nVarchar(50))
RETURNS @Table Table (Item Varchar(200))
/*
Written By: Nitish Kumar.
Objective: To split string with delimeter.
Written On: 28-July-2015.
      SELECT * FROM FN_SplitSTRING('1,2,3,5,6,4,8',',')
      SELECT * FROM FN_SplitSTRING('NITISH, KUMAR, JHA, FROM, SAMASTIPUR',',')
      SELECT * FROM FN_SplitSTRING('17*858*858*8569*89*58*nitish','*')
*/
AS
BEGIN
   
      DECLARE @i INT= LEN(@Text)

      WHILE @i <> 0
      BEGIN

            INSERT INTO @Table SELECT LTRIM(RTRIM(SUBSTRING(@Text,0,CHARINDEX(@Delimeter,@Text))))

            SET @Text= RIGHT(@Text,LEN(@Text) - LEN(SUBSTRING(@Text,0,CHARINDEX(@Delimeter,@Text)+1)) )

            IF(LEN(SUBSTRING(@Text,0,CHARINDEX(@Delimeter,@Text)+1)) = 0)
            BEGIN    
                  INSERT INTO @Table SELECT LTRIM(@Text)
                  SET @i= 1;
            END

            SET @i= @i-1
      END
   
      RETURN
END
GO

jQuery click events firing multiple times

Cause: If your table is binding multiple times then obviously page.bootpag will bind that many times.

So, to resolve this must unbind this on each table bind call.
ex:   $("#page").unbind();