如何循环sql server表来创建datagridview - sql表字段匹配csv字段 [英] how to loop sql server table to create a datagridview - sql table field matches csv field
问题描述
if (this.DataCenterLaborFileUpload.HasFile)
{
var extension = Path.GetExtension(DataCenterLaborFileUpload.FileName);
string currentName = "";
decimal currentHours = 0.00M;
decimal currentFTE = 0.00M;
string ResourceName = "";
if (extension == ".csv")
{
StreamReader csvreader = new StreamReader(DataCenterLaborFileUpload.FileContent);
DataTable dt = new DataTable();
dt.Columns.Add("txtName");
dt.Columns.Add("txtHours");
dt.Columns.Add("txtFTE");
while (!csvreader.EndOfStream)
{
DataRow dr = dt.NewRow();
var line = csvreader.ReadLine();
var values = line.Split(',');
if (values[0].Trim() != "Pers.No.")
{
SqlCommand cmd = new SqlCommand("SELECT ResourceName FROM StaffTracking where PersonnelResourceType = 'Supplier' order by PersonnelResourceType, ResourceName");
cmd.Connection = conn;
conn.Open();
if (ResourceName == (values[1].Trim()))
{
if (values[1].Trim() == currentName)
{
currentHours = currentHours + Convert.ToDecimal(values[9].Trim());
}
else
{
if (currentName != "")
{
dr["txtName"] = currentName;
dr["txtHours"] = currentHours;
dr["txtFTE"] = currentFTE + Math.Round(currentHours / (weekdaysInMonth() * 8), 2);
dt.Rows.Add(dr);
dt.AcceptChanges();
}
currentHours = Convert.ToDecimal(values[9].Trim());
currentName = values[1].Trim();
}
}
}
}
DataRow drfinal2 = dt.NewRow();
drfinal2["txtName"] = currentName;
drfinal2["txtHours"] = currentHours;
drfinal2["txtFTE"] = currentFTE + currentHours / (weekdaysInMonth() * 8);
dt.Rows.Add(drfinal2);
dt.AcceptChanges();
gvDataCenterLabor.DataSource = dt;
}
conn.Close();
gvDataCenterLabor.DataBind();
// Page.DataBind();
}
}
推荐答案
我不知道你的代码是否会在你之后100％运行应用此但至少它将解决从数据库中读取的问题。
示例数据库访问（对于SQL-Server），从打开连接到阅读全部记录，应用良好实践（使用块，Sql参数和抽象Db ***** - 基类而不是特定于数据库的类）：
I don't know if your code will work 100% after you apply this but at least it will solve the issue reading from the database.
A sample database-access (for SQL-Server), from opening the connection to reading all the records, applying good practices (using-Blocks, Sql-Parameters and abstract Db*****-baseclasses instead of database-specific classes):
using (DbConnection conn = new SqlConnection(connectionString))
{
conn.Open();
using (DbCommand cmd = conn.CreateCommand())
{
cmd.Connection = conn;
cmd.CommandText = "SELECT col1, col2 FROM table1 WHERE col3 = @p1;";
// let's assume col1 is an int (Int32); col2 and col3 a nvarchar (String)
DbParameter p1 = cmd.CreateParameter();
p1.ParameterName = "p1";
p1.Value = "something";
cmd.Parameters.Add(p1);
using (DbDataReader dr = cmd.ExecuteReader())
{
while (dr.Read()) // loops through all result records
{
int col1 = dr.GetInt32(0);
string col2 = dr.GetString(1);
// do something with col1 and col2 here
}
} // the datareader will be closed and disposed here
} // the command will be disposed here
} // the connection will be closed and disposed here
评论后编辑：
Sql-Parameters是将查询约束通信到数据库的最佳方式。在您的情况下，而不是：
Edit after comment:
Sql-Parameters are the best way to "communicate" your query-constraints to the database. In your case, instead of this:
SELECT ResourceName FROM StaffTracking where PersonnelResourceType = 'Supplier' order by PersonnelResourceType, ResourceName
你可以这样写：
you could write this:
SELECT ResourceName FROM StaffTracking where PersonnelResourceType = @restype order by PersonnelResourceType, ResourceName
并创建一个Sql-Parameter（在本例中名为restype ）它带有所需的值（在本例中为Supplier）并将其附加到参数集合中Sql-Command：
and create an Sql-Parameter (in this case named "restype") that carries the desired value (in this case "Supplier") and append it to the Parameter-Collection of the Sql-Command:
DbParameter p1 = cmd.CreateParameter();
p1.ParameterName = "restype";
p1.Value = "Supplier";
cmd.Parameters.Add(p1);
把它想象成SQL命令的某种变量。 SQL-Server接受您的查询字符串并用您分配给Sql-Parameter-Object的值替换所有出现的参数名称（以 @ 开头）同名。
这是最好的方式，因为：
- 如果您开发的应用程序存储来自陌生人的输入（例如网站访问者）进入您的数据库，您可以避免 SQL-Injection-Attacks [ ^ ]
- 它更易于维护，因为您的查询字符串很容易可读的
- 在字符串值的情况下，如果字符串包含引号，则可以避免潜在的SQL语法错误
编辑2 ：
最后两点并不直接适用于您之前的代码，但通常您会看到人们构建他们的查询字符串，如下所示： />
Think of it like some sort of variable for SQL-commands. SQL-Server takes your query-string and replaces all occurrences of those Parameter-Names (starting with an @) by the value that you assigned to the Sql-Parameter-Object with the same name.
It is "the best way" because:
- If you develop an application that stores input from strangers (e.g. website-visitors) into your database, you avoid the risk of SQL-Injection-Attacks[^]
- It is better maintainable because your query-string is easily readable
- In case of string-values you avoid potential SQL-syntax errors in case the string contains quotes
Edit 2:
The last two points don't directly apply to your previous code but very often you see people constructing their query-strings like this:
string query = "SELECT col1 FROM table1 WHERE col2 = '" + someVariable + "' AND col3 = '" someOtherVariable "'";
if (this.DataCenterLaborFileUpload.HasFile)
{
var extension = Path.GetExtension(DataCenterLaborFileUpload.FileName);
string currentName = "";
decimal currentHours = 0.00M;
decimal currentFTE = 0.00M;
string ResourceName = "";
if (extension == ".csv")
{
StreamReader csvreader = new StreamReader(DataCenterLaborFileUpload.FileContent);
DataTable dt = new DataTable();
dt.Columns.Add("txtName");
dt.Columns.Add("txtHours");
dt.Columns.Add("txtFTE");
while (!csvreader.EndOfStream)
{
DataRow dr = dt.NewRow();
var line = csvreader.ReadLine();
var values = line.Split(',');
if (values[0].Trim() != "Pers.No.")
{
using (DbConnection conn = new SqlConnection("SQLStaffingConn"))
{
conn.Open();
using (DbCommand cmd = conn.CreateCommand())
{
cmd.Connection = conn;
cmd.CommandText = "SELECT ResourceName FROM StaffTracking where PersonnelResourceType = @restype order by PersonnelResourceType, ResourceName";
DbParameter p1 = cmd.CreateParameter();
p1.ParameterName = "restype";
p1.Value = "Supplier";
cmd.Parameters.Add(p1);
using (DbDataReader row = cmd.ExecuteReader())
{
while (row.Read())
{
string col2 = row.GetString(1);
}
}
}
}
if (ResourceName == (values[1].Trim()))
{
if (values[1].Trim() == currentName)
{
currentHours = currentHours + Convert.ToDecimal(values[9].Trim());
}
else
{
if (currentName != "")
{
dr["txtName"] = currentName;
dr["txtHours"] = currentHours;
dr["txtFTE"] = currentFTE + Math.Round(currentHours / (weekdaysInMonth() * 8), 2);
dt.Rows.Add(dr);
dt.AcceptChanges();
}
currentHours = Convert.ToDecimal(values[9].Trim());
currentName = values[1].Trim();
}
}
}
}
DataRow drfinal2 = dt.NewRow();
drfinal2["txtName"] = currentName;
drfinal2["txtHours"] = currentHours;
drfinal2["txtFTE"] = currentFTE + currentHours / (weekdaysInMonth() * 8);
dt.Rows.Add(drfinal2);
dt.AcceptChanges();
gvDataCenterLabor.DataSource = dt;
}
gvDataCenterLabor.DataBind();
// Page.DataBind();
}
}
这篇关于如何循环sql server表来创建datagridview - sql表字段匹配csv字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
