Select multiple count in one result set in sql and C#.

Here, you can select multiple counts from different – 2 tables in one result set. with multiple coulumn names.

i used insight databse ORM in C#.

First i created a Model in c# where result set will bind with Columns name and model property name.

public class AnalyticsModel
    {
        public int totalAppointments { get; set; }
        public int totalPrescriptionRenewal { get; set; }
        public int totalNumberOfDropInsPerDay { get; set; }
        public int totalRequest { get; set; }
    }

And Now My Store Procedure in SQL

CREATE PROC [dbo].[AnalyticsTotal]
AS
BEGIN 
	SET NOCOUNT ON;

	SELECT
	(SELECT COUNT(*) FROM Appointment WHERE MeetingType IN(1,3) AND Status=1) AS [totalAppointments],

	(SELECT COUNT(*) FROM Appointment WHERE MeetingType=2 AND Status=1) AS [totalPrescriptionRenewal],

	(SELECT COUNT(*) FROM Appointment WHERE IsDropIn=1 AND Status=1) AS [totalNumberOfDropInsPerDay],

	(SELECT COUNT(*) FROM Appointment WHERE MeetingType IN(1,2,3) AND Status=1) AS [totalRequest]

END

And in last i got my result set in my DataClass.

var Result = DB.QuerySql<AnalyticsModel>(@"Exec [dbo].[AnalyticsTotal]").FirstOrDefault();

Here QuerySql is the method of Insight Database ORM.

You can Install in your visual studio from nuget package manager.

Install-Package Insight.Database -Version 6.2.8

Get Enum Description in C#

To get Enum description in c# here is a method or code.

For this first you have to import a namespace of Refletion because it is a part of Reflection.

using System.Reflection;

After importing namespace you can use reflection and get enum description.

public static string GetEnumDescription(Enum value)
        {
            FieldInfo fi = value.GetType().GetField(value.ToString());
            DescriptionAttribute[] attributes = (DescriptionAttribute[])fi.GetCustomAttributes(typeof(DescriptionAttribute), false);
            if (attributes.Length > 0)
            {
                return attributes[0].Description;
            }
            else
            {
                return value.ToString();
            }
        }

Now you can use this method to get enum description.

GetEnumDescription(Enum value)

Here value that we are passing will be string as same as in enum name.

How can I change the SQL Server Configurations(TCP/IP, port number,IP Address) using SMO through C#

You can configure your sql server and these settings through c#.

first of all you have to add 2 references in your program, that will be available at nuGet package manager.

install them and add in program first.

Go to visual studio->tools->nuget package manager->package manager console

and than install below two references.

Install-Package Microsoft.SQLServer.SMO -Version 150.18068.0-xplat
Install-Package MicrosoftSQLServerSMO2014Wmi -Version 1.0.1

after that include namespaces.


using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Smo.Wmi;
namespace ConsoleAppTestDemo
{
    class Program
    {
        static void Main(string[] args)
        {

            //Declare and create an instance of the ManagedComputer    
            ManagedComputer mc = new ManagedComputer("MAHAVEER");

            mc.ConnectionSettings.ProviderArchitecture = ProviderArchitecture.Use64bit; //if instance not found

            Service sqlservice = mc.Services["MSSQL$SQL"];

            if (sqlservice.ServiceState == ServiceState.Running)
            {
                sqlservice.Stop();
            }
            ServerProtocol tcpProtocols = mc.ServerInstances["SQLEXPRESS"].ServerProtocols["tcp"];
            ServerProtocol sharedmemoryProtocols = mc.ServerInstances["SQLEXPRESS"].ServerProtocols["sm"];

            //Enable TCP/IP
            tcpProtocols.IsEnabled = true;

            //Enable Shared Memory Protocol
            sharedmemoryProtocols.IsEnabled = true;

            //Set IP4 port
            tcpProtocols.IPAddresses["IP4"].IPAddressProperties["TcpPort"].Value = "1433";

            //Set IP4 Address
            tcpProtocols.IPAddresses["IP4"].IPAddressProperties["IpAddress"].Value = "127.0.0.1";


            //Set IPAll port
            tcpProtocols.IPAddresses["IPAll"].IPAddressProperties["TcpPort"].Value = "1433";

            //Done Changes
            tcpProtocols.Alter();
            sharedmemoryProtocols.Alter();

            sqlservice.Start();
}
}
}

Create window Inbound firewall rule for sql server 1433 port through programmatically

Here you can create inbound firewall rule for sql server 1433 port with multiprofile Domain, Public and Private through C#.

First you have to import a dll from your system “C:\Windows\System32/FirewallAPI.dll”.
Add this DLL to your project reference.
Go to solution explorer in visual studio -> References->Add Reference->click on Browse button near ok and cancel button -> browse path C:\Windows\System32/FirewallAPI.dll and add the dll.

After adding dll use namespace “NetFwTypeLib” in your code like this at above program.
using NetFwTypeLib;


using NetFwTypeLib;

namespace ConsoleAppTestDemo
{
    class Program
    {
        static void Main(string[] args)
        {
            Type tNetFwPolicy2 = Type.GetTypeFromProgID("HNetCfg.FwPolicy2");
            INetFwPolicy2 fwPolicy2 = (INetFwPolicy2)Activator.CreateInstance(tNetFwPolicy2);

            // Let's create a new rule
            INetFwRule2 inboundRule = (INetFwRule2)Activator.CreateInstance(Type.GetTypeFromProgID("HNetCfg.FWRule"));
            inboundRule.Enabled = true;
            //Allow through firewall
            inboundRule.Action = NET_FW_ACTION_.NET_FW_ACTION_ALLOW;

            //For all profile
            inboundRule.Profiles = (int)NET_FW_PROFILE_TYPE2_.NET_FW_PROFILE2_ALL;

            //Using protocol TCP
            inboundRule.Protocol = 6; // TCP
            //Local Port 1433
            inboundRule.LocalPorts = "1433";
            //Name of rule
            inboundRule.Name = "SQLRule";
           
            // Now add the rule
            INetFwPolicy2 firewallPolicy = (INetFwPolicy2)Activator.CreateInstance(Type.GetTypeFromProgID("HNetCfg.FwPolicy2"));
            firewallPolicy.Rules.Add(inboundRule);
        }
    }
}

