我正在使用应用程序(C#WPF,SQL(我想处理此程序的是,当我从SQL数据库中检索数据(产品,价格,数量(并在DataGrid中显示时,程序应自动更新该列,名为Total
我用于检索数据的代码如下所示
SqlCommand cmd = new SqlCommand("SELECT * From evid", conn);
DataTable dt = new DataTable("dtList");
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
dtg.ItemsSource = dt.DefaultView;
SqlDataAdapter adapt = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
adapt.Fill(ds);
conn.Close();
和我用来进行计算的代码显示出来:
int a = Convert.ToInt32(dtg.Columns[0]);
int b = Convert.ToInt32(dtg.Columns[1]);
int c = Convert.ToInt32(dtg.Columns[2]);
c = a * b;
我也希望从1到2更新列数量时。列总计应自行更新感谢大家
可能会更容易,而不是尝试在C#上计算新列数据,以在SQL查询中进行。首先,这对系统的压力要比计算多个变量的压力要小,其次,这将是更有效的代码。
c = a * b; //This assumed that those two columns are simply variables, which is not how DataTable rows work.
在您的SQL中,根据您提供的信息,我建议使用这些行:
SELECT
Product,
Price,
Quantity,
SUM(Price * Quantity) as Calculation
FROM
evid
GROUP BY
Product,
Price,
Quantity
此SQL查询将进入您的SQLCommand:
SqlCommand cmd = new SqlCommand("--Query above--", conn);
从那里您可以简单地将计算添加为新的数据表列。
更新向大家致歉,我误解了这个问题。您让我对解决这个问题的解决方案感到好奇。我提出了一些解决您遇到的问题的代码,并将如下解释:
首先,为了使它起作用,我必须更改填充表的方法,使用datagrid.fill(dataTable(无法使用,因为我必须使用自定义表达式作为数据源。
为了简单的可读性,我以编程为方案进行了处理,但是如果您愿意,这应该很容易转换为WPF。
代码:
SqlConnection sqlConn = new SqlConnection("server = ServerName; " + "Trusted_Connection = yes; " + "database = ReportPool; " + "connection timeout = 120");//Sql connection
SqlCommand sqlCmd = new SqlCommand(String.Format("SELECT {0} FROM {1}",//SQl command
"Product, Price, Quantity",
"ReportPool.dbo.TestTable"
), sqlConn);
DataTable dataTable = new DataTable();//Created a new DataTable
DataColumn dc = new DataColumn();//Made a new DataColumn to populate above DataTable
dc.DataType = System.Type.GetType("System.String");//Defined the DataType inside, this can be [[int]] if you want.
dc.ColumnName = "Product";//Gave it a name (important for the custom expression - can only be one word so use underscores if you need multiple words)
DataColumn dc2 = new DataColumn();
dc2.DataType = System.Type.GetType("System.Decimal");
dc2.ColumnName = "Price";
DataColumn dc3 = new DataColumn();
dc3.DataType = System.Type.GetType("System.Decimal");
dc3.ColumnName = "Quantity";
DataColumn dc4 = new DataColumn();
dc4.DataType = System.Type.GetType("System.Decimal");
dc4.ColumnName = "CalculatedColumn";
dc4.Expression = "Price * Quantity";//Multiplying the Price and Quantity DataColumns
dataTable.Columns.Add(dc);//Add them to the DataTable
dataTable.Columns.Add(dc2);
dataTable.Columns.Add(dc3);
dataTable.Columns.Add(dc4);
dataGridControl.ItemsSource = dataTable.DefaultView;//Set the DataGrid ItemSource to this new generated DataTable
sqlConn.Open();//Open the SQL connection
SqlDataReader reader = sqlCmd.ExecuteReader();//Create a SqlDataReader
while (reader.Read())//For each row that the SQL query returns do
{
DataRow dr = dataTable.NewRow();//Create new DataRow to populate the DataTable (which is currently binded to the DataGrid)
dr[0] = reader[0];//Fill DataTable column 0 current row (Product) with reader[0] (Product from sql)
dr[1] = reader[1];
dr[2] = reader[2];
dataTable.Rows.Add(dr);//Add the new created DataRow to the DataTable
}
希望您现在可以解决您一直在遇到的问题,如果您需要任何帮助解释此代码或需要更多帮助的帮助,请随时发表评论。
对较晚更新表示歉意。