Beispiel #1
0
        public bool AddUser(string username, string password)
        {
            bool            isOk = false;
            OleDbConnection conn = DBHelper.getConn(); //得到连接对象

            conn.Open();
            try
            {
                String                   strCommand      = "INSERT INTO TB_USER (username,pwd) VALUES (?,?)";
                OleDbCommand             command         = new OleDbCommand(strCommand, conn);
                OleDbParameterCollection paramCollection = command.Parameters;
                OleDbParameter           param1          = paramCollection.Add("username", OleDbType.VarChar);
                param1.Value = username;
                OleDbParameter param2 = paramCollection.Add("pwd", OleDbType.VarChar);
                param2.Value = password;

                command.ExecuteNonQuery();
                conn.Close();
                isOk = true;
            }
            catch (Exception e)
            {
                conn.Close();
                throw new Exception(e.Message);
            }
            return(isOk);
        }
Beispiel #2
0
        public bool ChangePwd(String username, String password)
        {
            bool            isOk = false;
            OleDbConnection conn = DBHelper.getConn(); //得到连接对象

            conn.Open();
            try
            {
                String                   strCommand      = "UPDATE TB_USER SET pwd = ? where username = ?";
                OleDbCommand             command         = new OleDbCommand(strCommand, conn);
                OleDbParameterCollection paramCollection = command.Parameters;
                OleDbParameter           param1          = paramCollection.Add("pwd", OleDbType.VarChar);
                param1.Value = password;
                OleDbParameter param2 = paramCollection.Add("username", OleDbType.VarChar);
                param2.Value = username;

                command.ExecuteNonQuery();
                conn.Close();
                isOk = true;
            }
            catch (Exception e)
            {
                conn.Close();
                throw new Exception(e.Message);
            }
            return(isOk);
        }
Beispiel #3
0
        protected override void setQueryParameters(OleDbParameterCollection parameters, QueryCondition queryCondition)
        {
            ApartmentQueryCondition qc = (ApartmentQueryCondition)queryCondition;

            if (!string.IsNullOrWhiteSpace(qc.unit_number))
            {
                parameters.Add("@unit_number", OleDbType.VarWChar, 255).Value = "%" + qc.unit_number + "%";
            }
            if (!string.IsNullOrWhiteSpace(qc.floorplan_id))
            {
                parameters.Add("@floorplan_id", OleDbType.VarWChar, 255).Value = qc.floorplan_id;
            }
            if (!string.IsNullOrWhiteSpace(qc.building_id))
            {
                parameters.Add("@building_id", OleDbType.VarWChar, 255).Value = qc.building_id;
            }
            else if (!string.IsNullOrWhiteSpace(qc.stage_id))
            {
                parameters.Add("@stage_id", OleDbType.VarWChar, 255).Value = qc.stage_id;
            }
            else if (!string.IsNullOrWhiteSpace(qc.project_id))
            {
                parameters.Add("@project_id", OleDbType.VarWChar, 255).Value = qc.project_id;
            }
        }
Beispiel #4
0
        public DataSet FindAllInstance(int systemid, int instanceid)
        {
            OleDbConnection          conn            = DBHelper.getConn(); //得到连接对象
            String                   strCommand      = "SELECT a.indexid,a.pid,a.indexname,b.indexvalue FROM TB_INDEX a LEFT JOIN TB_INDEX_INSTANCE b ON a.systemid = b.systemid AND a.indexid = b.indexid WHERE a.systemid = ? AND b.instanceid =?";
            OleDbCommand             command         = new OleDbCommand(strCommand, conn);
            OleDbParameterCollection paramCollection = command.Parameters;
            OleDbParameter           param1          = paramCollection.Add("systemid", OleDbType.Integer);

            param1.Value = systemid;
            OleDbParameter param2 = paramCollection.Add("instanceid", OleDbType.Integer);

            param2.Value = instanceid;
            conn.Open();

            try
            {
                OleDbDataAdapter adapter = new OleDbDataAdapter(command);

                DataSet sourceDataSet = new DataSet();
                adapter.Fill(sourceDataSet);
                conn.Close();
                return(sourceDataSet);
            }
            catch (Exception e)
            {
                conn.Close();
                throw new Exception(e.Message);
            }
        }
        public VfpParameter Add(VfpParameter value)
        {
            _vfpParamCollection.Add(value);
            _oleDbParameterCollection.Add(value.OleDbParameter);

            return(value);
        }
