Show me how to connect to a local database and run SQL commands using .NET Framework 4.8
- User
Show me how to connect to a local database and run SQL commands using .NET Framework 4.8
- Assistant
To connect to a local database and run SQL commands using .NET Framework 4.8, you can use the classes provided by the
System.Data.SqlClientnamespace for SQL Server databases, or the corresponding namespace for other databases (likeMySql.Data.MySqlClientfor MySQL,Npgsqlfor PostgreSQL, etc).Here is an example using SQL Server:
First, ensure that you have added the
System.Data.SqlClientreference to your project.Then, in your C# code, you would do something like this:
using System; using System.Data.SqlClient; class Program { static void Main(string[] args) { // Connection string for your local database // Make sure to modify the DataSource and InitialCatalog according to your setup // Integrated Security is set to true for Windows Authentication // For SQL Server Authentication, you will need to specify UserID and Password string connectionString = "Data Source=.;Initial Catalog=YourDatabaseName;Integrated Security=True"; // SQL command you want to execute string sql = "SELECT * FROM YourTableName"; // Create a SqlConnection using the provided connection string using (SqlConnection connection = new SqlConnection(connectionString)) { try { // Open the SqlConnection connection.Open(); Console.WriteLine("Connection opened successfully."); // Create a SqlCommand using the connection and the SQL string using (SqlCommand command = new SqlCommand(sql, connection)) { // Execute the SqlCommand and obtain a SqlDataReader using (SqlDataReader reader = command.ExecuteReader()) { // Read the data while (reader.Read()) { // Assuming your table has at least one column named 'ColumnName' Console.WriteLine(reader["ColumnName"].ToString()); } } } } catch (Exception ex) { // Handle any exceptions that occur Console.WriteLine("An error occurred: " + ex.Message); } finally { // Close the connection if it's open if (connection.State == System.Data.ConnectionState.Open) connection.Close(); Console.WriteLine("Connection closed."); } } Console.WriteLine("Press any key to exit..."); Console.ReadKey(); } }Before running the code, make sure the database server is running, the database exists, and you've correctly replaced
YourDatabaseName,YourTableName, and theDataSourcein the connection string with the relevant details of your local setup.Additionally, if you want to execute a non-query command (e.g.,
INSERT,UPDATE,DELETE), you would use theExecuteNonQuerymethod of theSqlCommandobject:string sql = "INSERT INTO YourTableName (Column1, Column2) VALUES ('Value1', 'Value2')"; using (SqlCommand command = new SqlCommand(sql, connection)) { int affectedRows = command.ExecuteNonQuery(); Console.WriteLine($"Number of rows affected: {affectedRows}"); }For parameterized queries (to prevent SQL injection), use parameters like so:
string sql = "INSERT INTO YourTableName (Column1, Column2) VALUES (@param1, @param2)"; using (SqlCommand command = new SqlCommand(sql, connection)) { command.Parameters.AddWithValue("@param1", "Value1"); command.Parameters.AddWithValue("@param2", "Value2"); int affectedRows = command.ExecuteNonQuery(); Console.WriteLine($"Number of rows affected: {affectedRows}"); }Make sure to match the data types of the columns in your table when adding values to the parameters.