After that you can check your firewall inbound rule.

How to get System IP address in ASP.NET MVC C#?

Getting client machine ip address in asp.net mvc.

you have to use System.Web.HttpContext context = System.Web.HttpContext.Current;
for current request.

public static string GetIPAddress()
        {
            System.Web.HttpContext context = System.Web.HttpContext.Current;
            string ipAddress = context.Request.ServerVariables["HTTP_X_FORWARDED_FOR"];

            if (!string.IsNullOrEmpty(ipAddress))
            {
                string[] addresses = ipAddress.Split(',');
                if (addresses.Length != 0)
                {
                    return addresses[0];
                }
            }

            return context.Request.ServerVariables["REMOTE_ADDR"];
        }

How to check string is alphanumeric or not in c#?

Entered string is alphanumeric or not.

you can check through Regex class. and here regex pattern for valid alphanumeric string.

public static bool IsAlphaNumeric(String strToCheck)
        {
            string strRegex = "[^a-zA-Z0-9 ]";
            System.Text.RegularExpressions.Regex _Regex = new System.Text.RegularExpressions.Regex(strRegex);
            if (_Regex.IsMatch(strToCheck))
                return (true);
            else
                return (false);
        }

Check entered email is valid or not in c#

how can you check that entered email is in valid format or not.

you can check this through Regex class that will match the format for email. you have to define the email format.

here the code in c#

 public static bool IsValidEmail(string Email)
        {
            string strRegex = @"^([a-zA-Z0-9_\-\.]+)@((\[[0-9]{1,3}" +
                @"\.[0-9]{1,3}\.[0-9]{1,3}\.)|(([a-zA-Z0-9\-]+\" +
                @".)+))([a-zA-Z]{2,4}|[0-9]{1,3})(\]?)$";
            System.Text.RegularExpressions.Regex _Regex = new System.Text.RegularExpressions.Regex(strRegex);
            if (_Regex.IsMatch(Email))
                return (true);
            else
                return (false);
        }

Download a image from a website uri in c#

Check whether the file exists and download the file if it does, it’s better to do this within the same request.

you neead web uri to download the image.

and this method will return bool value that represents that image is availabe or not.

public static bool DownloadRemoteImageFile(string uri, string fileName)
{
bool hasSucessfull = false;
try
{

HttpWebRequest request = (HttpWebRequest)WebRequest.Create(uri);
HttpWebResponse response = (HttpWebResponse)request.GetResponse();

// Check that the remote file was found. The ContentType
// check is performed since a request for a non-existent
// image file might be redirected to a 404-page, which would
// yield the StatusCode "OK", even though the image was not
// found.
if ((response.StatusCode == HttpStatusCode.OK ||
response.StatusCode == HttpStatusCode.Moved ||
response.StatusCode == HttpStatusCode.Redirect) &amp;&amp;
response.ContentType.StartsWith("image", StringComparison.OrdinalIgnoreCase))
{

// if the remote file was found, download oit
using (Stream inputStream = response.GetResponseStream())
using (Stream outputStream = File.OpenWrite(fileName))
{
byte[] buffer = new byte[4096];
int bytesRead;
do
{
bytesRead = inputStream.Read(buffer, 0, buffer.Length);
outputStream.Write(buffer, 0, bytesRead);
} while (bytesRead != 0);

hasSucessfull = true;
}
}

}
catch (Exception ex)
{
//ErrorLog.WriteError(ex);
}
return hasSucessfull;
}

How to access google chrome history in c# programming with sqlite

First you need to add a reference to the SQLite assembly, its very similar to standard ADO.net.

All the user history is stored in the History database located at the path in the connection string below.


using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace BrowserHistory
{
class Program
{
static void Main(string[] args)
{

SQLiteConnection conn = new SQLiteConnection
(@"Data Source=C:\Users\Mahaveer Jangid\Desktop\History");
conn.Open();
SQLiteCommand cmd = new SQLiteCommand();
cmd.Connection = conn;
// cmd.CommandText = "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name;";
// Use the above query to get all the table names
cmd.CommandText = "Select * From urls";
SQLiteDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
Console.WriteLine(dr[1].ToString());
Console.WriteLine("----------------------------------------");
}

Console.ReadLine();
}
}

}

Program to Check Whether a String Palindrome is or not in c#

It contains a program to reverse a string and to check whether a Entered string is palindrome or not.

using System; 
namespace palindrome 
{ 
class Program 
{ 
static void Main(string[] args) 
{ 
string s,revs=""; 
Console.WriteLine(" Enter string"); 
s = Console.ReadLine(); 
for (int i = s.Length-1; i &gt;=0; i--) //String Reverse 
{ 
revs += s[i].ToString(); 
} 
if (revs == s) // Checking whether string is palindrome or not 
{ 
Console.WriteLine("String is Palindrome \n Entered String Was {0} and reverse string is {1}", s, revs); 
} 
else 
{ 
Console.WriteLine("String is not Palindrome \n Entered String Was {0} and reverse string is {1}", s, revs); 
} 
Console.ReadKey(); 
} 
} 
}