Beispiel #6
0
        private void simpleButton1_Click(object sender, EventArgs e)
        {
            if (textEdit1.Text.Trim() == "")
            {
                MessageBox.Show("请输入对应名称", "错误", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return;
            }

            //添加进数据库
            OleDbConnection conn = DBHelper.getConn(); //得到连接对象

            conn.Open();
            int indexid1;

            try
            {
                int tmp = int.Parse(MainForm.pCurrentWin.treeList1.FocusedNode.GetValue("indexid").ToString());
                //根据指标体编号
                String          strCommand1 = "SELECT * FROM TB_INDEX t WHERE t.indexid = " + tmp;
                OleDbCommand    myCommand1  = new OleDbCommand(strCommand1, conn);
                OleDbDataReader reader;
                reader = myCommand1.ExecuteReader(); //执行command并得到相应的DataReader
                reader.Read();
                //int indexid = int.Parse(reader["indexid"].ToString());//指标编号
                int indexsys = int.Parse(reader["systemid"].ToString());//指标体系编号
                reader.Close();
                //加入指标表
                String                   strCommand      = "INSERT INTO TB_INDEX (pid,indexname,systemid) VALUES (?,?,?)";
                OleDbCommand             command         = new OleDbCommand(strCommand, conn);
                OleDbParameterCollection paramCollection = command.Parameters;
                OleDbParameter           param1          = paramCollection.Add("pid", OleDbType.VarChar);
                param1.Value = tmp;
                OleDbParameter param2 = paramCollection.Add("indexname", OleDbType.VarChar);
                param2.Value = textEdit1.Text;
                OleDbParameter param3 = paramCollection.Add("systemid", OleDbType.VarChar);
                param3.Value = indexsys;
                command.ExecuteNonQuery();
                //根据指标体系名查找指标体系编号
                string       strCommand2 = "select max(indexid) from TB_INDEX";
                OleDbCommand myCommand2  = new OleDbCommand(strCommand2, conn);
                object       o           = myCommand2.ExecuteScalar();
                indexid1 = int.Parse(o.ToString());
            }
            catch (System.Exception ex)
            {
                conn.Close();
                throw new Exception("[ERROR] 数据库操作出现异常:" + ex.Message);
            }
            MainForm.pCurrentWin.treeList1.BeginUnboundLoad();
            MainForm.pCurrentWin.treeList1.AppendNode(new object[] { indexid1, MainForm.pCurrentWin.nodeid, textEdit1.Text }, MainForm.pCurrentWin.nodeid);
            MainForm.pCurrentWin.treeList1.EndUnboundLoad();
            MainForm.pCurrentWin.SetImageIndex(MainForm.pCurrentWin.treeList1, null, 20, 21);
            conn.Close();
            this.Close();
        }
Beispiel #7
0
        private OleDbCommand method_5(string string_1, OleDbConnection oleDbConnection_0)
        {
            string                   str          = string.Concat("INSERT INTO [", string_1, "] ([Name], [Category], [Object])  Values (?, ?, ?)");
            OleDbCommand             oleDbCommand = new OleDbCommand(str, oleDbConnection_0);
            OleDbParameterCollection parameters   = oleDbCommand.Parameters;

            parameters.Add("Name", OleDbType.Char);
            parameters.Add("Category", OleDbType.Char);
            parameters.Add("Object", OleDbType.Binary);
            return(oleDbCommand);
        }
Beispiel #8
0
        private OleDbCommand method_6(string string_1, OleDbConnection oleDbConnection_0)
        {
            string str = string.Concat("Update [", string_1,
                                       "] Set [Name] = ?, [Category] = ?, [Object] = ? Where [ID] = ?");
            OleDbCommand             oleDbCommand = new OleDbCommand(str, oleDbConnection_0);
            OleDbParameterCollection parameters   = oleDbCommand.Parameters;

            parameters.Add("Name", OleDbType.Char);
            parameters.Add("Category", OleDbType.Char);
            parameters.Add("Object", OleDbType.Binary);
            parameters.Add("ID", OleDbType.Integer);
            return(oleDbCommand);
        }
Beispiel #9
0
        protected override void setQueryParameters(OleDbParameterCollection parameters, QueryCondition queryCondition)
        {
            BuildingQueryCondition qc = (BuildingQueryCondition)queryCondition;

            if (!string.IsNullOrWhiteSpace(qc.stage_id))
            {
                parameters.Add("@stage_id", OleDbType.VarWChar, 255).Value = qc.stage_id;
            }
            else if (!string.IsNullOrWhiteSpace(qc.project_id))
            {
                parameters.Add("@project_id", OleDbType.VarWChar, 255).Value = qc.project_id;
            }
        }
            public virtual OleDbCommand CreateInsertCommand()
            {
                const string insertCmd =
                    "INSERT INTO sampleLog " +
                    "(logTime, event) " +
                    "VALUES (?, ?)";

                OleDbCommand             cmd = new OleDbCommand(insertCmd);
                OleDbParameterCollection pc  = cmd.Parameters;

                pc.Add("logTime", OleDbType.DBTimeStamp, 0, "logTime");
                pc.Add("event", OleDbType.Char, 32, "event");
                return(cmd);
            }
Beispiel #11
0
        public bool DeleteUser(string username)
        {
            bool            isOk = false;
            OleDbConnection conn = DBHelper.getConn(); //得到连接对象

            conn.Open();
            try
            {
                String                   strCommand      = "DELETE FROM TB_USER where username = ?";
                OleDbCommand             command         = new OleDbCommand(strCommand, conn);
                OleDbParameterCollection paramCollection = command.Parameters;
                OleDbParameter           param1          = paramCollection.Add("username", OleDbType.VarChar);
                param1.Value = username;

                command.ExecuteNonQuery();
                conn.Close();
                isOk = true;
            }
            catch (Exception e)
            {
                conn.Close();
                throw new Exception(e.Message);
            }
            return(isOk);
        }
Beispiel #12
0
 /// <summary>
 /// Builds an OleDbParametersCollection for an INSERT command, according to the DbTypeParameters in this collection.
 /// </summary>
 /// <param name="a_oParams">The OleDbParameterCollection to be filled.</param>
 private void AddInsertCommandParameters(OleDbParameterCollection a_oParams)
 {
     foreach (DbTypeParameter l_oCurrent in this)
     {
         a_oParams.Add(l_oCurrent.ParameterName, l_oCurrent.Value);
     }
 }
Beispiel #13
0
    // <Snippet1>
    public void CreateParamCollection(OleDbCommand command)
    {
        OleDbParameterCollection paramCollection = command.Parameters;

        paramCollection.Add("@CategoryName", OleDbType.Char);
        paramCollection.Add("@Description", OleDbType.Char);
        paramCollection.Add("@Picture", OleDbType.Binary);
        string parameterNames = "";

        for (int i = 0; i < paramCollection.Count; i++)
        {
            parameterNames += paramCollection[i].ToString() + "\n";
        }
        Console.WriteLine(parameterNames);
        paramCollection.Clear();
    }
        /// <summary>
        /// Creates insert command.
        /// </summary>
        /// <param name="tableName">Table name.</param>
        /// <param name="tableDescription">Table Description.</param>
        /// <param name="fields">Fields to export.</param>
        /// <returns>Insert command.</returns>
        private OleDbCommand _CreateInsertCommand(string tableName,
                                                  TableDescription tableDescription,
                                                  ICollection <string> fields)
        {
            var command = new OleDbCommand();

            var valueNames = new StringBuilder();
            var values     = new StringBuilder();
            OleDbParameterCollection parameters = command.Parameters;

            foreach (string field in fields)
            {
                if (!string.IsNullOrEmpty(valueNames.ToString()))
                {
                    valueNames.Append(SQL_PARAM_SEPARATOR);
                    values.Append(SQL_PARAM_SEPARATOR);
                }

                FieldInfo info = tableDescription.GetFieldInfo(field);
                Debug.Assert(null != info);

                string realName = _FormatFieldName(field);
                valueNames.Append(realName);
                values.Append(SQL_VALUE_SYMBOL);

                parameters.Add(field, info.Type, info.Size, field);
            }

            command.CommandText = string.Format(SQL_INSERT_COMMAND_FORMAT, tableName,
                                                valueNames.ToString(), values.ToString());
            return(command);
        }
Beispiel #15
0
 protected override void setParameters(OleDbParameterCollection parameters, Media entity)
 {
     parameters.Add("@project_id", OleDbType.VarWChar, 255).Value     = entity.project_id;
     parameters.Add("@name", OleDbType.VarWChar, 255).Value           = entity.name;
     parameters.Add("@small_url", OleDbType.VarWChar, 255).Value      = entity.small_url;
     parameters.Add("@medium_url", OleDbType.VarWChar, 255).Value     = entity.medium_url;
     parameters.Add("@large_url", OleDbType.VarWChar, 255).Value      = entity.large_url;
     parameters.Add("@alt", OleDbType.VarWChar, 255).Value            = entity.alt;
     parameters.Add("@media_type", OleDbType.UnsignedTinyInt).Value   = entity.media_type;
     parameters.Add("@content_type", OleDbType.UnsignedTinyInt).Value = entity.content_type;
     parameters.Add("@removed", OleDbType.UnsignedTinyInt).Value      = Utils.boolToByte(entity.removed);
     if (!string.IsNullOrWhiteSpace(entity.id))
     {
         parameters.Add("@id", OleDbType.VarWChar, 255).Value = entity.id;
     }
 }
Beispiel #16
0
        public List <IndexInstance> FindDistinctIndexInstance(int systemid)
        {
            OleDbConnection          conn            = DBHelper.getConn(); //得到连接对象
            String                   strCommand      = "SELECT DISTINCT(instanceid),instancename FROM TB_INDEX_INSTANCE where systemid = ?";
            OleDbCommand             command         = new OleDbCommand(strCommand, conn);
            OleDbParameterCollection paramCollection = command.Parameters;
            OleDbParameter           param1          = paramCollection.Add("systemid", OleDbType.Integer);

            param1.Value = systemid;
            conn.Open();

            OleDbDataReader reader;

            reader = command.ExecuteReader(); //执行command并得到相应的DataReader
            List <IndexInstance> list = new List <IndexInstance>();

            while (reader.Read())
            {
                IndexInstance indexInstance = new IndexInstance();
                //indexInstance.Indexid = int.Parse(reader["indexid"].ToString());
                indexInstance.Instanceid = int.Parse(reader["instanceid"].ToString());
                //indexInstance.Indexvalue = double.Parse(reader["indexvalue"].ToString());
                indexInstance.Instancename = reader["instancename"].ToString();
                list.Add(indexInstance);
            }
            reader.Close();
            conn.Close();
            return(list);
        }
            public OleDbCommand CreateInsertCommand()
            {
                const string insertCmd =
                    "INSERT INTO eventLog " +
                    "(eventTime, system, category, message) " +
                    "VALUES (?, ?, ?, ?)";

                OleDbCommand             cmd = new OleDbCommand(insertCmd);
                OleDbParameterCollection pc  = cmd.Parameters;

                pc.Add("eventTime", OleDbType.DBTimeStamp, 0, "eventTime");
                pc.Add("system", OleDbType.Char, 16, "system");
                pc.Add("category", OleDbType.Char, 16, "category");
                pc.Add("message", OleDbType.VarChar, 512, "message");
                return(cmd);
            }
Beispiel #18
0
        public DataSet FindAllIndex(int systemid)
        {
            OleDbConnection          conn            = DBHelper.getConn(); //得到连接对象
            String                   strCommand      = "SELECT * FROM TB_INDEX where systemid = ?";
            OleDbCommand             command         = new OleDbCommand(strCommand, conn);
            OleDbParameterCollection paramCollection = command.Parameters;
            OleDbParameter           param1          = paramCollection.Add("systemid", OleDbType.Integer);

            param1.Value = systemid;
            conn.Open();

            try
            {
                OleDbDataAdapter adapter = new OleDbDataAdapter(command);

                DataSet sourceDataSet = new DataSet();
                adapter.Fill(sourceDataSet);
                conn.Close();
                return(sourceDataSet);
            }
            catch (Exception e)
            {
                conn.Close();
                throw new Exception(e.Message);
            }
        }
Beispiel #19
0
 // <Snippet1>
 public void CreateParameters(OleDbConnection connection)
 {
     OleDbCommand command = new OleDbCommand(
         "SELECT * FROM Customers WHERE CustomerID = ?", connection);
     OleDbParameterCollection paramCollection = command.Parameters;
     OleDbParameter           parameter       = paramCollection.Add(
         "CustomerID", OleDbType.VarChar, 5, "CustomerID");
 }
Beispiel #20
0
 protected override void setParameters(OleDbParameterCollection parameters, Building entity)
 {
     parameters.Add("@stage_id", OleDbType.VarWChar, 255).Value      = entity.stage_id;
     parameters.Add("@building_num", OleDbType.VarWChar, 255).Value  = entity.building_num;
     parameters.Add("@description", OleDbType.LongVarWChar).Value    = entity.description;
     parameters.Add("@finish_date", OleDbType.Date).Value            = entity.finish_date;
     parameters.Add("@floor_count", OleDbType.SmallInt).Value        = entity.floor_count;
     parameters.Add("@orientation", OleDbType.SmallInt).Value        = entity.orientation;
     parameters.Add("@ready_house", OleDbType.UnsignedTinyInt).Value = Utils.boolToByte(entity.ready_house);
     parameters.Add("@removed", OleDbType.UnsignedTinyInt).Value     = Utils.boolToByte(entity.removed);
     if (!string.IsNullOrWhiteSpace(entity.id))
     {
         parameters.Add("@id", OleDbType.VarWChar, 255).Value = entity.id;
     }
 }
        private int GetCountOfSharedState(string path)
        {
            string getSharedStateCount =
                "SELECT COUNT(*)" +
                " FROM aspnet_PagePersonalizationAllUsers AllUsers, aspnet_Paths Paths" +
                " WHERE AllUsers.PathId = Paths.PathId AND Paths.ApplicationId = @ApplicationId";

            AccessConnectionHolder connectionHolder = null;
            OleDbConnection        connection       = null;
            int count = 0;


            try
            {
                try
                {
                    connectionHolder = GetConnectionHolder();
                    connection       = connectionHolder.Connection;

                    OleDbCommand command = new OleDbCommand();
                    command.Connection = connection;
                    OleDbParameterCollection parameters = command.Parameters;

                    int appId = GetApplicationID(connectionHolder);
                    parameters.AddWithValue("ApplicationId", appId);

                    if (path != null)
                    {
                        getSharedStateCount += " AND Paths.Path LIKE '%'+@Path+'%'";
                        OleDbParameter parameter = parameters.Add("Path", OleDbType.WChar);
                        parameter.Value = path;
                    }
                    command.CommandText = getSharedStateCount;

                    object result = command.ExecuteScalar();
                    if ((result != null) && (result is Int32))
                    {
                        count = (Int32)result;
                    }
                }
                finally
                {
                    if (connectionHolder != null)
                    {
                        connectionHolder.Close();
                        connectionHolder = null;
                    }
                }
            }
            catch
            {
                throw;
            }

            return(count);
        }
Beispiel #22
0
 void SetParameterValue(OleDbParameterCollection Params, object value, OleDbType type)
 {
     if (value != null)
     {
         Params.Add("?", type).Value = value;
     }
     else
     {
         Params.AddWithValue("?", DBNull.Value);
     }
 }
Beispiel #23
0
        /// <summary>
        /// Logs an error to the database.
        /// </summary>
        /// <remarks>
        /// Use the stored procedure called by this implementation to set a
        /// policy on how long errors are kept in the log. The default
        /// implementation stores all errors for an indefinite time.
        /// </remarks>

        public override string Log(Error error)
        {
            if (error == null)
            {
                throw new ArgumentNullException("error");
            }

            string errorXml = ErrorXml.EncodeString(error);

            using (OleDbConnection connection = new OleDbConnection(this.ConnectionString))
                using (OleDbCommand command = connection.CreateCommand())
                {
                    connection.Open();

                    command.CommandType = CommandType.Text;
                    command.CommandText = @"INSERT INTO ELMAH_Error
                                            (Application, Host, Type, Source, 
                                            Message, UserName, StatusCode, TimeUtc, AllXml)
                                        VALUES
                                            (@Application, @Host, @Type, @Source, 
                                            @Message, @UserName, @StatusCode, @TimeUtc, @AllXml)";
                    command.CommandType = CommandType.Text;

                    OleDbParameterCollection parameters = command.Parameters;

                    parameters.Add("@Application", OleDbType.VarChar, _maxAppNameLength).Value = ApplicationName;
                    parameters.Add("@Host", OleDbType.VarChar, 30).Value   = error.HostName;
                    parameters.Add("@Type", OleDbType.VarChar, 100).Value  = error.Type;
                    parameters.Add("@Source", OleDbType.VarChar, 60).Value = error.Source;
                    parameters.Add("@Message", OleDbType.LongVarChar, error.Message.Length).Value = error.Message;
                    parameters.Add("@User", OleDbType.VarChar, 50).Value   = error.User;
                    parameters.Add("@StatusCode", OleDbType.Integer).Value = error.StatusCode;
                    parameters.Add("@TimeUtc", OleDbType.Date).Value       = error.Time.ToUniversalTime();
                    parameters.Add("@AllXml", OleDbType.LongVarChar, errorXml.Length).Value = errorXml;

                    command.ExecuteNonQuery();

                    using (OleDbCommand identityCommand = connection.CreateCommand())
                    {
                        identityCommand.CommandType = CommandType.Text;
                        identityCommand.CommandText = "SELECT @@IDENTITY";

                        return(Convert.ToString(identityCommand.ExecuteScalar(), CultureInfo.InvariantCulture));
                    }
                }
        }
Beispiel #24
0
        /// <summary>
        /// Returns the specified error from the database, or null
        /// if it does not exist.
        /// </summary>

        public override ErrorLogEntry GetError(string id)
        {
            if (id == null)
            {
                throw new ArgumentNullException("id");
            }

            if (id.Length == 0)
            {
                throw new ArgumentException(null, "id");
            }

            int errorId;

            try
            {
                errorId = int.Parse(id, CultureInfo.InvariantCulture);
            }
            catch (FormatException e)
            {
                throw new ArgumentException(e.Message, "id", e);
            }
            catch (OverflowException e)
            {
                throw new ArgumentException(e.Message, "id", e);
            }

            string errorXml;

            using (OleDbConnection connection = new OleDbConnection(this.ConnectionString))
                using (OleDbCommand command = connection.CreateCommand())
                {
                    command.CommandText = @"SELECT   AllXml
                                        FROM     ELMAH_Error
                                        WHERE    ErrorId = @ErrorId";
                    command.CommandType = CommandType.Text;

                    OleDbParameterCollection parameters = command.Parameters;
                    parameters.Add("@ErrorId", OleDbType.Integer).Value = errorId;

                    connection.Open();
                    errorXml = (string)command.ExecuteScalar();
                }

            if (errorXml == null)
            {
                return(null);
            }

            Error error = ErrorXml.DecodeString(errorXml);

            return(new ErrorLogEntry(this, id, error));
        }
Beispiel #25
0
        protected override void setQueryParameters(OleDbParameterCollection parameters, QueryCondition queryCondition)
        {
            MediaQueryCondition qc = (MediaQueryCondition)queryCondition;

            if (!string.IsNullOrWhiteSpace(qc.url))
            {
                parameters.Add("@url", OleDbType.VarWChar, 255).Value = "%" + qc.url + "%";
            }
            if (!string.IsNullOrWhiteSpace(qc.floorplan_id))
            {
                parameters.Add("@floorplan_id", OleDbType.VarWChar, 255).Value = qc.floorplan_id;
            }
            else if (!string.IsNullOrWhiteSpace(qc.project_id))
            {
                if (!qc.project_id.Equals("-1"))
                {
                    parameters.Add("@project_id", OleDbType.VarWChar, 255).Value = qc.project_id;
                }
            }
            if (!string.IsNullOrWhiteSpace(qc.overview_project_id))
            {
                parameters.Add("@overview_project_id", OleDbType.VarWChar, 255).Value = qc.overview_project_id;
            }
            if (qc.media_type != 222)
            {
                parameters.Add("@media_type", OleDbType.UnsignedTinyInt).Value = qc.media_type;
            }
            if (qc.content_type != 222)
            {
                parameters.Add("@content_type", OleDbType.UnsignedTinyInt).Value = qc.content_type;
            }
        }
Beispiel #26
0
 protected override void setParameters(OleDbParameterCollection parameters, Stage entity)
 {
     parameters.Add("@project_id", OleDbType.VarWChar, 255).Value = entity.project_id;
     parameters.Add("@name", OleDbType.VarWChar, 255).Value       = entity.name;
     parameters.Add("@description", OleDbType.LongVarWChar).Value = entity.description;
     parameters.Add("@finish_date", OleDbType.Date).Value         = entity.finish_date;
     parameters.Add("@removed", OleDbType.UnsignedTinyInt).Value  = Utils.boolToByte(entity.removed);
     if (!string.IsNullOrWhiteSpace(entity.id))
     {
         parameters.Add("@id", OleDbType.VarWChar, 255).Value = entity.id;
     }
 }
        // ----------------------------------------------------------------
        // ----------------------------------------------------------------

        private OleDbCommand GetOrderLinesCommand(String strStoredProcName, ref OleDbConnection objConnect, String strCommandType)
        {
            // creates a new Command object and adds parameters, specifying the
            // column name and version from which values will be taken

            OleDbCommand objCommand = new OleDbCommand(strStoredProcName, objConnect);

            objCommand.CommandType = CommandType.StoredProcedure;

            OleDbParameterCollection colParams = objCommand.Parameters;
            OleDbParameter           objParam;

            objParam = colParams.Add("OrderID", OleDbType.Integer, 4, "OrderID");
            objParam.SourceVersion = DataRowVersion.Original;
            objParam = colParams.Add("ProductID", OleDbType.Integer, 4, "ProductID");
            objParam.SourceVersion = DataRowVersion.Original;

            if (strCommandType != "INSERT")
            {
                // add "Original" parameter values for Delete and Update actions
                objParam = colParams.Add("OldUnitPrice", OleDbType.Decimal, 4, "UnitPrice");
                objParam.SourceVersion = DataRowVersion.Original;
                objParam = colParams.Add("OldQuantity", OleDbType.SmallInt, 2, "Quantity");
                objParam.SourceVersion = DataRowVersion.Original;
                objParam = colParams.Add("OldDiscount", OleDbType.Double, 2, "Discount");
                objParam.SourceVersion = DataRowVersion.Original;
            }

            if (strCommandType != "DELETE")
            {
                // add "Current" parameter values for Update and Insert actions
                objParam = colParams.Add("NewUnitPrice", OleDbType.Decimal, 4, "UnitPrice");
                objParam.SourceVersion = DataRowVersion.Current;
                objParam = colParams.Add("NewQuantity", OleDbType.SmallInt, 2, "Quantity");
                objParam.SourceVersion = DataRowVersion.Current;
                objParam = colParams.Add("NewDiscount", OleDbType.Double, 2, "Discount");
                objParam.SourceVersion = DataRowVersion.Current;
            }
            return(objCommand);
        }
        protected void ApplyParameters()
        {
            if (_paramList == null)
            {
                return;
            }
            if (_dataAdpater == null)
            {
                return;
            }
            OleDbParameterCollection CommandParams = _dataAdpater.SelectCommand.Parameters;

            CommandParams.Clear();
            int i;

            for (i = 0; i < _paramList.Count; i++)
            {
                CommandParams.Add(new OleDbParameter("@P" + i, _paramList[i]));
            }
        }
Beispiel #29
0
        public static bool DataTableExportToExcel(DataTable dt, string filename, String tabName, ref String reMsg)
        {
            if (dt.Rows.Count <= 0)
            {
                reMsg = "目前无数据不需要导出";
                return(false);
            }
            int           rows       = dt.Rows.Count;
            int           cols       = dt.Columns.Count;
            StringBuilder sb         = new StringBuilder();
            string        connString = String.Empty;

            connString = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0;", filename);
            sb.Append("CREATE TABLE " + tabName + " (");
            String colName       = String.Empty;
            String colNames      = String.Empty;
            String colNamePramas = String.Empty;
            String colType       = String.Empty;

            for (int i = 0; i < cols; i++)
            {
                colName = dt.Columns[i].ColumnName.ToString();
                colType = dt.Columns[i].DataType.ToString();
                colType = NetDataTypeToDataBaseType(colType);
                if (i == 0)
                {
                    sb.Append(colName + "  " + colType);
                    colNames      += colName;
                    colNamePramas += "@" + colName;
                }
                else
                {
                    sb.Append(", " + colName + "  " + colType);
                    colNames      += "," + colName;
                    colNamePramas += ",@" + colName;
                }
            }
            sb.Append(" )");
            if (colNames == String.Empty)
            {
                reMsg = "数据集的列数必须大于0";
                return(false);
            }
            using (OleDbConnection objConn = new OleDbConnection(connString))
            {
                OleDbCommand objCmd = new OleDbCommand();
                objCmd.Connection  = objConn;
                objCmd.CommandText = sb.ToString();
                try
                {
                    objConn.Open();
                    objCmd.ExecuteNonQuery();
                }
                catch (Exception e)
                {
                    reMsg = "在Excel中创建表失败,错误信息:" + e.Message;
                    return(false);
                }
                sb.Remove(0, sb.Length);
                sb.Append(" insert into " + tabName + " (" + colNames + ") values(" + colNamePramas + " )");
                objCmd.CommandText = sb.ToString();
                OleDbParameterCollection param = objCmd.Parameters;
                for (int i = 0; i < cols; i++)
                {
                    colType = dt.Columns[i].DataType.ToString();
                    colName = dt.Columns[i].ColumnName.ToString();
                    if (colType == "System.String")
                    {
                        param.Add(new OleDbParameter("@" + colName, OleDbType.VarChar));
                    }
                    else if (colType == "System.DateTime")
                    {
                        param.Add(new OleDbParameter("@" + colName, OleDbType.Date));
                    }
                    else if (colType == "System.Boolean")
                    {
                        param.Add(new OleDbParameter("@" + colName, OleDbType.Boolean));
                    }
                    else if (colType == "System.Decimal")
                    {
                        param.Add(new OleDbParameter("@" + colName, OleDbType.Decimal));
                    }
                    else if (colType == "System.Double")
                    {
                        param.Add(new OleDbParameter("@" + colName, OleDbType.Double));
                    }
                    else if (colType == "System.Single")
                    {
                        param.Add(new OleDbParameter("@" + colName, OleDbType.Single));
                    }
                    else if (colType == "System.Single")
                    {
                        param.Add(new OleDbParameter("@" + colName, OleDbType.Single));
                    }
                    else
                    {
                        param.Add(new OleDbParameter("@" + colName, OleDbType.Integer));
                    }
                }
                foreach (DataRow row in dt.Rows)
                {
                    for (int i = 0; i < param.Count; i++)
                    {
                        param[i].Value = row[i];
                    }

                    objCmd.ExecuteNonQuery();
                }
            }
            reMsg = "数据成功导出";
            return(true);
        }
Beispiel #30
0
        /// <summary>
        /// 写数据到Excel。
        /// </summary>
        /// <param name="dtSource">数据源</param>
        /// <param name="filePath">Excel导出路径</param>
        /// <param name="excelVersion">excel版本,为ExcelVersion类型</param>
        /// <param name="pHDR">第一行是否标题,为HDRType类型</param>
        public static void SetData(DataTable dtSource, string filePath, string excelVersion, string pHDR)
        {
            //数据源为空
            if (dtSource == null)
            {
                throw new Exception("无数据可导");
            }
            //保存路径为空
            if (string.IsNullOrEmpty(filePath))
            {
                throw new Exception("未设置Excel保存路径");
            }
            //删除文件
            if (File.Exists(filePath))
            {
                File.Delete(filePath);
            }

            //链接字符串
            string connectionString = string.Format(m_excelVersion[excelVersion], filePath, pHDR);

            // 连接Excel
            using (OleDbConnection Connection = new OleDbConnection(connectionString))
            {
                Connection.Open();

                //导入数据
                using (OleDbCommand command = new OleDbCommand())
                {
                    command.Connection = Connection;

                    //构建列  格式如:Name VarChar,CreateDate Date
                    string colList = CreateExcelColums(dtSource);

                    //构建插入SQL语句
                    //格式如 "INSERT INTO TABLE [tablename](col1,col2,col3) VALUES(@col1,@col2,@col3)";

                    StringBuilder sbColumNames = new StringBuilder();
                    StringBuilder sbColumValues = new StringBuilder();

                    foreach (DataColumn dc in dtSource.Columns)
                    {
                        sbColumNames.AppendFormat(",[{0}]", dc.ColumnName);
                        sbColumValues.AppendFormat(",@{0}", dc.ColumnName);
                    }

                    //去掉多余的逗号
                    sbColumNames.Remove(0, 1);
                    sbColumValues.Remove(0, 1);

                    //当数据量超过每页最大数据量时,自动分页
                    int totalRows = dtSource.Rows.Count;//总数据量
                    int pageIndex = 0;

                    //开始插入数据  do...while循环是为了处理分页逻辑
                    do
                    {
                        //计算此轮插入的数据量
                        int insertRows = m_maxSheelSize - 1;

                        //如果总数据量没有达到容量
                        if (totalRows < insertRows)
                        {
                            insertRows = totalRows;
                        }

                        string tableName = dtSource.TableName + pageIndex;
                        if (pageIndex == 0)
                        {
                            tableName = "Sheet1";
                        }

                        //创建表框架
                        StringBuilder sbCom = new StringBuilder();
                        sbCom.Append("CREATE TABLE [");
                        sbCom.Append(tableName);
                        sbCom.Append("](");
                        sbCom.Append(colList);
                        sbCom.Append(")");
                        command.CommandText = sbCom.ToString();

                        //try
                        //{
                        command.ExecuteNonQuery();
                        //}
                        //catch
                        //{
                        //    //如果使用Create语句创建失败则直接创建Excel文件
                        //    CreateExcelFile(filePath, excelVersion, command.CommandText);
                        //}

                        //插入数据
                        sbCom = new StringBuilder();
                        sbCom.AppendFormat("INSERT INTO [{0}]({1}) VALUES({2})",
                                            tableName, sbColumNames.ToString(), sbColumValues.ToString());

                        int startIndex = pageIndex * (m_maxSheelSize - 1);
                        int endIndex = pageIndex * (m_maxSheelSize - 1) + insertRows;

                        for (int i = startIndex; i < endIndex; i++)
                        {
                            DataRow drData = dtSource.Rows[i];
                            OleDbParameterCollection dbParam = command.Parameters;
                            dbParam.Clear();
                            foreach (DataColumn dc in dtSource.Columns)
                            {
                                dbParam.Add(new OleDbParameter("@" + dc.ColumnName, GetOleDbTypeByDataColumn(dc)));
                                dbParam["@" + dc.ColumnName].Value = drData[dc.ColumnName];
                            }
                            command.CommandText = sbCom.ToString();
                            command.ExecuteNonQuery();
                        }

                        //计算剩余数据量
                        totalRows = totalRows - insertRows;
                        pageIndex++;

                    } while (totalRows > 0);
                }//end of using OleDbCommand
            }// end of  using OleDbConnection
        }