Check the possiblity of SQL query to retrieve or insert data in C#-CodePudding

I have a number of SQL queries to run against a SQL Server database using ADO.NET and ASP.NET Core 5. Is there are a way to test-run the queries before really running them?

These queries can produce errors while reading or inserting data and this consumes a lot of time without a concrete result. I want to make sure there are no problems that might interfere before actually running all the queries.

How can I do this?

CodePudding user response：

It's not clear exactly what problem you're running into, but for the most likely case what you are wanting to do is not possible.

To elaborate, the common situation here is the errors are results of conflicts in the data: things like foreign or duplicate key violations. You can't know about those conflicts without actually evaluating all of the data that will be touched by the query, and this is effectively the same amount of work as running the query itself.

Therefore trial-running your queries as way to avoid these conflicts does not save you anything, and in reality doubles the amount of work you need to do on success, because after the trial run finishes you still need to run the query for real.

The next most common case is basic server and infrastructure availability. In this case, remember ADO.Net separates the DbConnection and DbCommand objects. If you can successfully call .Open() on your connection object, the infrastructure IS available and ready to process SQL commands; no need to run a query for that test, and you have to open the connection as part of your normal processing anyway.

CodePudding user response：

Maybe you can start with this generic concept.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;

namespace WindowsApplication1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            string connetionString = null;
            SqlConnection connection ;
            SqlDataAdapter adapter = new SqlDataAdapter();
            string sql = null;
            connetionString = "Data Source=ServerName;Initial Catalog=DatabaseName;User ID=UserName;Password=Password";
            connection = new SqlConnection(connetionString);
            sql = "insert into product (Product_id,Product_name,Product_price) values(6,'Product6',600)";
            try
            {
                connection.Open();
                adapter.InsertCommand = new SqlCommand(sql, connection);
                adapter.InsertCommand.ExecuteNonQuery();
                MessageBox.Show ("Row inserted !! ");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }
    }
}