Insert into C# with SQLcommand

SQL Insert Query Using C#

I assume you have a connection to your database and you can not do the insert parameters using c #.

You are not adding the parameters in your query. It should look like:

String query = "INSERT INTO dbo.SMS_PW (id,username,password,email) VALUES (@id,@username,@password, @email)";

SqlCommand command = new SqlCommand(query, db.Connection);
command.Parameters.Add("@id","abc");
command.Parameters.Add("@username","abc");
command.Parameters.Add("@password","abc");
command.Parameters.Add("@email","abc");

command.ExecuteNonQuery();

Updated:

using(SqlConnection connection = new SqlConnection(_connectionString))
{
    String query = "INSERT INTO dbo.SMS_PW (id,username,password,email) VALUES (@id,@username,@password, @email)";

    using(SqlCommand command = new SqlCommand(query, connection))
    {
        command.Parameters.AddWithValue("@id", "abc");
        command.Parameters.AddWithValue("@username", "abc");
        command.Parameters.AddWithValue("@password", "abc");
        command.Parameters.AddWithValue("@email", "abc");

        connection.Open();
        int result = command.ExecuteNonQuery();

        // Check Error
        if(result < 0)
            Console.WriteLine("Error inserting data into Database!");
    }
}

How to insert data into a database table using a SqlCommand

If I had enough reputation, I would rather post this as a reply, but it might actually be the solution.

The reason why it stops there is because you are not providing a legit SqlConnection, since your input is: "ConnString", which is just that text.

The connection string should look something like:

const string MyConnectionString = "SERVER=localhost;DATABASE=DbName;UID=userID;PWD=userPW;"

Which in your case should end up like:

System.Data.SqlClient.SqlConnection sqlConnection1 = new System.Data.SqlClient.SqlConnection(MyConnectionString);

Besides that, you should build your connections like following:

using (SqlConnection con = new SqlConnection(MyConnectionString)) {

using (SqlCommand cmd = new SqlCommand())
{
    cmd.CommandText = xxxxxx; // Your query to the database
    cmd.Connection = con;
    cmd.Connection.Open();
    cmd.ExecuteNonQuery();
}

}

This will do the closing for you and it also makes it easier for you to nestle connections. I did a project recently and did the connection your way, which ended up not working when I wanted to do more than one execute in one function. Just important to make a new command for each execute.

SqlCommand INSERT query does not insert data into the database

You have forgotten to execute the query:

cmd.ExecuteNonQuery();

It is also better to close the connection after the work is done:

conn.Close();

How do I to insert data into an SQL table using C# as well as implement an upload function?

You should use parameters in your query to prevent attacks, like if someone entered '); drop table ArticlesTBL;--' as one of the values.

string query = "INSERT INTO ArticlesTBL (ArticleTitle, ArticleContent, ArticleType, ArticleImg, ArticleBrief,  ArticleDateTime, ArticleAuthor, ArticlePublished, ArticleHomeDisplay, ArticleViews)";
query += " VALUES (@ArticleTitle, @ArticleContent, @ArticleType, @ArticleImg, @ArticleBrief, @ArticleDateTime, @ArticleAuthor, @ArticlePublished, @ArticleHomeDisplay, @ArticleViews)";

SqlCommand myCommand = new SqlCommand(query, myConnection);
myCommand.Parameters.AddWithValue("@ArticleTitle", ArticleTitleTextBox.Text);
myCommand.Parameters.AddWithValue("@ArticleContent", ArticleContentTextBox.Text);
// ... other parameters
myCommand.ExecuteNonQuery();

(xkcd)

How do I insert SQL values into an SQL Command to create a table?

You need to use dynamic SQL for this.

• Be careful: you must correctly escape the table name using QUOTENAME
• I would advise you not to blindly catch all exceptions. Let them pass through to the calling function, which can decide how to display or log the error.
• The parameter should be nvarchar(128)
• Note the use of a verbatim multi-line string using @""

public void CreateTable(string tableName)
{
    const string query = @"
DECLARE @sql nvarchar(max) = '
CREATE TABLE ' + QUOTENAME(@tableName) + '(
  indicatorid INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
  value REAL,
  date DATE,
  ticker VARCHAR(20)
)
';

EXEC sp_executesql @sql;
";
    using (SqlConnection conn = new SqlConnection(this.connectionstring))
    using (SqlCommand cmd = new SqlCommand(query, conn)
    {
        cmd.Parameters.Add("@tableName", SqlDbType.NVarChar, 128).Value = tableName;
        conn.Open();
        cmd.ExecuteNonQuery();
    }
}

Why insert statement is slow with SqlCommand?

It sounds like there is a high latency between your SQL server and your application server. When I do this locally, the pure TSQL version runs in 363ms, and the C# version with 2000 round trips takes 1061ms (so: about 0.53ms per round-trip). Note: I took the Console.WriteLine away, because I didn't want to measure how fast Console isn't!

For 2000 inserts, this is a pretty fair comparison. If you're seeing something massively different, then I suspect:

• your SQL server is horribly under-powered - it should not take 15s (from the question) to insert 2000 rows, under any circumstances (my 363ms timing is on my desktop PC, not a fast server)
• as already suggested; you have high latency

Note there are also things like "DTC" which might impact the performance based on the connection string and ambient transactions (TransactionScope), but I'm assuming those aren't factors here.

If you need to improve the performance here, the first thing to do would be to find out why it is so horribly bad - i.e. the raw server performance is terrible, and the latency is huge. Neither of those is a coding question: those are infrastructure questions.

If you can't fix those, then you can code around them. Table valued parameters or bulk-insert (SqlBulkCopy) both provide ways to transfer multiple rows without having to pay a round-trip per execute. You can also use "MARS" (multiple active results sets) and pipelined inserts, but that is quite an advanced topic (and most people tend to recommend not enabling MARS).

---

Related Topics