Uncategorized

MSSQL StoredProcedure using a parameter

This SP has been developed using MS SQL . This SP takes parameter @tblProjects_ID and Updates The Column bClosed WHERE TABLE ID = That parameter .

At The End of this Post , Click the Link to know how to Call this SP from C# .

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spU_ChangeProjectStatus]
(
@tblProjects_ID INT
)
AS
SET NOCOUNT ON
BEGIN
DECLARE @B_CLOSED BIT;
SELECT @B_CLOSED=bClosed FROM tblProjects WHERE ID=@tblProjects_ID
IF @B_CLOSED = 'FALSE'
UPDATE tblProjects SET bClosed='true' WHERE ID=@tblProjects_ID
ELSE
UPDATE tblProjects SET bClosed='false' WHERE ID=@tblProjects_ID
END

Click Here to see How to Call SP from C#

Advertisements
Standard
Uncategorized

Sql Transaction in C# : Part 2 – Doing in different Database

Here ,I have tried to show how you can use Sql Transaction for 2 Databases with different conection String . So you can use Sql Transaction on two DB Servers .

When you will go to first run this Sql Transaction in a Transaction Scope , you will get exception . For this, Click Enable “Distributed Transaction Coordinator” in SQL Server.

private void btnTransactionInDifferentDatabase_Click(object sender, EventArgs e)
        {
            string connectString1 = "Data Source=masoom-PC;Initial Catalog=TransactionDB;Integrated Security=true;";
            string connectString2= "Data Source=masoom-PC;Initial Catalog=TransactionDB2;Integrated Security=true;";

            string commandText1 = "INSERT INTO Transaction_T VALUES('2','30.33','Babu')";
            string commandText2 = "INSERT INTO Transaction2_T VALUES('2','20.3','Babu')";
            
            int returnValue = 0;
            System.IO.StringWriter writer = new System.IO.StringWriter();

            try
            {
               using (TransactionScope scope = new TransactionScope())
                {
                    using (SqlConnection connection1 = new SqlConnection(connectString1))
                    {
                        connection1.Open();

                        // Create the SqlCommand object and execute the first command.
                        SqlCommand command1 = new SqlCommand(commandText1, connection1);
                        returnValue = command1.ExecuteNonQuery();
                        writer.WriteLine("Rows to be affected by command1: {0}", returnValue);
                       
                        using (SqlConnection connection2 = new SqlConnection(connectString2))
                        {
                            // The transaction is escalated to a full distributed
                            // transaction when connection2 is opened.
                            connection2.Open();

                            // Execute the second command in the second database.
                            returnValue = 0;
                            SqlCommand command2 = new SqlCommand(commandText2, connection2);
                            returnValue = command2.ExecuteNonQuery();
                            writer.WriteLine("Rows to be affected by command2: {0}", returnValue);
                        }
                    }
                    // The Complete method commits the transaction. If an exception has been thrown,
                    // Complete is not  called and the transaction is rolled back.
                    scope.Complete();
                }
            }
            catch (TransactionAbortedException ex)
            {
                writer.WriteLine("TransactionAbortedException Message: {0}", ex.Message);
            }
            catch (ApplicationException ex)
            {
                writer.WriteLine("ApplicationException Message: {0}", ex.Message);
            }

            label1.Text = writer.ToString();
        }

Standard
Uncategorized

Sql Transaction in C# : Part 1 – Doing in same Database

Here ,sqlQuery1 will execute and the value should be inserted in the table but it will fail for sqlQuery2. Coz in the sqlQuery2 , 2nd column value is varchar rather than decimal .sqlQuery2 is the coz of failure because both sqlQuery1 and sqlQuery2 are in the same transaction.As a result , no row will be inserted to the table Transaction_T

private void btnTransactionInSameDatabase_Click(object sender, EventArgs e)
        {
            string connectionStr = "Data Source=masoom-PC;Initial Catalog=TransactionDB;Integrated Security=true;";

            SqlConnection conn = new SqlConnection(connectionStr);
            conn.Open();

            string sqlQuery1 = "INSERT INTO Transaction_T VALUES('2','30.33','Babu')";
            string sqlQuery2 = "INSERT INTO Transaction_T VALUES('2','Mas','Babu')";

            SqlTransaction transaction = conn.BeginTransaction();

            try
            {
                SqlCommand cmd = new SqlCommand(sqlQuery1, conn,transaction);
                int res = cmd.ExecuteNonQuery();
                 
                cmd = new SqlCommand(sqlQuery2, conn,transaction);
                int res2 = cmd.ExecuteNonQuery();

                transaction.Commit();
            }
            catch (SqlException exception)
            {
                transaction.Rollback();
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
        }

Standard
Uncategorized

How to consume asmx service in ASP.Net , C#

Below ,Its only a test application which will test the service is working fine or not .

Service.add_account__486ndh42929d_3wkqq_43kj3j2003ksinn_33 service = new add_account__486ndh42929d_3wkqq_43kj3j2003ksinn_33();
service.Url = "http://localhost:3379/add-account--486ndh42929d-3wkqq-43kj3j2003ksinn-33.asmx";
service.Timeout = 500 * 1000;
structCustomerAdder customerAdder = service.structCustomerAdderAddCustomer
(
(Service.enumTiers) Enum.Parse(typeof(enumTiers),ddlAppTier.SelectedItem.ToString()),
txtFirstName.Text,
txtLastName.Text,
txtEmail.Text,
SiteAuthentication.clsSiteAuthentication.sPwd_Add_Acct_WebService
);

Standard