CODEWITHSUNDEEP
asp.netsql-servervisual-studio-2012
naz Edyra
naz Edyra

Reputation: 35

How to write the SQL query in Visual Studio?

I want to write all the insert, select and delete queries in Visual Studio but I don't know how to write them.

SqlConnection con = new SqlConnection("Data Source=5CG50749V3\\SQLEXPRESS;Initial Catalog=test;Integrated Security=True");
con.Open();

SqlCommand cmd = new SqlCommand("INSERT INTO backup(Option,EquipmentID,SerialNumber,Description,Location,DueDate,DaytoDue,EquipmentWithdraworRemarks,NCRorOOTHistory,LastOOTissuanceDate,AvailableinSapphire,ResponsiblePerson,CalibrationOption,CalibrationSourceorLab,YearofManufacturing,ManufacturerorVendor,CalibrationCost,AssetNo,CalibrationTAT,SendInDate,Status), SELECT * FROM Equipment where (SerialNumber = '" + TextBox2.Text + "' or EquipmentID = '" + TextBox1.Text + "' ), DELETE FROM Equipment where (SerialNumber = '" + TextBox2.Text + "' or EquipmentID = '" + TextBox1.Text + "' )", con);

cmd.ExecuteNonQuery();
con.Close();

Upvotes: 0

Views: 7629

Answers (2)

Amir
Amir

Reputation: 2052

You can do this using parameterized query like below:

string connectionstring = "Data Source=5CG50749V3\\SQLEXPRESS;Initial Catalog=test;Integrated Security=True";
            using (SqlConnection connection = new SqlConnection(connectionstring))
            {

                string sql = @"INSERT IntO [backup](Option,EquipmentID,SerialNumber,Description,Location,
                                DueDate,DaytoDue,EquipmentWithdraworRemarks,NCRorOOTHistory,LastOOTissuanceDate,
                                AvailableinSapphire,ResponsiblePerson,CalibrationOption,CalibrationSourceorLab,
                                YearofManufacturing,ManufacturerorVendor,CalibrationCost,AssetNo,CalibrationTAT,SendInDate,Status)
                                SELECT * FROM Equipment where SerialNumber = @serialnumber or EquipmentID = @equipmentId; 
                                DELETE FROM Equipment where SerialNumber = @serialnumber or EquipmentID = @equipmentId";
                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    command.Parameters.Add("@serialnumber", SqlDbType.NVarChar).Value = "testSerialNumber";
                    command.Parameters.Add("@equipmentId", SqlDbType.NVarChar).Value = "testequipmentid";
                    connection.Open();
                    command.ExecuteNonQuery();
                    connection.Close();
                }

            }

Upvotes: 1

AsthaUndefined
AsthaUndefined

Reputation: 1109

The SqlCommand(String) method takes only one argument and initializes a new instance of the SqlCommand class with the text of the query.

Upvotes: 1

Related Questions