在 C# 中连接到 SQL Server
本文关键字:SQL Server 连接 | 更新日期: 2024-10-31 11:44:52
我是C#的初学者程序员。我正在尝试开发一个连接到数据库的应用程序,并执行插入、删除、更新和获取等典型操作。
我在数据库连接时遇到错误。我正在使用SQL Server 2012,其中我创建了一个名为company
的数据库。
这是我的代码:
namespace DAL
{
public class DAL
{
public const string CADENA_CONEXION = "Data Source=localhost;" +
"Initial Catalog=Company" +
"Integrated Security=false" +
"UID=root PWD=root";
public SqlConnection con;
public SqlCommand command;
public DAL()
{
con = new SqlConnection();
con.ConnectionString = CADENA_CONEXION;
}
public Boolean addEmployee(Employee emp)
{
try
{
/*String sqlInsertString = "INSERT INTO Employee (FirstName, LastName, ID, " +
"Designation) VALUES ("+e.firstName+","+ e.lastName+","+e.empCode+","+e.designation+")";*/
string sqlInsertString =
"INSERT INTO Employee (FirstName, LastName, ID, " +
"Designation) VALUES (@firstName, @lastName, @ID, @designation)";
command = new SqlCommand();
command.Connection.Open();
command.CommandText = sqlInsertString;
SqlParameter firstNameparam = new SqlParameter("@firstName", emp.FirstName);
SqlParameter lastNameparam = new SqlParameter("@lastName", emp.LastName);
SqlParameter IDparam = new SqlParameter("@ID", emp.EmpCode);
SqlParameter designationParam = new SqlParameter("@designation", emp.Designation);
command.Parameters.AddRange(new SqlParameter[]{
firstNameparam,lastNameparam,IDparam,designationParam});
command.ExecuteNonQuery();
command.Connection.Close();
return true;
}
catch (Exception ex)
{
return false;
throw;
}
return true;
}
}
错误是什么?我在这一行得到一个例外:
command.Connection.Open();
提前致谢
SqlConnection con = new SqlConnection("Your Connection String Goes here");
你应该像这样分配对 SqlCommand 对象的连接
SqlCommand command = new SqlCommand();
command.Connection = con;
或
SqlCommand command = new SqlCommand("YourQuery",con);
执行命令的一些重要步骤
1:创建 SqlConnection 对象并为该对象分配连接字符串
SqlConnection con = new SqlConnection("Your Connection String Goes here");
或
SqlConnection con = new SqlConnection();
con.Connection = "Your Connection String Goes here";
2:创建 SqlCommand 对象并将命令文本(查询)和连接字符串附加到该对象
SqlCommand command = new SqlCommand("Select * from Products",con);
或
SqlCommand command = new SqlCommand();
command.Connection = con;
command.CommandText ="Select * from Products";
您还可以指定命令类型
command.CommandType =CommandType.Text;
/* if you are executing storedprocedure CommandType Will be
=> CommandType.StoredProcedure; */
那么你可以像这样执行命令
try
{
con.Open();
int TotalRowsAffected = command.ExecuteNonQuery();
}
catch(Exeception ex)
{
MessageBox.Show(ex.Message);
}
finaly
{
con.Close();
}
仅供参考: 尝试最终阻止的替代方法,确保数据库连接关闭,是使用 using 语句,例如:
using (SqlConnection connection = new SqlConnection(
connectionString))
{
try
{
SqlCommand command = new SqlCommand(queryString, connection);
command.Connection.Open();
command.ExecuteNonQuery();
}
catch (InvalidOperationException)
{
//log and/or rethrow or ignore
}
catch (SqlException)
{
//log and/or rethrow or ignore
}
catch (ArgumentException)
{
//log and/or rethrow or ignore
}
}
请参阅此处的 SqlCommand 类的 MSDN 文档,https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.aspx,并查找用于执行 INSERT、UPDATE 和 DELETE 语句的 ExecuteNonQuery() 方法。 然后查找可用于执行返回单个值的 SELECT 语句的 ExecuteScalar() 方法。 您可以使用 ExecuteReader() 为 SELECT 语句返回返回多个列的 SqlDataReader。
不初始化sqlcommand连接,初始化方法是:
command.Connection=con;
这是完整的代码:
namespace DAL
{
public class DAL
{
public const string CADENA_CONEXION = "Data Source=localhost;" +
"Initial Catalog=Company" +
"Integrated Security=false" +
"UID=root PWD=root";
public SqlConnection con;
public SqlCommand command;
public DAL()
{
con = new SqlConnection();
con.ConnectionString = CADENA_CONEXION;
}
public Boolean addEmployee(Employee emp)
{
try
{
/*String sqlInsertString = "INSERT INTO Employee (FirstName, LastName, ID, " +
"Designation) VALUES ("+e.firstName+","+ e.lastName+","+e.empCode+","+e.designation+")";*/
string sqlInsertString =
"INSERT INTO Employee (FirstName, LastName, ID, " +
"Designation) VALUES (@firstName, @lastName, @ID, @designation)";
command = new SqlCommand();
command.Connection=con;
command.Connection.Open();
command.CommandText = sqlInsertString;
SqlParameter firstNameparam = new SqlParameter("@firstName", emp.FirstName);
SqlParameter lastNameparam = new SqlParameter("@lastName", emp.LastName);
SqlParameter IDparam = new SqlParameter("@ID", emp.EmpCode);
SqlParameter designationParam = new SqlParameter("@designation", emp.Designation);
command.Parameters.AddRange(new SqlParameter[]{
firstNameparam,lastNameparam,IDparam,designationParam});
command.ExecuteNonQuery();
command.Connection.Close();
return true;
}
catch (Exception ex)
{
return false;
throw;
}
return true;
}
}