clsdb.cs

通过配置数据库连接并选择数据库表,可自动生成数据库表的实体类,并且实体类中包含记录集合,可在类中对各记录集合进行修改后一次性提交保存,支持事务.

                                        {
                                            if (TableSave.Rows[j][i].ToString() == "")
                                            {
                                                strValues = strValues + "Null,";
                                            }
                                            else
                                            {
                                                strValues = strValues + TableSave.Rows[j][i].ToString() + ",";
                                            }
                                            break;
                                        }
                                }
                            }
                        }
                    }
                    strSql = strSql.Substring(0, strSql.Length - 1);
                    strValues = strValues.Substring(0, strValues.Length - 1) + ")";
                    strSql = strSql + strValues;
                    ExeSql(strSql);
                }
            }
            else
            {
                for (int j = 0; j < TableSave.Rows.Count; j++)
                {
                    strSql = "Update " + TableName + " Set ";
                    strWhere = " Where " + IDCol + "=";
                    for (int i = 0; i < TableSave.Columns.Count; i++)
                    {
                            //strValues=strValues+"hibernate_sequence.nextval,";
                            switch (TableSave.Columns[i].DataType.ToString())
                            {
                                case ("System.String"):
                                    {
                                        if (TableSave.Columns[i].ColumnName == IDCol)
                                        {
                                            strWhere = strWhere + "'" + TableSave.Rows[j][i].ToString() + "'";
                                        }
                                        else
                                        {
                                            strSql = strSql + TableSave.Columns[i].ColumnName + "='" + TableSave.Rows[j][i].ToString() + "',";
                                        }
                                        break;
                                    }
                                case ("System.DateTime"):
                                    {
                                        if (TableSave.Columns[i].ColumnName == IDCol)
                                        {
                                            strWhere = strWhere + "'" + TableSave.Rows[j][i].ToString() + "'";
                                        }
                                        else
                                        {
                                            if (TableSave.Rows[0][i] == DBNull.Value)
                                            {
                                                strSql = strSql + TableSave.Columns[i].ColumnName + "=Null,";
                                            }
                                            else
                                            {
                                                strSql = strSql + TableSave.Columns[i].ColumnName + "='" + TableSave.Rows[j][i].ToString() + "',";
                                            }
                                        }
                                        break;
                                    }
                                case ("System.Boolean"):
                                    {
                                        if (TableSave.Rows[j][i] != DBNull.Value)
                                        {
                                            int intVal;
                                            if (TableSave.Rows[j][i].ToString().ToLower() == "true")
                                            {
                                                intVal = 1;
                                            }
                                            else
                                            {
                                                intVal = 0;
                                            }
                                            strSql = strSql + TableSave.Columns[i].ColumnName + "=" + intVal.ToString() + ",";
                                        }
                                        else
                                        {
                                            strSql = strSql + TableSave.Columns[i].ColumnName + "=Null,";
                                        }
                                        break;
                                    }
                                default:
                                    {
                                        if (TableSave.Columns[i].ColumnName == IDCol)
                                        {
                                            strWhere = strWhere + TableSave.Rows[j][i].ToString();
                                        }
                                        else
                                        {
                                            if (TableSave.Rows[0][i].ToString() == "")
                                            {
                                                strSql = strSql + TableSave.Columns[i].ColumnName + "=Null,";
                                            }
                                            else
                                            {
                                                strSql = strSql + TableSave.Columns[i].ColumnName + "=" + TableSave.Rows[j][i].ToString() + ",";
                                            }
                                        }
                                        break;
                                    }
                            }
                    }
                    strSql = strSql.Substring(0, strSql.Length - 1);
                    strSql = strSql + strWhere;
                    ExeSql(strSql);
                }
            }
        }

        public static void SaveDataByTable(DataTable TableSave, string TableName, string IDCol)
        {
            //			OracleCommand ocmSave =new OracleCommand();
            string strSql = "";
            string strValues = "";
            string strWhere = "";
            List<SqlParameter> Paras = new List<SqlParameter>();
            SqlParameter para = null;
            for (int j = 0; j < TableSave.Rows.Count; j++)
            {
                if (TableSave.Rows[j][IDCol] == DBNull.Value)
                {
                    strSql = "Insert Into " + TableName + " (";
                    strValues = ") Values (";
                }
                else
                {
                    strSql = "Update " + TableName + " Set ";
                    strWhere = " Where " + IDCol + "=";
                }
                for (int i = 0; i < TableSave.Columns.Count; i++)
                {
                    if (TableSave.Columns[i].ColumnName == IDCol)
                    {
                        if (TableSave.Rows[j][IDCol] == DBNull.Value)
                        {

                        }
                        else
                        {
                            strWhere = strWhere + TableSave.Rows[j][i].ToString();
                        }
                    }
                    else
                    {
                        if (TableSave.Rows[j][IDCol] == DBNull.Value)
                        {
                            strSql = strSql + TableSave.Columns[i].ColumnName + ",";
                            strValues = strValues + "@" + TableSave.Columns[i].ColumnName + ",";
                        }
                        else
                        {
                            strSql = strSql + TableSave.Columns[i].ColumnName + "=" + "@" + TableSave.Columns[i].ColumnName + ",";
                        }
                        para = new SqlParameter();
                        if (TableSave.Rows[j][i] != DBNull.Value)
                        {
                            para.Value = TableSave.Rows[j][i];
                        }
                        else
                        {
                            para.Value = DBNull.Value;
                        }
                        para.ParameterName = "@" + TableSave.Columns[i].ColumnName;
                        switch (TableSave.Columns[i].DataType.ToString())
                        {
                            case ("System.String"):
                                {
                                    para.SqlDbType = SqlDbType.NVarChar;
                                    break;
                                }
                            case ("System.DateTime"):
                                {
                                    para.SqlDbType = SqlDbType.DateTime;
                                    break;
                                }
                            case ("System.Boolean"):
                                {
                                    para.SqlDbType = SqlDbType.Bit;
                                    break;
                                }
                            case ("System.Int32"):
                                {
                                    para.SqlDbType = SqlDbType.Int;
                                    break;
                                }
                            case ("System.Double"):
                                {
                                    para.SqlDbType = SqlDbType.Float;
                                    break;
                                }
                            case "System.Single":
                                {
                                    para.SqlDbType = SqlDbType.Real;
                                    break;
                                }
                            case "System.Byte[]":
                                {
                                    para.SqlDbType = SqlDbType.Image;
                                    if (TableSave.Rows[j][i] == DBNull.Value)
                                    {
                                        para.Size = 0;
                                    }
                                    else
                                    {
                                        para.Size = ((byte[])TableSave.Rows[j][i]).Length;
                                    }
                                    break;
                                }
                            default:
                                {
                                    MessageBox.Show("未预置的数据类型:" + TableSave.Columns[i].DataType.ToString());
                                    break;
                                }
                        }
                        Paras.Add(para);
                    }
                }
                if (TableSave.Rows[j][IDCol] == DBNull.Value)
                {
                    strSql = strSql.Substring(0, strSql.Length - 1);
                    strValues = strValues.Substring(0, strValues.Length - 1) + ")";
                    strSql = strSql + strValues;
                }
                else
                {
                    strSql = strSql.Substring(0, strSql.Length - 1) + strWhere;
                }
                ExeSql(strSql, Paras);
            }
        }
            


        public static void BeginTrans()
        {
            mscnData.Open();
            mstsTrans = mscnData.BeginTransaction();
            mscmData.Transaction = mstsTrans;
            mbolIsInTrans = true;
        }

        public static bool Commit()
        {
            try
            {
                mstsTrans.Commit();
                mbolIsInTrans = false;
                return true;
            }
            catch (Exception ex)
            {
                mstsTrans.Rollback();
                MessageBox.Show(ex.Message);
                return false;
            }
            finally
            {
                mscnData.Close();
            }
        }

        public static bool RollBack()
        {
            try
            {
                if (mbolIsInTrans)
                {
                    mstsTrans.Rollback();
                    mbolIsInTrans = false;
                    return true;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return false;
            }
            finally
            {
                mscnData.Close();
            }
            return false;
        }
    }
}