如何从sql server转换为boolean列 [英] How to convert to boolean column from sql server

问题描述

我有一个带有两个预定义列的datagridview，一个是textboxcolumn，另一个是复选框列，我试图将预定义列绑定到我的数据库，但是复选框列导致错误，即使数据库中的信息也是如此在1和0。所以我现在的问题是转换为我不知道的布尔值。请帮忙。

以下是我的代码..

  private   void  BindCommand（）
 {
  try  
 {
 sql =  从[user] .FormCommands中选择*其中UserID =' + txtAutoCode.Text +  '; 
 da =  new  SqlDataAdapter（sql，con）; 
 ds1 =  new  DataSet（）; 
 SqlCommandBuilder command =  new  SqlCommandBuilder（da）; 
 da.Fill（ds1，  [user] .FormCommands）; 
 
 CommandSource.DataSource = ds1.Tables [  [user] .FormCommands]; 
 dataGridView4.AutoGenerateColumns =  false ; 
 dataGridView4.DataSource = CommandSource; 
 
 MenuColum.DataPropertyName =  菜单; 
 MenuViewColumn.DataPropertyName =  状态; 
} 
  catch （SqlException exp）
 {
 MessageBox.Show（ 连接错误！ + exp.Message）; 
 
} 
  catch （System.Exception exp）
 {
 MessageBox.Show（ 错误！ + exp.Message）; 
} 
} 

我的尝试：

 私人  void  BindCommand （）
 {
  try  
 {
 sql =  从[user] .FormCommands中选择*其中UserID =' + txtAutoCode.Text +  < span class =code-string>'; 
 da =  new  SqlDataAdapter（sql，con）; 
 ds1 =  new  DataSet（）; 
 SqlCommandBuilder command =  new  SqlCommandBuilder（da）; 
 da.Fill（ds1，  [user] .FormCommands）; 
 
 CommandSource.DataSource = ds1.Tables [  [user] .FormCommands]; 
 dataGridView4.AutoGenerateColumns =  false ; 
 dataGridView4.DataSource = CommandSource; 
 
 MenuColum.DataPropertyName =  菜单; 
 MenuViewColumn.DataPropertyName =  状态; 
} 
  catch （SqlException exp）
 {
 MessageBox.Show（ 连接错误！ + exp.Message）; 
 
} 
  catch （System.Exception exp）
 {
 MessageBox.Show（ 错误！ + exp.Message）; 
} 
} 

解决方案

选项1：将数据库中列的类型更改为位（映射到布尔值） - 推荐（如果您可以自由更改数据库架构）。



选项2：在SELECT查询中将0和1转换为Bit（布尔值）：

  SELECT  
 CAST（[CheckBoxColumnName]  AS  位<​​/ span >） AS  CheckBoxColumnName，
 [OtherColumn1]，
 [OtherColumn2] 
  FROM  [TableName] 

选项3：DataAdapter填充DataSet / DataTable后，将新的DataColumn添加到DataTable中类型为布尔值，循环遍历DataRows，并将源列中的0和1转换为true，将false转换为新列。 （仅提及完整性，选项1最好，后跟选项2.）



除此之外：不要使用字符串用于构建SQL语句的串联：

- 您的应用程序容易受到SQL注入的影响。

- 这是SQL语法错误问题的根源。

- 它会严重影响查询性能。

- 对于较大的SQL语句，它变得不那么易读和可维护。

使用SQL参数代替：SqlParameter Class（System.Data.SqlClient） [ ^ ]

i have a datagridview with two predefined columns one is a textboxcolumn and the other is a checkbox column, i am trying to bind the predefined column to my database, but the checkbox column is causing an error even when the info in the database are in "1 and 0". so my problem now is converting to Boolean which i have no idea about. Please help.
below is my code..

private void BindCommand()
        {
            try
            {
                sql = "Select * from [user].FormCommands where UserID = '" + txtAutoCode.Text + "' ";
                da = new SqlDataAdapter(sql, con);
                ds1 = new DataSet();
                SqlCommandBuilder command = new SqlCommandBuilder(da);
                da.Fill(ds1, "[user].FormCommands");

                CommandSource.DataSource = ds1.Tables["[user].FormCommands"];
                dataGridView4.AutoGenerateColumns = false;
                dataGridView4.DataSource = CommandSource;

                MenuColum.DataPropertyName =  "Menu";
                MenuViewColumn.DataPropertyName = "Status";
            }
            catch (SqlException exp)
            {
                MessageBox.Show("Error in connection! " + exp.Message);

            }
            catch (System.Exception exp)
            {
                MessageBox.Show("Error! " + exp.Message);
            }
        }

What I have tried:

private void BindCommand()
        {
            try
            {
                sql = "Select * from [user].FormCommands where UserID = '" + txtAutoCode.Text + "' ";
                da = new SqlDataAdapter(sql, con);
                ds1 = new DataSet();
                SqlCommandBuilder command = new SqlCommandBuilder(da);
                da.Fill(ds1, "[user].FormCommands");

                CommandSource.DataSource = ds1.Tables["[user].FormCommands"];
                dataGridView4.AutoGenerateColumns = false;
                dataGridView4.DataSource = CommandSource;

                MenuColum.DataPropertyName =  "Menu";
                MenuViewColumn.DataPropertyName = "Status";
            }
            catch (SqlException exp)
            {
                MessageBox.Show("Error in connection! " + exp.Message);

            }
            catch (System.Exception exp)
            {
                MessageBox.Show("Error! " + exp.Message);
            }
        }

解决方案

Option 1: Change the type of the column in the database to Bit (which maps to Boolean) - Recommended (if you're free to change the database schema).

Option 2: Cast the "0"'s and "1"'s to Bit (Boolean) in your SELECT query:

SELECT
   CAST([CheckBoxColumnName] AS Bit) AS CheckBoxColumnName,
   [OtherColumn1],
   [OtherColumn2]
FROM [TableName]

Option 3: After the DataAdapter filled the DataSet/DataTable, add a new DataColumn to the DataTable with a type of Boolean, loop over the DataRows and convert the "0"'s and "1"'s from the source-column into true's and false's into the new column. (Only mentioned for completeness, option 1 is best, followed by option 2.)

Apart from that: Don't ever use string concatenation to build your SQL-statements:
- Your application becomes susceptible to SQL-injection.
- It's a source of SQL-syntax error problems.
- It can severely impact query performance.
- For larger SQL-statements it becomes less readable and maintainable.
Use SQL-parameters instead: SqlParameter Class (System.Data.SqlClient)[^]

这篇关于如何从sql server转换为boolean列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！