如何使用背后的c#代码创建sql连接，访问sql server然后有条件地重定向？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9806166/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to create sql connection with c# code behind, access the sql server then conditionally redirect?
提问by elarrow
This is a question from an experienced beginner!
这是一个有经验的初学者的问题！
Using ASP.NET 4 C# AND SQL server,
使用 ASP.NET 4 C# 和 SQL 服务器，
I have a connection string in web.config to myDatabase named "myCS". I have a database named myDB. I have a table named myTable with a primary key named myPK
我在 web.config 中有一个连接字符串到名为“myCS”的 myDatabase。我有一个名为 myDB 的数据库。我有一个名为 myTable 的表，主键名为 myPK
What are the NECESSARY lines of code behind (minimal code) to create a SQL connection, then select from myTable where myPK=="simpleText"
创建 SQL 连接的必要代码行是什么（最小代码），然后从 myTable 中选择其中 myPK=="simpleText"
it will probably include:
它可能包括：
sqlconnection conn = new sqlconnection(??? myCS)
string SQLcommand = select * from myDB.myTable where myPK==myTestString;
sqlCommand command = new SqlCommand(SQL,conn);
conn.Open();
booleanFlag = ????
conn.Close();
conn.Dispose();
then
然后
If ( theAnswer != NULL ) // or (if flag)
{
Response.Redirect("Page1.aspx");
}
else
{
Response.Redirect("Page2.aspx");
}
采纳答案by Different111222
Here is a limited simple tutorial:
这是一个有限的简单教程：
First, you want to have a class to do the hard work for you, then you will use it with ease.
首先，您希望有一个类为您完成繁重的工作，然后您将轻松使用它。
First, you have to crate the connection string in your web.config file and name it.
Here it is named DatabaseConnectionString, but you may named it myCSas required in the question.
首先，您必须在 web.config 文件中创建连接字符串并为其命名。这里它被命名为DatabaseConnectionString，但您可以myCS根据问题的要求命名它。
Now, in App_Code create a new class file and name it SqlComm(this is just an example name) like:
现在，在 App_Code 中创建一个新的类文件并将其命名SqlComm（这只是一个示例名称），例如：
using System;
using System.Data;
using System.Data.SqlClient;
using System.Web;
public class SqlComm
{
// this is a shortcut for your connection string
static string DatabaseConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["dbConStr"].ConnectionString;
// this is for just executing sql command with no value to return
public static void SqlExecute(string sql)
{
using (SqlConnection conn = new SqlConnection(DatabaseConnectionString))
{
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Connection.Open();
cmd.ExecuteNonQuery();
}
}
// with this you will be able to return a value
public static object SqlReturn(string sql)
{
using (SqlConnection conn = new SqlConnection(DatabaseConnectionString))
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
object result = (object)cmd.ExecuteScalar();
return result;
}
}
// with this you can retrieve an entire table or part of it
public static DataTable SqlDataTable(string sql)
{
using (SqlConnection conn = new SqlConnection(DatabaseConnectionString))
{
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Connection.Open();
DataTable TempTable = new DataTable();
TempTable.Load(cmd.ExecuteReader());
return TempTable;
}
}
// sooner or later you will probably use stored procedures.
// you can use this in order to execute a stored procedure with 1 parameter
// it will work for returning a value or just executing with no returns
public static object SqlStoredProcedure1Param(string StoredProcedure, string PrmName1, object Param1)
{
using (SqlConnection conn = new SqlConnection(DatabaseConnectionString))
{
SqlCommand cmd = new SqlCommand(StoredProcedure, conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter(PrmName1, Param1.ToString()));
cmd.Connection.Open();
object obj = new object();
obj = cmd.ExecuteScalar();
return obj;
}
}
}
Okay, this only a class, and now you should know how to use it:
好的，这只是一个类，现在你应该知道如何使用它了：
If you wish to execute a command like delete, insert, update etc. use this:
如果您希望执行删除、插入、更新等命令，请使用以下命令：
SqlComm.SqlExecute("TRUNCATE TABLE Table1");
but if you need to retrieve a specific value from the database use this:
但如果您需要从数据库中检索特定值，请使用以下命令：
int myRequiredScalar = 0;
object obj = new object();
obj = SqlComm.SqlReturn("SELECT TOP 1 Col1 FROM Table1");
if (obj != null) myRequiredScalar = (int)obj;
You can retrieve a bunch of rows from the database this way (others like other ways) This is relevant to your sepecific question
您可以通过这种方式从数据库中检索一堆行（其他方式如其他方式）这与您的特定问题有关
int Col1Value = 0;
DataTable dt = new DataTable();
dt = SqlComm.SqlDataTable("SELECT * FROM myTable WHERE myPK='simpleText'");
if (dt.Rows.Count == 0)
{
// do something if the query return no rows
// you may insert the relevant redirection you asked for
}
else
{
// Get the value of Col1 in the 3rd row (0 is the first row)
Col1Value = (int)dt.Rows[2]["Col1"];
// or just make the other redirection from your question
}
If you need to execute a stored procedure with or without returning a value back this is the way to do that (in this example there are no returning value)
如果您需要在有或没有返回值的情况下执行存储过程，这是执行此操作的方法（在此示例中没有返回值）
SqlComm.SqlStoredProcedure1Param("TheStoredProcedureName", "TheParameterName", TheParameterValue);
Again, for your specific question return the table using the SqlDataTable, and redirect if dt.Rows.Count >0
同样，对于您的特定问题，使用 返回表SqlDataTable，并重定向如果dt.Rows.Count >0
Have fun.
玩得开心。
回答by Amen Ayach
There are many ways: LINQ, SqlDataReader, SQLDataAdapter, according to what you want to read (single value, datatable ...), so here is an example:
有很多方法：LINQ，SqlDataReader，SQLDataAdapter，根据你想读什么（单值，数据表...），所以这里是一个例子：
using (SqlConnection con = new SqlConnection("SomeConnectionString"))
{
var cmd = new SqlCommand("select from myTable where myPK==N'"+ simpleText+ "'",con);
cmd.Connection.Open();
var sqlReader = cmd.ExecuteReader();
while(sqlReader.Read())
{
//Fill some data like : string result = sqlReader("SomeFieldName");
}
sqlReader.Close();
cmd.Connection.Close();
cmd.Dispose();
}
