Database

if not exists(select * from tblStafff where StaffID = @StaffID)
Begin
   -- Add record
End

ALTER PROCEDURE SP_CHECKDUPLICATEID
	-- Add the parameters for the stored procedure here
	(@ID INT)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here

	-- VARIABLE DECLARATIONS
	
    IF ( (SELECT COUNT(*) FROM TBLSTAFF WHERE ID=@ID) = 0 )

	BEGIN

		INSERT INTO TBLSTAFF(ID) VALUES(@ID)

	END
END
GO

Phumlani Kunene wrote:

How to code connection string for retriving database when is online?

Phumlani Kunene wrote:

How to code dynamic connection string?

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <configSections>
  </configSections>
  <connectionStrings>   
    <add name="dbName" connectionString="Data Source=DBServer\SQLEXPRESS;Initial Catalog=DBHM;Integrated Security=True"
      providerName="System.Data.SqlClient" User Name="Niladri" Pwd="password"/>
  </connectionStrings>  
</configuration>

a)name="dbName" ; 

It is a string which we will access from the class file

b)Data Source</b>=DBServer\SQLEXPRESS;; 

The database server name 

c)Initial Catalog</b>=DBHM; 

The database name

System.Configuration.ConfigurationManager.ConnectionStrings["dbName"].ConnectionString;

<add name="dbName1" connectionString="Data Source=DBServer\SQLEXPRESS;Initial Catalog=DBHM1;Integrated Security=True"
      providerName="System.Data.SqlClient" />

<add name="dbName2" connectionString="Data Source=DBServer\SQLEXPRESS;Initial Catalog=DBHM2;Integrated Security=True"
      providerName="System.Data.SqlClient" />

<add name="dbName3" connectionString="Data Source=DBServer\SQLEXPRESS;Initial Catalog=DBHM3;Integrated Security=True"
      providerName="System.Data.SqlClient" />

<add name="dbName4" connectionString="Data Source=DBServer\SQLEXPRESS;Initial Catalog=DBHM4;Integrated Security=True"
      providerName="System.Data.SqlClient" />

SqlConnection Conn = new SqlConnection();
switch(condition)
{

case 1: Conn.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["dbName1"].ConnectionString;
break;

case 2: Conn.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["dbName2"].ConnectionString;
break;


case 3: Conn.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["dbName3"].ConnectionString;
break;


case 4: Conn.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["dbName4"].ConnectionString;
break;
}

Conn.Open();

ALTER PROCEDURE [dbo].[sp_ValidateUser] 
	-- Add the parameters for the stored procedure here
	(
		@UserName VarChar(50), @Password VarChar(50)
	)

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    SELECT count(*) AS Num_of_User FROM tblUser
	WHERE (((tblUser.U_Name) = (@UserName)) AND ((tblUser.U_Password) = (@Password)))

END

public SqlConnection OpenConnection()
        {
            SqlConnection Conn = new SqlConnection();
            Conn.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["dbName"].ConnectionString;
            Conn.Open();
            return Conn;
        }

        public void CloseConnection(SqlConnection Conn)
        {
            Conn.Close();
        }

        public SqlCommand GetStoredProcCommand(string CommandText)
        {
            SqlCommand CmdStProc = new SqlCommand();
            try
            {
                CmdStProc.Connection = OpenConnection();
                CmdStProc.CommandText = CommandText;
                CmdStProc.CommandType = CommandType.StoredProcedure;
            }
            catch (Exception ex)
            {
                string msg = ex.Message;
            }
            return CmdStProc;
        } 

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <configSections>
  </configSections>
  <connectionStrings>
    <add name="dbName" connectionString="Data Source=yourdatasource;Initial Catalog=DatabaseName;Integrated Security=True"
      providerName="System.Data.SqlClient" />   
  </connectionStrings>  
</configuration>

public DataTable UserValidation(string UserName, string Password)
        {
            DBConnection ObjData = new DBConnection();
            SqlCommand SelectCmd = new SqlCommand();
            SqlDataAdapter Adpt = new SqlDataAdapter();
            DataTable DtUserInfo = new DataTable();

            try
            {
                SelectCmd = ObjData.GetStoredProcCommand("sp_ValidateUser");
                SelectCmd.Parameters.Add("@UserName", SqlDbType.Int);
                SelectCmd.Parameters.Add("@Password", SqlDbType.VarChar,1);

                SelectCmd.Parameters[0].Value = UserName;
                SelectCmd.Parameters[1].Value = Password;

                Adpt.SelectCommand = SelectCmd;
                Adpt.Fill(DtUserInfo);
            }
            catch (Exception ex)
            {
                string error = ex.Message;
            }
            Adpt = null;
            SelectCmd = null;
            ObjData = null;
            return DtUserInfo;
        }

ValidateUser objValidateUser = new ValidateUser();

DataTable dt = objValidateUser .UserValidation(txtUserName.Text,txtPassword.Text);

if(dt!=null)
{
    if(dt.Rows.Count > 0)
 
   {
          if(Convert.Toint32(dt.Rows[0][0].ToString()) > 0) 

            lblMessage.Text = "Valid User";

          else
              lblMessage.Text = "InValid User";
  }

}

a
b
c
 a
 b
 c
AC
AD
 AD
AE
 AE
AF
 AF
       AFGTHY
BE
 BE

ALTER PROCEDURE sp_Search
	-- Add the parameters for the stored procedure here
	(@SEARCHSTRING VARCHAR(50))
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- SEARCH WITH ABC COMBINATION
	IF(UPPER(@SEARCHSTRING) = 'ABC')

		BEGIN
			
			SELECT * 
			FROM TBLSEARCH WHERE RTRIM(LTRIM(NAME)) LIKE '[A-C]%'

		END

	-- SEARCH WITH DEF COMBINATION
	IF(UPPER(@SEARCHSTRING) = 'DEF')

		BEGIN
			
			SELECT * 
			FROM TBLSEARCH WHERE 

				RTRIM(LTRIM(NAME)) LIKE 'A[DEF]%' OR

				RTRIM(LTRIM(NAME)) LIKE 'B[DEF]%' OR

				RTRIM(LTRIM(NAME)) LIKE 'C[DEF]%'

		END

	-- SEARCH WITH GHI COMBINATION
	IF(UPPER(@SEARCHSTRING) = 'GHI')

		BEGIN
			
			SELECT * 
			FROM TBLSEARCH WHERE 

				RTRIM(LTRIM(NAME)) LIKE 'A[DEF][GHI]%' OR

				RTRIM(LTRIM(NAME)) LIKE 'B[DEF][GHI]%' OR

				RTRIM(LTRIM(NAME)) LIKE 'C[DEF][GHI]%'

		END

END
GO