Database

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

Coming to your question directly, what will be the datatype for calculation,percentage?

Niladri_Biswas wrote:

For the choice of calculation, you didn't specify, what kind of calculation it is! However, if is mathematical calculations of currency or any aggregation of the same(e.g. Add,Multiply etc. which will be in fraction), go with money datatype.